MonetDB

File Backup

Our database ( "voc" ) is just a folder inside of the DBfarm folder. We can backup our database by making a copy of the "voc" folder. The problem is that before doing this we have to wait for the users to finish their queries, and then to prevent them from running new queries.

Disconnecting Users

We can check other users by reading form the sessions table. – Column "idle" is timestamp for session "1". This is the moment when that session finished running its last statement. – Column "idle" is null for session "0". In this session we are running current "SELECT … FROM sessions" statement, so this session is busy.

select sessionid, username, idle, login from sys.sessions;

If we estimate that database is not overloaded, then we can assume that all of the existing queries will end in 30 seconds. We will let existing transaction to run 30 seconds more, and then we will close all the connections.   call sys.shutdown(30); This procedure can be called only by the administrator.

If the "voc" user try to run a query, he will get "unexpected end of file" and his session will end. He will not be allowed to login again.

Shutdown procedure is using SIGTERM command. This procedure will ask database to shutdown, but the database can refuse to do it. In such circumstances we can use the second argument "call sys.shutdown(30, true);". Now we use SIGKILL command, and database will not be able to refuse shutting down.

After calling "sys.shutdown()", admin will still be able to run statements, not only during 30 seconds, but until he decides to leave the session. If admin quit the session, he will not be able to log into the server again.

At this moment, our database is not locked, but no one can connect to it.

monetdb status -l voc

Connection to this database will be possible only after the restart. But, after stopping the database, we want to prevent users from starting it again. We will lock our database so that only admin can start it again.   monetdb lock voc

The last step is to fully stop our database:   monetdb stop voc

Restoring Backuped File

We now just have to create a copy of the database folder. This copy can be placed on the same computer or we can move it to some other computer.

We can now release the new database and we can start it. monetdb release vocCopy mclient -u monetdb -d vocCopy

Cleaning Up After File Backup

We will quit and stop database vocCopy. quit monetdb stop vocCopy Now we can delete this vocCopy database: monetdb destroy vocCopy

Original "voc" database is still in the maintenance mode. We will release it with "monetdb release voc".

Hot Backup of a Database

A hot backup is the same as a file backup, but we don't have to stop the database. We'll make a copy of the database files while our database is still running.

Local Copy

As admin, we will call "hot_snapshot" procedure. The result will be our "voc" folder packed inside of the "tar" format. mclient -u monetdb -d voc    #password monetdb call sys.hot_snapshot('/home/fffovde/Desktop/backup.tar');
It is also possible to create compressed tar file. We can use ".lz4, .gz, .bz2, .xz" compression.   call sys.hot_snapshot('/home/fffovde/Desktop/backup.tar.lz4');

We can grant privileges of using our procedures to any user.

grant execute on procedure sys.hot_snapshot(string) to voc; grant execute on procedure sys.shutdown(tinyint) to voc;

Remote Copy

We will now backup database from the green server. Green server allows external access from the network. I explained how to create green server in this blog post "link".

We will open the green Database, on the green server. monetdbd start /home/sima/monetdb/DBfarmG monetdb start DatabaseG

From the "voc" server, we will login to the green server: mclient -h 192.168.100.145 -p 50000 -u monetdb -d DatabaseG

We will call "hot_snapshot", but we will provide second argument "false". That means that backup shouldn't be created on the server, but on the client. Backup of the green database will appear on the "voc" server. call sys.hot_snapshot('/home/fffovde/Desktop/backupGreen.tar', false);

Restoring Hot Backup

First, we will extract "voc" folder from the "tar" or compressed file.

I will place folder "DatabaseG" into farm folder on the "voc" server, and I will log into it. mclient -u monetdb -d DatabaseG  #password monetdb Now, we have green database on the "voc" server.

Cleaning Up After Hot Backup

I will quit green database session, and I will delete this database on the "voc" server. quit monetdb stop DatabaseG monetdb destroy DatabaseG

Database Schema ( as a file )

A database schema is a list of all database objects and their attributes, written in a formal language. If we already have a ubiquitous formal language for creating database objects, SQL, then we should use SQL language for writing our schema.

If our server understands a particular dialect of SQL, it can read the list of SQL statements and, based on that, can create the entire specified database. Specification is provided as a file with a "*.sql" extension.

A schema is useful when we want to create a database according to a specification. But we want to back up our database, and for that we need a backup of the data in addition to the schema. We can include INSERT INTO statements with all the data, in a schema. Now we have a file that is an exact symbolic image of our database, and can be used as backup file.

We already used such file in the blog post about sample MonetDB database "link".   This file, with a data, can also be considered as a schema file, in the broader sense.

Qualities of the Good Schemas

Database objects should be created in the proper order. For example, tables should be created before we create views that are based on those tables. Here we can see recommended order of statements:

a) CREATE SCHEMAS b) CREATE SEQUENCES c) CREATE TABLES d) ALTER TABLES for FOREIGN KEYS e) CREATE INDEXES

f) CREATE VIEWS g) CREATE FUNCTIONS and PROCEDURES h) CREATE TRIGGERS i) INSERT INTO data j) GRANT/REVOKE

When running schema file, we should wrap it into transaction to avoid incomplete creation.

Credentials

In this blog post "link", I have show you how to create a file with credentials, and we placed it into $HOME folder ( "/home/fffovde" ) on the "voc" server. If we have such file, we don't need to provide credentials, for MonetDB, every time we run some command in the shell. You can notice bellow that I will run my Msqldump commands without providing credentials, by using such file. Users can only backup database objects they have access to, so with administrator account we will be able to backup everything.

Msqldump

Msqldump is a console program used to create a schema from an existing MonetDB database. The resulting file can be used for backups or to migrate the database to another MonetDB server. With some manual tweaking of this file, we can use it to migrate database to a server other than MonetDB (perhaps Postgres or MySQL).

We should redirect the result of this app to the file on our computer. msqldump -d voc > /home/fffovde/Desktop/voc_db.sql
If we don't, the long schema will go to stdin, and we usually don't want that.

By default, instead of the INSERT INTO statements, our schema will return "COPY FROM stdin" statements. This will make restoring of the database faster.
We can use "-e" switch to add NO ESCAPE clause to COPY statement. msqldump -d voc -e > /home/fffovde/Desktop/voc_db.sql

For returning INSERT INTO statements, we should use "-N" switch. msqldump -d voc -N > /home/fffovde/Desktop/voc_db.sql

