Changeset 18520 for trunk/ippTools/share/pxadmin_create_tables.sql

Timestamp:

Jul 14, 2008, 3:18:02 PM (18 years ago)

Author:

Paul Price

Message:

Updating tables for magic (adding fault codes, removing skyfilemask)

File:

• trunk/ippTools/share/pxadmin_create_tables.sql (modified) (1 diff)

trunk/ippTools/share/pxadmin_create_tables.sql

@@ -719 +719 @@
 CREATE TABLE diffInputSkyfile (diff_id BIGINT, template TINYINT, stack_id BIGINT, warp_id BIGINT, skycell_id VARCHAR(64), tess_id VARCHAR(64), kind VARCHAR(64), PRIMARY KEY(diff_id, template), KEY(stack_id), KEY(warp_id), 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)) ENGINE=innodb DEFAULT CHARSET=latin1;
 CREATE TABLE diffSkyfile (diff_id BIGINT, uri VARCHAR(255), path_base VARCHAR(255), bg DOUBLE, bg_stdev DOUBLE, stamps_num INT, stamps_rms FLOAT, sources INT, dtime_diff FLOAT, hostname VARCHAR(64), good_frac FLOAT, fault SMALLINT, PRIMARY KEY(diff_id), KEY(good_frac), KEY(fault), FOREIGN KEY (diff_id)  REFERENCES  diffRun(diff_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
-CREATE TABLE magicRun (magic_id BIGINT AUTO_INCREMENT, state VARCHAR(64), workdir VARCHAR(255), workdir_state VARCHAR(255), label VARCHAR(64), dvodb VARCHAR(255), registered DATETIME, PRIMARY KEY(magic_id), KEY(magic_id), KEY(state), KEY(workdir_state), KEY(label)) 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)) ENGINE=innodb DEFAULT CHARSET=latin1;
-CREATE TABLE magicTree (magic_id BIGINT, node VARCHAR(64), dep VARCHAR(64), KEY(magic_id), KEY(node), KEY(dep), INDEX(magic_id, node), FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
-CREATE TABLE magicNodeResult (magic_id BIGINT, node VARCHAR(64), uri VARCHAR(255), PRIMARY KEY(magic_id, node), FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id), FOREIGN KEY (magic_id, node)  REFERENCES  magicTree(magic_id, node)) ENGINE=innodb DEFAULT CHARSET=latin1;
-CREATE TABLE magicMask (magic_id BIGINT, uri VARCHAR(255), PRIMARY KEY(magic_id), FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
-CREATE TABLE magicSkyfileMask (magic_id BIGINT, diff_id BIGINT, uri VARCHAR(255), PRIMARY KEY(magic_id, diff_id), KEY(uri), FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id), FOREIGN KEY (magic_id, diff_id)  REFERENCES  magicInputSkyfile(magic_id, diff_id), FOREIGN KEY (magic_id)  REFERENCES  magicMask(magic_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE magicRun (
+        magic_id BIGINT AUTO_INCREMENT,
+        state VARCHAR(64),
+        workdir VARCHAR(255),
+        workdir_state VARCHAR(255),
+        label VARCHAR(64),
+        dvodb VARCHAR(255),
+        registered DATETIME,
+        fault SMALLINT,
+        PRIMARY KEY(magic_id),
+        KEY(magic_id),
+        KEY(state),
+        KEY(workdir_state),
+        KEY(label),
+        KEY(fault)
+) 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)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE magicTree (
+        magic_id BIGINT,
+        node VARCHAR(64),
+        dep VARCHAR(64),
+        KEY(magic_id),
+        KEY(node),
+        KEY(dep),
+        INDEX(magic_id, node),
+        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE magicNodeResult (
+        magic_id BIGINT,
+        node VARCHAR(64),
+        uri VARCHAR(255),
+        fault SMALLINT,
+        PRIMARY KEY(magic_id, node),
+        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id),
+        FOREIGN KEY (magic_id, node)  REFERENCES  magicTree(magic_id, node),
+        KEY(fault)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+CREATE TABLE magicMask (
+        magic_id BIGINT,
+        uri VARCHAR(255),
+        streaks INT,
+        fault SMALLINT,
+        PRIMARY KEY(magic_id),
+        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id),
+        KEY(fault)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
 CREATE TABLE calDB (cal_id BIGINT AUTO_INCREMENT, dvodb VARCHAR(64), state VARCHAR(64), PRIMARY KEY(cal_id), KEY(cal_id)) ENGINE=innodb DEFAULT CHARSET=latin1;
 CREATE TABLE calRun (cal_id BIGINT AUTO_INCREMENT, region VARCHAR(64), last_step VARCHAR(64), state VARCHAR(64), PRIMARY KEY(cal_id), KEY(cal_id), KEY(last_step), FOREIGN KEY (cal_id)  REFERENCES  calDB(cal_id)) ENGINE=innodb DEFAULT CHARSET=latin1;