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 1740 tao
drop table [dbo].[xml_accesssubtree];
30 729 berkley
31
/*
32 755 bojilova
 * ACL -- table to store ACL for XML documents by principals
33 729 berkley
 */
34 755 bojilova
CREATE TABLE [dbo].[xml_access] (
35 772 bojilova
  [docid]		[varchar] (250) NULL ,
36
  [accessfileid]	[varchar] (250) NULL ,
37
  [principal_name]	[varchar] (100) NULL ,
38
  [permission]		[int] NULL ,
39
  [perm_type]		[varchar] (50) NULL ,
40
  [perm_order]		[varchar] (50) NULL ,
41
  [begin_time]		[datetime] NULL ,
42
  [end_time]		[datetime] NULL ,
43 1743 tao
  [ticket_count]	[int] NULL ,
44 1740 tao
  [subtreeid]  [varchar] (250) NULL ,
45
  [startnodeid]  [bigint] NULL ,
46
  [endnodeid]  [bigint] NULL
47 755 bojilova
) ON [PRIMARY]
48
GO
49 729 berkley
50
/*
51 1740 tao
 * accesssubtree -- table to store access subtree info
52
 */
53
CREATE TABLE [dbo].[xml_accesssubtree] (
54
  [docid]		[varchar] (250) NULL ,
55
  [rev]			[int] NULL ,
56
  [controllevel]		[varchar] (250) NULL ,
57
  [subtreeid]		[varchar] (250) NULL ,
58
	[startnodeid]  [bigint] NULL ,
59
  [endnodeid]  [bigint] NULL
60
)
61
GO
62
63
/*
64 729 berkley
 * XML Catalog -- table to store all external sources for XML documents
65
 */
66 755 bojilova
CREATE TABLE [dbo].[xml_catalog] (
67 772 bojilova
  [catalog_id]		[bigint] IDENTITY (1, 1) NOT NULL ,
68
  [entry_type]		[varchar] (50) NULL ,
69
  [source_doctype]	[varchar] (250) NULL ,
70
  [target_doctype]	[varchar] (250) NULL ,
71
  [public_id]		[varchar] (250) NULL ,
72
  [system_id]		[varchar] (512) NULL
73 755 bojilova
) ON [PRIMARY]
74
GO
75 729 berkley
76
/*
77
 * Documents -- table to store XML documents
78
 */
79 755 bojilova
CREATE TABLE [dbo].[xml_documents] (
80 772 bojilova
  [docid]		[varchar] (250) NOT NULL ,
81
  [rootnodeid]		[bigint] NULL ,
82
  [docname]		[varchar] (100) NULL ,
83
  [doctype]		[varchar] (100) NULL ,
84
  [user_owner]		[varchar] (100) NULL ,
85
  [user_updated]	[varchar] (100) NULL ,
86
  [server_location]	[bigint] NULL ,
87
  [rev]			[int] NULL ,
88
  [date_created]	[datetime] NULL ,
89
  [date_updated]	[datetime] NULL ,
90
  [public_access]	[bit] NULL ,
91
  [catalog_id]		[bigint] NULL
92 755 bojilova
) ON [PRIMARY]
93
GO
94 729 berkley
95
/*
96 755 bojilova
 * Index of Nodes -- table to store precomputed paths through tree for
97
 * quick searching in structured searches
98 729 berkley
 */
99 755 bojilova
CREATE TABLE [dbo].[xml_index] (
100 772 bojilova
  [nodeid]		[bigint] NOT NULL ,
101
  [path]		[varchar] (200) NOT NULL ,
102
  [docid]		[varchar] (250) NULL ,
103
  [doctype]		[varchar] (100) NULL ,
104
  [parentnodeid]	[bigint] NULL
105 755 bojilova
) ON [PRIMARY]
106
GO
107 729 berkley
108
/*
109 755 bojilova
 * Nodes -- table to store XML Nodes (both elements and attributes)
110 729 berkley
 */
111 755 bojilova
CREATE TABLE [dbo].[xml_nodes] (
112 772 bojilova
  [nodeid]		[bigint] IDENTITY (1, 1) NOT NULL ,
113
  [nodeindex]		[int] NULL ,
114
  [nodetype]		[varchar] (20) NULL ,
115
  [nodename]		[varchar] (250) NULL ,
116 1743 tao
  [nodeprefix]	        [varchar] (50) NULL ,
117 772 bojilova
  [nodedata]		[varchar] (4000) NULL ,
118
  [parentnodeid]	[bigint] NULL ,
119
  [rootnodeid]		[bigint] NULL ,
120
  [docid]		[varchar] (250) NULL ,
121
  [date_created]	[datetime] NULL ,
122
  [date_updated]	[datetime] NULL
123 755 bojilova
) ON [PRIMARY]
124
GO
125 729 berkley
126
/*
127 755 bojilova
 * Relations -- table to store relations of form <subject,relationship,object>
128 729 berkley
 */