Partial Backup of Database

Option "-f" means that we will only backup functions. msqldump -d voc -f > /home/fffovde/Desktop/voc_db.sql
With option "-t", we can backup only one table. We should provide fully qualified name of a table, because default schema for administrator is sys ( we are logged in as administrator ). msqldump -d voc -t voc.invoices > /home/fffovde/Desktop/voc_db.sql
With wild cards we can backup a set of tables with a similar name. This code bellow would return only tables "passengers, seafarers and soldiers". msqldump -d voc -t voc.%ers > /home/fffovde/Desktop/voc_db.sql
With upper letter "-D voc", we will export database without data. msqldump -D voc > /home/fffovde/Desktop/voc_db.sql

Location of A Schema File

We don't have to use redirection to a file. We can use option "-o". msqldump -d voc -o /home/fffovde/Desktop/voc_db.sql

We can export our database to a folder. In that case switch "-O" is using upper letter. msqldump -d voc -O /home/fffovde/Desktop/voc_db_folder
If we export database to a folder, that folder will contain all of the DDL ( data definition language ) statements inside of the "dump.sql" file, but the data will be one separate CSV file for each table. CSV files will not have a header (because we have CREATE TABLE statements in the "dump.sql"), delimiter will be TAB.
Only when we use export to a folder, it is possible to use "-x" option. CSV files will be compressed to that file format. We can use "lz4,gz,bz2,xz". msqldump -d voc -x lz4 -O /home/fffovde/Desktop/voc_db_folder

Credentials in the Command

I will now move my credentials to temporary folder, so from now we will have to provide credentials to Msqldump. mv /home/fffovde/.monetdb /tmp/.monetdb
Name of our user now has to be provided with the switch "-u". msqldump -u monetdb -d voc -o /home/fffovde/Desktop/voc_db.sql	Password is always provided separately.

I will now jump into green server. I explained how to create green server in this blog post "link". I am using this server because that server is set to accept external connections. Green server has IP address "192.168.100.145" and database DatabaseG.
I will run this command on the green server, so I am just doing backup locally. msqldump -h 192.168.100.145 -p 50000 -u monetdb -d DatabaseG -o /home/sima/Desktop/DatabaseG_db.sql
I will now run the same command from the "voc" server. This will backup "DatabaseG", from the green server, onto "voc" server. msqldump -h 192.168.100.145 -p 50000 -u monetdb -d DatabaseG -o /home/fffovde/Desktop/DatabaseG_db.sql
In the previous blogpost "link", I have created purple server, which is protected with TLS. IP address of this server is 192.168.100.152, and its database is "tlsDB". I will now run this command on the "voc" server to backup database from the purple server. msqldump -u monetdb -d 'monetdbs://192.168.100.152:50000/tlsDB?cert=/home/fffovde/selfsigned.crt' -o /home/fffovde/Desktop/tlsDB_db.sql

By providing IP address and the port number, we can backup database locally, or we can pull it from the remote server, even if it is protected with TLS.

Other Msqldump Options

We will run these commands on the "voc" server.

Whichever way we create a backup, at the beginning of the sql file, we will have a welcome message. msqldump -u monetdb -d voc -o /home/fffovde/Desktop/voc_db.sql
We have to add "quiet" switch to avoid that message. msqldump -q -u monetdb -d voc -o /home/fffovde/Desktop/voc_db.sql

If we use "-X" option, we will get message for every step msqldump is doing. msqldump -X -u monetdb -d voc -o /home/fffovde/Desktop/voc_db.sql

By running this command in the shell, we will get a list of all of the possible options in Msqldump.   msqldump -?   We can notice that longer syntax for any option exists, too: msqldump --help

Usage: msqldump [ options ] [ dbname ] -h hostname | --host=hostname    host to connect to -p portnr   | --port=portnr      port to connect to -u user     | --user=user        user id -d database | --database=database  database to connect to -o filename | --output=filename  write dump to filename -O dir      | --outputdir=dir    write multi-file dump to dir -x ext      | --compression=ext  compression method ext for multi-file dump -f          | --functions        dump functions -t table    | --table=table      dump a database table -D          | --describe         describe database -N          | --inserts          use INSERT INTO statements -e          | --noescape         use NO ESCAPE -q          | --quiet            don't print welcome message -X          | --Xdebug           trace mapi network interaction -?          | --help             show this usage message --functions and --table are mutually exclusive --output and --outputdir are mutually exclusive --inserts and --outputdir are mutually exclusive --compression only has effect with --outputdir

 Environment Variables

Environment variables are global variables that can be accessed by any process. They are key-value pairs that provide a way for processes to communicate with each other and the operating system.

We can list all the environment variables with "printenv".

For specific variables we just grep the result of "printenv": printenv | grep home

If we know the name of variable, then we can read its value with "echo": echo "$HOME"

My local time is set to Serbian. echo "$LC_TIME"    #sr_RS date # нед, 10. авг 2025.  09:48:22 CEST

For this session I will set it to English. Set means that we will create or update variable. export LC_TIME=en_US.utf8 date # Sun 10 Aug 2025 09:56:16 AM CEST

With "unset", we will remove variable from the current session. unset LC_TIME echo "$LC_TIME"

# export LC_TIME=en_US.utf8 This command will set environment variable permanently if we write it to the file "$HOME/.bashrc". cat /home/fffovde/.bashrc After that, we reload this file with: source ~/.bashrc  #tilda is the same as $HOME, or "/home/fffovde" in this case.

DOTMONETDBFILE Environment Variable

If we want to make our Msqldump commands shorter, we can place values for some of the options into file. DOTMONETDBFILE is an environment variable with the path toward that file.

In the temporary folder I already have the file ".monetdb". It has username and password. This is the file we have moved before. I will add IP address and port number of the green server into this file. nano /tmp/.monetdb I will set my DOTMONETDBFILE environment variable to this file on the Desktop. This will be valid for one session. export DOTMONETDBFILE=/tmp/.monetdb

user=monetdb password=monetdb host=192.168.100.145 port=50000

Now we can use Msqldump, to backup DatabaseG from the green computer, without providing all the credentials inside of the command. All the credentials are already stored in the ".monetdb" file. msqldump -d DatabaseG -o /home/fffovde/Desktop/DatabaseG_db.sql

Default Folders for .monetdb File

