IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 31038 for trunk/ippTools/share


Ignore:
Timestamp:
Mar 24, 2011, 2:09:53 PM (15 years ago)
Author:
watersc1
Message:

implementation of summit_id index in summit{Exp,Imfile},pzDownload{Exp,Imfile},newExp tables.

Location:
trunk/ippTools/share
Files:
6 edited

Legend:

Unmodified
Added
Removed

  • trunk/ippTools/share/pxadmin_create_tables.sql

    r30945 r31038  
    1414
    1515CREATE TABLE summitExp (
     16    summit_id BIGINT NOT NULL AUTO_INCREMENT,
    1617    exp_name VARCHAR(64),
    1718    camera VARCHAR(64),
    … …  
    2324    fault SMALLINT NOT NULL,
    2425    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    25     PRIMARY KEY(exp_name, camera, telescope),
     26    PRIMARY KEY(summit_id),
     27    KEY(exp_name, camera, telescope),
    2628    KEY(fault)
    2729) ENGINE=innodb DEFAULT CHARSET=latin1;
    2830
    2931CREATE TABLE summitImfile (
     32    summit_id BIGINT,
    3033    exp_name VARCHAR(64),
    3134    camera VARCHAR(64),
    … …  
    3841    uri VARCHAR(255),
    3942    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    40     PRIMARY KEY(exp_name, camera, telescope, class, class_id),
     43    PRIMARY KEY(summit_id, class, class_id),
     44    KEY(exp_name, camera, telescope, class, class_id),
    4145    KEY(file_id),
     46    FOREIGN KEY(summit_id) REFERENCES summitExp(summit_id),
    4247    FOREIGN KEY(exp_name, camera, telescope) REFERENCES summitExp(exp_name, camera, telescope)
    4348) ENGINE=innodb DEFAULT CHARSET=latin1;
    4449
    4550CREATE TABLE pzDownloadExp (
     51    summit_id BIGINT,
    4652    exp_name VARCHAR(64),
    4753    camera VARCHAR(64),
    … …  
    4955    state VARCHAR(64),
    5056    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    51     PRIMARY KEY(exp_name, camera, telescope),
     57    PRIMARY KEY(summit_id),
     58    KEY(exp_name, camera, telescope),
    5259    KEY(state),
     60    FOREIGN KEY(summit_id) REFERENCES summitExp(summit_id),
    5361    FOREIGN KEY(exp_name, camera, telescope) REFERENCES summitExp(exp_name, camera, telescope)
    5462) ENGINE=innodb DEFAULT CHARSET=latin1;
    5563
    5664CREATE TABLE pzDownloadImfile (
     65    summit_id BIGINT,
    5766    exp_name VARCHAR(64),
    5867    camera VARCHAR(64),
    … …  
    6675    bytes INT,
    6776    md5sum VARCHAR(32),
    68     PRIMARY KEY(exp_name, camera, telescope, class, class_id),
    69     KEY(fault),
     77    PRIMARY KEY(summit_id, class, class_id),
     78    KEY(exp_name, camera, telescope, class, class_id),
     79    KEY(fault),
     80    FOREIGN KEY(summit_id) REFERENCES pzDownloadExp(summit_id),
     81    FOREIGN KEY(summit_id, class, class_id) REFERENCES summitImfile(summit_id, class, class_id),
    7082    FOREIGN KEY (exp_name, camera, telescope) REFERENCES pzDownloadExp(exp_name, camera, telescope),
    7183    FOREIGN KEY(exp_name, camera, telescope, class, class_id) REFERENCES summitImfile(exp_name, camera, telescope, class, class_id)
    … …  
    7486CREATE TABLE newExp (
    7587    exp_id BIGINT AUTO_INCREMENT,
     88    summit_id BIGINT,
    7689    tmp_exp_name VARCHAR(64),
    7790    tmp_camera VARCHAR(64),
    … …  
    88101    PRIMARY KEY(exp_id),
    89102    KEY(exp_id),
     103    KEY(summit_id),
    90104    KEY(tmp_exp_name),
    91105    KEY(tmp_camera),

  • trunk/ippTools/share/pztool_find_completed_exp.sql

    r23643 r31038  
    11SELECT DISTINCT
     2    summit_id,
    23    exp_name,
    34    camera,
    … …  
    1314    FROM pzDownloadExp
    1415    JOIN summitExp
    15         USING(exp_name, camera, telescope)
     16        USING(summit_id)
    1617    LEFT JOIN pzDownloadImfile
    17         USING(exp_name, camera, telescope)
     18        USING(summit_id)
    1819    LEFT JOIN newExp
    19         ON pzDownloadExp.exp_name = newExp.tmp_exp_name
    20         AND pzDownloadExp.camera = newExp.tmp_camera
    21         AND pzDownloadExp.telescope = newExp.tmp_telescope
     20        USING(summit_id)
    2221    WHERE
    2322        pzDownloadExp.state = 'run'
     23        AND newExp.summit_id IS NULL
    2424        AND newExp.tmp_exp_name IS NULL
    2525        AND newExp.tmp_camera IS NULL
    2626        AND newExp.tmp_telescope IS NULL
    2727    GROUP BY
    28         pzDownloadExp.exp_name,
    29         pzDownloadExp.camera,
    30         pzDownloadExp.telescope
     28        pzDownloadExp.summit_id,
    3129    -- it doesn't matter which field in pzDownloadImfile we count as we've
    3230    -- already Download a group by

  • trunk/ippTools/share/pztool_pendingimfile.sql

    r18305 r31038  
    66    FROM summitImfile
    77    JOIN pzDownloadExp
    8         USING(exp_name, camera, telescope)
     8        USING(summit_id)
    99    LEFT JOIN pzDownloadImfile
    10         USING(exp_name, camera, telescope, class, class_id)
     10        USING(summit_id, class, class_id)
    1111    JOIN summitExp
    12         USING(exp_name, camera, telescope)
     12        USING(summit_id)
    1313    WHERE
    1414        pzDownloadExp.state = 'run'
     15        AND pzDownloadImfile.summit_id IS NULL
    1516        AND pzDownloadImfile.exp_name IS NULL
    1617        AND pzDownloadImfile.camera IS NULL

  • trunk/ippTools/share/regtool_checkburntoolimfile.sql

    r30283 r31038  
    2121       FROM
    2222       summitExp
    23        JOIN summitImfile USING(exp_name)
    24        LEFT JOIN pzDownloadExp USING(exp_name)
    25        LEFT JOIN newExp ON summitExp.exp_name = tmp_exp_name
     23       JOIN summitImfile USING(summit_id)
     24       LEFT JOIN pzDownloadExp USING(summit_id)
     25       LEFT JOIN newExp USING(summit_id)
    2626       LEFT JOIN newImfile ON (summitImfile.class_id = newImfile.tmp_class_id AND newExp.exp_id = newImfile.exp_id)
    2727       LEFT JOIN rawExp ON rawExp.exp_id = newExp.exp_id

  • trunk/ippTools/share/regtool_checkstatus.sql

    r30997 r31038  
    2020        (rawImfile.burntool_state IS NOT NULL) AS is_registered
    2121        FROM        summitExp
    22         JOIN summitImfile USING(exp_name)
    23         LEFT JOIN pzDownloadExp USING(exp_name)
    24         LEFT JOIN newExp ON summitExp.exp_name = tmp_exp_name
     22        JOIN summitImfile USING(summit_id)
     23        LEFT JOIN pzDownloadExp USING(summit_id)
     24        LEFT JOIN newExp USING(summit_id)
    2525        LEFT JOIN newImfile ON (summitImfile.class_id = newImfile.tmp_class_id AND newExp.exp_id = newImfile.exp_id)
    2626        LEFT JOIN rawExp ON rawExp.exp_id = newExp.exp_id

  • trunk/ippTools/share/regtool_pendingburntoolimfile.sql

    r30283 r31038  
    2222       FROM
    2323       summitExp
    24        JOIN summitImfile USING(exp_name)
    25        LEFT JOIN pzDownloadExp USING(exp_name)
    26        LEFT JOIN newExp ON summitExp.exp_name = tmp_exp_name
     24       JOIN summitImfile USING(summit_id)
     25       LEFT JOIN pzDownloadExp USING(summit_id)
     26       LEFT JOIN newExp USING(summit_id)
    2727       LEFT JOIN newImfile ON (summitImfile.class_id = newImfile.tmp_class_id AND newExp.exp_id = newImfile.exp_id)
    2828       LEFT JOIN rawExp ON rawExp.exp_id = newExp.exp_id
Note: See TracChangeset for help on using the changeset viewer.