/**********************************************************************/
/******    NEW SLIDE DATABASE SCHEMA          Date: 3/25/02      ******/
/**********************************************************************/



/**********************************************************************/
/******    DROP SLIDE TABLES                                     ******/
/**********************************************************************/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OBJECT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[OBJECT]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CHILDREN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[CHILDREN]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[LINKS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[LINKS]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[LOCKS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[LOCKS]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VERSION_CONTENT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_CONTENT]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PROPERTIES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[PROPERTIES]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PERMISSIONS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[PERMISSIONS]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VERSION_PREDS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_PREDS]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VERSION_LABELS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_LABELS]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VERSION_HISTORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION_HISTORY]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VERSION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[VERSION]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BRANCH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BRANCH]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[LABEL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[LABEL]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[URI]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[URI]
GO


/**********************************************************************/
/******    DROP EXISTING USER DEFINED DATA TYPES                 ******/
/**********************************************************************/

sp_droptype id_type
GO

sp_droptype uri_str_type
GO


/**********************************************************************/
/******    ADD USER DEFINED DATA TYPES                           ******/
/**********************************************************************/

sp_addtype id_type,       bigint
GO


sp_addtype uri_str_type, "varchar(800)"
GO

/***********************************************/
/**                  Sybase                   **/
/***********************************************/
/** sp_addtype id_type,      "numeric(18,0)"  **/
/** GO                                        **/
/** sp_addtype uri_str_type, "varchar(255)"   **/
/** GO                                        **/
/***********************************************/


/**********************************************************************/
/******    CREATE SLIDE TABLES                                   ******/
/**********************************************************************/

CREATE TABLE URI (
    URI_ID          id_type               IDENTITY  UNIQUE  NOT NULL,
    URI_STRING      uri_str_type          NOT NULL,
    UNIQUE NONCLUSTERED (URI_ID)
)
GO

CREATE TABLE OBJECT (
    URI_ID          id_type               PRIMARY KEY,
    CLASS_NAME      varchar(255)          NOT NULL,
    CONSTRAINT      FK_OBJECT_URI_ID
       FOREIGN KEY (URI_ID)
       REFERENCES   URI (URI_ID)
)
GO    

CREATE TABLE CHILDREN (
    URI_ID          id_type               NOT NULL  FOREIGN KEY 
    	REFERENCES  URI (URI_ID),
    CHILD_URI_ID    id_type               NOT NULL  FOREIGN KEY 
    	REFERENCES  URI (URI_ID),
    UNIQUE CLUSTERED (URI_ID, CHILD_URI_ID)
)
GO

CREATE TABLE LINKS (
    URI_ID          id_type               NOT NULL  FOREIGN KEY
        REFERENCES  URI (URI_ID),
    LINK_TO_ID      id_type               NOT NULL  FOREIGN KEY
        REFERENCES  URI (URI_ID),
    UNIQUE CLUSTERED (URI_ID, LINK_TO_ID)
)
GO

CREATE TABLE LOCKS (
    LOCK_ID         id_type               PRIMARY KEY,
    OBJECT_ID       id_type               NOT NULL  FOREIGN KEY
       REFERENCES   URI (URI_ID),
    SUBJECT_ID      id_type               NOT NULL  FOREIGN KEY
       REFERENCES   URI (URI_ID),
    TYPE_ID         id_type               NOT NULL  FOREIGN KEY
       REFERENCES   URI (URI_ID),
    EXPIRATION_DATE numeric(14, 0)   	  NOT NULL,
    IS_INHERITABLE  bit                   NOT NULL, 
    IS_EXCLUSIVE    bit                   NOT NULL,
    CONSTRAINT      FK_LOCKS_LOCK_ID
       FOREIGN KEY (LOCK_ID)
       REFERENCES   URI (URI_ID)
)
GO

CREATE TABLE BRANCH ( 
    BRANCH_ID       id_type               IDENTITY  UNIQUE NOT NULL,
    BRANCH_STRING   varchar(255)          NOT NULL,
    UNIQUE NONCLUSTERED (BRANCH_ID)
)
GO

CREATE TABLE LABEL (
    LABEL_ID        id_type               IDENTITY  UNIQUE NOT NULL,
    LABEL_STRING    varchar(255)          NOT NULL,
    UNIQUE NONCLUSTERED (LABEL_ID)
)
GO

CREATE TABLE VERSION (
    URI_ID          id_type               PRIMARY KEY,
    IS_VERSIONED    bit                   NOT NULL,    
    CONSTRAINT      FK_VERSION_URI_ID
       FOREIGN KEY (URI_ID)
       REFERENCES   URI (URI_ID)
)
GO

CREATE TABLE VERSION_HISTORY (
    VERSION_ID      id_type               IDENTITY  UNIQUE NOT NULL,
    URI_ID          id_type               NOT NULL  FOREIGN KEY
       REFERENCES   VERSION (URI_ID),
    BRANCH_ID       id_type               NOT NULL  FOREIGN KEY
       REFERENCES   BRANCH (BRANCH_ID),
    REVISION_NO     VARCHAR(20)	       	  NOT NULL,
    UNIQUE NONCLUSTERED (URI_ID, BRANCH_ID, REVISION_NO)
)
GO

CREATE TABLE VERSION_PREDS (
    VERSION_ID         id_type            NOT NULL  FOREIGN KEY 
        REFERENCES  VERSION_HISTORY (VERSION_ID),
    PREDECESSOR_ID     id_type            NOT NULL  FOREIGN KEY
        REFERENCES  VERSION_HISTORY (VERSION_ID),
    UNIQUE CLUSTERED (VERSION_ID, PREDECESSOR_ID)
)
GO

CREATE TABLE VERSION_LABELS (
    VERSION_ID         id_type            NOT NULL  FOREIGN KEY
        REFERENCES  VERSION_HISTORY (VERSION_ID),
    LABEL_ID           id_type            NOT NULL  FOREIGN KEY
        REFERENCES  LABEL (LABEL_ID), 
    UNIQUE CLUSTERED (VERSION_ID, LABEL_ID)
)
GO

CREATE TABLE VERSION_CONTENT (
    VERSION_ID         id_type            PRIMARY KEY,
    CONTENT            image              NOT NULL,
    CONSTRAINT FK_VC_VERSION_ID 
        FOREIGN KEY (VERSION_ID)
        REFERENCES  VERSION_HISTORY (VERSION_ID),
)
GO

CREATE TABLE PROPERTIES (
    VERSION_ID         id_type            NOT NULL  FOREIGN KEY
        REFERENCES  VERSION_HISTORY (VERSION_ID),    
    PROPERTY_NAMESPACE varchar(50)        NOT NULL, 
    PROPERTY_NAME      varchar(50)        NOT NULL,        
    PROPERTY_VALUE     varchar(255)       NOT NULL,
    PROPERTY_TYPE      varchar(50)        NOT NULL, 
    IS_PROTECTED       bit                NOT NULL,
    UNIQUE CLUSTERED (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME)
)
GO

CREATE TABLE PERMISSIONS (
    OBJECT_ID       id_type               NOT NULL  FOREIGN KEY
       REFERENCES   URI (URI_ID),
    SUBJECT_ID      id_type               NOT NULL  FOREIGN KEY
       REFERENCES   URI (URI_ID),
    ACTION_ID       id_type               NOT NULL  FOREIGN KEY
       REFERENCES   URI (URI_ID),
    VERSION_NO      VARCHAR(20),
    IS_INHERITABLE  bit                   NOT NULL,
    IS_NEGATIVE     bit                   NOT NULL,
    UNIQUE CLUSTERED (OBJECT_ID, SUBJECT_ID, ACTION_ID)
)
GO
