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           | 
| pending             | 
| stack               | 
| stripe              | 
+---------------------+

The config table

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

The detection table

The stack table

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;