podoc:photo_sql_table
This is an old revision of the document!
Database Table Description of "photo"
Name
photo
Description
Purpose
Schema
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 );
References tables
- access_rights references access_type(identifier)
- folder references folder(identifier)
- location references location(identifier)
- users references users(identifier)
Is referenced by tables
- album_content(photo) references identifier
- photo_dupe(photo) references identifier
- photo_item(photo) references identifier
- photo_keywords(photo) references identifier
- photo_spooler(photo) references identifier
- photo_tech(photo) references identifier
- photo_version(photo) references identifier
- rating(photo) references identifier
Related tables
Related sequences
Related indexes
- <del>photo_id_idx</de> removed in 2.34
Related functions
Related views
History
Used first
Version 2.5
Used last
Still in use
Schema change history
= 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;
Data change history
= 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';
podoc/photo_sql_table.1192825455.txt.gz · Last modified: 2007/10/19 20:24 by Luud