Index: trunk/dbconfig/cam.md =================================================================== --- trunk/dbconfig/cam.md (revision 18014) +++ trunk/dbconfig/cam.md (revision 18029) @@ -16,6 +16,6 @@ # the camPendingExp row gets deleted so we can not put a fkey on cam_id cam_id S64 0 # Primary Key - chip_id S64 0 # Primary Key fkey(cam_id, chip_id) ref camRun(cam_id, chip_id) - uri STR 255 + uri STR 255 # fkey(cam_id) ref camRun(cam_id) + bg F32 0.0 bg_stdev F32 0.0 Index: trunk/dbconfig/changes.txt =================================================================== --- trunk/dbconfig/changes.txt (revision 18014) +++ trunk/dbconfig/changes.txt (revision 18029) @@ -173,4 +173,184 @@ -- 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 --- just drop/recreate any fake* tables prior to this point +-- 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, + 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; Index: trunk/dbconfig/warp.md =================================================================== --- trunk/dbconfig/warp.md (revision 18014) +++ trunk/dbconfig/warp.md (revision 18029) @@ -1,3 +1,3 @@ -# $Id: warp.md,v 1.16 2008-05-23 22:52:48 price Exp $ +# $Id: warp.md,v 1.17 2008-06-09 22:55:57 jhoblitt Exp $ # @@ -11,5 +11,5 @@ warpRun METADATA warp_id S64 0 # Primary Key AUTO_INCREMENT - cam_id S64 0 # Key INDEX(warp_id, cam_id) fkey(cam_id) ref camProcessedExp(cam_id) + fake_id S64 0 # Key INDEX(warp_id, fake_id) fkey(fake_id) ref camProcessedExp(fake_id) mode STR 64 # Key state STR 64 # Key @@ -26,10 +26,7 @@ warpSkyCellMap METADATA - warp_id S64 0 # Primary Key fkey(warp_id, cam_id) ref warpRun(warp_id, cam_id) + warp_id S64 0 # Primary Key fkey(warp_id) ref warpRun(warp_id) skycell_id STR 64 # Primary Key tess_id STR 64 # Primary Key - cam_id S64 0 # Primary Key -# class is not yet consistently carried through pXtools -# class STR 64 # Primary Key class_id STR 64 # Primary Key fault S16 0 # Key