-- ThumbsPlus Thumbnail Database Creation Script    9/24/98

--

-- This script should be modified to suit your needs. The Create Database

-- 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 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, 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 USERS

--





--

-- CREATE ALIASES

--

EXEC sp_addalias 'ThumbsUser','dbo'

go

IF EXISTS (SELECT * FROM sysalternates WHERE suid=SUSER_ID('ThumbsUser'))

    PRINT '<<< CREATED ALIAS ThumbsUser >>>'

ELSE

    PRINT '<<< FAILED CREATING ALIAS ThumbsUser >>>'

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

)

go

IF OBJECT_ID('DatabaseInfo') IS NOT NULL

    PRINT '<<< CREATED TABLE DatabaseInfo >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE DatabaseInfo >>>'

go



CREATE TABLE CurrentUsers (

        uid varchar (16) NOT NULL ,

        starttime int NULL ,

        lasttime int NULL 

)

GO

IF OBJECT_ID('CurrentUsers') IS NOT NULL

    PRINT '<<< CREATED TABLE CurrentUsers >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE CurrentUsers >>>'

go





CREATE TABLE Gallery 

(

    idGallery int         IDENTITY,

    name      varchar(50) NOT NULL,

    sortorder int         NULL,

    descend   int         NULL,

    CONSTRAINT PK__Gallery__754D733A PRIMARY KEY CLUSTERED (idGallery)

)

go

IF OBJECT_ID('Gallery') IS NOT NULL

    PRINT '<<< CREATED TABLE Gallery >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE Gallery >>>'

go



CREATE TABLE GalleryThumb 

(

    idGallery int NOT NULL,

    idThumb   int NOT NULL,

    seq       int NULL,

    CONSTRAINT PK__GalleryThumb__791E041E PRIMARY KEY CLUSTERED (idGallery,idThumb)

)

go

IF OBJECT_ID('GalleryThumb') IS NOT NULL

    PRINT '<<< CREATED TABLE GalleryThumb >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE GalleryThumb >>>'

go



CREATE TABLE Keyword 

(

    idKeyword int         IDENTITY,

    keyword   varchar(32) NOT NULL,

    CONSTRAINT PK__Keyword__76419773 PRIMARY KEY CLUSTERED (idKeyword)

)

go

IF OBJECT_ID('Keyword') IS NOT NULL

    PRINT '<<< CREATED TABLE Keyword >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE Keyword >>>'

go



CREATE TABLE Path 

(

    idPath   int          IDENTITY,

    idVol    int          NOT NULL,

    pathtype int          NULL,

    name     varchar(255) NULL,

    CONSTRAINT PK__Path__74594F01 PRIMARY KEY CLUSTERED (idPath),

    CONSTRAINT UQ_Path_1__36 UNIQUE NONCLUSTERED (idPath,name)

)

go

IF OBJECT_ID('Path') IS NOT NULL

    PRINT '<<< CREATED TABLE Path >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE Path >>>'

go



CREATE TABLE TempUserFields 

(

    idThumbUDF int      NOT NULL

)

go

IF OBJECT_ID('TempUserFields') IS NOT NULL

    PRINT '<<< CREATED TABLE TempUserFields >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE TempUserFields >>>'

go



CREATE TABLE Thumbnail 

(

    idThumb          int          IDENTITY,

    idPath           int          NOT NULL,

    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,

    CONSTRAINT PK__Thumbnail__73652AC8 PRIMARY KEY CLUSTERED (idThumb)

)

go

IF OBJECT_ID('Thumbnail') IS NOT NULL

    PRINT '<<< CREATED TABLE Thumbnail >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE Thumbnail >>>'

go



CREATE TABLE ThumbnailKeyword 

(

    idThumb   int NOT NULL,

    idKeyword int NOT NULL,

    CONSTRAINT PK__ThumbnailKeyword__7271068F PRIMARY KEY CLUSTERED (idThumb,idKeyword)

)

go

