Project

General

Profile

metacat / src / xmltables-sqlserver.sql @ 3443

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 2359 2004-12-30 23:51:24Z sgarg $'
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

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

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

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

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

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

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

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

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

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

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

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

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

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

    
239
/* 
240
 * Constraints and indexes
241
 */
242
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
243
        CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED 
244
        (
245
                [catalog_id]
246
        )  ON [PRIMARY] 
247
GO
248

    
249
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
250
        CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED 
251
        (
252
                [docid]
253
        )  ON [PRIMARY] 
254
GO
255

    
256
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD 
257
        CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED 
258
        (
259
                [nodeid],
260
                [path]
261
        )  ON [PRIMARY] 
262
GO
263

    
264
ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD 
265
        CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED 
266
        (
267
                [nodeid]
268
        )  ON [PRIMARY] 
269
GO
270

    
271
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
272
        CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED 
273
        (
274
                [relationid]
275
        )  ON [PRIMARY] 
276
GO
277

    
278
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD 
279
        CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED 
280
        (
281
                [serverid]
282
        )  ON [PRIMARY] 
283
GO
284

    
285
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD 
286
        CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED 
287
        (
288
                [revisionid]
289
        )  ON [PRIMARY] 
290
GO
291

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

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

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

    
313
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD 
314
        CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time])
315
GO
316

    
317
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
318
        CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED 
319
        (
320
                [entry_type],
321
                [source_doctype],
322
                [target_doctype],
323
                [public_id]
324
        )  ON [PRIMARY] 
325
GO
326

    
327
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
328
        CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev]
329
GO
330

    
331
ALTER TABLE [dbo].[xml_accesssubtree] WITH NOCHECK ADD 
332
        CONSTRAINT [DF_xml_accesssubtree_rev] DEFAULT (1) FOR [rev]
333
GO
334

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

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

    
349
 CREATE  INDEX [IX_xml_index] ON [dbo].[xml_index]([path]) ON [PRIMARY]
350
GO
351

    
352
 CREATE  INDEX [IX1_xml_nodes] ON [dbo].[xml_nodes]([rootnodeid]) ON [PRIMARY]
353
GO
354

    
355
 CREATE  INDEX [IX2_xml_nodes] ON [dbo].[xml_nodes]([parentnodeid]) ON [PRIMARY]
356
GO
357

    
358
 CREATE  INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY]
359
GO
360

    
361
ALTER TABLE [dbo].[xml_accesssubtree] ADD 
362
        CONSTRAINT [FK_xml_accesssubtree_xml_documents] FOREIGN KEY
363
  (
364
                [docid]
365
        ) REFERENCES [dbo].[xml_documents] (
366
                [docid]
367
        )
368
GO
369

    
370
ALTER TABLE [dbo].[xml_access] ADD 
371
        CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY 
372
        (
373
                [accessfileid]
374
        ) REFERENCES [dbo].[xml_documents] (
375
                [docid]
376
        )
377
GO
378

    
379
ALTER TABLE [dbo].[xml_documents] ADD 
380
        CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY 
381
        (
382
                [catalog_id]
383
        ) REFERENCES [dbo].[xml_catalog] (
384
                [catalog_id]
385
        ),
386
        CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY 
387
        (
388
                [rootnodeid]
389
        ) REFERENCES [dbo].[xml_nodes] (
390
                [nodeid]
391
        ),
392
        CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY 
393
        (
394
                [server_location]
395
        ) REFERENCES [dbo].[xml_replication] (
396
                [serverid]
397
        )
398
GO
399

    
400
ALTER TABLE [dbo].[xml_index] ADD 
401
        CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY 
402
        (
403
                [docid]
404
        ) REFERENCES [dbo].[xml_documents] (
405
                [docid]
406
        ),
407
        CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY 
408
        (
409
                [nodeid]
410
        ) REFERENCES [dbo].[xml_nodes] (
411
                [nodeid]
412
        )
413
GO
414

    
415
ALTER TABLE [dbo].[xml_nodes] ADD 
416
        CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY 
417
        (
418
                [parentnodeid]
419
        ) REFERENCES [dbo].[xml_nodes] (
420
                [nodeid]
421
        ),
422
        CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY 
423
        (
424
                [rootnodeid]
425
        ) REFERENCES [dbo].[xml_nodes] (
426
                [nodeid]
427
        )
428
GO
429

    
430
ALTER TABLE [dbo].[xml_relation] ADD 
431
        CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY 
432
        (
433
                [docid]
434
        ) REFERENCES [dbo].[xml_documents] (
435
                [docid]
436
        )
437
GO
438

    
439
ALTER TABLE [dbo].[xml_revisions] ADD 
440
        CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY 
441
        (
442
                [catalog_id]
443
        ) REFERENCES [dbo].[xml_catalog] (
444
                [catalog_id]
445
        ),
446
        CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY 
447
        (
448
                [rootnodeid]
449
        ) REFERENCES [dbo].[xml_nodes] (
450
                [nodeid]
451
        ),
452
        CONSTRAINT [FK_xml_revisions_xml_replication] FOREIGN KEY 
453
        (
454
                [server_location]
455
        ) REFERENCES [dbo].[xml_replication] (
456
                [serverid]
457
        )
458
GO
459

    
460
ALTER TABLE [dbo].[harvest_detail_log] ADD 
461
        CONSTRAINT [FK_harvest_detail_log_harvest_log] FOREIGN KEY 
462
        (
463
                [harvest_log_id]
464
        ) REFERENCES [dbo].[harvest_log] (
465
                [harvest_log_id]
466
        )
467
GO
468