-- ThumbsPlus Thumbnail Database Creation Script
--
-- This script should be modified to suit your needs. You should add a
-- Create Database command or create a database Sybase Central. The
-- command should allocate appropriate storage for the database, and you
-- can change the last command, which specifies the parameters for the
-- database
--
-- Of course, you can allocate space for the database using Sybase Central.
-- And you can create users with whatever priveleges that are appropriate
-- for your organization.
--
-- (C) 1998-2006, Cerious Software Inc. All Rights Reserved.
--

-- CREATE USERS

GRANT CONNECT TO ThumbsPlus;
GRANT GROUP TO ThumbsPlus;
GRANT CONNECT TO ThumbsAdmin IDENTIFIED BY "password";
GRANT CONNECT TO ThumbsUser IDENTIFIED BY "password";
GRANT CONNECT TO ThumbsBrowse IDENTIFIED BY "password";
GRANT MEMBERSHIP IN GROUP ThumbsPlus to ThumbsAdmin;
GRANT MEMBERSHIP IN GROUP ThumbsPlus to ThumbsUser;
GRANT MEMBERSHIP IN GROUP ThumbsPlus to ThumbsBrowse;

-- CREATE TABLES

CREATE TABLE ThumbsPlus.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
)

CREATE TABLE ThumbsPlus.Gallery 
(
    idGallery int         NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
    name      varchar(255) NOT NULL,
    sortorder int         NULL,
    descend   int         NULL,
)

CREATE TABLE ThumbsPlus.Keyword 
(
    idKeyword int         NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
    keyword   varchar(32) NOT NULL,
)

CREATE TABLE ThumbsPlus.Volume 
(
    idVol      int          NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
    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,
)

CREATE TABLE ThumbsPlus.Path 
(
    idPath   int          NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
    idVol    int          NOT NULL REFERENCES ThumbsPlus.Volume(idVol) ON DELETE CASCADE,
    pathtype int          NULL,
    name     varchar(255) NULL,

        UNIQUE (idPath, name)
)

CREATE TABLE ThumbsPlus.Thumbnail 
(
    idThumb          int          NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
    idPath           int          NOT NULL REFERENCES ThumbsPlus.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,
)

CREATE TABLE ThumbsPlus.GalleryThumb 
(
    idGallery int NOT NULL REFERENCES ThumbsPlus.Gallery(idGallery) ON DELETE CASCADE,
    idThumb   int NOT NULL REFERENCES ThumbsPlus.Thumbnail(idThumb) ON DELETE CASCADE,
    seq       int NULL,

        PRIMARY KEY (idGallery, idThumb)
)

CREATE TABLE ThumbsPlus.TempUserFields 
(
    idThumbUDF int      NOT NULL
)

CREATE TABLE ThumbsPlus.ThumbnailKeyword 
(
    idThumb   int NOT NULL REFERENCES ThumbsPlus.Thumbnail(idThumb) ON DELETE CASCADE,
    idKeyword int NOT NULL REFERENCES ThumbsPlus.Keyword(idKeyword) ON DELETE CASCADE,

        PRIMARY KEY (idThumb,idKeyword)
)

CREATE TABLE ThumbsPlus.UserFields 
(
    idThumbUDF int      NOT NULL
)

CREATE TABLE ThumbsPlus.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,

    PRIMARY KEY (Version,FieldName)
)

GRANT DBA TO ThumbsAdmin
GRANT ALL ON ThumbsPlus.DatabaseInfo TO ThumbsUser
GRANT ALL ON ThumbsPlus.Gallery TO ThumbsUser
GRANT ALL ON ThumbsPlus.Keyword TO ThumbsUser
GRANT ALL ON ThumbsPlus.Volume TO ThumbsUser
GRANT ALL ON ThumbsPlus.Path TO ThumbsUser
GRANT ALL ON ThumbsPlus.Thumbnail TO ThumbsUser
GRANT ALL ON ThumbsPlus.GalleryThumb TO ThumbsUser
GRANT ALL ON ThumbsPlus.ThumbnailKeyword TO ThumbsUser
GRANT ALL ON ThumbsPlus.UserFieldsInfo TO ThumbsUser
GRANT ALL ON ThumbsPlus.TempUserFields TO ThumbsUser
GRANT ALL ON ThumbsPlus.UserFields TO ThumbsUser
GRANT ALL ON ThumbsPlus.Gallery TO ThumbsUser
GRANT ALL ON ThumbsPlus.GalleryThumbStart to ThumbsUser
GRANT ALL ON ThumbsPlus.KeywordByFile to ThumbsUser
GRANT ALL ON ThumbsPlus.ThumbnailGallery to ThumbsUser
GRANT ALL ON ThumbsPlus.ThumbnailPath to ThumbsUser
GRANT ALL ON ThumbsPlus.ThumbnailUDF to ThumbsUser

GRANT SELECT ON ThumbsPlus.DatabaseInfo TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.Gallery TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.Keyword TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.Volume TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.Path TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.Thumbnail TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.GalleryThumb TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.ThumbnailKeyword TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.UserFieldsInfo TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.TempUserFields TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.UserFields TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.Gallery TO ThumbsBrowse
GRANT SELECT ON ThumbsPlus.GalleryThumbStart to ThumbsBrowse
GRANT SELECT ON ThumbsPlus.KeywordByFile to ThumbsBrowse
GRANT SELECT ON ThumbsPlus.ThumbnailGallery to ThumbsBrowse
GRANT SELECT ON ThumbsPlus.ThumbnailPath to ThumbsBrowse
GRANT SELECT ON ThumbsPlus.ThumbnailUDF to ThumbsBrowse

go

-- CREATE INDEXES

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

CREATE UNIQUE NONCLUSTERED INDEX KeywordKey
    ON ThumbsPlus.Keyword(keyword)
go

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

CREATE NONCLUSTERED INDEX checksum_key
    ON ThumbsPlus.Thumbnail(checksum)
go

CREATE NONCLUSTERED INDEX path_key
    ON ThumbsPlus.Thumbnail(idPath)
go

CREATE NONCLUSTERED INDEX name_key
    ON ThumbsPlus.Thumbnail(name)
go

CREATE NONCLUSTERED INDEX thumbkey_keyword
    ON ThumbsPlus.ThumbnailKeyword(idKeyword)
go

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