IF OBJECT_ID('ThumbnailKeyword') IS NOT NULL

    PRINT '<<< CREATED TABLE ThumbnailKeyword >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE ThumbnailKeyword >>>'

go



CREATE TABLE UserFields 

(

    idThumbUDF int      NOT NULL

)

go

IF OBJECT_ID('UserFields') IS NOT NULL

    PRINT '<<< CREATED TABLE UserFields >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE UserFields >>>'

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__6EA075AB PRIMARY KEY CLUSTERED (Version,FieldName)

)

go

IF OBJECT_ID('UserFieldsInfo') IS NOT NULL

    PRINT '<<< CREATED TABLE UserFieldsInfo >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE UserFieldsInfo >>>'

go



CREATE TABLE Volume 

(

    idVol      int          IDENTITY,

    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,

    CONSTRAINT PK__Volume__6DAC5172 PRIMARY KEY NONCLUSTERED (idVol)

)

go

IF OBJECT_ID('Volume') IS NOT NULL

    PRINT '<<< CREATED TABLE Volume >>>'

ELSE

    PRINT '<<< FAILED CREATING TABLE Volume >>>'

go





--

-- CREATE INDEXES

--

CREATE NONCLUSTERED INDEX gallery_seq

    ON GalleryThumb(idGallery,seq)

go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('GalleryThumb') AND name='gallery_seq')

    PRINT '<<< CREATED INDEX GalleryThumb.gallery_seq >>>'

ELSE

    PRINT '<<< FAILED CREATING INDEX GalleryThumb.gallery_seq >>>'

go



CREATE UNIQUE NONCLUSTERED INDEX KeywordKey

    ON Keyword(keyword)

go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Keyword') AND name='KeywordKey')

    PRINT '<<< CREATED INDEX Keyword.KeywordKey >>>'

ELSE

    PRINT '<<< FAILED CREATING INDEX Keyword.KeywordKey >>>'

go



CREATE NONCLUSTERED INDEX pathname_key

    ON Path(idVol,name)

go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Path') AND name='pathname_key')

    PRINT '<<< CREATED INDEX Path.pathname_key >>>'

ELSE

    PRINT '<<< FAILED CREATING INDEX Path.pathname_key >>>'

go



CREATE NONCLUSTERED INDEX checksum_key

    ON Thumbnail(checksum)

go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Thumbnail') AND name='checksum_key')

    PRINT '<<< CREATED INDEX Thumbnail.checksum_key >>>'

ELSE

    PRINT '<<< FAILED CREATING INDEX Thumbnail.checksum_key >>>'

go



CREATE NONCLUSTERED INDEX path_key

    ON Thumbnail(idPath)

go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Thumbnail') AND name='path_key')

    PRINT '<<< CREATED INDEX Thumbnail.path_key >>>'

ELSE

    PRINT '<<< FAILED CREATING INDEX Thumbnail.path_key >>>'

go



CREATE NONCLUSTERED INDEX name_key

    ON Thumbnail(name)

go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Thumbnail') AND name='name_key')

    PRINT '<<< CREATED INDEX Thumbnail.name_key >>>'

ELSE

    PRINT '<<< FAILED CREATING INDEX Thumbnail.name_key >>>'

go



CREATE NONCLUSTERED INDEX thumbkey_keyword

    ON ThumbnailKeyword(idKeyword)

go

IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('ThumbnailKeyword') AND name='thumbkey_keyword')

    PRINT '<<< CREATED INDEX ThumbnailKeyword.thumbkey_keyword >>>'

ELSE

    PRINT '<<< FAILED CREATING INDEX ThumbnailKeyword.thumbkey_keyword >>>'

go





--

-- CREATE VIEWS

--

/****** Object:  View GalleryThumbStart    Script Date: 9/11/98 12:33:21 PM ******/

CREATE VIEW GalleryThumbStart AS

SELECT DISTINCT Gallery.idGallery, IsNull(Max(seq),-1)+1 AS cnt, Gallery.name, Gallery.sortorder, Gallery.descend

FROM Gallery LEFT JOIN GalleryThumb ON Gallery.idGallery = GalleryThumb.idGallery

