Changeset 17967 for branches/faketool/dbconfig/changes.txt

Timestamp:

Jun 6, 2008, 4:03:01 PM (18 years ago)

Author:

jhoblitt

Message:

changes for faketool

File:

• branches/faketool/dbconfig/changes.txt (modified) (1 diff)

branches/faketool/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);
+
+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,
+    'run',
+    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;