Installation of Pymonetdb Module

Python comes preinstalled on most of the linux distributions. We can check version of our python with a command:

python3 --version           

Now that we know that python is installed, we can install python module which we will use to connect to MonetDB from python. First, we will update the list of available software packages and we will check whether pymonetdb module is available:

sudo apt update
apt search pymonetdb

We will notice that we have two versions of pymonetdb module. Former is for python2 and latter is for python3.

Because Ubuntu’s repository has appropriate pymonetdb module, we can install it. For installation we need pip. Pip is a console program used for installing python modules. So, first we need to install pip:

sudo apt install python3-pip                      

After installing pip, we will use it to install pymonetdb module, pip will know which module to install (python3):

pip install pymonetdb  

Pymonetdb module is installed.

Installing of Spyder IDE on Ubuntu

Now we can try to connect to MonetDB from python. For that, I will type python commands into Spyder IDE. We have to first install Spyder IDE on Ubuntu.

sudo apt install spyder                  

We can then start Spyder from the graphical interface (1). This is how spyder looks like (2):

Spyder is a free and open source scientific environment for Python.

Python Script to Connect to MonetDB

Inside of Spyder IDE, I will add this script. This script will first create connection object. Using that connection object, we will create cursor object. Then we can use cursor object to execute our query.

import pymonetdb
connection = pymonetdb.connect(username="voc", password="voc", hostname="localhost", database="voc")
cursor = connection.cursor()
cursor.execute('SELECT * FROM voc.total')
[print( row ) for row in cursor.fetchall() ]

Result of our query will be list of tuples (like [(a,b,c),(1,2,3)] ), where each tuple is one row of a table. We will use list comprehension to print those rows one by one. At the end, Spyder console (1) will show us result.

Pymonetdb Help

If you want to learn more about pymonetdb, you can go to official documentation on this address:

https://pymonetdb.readthedocs.io/en/latest/index.html

Download of Schema

First, we will download sample database from this location:
https://dev.monetdb.org/Assets/VOC/voc_dump.zip

This will give us ZIP file. Inside of it, there is SQL script with the schema for our new database.

What is a Schema?

Database is made of tables, views, indices and so on. Inside of Database, each of these objects belong to some schema. Database is organizationally divided into schemas. There is no overlap between schemas. There could be some special objects that are outside of a scheme, like roles.

Creation, usage and modification of schema elements is strictly done by the user or the role that owns that scheme. During creation of a scheme, we should decide who will be owner, because later it will not be possible to change ownership. If we want several people to own one schema then we should set a role as an owner of a scheme.

Only ‘monetdb’ user and ‘sysadmin’ role are allowed to create new schemes.

Creation of a New User

For our schema we will create a new user. First, we will enter mclient with ‘monetdb’ privileges.

> mclient -u monetdb -d voc           

For creation of a user, we need username (USER), password (WITH PASSWORD), user’s full name (NAME), and default schema for that user (SCHEMA). If a user wanst to use table from some schema he can use schema as a prefix “myschema.mytable”.If a user doesn’t provide schema as a prefix, then the default schema will be used.

sql> CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";

As a ‘monetdb‘ user we can create new schema. We will say that previously created “voc” user is the owner of that schema.

sql> CREATE SCHEMA "voc" AUTHORIZATION "voc";   

We will set that new schema as the default schema for our user.

sql> ALTER USER "voc" SET SCHEMA "voc";  

sql> \q   -- we can exit mclient with "quit" or "\q"

Populating our Schema with Database Objects

Our schema is currently empty, but we have definitions of all the tables, view, indices … inside of our downloaded SQL script. We will use that script to populate our schema. We type:

> mclient -u voc -d voc ~/Desktop/voc_dump.sql   

We can use mclient command “\d” to list all the tables and views inside of our database. This is what we would get:

sql> \d TABLE voc.craftsmen TABLE voc.impotenten TABLE voc.invoices TABLE voc.passengers TABLE voc.seafarers TABLE voc.soldiers TABLE voc.total TABLE voc.voyages

DBeaver Database Manager Program

We will install DBeaver database manager program in order to peruse our database.

> sudo snap install dbeaver-ce      

