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';