.. _cr_database

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 :mod:`~pycrtools.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 :mod:`~pycrtools.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.**