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 6012 2011-03-17 05:56:31Z leinfelder $'
14
 *
15
 */
16

    
17
/*
18
 * Drop all of the objects in proper order
19
 */
20
drop table [dbo].[xml_index];
21
drop table [dbo].[xml_access];
22
drop table [dbo].[xml_accesssubtree];
23
drop table [dbo].[xml_revisions];
24
drop table [dbo].[xml_relation];
25
drop table [dbo].[xml_documents];
26
drop table [dbo].[xml_nodes];
27
drop table [dbo].[xml_replication];
28
drop table [dbo].[xml_catalog];
29
drop table [dbo].[accession_number];
30
drop table [dbo].[harvest_site_schedule];
31
drop table [dbo].[harvest_log];
32
drop table [dbo].[harvest_detail_log];
33
drop table [dbo].[db_version];
34

    
35
/* 
36
 * ACL -- table to store ACL for XML documents by principals
37
 */
38
CREATE TABLE [dbo].[xml_access] (
39
  [docid]		[varchar] (250) NULL ,
40
  [accessfileid]	[varchar] (250) NULL ,
41
  [principal_name]	[varchar] (100) NULL ,
42
  [permission]		[int] NULL ,
43
  [perm_type]		[varchar] (50) NULL ,
44
  [perm_order]		[varchar] (50) NULL ,
45
  [begin_time]		[datetime] NULL ,
46
  [end_time]		[datetime] NULL ,
47
  [ticket_count]	[int] NULL ,
48
  [subtreeid]  [varchar] (250) NULL ,
49
  [startnodeid]  [bigint] NULL ,
50
  [endnodeid]  [bigint] NULL
51
) ON [PRIMARY]
52
GO
53

    
54
/* 
55
 * accesssubtree -- table to store access subtree info 
56
 */
57
CREATE TABLE [dbo].[xml_accesssubtree] (
58
  [docid]		[varchar] (250) NULL ,
59
  [rev]			[int] NULL , 		
60
  [controllevel]		[varchar] (250) NULL , 
61
  [subtreeid]		[varchar] (250) NULL ,
62
	[startnodeid]  [bigint] NULL ,
63
  [endnodeid]  [bigint] NULL
64
)
65
GO
66

    
67
/* 
68
 * XML Catalog -- table to store all external sources for XML documents
69
 */
70
CREATE TABLE [dbo].[xml_catalog] (
71
  [catalog_id]		[bigint] IDENTITY (1, 1) NOT NULL ,
72
  [entry_type]		[varchar] (50) NULL ,
73
  [source_doctype]	[varchar] (250) NULL ,
74
  [target_doctype]	[varchar] (250) NULL ,
75
  [public_id]		[varchar] (250) NULL ,
76
  [system_id]		[varchar] (512) NULL 
77
) ON [PRIMARY]
78
GO
79

    
80
/* 
81
 * Documents -- table to store XML documents
82
 */
83
CREATE TABLE [dbo].[xml_documents] (
84
  [docid]		[varchar] (250) NOT NULL ,
85
  [rootnodeid]		[bigint] NULL ,
86
  [docname]		[varchar] (100) NULL ,
87
  [doctype]		[varchar] (100) NULL ,
88
  [user_owner]		[varchar] (100) NULL ,
89
  [user_updated]	[varchar] (100) NULL ,
90
  [server_location]	[bigint] NULL ,
91
  [rev]			[int] NULL ,
92
  [date_created]	[datetime] NULL ,
93
  [date_updated]	[datetime] NULL ,
94
  [public_access]	[bit] NULL ,
95
  [catalog_id]		[bigint] NULL 
96
) ON [PRIMARY]
97
GO
98

    
99
/* 
100
 * Index of Nodes -- table to store precomputed paths through tree for 
101
 * quick searching in structured searches
102
 */
103
CREATE TABLE [dbo].[xml_index] (
104
  [nodeid]		[bigint] NOT NULL ,
105
  [path]		[varchar] (200) NOT NULL ,
106
  [docid]		[varchar] (250) NULL ,
107
  [doctype]		[varchar] (100) NULL ,
108
  [parentnodeid]	[bigint] NULL 
109
) ON [PRIMARY]
110
GO
111

    
112
/* 
113
 * Nodes -- table to store XML Nodes (both elements and attributes)
114
 */
