Photo Organizer

photo

CREATE TABLE photo (
  identifier             INTEGER NOT NULL PRIMARY KEY,
  users                  INTEGER NOT NULL REFERENCES users(identifier),       -- the users who uploaded the picture, not necessarily the owner
  folder                 INTEGER NOT NULL REFERENCES folder(identifier),
  location               INTEGER NOT NULL REFERENCES location(identifier),
  caption                text,             -- a novel describing how the picture was made, or what can we see on it
  date_of_exposure       TIMESTAMP,        -- date when the picture was taken
  access_rights          INTEGER NOT NULL REFERENCES access_type(identifier),
  views                  INTEGER,
  copyright_statement    text,
  hide_original          CHAR(1) CHECK (hide_original IN ('t', 'f')),
  author                 text,
  title                  text,
  caption_writer         text,
  category               VARCHAR(3),
  credit                 text,
  SOURCE                 text,
  headline               text,
  instructions           text,
  transmission_reference text,
  supplemental_category  text,
  web_statement          text,
  date_added             TIMESTAMP WITHOUT TIME zone,
  date_changed           TIMESTAMP WITHOUT TIME zone,
  store_url              text,
  comments               text
);

• access_rights references access_type(identifier)
• folder references folder(identifier)
• location references location(identifier)
• users references users(identifier)

• album_content(photo) references identifier
• photo_dupe(photo) references identifier
• photo_item(photo) references identifier removed in 2.34
• photo_keywords(photo) references identifier
• photo_spooler(photo) references identifier removed in 2.34
• photo_tech(photo) references identifier
• photo_version(photo) references identifier
• rating(photo) references identifier

• photo_version

• photo_id_sequence

• photo_folder
• photo_id_idx removed in 2.34
• photo_users_idx
• photo_location_idx

• count_photo_versions_by_user
• count_photos
• count_photos_by_album
• count_photos_by_folder
• count_photos_by_user
• is_photo_for_sale
• number_of_location_references

• view_photo
• view_random_photo

Version 2.5

Still in use

= Version 2.5 =

CREATE TABLE photo (
  identifier            INTEGER NOT NULL PRIMARY KEY,
  users                 INTEGER NOT NULL REFERENCES users(identifier),       -- the users who uploaded the picture, not necessarily the owner
  folder                INTEGER NOT NULL REFERENCES folder(identifier),
  location              INTEGER REFERENCES location(identifier),
  caption               text,             -- a novel describing how the picture was made, or what can we see on it
  date_of_exposure      TIMESTAMP,        -- date when the picture was taken
  small_image_path      VARCHAR(500),     -- path to the thumbnail
  medium_image_path     VARCHAR(500),     -- path to the medium image
  large_image_path      VARCHAR(500),     -- path to the highres image
  access_rights         INTEGER NOT NULL REFERENCES access_type(identifier)
);

= Version 2.6 =

ALTER TABLE photo ADD COLUMN views INTEGER;
ALTER TABLE photo ADD COLUMN copyright_statement text;

= Version 2.7 =

-- we rename colums first since droping was implemented only with 7.3
ALTER TABLE photo RENAME COLUMN small_image_path  TO drop_me_small_image_path;
ALTER TABLE photo RENAME COLUMN medium_image_path TO drop_me_medium_image_path;
ALTER TABLE photo RENAME COLUMN large_image_path  TO drop_me_large_image_path;
 
-- drop columns which aren't used any longer
ALTER TABLE photo DROP COLUMN drop_me_small_image_path;
ALTER TABLE photo DROP COLUMN drop_me_medium_image_path;
ALTER TABLE photo DROP COLUMN drop_me_large_image_path;

= Version 2.11 =

-- add support for original protection
ALTER TABLE photo ADD COLUMN hide_original  CHAR(1) CHECK (hide_original IN ('t', 'f'));

= Version 2.15 =

ALTER TABLE photo ADD COLUMN author text;
ALTER TABLE photo ADD COLUMN keyword text;
ALTER TABLE photo ADD COLUMN title text;

= Version 2.17 =

ALTER TABLE photo ADD COLUMN caption_writer text;
ALTER TABLE photo ADD COLUMN category VARCHAR(3);
ALTER TABLE photo ADD COLUMN credit text;
ALTER TABLE photo ADD COLUMN SOURCE text;
ALTER TABLE photo ADD COLUMN headline text;
ALTER TABLE photo ADD COLUMN instructions text;
ALTER TABLE photo ADD COLUMN transmission_reference text;
ALTER TABLE photo ADD COLUMN supplemental_category text;
ALTER TABLE photo ADD COLUMN web_statement text;

= Version 2.31 =

UPDATE photo SET location = 0 WHERE location IS NULL;
ALTER TABLE photo ALTER location SET NOT NULL;

= Version 2.33 =

-- This is called by the upgrade script --
-- alter table photo drop keyword;

= Version 2.34 =

-- Add a field to track when a photo was added
ALTER TABLE photo ADD date_added TIMESTAMP WITHOUT TIME zone;

ALTER TABLE photo ADD date_changed TIMESTAMP WITHOUT TIME zone;

--- Add a 'purcahse url' to photos.
ALTER TABLE photo ADD store_url text;

-- Add a comments column to the photos.
ALTER TABLE photo ADD COLUMN comments text;

= Version 2.6 =

-- set the photo view flag to null for each photo
UPDATE photo SET views=0;

= Version 2.11 =

UPDATE photo SET hide_original='f';

= Version 2.15 =

UPDATE photo SET caption ='' WHERE caption='No Caption';