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/12 12:30] Luudpodoc:photo_dupe_sql_table [2007/10/31 15:38] (current) Luud
Line 1: Line 1:
-===== Database Table Description =====+===== Database Table Description of "photo_dupe" =====
  
 === Name === === Name ===
 +
 +photo_dupe
  
 === Description === === Description ===
Line 8: Line 10:
  
 === Schema === === Schema ===
 +
 +<code sql>
 +create table photo_dupe (
 +  identifier            integer not null primary key,
 +  key                   integer not null,
 +  photo                 integer not null references photo(identifier),
 +  date_of_creation      timestamp,        -- date when it was inserted in the system
 +  storage_location      varchar(50),
 +  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 ===
 +
 +  * [[photo_dupe_id_sequence sql sequence|photo_dupe_id_sequence]]
  
 === Related indexes === === Related indexes ===
 +
 +  * [[photo_dupe_version_idx sql index|photo_dupe_version_idx]]
  
 === Related functions === === Related functions ===
 +
 +  * [[number_of_dupes sql function|number_of_dupes]]
  
 === Related views === === Related views ===
 +
 +  * [[view_photo sql view|view_photo]]
 +  * <del>[[view_submission sql view|view_submission]]</del> removed in 2.34
  
 === History === === History ===
Line 27: Line 60:
 Still in use Still in use
  
-== Change history ==+== Schema change history ==
  
 = Version 2.5 = = Version 2.5 =
  
-  create table ... ( +<code sql> 
-    ...   +create table photo_dupe ( 
-  );+  identifier            integer not null primary key, 
 +  key                   integer not null, 
 +  photo                 integer not null references photo(identifier), 
 +  date_of_creation      timestamp,        -- date when it was inserted in the system 
 +  storage_location      varchar(50), 
 +  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