[[PageOutline]]

[wiki:ippToPsps Back to ippToPsps main page]

= The {{{ippToPsps}}} scratch database =

== Overview ==

All instances of {{{loader.py}}} need use of a local 'scratch' database in which to import FITS tables, manipulate then export.

You can have more than one scratch database per machine. The naming convention for scratch databases on a single machine is:

{{{
ipptopsps_scratch
ipptopsps_scratch2
ipptopsps_scratch3
}}}

Instances of {{{loader.py}}} will cycle through all of the above until a 'free' scratch database is found. It is obviously important that two loaders do not use the same scratch database at the same time.

All code dealing directly with the scratch database is in the '''!ScratchDb''' class, [browser:trunk/ippToPsps/jython/scratchdb.py here].

== DVO tables ==

Because scratch databases are used to import data from DVO databases, there are a number of DVO tables. These are:

 * {{{dvoDetection}}}
 * {{{dvoDetectionFull}}}
 * {{{dvoDone}}}
 * {{{dvoImages}}}
 * {{{dvoPhotcodes}}}
 * {{{dvoSkyTable}}}

=== {{{dvoDone}}} ===
 
{{{dvoDone}}} is a table that records which DVO files have been ingested into this MySQL scratch database. For example:

{{{
+----+---------------+-------------------------------------------------------------+--------------+-----------+
| id | region        | path                                                        | modifiedDate | size      |
+----+---------------+-------------------------------------------------------------+--------------+-----------+
|  1 | null          | /data/ipp005.0/gpc1/catdirs/MD04.20120307/SkyTable.fits     |   1331175448 |   9077760 | 
|  2 | null          | /data/ipp005.0/gpc1/catdirs/MD04.20120307/Photcodes.dat     |   1322621121 |     97920 | 
|  3 | null          | /data/ipp005.0/gpc1/catdirs/MD04.20120307/Images.dat        |   1331174312 |  36460800 | 
|  4 | n0000/0240.01 | /data/ipp005.0/gpc1/catdirs/MD04.20120307/n0000/0240.01.cpm |   1331174328 | 544037760 | 
+----+---------------+-------------------------------------------------------------+--------------+-----------+
}}}

Whenever connecting to a scratch database, {{{ippToPsps}}} programs first check that the DVO database you plan to use (as defined in the [wiki:ippToPsps#Loadingconfigs config]) correspond to the one currently ingested into the scratch database. If not, the user will be prompted and asked to confirm whether he or she wishes to switch. Care must be taken as you may have ingested many gigabytes of DVO data into a scratch database and all will be lost if you switch to a different DVO.

The first three rows in the table above detail three DVO metadata files, {{{SkyTable.fits}}}, {{{Photcodes.dat}}} and {{{Images.dat}}}, which are detailed [wiki:ippToPsps_ScratchDatabase#dvoImagesdvoPhotcodesdvoSkyTable below]. Following this are DVO detection files, eg {{{cpm}}}, {{{cpt}}} or {{{cps}}} files. By maintaining file modified dates of all files ingested, the [browser:trunk/ippToPsps/jython/dvo.py Dvo] class is able to operate a little like rsync and update any DVO files that may have changed on disk since the last time the scratch database was synchronized with DVO.
=== {{{dvoDetection}}}/{{{dvoDetectionFull}}} ===

This table is used to store all IDs that we take from DVO, eg objID ippObjId etc. {{{dvoDetecion}}} is used when we are using the standard C-interface to access DVO (details [wiki:ippToPsps#AccessingDVO here]), whereas {{{dvoDetectionFull}}} is used when pre-ingesting large amounts of data from DVO.

=== {{{dvoImages}}}/{{{dvoPhotcodes}}}/{{{dvoSkyTable}}} ===

These tables represent the DVO FITS files Images.dat, Photcodes.dat and !SkyTable.dat respectively.


'''Images.dat'''

'''Photcodes.dat'''

'''SkyTable.dat'''

This table defines regions within DVO.
== Setting up a loading machine ==

Each instance of {{{loader.py}}} uses a 'scratch' MySQL database on {{{localhost}}} while processing. So, to do any loading, a local MySQL server should be up-and-running with a database created as the {{{ipp}}} user for use by {{{ippToPsps}}}. The default name for this is {{{ipptopsps_scratch}}}.

Because {{{ippToPsps}}} uses this local database as a scratch database, there are large number of inserts and deletions, meaning large log files. For this reason, it is important to configure MySQL to store its data on a local partition, and not {{{/var}}} as is the default. For example, on {{{ipp005}}}:

{{{
/var/lib/mysql
}}}

was moved to:

{{{
/export/ipp005.0/mysql
}}}

In addition, it is prudent to decrease the default MySQL log-retention period from 90 days to 1 day. This is done is in

{{{
/etc/mysql/my.cnf 
}}}

on the line

{{{
expire_logs_days                                = 90
}}}

You will need (if it is not done already) to create an {{{ipp}}} user. Connect to the local MySQL server as root then to the following.

{{{
GRANT ALL PRIVILEGES ON *.* TO 'ipp'@'localhost' IDENTIFIED BY 'ipp' WITH GRANT OPTION;
}}}

Now disconnect then reconnect as {{{ipp}}} and create the scratch database.

{{{
CREATE DATABASE ipptopsps_scratch;
}}}

Finally, you need to setup the scratch database with the initialization tables and stored procedures necessary for processing. To do this, use the {{{setupScratchDb.py}}} program, like this:

{{{
./run.sh setupScratchDb.py someConfig nameOfScratchDb
}}}

The choice of config here is not important, so use any.

At the time of writing, scratch databases are set up on the following IPP hosts:

{{{
ipp005
ipp006
ipp007
ipp033
ipp034
ipp045
}}}

{{{ipp005}}} and {{{ipp045}}} in fact have ''three'' scratch databases each, so three clients can potentially be run on these machines. This is particularly useful on {{{ipp005}}} as this is where the DVO databases are currently stored, so clients running on the same machine have a much higher throughput. ({{{ipp045}}} used to host a backup 3pi DVO database.)


== Stored procedure for psf likelihoods ==

It is necessary, hopefully temporarily, that {{{ippToPsps}}} calculates psf, extended and cosmic ray likelihoods for each detection. Fot this it has been necessary to implement a stored procedure within each scratch database in order to perform the calculations. This procedure is installed by a method on the !ScratchDb class, [browser:trunk/ippToPsps/jython/scratchdb.py here].