DOTMONETDBFILE is only used if we want to keep ".monetdb" in non-standard folder. For standard folders, we don't need DOTMONETDBFILE. We just have to place the file into one of the standard folders, and this is how we used ".monetdb" file before the talk about environment variables.

1) Current directory. 2) Path inside of the $XDG_CONFIG_HOME variable (if exists). 3) $HOME directory.

Msqldump will first look for a ".monetdb" in the current directory, then in $XDG_CONFIG_HOME, and at the end in the $HOME ( /home/fffovde ).

I will move ".monetdb" file into "/home/fffovde", and I will disable DOTMONETDBFILE: mv /tmp/.monetdb /home/fffovde/ unset DOTMONETDBFILE
If we now try to run Msqldump, it will read ".monetdb" from the current folder and will work. msqldump -d DatabaseG -o /home/fffovde/Desktop/DatabaseG_db.sql

Ignoring Default Folders

If we set DOTMONETDBFILE to empty string, Msqldump will ignore all of the configuration files, and will always ask for credentials.

# export DOTMONETDBFILE=""

From here you can download "voc" database sql dump file (schema file).

MonetDB can not encrypt its communication over the wire. This is leaved to specialized programs that are called "TLS Termination Proxy". In that way we get superb protection and flexibility provided by these tools. Clients must also have support for TLS. "Mclient" and "Pymonetdb" have support for TLS.

TLS (Transport Layer Security ) is a critical internet protocol that encrypts and authenticates data in transit. TLS is widely tested and trusted, and it is used in HTTPS, email, and VPNs to prevent eavesdropping and tampering. Most popular client tools (DBeaver, JDBC, Python libraries, etc.) already support TLS.

Purple Server

I will create totally new server for this blog post. I will call it "Purple Server ". This is the server on the new virtual machine. I am currently the user "fff". I will add myself to "monetdb" group. This group was created after the installation of the MonetDB. People from this group can start a database. sudo adduser fff monetdb For this to apply, we have to log out, and then to log in.

Then, I will start monetdbd servis, and I will create a database "tlsDB". sudo systemctl start monetdbd monetdb create tlsDB monetdb release tlsDB This database will be created in the default folder "/var/monetdb5/dbfarm/". This command bellow will make my server the full fledged systemctl controled daemon. That means that MonetDB will automatically start when I start my computer. systemctl enable monetdbd

Self-Signed Certificate

In this tutorial we will create certificate ourselves, and we will use that certificate both on the server and on the client.

We will change default port of the MonetDB to 49999. This is because I want to use the port 50000 for TLS connection.

monetdbd set port=49999 /var/monetdb5/dbfarm

Internal communication will use 127.0.0.1:49999, and external encrypted communication will use 192.168.100.152:50000. The only way to access MonetDB is through the TLS proxy server.

Creation of a Self-Signed Certificate

At the location "/etc/ssl/private" we have a folder that is only accessible to root. We can use any other folder, but it is important that folder has restrictive permissions, and that only admin can access it. We can not access this folder with "sudo cd" because sudo doesn't work with built-in command "cd". We will first take the role of the root with "sudo -i", and then we will "cd /etc/ssl/private". That will make this folder our current folder.

Inside of this folder I will make a script. touch /etc/ssl/private/SelfCertScript.sh I will open this script in nano. I will paste the code. nano SelfCertScript.sh This is the version where we use IP address for the server. I don't have a domain name, so I will use this version. =>

#!/bin/bash set -euo pipefail IP="192.168.100.152" DAYS=90 KEY="selfsigned.key" CRT="selfsigned.crt" openssl req -x509 -newkey rsa:2048 -sha256 -nodes \ -keyout "$KEY" -out "$CRT" -days "$DAYS" \ -subj "/CN=$IP" \ -addext "subjectAltName=IP:$IP"

This is the version you use, if you have a domain name.   #!/bin/bash set -euo pipefail   DOMAIN="dbhost.mymonetdb.org" DAYS=90 KEY="selfsigned.key" CRT="selfsigned.crt"   openssl req -x509 -newkey rsa:2048 -sha256 -nodes \ -keyout "$KEY" -out "$CRT" -days "$DAYS" \ -subj "/CN=$DOMAIN" \ -addext " subjectAltName=DNS:$DOMAIN"

This is the version with the both.   #!/bin/bash set -euo pipefail   IP="192.168.100.152" DOMAIN="dbhost.mymonetdb.org" DAYS=90 KEY="selfsigned.key" CRT="selfsigned.crt"   openssl req -x509 -newkey rsa:2048 -sha256 -nodes \ -keyout "$KEY" -out "$CRT" -days "$DAYS" \ -subj "/CN=$DOMAIN" \ -addext "subjectAltName=DNS:$DOMAIN,IP:$IP"

https://dbhost.mymonetdb.org https://192.168.100.152

If we use IP in the script, then the clients must use IP. If we use domain name, then the clients must use domain name. If we use both in the script, then the client tool can connect both with the IP or with the domain name.

We will now run our script. bash ./SelfCertScript.sh Two files will be created inside of the private folder. "selfsigned.key" is our secret that must be secure. This is why is important to use folder accessible only to root. "selfsigned.crt" is file that we will send to clients so that they can access our database.

We can also change permissions on files. sudo chmod 600 /etc/ssl/private/selfsigned.*

Only root now have read, write rights on our files.

Using Some Other Folder

We can also keep our script, key and certificate in some other folder. First, we will create a folder, and then we can make that folder only accessible to a root. "chmod 700" means read/write/execute rights.

sudo mkdir /etc/ssl/private2 sudo chown root:root /etc/ssl/private2 sudo chmod 700 /etc/ssl/private2

Script Explanation

!/bin/bash	Shebang: run this script with the Bash shell (not sh, zsh, etc.).
-e (errexit)	Exit immediately if any simple command returns a non-zero status (if there is an error).
-u (nounset)	Treat the use of unset or empty variables as an error, and exit.
-o pipefail	in a pipeline a | b | c, the pipeline's exit status is the first non-zero exit code among a, b, or c (instead of just c).

Let's dissect this line.

openssl req -x509 -newkey rsa:2048 -sha256 -nodes -keyout "$KEY" -out "$CRT" -days "$DAYS" -subj "/CN=$DOMAIN" -addext "subjectAltName=DNS:$DOMAIN,IP:$IP"