This program is GUI program, so we will open it in our desktop environment. In the program we will click on the arrow (1), and then we will select Other (2) to open other servers. There we will find MonetDB. We will click on it (3), and a new dialog will open. In this dialog, host and port are already entered. We just need to enter Database/Schema (4), Username and Password (which is “voc”) (5).

DBeaver will not have driver for MonetDB database installed, so it will offer you to download it. Just accept that offer.

At the end, objects of our database will appear inside of pane on the left side of a program. There, we should double click on schema name (1). After that we can select tab “ER Diagram” (2). There, after some rearangement, we will see ER diagram of our database (3). As we can see, tables are organised in star schema with “voyages” table as the only fact table. All tables are connected with foreign key constraints, where foreign key is also primary key inside of dimension tables. The only exception is Inovice table where foreign key columns are not primary key columns, and that is why that relationship is shown with dashed line (4).

DBeaver is an excellent program. If you want to learn more about it you can try this linkedin tutorial.
https://www.linkedin.com/learning/dbeaver-essential-training?trk=learning-topics_trending-courses_related-content-card&upsellOrderOrigin=default_guest_learning

General architecture

MonetDB database is presented with MonetDB daemon (service). This daemon can control several servers. Each server is defined inside of one directory that we create. Common name for such directories is “dbfarm”. Beside configuration files for that server, all of the databases of that server will also be placed inside of subfolders of dbfarm directory.

Server creation

When we want to create new server, first thing is to create folder for that server.

-> monetdbd create /home/fffovde/DBfarm1

Inside of that folder a new file with the name “.meroviginian_properties” will be created. This file will have properties for our database.

The Merovingian dynasty was the ruling family of the Franks from the mid-5th century until 751. This dynasty ruled the Netherlands, the country from which MonetDB originates. MonetDB is using this term for some of its internal files and commands.

If we take a look inside of this file, we will find only one property:

-> cat .merovingian_properties
# DO NOT EDIT THIS FILE - use monetdb(1) and monetdbd(1) to set properties
# This file is used by monetdbd
control=false

All other properties are using default values. We can read those default values by command:

> monetdbd get all /home/fffovde/DBfarm1

property hostname dbfarm status mserver logfile pidfile loglevel sockdir listenaddr port exittimeout forward discovery discoveryttl control passphrase snapshotdir snapshotcompression mapisock controlsock

value FffOvdeKomp /home/fffovde/DBfarm1 no monetdbd is serving this dbfarm unknown (monetdbd not running) /home/fffovde/DBfarml/merovingian.log /home/fffovde/DBfarml/merovingian.pid information /tmp localhost /50000 60 proxy true 600 no <unset> <unset> .tar.lz4 /tmp/.s.monetdb.50000 /tmp/.s.merovingian.50000

***********************************************

Monetdb daemon will use default port 50000. It is possible to have several Monetdb daemons. Their port numbers could collide. If we have several Monetdb daemons, then we should immediately change default port number to some unused port number. 

We can set another port number by running this command:

> monetdbd set port=12345 /home/fffovde/DBfarm1

***********************************************

If our monetdbd service is already running, we should stop it. We are doing this in order to release port 50000.

> systemctl stop monetdbd
> systemctl disable monetdbd

Now we can start our server. Our new server will use default port 50000.

> monetdbd start /home/fffovde/DBfarm1

If we now look inside of our DBfarm1 directory, we will now see all of this files.

File “.merovingian_lock” is empty. This file probably just signalized that there is a server inside of directory dbfarm.

File “merovingian.pid” has the number 2436. This is the number of monetdbd process. If we use command “sudo netstat -tulnp” to show us all listening ports, we will see the name monetdbd beside process 2436, and this process will listen the port 50000.

We can also read content of log file. There we will see how our action succeeded.

After this step we no longer have to use command monetdbd, we can just use monetdb (without d).

Database creation

We will create new database in this way. This database is created in “maintenance mode” because no one can access it before we can properly configure it. This command will create a new folder with the name “voc” inside of our DBfarm1 directory.

monetdb create voc

Now, we can start our database, so that only members of monetdb group can access it.

