Changeset 28343 for trunk/ippTools/share/pxadmin_create_tables.sql

Timestamp:

Jun 15, 2010, 3:27:31 PM (16 years ago)

Author:

Paul Price

Message:

Adding database tables and tools for (re-)photometry of diffs.

File:

• trunk/ippTools/share/pxadmin_create_tables.sql (modified) (5 diffs)

trunk/ippTools/share/pxadmin_create_tables.sql

@@ -994 +994 @@
         tess_id VARCHAR(64),
         filter VARCHAR(64),
-        software_ver VARCHAR(16),
+        software_ver VARCHAR(16),
         note VARCHAR(255),
         PRIMARY KEY(stack_id),
@@ -1042 +1042 @@
         quality SMALLINT NOT NULL DEFAULT 0,
         fault SMALLINT,
-        software_ver VARCHAR(16),
+        software_ver VARCHAR(16),
         PRIMARY KEY(stack_id),
         KEY(dtime_stack),
@@ -1065 +1065 @@
         exposure TINYINT DEFAULT 0,
         magicked BIGINT,
-        software_ver VARCHAR(16),
+        software_ver VARCHAR(16),
         maskfrac_npix FLOAT,
         maskfrac_static FLOAT,
@@ -1134 +1134 @@
         fault SMALLINT,
         magicked BIGINT,
-        software_ver VARCHAR(16),
-        maskfrac_npix INT,
-        maskfrac_static FLOAT,
-        maskfrac_dynamic FLOAT,
-        maskfrac_magic FLOAT,
+        software_ver VARCHAR(16),
+        maskfrac_npix INT,
+        maskfrac_static FLOAT,
+        maskfrac_dynamic FLOAT,
+        maskfrac_magic FLOAT,
         maskfrac_advisory FLOAT,
         PRIMARY KEY(diff_id, skycell_id),
@@ -1678 +1678 @@
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
+
+-- Tables to support (re-)photometry of a diff
+
+CREATE TABLE diffPhotRun (
+    diff_phot_id BIGINT AUTO_INCREMENT, -- Identifier for diffPhotRun
+    diff_id BIGINT NOT NULL,            -- Identifier for diffRun
+    state VARCHAR(64) NOT NULL,         -- State of run
+    workdir VARCHAR(255) NOT NULL, -- working directory
+    label VARCHAR(64),             -- processing label
+    data_group VARCHAR(64),        -- group for data
+    reduction VARCHAR(64),         -- reduction class (for altering recipe)
+    registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
+    note VARCHAR(255),             -- note
+    PRIMARY KEY(diff_phot_id),
+    KEY(diff_id),
+    KEY(state),
+    KEY(label),
+    KEY(data_group),
+    FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE diffPhotSkyfile (
+    diff_phot_id BIGINT AUTO_INCREMENT, -- Identifier for diffPhotRun
+    skycell_id VARCHAR(64) NOT NULL,            -- Skycell identifier
+    path_base VARCHAR(255) NOT NULL, -- Base of path for output
+    dtime_script FLOAT,              -- elapsed time for script
+    hostname VARCHAR(64) NOT NULL,   -- host that executed script
+    fault SMALLINT NOT NULL,         -- fault code
+    quality SMALLINT NOT NULL,       -- bad quality flag
+    software_ver VARCHAR(16),                       -- software version
+    PRIMARY KEY(diff_phot_id, skycell_id),
+    KEY(state),
+    KEY(fault),
+    KEY(quality),
+    FOREIGN KEY(diff_phot_id) REFERENCES diffPhotRun(diff_phot_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
 -- This comment line is here to avoid empty query error.
 -- Another way to avoid that problem is to omit the semicolon above but I think that is untidy.