IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Ignore:
Timestamp:
May 26, 2009, 1:59:32 PM (17 years ago)
Author:
beaumont
Message:

merged with trunk

Location:
branches/cnb_branches/cnb_branch_20090301
Files:
10 deleted
54 edited
58 copied

Legend:

Unmodified
Added
Removed

  • branches/cnb_branches/cnb_branch_20090301

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share

    • Property svn:ignore

      •  

        old new  
        11Makefile.in
        22Makefile
         3pxadmin_create_mirror_tables.sql

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/Makefile.am

    r23594 r24244  
     1pxadmin_create_mirror_tables.sql: pxadmin_create_tables.sql
     2        -$(RM) pxadmin_create_mirror_tables.sql
     3        $(SED) -n -e '/^  *$$/ d' -e 's/--.*//' -e '/./ H' -e '/;/ !b' -e 'x ; s/\n//g ; /^CREATE TABLE receive/ { b print }' -e 's/AUTO_INCREMENT//g ; s/,\{0,1\} *FOREIGN *KEY *([a-zA-Z][a-zA-Z0-9\-\_, ]*) *REFERENCES *[a-zA-Z]\{1,\}([a-zA-Z][a-zA-Z0-9\-\_, ]*)//g' -e ': print' -e 's/( */(/g ; s/ *)/)/g ; s/  */ /g ; p ; s/.*//g ; x'  pxadmin_create_tables.sql > pxadmin_create_mirror_tables.sql
     4        echo '-- This comment line is here to avoid empty query error.' >> pxadmin_create_mirror_tables.sql
     5
    16dist_pkgdata_DATA = \
    27     camtool_donecleanup.sql \
    … …  
    1015     camtool_reset_faulted_runs.sql \
    1116     camtool_revertprocessedexp.sql \
     17     camtool_export_run.sql \
     18     camtool_export_processed_exp.sql \
    1219     chiptool_change_exp_state.sql \
    1320     chiptool_change_imfile_data_state.sql \
    … …  
    2128     chiptool_revertprocessedimfile.sql \
    2229     chiptool_run.sql \
     30     chiptool_export_imfile.sql \
     31     chiptool_export_processed_imfile.sql \
    2332     chiptool_export_run.sql \
    24      chiptool_export_imfiles.sql \
    2533     chiptool_unmasked.sql \
    2634     detselect_search.sql \
    … …  
    7381     dettool_tostacked.sql \
    7482     difftool_completed_runs.sql \
    75      difftool_definebyquery_part1.sql \
    76      difftool_definebyquery_part2.sql \
    77      difftool_definebyquery_temp_create.sql \
     83     difftool_definewarpstack_part1.sql \
     84     difftool_definewarpstack_part2.sql \
     85     difftool_definewarpstack_temp_create.sql \
     86     difftool_definewarpwarp_temp_create.sql \
     87     difftool_definewarpwarp_temp_insert.sql \
    7888     difftool_definewarpwarp_select.sql \
    7989     difftool_definewarpwarp_insert.sql \
    8090     difftool_donecleanup.sql \
     91     difftool_export_input_skyfile.sql \
     92     difftool_export_run.sql \
     93     difftool_export_skyfile.sql \
    8194     difftool_inputskyfile.sql \
    8295     difftool_pendingcleanuprun.sql \
    … …  
    8699     difftool_skyfile.sql \
    87100     difftool_todiffskyfile.sql \
     101     disttool_definebyquery_camera.sql \
     102     disttool_definebyquery_chip.sql \
     103     disttool_definebyquery_diff.sql \
     104     disttool_definebyquery_fake.sql \
     105     disttool_definebyquery_raw.sql \
     106     disttool_definebyquery_stack.sql \
     107     disttool_definebyquery_warp.sql \
    88108     disttool_pendingcomponent.sql \
     109     disttool_pendingfileset.sql \
     110     disttool_pendingdest.sql \
    89111     disttool_processedcomponent.sql \
     112     disttool_queuercrun.sql \
     113     disttool_revertrcrun.sql \
    90114     disttool_revertrun_update.sql \
    91115     disttool_revertrun_delete.sql \
     116     disttool_revertfileset.sql \
    92117     disttool_toadvance.sql \
     118     disttool_updatercrun.sql \
    93119     faketool_change_exp_state.sql \
    94120     faketool_change_imfile_data_state.sql \
    95121     faketool_completely_processed_exp.sql \
    96122     faketool_donecleanup.sql \
     123     faketool_export_processed_imfile.sql \
     124     faketool_export_run.sql \
    97125     faketool_find_camrun.sql \
    98126     faketool_find_pendingexp.sql \
    … …  
    111139     magictool_addmask.sql \
    112140     magictool_create_tmp_warpcomplete.sql \
    113      magictool_definebyquery.sql \
    114141     magictool_definebyquery_insert.sql \
    115142     magictool_definebyquery_select.sql \
    116      magictool_definebyquery_select_test.sql \
    117      magictool_definebyquery_temp_create.sql \
    118      magictool_definebyquery_temp_insert.sql \
    119143     magictool_inputs.sql \
    120144     magictool_inputskyfile.sql \
    … …  
    129153     magictool_chipprocessedimfile.sql \
    130154     magictool_rawimfile.sql \
     155     magictool_revertnode.sql \
    131156     magicdstool_todestreak.sql \
    132157     magicdstool_completed_runs.sql \
    … …  
    140165     pstamptool_project.sql \
    141166     pxadmin_create_tables.sql \
     167     pxadmin_create_mirror_tables.sql \
    142168     pxadmin_drop_tables.sql \
    143169     pztool_find_completed_exp.sql \
    … …  
    146172     pztool_revert_fileset_faults.sql \
    147173     pztool_revertcopied.sql \
     174     receivetool_list.sql \
     175     receivetool_addfileset.sql \
     176     receivetool_pendingfileset.sql \
     177     receivetool_pendingfile.sql \
     178     receivetool_revert.sql \
     179     receivetool_toadvance.sql \
    148180     regtool_create_dup_table.sql \
     181     regtool_export_exp.sql \
     182     regtool_export_imfile.sql \
    149183     regtool_pendingexp.sql \
    150184     regtool_pendingimfile.sql \
    … …  
    162196     stacktool_definebyquery_test.sql \
    163197     stacktool_donecleanup.sql \
     198     stacktool_export_input_skyfile.sql \
     199     stacktool_export_run.sql \
     200     stacktool_export_sum_skyfile.sql \
    164201     stacktool_find_complete_warps.sql \
    165202     stacktool_inputskyfile.sql \
    … …  
    175212     warptool_donecleanup.sql \
    176213     warptool_exp.sql \
     214     warptool_export_imfile.sql \
     215     warptool_export_run.sql \
     216     warptool_export_skyfile.sql \
     217     warptool_export_skycell_map.sql \
    177218     warptool_finished_run_select.sql \
    178219     warptool_finish_run.sql \

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/camtool_find_chip_id.sql

    r20041 r24244  
    11SELECT
    2     *
    3 FROM
    4     (SELECT DISTINCT
    5         chipRun.*,
    6         rawExp.camera,
    7         rawExp.telescope,
    8         rawExp.dateobs,
    9         rawExp.exp_tag,
    10         rawExp.exp_type,
    11         rawExp.filelevel,
    12         rawExp.filter,
    13         rawExp.airmass,
    14         rawExp.ra,
    15         rawExp.decl,
    16         rawExp.exp_time,
    17         rawExp.sat_pixel_frac,
    18         rawExp.bg,
    19         rawExp.bg_stdev,
    20         rawExp.bg_mean_stdev,
    21         rawExp.alt,
    22         rawExp.az,
    23         rawExp.ccd_temp,
    24         rawExp.posang,
    25         rawExp.object,
    26         rawExp.sun_angle
    27     FROM chipRun
    28     JOIN rawExp
    29         using(exp_id)
    30     WHERE
    31         chipRun.state = 'full') as Foo
     2    chipRun.*,
     3    rawExp.camera,
     4    rawExp.telescope,
     5    rawExp.dateobs,
     6    rawExp.exp_tag,
     7    rawExp.exp_type,
     8    rawExp.filelevel,
     9    rawExp.filter,
     10    rawExp.airmass,
     11    rawExp.ra,
     12    rawExp.decl,
     13    rawExp.exp_time,
     14    rawExp.sat_pixel_frac,
     15    rawExp.bg,
     16    rawExp.bg_stdev,
     17    rawExp.bg_mean_stdev,
     18    rawExp.alt,
     19    rawExp.az,
     20    rawExp.ccd_temp,
     21    rawExp.posang,
     22    rawExp.object,
     23    rawExp.sun_angle
     24FROM chipRun
     25JOIN rawExp
     26    using(exp_id)
     27WHERE
     28    chipRun.state = 'full'

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/camtool_find_pendingexp.sql

    r19558 r24244  
    22-- does a little more work then is necessary for -addprocessed but it seems
    33-- "cleaner" to use the same query for both cases
    4 SELECT * FROM
    5     (SELECT
    6         camRun.*,
    7         rawExp.exp_tag,
    8         rawExp.exp_id,
    9         rawExp.exp_name,
    10         rawExp.camera,
    11         rawExp.telescope,
    12         rawExp.filelevel
    13     FROM camRun
    14     JOIN chipRun
    15         USING(chip_id)
    16 --  JOIN chipProcessedImfile
    17 --      USING(chip_id)
    18     JOIN rawExp
    19         USING(exp_id)
    20 --      ON chipProcessedImfile.exp_id = rawExp.exp_id
    21     LEFT JOIN camProcessedExp
    22         USING(cam_id)
    23     LEFT JOIN camMask
    24         ON camRun.label = camMask.label
    25     WHERE
    26         chipRun.state = 'full'
    27         AND ((camRun.state = 'new' AND camProcessedExp.cam_id IS NULL)
    28             OR camRun.state = 'update')
    29         AND camMask.label IS NULL
    30     ) as Foo
     4SELECT
     5    camRun.*,
     6    rawExp.exp_tag,
     7    rawExp.exp_id,
     8    rawExp.exp_name,
     9    rawExp.camera,
     10    rawExp.telescope,
     11    rawExp.filelevel
     12FROM camRun
     13JOIN chipRun
     14    USING(chip_id)
     15JOIN rawExp
     16    USING(exp_id)
     17LEFT JOIN camProcessedExp
     18    USING(cam_id)
     19LEFT JOIN camMask
     20    ON camRun.label = camMask.label
     21WHERE
     22    chipRun.state = 'full'
     23    AND ((camRun.state = 'new' AND camProcessedExp.cam_id IS NULL) OR camRun.state = 'update')
     24    AND camMask.label IS NULL

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/camtool_find_pendingimfile.sql

    r19972 r24244  
    1 SELECT DISTINCT * FROM
    2      -- the subselect is so where criteria can be specified without knowing
    3      -- which table the field came from
    4     (SELECT
    5         camRun.cam_id,
    6         chipProcessedImfile.*,
    7         rawExp.exp_name,
    8         rawExp.camera,
    9         rawExp.telescope,
    10         rawExp.filelevel
    11     FROM camRun
    12     JOIN chipRun
    13 --      ON camRun.chip_id = chipRun.chip_id
    14         USING (chip_id)
    15     JOIN chipProcessedImfile
    16 --      ON camRun.chip_id = chipProcessedImfile.chip_id
    17         USING (chip_id)
    18     JOIN rawExp
    19       ON chipProcessedImfile.exp_id = rawExp.exp_id
    20     LEFT JOIN camProcessedExp
    21         USING(cam_id)
    22     LEFT JOIN camMask
    23         ON camRun.label = camMask.label
    24     WHERE
    25         camMask.label IS NULL) as foo
     1SELECT
     2    camRun.cam_id,
     3    chipProcessedImfile.*,
     4    rawExp.exp_name,
     5    rawExp.camera,
     6    rawExp.telescope,
     7    rawExp.filelevel
     8FROM camRun
     9JOIN chipRun
     10    USING (chip_id)
     11JOIN chipProcessedImfile
     12    USING (chip_id)
     13JOIN rawExp
     14  ON chipProcessedImfile.exp_id = rawExp.exp_id
     15LEFT JOIN camProcessedExp
     16    USING(cam_id)
     17LEFT JOIN camMask
     18    ON camRun.label = camMask.label
     19WHERE
     20    camMask.label IS NULL

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/camtool_find_processedexp.sql

    r23594 r24244  
    1 SELECT DISTINCT
     1SELECT
    22    camProcessedExp.*,
     3    camRun.workdir,
    34    chipRun.chip_id,
    45    rawExp.exp_tag,
    … …  
    1213JOIN chipRun
    1314    USING(chip_id)
    14 JOIN chipProcessedImfile
    15     USING(chip_id)
    1615JOIN rawExp
    17     ON chipProcessedImfile.exp_id = rawExp.exp_id
     16    ON chipRun.exp_id = rawExp.exp_id

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/chiptool_completely_processed_exp.sql

    r23594 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/chiptool_find_rawexp.sql

    r19573 r24244  
    22-- processeing
    33SELECT
    4     *
     4    rawExp.*,
     5    newExp.label
    56FROM rawExp
     7JOIN newExp using (exp_id)
    68WHERE
    79    rawExp.fault = 0

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/chiptool_processedimfile.sql

    r21319 r24244  
    99    chipProcessedImfile.path_base,
    1010    chipRun.state,
     11    chipRun.workdir,
    1112    rawExp.exp_id,
    1213    rawExp.exp_tag,

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/difftool_definewarpwarp_insert.sql

    r23594 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/difftool_definewarpwarp_select.sql

    r23594 r24244  
    11SELECT
    22    inputWarpRun.warp_id AS input_warp_id,
     3    inputWarpRun.tess_id AS tess_id,
    34    inputRawExp.exp_id AS input_exp_id,
    4     inputWarpRun.tess_id AS tess_id,
     5    -- The following trick pulls out the warp_id that has the smallest distance
    56    SUBSTRING_INDEX(GROUP_CONCAT(templateWarpRun.warp_id ORDER BY ABS(ASIN(SQRT(POW(SIN(0.5*(inputRawExp.decl - templateRawExp.decl)),2) + COS(inputRawExp.decl) * COS(templateRawExp.decl) * POW(SIN(0.5*(inputRawExp.ra - templateRawExp.ra)),2))))), ',', 1) AS template_warp_id
    67FROM warpRun AS inputWarpRun
    … …  
    910JOIN chipRun AS inputChipRun USING(chip_id)
    1011JOIN rawExp AS inputRawExp USING(exp_id)
    11 -- To find exposures that haven't been diffed:%s LEFT JOIN diffRun ON diffRun.exp_id = inputRawExp.exp_id
     12-- To find exposures that haven't been diffed, insert newline here:%s LEFT JOIN diffs USING(exp_id)
    1213JOIN warpRun AS templateWarpRun
    1314    ON templateWarpRun.warp_id != inputWarpRun.warp_id -- Don't use self as template!

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/difftool_pendingcleanuprun.sql

    r19092 r24244  
    99    USING(diff_id)
    1010JOIN warpSkyfile
    11     ON  diffInputSkyfile.warp_id    = warpSkyfile.warp_id
     11    ON  diffInputSkyfile.warp1    = warpSkyfile.warp_id
    1212    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
    1313    AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
    … …  
    2323    USING(exp_id)
    2424WHERE
    25     diffRun.state = 'goto_cleaned'
     25    (diffRun.state = 'goto_cleaned' OR diffRun.state = 'goto_scrubbed' OR diffRun.state = 'goto_purged')

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/difftool_pendingcleanupskyfile.sql

    r19092 r24244  
    99    USING(diff_id)
    1010WHERE
    11     diffRun.state = 'goto_cleaned'
     11   ((diffRun.state = 'goto_cleaned'  AND diffSkyfile.data_state = 'full')
     12    OR
     13    (diffRun.state = 'goto_scrubbed' AND diffSkyfile.data_state != 'scrubbed')
     14    OR
     15    (diffRun.state = 'goto_purged'   AND diffSkyfile.data_state != 'purged'))

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/difftool_skyfile.sql

    r21308 r24244  
    44    diffRun.tess_id,
    55    diffRun.state,
     6    diffRun.workdir,
    67    warp1,
    78    stack1,
    89    warp2,
    9     stack2,
    10     exp_id
     10    stack2
    1111FROM diffRun
    12 JOIN diffSkyfile
    13     USING(diff_id)
    14 JOIN diffInputSkyfile
    15     USING (diff_id, skycell_id)
     12JOIN diffSkyfile USING(diff_id)
     13JOIN diffInputSkyfile USING(diff_id, skycell_id)
    1614JOIN warpRun
    1715    ON warpRun.warp_id = diffInputSkyfile.warp1
    18 JOIN rawExp
    19     USING(exp_id)
     16JOIN fakeRun USING(fake_id)
     17JOIN camRun USING(cam_id)
     18JOIN chipRun USING(chip_id)
     19JOIN rawExp USING(exp_id)

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/difftool_todiffskyfile.sql

    r21308 r24244  
    99    diffRun.tess_id,
    1010    diffRun.label,
    11     diffRun.state
     11    diffRun.state,
     12    diffRun.bothways
    1213FROM diffRun
    1314JOIN diffInputSkyfile USING(diff_id)
    … …  
    6364    AND diffSkyfile.diff_id IS NULL)
    6465    OR (diffRun.state = 'update'
    65     AND diffSkyfile.fault = 0)
     66    AND diffSkyfile.fault = 0
     67    AND diffSkyfile.quality = 0)
    6668    )
    6769-- Ensure input warps are available
    … …  
    6971    OR (warpRun.state = 'full'
    7072    AND warpSkyfile.fault = 0
    71     AND warpSkyfile.ignored = 0))
     73    AND warpSkyfile.quality = 0))
    7274-- Ensure input stacks are available
    7375    AND (diffInputSkyfile.stack1 IS NULL
    7476    OR (stackRun.state = 'full'
    75     AND stackSumSkyfile.fault = 0))
     77    AND stackSumSkyfile.fault = 0
     78    AND stackSumSkyfile.quality = 0))
    7679-- Ensure template warps are available
    7780    AND (diffInputSkyfile.warp2 IS NULL
    7881    OR (warpTemplateRun.state = 'full'
    7982    AND warpTemplateSkyfile.fault = 0
    80     AND warpTemplateSkyfile.ignored = 0))
     83    AND warpTemplateSkyfile.quality = 0))
    8184-- Ensure template stacks are available
    8285    AND (diffInputSkyfile.stack2 IS NULL
    8386    OR (stackTemplateRun.state = 'full'
    84     AND stackTemplateSkyfile.fault = 0))
     87    AND stackTemplateSkyfile.fault = 0
     88    AND stackTemplateSkyfile.quality = 0))
    8589
    8690

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/disttool_pendingcomponent.sql

    r23594 r24244  
    11SELECT * FROM (
     2    -- raw stage
    23SELECT
    34    distRun.dist_id,
    … …  
    89    clean,
    910    rawExp.camera,
    10     outroot,
     11    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
    1112    rawImfile.uri as path_base,         -- change this once rawImfile has path_base
    1213    chipProcessedImfile.path_base as chip_path_base,
    1314    NULL as state,
    1415    NULL as data_state,
     16    0 as quality,
     17    distRun.no_magic,
    1518    rawImfile.magicked
    1619FROM distRun
    1720JOIN rawExp ON exp_id = stage_id
    18 JOIN (                      -- find the last magicked chip run
     21JOIN (                      -- find the last satisfactory chip run
     22                            -- note this requires that a chip run have been completed even when
     23                            -- magic is not required for the run
    1924    SELECT
    2025        exp_id,
    2126        MAX(chip_id) AS chip_id
    2227    FROM chipRun
     28    JOIN distRun ON stage_id = exp_id AND stage = 'raw'
    2329    WHERE
    2430        chipRun.state = 'full'
    2531        AND chipRun.exp_id = exp_id
    26         --   AND chipRun.magicked
    27         -- magicked hook 1 %s
     32        AND (chipRun.magicked OR distRun.no_magic)
    2833        GROUP BY exp_id
    2934    ) AS bestChipRun
    … …  
    3742WHERE
    3843    distRun.state = 'new'
     44    AND distRun.clean = 0
    3945    AND distRun.stage = 'raw'
    4046    AND distComponent.dist_id IS NULL
    41     -- if magicked add AND rawImfile.magicked here
     47    AND (rawExp.magicked OR distRun.no_magic)
    4248    -- where hook 1 %s
     49UNION
     50    -- raw stage clean (dbinfo only)
     51SELECT
     52    distRun.dist_id,
     53    distRun.label,
     54    stage,
     55    stage_id,
     56    'exposure' AS component,
     57    clean,
     58    rawExp.camera,
     59    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
     60    NULL,
     61    NULL,
     62    NULL as state,
     63    NULL as data_state,
     64    0 as quality,
     65    distRun.no_magic,
     66    rawExp.magicked
     67FROM distRun
     68JOIN rawExp ON exp_id = stage_id
     69LEFT JOIN distComponent
     70    ON distRun.dist_id = distComponent.dist_id
     71WHERE
     72    distRun.state = 'new'
     73    AND distRun.stage = 'raw'
     74    AND distRun.clean
     75    AND distComponent.dist_id IS NULL
     76    -- where hook 2 %s
    4377
    4478-- chip stage
    … …  
    5286    clean,
    5387    rawExp.camera,
    54     outroot,
     88    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
    5589    chipProcessedImfile.path_base,
    5690    chipProcessedImfile.path_base as chip_path_base,
    5791    chipRun.state,
    5892    chipProcessedImfile.data_state,
     93    chipProcessedImfile.quality,
     94    distRun.no_magic,
    5995    chipProcessedImfile.magicked
    6096FROM distRun
    … …  
    69105    AND distRun.stage = 'chip'
    70106    AND distComponent.dist_id IS NULL
    71     -- where hook 2 %s
    72 UNION
    73 SELECT
    74     distRun.dist_id,
    75     distRun.label,
    76     stage,
    77     stage_id,
    78     chipProcessedImfile.class_id AS component,
    79     clean,
    80     rawExp.camera,
    81     outroot,
     107    AND (distRun.clean OR chipRun.magicked OR distRun.no_magic)
     108    AND (chipRun.state = 'full' OR (distRun.clean AND chipRun.state = 'cleaned'))
     109    -- where hook 3 %s
     110UNION
     111SELECT
     112    distRun.dist_id,
     113    distRun.label,
     114    stage,
     115    stage_id,
     116    'exposure' AS component,
     117--    chipProcessedImfile.class_id AS component,
     118    clean,
     119    rawExp.camera,
     120    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
    82121    camProcessedExp.path_base,
    83     chipProcessedImfile.path_base as chip_path_base,
     122    NULL as chip_path_base,
    84123    camRun.state,
    85124    NULL,
    86     0
     125    camProcessedExp.quality,
     126    distRun.no_magic,
     127    chipRun.magicked
    87128FROM distRun
    88129JOIN camRun ON camRun.cam_id = distRun.stage_id
    89130JOIN camProcessedExp USING(cam_id)
    90131JOIN chipRun USING(chip_id)
    91 JOIN chipProcessedImfile USING(exp_id, chip_id)
    92 JOIN rawExp using(exp_id)
    93 LEFT JOIN distComponent
    94     ON distRun.dist_id = distComponent.dist_id
    95     AND chipProcessedImfile.class_id = distComponent.component
     132-- JOIN chipProcessedImfile USING(exp_id, chip_id)
     133JOIN rawExp using(exp_id)
     134LEFT JOIN distComponent
     135    ON distRun.dist_id = distComponent.dist_id
     136--    AND chipProcessedImfile.class_id = distComponent.component
    96137WHERE
    97138    distRun.state = 'new'
    98139    AND distRun.stage = 'camera'
    99140    AND distComponent.dist_id IS NULL
    100     -- where hook 3 %s
     141    AND (distRun.clean OR chipRun.magicked OR distRun.no_magic)
     142    AND (camRun.state = 'full' OR (distRun.clean AND camRun.state = 'cleaned'))
     143    -- where hook 4 %s
    101144UNION
    102145SELECT
    … …  
    108151    clean,
    109152    rawExp.camera,
    110     outroot,
     153    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
    111154    fakeProcessedImfile.path_base,
    112155    NULL,
    113156    fakeRun.state,
    114157    NULL,
     158    0 as quality,
     159    distRun.no_magic,
    115160    0
    116161FROM distRun
    … …  
    127172    AND distRun.stage = 'fake'
    128173    AND distComponent.dist_id IS NULL
    129     -- where hook 4 %s
     174    -- where hook 5 %s
    130175UNION
    131176SELECT
    … …  
    137182    clean,
    138183    rawExp.camera,
    139     outroot,
     184    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
    140185    warpSkyfile.path_base,
    141186    NULL as chip_path_base,
    142187    warpRun.state,
    143188    warpSkyfile.data_state,
     189    warpSkyfile.quality,
     190    distRun.no_magic,
    144191    warpSkyfile.magicked
    145192FROM distRun
    … …  
    157204    AND distRun.stage = 'warp'
    158205    AND distComponent.dist_id IS NULL
    159     -- where hook 5 %s
     206    AND (distRun.clean OR warpRun.magicked OR distRun.no_magic)
     207    AND (warpRun.state = 'full' OR (distRun.clean AND warpRun.state = 'cleaned'))
     208    -- where hook 6 %s
    160209UNION
    161210SELECT
    … …  
    167216    clean,
    168217    rawExp.camera,
    169     outroot,
     218    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
    170219    diffSkyfile.path_base,
    171220    NULL as chip_path_base,
    … …  
    174223    -- diffSkyfile.data_state,
    175224    'full' AS data_state,
     225    diffSkyfile.quality,
     226    distRun.no_magic,
    176227    diffSkyfile.magicked
    177228FROM distRun
    178229JOIN diffRun ON stage_id = diff_id
    179230JOIN diffSkyfile using(diff_id)
    180 JOIN rawExp using(exp_id)
     231JOIN diffInputSkyfile USING(diff_id, skycell_id)
     232JOIN warpRun
     233    ON warpRun.warp_id = diffInputSkyfile.warp1
     234JOIN fakeRun USING(fake_id)
     235JOIN camRun USING(cam_id)
     236JOIN chipRun USING(chip_id)
     237JOIN rawExp USING(exp_id)
    181238LEFT JOIN distComponent
    182239    ON distRun.dist_id = distComponent.dist_id
    … …  
    186243    AND distRun.stage = 'diff'
    187244    AND distComponent.dist_id IS NULL
    188     -- where hook 6 %s
     245    AND (distRun.clean OR diffRun.magicked OR distRun.no_magic)
     246    AND (diffRun.state = 'full' OR (distRun.clean AND diffRun.state = 'cleaned'))
     247    -- where hook 7 %s
    189248UNION
    190249SELECT DISTINCT
    … …  
    196255    clean,
    197256    rawExp.camera,
    198     outroot,
     257    CONCAT_WS('.', outroot, CONVERT(distRun.dist_id, CHAR)) as outdir,
    199258    stackSumSkyfile.path_base,
    200259    NULL as chip_path_base,
    … …  
    203262    -- stackSumSkyfile.data_state,
    204263    'full' AS data_state,
     264    stackSumSkyfile.quality,
     265    1 AS no_magic,
    205266    0 AS magicked
    206267FROM distRun
    … …  
    232293    AND distRun.stage = 'stack'
    233294    AND distComponent.dist_id IS NULL
    234     -- where hook 7 %s
     295    AND (stackRun.state = 'full' OR (distRun.clean AND stackRun.state = 'cleaned'))
     296    -- where hook 8 %s
    235297) as Foo

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/disttool_processedcomponent.sql

    r23352 r24244  
    1 SELECT *
    2 FROM    distComponent
     1SELECT
     2    dist_id,
     3    target_id,
     4    stage,
     5    stage_id,
     6    component,
     7    CONCAT_WS('.', outroot, CONVERT(dist_id, CHAR)) as outdir,
     8    bytes,
     9    md5sum,
     10    name,
     11    distComponent.state,
     12    distComponent.fault
     13FROM    distRun
     14JOIN distComponent USING(dist_id)

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/disttool_toadvance.sql

    r23352 r24244  
    33    stage,
    44    stage_id,
    5     outroot
     5    CONCAT_WS('.', outroot, CONVERT(dist_id, CHAR)) as outdir,
     6    label,
     7    clean
    68FROM
    79    (
    8 -- raw stage
    9 SELECT 
     10-- raw stage not clean
     11SELECT
    1012    distRun.dist_id,
    1113    stage,
    1214    stage_id,
    13     outroot
     15    outroot,
     16    label,
     17    clean
    1418    FROM distRun
    1519    JOIN rawImfile ON stage_id = rawImfile.exp_id
    … …  
    1923    WHERE
    2024        distRun.state = 'new'
     25        AND distRun.clean = 0
    2126        AND distRun.fault = 0
    2227        AND distRun.stage = 'raw'
    … …  
    2833        AND SUM(distComponent.fault) = 0
    2934UNION
    30 -- chip stage
    31 SELECT 
     35-- clean distribution of raw files (dbinfo only) only 1 component
     36SELECT
    3237    distRun.dist_id,
    3338    stage,
    3439    stage_id,
    35     outroot
     40    outroot,
     41    label,
     42    clean
     43    FROM distRun
     44    LEFT JOIN distComponent
     45        ON distRun.dist_id = distComponent.dist_id
     46    WHERE
     47        distRun.state = 'new'
     48        AND distRun.clean
     49        AND distRun.fault = 0
     50        AND distRun.stage = 'raw'
     51        AND distComponent.component IS NOT NULL
     52        AND distComponent.fault = 0
     53UNION
     54-- chip stage
     55SELECT
     56    distRun.dist_id,
     57    stage,
     58    stage_id,
     59    outroot,
     60    label,
     61    clean
    3662    FROM distRun
    3763    JOIN chipProcessedImfile ON stage_id = chipProcessedImfile.chip_id
    … …  
    5076        AND SUM(distComponent.fault) = 0
    5177UNION
    52 -- warp stage
    53 SELECT
     78-- camera stage
     79SELECT distRun.dist_id,
     80    stage,
     81    stage_id,
     82    outroot,
     83    distRun.label,
     84    clean
     85    FROM distRun
     86    JOIN camRun ON stage_id = cam_id
     87    JOIN chipRun USING(chip_id)
     88    LEFT JOIN distComponent  USING(dist_id)
     89    WHERE
     90        distRun.state = 'new'
     91        AND distRun.fault = 0
     92        AND distComponent.fault = 0
     93        AND distRun.stage = 'camera'
     94--        AND ((chipRun.magicked AND camRun.state = 'full') OR distRun.no_magic)
     95--        AND (camRun.state = 'full' OR (distRun.clean and camRun.state = 'cleaned'))
     96UNION
     97-- fake stage
     98SELECT
    5499    distRun.dist_id,
    55100    stage,
    56101    stage_id,
    57     outroot
     102    outroot,
     103    label,
     104    clean
     105    FROM distRun
     106    JOIN fakeProcessedImfile ON stage_id = fakeProcessedImfile.fake_id
     107    LEFT JOIN distComponent
     108        ON distComponent.dist_id = distRun.dist_id
     109        AND distComponent.component = fakeProcessedImfile.class_id
     110    WHERE
     111        distRun.state = 'new'
     112        AND distRun.fault = 0
     113        AND distRun.stage = 'fake'
     114    GROUP BY
     115        dist_id,
     116        fakeProcessedImfile.fake_id
     117    HAVING
     118        COUNT(fakeProcessedImfile.class_id) = COUNT(distComponent.component)
     119        AND SUM(distComponent.fault) = 0
     120UNION
     121-- warp stage
     122SELECT
     123    distRun.dist_id,
     124    stage,
     125    stage_id,
     126    outroot,
     127    label,
     128    clean
    58129    FROM distRun
    59130    JOIN warpSkyfile on stage_id = warp_id
    … …  
    66137        AND distRun.stage = 'warp'
    67138--        AND warpSkyfile.fault = 0
    68 --        AND warpSkyfile.ignored = 0
     139--        AND warpSkyfile.quality = 0
    69140    GROUP BY
    70141        distRun.dist_id,
    … …  
    75146UNION
    76147-- diff stage
    77 SELECT DISTINCT
     148SELECT
    78149    distRun.dist_id,
    79150    stage,
    80151    stage_id,
    81     outroot
     152    outroot,
     153    distRun.label,
     154    clean
    82155    FROM distRun
    83156    JOIN diffSkyfile
    84         ON distRun.dist_id = diffSkyfile.diff_id
     157        ON stage_id = diffSkyfile.diff_id
    85158    LEFT JOIN distComponent
    86159        ON distRun.dist_id = distComponent.dist_id
    … …  
    99172UNION
    100173-- stack stage
    101 SELECT 
     174SELECT
    102175    distRun.dist_id,
    103176    stage,
    104177    stage_id,
    105     outroot
     178    outroot,
     179    label,
     180    clean
    106181    FROM distRun
    107182    JOIN stackSumSkyfile on stage_id = stack_id

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/faketool_processedimfile.sql

    r17938 r24244  
    11SELECT DISTINCT
    22    fakeProcessedImfile.*,
     3    fakeRun.workdir,
    34    rawExp.exp_tag,
    45    rawExp.exp_name,

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/flatcorr_completely_processed_chiprun.sql

    r18366 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/flatcorr_pending.sql

    r18366 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magicdstool_completed_runs.sql

    r23594 r24244  
    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,
    … …  
    6666    JOIN magicInputSkyfile USING(magic_id)
    6767    JOIN diffSkyfile
    68         ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     68        ON magicRun.diff_id = diffSkyfile.diff_id
    6969        AND magicInputSkyfile.node = diffSkyfile.skycell_id
    7070    LEFT JOIN magicDSFile
    … …  
    7575        AND magicDSRun.stage = 'diff'
    7676        AND diffSkyfile.fault = 0
     77        AND diffSkyfile.quality = 0
    7778    GROUP BY
    7879        magicDSRun.magic_ds_id,

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magicdstool_getrunids.sql

    r20973 r24244  
    99JOIN magicInputSkyfile USING(magic_id)
    1010JOIN diffSkyfile
    11     ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     11    ON magicRun.diff_id = diffSkyfile.diff_id
    1212    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    1313JOIN diffInputSkyfile
    … …  
    1515    AND diffInputSkyfile.skycell_id = diffSkyfile.skycell_id
    1616JOIN warpRun
    17     ON diffInputSkyfile.warp1 = warp_id
     17    ON (diffInputSkyfile.warp1 = warp_id AND !magicRun.inverse)
     18    OR (diffInputSkyfile.warp2 = warp_id AND magicRun.inverse)
    1819JOIN fakeRun USING(fake_id)
    1920JOIN camRun USING(cam_id)

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magicdstool_getskycells.sql

    r20973 r24244  
    22    diffSkyfile.diff_id,
    33    diffSkyfile.skycell_id,
    4     diffSkyfile.uri,
    54    diffSkyfile.path_base
    65FROM magicDSRun
    76JOIN magicRun USING(magic_id)
    8 JOIN magicInputSkyfile USING(magic_id, diff_id)
     7JOIN magicInputSkyfile USING(magic_id)
    98JOIN diffSkyfile
    10     ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     9    ON magicRun.diff_id = diffSkyfile.diff_id
    1110    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    1211JOIN diffInputSkyfile
    … …  
    2120    ON warpSkyfile.warp_id = warpSkyCellMap.warp_id
    2221    AND warpSkyfile.skycell_id = warpSkyCellMap.skycell_id
    23     AND warpSkyfile.ignored = 0
     22    AND warpSkyfile.quality = 0
    2423WHERE
    2524    diffSkyfile.fault = 0
     25    AND diffSkyfile.quality = 0
    2626    AND magic_ds_id = %lld

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magicdstool_todestreak.sql

    r23594 r24244  
    55    magicRun.magic_id,
    66    magicRun.exp_id,
     7    magicDSRun.label,
    78    camera,
    89    magicMask.uri as streaks_uri,
    … …  
    1213    rawImfile.uri as uri,
    1314    NULL as path_base,
     15    magicRun.inverse,
    1416    camProcessedExp.path_base as cam_path_base,
    1517    outroot,
    … …  
    3537    magicDSRun.magic_id,
    3638    chipRun.exp_id,
     39    magicDSRun.label,
    3740    camera,
    3841    magicMask.uri as streaks_uri,
    … …  
    4245    chipProcessedImfile.uri,
    4346    chipProcessedImfile.path_base,
     47    magicRun.inverse,
    4448    camProcessedExp.path_base as cam_path_base,
    4549    outroot,
    … …  
    4953FROM magicDSRun
    5054JOIN magicMask USING (magic_id)
     55JOIN magicRun USING(magic_id)
    5156JOIN camProcessedExp USING(cam_id)
    5257JOIN chipRun ON chip_id = stage_id
    … …  
    6166    AND chipRun.state = 'full'
    6267    AND chipProcessedImfile.fault = 0
     68    AND chipProcessedImfile.quality = 0
    6369    AND magicDSFile.component IS NULL
    6470UNION
    … …  
    6874    magicRun.magic_id,
    6975    magicRun.exp_id,
     76    magicDSRun.label,
    7077    camera,
    7178    magicMask.uri as streaks_uri,
    … …  
    7582    warpSkyfile.uri,
    7683    warpSkyfile.path_base,
     84    magicRun.inverse,
    7785    NULL as cam_path_base,
    7886    outroot,
    … …  
    95103    AND warpRun.state = 'full'
    96104    AND warpSkyfile.fault = 0
    97     AND warpSkyfile.ignored = 0
     105    AND warpSkyfile.quality = 0
    98106    AND magicDSFile.component IS NULL
    99107UNION
    … …  
    103111    magicRun.magic_id,
    104112    magicRun.exp_id,
     113    magicDSRun.label,
    105114    rawExp.camera,
    106115    magicMask.uri as streaks_uri,
    … …  
    108117    magicRun.diff_id as stage_id,
    109118    diffSkyfile.skycell_id as component,
    110     diffSkyfile.uri,
     119    NULL AS uri,
    111120    diffSkyfile.path_base,
     121    magicRun.inverse,
    112122    NULL as cam_path_base,
    113123    outroot,
    … …  
    121131JOIN magicInputSkyfile USING(magic_id)
    122132JOIN diffSkyfile
    123     ON  magicInputSkyfile.diff_id = diffSkyfile.diff_id
     133    ON  magicRun.diff_id = diffSkyfile.diff_id
    124134    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    125135LEFT JOIN magicDSFile
    … …  
    130140    AND magicDSRun.stage = 'diff'
    131141    AND diffSkyfile.fault = 0
     142    AND diffSkyfile.quality = 0
    132143    AND magicDSFile.component IS NULL
    133144) as Foo

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_chipprocessedimfile.sql

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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_definebyquery_insert.sql

    r20973 r24244  
    33SELECT
    44    @MAGIC_ID@, -- Update this with the appropriate magic_id
    5     diff_id,
    65    skycell_id
    76FROM diffSkyfile
    … …  
    98    diff_id = @DIFF_ID@ -- Update this with the appropriate diff_id
    109    AND fault = 0
     10    AND quality = 0

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_definebyquery_select.sql

    r20973 r24244  
    11-- Get a list of exposures on which magic may be performed
    2 SELECT 
     2SELECT
    33    exp_id,
    4     MAX(diffRun.diff_id) AS diff_id
    5 FROM diffRun
    6 JOIN rawExp USING(exp_id)
     4    MAX(diffWarps.diff_id) AS diff_id,
     5    -- The following trick pulls out the 'inverse' value for the maximum diff_id
     6    CONVERT(SUBSTRING_INDEX(GROUP_CONCAT(diffWarps.inverse ORDER BY diffWarps.diff_id), ',', 1), UNSIGNED) AS inverse
     7FROM (
     8    -- Forward diffs
     9    SELECT
     10        diffRun.diff_id,
     11        warp1 AS warp_id,
     12        0 AS inverse
     13    FROM diffRun
     14    JOIN diffInputSkyfile USING(diff_id)
     15    WHERE diffInputSkyfile.warp1 IS NOT NULL
     16        AND diffRun.exposure = 1
     17    -- WHERE hook %s
     18    UNION
     19    -- Backward diffs
     20    SELECT
     21        diffRun.diff_id,
     22        warp2 AS warp_id,
     23        1 AS inverse
     24    FROM diffRun
     25    JOIN diffInputSkyfile USING(diff_id)
     26    WHERE diffInputSkyfile.warp2 IS NOT NULL
     27        AND diffRun.exposure = 1
     28        AND diffRun.bothways = 1
     29    -- WHERE hook %s
     30    ) AS diffWarps
     31JOIN warpRun USING(warp_id)
     32JOIN fakeRun USING(fake_id)
     33JOIN camRun USING(cam_id)
     34JOIN chipRun USING(chip_id)
    735LEFT JOIN magicRun USING(exp_id)
    836-- WHERE hook %s

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_diffskyfile.sql

    r23594 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_inputs.sql

    r20973 r24244  
    1 SELECT *
    2 FROM (
    3 -- Single skycells
     1-- Get input details for a magic node
    42SELECT
    53    magicRun.magic_id,
    6     magicRun.state,
    7     magicInputSkyfile.node,
    8     diffSkyfile.diff_id,
    9     diffSkyfile.uri,
    10     diffSkyfile.path_base,
    11     diffSkyfile.fault
     4    magicRun.inverse, -- Using the inverse subtraction?
     5    magicRun.diff_id,
     6    magicInputs.node,
     7    -- Only one of diff_path_base and magic_path_base should be non-NULL
     8    -- If diff_path_base is non-NULL, then only one of warp_path_base and stack_path_base should be non-NULL
     9    magicInputs.diff_path_base, -- path_base for the diff (if any)
     10    magicInputs.warp_path_base, -- path_base for the template warp (if any)
     11    magicInputs.stack_path_base, -- path_base for the template stack (if any)
     12    magicInputs.magic_path_base -- path_base for child nodes (if any)
    1213FROM magicRun
    13 JOIN magicInputSkyfile
    14 USING(magic_id)
    15 JOIN diffSkyfile
    16     ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
    17     AND magicInputSkyfile.node = diffSkyfile.skycell_id
    18 UNION
    19 -- Merged skycells
    20 SELECT
    21     magicRun.magic_id,
    22     magicRun.state,
    23     magicTree.node,
    24     0,   -- no diff_id
    25     magicNodeResult.uri,
    26     NULL, -- magicNodeResult doesn't have a path_base
    27     magicNodeResult.fault
    28 FROM magicTree
    29 JOIN magicRun
    30     USING(magic_id)
    31 JOIN magicNodeResult
    32     ON magicTree.magic_id = magicNodeResult.magic_id
    33     AND magicTree.dep = magicNodeResult.node
    34 ) as Foo
    35 WHERE
    36     fault = 0
     14JOIN (
     15    -- Single skycells: have uri=NULL
     16    SELECT
     17        magic_id,
     18        magicRun.diff_id,
     19        skycell_id AS node,
     20        diffSkyfile.path_base AS diff_path_base,
     21        warpSkyfile.path_base AS warp_path_base,
     22        stackSumSkyfile.path_base AS stack_path_base,
     23        NULL AS magic_path_base
     24    FROM magicRun
     25    JOIN magicTree USING(magic_id)
     26    JOIN magicInputSkyfile USING(magic_id, node)
     27    JOIN diffRun USING(diff_id)
     28    JOIN diffSkyfile
     29        ON diffSkyfile.diff_id = magicRun.diff_id
     30        AND diffSkyfile.skycell_id = magicInputSkyfile.node
     31    JOIN (
     32        -- Template for non-inverse
     33        SELECT
     34            magic_id,
     35            skycell_id,
     36            warp2 AS warp_id,
     37            stack2 AS stack_id
     38        FROM magicRun
     39        JOIN diffInputSkyfile USING(diff_id)
     40        WHERE magicRun.inverse = 0
     41            -- WHERE hook (magicRun.magic_id, diffInputSkyfile.skycell_id) %s
     42        UNION
     43        -- Template for inverse
     44        SELECT
     45            magic_id,
     46            skycell_id,
     47            warp1 AS warp_id,
     48            stack1 AS stack_id
     49        FROM magicRun
     50        JOIN diffInputSkyfile USING(diff_id)
     51        WHERE magicRun.inverse = 1
     52            -- WHERE hook (magicRun.magic_id, diffInputSkyfile.skycell_id) %s
     53        ) AS diffTemplates USING(magic_id, skycell_id)
     54    LEFT JOIN warpSkyfile USING(warp_id, skycell_id)
     55    LEFT JOIN stackSumSkyfile USING(stack_id)
     56    -- WHERE hook (magicRun.magic_id, magicTree.node) %s
     57    UNION
     58    -- Merged skycells: have diff_id=0, various_path_base=NULL
     59    SELECT
     60        magicTree.magic_id,
     61        0 AS diff_id,
     62        magicTree.dep,
     63        NULL AS diff_path_base,
     64        NULL AS warp_path_base,
     65        NULL AS stack_path_base,
     66        magicNodeResult.path_base AS magic_path_base
     67    FROM magicTree
     68    JOIN magicRun USING(magic_id)
     69    JOIN magicNodeResult
     70        ON magicTree.magic_id = magicNodeResult.magic_id
     71        AND magicTree.dep = magicNodeResult.node
     72    -- WHERE hook (magicRun.magic_id, magicTree.node) %s
     73    ) AS magicInputs USING(magic_id)

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_inputskyfile.sql

    r23594 r24244  
    11SELECT
    22    magicInputSkyfile.*,
    3     diffSkyfile.uri
     3    magicRun.inverse,
     4    diffSkyfile.path_base
    45FROM magicRun
    5 JOIN magicInputSkyfile
    6     USING(magic_id)
     6JOIN magicInputSkyfile USING(magic_id)
    77JOIN diffSkyfile
    8     ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
    9     AND magicInputSkyfile.node = diffSkyfile.skycell_id
     8    ON diffSkyfile.diff_id = magicRun.diff_id
     9    AND diffSkyfile.skycell_id = magicInputSkyfile.node
    1010WHERE
    1111    magicRun.state = 'new'

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_toprocess_inputs.sql

    r23594 r24244  
    11SELECT
    22    magicTree.*,
     3    magicRun.workdir,
     4    rawExp.exp_id,
    35    rawExp.camera,
    4     rawExp.exp_id,
    5     diffSkyfile.path_base,
    6     magicRun.workdir,
    76    -- convert magic_id into a boolean value (1 or 0)
    87    -- note that the type stays a 64 bit int
    9     magicNodeResult.magic_id IS TRUE as done
    10 FROM magicTree
    11 JOIN magicRun USING(magic_id)
     8    magicNodeResult.magic_id IS TRUE AS done,
     9    magicNodeResult.fault IS TRUE AS bad
     10FROM magicRun
     11JOIN magicTree USING(magic_id)
    1212JOIN magicInputSkyfile USING(magic_id, node)
    13 JOIN diffSkyfile
    14     ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
    15     AND magicInputSkyfile.node = diffSkyfile.skycell_id
    1613JOIN rawExp USING(exp_id)
    17 LEFT JOIN magicNodeResult
    18     ON magicTree.magic_id = magicNodeResult.magic_id
    19     AND magicTree.node = magicNodeResult.node
     14JOIN diffSkyfile -- only get nodes that match a skycell
     15    ON diffSkyfile.diff_id = magicRun.diff_id
     16    AND diffSkyfile.skycell_id = magicInputSkyfile.node
     17LEFT JOIN magicNodeResult USING(magic_id, node)
    2018WHERE
    2119    magicRun.state = 'new'
    2220    AND magicNodeResult.magic_id IS NULL
    2321    AND magicNodeResult.node IS NULL
     22-- WHERE hook %s

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_toprocess_tree.sql

    r23594 r24244  
    11SELECT
    22    magicTree.*,
    3     exp_id,
    4     camera,
    53    magicRun.workdir,
     4    rawExp.exp_id,
     5    rawExp.camera,
    66    -- convert magic_id into a boolean value (1 or 0)
    77    -- note that the type stays a 64 bit int
    8     magicNodeResult.magic_id IS TRUE as done,
    9     magicNodeResult.fault IS TRUE as bad
     8    magicNodeResult.magic_id IS TRUE AS done,
     9    magicNodeResult.fault IS TRUE AS bad
    1010FROM magicTree
    1111JOIN magicRun USING(magic_id)
    1212JOIN rawExp USING(exp_id)
    13 LEFT JOIN magicNodeResult
    14     ON magicTree.magic_id = magicNodeResult.magic_id
    15     AND magicTree.node = magicNodeResult.node
     13LEFT JOIN magicNodeResult USING(magic_id, node)
    1614WHERE
    1715    magicRun.state = 'new'
    18     -- where hook %s
     16-- WHERE hook %s
    1917ORDER BY
    2018    magicRun.magic_id

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_totree.sql

    r23594 r24244  
    66    ra,
    77    decl,
    8     tess_id
     8    diffRun.tess_id
    99FROM magicRun
     10JOIN diffRun USING(diff_id)
    1011JOIN rawExp USING(exp_id)
    1112LEFT JOIN magicTree

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/magictool_warpskyfile.sql

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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/pxadmin_create_tables.sql

    r23594 r24244  
    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),
     244    KEY(dateobs),
    247245    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;
     246    FOREIGN KEY(exp_id, tmp_class_id) REFERENCES newImfile(exp_id, tmp_class_id)
     247) ENGINE=innodb DEFAULT CHARSET=latin1;
    253248
    254249CREATE TABLE chipRun (
    … …  
    273268    KEY(end_stage),
    274269    INDEX(chip_id, exp_id),
    275     FOREIGN KEY (exp_id)
    276         REFERENCES rawExp(exp_id))
    277 ENGINE=innodb DEFAULT CHARSET=latin1;
     270    FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id)
     271) ENGINE=innodb DEFAULT CHARSET=latin1;
    278272
    279273CREATE TABLE chipImfile (
    … …  
    283277    PRIMARY KEY(chip_id, class_id),
    284278    KEY(chip_imfile_id),
    285     FOREIGN KEY (chip_id) REFERENCES  chipRun(chip_id))
    286 ENGINE=innodb DEFAULT CHARSET=latin1;
     279    FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id)
     280) ENGINE=innodb DEFAULT CHARSET=latin1;
    287281
    288282CREATE TABLE chipProcessedImfile (
    … …  
    343337    n_cr INT,
    344338    path_base VARCHAR(255),
     339    quality SMALLINT NOT NULL DEFAULT 0,
    345340    fault SMALLINT NOT NULL,
    346341    magicked BIGINT,
    … …  
    348343    KEY(data_state),
    349344    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;
     345    KEY(quality),
     346    FOREIGN KEY(chip_id, exp_id) REFERENCES chipRun(chip_id, exp_id),
     347    FOREIGN KEY(exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)
     348) ENGINE=innodb DEFAULT CHARSET=latin1;
    355349
    356350CREATE TABLE chipMask (
    357351    label VARCHAR(64),
    358     PRIMARY KEY(label))
    359 ENGINE=innodb DEFAULT CHARSET=latin1;
     352    PRIMARY KEY(label)
     353) ENGINE=innodb DEFAULT CHARSET=latin1;
    360354
    361355CREATE TABLE camRun (
    … …  
    380374    KEY(end_stage),
    381375    INDEX(cam_id, chip_id),
    382     FOREIGN KEY (chip_id)
    383         REFERENCES chipRun(chip_id))
    384 ENGINE=innodb DEFAULT CHARSET=latin1;
     376    FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id)
     377) ENGINE=innodb DEFAULT CHARSET=latin1;
    385378
    386379CREATE TABLE camProcessedExp (
    … …  
    444437    n_astrom INT,
    445438    path_base VARCHAR(255),
     439    quality SMALLINT NOT NULL DEFAULT 0,
    446440    fault SMALLINT NOT NULL,
    447441    PRIMARY KEY(cam_id),
    448442    KEY(fault),
    449     FOREIGN KEY (cam_id)
    450         REFERENCES camRun(cam_id))
    451 ENGINE=innodb DEFAULT CHARSET=latin1;
     443    KEY(quality),
     444    FOREIGN KEY(cam_id) REFERENCES camRun(cam_id)
     445) ENGINE=innodb DEFAULT CHARSET=latin1;
    452446
    453447CREATE TABLE camMask (
    454448    label VARCHAR(64),
    455     PRIMARY KEY(label))
    456 ENGINE=innodb DEFAULT CHARSET=latin1;
     449    PRIMARY KEY(label)
     450) ENGINE=innodb DEFAULT CHARSET=latin1;
    457451
    458452CREATE TABLE fakeRun (
    … …  
    475469    KEY(end_stage),
    476470    INDEX(fake_id, cam_id),
    477     FOREIGN KEY (cam_id)
    478         REFERENCES camRun(cam_id))
    479 ENGINE=innodb DEFAULT CHARSET=latin1;
     471    FOREIGN KEY(cam_id) REFERENCES camRun(cam_id)
     472) ENGINE=innodb DEFAULT CHARSET=latin1;
    480473
    481474CREATE TABLE fakeProcessedImfile (
    … …  
    493486    PRIMARY KEY(fake_id, exp_id, class_id),
    494487    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;
     488    FOREIGN KEY(fake_id) REFERENCES fakeRun(fake_id),
     489    FOREIGN KEY(exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)
     490) ENGINE=innodb DEFAULT CHARSET=latin1;
    500491
    501492CREATE TABLE fakeMask (
    502493    label VARCHAR(64),
    503     PRIMARY KEY(label))
    504 ENGINE=innodb DEFAULT CHARSET=latin1;
     494    PRIMARY KEY(label)
     495) ENGINE=innodb DEFAULT CHARSET=latin1;
    505496
    506497CREATE TABLE detRun (
    … …  
    535526    ref_det_id BIGINT,
    536527    ref_iter INT,
    537     -- parent INT, :: dropping this
    538528    PRIMARY KEY(det_id),
    539529    KEY(det_id),
    … …  
    543533    KEY(state),
    544534    KEY(label),
    545     -- KEY(parent), :: dropping this
    546     INDEX(det_id, iteration))
    547 ENGINE=innodb DEFAULT CHARSET=latin1;
     535    INDEX(det_id, iteration)
     536) ENGINE=innodb DEFAULT CHARSET=latin1;
    548537
    549538CREATE 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;
     539    det_id BIGINT,
     540    iteration INT,
     541    exp_id BIGINT,
     542    include TINYINT,
     543    PRIMARY KEY(det_id, iteration, exp_id),
     544    INDEX(det_id, exp_id),
     545    INDEX(det_id, iteration),
     546    FOREIGN KEY(det_id) REFERENCES detRun(det_id),
     547    FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id)
     548) ENGINE=innodb DEFAULT CHARSET=latin1;
    562549
    563550CREATE TABLE detProcessedImfile (
    … …  
    585572    INDEX(det_id, class_id),
    586573    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)
     574    FOREIGN KEY(det_id, exp_id) REFERENCES detInputExp(det_id, exp_id),
     575    FOREIGN KEY(exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)
    591576) ENGINE=innodb DEFAULT CHARSET=latin1;
    592577
    … …  
    611596    PRIMARY KEY(det_id, exp_id),
    612597    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)
     598    FOREIGN KEY(det_id, exp_id) REFERENCES detInputExp(det_id, exp_id),
     599    FOREIGN KEY(det_id, exp_id) REFERENCES detProcessedImfile(det_id, exp_id)
    617600) ENGINE=innodb DEFAULT CHARSET=latin1;
    618601
    … …  
    635618    PRIMARY KEY(det_id, iteration, class_id),
    636619    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)
     620    FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     621    FOREIGN KEY(det_id, class_id) REFERENCES detProcessedImfile(det_id, class_id)
    641622) ENGINE=innodb DEFAULT CHARSET=latin1;
    642623
    … …  
    650631    PRIMARY KEY(det_id, iteration, class_id),
    651632    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)
     633    FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     634    FOREIGN KEY(det_id, iteration, class_id) REFERENCES  detStackedImfile(det_id, iteration, class_id)
    656635) ENGINE=innodb DEFAULT CHARSET=latin1;
    657636
    … …  
    675654    KEY(fault),
    676655    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)
     656    FOREIGN KEY(det_id) REFERENCES detInputExp(det_id),
     657    FOREIGN KEY(det_id, iteration, class_id) REFERENCES detNormalizedStatImfile(det_id, iteration, class_id)
    681658) ENGINE=innodb DEFAULT CHARSET=latin1;
    682659
    … …  
    698675    PRIMARY KEY(det_id, iteration),
    699676    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)
     677    FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     678    FOREIGN KEY(det_id, iteration) REFERENCES detNormalizedImfile(det_id, iteration)
    704679) ENGINE=innodb DEFAULT CHARSET=latin1;
    705680
    … …  
    736711    KEY(fault),
    737712    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)
     713    FOREIGN KEY (det_id, iteration, exp_id) REFERENCES detInputExp(det_id, iteration, exp_id),
     714    FOREIGN KEY (det_id, exp_id, class_id) REFERENCES detProcessedImfile(det_id, exp_id, class_id)
    742715) ENGINE=innodb DEFAULT CHARSET=latin1;
    743716
    … …  
    771744       KEY(fault),
    772745       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)
     746       FOREIGN KEY(det_id, iteration, exp_id) REFERENCES detInputExp(det_id, iteration, exp_id),
     747       FOREIGN KEY(det_id, iteration, exp_id) REFERENCES detResidImfile(det_id, iteration, exp_id)
    777748) ENGINE=innodb DEFAULT CHARSET=latin1;
    778749
    … …  
    788759       PRIMARY KEY(det_id, iteration),
    789760       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)
     761       FOREIGN KEY(det_id, iteration) REFERENCES detInputExp(det_id, iteration),
     762       FOREIGN KEY(det_id, iteration) REFERENCES detResidExp(det_id, iteration)
    794763) ENGINE=innodb DEFAULT CHARSET=latin1;
    795764
    … …  
    812781       PRIMARY KEY(det_id, iteration, class_id),
    813782       KEY(fault),
    814        FOREIGN KEY (det_id, iteration)
    815          REFERENCES  detRun(det_id, iteration)
     783       FOREIGN KEY(det_id, iteration) REFERENCES detRun(det_id, iteration)
    816784) ENGINE=innodb DEFAULT CHARSET=latin1;
    817785
    … …  
    838806    KEY(end_stage),
    839807    INDEX(warp_id, fake_id),
    840     FOREIGN KEY (fake_id)
    841         REFERENCES fakeRun(fake_id)
     808    FOREIGN KEY(fake_id) REFERENCES fakeRun(fake_id)
    842809) ENGINE=innodb DEFAULT CHARSET=latin1;
    843810
    … …  
    850817    PRIMARY KEY(warp_id, skycell_id, tess_id, class_id),
    851818    KEY(fault),
    852     FOREIGN KEY (warp_id)
    853         REFERENCES warpRun(warp_id)
     819    FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id)
    854820) ENGINE=innodb DEFAULT CHARSET=latin1;
    855821
    … …  
    860826    PRIMARY KEY(warp_id, skycell_id),
    861827    KEY(warp_skyfile_id),
    862     FOREIGN KEY (warp_id) REFERENCES  warpRun(warp_id))
    863 ENGINE=innodb DEFAULT CHARSET=latin1;
     828    FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id)
     829) ENGINE=innodb DEFAULT CHARSET=latin1;
    864830
    865831
    … …  
    881847    ymin INT,
    882848    ymax INT,
    883     ignored TINYINT,
     849    quality SMALLINT NOT NULL DEFAULT 0,
    884850    fault SMALLINT,
    885851    magicked TINYINT,
    886852    PRIMARY KEY(warp_id, skycell_id, tess_id),
    887853    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)
     854    KEY(fault),
     855    KEY(quality),
     856    FOREIGN KEY(warp_id, skycell_id, tess_id) REFERENCES warpSkyCellMap(warp_id, skycell_id, tess_id)
    891857) ENGINE=innodb DEFAULT CHARSET=latin1;
    892858
    … …  
    911877        KEY(state),
    912878        KEY(skycell_id),
    913         KEY(tess_id)
     879        KEY(tess_id),
     880        KEY(label)
    914881) ENGINE=innodb DEFAULT CHARSET=latin1;
    915882
    … …  
    918885        warp_id BIGINT,
    919886        PRIMARY KEY(stack_id, warp_id),
    920         FOREIGN KEY (stack_id)  REFERENCES stackRun(stack_id),
    921         FOREIGN KEY (warp_id)  REFERENCES warpSkyfile(warp_id)
     887        FOREIGN KEY(stack_id) REFERENCES stackRun(stack_id),
     888        FOREIGN KEY(warp_id) REFERENCES warpSkyfile(warp_id)
    922889) ENGINE=innodb DEFAULT CHARSET=latin1;
    923890
    … …  
    948915        hostname VARCHAR(64),
    949916        good_frac FLOAT,
     917        quality SMALLINT NOT NULL DEFAULT 0,
    950918        fault SMALLINT,
    951919        PRIMARY KEY(stack_id),
    … …  
    953921        KEY(good_frac),
    954922        KEY(fault),
    955         FOREIGN KEY (stack_id)  REFERENCES  stackRun(stack_id)
     923        KEY(quality),
     924        FOREIGN KEY(stack_id) REFERENCES stackRun(stack_id)
    956925) ENGINE=innodb DEFAULT CHARSET=latin1;
    957926
    … …  
    965934        registered DATETIME,
    966935        tess_id VARCHAR(64),
    967         exp_id  BIGINT,
     936        bothways TINYINT DEFAULT 0,
     937        exposure TINYINT DEFAULT 0,
    968938        magicked TINYINT,
    969939        PRIMARY KEY(diff_id),
    … …  
    971941        KEY(state),
    972942        KEY(tess_id),
    973         FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id)
     943        KEY(label)
    974944) ENGINE=innodb DEFAULT CHARSET=latin1;
    975945
    … …  
    991961        KEY(skycell_id),
    992962        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)
     963        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id),
     964        FOREIGN KEY(warp1, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id),
     965        FOREIGN KEY(warp2, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id),
     966        FOREIGN KEY(stack1) REFERENCES stackSumSkyfile(stack_id),
     967        FOREIGN KEY(stack2) REFERENCES stackSumSkyfile(stack_id)
    998968) ENGINE=innodb DEFAULT CHARSET=latin1;
    999969
    … …  
    1001971        diff_id BIGINT,
    1002972        skycell_id VARCHAR(64),
    1003         uri VARCHAR(255),
    1004973        path_base VARCHAR(255),
     974        data_state VARCHAR(64),
    1005975        bg DOUBLE,
    1006976        bg_stdev DOUBLE,
    … …  
    1015985        kernel_xy FLOAT,
    1016986        kernel_yy FLOAT,
     987        deconv_max FLOAT,
    1017988        sources INT,
    1018989        dtime_diff FLOAT,
    … …  
    1022993        hostname VARCHAR(64),
    1023994        good_frac FLOAT,
     995        quality SMALLINT NOT NULL DEFAULT 0,
    1024996        fault SMALLINT,
    1025997        magicked TINYINT,
    … …  
    1027999        KEY(good_frac),
    10281000        KEY(fault),
    1029         FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)
     1001        KEY(quality),
     1002        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id)
    10301003) ENGINE=innodb DEFAULT CHARSET=latin1;
    10311004
    … …  
    10341007        exp_id BIGINT,
    10351008        diff_id BIGINT,
     1009        inverse TINYINT NOT NULL DEFAULT 0,
    10361010        state VARCHAR(64),
    10371011        workdir VARCHAR(255),
    … …  
    10471021        KEY(label),
    10481022        KEY(fault),
    1049         FOREIGN KEY (exp_id)  REFERENCES rawExp(exp_id),
    1050         FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
     1023        FOREIGN KEY(exp_id)  REFERENCES rawExp(exp_id),
     1024        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id)
    10511025) ENGINE=innodb DEFAULT CHARSET=latin1;
    10521026
    … …  
    10561030        node VARCHAR(64),
    10571031        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)
     1032        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id),
     1033        FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id)
    10601034) ENGINE=innodb DEFAULT CHARSET=latin1;
    10611035
    … …  
    10681042        KEY(dep),
    10691043        INDEX(magic_id, node),
    1070         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id)
     1044        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id)
    10711045) ENGINE=innodb DEFAULT CHARSET=latin1;
    10721046
    … …  
    10741048        magic_id BIGINT,
    10751049        node VARCHAR(64),
    1076         uri VARCHAR(255),
     1050        path_base VARCHAR(255),
    10771051        fault SMALLINT,
    10781052        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),
     1053        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id),
     1054        FOREIGN KEY(magic_id, node) REFERENCES magicTree(magic_id, node),
    10811055        KEY(fault)
    10821056) ENGINE=innodb DEFAULT CHARSET=latin1;
    … …  
    10881062        fault SMALLINT,
    10891063        PRIMARY KEY(magic_id),
    1090         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id),
     1064        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id),
    10911065        KEY(fault)
    10921066) ENGINE=innodb DEFAULT CHARSET=latin1;
    … …  
    11091083        KEY(magic_id),
    11101084        KEY(label),
    1111         FOREIGN KEY (magic_id)  REFERENCES magicRun(magic_id)
     1085        FOREIGN KEY(magic_id) REFERENCES magicRun(magic_id)
    11121086) ENGINE=innodb DEFAULT CHARSET=latin1;
    11131087
    … …  
    11201094    PRIMARY KEY(magic_ds_id, component),
    11211095    KEY(fault),
    1122     FOREIGN KEY (magic_ds_id) REFERENCES magicDSRun(magic_ds_id)
     1096    FOREIGN KEY(magic_ds_id) REFERENCES magicDSRun(magic_ds_id)
    11231097) ENGINE=innodb DEFAULT CHARSET=latin1;
    11241098
    … …  
    11391113        KEY(cal_id),
    11401114        KEY(last_step),
    1141         FOREIGN KEY (cal_id)  REFERENCES calDB(cal_id)
     1115        FOREIGN KEY(cal_id) REFERENCES calDB(cal_id)
    11421116) ENGINE=innodb DEFAULT CHARSET=latin1;
    11431117
    … …  
    11671141        include TINYINT,
    11681142        PRIMARY KEY(corr_id, chip_id),
    1169         FOREIGN KEY (corr_id)  REFERENCES flatcorrRun(corr_id),
    1170         FOREIGN KEY (chip_id)  REFERENCES chipRun(chip_id)
     1143        FOREIGN KEY(corr_id) REFERENCES flatcorrRun(corr_id),
     1144        FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id)
    11711145) ENGINE=innodb DEFAULT CHARSET=latin1;
    11721146
    … …  
    11771151        include TINYINT,
    11781152        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)
     1153        FOREIGN KEY(corr_id) REFERENCES flatcorrRun(corr_id),
     1154        FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id),
     1155        FOREIGN KEY(cam_id) REFERENCES camRun(cam_id)
    11821156) ENGINE=innodb DEFAULT CHARSET=latin1;
    11831157
    … …  
    12311205        PRIMARY KEY(job_id, req_id),
    12321206        KEY(job_id),
    1233         FOREIGN KEY (req_id)  REFERENCES pstampRequest(req_id)
     1207        FOREIGN KEY(req_id) REFERENCES pstampRequest(req_id)
    12341208) ENGINE=innodb DEFAULT CHARSET=latin1;
    12351209
    12361210CREATE TABLE distTarget (
    1237     target_id   BIGINT AUTO_INCREMENT, 
     1211    target_id   BIGINT AUTO_INCREMENT,
    12381212    obs_mode    VARCHAR(64),
    12391213    stage       VARCHAR(64),
    … …  
    12521226    outroot     VARCHAR(255),
    12531227    clean       TINYINT,
     1228    no_magic    TINYINT,
    12541229    state       VARCHAR(64),
    12551230    time_stamp  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    … …  
    12621237
    12631238CREATE TABLE distComponent (
    1264     dist_id     BIGINT, 
     1239    dist_id     BIGINT,
    12651240    component   VARCHAR(64),
    12661241    bytes       INT,
    … …  
    12801255    dbname      VARCHAR(64),
    12811256    dbhost      VARCHAR(64),
    1282     prod_root   VARCHAR(255),
    12831257    PRIMARY KEY(prod_id)
    12841258)  ENGINE=innodb DEFAULT CHARSET=latin1;
    … …  
    13011275    dest_id     BIGINT,
    13021276    target_id   BIGINT,
    1303     prod_id     BIGINT,
    13041277    state       VARCHAR(64),
    13051278    PRIMARY KEY(int_id),
    13061279    FOREIGN KEY(dest_id) REFERENCES rcDestination(dest_id),
    1307     FOREIGN KEY(target_id) REFERENCES distTarget(target_id),
    1308     FOREIGN KEY(prod_id) REFERENCES rcDSProduct(prod_id)
     1280    FOREIGN KEY(target_id) REFERENCES distTarget(target_id)
    13091281)  ENGINE=innodb DEFAULT CHARSET=latin1;
    13101282
    … …  
    13131285    dist_id     BIGINT,
    13141286    prod_id     BIGINT,
    1315     name        VARCHAR(64),
     1287    name        VARCHAR(255),
    13161288    state       VARCHAR(64),
    13171289    registered  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    1318     PRIMARY KEY(fs_id),
     1290    fault       SMALLINT DEFAULT 0,
     1291    PRIMARY KEY(dist_id, prod_id),
     1292    KEY(fs_id),
    13191293    FOREIGN KEY(dist_id) REFERENCES distRun(dist_id),
    13201294    FOREIGN KEY(prod_id) REFERENCES rcDSProduct(prod_id)
     1295--    KEY(dist_id, prod_id)
    13211296)  ENGINE=innodb DEFAULT CHARSET=latin1;
    13221297
    … …  
    13261301    dest_id     BIGINT,
    13271302    state       VARCHAR(64),
     1303    status_fs_name   VARCHAR(64),
    13281304    registered  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     1305    fault       SMALLINT DEFAULT 0,
    13291306    PRIMARY KEY(rc_id),
    13301307    FOREIGN KEY(fs_id) REFERENCES rcDSFileset(fs_id),
    … …  
    13321309)  ENGINE=innodb DEFAULT CHARSET=latin1;
    13331310
    1334 
    1335 
    1336 -- This comment line is here to avoid empty query error.
     1311-- Sources from which to receive files
     1312CREATE TABLE receiveSource (
     1313    source_id BIGINT AUTO_INCREMENT, -- unique identifier
     1314    source VARCHAR(128) NOT NULL, -- source URI
     1315    product VARCHAR(64) NOT NULL, -- product of interest
     1316    workdir VARCHAR(255) NOT NULL, -- where to extract
     1317    comment VARCHAR(255),       -- for human memory
     1318    fileset_last VARCHAR(128),  -- last fileset seen
     1319    status_product VARCHAR(64), -- status data store product
     1320    ds_dbname VARCHAR(64),      -- status data store's database name
     1321    ds_dbhost VARCHAR(64),      -- status data store's host name
     1322    PRIMARY KEY(source_id),
     1323    KEY(source),
     1324    KEY(product),
     1325    KEY(comment)
     1326) ENGINE=innodb DEFAULT CHARSET=latin1;
     1327
     1328-- Filesets to receive
     1329CREATE TABLE receiveFileset (
     1330    fileset_id BIGINT AUTO_INCREMENT, -- unique identifier
     1331    source_id BIGINT NOT NULL,  -- link to receiveSource
     1332    fileset VARCHAR(128) NOT NULL, -- fileset to receive
     1333    state VARCHAR(64), -- new or full
     1334    dirinfo VARCHAR(255), -- uri for directory info file for this run
     1335    dbinfo VARCHAR(255), -- uri for database dump file for this run
     1336    fault SMALLINT NOT NULL DEFAULT 0, -- Fault code
     1337    PRIMARY KEY(fileset_id),
     1338    KEY(source_id),
     1339    CONSTRAINT UNIQUE(source_id, fileset),
     1340    FOREIGN KEY(source_id) REFERENCES receiveSource(source_id)
     1341) ENGINE=innodb DEFAULT CHARSET=latin1;
     1342
     1343-- Files to receive
     1344CREATE TABLE receiveFile (
     1345    file_id BIGINT AUTO_INCREMENT, -- unique identifier
     1346    fileset_id BIGINT NOT NULL,  -- link to receiveFileset
     1347    file VARCHAR(128) NOT NULL, -- file to receive
     1348    bytes BIGINT,
     1349    md5sum VARCHAR(255),
     1350    file_type VARCHAR(64),
     1351    component VARCHAR(64),
     1352    PRIMARY KEY(file_id),
     1353    KEY(fileset_id),
     1354    CONSTRAINT UNIQUE(fileset_id, file),
     1355    FOREIGN KEY(fileset_id) REFERENCES receiveFileset(fileset_id)
     1356) ENGINE=innodb DEFAULT CHARSET=latin1;
     1357
     1358-- Result of receiving files
     1359CREATE TABLE receiveResult (
     1360    file_id BIGINT AUTO_INCREMENT, -- link to receiveFile
     1361    dtime_copy FLOAT,           -- Time to copy
     1362    dtime_extract FLOAT,        -- Time to extract
     1363    fault SMALLINT NOT NULL DEFAULT 0, -- Fault code
     1364    PRIMARY KEY(file_id),
     1365    KEY(fault),
     1366    FOREIGN KEY(file_id) REFERENCES receiveFile(file_id)
     1367) ENGINE=innodb DEFAULT CHARSET=latin1;
     1368
     1369
     1370-- This comment line is here to avoid empty query error.
    13371371-- Another way to avoid that problem is to omit the semicolon above but I think that is untidy.

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/pxadmin_drop_tables.sql

    r23594 r24244  
    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;
    … …  
    4848DROP TABLE IF EXISTS magicNodeResult;
    4949DROP TABLE IF EXISTS magicMask;
    50 DROP TABLE IF EXISTS magicSkyfileMask;
    5150DROP TABLE IF EXISTS magicDSRun;
    5251DROP TABLE IF EXISTS magicDSFile;
    … …  
    6968DROP TABLE IF EXISTS rcDSFileset;
    7069DROP TABLE IF EXISTS rcRun;
     70DROP TABLE IF EXISTS receiveSource;
     71DROP TABLE IF EXISTS receiveFileset;
     72DROP TABLE IF EXISTS receiveFile;
     73DROP TABLE IF EXISTS receiveResult;
    7174
    7275SET FOREIGN_KEY_CHECKS=1

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/pztool_find_completed_exp.sql

    r23594 r24244  
    33    camera,
    44    telescope,
     5    dateobs,
    56    state
    67FROM (
    … …  
    89        pzDownloadImfile.*,
    910        pzDownloadExp.state,
    10         summitExp.imfiles
     11        summitExp.imfiles,
     12        summitExp.dateobs
    1113    FROM pzDownloadExp
    1214    JOIN summitExp

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_definebyquery_insert.sql

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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_definebyquery_insert_random_part1.sql

    r19258 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_definebyquery_part1.sql

    r19258 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_definebyquery_test.sql

    r19258 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_find_complete_warps.sql

    r19258 r24244  
    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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_pendingcleanuprun.sql

    r19526 r24244  
    2727     USING (exp_id)
    2828WHERE
    29     stackRun.state = 'goto_cleaned'
     29    (stackRun.state = 'goto_cleaned' OR stackRun.state = 'goto_scrubbed' OR stackRun.state = 'goto_purged')

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_pendingcleanupskyfile.sql

    r19092 r24244  
    44    stackRun.workdir,
    55    stackRun.dvodb,
    6     stackRun.tess_id
     6    stackRun.tess_id,
     7    stackRun.skycell_id
    78FROM stackRun
    89JOIN stackSumSkyfile
    910    USING(stack_id)
    1011WHERE
    11     stackRun.state = 'goto_cleaned'
     12    (stackRun.state = 'goto_cleaned' OR stackRun.state = 'goto_scrubbed' OR stackRun.state = 'goto_purged')

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_sumskyfile.sql

    r20072 r24244  
    22    stackSumSkyfile.*,
    33    stackRun.state,
     4    stackRun.skycell_id,
     5    stackRun.workdir,
    46    (SELECT rawExp.camera FROM
    57        stackInputSkyfile

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/stacktool_tosum.sql

    r19678 r24244  
    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))

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/warptool_definebyquery.sql

    r23594 r24244  
    1 SELECT
    2     *
    3 FROM
    4     (SELECT DISTINCT
    5         fakeRun.*,
    6         chipRun.chip_id,
    7         rawExp.camera,
    8         rawExp.telescope,
    9         rawExp.dateobs,
    10         rawExp.exp_tag,
    11         rawExp.exp_name,
    12         rawExp.exp_type,
    13         rawExp.filelevel,
    14         rawExp.filter,
    15         rawExp.airmass,
    16         rawExp.ra,
    17         rawExp.decl,
    18         rawExp.exp_time,
    19         rawExp.sat_pixel_frac,
    20         rawExp.bg,
    21         rawExp.bg_stdev,
    22         rawExp.bg_mean_stdev,
    23         rawExp.alt,
    24         rawExp.az,
    25         rawExp.ccd_temp,
    26         rawExp.posang,
    27         rawExp.object,
    28         rawExp.sun_angle
    29     FROM fakeRun
    30     JOIN camRun
    31         using(cam_id)
    32     JOIN chipRun
    33         using(chip_id)
    34     JOIN rawExp
    35         using(exp_id)
    36     WHERE
    37         fakeRun.state = 'full') as Foo
     1SELECT DISTINCT
     2    fakeRun.*,
     3    chipRun.chip_id,
     4    rawExp.camera,
     5    rawExp.telescope,
     6    rawExp.dateobs,
     7    rawExp.exp_id,
     8    rawExp.exp_tag,
     9    rawExp.exp_name,
     10    rawExp.exp_type,
     11    rawExp.filelevel,
     12    rawExp.filter,
     13    rawExp.airmass,
     14    rawExp.ra,
     15    rawExp.decl,
     16    rawExp.exp_time,
     17    rawExp.sat_pixel_frac,
     18    rawExp.bg,
     19    rawExp.bg_stdev,
     20    rawExp.bg_mean_stdev,
     21    rawExp.alt,
     22    rawExp.az,
     23    rawExp.ccd_temp,
     24    rawExp.posang,
     25    rawExp.object,
     26    rawExp.sun_angle
     27FROM fakeRun
     28JOIN camRun USING(cam_id)
     29JOIN chipRun USING(chip_id)
     30JOIN rawExp USING(exp_id)
     31WHERE camRun.state = 'full'
     32AND chipRun.state = 'full'

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/warptool_finished_run_select.sql

    r23594 r24244  
    55   (SELECT DISTINCT
    66       warpRun.warp_id,
     7       warpRun.label as label,
    78       warpSkyCellMap.warp_id as foo,
    89       warpSkyfile.warp_id as bar,
    … …  
    1920   HAVING
    2021       COUNT(warpSkyCellMap.warp_id) = COUNT(warpSkyfile.warp_id)
    21        AND SUM(warpSkyfile.fault) = 0
     22       AND SUM(warpSkyfile.fault > 0) = 0
    2223 ) as Foo

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/warptool_imfile.sql

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

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/warptool_pendingcleanupskyfile.sql

    r23594 r24244  
    1212   ((warpRun.state = 'goto_cleaned'  AND warpSkyfile.data_state = 'full')
    1313    OR
    14     (warpRun.state = 'goto_scrubbed' AND warpSkyfile.data_state = 'full')
     14    (warpRun.state = 'goto_scrubbed' AND warpSkyfile.data_state != 'scrubbed')
    1515    OR
    1616    (warpRun.state = 'goto_purged'   AND warpSkyfile.data_state != 'purged'))

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/warptool_revertwarped_delete.sql

    r20722 r24244  
    66    AND camRun.chip_id = chipRun.chip_id
    77    AND chipRun.exp_id = rawExp.exp_id
     8    AND warpRun.state = 'new'
    89    AND warpSkyfile.fault != 0
     10

  • branches/cnb_branches/cnb_branch_20090301/ippTools/share/warptool_warped.sql

    r21321 r24244  
    22    warpSkyfile.*,
    33    warpRun.state,
     4    warpRun.workdir,
    45    warpImfile.warp_skyfile_id,
    56    rawExp.exp_id,
Note: See TracChangeset for help on using the changeset viewer.