MonetDB

Sample Table

We will start mclient with the timer turned on. The timer will measure the time to execute the query.

mclient --timer="clock" -u voc -d voc

Then we will create the sample table:

CREATE TABLE tblSample ( Letter VARCHAR(10) UNIQUE                        , Number INT PRIMARY KEY                        , Calendar DATE NOT NULL );   INSERT INTO tblSample ( Letter, Number, Calendar )                                  VALUES ( 'A', 1, '2025-02-01' )                                            , ( 'B', 2, '2025-02-02' )                                            , ( 'C', 3, '2025-02-03' );   SELECT * FROM tblSample;   We can see that the query timing is 0.421 milliseconds.

Inserting Data with INSERT INTO

We can insert data through some application to MonetDB, by sending INSERT INTO statements. This will work great if we don' t load a lot of rows. If we are USING a lot of consecutive INSERT statements then we can have performance issues.

INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'D', 4, '2024-02-04' );
··· 1.000.000 X ···
INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'ZZZZZZZ', 1000004, '2240-02-04' );

We know that in MonetDB, we can insert several rows with the one INSERT statement. This will not save us, because we shouldn't use more than 20 rows per one INSERT STATEMENT. If we use more than that, we will decrease performance.

INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'D', 4, '2024-02-05' )
                                                        , ( 'E', 5, '2024-02-06' )
                                                        ··· 17 X ··· 
                                                        , ( 'F', 6, '2024-02-07' );    --20 rows at most

We can improve performance by following these 5 advice:

1. Disable autocommit.  Autocommit will commit the data after each INSERT statement. If we can avoid that, we can speed up things.
2. We should prepare our statement. That means that our statement will be parsed and cached once. After that, each consecutive INSERT query will use the same statement, just with another parameters.
3. Use batch processing. Instead of sending a million INSERT statements, we can sent 100 batches of 10.000 INSERT statements. This will reduce communication latency between application and MonetDB, it will reduce memory usage and will minimize locking of a table.
4. We should disable optimizer. Optimizer can speed up more complex statements, but there is nothing that can be improved for the simple INSERT statement.
5. We can temporarily disable table constraints like primary key, foreign key or unique. We can restore those constraints after the import.

SQL benchmark

We'll insert one row, with one INSERT INTO statement. Then we'll see if we can noticeably increase the speed by following the tips above.

INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'D', 4, '2024-02-05' );

First, we will disable our optimizer. SET sys.optimizer = 'minimal_pipe';

In order to delete constraints, we have to found out their names. We can do that from the system tables.

SELECT T.id, T.name, C.id, C.name, K.key_id, K.key_name FROM sys.tables  T INNER JOIN sys.columns C             ON T.id  = C.table_id INNER JOIN dependency_columns_on_keys K             ON C.id = K.column_id AND C.table_id = K.table_id WHERE T.name IN ( 'tblsample' );

Our constraints will be temporarily removed. ALTER TABLE tblSample DROP CONSTRAINT tblsample_number_pkey; ALTER TABLE tblSample DROP CONSTRAINT tblsample_letter_unique;

We will not remove constraint "NOT NULL", because that constraint will not restrain performance.

START TRANSACTION;

Now, we will start a transaction to disable autocommit.

Then we will prepare our statement. Zero is an ID of a prepared statement. PREPARE INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( ?, ?, ? );

After all this, we will again check the timing of our INSERT statement. We are now faster. EXECUTE 0( 'E', 5, '2024-02-06');

The last thing is that we have to change everything the way it was.

COMMIT;                                          -- finish transaction DEALLOCATE PREPARE ALL;                          -- delete prepared statement SET sys.optimizer = 'default_pipe';              -- turn on optimizer ALTER TABLE tblSample ADD UNIQUE ( Letter );     -- bring back unique constraint ALTER TABLE tblSample ADD PRIMARY KEY ( Number );-- bring back primary key constraint

Python Benchmark

We will now try INSERT with python script. In the blog post  "Connect to MonetDB from Python" we have already saw how to use python with MonetDB. Bellow is the script we will use now. This time we will insert 10 rows of data.

import pymonetdb
import time

