Revision 772
Added by bojilova over 23 years ago
| src/xmltables-sqlserver.sql | ||
|---|---|---|
| 31 | 31 |
* ACL -- table to store ACL for XML documents by principals |
| 32 | 32 |
*/ |
| 33 | 33 |
CREATE TABLE [dbo].[xml_access] ( |
| 34 |
[docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 35 |
[accessfileid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 36 |
[principal_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 37 |
[permission] [int] NULL ,
|
|
| 38 |
[perm_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 39 |
[perm_order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 40 |
[begin_time] [datetime] NULL ,
|
|
| 41 |
[end_time] [datetime] NULL ,
|
|
| 42 |
[ticket_count] [int] NULL
|
|
| 34 |
[docid] [varchar] (250) NULL ,
|
|
| 35 |
[accessfileid] [varchar] (250) NULL ,
|
|
| 36 |
[principal_name] [varchar] (100) NULL ,
|
|
| 37 |
[permission] [int] NULL ,
|
|
| 38 |
[perm_type] [varchar] (50) NULL ,
|
|
| 39 |
[perm_order] [varchar] (50) NULL ,
|
|
| 40 |
[begin_time] [datetime] NULL ,
|
|
| 41 |
[end_time] [datetime] NULL ,
|
|
| 42 |
[ticket_count] [int] NULL
|
|
| 43 | 43 |
) ON [PRIMARY] |
| 44 | 44 |
GO |
| 45 | 45 |
|
| ... | ... | |
| 47 | 47 |
* XML Catalog -- table to store all external sources for XML documents |
| 48 | 48 |
*/ |
| 49 | 49 |
CREATE TABLE [dbo].[xml_catalog] ( |
| 50 |
[catalog_id] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 51 |
[entry_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 52 |
[source_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 53 |
[target_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 54 |
[public_id] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 55 |
[system_id] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
|
|
| 50 |
[catalog_id] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 51 |
[entry_type] [varchar] (50) NULL ,
|
|
| 52 |
[source_doctype] [varchar] (250) NULL ,
|
|
| 53 |
[target_doctype] [varchar] (250) NULL ,
|
|
| 54 |
[public_id] [varchar] (250) NULL ,
|
|
| 55 |
[system_id] [varchar] (512) NULL
|
|
| 56 | 56 |
) ON [PRIMARY] |
| 57 | 57 |
GO |
| 58 | 58 |
|
| ... | ... | |
| 60 | 60 |
* Documents -- table to store XML documents |
| 61 | 61 |
*/ |
| 62 | 62 |
CREATE TABLE [dbo].[xml_documents] ( |
| 63 |
[docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
|
|
| 64 |
[rootnodeid] [bigint] NULL ,
|
|
| 65 |
[docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 66 |
[doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 67 |
[user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 68 |
[user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 69 |
[server_location] [bigint] NULL ,
|
|
| 70 |
[rev] [int] NULL ,
|
|
| 71 |
[date_created] [datetime] NULL ,
|
|
| 72 |
[date_updated] [datetime] NULL ,
|
|
| 73 |
[public_access] [bit] NULL ,
|
|
| 74 |
[catalog_id] [bigint] NULL
|
|
| 63 |
[docid] [varchar] (250) NOT NULL ,
|
|
| 64 |
[rootnodeid] [bigint] NULL ,
|
|
| 65 |
[docname] [varchar] (100) NULL ,
|
|
| 66 |
[doctype] [varchar] (100) NULL ,
|
|
| 67 |
[user_owner] [varchar] (100) NULL ,
|
|
| 68 |
[user_updated] [varchar] (100) NULL ,
|
|
| 69 |
[server_location] [bigint] NULL ,
|
|
| 70 |
[rev] [int] NULL ,
|
|
| 71 |
[date_created] [datetime] NULL ,
|
|
| 72 |
[date_updated] [datetime] NULL ,
|
|
| 73 |
[public_access] [bit] NULL ,
|
|
| 74 |
[catalog_id] [bigint] NULL
|
|
| 75 | 75 |
) ON [PRIMARY] |
| 76 | 76 |
GO |
| 77 | 77 |
|
| ... | ... | |
| 80 | 80 |
* quick searching in structured searches |
| 81 | 81 |
*/ |
| 82 | 82 |
CREATE TABLE [dbo].[xml_index] ( |
| 83 |
[nodeid] [bigint] NOT NULL ,
|
|
| 84 |
[path] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
|
|
| 85 |
[docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 86 |
[doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 87 |
[parentnodeid] [bigint] NULL
|
|
| 83 |
[nodeid] [bigint] NOT NULL ,
|
|
| 84 |
[path] [varchar] (200) NOT NULL ,
|
|
| 85 |
[docid] [varchar] (250) NULL ,
|
|
| 86 |
[doctype] [varchar] (100) NULL ,
|
|
| 87 |
[parentnodeid] [bigint] NULL
|
|
| 88 | 88 |
) ON [PRIMARY] |
| 89 | 89 |
GO |
| 90 | 90 |
|
| ... | ... | |
| 92 | 92 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
| 93 | 93 |
*/ |
| 94 | 94 |
CREATE TABLE [dbo].[xml_nodes] ( |
| 95 |
[nodeid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 96 |
[nodeindex] [int] NULL ,
|
|
| 97 |
[nodetype] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 98 |
[nodename] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 99 |
[nodedata] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 100 |
[parentnodeid] [bigint] NULL ,
|
|
| 101 |
[rootnodeid] [bigint] NULL ,
|
|
| 102 |
[docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 103 |
[date_created] [datetime] NULL ,
|
|
| 104 |
[date_updated] [datetime] NULL
|
|
| 95 |
[nodeid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 96 |
[nodeindex] [int] NULL ,
|
|
| 97 |
[nodetype] [varchar] (20) NULL ,
|
|
| 98 |
[nodename] [varchar] (250) NULL ,
|
|
| 99 |
[nodedata] [varchar] (4000) NULL ,
|
|
| 100 |
[parentnodeid] [bigint] NULL ,
|
|
| 101 |
[rootnodeid] [bigint] NULL ,
|
|
| 102 |
[docid] [varchar] (250) NULL ,
|
|
| 103 |
[date_created] [datetime] NULL ,
|
|
| 104 |
[date_updated] [datetime] NULL
|
|
| 105 | 105 |
) ON [PRIMARY] |
| 106 | 106 |
GO |
| 107 | 107 |
|
| ... | ... | |
| 109 | 109 |
* Relations -- table to store relations of form <subject,relationship,object> |
| 110 | 110 |
*/ |
| 111 | 111 |
CREATE TABLE [dbo].[xml_relation] ( |
| 112 |
[relationid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 113 |
[docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 114 |
[packagetype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 115 |
[subject] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
|
|
| 116 |
[subdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 117 |
[relationship] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
|
|
| 118 |
[object] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
|
|
| 119 |
[objdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
|
|
| 112 |
[relationid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 113 |
[docid] [varchar] (250) NULL ,
|
|
| 114 |
[packagetype] [varchar] (250) NULL ,
|
|
| 115 |
[subject] [varchar] (250) NOT NULL ,
|
|
| 116 |
[subdoctype] [varchar] (128) NULL ,
|
|
| 117 |
[relationship] [varchar] (128) NOT NULL ,
|
|
| 118 |
[object] [varchar] (250) NOT NULL ,
|
|
| 119 |
[objdoctype] [varchar] (128) NULL
|
|
| 120 | 120 |
) ON [PRIMARY] |
| 121 | 121 |
GO |
| 122 | 122 |
|
| ... | ... | |
| 124 | 124 |
* Replication -- table to store servers that metacat is replicated to |
| 125 | 125 |
*/ |
| 126 | 126 |
CREATE TABLE [dbo].[xml_replication] ( |
| 127 |
[serverid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 128 |
[server] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 129 |
[last_checked] [datetime] NULL ,
|
|
| 130 |
[replicate] [bit] NULL
|
|
| 127 |
[serverid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 128 |
[server] [varchar] (512) NULL ,
|
|
| 129 |
[last_checked] [datetime] NULL ,
|
|
| 130 |
[replicate] [bit] NULL
|
|
| 131 | 131 |
) ON [PRIMARY] |
| 132 | 132 |
GO |
| 133 | 133 |
|
| ... | ... | |
| 140 | 140 |
* or DELETE |
| 141 | 141 |
*/ |
| 142 | 142 |
CREATE TABLE [dbo].[xml_revisions] ( |
| 143 |
[revisionid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 144 |
[docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 145 |
[rootnodeid] [bigint] NULL ,
|
|
| 146 |
[docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 147 |
[doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 148 |
[user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 149 |
[user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
|
|
| 150 |
[server_location] [bigint] NULL ,
|
|
| 151 |
[rev] [int] NULL ,
|
|
| 152 |
[date_created] [datetime] NULL ,
|
|
| 153 |
[date_updated] [datetime] NULL ,
|
|
| 154 |
[public_access] [bit] NULL ,
|
|
| 155 |
[catalog_id] [bigint] NULL
|
|
| 143 |
[revisionid] [bigint] IDENTITY (1, 1) NOT NULL ,
|
|
| 144 |
[docid] [varchar] (250) NULL ,
|
|
| 145 |
[rootnodeid] [bigint] NULL ,
|
|
| 146 |
[docname] [varchar] (100) NULL ,
|
|
| 147 |
[doctype] [varchar] (100) NULL ,
|
|
| 148 |
[user_owner] [varchar] (100) NULL ,
|
|
| 149 |
[user_updated] [varchar] (100) NULL ,
|
|
| 150 |
[server_location] [bigint] NULL ,
|
|
| 151 |
[rev] [int] NULL ,
|
|
| 152 |
[date_created] [datetime] NULL ,
|
|
| 153 |
[date_updated] [datetime] NULL ,
|
|
| 154 |
[public_access] [bit] NULL ,
|
|
| 155 |
[catalog_id] [bigint] NULL
|
|
| 156 | 156 |
) ON [PRIMARY] |
| 157 | 157 |
GO |
| 158 | 158 |
|
| ... | ... | |
| 160 | 160 |
* Table used as Unique ID generator for the uniqueid part of Accession# |
| 161 | 161 |
*/ |
| 162 | 162 |
CREATE TABLE [dbo].[accession_number] ( |
| 163 |
[uniqueid] [int] IDENTITY (1, 1) NOT NULL,
|
|
| 164 |
[site_code] [varchar] (100),
|
|
| 165 |
[date_created] [datetime],
|
|
| 163 |
[uniqueid] [int] IDENTITY (1, 1) NOT NULL,
|
|
| 164 |
[site_code] [varchar] (100),
|
|
| 165 |
[date_created] [datetime],
|
|
| 166 | 166 |
CONSTRAINT [PK_accession_number] PRIMARY KEY CLUSTERED ([uniqueid]) |
| 167 | 167 |
) ON [PRIMARY] |
| 168 | 168 |
GO |
Also available in: Unified diff
cleared the COLLATION specifiers on column definitions:
it is new in SQL Server 2000 and not supported in its earlier versions