-- ThumbsPlus Thumbnail Database Creation Script    12/01/2000
--
-- This script should be modified to suit your needs. Create the database
-- using Enterprise Manager, and allocate appropriate storage for the database.
-- You can change the last command, which specifies the parameters for the
-- database.
--
-- Of course, you can allocate space for the database using SQL Enterprise
-- manager as well (or any SQL Server management tool). And you can create
-- users with whatever priveleges that are appropriate for your organization.
--
-- (C) 1998-2006, Cerious Software Inc. All Rights Reserved.
--

-- CREATE DATABASE Thumbs
-- go

USE Thumbs
go

--
-- CREATE GROUPS
--

GRANT CREATE TABLE TO public
go
GRANT DUMP DATABASE TO public
go
GRANT DUMP TRANSACTION TO public
go

--
-- CREATE TABLES
--

CREATE TABLE DatabaseInfo 
(
    thumbnail_width  int          NOT NULL,
    thumbnail_height int          NOT NULL,
    thumbnail_type   int          NOT NULL,
    compress         int          NOT NULL,
    alias            varchar(255) NOT NULL,
    like_style		 int
)
go

CREATE TABLE CurrentUsers
(
        uid varchar (16) NOT NULL ,
        starttime int NULL ,
        lasttime int NULL 
)
GO

CREATE TABLE Gallery 
(
    idGallery int         IDENTITY PRIMARY KEY CLUSTERED,
    name      varchar(255) NOT NULL,
    sortorder int         NULL,
    descend   int         NULL,
)
go

CREATE TABLE UserFields 
(
    idThumbUDF int      NOT NULL
)
go

CREATE TABLE UserFieldsInfo 
(
    Version int         NOT NULL,
    FieldName    varchar(64) NOT NULL,
    ColName  varchar(64) NOT NULL,
    ColType    smallint    NOT NULL,
    ColLength  smallint    NOT NULL,
    FieldSeq   smallint    NOT NULL,
    CONSTRAINT PK_UserFieldsInfo PRIMARY KEY CLUSTERED (Version,FieldName)
)
go

CREATE TABLE Keyword 
(
    idKeyword int         IDENTITY PRIMARY KEY CLUSTERED,
    keyword   varchar(32) NOT NULL,
)
go

CREATE TABLE Volume 
(
    idVol      int          IDENTITY PRIMARY KEY CLUSTERED,
    vtype      int          NULL,
    serialno   int          NULL,
    maxcomplen int          NULL,
    vchar      int          NULL,
    filesystem varchar(32)  NULL,
    label      varchar(64)  NULL,
    netname    varchar(255) NULL,
    alias      varchar(80)  NULL,
)
go

CREATE TABLE Path 
(
    idPath   int          IDENTITY PRIMARY KEY CLUSTERED,
    idVol    int          NOT NULL FOREIGN KEY REFERENCES Volume(idVol) ON DELETE CASCADE,
    pathtype int          NULL,
    name     varchar(255) NULL,
    CONSTRAINT UQ_Path_1__36 UNIQUE NONCLUSTERED (idPath,name)
)
go

CREATE TABLE TempUserFields 
(
    idThumbUDF int      NOT NULL
)
go

CREATE TABLE Thumbnail 
(
    idThumb          int          IDENTITY PRIMARY KEY CLUSTERED,
    idPath           int          NOT NULL FOREIGN KEY REFERENCES Path(idPath) ON DELETE CASCADE,
    idFiletype       int          NULL,
    file_time        int          NOT NULL,
    thumbnail_time   int          NOT NULL,
    filesize         int          NOT NULL,
    checksum         int          NOT NULL,
    width            int          NULL,
    height           int          NULL,
    horiz_res        int          NULL,
    vert_res         int          NULL,
    colortype        smallint     NULL,
    colordepth       smallint     NULL,
    gamma            smallint     NULL,
    thumbnail_width  smallint     NOT NULL,
    thumbnail_height smallint     NOT NULL,
    thumbnail_type   smallint     NOT NULL,
    thumbnail_size   int          NOT NULL,
    name             varchar(255) NOT NULL,
    thumbnail        image        NULL,
    annotation       text         NULL,
    metric1          binary(64)   NULL,
    metric2          binary(64)   NULL,
    metric3          binary(64)   NULL,
)
go

CREATE TABLE GalleryThumb 
(
    idGallery int NOT NULL FOREIGN KEY REFERENCES Gallery(idGallery) ON DELETE CASCADE,
    idThumb   int NOT NULL FOREIGN KEY REFERENCES Thumbnail(idThumb) ON DELETE CASCADE,
    seq       int NULL,
    CONSTRAINT PK_GalleryThumb PRIMARY KEY CLUSTERED (idGallery,idThumb)
)
go