connection = pymonetdb.connect(username="voc", password="voc", hostname="localhost", database="voc")
cursor = connection.cursor()
insert_statements = [
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('F', 6, '2024-02-06');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('G', 7, '2024-02-07');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('H', 8, '2024-02-08');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('I', 9, '2024-02-09');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('J', 10, '2024-02-10');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('K', 11, '2024-02-11');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('L', 12, '2024-02-12');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('M', 13, '2024-02-13');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('N', 14, '2024-02-14');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('O', 15, '2024-02-15');",
    ]
overall_start_time = time.time()
for i, sql in enumerate(insert_statements, start=1):
       cursor.execute(sql)
connection.commit()
overall_end_time = time.time()
total_time = overall_end_time - overall_start_time
print(f"\n⏳ Total execution time for all inserts: {total_time:.6f} seconds")
cursor.close()
connection.close()

Total execution time is 0.008114 seconds.

Python Benchmark With Accelerations

We can speed up our Python script by using all of the advice mentioned in the text above. This is how our python procedure now looks like:

import pymonetdb
import time

connection = pymonetdb.connect(username="voc", password="voc", hostname="localhost", database="voc")
cursor = connection.cursor()
overall_start_time = time.time()
cursor.execute("SET sys.optimizer = 'default_pipe';")
cursor.execute("ALTER TABLE tblSample DROP CONSTRAINT tblsample_number_pkey;")
cursor.execute("ALTER TABLE tblSample DROP CONSTRAINT tblsample_letter_unique;")
sql = "INSERT INTO tblSample (Letter, Number, Calendar) VALUES (%s, %s, %s);"
data = [    ('P', 16, '2024-02-16'),
            ('Q', 17, '2024-02-17'),
            ('R', 18, '2024-02-18'),
            ('S', 19, '2024-02-19'),
            ('T', 20, '2024-02-20'),
            ('U', 21, '2024-02-21'),
            ('V', 22, '2024-02-22'),
            ('W', 23, '2024-02-23'),
            ('X', 24, '2024-02-24'),
            ('Y', 25, '2024-02-25')
        ]
overall_start_time = time.time()
cursor.executemany(sql, data)
connection.commit()
overall_end_time = time.time()
cursor.execute("DEALLOCATE PREPARE ALL;")
cursor.execute("SET sys.optimizer = 'default_pipe';")
cursor.execute("ALTER TABLE tblSample ADD PRIMARY KEY ( Number )")
cursor.execute("ALTER TABLE tblSample ADD UNIQUE ( Letter );")
connection.commit()
total_time = overall_end_time - overall_start_time
print(f"\n⏳ Total execution time for all inserts: {total_time:.6f} seconds")
cursor.close()
connection.close()

We don't have to explicitly start a new transaction, pymonetdb will do that automatically.

Now our timing is 0.004584 seconds.

Timing

How to Measure Time of the Query Execution

mclient --timer="clock" -u voc -d voc

Timer can be "none" (default), "clock" or "performance".

Bellow we can results for these three modes of the timer switch.

mclient --timer="none" -u voc -d voc

mclient --timer="clock" -u voc -d voc

mclient --timer="performance" -u voc -d voc

When we use "performance", we get 4 results. "SQL" is time used for parsing. "Opt" is time used for optimizing statement. "Run" is time used for running the statement. "Clk" is total time used.

Query History

Data about executed statements is kept in the two tables. Those two tables can be returned with functions " sys.querylog_catalog()" and "sys.querylog_calls()". In order to work with those tables we have to login as administrator.

mclient -u monetdb -d voc

Password is "monetdb".

Data saved in these two tables is persistent between sessions. We can use procedure "sys.querylog_empty()" to clear content from those two tables.

CALL sys.querylog_empty(); –procedures are started with the "call" keyword

In the current session we can start logging with the procedure "querylog_enable()".

CALL querylog_enable();

After that, I will run statement "SELECT * FROM voc.tblSample;" three times.

This will be, now, the content of the "querylog_catalog()" table. SELECT * FROM querylog_catalog(); "Owner" is the user who started the query at "defined" time.

We can also read from the "querylog_calls()" table.

SELECT * FROM querylog_calls(); In this table we can see the "start" time and the "end" time of our query.

We can stop logging or queries before the end of the session with:

CALL querylog_disable();

Threshold

Each time we enable logging, our logging tables will become bigger and bigger. This can make the search for a query troublesome. In order to control amount od statements that will be logged, we can use "threshold" argument.