115
CREATE TABLE [dbo].[xml_nodes] (
116
  [nodeid]		[bigint] IDENTITY (1, 1) NOT NULL ,
117
  [nodeindex]		[int] NULL ,
118
  [nodetype]		[varchar] (20) NULL ,
119
  [nodename]		[varchar] (250) NULL ,
120
  [nodeprefix]	        [varchar] (50) NULL ,
121
  [nodedata]		[varchar] (4000) NULL ,
122
  [parentnodeid]	[bigint] NULL ,
123
  [rootnodeid]		[bigint] NULL ,
124
  [docid]		[varchar] (250) NULL ,
125
  [date_created]	[datetime] NULL ,
126
  [date_updated]	[datetime] NULL, 
127
  [nodedatanumerical]   [float] NULL,
128
  [nodedatadate]   [datetime] NULL
129
) ON [PRIMARY]
130
GO
131

    
132
/* 
133
 * Relations -- table to store relations of form <subject,relationship,object>
134
 */
135
CREATE TABLE [dbo].[xml_relation] (
136
  [relationid]		[bigint] IDENTITY (1, 1) NOT NULL ,
137
  [docid]		[varchar] (250) NULL ,
138
  [packagetype]		[varchar] (250) NULL ,
139
  [subject]		[varchar] (250) NOT NULL ,
140
  [subdoctype]		[varchar] (128) NULL ,
141
  [relationship]	[varchar] (128) NOT NULL ,
142
  [object]		[varchar] (250) NOT NULL ,
143
  [objdoctype]		[varchar] (128) NULL 
144
) ON [PRIMARY]
145
GO
146

    
147
/* 
148
 * Replication -- table to store servers that metacat is replicated to
149
 */
150
CREATE TABLE [dbo].[xml_replication] (
151
  [serverid]		[bigint] IDENTITY (1, 1) NOT NULL ,
152
  [server]		[varchar] (512) NULL ,
153
  [last_checked]	[datetime] NULL ,
154
  [replicate]  [bit] NULL ,
155
  [datareplicate]  [bit] NULL ,
156
  [hub]  [bit] NULL
157
) ON [PRIMARY]
158
GO
159
     
160
set identity_insert xml_replication on
161
INSERT INTO xml_replication (serverid, server, replicate, datareplicate, hub) VALUES ('1', 'localhost', '0', '0', '0');
162
set identity_insert xml_replication off 
163

    
164
/* 
165
 * Revised Documents -- table to store XML documents saved after an UPDATE
166
 *                    or DELETE
167
 */
168
CREATE TABLE [dbo].[xml_revisions] (
169
  [revisionid]		[bigint] IDENTITY (1, 1) NOT NULL ,
170
  [docid]		[varchar] (250) NULL ,
171
  [rootnodeid]		[bigint] NULL ,
172
  [docname]		[varchar] (100) NULL ,
173
  [doctype]		[varchar] (100) NULL ,
174
  [user_owner]		[varchar] (100) NULL ,
175
  [user_updated]	[varchar] (100) NULL ,
176
  [server_location]	[bigint] NULL ,
177
  [rev]			[int] NULL ,
178
  [date_created]	[datetime] NULL ,
179
  [date_updated]	[datetime] NULL ,
180
  [public_access]	[bit] NULL ,
181
  [catalog_id]		[bigint] NULL 
182
) ON [PRIMARY]
183
GO                                
184

    
185
/* 
186
 * Table used as Unique ID generator for the uniqueid part of Accession#
187
 */
188
CREATE TABLE [dbo].[accession_number] (
189
  [uniqueid]		[int] IDENTITY (1, 1) NOT NULL,
190
  [site_code]		[varchar] (100),
191
  [date_created]	[datetime],
192
  CONSTRAINT [PK_accession_number] PRIMARY KEY CLUSTERED ([uniqueid])
193
) ON [PRIMARY]
194
GO
195

    
196
/* 
197
 * harvest_site_schedule -- table to store harvest sites and schedule info
198
 */
