-- ThumbsPlus database creation script for Postgres 8.1.4 or later
--
-- The CREATE DATABASE command should be modified as necessary.
--
-- (C) 2006, Cerious Software Inc. All Rights Reserved.
--

CREATE DATABASE Thumbs;

USE Thumbs;

--
-- Table structure for table "DatabaseInfo"
--

CREATE TABLE "DatabaseInfo" (
  "thumbnail_width" INTEGER NOT NULL,
  "thumbnail_height" INTEGER NOT NULL,
  "thumbnail_type" INTEGER NOT NULL,
  "compress" INTEGER NOT NULL,
  "alias" VARCHAR(80) DEFAULT NULL,
  "like_style" INTEGER NOT NULL DEFAULT '1'
);

--
-- Table structure for table "CurrentUsers"
--

CREATE TABLE "CurrentUsers" (
  "uid" VARCHAR(16) NOT NULL,
  "starttime" INTEGER DEFAULT NULL,
  "lasttime" INTEGER DEFAULT NULL,
  PRIMARY KEY ("uid")
);

--
-- Table structure for table "FindCriteria"
--

CREATE TABLE "FindCriteria" (
  "idList" INTEGER NOT NULL,
  "key" VARCHAR(255) NOT NULL,
  "value" VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY  ("idList","key")
);

--
-- Table structure for table "FoundLists"
--

CREATE TABLE "FoundLists" (
  "idList" INTEGER NOT NULL DEFAULT '0',
  "ListName" VARCHAR(255) UNIQUE NOT NULL DEFAULT '',
  "TableName" VARCHAR(255) NOT NULL DEFAULT '',
  "SQLText" text,
  PRIMARY KEY  ("idList")
);

--
-- Table structure for table "FoundThumbs"
--

CREATE TABLE "FoundThumbs" (
  "idList" INTEGER NOT NULL,
  "idThumb" INTEGER NOT NULL,
  "flags" INTEGER DEFAULT NULL,
  PRIMARY KEY  ("idList","idThumb")
);

--
-- Table structure for table "Gallery"
--

CREATE TABLE "Gallery" (
  "idGallery" SERIAL UNIQUE,
  "name" VARCHAR(255) DEFAULT NULL,
  "sortorder" INTEGER DEFAULT NULL,
  "descend" SMALLINT DEFAULT NULL,
  PRIMARY KEY ("idGallery")
);

--
-- Table structure for table "UserFields"
--

CREATE TABLE "UserFields" (
  "idThumbUDF" INTEGER NOT NULL,
  "uf_DateTimeOrig" VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY  ("idThumbUDF")
);

--
-- Table structure for table "UserFieldsInfo"
--

CREATE TABLE "UserFieldsInfo" (
  "Version" INTEGER NOT NULL,
  "FieldName" VARCHAR(64) NOT NULL,
  "ColName" VARCHAR(64) NOT NULL,
  "ColType" SMALLINT NOT NULL,
  "ColLength" SMALLINT DEFAULT NULL,
  "FieldSeq" SMALLINT DEFAULT NULL,
  PRIMARY KEY  ("Version","FieldName")
);

--
-- Table structure for table "Keyword"
--

CREATE TABLE "Keyword" (
  "idKeyword" SERIAL UNIQUE,
  "keyword" VARCHAR(32) UNIQUE NOT NULL,
  PRIMARY KEY  ("idKeyword")
);

--
-- Table structure for table "Volume"
--

CREATE TABLE "Volume" (
  "idVol" SERIAL UNIQUE,
  "vtype" INTEGER NOT NULL,
  "serialno" INTEGER DEFAULT NULL,
  "maxcomplen" INTEGER DEFAULT NULL,
  "vchar" INTEGER DEFAULT NULL,
  "filesystem" VARCHAR(32) DEFAULT NULL,
  "label" VARCHAR(64) DEFAULT NULL,
  "netname" VARCHAR(255) DEFAULT NULL,
  "alias" VARCHAR(80) DEFAULT NULL,
  PRIMARY KEY  ("idVol")
);

