flask_photo_scaling_app/examples/database/create_database_tables.sql

\c photo_app_database_name

CREATE TABLE IF NOT EXISTS email_white_list(
  id SERIAL PRIMARY KEY,
  email VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS contributor(
  id SERIAL PRIMARY KEY,
  name VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(128),
  num_photos INT,
  totp_key CHAR(16),
  use_totp BOOLEAN DEFAULT '0'
);

/*
ALTER TABLE contributor ADD COLUMN IF NOT EXISTS num_photos INT;
ALTER TABLE contributor ADD COLUMN IF NOT EXISTS totp_key CHAR(16);
ALTER TABLE contributor ADD COLUMN IF NOT EXISTS use_totp BOOLEAN DEFAULT '0';
*/

CREATE TABLE IF NOT EXISTS photo(
  id SERIAL PRIMARY KEY,
  photo_name VARCHAR(120) NOT NULL UNIQUE,
  contributor_id INT NOT NULL,
  timestamp TIMESTAMP WITHOUT TIME ZONE,
  timestamp_int BIGINT,
  photo_format VARCHAR(12),
  photo_width INT,
  photo_height INT,
  photo_1280_width INT,
  photo_1280_height INT,
  photo_480_width INT,
  photo_480_height INT,
  "Make" VARCHAR,
  "Model" VARCHAR,
  "Software" VARCHAR,
  "DateTime" TIMESTAMP WITHOUT TIME ZONE,
  "DateTimeOriginal" TIMESTAMP WITHOUT TIME ZONE,
  "DateTimeDigitized" TIMESTAMP WITHOUT TIME ZONE,
  fnumber DECIMAL,
  "DigitalZoomRatio" DECIMAL,
  "AspectRatio" DECIMAL,
  photo_raw_size BIGINT,
  photo_1280_size BIGINT,
  photo_480_size BIGINT,
  "TimeZoneOffset" INT,
  "GPSAltitude" DECIMAL,
  "GPSAboveSeaLevel" BOOLEAN,
  "GPSLatitude" DECIMAL,
  "GPSLongitude" DECIMAL
);

/*
ALTER TABLE photo ADD COLUMN IF NOT EXISTS contributor_id INT NOT NULL;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS timestamp TIMESTAMP WITHOUT TIME ZONE;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS timestamp_int BIGINT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_format VARCHAR(12);
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_width INT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_height INT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_1280_width INT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_1280_height INT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_480_width INT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_480_height INT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "Make" VARCHAR;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "Model" VARCHAR;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "Software" VARCHAR;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "DateTime" TIMESTAMP WITHOUT TIME ZONE;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "DateTimeOriginal" TIMESTAMP WITHOUT TIME ZONE;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "DateTimeDigitized" TIMESTAMP WITHOUT TIME ZONE;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS fnumber DECIMAL;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "DigitalZoomRatio" DECIMAL;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "AspectRatio" DECIMAL;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_raw_size BIGINT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_1280_size BIGINT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS photo_480_size BIGINT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "TimeZoneOffset" INT;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "GPSAltitude" DECIMAL;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "GPSAboveSeaLevel" BOOLEAN;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "GPSLatitude" DECIMAL;
ALTER TABLE photo ADD COLUMN IF NOT EXISTS "GPSLongitude" DECIMAL
*/