openssl req	We start self-signed certificate creation process.
-x509	Create a self-signed certificate instead of a certificate signing request (CSR). CSR is for commercial certificates.
-newkey rsa:2048	Generate a new RSA private key of 2048 bits.
-sha256	Use SHA-256 as the hash algorithm.
-nodes	Private key will not be encrypted. This is useful for automation, because there is no need for a password.
-keyout "$KEY_FILE"	Private key will be saved to this file.
-out "$CRT_FILE"	Certificate will be saved to this file.
-days "$EXPIRATION_DAYS"	Certificate will be valid for 90 days. Clients usually do not trust certificates with a longer lifespan.
-subj "/CN=$IP"	IP address (or domain name).
-addext "subjectAltName=IP:$IP"	The same as above, but modern, because it can accept several IP addresses or domains.

Stunnel

"Stunnel" is the name of a program that we will use as a "TLS Termination Proxy". We now have to install it and configure it.

apt install stunnel4

We install it. We are already the root, so we don't need "sudo". stunnel -version

which stunnel

We find its installation folder.

cd /etc/stunnel touch monetdb.conf

We'll go to stunnel installation folder. There we will create configuration file.

We'll open this file in nano, and we will paste the code.   nano monetdb.conf

foreground = yes cert = /etc/ssl/private/selfsigned.crt key  = /etc/ssl/private/selfsigned.key [monetdb] accept  = 0.0.0.0:50000 connect = 127.0.0.1:49999

Stunnel Systemd Service

MonetDB service will always run automatically, because it is controlled by systemd. We want the same for stunnel. For stunnel to become a service, first we have to create configuration file for that service (systemd unit file).

First, we go to folder where we have to place systemd unit file. cd /etc/systemd/system   We create new file: touch stunnel-monetdb.service   We open this file in nano text editor: nano stunnel-monetdb.service   We paste our code into it:

[Unit] Description=Stunnel TLS for MonetDB After=network.target [Service] ExecStart=/usr/bin/stunnel /etc/stunnel/monetdb.conf ExecReload=/bin/kill -HUP $MAINPID Restart=on-failure PrivateTmp=yes [Install] WantedBy=multi-user.target

Let's explain parts of this systemd unit file:

After=network.target	This is a prerequisite. Stunnel should only be started after the network becomes functional during system startup.
ExecStart=/usr/bin/stunnel /etc/stunnel/monetdb.conf	Start stunnel program, with specified configuration file.
ExecReload=/bin/kill -HUP $MAINPID	$MAINPID is process ID of the stunnel. "-HUP" means that stunnel should reload without restarting process.
Restart=on-failure	Restart stunnel if it crushes.
PrivateTmp=yes	Instead of /tmp and /var/tmp, service will use its own private temporary folders.
WantedBy=multi-user.target	During the startup, stunnel should start after some basic services already started.

Now we have to use this "stunnel-monetdb.service" file to register our new service with systemd:

sudo systemctl daemon-reexec	Used to refresh systemd.
sudo systemctl daemon-reload	Reload all of the "systemd unit files". Now, it will include our "stunnel-monetdb.service" file.
sudo systemctl enable stunnel-monetdb.service	To have the service start automatically during reboot.
sudo systemctl start stunnel-monetdb.service	Start our service immediately, without waiting for the first reboot.

We can now check who is listening on port 50.000. So, port 50.000 is listened by stunnel.

sudo ss -tnlp | grep 50000

FYI: MonetDB Systemd Unit File

We can find location on MonetDB systemd unit file with this command: systemctl status monetdbd

We can read its content like this: systemctl cat monetdbd

[Unit] Description=MonetDB database server daemon Documentation=man:monetdbd https://www.monetdb.org/documentation/admin-guide/manpages/monetdbd/ After=network.target [Service] Type=forking User=monetdb Group=monetdb ExecStart=/usr/bin/monetdbd-11.53.3 start /var/monetdb5/dbfarm ExecStop=/usr/bin/monetdbd-11.53.3 stop /var/monetdb5/dbfarm Restart=on-failure PIDFile=/run/monetdb/merovingian.pid PrivateDevices=no ProtectSystem=full ProtectHome=read-only [Install] WantedBy=multi-user.target

TLS Connection from the Local Computer

I will exit being root with "exit". I am running this on the purple server. URL is of the purple server. mclient -d monetdbs://192.168.100.152:50000/tlsDB It won't work because mclient doesn't have access to the certificate.

We will copy content of our certificate to $HOME folder. We need folder where mclient has access.

sudo cat /etc/ssl/private/selfsigned.crt >$HOME/selfsigned.crt

We will use that folder to signal mclient where to find certificate. mclient -d "monetdbs://192.168.100.152:50000/tlsDB?cert=$HOME/selfsigned.crt" Now, we have access.

If you continue to get message "certificate verify failed", check your SelfCertScript.sh. Make sure that there are no invisible characters in it. After that recreate your certificate and copy it again for mclient. Try to restart your computer a few times. I had problems each time, and restarting of the computer helped.

TLS Connection from the Remote Computer

On the other virtual machine, I will place "selfsigned.crt" into home directory. ls -alh selfsigned.crt

The same command will work here, too. mclient -d "monetdbs://192.168.100.152:50000/tlsDB?cert=$HOME/selfsigned.crt"

How to use TLS with Pymonetdb

On this blog post "link", I have explained how to install pymonetdb. In the mean time there were some changes in the newest versions of Ubuntu.

If we follow that old blog post, we'll get an error. sudo apt install python3-pip     #install pip pip install pymonetdb                   #install pymonetdb

While we can install pip, we will not be able to install pymonetdb in this way. Ubuntu is trying to prevent us to install packages into global python context. Ubuntu wants us to use virtual environment. It doesn't want us to mess with global python environment.

We can still install into global environment in this way. This will only work for packages that are inside of the ubuntu repository. sudo apt install python3-pymonetdb

I will install Spyder IDE: sudo apt install spyder I will open this GUI program and I will run this code=>

import pymonetdb connection = pymonetdb.connect("monetdbs://192.168.100.152:50000/tlsDB?cert=/home/fff/selfsigned.crt") cursor = connection.cursor() cursor.execute('SELECT 2') print(cursor.fetchone())

We can also test the code in the command line. python3 -c 'import pymonetdb;connection = pymonetdb.connect("monetdbs://192.168.100.152:50000/tlsDB?cert=/home/fff/selfsigned.crt" );cursor = connection.cursor();cursor.execute("SELECT 2");print(cursor.fetchone())'

