IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 23688 for trunk/ippTools/share


Ignore:
Timestamp:
Apr 2, 2009, 2:51:37 PM (17 years ago)
Author:
Paul Price
Message:

Merging in branches/pap/ from r23685. Some conflicts, most notably in ippTools, but these resolved fairly simply. Only question mark is on regtool reversion, but I think I got it right. Everything builds fine.

Location:
trunk
Files:
32 edited

Legend:

Unmodified
Added
Removed

  • trunk

  • trunk/ippTools/share/chiptool_completely_processed_exp.sql

    r23381 r23688  
    3535        COUNT(rawImfile.class_id) = COUNT(chipProcessedImfile.class_id)
    3636        AND SUM(chipProcessedImfile.fault) = 0
     37        AND SUM(chipProcessedImfile.quality > 0) != COUNT(*)
    3738    ) as Foo

  • trunk/ippTools/share/difftool_definebyquery.sql

    r20973 r23688  
    3838    WHERE
    3939        warpSkyfile.fault = 0
    40         AND warpSkyfile.ignored = 0
     40        AND warpSkyfile.quality = 0
    4141    -- warpsToDiff WHERE hook %s
    4242    ) AS warpsToDiff
    … …  
    5151    WHERE stackRun.state = 'full'
    5252        AND stackSumSkyfile.fault = 0
     53        AND stackSumSkyfile.quality = 0
    5354    -- stacksForDiff WHERE hook %s
    5455    GROUP BY

  • trunk/ippTools/share/difftool_definebyquery_part1.sql

    r20973 r23688  
    1     SELECT
    2         exp_id,
    3         warp_id,
    4         rawExp.filter,
    5         warpRun.label,
    6         warpRun.tess_id,
    7         count(skycell_id) as skycell_count
    8     FROM warpRun
    9     JOIN warpSkyfile USING(warp_id)
    10     JOIN fakeRun USING(fake_id)
    11     JOIN camRun USING(cam_id)
    12     JOIN chipRun USING (chip_id)
    13     JOIN rawExp USING(exp_id)
    14     LEFT JOIN diffRun USING(exp_id)
    15 
    16     WHERE
    17         warpSkyfile.ignored = 0
    18         -- warp where hook %s
    19         -- exp where hook %s
    20         -- diff where hook %s
    21 
    22     GROUP BY exp_id, warp_id DESC
     1SELECT
     2    exp_id,
     3    warp_id,
     4    rawExp.filter,
     5    warpRun.label,
     6    warpRun.tess_id,
     7    COUNT(skycell_id) as skycell_count
     8FROM warpRun
     9JOIN warpSkyfile USING(warp_id)
     10JOIN fakeRun USING(fake_id)
     11JOIN camRun USING(cam_id)
     12JOIN chipRun USING(chip_id)
     13JOIN rawExp USING(exp_id)
     14LEFT JOIN diffRun USING(exp_id)
     15WHERE
     16    warpSkyfile.fault = 0
     17    AND warpSkyfile.quality = 0
     18    -- warp where hook %s
     19    -- exp where hook %s
     20    -- diff where hook %s
     21GROUP BY exp_id, warp_id DESC

  • trunk/ippTools/share/difftool_definebyquery_part2.sql

    r21308 r23688  
    11-- insert skycells to diff
    22INSERT INTO skycellsToDiff
     3SELECT
     4    0,                      -- diff_id
     5    warpSkyfile.skycell_id,
     6    warpSkyfile.warp_id,    -- warp1
     7    NULL,                   -- stack1
     8    NULL,                   -- warp2
     9    max_stack_id,           -- stack2
     10    warpSkyfile.tess_id,
     11    0
     12FROM warpSkyfile
     13JOIN warpRun USING(warp_id)
     14LEFT JOIN (
    315    SELECT
    4         0,                      -- diff_id
    5         warpSkyfile.skycell_id,
    6         warpSkyfile.warp_id,    -- warp1
    7         NULL,                   -- stack1
    8         NULL,                   -- warp2
    9         max_stack_id,           -- stack2
    10         warpSkyfile.tess_id,
    11         0
    12         FROM warpSkyfile
    13         JOIN warpRun USING(warp_id)
    14         LEFT JOIN (
    15             SELECT
    16                 MAX(stack_id) AS max_stack_id, -- most recent stack, by virtue of auto-increment
    17                 stackRun.skycell_id,
    18                 stackRun.tess_id,
    19                 filter
    20             FROM stackRun
    21             JOIN stackSumSkyfile USING(stack_id)
    22             WHERE stackSumSkyfile.fault = 0
    23                 AND stackRun.state = 'full'
    24             -- stacks where hook %s
    25             GROUP BY
    26                 skycell_id,
    27                 filter
    28             ) as bestStacks
    29         USING(skycell_id)
    30         WHERE
    31             warpSkyfile.warp_id = %lld
    32             AND ignored = 0
    33             AND filter = '%s'
    34             AND warpSkyfile.tess_id = bestStacks.tess_id
    35         -- (good fraction test goes here for example also above)
    36         -- warp where hook %s
     16        MAX(stack_id) AS max_stack_id, -- most recent stack, by virtue of auto-increment
     17        stackRun.skycell_id,
     18        stackRun.tess_id,
     19        filter
     20    FROM stackRun
     21    JOIN stackSumSkyfile USING(stack_id)
     22    WHERE stackSumSkyfile.fault = 0
     23        AND stackSumSkyfile.quality = 0
     24        AND stackRun.state = 'full'
     25    -- stacks where hook %s
     26    GROUP BY
     27        skycell_id,
     28        filter
     29    ) as bestStacks USING(skycell_id)
     30WHERE
     31    warpSkyfile.warp_id = %lld
     32    AND warpSkyfile.fault = 0
     33    AND warpSkyfile.quality = 0
     34    AND filter = '%s'
     35    AND warpSkyfile.tess_id = bestStacks.tess_id
     36-- warp where hook %s

  • trunk/ippTools/share/difftool_definewarpwarp_insert.sql

    r23504 r23688  
    1111FROM warpSkyfile AS inputWarpSkyfile
    1212JOIN warpSkyfile AS templateWarpSkyfile USING(skycell_id, tess_id)
    13 WHERE inputWarpSkyfile.ignored = 0
    14     AND templateWarpSkyfile.ignored = 0
     13WHERE inputWarpSkyfile.quality = 0
     14    AND templateWarpSkyfile.quality = 0
    1515    AND inputWarpSkyfile.warp_id = %s
    1616    AND templateWarpSkyfile.warp_id = %s

  • trunk/ippTools/share/difftool_todiffskyfile.sql

    r21308 r23688  
    6363    AND diffSkyfile.diff_id IS NULL)
    6464    OR (diffRun.state = 'update'
    65     AND diffSkyfile.fault = 0)
     65    AND diffSkyfile.fault = 0
     66    AND diffSkyfile.quality = 0)
    6667    )
    6768-- Ensure input warps are available
    … …  
    6970    OR (warpRun.state = 'full'
    7071    AND warpSkyfile.fault = 0
    71     AND warpSkyfile.ignored = 0))
     72    AND warpSkyfile.quality = 0))
    7273-- Ensure input stacks are available
    7374    AND (diffInputSkyfile.stack1 IS NULL
    7475    OR (stackRun.state = 'full'
    75     AND stackSumSkyfile.fault = 0))
     76    AND stackSumSkyfile.fault = 0
     77    AND stackSumSkyfile.quality = 0))
    7678-- Ensure template warps are available
    7779    AND (diffInputSkyfile.warp2 IS NULL
    7880    OR (warpTemplateRun.state = 'full'
    7981    AND warpTemplateSkyfile.fault = 0
    80     AND warpTemplateSkyfile.ignored = 0))
     82    AND warpTemplateSkyfile.quality = 0))
    8183-- Ensure template stacks are available
    8284    AND (diffInputSkyfile.stack2 IS NULL
    8385    OR (stackTemplateRun.state = 'full'
    84     AND stackTemplateSkyfile.fault = 0))
     86    AND stackTemplateSkyfile.fault = 0
     87    AND stackTemplateSkyfile.quality = 0))
    8588
    8689

  • trunk/ippTools/share/disttool_toadvance.sql

    r23245 r23688  
    77    (
    88-- raw stage
    9 SELECT 
     9SELECT
    1010    distRun.dist_id,
    1111    stage,
    … …  
    2929UNION
    3030-- chip stage
    31 SELECT 
     31SELECT
    3232    distRun.dist_id,
    3333    stage,
    … …  
    5151UNION
    5252-- warp stage
    53 SELECT 
     53SELECT
    5454    distRun.dist_id,
    5555    stage,
    … …  
    6666        AND distRun.stage = 'warp'
    6767--        AND warpSkyfile.fault = 0
    68 --        AND warpSkyfile.ignored = 0
     68--        AND warpSkyfile.quality = 0
    6969    GROUP BY
    7070        distRun.dist_id,
    … …  
    9999UNION
    100100-- stack stage
    101 SELECT 
     101SELECT
    102102    distRun.dist_id,
    103103    stage,

  • trunk/ippTools/share/flatcorr_completely_processed_chiprun.sql

    r18366 r23688  
    1414        dvodb
    1515    FROM
    16         (SELECT 
     16        (SELECT
    1717            chipRun.*,
    1818            rawImfile.class_id as rawimfile_class_id,
    … …  
    3333            COUNT(rawImfile.class_id) = COUNT(chipProcessedImfile.class_id)
    3434            AND SUM(chipProcessedImfile.fault) = 0
    35         ) as Foo
     35            AND SUM(chipProcessedImfile.quality > 0) != COUNT(*)
     36       ) as Foo
    3637    ) as Bar

  • trunk/ippTools/share/flatcorr_pending.sql

    r18366 r23688  
    1 SELECT 
     1SELECT
    22    *
    33FROM
    … …  
    2121HAVING
    2222    COUNT(rawImfile.class_id) = COUNT(chipProcessedImfile.class_id)
    23     AND SUM(chipProcessedImfile.fault) = 0
     23    AND SUM(chipProcessedImfile.fault > 0) = 0
    2424

  • trunk/ippTools/share/magicdstool_completed_runs.sql

    r23389 r23688  
    44    (
    55-- raw stage
    6 SELECT 
     6SELECT
    77    magicDSRun.magic_ds_id
    88    FROM magicDSRun
    … …  
    2222UNION
    2323-- chip stage
    24 SELECT 
     24SELECT
    2525    magicDSRun.magic_ds_id
    2626    FROM magicDSRun
    … …  
    4040UNION
    4141-- warp stage
    42 SELECT 
     42SELECT
    4343    magicDSRun.magic_ds_id
    4444    FROM magicDSRun
    … …  
    5151        AND magicDSRun.stage = 'warp'
    5252        AND warpSkyfile.fault = 0
    53         AND warpSkyfile.ignored = 0
     53        AND warpSkyfile.quality = 0
    5454    GROUP BY
    5555        magicDSRun.magic_ds_id,
    … …  
    7575        AND magicDSRun.stage = 'diff'
    7676        AND diffSkyfile.fault = 0
     77        AND diffSkyfile.quality = 0
    7778    GROUP BY
    7879        magicDSRun.magic_ds_id,

  • trunk/ippTools/share/magicdstool_getskycells.sql

    r20973 r23688  
    2121    ON warpSkyfile.warp_id = warpSkyCellMap.warp_id
    2222    AND warpSkyfile.skycell_id = warpSkyCellMap.skycell_id
    23     AND warpSkyfile.ignored = 0
     23    AND warpSkyfile.quality = 0
    2424WHERE
    2525    diffSkyfile.fault = 0
     26    AND diffSkyfile.quality = 0
    2627    AND magic_ds_id = %lld

  • trunk/ippTools/share/magicdstool_todestreak.sql

    r23389 r23688  
    6161    AND chipRun.state = 'full'
    6262    AND chipProcessedImfile.fault = 0
     63    AND chipProcessedImfile.quality = 0
    6364    AND magicDSFile.component IS NULL
    6465UNION
    … …  
    9596    AND warpRun.state = 'full'
    9697    AND warpSkyfile.fault = 0
    97     AND warpSkyfile.ignored = 0
     98    AND warpSkyfile.quality = 0
    9899    AND magicDSFile.component IS NULL
    99100UNION
    … …  
    130131    AND magicDSRun.stage = 'diff'
    131132    AND diffSkyfile.fault = 0
     133    AND diffSkyfile.quality = 0
    132134    AND magicDSFile.component IS NULL
    133135) as Foo

  • trunk/ippTools/share/magictool_chipprocessedimfile.sql

    r23389 r23688  
    2626    chipRun.state = 'full'
    2727    AND chipProcessedImfile.fault = 0
     28    AND chipProcessedImfile.quality = 0
    2829--   AND magicRun.state = 'full'
    2930--   AND magicMask.fault = 0

  • trunk/ippTools/share/magictool_definebyquery.sql

    r20743 r23688  
    4545WHERE
    4646    diffSkyfile.fault = 0
     47    AND diffSkyfile.quality = 0
    4748-- WHERE hook %s
    48 AND exp_id = 36475
    49 AND diffRun.label = 'magic_2008-11-12'
     49    AND exp_id = 36475
     50    AND diffRun.label = 'magic_2008-11-12'
    5051GROUP BY
    5152    exp_id,
    … …  
    7980    JOIN diffRun USING(diff_id)
    8081    WHERE
    81         warpSkyfile.ignored = 0
     82        warpSkyfile.quality = 0
    8283        -- magicSkycellNums WHERE hook %s
    8384AND exp_id = 36475

  • trunk/ippTools/share/magictool_definebyquery_insert.sql

    r20973 r23688  
    99    diff_id = @DIFF_ID@ -- Update this with the appropriate diff_id
    1010    AND fault = 0
     11    AND quality = 0

  • trunk/ippTools/share/magictool_definebyquery_select_test.sql

    r20475 r23688  
    2525    JOIN warpSkyfile USING(warp_id, skycell_id)
    2626    WHERE
    27         warpSkyfile.ignored = 0
     27        warpSkyfile.quality = 0
    2828        AND warpRun.state = 'full'
    2929    -- INSERT HERE any additional restrictions (e.g., exp_id, warpSkyfile.good_frac)

  • trunk/ippTools/share/magictool_definebyquery_temp_insert.sql

    r20973 r23688  
    2020WHERE
    2121    diffSkyfile.fault = 0
     22    AND diffSkyfile.quality = 0
    2223-- WHERE hook %s
    2324GROUP BY

  • trunk/ippTools/share/magictool_diffskyfile.sql

    r23389 r23688  
    2020    ON warpSkyfile.warp_id = warpSkyCellMap.warp_id
    2121    AND warpSkyfile.skycell_id = warpSkyCellMap.skycell_id
    22     AND warpSkyfile.ignored = 0
     22    AND warpSkyfile.quality = 0
    2323WHERE
    2424    diffRun.state = 'full'
    2525    AND diffSkyfile.fault = 0
     26    AND diffSkyfile.quality = 0
    2627--   AND magicRun.state = 'full'
    2728--   AND magicMask.fault = 0

  • trunk/ippTools/share/magictool_inputs.sql

    r20973 r23688  
    1010    diffSkyfile.path_base,
    1111    diffSkyfile.fault
     12    diffSkyfile.quality
    1213FROM magicRun
    1314JOIN magicInputSkyfile
    … …  
    3536WHERE
    3637    fault = 0
     38    AND quality = 0

  • trunk/ippTools/share/magictool_warpskyfile.sql

    r23389 r23688  
    2121    warpRun.state = 'full'
    2222    AND warpSkyfile.fault = 0
     23    AND warpSkyfile.quality = 0
    2324--   AND magicRun.state = 'full'
    2425--   AND magicMask.fault = 0

  • trunk/ippTools/share/pxadmin_create_tables.sql

    r23659 r23688  
    1818    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    1919    PRIMARY KEY(exp_name, camera, telescope),
    20     KEY(fault))
    21 ENGINE=innodb DEFAULT CHARSET=latin1;
     20    KEY(fault)
     21) ENGINE=innodb DEFAULT CHARSET=latin1;
    2222
    2323CREATE TABLE summitImfile (
    … …  
    3434    PRIMARY KEY(exp_name, camera, telescope, class, class_id),
    3535    KEY(file_id),
    36     FOREIGN KEY (exp_name, camera, telescope)
    37         REFERENCES  summitExp(exp_name, camera, telescope))
    38 ENGINE=innodb DEFAULT CHARSET=latin1;
     36    FOREIGN KEY(exp_name, camera, telescope) REFERENCES summitExp(exp_name, camera, telescope)
     37) ENGINE=innodb DEFAULT CHARSET=latin1;
    3938
    4039CREATE TABLE pzDownloadExp (
    … …  
    4645    PRIMARY KEY(exp_name, camera, telescope),
    4746    KEY(state),
    48     FOREIGN KEY (exp_name, camera, telescope)
    49         REFERENCES  summitExp(exp_name, camera, telescope))
    50 ENGINE=innodb DEFAULT CHARSET=latin1;
     47    FOREIGN KEY(exp_name, camera, telescope) REFERENCES summitExp(exp_name, camera, telescope)
     48) ENGINE=innodb DEFAULT CHARSET=latin1;
    5149
    5250CREATE TABLE pzDownloadImfile (
    … …  
    6260    PRIMARY KEY(exp_name, camera, telescope, class, class_id),
    6361    KEY(fault),
    64     FOREIGN KEY (exp_name, camera, telescope)
    65         REFERENCES  pzDownloadExp(exp_name, camera, telescope),
    66     FOREIGN KEY (exp_name, camera, telescope, class, class_id)
    67         REFERENCES  summitImfile(exp_name, camera, telescope, class, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
     62    FOREIGN KEY (exp_name, camera, telescope) REFERENCES pzDownloadExp(exp_name, camera, telescope),
     63    FOREIGN KEY(exp_name, camera, telescope, class, class_id) REFERENCES summitImfile(exp_name, camera, telescope, class, class_id)
     64) ENGINE=innodb DEFAULT CHARSET=latin1;
    6865
    6966CREATE TABLE newExp (
    … …  
    9895    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    9996    PRIMARY KEY(exp_id, tmp_class_id),
    100     FOREIGN KEY (exp_id)
    101         REFERENCES  newExp(exp_id))
    102 ENGINE=innodb DEFAULT CHARSET=latin1;
     97    FOREIGN KEY(exp_id) REFERENCES newExp(exp_id)
     98) ENGINE=innodb DEFAULT CHARSET=latin1;
    10399
    104100CREATE TABLE rawExp (
    … …  
    172168    KEY(end_stage),
    173169    KEY(fault),
    174     FOREIGN KEY (exp_id)
    175         REFERENCES  newExp(exp_id))
    176 ENGINE=innodb DEFAULT CHARSET=latin1;
     170    FOREIGN KEY(exp_id) REFERENCES newExp(exp_id)
     171) ENGINE=innodb DEFAULT CHARSET=latin1;
    177172
    178173CREATE TABLE rawImfile (
    … …  
    237232    ignored TINYINT DEFAULT 0,
    238233    hostname VARCHAR(64),
     234    quality SMALLINT NOT NULL DEFAULT 0,
    239235    fault SMALLINT NOT NULL,
    240236    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    … …  
    245241    KEY(fault),
    246242    KEY(raw_image_id),
     243    KEY(quality),
    247244    UNIQUE KEY(exp_id, tmp_class_id),
    248     FOREIGN KEY (exp_id, tmp_class_id)
    249         REFERENCES newImfile(exp_id, tmp_class_id))
    250 ENGINE=innodb DEFAULT CHARSET=latin1;
    251 
    252 CREATE TABLE guidePendingExp (guide_id BIGINT AUTO_INCREMENT, exp_id BIGINT, recipe VARCHAR(64), PRIMARY KEY(guide_id), KEY(guide_id), KEY(exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
     245    FOREIGN KEY(exp_id, tmp_class_id) REFERENCES newImfile(exp_id, tmp_class_id)
     246) ENGINE=innodb DEFAULT CHARSET=latin1;
    253247
    254248CREATE TABLE chipRun (
    … …  
    273267    KEY(end_stage),
    274268    INDEX(chip_id, exp_id),
    275     FOREIGN KEY (exp_id)
    276         REFERENCES rawExp(exp_id))
    277 ENGINE=innodb DEFAULT CHARSET=latin1;
     269    FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id)
     270) ENGINE=innodb DEFAULT CHARSET=latin1;
    278271
    279272CREATE TABLE chipImfile (
    … …  
    283276    PRIMARY KEY(chip_id, class_id),
    284277    KEY(chip_imfile_id),
    285     FOREIGN KEY (chip_id) REFERENCES  chipRun(chip_id))
    286 ENGINE=innodb DEFAULT CHARSET=latin1;
     278    FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id)
     279) ENGINE=innodb DEFAULT CHARSET=latin1;
    287280
    288281CREATE TABLE chipProcessedImfile (
    … …  
    343336    n_cr INT,
    344337    path_base VARCHAR(255),
     338    quality SMALLINT NOT NULL DEFAULT 0,
    345339    fault SMALLINT NOT NULL,
    346340    magicked BIGINT,
    … …  
    348342    KEY(data_state),
    349343    KEY(fault),
    350     FOREIGN KEY (chip_id, exp_id)
    351         REFERENCES  chipRun(chip_id, exp_id),
    352     FOREIGN KEY (exp_id, class_id)
    353         REFERENCES  rawImfile(exp_id, class_id))
    354 ENGINE=innodb DEFAULT CHARSET=latin1;
     344    KEY(quality),
     345    FOREIGN KEY(chip_id, exp_id) REFERENCES chipRun(chip_id, exp_id),
     346    FOREIGN KEY(exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)
     347) ENGINE=innodb DEFAULT CHARSET=latin1;
    355348
    356349CREATE TABLE chipMask (
    357350    label VARCHAR(64),
    358     PRIMARY KEY(label))
    359 ENGINE=innodb DEFAULT CHARSET=latin1;
     351    PRIMARY KEY(label)
     352) ENGINE=innodb DEFAULT CHARSET=latin1;
    360353
    361354CREATE TABLE camRun (
    … …  
    380373    KEY(end_stage),
    381374    INDEX(cam_id, chip_id),
    382     FOREIGN KEY (chip_id)
    383         REFERENCES chipRun(chip_id))
    384 ENGINE=innodb DEFAULT CHARSET=latin1;
     375    FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id)
     376) ENGINE=innodb DEFAULT CHARSET=latin1;
    385377
    386378CREATE TABLE camProcessedExp (
    … …  
    444436    n_astrom INT,
    445437    path_base VARCHAR(255),
     438    quality SMALLINT NOT NULL DEFAULT 0,
    446439    fault SMALLINT NOT NULL,
    447440    PRIMARY KEY(cam_id),
    448441    KEY(fault),
    449     FOREIGN KEY (cam_id)
    450         REFERENCES camRun(cam_id))
    451 ENGINE=innodb DEFAULT CHARSET=latin1;
     442    KEY(quality),
     443    FOREIGN KEY(cam_id) REFERENCES camRun(cam_id)
     444) ENGINE=innodb DEFAULT CHARSET=latin1;
    452445
    453446CREATE TABLE camMask (
    454447    label VARCHAR(64),
    455     PRIMARY KEY(label))
    456 ENGINE=innodb DEFAULT CHARSET=latin1;
     448    PRIMARY KEY(label)
     449) ENGINE=innodb DEFAULT CHARSET=latin1;
    457450
    458451CREATE TABLE fakeRun (
    … …  
    475468    KEY(end_stage),
    476469    INDEX(fake_id, cam_id),
    477     FOREIGN KEY (cam_id)
    478         REFERENCES camRun(cam_id))
    479 ENGINE=innodb DEFAULT CHARSET=latin1;
     470    FOREIGN KEY(cam_id) REFERENCES camRun(cam_id)
     471) ENGINE=innodb DEFAULT CHARSET=latin1;
    480472
    481473CREATE TABLE fakeProcessedImfile (
    … …  
    493485    PRIMARY KEY(fake_id, exp_id, class_id),
    494486    KEY(fault),
    495     FOREIGN KEY (fake_id)
    496         REFERENCES fakeRun(fake_id),
    497     FOREIGN KEY (exp_id, class_id)
    498         REFERENCES rawImfile(exp_id, class_id))
    499 ENGINE=innodb DEFAULT CHARSET=latin1;
     487    FOREIGN KEY(fake_id) REFERENCES fakeRun(fake_id),
     488    FOREIGN KEY(exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)
     489) ENGINE=innodb DEFAULT CHARSET=latin1;
    500490
    501491CREATE TABLE fakeMask (
    502492    label VARCHAR(64),
    503     PRIMARY KEY(label))
    504 ENGINE=innodb DEFAULT CHARSET=latin1;
     493    PRIMARY KEY(label)
     494) ENGINE=innodb DEFAULT CHARSET=latin1;
    505495
    506496CREATE TABLE detRun (
    … …  
    535525    ref_det_id BIGINT,
    536526    ref_iter INT,
    537     -- parent INT, :: dropping this
    538527    PRIMARY KEY(det_id),
    539528    KEY(det_id),
    … …  
    543532    KEY(state),
    544533    KEY(label),
    545     -- KEY(parent), :: dropping this
    546     INDEX(det_id, iteration))
    547 ENGINE=innodb DEFAULT CHARSET=latin1;
     534    INDEX(det_id, iteration)
     535) ENGINE=innodb DEFAULT CHARSET=latin1;
    548536
    549537CREATE TABLE detInputExp (
    550 det_id BIGINT,
    551 iteration INT,
    552 exp_id BIGINT,
    553 include TINYINT,
    554 PRIMARY KEY(det_id, iteration, exp_id),
    555 INDEX(det_id, exp_id),
    556 INDEX(det_id, iteration),
    557 FOREIGN KEY (det_id)
    558 REFERENCES  detRun(det_id),
    559 FOREIGN KEY (exp_id)
    560 REFERENCES  rawExp(exp_id))
    561 ENGINE=innodb DEFAULT CHARSET=latin1;
     538    det_id BIGINT,
     539    iteration INT,
     540    exp_id BIGINT,
     541    include TINYINT,
     542    PRIMARY KEY(det_id, iteration, exp_id),
     543    INDEX(det_id, exp_id),
     544    INDEX(det_id, iteration),
     545    FOREIGN KEY(det_id) REFERENCES detRun(det_id),
     546    FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id)
     547) ENGINE=innodb DEFAULT CHARSET=latin1;
    562548
    563549CREATE TABLE detProcessedImfile (
    … …  
    585571    INDEX(det_id, class_id),
    586572    INDEX(det_id, exp_id),
    587     FOREIGN KEY (det_id, exp_id)
    588         REFERENCES  detInputExp(det_id, exp_id),
    589     FOREIGN KEY (exp_id, class_id)
    590         REFERENCES  rawImfile(exp_id, class_id)
     573    FOREIGN KEY(det_id, exp_id) REFERENCES detInputExp(det_id, exp_id),
     574    FOREIGN KEY(exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)
    591575) ENGINE=innodb DEFAULT CHARSET=latin1;
    592576
    … …  
    611595    PRIMARY KEY(det_id, exp_id),
    612596    KEY(fault),
    613     FOREIGN KEY (det_id, exp_id)
    614         REFERENCES  detInputExp(det_id, exp_id),
    615     FOREIGN KEY (det_id, exp_id)
    616         REFERENCES  detProcessedImfile(det_id, exp_id)
     597    FOREIGN KEY(det_id, exp_id) REFERENCES detInputExp(det_id, exp_id),
     598    FOREIGN KEY(det_id, exp_id) REFERENCES detProcessedImfile(det_id, exp_id)
    617599) ENGINE=innodb DEFAULT CHARSET=latin1;
    618600
    … …  
    635617    PRIMARY KEY(det_id, iteration, class_id),
    636618    KEY(fault),
    637     FOREIGN KEY (det_id, iteration)
    638         REFERENCES  detInputExp(det_id, iteration),
    639     FOREIGN KEY (det_id, class_id)
    640         REFERENCES  detProcessedImfile(det_id, class_id)
     619    FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     620    FOREIGN KEY(det_id, class_id) REFERENCES detProcessedImfile(det_id, class_id)
    641621) ENGINE=innodb DEFAULT CHARSET=latin1;
    642622
    … …  
    650630    PRIMARY KEY(det_id, iteration, class_id),
    651631    KEY(fault),
    652     FOREIGN KEY (det_id, iteration)
    653     REFERENCES  detInputExp(det_id, iteration),
    654     FOREIGN KEY (det_id, iteration, class_id)
    655     REFERENCES  detStackedImfile(det_id, iteration, class_id)
     632    FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     633    FOREIGN KEY(det_id, iteration, class_id) REFERENCES  detStackedImfile(det_id, iteration, class_id)
    656634) ENGINE=innodb DEFAULT CHARSET=latin1;
    657635
    … …  
    675653    KEY(fault),
    676654    INDEX(det_id, iteration),
    677     FOREIGN KEY (det_id)
    678     REFERENCES  detInputExp(det_id),
    679     FOREIGN KEY (det_id, iteration, class_id)
    680     REFERENCES  detNormalizedStatImfile(det_id, iteration, class_id)
     655    FOREIGN KEY(det_id) REFERENCES detInputExp(det_id),
     656    FOREIGN KEY(det_id, iteration, class_id) REFERENCES detNormalizedStatImfile(det_id, iteration, class_id)
    681657) ENGINE=innodb DEFAULT CHARSET=latin1;
    682658
    … …  
    698674    PRIMARY KEY(det_id, iteration),
    699675    KEY(fault),
    700     FOREIGN KEY (det_id, iteration)
    701     REFERENCES  detInputExp(det_id, iteration),
    702     FOREIGN KEY (det_id, iteration)
    703     REFERENCES  detNormalizedImfile(det_id, iteration)
     676    FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     677    FOREIGN KEY(det_id, iteration) REFERENCES detNormalizedImfile(det_id, iteration)
    704678) ENGINE=innodb DEFAULT CHARSET=latin1;
    705679
    … …  
    736710    KEY(fault),
    737711    INDEX(det_id, iteration, exp_id),
    738     FOREIGN KEY (det_id, iteration, exp_id)
    739     REFERENCES  detInputExp(det_id, iteration, exp_id),
    740     FOREIGN KEY (det_id, exp_id, class_id)
    741     REFERENCES  detProcessedImfile(det_id, exp_id, class_id)
     712    FOREIGN KEY (det_id, iteration, exp_id) REFERENCES detInputExp(det_id, iteration, exp_id),
     713    FOREIGN KEY (det_id, exp_id, class_id) REFERENCES detProcessedImfile(det_id, exp_id, class_id)
    742714) ENGINE=innodb DEFAULT CHARSET=latin1;
    743715
    … …  
    771743       KEY(fault),
    772744       INDEX(det_id, iteration),
    773        FOREIGN KEY (det_id, iteration, exp_id)
    774        REFERENCES  detInputExp(det_id, iteration, exp_id),
    775        FOREIGN KEY (det_id, iteration, exp_id)
    776        REFERENCES  detResidImfile(det_id, iteration, exp_id)
     745       FOREIGN KEY(det_id, iteration, exp_id) REFERENCES detInputExp(det_id, iteration, exp_id),
     746       FOREIGN KEY(det_id, iteration, exp_id) REFERENCES detResidImfile(det_id, iteration, exp_id)
    777747) ENGINE=innodb DEFAULT CHARSET=latin1;
    778748
    … …  
    788758       PRIMARY KEY(det_id, iteration),
    789759       KEY(fault),
    790        FOREIGN KEY (det_id, iteration)
    791          REFERENCES  detInputExp(det_id, iteration),
    792        FOREIGN KEY (det_id, iteration)
    793          REFERENCES  detResidExp(det_id, iteration)
     760       FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     761       FOREIGN KEY(det_id, iteration) REFERENCES detResidExp(det_id, iteration)
    794762) ENGINE=innodb DEFAULT CHARSET=latin1;
    795763
    … …  
    812780       PRIMARY KEY(det_id, iteration, class_id),
    813781       KEY(fault),
    814        FOREIGN KEY (det_id, iteration)
    815          REFERENCES  detRun(det_id, iteration)
     782       FOREIGN KEY(det_id, iteration) REFERENCES detRun(det_id, iteration)
    816783) ENGINE=innodb DEFAULT CHARSET=latin1;
    817784
    … …  
    838805    KEY(end_stage),
    839806    INDEX(warp_id, fake_id),
    840     FOREIGN KEY (fake_id)
    841         REFERENCES fakeRun(fake_id)
     807    FOREIGN KEY(fake_id) REFERENCES fakeRun(fake_id)
    842808) ENGINE=innodb DEFAULT CHARSET=latin1;
    843809
    … …  
    850816    PRIMARY KEY(warp_id, skycell_id, tess_id, class_id),
    851817    KEY(fault),
    852     FOREIGN KEY (warp_id)
    853         REFERENCES warpRun(warp_id)
     818    FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id)
    854819) ENGINE=innodb DEFAULT CHARSET=latin1;
    855820
    … …  
    860825    PRIMARY KEY(warp_id, skycell_id),
    861826    KEY(warp_skyfile_id),
    862     FOREIGN KEY (warp_id) REFERENCES  warpRun(warp_id))
    863 ENGINE=innodb DEFAULT CHARSET=latin1;
     827    FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id)
     828) ENGINE=innodb DEFAULT CHARSET=latin1;
    864829
    865830
    … …  
    881846    ymin INT,
    882847    ymax INT,
    883     ignored TINYINT,
     848    quality SMALLINT NOT NULL DEFAULT 0,
    884849    fault SMALLINT,
    885850    magicked TINYINT,
    886851    PRIMARY KEY(warp_id, skycell_id, tess_id),
    887852    KEY(good_frac),
    888     KEY(ignored), KEY(fault),
    889     FOREIGN KEY (warp_id, skycell_id, tess_id)
    890         REFERENCES warpSkyCellMap(warp_id, skycell_id, tess_id)
     853    KEY(fault),
     854    KEY(quality),
     855    FOREIGN KEY(warp_id, skycell_id, tess_id) REFERENCES warpSkyCellMap(warp_id, skycell_id, tess_id)
    891856) ENGINE=innodb DEFAULT CHARSET=latin1;
    892857
    … …  
    918883        warp_id BIGINT,
    919884        PRIMARY KEY(stack_id, warp_id),
    920         FOREIGN KEY (stack_id)  REFERENCES stackRun(stack_id),
    921         FOREIGN KEY (warp_id)  REFERENCES warpSkyfile(warp_id)
     885        FOREIGN KEY(stack_id) REFERENCES stackRun(stack_id),
     886        FOREIGN KEY(warp_id) REFERENCES warpSkyfile(warp_id)
    922887) ENGINE=innodb DEFAULT CHARSET=latin1;
    923888
    … …  
    948913        hostname VARCHAR(64),
    949914        good_frac FLOAT,
     915        quality SMALLINT NOT NULL DEFAULT 0,
    950916        fault SMALLINT,
    951917        PRIMARY KEY(stack_id),
    … …  
    953919        KEY(good_frac),
    954920        KEY(fault),
    955         FOREIGN KEY (stack_id)  REFERENCES  stackRun(stack_id)
     921        KEY(quality),
     922        FOREIGN KEY(stack_id) REFERENCES stackRun(stack_id)
    956923) ENGINE=innodb DEFAULT CHARSET=latin1;
    957924
    … …  
    971938        KEY(state),
    972939        KEY(tess_id),
    973         FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id)
     940        FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id)
    974941) ENGINE=innodb DEFAULT CHARSET=latin1;
    975942
    … …  
    991958        KEY(skycell_id),
    992959        KEY(tess_id),
    993         FOREIGN KEY (diff_id)  REFERENCES diffRun(diff_id),
    994         FOREIGN KEY (warp1, skycell_id, tess_id)  REFERENCES warpSkyfile(warp_id, skycell_id, tess_id),
    995         FOREIGN KEY (warp2, skycell_id, tess_id)  REFERENCES warpSkyfile(warp_id, skycell_id, tess_id),
    996         FOREIGN KEY (stack1)  REFERENCES stackSumSkyfile(stack_id),
    997         FOREIGN KEY (stack2)  REFERENCES stackSumSkyfile(stack_id)
     960        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id),
     961        FOREIGN KEY(warp1, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id),
     962        FOREIGN KEY(warp2, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id),
     963        FOREIGN KEY(stack1) REFERENCES stackSumSkyfile(stack_id),
     964        FOREIGN KEY(stack2) REFERENCES stackSumSkyfile(stack_id)
    998965) ENGINE=innodb DEFAULT CHARSET=latin1;
    999966
    … …  
    1022989        hostname VARCHAR(64),
    1023990        good_frac FLOAT,
     991        quality SMALLINT NOT NULL DEFAULT 0,
    1024992        fault SMALLINT,
    1025993        magicked TINYINT,
    … …  
    1027995        KEY(good_frac),
    1028996        KEY(fault),
    1029         FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)
     997        KEY(quality),
     998        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id)
    1030999) ENGINE=innodb DEFAULT CHARSET=latin1;
    10311000
    … …  
    10471016        KEY(label),
    10481017        KEY(fault),
    1049         FOREIGN KEY (exp_id)  REFERENCES rawExp(exp_id),
    1050         FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
     1018        FOREIGN KEY(exp_id)  REFERENCES rawExp(exp_id),
     1019        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id)
    10511020) ENGINE=innodb DEFAULT CHARSET=latin1;
    10521021
    … …  
    10561025        node VARCHAR(64),
    10571026        PRIMARY KEY(magic_id, diff_id, node),
    1058         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id),
    1059         FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
     1027        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id),
     1028        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id)
    10601029) ENGINE=innodb DEFAULT CHARSET=latin1;
    10611030
    … …  
    10681037        KEY(dep),
    10691038        INDEX(magic_id, node),
    1070         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id)
     1039        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id)
    10711040) ENGINE=innodb DEFAULT CHARSET=latin1;
    10721041
    … …  
    10771046        fault SMALLINT,
    10781047        PRIMARY KEY(magic_id, node),
    1079         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id),
    1080         FOREIGN KEY (magic_id, node)  REFERENCES magicTree(magic_id, node),
     1048        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id),
     1049        FOREIGN KEY(magic_id, node) REFERENCES magicTree(magic_id, node),
    10811050        KEY(fault)
    10821051) ENGINE=innodb DEFAULT CHARSET=latin1;
    … …  
    10881057        fault SMALLINT,
    10891058        PRIMARY KEY(magic_id),
    1090         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id),
     1059        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id),
    10911060        KEY(fault)
    10921061) ENGINE=innodb DEFAULT CHARSET=latin1;
    … …  
    11091078        KEY(magic_id),
    11101079        KEY(label),
    1111         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id)
     1080        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id)
    11121081) ENGINE=innodb DEFAULT CHARSET=latin1;
    11131082
    … …  
    11201089    PRIMARY KEY(magic_ds_id, component),
    11211090    KEY(fault),
    1122     FOREIGN KEY (magic_ds_id) REFERENCES magicDSRun(magic_ds_id)
     1091    FOREIGN KEY(magic_ds_id) REFERENCES magicDSRun(magic_ds_id)
    11231092) ENGINE=innodb DEFAULT CHARSET=latin1;
    11241093
    … …  
    11391108        KEY(cal_id),
    11401109        KEY(last_step),
    1141         FOREIGN KEY (cal_id)  REFERENCES calDB(cal_id)
     1110        FOREIGN KEY(cal_id) REFERENCES calDB(cal_id)
    11421111) ENGINE=innodb DEFAULT CHARSET=latin1;
    11431112
    … …  
    11671136        include TINYINT,
    11681137        PRIMARY KEY(corr_id, chip_id),
    1169         FOREIGN KEY (corr_id)  REFERENCES flatcorrRun(corr_id),
    1170         FOREIGN KEY (chip_id)  REFERENCES chipRun(chip_id)
     1138        FOREIGN KEY(corr_id) REFERENCES flatcorrRun(corr_id),
     1139        FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id)
    11711140) ENGINE=innodb DEFAULT CHARSET=latin1;
    11721141
    … …  
    11771146        include TINYINT,
    11781147        PRIMARY KEY(corr_id, cam_id),
    1179         FOREIGN KEY (corr_id)  REFERENCES flatcorrRun(corr_id),
    1180         FOREIGN KEY (chip_id)  REFERENCES chipRun(chip_id),
    1181         FOREIGN KEY (cam_id)  REFERENCES camRun(cam_id)
     1148        FOREIGN KEY(corr_id) REFERENCES flatcorrRun(corr_id),
     1149        FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id),
     1150        FOREIGN KEY(cam_id) REFERENCES camRun(cam_id)
    11821151) ENGINE=innodb DEFAULT CHARSET=latin1;
    11831152
    … …  
    12311200        PRIMARY KEY(job_id, req_id),
    12321201        KEY(job_id),
    1233         FOREIGN KEY (req_id)  REFERENCES pstampRequest(req_id)
     1202        FOREIGN KEY(req_id) REFERENCES pstampRequest(req_id)
    12341203) ENGINE=innodb DEFAULT CHARSET=latin1;
    12351204
    12361205CREATE TABLE distTarget (
    1237     target_id   BIGINT AUTO_INCREMENT, 
     1206    target_id   BIGINT AUTO_INCREMENT,
    12381207    obs_mode    VARCHAR(64),
    12391208    stage       VARCHAR(64),
    … …  
    12631232
    12641233CREATE TABLE distComponent (
    1265     dist_id     BIGINT, 
     1234    dist_id     BIGINT,
    12661235    component   VARCHAR(64),
    12671236    bytes       INT,
    … …  
    13351304
    13361305
    1337 -- This comment line is here to avoid empty query error. 
     1306-- This comment line is here to avoid empty query error.
    13381307-- Another way to avoid that problem is to omit the semicolon above but I think that is untidy.

  • trunk/ippTools/share/pxadmin_drop_tables.sql

    r23390 r23688  
    11SET FOREIGN_KEY_CHECKS=0;
     2
    23DROP TABLE IF EXISTS pzDataStore;
    34DROP TABLE IF EXISTS summitExp;
    … …  
    910DROP TABLE IF EXISTS rawExp;
    1011DROP TABLE IF EXISTS rawImfile;
    11 DROP TABLE IF EXISTS guidePendingExp;
    1212DROP TABLE IF EXISTS chipRun;
    1313DROP TABLE IF EXISTS chipProcessedImfile;

  • trunk/ippTools/share/stacktool_definebyquery_insert.sql

    r19258 r23688  
    2121    AND rawExp.filter = '%s'
    2222    AND warpSkyfile.fault = 0
    23     AND warpSkyfile.ignored = 0
     23    AND warpSkyfile.quality = 0

  • trunk/ippTools/share/stacktool_definebyquery_insert_random_part1.sql

    r19258 r23688  
    2828        AND rawExp.filter = '%s' -- the result of the query is grouped by filter and inserted for one at a time
    2929        AND warpSkyfile.fault = 0
    30         AND warpSkyfile.ignored = 0
     30        AND warpSkyfile.quality = 0
    3131-- Put additional constraints here
    3232-- stacktool_definebyquery_insert_random_part2.sql goes here

  • trunk/ippTools/share/stacktool_definebyquery_part1.sql

    r19258 r23688  
    1717        COUNT(warpSkyfile.skycell_id) AS num_warp -- number of warps that can be stacked
    1818    FROM warpRun
    19         JOIN warpSkyfile USING(warp_id)
    20         JOIN fakeRun USING(fake_id)
    21         JOIN camRun USING(cam_id)
    22         JOIN chipRun USING(chip_id)
    23         JOIN rawExp USING(exp_id)
     19    JOIN warpSkyfile USING(warp_id)
     20    JOIN fakeRun USING(fake_id)
     21    JOIN camRun USING(cam_id)
     22    JOIN chipRun USING(chip_id)
     23    JOIN rawExp USING(exp_id)
    2424    WHERE
    2525        warpRun.state = 'full'
    26     AND warpSkyfile.ignored = 0
    27     AND warpSkyfile.fault = 0
     26        AND warpSkyfile.fault = 0
     27        AND warpSkyfile.quality = 0
    2828    -- Here should follow the SQL in stacktool_definebyquery_part2.sql,
    2929    -- after any additional WHERE conditions have been added

  • trunk/ippTools/share/stacktool_definebyquery_test.sql

    r19258 r23688  
    3131    WHERE
    3232        warpRun.state = 'full'
    33     AND warpSkyfile.ignored = 0
     33    AND warpSkyfile.quality = 0
    3434    AND warpSkyfile.fault = 0
    3535    -- Any additional selection on warps/exposures goes here

  • trunk/ippTools/share/stacktool_find_complete_warps.sql

    r19258 r23688  
    66    COUNT(stackRun.stack_id) AS num_extant
    77FROM warpRun
    8 JOIN warpSkyfile 
     8JOIN warpSkyfile
    99    USING(warp_id)
    1010JOIN fakeRun
    … …  
    2323WHERE
    2424    warpRun.state = 'full'
    25     AND warpSkyfile.ignored = 0
     25    AND warpSkyfile.fault = 0
     26    AND warpSkyfile.quality = 0
    2627GROUP BY
    2728    warpSkyfile.skycell_id, stackRun.stack_id

  • trunk/ippTools/share/stacktool_tosum.sql

    r19678 r23688  
    1212    USING(stack_id)
    1313WHERE
    14     ((stackRun.state = 'new'
    15         AND stackSumSkyfile.stack_id IS NULL)
    16     OR
    17     (stackRun.state = 'update'
    18         AND stackSumSkyfile.fault = 0))
     14    ((stackRun.state = 'new' AND stackSumSkyfile.stack_id IS NULL)
     15    OR (stackRun.state = 'update' AND stackSumSkyfile.fault = 0 AND stackSumSkyfile.quality = 0))

  • trunk/ippTools/share/warptool_finished_run_select.sql

    r23459 r23688  
    1919   HAVING
    2020       COUNT(warpSkyCellMap.warp_id) = COUNT(warpSkyfile.warp_id)
    21        AND SUM(warpSkyfile.fault) = 0
     21       AND SUM(warpSkyfile.fault > 0) = 0
    2222 ) as Foo

  • trunk/ippTools/share/warptool_imfile.sql

    r20608 r23688  
    2626    AND camRun.state = 'full'
    2727    AND chipRun.state = 'full'
     28    AND chipProcessedImfile.quality = 0
     29    and camProcessedExp.quality = 0
    2830

  • trunk/ippTools/share/warptool_revertwarped_delete.sql

    r20722 r23688  
    77    AND chipRun.exp_id = rawExp.exp_id
    88    AND warpSkyfile.fault != 0
     9
Note: See TracChangeset for help on using the changeset viewer.