IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 20841


Ignore:
Timestamp:
Nov 25, 2008, 5:21:13 PM (17 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
Files:
7 edited

Legend:

Unmodified
Added
Removed

  • trunk/dbconfig/changes.txt

    r20793 r20841  
    713713) ENGINE=innodb DEFAULT CHARSET=latin1;
    714714
     715-- add to the run table to make the queries far less expensive
     716alter table magicDSRun add column stage_id BIGINT after stage
     717alter table magicDSRun add column cam_id BIGINT after stage_id;
     718

  • trunk/dbconfig/magic.md

    r20785 r20841  
    1 # $Id: magic.md,v 1.11 2008-11-18 01:54:01 bills Exp $
     1# $Id: magic.md,v 1.12 2008-11-26 03:21:13 bills Exp $
    22
    33### Fault in magicRun indicates that the processing tree failed
    … …  
    4545    state       STR         0       # Key
    4646    stage       STR         64
     47    stage_id    S64         0
     48    cam_id      S64         0
    4749    outroot     STR         255
    4850    recoveryroot    STR     255

  • 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),

  • trunk/ippTools/src/magicdstool.c

    r20788 r20841  
    4141static bool setmagicDSRunState(pxConfig *config, psS64 magic_id, const char *state);
    4242static bool magicDSRunComplete(pxConfig *config);
     43static bool magicDSGetIDs(pxConfig *config, psString stage, psS64 magic_id, psS64 *stage_id, psS64 *cam_id);
    4344
    4445#ifdef notdef
    … …  
    344345    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    345346
     347    psS64 stage_id, cam_id;
     348
     349    if (!magicDSGetIDs(config, stage, magic_id, &stage_id, &cam_id)) {
     350        psError(PS_ERR_UNKNOWN, false, "failed to get ids");
     351        return false;
     352    }
     353
    346354    magicDSRunRow *run = magicDSRunRowAlloc(
    347355            0,          // ID
    … …  
    349357            "run",      // state
    350358            stage,
    351             outroot,   
     359            stage_id,
     360            cam_id,
     361            outroot,
    352362            recoveryroot,
    353363            re_place,
    … …  
    517527}
    518528
     529static bool magicDSGetIDs(pxConfig *config, psString stage, psS64 magic_id, psS64 *stage_id, psS64 *cam_id)
     530{
     531    PS_ASSERT_PTR_NON_NULL(config, false);
     532    PS_ASSERT_PTR_NON_NULL(stage, false);
     533    PS_ASSERT_PTR_NON_NULL(stage_id, false);
     534    PS_ASSERT_PTR_NON_NULL(cam_id, false);
     535
     536    if (!strcmp(stage, "diff")) {
     537        // don't need these ids for diff stage
     538        *stage_id = 0;
     539        *cam_id = 0;
     540        return true;
     541    }
     542   
     543    int stageNum;
     544    if (!strcmp(stage, "raw")) {
     545        stageNum = 0;
     546    } else if (!strcmp(stage, "chip")) {
     547        stageNum = 1;
     548    } else if (!strcmp(stage, "warp")) {
     549        stageNum = 2;
     550    } else {
     551        psError(PXTOOLS_ERR_DATA, true, "%s is not a valid value for stage", stage);
     552        return false;
     553    }
     554
     555
     556    psString query = pxDataGet("magicdstool_getrunids.sql");
     557    if (!query) {
     558        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
     559        return false;
     560    }
     561
     562    if (!p_psDBRunQuery(config->dbh, query, magic_id)) {
     563        psError(PS_ERR_UNKNOWN, false, "database error");
     564        psFree(query);
     565        return false;
     566    }
     567    psFree(query);
     568
     569    psArray *output = p_psDBFetchResult(config->dbh);
     570    if (!output) {
     571        psError(PS_ERR_UNKNOWN, false, "database error");
     572        return false;
     573    }
     574    if (!psArrayLength(output)) {
     575        psTrace("magicdstool", PS_LOG_INFO, "no rows found");
     576        psFree(output);
     577        return true;
     578    }
     579    if (psArrayLength(output) > 1) {
     580        psError(PS_ERR_UNKNOWN, true, "unexpected number of rows found %ld for magic_id %ld",
     581            psArrayLength(output), magic_id);
     582        return false;
     583    }
     584    psMetadata *row = output->data[0];
     585
     586    *cam_id = psMetadataLookupS64(NULL, row, "cam_id");
     587    if (stageNum == 0) {
     588        *stage_id = psMetadataLookupS64(NULL, row, "exp_id");
     589    } else if (stageNum == 1) {
     590        *stage_id = psMetadataLookupS64(NULL, row, "chip_id");
     591    } else if (stageNum == 2) {
     592        *stage_id = psMetadataLookupS64(NULL, row, "warp_id");
     593    }
     594
     595    return true;
     596}
     597
    519598static bool magicDSRunComplete(pxConfig *config)
    520599{
Note: See TracChangeset for help on using the changeset viewer.