We can also use syntax like this one: connection = pymonetdb.connect("monetdbs://192.168.100.152:50000/tlsDB", cert="/home/fff/selfsigned.crt")

How to use TLS with ODBC

We will again use our Green server. We created that server in this blog post "link", and we installed ODBC drivers in it, in this other post "link".

In the mentioned post "0500 Proto_loaders, ODBC and COPY in MonetDB", we created the file "/etc/odbc.ini" with the credentials of the Blue server. We will now modify that file, so that it leads toward the Purple server, because we want to test TLS connection. We'll change credentials, but we will add two more properties. We'll add TLS and CERT property.

FOR THE BLUE SERVER: [DatabaseB] Description = Connect to Blue Server Driver = MonetDB Server = 192.168.100.146 Port = 50000 Database = DatabaseB User = monetdb Password = monetdb

FOR THE PURPLE SERVER: [tlsDB] Description = Connect to Blue Server Driver = MonetDB Server = 192.168.100.152 Port = 50000 Database = tlsDB User = monetdb Password = monetdb TLS = ON CERT = /home/sima/selfsigned.crt

Now it is easy to connect to stunnel through ODBC driver manager. isql -v tlsdB

How to use TLS with JDBC

In this blog post "link", we downloaded JDBC driver/client to the green server. We will use that client again, but this time with the connection string for TLS.

java -jar /home/sima/Desktop/jdbcclient.jre8.jar -u monetdb -d "jdbc:monetdbs://192.168.100.152:50000/tlsDB?cert=/home/sima/selfsigned.crt"

We will also connect through DBeaver and JDBC. Before my explanation, it would be wise to read how to use DBeaver and JDBC without TLS. It is explained on this blog "link". I will not repeat here the whole story, just the difference. The difference is in the connection, we have to provide certificate.

We can create new connection (1), or we can edit the existing one (2).

We will fill the dialog with credentials for the Purple server (3). After that, we will go to the "Driver properties" tab (4). There we have to add our certificate. Now we can connect.

Including Certificate Inside of the URL

We can run this command on the purple and the green server, and we would get the same result:

openssl x509 -in /home/fff/selfsigned.crt -outform DER | sha256sum
openssl x509 -in /home/sima/selfsigned.crt -outform DER | sha256sum

This command will take our certificate, it would transform it into DER format, and then it would calculate SHA256 hash of that format in HEX digits.
b89c338234850f8def5d4612e6c868cc5f85fe22e6d6a6b5acf8a7d17a15d764 *stdin

For this demonstration, we just need the first 16 digits. So we would complete command like this:
openssl x509 -in /home/sima/selfsigned.crt -outform DER | openssl dgst -sha256 -r | cut -c1-16
b89c338234850f8d

We can now use those first 16 digits inside of our URL: mclient -d "monetdbs://192.168.100.152:50000/tlsDB?certhash=sha256:b89c338234850f8d"
This way we can also connect to MonetDB.

Sample Table

CREATE TABLE tabProducts (     Color VARCHAR(10),     Size  VARCHAR(5),     Qty   INT );

INSERT INTO tabProducts (Color, Size, Qty) VALUES    ('Red',  'M',  10),    ('Red',  'XXL', 10),    ('Blue', 'XL', 30);

RETURNING Clause

Only SELECT statement returns some values. INSERT, UPDATE, DELETE just silently do their job, without any feedback.

If we execute statement: UPDATE t1 SET Col1 = 'zzz' WHERE Id = 99;

We can afterward check rows that are updated: SELECT * FROM t1 WHERE Id = 99;

If we could do both things with one statement that would simplify things and reduce the strain on a database.

INSERT INTO tabProducts (Color, Size, Qty) VALUES ( 'Blue', 'S', 40 ),        ( 'Blue', 'S', 50 ) RETURNING Color, Size, Qty;

This INSERT statement will return values.  RETURNING clause will show us all the values that we have entered.

We can do the same thing with DELETE and UPDATE. Delete will return deleted rows, and UPDATE will return updated rows.

DELETE FROM tabProducts WHERE Qty = 50 RETURNING Size, Qty;

UPDATE tabProducts SET Size = 'M' WHERE Qty = 40 RETURNING Size, Color, Qty;

We can use expressions in the RETURNING clause.

UPDATE tabProducts SET Qty = 60 WHERE Qty = 40 RETURNING COUNT( * ), SUM( Qty );

DELETE FROM tabProducts WHERE Qty = 60 RETURNING Qty + 15;

INSERT INTO tabProducts VALUES( 'Blue', 'M', 100 ) RETURNING Color, SUM( Qty ) GROUP BY Color;

This will not work. We can not use GROUP BY in the RETURNING clause. RETURNING clause must remain simple.

Referencing Columns by Their Position

SELECT Color, Size, qty FROM tabProducts ORDER BY 1, 2; --BY Color, qty

SELECT Color, SUM( qty ) FROM tabProducts GROUP BY 1;  —BY Color

In MonetDB it is possible to reference columns by their position in the select clause.

Referencing Set of Columns with the Keyword ALL

SELECT Color, qty, COUNT( Size ) FROM tabProducts GROUP BY ALL;    –BY Color, qty

When using GROUP BY, we can only group by columns which are not aggregated. If we want to group by all of such columns, we can use "GROUP BY ALL".

SELECT Color, Size, qty FROM tabProducts ORDER BY ALL;  –BY Color, Size, qty

We can use ALL in the ORDER BY clause. This is the same as "ORDER BY Color, Size, qty".

MonetDB will accept this syntax, but there is a bug and this syntax does nothing. There will be no sorting.

Instead of the keyword ALL, we can use the star "*" sign.

SELECT Color, qty, COUNT( Size ) FROM tabProducts GROUP BY *;    –BY Color, qty

SELECT Color, Size, qty FROM tabProducts ORDER BY *;  –BY Color, Size, qty

IS [NOT] DISTINCT FROM

Anything compared with NULL will return NULL.

SELECT 'null' = null; SELECT null = null;

IS [NOT] DISTINCT FROM is a null-safe comparison operator. This operator will always return TRUE or FALSE, even if one of operands is NULL.

SELECT NULL IS DISTINCT FROM NULL;		SELECT 'A' IS DISTINCT FROM NULL;
SELECT NULL IS NOT DISTINCT FROM NULL;		SELECT 'A' IS NOT DISTINCT FROM NULL;

