Changeset 42633

Timestamp:

Feb 22, 2024, 9:44:30 AM (2 years ago)

Author:

eugene

Message:

adding update to gpc1

File:

• branches/eam_branches/ipp-20230313/dbconfig/changes.txt (modified) (3 diffs)

branches/eam_branches/ipp-20230313/dbconfig/changes.txt

@@ -2632 +2632 @@
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
+-- EAM 2024.02.20 : adding the following missing fields to gpc2:
+
+-- example ALTER TABLE stackSumSkyfile ADD COLUMN path_base VARCHAR(255) NULL DEFAULT NULL AFTER uri;
+ALTER TABLE chipBackgroundImfile ADD COLUMN data_state VARCHAR(64) AFTER path_base;
+-- ALTER TABLE minidvodbRun ADD COLUMN note VARCHAR(255) AFTER state;
+ALTER TABLE skycalResult CHANGE COLUMN software_ver software_ver VARCHAR(16);
+ALTER TABLE ippRelease ADD COLUMN accessLevelMin INT AFTER ubercal_file;
+
 -- EAM 2023.11.04 : adding xcstackRun, xcstackInputSkyfile, xcstackSumSkyfile
-
+-- EAM 2024.02.20 : the following need to be added to the gpc1 and gpc2 databases
+
+CREATE TABLE xcstackCamera (
+        camera_id BIGINT AUTO_INCREMENT,
+        camera VARCHAR(255),
+        PRIMARY KEY(camera_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
 
 CREATE TABLE xcstackRun (
@@ -2663 +2677 @@
         camera_id BIGINT,
         stack_id BIGINT,
-        PRIMARY KEY(xcstack_id, stack_id),
+        PRIMARY KEY(xcstack_id, camera_id, stack_id),
+        KEY(xcstack_id),
+        KEY(stack_id),
         FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id),
+        FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
@@ -2687 +2704 @@
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
+-- A static sky analysis set
+CREATE TABLE xcskyRun (
+      xcsky_id BIGINT AUTO_INCREMENT, -- unique identifier
+      state VARCHAR(64) NOT NULL,  -- state of run (new, full, etc.)
+      workdir VARCHAR(255) NOT NULL, -- working directory
+      label VARCHAR(64),             -- processing label
+      data_group VARCHAR(64),        -- group for data
+      dist_group VARCHAR(64),        -- group for distribution
+      reduction VARCHAR(64),         -- reduction class (for altering recipe)
+      note VARCHAR(255),             -- note
+      registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
+      PRIMARY KEY(xcsky_id),
+      KEY(state),
+      KEY(label),
+      KEY(data_group),
+      KEY(dist_group)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+-- Inputs for static sky analysis
+CREATE TABLE xcskyInput (
+      xcsky_id BIGINT,             -- static sky identifier
+      xcstack_id BIGINT,          -- stack identifier
+      PRIMARY KEY(xcsky_id,xcstack_id),
+      KEY(xcstack_id),
+      FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id),
+      FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+-- Result of static sky analysis
+CREATE TABLE xcskyResult (
+      xcsky_id BIGINT,             -- static sky identifier
+      path_base VARCHAR(255) NOT NULL, -- root name for outputs
+      dtime_phot FLOAT,                -- elapsed time for photometry
+      dtime_script FLOAT,              -- elapsed time for script
+      sources INT,                     -- number of sources
+      num_inputs INT,                  -- number of input images
+      hostname VARCHAR(64) NOT NULL,   -- host that executed script
+      good_frac FLOAT,                 -- good fraction of skycell
+      quality SMALLINT NOT NULL,       -- bad quality flag
+      fault SMALLINT NOT NULL,         -- fault code
+      PRIMARY KEY(xcsky_id),
+      KEY(good_frac),
+      KEY(fault),
+      KEY(quality),
+      FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE xccalRun (
+      xccal_id BIGINT AUTO_INCREMENT, -- unique identifier
+      xcsky_id BIGINT,
+      xcstack_id BIGINT,
+      state VARCHAR(64) NOT NULL,  -- state of run (new, full, etc.)
+      workdir VARCHAR(255) NOT NULL, -- working directory
+      label VARCHAR(64),             -- processing label
+      data_group VARCHAR(64),        -- group for data
+      dist_group VARCHAR(64),        -- group for distribution
+      reduction VARCHAR(64),         -- reduction class (for altering recipe)
+      registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
+      note VARCHAR(255),             -- note
+      PRIMARY KEY(xccal_id),
+      KEY(state),
+      KEY(label),
+      KEY(data_group),
+      KEY(dist_group),
+      KEY(xcsky_id),
+      KEY(xcstack_id),
+      FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id),
+      FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+-- Result of sky calibration analysis
+CREATE TABLE xccalResult (
+      xccal_id BIGINT,
+      path_base VARCHAR(255) NOT NULL,
+      dtime_script FLOAT,
+      dtime_astrom FLOAT,
+      sigma_ra FLOAT,
+      sigma_dec FLOAT,
+      n_astrom INT,
+      n_detections INT,
+      n_extended INT,
+      n_forced INT,
+      zpt_obs FLOAT,
+      zpt_stdev FLOAT,
+      fwhm_major  FLOAT,
+      fwhm_minor  FLOAT,
+      quality SMALLINT NOT NULL,
+      hostname VARCHAR(64) NOT NULL,
+      software_ver VARCHAR(64),
+      fault SMALLINT NOT NULL,
+      PRIMARY KEY(xccal_id),
+      KEY(fault),
+      KEY(quality),
+      FOREIGN KEY(xccal_id) REFERENCES xccalRun(xccal_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE xcForceRun (
+    xcff_id         BIGINT NOT NULL AUTO_INCREMENT,
+    xccal_id        BIGINT, -- NOT a foreign key: reference to entry in xcamera db
+    sources_path_base VARCHAR(255),
+    state           VARCHAR(64),
+    workdir         VARCHAR(255),
+    label           VARCHAR(64),
+    skycell_id      VARCHAR(64),
+    tess_id         VARCHAR(64),
+    data_group      VARCHAR(64),
+    dist_group      VARCHAR(64),
+    note            VARCHAR(255),
+    reduction       VARCHAR(64),
+    registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
+    PRIMARY KEY(xcff_id),
+    KEY(state),
+    KEY(label),
+    KEY(data_group),
+    KEY(xccal_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE xcForceInput (
+    xcff_id              BIGINT,
+    warp_id              BIGINT,
+    camera_id            BIGINT,
+    PRIMARY KEY(xcff_id, warp_id, camera_id),
+    FOREIGN KEY(xcff_id)   REFERENCES xcForceRun(xcff_id),
+    FOREIGN KEY(warp_id)   REFERENCES warpRun(warp_id),
+    FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE xcForceResult (
+    xcff_id         BIGINT,
+    warp_id         BIGINT,
+    path_base       VARCHAR(255) NOT NULL,
+    dtime_script    FLOAT,
+    quality         SMALLINT NOT NULL,
+    hostname        VARCHAR(64) NOT NULL,
+    software_ver    VARCHAR(16),
+    fault           SMALLINT NOT NULL,
+    PRIMARY KEY(xcff_id, warp_id),
+    KEY(fault),
+    KEY(quality),
+    FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id),
+    FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE xcForceSummary (
+    xcff_id         BIGINT,
+    path_base       VARCHAR(255) NOT NULL,
+    dtime_script    FLOAT,
+    quality         SMALLINT NOT NULL,
+    hostname        VARCHAR(64) NOT NULL,
+    software_ver    VARCHAR(16),
+    fault           SMALLINT NOT NULL,
+    PRIMARY KEY(xcff_id),
+    KEY(fault),
+    KEY(quality),
+    FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+