monetdb start voc                  

We can check status of our database with this command (50000 is port number):

monetdb -p50000 status   

Last step would be to make this database available to all of users:

monetdb release voc   

Making queries

“mclient” is application used by users to send queries to databases. We have to provide name of a user with “-u” switch, and name of a database with “-d” switch. Everyone that are inside of “monetdb” group can use “monetdb” username. We will be asked for password, and default password is “monetdb“. At the bottom we can notice “sql>” prompt. This is where we can type our queries.

mclient -u monetdb -d voc   

**************************************************

If we have used “set port” command to set some other port for our server, then we have to supply that alternative port number to mclient:

mclient -p50007 -u monetdb -d voc

**************************************************

Now we can type our first query. Don’t forget the semicolon. We can exit “sql>” prompt with the command “quit“.

SELECT 'columnValue' as columnName;    

How to Stop or Lock Our Server?

We can stop our server with stop command:

Monetdbd stop  ~/DBfarm1     

After we do this, we can check our “merovingian.log” file. Inside of it, we will see all of the databases of that server to be shut down.

cat merovingian.log     

If we just want to make our database unavailable then we use “lock” command. This would put our database under maintenance mod.

monetdb lock voc         

We already know that we can exit maintenance mode with “monetdb release voc“.

Getting the Codename of our Ubuntu Version

First, we need to know the code name of our Ubuntu version. We can find that by reading from the file “os-release”. From this file we can read only the line that has words “VERSION_CODENAME” inside of it.

> cat /etc/os-release | grep VERSION_CODENAME

Our Ubuntu codename is “focal”. It is also possible to use command:

> lsb_release -cs

We can see from the command line above that our user account is “user“. “computer” is the name of our system.

Adding a Repository Where MonetDB is Stored

Next, in folder “/etc/apt/sources.list.d” we will create a file with the name “monetdb.list”.

> cd /etc/apt/sources.list.d    #jump to that folder
> sudo touch monetdb.list       #create new file, you will be asked to provide password

Inside of this file we have to place this text. These are addresses to MonetdDB repository.
deb https://dev.monetdb.org/downloads/deb/ focal monetdb
deb-src https://dev.monetdb.org/downloads/deb/ focal monetdb
We can add this text by inserting these two lines in our terminal:

sudo sh -c 'echo "deb https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list'
sudo sh -c 'echo "deb-src https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list'

Now our file looks like this:

Installing GPG key

Then, we would execute this command. This command will read GPG key file from the internet, and it will place that file in location /etc/apt/trusted.gpg.d/monetdb.gpg. GPG key is a file which will be used to verify MonetDB packages before installing them.

sudo wget --output-document=/etc/apt/trusted.gpg.d/monetdb.gpg https://dev.monetdb.org/downloads/MonetDB-GPG-KEY.gpg

This monetd.gpg file is binary file. We can read its content with the command:

sudo apt-key finger

This command will read values of all the GPG keys in our Ubuntu. One of those keys will be for MonetDB:

If result of this command is equal to “8289 A5F5 75C4 9F50 22F8 EE20 F654 63E2 DF0E 54F3” for MonetDB, then that means that we have installed the correct key.

MonetDB Installation

Now we can install MonetDB. First, we will update our list of available software with command:

sudo apt update

Then we can install MonetDB server and client:

sudo apt install monetdb5-sql monetdb-client

Next step is to enable “MonetDB” service. This service will run each time we boot our computer.

sudo systemctl enable monetdbd

But, if we want our service to run immediately, we don’t have to wait for the next boot. We can start our service with:

sudo systemctl start monetdbd

Now that we started our service, let’s check its status. We type:

systemctl status monetdbd

We can see that our process is enabled and is running.

Adding an User in MonetDB

Next step is to add users, who are allowed to run a database server, to user group monetdb. “user” is the name of the users account.

sudo adduser user monetdb

To activate this change, we have to log out. If we are using console, we just have to type “exit” and then we can log in again. If we are using GUI, then we can just click on “Log Out” button somewhere in our desktop environment.

We can now  type “mserver5” in terminal (or console), and we will get a proof that our server is installed.

We can also type “monetdb status“.