0310 Schemas in MonetDB

Difference Between Schema and Database

Schema is logical container for objects like tables, views, functions, indices, procedures, triggers….   ← On one server machine we can install several server applications. For example, on one server machine we can install SQL Server and MonetDB server (or we can have several instances of the same server application). In SQL Server, objects are organized in the fashion: Server Machine > Server Application > Database > Schema > Object. In MonetDB, there are no databases, so hierarchy is: Server Machine > Server Application      >       Schema > Object.

Separate server application (like SQL Server and MonetDB on the image) are totally independent. Databases are almost like that:

This is what differentiate databases: – Each database is a separate process. – Each database has its own system tables and storage structures. – For each database we have to make a separate connection. – We can not query tables that belong to different databases.

This is what connects databases: – Some settings can be defined on the server application level (CPU, memory, disk quotas, cache size). – You access databases through the same Hostname/IP Address.

Schemas are not like databases. Schemas are just a way to organize our objects. We benefit from schemas because:

• Schemas allow us to organize our objects.
• Schemas allow us to differently set security and access control.
• Schemas allow us to have objects that have the same name, but they have to be in the separate schemas
  ( MySchema.TableName, YourSchema.TableName ).
• We can query tables from different schemas with one SELECT statement, but only if we have access rights.

Schemas in MonetDB

System table sys.schemas has a list of all of our schemas. SELECT * FROM sys.schemas; Each server will have system schemas, like sys, tmp, json, profiler, logging, information_schema. On the image, we can see that the only user schema is "voc" schema. In one moment, only one schema can be current schema. That means: – We can call objects from that schema without specifying schema name (TableName instead of MySchema.TableName). – Table created, without specifying schema, will be created in the current schema.

We can get the name of the current schema with statement: SELECT CURRENT_SCHEMA;

We can create new schema with statement: CREATE SCHEMA newSchema; This will fail because only members of monetdb group can create schemas.

Creation of the New Schema

We will exit "mclient" session in order to login as administrators. Now we can create some new schemas. mclient -u monetdb -d voc REMEMBER: administrators group password is "monetdb".

Notice that, when logged as administrators, our current schema will be "sys" schema. We will change our current schema to VOC schema by using statement: SET SCHEMA VOC;   * mclient -u monetdb -d voc    VOC provided during logon is for the database server. This is not schema name. It is server name. We log in into the server.

We will create schema VOC2. CREATE SCHEMA VOC2; After that we will create table "NewTable". This table will be created in the current schema (VOC). CREATE TABLE NewTable (Text VARCHAR(10));

This SELECT statement will fail. Our current schema is VOC. There is no table "NewTable" in VOC2 schema. SELECT * FROM VOC2.NewTable;

We have explicitly to create table inside of the VOC2 schema. Only then we can read from this table. CREATE TABLE VOC2.NewTable  (Text VARCHAR(10)); SELECT * FROM VOC2.NewTable; Notice that we now have two tables with the same name. VOC.NewTable               VOC2.NewTable

We will write something into our two tables so we can make a distinction between them:

INSERT INTO NewTable ( Text ) VALUES ( 'VOC');

INSERT INTO VOC2.NewTable ( Text ) VALUES ( 'VOC2');

If we read from the NewTable, we will read from our current schema. SELECT * FROM NewTable;

We have to be explicit to read from VOC2 schema. SELECT * FROM VOC2.NewTable;

We can change current schema with this statement: SET SCHEMA VOC2;   This time we will read VOC2.NewTable, and not VOC.NewTable. SELECT * FROM NewTable;

As administrators, we can read from different schemas with one SELECT query: SELECT * FROM VOC.NewTable UNION ALL SELECT * FROM VOC2.NewTable; If we try the same from VOC account, we would get an error: SELECT: access denied for voc to table 'voc2.newtable'

Ownership of a Schema

We can find owner of the schema by using an information schema view. Administrators are the only ones who can create schemas. That is why we always have "monetdb" in the schema_owner column.

SELECT * FROM information_schema.schemata;

Authorization of a Schema

We know that the user "voc" has right to use "voc" schema. This is something that we can find in sys.schemas table. In the column "authorization" we can see number 7109.  What does that number represent? SELECT * FROM sys.schemas;

Each user is represented by their default role. This default role is created when a user is created. The default role is bound to one user and cannot have other users. User "voc" has a default role with ID 7109. We can see this in the system table sys.users. The schema "voc" has authorization 7109. The conclusion is that the user "voc" is the only user who can create and modify and read objects in this schema (besides the administrators). SELECT name, fullname, default_role, default_schema FROM sys.users;

Notice above, that user voc has the default schema 7110*, and role monetdb has the default schema 2000*. When we log in as such users, this will be our initial current schema.

If we go back to one of the first posts in this series, we will find that we gave authorization to the user " VOC " during creation of VOC schema:   CREATE SCHEMA "VOC" AUTHORIZATION "VOC";

During creation of the schema VOC2, we haven't authorized anyone. In that case authority will belong to "MonetDB Admin". We can see that in images above. Authorization for VOC2 schema belogs to default role 3, and that is monetdb group of adiminstrators.

After the keyword AUTHORIZATION, we can have either a role or a user name. Only one username or role can be authorized. If we want for several users to control some schema, then we have to give the authorization to the role to which those users belong. After creation of a schema, it is not possible to change its authorization. Because of this, it is always better to give authorization to a role, than to a user. Afterwards we can just add or remove users to that role.

Easy Creation of a Schema for Some Role/User

We don't have to provide name for a schema, just authorization. In that case the name of the group/user which have authorization will be also the name of a schema. CREATE SCHEMA AUTHORIZATION MonetDB;   We created a schema authorized to MonetDB group. In the image on the right side, we can see that now we have a schema with a name "monetdb".

Renaming of a Schema

ALTER SCHEMA voc2 RENAME TO voc3;

We can only rename schema if there are no objects that depends on the name of that schema.

We will now create a VIEW, an object that is dependent on schema voc3. CREATE VIEW aaa AS SELECT * FROM voc3.NewTable;

Because of that view, it is no more possible to rename this schema.   ALTER SCHEMA voc3 RENAME TO voc2;

Deleting a Schema

We will first change current schema to VOC, because it is not possible to delete current schema:
SET SCHEMA VOC;

DROP SCHEMA voc3;          --We can not delete schema because there is a view that depends on that schema.

We will delete our View. DROP VIEW VOC3.aaa;

But it is still impossible to delete schema, because there is the table "New Table" in it.

In MonetDB, we can not easily delete the schema because RESTRICT is the default mode: DROP SCHEMA voc3 RESTRICT; We have to supply the keyword CASCADE to easily delete schema. This means that schema will be deleted together with all of the dependent objects (tables and views): DROP SCHEMA voc3 CASCADE;

Avoiding Errors

CREATE SCHEMA IF NOT EXISTS SchemaName; DROP SCHEMA IF EXISTS SchemaName;

By using "IF NOT EXISTS" and "IF EXISTS", we can avoid getting error messages.