Changeset 20903

Timestamp:

Dec 4, 2008, 1:44:33 PM (17 years ago)

Author:

bills

Message:

on branch. Reorganizing the diff stage to consist of a collection of
skyfiles

Location:

branches/bills_081204

Files:

• dbconfig/changes.txt (modified) (1 diff)
• dbconfig/diff.md (modified) (3 diffs)
• dbconfig/magic.md (modified) (3 diffs)
• ippScripts/scripts/diff_skycell.pl (modified) (7 diffs)
• ippTasks/diff.pro (modified) (1 diff)
• ippTasks/simtest.auto (modified) (1 diff)
• ippTools/share/Makefile.am (modified) (1 diff)
• ippTools/share/difftool_definebyquery.sql (modified) (3 diffs)
• ippTools/share/difftool_inputskyfile.sql (modified) (4 diffs)
• ippTools/share/difftool_skyfile.sql (modified) (2 diffs)
• ippTools/share/difftool_todiffskyfile.sql (modified) (2 diffs)
• ippTools/share/magictool_definebyquery_insert.sql (modified) (1 diff)
• ippTools/share/magictool_definebyquery_select.sql (modified) (1 diff)
• ippTools/share/magictool_definebyquery_temp_insert.sql (modified) (1 diff)
• ippTools/share/magictool_inputs.sql (modified) (2 diffs)
• ippTools/share/pxadmin_create_tables.sql (modified) (5 diffs)
• ippTools/src/difftool.c (modified) (21 diffs)
• ippTools/src/difftoolConfig.c (modified) (5 diffs)
• ippTools/src/magictool.c (modified) (7 diffs)
• ippTools/src/magictoolConfig.c (modified) (1 diff)

branches/bills_081204/dbconfig/changes.txt

@@ -717 +717 @@
 alter table magicDSRun add column cam_id BIGINT after stage_id;
 
