Project

General

Profile

1 729 berkley
/*
2 755 bojilova
 * xmltables-sqlserver.sql
3
 *             : Create or replace tables for storing XML in MS SQL Server
4 729 berkley
 *
5 755 bojilova
 *      Purpose: creates tables needed for storing XML in MS SQL Server database
6 729 berkley
 *
7 755 bojilova
 *      Created: 25 May 2001
8
 *       Author: Jivka Bojilova
9 729 berkley
 * Organization: National Center for Ecological Analysis and Synthesis
10
 *    Copyright: 2000 Regents of the University of California and the
11
 *               National Center for Ecological Analysis and Synthesis
12
 *  For Details: http://www.nceas.ucsb.edu/
13
 *    File Info: '$Id$'
14
 *
15
 */
16
17
/*
18
 * Drop all of the objects in proper order
19
 */
20 759 bojilova
drop table [dbo].[xml_index];
21
drop table [dbo].[xml_access];
22
drop table [dbo].[xml_revisions];
23
drop table [dbo].[xml_relation];
24
drop table [dbo].[xml_documents];
25
drop table [dbo].[xml_nodes];
26
drop table [dbo].[xml_replication];
27
drop table [dbo].[xml_catalog];
28
drop table [dbo].[accession_number];
29 729 berkley
30
/*
31 755 bojilova
 * ACL -- table to store ACL for XML documents by principals
32 729 berkley
 */
33 755 bojilova
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
43
) ON [PRIMARY]
44
GO
45 729 berkley
46
/*
47
 * XML Catalog -- table to store all external sources for XML documents
48
 */
49 755 bojilova
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
56
) ON [PRIMARY]
57
GO
58 729 berkley
59
/*
60
 * Documents -- table to store XML documents
61
 */
62 755 bojilova
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
75
) ON [PRIMARY]
76
GO
77 729 berkley
78
/*
79 755 bojilova
 * Index of Nodes -- table to store precomputed paths through tree for
80
 * quick searching in structured searches
81 729 berkley
 */
82 755 bojilova
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
88
) ON [PRIMARY]
89
GO
90 729 berkley
91
/*
92 755 bojilova
 * Nodes -- table to store XML Nodes (both elements and attributes)
93 729 berkley
 */
94 755 bojilova
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
105
) ON [PRIMARY]
106
GO
107 729 berkley
108
/*
109 755 bojilova
 * Relations -- table to store relations of form <subject,relationship,object>
110 729 berkley
 */
111 755 bojilova
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
120
) ON [PRIMARY]
121
GO
122 729 berkley
123
/*
124 755 bojilova
 * Replication -- table to store servers that metacat is replicated to
125 729 berkley
 */
126 755 bojilova
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
131
) ON [PRIMARY]
132
GO
133 762 bojilova
134
set identity_insert xml_replication on
135
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
136
set identity_insert xml_replication off
137 729 berkley
138
/*
139 755 bojilova
 * Revised Documents -- table to store XML documents saved after an UPDATE
140
 *                    or DELETE
141 729 berkley
 */
142 755 bojilova
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
156
) ON [PRIMARY]
157 759 bojilova
GO
158
159
/*
160
 * Table used as Unique ID generator for the uniqueid part of Accession#
161
 */
162
CREATE TABLE [dbo].[accession_number] (
163
  [uniqueid] [int] IDENTITY (1, 1) NOT NULL,
164
  [site_code] [varchar] (100),
165
  [date_created] [datetime],
166
  CONSTRAINT [PK_accession_number] PRIMARY KEY CLUSTERED ([uniqueid])
167
) ON [PRIMARY]
168 755 bojilova
GO
169 729 berkley
170
/*
171 755 bojilova
 * Constraints and indexes
172 729 berkley
 */
173 755 bojilova
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD
174
	CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED
175
	(
176
		[catalog_id]
177
	)  ON [PRIMARY]
178
GO
179 729 berkley
180 755 bojilova
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD
181
	CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED
182
	(
183
		[docid]
184
	)  ON [PRIMARY]
185
GO
186 729 berkley
187 755 bojilova
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD
188
	CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED
189
	(
190
		[nodeid],
191
		[path]
192
	)  ON [PRIMARY]