CHECK Constraint

A check constraint is a type of rule which specifies a condition ( boolean expression )  that must be met by each row in a database table. This rule limits acceptable values for data.

In our table I will add check constraint that quantity column must have values less then 1. This will fail because the existing data is already bigger than 1. ALTER TABLE tabProducts ADD CONSTRAINT "QtyLess1" CHECK (qty < 1);

If we change our condition, so that qty must be less than 100, then it will succeed. ALTER TABLE tabProducts ADD CONSTRAINT "QtyLess100" CHECK (qty < 100); After that, we will try to insert number 111 into qty column =>

INSERT INTO tabProducts( Color, Size, Qty ) VALUES ( 'Blue', 'XL', 111 );

This will fail because of the constraint (111>100).

UPDATE tabProducts SET qty = 111 WHERE qty = 30;

This also mean that we can not update the value in the qty column to a value that is bigger than 100.

We can only insert values that are smaller then 100. UPDATE tabProducts SET qty = 99 WHERE qty = 30;

SELECT * FROM tabProducts;

How to Add Check Constraint?

We can add several constraints on the same column. We now have two constraints, that "qty > 0" and "qty < 100". ALTER TABLE tabProducts ADD CONSTRAINT "QtyGrt0" CHECK (qty > 0);

That is not efficient. It is much better to add both constraints with one statement. We can connect conditions with AND, OR. ALTER TABLE tabProducts ADD CONSTRAINT QtyConstraints CHECK (qty > 0 AND qty < 100);

Constraints can combine several columns in the requirement expression: ALTER TABLE tabProducts ADD CONSTRAINT CheckColorSize CHECK ( Color = 'Red' OR Size = 'XL' );

We can add constraint during the creation of a table. CREATE TABLE tabOrders ( Price INT, Qty INT, CONSTRAINT ValidOrders CHECK ( Price > 10 AND Qty < 10 )  );

During table creation, we can add a constraint that is at the single column level. The server will provide a default name for such a constraint.

CREATE TABLE tabOneColumn ( Col1 CHAR, Col2 INT CHECK ( Col2 < 999 )  ); Default name consists of the table name, the column name, and the keyword "check". In this example, that would be "tabonecolumn_col2_check".

Instead of using the default name for a single-column CHECK constraint, it is much better to give the constraint a specific name.

CREATE TABLE tabOneColumnNamed ( Col1 INT CONSTRAINT Col1Constraint CHECK ( Col1 < 999 )  );

Limitations on CHECK Constraints

CHECK constraint can only refer to one row of a table. We can not use aggregation functions, because that would break such limitation. ALTER TABLE tabProducts ADD CONSTRAINT OverFlow CHECK ( SUM( Qty ) < 1000 ); This also means that a CHECK constraint cannot refer to tables other than the table on which it is defined.

A simple check on a single column has minimal impact on performance. We should avoid complex check conditions.

CHECK Constraints and Nulls

INSERT INTO tabOneColumn ( Col1, Col2 ) VALUES ( null, null );

This INSERT statement will always succeed. CHECK constraints can not check null values. SELECT * FROM tabOneColumn;

This CHECK constraint will always return null, because nothing can be compared with a null. ALTER TABLE tabOneColumn ADD CONSTRAINT ZZZ CHECK ( Col1 <> null ); CHECK will prevent entry of a row, only if condition is returning FALSE, so this constraint is useless. INSERT INTO tabOneColumn ( Col1, Col2 ) VALUES ( null, 33 );

SELECT * FROM tabOneColumn;

How to Delete CHECK Constraints?

We can find our CHECK constraint in the view "Information_schema.check_constraint". SELECT * FROM information_schema.check_constraints WHERE table_name = 'tabonecolumn';

We can delete constraints like this: – We can delete just the CHECK constraint. – We can delete the whole table.

ALTER TABLE tabonecolumn DROP CONSTRAINT zzz; DROP TABLE tabOneColumnNamed;

Now, our constraints are gone.

SELECT * FROM information_schema.check_constraints WHERE table_name = 'tabOneColumnNamed' or constraint_name = 'zzz';

LIKE and ILIKE Operators

LIKE is a pattern matching operator. It can help us to find patterns in a text. LIKE is based on two wildcard characters. Percent sign "%" will replace any set of consecutive characters. Underscore "_" will replace exactly one character. Here are some examples:

SELECT 'zzz' LIKE '%';          –true SELECT 'zzz' LIKE '___';   –true

SELECT 'Azz' LIKE 'A%';    –true SELECT 'AzzA' LIKE '%zz%';    –true

SELECT 'AzzA' LIKE 'A__A';   –true SELECT 'Azz' LIKE 'Azz_';   –false

ILIKE is case insensitive version.

SELECT 'fff' LIKE 'F_F';   –false

SELECT 'fff' ILIKE 'F_F';   –true

We can negate LIKE with NOT.

SELECT 'M' NOT LIKE 'M';   –false

SELECT 'M' LIKE 'M';   –true

Default escape character is backslash "\".	SELECT '%_' LIKE '\%\_';  –true
We can declare any other character to be ESCAPE sign.	SELECT '%_' LIKE '#%#_' ESCAPE '#';  –-true

Function regexp_replace

For more complex patterns, we can use the regexp_replace function. This function accepts the original string, a search pattern, and a string that will replace the found pattern.

We will replace "m", followed by two letters, with the "z". SELECT regexp_replace( 'maaam', 'm.{2}', 'z' );

This function is case sensitive, except if we use the forth, optional argument.

SELECT regexp_replace( 'maaam', 'M.{2}', 'z' ); — false

SELECT regexp_replace( 'maaam', 'M.{2}', 'z', 'i' ); –true

This function also accepts other modifiers (flags). I tested that it will accept "m,i,s,x,xx". Flag "xx" is the same as "x".

It is interesting that it will not accept flag "g". It seems that this modifier is constanly turned on.

SELECT regexp_replace( 'SSS', 'S', 'P' );

SELECT regexp_replace('first\nfirst', '^first', '*', 'm') AS result; SELECT regexp_replace('a\nb', 'a.b', 'X', 's') AS result; SELECT regexp_replace('abc123', ' 1 2 3 ', '', 'x') AS result;

Here are examples that you can try with and without modifier (flag).

