Project

General

Profile

metacat / src / xmltables-sqlserver.sql @ 4699

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 4080 2008-07-07 04:25:34Z daigle $'
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
) ON [PRIMARY]
129
GO
130

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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