Changeset 35227

Timestamp:

Mar 4, 2013, 2:08:07 PM (13 years ago)

Author:

bills

Message:

updates to release tables. This version was used to create the
tables in gpc1

Location:

trunk

Files:

• dbconfig/changes.txt (modified) (6 diffs)
• ippTools/share/pxadmin_create_tables.sql (modified) (6 diffs)
• ippTools/share/pxadmin_drop_tables.sql (modified) (1 diff)
• ippTools/src/releasetool.c (modified) (2 diffs)
• ippTools/src/releasetoolConfig.c (modified) (1 diff)

trunk/dbconfig/changes.txt

@@ -2272 +2272 @@
 UPDATE dbversion set schema_version = '1.1.74', updated= CURRENT_TIMESTAMP();
 
+
+-- Note the following 3 changes were implemented in the ipp-20121218 tag
+-- without changing the schema_version from 1.1.74
+
+-- Add cam_id to chipBackgroundRun
+ALTER TABLE chipBackgroundRun ADD COLUMN cam_id BIGINT DEFAULT 0 AFTER chip_id;
+
+-- add fwhm measuements to skycalResult
+ALTER TABLE skycalResult ADD COLUMN fwhm_major FLOAT AFTER zpt_stdev;
+ALTER TABLE skycalResult ADD COLUMN fwhm_minor FLOAT AFTER fwhm_major;
+
 -- new tables for managing the "release" of ipp data
 
 CREATE TABLE survey (
-    surveyID    INT(8),
+    surveyID    INT,
     surveyName  VARCHAR(16) NOT NULL,
     description VARCHAR(255),
@@ -2282 +2293 @@
 ) ENGINE=InnoDB CHARSET=latin1;
 
-
 CREATE TABLE ippRelease (
-    rel_id      INT(8) AUTO_INCREMENT,
-    surveyID    INT(8),
-    releaseName VARCHAR(64),
-    release_state VARCHAR(16),    -- active, pending archive drop 
-    dataRelease INT(8),         -- PSPS dataRelease
+    rel_id      INT AUTO_INCREMENT,
+    surveyID    INT,
+    release_name VARCHAR(64),
+    release_state VARCHAR(16),   -- active, pending, archive, drop 
+    dataRelease INT,         -- PSPS dataRelease
+    priority    INT,
     PRIMARY KEY(rel_id),
-    KEY(releaseName),
-    KEY(state),
+    KEY(release_name),
+    KEY(release_state),
+    KEY(priority),
     FOREIGN KEY(surveyID) REFERENCES survey(surveyID)
 ) ENGINE=Innodb DEFAULT CHARSET=latin1;
