Project

General

Profile

1
/*
2
 * xmltables-sqlserver.sql
3
 *             : Create or replace tables for storing XML in MS SQL Server
4
 *
5
 *      Purpose: creates tables needed for storing XML in MS SQL Server database
6
 * 
7
 *      Created: 25 May 2001
8
 *       Author: Jivka Bojilova
9
 * 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: xmltables-sqlserver.sql 755 2001-05-29 18:09:52Z bojilova $'
14
 *
15
 */
16

    
17
/*
18
 * Drop all of the objects in proper order
19
 */
20
if exists (select * from dbo.sysobjects 
21
 where id = object_id(N'[dbo].[FK_xml_documents_xml_catalog]') 
22
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
23
ALTER TABLE [dbo].[xml_documents] DROP CONSTRAINT FK_xml_documents_xml_catalog
24
GO
25

    
26
if exists (select * from dbo.sysobjects
27
 where id = object_id(N'[dbo].[FK_xml_revisions_xml_catalog]')
28
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
29
ALTER TABLE [dbo].[xml_revisions] DROP CONSTRAINT FK_xml_revisions_xml_catalog
30
GO
31

    
32
if exists (select * from dbo.sysobjects
33
 where id = object_id(N'[dbo].[FK_xml_access_xml_documents]')
34
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
35
ALTER TABLE [dbo].[xml_access] DROP CONSTRAINT FK_xml_access_xml_documents
36
GO
37

    
38
if exists (select * from dbo.sysobjects
39
 where id = object_id(N'[dbo].[FK_xml_index_xml_documents]')
40
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
41
ALTER TABLE [dbo].[xml_index] DROP CONSTRAINT FK_xml_index_xml_documents
42
GO
43

    
44
if exists (select * from dbo.sysobjects
45
 where id = object_id(N'[dbo].[FK_xml_relation_xml_documents]')
46
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
47
ALTER TABLE [dbo].[xml_relation] DROP CONSTRAINT FK_xml_relation_xml_documents
48
GO
49

    
50
if exists (select * from dbo.sysobjects
51
 where id = object_id(N'[dbo].[FK_xml_documents_xml_nodes]')
52
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
53
ALTER TABLE [dbo].[xml_documents] DROP CONSTRAINT FK_xml_documents_xml_nodes
54
GO
55

    
56
if exists (select * from dbo.sysobjects
57
 where id = object_id(N'[dbo].[FK_xml_index_xml_nodes]')
58
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
59
ALTER TABLE [dbo].[xml_index] DROP CONSTRAINT FK_xml_index_xml_nodes
60
GO
61

    
62
if exists (select * from dbo.sysobjects
63
 where id = object_id(N'[dbo].[FK_xml_nodes_parentnodeid]')
64
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
65
ALTER TABLE [dbo].[xml_nodes] DROP CONSTRAINT FK_xml_nodes_parentnodeid
66
GO
67

    
68
if exists (select * from dbo.sysobjects
69
 where id = object_id(N'[dbo].[FK_xml_nodes_rootnodeid]')
70
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
71
ALTER TABLE [dbo].[xml_nodes] DROP CONSTRAINT FK_xml_nodes_rootnodeid
72
GO
73

    
74
if exists (select * from dbo.sysobjects
75
 where id = object_id(N'[dbo].[FK_xml_revisions_xml_nodes]')
76
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
77
ALTER TABLE [dbo].[xml_revisions] DROP CONSTRAINT FK_xml_revisions_xml_nodes
78
GO
79

    
80
if exists (select * from dbo.sysobjects
81
 where id = object_id(N'[dbo].[FK_xml_documents_xml_replication]')
82
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
83
ALTER TABLE [dbo].[xml_documents] 
84
DROP CONSTRAINT FK_xml_documents_xml_replication
85
GO
86

    
87
if exists (select * from dbo.sysobjects
88
 where id = object_id(N'[dbo].[FK_xml_revisions_xml_replication]')
89
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
90
ALTER TABLE [dbo].[xml_revisions] 
91
DROP CONSTRAINT FK_xml_revisions_xml_replication
92
GO
93

    
94
if exists (select * from dbo.sysobjects
95
 where id = object_id(N'[dbo].[xml_access]')
96
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
97
drop table [dbo].[xml_access]
98
GO
99

    
100
if exists (select * from dbo.sysobjects
101
 where id = object_id(N'[dbo].[xml_catalog]')
102
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
103
drop table [dbo].[xml_catalog]
104
GO
105

    
106
if exists (select * from dbo.sysobjects
107
 where id = object_id(N'[dbo].[xml_documents]')
108
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
109
drop table [dbo].[xml_documents]
110
GO
111

    
112
if exists (select * from dbo.sysobjects
113
 where id = object_id(N'[dbo].[xml_index]')
114
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
115
drop table [dbo].[xml_index]
116
GO
117

    
118
if exists (select * from dbo.sysobjects
119
 where id = object_id(N'[dbo].[xml_nodes]')
120
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
121
drop table [dbo].[xml_nodes]
122
GO
123

    
124
if exists (select * from dbo.sysobjects
125
 where id = object_id(N'[dbo].[xml_relation]')
126
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
127
drop table [dbo].[xml_relation]
128
GO
129

    
130
if exists (select * from dbo.sysobjects
131
 where id = object_id(N'[dbo].[xml_replication]')
132
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
133
drop table [dbo].[xml_replication]
134
GO
135

    
136
if exists (select * from dbo.sysobjects
137
 where id = object_id(N'[dbo].[xml_revisions]')
138
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
139
drop table [dbo].[xml_revisions]
140
GO
141

    
142
/* 
143
 * ACL -- table to store ACL for XML documents by principals
144
 */
145
CREATE TABLE [dbo].[xml_access] (
146
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
147
  [accessfileid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
148
  [principal_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
149
  [permission] [int] NULL ,
150
  [perm_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
151
  [perm_order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
152
  [begin_time] [datetime] NULL ,
153
  [end_time] [datetime] NULL ,
154
  [ticket_count] [int] NULL 
155
) ON [PRIMARY]
156
GO
157

    
158
/* 
159
 * XML Catalog -- table to store all external sources for XML documents
160
 */
161
CREATE TABLE [dbo].[xml_catalog] (
162
  [catalog_id] [bigint] IDENTITY (1, 1) NOT NULL ,
163
  [entry_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
164
  [source_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
165
  [target_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
166
  [public_id] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
167
  [system_id] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
168
) ON [PRIMARY]
169
GO
170

    
171
/* 
172
 * Documents -- table to store XML documents
173
 */
174
CREATE TABLE [dbo].[xml_documents] (
175
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
176
  [rootnodeid] [bigint] NULL ,
177
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
178
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
179
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
180
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
181
  [server_location] [bigint] NULL ,
182
  [rev] [int] NULL ,
183
  [date_created] [datetime] NULL ,
184
  [date_updated] [datetime] NULL ,
185
  [public_access] [bit] NULL ,
186
  [catalog_id] [bigint] NULL 
187
) ON [PRIMARY]
188
GO
189

    
190
/* 
191
 * Index of Nodes -- table to store precomputed paths through tree for 
192
 * quick searching in structured searches
193
 */
194
CREATE TABLE [dbo].[xml_index] (
195
  [nodeid] [bigint] NOT NULL ,
196
  [path] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
197
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
198
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
199
  [parentnodeid] [bigint] NULL 
200
) ON [PRIMARY]
201
GO
202

    
203
/* 
204
 * Nodes -- table to store XML Nodes (both elements and attributes)
205
 */
206
CREATE TABLE [dbo].[xml_nodes] (
207
  [nodeid] [bigint] IDENTITY (1, 1) NOT NULL ,
208
  [nodeindex] [int] NULL ,
209
  [nodetype] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
210
  [nodename] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
211
  [nodedata] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
212
  [parentnodeid] [bigint] NULL ,
213
  [rootnodeid] [bigint] NULL ,
214
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
215
  [date_created] [datetime] NULL ,
216
  [date_updated] [datetime] NULL 
217
) ON [PRIMARY]
218
GO
219

    
220
/* 
221
 * Relations -- table to store relations of form <subject,relationship,object>
222
 */
223
CREATE TABLE [dbo].[xml_relation] (
224
  [relationid] [bigint] IDENTITY (1, 1) NOT NULL ,
225
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
226
  [packagetype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
227
  [subject] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
228
  [subdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
229
  [relationship] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
230
  [object] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
231
  [objdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
232
) ON [PRIMARY]
233
GO
234

    
235
/* 
236
 * Replication -- table to store servers that metacat is replicated to
237
 */
238
CREATE TABLE [dbo].[xml_replication] (
239
  [serverid] [bigint] IDENTITY (1, 1) NOT NULL ,
240
  [server] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
241
  [last_checked] [datetime] NULL ,
242
  [replicate] [bit] NULL 
243
) ON [PRIMARY]
244
GO
245

    
246
/* 
247
 * Revised Documents -- table to store XML documents saved after an UPDATE
248
 *                    or DELETE
249
 */
250
CREATE TABLE [dbo].[xml_revisions] (
251
  [revisionid] [bigint] IDENTITY (1, 1) NOT NULL ,
252
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
253
  [rootnodeid] [bigint] NULL ,
254
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
255
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
256
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
257
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
258
  [server_location] [bigint] NULL ,
259
  [rev] [int] NULL ,
260
  [date_created] [datetime] NULL ,
261
  [date_updated] [datetime] NULL ,
262
  [public_access] [bit] NULL ,
263
  [catalog_id] [bigint] NULL 
264
) ON [PRIMARY]
265
GO
266

    
267
/* 
268
 * Constraints and indexes
269
 */
270
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
271
	CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED 
272
	(
273
		[catalog_id]
274
	)  ON [PRIMARY] 
275
GO
276

    
277
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
278
	CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED 
279
	(
280
		[docid]
281
	)  ON [PRIMARY] 
282
GO
283

    
284
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD 
285
	CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED 
286
	(
287
		[nodeid],
288
		[path]
289
	)  ON [PRIMARY] 
290
GO
291

    
292
ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD 
293
	CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED 
294
	(
295
		[nodeid]
296
	)  ON [PRIMARY] 
297
GO
298

    
299
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
300
	CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED 
301
	(
302
		[relationid]
303
	)  ON [PRIMARY] 
304
GO
305

    
306
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD 
307
	CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED 
308
	(
309
		[serverid]
310
	)  ON [PRIMARY] 
311
GO
312

    
313
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD 
314
	CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED 
315
	(
316
		[revisionid]
317
	)  ON [PRIMARY] 
318
GO
319

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

    
324
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
325
	CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED 
326
	(
327
		[entry_type],
328
		[source_doctype],
329
		[target_doctype],
330
		[public_id]
331
	)  ON [PRIMARY] 
332
GO
333

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

    
338
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
339
	CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED 
340
	(
341
		[subject],
342
		[relationship],
343
		[object]
344
	)  ON [PRIMARY] 
345
GO
346

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

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

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

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

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

    
363
ALTER TABLE [dbo].[xml_access] ADD 
364
	CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY 
365
	(
366
		[accessfileid]
367
	) REFERENCES [dbo].[xml_documents] (
368
		[docid]
369
	)
370
GO
371

    
372
ALTER TABLE [dbo].[xml_documents] ADD 
373
	CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY 
374
	(
375
		[catalog_id]
376
	) REFERENCES [dbo].[xml_catalog] (
377
		[catalog_id]
378
	),
379
	CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY 
380
	(
381
		[rootnodeid]
382
	) REFERENCES [dbo].[xml_nodes] (
383
		[nodeid]
384
	),
385
	CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY 
386
	(
387
		[server_location]
388
	) REFERENCES [dbo].[xml_replication] (
389
		[serverid]
390
	)
391
GO
392

    
393
ALTER TABLE [dbo].[xml_index] ADD 
394
	CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY 
395
	(
396
		[docid]
397
	) REFERENCES [dbo].[xml_documents] (
398
		[docid]
399
	),
400
	CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY 
401
	(
402
		[nodeid]
403
	) REFERENCES [dbo].[xml_nodes] (
404
		[nodeid]
405
	)
406
GO
407

    
408
ALTER TABLE [dbo].[xml_nodes] ADD 
409
	CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY 
410
	(
411
		[parentnodeid]
412
	) REFERENCES [dbo].[xml_nodes] (
413
		[nodeid]
414
	),
415
	CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY 
416
	(
417
		[rootnodeid]
418
	) REFERENCES [dbo].[xml_nodes] (
419
		[nodeid]
420
	)
421
GO
422

    
423
ALTER TABLE [dbo].[xml_relation] ADD 
424
	CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY 
425
	(
426
		[docid]
427
	) REFERENCES [dbo].[xml_documents] (
428
		[docid]
429
	)
430
GO
431

    
432
ALTER TABLE [dbo].[xml_revisions] ADD 
433
	CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY 
434
	(
435
		[catalog_id]
436
	) REFERENCES [dbo].[xml_catalog] (
437
		[catalog_id]
438
	),
439
	CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY 
440
	(
441
		[rootnodeid]
442
	) REFERENCES [dbo].[xml_nodes] (
443
		[nodeid]
444
	),
445
	CONSTRAINT [FK_xml_revisions_xml_r
446
eplication] FOREIGN KEY 
447
	(
448
		[server_location]
449
	) REFERENCES [dbo].[xml_replication] (
450
		[serverid]
451
	)
452
GO
453

    
(11-11/13)