Changeset 33032 for trunk/dbconfig

Timestamp:

Dec 27, 2011, 4:10:43 PM (15 years ago)

Author:

heather

Message:

commiting the db changes

File:

• trunk/dbconfig/changes.txt (modified) (1 diff)

trunk/dbconfig/changes.txt

@@ -2184 +2184 @@
 
 alter table addRun add column stage_extra1 int after stage_id;
+
+
+-- This adds new features to addRun, first add the tables:
+
+CREATE TABLE mergedvodbRun (
+       merge_id BIGINT AUTO_INCREMENT,
+       minidvodb_id BIGINT,
+       mergedvodb VARCHAR(64) NOT NULL,
+       mergedvodb_path VARCHAR(255) NOT NULL,
+       state VARCHAR(64) NOT NULL,
+       creation_date TIMESTAMP,
+       PRIMARY KEY(merge_id), KEY(minidvodb_id), KEY(mergedvodb), INDEX(merge_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+
+CREATE TABLE mergedvodbProcessed (
+      merge_id BIGINT,
+      dtime_verify FLOAT,
+      dtime_merge FLOAT,
+      dtime_script FLOAT,
+      epoch timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+      fault SMALLINT NOT NULL,
+      UNIQUE KEY (merge_id),
+      KEY(merge_id),
+      FOREIGN KEY(merge_id) REFERENCES mergedvodbRun(merge_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE mergedvodbCopy (
+    mergedvodbcopy_id     BIGINT AUTO_INCREMENT,
+    merge_id     BIGINT,
+    mergedvodb_rsync_path VARCHAR(255) NOT NULL,
+    destination_host VARCHAR(255) NOT NULL,
+    fault  SMALLINT NOT NULL,
+    state   VARCHAR(64) NOT NULL,
+    epoch TIMESTAMP,
+    dtime FLOAT,
+    PRIMARY KEY(mergedvodbcopy_id),
+    KEY(merge_id),
+    FOREIGN KEY(merge_id) REFERENCES mergedvodbRun(merge_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+alter table mergedvodbProcessed add column merge_order BIGINT after merge_id;
+
+-- then copy over the info into the 2 tables:
+ insert into mergedvodbRun ( minidvodb_id, mergedvodb,mergedvodb_path,
+   state, creation_date)  select minidvodb_id, minidvodb_group,mergedvodb_path,
+minidvodbRun.state,
+   minidvodbRun.creation_date from minidvodbRun where minidvodb_id >= 0;
+
+insert into mergedvodbProcessed (merge_id, merge_order, dtime_verify,
+dtime_merge, dtime_script, epoch, fault) select merge_id, merge_order,
+minidvodbProcessed.dtime_verify, minidvodbProcessed.dtime_merge,
+minidvodbProcessed.dtime_script, minidvodbProcessed.epoch,
+minidvodbProcessed.fault from minidvodbProcessed join mergedvodbRun using
+(minidvodb_id) where merge_id > -1 ;
+
+--then clean up the tables:
+-- once the tables have had the columns migrated over, then and only then do this:
+
+alter table minidvodbRun drop column mergedvodb_path;
+alter table minidvodbProcessed drop column dtime_verify;
+alter table minidvodbProcessed drop column merge_order;
+alter table minidvodbProcessed drop column dtime_merge;
+alter table minidvodbProcessed drop column mergedvodb_path;
+
+
+--then to keep compatible with old camruns (stage_extra1 = negative means it used a destreaked smf)
+
+--it's commented out as it only needs to be run once (and only on gpc1)
+--update addRun set stage_extra1 = -1 where stage = 'cam' <= 483624; 
+