Back to ippToPsps main page

The ippToPsps database

Overview

The ippToPsps database is a central component. It is currently hosted on the same machine as gpc1, but all databases used by ippToPsps are detailed in the settings file, ​here, while it's schema can be found in svn, ​here.

cascading deletes where necessary

+---------------------+
| Tables_in_ipptopsps |
+---------------------+
| batch               | 
| box                 | 
| clients             | 
| config              | 
| detection           | 
| object              | 
| pending             | 
| stack               | 
| stripe              | 
+---------------------+

The config table

The config table contains all available configurations of 'loading campaigns'. Generally this means a row for 3pi, a row for MDF, SAS etc. All ippToPsps programs require the name of a config as their first argument when run from the command line, but changes can be made to config while programs are running and they will update accordingly.

The clients table

Whenever an ippToPsps program is started, it registers with this table in the database. It does this by storing its hostname, PID and program name as shown here:

+---------------------+------------+-------+--------+--------+--------+--------+---------------------+-----+
| started             | type       | pid   | host   | config | paused | killed | timestamp           | id  |
+---------------------+------------+-------+--------+--------+--------+--------+---------------------+-----+
| 2012-03-06 22:22:09 | queue.py   | 15477 | ipp007 | new3pi |      0 |      0 | 2012-03-06 22:25:13 | 171 | 
| 2012-03-06 16:44:03 | loader.py  | 15510 | ipp033 | new3pi |      0 |      1 | 2012-03-07 07:25:51 | 166 | 
| 2012-03-05 16:54:26 | loader.py  | 25131 | ipp005 | new3pi |      0 |      0 | 2012-03-07 09:43:34 |  94 | 
| 2012-03-07 09:01:28 | pollOdm.py |  9202 | ipp006 | new3pi |      0 |      0 | 2012-03-07 10:20:11 | 174 | 
| 2012-03-07 09:05:09 | loader.py  | 25782 | ipp005 | new3pi |      0 |      0 | 2012-03-07 10:48:13 | 175 | 
+---------------------+------------+-------+--------+--------+--------+--------+---------------------+-----+

The queue tables

The queue.py program is in charge of queuing up unprocessed items (be they stacks, detections or whatever). It populates the tables below so that the loader.py programs can get to work.

The box table

The stripe table

The pending table

The batch tables

The batch table

batch contains an entry for every batch, regardless of type, that has ever been processed or attempted to be processed through ippToPsps. The detection, stack and object tables all have foreign key relationships with this table using the batch_id column. batch_id is an auto-incrementing integer and ends up forming the batch name of the final file submitted to PSPS.

Setting the purged column to 1 will mean this batch will be removed from the datastore and local disk by cleanup.py regardless of load status. See here.

The detection table

This table contains one row per detection (P2) batch attempted. It references the batch table (above) with a foreign key constraint on the batch_id column.

The stack table

This table contains one row per stack (ST) batch attempted. It references the batch table (above) with a foreign key constraint on the batch_id column.

Some handy SQL

Count pending items per strip

SELECT ra_center, COUNT(*) AS num  FROM box JOIN pending ON (id = box_id) WHERE config = 'new3pi' GROUP BY ra_center;

See what each client is working on

SELECT clients.config, host, timestamp, ra_center FROM stripe JOIN clients ON (client_id = id) ORDER BY ra_center;

Counts of pending stuff for a given config

SELECT config, batch_type, COUNT(*) FROM pending JOIN box ON (id = box_id) WHERE config = 'newmd4' GROUP BY batch_type;

Find duplicated exposures

SELECT timestamp, exp_id, batch_type, batch_id,  COUNT(exp_id) AS NumOccurrences FROM detection JOIN batch USING (batch_id) WHERE dvo_db = 'LAP.ThreePi.20110809' AND loaded_to_datastore  GROUP BY exp_id HAVING NumOccurrences > 1 order by timestamp;