SELECT regexp_replace('Prisca Gbaguidi', '\\w+\\s\\w+', 'Mireille Gbaguidi'); –Mireille Gbaguidi	Backslashes have to be escaped.
SELECT regexp_replace('Prisca Gbaguidi', '(\\w+)(\\s)(\\w+)', '\\3 \\1'); —Gbaguidi Prisca	Regex with numbered capturing groups.

TRUNCATE and SERIAL Data Type

We will examine how TRUNCATE can affect the SERIAL data type. CREATE TABLE Students ( ID SERIAL, Name VARCHAR(10) ); INSERT INTO students(name) VALUES ('Wiremu'), ('Hinewai');

SELECT * FROM Students;

We will truncate this table, but we will continue sequence. TRUNCATE students CONTINUE IDENTITY; INSERT INTO students(name) VALUES ('Wiremu'), ('Hinewai');

SELECT * FROM Students;

Default behavior is to restart sequence. TRUNCATE students RESTART IDENTITY; INSERT INTO students(name) VALUES ('Wiremu'), ('Hinewai');

SELECT * FROM Students;

We will continue using the green and blue server that we have created in this post => link ( or you can watch the video on the youtube  => link ). This was the post about distributed query processing.

monetdbd start /home/sima/monetdb/DBfarmG mclient -u monetdb -d DatabaseG   –password monetdb monetdbd start /home/sima/monetdb/DBfarmB mclient -u monetdb -d DatabaseB   –password monetdb

JDBC

We will now connect to the blue MonetDB server, from the green server, through JDBC connector.

Installing Java

Let's see if we have Java installed. java -version
We don't have it, so we can install it like this: sudo apt install default-jre

Connecting With the JDBC Client

From this link: https://www.monetdb.org/downloads/Java/

Download this file "jdbcclient.jre8.jar".

This is JAR file that includes java console client application, but also the driver. It is all in one.

Inside of the green server, run this command from the shell: java -jar /home/sima/Desktop/jdbcclient.jre8.jar -h 192.168.100.146 -p 50000 -u monetdb -d DatabaseB

We are now connected. We can use this java client at the same way as we use "mclient" program. SELECT * FROM SchemaGB.factB;

Using JDBC Connection with DBeaver

From this link: https://www.monetdb.org/downloads/Java/

Download JDBC driver "monetdb-jdbc-12.0.jre8.jar".

We don't have DBeaver on the green server, so we have to install it. We will download DBeaver ".deb" file with wget command. Then we can install it.

cd /home/sima/Desktop wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb sudo apt install ./dbeaver-ce_latest_amd64.deb

DBeaver is a GUI program. Click on Database > Driver Manager menu.

Click "New".

In the new dialog enter these values in "Settings" tab: Driver Name:        MonetDB_JDBC Class Name:        org.monetdb.jdbc.MonetDriver URL Template:     jdbc:monetdb://{host}:{port}/{database}

This is how "Settings" tab should look:

In the "Libraries" tab click on "Add File". Find "monetdb-jdbc-12.0.jre8.jar" driver file. This will add our driver file into DBeaver.

Close everything and in the main window click on the icon for the New Connection.

Find our driver with a search. Click Next and in the new dialog enter these values:

Host:                192.168.100.146 Port:                50000 Database/Schema:     DatabaseB Username:            monetdb Password:            monetdb

This is how dialog will look like. When you click on the "Test" button you will get an error: Invalid JDBC URL:  jdbc:monetdb://192.168.100.146:50000/DatabaseB

This error is because DBeaver is trying to validate "jdbc:monetdb://192.168.100.146:50000/DatabaseB" URL, or it is trying to add some hidden parameters or escape characters.

Solution is to change dialog to manual input.

DBeaver will now use the raw URL. After we click on the "Test" button, we will get confirmation dialog.

We can now expand our connection and there we will find all of the schemas and tables, and now we can run queries.

Recursive Common Table Expressions

In the blog post about common table expressions ( link ), I unintentionally lied that MonetDB doesn't support Recursive CTEs. But it does, and I will explain them now.

What is Recursion

When we need to take a shower, it is always difficult to set the right water temperature. To find it, we repeat the same action. We move the lever left and right until we find the perfect spot. With each movement of the lever, we get closer to the desired temperature.

Recursion is an iterative process of finding a solution. We repeat the same logic each time, but each time we are closer to the solution because we can base our logic on the information we have gained during previous iterations.

Let's look at this example. Our friend imagined a number between 1 and 10. We have to guess that number with the minimal number of questions. The best approach is to use binary logic, based on an elimination process where in each iteration we can remove half of the numbers.

Question: Is it bigger than 5?   Answer: Yes, it is.

Question: Is it bigger than 8? Answer: No, it is not.

Question: Is it smaller than 7? Answer: Yes, it is.

It has to be 6.

The only argument in our alghorithm is the range of the possible numbers. Our logic has two steps: 1) Does the range of possible numbers has only one number. 2) If it doesn't, ask the question to eliminate half of the numbers and reduce the range of possible numbers by half.

We can pack the second step into function with a name "EliminateHalf". This function will return the range of all the possible numbers. We will call this function 3 times.

1) EliminateHalf (1,2,3,4,5,6,7,8,9,10) = (6,7,8,9,10) 2) EliminateHalf (6,7,8,9,10) = (6,7) 3) EliminateHalf (6,7) = (6)

We can nest these functions: EliminateHalf(EliminateHalf(    EliminateHalf(         1,2,3,4,5,6,7,8,9,10))) = 6

Problem is that we don't know how many nested functions do we need. I will create a pseudo code that will nest as many functions as needed to get the final result.

Result = EliminateHalf( 1,2,3,4,5,6,7,8,9,10 )  # initial state Do Until Count( Result ) = 1                    # are we finished        Result = EliminateHalf( Result )            # if not, continue  Loop

This is RECURSION. We broke the complex problem into small steps. Each step has the same logic. Each step is using arguments that are the result of the previous step. This is just one iterative process which brings us closer to the solution with each step.

You want example from the real life. The coach of the football team analyze data from the previous game. After each analysis he change the game of his team. He continues with this practice until his team start winning.

The Structure of Recursion

Recursion structure always has four steps. The first step "initial state" is a problem that we want to solve. We will solve it by improving our statistics.

1) Set initial values for our arguments.                                                     # initial state                           2) Has our goal been achieved?                                                                 # are we there yet            # recursive part 3) Improve our arguments by using some strategy.                             # continue with effort     # recursive part 4) Repeat steps 2 and 3 until we reach the goal.                                   # be persistent