GROUP BY Gallery.name, Gallery.idGallery, Gallery.sortorder, Gallery.descend



go

IF OBJECT_ID('GalleryThumbStart') IS NOT NULL

    PRINT '<<< CREATED VIEW GalleryThumbStart >>>'

ELSE

    PRINT '<<< FAILED CREATING VIEW GalleryThumbStart >>>'

go



/****** Object:  View KeywordByFile    Script Date: 9/11/98 12:33:21 PM ******/

CREATE VIEW KeywordByFile AS

SELECT Keyword.keyword, ThumbnailKeyword.idThumb

FROM Keyword INNER JOIN ThumbnailKeyword ON Keyword.idKeyword = ThumbnailKeyword.idKeyword



go

IF OBJECT_ID('KeywordByFile') IS NOT NULL

    PRINT '<<< CREATED VIEW KeywordByFile >>>'

ELSE

    PRINT '<<< FAILED CREATING VIEW KeywordByFile >>>'

go



/****** Object:  View ThumbnailGallery    Script Date: 9/11/98 12:33:21 PM ******/

CREATE VIEW ThumbnailGallery AS

SELECT Thumbnail.idThumb, Thumbnail.idPath, Thumbnail.idFiletype, Thumbnail.file_time, Thumbnail.thumbnail_time, Thumbnail.filesize, Thumbnail.checksum, Thumbnail.width, Thumbnail.height, Thumbnail.horiz_res, Thumbnail.vert_res, Thumbnail.colortype, Thumbnail.colordepth, Thumbnail.gamma, Thumbnail.thumbnail_width, Thumbnail.thumbnail_height, Thumbnail.thumbnail_type, Thumbnail.thumbnail_size, Thumbnail.name, Thumbnail.metric1, Thumbnail.metric2, Thumbnail.metric3, Thumbnail.thumbnail, Thumbnail.annotation, GalleryThumb.idGallery, GalleryThumb.seq

FROM Thumbnail INNER JOIN GalleryThumb ON Thumbnail.idThumb = GalleryThumb.idThumb



go

IF OBJECT_ID('ThumbnailGallery') IS NOT NULL

    PRINT '<<< CREATED VIEW ThumbnailGallery >>>'

ELSE

    PRINT '<<< FAILED CREATING VIEW ThumbnailGallery >>>'

go



CREATE VIEW ThumbnailPath AS

SELECT Thumbnail.idThumb, Thumbnail.idPath, Thumbnail.idFiletype, Thumbnail.file_time, Thumbnail.thumbnail_time, Thumbnail.filesize, Thumbnail.checksum, Thumbnail.width, Thumbnail.height, Thumbnail.horiz_res, Thumbnail.vert_res, Thumbnail.colortype, Thumbnail.colordepth, Thumbnail.gamma, Thumbnail.thumbnail_width, Thumbnail.thumbnail_height, Thumbnail.thumbnail_type, Thumbnail.thumbnail_size, Thumbnail.name, Thumbnail.metric1, Thumbnail.metric2, Thumbnail.metric3

FROM Path INNER JOIN Thumbnail ON Path.idPath = Thumbnail.idPath

go

IF OBJECT_ID('ThumbnailPath') IS NOT NULL

    PRINT '<<< CREATED VIEW ThumbnailPath >>>'

ELSE

    PRINT '<<< FAILED CREATING VIEW ThumbnailPath >>>'

go



CREATE VIEW ThumbnailUDF AS

SELECT Thumbnail.idThumb, Thumbnail.idPath, Thumbnail.idFiletype, Thumbnail.file_time, Thumbnail.thumbnail_time, Thumbnail.filesize, Thumbnail.checksum, Thumbnail.width, Thumbnail.height, Thumbnail.horiz_res, Thumbnail.vert_res, Thumbnail.colortype, Thumbnail.colordepth, Thumbnail.gamma, Thumbnail.thumbnail_width, Thumbnail.thumbnail_height, Thumbnail.thumbnail_type, Thumbnail.thumbnail_size, Thumbnail.name, Thumbnail.thumbnail, Thumbnail.annotation, Thumbnail.metric1, Thumbnail.metric2, Thumbnail.metric3, UserFields.*