--
-- Table structure for table "Path"
--

CREATE TABLE "Path" (
  "idPath" SERIAL UNIQUE,
  "idVol" INTEGER NOT NULL,
  "pathtype" INTEGER NOT NULL,
  "name" VARCHAR(255) NOT NULL,
  PRIMARY KEY  ("idPath"),
  FOREIGN KEY ("idVol") REFERENCES "Volume"("idVol") ON DELETE CASCADE
);

CREATE INDEX "path_vol" ON "Path"("idVol");
CREATE UNIQUE INDEX "path_name" ON "Path"("idVol", "name");


--
-- Table structure for table "Thumbnail"
--

CREATE TABLE "Thumbnail" (
  "idThumb" SERIAL UNIQUE,
  "idPath" INTEGER NOT NULL ,
  "idFiletype" INTEGER DEFAULT NULL,
  "file_time" INTEGER DEFAULT NULL,
  "thumbnail_time" INTEGER DEFAULT NULL,
  "filesize" INTEGER DEFAULT NULL,
  "checksum" INTEGER DEFAULT NULL,
  "width" INTEGER DEFAULT NULL,
  "height" INTEGER DEFAULT NULL,
  "horiz_res" INTEGER DEFAULT NULL,
  "vert_res" INTEGER DEFAULT NULL,
  "colortype" SMALLINT DEFAULT NULL,
  "colordepth" SMALLINT DEFAULT NULL,
  "gamma" SMALLINT DEFAULT NULL,
  "thumbnail_width" SMALLINT NOT NULL,
  "thumbnail_height" SMALLINT NOT NULL,
  "thumbnail_type" INTEGER DEFAULT NULL,
  "thumbnail_size" INTEGER DEFAULT NULL,
  "name" VARCHAR(255) DEFAULT NULL,
  "thumbnail" BYTEA,
  "annotation" TEXT,
  "metric1" BYTEA,
  "metric2" BYTEA,
  "metric3" BYTEA,
  PRIMARY KEY  ("idThumb"),
  FOREIGN KEY ("idPath") REFERENCES "Path"("idPath") ON DELETE CASCADE
);

CREATE INDEX "thumb_path" on "Thumbnail"("idPath");
CREATE INDEX "thumb_name" on "Thumbnail"("name");
CREATE UNIQUE INDEX "thumb_uniq" on "Thumbnail"("idPath", "name");

--
-- Table structure for table "GalleryThumb"
--

CREATE TABLE "GalleryThumb" (
  "idGallery" INTEGER NOT NULL,
  "idThumb" INTEGER NOT NULL,
  "seq" INTEGER DEFAULT NULL,
  PRIMARY KEY  ("idGallery","idThumb"),
  FOREIGN KEY ("idGallery") REFERENCES "Gallery"("idGallery") ON DELETE CASCADE,
  FOREIGN KEY ("idThumb") REFERENCES "Thumbnail"("idThumb") ON DELETE CASCADE
);

CREATE INDEX "gal_seq" on "GalleryThumb"("idGallery", "seq");
CREATE INDEX "gal_thumb" on "GalleryThumb"("idThumb");

--
-- Table structure for table "ThumbnailKeyword"
--

CREATE TABLE "ThumbnailKeyword" (
  "idThumb" INTEGER NOT NULL,
  "idKeyword" INTEGER NOT NULL,
  PRIMARY KEY  ("idThumb","idKeyword"),
  FOREIGN KEY ("idThumb") REFERENCES "Thumbnail"("idThumb") ON DELETE CASCADE,
  FOREIGN KEY ("idKeyword") REFERENCES "Keyword"("idKeyword") ON DELETE CASCADE
);

CREATE INDEX "tk_keyword" on "ThumbnailKeyword"("idKeyword");


INSERT INTO "UserFieldsInfo" VALUES (0,'DateTimeOrig','uf_DateTimeOrig',12,255,0);
INSERT INTO "DatabaseInfo" VALUES (120,120,6,80,'ThumbsPlus Postgres Database',1);