CREATE TABLE ThumbnailKeyword 
(
    idThumb   int NOT NULL FOREIGN KEY REFERENCES Thumbnail(idThumb) ON DELETE CASCADE,
    idKeyword int NOT NULL FOREIGN KEY REFERENCES Keyword(idKeyword) ON DELETE CASCADE,
    CONSTRAINT PK_ThumbnailKeyword PRIMARY KEY CLUSTERED (idThumb,idKeyword)
)
go

--
-- CREATE INDEXES
--

CREATE NONCLUSTERED INDEX gallery_seq
    ON GalleryThumb(idGallery,seq)
go

CREATE UNIQUE NONCLUSTERED INDEX KeywordKey
    ON Keyword(keyword)
go

CREATE NONCLUSTERED INDEX pathname_key
    ON Path(idVol,name)
go

CREATE NONCLUSTERED INDEX path_key
    ON Thumbnail(idPath)
go

CREATE NONCLUSTERED INDEX filename_key
    ON Thumbnail(name)
go

CREATE NONCLUSTERED INDEX thumbkey_keyword
    ON ThumbnailKeyword(idKeyword)
go


GRANT REFERENCES ON DatabaseInfo TO public
GRANT REFERENCES ON CurrentUsers TO public
GRANT REFERENCES ON Keyword TO public
GRANT REFERENCES ON Gallery TO public
GRANT REFERENCES ON GalleryThumb TO public
GRANT REFERENCES ON Path TO public
GRANT REFERENCES ON Thumbnail TO public
GRANT REFERENCES ON ThumbnailKeyword TO public
GRANT REFERENCES ON UserFieldsInfo TO public
GRANT REFERENCES ON Volume TO public
GRANT SELECT ON sysobjects TO public
GRANT SELECT ON sysindexes TO public
GRANT SELECT ON syscolumns TO public
GRANT SELECT ON systypes TO public
GRANT SELECT ON syscomments TO public
GRANT SELECT ON sysprotects TO public
GRANT SELECT ON sysusers TO public
GRANT SELECT ON sysdepends TO public
GRANT SELECT ON sysindexkeys TO public
GRANT SELECT ON sysreferences TO public
GRANT SELECT ON Keyword TO public
GRANT SELECT ON Gallery TO public
GRANT SELECT ON DatabaseInfo TO public
GRANT SELECT ON CurrentUsers TO public
GRANT SELECT ON GalleryThumb TO public
GRANT SELECT ON Path TO public
GRANT SELECT ON Thumbnail TO public
GRANT SELECT ON ThumbnailKeyword TO public
GRANT SELECT ON UserFieldsInfo TO public
GRANT SELECT ON Volume TO public
GRANT INSERT ON Keyword TO public
GRANT INSERT ON Gallery TO public
GRANT INSERT ON DatabaseInfo TO public
GRANT INSERT ON CurrentUsers TO public
GRANT INSERT ON GalleryThumb TO public
GRANT INSERT ON Path TO public
GRANT INSERT ON Thumbnail TO public
GRANT INSERT ON ThumbnailKeyword TO public
GRANT INSERT ON UserFieldsInfo TO public
GRANT INSERT ON Volume TO public
GRANT DELETE ON Keyword TO public
GRANT DELETE ON Gallery TO public
GRANT DELETE ON DatabaseInfo TO public
GRANT DELETE ON CurrentUsers TO public
GRANT DELETE ON GalleryThumb TO public
GRANT DELETE ON Path TO public
GRANT DELETE ON Thumbnail TO public
GRANT DELETE ON ThumbnailKeyword TO public
GRANT DELETE ON UserFieldsInfo TO public
GRANT DELETE ON Volume TO public
GRANT UPDATE ON Keyword TO public
GRANT UPDATE ON Gallery TO public
GRANT UPDATE ON DatabaseInfo TO public
GRANT UPDATE ON CurrentUsers TO public
GRANT UPDATE ON GalleryThumb TO public
GRANT UPDATE ON Path TO public
GRANT UPDATE ON Thumbnail TO public
GRANT UPDATE ON ThumbnailKeyword TO public
GRANT UPDATE ON UserFieldsInfo TO public
GRANT UPDATE ON Volume TO public
go

INSERT INTO DatabaseInfo values (120, 120, 6, 80, 'ThumbsPlus SQL Server Database', 1)
go