# ThumbsPlus database creation script for MySQL 4.1.8 or later
#
# Based on prior work by J.D. Hodges and Scott R. Senay
# www.jdhodges.com
# www.worldlog.com
#

# Notes:
#   MySQL should be started in ANSI mode (--ansi), both for running this script and
#   for using the database.

#
# ThumbsPlus requires ANSI-mode quoted identifiers
#
# 
# (C) 2006, Cerious Software Inc. All Rights Reserved.
#

SET GLOBAL sql_mode="ansi";

# Comment or change the following lines if you create a database manually or need
# to connect to a different database name.

create database "thumbs";
use "thumbs";

#
# Table structure for table "DatabaseInfo"
#

CREATE TABLE "DatabaseInfo" (
  "thumbnail_width" int(11) NOT NULL,
  "thumbnail_height" int(11) NOT NULL,
  "thumbnail_type" int(11) NOT NULL,
  "compress" int(11) NOT NULL,
  "alias" char(80) default NULL,
  "like_style" int(11) NOT NULL default '1'
) TYPE=InnoDB;

#
# Table structure for table "CurrentUsers"
#

CREATE TABLE "CurrentUsers" (
  "uid" char(16) NOT NULL,
  "starttime" int(11) default NULL,
  "lasttime" int(11) default NULL,
  PRIMARY KEY ("uid")
) TYPE=InnoDB;

#
# Table structure for table "FindCriteria"
#

CREATE TABLE "FindCriteria" (
  "idList" int(11) 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" int(11) NOT NULL default '0',
  "ListName" varchar(255) NOT NULL default '',
  "TableName" varchar(255) NOT NULL default '',
  "SQLText" text,
  PRIMARY KEY  ("idList"),
  UNIQUE KEY ("ListName")
);

#
# Table structure for table "FoundThumbs"
#

CREATE TABLE "FoundThumbs" (
  "idList" int(11) NOT NULL,
  "idThumb" int(11) NOT NULL,
  "flags" int(11) default NULL,
  PRIMARY KEY  ("idList","idThumb")
);

#
# Table structure for table "Gallery"
#

CREATE TABLE "Gallery" (
  "idGallery" int(11) NOT NULL auto_increment,
  "name" char(255) default NULL,
  "sortorder" int(11) default NULL,
  "descend" smallint(6) default NULL,
  PRIMARY KEY ("idGallery")
) TYPE=InnoDB;

#
# Table structure for table "UserFields"
#

CREATE TABLE "UserFields" (
  "idThumbUDF" int(11) NOT NULL,
  "uf_DateTimeOrig" varchar(255) default NULL,
  PRIMARY KEY  ("idThumbUDF")
) TYPE=InnoDB;

#
# Table structure for table "UserFieldsInfo"
#

CREATE TABLE "UserFieldsInfo" (
  "Version" int(11) NOT NULL,
  "FieldName" char(64) NOT NULL,
  "ColName" char(64) NOT NULL,
  "ColType" smallint(6) NOT NULL,
  "ColLength" smallint(6) default NULL,
  "FieldSeq" smallint(6) default NULL,
  PRIMARY KEY  ("Version","FieldName")
) TYPE=InnoDB;

#
# Table structure for table "Keyword"
#

CREATE TABLE "Keyword" (
  "idKeyword" int(11) NOT NULL auto_increment,
  "keyword" char(32) NOT NULL,
  PRIMARY KEY  ("idKeyword"),
  UNIQUE KEY ("Keyword")
) TYPE=InnoDB;

#
# Table structure for table "Volume"
#

CREATE TABLE "Volume" (
  "idVol" int(11) NOT NULL auto_increment,
  "vtype" int(11) NOT NULL,
  "serialno" int(11) default NULL,
  "maxcomplen" int(11) default NULL,
  "vchar" int(11) default NULL,
  "filesystem" char(32) default NULL,
  "label" char(64) default NULL,
  "netname" char(255) default NULL,
  "alias" char(80) default NULL,
  PRIMARY KEY  (idVol)
) TYPE=InnoDB;

#
# Table structure for table "Path"
#

CREATE TABLE "Path" (
  "idPath" int(11) NOT NULL auto_increment,
  "idVol" int(11) NOT NULL,
  "pathtype" int(11) NOT NULL,
  "name" char(255) NOT NULL,
  PRIMARY KEY  ("idPath"),
  INDEX ("idVol"),
  UNIQUE KEY ("idVol","name")
# FOREIGN KEY ("idVol") REFERENCES "Volume"("idVol") ON DELETE CASCADE
) TYPE=InnoDB;

#
# Table structure for table "Thumbnail"
#

CREATE TABLE "Thumbnail" (
  "idThumb" int(11) NOT NULL auto_increment,
  "idPath" int(11) NOT NULL ,
  "idFiletype" int(11) default NULL,
  "file_time" int(11) default NULL,
  "thumbnail_time" int(11) default NULL,
  "filesize" int(11) default NULL,
  "checksum" int(11) default NULL,
  "width" int(11) default NULL,
  "height" int(11) default NULL,
  "horiz_res" int(11) default NULL,
  "vert_res" int(11) default NULL,
  "colortype" smallint(6) default NULL,
  "colordepth" smallint(6) default NULL,
  "gamma" smallint(6) default NULL,
  "thumbnail_width" smallint(6) NOT NULL,
  "thumbnail_height" smallint(6) NOT NULL,
  "thumbnail_type" int(11) default NULL,
  "thumbnail_size" int(11) default NULL,
  "name" varchar(255) default NULL,
  "thumbnail" longblob,
  "annotation" text,
  "metric1" tinyblob,
  "metric2" tinyblob,
  "metric3" tinyblob,
  PRIMARY KEY  ("idThumb"),
  INDEX ("idPath"),
  INDEX ("name")
# FOREIGN KEY ("idPath") REFERENCES "Path"("idPath") ON DELETE CASCADE,
) TYPE=InnoDB;

#
# Table structure for table "GalleryThumb"
#

CREATE TABLE "GalleryThumb" (
  "idGallery" int(11) NOT NULL,
  "idThumb" int(11) NOT NULL,
  "seq" int(11) default NULL,
  PRIMARY KEY  ("idGallery","idThumb"),
  INDEX ("idGallery","seq"),
  INDEX ("idThumb")
# FOREIGN KEY ("idGallery") REFERENCES "Gallery"("idGallery") ON DELETE CASCADE,
# FOREIGN KEY ("idThumb") REFERENCES "Thumbnail"("idThumb") ON DELETE CASCADE,
) TYPE=InnoDB;

#
# Table structure for table "ThumbnailKeyword"
#

CREATE TABLE "ThumbnailKeyword" (
  "idThumb" int(11) NOT NULL,
  "idKeyword" int(11) NOT NULL,
  PRIMARY KEY  ("idThumb","idKeyword"),
  INDEX ("idKeyword")
# FOREIGN KEY ("idThumb") REFERENCES "Thumbnail"("idThumb") ON DELETE CASCADE
# FOREIGN KEY ("idKeyword") REFERENCES "Keyword"("idKeyword") ON DELETE CASCADE
  ) TYPE=InnoDB;


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