@@ -2297 +2309 @@
 CREATE TABLE relExp (
     relexp_id   BIGINT AUTO_INCREMENT,
-    rel_id      INT(8),
+    rel_id      INT,
     exp_id      BIGINT,
     chip_id     BIGINT,         -- links to the runs that supplied the data
     cam_id      BIGINT,         -- for this release
+    group_id    INT,         -- id of relGroup that this exposure is contained in
     state       VARCHAR(16),    -- released, pending, archive, drop  
-    flags       INT UNSIGNED,   -- flags for relphot, relastro, ??
+    flags       INT UNSIGNED,  -- flags for relphot, relastro, ??
     zpt_obs     FLOAT,          -- calibrated zero point for this release of
     zpt_stdev   FLOAT,          -- this exposure
@@ -2321 +2334 @@
 CREATE TABLE relStack (
     relstack_id BIGINT AUTO_INCREMENT,
-    rel_id      INT(8),
+    rel_id      INT,
     stack_id    BIGINT,         -- id of the stackRun
     skycal_id   BIGINT,         -- id of the sky calibration run that supplied the calibration
@@ -2333 +2346 @@
     zpt_obs     FLOAT,          -- calibrated zero point for this release of this skycell
     zpt_stdev   FLOAT,
-    mjd_obs     INT UNSIGNED,   -- for nightly stacks the mjd_obs
+    mjd_obs     INT UNSIGNED,   -- for nightly stacks the mjd_obs, zero for deep and reference
     path_base   VARCHAR(255),   -- path_base of any supporting files,
     fault SMALLINT NOT NULL,
@@ -2349 +2362 @@
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
--- Add cam_id to chipBackgroundRun
-ALTER TABLE chipBackgroundRun ADD COLUMN cam_id BIGINT DEFAULT 0 AFTER chip_id;
-
-ALTER TABLE skycalResult ADD COLUMN fwhm_major FLOAT AFTER zpt_stdev;
-ALTER TABLE skycalResult ADD COLUMN fwhm_minor FLOAT AFTER fwhm_major;
+CREATE TABLE relGroup (
+    group_id    INT AUTO_INCREMENT,
+    rel_id      INT,
+    group_type  VARCHAR(16),
+    lap_id      BIGINT,
+    group_name  VARCHAR(16),
+    state       VARCHAR(16),    -- new full
+    label       VARCHAR(64),
+    exp_list_path   VARCHAR(255),
+    fault SMALLINT NOT NULL,
+    registered  DATETIME,
+    PRIMARY KEY (group_id),
+    KEY (rel_id, group_name),
+    KEY (group_type),
+    KEY (lap_id),
+    KEY (group_name),
+    KEY (state),
+    KEY (label),
+    KEY (fault),
+    FOREIGN KEY(rel_id) REFERENCES ippRelease(rel_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+-- new table grouping multi filter lap runs into a group. This is used to
+-- determine when all 5 filters for a projection cell have completed processing
+CREATE TABLE lapGroup  (
+    seq_id  BIGINT,
+    projection_cell VARCHAR(64),
+    state   VARCHAR(16),
+    fault   SMALLINT,
+    PRIMARY KEY(seq_id, projection_cell),
+    KEY(state),
+    KEY(fault),
+    FOREIGN KEY(seq_id) REFERENCES lapSequence(seq_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
 
 UPDATE dbversion set schema_version = '1.1.75', updated= CURRENT_TIMESTAMP();
+

trunk/ippTools/share/pxadmin_create_tables.sql

@@ -2153 +2153 @@
 
 CREATE TABLE survey (
-    surveyID    INT(8),
+    surveyID    INT,
     surveyName  VARCHAR(16) NOT NULL,
     description VARCHAR(255),
     PRIMARY KEY(surveyID),
-    UNIQUE  KEY(name)
+    UNIQUE  KEY(surveyName)
 ) ENGINE=InnoDB CHARSET=latin1;
 
-
 CREATE TABLE ippRelease (
-    rel_id      INT(8) AUTO_INCREMENT,
-    surveyID    INT(8),
+    rel_id      INT AUTO_INCREMENT,
+    surveyID    INT,
     release_name VARCHAR(64),
-    relase_state VARCHAR(16),   -- active, pending, archive, drop 
-    dataRelease INT(8),         -- PSPS dataRelease
-    priority    INT(8),
+    release_state VARCHAR(16),   -- active, pending, archive, drop 
+    dataRelease INT,         -- PSPS dataRelease
+    priority    INT,
     PRIMARY KEY(rel_id),
     KEY(release_name),
-    KEY(state),
+    KEY(release_state),
     KEY(priority),
     FOREIGN KEY(surveyID) REFERENCES survey(surveyID)
@@ -2177 +2176 @@
 CREATE TABLE relExp (
     relexp_id   BIGINT AUTO_INCREMENT,
-    rel_id      INT(8),
+    rel_id      INT,
     exp_id      BIGINT,
     chip_id     BIGINT,         -- links to the runs that supplied the data
     cam_id      BIGINT,         -- for this release
-    group_id    INT(8),         -- id of relGroup that this exposure is contained in
+    group_id    INT,         -- id of relGroup that this exposure is contained in
     state       VARCHAR(16),    -- released, pending, archive, drop  
-    flags       INT UNSIGNED,   -- flags for relphot, relastro, ??
+    flags       INT UNSIGNED,  -- flags for relphot, relastro, ??
     zpt_obs     FLOAT,          -- calibrated zero point for this release of
     zpt_stdev   FLOAT,          -- this exposure
@@ -2202 +2201 @@
 CREATE TABLE relStack (
     relstack_id BIGINT AUTO_INCREMENT,
-    rel_id      INT(8),
+    rel_id      INT,
     stack_id    BIGINT,         -- id of the stackRun
     skycal_id   BIGINT,         -- id of the sky calibration run that supplied the calibration
@@ -2214 +2213 @@
     zpt_obs     FLOAT,          -- calibrated zero point for this release of this skycell
     zpt_stdev   FLOAT,
-    mjd_obs     INT UNSIGNED,   -- for nightly stacks the mjd_obs
+    mjd_obs     INT UNSIGNED,   -- for nightly stacks the mjd_obs, zero for deep and reference
     path_base   VARCHAR(255),   -- path_base of any supporting files,
     fault SMALLINT NOT NULL,
@@ -2231 +2230 @@
 
 CREATE TABLE relGroup (
-    group_id    INT(8) AUTO_INCREMENT,
-    rel_id      INT(8),
+    group_id    INT AUTO_INCREMENT,
+    rel_id      INT,
     group_type  VARCHAR(16),
     lap_id      BIGINT,
@@ -2252 +2251 @@
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
+CREATE TABLE lapGroup  (
+    seq_id  BIGINT,
+    projection_cell VARCHAR(64),
+    state   VARCHAR(16),
+    fault   SMALLINT,
+    PRIMARY KEY(seq_id, projection_cell),
+    KEY(state),
+    KEY(fault),
+    FOREIGN KEY(seq_id) REFERENCES lapSequence(seq_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
 
 

trunk/ippTools/share/pxadmin_drop_tables.sql

@@ -109 +109 @@
 DROP TABLE IF EXISTS skycalResult;
 DROP TABLE IF EXISTS skycell;
+DROP TABLE IF EXISTS relStack;
 DROP TABLE IF EXISTS relExp;
+DROP TABLE IF EXISTS relGroup;
 DROP TABLE IF EXISTS ippRelease;
 DROP TABLE IF EXISTS survey;
+DROP TABLE IF EXISTS lapGroup;
 
 SET FOREIGN_KEY_CHECKS=1

trunk/ippTools/src/releasetool.c

@@ -214 +214 @@
     PXOPT_LOOKUP_STR(release_state,    config->args, "-set_release_state",  false, false);
     PXOPT_LOOKUP_S32(priority,         config->args, "-set_priority",  false, false);
-    if (!release_state && !priority) {
-        psError(PXTOOLS_ERR_CONFIG, true, "at least one of -set_release_staate and -set_priority is required\n");
+    PXOPT_LOOKUP_S32(dataRelease,      config->args, "-set_dataRelease",  false, false);
+    if (!release_state && priority == 0 && dataRelease == -1) {
+        psError(PXTOOLS_ERR_CONFIG, true, "at least one of -set_release_state -set_priority and -set_dataRelease is required\n");
         return false;
     }
@@ -233 +234 @@
     if (priority != 0) {
         psStringAppend(&query, "%s priority = %d", sep, priority);
+        sep = comma;
+    }
+    if (dataRelease != -1) {
+        psStringAppend(&query, "%s dataRelease = %d", sep, dataRelease);
         sep = comma;
     }

trunk/ippTools/src/releasetoolConfig.c

@@ -74 +74 @@
     psMetadataAddStr(updatereleaseArgs, PS_LIST_TAIL, "-set_release_state", 0,  "define new release state", NULL);
     psMetadataAddS32(updatereleaseArgs, PS_LIST_TAIL, "-set_priority", 0,       "define new release priority", 0);
-
-    // Only support changing state and priority for now
-//    psMetadataAddS32(updatereleaseArgs, PS_LIST_TAIL, "-set_surveyID", 0,       "define surveyID (required)", 0);
-//    psMetadataAddStr(updatereleaseArgs, PS_LIST_TAIL, "-set_release_name", 0,    "define release name (required)", NULL);
-//    psMetadataAddS32(updatereleaseArgs, PS_LIST_TAIL, "-set_dataRelease", 0,    "define dataRelease", -1);
+    psMetadataAddS32(updatereleaseArgs, PS_LIST_TAIL, "-set_dataRelease", 0,    "define data release", -1);