Photo Organizer

photo_version

CREATE TABLE photo_version (
  identifier            INTEGER NOT NULL PRIMARY KEY,
  KEY                   INTEGER NOT NULL,
  photo                 INTEGER NOT NULL REFERENCES photo(identifier),
  master                CHAR(1) CHECK (master IN ('t', 'f')),
  small_image_path      VARCHAR(500),
  medium_image_path     VARCHAR(500),
  large_image_path      VARCHAR(500),
  date_of_creation      TIMESTAMP,
  comment               text,
  original_image_name   VARCHAR(500),
  colorspace            INTEGER NOT NULL,
  orientation           INTEGER NOT NULL REFERENCES orientations(identifier)
);

• photo references photo(identifier)
• orientation references orientations(identifier)

• imagemagick_composite_options(photo_version) references identifier

• photo_version_id_sequence

• photo_version_photo_idx

• count_photo_versions
• count_photo_versions_by_user

• view_photo
• view_random_photo

Version 2.7

Still in use

= Version 2.7 =

CREATE TABLE photo_version (
  identifier            INTEGER NOT NULL PRIMARY KEY,
  KEY                   INTEGER NOT NULL,
  photo                 INTEGER NOT NULL REFERENCES photo(identifier),
  master                CHAR(1) CHECK (master IN ('t', 'f')),
  small_image_path      VARCHAR(500),
  medium_image_path     VARCHAR(500),
  large_image_path      VARCHAR(500),
  date_of_creation      TIMESTAMP,
  comment               text
);

= Version 2.13 =

ALTER TABLE photo_version ADD COLUMN original_image_name VARCHAR(500);

= Version 2.26 =

ALTER TABLE photo_version ADD COLUMN colorspace INTEGER;
UPDATE photo_version SET colorspace = 1 WHERE colorspace IS NULL;
ALTER TABLE photo_version ALTER colorspace SET NOT NULL;
 
ALTER TABLE photo_version ADD COLUMN orientation INTEGER REFERENCES orientations(identifier);
UPDATE photo_version SET orientation = 1 WHERE orientation IS NULL;
ALTER TABLE photo_version ALTER orientation SET NOT NULL;

= Version 2.7 =

-- populate version control table
INSERT INTO photo_version
  SELECT (NEXTVAL('photo_version_id_sequence')), 1, identifier, 't', small_image_path, medium_image_path, large_image_path, now(), '' FROM photo;

= Version 2.13 =

UPDATE photo_version SET original_image_name = 'file';