[[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 | | object | | pending | | stack | | stripe | +---------------------+ }}} == The {{{config}}} table == '''Note:''' Configs can be created from scratch, or edited, using any {{{ippToPsps}}} program by specifying {{{edit}}} as the config argument at start-up. More details can be found [wiki:ippToPsps#Loadingconfigs here]. 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. || '''Field''' || '''Type''' || '''Null''' || '''Key''' || '''Default value''' || '''Description''' || || timestamp || timestamp || NO || || CURRENT_TIMESTAMP || all clients update this timestamp regularly, like a heartbeat || || name || varchar(30) || NO || PRI || NULL || the text name of this config || || datastore_product || varchar(30) || YES || || NULL || the datastore product, thus far only PSPS_test and PSPS_JHU have been used || || datastore_type || varchar(30) || YES || || IPP_PSPS || the datastore type. The defauls is only ever used || || datastore_publish || tinyint(1) || YES || || 0 || 1 or 0 - should we publish or not? || || dvo_label || varchar(100) || YES || || NULL || the label for querying gpc1 to get contents of the DVO database || || dvo_location || varchar(1000) || YES || || NULL || the location of this DVO database on disk || || min_ra || double || YES || || 0 || the min RA of the box on the sky we should load from (in degrees) || || max_ra || double || YES || || 360 || the max RA of the box on the sky we should load from (in degrees) || || min_dec || double || YES || || -30 || the min Dec of the box on the sky we should load from (in degrees) || || max_dec || double || YES || || 90 || the max Dec of the box on the sky we should load from (in degrees) || || box_size || double || YES || || 4 || the size of the box (in degrees) that we should use to tile up the sky || || base_path || varchar(1000) || YES || || /data/ipp005.0/ipptopsps/ || the base path for all data to be stored || || data_release || smallint(6) || YES || || 0 || data release to end up in PSPS meta tables || || delete_local || tinyint(1) || YES || || 0 || 0 or 1 - should we delete local data when ready? || || delete_datastore || tinyint(1) || YES || || 1 || 0 or 1 - should we delete from datastore when ready?|| || delete_dxlayer || tinyint(1) || YES || || 1 || 0 or 1 - should we delete from DXLayer when ready? || || epoch || timestamp || NO || || 0000-00-00 00:00:00 || when queuing, this is taken as the beginning of time. Means we can reload surveys more than once - if we are careful || || survey || varchar(10) || YES || || NULL || what survey? eg 3PI, MDF (see available surveys in IN table [wiki:ippToPsps_Initialization here]) || || psps_survey || varchar(10) || YES || || NULL || PSPS survey, eg 'SA3' for SAS 3 year survey. Always a 3-character name || || queue_P2 || tinyint(1) || YES || || 1 || 0 or 1 - should we queue P2 batches? || || queue_ST || tinyint(1) || YES || || 0 || 0 or 1 - should we queue ST batches? || || queue_OB || tinyint(1) || YES || || 0 || 0 or 1 - should we queue OB batches? || || active || tinyint(1) || YES || || 1 || defines whether this config is in active use or not || == 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 {{{timestamp}}} column acts as form of 'heartbeat' for a client. All {{{ippToPsps}}} programs check-in with this table routinely and update {{{timestamp}}} so it is easy to see of a client has stopped. To manually pause or kill a client, simply update the relevant column in this table to '1'. == 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 === When queuing, the sky is split into equally sized boxes, the size of which is determined in the config in use. The {{{box}}} tables lists the RA/Dec centers of each box, as well as the box size, for each config that currently has items queued. {{{ +---------------------+-------+--------+-----------+------------+----------+ | timestamp | id | config | ra_center | dec_center | box_side | +---------------------+-------+--------+-----------+------------+----------+ | 2012-04-03 16:47:35 | 41879 | new3pi | 2 | -28 | 4 | | 2012-04-03 16:47:35 | 41880 | new3pi | 2 | -24 | 4 | | 2012-04-03 16:47:35 | 41881 | new3pi | 2 | -20 | 4 | | 2012-04-03 16:47:35 | 41882 | new3pi | 2 | -16 | 4 | | 2012-04-03 16:47:35 | 41883 | new3pi | 2 | -12 | 4 | | 2012-04-03 16:47:35 | 41884 | new3pi | 2 | -8 | 4 | | 2012-04-03 16:47:35 | 41885 | new3pi | 2 | -4 | 4 | | 2012-04-03 16:47:35 | 41886 | new3pi | 2 | 0 | 4 | | 2012-04-03 16:47:35 | 41887 | new3pi | 2 | 4 | 4 | | 2012-04-03 16:47:35 | 41888 | new3pi | 2 | 8 | 4 | | 2012-04-03 16:47:35 | 41889 | new3pi | 2 | 12 | 4 | etc }}} The {{{config}}} column has a foreign key constraint with {{{name}}} from the {{{config}}} table. This means if a particular config is deleted from the {{{config}}} table then all related entries here in the {{{box}}} table will also be removed automatically. The {{{id}}} column is unique and is acts as a foreign key for the {{{pending}}} table, i.e. removal of a box here will remove all related entries in the pending table. === The {{{stripe}}} table === When queuing, the sky is split up into equally size boxes. Because of the way the Dvo class ingests DVO files, it is more efficient for an instance of {{{loader.py}}} to process boxes in stripes so that any DVO files that overlap the boundary of two neighboring boxes can be retained when the next box-worth of DVO files is ingested. The stripe table is where {{{loader.py}}} instances register that stripe that they are working on. New instances of {{{loader.py}}} look first for stripes that are not being processed by other clients, but failing that will choose the one with the most pending items. === The {{{pending}}} table === the {{{pending}}} table is first populated by {{{queue.py}}} then read by instances {{{loader.py}}}. It consists of unique stage_ids for each batch type that has been queues for each box on the sky, as shown here: {{{ +--------+------------+----------+ | box_id | batch_type | stage_id | +--------+------------+----------+ | 44579 | OB | 93141 | | 44579 | OB | 93142 | | 44579 | OB | 93143 | | 44579 | OB | 93144 | | 44579 | OB | 93145 | | 44579 | OB | 93146 | | 44579 | OB | 93147 | | 44579 | OB | 93148 | | 44579 | OB | 93149 | | 44579 | OB | 93150 | | 44579 | OB | 93151 | | 44579 | OB | 93152 | | 44579 | OB | 93153 | etc }}} A foreign key constraint on {{{box_id}}} with the {{{box}}} table means that entries will be deleted automatically if a box definition is removed from the {{{box}}} table. Also, {{{loader.py}}} instances will remove rows from this table as they begin to process them. If they fail to process the item in question for whatever reason, it will be re-queued the next time {{{queue.py}}} is run. == 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 [wiki:ippToPsps#Purging 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. === The {{{object}}} table === This table contains one row per object (OB) 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; }}}