+-- diff reorginization
+alter table magicRun add column diff_id BIGINT after exp_id 
+ALTER TABLE magicRun ADD KEY(diff_id);
+ALTER TABLE warpRun ADD KEY(warp_id, cam_id);
+ALTER TABLE warpRun ADD CONSTRAINT FOREIGN KEY(cam_id) REFERENCES
+camRun(cam_id);
+ALTER TABLE warpRun ADD COLUMN magiced TINYINT AFTER registered;
+ALTER TABLE warpSkyCellMap DROP FOREIGN KEY warpSkyCellMap_ibfk_1;
+UPDATE warpRun JOIN warpInputExp USING(warp_id) SET warpRun.cam_id =
+warpInputExp.cam_id WHERE warpRun.warp_id = warpInputExp.warp_id;
+ALTER TABLE warpSkyCellMap ADD CONSTRAINT FOREIGN KEY(warp_id, cam_id)
+REFERENCES warpRun(warp_id, cam_id);
+DROP TABLE warpInputExp;
+
+-- 1.1.32
+ALTER TABLE warpRun ADD COLUMN label VARCHAR(64) AFTER workdir_state;
+ALTER TABLE warpRun ADD KEY(label);
+CREATE TABLE warpMask (label VARCHAR(64), PRIMARY KEY(label)) ENGINE=innodb;
+
+-- 1.1.33
+ALTER TABLE summitExp ADD COLUMN fault smallint(6) NOT NULL AFTER imfiles;
+ALTER TABLE summitExp ADD KEY(fault);
+
+-- 1.1.34
+ALTER TABLE rawImfile ADD KEY(exp_name);
+
+-- 1.1.35
+ALTER TABLE rawImfile ADD INDEX UNQIUE(exp_id, tmp_class_id);
+
+-- 1.1.36
+ALTER TABLE warpSkyfile ADD COLUMN (xmin INT, xmax INT, ymin INT, ymax INT);
+ALTER TABLE diffSkyfile ADD COLUMN (stamps_num INT, stamps_rms FLOAT, sources INT);
+ALTER TABLE rawImfile ADD COLUMN hostname VARCHAR(64) AFTER object;
+ALTER TABLE rawExp ADD COLUMN hostname VARCHAR(64) AFTER object;
+
+-- 1.1.37
+ALTER TABLE pzDataStore ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
+ALTER TABLE summitExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
+ALTER TABLE summitImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
+ALTER TABLE pzDownloadExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER state;
+ALTER TABLE pzDownloadImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
+ALTER TABLE newExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER label;
+ALTER TABLE newImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
+ALTER TABLE rawExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
+ALTER TABLE rawImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
+
+-- 1.1.38
+
+-- convert from utf8 -> latin1 for performance/space gains
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE calDB CONVERT TO CHARACTER SET latin1;
+ALTER TABLE calRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE camMask CONVERT TO CHARACTER SET latin1;
+ALTER TABLE camProcessedExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE camRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE chipMask CONVERT TO CHARACTER SET latin1;
+ALTER TABLE chipProcessedImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE chipRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detInputExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detNormalizedExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detNormalizedImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detNormalizedStatImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detProcessedExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detProcessedImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detRegisteredImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detResidExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detResidImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detRunSummary CONVERT TO CHARACTER SET latin1;
+ALTER TABLE detStackedImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE diffInputSkyfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE diffRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE diffSkyfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE fakeMask CONVERT TO CHARACTER SET latin1;
+ALTER TABLE fakeProcessedImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE fakeRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE flatcorrExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE flatcorrRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE guidePendingExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE magicInputSkyfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE magicMask CONVERT TO CHARACTER SET latin1;
+ALTER TABLE magicNodeResult CONVERT TO CHARACTER SET latin1;
+ALTER TABLE magicRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE magicSkyfileMask CONVERT TO CHARACTER SET latin1;
+ALTER TABLE magicTree CONVERT TO CHARACTER SET latin1;
+ALTER TABLE newExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE newImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE pstampDataStore CONVERT TO CHARACTER SET latin1;
+ALTER TABLE pstampJob CONVERT TO CHARACTER SET latin1;
+ALTER TABLE pstampRequest CONVERT TO CHARACTER SET latin1;
+ALTER TABLE pzDataStore CONVERT TO CHARACTER SET latin1;
+ALTER TABLE pzDownloadExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE pzDownloadImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE rawExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE rawImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE stackInputSkyfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE stackRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE stackSumSkyfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE summitExp CONVERT TO CHARACTER SET latin1;
+ALTER TABLE summitImfile CONVERT TO CHARACTER SET latin1;
+ALTER TABLE warpMask CONVERT TO CHARACTER SET latin1;
+ALTER TABLE warpRun CONVERT TO CHARACTER SET latin1;
+ALTER TABLE warpSkyCellMap CONVERT TO CHARACTER SET latin1;
+ALTER TABLE warpSkyfile CONVERT TO CHARACTER SET latin1;
+SET FOREIGN_KEY_CHECKS=1;
+
+-- first use of fake* tables
+-- drop and recreate any fake* tables created prior to this point
+
+DROP TABLE IF EXISTS fakeRun;
+CREATE TABLE fakeRun (
+    fake_id BIGINT AUTO_INCREMENT,
+    cam_id BIGINT,
+    state VARCHAR(64),
+    workdir VARCHAR(255),
+    label VARCHAR(64),
+    reduction VARCHAR(64),
+    expgroup VARCHAR(64),
+    dvodb VARCHAR(255),
+    tess_id VARCHAR(64),
+    end_stage VARCHAR(64),
+    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY(fake_id),
+    KEY(cam_id),
+    KEY(state),
+    KEY(label),
+    KEY(expgroup),
+    KEY(end_stage),
+    INDEX(fake_id, cam_id),
+    FOREIGN KEY (cam_id)
+        REFERENCES camRun(cam_id))
+ENGINE=innodb DEFAULT CHARSET=latin1;
+
+DROP TABLE IF EXISTS fakeProcessedImfile;
+CREATE TABLE fakeProcessedImfile (
+    fake_id BIGINT AUTO_INCREMENT,
+    exp_id BIGINT(20),
+    class_id VARCHAR(64),
+    uri VARCHAR(255),
+    dtime_fake FLOAT,
+    hostname VARCHAR(64),
+    path_base VARCHAR(255),
+    fault SMALLINT NOT NULL,
+    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    PRIMARY KEY(fake_id, exp_id, class_id),
+    KEY(fault),
+    FOREIGN KEY (fake_id)
+        REFERENCES fakeRun(fake_id),
+    FOREIGN KEY(exp_id, class_id)
+        REFERENCES rawImfile(exp_id, class_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+DROP TABLE IF EXISTS fakeMask;
+CREATE TABLE fakeMask (
+    label VARCHAR(64),
+    PRIMARY KEY(label))
+ENGINE=innodb DEFAULT CHARSET=latin1;
+
+-- re-normalize camProcessedExp table
+-- unrelated to other changes
+ALTER TABLE camProcessedExp DROP FOREIGN KEY camProcessedExp_ibfk_1;
+ALTER TABLE camProcessedExp DROP COLUMN chip_id;
+ALTER TABLE camProcessedExp ADD FOREIGN KEY (cam_id) REFERENCES camRun (cam_id);
+
+-- insert fakeRun betwen camRun and warpRun
+-- populate fakeRun/fakeProcessedImfile
+-- then heal the foreign key references
+
+CREATE TEMPORARY TABLE warpCamMap (
+    warp_id BIGINT,
+    cam_id  BIGINT,
+    PRIMARY KEY(warp_id),
+    KEY(cam_id)
+) ENGINE=MEMORY;
+
+INSERT INTO warpCamMap SELECT warp_id, cam_id FROM warpRun;
+
+ALTER TABLE warpRun drop FOREIGN KEY warpRun_ibfk_1;
+ALTER TABLE warpSkyCellMap DROP FOREIGN KEY warpSkyCellMap_ibfk_1;
+
+ALTER TABLE warpRun change COLUMN cam_id fake_id BIGINT(20) DEFAULT NULL;
+ALTER TABLE warpSkyCellMap DROP COLUMN cam_id;
+
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE warpRun ADD FOREIGN KEY (fake_id) REFERENCES fakeRun (fake_id);
+SET FOREIGN_KEY_CHECKS=1;
+ALTER TABLE warpSkyCellMap ADD FOREIGN KEY (warp_id) REFERENCES warpRun(warp_id);
+
+INSERT INTO fakeRun
+SELECT
+    NULL,
+    camRun.cam_id,
+    'stop',
+    workdir,
+    label,
+    reduction,
+    expgroup,
+    dvodb, 
+    tess_id, 
+    end_stage, 
+    NULL 
+FROM camRun
+JOIN warpCamMap
+    using(cam_id);
+
+INSERT INTO fakeProcessedImfile
+SELECT
+    fake_id,
+    chipRun.exp_id,
+    class_id,
+    uri,
+    0,
+    hostname,
+    path_base, 
+    fault, 
+    NULL
+FROM fakeRun
+JOIN camRun
+    USING(cam_id)
+JOIN chipRun
+    USING(chip_id)
+JOIN chipProcessedImfile
+    USING(chip_id);
+
+UPDATE warpRun, fakeRun, warpCamMap
+SET warpRun.fake_id = fakeRun.fake_id
+WHERE warpRun.warp_id = warpCamMap.warp_id
+    AND warpCamMap.cam_id = fakeRun.fake_id;
+
+DROP TABLE warpCamMap;
+
+
+-- new values for chipRun.state
+
+update chipRun set state = 'new' where state = 'run';
+update chipRun set state = 'full' where state = 'stop';
+
+-- from eam_branch_20080806
+-- add 'data_state' to detrend tables
+
+alter table detProcessedImfile      add column data_state varchar(64) after path_base;
+alter table detProcessedExp         add column data_state varchar(64) after path_base;
+alter table detStackedImfile        add column data_state varchar(64) after user_5;
+alter table detNormalizedStatImfile add column data_state varchar(64) after norm;
+alter table detNormalizedImfile     add column data_state varchar(64) after path_base;
+alter table detNormalizedExp        add column data_state varchar(64) after path_base;
+alter table detResidImfile          add column data_state varchar(64) after path_base;
+alter table detResidExp             add column data_state varchar(64) after path_base;
+alter table detRunSummary           add column data_state varchar(64) after iteration;
+alter table detRegisteredImfile     add column data_state varchar(64) after path_base;
+
+update camRun set state = 'new' where state = 'run';
+update camRun set state = 'full' where state = 'stop';
+
+update fakeRun set state = 'new' where state = 'run';
+update fakeRun set state = 'full' where state = 'stop';
+
+update warpRun set state = 'new' where state = 'run';
+update warpRun set state = 'full' where state = 'stop';
+
+update stackRun set state = 'new' where state = 'run';
+update stackRun set state = 'full' where state = 'stop';
+
+update diffRun set state = 'new' where state = 'run';
+update diffRun set state = 'full' where state = 'stop';
+
+-- not sure when 'filter' was added to stackRun, but it is needed now:
+
+alter table stackRun add column filter varchar(64) after tess_id;
+
+-- changes to the flatcorr tables : no valid flatcorr tables have been created to date expect my tests
+-- this sql just drops and recreates the flatcorrRun with the correct layout
+
+delete from flatcorrExp;
+delete from flatcorrRun;
+
+drop table flatcorrExp;
+drop table flatcorrRun;
+
+CREATE TABLE flatcorrRun (
+        corr_id BIGINT AUTO_INCREMENT,
+        dvodb VARCHAR(64),
+        filter VARCHAR(64),
+        state VARCHAR(64),
+        workdir VARCHAR(255),
+        label VARCHAR(64),
+        reduction VARCHAR(64),
+        region VARCHAR(64),
+        hostname VARCHAR(64),
+        fault SMALLINT NOT NULL,
+        PRIMARY KEY(corr_id),
+        KEY(corr_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE flatcorrChipLink (
+        corr_id BIGINT,
+        chip_id BIGINT,
+        PRIMARY KEY(corr_id, chip_id),
+        FOREIGN KEY (corr_id)  REFERENCES  flatcorrRun(corr_id),
+        FOREIGN KEY (chip_id)  REFERENCES  chipRun(chip_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE flatcorrCamLink (
+        corr_id BIGINT,
+        chip_id BIGINT,
+        cam_id BIGINT,
+        PRIMARY KEY(corr_id, chip_id, cam_id),
+        FOREIGN KEY (corr_id)  REFERENCES  flatcorrRun(corr_id),
+        FOREIGN KEY (chip_id)  REFERENCES  chipRun(chip_id),
+        FOREIGN KEY (cam_id)  REFERENCES  camRun(cam_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+-- I initially defined flatcorrCamLin without the chip_id entry.  this
+-- command corrects for that, but should not be needed widely.
+-- ALTER TABLE flatcorrCamLink ADD COLUMN chip_id BIGINT AFTER corr_id;
+-- alter table flatcorrRun add column hostname varchar(64) after region;
+-- alter table flatcorrRun add column fault smallint after hostname;
+-- alter table flatcorrRun add column reduction varchar(64) after stats;
+
+-- changes to the chip tables to support cleanup and reprocessing
+
+ALTER TABLE chipProcessedImfile ADD COLUMN data_state VARCHAR(64) AFTER class_id;
+UPDATE chipProcessedImfile SET data_state = 'full';
+
+-- I have added 'label' and 'reduction' entries to stack and diff
+
+ALTER TABLE stackRun ADD COLUMN label VARCHAR(64) AFTER workdir;
+ALTER TABLE stackRun ADD COLUMN reduction VARCHAR(64) AFTER label;
+
+ALTER TABLE stackSumSkyfile add column data_state varchar(64) after path_base;
+UPDATE stackSumSkyfile SET data_state = 'full';
+
+alter table diffRun add column label varchar(64) after workdir;
+alter table diffRun add column reduction varchar(64) after label;
+
+ALTER TABLE diffSkyfile add column data_state varchar(64) after path_base;
+UPDATE diffSkyfile SET data_state = 'full';
+
+ALTER TABLE warpSkyfile add column data_state varchar(64) after path_base;
+UPDATE warpSkyfile SET data_state = 'full';
+
+ALTER TABLE fakeProcessedImfile add column data_state varchar(64) after path_base;
+update fakeProcessedImfile set data_state ='full';
+
+-- adding ref_det_id and ref_iter to detRun and detResidImfile
+
+ALTER TABLE detResidImfile add column ref_det_id bigint after iteration;
+ALTER TABLE detResidImfile add column ref_iter int after ref_det_id;
+
+ALTER TABLE detRun change column parent ref_det_id bigint;
+ALTER TABLE detRun add column ref_iter int after ref_det_id;
+
+-- populate ref_det_id and ref_iter 
+UPDATE detResidImfile SET ref_det_id = det_id, ref_iter = iteration;
+
+-- Next we drop the old constraint and add the new one
+ALTER TABLE detResidImfile DROP FOREIGN KEY detResidImfile_ibfk_3;
+ALTER TABLE detResidImfile add FOREIGN KEY  (ref_det_id, ref_iter)
+    REFERENCES  detNormalizedExp(det_id, iteration);
+
+--- Additions for stack/diff QA by PAP.
+ALTER TABLE diffSkyfile ADD COLUMN stamps_mean FLOAT AFTER stamps_num;
+ALTER TABLE diffSkyfile ADD COLUMN dtime_match FLOAT AFTER dtime_diff;
+ALTER TABLE diffSkyfile ADD COLUMN dtime_phot FLOAT AFTER dtime_match;
+
+ALTER TABLE stackSumSkyfile ADD COLUMN dtime_match_mean FLOAT AFTER dtime_stack;
+ALTER TABLE stackSumSkyfile ADD COLUMN dtime_match_stdev FLOAT AFTER dtime_match_mean;
+ALTER TABLE stackSumSkyfile ADD COLUMN dtime_initial FLOAT AFTER dtime_match_stdev;
+ALTER TABLE stackSumSkyfile ADD COLUMN dtime_reject FLOAT AFTER dtime_initial;
+ALTER TABLE stackSumSkyfile ADD COLUMN dtime_final FLOAT AFTER dtime_reject;
+ALTER TABLE stackSumSkyfile ADD COLUMN dtime_phot FLOAT AFTER dtime_final;
+ALTER TABLE stackSumSkyfile ADD COLUMN match_mean FLOAT AFTER dtime_phot;
+ALTER TABLE stackSumSkyfile ADD COLUMN match_stdev FLOAT AFTER match_mean;
+ALTER TABLE stackSumSkyfile ADD COLUMN match_rms FLOAT AFTER match_stdev;
+ALTER TABLE stackSumSkyfile ADD COLUMN stamps_mean FLOAT AFTER match_rms;
+ALTER TABLE stackSumSkyfile ADD COLUMN stamps_stdev FLOAT AFTER stamps_mean;
+ALTER TABLE stackSumSkyfile ADD COLUMN stamps_min INT AFTER stamps_stdev;
+ALTER TABLE stackSumSkyfile ADD COLUMN reject_images INT AFTER stamps_min;
+ALTER TABLE stackSumSkyfile ADD COLUMN reject_pix_mean FLOAT AFTER reject_images;
+ALTER TABLE stackSumSkyfile ADD COLUMN reject_pix_stdev FLOAT AFTER reject_pix_mean;
+ALTER TABLE stackSumSkyfile ADD COLUMN sources INT AFTER reject_pix_stdev;
+ALTER TABLE stackSumSkyfile DROP COLUMN data_state;
+ALTER TABLE diffSkyfile DROP COLUMN data_state;
+
+-- Adding the new solar-system info (sun & moon data)
+ALTER TABLE rawExp CHANGE COLUMN solang sun_angle FLOAT;
+ALTER TABLE rawExp ADD COLUMN sun_alt    FLOAT AFTER sun_angle;
+ALTER TABLE rawExp ADD COLUMN moon_angle FLOAT AFTER sun_alt;
+ALTER TABLE rawExp ADD COLUMN moon_alt   FLOAT AFTER moon_angle;
+ALTER TABLE rawExp ADD COLUMN moon_phase FLOAT AFTER moon_alt;
+
+ALTER TABLE rawImfile ADD COLUMN sun_angle  FLOAT AFTER object;
+ALTER TABLE rawImfile ADD COLUMN sun_alt    FLOAT AFTER sun_angle;
+ALTER TABLE rawImfile ADD COLUMN moon_angle FLOAT AFTER sun_alt;
+ALTER TABLE rawImfile ADD COLUMN moon_alt   FLOAT AFTER moon_angle;
+ALTER TABLE rawImfile ADD COLUMN moon_phase FLOAT AFTER moon_alt;
+
+CREATE TABLE pstampProject (
+        proj_id BIGINT AUTO_INCREMENT,
+        state VARCHAR(64),
+        dbname VARCHAR(64),
+        dvodb VARCHAR(64),
+        camera VARCHAR(64),
+        telescope VARCHAR(64),
+        need_magic TINYINT,
+        PRIMARY KEY(proj_id),
+        KEY(proj_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+
+-- Reusing astrometry time field since ppImage doesn't do that any more
+ALTER TABLE chipProcessedImfile CHANGE COLUMN dtime_astrom dtime_total FLOAT;
+
+-- Deleting fields requiring astrometric matching from chipProcessedImfile
+ALTER TABLE chipProcessedImfile DROP COLUMN sigma_ra;
+ALTER TABLE chipProcessedImfile DROP COLUMN sigma_dec;
+ALTER TABLE chipProcessedImfile DROP COLUMN zp_mean;
+ALTER TABLE chipProcessedImfile DROP COLUMN zp_stdev;
+ALTER TABLE chipProcessedImfile DROP COLUMN n_astrom;
+
+-- No need to propagate processing times from chip to camera
+ALTER TABLE camProcessedExp DROP COLUMN dtime_detrend;
+ALTER TABLE camProcessedExp DROP COLUMN dtime_photom;
+ALTER TABLE camProcessedExp DROP COLUMN dtime_astrom;
+
+-- Add "dtime_script" to processing results
+ALTER TABLE chipProcessedImfile ADD COLUMN dtime_script FLOAT AFTER dtime_total;
+ALTER TABLE camProcessedExp ADD COLUMN dtime_script FLOAT AFTER fwhm_minor;
+ALTER TABLE fakeProcessedImfile ADD COLUMN dtime_script FLOAT AFTER dtime_fake;
+ALTER TABLE warpSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_warp;
+ALTER TABLE stackSumSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_phot;
+ALTER TABLE diffSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_phot;
+
+* 1.1.40
+
+-- Add moments statistics (M2, M3, M4)
+
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2     FLOAT AFTER fwhm_minor;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_err FLOAT AFTER iq_m2;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_lq  FLOAT AFTER iq_m2_err;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_uq  FLOAT AFTER iq_m2_lq;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c     FLOAT AFTER iq_m2_uq;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_err FLOAT AFTER iq_m2c;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_lq  FLOAT AFTER iq_m2c_err;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_uq  FLOAT AFTER iq_m2c_lq;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s     FLOAT AFTER iq_m2c_uq;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_err FLOAT AFTER iq_m2s;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_lq  FLOAT AFTER iq_m2s_err;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_uq  FLOAT AFTER iq_m2s_lq;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3     FLOAT AFTER iq_m2s_uq;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_err FLOAT AFTER iq_m3;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_lq  FLOAT AFTER iq_m3_err;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_uq  FLOAT AFTER iq_m3_lq;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4     FLOAT AFTER iq_m3_uq;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_err FLOAT AFTER iq_m4;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_lq  FLOAT AFTER iq_m4_err;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_uq  FLOAT AFTER iq_m4_lq;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN n_psfstars INT AFTER n_stars;
+ALTER TABLE chipProcessedImfile ADD COLUMN n_iqstars INT AFTER n_psfstars;
+
+
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2      FLOAT AFTER fwhm_minor;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2_err  FLOAT AFTER iq_m2;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2_lq   FLOAT AFTER iq_m2_err;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2_uq   FLOAT AFTER iq_m2_lq;
+
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2c     FLOAT AFTER iq_m2_uq;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_err FLOAT AFTER iq_m2c;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_lq  FLOAT AFTER iq_m2c_err;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_uq  FLOAT AFTER iq_m2c_lq;
+
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2s     FLOAT AFTER iq_m2c_uq;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_err FLOAT AFTER iq_m2s;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_lq  FLOAT AFTER iq_m2s_err;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_uq  FLOAT AFTER iq_m2s_lq;
+
+ALTER TABLE camProcessedExp ADD COLUMN iq_m3      FLOAT AFTER iq_m2s_uq;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m3_err  FLOAT AFTER iq_m3;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m3_lq   FLOAT AFTER iq_m3_err;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m3_uq   FLOAT AFTER iq_m3_lq;
+
+ALTER TABLE camProcessedExp ADD COLUMN iq_m4      FLOAT AFTER iq_m3_uq;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m4_err  FLOAT AFTER iq_m4;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m4_lq   FLOAT AFTER iq_m4_err;
+ALTER TABLE camProcessedExp ADD COLUMN iq_m4_uq   FLOAT AFTER iq_m4_lq;
+
+ALTER TABLE camProcessedExp ADD COLUMN n_psfstars INT AFTER n_stars;
+ALTER TABLE camProcessedExp ADD COLUMN n_iqstars INT AFTER n_psfstars;
+
+* 1.1.41
+
+ALTER TABLE camProcessedExp ADD COLUMN dtime_astrom FLOAT AFTER dtime_script;
+ALTER TABLE camProcessedExp ADD COLUMN dtime_addstar FLOAT AFTER dtime_astrom;
+
+* 1.1.42
+
+ALTER TABLE diffSkyfile ADD COLUMN norm FLOAT AFTER stamps_rms;
+ALTER TABLE diffSkyfile ADD COLUMN bg_diff FLOAT AFTER norm;
+ALTER TABLE diffSkyfile ADD COLUMN kernel_x FLOAT AFTER bg_diff;
+ALTER TABLE diffSkyfile ADD COLUMN kernel_y FLOAT AFTER kernel_x;
+ALTER TABLE diffSkyfile ADD COLUMN kernel_xx FLOAT AFTER kernel_y;
+ALTER TABLE diffSkyfile ADD COLUMN kernel_xy FLOAT AFTER kernel_xx;
+ALTER TABLE diffSkyfile ADD COLUMN kernel_yy FLOAT AFTER kernel_xy;
+
+* 1.1.43
+
+ALTER TABLE rawExp ADD COLUMN obs_mode VARCHAR(64) AFTER comment;
+ALTER TABLE rawExp ADD COLUMN obs_group VARCHAR(64) AFTER obs_mode;
+
+ALTER TABLE rawImfile ADD COLUMN obs_mode VARCHAR(64) AFTER comment;
+ALTER TABLE rawImfile ADD COLUMN obs_group VARCHAR(64) AFTER obs_mode;
+
+* 1.1.44
+
+ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_major_lq FLOAT AFTER fwhm_major;
+ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_major_uq FLOAT AFTER fwhm_major_lq;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_minor_lq FLOAT AFTER fwhm_minor;
+ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_minor_uq FLOAT AFTER fwhm_minor_lq;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_major     FLOAT AFTER fwhm_minor_uq;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_major_err FLOAT AFTER iq_fwhm_major;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_minor     FLOAT AFTER iq_fwhm_major_err;
+ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_minor_err FLOAT AFTER iq_fwhm_minor;
+
+ALTER TABLE camProcessedExp ADD COLUMN fwhm_major_lq     FLOAT AFTER fwhm_major;
+ALTER TABLE camProcessedExp ADD COLUMN fwhm_major_uq     FLOAT AFTER fwhm_major_lq;
+
+ALTER TABLE camProcessedExp ADD COLUMN fwhm_minor_lq     FLOAT AFTER fwhm_minor;
+ALTER TABLE camProcessedExp ADD COLUMN fwhm_minor_uq     FLOAT AFTER fwhm_minor_lq;
+
+ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_major     FLOAT AFTER fwhm_minor_uq;
+ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_major_err FLOAT AFTER iq_fwhm_major;
+ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_minor     FLOAT AFTER iq_fwhm_major_err;
+ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_minor_err FLOAT AFTER iq_fwhm_minor;
+
+ALTER TABLE flatcorrRun ADD COLUMN camera VARCHAR(64) AFTER dvodb;
+ALTER TABLE flatcorrRun ADD COLUMN telescope VARCHAR(64) AFTER camera;
+ALTER TABLE flatcorrRun ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER telescope;
+
+* 1.1.45
+
+ALTER TABLE camProcessedExp CHANGE COLUMN zp_mean  zpt_obs   FLOAT;
+ALTER TABLE camProcessedExp CHANGE COLUMN zp_stdev zpt_stdev FLOAT;
+ALTER TABLE camProcessedExp ADD COLUMN    zpt_lq             FLOAT AFTER zpt_stdev;
+ALTER TABLE camProcessedExp ADD COLUMN    zpt_uq             FLOAT AFTER zpt_lq;
+
+* 1.1.46
+
+ALTER TABLE pzDownloadImfile ADD COLUMN hostname varchar(64) after epoch;
+ALTER TABLE flatcorrRun ADD COLUMN det_type varchar(64) after corr_id;
+ALTER TABLE flatcorrChipLink ADD COLUMN include tinyint after chip_id;
+ALTER TABLE flatcorrCamLink ADD COLUMN include tinyint after cam_id;
+
+ALTER TABLE rawImfile ADD COLUMN ignored TINYINT DEFAULT 0 AFTER moon_phase;
+
+-- add new tables to control magic de-streaking
+CREATE TABLE magicDSRun (
+        magic_ds_id BIGINT AUTO_INCREMENT,
+        magic_id BIGINT,
+        state VARCHAR(64),
+        stage VARCHAR(64),
+        outroot VARCHAR(255),
+        recoveryroot VARCHAR(255),
+        re_place TINYINT,
+        remove TINYINT,
+        PRIMARY KEY(magic_ds_id),
+        KEY(magic_ds_id),
+        KEY(state),
+        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE magicDSFile (
+    magic_ds_id BIGINT,
+    component VARCHAR(64),
+    backup_path_base VARCHAR(255),
+    recovery_path_base VARCHAR(255),
+    fault SMALLINT,
+    PRIMARY KEY(magic_ds_id, component),
+    KEY(fault),
+    FOREIGN KEY (magic_ds_id) REFERENCES magicDSRun(magic_ds_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+-- add to the run table to make the queries far less expensive
+ALTER TABLE magicDSRun ADD COLUMN stage_id BIGINT AFTER stage;
+ALTER TABLE magicDSRun ADD COLUMN cam_id BIGINT AFTER stage_id;
+
+
+
+-- magic adaptations to diff reorginization
+ALTER TABLE magicRun ADD COLUMN diff_id BIGINT AFTER exp_id;
+ALTER TABLE magicRun ADD CONSTRAINT FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id);
+
+ALTER TABLE magicInputSkyfile DROP FOREIGN KEY magicInputSkyfile_ibfk_2;
+ALTER TABLE magicInputSkyfile DROP column diff_id;

branches/bills_081204/dbconfig/diff.md

@@ -1 @@
-# $Id: diff.md,v 1.14 2008-10-11 02:33:40 price Exp $
+# $Id: diff.md,v 1.14.8.1 2008-12-04 23:44:33 bills Exp $
 
 diffRun METADATA
@@ -9 +9 @@
     dvodb       STR         255
     registered  TAI         NULL
-    skycell_id  STR         64      # Key
+    exp_id      S64         0       # fkey(exp_id) ref rawExp(exp_id)
     tess_id     STR         64      # Key
 END
@@ -17 +17 @@
 #
 
-# only ever 2 per run - one template / one not
 diffInputSkyfile METADATA
     diff_id     S64         0       # Primary Key fkey(diff_id) ref diffRun(diff_id)
-    template    BOOL        f       # Primary Key
-    stack_id    S64         0       # fkey(stack_id) ref stackSumSkyfile(stack_id) 
-    warp_id     S64         0       # fkey(warp_id, skycell_id, tess_id) ref warpSkyfile(warp_id, skycell_id, tess_id)
+    warp1       S64         0       # fkey(warp1, skycell_id, tess_id) ref warpSkyfile(warp_id, skycell_id, tess_id)
+    warp2       S64         0       # fkey(warp2, skycell_id, tess_id) ref warpSkyfile(warp_id, skycell_id, tess_id)
+    stack1      S64         0       # fkey(stack1) ref stackSumSkyfile(stack_id) 
+    stack2      S64         0       # fkey(stack2) ref stackSumSkyfile(stack_id) 
     skycell_id  STR         64      # Key
     tess_id     STR         64      # Key
-# either a input or a template
-    kind        STR         64      # Key 
 END
 
 diffSkyfile METADATA
     diff_id      S64        0       # Primary Key fkey(diff_id) ref diffRun(diff_id)
+    skycell_id   STR        64      # Key
     uri          STR        255
     path_base    STR        255

branches/bills_081204/dbconfig/magic.md

@@ -1 @@
-# $Id: magic.md,v 1.12 2008-11-26 03:21:13 bills Exp $
+# $Id: magic.md,v 1.12.2.1 2008-12-04 23:44:33 bills Exp $
 
 ### Fault in magicRun indicates that the processing tree failed
@@ -5 +5 @@
     magic_id    S64         0       # Primary Key AUTO_INCREMENT
     exp_id      S64         0       # Key
+    diff_id     S64         0       # Key
     state       STR         64      # Key
     workdir     STR         255
@@ -16 +17 @@
 magicInputSkyfile METADATA
     magic_id    S64         0       # Primary Key fkey(magic_id) ref magicRun(magic_id)
-    diff_id     S64         0       # Primary Key fkey(diff_id) ref diffRun(diff_id)
     node        STR         64      #
 END

branches/bills_081204/ippScripts/scripts/diff_skycell.pl

@@ -28 +28 @@
 
 my ($diff_id, $dbname, $threads, $outroot, $reduction, $verbose, $no_update, $no_op, $redirect);
+my $skycell_id;                 # Skycell identifier
 GetOptions(
-    'diff_id|d=s'       => \$diff_id, # Diff identifier
+    'diff_id=s'         => \$diff_id, # Diff identifier
+    'skycell_id=s'      => \$skycell_id, # Diff identifier
     'dbname|d=s'        => \$dbname, # Database name
     'threads=s'         => \$threads,   # Number of threads to use
@@ -42 +44 @@
 pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
 pod2usage(
-    -msg => "Required options: --diff_id --outroot",
+    -msg => "Required options: --diff_id --skycell_id --outroot",
     -exitval => 3,
           ) unless defined $diff_id
+    and defined $skycell_id
     and defined $outroot;
 
@@ -67 +70 @@
 my $files;
 {
-    my $command = "$difftool -inputskyfile -diff_id $diff_id";
+    my $command = "$difftool -inputskyfile -diff_id $diff_id -skycell_id $skycell_id";
     $command .= " -dbname $dbname" if defined $dbname;
     my ( $success, $error_code, $full_buf, $stdout_buf, $stderr_buf ) =
@@ -89 +92 @@
 my ($template, $templateMask, $templateWeight, $templatePath, $templateSources); # Template files and path
 my $tess_id;                    # Tesselation identifier
-my $skycell_id;                 # Skycell identifier
 my $camera;                     # Camera
 foreach my $file (@$files) {
@@ -220 +222 @@
     &my_die("Couldn't find expected output file: $outputMask", $diff_id, $PS_EXIT_SYS_ERROR) unless $ipprc->file_exists($outputMask);
     &my_die("Couldn't find expected output file: $outputWeight", $diff_id, $PS_EXIT_SYS_ERROR) unless $ipprc->file_exists($outputWeight);
-    &my_die("Couldn't find expected output file: $outputSources", $diff_id, $PS_EXIT_SYS_ERROR) unless $ipprc->file_exists($outputSources);
+#    &my_die("Couldn't find expected output file: $outputSources", $diff_id, $PS_EXIT_SYS_ERROR) unless $ipprc->file_exists($outputSources);
 #    &my_die("Couldn't find expected output file: $bin1Name",    $diff_id, $PS_EXIT_SYS_ERROR) unless $ipprc->file_exists($bin1Name);
 #    &my_die("Couldn't find expected output file: $bin2Name",    $diff_id, $PS_EXIT_SYS_ERROR) unless $ipprc->file_exists($bin2Name);
@@ -245 +247 @@
     # Add the subtraction result
     {
-        my $command = "$difftool -adddiffskyfile -diff_id $diff_id -uri $outputName -path_base $outroot";
+        my $command = "$difftool -adddiffskyfile -diff_id $diff_id -skycell_id $skycell_id -uri $outputName -path_base $outroot";
         $command .= " $cmdflags";
         $command .= (" -dtime_script " . ((DateTime->now->mjd - $mjd_start) * 86400));
@@ -269 +271 @@
     warn($msg);
     if (defined $diff_id and not $no_update) {
-        my $command = "$difftool -adddiffskyfile -diff_id $diff_id -code $exit_code";
+        my $command = "$difftool -adddiffskyfile -diff_id $diff_id -skycell_id $skycell_id -code $exit_code";
         $command .= (" -dtime_script " . ((DateTime->now->mjd - $mjd_start) * 86400));
         $command .= " -hostname $host" if defined $host;

branches/bills_081204/ippTasks/diff.pro

@@ -156 +156 @@
     stderr $LOGDIR/diff.skycell.log
 
-    $run = diff_skycell.pl --threads @MAX_THREADS@ --diff_id $DIFF_ID --outroot $outroot --redirect-output
+    $run = diff_skycell.pl --threads @MAX_THREADS@ --diff_id $DIFF_ID --skycell_id $SKYCELL_ID --outroot $outroot --redirect-output
     add_standard_args run
 

branches/bills_081204/ippTasks/simtest.auto

@@ -73 +73 @@
 automate METADATA
   name       STR MAGIC
-  regular    STR "magictool -definebyquery -workdir file://@CWD@/magic -good_frac 0.2 -dbname @DBNAME@"
+  regular    STR "magictool -definebyquery -workdir file://@CWD@/magic -dbname @DBNAME@"
 END
 

branches/bills_081204/ippTools/share/Makefile.am

@@ -70 +70 @@
      dettool_toresidimfile.sql \
      dettool_tostacked.sql \
+     difftool_completed_runs.sql \
      difftool_definebyquery.sql \
      difftool_donecleanup.sql \

branches/bills_081204/ippTools/share/difftool_definebyquery.sql

@@ -9 +9 @@
     warpsToDiff.good_frac,
     warpsToDiff.diff_id,
-    warpsToDiff.kind,
     current_stack_id,
-    best_stack_id
+    best_stack_id,
+    exp_id
 FROM (
     -- Get list of warps that can be diffed, with any associated diff
@@ -23 +23 @@
         warpRun.label as warp_label,
         diffInputs.diff_id,
-        diffInputs.kind,
-        diffTemplates.stack_id AS current_stack_id
+        diffInputs.stack2 AS current_stack_id,
+        rawExp.exp_id
     FROM warpSkyfile
     JOIN warpRun USING(warp_id)
@@ -33 +33 @@
     -- Check if it has an associated diff
     LEFT JOIN diffInputSkyfile AS diffInputs
-        ON diffInputs.warp_id = warpSkyfile.warp_id
+        ON diffInputs.warp1 = warpSkyfile.warp_id
         AND diffInputs.skycell_id = warpSkyfile.skycell_id
-        AND diffInputs.template = 0 -- only join input files
-    -- Get the stack_id currently used as a template, if any
-    LEFT JOIN diffInputSkyfile AS diffTemplates
-        ON diffTemplates.diff_id = diffInputs.diff_id
-        AND diffTemplates.template != 0 -- only join template files
+        AND diffInputs.stack2 IS NOT NULL
     WHERE
         warpSkyfile.fault = 0

branches/bills_081204/ippTools/share/difftool_inputskyfile.sql

@@ -1 +1 @@
 SELECT * FROM
-    (SELECT 
+    (SELECT
+        -- warp input
         diffRun.diff_id,
-        diffRun.skycell_id,
-        diffRun.tess_id,
+        diffInputSkyfile.skycell_id,
+        diffInputSkyfile.tess_id,
         0 as stack_id,
         warpSkyfile.warp_id,
         warpSkyfile.uri,
         warpSkyfile.path_base,
-        diffInputSkyfile.template,
+        0 as template,
         rawExp.camera
     FROM diffRun
@@ -14 +15 @@
         USING(diff_id)
     JOIN warpSkyfile
-        ON  diffInputSkyfile.warp_id    = warpSkyfile.warp_id
+        ON  diffInputSkyfile.warp1      = warpSkyfile.warp_id
         AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
         AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
     JOIN warpRun
-        ON diffInputSkyfile.warp_id = warpRun.warp_id
+        ON diffInputSkyfile.warp1 = warpRun.warp_id
+    JOIN fakeRun
+        USING(fake_id)
+    JOIN camRun
+        USING(cam_id)
+    JOIN chipRun
+        USING(chip_id)
+    JOIN chipProcessedImfile
+        USING(chip_id)
+    JOIN rawExp
+        ON chipRun.exp_id = rawExp.exp_id
+    WHERE
+        diffRun.state = 'new'
+        AND warpRun.state = 'full'
+        AND fakeRun.state = 'full'
+        AND camRun.state = 'full'
+        AND chipRun.state = 'full'
+        -- where hook %s
+    UNION
+    SELECT
+        -- warp template
+        diffRun.diff_id,
+        diffInputSkyfile.skycell_id,
+        diffInputSkyfile.tess_id,
+        0 as stack_id,
+        warpSkyfile.warp_id,
+        warpSkyfile.uri,
+        warpSkyfile.path_base,
+        0 as template,
+        rawExp.camera
+    FROM diffRun
+    JOIN diffInputSkyfile
+        USING(diff_id)
+    JOIN warpSkyfile
+        ON  diffInputSkyfile.warp2      = warpSkyfile.warp_id
+        AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
+        AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
+    JOIN warpRun
+        ON diffInputSkyfile.warp2 = warpRun.warp_id
     JOIN fakeRun
         USING(fake_id)
@@ -38 +77 @@
     UNION
     SELECT 
+        -- stack input
         diffRun.diff_id,
-        diffRun.skycell_id,
-        diffRun.tess_id,
+        diffInputSkyfile.skycell_id,
+        diffInputSkyfile.tess_id,
         stackSumSkyfile.stack_id,
         0 as warp_id,
         stackSumSkyfile.uri,
         stackSumSkyfile.path_base,
-        diffInputSkyfile.template,
+        1 as template,
         rawExp.camera
     FROM diffRun
@@ -51 +91 @@
         USING(diff_id)
     JOIN stackSumSkyfile
-        ON  diffInputSkyfile.stack_id = stackSumSkyfile.stack_id
+        ON  diffInputSkyfile.stack1 = stackSumSkyfile.stack_id
     JOIN stackInputSkyfile
-        ON diffInputSkyfile.stack_id = stackInputSkyfile.stack_id
-    JOIN warpRun
-        ON stackInputSkyfile.warp_id = warpRun.warp_id
-    JOIN fakeRun
-        USING(fake_id)
-    JOIN camRun
-        USING(cam_id)
-    JOIN chipRun
-        USING(chip_id)
-    JOIN chipProcessedImfile
-        USING(chip_id)
+        ON diffInputSkyfile.stack1 = stackInputSkyfile.stack_id
     JOIN rawExp
-        ON chipRun.exp_id = rawExp.exp_id
+        USING(exp_id)
     WHERE
         diffRun.state = 'new'
-        AND warpRun.state = 'full'
-        AND fakeRun.state = 'full'
-        AND camRun.state = 'full'
-        AND chipRun.state = 'full'
+        -- where hook %s
+    UNION
+    SELECT 
+        -- stack template
+        diffRun.diff_id,
+        diffInputSkyfile.skycell_id,
+        diffInputSkyfile.tess_id,
+        stackSumSkyfile.stack_id,
+        0 as warp_id,
+        stackSumSkyfile.uri,
+        stackSumSkyfile.path_base,
+        1 as template,
+        rawExp.camera
+    FROM diffRun
+    JOIN diffInputSkyfile
+        USING(diff_id)
+    JOIN stackSumSkyfile
+        ON  diffInputSkyfile.stack2 = stackSumSkyfile.stack_id
+    JOIN stackInputSkyfile
+        ON diffInputSkyfile.stack2 = stackInputSkyfile.stack_id
+    JOIN rawExp
+        USING(exp_id)
+    WHERE
+        diffRun.state = 'new'
         -- where hook %s
     ) as Foo

branches/bills_081204/ippTools/share/difftool_skyfile.sql

@@ -1 +1 @@
 SELECT
-    diffRun.skycell_id,
+    diffSkyfile.*,
     diffRun.tess_id,
     diffRun.state,
-    diffSkyfile.*,
-    (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
-        AND diffInputSkyfile.template = 0 ) AS warp_id_temp_0,
-    (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
-        AND diffInputSkyfile.template = 0 ) AS stack_id_temp_0,
-    (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
-        AND diffInputSkyfile.template = 1 ) AS warp_id_temp_1,
-    (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
-        AND diffInputSkyfile.template = 1 ) AS stack_id_temp_1
+    warp1,
+    stack1,
+    warp2,
+    stack2
+--    (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
+--        AND diffInputSkyfile.template = 0 ) AS warp_id_temp_0,
+--    (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
+--        AND diffInputSkyfile.template = 0 ) AS stack_id_temp_0,
+--    (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
+--        AND diffInputSkyfile.template = 1 ) AS warp_id_temp_1,
+--    (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id
+--        AND diffInputSkyfile.template = 1 ) AS stack_id_temp_1
 FROM diffRun
 JOIN diffSkyfile
@@ -17 +20 @@
 JOIN diffInputSkyfile
     ON diffInputSkyfile.diff_id = diffRun.diff_id
-    AND diffInputSkyfile.template = 0
 JOIN warpRun
-    USING(warp_id)
-JOIN fakeRun
-    USING(fake_id)
-JOIN camRun
-    USING(cam_id)
-JOIN chipRun
-    USING(chip_id)
+    ON warpRun.warp_id = diffInputSkyfile.warp1
 JOIN rawExp
     USING(exp_id)

branches/bills_081204/ippTools/share/difftool_todiffskyfile.sql

@@ -5 +5 @@
     diffRun.diff_id,
     diffRun.workdir,
-    diffRun.skycell_id,
+    diffInputSkyfile.skycell_id,
     diffRun.tess_id,
     diffRun.label,
     diffRun.state
 FROM diffRun
+JOIN diffInputSkyfile USING(diff_id)
+
 -- Get list of templates for each diffRun
-JOIN diffInputSkyfile AS diffTemplateSkyfile
-    ON diffRun.diff_id = diffTemplateSkyfile.diff_id
-    AND diffRun.skycell_id = diffTemplateSkyfile.skycell_id
-    AND diffTemplateSkyfile.template = 1
+-- JOIN diffInputSkyfile AS diffTemplateSkyfile
+--     ON diffRun.diff_id = diffTemplateSkyfile.diff_id
+--     AND diffRun.skycell_id = diffTemplateSkyfile.skycell_id
+--     AND diffTemplateSkyfile.template = 1
 -- Get list of inputs for each diffRun
-JOIN diffInputSkyfile
-    ON diffRun.diff_id = diffInputSkyfile.diff_id
-    AND diffRun.skycell_id = diffInputSkyfile.skycell_id
-    AND diffInputSkyfile.template = 0
+-- JOIN diffInputSkyfile
+--     ON diffRun.diff_id = diffInputSkyfile.diff_id
+--     AND diffRun.skycell_id = diffInputSkyfile.skycell_id
+--     AND diffInputSkyfile.template = 0
+
 -- Get warp templates
 LEFT JOIN warpRun AS warpTemplateRun
-    ON warpTemplateRun.warp_id = diffTemplateSkyfile.warp_id
-    AND diffTemplateSkyfile.warp_id IS NOT NULL
+    ON warpTemplateRun.warp_id = diffInputSkyfile.warp2
+    AND diffInputSkyfile.warp2 IS NOT NULL
 LEFT JOIN warpSkyfile AS warpTemplateSkyfile
     ON warpTemplateSkyfile.warp_id = warpTemplateRun.warp_id
-    AND warpTemplateSkyfile.skycell_id = diffTemplateSkyfile.skycell_id
+    AND warpTemplateSkyfile.skycell_id = diffInputSkyfile.skycell_id
+
 -- Get warp inputs
 LEFT JOIN warpRun
-    ON warpRun.warp_id = diffInputSkyfile.warp_id
-    AND diffInputSkyfile.warp_id IS NOT NULL
+    ON warpRun.warp_id = diffInputSkyfile.warp1
+    AND diffInputSkyfile.warp1 IS NOT NULL
 LEFT JOIN warpSkyfile
     ON warpSkyfile.warp_id = warpRun.warp_id
     AND warpSkyfile.skycell_id = diffInputSkyfile.skycell_id
+
 -- Get stack templates
 LEFT JOIN stackRun AS stackTemplateRun
-    ON stackTemplateRun.stack_id = diffTemplateSkyfile.stack_id
+    ON stackTemplateRun.stack_id = diffInputSkyfile.stack2
+    AND diffInputSkyfile.stack2 IS NOT NULL
 LEFT JOIN stackSumSkyfile AS stackTemplateSkyfile
     ON stackTemplateSkyfile.stack_id = stackTemplateRun.stack_id
+
 -- Get stack inputs
 LEFT JOIN stackRun
-    ON stackRun.stack_id = diffInputSkyfile.stack_id
-    AND diffInputSkyfile.warp_id IS NULL
+    ON stackRun.stack_id = diffInputSkyfile.stack1
+    AND diffInputSkyfile.stack1 IS NOT NULL
 LEFT JOIN stackSumSkyfile
     ON stackSumSkyfile.stack_id = stackRun.stack_id
-    AND diffInputSkyfile.warp_id IS NULL
+
 -- Get what's already been processed
 LEFT JOIN diffSkyfile
@@ -57 +64 @@
     )
 -- Ensure input warps are available
-    AND (diffInputSkyfile.warp_id IS NULL
+    AND (diffInputSkyfile.warp1 IS NULL
     OR (warpRun.state = 'full'
     AND warpSkyfile.fault = 0
     AND warpSkyfile.ignored = 0))
 -- Ensure input stacks are available
-    AND (diffInputSkyfile.stack_id IS NULL
+    AND (diffInputSkyfile.stack1 IS NULL
     OR (stackRun.state = 'full'
     AND stackSumSkyfile.fault = 0))
 -- Ensure template warps are available
-    AND (diffTemplateSkyfile.warp_id IS NULL
+    AND (diffInputSkyfile.warp2 IS NULL
     OR (warpTemplateRun.state = 'full'
     AND warpTemplateSkyfile.fault = 0
     AND warpTemplateSkyfile.ignored = 0))
 -- Ensure template stacks are available
-    AND (diffTemplateSkyfile.stack_id IS NULL
+    AND (diffInputSkyfile.stack2 IS NULL
     OR (stackTemplateRun.state = 'full'
     AND stackTemplateSkyfile.fault = 0))

branches/bills_081204/ippTools/share/magictool_definebyquery_insert.sql

@@ -3 +3 @@
 SELECT
     @MAGIC_ID@, -- Update this with the appropriate magic_id
-    diff_id,
     skycell_id
-FROM magicBestDiffs
+FROM diffSkyfile
 WHERE
-    exp_id = @EXP_ID@ -- Update this with the appropriate exp_id
+    diff_id = @DIFF_ID@ -- Update this with the appropriate diff_id
+    AND fault = 0

branches/bills_081204/ippTools/share/magictool_definebyquery_select.sql

@@ -1 +1 @@
 -- Get a list of exposures on which magic may be performed
-SELECT DISTINCT
+SELECT 
     exp_id,
-    filter,
-    num_todo,
-    num_done,
-    MAX(magic_id)
-FROM (
-    -- Number of skycells as a function of exposure
-    SELECT
-        exp_id,
-        filter,
-        COUNT(DISTINCT warpSkyfile.tess_id,warpSkyfile.skycell_id) AS num_todo
-    FROM rawExp
-    JOIN chipRun USING(exp_id)
-    JOIN camRun USING(chip_id)
-    JOIN fakeRun USING(cam_id)
-    JOIN warpRun USING(fake_id)
-    JOIN warpSkyCellMap USING(warp_id)
-    JOIN warpSkyfile USING(warp_id, skycell_id)
-    JOIN diffInputSkyfile USING(warp_id,skycell_id)
-    JOIN diffRun USING(diff_id)
-    WHERE
-        warpSkyfile.ignored = 0
-        -- magicSkycellNums WHERE hook %s
-    GROUP BY
-        exp_id
-    ) AS magicSkycellNums
-JOIN (
-    -- Number of completed diffs for an exposure
-    SELECT
-        exp_id,
-        COUNT(diff_id) AS num_done
-    FROM magicBestDiffs
-    GROUP BY
-        exp_id
-    ) AS magicDiffNums USING(exp_id)
+    MAX(diff_id) AS diff_id
+FROM diffRun
+JOIN rawExp USING(exp_id)
 LEFT JOIN magicRun USING(exp_id)
+-- WHERE hook %s
+GROUP BY exp_id

branches/bills_081204/ippTools/share/magictool_definebyquery_temp_insert.sql

@@ -14 +14 @@
 JOIN warpSkyfile USING(warp_id, skycell_id)
 JOIN diffInputSkyfile
-    ON diffInputSkyfile.warp_id = warpSkyfile.warp_id
+    ON diffInputSkyfile.warp1 = warpSkyfile.warp_id
     AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
-    AND diffInputSkyfile.template = 0 -- selecting inputs only
 JOIN diffRun USING(diff_id)
 JOIN diffSkyfile USING(diff_id)

branches/bills_081204/ippTools/share/magictool_inputs.sql

@@ -11 +11 @@
     diffSkyfile.fault
 FROM magicInputSkyfile
+JOIN magicRun USING(magic_id)
 JOIN diffSkyfile
     USING(diff_id)
-JOIN magicRun
-    USING(magic_id)
 UNION
 -- Merged skycells
@@ -21 +20 @@
     magicRun.state,
     magicTree.node,
-    0,
+    0,   -- no diff_id
     magicNodeResult.uri,
     NULL, -- magicNodeResult doesn't have a path_base

branches/bills_081204/ippTools/share/pxadmin_create_tables.sql

@@ -941 +941 @@
         dvodb VARCHAR(255),
         registered DATETIME,
-        skycell_id VARCHAR(64),
+        exp_id  BIGINT,
         tess_id VARCHAR(64),
         PRIMARY KEY(diff_id),
         KEY(diff_id),
         KEY(state),
-        KEY(skycell_id),
         KEY(tess_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
@@ -952 +951 @@
 CREATE TABLE diffInputSkyfile (
         diff_id BIGINT,
-        template TINYINT,
-        stack_id BIGINT,
-        warp_id BIGINT,
+        warp1 BIGINT,
+        warp2 BIGINT,
+        stack1 BIGINT,
+        stack2 BIGINT,
         skycell_id VARCHAR(64),
         tess_id VARCHAR(64),
-        kind VARCHAR(64),
-        PRIMARY KEY(diff_id, template),
-        KEY(stack_id),
-        KEY(warp_id),
+        PRIMARY KEY(diff_id),
+        KEY(warp1),
+        KEY(warp2),
+        KEY(stack1),
+        KEY(stack2),
         KEY(skycell_id),
         KEY(tess_id),
-        KEY(kind),
         FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id),
-        FOREIGN KEY (stack_id)  REFERENCES  stackSumSkyfile(stack_id),
-        FOREIGN KEY (warp_id, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id)
+        FOREIGN KEY (warp1, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id),
+        FOREIGN KEY (warp2, skycell_id, tess_id)  REFERENCES  warpSkyfile(warp_id, skycell_id, tess_id),
+        FOREIGN KEY (stack1)  REFERENCES  stackSumSkyfile(stack_id),
+        FOREIGN KEY (stack2)  REFERENCES  stackSumSkyfile(stack_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
 CREATE TABLE diffSkyfile (
         diff_id BIGINT,
+        skycell_id VARCHAR(64),
         uri VARCHAR(255),
         path_base VARCHAR(255),
@@ -996 +999 @@
         KEY(good_frac),
         KEY(fault),
+        KEY(skycell_id),
         FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
@@ -1002 +1006 @@
         magic_id BIGINT AUTO_INCREMENT,
         exp_id BIGINT,
+        diff_id BIGINT,
         state VARCHAR(64),
         workdir VARCHAR(255),
@@ -1015 +1020 @@
         KEY(label),
         KEY(fault),
-        FOREIGN KEY (exp_id)  REFERENCES  rawExp(exp_id)
+        FOREIGN KEY (exp_id)  REFERENCES rawExp(exp_id),
+        FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
 CREATE TABLE magicInputSkyfile (
         magic_id BIGINT,
-        diff_id BIGINT,
         node VARCHAR(64),
-        PRIMARY KEY(magic_id, diff_id),
-        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id),
-        FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)
+        PRIMARY KEY(magic_id),
+        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 

branches/bills_081204/ippTools/src/difftool.c

@@ -47 +47 @@
 
 static bool setdiffRunState(pxConfig *config, psS64 diff_id, const char *state);
+static bool diffRunComplete(pxConfig *config);
 
 # define MODECASE(caseName, func) \
@@ -108 +109 @@
     // required options
     PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false);
-    PXOPT_LOOKUP_STR(skycell_id, config->args, "-skycell_id", true, false);
     PXOPT_LOOKUP_STR(tess_id, config->args, "-tess_id", true, false);
     PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);
     PXOPT_LOOKUP_STR(reduction, config->args, "-reduction", false, false);
+    PXOPT_LOOKUP_S64(exp_id, config->args, "-exp_id", false, false);
 
     // default
@@ -125 +126 @@
             NULL,       // dvodb
             registered,
-            skycell_id,
+            exp_id,
             tess_id
     );
@@ -178 +179 @@
 
     // optional
-    PXOPT_LOOKUP_S64(stack_id, config->args, "-stack_id", false, false);
-    PXOPT_LOOKUP_S64(warp_id, config->args, "-warp_id", false, false);
-    PXOPT_LOOKUP_STR(kind, config->args, "-kind", false, false);
-
-    // defaults to false
-    PXOPT_LOOKUP_BOOL(template, config->args, "-template", false);
-
-    // must provide either stack_id or warp_id but not BOTH
-    if (!(stack_id || warp_id)) {
-        psError(PS_ERR_UNKNOWN, true, "either -stack_id or -warp_id must be specified");
-        return false;
-    }
-    if (stack_id && warp_id) {
-        psError(PS_ERR_UNKNOWN, true, "either -stack_id or -warp_id must be specified");
-        return false;
-    }
-
-    // if a warp_id was provided we need to lookup the skycell_id and tess_id
-    // from the warpRun
-    psString skycell_id = NULL;
+    PXOPT_LOOKUP_S64(stack1, config->args, "-stack1", false, false);
+    PXOPT_LOOKUP_S64(stack2, config->args, "-stack2", false, false);
+    PXOPT_LOOKUP_S64(warp1, config->args, "-warp1", false, false);
+    PXOPT_LOOKUP_S64(warp2, config->args, "-warp2", false, false);
+    PXOPT_LOOKUP_STR(skycell_id, config->args, "-skycell_id", true, false);
+
+    // must provide either stack1 or warp1 but not BOTH
+    if (!(stack1 || warp1)) {
+        psError(PS_ERR_UNKNOWN, true, "either -stack1 or -warp1 must be specified");
+        return false;
+    }
+    if (stack1 && warp1) {
+        psError(PS_ERR_UNKNOWN, true, "either -stack1 or -warp1 must be specified");
+        return false;
+    }
+    // must provide either stack2 or warp2 but not BOTH
+    if (!(stack2 || warp2)) {
+        psError(PS_ERR_UNKNOWN, true, "either -stack2 or -warp2 must be specified");
+        return false;
+    }
+    if (stack2 && warp2) {
+        psError(PS_ERR_UNKNOWN, true, "either -stack2 or -warp2 must be specified");
+        return false;
+    }
+
+    // if a warp1 was provided we need to lookup the and tess_id from the diffRun
     psString tess_id = NULL;
-    if (warp_id) {
+    if (warp1) {
         if (!p_psDBRunQuery(config->dbh, "SELECT * from diffRun WHERE diff_id = %" PRId64, diff_id)) {
             psError(PS_ERR_UNKNOWN, false, "database error");
@@ -217 +224 @@
 
         diffRunRow *run = diffRunObjectFromMetadata(output->data[0]);
-        skycell_id = run->skycell_id;
         tess_id = run->tess_id;
     }
@@ -228 +234 @@
     if (!diffInputSkyfileInsert(config->dbh,
             diff_id,
-            template,
-            stack_id ? stack_id : PS_MAX_S64, // defined or NULL
-            warp_id ? warp_id : PS_MAX_S64, // defined or NULL
+            warp1 ? warp1 : PS_MAX_S64, // defined or NULL
+            warp2 ? warp2 : PS_MAX_S64, // defined or NULL
+            stack1 ? stack1 : PS_MAX_S64, // defined or NULL
+            stack2 ? stack2 : PS_MAX_S64, // defined or NULL
             skycell_id,
-            tess_id,
-            kind
+            tess_id
         )) {
         if (!psDBRollback(config->dbh)) {
@@ -300 +306 @@
     PXOPT_COPY_STR(config->args, where, "-skycell_id", "skycell_id", "==");
     PXOPT_COPY_STR(config->args, where,  "-tess_id", "tess_id", "==");
-    PXOPT_COPY_STR(config->args, where,  "-kind", "kind", "==");
 
     PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
@@ -349 +354 @@
     }
 
-    if (!p_psDBRunQuery(config->dbh, query, whereClause, whereClause)) {
+    if (!p_psDBRunQuery(config->dbh, query, whereClause, whereClause, whereClause, whereClause)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
         psFree(whereClause);
@@ -471 +476 @@
 
     PXOPT_LOOKUP_S64(diff_id, config->args, "-diff_id", true, false); // required
+    PXOPT_LOOKUP_STR(skycell_id, config->args, "-skycell_id", true, false);
     PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);
     PXOPT_LOOKUP_STR(uri, config->args, "-uri", (code == 0), false);
@@ -503 +509 @@
     if (!diffSkyfileInsert(config->dbh,
                            diff_id,
+                           skycell_id,
                            uri,
                            path_base,
@@ -533 +540 @@
     }
 
-    if (!setdiffRunState(config, diff_id, "full")) {
+    if (!diffRunComplete(config)) {
         if (!psDBRollback(config->dbh)) {
             psError(PS_ERR_UNKNOWN, false, "database error");
@@ -546 +553 @@
         return false;
     }
+
 
     return true;
@@ -762 +770 @@
                          psS64 template_warp_id, // Warp identifier for template image, PS_MAX_S64 for none
                          psS64 template_stack_id, // Stack identifier for template image, PS_MAX_S64 for none
+                         psS64 exp_id, // exposure id for input_warp_id (if defined)
                          pxConfig *config // Configuration
                          )
@@ -791 +800 @@
             NULL,       // dvodb
             registered,
-            skycell_id,
+            exp_id,
             tess_id
     );
@@ -817 +826 @@
     if (!diffInputSkyfileInsert(config->dbh,
             run->diff_id,
-            true,
+            input_warp_id,
+            template_warp_id,
+            input_stack_id,
             template_stack_id,
-            template_warp_id,
             skycell_id,
-            tess_id,
-            NULL    // kind
-        )) {
-        if (!psDBRollback(config->dbh)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-        }
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        return false;
-    }
-
-    // Input
-    if (!diffInputSkyfileInsert(config->dbh,
-            run->diff_id,
-            false,
-            input_stack_id,
-            input_warp_id,
-            skycell_id,
-            tess_id,
-            NULL    // kind
+            tess_id
         )) {
         if (!psDBRollback(config->dbh)) {
@@ -887 +879 @@
     PXOPT_LOOKUP_S64(input_warp_id, config->args, "-input_warp_id", false, false);
     PXOPT_LOOKUP_S64(input_stack_id, config->args, "-input_stack_id", false, false);
+    PXOPT_LOOKUP_S64(exp_id, config->args, "-exp_id", false, false);
     PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
 
@@ -906 +899 @@
         psError(PS_ERR_BAD_PARAMETER_VALUE, true,
                 "No input has been defined (-input_stack_id or -input_warp_id)");
+        return false;
+    }
+    if (input_warp_id && !exp_id) {
+        psError(PS_ERR_BAD_PARAMETER_VALUE, true,
+                "-exp_id is required with -input_warp_id.");
         return false;
     }
@@ -916 +914 @@
                       template_warp_id ? template_warp_id : PS_MAX_S64,
                       template_stack_id ? template_stack_id : PS_MAX_S64,
+                      exp_id,
                       config)) {
         psError(PS_ERR_UNKNOWN, false, "failed to create populated diffRun");
@@ -945 +944 @@
     PXOPT_COPY_STR(config->args, warpWhere, "-filter", "rawExp.filter", "==");
     PXOPT_COPY_STR(config->args, warpWhere, "-warp_label", "warpRun.label", "==");
-    PXOPT_COPY_STR(config->args, warpWhere,  "-kind", "warpsToDiff.kind", "==");
     PXOPT_COPY_F32(config->args, warpWhere,  "-good_frac", "warpSkyfile.good_frac", ">=");
     PXOPT_COPY_STR(config->args, stackWhere, "-stack_label", "stackRun.label", "==");
@@ -1048 +1046 @@
             continue;
         }
+        psS64 exp_id = psMetadataLookupS64(&mdok, row, "exp_id");
+        if (!mdok) {
+            psWarning("exp_id not found --- ignoring row %ld", i);
+            continue;
+        }
 
         if (!populatedrun(list, workdir, skycell_id, tess_id, label, reduction, warp_id,
-                          PS_MAX_S64, PS_MAX_S64, stack_id, config)) {
+                          PS_MAX_S64, PS_MAX_S64, stack_id, exp_id, config)) {
             psWarning("Unable to add run for %s,%s,%" PRId64 ",%" PRId64, skycell_id, tess_id,
                       warp_id, stack_id);
@@ -1274 +1277 @@
 }
 
+static bool diffRunComplete(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    // look for completed diffRuns
+    psString query = pxDataGet("difftool_completed_runs.sql");
+    if (!query) {
+        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(query);
+        return false;
+    }
+    psFree(query);
+
+    psArray *output = p_psDBFetchResult(config->dbh);
+    if (!output) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+    if (!psArrayLength(output)) {
+        psTrace("difftool", PS_LOG_INFO, "no rows found");
+        psFree(output);
+        return true;
+    }
+    for (long i = 0; i < psArrayLength(output); i++) {
+        psMetadata *row = output->data[i];
+
+        psS64 diff_id = psMetadataLookupS64(NULL, row, "diff_id");
+
+        // set diffRun.state to 'stop'
+        if (!setdiffRunState(config, diff_id, "full")) {
+            psError(PS_ERR_UNKNOWN, false, "failed to change diffRun.state for diff_id: %" PRId64,
+                diff_id);
+            psFree(output);
+            return false;
+        }
+    }
+
+    return true;
+}
+

branches/bills_081204/ippTools/src/difftoolConfig.c

@@ -48 +48 @@
     psMetadata *definerunArgs = psMetadataAlloc();
     psMetadataAddStr(definerunArgs, PS_LIST_TAIL, "-workdir", 0,            "define workdir (required)", NULL);
-    psMetadataAddStr(definerunArgs, PS_LIST_TAIL, "-skycell_id",  0,            "define skycell ID (required)", NULL);
     psMetadataAddStr(definerunArgs, PS_LIST_TAIL, "-tess_id",  0,            "define tessellation ID (required)", NULL);
     psMetadataAddStr(definerunArgs, PS_LIST_TAIL, "-label",  0,            "define label", NULL);
@@ -65 +64 @@
     psMetadataAddS64(addinputskyfileArgs, PS_LIST_TAIL, "-stack_id", 0,            "define stack ID", 0);
     psMetadataAddS64(addinputskyfileArgs, PS_LIST_TAIL, "-warp_id", 0,            "define warp ID", 0);
-    psMetadataAddStr(addinputskyfileArgs, PS_LIST_TAIL, "-kind", 0,            "define kind", NULL);
     psMetadataAddBool(addinputskyfileArgs, PS_LIST_TAIL, "-template",  0,            "this sky cell file is the subtrahend", false);
 
@@ -74 +72 @@
     psMetadataAddStr(inputskyfileArgs, PS_LIST_TAIL, "-skycell_id", 0,            "search by skycell ID", NULL);
     psMetadataAddStr(inputskyfileArgs, PS_LIST_TAIL, "-tess_id", 0,            "search by tess ID", NULL);
-    psMetadataAddStr(inputskyfileArgs, PS_LIST_TAIL, "-kind", 0,            "search by kind", NULL);
     psMetadataAddBool(inputskyfileArgs, PS_LIST_TAIL, "-template",  0,            "find only subtrahend", false);
     psMetadataAddBool(inputskyfileArgs, PS_LIST_TAIL, "-input", 0, "find only minuend", false);
@@ -90 +87 @@
     psMetadata *adddiffskyfileArgs = psMetadataAlloc();
     psMetadataAddS64(adddiffskyfileArgs, PS_LIST_TAIL, "-diff_id", 0,            "define warp ID (required)", 0);
+    psMetadataAddStr(adddiffskyfileArgs, PS_LIST_TAIL, "-skycell_id", 0,       "define skycell of file (required)", 0);
     psMetadataAddS16(adddiffskyfileArgs, PS_LIST_TAIL, "-code",  0,            "set fault code", 0);
     psMetadataAddStr(adddiffskyfileArgs, PS_LIST_TAIL, "-uri", 0,            "define URI of file", 0);
@@ -155 +153 @@
     psMetadataAddStr(definebyqueryArgs, PS_LIST_TAIL, "-warp_label", 0, "search by warp label", NULL);
     psMetadataAddF32(definebyqueryArgs, PS_LIST_TAIL, "-good_frac", 0, "minimum good fraction of skycell", NAN);
-    psMetadataAddStr(definebyqueryArgs, PS_LIST_TAIL, "-kind", 0, "search by kind", NULL);
     psMetadataAddStr(definebyqueryArgs, PS_LIST_TAIL, "-workdir", 0, "define workdir (required)", NULL);
     psMetadataAddStr(definebyqueryArgs, PS_LIST_TAIL, "-label",  0, "define label", NULL);

branches/bills_081204/ippTools/src/magictool.c

@@ -128 +128 @@
     PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
 
-    // Create temporary table of the best diffs
-    {
-        psString query = pxDataGet("magictool_definebyquery_temp_create.sql");
-        if (!query) {
-            psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-            return false;
-        }
-
-        if (!p_psDBRunQuery(config->dbh, query)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-            return false;
-        }
-        psFree(query);
-    }
-
-    // Insert list of best diffs into temporary table
-    {
-        psString query = pxDataGet("magictool_definebyquery_temp_insert.sql");
-        if (!query) {
-            psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-            return false;
-        }
-
-        psMetadata *where = psMetadataAlloc();
-        PXOPT_COPY_S64(config->args, where, "-exp_id", "exp_id", "==");
-        PXOPT_COPY_STR(config->args, where, "-diff_label", "diffRun.label", "==");
-        PXOPT_COPY_F32(config->args, where, "-good_frac", "warpSkyfile.good_frac", ">=");
-
-        psString whereClause = NULL;    // WHERE conditions
-        if (psListLength(where->list)) {
-            whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-            psStringPrepend(&whereClause, "\n AND ");
-        }
-        psFree(where);
-
-        if (!p_psDBRunQuery(config->dbh, query, whereClause)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-            psFree(whereClause);
-            psFree(query);
-            return false;
-        }
-        psFree(whereClause);
-        psFree(query);
-    }
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, where, "-exp_id", "exp_id", "==");
+    PXOPT_COPY_STR(config->args, where, "-diff_label", "diffRun.label", "==");
+
 
     // Get list of exposures ready to magic
@@ -181 +141 @@
         }
 
-        psString magicSkyCellNumsWhere = NULL;    // WHERE conditions for magicSkyCellNums
-        {
-            psMetadata *where = psMetadataAlloc();
-            PXOPT_COPY_S64(config->args, where, "-exp_id", "exp_id", "==");
-            PXOPT_COPY_STR(config->args, where, "-diff_label", "diffRun.label", "==");
-            PXOPT_COPY_F32(config->args, where, "-good_frac", "warpSkyfile.good_frac", ">=");
-
-            if (psListLength(where->list)) {
-                magicSkyCellNumsWhere = psDBGenerateWhereConditionSQL(where, NULL);
-                psStringPrepend(&magicSkyCellNumsWhere, "\n AND ");
-            }
-            psFree(where);
-        }
-
-        // "available" means only concern ourselves with exposures that have all diffs completed, unless we're
-        // told to only take what's available.
-        // "new" means we want a new run even if there's already a magic run defined
+        // "available" means queue magic run even though the diffRun has skycells that did not complete
+        // "rerun" means we want a new run even if there's already a magic run defined for the exposure
         PXOPT_LOOKUP_BOOL(available, config->args, "-available", false);
-        PXOPT_LOOKUP_BOOL(new, config->args, "-new", false);
+        PXOPT_LOOKUP_BOOL(rerun, config->args, "-rerun", false);
 
         psString queryWhere = NULL;     // WHERE conditions for entire query
-        if (available) {
-            psStringAppend(&queryWhere, " WHERE num_done = num_todo");
-        }
-        if (new) {
+        if (!available) {
+            psStringAppend(&queryWhere, " \nWHERE diffRun.state = 'full'");
+        } else {
+            // what if no skycells for the diff run completed?
+        }
+        if (!rerun) {
             const char *newWhere = " magic_id IS NULL"; // String to add
             if (queryWhere) {
                 psStringAppend(&queryWhere, " AND %s", newWhere);
             } else {
-                psStringAppend(&queryWhere, " WHERE %s", newWhere);
+                psStringAppend(&queryWhere, "\nWHERE %s", newWhere);
             }
         }
-        if (queryWhere) {
-            psStringAppend(&query, " %s", queryWhere);
+        if (!queryWhere) {
+            psStringAppend(&queryWhere, " ");
+        }
+
+        if (!p_psDBRunQuery(config->dbh, query, queryWhere)) {
+            psError(PS_ERR_UNKNOWN, false, "database error");
             psFree(queryWhere);
-        }
-
-
-        if (!p_psDBRunQuery(config->dbh, query, magicSkyCellNumsWhere ? magicSkyCellNumsWhere : "")) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-            psFree(magicSkyCellNumsWhere);
             psFree(query);
             return false;
         }
-        psFree(magicSkyCellNumsWhere);
+        psFree(queryWhere);
         psFree(query);
     }
@@ -262 +207 @@
         psMetadata *row = output->data[i]; // Row of interest
         psS64 exp_id = psMetadataLookupS64(NULL, row, "exp_id"); // Exposure identifier
+        psS64 diff_id = psMetadataLookupS64(NULL, row, "diff_id"); // Exposure identifier
 
         // create a new magicRun for this group
-        magicRunRow *run = magicRunRowAlloc(0, exp_id, "run", workdir, "dirty", label, dvodb, registered, 0);
+        magicRunRow *run = magicRunRowAlloc(0, exp_id, diff_id, "run", workdir, "dirty", label, dvodb, registered, 0);
         if (!run) {
             psAbort("failed to alloc magicRun object");
@@ -297 +243 @@
         {
             psString idString = NULL;
-            psStringAppend(&idString, "%" PRId64, exp_id);
-            psStringSubstitute(&thisInsert, idString, "@EXP_ID@");
+            psStringAppend(&idString, "%" PRId64, diff_id);
+            psStringSubstitute(&thisInsert, idString, "@DIFF_ID@");
             psFree(idString);
         }
@@ -340 +286 @@
     PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false);
     PXOPT_LOOKUP_S64(exp_id, config->args, "-exp_id", true, false);
+    PXOPT_LOOKUP_S64(diff_id, config->args, "-diff_id", true, false);
 
     // optional
@@ -350 +297 @@
             0,          // ID
             exp_id,
+            diff_id,
             "reg",      // state
             workdir,
@@ -414 +362 @@
             config->dbh,
             magic_id,
-            diff_id,
             node
     );

branches/bills_081204/ippTools/src/magictoolConfig.c

@@ -55 +55 @@
     psMetadataAddS64(definebyqueryArgs, PS_LIST_TAIL, "-exp_id", 0, "search exp_id", 0);
     psMetadataAddStr(definebyqueryArgs, PS_LIST_TAIL, "-diff_label", 0, "select diff label", NULL);
-    psMetadataAddF32(definebyqueryArgs, PS_LIST_TAIL, "-good_frac", 0, "limit good_frac", NAN);
     psMetadataAddBool(definebyqueryArgs, PS_LIST_TAIL, "-available", 0, "process what's immediately available?", false);
-    psMetadataAddBool(definebyqueryArgs, PS_LIST_TAIL, "-new", 0, "generate new run even if existing?", false);
+    psMetadataAddBool(definebyqueryArgs, PS_LIST_TAIL, "-rerun", 0, "generate new run even if existing?", false);
     psMetadataAddBool(definebyqueryArgs, PS_LIST_TAIL, "-simple", 0, "use the simple output format", false);