129 755 bojilova
CREATE TABLE [dbo].[xml_relation] (
130 772 bojilova
  [relationid]		[bigint] IDENTITY (1, 1) NOT NULL ,
131
  [docid]		[varchar] (250) NULL ,
132
  [packagetype]		[varchar] (250) NULL ,
133
  [subject]		[varchar] (250) NOT NULL ,
134
  [subdoctype]		[varchar] (128) NULL ,
135
  [relationship]	[varchar] (128) NOT NULL ,
136
  [object]		[varchar] (250) NOT NULL ,
137
  [objdoctype]		[varchar] (128) NULL
138 755 bojilova
) ON [PRIMARY]
139
GO
140 729 berkley
141
/*
142 755 bojilova
 * Replication -- table to store servers that metacat is replicated to
143 729 berkley
 */
144 755 bojilova
CREATE TABLE [dbo].[xml_replication] (
145 772 bojilova
  [serverid]		[bigint] IDENTITY (1, 1) NOT NULL ,
146
  [server]		[varchar] (512) NULL ,
147
  [last_checked]	[datetime] NULL ,
148 1743 tao
  [replicate]  [bit] NULL ,
149
  [datareplicate]  [bit] NULL ,
150 1740 tao
  [hub]  [bit] NULL
151 755 bojilova
) ON [PRIMARY]
152
GO
153 762 bojilova
154
set identity_insert xml_replication on
155 1740 tao
INSERT INTO xml_replication (serverid, server, replicate, datareplicate, hub) VALUES ('1', 'localhost', '0', '0', '0');
156 762 bojilova
set identity_insert xml_replication off
157 729 berkley
158
/*
159 755 bojilova
 * Revised Documents -- table to store XML documents saved after an UPDATE
160
 *                    or DELETE
161 729 berkley
 */
162 755 bojilova
CREATE TABLE [dbo].[xml_revisions] (
163 772 bojilova
  [revisionid]		[bigint] IDENTITY (1, 1) NOT NULL ,
164
  [docid]		[varchar] (250) NULL ,
165
  [rootnodeid]		[bigint] NULL ,
166
  [docname]		[varchar] (100) NULL ,
167
  [doctype]		[varchar] (100) NULL ,
168
  [user_owner]		[varchar] (100) NULL ,
169
  [user_updated]	[varchar] (100) NULL ,
170
  [server_location]	[bigint] NULL ,
171
  [rev]			[int] NULL ,
172
  [date_created]	[datetime] NULL ,
173
  [date_updated]	[datetime] NULL ,
174
  [public_access]	[bit] NULL ,
175
  [catalog_id]		[bigint] NULL
176 755 bojilova
) ON [PRIMARY]
177 759 bojilova
GO
178
179
/*
180
 * Table used as Unique ID generator for the uniqueid part of Accession#
181
 */
182
CREATE TABLE [dbo].[accession_number] (
183 772 bojilova
  [uniqueid]		[int] IDENTITY (1, 1) NOT NULL,
184
  [site_code]		[varchar] (100),
185
  [date_created]	[datetime],
186 759 bojilova
  CONSTRAINT [PK_accession_number] PRIMARY KEY CLUSTERED ([uniqueid])
187
) ON [PRIMARY]
188 755 bojilova
GO
189 729 berkley
190
/*
191 755 bojilova
 * Constraints and indexes
192 729 berkley
 */
193 755 bojilova
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD
194
	CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED
195
	(
196
		[catalog_id]
197
	)  ON [PRIMARY]
198
GO
199 729 berkley
200 755 bojilova
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD
201
	CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED
202
	(
203
		[docid]
204
	)  ON [PRIMARY]
205
GO
206 729 berkley
207 755 bojilova
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD
208
	CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED
209
	(
210
		[nodeid],
211
		[path]
212
	)  ON [PRIMARY]
213
GO
214 729 berkley
215 755 bojilova
ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD
216
	CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED
217
	(
218
		[nodeid]
219
	)  ON [PRIMARY]
220
GO
221
222
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD
223
	CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED
224
	(
225
		[relationid]
226
	)  ON [PRIMARY]
227
GO
228
229
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD
230
	CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED
