Changeset 31038 for trunk/dbconfig

Timestamp:

Mar 24, 2011, 2:09:53 PM (15 years ago)

Author:

watersc1

Message:

implementation of summit_id index in summit{Exp,Imfile},pzDownload{Exp,Imfile},newExp tables.

Location:

trunk/dbconfig

Files:

• changes.txt (modified) (1 diff)
• new.md (modified) (1 diff)
• summitcopy.md (modified) (4 diffs)

trunk/dbconfig/changes.txt

@@ -2007 +2007 @@
 
 ALTER TABLE stackSumSkyfile ADD column dtime_convolve FLOAT AFTER dtime_match_stdev;
+
+-- Version 1.1.69
+
+ALTER TABLE summitImfile DROP FOREIGN KEY summitImfile_ibfk_1;
+ALTER TABLE pzDownloadExp DROP FOREIGN KEY pzDownloadExp_ibfk_1;
+
+ALTER TABLE summitExp DROP PRIMARY KEY;
+ALTER TABLE summitExp ADD COLUMN summit_id BIGINT NOT NULL AUTO_INCREMENT FIRST,  ADD PRIMARY KEY (summit_id);
+ALTER TABLE summitExp ADD KEY (exp_name,camera,telescope);
+
+
+ALTER TABLE pzDownloadImfile DROP FOREIGN KEY pzDownloadImfile_ibfk_2;
+
+ALTER TABLE summitImfile ADD COLUMN summit_id BIGINT FIRST;
+UPDATE summitImfile,summitExp SET summitImfile.summit_id = summitExp.summit_id WHERE summitImfile.exp_name = summitExp.exp_name AND summitImfile.camera = summitExp.camera AND summitImfile.telescope = summitExp.telescope;
+
+ALTER TABLE summitImfile DROP PRIMARY KEY, ADD PRIMARY KEY (summit_id,class,class_id);
+ALTER TABLE summitImfile ADD KEY (exp_name,camera,telescope,class,class_id);
+ALTER TABLE summitImfile ADD FOREIGN KEY(summit_id) REFERENCES summitExp(summit_id);
+ALTER TABLE summitImfile ADD FOREIGN KEY(exp_name,camera,telescope) REFERENCES summitExp(exp_name,camera,telescope);
+
+
+ALTER TABLE pzDownloadImfile DROP FOREIGN KEY pzDownloadImfile_ibfk_1;
+
+ALTER TABLE pzDownloadExp ADD COLUMN summit_id BIGINT FIRST;
+UPDATE pzDownloadExp,summitExp SET pzDownloadExp.summit_id = summitExp.summit_id WHERE pzDownloadExp.exp_name = summitExp.exp_name AND pzDownloadExp.camera = summitExp.camera AND pzDownloadExp.telescope = summitExp.telescope;
+
+ALTER TABLE pzDownloadExp DROP PRIMARY KEY, ADD PRIMARY KEY (summit_id);
+ALTER TABLE pzDownloadExp ADD KEY (exp_name,camera,telescope);
+ALTER TABLE pzDownloadExp ADD FOREIGN KEY(summit_id) REFERENCES summitExp(summit_id);
+ALTER TABLE pzDownloadExp ADD FOREIGN KEY(exp_name,camera,telescope) REFERENCES summitExp(exp_name,camera,telescope);
+
+
+ALTER TABLE pzDownloadImfile ADD COLUMN summit_id BIGINT FIRST;
+UPDATE pzDownloadImfile,pzDownloadExp SET pzDownloadImfile.summit_id = pzDownloadExp.summit_id WHERE pzDownloadImfile.exp_name = pzDownloadExp.exp_name AND pzDownloadImfile.camera = pzDownloadExp.camera AND pzDownloadImfile.telescope = pzDownloadExp.telescope;
+
+ALTER TABLE pzDownloadImfile DROP PRIMARY KEY, ADD PRIMARY KEY (summit_id,class,class_id);
+ALTER TABLE pzDownloadImfile ADD FOREIGN KEY(summit_id) REFERENCES pzDownloadExp(summit_id);
+ALTER TABLE pzDownloadImfile ADD FOREIGN KEY(summit_id,class,class_id) REFERENCES summitImfile(summit_id,class,class_id);
+ALTER TABLE pzDownloadImfile ADD FOREIGN KEY(exp_name,camera,telescope) REFERENCES summitExp(exp_name,camera,telescope);
+ALTER TABLE pzDownloadImfile ADD FOREIGN KEY(exp_name,camera,telescope,class,class_id) REFERENCES summitImfile(exp_name,camera,telescope,class,class_id);
+
+
+ALTER TABLE newExp ADD COLUMN summit_id BIGINT AFTER exp_id;
+UPDATE newExp,pzDownloadExp SET newExp.summit_id = pzDownloadExp.summit_id WHERE newExp.tmp_exp_name = pzDownloadExp.exp_name AND newExp.tmp_camera = pzDownloadExp.camera AND newExp.tmp_telescope = pzDownloadExp.telescope;
+
+ALTER TABLE newExp ADD KEY (summit_id);
+
+
+UPDATE dbversion set schema_version = '1.1.69', updated= CURRENT_TIMESTAMP();
+
+
+

