Photo Organizer

photo_tech

CREATE TABLE photo_tech (
  identifier            INTEGER NOT NULL PRIMARY KEY,
  photo                 INTEGER NOT NULL REFERENCES photo(identifier),
  camera                INTEGER REFERENCES camera(identifier),
  lens                  INTEGER REFERENCES lens(identifier),
  film                  INTEGER REFERENCES film(identifier),
  flash                 INTEGER REFERENCES flash(identifier),
  scanner               INTEGER REFERENCES scanner(identifier),
  FILTER                INTEGER REFERENCES FILTER(identifier),
  scan_resolution       INTEGER REFERENCES scan_resolution(identifier),
  aperture              INTEGER REFERENCES aperture(identifier),        -- key from aperture, must be within the lens limits
  focal_length          INTEGER REFERENCES focal_length(identifier),    -- key from focal length, must be within the lens limits
  shutter               INTEGER REFERENCES shutter(identifier),
  camera_metering       INTEGER REFERENCES camera_metering(identifier),
  camera_program        INTEGER REFERENCES camera_program(identifier),
  flash_mode            INTEGER REFERENCES flash_mode(identifier),
  exposure_comp         INTEGER REFERENCES ev_comp(identifier),
  flash_comp            INTEGER REFERENCES ev_comp(identifier),
  ev_difference         INTEGER REFERENCES ev_comp(identifier),
  iso_override          INTEGER REFERENCES film_iso(identifier),
  support               INTEGER REFERENCES support(identifier),
  scan_bitdepth         INTEGER REFERENCES scan_bitdepth(identifier),
  scan_multiscan        INTEGER REFERENCES scan_multiscan(identifier),
  exif                  text,
  iptc                  text,
  latitude              NUMERIC,                                        -- GPS information...
  longitude             NUMERIC,
  altitude              NUMERIC,
  img_direction         NUMERIC,
  rdf                   text
);

• aperture references aperture(identifier)
• camera references camera(identifier)
• camera_metering references camera_metering(identifier)
• camera_program references camera_program(identifier)
• ev_difference references ev_comp(identifier)
• exposure_comp references ev_comp(identifier)
• film references film(identifier)
• filter references filter(identifier)
• flash references flash(identifier)
• flash_mode references flash_mode(identifier)
• flash_comp references ev_comp(identifier)
• focal_length focal_length(identifier)
• iso_override references film_iso(identifier)
• lens references lens(identifier)
• photo references photo(identifier)
• scan_bitdepth references scan_bitdepth(identifier)
• scan_multiscan references scan_multiscan(identifier)
• scan_resolution references scan_resolution(identifier)
• scanner references scanner(identifier)
• shutter references shutter(identifier)
• support references support(identifier)

None.

• photo_tech_id_sequence

• photo_tech_id_idx
• photo_tech_photo_idx

• number_of_camera_references
• number_of_film_references
• number_of_filter_references
• number_of_flash_references
• number_of_lens_references
• number_of_scanner_references
• number_of_support_references

None.

Version 2.5

Still in use

= Version 2.5 =

CREATE TABLE photo_tech (
  identifier            INTEGER NOT NULL PRIMARY KEY,
  photo                 INTEGER NOT NULL REFERENCES photo(identifier),
  camera                INTEGER REFERENCES camera(identifier),
  lens                  INTEGER REFERENCES lens(identifier),
  film                  INTEGER REFERENCES film(identifier),
  flash                 INTEGER REFERENCES flash(identifier),
  scanner               INTEGER REFERENCES scanner(identifier),
  FILTER                INTEGER REFERENCES FILTER(identifier),
  scan_resolution       INTEGER REFERENCES scan_resolution(identifier),
  aperture              INTEGER REFERENCES aperture(identifier),        -- key from aperture, must be within the lens limits
  focal_length          INTEGER REFERENCES focal_length(identifier),    -- key from focal length, must be within the lens limits
  shutter               INTEGER REFERENCES shutter(identifier),
  camera_metering       INTEGER REFERENCES camera_metering(identifier),
  camera_program        INTEGER REFERENCES camera_program(identifier),
  flash_mode            INTEGER REFERENCES flash_mode(identifier),
  exposure_comp         INTEGER REFERENCES ev_comp(identifier),
  flash_comp            INTEGER REFERENCES ev_comp(identifier),
  ev_difference         INTEGER REFERENCES ev_comp(identifier),
  iso_override          INTEGER REFERENCES film_iso(identifier),
  support               INTEGER REFERENCES support(identifier),
  scan_bitdepth         INTEGER REFERENCES scan_bitdepth(identifier),
  scan_multiscan        INTEGER REFERENCES scan_multiscan(identifier)
);

= Version 2.9 =

ALTER TABLE photo_tech ADD COLUMN exif text;
ALTER TABLE photo_tech ADD COLUMN iptc text;

= Version 2.34 =

-- GPS information...
ALTER TABLE photo_tech ADD latitude NUMERIC;
ALTER TABLE photo_tech ADD longitude NUMERIC;
ALTER TABLE photo_tech ADD altitude NUMERIC;
ALTER TABLE photo_tech ADD img_direction NUMERIC;

-- Add an 'rdf' column to store all XMP data.
ALTER TABLE photo_tech ADD rdf text;

= Version 2.6 =

-- shift all existing ev comensation references
UPDATE photo_tech SET exposure_comp=exposure_comp+4 WHERE exposure_comp >21;
UPDATE photo_tech SET flash_comp=flash_comp+4 WHERE flash_comp >21;
UPDATE photo_tech SET ev_difference=ev_difference+4 WHERE ev_difference >21;