231
	(
232
		[serverid]
233
	)  ON [PRIMARY]
234
GO
235
236
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD
237
	CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED
238
	(
239
		[revisionid]
240
	)  ON [PRIMARY]
241
GO
242
243
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD
244
	CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time])
245
GO
246
247
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD
248
	CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED
249
	(
250
		[entry_type],
251
		[source_doctype],
252
		[target_doctype],
253
		[public_id]
254
	)  ON [PRIMARY]
255
GO
256
257
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD
258
	CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev]
259
GO
260
261 1740 tao
ALTER TABLE [dbo].[xml_accesssubtree] WITH NOCHECK ADD
262
	CONSTRAINT [DF_xml_accesssubtree_rev] DEFAULT (1) FOR [rev]
263
GO
264
265 755 bojilova
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD
266
	CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED
267
	(
268 1740 tao
		[docid],
269
    [subject],
270 755 bojilova
		[relationship],
271
		[object]
272
	)  ON [PRIMARY]
273
GO
274
275
 CREATE  INDEX [IX_xml_documents] ON [dbo].[xml_documents]([docid], [doctype])
276
                                                                    ON [PRIMARY]
277
GO
278
279
 CREATE  INDEX [IX_xml_index] ON [dbo].[xml_index]([path]) ON [PRIMARY]
280
GO
281
282
 CREATE  INDEX [IX1_xml_nodes] ON [dbo].[xml_nodes]([rootnodeid]) ON [PRIMARY]
283
GO
284
285
 CREATE  INDEX [IX2_xml_nodes] ON [dbo].[xml_nodes]([parentnodeid]) ON [PRIMARY]
286
GO
287
288
 CREATE  INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY]
289
GO
290
291 1740 tao
ALTER TABLE [dbo].[xml_accesssubtree] ADD
292
	CONSTRAINT [FK_xml_accesssubtree_xml_documents] FOREIGN KEY
293
  (
294
		[docid]
295
	) REFERENCES [dbo].[xml_documents] (
296
		[docid]
297
	)
298
GO
299
300 755 bojilova
ALTER TABLE [dbo].[xml_access] ADD
301
	CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY
302
	(
303
		[accessfileid]
304
	) REFERENCES [dbo].[xml_documents] (
305
		[docid]
306
	)
307
GO
308
309
ALTER TABLE [dbo].[xml_documents] ADD
310
	CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY
311
	(
312
		[catalog_id]
313
	) REFERENCES [dbo].[xml_catalog] (
314
		[catalog_id]
315
	),
316
	CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY
317
	(
318
		[rootnodeid]
319
	) REFERENCES [dbo].[xml_nodes] (
320
		[nodeid]
321
	),
322
	CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY
323
	(
324
		[server_location]
325
	) REFERENCES [dbo].[xml_replication] (
326
		[serverid]
327
	)
328
GO
329
330
ALTER TABLE [dbo].[xml_index] ADD
331
	CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY
332
	(
333
		[docid]
334
	) REFERENCES [dbo].[xml_documents] (
335
		[docid]
336
	),
337
	CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY
338
	(
339
		[nodeid]
340
	) REFERENCES [dbo].[xml_nodes] (
341
		[nodeid]
342
	)
343
GO
344
345
ALTER TABLE [dbo].[xml_nodes] ADD
346
	CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY
347
	(
348
		[parentnodeid]
349
	) REFERENCES [dbo].[xml_nodes] (
350
		[nodeid]
351
	),
352
	CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY
353
	(
354
		[rootnodeid]
355
	) REFERENCES [dbo].[xml_nodes] (
356
		[nodeid]
357
	)
358
GO
359
360
ALTER TABLE [dbo].[xml_relation] ADD
361
	CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY
362
	(
363
		[docid]
364
	) REFERENCES [dbo].[xml_documents] (
365
		[docid]
366
	)
367
GO
368
369
ALTER TABLE [dbo].[xml_revisions] ADD
370
	CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY
371
	(
372
		[catalog_id]
373
	) REFERENCES [dbo].[xml_catalog] (
374
		[catalog_id]
375
	),
376
	CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY
377
	(
378
		[rootnodeid]
379
	) REFERENCES [dbo].[xml_nodes] (
380
		[nodeid]
381
	),
382 1740 tao
	CONSTRAINT [FK_xml_revisions_xml_replication] FOREIGN KEY
383 755 bojilova
	(
384
		[server_location]
385
	) REFERENCES [dbo].[xml_replication] (
386
		[serverid]
387
	)
388
GO