trunk/dbconfig/new.md

@@ -1 +1 @@
 newExp METADATA
     exp_id      S64         0       # Primary Key AUTO_INCREMENT
+    summit_id   S64         0       # Key 
     tmp_exp_name STR        64      # Key
     tmp_camera    STR       64      # Key

trunk/dbconfig/summitcopy.md

@@ -11 +11 @@
 # -1 as NULL or 0 might be a valid value (empty fileset)
 summitExp METADATA
-    exp_name    STR         64      # Primary Key
-    camera      STR         64      # Primary Key
-    telescope   STR         64      # Primary Key
+    summit_id   S64         0       # Primary Key AUTO_INCREMENT
+    exp_name    STR         64      # Key
+    camera      STR         64      # Key
+    telescope   STR         64      # Key
     dateobs     UTC         NULL
     exp_type    STR         64
@@ -26 +27 @@
 # list of source images -- updated as exposures/filesets are queried
 summitImfile METADATA
-    exp_name    STR         64      # Primary Key fkey(exp_name, camera, telescope) ref summitExp(exp_name, camera, telescope)
-    camera      STR         64      # Primary Key
-    telescope   STR         64      # Primary Key
+    summit_id   S64         0       # Primary Key fkey(summit_id) ref summitExp(summit_id)
+    exp_name    STR         64      # Key fkey(exp_name, camera, telescope) ref summitExp(exp_name, camera, telescope)
+    camera      STR         64      # Key
+    telescope   STR         64      # Key
     file_id     STR         64      # Key
     bytes       S32         0
@@ -41 +43 @@
 # downloaded) 
 pzDownloadExp METADATA
-    exp_name    STR         64      # Primary Key fkey(exp_name, camera, telescope) ref summitExp(exp_name, camera, telescope)
-    camera      STR         64      # Primary Key
-    telescope   STR         64      # Primary Key
+    summit_id   S64         0       # Primary Key fkey(summit_id) ref summitExp(summit_id)
+    exp_name    STR         64      # Key fkey(exp_name, camera, telescope) ref summitExp(exp_name, camera, telescope)
+    camera      STR         64      # Key
+    telescope   STR         64      # Key
     state       STR         64      # Key 
     epoch       UTC         0001-01-01T00:00:00Z
@@ -49 +52 @@
 
 pzDownloadImfile METADATA
-    exp_name    STR         64      # Primary Key fkey(exp_name, camera, telescope) ref pzDownloadExp(exp_name, camera, telescope)
-    camera      STR         64      # Primary Key fkey(exp_name, camera, telescope, class, class_id) ref summitImfile(exp_name, camera, telescope, class, class_id)
-    telescope   STR         64      # Primary Key
-    class       STR         64      # Primary Key
+    summit_id   S64         0       # Primary Key fkey(summit_id) ref pzDownloadExp(summit_id)
+    exp_name    STR         64      # Key fkey(exp_name, camera, telescope) ref pzDownloadExp(exp_name, camera, telescope)
+    camera      STR         64      # Key fkey(exp_name, camera, telescope, class, class_id) ref summitImfile(exp_name, camera, telescope, class, class_id)
+    telescope   STR         64      # Key fkey(exp_name, camera, telescope) ref summitExp(exp_name, camera, telescope)
+    class       STR         64      # Primary Key fkey(summit_id,class,class_id) ref summitImfile(summit_id,class,class_id)
     class_id    STR         64      # Primary Key
     uri         STR         255