IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 20841 for trunk/ippTools/share


Ignore:
Timestamp:
Nov 25, 2008, 5:21:13 PM (18 years ago)
Author:
bills
Message:

Add the stage_id and cam_id to magicDSRun. This vastly reduces the
number of JOINs that we have to do

Location:
trunk/ippTools/share
Files:
4 edited

Legend:

Unmodified
Added
Removed

  • trunk/ippTools/share/Makefile.am

    r20787 r20841  
    120120     magicdstool_completed_runs.sql \
    121121     magicdstool_getskycells.sql \
     122     magicdstool_getrunids.sql \
    122123     pstamptool_addjob_otherjob.sql \
    123124     pstamptool_addjob_stampjob.sql \

  • trunk/ippTools/share/magicdstool_completed_runs.sql

    r20792 r20841  
    77    magicDSRun.magic_ds_id
    88    FROM magicDSRun
    9     JOIN magicRun USING (magic_id)
    10     JOIN magicInputSkyfile USING(magic_id)
    11     JOIN diffRun USING(diff_id)
    12     JOIN diffInputSkyfile
    13         ON diffInputSkyfile.diff_id = diffRun.diff_id
    14         AND diffInputSkyfile.skycell_id = diffRun.skycell_id
    15         -- Want input warps only AND diffInputSkyfile.warp_id IS NOT NULL
    16         AND diffInputSkyfile.template = 0
    17     JOIN warpRun USING(warp_id)
    18     JOIN fakeRun USING(fake_id)
    19     JOIN camRun USING(cam_id)
    20     JOIN camProcessedExp USING(cam_id)
    21     JOIN rawImfile ON magicRun.exp_id = rawImfile.exp_id
     9    JOIN rawImfile ON stage_id = rawImfile.exp_id
    2210    LEFT JOIN magicDSFile
    2311        ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    … …  
    3725    magicDSRun.magic_ds_id
    3826    FROM magicDSRun
    39     JOIN magicRun USING(magic_id)
    40     JOIN magicInputSkyfile USING(magic_id)
    41     JOIN diffRun USING(diff_id)
    42     JOIN diffInputSkyfile
    43         ON diffInputSkyfile.diff_id = diffRun.diff_id
    44         AND diffInputSkyfile.skycell_id = diffRun.skycell_id
    45         -- Want input warps only
    46         AND diffInputSkyfile.warp_id IS NOT NULL
    47         AND diffInputSkyfile.template = 0
    48     JOIN warpRun USING(warp_id)
    49     JOIN fakeRun USING(fake_id)
    50     JOIN camRun USING(cam_id)
    51     JOIN camProcessedExp USING(cam_id)
    52     JOIN chipRun USING(chip_id)
    53     JOIN chipProcessedImfile USING(chip_id)
    54 
     27    JOIN chipProcessedImfile ON stage_id = chip_id
    5528    LEFT JOIN magicDSFile
    5629        ON magicDSFile.magic_ds_id = magicDSRun.magic_ds_id
    … …  
    6033        AND magicDSRun.stage = 'chip'
    6134    GROUP BY
    62         magicDSRun.magic_ds_id,
    63         chipRun.chip_id
     35        magic_ds_id,
     36        chip_id
    6437    HAVING
    6538        COUNT(chipProcessedImfile.class_id) = COUNT(magicDSFile.component)
    … …  
    7043    magicDSRun.magic_ds_id
    7144    FROM magicDSRun
    72     JOIN magicRun USING (magic_id)
    73     JOIN magicInputSkyfile USING(magic_id)
    74     JOIN diffRun USING(diff_id)
    75     JOIN diffInputSkyfile
    76         ON diffInputSkyfile.diff_id = diffRun.diff_id
    77         AND diffInputSkyfile.skycell_id = diffRun.skycell_id
    78         -- Want input warps only
    79         AND diffInputSkyfile.warp_id IS NOT NULL
    80         AND diffInputSkyfile.template = 0
    81     JOIN warpSkyfile using(warp_id)
    82     JOIN warpRun USING(warp_id)
     45    JOIN warpSkyfile on stage_id = warp_id
    8346    LEFT JOIN magicDSFile
    8447        ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    … …  
    9154    GROUP BY
    9255        magicDSRun.magic_ds_id,
    93         warpRun.warp_id
     56        warp_id
    9457    HAVING
    9558        COUNT(warpSkyfile.skycell_id) = COUNT(magicDSFile.component)
    … …  
    10265    JOIN magicRun USING (magic_id)
    10366    JOIN magicInputSkyfile USING(magic_id)
     67    -- Do we really need to join back to diffInputSkyfile here?
    10468    JOIN diffRun USING(diff_id)
    10569    JOIN diffSkyfile USING(diff_id)

  • trunk/ippTools/share/magicdstool_todestreak.sql

    r20792 r20841  
    88    magicMask.uri as streaks_uri,
    99    stage,
    10     rawImfile.exp_id as stage_id,
     10    stage_id,
    1111    class_id as component,
    1212    rawImfile.uri as uri,
    … …  
    2020JOIN magicMask USING (magic_id)
    2121JOIN magicRun USING (magic_id)
    22 JOIN magicInputSkyfile USING(magic_id)
    23 JOIN diffRun USING(diff_id)
    24 JOIN diffInputSkyfile
    25     ON diffInputSkyfile.diff_id = diffRun.diff_id
    26     AND diffInputSkyfile.skycell_id = diffRun.skycell_id
    27     -- Want input warps only AND diffInputSkyfile.warp_id IS NOT NULL
    28     AND diffInputSkyfile.template = 0
    29 JOIN warpRun USING(warp_id)
    30 JOIN fakeRun USING(fake_id)
    31 JOIN camRun USING(cam_id)
    3222JOIN camProcessedExp USING(cam_id)
    3323JOIN rawImfile ON magicRun.exp_id = rawImfile.exp_id
    … …  
    4333SELECT DISTINCT
    4434    magicDSRun.magic_ds_id,
    45     magicRun.magic_id,
    46     magicRun.exp_id,
     35    magicDSRun.magic_id,
     36    chipRun.exp_id,
    4737    camera,
    4838    magicMask.uri as streaks_uri,
    4939    stage,
    50     chip_id as stage_id,
     40    stage_id,
    5141    class_id as component,
    5242    chipProcessedImfile.uri,
    … …  
    5949FROM magicDSRun
    6050JOIN magicMask USING (magic_id)
    61 JOIN magicRun USING (magic_id)
    62 JOIN magicInputSkyfile USING(magic_id)
    63 JOIN diffRun USING(diff_id)
    64 JOIN diffInputSkyfile
    65     ON diffInputSkyfile.diff_id = diffRun.diff_id
    66     AND diffInputSkyfile.skycell_id = diffRun.skycell_id
    67     -- Want input warps only
    68     AND diffInputSkyfile.warp_id IS NOT NULL
    69     AND diffInputSkyfile.template = 0
    70 JOIN warpRun USING(warp_id)
    71 JOIN fakeRun USING(fake_id)
    72 JOIN camRun USING(cam_id)
    7351JOIN camProcessedExp USING(cam_id)
    74 JOIN chipRun USING(chip_id)
     52JOIN chipRun ON chip_id = stage_id
    7553JOIN chipProcessedImfile USING(chip_id)
    7654JOIN rawExp ON chipRun.exp_id = rawExp.exp_id
    … …  
    9371    magicMask.uri as streaks_uri,
    9472    stage,
    95     warp_id as stage_id,
     73    stage_id,
    9674    warpSkyfile.skycell_id as component,
    9775    warpSkyfile.uri,
    … …  
    10684JOIN magicRun USING (magic_id)
    10785JOIN magicInputSkyfile USING(magic_id)
    108 JOIN diffRun USING(diff_id)
    109 JOIN diffInputSkyfile
    110     ON diffInputSkyfile.diff_id = diffRun.diff_id
    111     AND diffInputSkyfile.skycell_id = diffRun.skycell_id
    112     -- Want input warps only
    113     AND diffInputSkyfile.warp_id IS NOT NULL
    114     AND diffInputSkyfile.template = 0
    115 JOIN warpSkyfile using(warp_id)
     86JOIN warpSkyfile ON warp_id = stage_id
    11687JOIN warpRun USING(warp_id)
    11788JOIN rawExp ON magicRun.exp_id = rawExp.exp_id

  • trunk/ippTools/share/pxadmin_create_tables.sql

    r20786 r20841  
    10641064        state VARCHAR(64),
    10651065        stage VARCHAR(64),
     1066        stage_id BIGINT,
     1067        cam_id BIGINT,
    10661068        outroot VARCHAR(255),
    10671069        recoveryroot VARCHAR(255),
Note: See TracChangeset for help on using the changeset viewer.