Ignore:
Timestamp:
09/01/21 07:56:42 (3 years ago)
Author:
maslowski
Message:
Added better error handeling and tracking in the databas.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/Mars/hawc/processing/DiskToDB/write-calibration-query.sh

    r20091 r20103  
    1414function ErrExit()
    1515{
    16         echo "ERROR: Line `caller`: ${BASH_COMMAND}" >&2
     16        echo "ERROR: Line \`caller\`: ${BASH_COMMAND}" >&2
    1717        exit 1
    1818}
    … …  
    9494echo \
    9595"\
     96-- Set Status to 6 if there was an error is the extraction of RawData
     97UPDATE DataOnDisk
     98SET DataOnDisk.calibration = 6
     99WHERE DataOnDisk.header != 0;
     100
    96101CREATE TEMPORARY TABLE Calibration_tmp
    97102(
    98    WITH Table1 AS
    99    (
    100       SELECT
    101          -- NIGHT and RUNID of the data file
    102          R1.Telescope AS Telescope,
    103          R1.NIGHT     AS NIGHT,
    104          R1.RUNID     AS RUNID,
    105          -- NIGHT and RUNID of the DRS file
    106          R2.NIGHT     AS DrsNight,
    107          R2.RUNID     AS DrsRunID,
    108          -- Time difference between the two
    109          LEAST(
    110              ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
    111              ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-END\`)),
    112              ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
    113              ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)-UNIX_TIMESTAMP(R1.\`DATE-END\`))
    114          ) AS DeltaT,
    115          -- Assign a row number to each combination of data-file and drs-file
    116          -- after sorting them by time difference
    117          ROW_NUMBER() OVER(PARTITION BY R1.NIGHT, R1.RUNID, R1.Telescope ORDER BY
    118             LEAST(
    119             ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
    120             ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-END\`)),
    121             ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
    122             ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)-UNIX_TIMESTAMP(R1.\`DATE-END\`))
    123             )
    124          ) AS RowNumber
    125       FROM
    126          RawData R1
    127       -- Join the table with itself to get all possible combinations of runs
    128       CROSS JOIN
    129          RawData R2
    130       -- Join the DRS files with the table that tells you whether they can be used
    131       -- if the table does not (yet) exists, this can be omitted
    132       -- LEFT JOIN
    133       --    DoNotUse ON (R2.NIGHT=DoNotUse.NIGHT AND R2.RUNID=DoNotUse.RUNID)
    134       WHERE
    135          -- Of course claibration must be from the same telescope
    136          R1.Telescope=R2.Telescope
    137       AND
    138          -- Select all combinations that have a data file in R1 and a drs file (at step 1) in R2
    139          ISNULL(R1.DRSSTEP) AND R2.DRSSTEP=1
    140       AND
    141          -- Only DRS files with 100 events and the correct ROI are considered
    142          R2.ZNAXIS2=1000 AND R1.NROI=R2.NROI
    143          AND R1.NIGHT = ${NIGHT}
    144          AND R1.NIGHT = ${NIGHT}
    145          AND R1.Telescope = ${TEL}
    146       -- AND
    147          -- Check if they are valid
    148          -- IFNULL(DoNotUse.WrongVoltage,false)!=true
    149       -- This can be used to restrict the number of combinations     
    150       -- AND ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`))<78*3600
    151       ORDER BY
    152          R1.Telescope,
    153          R1.NIGHT,
    154          R1.RUNID,
    155          DeltaT
    156     )
    157     SELECT
    158        Telescope,
    159        NIGHT,
    160        RUNID,
    161        DrsNight,
    162        DrsRunId,
    163        DeltaT/3600e0 AS DeltaT
    164     FROM
    165        Table1
    166     WHERE
    167        -- Select only the rows with the smallest DeltaT
    168        RowNumber = 1
     103        WITH Table1 AS
     104        (
     105                SELECT
     106                -- NIGHT and RUNID of the data file
     107                R1.Telescope AS Telescope,
     108                R1.NIGHT     AS NIGHT,
     109                R1.RUNID     AS RUNID,
     110                -- NIGHT and RUNID of the DRS file
     111                R2.NIGHT     AS DrsNight,
     112                R2.RUNID     AS DrsRunID,
     113                -- Time difference between the two
     114                LEAST(
     115                        ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
     116                                - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
     117                        ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
     118                                - UNIX_TIMESTAMP(R1.\`DATE-END\`)),
     119                        ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
     120                                - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
     121                        ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
     122                                - UNIX_TIMESTAMP(R1.\`DATE-END\`))
     123                ) AS DeltaT,
     124                -- Assign a row number to each combination of data-file and
     125                -- drs-file after sorting them by time difference
     126                ROW_NUMBER() OVER(PARTITION BY R1.NIGHT, R1.RUNID, R1.Telescope
     127                        ORDER BY
     128                        LEAST(
     129                                ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
     130                                        - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
     131                                ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
     132                                        - UNIX_TIMESTAMP(R1.\`DATE-END\`)),
     133                                ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
     134                                        - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
     135                                ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
     136                                        - UNIX_TIMESTAMP(R1.\`DATE-END\`))
     137                        )
     138                ) AS RowNumber
     139                FROM (
     140                        SELECT RawData.* FROM RawData
     141                        INNER JOIN DataOnDisk
     142                        ON DataOnDisk.Telescope = RawData.Telescope
     143                        AND DataOnDisk.NIGHT = RawData.NIGHT
     144                        AND DataOnDisk.RUNID = RawData.RUNID
     145                        AND DataOnDisk.ISDRSFILE = 0
     146                        WHERE DataOnDisk.header = 0
     147                ) AS R1
     148                -- Join the table with itself to get all possible combinations
     149                -- of runs
     150                CROSS JOIN (
     151                        SELECT RawData.* FROM RawData
     152                        INNER JOIN DataOnDisk
     153                        ON DataOnDisk.Telescope = RawData.Telescope
     154                        AND DataOnDisk.NIGHT = RawData.NIGHT
     155                        AND DataOnDisk.RUNID = RawData.RUNID
     156                        AND DataOnDisk.ISDRSFILE = 0
     157                        WHERE DataOnDisk.header = 0
     158                ) AS R2
     159                -- Join the DRS files with the table that tells you whether
     160                -- they can be used if the table does not (yet) exists, this
     161                -- can be omitted
     162                WHERE
     163                -- Of course claibration must be from the same telescope
     164                R1.Telescope=R2.Telescope
     165                AND
     166                -- Select all combinations that have a data file in R1 and a
     167                -- drs file (at step 1) in R2
     168                ISNULL(R1.DRSSTEP) AND R2.DRSSTEP=1
     169                AND
     170                -- Only DRS files with 100 events and the correct ROI are
     171                -- considered
     172                R2.ZNAXIS2=1000 AND R1.NROI=R2.NROI
     173                AND R1.NIGHT = ${NIGHT}
     174                AND R2.NIGHT = ${NIGHT}
     175                AND R1.Telescope = ${TEL}
     176                -- AND
     177                -- Check if they are valid
     178                -- IFNULL(DoNotUse.WrongVoltage,false)!=true
     179                -- This can be used to restrict the number of combinations     
     180                -- AND ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`))<78*3600
     181                ORDER BY
     182                R1.Telescope,
     183                R1.NIGHT,
     184                R1.RUNID,
     185                DeltaT
     186        )
     187        SELECT
     188        Telescope,
     189        NIGHT,
     190        RUNID,
     191        DrsNight,
     192        DrsRunId,
     193        DeltaT/3600e0 AS DeltaT
     194        FROM
     195        Table1
     196        WHERE
     197        -- Select only the rows with the smallest DeltaT
     198        RowNumber = 1
    169199);
    170200
    171 ${INSERT} Calibration SELECT * FROM Calibration_tmp;
     201-- Insert Calibration data into Database
     202INSERT INTO Calibration SELECT * FROM Calibration_tmp;
     203
     204-- Set Status to 0 for all valid headers
     205UPDATE DataOnDisk
     206SET DataOnDisk.calibration = 0
     207WHERE DataOnDisk.header = 0;
     208
     209-- Set Status to 2 if there was no drs file with step = 1 in the NIGHT
     210UPDATE DataOnDisk
     211SET DataOnDisk.calibration = 2
     212WHERE EXISTS(SELECT * FROM Calibration WHERE NIGHT=${NIGHT}) = 0;
    172213"\ >> ${OUTPUT}
Note: See TracChangeset for help on using the changeset viewer.