FROM Thumbnail INNER JOIN UserFields ON Thumbnail.idThumb = UserFields.idThumbUDF



go

IF OBJECT_ID('ThumbnailUDF') IS NOT NULL

    PRINT '<<< CREATED VIEW ThumbnailUDF >>>'

ELSE

    PRINT '<<< FAILED CREATING VIEW ThumbnailUDF >>>'

go





--

-- CREATE TRIGGERS

--

CREATE TRIGGER gallery_delete ON Gallery 

FOR DELETE 

AS

begin transaction

        delete gallerythumb from gallerythumb, deleted where gallerythumb.idGallery = deleted.idGallery

commit

go

IF OBJECT_ID('gallery_delete') IS NOT NULL

    PRINT '<<< CREATED TRIGGER gallery_delete >>>'

ELSE

    PRINT '<<< FAILED CREATING TRIGGER gallery_delete >>>'

go



CREATE TRIGGER keyword_delete ON Keyword 

FOR DELETE 

AS

begin transaction

        delete thumbnailkeyword from thumbnailkeyword, deleted where thumbnailkeyword.idKeyword = deleted.idKeyword

commit

go

IF OBJECT_ID('keyword_delete') IS NOT NULL

    PRINT '<<< CREATED TRIGGER keyword_delete >>>'

ELSE

    PRINT '<<< FAILED CREATING TRIGGER keyword_delete >>>'

go



CREATE TRIGGER path_delete ON Path 

FOR DELETE 

AS

begin transaction

        delete thumbnail from thumbnail, deleted where thumbnail.idPath=deleted.idPath

commit

go

IF OBJECT_ID('path_delete') IS NOT NULL

    PRINT '<<< CREATED TRIGGER path_delete >>>'

ELSE

    PRINT '<<< FAILED CREATING TRIGGER path_delete >>>'

go



CREATE TRIGGER thumb_delete ON Thumbnail 

FOR DELETE 

AS

begin transaction

  delete GalleryThumb from GalleryThumb, deleted where GalleryThumb.idThumb = deleted.idThumb

  delete ThumbnailKeyword from ThumbnailKeyword, deleted where ThumbnailKeyword.idThumb = deleted.idThumb

commit

go

IF OBJECT_ID('thumb_delete') IS NOT NULL

    PRINT '<<< CREATED TRIGGER thumb_delete >>>'

ELSE

    PRINT '<<< FAILED CREATING TRIGGER thumb_delete >>>'

go



CREATE TRIGGER volume_delete ON Volume 

FOR DELETE 

AS

begin transaction

        delete path from path, deleted where path.idVol = deleted.idVol

commit

go

IF OBJECT_ID('volume_delete') IS NOT NULL

    PRINT '<<< CREATED TRIGGER volume_delete >>>'

ELSE

    PRINT '<<< FAILED CREATING TRIGGER volume_delete >>>'

go



GRANT REFERENCES ON DatabaseInfo TO public

go

GRANT REFERENCES ON CurrentUsers TO public

go

GRANT REFERENCES ON Keyword TO public

go

GRANT REFERENCES ON Gallery TO public

go

GRANT REFERENCES ON GalleryThumb TO public

go

GRANT REFERENCES ON Path TO public

go

GRANT REFERENCES ON Thumbnail TO public

go

GRANT REFERENCES ON ThumbnailKeyword TO public

go

GRANT REFERENCES ON UserFieldsInfo TO public

go

GRANT REFERENCES ON Volume TO public

go

GRANT SELECT ON sysobjects TO public

go

GRANT SELECT ON sysindexes TO public

go

GRANT SELECT ON syscolumns TO public

go

GRANT SELECT ON systypes TO public

go

GRANT SELECT ON sysprocedures TO public

go

GRANT SELECT ON syscomments TO public

