setuser
GO

EXEC sp_addtype N'id_type', N'bigint', N'null'
GO

setuser
GO

setuser
GO

EXEC sp_addtype N'uri_str_type', N'varchar (800)', N'null'
GO

setuser
GO

CREATE TABLE [dbo].[BRANCH] (
	[BRANCH_ID] [id_type] IDENTITY (1, 1) NOT NULL ,
	[BRANCH_STRING] [varchar] (255) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CHILDREN] (
	[URI_ID] [id_type] NOT NULL ,
	[CHILD_URI_ID] [id_type] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LABEL] (
	[LABEL_ID] [id_type] IDENTITY (1, 1) NOT NULL ,
	[LABEL_STRING] [varchar] (255) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LINKS] (
	[URI_ID] [id_type] NOT NULL ,
	[LINK_TO_ID] [id_type] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LOCKS] (
	[LOCK_ID] [id_type] NOT NULL ,
	[OBJECT_ID] [id_type] NOT NULL ,
	[SUBJECT_ID] [id_type] NOT NULL ,
	[TYPE_ID] [id_type] NOT NULL ,
	[EXPIRATION_DATE] [numeric](14, 0) NOT NULL ,
	[IS_INHERITABLE] [bit] NOT NULL ,
	[IS_EXCLUSIVE] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OBJECT] (
	[URI_ID] [id_type] NOT NULL ,
	[CLASS_NAME] [varchar] (255) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PERMISSIONS] (
	[OBJECT_ID] [id_type] NOT NULL ,
	[SUBJECT_ID] [id_type] NOT NULL ,
	[ACTION_ID] [id_type] NOT NULL ,
	[VERSION_NO] [varchar] (20) NULL ,
	[IS_INHERITABLE] [bit] NOT NULL ,
	[IS_NEGATIVE] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PROPERTIES] (
	[VERSION_ID] [id_type] NOT NULL ,
	[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 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[URI] (
	[URI_ID] [id_type] IDENTITY (1, 1) NOT NULL ,
	[URI_STRING] [uri_str_type] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VERSION] (
	[URI_ID] [id_type] NOT NULL ,
	[IS_VERSIONED] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VERSION_CONTENT] (
	[VERSION_ID] [id_type] NOT NULL ,
	[CONTENT] [image] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[VERSION_HISTORY] (
	[VERSION_ID] [id_type] IDENTITY (1, 1) NOT NULL ,
	[URI_ID] [id_type] NOT NULL ,
	[BRANCH_ID] [id_type] NOT NULL ,
	[REVISION_NO] [varchar] (20) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VERSION_LABELS] (
	[VERSION_ID] [id_type] NOT NULL ,
	[LABEL_ID] [id_type] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VERSION_PREDS] (
	[VERSION_ID] [id_type] NOT NULL ,
	[PREDECESSOR_ID] [id_type] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CHILDREN] WITH NOCHECK ADD 
	 UNIQUE  CLUSTERED 
	(
		[URI_ID],
		[CHILD_URI_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LINKS] WITH NOCHECK ADD 
	 UNIQUE  CLUSTERED 
	(
		[URI_ID],
		[LINK_TO_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LOCKS] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[LOCK_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[OBJECT] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[URI_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[PERMISSIONS] WITH NOCHECK ADD 
	 UNIQUE  CLUSTERED 
	(
		[OBJECT_ID],
		[SUBJECT_ID],
		[ACTION_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[PROPERTIES] WITH NOCHECK ADD 
	 UNIQUE  CLUSTERED 
	(
		[VERSION_ID],
		[PROPERTY_NAMESPACE],
		[PROPERTY_NAME]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[VERSION] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[URI_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[VERSION_CONTENT] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[VERSION_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[VERSION_LABELS] WITH NOCHECK ADD 
	 UNIQUE  CLUSTERED 
	(
		[VERSION_ID],
		[LABEL_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[VERSION_PREDS] WITH NOCHECK ADD 
	 UNIQUE  CLUSTERED 
	(
		[VERSION_ID],
		[PREDECESSOR_ID]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [URI1] ON [dbo].[URI]([URI_STRING]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BRANCH] WITH NOCHECK ADD 
	 UNIQUE  NONCLUSTERED 
	(
		[BRANCH_ID]
	)  ON [PRIMARY] ,
	 UNIQUE  NONCLUSTERED 
	(
		[BRANCH_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LABEL] WITH NOCHECK ADD 
	 UNIQUE  NONCLUSTERED 
	(
		[LABEL_ID]
	)  ON [PRIMARY] ,
	 UNIQUE  NONCLUSTERED 
	(
		[LABEL_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[URI] WITH NOCHECK ADD 
	 UNIQUE  NONCLUSTERED 
	(
		[URI_ID]
	)  ON [PRIMARY] ,
	 UNIQUE  NONCLUSTERED 
	(
		[URI_ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[VERSION_HISTORY] WITH NOCHECK ADD 
	 UNIQUE  NONCLUSTERED 
	(
		[VERSION_ID]
	)  ON [PRIMARY] ,
	 UNIQUE  NONCLUSTERED 
	(
		[URI_ID],
		[BRANCH_ID],
		[REVISION_NO]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CHILDREN] ADD 
	 FOREIGN KEY 
	(
		[CHILD_URI_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	),
	 FOREIGN KEY 
	(
		[URI_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	)
GO

ALTER TABLE [dbo].[LINKS] ADD 
	CONSTRAINT [FK__LINKS__LINK_TO_I__34C8D9D1] FOREIGN KEY 
	(
		[LINK_TO_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	) NOT FOR REPLICATION ,
	 FOREIGN KEY 
	(
		[URI_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	)
GO

alter table [dbo].[LINKS] nocheck constraint [FK__LINKS__LINK_TO_I__34C8D9D1]
GO

ALTER TABLE [dbo].[LOCKS] ADD 
	 FOREIGN KEY 
	(
		[OBJECT_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	),
	 FOREIGN KEY 
	(
		[SUBJECT_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	),
	 FOREIGN KEY 
	(
		[TYPE_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	),
	CONSTRAINT [FK_LOCKS_LOCK_ID] FOREIGN KEY 
	(
		[LOCK_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	)
GO

ALTER TABLE [dbo].[OBJECT] ADD 
	CONSTRAINT [FK_OBJECT_URI_ID] FOREIGN KEY 
	(
		[URI_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	)
GO

ALTER TABLE [dbo].[PERMISSIONS] ADD 
	 FOREIGN KEY 
	(
		[ACTION_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	),
	 FOREIGN KEY 
	(
		[OBJECT_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	),
	 FOREIGN KEY 
	(
		[SUBJECT_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	)
GO

ALTER TABLE [dbo].[PROPERTIES] ADD 
	 FOREIGN KEY 
	(
		[VERSION_ID]
	) REFERENCES [dbo].[VERSION_HISTORY] (
		[VERSION_ID]
	)
GO

ALTER TABLE [dbo].[VERSION] ADD 
	CONSTRAINT [FK_VERSION_URI_ID] FOREIGN KEY 
	(
		[URI_ID]
	) REFERENCES [dbo].[URI] (
		[URI_ID]
	)
GO

ALTER TABLE [dbo].[VERSION_CONTENT] ADD 
	CONSTRAINT [FK_VC_VERSION_ID] FOREIGN KEY 
	(
		[VERSION_ID]
	) REFERENCES [dbo].[VERSION_HISTORY] (
		[VERSION_ID]
	)
GO

ALTER TABLE [dbo].[VERSION_HISTORY] ADD 
	 FOREIGN KEY 
	(
		[BRANCH_ID]
	) REFERENCES [dbo].[BRANCH] (
		[BRANCH_ID]
	),
	 FOREIGN KEY 
	(
		[URI_ID]
	) REFERENCES [dbo].[VERSION] (
		[URI_ID]
	)
GO

ALTER TABLE [dbo].[VERSION_LABELS] ADD 
	 FOREIGN KEY 
	(
		[LABEL_ID]
	) REFERENCES [dbo].[LABEL] (
		[LABEL_ID]
	),
	 FOREIGN KEY 
	(
		[VERSION_ID]
	) REFERENCES [dbo].[VERSION_HISTORY] (
		[VERSION_ID]
	)
GO

ALTER TABLE [dbo].[VERSION_PREDS] ADD 
	 FOREIGN KEY 
	(
		[PREDECESSOR_ID]
	) REFERENCES [dbo].[VERSION_HISTORY] (
		[VERSION_ID]
	),
	 FOREIGN KEY 
	(
		[VERSION_ID]
	) REFERENCES [dbo].[VERSION_HISTORY] (
		[VERSION_ID]
	)
GO

