Project

General

Profile

metacat / src / xmltables-sqlserver.sql @ 755

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