Changeset 18029 for trunk/dbconfig

Timestamp:

Jun 9, 2008, 12:55:57 PM (18 years ago)

Author:

jhoblitt

Message:

merge faketool branch

Location:

trunk/dbconfig

Files:

• cam.md (modified) (1 diff)
• changes.txt (modified) (1 diff)
• warp.md (modified) (3 diffs)

trunk/dbconfig/cam.md

@@ -16 +16 @@
 # 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

trunk/dbconfig/changes.txt

@@ -173 +173 @@
 -- 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;

trunk/dbconfig/warp.md

@@ -1 @@
-# $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 +11 @@
 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 +26 @@
 
 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