193
GO
194 729 berkley
195 755 bojilova
ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD
196
	CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED
197
	(
198
		[nodeid]
199
	)  ON [PRIMARY]
200
GO
201
202
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD
203
	CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED
204
	(
205
		[relationid]
206
	)  ON [PRIMARY]
207
GO
208
209
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD
210
	CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED
211
	(
212
		[serverid]
213
	)  ON [PRIMARY]
214
GO
215
216
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD
217
	CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED
218
	(
219
		[revisionid]
220
	)  ON [PRIMARY]
221
GO
222
223
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD
224
	CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time])
225
GO
226
227
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD
228
	CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED
229
	(
230
		[entry_type],
231
		[source_doctype],
232
		[target_doctype],
233
		[public_id]
234
	)  ON [PRIMARY]
235
GO
236
237
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD
238
	CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev]
239
GO
240
241
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD
242
	CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED
243
	(
244
		[subject],
245
		[relationship],
246
		[object]
247
	)  ON [PRIMARY]
248
GO
249
250
 CREATE  INDEX [IX_xml_documents] ON [dbo].[xml_documents]([docid], [doctype])
251
                                                                    ON [PRIMARY]
252
GO
253
254
 CREATE  INDEX [IX_xml_index] ON [dbo].[xml_index]([path]) ON [PRIMARY]
255
GO
256
257
 CREATE  INDEX [IX1_xml_nodes] ON [dbo].[xml_nodes]([rootnodeid]) ON [PRIMARY]
258
GO
259
260
 CREATE  INDEX [IX2_xml_nodes] ON [dbo].[xml_nodes]([parentnodeid]) ON [PRIMARY]
261
GO
262
263
 CREATE  INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY]
264
GO
265
266
ALTER TABLE [dbo].[xml_access] ADD
267
	CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY
268
	(
269
		[accessfileid]
270
	) REFERENCES [dbo].[xml_documents] (
271
		[docid]
272
	)
273
GO
274
275
ALTER TABLE [dbo].[xml_documents] ADD
276
	CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY
277
	(
278
		[catalog_id]
279
	) REFERENCES [dbo].[xml_catalog] (
280
		[catalog_id]
281
	),
282
	CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY
283
	(
284
		[rootnodeid]
285
	) REFERENCES [dbo].[xml_nodes] (
286
		[nodeid]
287
	),
288
	CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY
289
	(
290
		[server_location]
291
	) REFERENCES [dbo].[xml_replication] (
292
		[serverid]
293
	)
294
GO
295
296
ALTER TABLE [dbo].[xml_index] ADD
297
	CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY
298
	(
299
		[docid]
300
	) REFERENCES [dbo].[xml_documents] (
301
		[docid]
302
	),
303
	CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY
304
	(
305
		[nodeid]
306
	) REFERENCES [dbo].[xml_nodes] (
307
		[nodeid]
308
	)
309
GO
310
311
ALTER TABLE [dbo].[xml_nodes] ADD
312
	CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY
313
	(
314
		[parentnodeid]
315
	) REFERENCES [dbo].[xml_nodes] (
316
		[nodeid]
317
	),
318
	CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY
319
	(
320
		[rootnodeid]
321
	) REFERENCES [dbo].[xml_nodes] (
322
		[nodeid]
323
	)
324
GO
325
326
ALTER TABLE [dbo].[xml_relation] ADD
327
	CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY
328
	(
329
		[docid]
330
	) REFERENCES [dbo].[xml_documents] (
331
		[docid]
332
	)
333
GO
334
335
ALTER TABLE [dbo].[xml_revisions] ADD
336
	CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY
337
	(
338
		[catalog_id]
339
	) REFERENCES [dbo].[xml_catalog] (
340
		[catalog_id]
341
	),
342
	CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY
343
	(
344
		[rootnodeid]
345
	) REFERENCES [dbo].[xml_nodes] (
346
		[nodeid]
347
	),
348
	CONSTRAINT [FK_xml_revisions_xml_r
349
eplication] FOREIGN KEY
350
	(
351
		[server_location]
352
	) REFERENCES [dbo].[xml_replication] (
353
		[serverid]
354
	)
355
GO