wiki:DatabaseBasedAnalysis/Tables

Version 39 (modified by tbretz, 11 months ago) (diff)

--

This is an overview of the most important tables and some important remarks. Links are given to the comments and type of each column.

RunType

factdata/RunType

Source

factdata/Source

RunInfo

factdata/RunInfo

The RunInfo table is the most important one. It stored run-wise information. In a first step, a lot of data is directly extracted from the header of the raw run-files. Later, values are calculated from other sources, mainly slow-control file (auxiliary data: aux) and filled into the table as well.

Calculating the on-time

The best estimate for the on-time of the data acquisition is the difference between the first and last event (fRunStop-fRunStart). It should be precise with roughly the data rate, so better than 1s. The on-time calculated by the FTM is only precise with the FTM rate which is a few seconds, but the FTM provides accurate information on the relative on-time w.r.t. to the total time. This information can be used to calculate the on-time in seconds of a run as follows:

TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn

For a 1min run, the accuracy should be in the order of 1s/60s (<2%) and for a 5min run 1s/300s (<0.4%). These are upper limits, as in reality, the rate of events is usually higher than 1Hz and the timing of the Ethernet connection should be at least in the order of the average time-difference between two events as well (although this is not guaranteed).

Selection by zenith angle

Ideally, if you select events from a certain zenith angle range (e.g. 10°-50°), you don't want events outside of that range. So, ideally the lower bound is cut on fZenithAngleMin and the upper bound on fZenithAngleMax like

fZenithAngleMin>10 AND fZenithAngleMax<50

Threshold setting

To select data by threshold, the ideal values is usually fThresholdMinSet. It reflects the value obtained as minimum threshold during a run from the currents at its beginning. While some trigger patches could have a significantly higher threshold during a run (which affects the mean value over all patches) the MinSet value reflects usually the majority of the patch thresholds and should be similar (or in most cases identical) to the average (over all events) median (over the camera).

Images

Some important remark about access speed! The table is intentionally laid out as compressed InnoDB table. InnoDB has the advantage that the index and the row the index is pointing to is basically the same. That means that ones a row has been selected, there is no need for the disc to read the data from somewhere else anymore. Although, the access itself is not important for a SSD, the time for processing and decompression is. In particular compression is done in blocks so that more than a single index has to be read anyhow. Translated to data access that means that if you access the data by means of the index, access is optimized and fast. That also means that if you do not access your data (e.g. you just want to count entries in your index) it can be annoyingly fast because all the data has to be read from the drive.

--- not yet available ---

Position

--- not yet available ---

AnalysisResultsRunISDC

factdata/AnalysisResultsRunISDC

The number of background events (fNumBgEvts) is already divided by the number of wobble regions (5).

To calculate the signifciance, you can simply do LiMa(fNumSigEvts, fNumBgEvts). To calculate the statistical error, use ExcErr(fNumSigEvts, fNumBgEvts). Both function assume that the number of background events given is the average number and that the scale factor is 5.

Plotting

Database Explorer

Status Tables

There are a few status tables which give status information on the on-going processing. They usually contain an index (one or more columns, e.g. fNight/fRunID), the columns fStartTime, fStopTime and fReturnCode. The meaning is the following:

Status fStartTime fStopTime fReturnCode Comment
Waiting NULL NULL [NULL] Processing is supposed to be done (just not yet started or pre-conditions are still missing)
Running NOT NULL NULL [NULL] Processing is supposed to be done and running already (this includes Scheduled)
Scheduled -marker- NULL [NULL] Pre-conditions fulfilled, scheduled for processing by job-manager (only callisto and star)
Success > 2000 > 2000 NULL Processing was successfully finished for this index
Failed > 2000 > 2000 NOT NULL Processing failed
Blocked < 2000 < 2000 [NULL] Processing is not supposed to run for this index

The marker -marker- is the special datetime 1971-01-01 01:01:01.

A corresponding query to get an overview of all index entries in a status table StatusTable? could look like the following