go

GRANT SELECT ON syssegments TO public

go

GRANT SELECT ON syslogs TO public

go

GRANT SELECT ON sysprotects TO public

go

GRANT SELECT ON sysusers TO public

go

GRANT SELECT ON sysalternates TO public

go

GRANT SELECT ON sysdepends TO public

go

GRANT SELECT ON syskeys TO public

go

GRANT SELECT ON sysreferences TO public

go

GRANT SELECT ON sysconstraints TO public

go

GRANT SELECT ON sysarticles TO public

go

GRANT SELECT ON syspublications TO public

go

GRANT SELECT ON syssubscriptions TO public

go

GRANT SELECT ON ThumbnailUDF TO public

go

GRANT SELECT ON Keyword TO public

go

GRANT SELECT ON Gallery TO public

go

GRANT SELECT ON DatabaseInfo TO public

go

GRANT SELECT ON CurrentUsers TO public

go

GRANT SELECT ON GalleryThumb TO public

go

GRANT SELECT ON Path TO public

go

GRANT SELECT ON Thumbnail TO public

go

GRANT SELECT ON ThumbnailKeyword TO public

go

GRANT SELECT ON UserFieldsInfo TO public

go

GRANT SELECT ON Volume TO public

go

GRANT SELECT ON GalleryThumbStart TO public

go

GRANT SELECT ON KeywordByFile TO public

go

GRANT SELECT ON ThumbnailGallery TO public

go

GRANT INSERT ON ThumbnailUDF TO public

go

GRANT INSERT ON Keyword TO public

go

GRANT INSERT ON Gallery TO public

go

GRANT INSERT ON DatabaseInfo TO public

go

GRANT INSERT ON CurrentUsers TO public

go

GRANT INSERT ON GalleryThumb TO public

go

GRANT INSERT ON Path TO public

go

GRANT INSERT ON Thumbnail TO public

go

GRANT INSERT ON ThumbnailKeyword TO public

go

GRANT INSERT ON UserFieldsInfo TO public

go

GRANT INSERT ON Volume TO public

go

GRANT INSERT ON GalleryThumbStart TO public

go

GRANT INSERT ON KeywordByFile TO public

go

GRANT INSERT ON ThumbnailGallery TO public

go

GRANT DELETE ON ThumbnailUDF TO public

go

GRANT DELETE ON Keyword TO public

go

GRANT DELETE ON Gallery TO public

go

GRANT DELETE ON DatabaseInfo TO public

go

GRANT DELETE ON CurrentUsers TO public

go

GRANT DELETE ON GalleryThumb TO public

go

GRANT DELETE ON Path TO public

go

GRANT DELETE ON Thumbnail TO public

go

GRANT DELETE ON ThumbnailKeyword TO public

go

GRANT DELETE ON UserFieldsInfo TO public

go

GRANT DELETE ON Volume TO public

go

GRANT DELETE ON GalleryThumbStart TO public

go

GRANT DELETE ON KeywordByFile TO public

go

GRANT DELETE ON ThumbnailGallery TO public

go

GRANT UPDATE ON ThumbnailUDF TO public

go

GRANT UPDATE ON Keyword TO public

go

GRANT UPDATE ON Gallery TO public

go

GRANT UPDATE ON DatabaseInfo TO public

go

GRANT UPDATE ON CurrentUsers TO public

go

GRANT UPDATE ON GalleryThumb TO public

go

GRANT UPDATE ON Path TO public

go

GRANT UPDATE ON Thumbnail TO public

go

GRANT UPDATE ON ThumbnailKeyword TO public

go

GRANT UPDATE ON UserFieldsInfo TO public

go

GRANT UPDATE ON Volume TO public

go

GRANT UPDATE ON GalleryThumbStart TO public

go

GRANT UPDATE ON KeywordByFile TO public

go

GRANT UPDATE ON ThumbnailGallery TO public

go



INSERT INTO DatabaseInfo values (100, 100, 5, 2, "ThumbsPlus SQL Server Database")

go

