Working with the database

The pipeline uses a PostgreSQL database. This database is currently running on coma00.science.ru.nl which also doubles as the file and web server. The database can be accessed through two different interfaces:

• object oriented though the crdatabase module and,
• directly with SQL.

The direct SQL interface is faster for most updates and queries and should be used when data from multiple events is needed. In contrast the object oriented interface is easier to work with for single events and is used in all processing pipelines.

The current entries of the database are always visible on the website.

Object oriented interface

The following Python script will open a connection to the database and read an event object:

#! /usr/bin/env python

from pycrtools import crdatabase as crdb

dbManager = crdb.CRDatabase("crdb", host="coma00.science.ru.nl",
    user="crdb", password="crdb", dbname="crdb")
db = dbManager.db

event = crdb.Event(db=db, id=83687299)

print event

print event["clock_frequency"]

for station in event.stations:
    print station.stationname

Note that assigning something will write the changes to the database!!! There will be no warning or checking step.

Direct SQL interface

To access the database directly and run SQL queries you may use:

• the command line psql tool or,
• the psycopg2 module in a Python script.

From the command line with psql

Simply type:

$ psql -h coma00.science.ru.nl -U crdb -d crdb -W

where $ is the prompt (and can be skipped). Now type the password which is also crdb and you are in. Note that this only works from coma.science.ru.nl where you need to login first.

You will get a crdb=> prompt back where you may now type your SQL query. For instance:

crdb=> SELECT * FROM events;

From a Python script

The following script will print out all events in the database:

#! /usr/bin/env python

import psycopg2

conn = psycopg2.connect(host="coma00.science.ru.nl", user="crdb",
    password="crdb", dbname="crdb")
c = conn.cursor()

sql = "SELECT * FROM events"
c.execute(sql)

print c.fetchall()

c.close()
conn.close()

Working with pickled parameters

Note that (almost) all parameters are stored in the database as pickled Python objects in base64 encoding in text strings. Very few entries are stored unpickled to allow for direct querying, such as the statuses or flagging parameters.

The functions pickle_parameter and unpickle_parameter from the crdatabase module take care of the necessary conversions. You should always use these functions to work with parameters when using the direct SQL interface!

For example:

#! /usr/bin/env python

import psycopg2

from pycrtools.crdatabase import unpickle_parameter

conn = psycopg2.connect(host="coma00.science.ru.nl", user="crdb",
    password="crdb", dbname="crdb")
c = conn.cursor()

sql = "SELECT clock_frequency FROM eventparameters
WHERE (eventid='83687299');"

c.execute(sql)

clock_frequency = unpickle_parameter(c.fetchone()[0])

print clock_frequency

c.close()
conn.close()

Changing the status of events

What you will most often need is the changing of the status of events to new. Here the syntax form the direct sql interface to change the status of event 12345:

crdb=> UPDATE events SET status='NEW' WHERE (eventID='12345');

If you are running this for many events, please make sure that you know what you are doing. Changes are not revertible and may delete entries that have been manually inserted and cannot be recovered.