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 1806 2003-08-20 20:40:25Z tao $'
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

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

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

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

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

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

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

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

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

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

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

    
190
/* 
191
 * Constraints and indexes
192
 */
193
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
194
	CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED 
195
	(
196
		[catalog_id]
197
	)  ON [PRIMARY] 
198
GO
199

    
200
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
201
	CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED 
202
	(
203
		[docid]
204
	)  ON [PRIMARY] 
205
GO
206

    
207
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD 
208
	CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED 
209
	(
210
		[nodeid],
211
		[path]
212
	)  ON [PRIMARY] 
213
GO
214

    
215
ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD 
216
	CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED 
217
	(
218
		[nodeid]
219
	)  ON [PRIMARY] 
220
GO
221

    
222
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
223
	CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED 
224
	(
225
		[relationid]
226
	)  ON [PRIMARY] 
227
GO
228

    
229
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD 
230
	CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED 
231
	(
232
		[serverid]
233
	)  ON [PRIMARY] 
234
GO
235

    
236
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD 
237
	CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED 
238
	(
239
		[revisionid]
240
	)  ON [PRIMARY] 
241
GO
242

    
243
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD 
244
	CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time])
245
GO
246

    
247
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
248
	CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED 
249
	(
250
		[entry_type],
251
		[source_doctype],
252
		[target_doctype],
253
		[public_id]
254
	)  ON [PRIMARY] 
255
GO
256

    
257
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
258
	CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev]
259
GO
260

    
261
ALTER TABLE [dbo].[xml_accesssubtree] WITH NOCHECK ADD 
262
	CONSTRAINT [DF_xml_accesssubtree_rev] DEFAULT (1) FOR [rev]
263
GO
264

    
265
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
266
	CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED 
267
	(
268
		[docid],
269
    [subject],
270
		[relationship],
271
		[object]
272
	)  ON [PRIMARY] 
273
GO
274

    
275
 CREATE  INDEX [IX_xml_documents] ON [dbo].[xml_documents]([docid], [doctype])
276
                                                                    ON [PRIMARY]
277
GO
278

    
279
 CREATE  INDEX [IX_xml_index] ON [dbo].[xml_index]([path]) ON [PRIMARY]
280
GO
281

    
282
 CREATE  INDEX [IX1_xml_nodes] ON [dbo].[xml_nodes]([rootnodeid]) ON [PRIMARY]
283
GO
284

    
285
 CREATE  INDEX [IX2_xml_nodes] ON [dbo].[xml_nodes]([parentnodeid]) ON [PRIMARY]
286
GO
287

    
288
 CREATE  INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY]
289
GO
290

    
291
ALTER TABLE [dbo].[xml_accesssubtree] ADD 
292
	CONSTRAINT [FK_xml_accesssubtree_xml_documents] FOREIGN KEY
293
  (
294
		[docid]
295
	) REFERENCES [dbo].[xml_documents] (
296
		[docid]
297
	)
298
GO
299

    
300
ALTER TABLE [dbo].[xml_access] ADD 
301
	CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY 
302
	(
303
		[accessfileid]
304
	) REFERENCES [dbo].[xml_documents] (
305
		[docid]
306
	)
307
GO
308

    
309
ALTER TABLE [dbo].[xml_documents] ADD 
310
	CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY 
311
	(
312
		[catalog_id]
313
	) REFERENCES [dbo].[xml_catalog] (
314
		[catalog_id]
315
	),
316
	CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY 
317
	(
318
		[rootnodeid]
319
	) REFERENCES [dbo].[xml_nodes] (
320
		[nodeid]
321
	),
322
	CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY 
323
	(
324
		[server_location]
325
	) REFERENCES [dbo].[xml_replication] (
326
		[serverid]
327
	)
328
GO
329

    
330
ALTER TABLE [dbo].[xml_index] ADD 
331
	CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY 
332
	(
333
		[docid]
334
	) REFERENCES [dbo].[xml_documents] (
335
		[docid]
336
	),
337
	CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY 
338
	(
339
		[nodeid]
340
	) REFERENCES [dbo].[xml_nodes] (
341
		[nodeid]
342
	)
343
GO
344

    
345
ALTER TABLE [dbo].[xml_nodes] ADD 
346
	CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY 
347
	(
348
		[parentnodeid]
349
	) REFERENCES [dbo].[xml_nodes] (
350
		[nodeid]
351
	),
352
	CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY 
353
	(
354
		[rootnodeid]
355
	) REFERENCES [dbo].[xml_nodes] (
356
		[nodeid]
357
	)
358
GO
359

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

    
369
ALTER TABLE [dbo].[xml_revisions] ADD 
370
	CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY 
371
	(
372
		[catalog_id]
373
	) REFERENCES [dbo].[xml_catalog] (
374
		[catalog_id]
375
	),
376
	CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY 
377
	(
378
		[rootnodeid]
379
	) REFERENCES [dbo].[xml_nodes] (
380
		[nodeid]
381
	),
382
	CONSTRAINT [FK_xml_revisions_xml_replication] FOREIGN KEY 
383
	(
384
		[server_location]
385
	) REFERENCES [dbo].[xml_replication] (
386
		[serverid]
387
	)
388
GO
389

    
(19-19/21)