User Tools

Site Tools


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,
  comments               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

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;
-- Add a comments column to the photos.
ALTER TABLE photo ADD COLUMN comments 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.1192827404.txt.gz · Last modified: 2007/10/19 20:56 by Luud