199
CREATE TABLE [dbo].[harvest_site_schedule] (
200
  [site_schedule_id]    [int] IDENTITY (1, 1) NOT NULL ,
201
  [documentlisturl]		[varchar] (250) NOT NULL ,
202
  [ldapdn]		        [varchar] (250) NOT NULL ,
203
  [datenextharvest]		[datetime] NULL ,
204
  [datelastharvest]	    [datetime] NULL ,
205
  [updatefrequency]		[int] NULL ,
206
  [unit]                [varchar] (50) NULL ,
207
  [contact_email]		[varchar] (50) NULL ,
208
  [ldappwd]		        [varchar] (20) NOT NULL 
209
) ON [PRIMARY]
210
GO
211

    
212
/* 
213
 * harvest_log -- table to log entries for harvest operations
214
 */
215
CREATE TABLE [dbo].[harvest_log] (
216
  [harvest_log_id]         [int] IDENTITY (1, 1) NOT NULL ,
217
  [harvest_date]		   [datetime] NOT NULL ,
218
  [status]		           [int] NOT NULL ,
219
  [message]		           [varchar] (1000) NULL ,
220
  [harvest_operation_code] [varchar] (30) NOT NULL ,
221
  [site_schedule_id]       [int] NOT NULL 
222
) ON [PRIMARY]
223
GO
224

    
225
/* 
226
 * harvest_detail_log -- table to log detailed info about documents that
227
 *                       generated errors during the harvest
228
 */
229
CREATE TABLE [dbo].[harvest_detail_log] (
230
  [detail_log_id]   [int] IDENTITY (1, 1) NOT NULL ,
231
  [harvest_log_id]	[int] NOT NULL ,
232
  [scope]		    [varchar] (50) NOT NULL ,
233
  [identifier]		[bigint] NOT NULL ,
234
  [revision]	    [bigint] NOT NULL ,
235
  [document_url]	[varchar] (255) NOT NULL ,
236
  [error_message]   [varchar] (1000) NOT NULL ,
237
  [document_type]   [varchar] (100) NOT NULL 
238
) ON [PRIMARY]
239
GO
240

    
241
/* 
242
 * db_version -- table to store the version history of this database
243
 */
244
CREATE TABLE [dbo].[db_version] (
245
  [db_version_id]	[bigint] IDENTITY (1, 1) NOT NULL ,
246
  [version]		    [varchar] (250) NOT NULL ,
247
  [status]		    [int] NOT NULL ,
248
  [date_created]	[datetime] NULL
249
) ON [PRIMARY]
250
GO
251

    
252
/* 
253
 * Constraints and indexes
254
 */
255
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
256
	CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED 
257
	(
258
		[catalog_id]
259
	)  ON [PRIMARY] 
260
GO
261

    
262
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
263
	CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED 
264
	(
265
		[docid]
266
	)  ON [PRIMARY] 
267
GO
268

    
269
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD 
270
	CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED 
271
	(
272
		[nodeid],
273
		[path]
274
	)  ON [PRIMARY] 
275
GO
276

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

    
284
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
285
	CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED 
286
	(
287
		[relationid]
288
	)  ON [PRIMARY] 
289
GO
290

    
291
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD 
292
	CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED 
293
	(
294
		[serverid]
295
	)  ON [PRIMARY] 
296
GO
297

    
298
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD 
299
	CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED 
300
	(
301
		[revisionid]
302
	)  ON [PRIMARY] 
303
GO
304

    
305
ALTER TABLE [dbo].[harvest_site_schedule] WITH NOCHECK ADD 
306
	CONSTRAINT [PK_harvest_site_schedule] PRIMARY KEY  CLUSTERED 
307
	(
308
		[site_schedule_id]
309
	)  ON [PRIMARY] 
310
GO
311

    
312
ALTER TABLE [dbo].[harvest_log] WITH NOCHECK ADD 
313
	CONSTRAINT [PK_harvest_log] PRIMARY KEY  CLUSTERED 
314
	(
315
		[harvest_log_id]
316
	)  ON [PRIMARY] 
317
GO
318

    
319
ALTER TABLE [dbo].[harvest_detail_log] WITH NOCHECK ADD 
320
	CONSTRAINT [PK_harvest_detail_log] PRIMARY KEY  CLUSTERED 
321
	(
322
		[detail_log_id]
323
	)  ON [PRIMARY] 
