-- ThumbsPlus database creation script for Firebird 2.0
--
-- 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.
--

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

CREATE DATABASE "d:\Firebird\Thumbs.fdb"
  USER "SYSDBA"
  PASSWORD "masterkey"
  PAGE_SIZE 16384
  DEFAULT CHARACTER SET UTF8;

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" BLOB SUB_TYPE TEXT,
  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 NOT NULL,
  "name" VARCHAR(255) COLLATE UNICODE,
  "sortorder" INTEGER,
  "descend" SMALLINT
);

CREATE SEQUENCE "gen_idGallery";
COMMIT;

SET TERM !! ;
CREATE TRIGGER "serial_Gallery" FOR "Gallery"
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW."idGallery" IS NULL) THEN
    NEW."idGallery" = GEN_ID("gen_idGallery", 1);
END !!
SET TERM ; !!

--
-- 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 NOT NULL,
  "keyword" VARCHAR(32) UNIQUE NOT NULL COLLATE UNICODE
);

CREATE SEQUENCE "gen_idKeyword";
COMMIT;

SET TERM !! ;
CREATE TRIGGER "serial_Keyword" FOR "Keyword"
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW."idKeyword" IS NULL) THEN
    NEW."idKeyword" = GEN_ID("gen_idKeyword", 1);
END !!
SET TERM ; !!

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

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

CREATE SEQUENCE "gen_idVol";
COMMIT;

SET TERM !! ;
CREATE TRIGGER "serial_Volume" FOR "Volume"
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW."idVol" IS NULL) THEN
    NEW."idVol" = GEN_ID("gen_idVol", 1);
END !!
SET TERM ; !!

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

CREATE TABLE "Path" (
  "idPath" INTEGER PRIMARY KEY NOT NULL,
  "idVol" INTEGER NOT NULL,
  "pathtype" INTEGER NOT NULL,
  "name" VARCHAR(255) NOT NULL COLLATE UNICODE,
  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");

CREATE SEQUENCE "gen_idPath";
COMMIT;

SET TERM !! ;
CREATE TRIGGER "serial_idPath" FOR "Path"
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW."idPath" IS NULL) THEN
    NEW."idPath" = GEN_ID("gen_idPath", 1);
END !!
SET TERM ; !!

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

CREATE TABLE "Thumbnail" (
  "idThumb" INTEGER PRIMARY KEY 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) COLLATE UNICODE,
  "thumbnail" BLOB,
  "annotation" BLOB SUB_TYPE TEXT,
  "metric1" CHAR(32),
  "metric2" CHAR(64),
  "metric3" CHAR(16),
  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");

CREATE SEQUENCE "gen_idThumb";
COMMIT;

SET TERM !! ;
CREATE TRIGGER "serial_Thumbnail" FOR "Thumbnail"
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW."idThumb" IS NULL) THEN
    NEW."idThumb" = GEN_ID("gen_idThumb", 1);
END !!
SET TERM ; !!

--
-- 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 Firebird Database',1);

COMMIT;