MonetDB

MonetDB is the DBMS used for Cherokee/MonetDB Handler OSM and POI tools.

This page will explain some basics on MonetDB usage. For further information visit the MonetDB website. Please note that the documentation provided there might be out of date. The monetdb-users mailing list may help, too.

A database schema can be found at MonetDB/Schema.

Basic Setup

Installation

The MonetDB website provides packages for multiple distributions, or you could build it yourself (see documentation on the MonetDB website or read the #Building_MonetDB_from_Mercurial section).

Gentoo ebuilds are provided in the osm-poi-tools overlay, see POI_tools/Installation#Gentoo.

Configuration

Edit the configuration file /etc/monetdb5.conf

Merovingian

Merovingian is the MonetDB Database Server daemon for MonetDB5 (sql).

Most information is available in the manpage:

man monetdb

The daemon is started by simply running

merovingian

You can include this command in a startup script. It is a good idea to run the daemon as a non-root user who has write access to the pid-file, logfiles and dbfarm configured in /etc/monetdb5.conf

If you use Cherokee and its information source feature, you do not need merovingian.

Adding a Database

Create a new database by running

monetdb create mydatabase

To run the database server, execute

monetdb start mydatabase

Inspect your database servers using

monetdb status

The database is still locked (maintenance mode). To allow users other than the administrator to connect, release it:

monetdb release mydatabase

The database can be locked again running

monetdb lock mydatabase

Connecting to the Database

You can use mclient to connect to the new database. Specify the language (sql) and database name:

mclient -l sql -d mydatabase

It may be useful to have automatic login. Supplying the password as parameter is not possible because of security reasons. Create the file $HOME/.monetdb with the login data:

cat << EOF > $HOME/.monetdb
user=monetdb
password=monetdb
language=sql
EOF

Change the user and password according to your configuration.

You can now set an environment variable to specify the login file:

export DOTMONETDBFILE=$HOME/.monetdb
echo 'export DOTMONETDBFILE=$HOME/.monetdb' >> $HOME/.bashrc

With this file you can just log in running

mclient -d mydatabase

Modules

Loading modules

In the past it was neccessary to load additional modules like this:

mserver5 --dbinit="include sphinx;"

Now all present modules are loaded automatically.

geom

The geom module adds GIS functionality via GEOS. You might know the fuction names from PostGIS. Documentation

Example to get a geometry from the nodes table:

SELECT PointFromText('POINT(' || long || ' ' || lat || ')') FROM nodes LIMIT 1;

Sphinx search

In order to use Sphinx search on MonetDB, you need the sphinx module. It is not packaged yet, which means you must compile it yourself. Documentation

Compile libsphinxclient from the Sphinx source (located in the subfolder api/libsphinxclient/):

./configure
make && make install
ldconfig

The version that comes with sphinx-0.9.9 will not compile because of this bug, but if you add static in line 282, it will work:

static void sock_close ( int sock );

You need to add the following option to your MonetDB5 configure command if your libsphinxclient library path is different from the usual paths:

--with-sphinxclient=$DIR

where $DIR is the path containing libsphinxclient. If you did not configure a path when compiling libsphinxclient, the directory is DIR=/usr/local, the subdirectory /lib will be added by configure.

The Sphinx module will be built when the libsphinxclient headers are present.

ODBC installation

MonetDB will be compiled with ODBC support, if the ODBC libraries exist (Gentoo: dev-db/unixODBC). You might need this patch if you experience build errors.

ODBC configuration

ODBC is used to connect Sphinx with MonetDB.

cat << EOF > /etc/unixODBC/odbcinst.ini
[MonetDB]
Description             = MonetDB driver
Driver          = /opt/monetdb-head/lib/libMonetODBC.so.1.37.0
Setup           = /opt/monetdb-head/lib/libMonetODBCs.so.1.37.0
UsageCount              = 1
EOF

cat << EOF > /etc/unixODBC/odbc.ini 
Description = germany.osm
Driver = MonetDB
Database = germany
Servername = localhost
Username = dbslayer
Password = dbslayer12345
Port = 50001
ReadOnly = Yes
EOF

Create the DBSlayer user and grant read access.

CREATE USER "dbslayer" WITH PASSWORD 'dbslayer12345' NAME 'DBSlayer' SCHEMA "sys";
GRANT SELECT ON nodes_legacy TO dbslayer;
GRANT SELECT ON node_tags TO dbslayer;
GRANT SELECT ON ways TO dbslayer;
GRANT SELECT ON way_tags TO dbslayer;
GRANT SELECT ON way_nds TO dbslayer;
GRANT SELECT ON relations TO dbslayer;
GRANT SELECT ON relation_members_node TO dbslayer;
GRANT SELECT ON relation_members_relation TO dbslayer;
GRANT SELECT ON relation_members_way TO dbslayer;
GRANT SELECT ON relation_tags TO dbslayer;

Cherokee DBSlayer

You can also access MonetDB databases with Cherokee's DBSlayer module. DBSlayer was created for MySQL, but there is a version supporting MonetDB.

FIXME: where to get that version, how to install it

Cherokee configuration

Run cherokee-admin and open it in your browser. Add a rule, for example directory /json and set the handler to DBSlayer ("MySQL Bridge"). Fill in the database details and select "Round Robin" as balancer. Create an information source with the correct port of your MonetDB server, for example 127.0.0.1:50000. At this point you could make Cherokee run the MonetDB server if it is not running.

If you select the information source type "Local interpreter", you can start the MonetDB server from Cherokee, if not present. Enter the interpreter command

mserver5 --dbname germany

with the database name you like. Run it as non-root user, for example user and group "monetdb" (user and group must exist).

The configuration (/etc/cherokee/cherokee.conf) should look similar to this one:

vserver!70!rule!200!disabled = 0
vserver!70!rule!200!encoder!gzip = allow
vserver!70!rule!200!expiration = time
vserver!70!rule!200!expiration!time = 5m
vserver!70!rule!200!handler = dbslayer
vserver!70!rule!200!handler!balancer = round_robin
vserver!70!rule!200!handler!balancer!source!1 = 12
vserver!70!rule!200!handler!db = germany
vserver!70!rule!200!handler!lang = json
vserver!70!rule!200!handler!password = dbslayer12345
vserver!70!rule!200!handler!type = monetdb
vserver!70!rule!200!handler!user = dbslayer
vserver!70!rule!200!match = directory
vserver!70!rule!200!match!directory = /json
vserver!70!rule!200!match!final = 1
vserver!70!rule!200!no_log = 0
vserver!70!rule!200!only_secure = 0

The important line is

vserver!70!rule!200!handler!type = monetdb

This line will not be created by cherokee-admin, you have to add it manually in the configuration file.

You can use the following values for handler lang: json, php, python, ruby, csv. CSV is not available in cherokee-admin yet, but it is already supported.

Building MonetDB from Mercurial

#!/bin/bash

# Debian: requires mercurial automake build-essential bison flex libtool libpcre3-dev libssl-dev


if [ ! -e monetdb-hg/ ]; then
    echo "checking out MonetDB from mercurial"
    hg clone http://dev.monetdb.org/hg/MonetDB monetdb-hg &&
    cd monetdb-hg/
else
    cd monetdb-hg/
    hg pull
    hg update
fi

OPTIONS="--prefix=/opt/monetdb --with-sphinxclient=/usr/local"
DEBUG="--enable-optimize --disable-debug --disable-assert"
export PYTHONPATH=/opt/monetdb/lib64/python2.6/site-packages:/opt/monetdb/lib/python2.6/site-packages
export PATH=/opt/monetdb/bin:$PATH

function compile() # requires one parameter: <target>
{
  cd $1 &&
  if [ -e Linux/ ]; then
      rm -r Linux;
      echo "deleting $(pwd)/Linux"
  fi;
  ./bootstrap &&
  mkdir -p Linux &&
  cd Linux &&
  ../configure $OPTIONS $DEBUG &&
  make && make install &&
  cd ../.. &&
  return 0 || return 1;
}

compile buildtools &&
compile MonetDB &&
compile testing &&
compile clients &&
compile MonetDB5 &&
compile sql &&
compile geom &&
cd python/ &&
python setup.py install $OPTIONS