324
GO
325

    
326
ALTER TABLE [dbo].[db_version] WITH NOCHECK ADD 
327
	CONSTRAINT [PK_db_version] PRIMARY KEY  CLUSTERED 
328
	(
329
		[db_version_id]
330
	)  ON [PRIMARY] 
331
GO
332

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

    
337
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
338
	CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED 
339
	(
340
		[entry_type],
341
		[source_doctype],
342
		[target_doctype],
343
		[public_id]
344
	)  ON [PRIMARY] 
345
GO
346

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

    
351
ALTER TABLE [dbo].[xml_accesssubtree] WITH NOCHECK ADD 
352
	CONSTRAINT [DF_xml_accesssubtree_rev] DEFAULT (1) FOR [rev]
353
GO
354

    
355
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
356
	CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED 
357
	(
358
		[docid],
359
    [subject],
360
		[relationship],
361
		[object]
362
	)  ON [PRIMARY] 
363
GO
364

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

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

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

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

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

    
381
ALTER TABLE [dbo].[xml_accesssubtree] ADD 
382
	CONSTRAINT [FK_xml_accesssubtree_xml_documents] FOREIGN KEY
383
  (
384
		[docid]
385
	) REFERENCES [dbo].[xml_documents] (
386
		[docid]
387
	)
388
GO
389

    
390
ALTER TABLE [dbo].[xml_access] ADD 
391
	CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY 
392
	(
393
		[accessfileid]
394
	) REFERENCES [dbo].[xml_documents] (
395
		[docid]
396
	)
397
GO
398

    
399
ALTER TABLE [dbo].[xml_documents] ADD 
400
	CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY 
401
	(
402
		[catalog_id]
403
	) REFERENCES [dbo].[xml_catalog] (
404
		[catalog_id]
405
	),
406
	CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY 
407
	(
408
		[rootnodeid]
409
	) REFERENCES [dbo].[xml_nodes] (
410
		[nodeid]
411
	),
412
	CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY 
413
	(
414
		[server_location]
415
	) REFERENCES [dbo].[xml_replication] (
416
		[serverid]
417
	)
418
GO
419

    
420
ALTER TABLE [dbo].[xml_index] ADD 
421
	CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY 
422
	(
423
		[docid]
424
	) REFERENCES [dbo].[xml_documents] (
425
		[docid]
426
	),
427
	CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY 
428
	(
429
		[nodeid]
430
	) REFERENCES [dbo].[xml_nodes] (
431
		[nodeid]
432
	)
433
GO
434

    
435
ALTER TABLE [dbo].[xml_nodes] ADD 
436
	CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY 
437
	(
438
		[parentnodeid]
439
	) REFERENCES [dbo].[xml_nodes] (
440
		[nodeid]
441
	),
442
	CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY 
443
	(
444
		[rootnodeid]
445
	) REFERENCES [dbo].[xml_nodes] (
446
		[nodeid]
447
	)
448
GO
449

    
450
ALTER TABLE [dbo].[xml_relation] ADD 
451
	CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY 
452
	(
453
		[docid]
454
	) REFERENCES [dbo].[xml_documents] (
455
		[docid]
456
	)
457
GO
458

    
459
ALTER TABLE [dbo].[xml_revisions] ADD 
460
	CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY 
461
	(
462
		[catalog_id]
463
	) REFERENCES [dbo].[xml_catalog] (
464
		[catalog_id]
465
	),
466
	CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY 
467
	(
468
		[rootnodeid]
469
	) REFERENCES [dbo].[xml_nodes] (
470
		[nodeid]
471
	),
472
	CONSTRAINT [FK_xml_revisions_xml_replication] FOREIGN KEY 
473
	(
474
		[server_location]
475
	) REFERENCES [dbo].[xml_replication] (
476
		[serverid]
477
	)
478
GO
479

    
480
ALTER TABLE [dbo].[harvest_detail_log] ADD 
481
	CONSTRAINT [FK_harvest_detail_log_harvest_log] FOREIGN KEY 
482
	(
483
		[harvest_log_id]
484
	) REFERENCES [dbo].[harvest_log] (
485
		[harvest_log_id]
486
	)
487
GO
488

    
(57-57/57)