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