CALL  querylog_enable(5000);

The threshold will limit the logged statements to only those whose execution time is longer than 5000 milliseconds. This allows us to perform profiling, to find the queries that are sucking up our resources the most.

We will start this session as a admin. The only reason for this is because I want to show that we can move table to some other schema.

Password is "monetdb". mclient -u monetdb -d voc

Sample Tables

CREATE TABLE tblTurnover ( ID INT PRIMARY KEY,  SubID INT                         , IDwithNULL INT, Turnover INT DEFAULT 100 NOT NULL ); INSERT INTO tblTurnover ( ID, SubID, IDwithNULL, Turnover ) VALUES ( 1, 1, 1, 101 ), ( 2, 2, 2, 102 ), ( 3, 3, NULL, 103 );
CREATE TABLE tblCost ( ID INT UNIQUE, SubID INT                      , IDwithNULL INT, Cost INT );   INSERT INTO tblCost ( ID, SubID, IDwithNULL, Cost ) VALUES (1, 1, 1, 50), (2, 2, NULL, 50), (3, 3, NULL, 50);

Altering Our Tables

This is how we can change the name of our table. ALTER TABLE tblTurnover RENAME TO tblIncome; SELECT * FROM tblIncome;

We can change the name of a column. ALTER TABLE tblIncome RENAME COLUMN Turnover to Income; SELECT * FROM tblIncome;

We can alter our column to accept nulls. ALTER TABLE tblIncome ALTER COLUMN Income SET NULL;

Beside READ ONLY and READ WRITE, we also have INSERT ONLY. ALTER TABLE tblCost SET INSERT ONLY;

If we try to delete a row from INSERT ONLY table, that will be prohibited.

We can change default value for some column: ALTER TABLE tblIncome ALTER COLUMN Income SET DEFAULT 200;

We can delete DEFAULT value for Income column: ALTER TABLE tblIncome ALTER COLUMN Income DROP DEFAULT;

We can add new column in tblCost table. ALTER TABLE tblCost ADD COLUMN NewColumn BIGINT DEFAULT 88; SELECT * FROM tblCost;                                                                If we hadn't provided default value, then this new column would be filled with nulls.

We will delete our new column: ALTER TABLE tblCost DROP COLUMN NewColumn;

Both of our tables are created in VOC schema. We can move  tblCost table to sys schema , and we can move it back: ALTER TABLE tblCost SET SCHEMA voc; ALTER TABLE tblCost SET SCHEMA sys; We can check with "\d" command that our table is transferred to some other schema.

Removing Constraints

If want to remove some constraint (primary key, unique constraint, foreign key), we have to know the name of that constraint. In our example we have placed PRIMARY KEY constraint on the tblIncome table, column ID, and UNIQUE constraint on the tblCost table, column ID . We will first read from system table sys.tables to find what are IDs of our tables:

SELECT * FROM sys.tables WHERE name IN ('tblcost', 'tblincome');

Now that we know that our tables have IDs 13938 and 13946, we will search for our columns in the table sys.columns. Our columns have IDs 13932 and 13940.

SELECT * FROM sys.columns WHERE table_id IN ( 13938, 13946 ) AND name = 'id';

Next step is to find names of our constraints. We will find them in the "dependency_columns_on_keys"  table. We will filter this table by our tables and columns.

SELECT * FROM dependency_columns_on_keys WHERE table_id IN ( 13938, 13946 ) AND column_id IN ( 13940, 13932 );

We can now create a query that will return the names of the constraints on the specific columns:

SELECT T.id, T.name, C.id, C.name, K.key_id, K.key_name FROM sys.tables  T INNER JOIN sys.columns C       ON T.id  = C.table_id INNER JOIN dependency_columns_on_keys K       ON C.id = K.column_id AND C.table_id = K.table_id WHERE T.name IN ( 'tblincome', 'tblcost' ) AND C.name = 'id';

Now that we know our constraints names, we can delete them: ALTER TABLE tblIncome DROP CONSTRAINT tblturnover_id_pkey; ALTER TABLE tblCost DROP CONSTRAINT tblcost_id_unique;

Constraint on Several columns

When we want to make constraint that encompass several columns, then we have to make a constraint on a table itself. In that case, our CREATE TABLE statement would be like:
CREATE TABLE Tab1 ( Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10), PRIMARY KEY ( Col1, Col2 ), UNIQUE ( Col2, Col3 ));

