Changeset 35236 for branches/eam_branches/ipp-20130207/ippTools/share/pxadmin_create_tables.sql

Timestamp:

Mar 5, 2013, 2:24:05 PM (13 years ago)

Author:

eugene

Message:

merge changes from trunk

Location:

branches/eam_branches/ipp-20130207

Files:

• . (modified) (1 prop)
• ippTools/share (modified) (1 prop)
• ippTools/share/pxadmin_create_tables.sql (modified) (9 diffs, 1 prop)

branches/eam_branches/ipp-20130207/ippTools/share/pxadmin_create_tables.sql

@@ -521 +521 @@
     deteff_uq FLOAT,
     quality SMALLINT NOT NULL DEFAULT 0,
+    background_model SMALLINT,
     PRIMARY KEY(cam_id),
     KEY(fault),
@@ -1791 +1792 @@
     chip_bg_id BIGINT AUTO_INCREMENT, -- unique identifier
     chip_id BIGINT NOT NULL,          -- link to chipRun
+    cam_id BIGINT DEFAULT 0,          -- optional link to camRun
     state VARCHAR(64) NOT NULL,       -- state of run (new, full, etc.)
     workdir VARCHAR(255) NOT NULL,    -- working directory
@@ -1843 +1845 @@
     data_group VARCHAR(64),           -- group for data
     dist_group VARCHAR(64),           -- group for distribution
-    reduction VARCHAR(64),    -- reduction class (for altering recipe)
+    reduction VARCHAR(64),            -- reduction class (for altering recipe)
     note VARCHAR(255),        -- note
     registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
@@ -2122 +2124 @@
       zpt_obs FLOAT,
       zpt_stdev FLOAT,
+      fwhm_major  FLOAT,
+      fwhm_minor  FLOAT,
       quality SMALLINT NOT NULL,
       hostname VARCHAR(64) NOT NULL,
@@ -2149 +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),
-    releaseName VARCHAR(64),
-    relase_state VARCHAR(16),   -- active, pending, archive, drop 
-    dataRelease INT(8),         -- PSPS dataRelease
-    priority    INT(8),
+    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)
@@ -2173 +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,         -- 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
     path_base   VARCHAR(255),   -- path_base of any supporting files for this
                                 -- release of this exposure.
+    fault SMALLINT NOT NULL,
     registered  DATETIME,       -- insertion time for this row
     time_stamp  DATETIME,       -- time of last update for this row
@@ -2188 +2193 @@
     FOREIGN KEY(rel_id) REFERENCES ippRelease(rel_id),
     KEY (state),
+    KEY (fault),
     FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id),
     FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id),
@@ -2195 +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
-                                -- for this release. We get back to stack from here
-    skycell_id  varchar(64),
-    tess_id     varchar(64),
+                                -- for this release of this skycell.
+    skycell_id  VARCHAR(64),
+    tess_id     VARCHAR(64),
+    filter      VARCHAR(16),
     state       VARCHAR(16),    -- released, pending, archive, drop  
     flags       INT UNSIGNED,
@@ -2205 +2213 @@
     zpt_obs     FLOAT,          -- calibrated zero point for this release of this skycell
     zpt_stdev   FLOAT,
-    fwhm_major  FLOAT,          -- seeing as measured by staticsky
+    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,
     registered  DATETIME,
     time_stamp  DATETIME,
     PRIMARY KEY (relstack_id),
     KEY (tess_id, skycell_id),
-    KEY (rel_id, tess_id, skycell_id),
+    KEY (rel_id, tess_id, skycell_id, filter, mjd_obs),
+    KEY (stack_type),
+    KEY (mjd_obs),
+    KEY (state),
+    KEY (fault),
     FOREIGN KEY(rel_id) REFERENCES ippRelease(rel_id),
-    FOREIGN KEY(skycal_id) REFERENCES skycalRun(skycal_id)
+    FOREIGN KEY(stack_id) REFERENCES stackRun(stack_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+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;
+
+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;