[[PageOutline]]

[wiki:ippToPsps 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, [http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/browser/trunk/ippToPsps/config/settings.xml here], while it's schema can be found in svn, [http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/browser/trunk/ippToPsps/config/ippToPspsDbSchema.sql here].

cascading deletes where necessary

{{{
+---------------------+
| Tables_in_ipptopsps |
+---------------------+
| batch               | 
| box                 | 
| clients             | 
| config              | 
| detection           | 
| 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 ===

A very important table, {{{batch}}} stores TODO

{{{batch_id}}} is an auto-incrementing integer column. This number ends up as the actual batch name in the final file submitted to PSPS

=== 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;
}}}