-- ThumbsPlus database creation script for DB2
--
-- The CREATE DATABASE command should be modified to reflect the location
-- and code set of the database.
--
-- (C) 2006, Cerious Software Inc. All Rights Reserved.
--

CREATE DATABASE "Thumbs"
  ON 'D:'
  USING CODESET UTF-8 TERRITORY US
  COLLATE USING UCA400_NO
  PAGESIZE 16384;

COMMIT;

CONNECT TO "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),
  "like_style" INTEGER NOT NULL
);

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

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

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

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

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

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

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

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

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

CREATE TABLE "Gallery" (
  "idGallery" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY NOT NULL,
  "name" VARCHAR(255),
  "sortorder" INTEGER,
  "descend" SMALLINT
);

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

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

--
-- 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,
  "FieldSeq" SMALLINT,
  PRIMARY KEY  ("Version","FieldName")
);

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

CREATE TABLE "Keyword" (
  "idKeyword" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY NOT NULL,
  "keyword" VARCHAR(32) UNIQUE NOT NULL
);

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

CREATE TABLE "Volume" (
  "idVol" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY NOT NULL,
  "vtype" INTEGER NOT NULL,
  "serialno" INTEGER,
  "maxcomplen" INTEGER,
  "vchar" INTEGER,
  "filesystem" VARCHAR(32),
  "label" VARCHAR(64),
  "netname" VARCHAR(255),
  "alias" VARCHAR(80)
);

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

CREATE TABLE "Path" (
  "idPath" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY NOT NULL,
  "idVol" INTEGER NOT NULL,
  "pathtype" INTEGER NOT NULL,
  "name" VARCHAR(255) NOT NULL,
  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" INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY NOT NULL,
  "idPath" INTEGER NOT NULL ,
  "idFiletype" INTEGER,
  "file_time" INTEGER,
  "thumbnail_time" INTEGER,
  "filesize" INTEGER,
  "checksum" INTEGER,
  "width" INTEGER,
  "height" INTEGER,
  "horiz_res" INTEGER,
  "vert_res" INTEGER,
  "colortype" SMALLINT,
  "colordepth" SMALLINT,
  "gamma" SMALLINT,
  "thumbnail_width" SMALLINT NOT NULL,
  "thumbnail_height" SMALLINT NOT NULL,
  "thumbnail_type" INTEGER,
  "thumbnail_size" INTEGER,
  "name" VARCHAR(255),
  "thumbnail" BLOB,
  "annotation" CLOB,
  "metric1" CHAR(32) FOR BIT DATA,
  "metric2" CHAR(64) FOR BIT DATA,
  "metric3" CHAR(16) FOR BIT DATA,
  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,
  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 DB2 Database',1);

COMMIT;