IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 20973 for trunk/ippTools/share


Ignore:
Timestamp:
Dec 13, 2008, 10:17:34 AM (18 years ago)
Author:
bills
Message:

Rework the diff stage to process the skycells from an exposure in a single
diffRun

Location:
trunk/ippTools/share
Files:
4 added
17 edited

Legend:

Unmodified
Added
Removed

  • trunk/ippTools/share/Makefile.am

    r20841 r20973  
    7070     dettool_toresidimfile.sql \
    7171     dettool_tostacked.sql \
    72      difftool_definebyquery.sql \
     72     difftool_completed_runs.sql \
     73     difftool_definebyquery_part1.sql \
     74     difftool_definebyquery_part2.sql \
     75     difftool_definebyquery_temp_create.sql \
    7376     difftool_donecleanup.sql \
    7477     difftool_inputskyfile.sql \

  • trunk/ippTools/share/difftool_definebyquery.sql

    r20682 r20973  
    99    warpsToDiff.good_frac,
    1010    warpsToDiff.diff_id,
    11     warpsToDiff.kind,
    1211    current_stack_id,
    13     best_stack_id
     12    best_stack_id,
     13    exp_id
    1414FROM (
    1515    -- Get list of warps that can be diffed, with any associated diff
    … …  
    2323        warpRun.label as warp_label,
    2424        diffInputs.diff_id,
    25         diffInputs.kind,
    26         diffTemplates.stack_id AS current_stack_id
     25        diffInputs.stack2 AS current_stack_id,
     26        rawExp.exp_id
    2727    FROM warpSkyfile
    2828    JOIN warpRun USING(warp_id)
    … …  
    3333    -- Check if it has an associated diff
    3434    LEFT JOIN diffInputSkyfile AS diffInputs
    35         ON diffInputs.warp_id = warpSkyfile.warp_id
     35        ON diffInputs.warp1 = warpSkyfile.warp_id
    3636        AND diffInputs.skycell_id = warpSkyfile.skycell_id
    37         AND diffInputs.template = 0 -- only join input files
    38     -- Get the stack_id currently used as a template, if any
    39     LEFT JOIN diffInputSkyfile AS diffTemplates
    40         ON diffTemplates.diff_id = diffInputs.diff_id
    41         AND diffTemplates.template != 0 -- only join template files
     37        AND diffInputs.stack2 IS NOT NULL
    4238    WHERE
    4339        warpSkyfile.fault = 0

  • trunk/ippTools/share/difftool_inputskyfile.sql

    r19582 r20973  
    11SELECT * FROM
    2     (SELECT
     2    (SELECT
     3        -- warp input
    34        diffRun.diff_id,
    4         diffRun.skycell_id,
    5         diffRun.tess_id,
     5        diffInputSkyfile.skycell_id,
     6        diffInputSkyfile.tess_id,
    67        0 as stack_id,
    78        warpSkyfile.warp_id,
    89        warpSkyfile.uri,
    910        warpSkyfile.path_base,
    10         diffInputSkyfile.template,
     11        0 as template,
    1112        rawExp.camera
    1213    FROM diffRun
    … …  
    1415        USING(diff_id)
    1516    JOIN warpSkyfile
    16         ON  diffInputSkyfile.warp_id    = warpSkyfile.warp_id
     17        ON  diffInputSkyfile.warp1      = warpSkyfile.warp_id
    1718        AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
    1819        AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
    1920    JOIN warpRun
    20         ON diffInputSkyfile.warp_id = warpRun.warp_id
     21        ON diffInputSkyfile.warp1 = warpRun.warp_id
     22    JOIN fakeRun
     23        USING(fake_id)
     24    JOIN camRun
     25        USING(cam_id)
     26    JOIN chipRun
     27        USING(chip_id)
     28    JOIN chipProcessedImfile
     29        USING(chip_id)
     30    JOIN rawExp
     31        ON chipRun.exp_id = rawExp.exp_id
     32    WHERE
     33        diffRun.state = 'new'
     34        AND warpRun.state = 'full'
     35        AND fakeRun.state = 'full'
     36        AND camRun.state = 'full'
     37        AND chipRun.state = 'full'
     38        -- where hook %s
     39    UNION
     40    SELECT
     41        -- warp template
     42        diffRun.diff_id,
     43        diffInputSkyfile.skycell_id,
     44        diffInputSkyfile.tess_id,
     45        0 as stack_id,
     46        warpSkyfile.warp_id,
     47        warpSkyfile.uri,
     48        warpSkyfile.path_base,
     49        0 as template,
     50        rawExp.camera
     51    FROM diffRun
     52    JOIN diffInputSkyfile
     53        USING(diff_id)
     54    JOIN warpSkyfile
     55        ON  diffInputSkyfile.warp2      = warpSkyfile.warp_id
     56        AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
     57        AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
     58    JOIN warpRun
     59        ON diffInputSkyfile.warp2 = warpRun.warp_id
    2160    JOIN fakeRun
    2261        USING(fake_id)
    … …  
    3877    UNION
    3978    SELECT
     79        -- stack input
    4080        diffRun.diff_id,
    41         diffRun.skycell_id,
    42         diffRun.tess_id,
     81        diffInputSkyfile.skycell_id,
     82        diffInputSkyfile.tess_id,
    4383        stackSumSkyfile.stack_id,
    4484        0 as warp_id,
    4585        stackSumSkyfile.uri,
    4686        stackSumSkyfile.path_base,
    47         diffInputSkyfile.template,
     87        1 as template,
    4888        rawExp.camera
    4989    FROM diffRun
    … …  
    5191        USING(diff_id)
    5292    JOIN stackSumSkyfile
    53         ON  diffInputSkyfile.stack_id = stackSumSkyfile.stack_id
    54     JOIN stackInputSkyfile
    55         ON diffInputSkyfile.stack_id = stackInputSkyfile.stack_id
    56     JOIN warpRun
    57         ON stackInputSkyfile.warp_id = warpRun.warp_id
    58     JOIN fakeRun
    59         USING(fake_id)
    60     JOIN camRun
    61         USING(cam_id)
    62     JOIN chipRun
    63         USING(chip_id)
    64     JOIN chipProcessedImfile
    65         USING(chip_id)
     93        ON  diffInputSkyfile.stack1 = stackSumSkyfile.stack_id
    6694    JOIN rawExp
    67         ON chipRun.exp_id = rawExp.exp_id
     95        USING(exp_id)
    6896    WHERE
    6997        diffRun.state = 'new'
    70         AND warpRun.state = 'full'
    71         AND fakeRun.state = 'full'
    72         AND camRun.state = 'full'
    73         AND chipRun.state = 'full'
     98        -- where hook %s
     99    UNION
     100    SELECT
     101        -- stack template
     102        diffRun.diff_id,
     103        diffInputSkyfile.skycell_id,
     104        diffInputSkyfile.tess_id,
     105        stackSumSkyfile.stack_id,
     106        0 as warp_id,
     107        stackSumSkyfile.uri,
     108        stackSumSkyfile.path_base,
     109        1 as template,
     110        rawExp.camera
     111    FROM diffRun
     112    JOIN diffInputSkyfile
     113        USING(diff_id)
     114    JOIN stackSumSkyfile
     115        ON  diffInputSkyfile.stack2 = stackSumSkyfile.stack_id
     116    JOIN rawExp
     117        USING(exp_id)
     118    WHERE
     119        diffRun.state = 'new'
    74120        -- where hook %s
    75121    ) as Foo

  • trunk/ippTools/share/difftool_skyfile.sql

    r20694 r20973  
    11SELECT
    2     diffRun.skycell_id,
     2    diffSkyfile.*,
    33    diffRun.tess_id,
    44    diffRun.state,
    5     diffSkyfile.*,
    6     (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
    7         AND diffInputSkyfile.template = 0 ) AS warp_id_temp_0,
    8     (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
    9         AND diffInputSkyfile.template = 0 ) AS stack_id_temp_0,
    10     (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
    11         AND diffInputSkyfile.template = 1 ) AS warp_id_temp_1,
    12     (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
    13         AND diffInputSkyfile.template = 1 ) AS stack_id_temp_1
     5    warp1,
     6    stack1,
     7    warp2,
     8    stack2
     9--    (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
     10--        AND diffInputSkyfile.template = 0 ) AS warp_id_temp_0,
     11--    (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
     12--        AND diffInputSkyfile.template = 0 ) AS stack_id_temp_0,
     13--    (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
     14--        AND diffInputSkyfile.template = 1 ) AS warp_id_temp_1,
     15--    (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
     16--        AND diffInputSkyfile.template = 1 ) AS stack_id_temp_1
    1417FROM diffRun
    1518JOIN diffSkyfile
    … …  
    1720JOIN diffInputSkyfile
    1821    ON diffInputSkyfile.diff_id = diffRun.diff_id
    19     AND diffInputSkyfile.template = 0
    2022JOIN warpRun
    21     USING(warp_id)
    22 JOIN fakeRun
    23     USING(fake_id)
    24 JOIN camRun
    25     USING(cam_id)
    26 JOIN chipRun
    27     USING(chip_id)
     23    ON warpRun.warp_id = diffInputSkyfile.warp1
    2824JOIN rawExp
    2925    USING(exp_id)

  • trunk/ippTools/share/difftool_todiffskyfile.sql

    r20934 r20973  
    66    0 as diff_skyfile_id,
    77    diffRun.workdir,
    8     diffRun.skycell_id,
     8    diffInputSkyfile.skycell_id,
    99    diffRun.tess_id,
    1010    diffRun.label,
    1111    diffRun.state
    1212FROM diffRun
     13JOIN diffInputSkyfile USING(diff_id)
     14
    1315-- Get list of templates for each diffRun
    14 JOIN diffInputSkyfile AS diffTemplateSkyfile
    15     ON diffRun.diff_id = diffTemplateSkyfile.diff_id
    16     AND diffRun.skycell_id = diffTemplateSkyfile.skycell_id
    17     AND diffTemplateSkyfile.template = 1
     16-- JOIN diffInputSkyfile AS diffTemplateSkyfile
     17--     ON diffRun.diff_id = diffTemplateSkyfile.diff_id
     18--     AND diffRun.skycell_id = diffTemplateSkyfile.skycell_id
     19--     AND diffTemplateSkyfile.template = 1
    1820-- Get list of inputs for each diffRun
    19 JOIN diffInputSkyfile
    20     ON diffRun.diff_id = diffInputSkyfile.diff_id
    21     AND diffRun.skycell_id = diffInputSkyfile.skycell_id
    22     AND diffInputSkyfile.template = 0
     21-- JOIN diffInputSkyfile
     22--     ON diffRun.diff_id = diffInputSkyfile.diff_id
     23--     AND diffRun.skycell_id = diffInputSkyfile.skycell_id
     24--     AND diffInputSkyfile.template = 0
     25
    2326-- Get warp templates
    2427LEFT JOIN warpRun AS warpTemplateRun
    25     ON warpTemplateRun.warp_id = diffTemplateSkyfile.warp_id
    26     AND diffTemplateSkyfile.warp_id IS NOT NULL
     28    ON warpTemplateRun.warp_id = diffInputSkyfile.warp2
     29    AND diffInputSkyfile.warp2 IS NOT NULL
    2730LEFT JOIN warpSkyfile AS warpTemplateSkyfile
    2831    ON warpTemplateSkyfile.warp_id = warpTemplateRun.warp_id
    29     AND warpTemplateSkyfile.skycell_id = diffTemplateSkyfile.skycell_id
     32    AND warpTemplateSkyfile.skycell_id = diffInputSkyfile.skycell_id
     33
    3034-- Get warp inputs
    3135LEFT JOIN warpRun
    32     ON warpRun.warp_id = diffInputSkyfile.warp_id
    33     AND diffInputSkyfile.warp_id IS NOT NULL
     36    ON warpRun.warp_id = diffInputSkyfile.warp1
     37    AND diffInputSkyfile.warp1 IS NOT NULL
    3438LEFT JOIN warpSkyfile
    3539    ON warpSkyfile.warp_id = warpRun.warp_id
    3640    AND warpSkyfile.skycell_id = diffInputSkyfile.skycell_id
     41
    3742-- Get stack templates
    3843LEFT JOIN stackRun AS stackTemplateRun
    39     ON stackTemplateRun.stack_id = diffTemplateSkyfile.stack_id
     44    ON stackTemplateRun.stack_id = diffInputSkyfile.stack2
     45    AND diffInputSkyfile.stack2 IS NOT NULL
    4046LEFT JOIN stackSumSkyfile AS stackTemplateSkyfile
    4147    ON stackTemplateSkyfile.stack_id = stackTemplateRun.stack_id
     48
    4249-- Get stack inputs
    4350LEFT JOIN stackRun
    44     ON stackRun.stack_id = diffInputSkyfile.stack_id
    45     AND diffInputSkyfile.warp_id IS NULL
     51    ON stackRun.stack_id = diffInputSkyfile.stack1
     52    AND diffInputSkyfile.stack1 IS NOT NULL
    4653LEFT JOIN stackSumSkyfile
    4754    ON stackSumSkyfile.stack_id = stackRun.stack_id
    48     AND diffInputSkyfile.warp_id IS NULL
     55
    4956-- Get what's already been processed
    5057LEFT JOIN diffSkyfile
    5158    ON diffInputSkyfile.diff_id = diffSkyfile.diff_id
     59    AND diffInputSkyfile.skycell_id = diffSkyfile.skycell_id
    5260WHERE
    5361-- Ready to be processed
    … …  
    5866    )
    5967-- Ensure input warps are available
    60     AND (diffInputSkyfile.warp_id IS NULL
     68    AND (diffInputSkyfile.warp1 IS NULL
    6169    OR (warpRun.state = 'full'
    6270    AND warpSkyfile.fault = 0
    6371    AND warpSkyfile.ignored = 0))
    6472-- Ensure input stacks are available
    65     AND (diffInputSkyfile.stack_id IS NULL
     73    AND (diffInputSkyfile.stack1 IS NULL
    6674    OR (stackRun.state = 'full'
    6775    AND stackSumSkyfile.fault = 0))
    6876-- Ensure template warps are available
    69     AND (diffTemplateSkyfile.warp_id IS NULL
     77    AND (diffInputSkyfile.warp2 IS NULL
    7078    OR (warpTemplateRun.state = 'full'
    7179    AND warpTemplateSkyfile.fault = 0
    7280    AND warpTemplateSkyfile.ignored = 0))
    7381-- Ensure template stacks are available
    74     AND (diffTemplateSkyfile.stack_id IS NULL
     82    AND (diffInputSkyfile.stack2 IS NULL
    7583    OR (stackTemplateRun.state = 'full'
    7684    AND stackTemplateSkyfile.fault = 0))

  • trunk/ippTools/share/magicdstool_completed_runs.sql

    r20841 r20973  
    6565    JOIN magicRun USING (magic_id)
    6666    JOIN magicInputSkyfile USING(magic_id)
    67     -- Do we really need to join back to diffInputSkyfile here?
    68     JOIN diffRun USING(diff_id)
    69     JOIN diffSkyfile USING(diff_id)
    70     JOIN diffInputSkyfile USING(diff_id)
     67    JOIN diffSkyfile
     68        ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     69        AND magicInputSkyfile.node = diffSkyfile.skycell_id
    7170    LEFT JOIN magicDSFile
    7271        ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    73         AND magicDSFile.component = diffInputSkyfile.skycell_id
     72        AND magicDSFile.component = diffSkyfile.skycell_id
    7473    WHERE
    7574        magicDSRun.state = 'run'
    7675        AND magicDSRun.stage = 'diff'
    77         AND diffRun.state = 'full'
    7876        AND diffSkyfile.fault = 0
    7977    GROUP BY

  • trunk/ippTools/share/magicdstool_getrunids.sql

    r20846 r20973  
    88FROM magicRun
    99JOIN magicInputSkyfile USING(magic_id)
    10 JOIN diffInputSkyfile USING(diff_id)
    11 JOIN warpRun USING(warp_id)
     10JOIN diffSkyfile
     11    ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     12    AND magicInputSkyfile.node = diffSkyfile.skycell_id
     13JOIN diffInputSkyfile
     14    ON diffInputSkyfile.diff_id = diffSkyfile.diff_id
     15    AND diffInputSkyfile.skycell_id = diffSkyfile.skycell_id
     16JOIN warpRun
     17    ON diffInputSkyfile.warp1 = warp_id
    1218JOIN fakeRun USING(fake_id)
    1319JOIN camRun USING(cam_id)
    1420JOIN chipRun USING(chip_id)
    15 WHERE template  = 0
    16 AND magic_id = %ld
     21WHERE magic_id = %ld

  • trunk/ippTools/share/magicdstool_getskycells.sql

    r20782 r20973  
    11SELECT DISTINCT
    22    diffSkyfile.diff_id,
    3     diffRun.skycell_id,
     3    diffSkyfile.skycell_id,
    44    diffSkyfile.uri,
    55    diffSkyfile.path_base
    66FROM magicDSRun
    77JOIN magicRun USING(magic_id)
    8 JOIN magicInputSkyfile USING(magic_id)
    9 JOIN diffRun USING(diff_id)
    10 JOIN diffSkyfile USING(diff_id)
     8JOIN magicInputSkyfile USING(magic_id, diff_id)
     9JOIN diffSkyfile
     10    ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     11    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    1112JOIN diffInputSkyfile
    12     ON diffInputSkyfile.diff_id = diffRun.diff_id
    13     AND diffInputSkyfile.skycell_id = diffRun.skycell_id
     13    ON diffInputSkyfile.diff_id = diffSkyfile.diff_id
     14    AND diffInputSkyfile.skycell_id = diffSkyfile.skycell_id
    1415    -- Want input warps only
    15     AND diffInputSkyfile.warp_id IS NOT NULL
    16     AND diffInputSkyfile.template = 0
     16    AND diffInputSkyfile.warp1 IS NOT NULL
    1717JOIN warpSkyCellMap
    18     ON warpSkyCellMap.warp_id = diffInputSkyfile.warp_id
     18    ON warpSkyCellMap.warp_id = diffInputSkyfile.warp1
    1919    AND warpSkyCellMap.skycell_id = diffInputSkyfile.skycell_id
    2020JOIN warpSkyfile
    … …  
    2323    AND warpSkyfile.ignored = 0
    2424WHERE
    25     diffRun.state = 'full'
    26     AND diffSkyfile.fault = 0
     25    diffSkyfile.fault = 0
    2726    AND magic_ds_id = %lld

  • trunk/ippTools/share/magicdstool_todestreak.sql

    r20841 r20973  
    106106    magicMask.uri as streaks_uri,
    107107    stage,
    108     diff_id as stage_id,
    109     diffInputSkyfile.skycell_id as component,
     108    magicRun.diff_id as stage_id,
     109    diffSkyfile.skycell_id as component,
    110110    diffSkyfile.uri,
    111111    diffSkyfile.path_base,
    … …  
    120120JOIN magicDSRun USING(magic_id)
    121121JOIN magicInputSkyfile USING(magic_id)
    122 JOIN diffRun USING(diff_id)
    123 JOIN diffSkyfile USING(diff_id)
    124 JOIN diffInputSkyfile USING(diff_id)
     122JOIN diffSkyfile
     123    ON  magicInputSkyfile.diff_id = diffSkyfile.diff_id
     124    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    125125LEFT JOIN magicDSFile
    126126    ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    127     AND magicDSFile.component = diffInputSkyfile.skycell_id
     127    AND magicDSFile.component = diffSkyfile.skycell_id
    128128WHERE
    129129    magicDSRun.state = 'run'
    130130    AND magicDSRun.stage = 'diff'
    131     AND diffRun.state = 'full'
    132131    AND diffSkyfile.fault = 0
    133132    AND magicDSFile.component IS NULL

  • trunk/ippTools/share/magictool_definebyquery_insert.sql

    r18700 r20973  
    55    diff_id,
    66    skycell_id
    7 FROM magicBestDiffs
     7FROM diffSkyfile
    88WHERE
    9     exp_id = @EXP_ID@ -- Update this with the appropriate exp_id
     9    diff_id = @DIFF_ID@ -- Update this with the appropriate diff_id
     10    AND fault = 0

  • trunk/ippTools/share/magictool_definebyquery_select.sql

    r20742 r20973  
    11-- Get a list of exposures on which magic may be performed
    2 SELECT DISTINCT
     2SELECT
    33    exp_id,
    4     filter,
    5     num_todo,
    6     num_done,
    7     MAX(magic_id)
    8 FROM (
    9     -- Number of skycells as a function of exposure
    10     SELECT
    11         exp_id,
    12         filter,
    13         COUNT(DISTINCT warpSkyfile.tess_id,warpSkyfile.skycell_id) AS num_todo
    14     FROM rawExp
    15     JOIN chipRun USING(exp_id)
    16     JOIN camRun USING(chip_id)
    17     JOIN fakeRun USING(cam_id)
    18     JOIN warpRun USING(fake_id)
    19     JOIN warpSkyCellMap USING(warp_id)
    20     JOIN warpSkyfile USING(warp_id, skycell_id)
    21     JOIN diffInputSkyfile USING(warp_id,skycell_id)
    22     JOIN diffRun USING(diff_id)
    23     WHERE
    24         warpSkyfile.ignored = 0
    25         -- magicSkycellNums WHERE hook %s
    26     GROUP BY
    27         exp_id
    28     ) AS magicSkycellNums
    29 JOIN (
    30     -- Number of completed diffs for an exposure
    31     SELECT
    32         exp_id,
    33         COUNT(diff_id) AS num_done
    34     FROM magicBestDiffs
    35     GROUP BY
    36         exp_id
    37     ) AS magicDiffNums USING(exp_id)
     4    MAX(diffRun.diff_id) AS diff_id
     5FROM diffRun
     6JOIN rawExp USING(exp_id)
    387LEFT JOIN magicRun USING(exp_id)
     8-- WHERE hook %s
     9GROUP BY exp_id

  • trunk/ippTools/share/magictool_definebyquery_temp_insert.sql

    r20738 r20973  
    1414JOIN warpSkyfile USING(warp_id, skycell_id)
    1515JOIN diffInputSkyfile
    16     ON diffInputSkyfile.warp_id = warpSkyfile.warp_id
     16    ON diffInputSkyfile.warp1 = warpSkyfile.warp_id
    1717    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
    18     AND diffInputSkyfile.template = 0 -- selecting inputs only
    1918JOIN diffRun USING(diff_id)
    2019JOIN diffSkyfile USING(diff_id)

  • trunk/ippTools/share/magictool_inputs.sql

    r20695 r20973  
    1010    diffSkyfile.path_base,
    1111    diffSkyfile.fault
    12 FROM magicInputSkyfile
     12FROM magicRun
     13JOIN magicInputSkyfile
     14USING(magic_id)
    1315JOIN diffSkyfile
    14     USING(diff_id)
    15 JOIN magicRun
    16     USING(magic_id)
     16    ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     17    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    1718UNION
    1819-- Merged skycells
    … …  
    2122    magicRun.state,
    2223    magicTree.node,
    23     0,
     24    0,   -- no diff_id
    2425    magicNodeResult.uri,
    2526    NULL, -- magicNodeResult doesn't have a path_base

  • trunk/ippTools/share/magictool_inputskyfile.sql

    r20696 r20973  
    22    magicInputSkyfile.*,
    33    diffSkyfile.uri
    4 FROM magicInputSkyfile
    5 JOIN magicRun
     4FROM magicRun
     5JOIN magicInputSkyfile
    66    USING(magic_id)
    77JOIN diffSkyfile
    8     USING(diff_id)
     8    ON magicInputSkyfile.diff_id = diffSkyfile.diff_id
     9    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    910WHERE
    1011    magicRun.state = 'run'

  • trunk/ippTools/share/magictool_toprocess_inputs.sql

    r20697 r20973  
    1111JOIN magicRun USING(magic_id)
    1212JOIN magicInputSkyfile USING(magic_id, node)
    13 JOIN diffSkyfile USING(diff_id)
     13JOIN diffSkyfile
     14    ON magicRun.diff_id = diffSkyfile.diff_id
     15    AND magicInputSkyfile.node = diffSkyfile.skycell_id
    1416JOIN rawExp USING(exp_id)
    1517LEFT JOIN magicNodeResult

  • trunk/ippTools/share/pxadmin_create_tables.sql

    r20841 r20973  
    238238    fault SMALLINT NOT NULL,
    239239    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     240    raw_image_id BIGINT AUTO_INCREMENT,
     241    magicked TINYINT,
    240242    PRIMARY KEY(exp_id, class_id),
    241243    KEY(tmp_class_id),
    242244    KEY(fault),
     245    KEY(raw_image_id),
    243246    UNIQUE KEY(exp_id, tmp_class_id),
    244247    FOREIGN KEY (exp_id, tmp_class_id)
    … …  
    330333    path_base VARCHAR(255),
    331334    fault SMALLINT NOT NULL,
     335    chip_image_id BIGINT AUTO_INCREMENT,
     336    magicked BIGINT,
    332337    PRIMARY KEY(chip_id, exp_id, class_id),
    333338    KEY(data_state),
    334339    KEY(fault),
     340    KEY(chip_image_id),
    335341    FOREIGN KEY (chip_id, exp_id)
    336342        REFERENCES  chipRun(chip_id, exp_id),
    … …  
    815821    end_stage VARCHAR(64),
    816822    registered DATETIME,
    817     magiced TINYINT,
    818823    PRIMARY KEY(warp_id),
    819824    KEY(warp_id),
    … …  
    824829    KEY(label),
    825830    KEY(end_stage),
    826     KEY(magiced),
    827831    INDEX(warp_id, fake_id),
    828832    FOREIGN KEY (fake_id)
    … …  
    861865    ignored TINYINT,
    862866    fault SMALLINT,
     867    warp_image_id BIGINT AUTO_INCREMENT,
     868    magicked TINYINT,
    863869    PRIMARY KEY(warp_id, skycell_id, tess_id),
    864870    KEY(good_frac),
    865871    KEY(ignored), KEY(fault),
     872    KEY(warp_image_id),
    866873    FOREIGN KEY (warp_id, skycell_id, tess_id)
    867874        REFERENCES warpSkyCellMap(warp_id, skycell_id, tess_id)
    … …  
    941948        dvodb VARCHAR(255),
    942949        registered DATETIME,
    943         skycell_id VARCHAR(64),
    944950        tess_id VARCHAR(64),
     951        exp_id  BIGINT,
    945952        PRIMARY KEY(diff_id),
    946953        KEY(diff_id),
    947954        KEY(state),
    948         KEY(skycell_id),
    949         KEY(tess_id)
     955        KEY(tess_id),
     956        FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id)
    950957) ENGINE=innodb DEFAULT CHARSET=latin1;
    951958
    952959CREATE TABLE diffInputSkyfile (
    953960        diff_id BIGINT,
    954         template TINYINT,
    955         stack_id BIGINT,
    956         warp_id BIGINT,
    957961        skycell_id VARCHAR(64),
     962        warp1 BIGINT,
     963        stack1 BIGINT,
     964        warp2 BIGINT,
     965        stack2 BIGINT,
    958966        tess_id VARCHAR(64),
    959         kind VARCHAR(64),
    960         PRIMARY KEY(diff_id, template),
    961         KEY(stack_id),
    962         KEY(warp_id),
     967        PRIMARY KEY(diff_id, skycell_id),
     968        KEY(warp1),
     969        KEY(warp2),
     970        KEY(stack1),
     971        KEY(stack2),
    963972        KEY(skycell_id),
    964973        KEY(tess_id),
    965         KEY(kind),
    966974        FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id),
    967         FOREIGN KEY (stack_id)  REFERENCES  stackSumSkyfile(stack_id),
    968         FOREIGN KEY (warp_id, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id)
     975        FOREIGN KEY (warp1, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id),
     976        FOREIGN KEY (warp2, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id),
     977        FOREIGN KEY (stack1)  REFERENCES  stackSumSkyfile(stack_id),
     978        FOREIGN KEY (stack2)  REFERENCES  stackSumSkyfile(stack_id)
    969979) ENGINE=innodb DEFAULT CHARSET=latin1;
    970980
    971981CREATE TABLE diffSkyfile (
    972982        diff_id BIGINT,
     983        skycell_id VARCHAR(64),
    973984        uri VARCHAR(255),
    974985        path_base VARCHAR(255),
    … …  
    9931004        good_frac FLOAT,
    9941005        fault SMALLINT,
    995         PRIMARY KEY(diff_id),
     1006        diff_image_id BIGINT AUTO_INCREMENT,
     1007        magicked TINYINT,
     1008        PRIMARY KEY(diff_id, skycell_id),
    9961009        KEY(good_frac),
    9971010        KEY(fault),
     1011        KEY(diff_image_id),
    9981012        FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)
    9991013) ENGINE=innodb DEFAULT CHARSET=latin1;
    … …  
    10021016        magic_id BIGINT AUTO_INCREMENT,
    10031017        exp_id BIGINT,
     1018        diff_id BIGINT,
    10041019        state VARCHAR(64),
    10051020        workdir VARCHAR(255),
    … …  
    10151030        KEY(label),
    10161031        KEY(fault),
    1017         FOREIGN KEY (exp_id)  REFERENCES  rawExp(exp_id)
     1032        FOREIGN KEY (exp_id)  REFERENCES rawExp(exp_id),
     1033        FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
    10181034) ENGINE=innodb DEFAULT CHARSET=latin1;
    10191035
    … …  
    10221038        diff_id BIGINT,
    10231039        node VARCHAR(64),
    1024         PRIMARY KEY(magic_id, diff_id),
     1040        PRIMARY KEY(magic_id, diff_id, node),
    10251041        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id),
    1026         FOREIGN KEY (diff_id)  REFERENCES diffRun(diff_id)
     1042        FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
    10271043) ENGINE=innodb DEFAULT CHARSET=latin1;
    10281044

  • trunk/ippTools/share/warptool_tooverlap.sql

    r19092 r20973  
    77    rawExp.camera,
    88    exp_id,
    9     exp_tag,
    10     warpRun.magiced
     9    exp_tag
    1110FROM warpRun
    1211JOIN fakeRun
Note: See TracChangeset for help on using the changeset viewer.