Linear and Tree Recursion

In linear recursion we only have two possible outcomes. We are either satisfied with the result or we will continue with our effort. For example, the coach can be satisfied with his team or he can continue introducing improvements.

Tree Recursion is when we have several possible strategies to direct our effort. For example, the coach can change the team's game, or he can look for position in some other team. If we create a diagram of his possible actions we can get something like this:

1) Red path: Be satisfied with a Real, and then transfer to Arsenal, and start improving Arsenal game. 2) Orange path: Be satisfied with a Real, and then transfer to Arsenal, and then to Barcelona, and start improving Barcelona's game. 3) Purple path: Leave Real, improve Liverpool for a while, and then transfer to Juventus to improve Juventus game.

We can present this coach career as folders and subfolders. Each time he improves his team, he is going deeper in the subfolders ( Real → Real+ ). Each time he changes his job, he is moving to another folder at the same level ( Real+ → Juventus ). This is why this kind of recursion is called Tree Recursion, because we have a hierarchy. This hierarchy is reflection of strategic decisions we have to make to reach our goal. Each decision will move us to another path, but we always use the same logic to decide.

Structure of The Recursive Common Table Expression

WITH RECURSIVE cte_name AS (     SELECT ...           –initial state     UNION ALL         SELECT ...           –continue with improvements     FROM cte_name        –get the previous state     WHERE ...            –are we there yet ) SELECT * FROM cte_name;  –return result

In the simplest form, recursive CTE has two SELECT statements connected with UNION ALL. First select statement will define initial state. RECURSIVE CTE will return all interim results connected with UNION ALL. Second select statement will calculate the new status. It will reference the previous status by the name of the CTE. WHERE in second select statement will tell us when to stop.

WITH RECURSIVE numbers(n) AS (    SELECT 1           UNION ALL          SELECT n + 1     FROM numbers     WHERE n < 4 ) SELECT * FROM numbers;

The first select statement is called ANCHOR member. The second one is RECURSIVE member. ANCHOR member will be executed only once. In this example, RECURSIVE member will be executed several times, each time with the different value of "n", until the condition becomes false.

We will apply UNION ALL on all of the interim results.

WITH RECURSIVE factorial(n, fact) AS (     SELECT 1, 1           UNION ALL          SELECT n + 1, fact * (n + 1)     FROM factorial     WHERE n < 5 ) SELECT n, fact FROM factorial;

This is an example how to calculate factorial.

These two examples are for the linear recursion.

Tree Recursion

This is our hierarchy.

Our hierarchy can be presented with the self referencing table. We'll create such table in MonetDB.

CREATE TABLE Folders ( Folder TEXT, Parent TEXT ); INSERT INTO Folders ( Folder, Parent ) VALUES ('Folder1', NULL), ('Folder2', NULL), ('Folder11', 'Folder1'), ('Folder12', 'Folder1'), ('Folder21', 'Folder2'), ('Folder211', 'Folder21');

We'll make our CTE: WITH RECURSIVE Folder_paths AS (     SELECT Folder, Folder AS Path     FROM Folders     WHERE Parent IS NULL     UNION ALL     SELECT f.Folder, fp.Path || '>' || f.Folder AS Path     FROM Folders f  JOIN Folder_paths fp ON f.Parent = fp.Folder ) SELECT * FROM Folder_paths ORDER BY path;

This is initial state. This table represents folders at the top level. We'll join original Folders table with this initial state. That will give us our new state.

We'll do it again, between Folders table and new state. This is inner join.

We can now make a final result as a union between (1),(2),(3).

Tree recursion occurs then ANCHOR and RECURSIVE members are select statements that return tables with several rows. Those rows represent folders at the same level. We have (1), (2), (3) for top (1), middle (2) and bottom (3) folders. First, we get top folders (initial state), then middle folders (first recursion), and then bottom folders (second recursion). Each recursion is used to collect folders from the level bellow.

Recursive CTEs Caveats

1) Recursive and anchor member must match in columns number and data types.

2) If we use UNION ( instead UNION ALL ), recursion will stop immediately when we face a duplicate. This statement  will immediately end, although it doesn't have WHERE statement. This is because the first recursion will return number 1, which is a duplicate. UNION operator will remove that duplicate, so our result will have only one row, and that row will have the original number 1.

WITH RECURSIVE numbers AS (     SELECT 1     UNION     SELECT 1 FROM numbers ) SELECT * FROM numbers;

3) Don't user OUTER JOINS in the recursive member. The query will never end. Only INNER join is acceptable.

4) MonetDB will not complain if we use aggregate or window functions in recursive member. We can also use DISTINCT and GROUP BY in the recursive member. In MonetDB, we can use CTE's name in the FROM clause, but we can also use it in subquery. Some other servers don't allow this.

New Functions

DayName and MonthName Functions

SELECT DAYNAME('2025-07-12');	Saturday	This function returns a name of a day in a week according to the current locale, set in the OS.
SELECT MONTHNAME('2025-07-12');	July	This is similar function that is returning the name of a month.

Beside date arguments, we can also use timestamp ('1987-09-23 11:40') or timestamp TZ ('15:35:02.002345+01:00').

Generate Series Functions

SELECT * FROM generate_series(1,9,2);

1,3,5,7

This function will return numbers from 1 do 9, with step 2. Default step is 1.

SELECT * FROM generate_series('2025-01-01','2025-01-10',INTERVAL '5' DAY);	2025-01-01,2025-01-06	All dates from the range, but with the step of 5 days.
SELECT * FROM generate_series('2025-01-01','2025-05-10',INTERVAL '2' MONTH);	2025-01-01, 2025-03-01	We can also get the months with the step od 2 months.

We can list seconds or days between two timestamps.
SELECT * FROM generate_series('2025-01-01 01:40:00','2025-01-01 1:40:05', INTERVAL '3' SECOND);	2025-01-01 01:40:00 2025-01-01 01:40:03
SELECT * FROM generate_series('2025-01-01 01:40:00','2025-01-06 1:40:05', INTERVAL '3' DAY);	2025-01-01 01:40:00 2025-01-04 01:40:00

Generate Series functions will return a column with the name "value". SELECT * FROM generate_series( 1,7,2); SELECT * FROM generate_series('2025-01-01','2025-01-10',INTERVAL '5' DAY);

---