/*
 * xmltables-sqlserver.sql
 *             : Create or replace tables for storing XML in MS SQL Server
 *
 *      Purpose: creates tables needed for storing XML in MS SQL Server database
 * 
 *      Created: 25 May 2001
 *       Author: Jivka Bojilova
 * Organization: National Center for Ecological Analysis and Synthesis
 *    Copyright: 2000 Regents of the University of California and the
 *               National Center for Ecological Analysis and Synthesis
 *  For Details: http://www.nceas.ucsb.edu/
 *    File Info: '$Id: xmltables-sqlserver.sql 759 2001-06-01 00:26:32Z bojilova $'
 *
 */

/*
 * Drop all of the objects in proper order
 */
drop table [dbo].[xml_index];
drop table [dbo].[xml_access];
drop table [dbo].[xml_revisions];
drop table [dbo].[xml_relation];
drop table [dbo].[xml_documents];
drop table [dbo].[xml_nodes];
drop table [dbo].[xml_replication];
drop table [dbo].[xml_catalog];
drop table [dbo].[accession_number];

/* 
 * ACL -- table to store ACL for XML documents by principals
 */
CREATE TABLE [dbo].[xml_access] (
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [accessfileid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [principal_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [permission] [int] NULL ,
  [perm_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [perm_order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [begin_time] [datetime] NULL ,
  [end_time] [datetime] NULL ,
  [ticket_count] [int] NULL 
) ON [PRIMARY]
GO

/* 
 * XML Catalog -- table to store all external sources for XML documents
 */
CREATE TABLE [dbo].[xml_catalog] (
  [catalog_id] [bigint] IDENTITY (1, 1) NOT NULL ,
  [entry_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [source_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [target_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [public_id] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [system_id] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

/* 
 * Documents -- table to store XML documents
 */
CREATE TABLE [dbo].[xml_documents] (
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [rootnodeid] [bigint] NULL ,
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [server_location] [bigint] NULL ,
  [rev] [int] NULL ,
  [date_created] [datetime] NULL ,
  [date_updated] [datetime] NULL ,
  [public_access] [bit] NULL ,
  [catalog_id] [bigint] NULL 
) ON [PRIMARY]
GO

/* 
 * Index of Nodes -- table to store precomputed paths through tree for 
 * quick searching in structured searches
 */
CREATE TABLE [dbo].[xml_index] (
  [nodeid] [bigint] NOT NULL ,
  [path] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [parentnodeid] [bigint] NULL 
) ON [PRIMARY]
GO

/* 
 * Nodes -- table to store XML Nodes (both elements and attributes)
 */
CREATE TABLE [dbo].[xml_nodes] (
  [nodeid] [bigint] IDENTITY (1, 1) NOT NULL ,
  [nodeindex] [int] NULL ,
  [nodetype] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [nodename] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [nodedata] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [parentnodeid] [bigint] NULL ,
  [rootnodeid] [bigint] NULL ,
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [date_created] [datetime] NULL ,
  [date_updated] [datetime] NULL 
) ON [PRIMARY]
GO

/* 
 * Relations -- table to store relations of form <subject,relationship,object>
 */
CREATE TABLE [dbo].[xml_relation] (
  [relationid] [bigint] IDENTITY (1, 1) NOT NULL ,
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [packagetype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [subject] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [subdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [relationship] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [object] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [objdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

/* 
 * Replication -- table to store servers that metacat is replicated to
 */
CREATE TABLE [dbo].[xml_replication] (
  [serverid] [bigint] IDENTITY (1, 1) NOT NULL ,
  [server] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [last_checked] [datetime] NULL ,
  [replicate] [bit] NULL 
) ON [PRIMARY]
GO

/* 
 * Revised Documents -- table to store XML documents saved after an UPDATE
 *                    or DELETE
 */
CREATE TABLE [dbo].[xml_revisions] (
  [revisionid] [bigint] IDENTITY (1, 1) NOT NULL ,
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [rootnodeid] [bigint] NULL ,
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [server_location] [bigint] NULL ,
  [rev] [int] NULL ,
  [date_created] [datetime] NULL ,
  [date_updated] [datetime] NULL ,
  [public_access] [bit] NULL ,
  [catalog_id] [bigint] NULL 
) ON [PRIMARY]
GO                                

/* 
 * Table used as Unique ID generator for the uniqueid part of Accession#
 */
CREATE TABLE [dbo].[accession_number] (
  [uniqueid] [int] IDENTITY (1, 1) NOT NULL,
  [site_code] [varchar] (100),
  [date_created] [datetime],
  CONSTRAINT [PK_accession_number] PRIMARY KEY CLUSTERED ([uniqueid])
) ON [PRIMARY]
GO

/* 
 * Constraints and indexes
 */
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
	CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED 
	(
		[catalog_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
	CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED 
	(
		[docid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD 
	CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED 
	(
		[nodeid],
		[path]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD 
	CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED 
	(
		[nodeid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
	CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED 
	(
		[relationid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD 
	CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED 
	(
		[serverid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD 
	CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED 
	(
		[revisionid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD 
	CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time])
GO

ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
	CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED 
	(
		[entry_type],
		[source_doctype],
		[target_doctype],
		[public_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
	CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev]
GO

ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
	CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED 
	(
		[subject],
		[relationship],
		[object]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_xml_documents] ON [dbo].[xml_documents]([docid], [doctype])
                                                                    ON [PRIMARY]
GO

 CREATE  INDEX [IX_xml_index] ON [dbo].[xml_index]([path]) ON [PRIMARY]
GO

 CREATE  INDEX [IX1_xml_nodes] ON [dbo].[xml_nodes]([rootnodeid]) ON [PRIMARY]
GO

 CREATE  INDEX [IX2_xml_nodes] ON [dbo].[xml_nodes]([parentnodeid]) ON [PRIMARY]
GO

 CREATE  INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[xml_access] ADD 
	CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY 
	(
		[accessfileid]
	) REFERENCES [dbo].[xml_documents] (
		[docid]
	)
GO

ALTER TABLE [dbo].[xml_documents] ADD 
	CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY 
	(
		[catalog_id]
	) REFERENCES [dbo].[xml_catalog] (
		[catalog_id]
	),
	CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY 
	(
		[rootnodeid]
	) REFERENCES [dbo].[xml_nodes] (
		[nodeid]
	),
	CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY 
	(
		[server_location]
	) REFERENCES [dbo].[xml_replication] (
		[serverid]
	)
GO

ALTER TABLE [dbo].[xml_index] ADD 
	CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY 
	(
		[docid]
	) REFERENCES [dbo].[xml_documents] (
		[docid]
	),
	CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY 
	(
		[nodeid]
	) REFERENCES [dbo].[xml_nodes] (
		[nodeid]
	)
GO

ALTER TABLE [dbo].[xml_nodes] ADD 
	CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY 
	(
		[parentnodeid]
	) REFERENCES [dbo].[xml_nodes] (
		[nodeid]
	),
	CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY 
	(
		[rootnodeid]
	) REFERENCES [dbo].[xml_nodes] (
		[nodeid]
	)
GO

ALTER TABLE [dbo].[xml_relation] ADD 
	CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY 
	(
		[docid]
	) REFERENCES [dbo].[xml_documents] (
		[docid]
	)
GO

ALTER TABLE [dbo].[xml_revisions] ADD 
	CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY 
	(
		[catalog_id]
	) REFERENCES [dbo].[xml_catalog] (
		[catalog_id]
	),
	CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY 
	(
		[rootnodeid]
	) REFERENCES [dbo].[xml_nodes] (
		[nodeid]
	),
	CONSTRAINT [FK_xml_revisions_xml_r
eplication] FOREIGN KEY 
	(
		[server_location]
	) REFERENCES [dbo].[xml_replication] (
		[serverid]
	)
GO

