Changeset 28091 for trunk/ippTools/share

Timestamp:

May 25, 2010, 1:38:50 PM (16 years ago)

Author:

eugene

Message:

updates to staticskytool (now mostly working)

Location:

trunk/ippTools/share

Files:

• Makefile.am (modified) (1 diff)
• pxadmin_create_tables.sql (modified) (5 diffs)
• staticskytool_definebyquery.sql (modified) (1 diff)
• staticskytool_definebyquery_inputs.sql (added)
• staticskytool_definebyquery_select.sql (added)
• staticskytool_inputs.sql (added)
• staticskytool_result.sql (added)
• staticskytool_todo.sql (added)

trunk/ippTools/share/Makefile.am

@@ -289 +289 @@
      stacktool_sumskyfile.sql \
      stacktool_tosum.sql \
+     staticskytool_definebyquery_select.sql \
+     staticskytool_definebyquery_inputs.sql \
+     staticskytool_inputs.sql \
+     staticskytool_todo.sql \
+     staticskytool_result.sql \
      warptool_change_skyfile_data_state.sql \
      warptool_change_run_state.sql \

trunk/ippTools/share/pxadmin_create_tables.sql

@@ -1617 +1617 @@
 -- A static sky analysis set
 CREATE TABLE staticskyRun (
-      ss_id BIGINT AUTO_INCREMENT, -- unique identifier
+      sky_id BIGINT AUTO_INCREMENT, -- unique identifier
       state VARCHAR(64) NOT NULL,  -- state of run (new, full, etc.)
       workdir VARCHAR(255) NOT NULL, -- working directory
@@ -1626 +1626 @@
       note VARCHAR(255),             -- note
       registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered
-      PRIMARY KEY(ss_id),
+      PRIMARY KEY(sky_id),
       KEY(state),
       KEY(label),
@@ -1635 +1635 @@
 -- Inputs for static sky analysis
 CREATE TABLE staticskyInput (
-      ss_id BIGINT,             -- static sky identifier
+      sky_id BIGINT,             -- static sky identifier
       stack_id BIGINT,          -- stack identifier
-      PRIMARY KEY(ss_id,stack_id),
+      PRIMARY KEY(sky_id,stack_id),
       KEY(stack_id),
-      FOREIGN KEY(ss_id) REFERENCES staticskyRun(ss_id),
+      FOREIGN KEY(sky_id) REFERENCES staticskyRun(sky_id),
       FOREIGN KEY(stack_id) REFERENCES stackSumSkyfile(stack_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
@@ -1645 +1645 @@
 -- Result of static sky analysis
 CREATE TABLE staticskyResult (
-      ss_id BIGINT,             -- static sky identifier
+      sky_id BIGINT,             -- static sky identifier
       path_base VARCHAR(255) NOT NULL, -- root name for outputs
       dtime_phot FLOAT,                -- elapsed time for photometry
@@ -1654 +1654 @@
       quality SMALLINT NOT NULL,       -- bad quality flag
       fault SMALLINT NOT NULL,         -- fault code
-      PRIMARY KEY(ss_id),
+      PRIMARY KEY(sky_id),
       KEY(good_frac),
       KEY(fault),
       KEY(quality),
-      FOREIGN KEY(ss_id) REFERENCES staticskyRun(ss_id)
+      FOREIGN KEY(sky_id) REFERENCES staticskyRun(sky_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 

trunk/ippTools/share/staticskytool_definebyquery.sql

@@ -1 @@
-SELECT
-    tess_id,
-    skycell_id,
-    COUNT(*) AS num,
-    COUNT(DISTINCT filter) AS num_filter
-FROM stackRun
-JOIN stackSumSkyfile USING(stack_id)
-WHERE stackRun.state = 'full'
-    AND stackSumSkyfile.fault = 0
-    AND stackSumSkyfile.quality = 0
--- WHERE hook %s
-GROUP BY
-    tess_id,
-    skycell_id
+SELECT 
+       tess_id, 
+       skycell_id, 
+       num_filter 
+FROM (
+  SELECT
+      tess_id,
+      skycell_id,
+      COUNT(DISTINCT filter) AS num_filter
+  FROM stackRun
+  JOIN stackSumSkyfile USING(stack_id)
+  WHERE stackRun.state = 'full'
+      AND stackSumSkyfile.fault = 0
+      AND stackSumSkyfile.quality = 0
+  -- WHERE hook 1 %s 
+  -- restrict by stackSumSkyfile.good_frac, stackRun.skycell_id, stackRun.label
+  GROUP BY
+      tess_id,
+      skycell_id
+  ) AS TMP
+WHERE num_filter == %d
+