User Tools

Site Tools


podoc:photo_dupe_sql_table

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
podoc:photo_dupe_sql_table [2007/04/19 08:23] Luudpodoc:photo_dupe_sql_table [2007/10/31 15:38] (current) Luud
Line 11: Line 11:
 === Schema === === Schema ===
  
-  create table photo_dupe ( +<code sql> 
-    identifier            integer not null primary key, +create table photo_dupe ( 
-    key                   integer not null, +  identifier            integer not null primary key, 
-    photo                 integer not null references photo(identifier), +  key                   integer not null, 
-    date_of_creation      timestamp,        -- date when it was inserted in the system +  photo                 integer not null references photo(identifier), 
-    storage_location      varchar(50), +  date_of_creation      timestamp,        -- date when it was inserted in the system 
-    storage_index         varchar(10), +  storage_location      varchar(50), 
-    remark                text              -- dupe remark +  storage_index         varchar(10), 
-  );+  remark                text,             -- dupe remark 
 +  version               integer not null references photo_version(identifier) 
 +); 
 +</code> 
 + 
 +=== References other tables === 
 + 
 +  * photo references [[photo sql table|photo]](identifier) 
 +  * version references [[photo_version sql table|photo_version]](identifier) 
 + 
 +=== Is referenced by other tables === 
 + 
 +  * <del>[[submission sql table|submission]](photo_dupe) references identifier</del> removed in 2.34
  
 === Related sequences === === Related sequences ===
Line 27: Line 39:
 === Related indexes === === Related indexes ===
  
-None.+  * [[photo_dupe_version_idx sql index|photo_dupe_version_idx]]
  
 === Related functions === === Related functions ===
Line 36: Line 48:
  
   * [[view_photo sql view|view_photo]]   * [[view_photo sql view|view_photo]]
-  * [[view_submission sql view|view_submission]]+  * <del>[[view_submission sql view|view_submission]]</del> removed in 2.34
  
 === History === === History ===
Line 48: Line 60:
 Still in use Still in use
  
-== Change history ==+== Schema change history ==
  
 = Version 2.5 = = Version 2.5 =
  
-  create table photo_dupe ( +<code sql> 
-    identifier            integer not null primary key, +create table photo_dupe ( 
-    key                   integer not null, +  identifier            integer not null primary key, 
-    photo                 integer not null references photo(identifier), +  key                   integer not null, 
-    date_of_creation      timestamp,        -- date when it was inserted in the system +  photo                 integer not null references photo(identifier), 
-    storage_location      varchar(50), +  date_of_creation      timestamp,        -- date when it was inserted in the system 
-    storage_index         varchar(10), +  storage_location      varchar(50), 
-    remark                text              -- dupe remark +  storage_index         varchar(10), 
-  );+  remark                text              -- dupe remark 
 +); 
 +</code> 
 + 
 += Version 2.34 = 
 + 
 +<code sql> 
 +-- Add 'version' to photo_dupe to allow individual versions. 
 + 
 + 
 +ALTER TABLE photo_dupe 
 +  ADD version integer REFERENCES photo_version(identifier); 
 + 
 + 
 +ALTER TABLE photo_dupe ALTER version SET NOT NULL; 
 +</code> 
 + 
 +== Data change history = 
 + 
 += Version 2.34 = 
 + 
 +<code sql> 
 +UPDATE photo_dupe set version = 
 +   (SELECT identifier from photo_version v 
 +    WHERE photo_dupe.photo = v.photo AND v.master = 't'); 
 +</code>
  
podoc/photo_dupe_sql_table.txt · Last modified: 2007/10/31 15:38 by Luud