002. Creation of MonetDB Database
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/DBfarm1Inside 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=falseAll 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 monetdbdNow we can start our server. Our new server will use default port 50000.
> monetdbd start /home/fffovde/DBfarm1If 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 vocNow, 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".