SELECT 
   COUNT(IF(    ISNULL(fStartTime)           AND ISNULL(fStopTime),                                           1, NULL)) AS 'WAITING', 
   COUNT(IF(NOT ISNULL(fStartTime)           AND ISNULL(fStopTime),                                           1, NULL)) AS 'RUNNING', 
   COUNT(IF(fStartTime='1971-01-01 01:01:01' AND ISNULL(fStopTime),                                           1, NULL)) AS 'SCHEDULED', 
   COUNT(IF(fStartTime>'2000-01-01 00:00:00' AND fStopTime>'2000-01-01 00:00:00' AND     ISNULL(fReturnCode), 1, NULL)) AS 'SUCCESS', 
   COUNT(IF(fStartTime>'2000-01-01 00:00:00' AND fStopTime>'2000-01-01 00:00:00' AND NOT ISNULL(fReturnCode), 1, NULL)) AS 'FAILED',
   COUNT(IF(fStartTime<'2000-01-01 00:00:00' AND fStopTime<'2000-01-01 00:00:00',                             1, NULL)) AS 'BLOCKED'
FROM
   StatusTable

Here is a query to give a full status overview (only data runs are included)

SELECT
   'RunInfo' AS 'Type',
   COUNT(IF(fRunTypeKEY!=1, 1, NULL)) AS 'BLOCKED',
   0 AS 'WAITING',
   0 AS 'RUNNING',
   0 AS 'SCHEDULED',
   COUNT(IF(fRunTypeKEY=1, 1, NULL)) AS 'SUCCESS',
   0 AS 'FAILED'
FROM
   RunInfo

UNION ALL

SELECT
   'Callisto',
   COUNT(IF(fStartTime<'1972-01-01 00:00:00' AND fStopTime<'1972-01-01 00:00:00', 1, NULL)),
   COUNT(IF(    ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(NOT ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime='1971-01-01 01:01:01' AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND     ISNULL(fReturnCode), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND NOT ISNULL(fReturnCode), 1, NULL))
FROM
   CallistoStatus
LEFT JOIN
   RunInfo USING (fNight, fSequenceID)
WHERE
   fRunTypeKey=1

UNION ALL

SELECT
   'Star',
   COUNT(IF(fStartTime<'1972-01-01 00:00:00' AND fStopTime<'1972-01-01 00:00:00', 1, NULL)),
   COUNT(IF(    ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(NOT ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime='1971-01-01 01:01:01' AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND     ISNULL(fReturnCode), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND NOT ISNULL(fReturnCode), 1, NULL))
FROM
   StarStatus
LEFT JOIN
   RunInfo USING (fNight, fSequenceID)
WHERE
   fRunTypeKey=1

UNION ALL

SELECT 
   'Images',
   COUNT(IF(fStartTime<'1972-01-01 00:00:00' AND fStopTime<'1972-01-01 00:00:00', 1, NULL)),
   COUNT(IF(    ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(NOT ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime='1971-01-01 01:01:01' AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND     ISNULL(fReturnCode), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND NOT ISNULL(fReturnCode), 1, NULL))
FROM
   StarEventsFilledStatus
LEFT JOIN
   RunInfo USING (fNight, fRunID)
WHERE
   fRunTypeKey=1

UNION ALL

SELECT
   'Position',
   COUNT(IF(fStartTime<'1972-01-01 00:00:00' AND fStopTime<'1972-01-01 00:00:00', 1, NULL)),
   COUNT(IF(    ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(NOT ISNULL(fStartTime) AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime='1971-01-01 01:01:01' AND ISNULL(fStopTime), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND     ISNULL(fReturnCode), 1, NULL)),
   COUNT(IF(fStartTime>'1972-01-01 00:00:00' AND fStopTime>'1972-01-01 00:00:00' AND NOT ISNULL(fReturnCode), 1, NULL))
FROM
   CalcSourceStatus
LEFT JOIN
   RunInfo USING (fNight, fRunID)
WHERE
   fRunTypeKey=1