We will now create PRIMARY KEY and UNIQUE constraints on our tables by using ALTER TABLE statement:
ALTER TABLE tblIncome ADD PRIMARY KEY ( ID, SubID );
ALTER TABLE tblCost ADD UNIQUE ( ID, SubID );

If we now run the statement that will return constraints names, we will get four rows as a result: SELECT T.id, T.name, C.id, C.name, K.key_id, K.key_name FROM sys.tables  T INNER JOIN sys.columns C                ON T.id  = C.table_id INNER JOIN dependency_columns_on_keys K                ON C.id = K.column_id AND C.table_id = K.table_id WHERE T.name IN ( 'tblincome', 'tblcost' ) AND C.name IN ( 'id', 'subid' );

Naming of Constraints

We saw earlier that the name of constraint is created automatically by concatenating a) table name, b) column name, c) constraint type ( tblturnover_id_pkey ). If the constraint encompasses several columns, for example id and subid, then the name will be like tblturnover_id_subid_ pkey. If we want to give our constraint a name then we have to use this syntax in CREATE TABLE statement:

CREATE TABLE Tab2 ( Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10), CONSTRAINT PKconstraint PRIMARY KEY ( Col1, Col2 )
                                                                        , CONSTRAINT Uconstraint UNIQUE ( Col2, Col3 ));

We will now give names to our constraints by using ALTER TABLE statement. We can not create another primary key constraint because table can only have one primary key constraint.

ALTER TABLE tblIncome ADD CONSTRAINT Primarius PRIMARY KEY ( ID, SubID );

We will first delete old primary key constraint, and then we will create a new one, that will have a custom name:

ALTER TABLE tblIncome DROP CONSTRAINT tblincome_id_subid_pkey;

ALTER TABLE tblIncome ADD CONSTRAINT Primarius PRIMARY KEY ( ID, SubID );

On the other side, it is possible to have many UNIQUE constraints on one table.
ALTER TABLE tblCost ADD CONSTRAINT Uniquous UNIQUE ( ID, SubID );

Now we have three constraints. We still have old UNIQUE constraint, and we have two new constraints with custom names. SELECT T.id, T.name, C.id, C.name, K.key_id, K.key_name, K.key_type FROM sys.tables  T INNER JOIN sys.columns C                ON T.id  = C.table_id INNER JOIN dependency_columns_on_keys K                ON C.id = K.column_id AND C.table_id = K.table_id WHERE T.name IN ( 'tblincome', 'tblcost' ) AND C.name IN ( 'id', 'subid' );

On the image above, we have a new column "key_type". Zero in this column means that we are dealing with primary key constraint, and 1 is unique constraint. In the table sys.keytypes we can see that there are 4 types of constraints. CHECK constraint is not supported in MonetDB. SELECT * FROM sys.key_types;

Foreign Key Constraint

We know that in Excel, "data validation" allows us to restrict that the user can only enter values (2)  ​​from a predefined list (1). Similarly, on the image below, the "key_type" column can only accept values ​​0,1,2,3,4 because those are the only values ​​listed in the main table sis.key_types (3). In relational databases, a list (1) is located within the Primary Key column (4) of the table that performs the function of a "Parent Table". The list (2), where we are limiting what to enter, is in the "Child Table", in the column declared as a "Foreign Key" (5).

This "primary key" > "foreign key" relation is called foreign key constraint, because "primary key" column defines what can be entered into "foreign key" column.
When creating a table, we should use a syntax like this one to create foreign key constraint:

CREATE TABLE Tab3 ( Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10), CONSTRAINT PkFk FOREIGN KEY ( Col1, Col2 )
                                                                          REFERENCES Tab2 ( Col1, Col2 ) ); 

We can omit the part ", CONSTRAINT PkFk", but in that case our constraint will get the default name " tab3_col1_col2_fkey".

We can find data about foreign key constraints in this system table. 14009 is Tab2 which has primary key 14277 "pkconstraint". On the other sid is Tab3 with ID 14294, which has foreign key 14292 pkfk. SELECT * FROM sys.dependency_keys_on_foreignkeys;

In order to find what columns are included in some foreign key constraint, we should read from this system table. SELECT * FROM describe_foreign_keys;

We can find everything about FK constraints in the table Tab3 with this SELECT query: SELECT pk_t PkTable, pk_c PkColumn, key_name PkName      , fk_t FkTable, fk_c FkColumn, fk_name FkName FROM sys.dependency_keys_on_foreignkeys Fk INNER JOIN describe_foreign_keys DFk        ON Fk.fk_name = DFk.fk WHERE fk_t = 'tab3';

We will now add foreign key constraints with ALTER TABLE statement. We will try to link "id" columns. This will fail. Foreign key constraint can only relate to column ( or columns ) that are declared as primary key or as unique columns.

ALTER TABLE tblCost ADD CONSTRAINT FromTblIncomeConstraint FOREIGN KEY ( id ) REFERENCES tblIncome ( id );

We will alter our statement to relate to primary key in the table tblIncome. Current primary key in the table tblIncome is composite key ( id, subid ). We will use that.

ALTER TABLE tblCost ADD CONSTRAINT FromTblIncomeConstraint FOREIGN KEY ( id, subid ) REFERENCES tblIncome ( id, subid );

Foreign key constraint now limits what can be placed into tblCost table. We can not add "4" into ID column in the table tblCost. That would violate the foreign key constraint. We can not delete any row from the table tblIncome because each row is referenced in the tblCost table, and that would, again, violate the foreign key constraint.
INSERT INTO tblCost ( ID, SubID, IDwithNull, Cost ) VALUES ( 4, 4, null, 50 );
DELETE FROM tblIncome WHERE ID = 3;

RESTRICT / CASCADE when Deleting Constraints and Columns

We will try to delete ID column in tblCost table. ALTER TABLE tblCost DROP COLUMN ID;	We will try to delete primary key constraint in the tblIncome table. ALTER TABLE tblIncome DROP CONSTRAINT Primarius;

We will fail big. This is because the default mode is RESTRICT. By using CASCADE, we would be able to delete this column and this constraint.

START TRANSACTION; ALTER TABLE tblIncome DROP CONSTRAINT Primarius CASCADE; ROLLBACK;

If we rollback our transaction, we want really delete the constraint. I don't want to delete it.

NULL Handling         

We can not insert NULL into primary key column. Primary key column doesn't allow nulls.

INSERT INTO tblIncome ( ID, SubID, IDwithNULL, Income ) VALUES ( null, 4, null, 104 );

tblIncome

tblCost

Column IDwithNULL in tblIncome table has one field with null. Constraint UNIQUE NULLS NOT DISTINCT can be added to this column, because there will be no duplicate nulls (we only have single null). ALTER TABLE tblIncome ADD CONSTRAINT IncomeUniqueNull UNIQUE NULLS NOT DISTINCT ( IDwithNULL );

Column IDwithNULL in tblCost table has two fields with nulls. Suprise is that UNIQUE NULLS NOT DISTINCT constraint can be added to this column. ALTER TABLE tblCost ADD CONSTRAINT CostUniqueNull UNIQUE NULLS NOT DISTINCT ( IDwithNULL );

This constraint will only have effect when we try to add new null: INSERT INTO tblIncome ( id, subid, idwithnull, income ) VALUES ( 4, 4, null, 50 );

The default value for this subclause is NULLS DISTINCT. By default, we will consider all nulls to be distinct.

What is Not Supported in Foreign Key Constraint

For foreign key constraint, match can be only SIMPLE, on update can only be RESTRICT, on delete can only be RESTRICT. Other options are not supported so we will ignore them.

ALTER TABLE Tab3 ADD Constraint PkFk2 FOREIGN KEY ( Col1, Col2 ) REFERENCES Tab2 ( Col1, Col2 )
MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT;

The ON UPDATE RESTRICT and ON DELETE RESTRICT subclauses mean that it is not possible to update or delete a key in the parent table if there is a row in the child table that referenced that key.

MATCH SIMPLE means that if a foreign key is a composite key ( it is made of several columns ), and if one of the columns in the foreign key has a null, then the referential integrity will not be checked for that foreign key.

Error Avoidance

As always, if we use IF NOT EXISTS or IF EXISTS, we will avoid errors. We will always get "operation successful".

CREATE TABLE IF NOT EXISTS employees ( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10, 2) );
ALTER TABLE IF EXISTS employees ADD COLUMN hire_date DATE;

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.