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 762 2001-06-01 19:08:37Z bojilova $'
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_revisions];
23
drop table [dbo].[xml_relation];
24
drop table [dbo].[xml_documents];
25
drop table [dbo].[xml_nodes];
26
drop table [dbo].[xml_replication];
27
drop table [dbo].[xml_catalog];
28
drop table [dbo].[accession_number];
29

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

    
46
/* 
47
 * XML Catalog -- table to store all external sources for XML documents
48
 */
49
CREATE TABLE [dbo].[xml_catalog] (
50
  [catalog_id] [bigint] IDENTITY (1, 1) NOT NULL ,
51
  [entry_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
52
  [source_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
53
  [target_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
54
  [public_id] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
55
  [system_id] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
56
) ON [PRIMARY]
57
GO
58

    
59
/* 
60
 * Documents -- table to store XML documents
61
 */
62
CREATE TABLE [dbo].[xml_documents] (
63
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
64
  [rootnodeid] [bigint] NULL ,
65
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
66
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
67
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
68
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
69
  [server_location] [bigint] NULL ,
70
  [rev] [int] NULL ,
71
  [date_created] [datetime] NULL ,
72
  [date_updated] [datetime] NULL ,
73
  [public_access] [bit] NULL ,
74
  [catalog_id] [bigint] NULL 
75
) ON [PRIMARY]
76
GO
77

    
78
/* 
79
 * Index of Nodes -- table to store precomputed paths through tree for 
80
 * quick searching in structured searches
81
 */
82
CREATE TABLE [dbo].[xml_index] (
83
  [nodeid] [bigint] NOT NULL ,
84
  [path] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
85
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
86
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
87
  [parentnodeid] [bigint] NULL 
88
) ON [PRIMARY]
89
GO
90

    
91
/* 
92
 * Nodes -- table to store XML Nodes (both elements and attributes)
93
 */
94
CREATE TABLE [dbo].[xml_nodes] (
95
  [nodeid] [bigint] IDENTITY (1, 1) NOT NULL ,
96
  [nodeindex] [int] NULL ,
97
  [nodetype] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
98
  [nodename] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
99
  [nodedata] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
100
  [parentnodeid] [bigint] NULL ,
101
  [rootnodeid] [bigint] NULL ,
102
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
103
  [date_created] [datetime] NULL ,
104
  [date_updated] [datetime] NULL 
105
) ON [PRIMARY]
106
GO
107

    
108
/* 
109
 * Relations -- table to store relations of form <subject,relationship,object>
110
 */
111
CREATE TABLE [dbo].[xml_relation] (
112
  [relationid] [bigint] IDENTITY (1, 1) NOT NULL ,
113
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
114
  [packagetype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
115
  [subject] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
116
  [subdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
117
  [relationship] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
118
  [object] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
119
  [objdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
120
) ON [PRIMARY]
121
GO
122

    
123
/* 
124
 * Replication -- table to store servers that metacat is replicated to
125
 */
126
CREATE TABLE [dbo].[xml_replication] (
127
  [serverid] [bigint] IDENTITY (1, 1) NOT NULL ,
128
  [server] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
129
  [last_checked] [datetime] NULL ,
130
  [replicate] [bit] NULL 
131
) ON [PRIMARY]
132
GO
133
     
134
set identity_insert xml_replication on
135
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
136
set identity_insert xml_replication off 
137

    
138
/* 
139
 * Revised Documents -- table to store XML documents saved after an UPDATE
140
 *                    or DELETE
141
 */
142
CREATE TABLE [dbo].[xml_revisions] (
143
  [revisionid] [bigint] IDENTITY (1, 1) NOT NULL ,
144
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
145
  [rootnodeid] [bigint] NULL ,
146
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
147
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
148
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
149
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
150
  [server_location] [bigint] NULL ,
151
  [rev] [int] NULL ,
152
  [date_created] [datetime] NULL ,
153
  [date_updated] [datetime] NULL ,
154
  [public_access] [bit] NULL ,
155
  [catalog_id] [bigint] NULL 
156
) ON [PRIMARY]
157
GO                                
158

    
159
/* 
160
 * Table used as Unique ID generator for the uniqueid part of Accession#
161
 */
162
CREATE TABLE [dbo].[accession_number] (
163
  [uniqueid] [int] IDENTITY (1, 1) NOT NULL,
164
  [site_code] [varchar] (100),
165
  [date_created] [datetime],
166
  CONSTRAINT [PK_accession_number] PRIMARY KEY CLUSTERED ([uniqueid])
167
) ON [PRIMARY]
168
GO
169

    
170
/* 
171
 * Constraints and indexes
172
 */
173
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
174
	CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED 
175
	(
176
		[catalog_id]
177
	)  ON [PRIMARY] 
178
GO
179

    
180
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
181
	CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED 
182
	(
183
		[docid]
184
	)  ON [PRIMARY] 
185
GO
186

    
187
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD 
188
	CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED 
189
	(
190
		[nodeid],
191
		[path]
192
	)  ON [PRIMARY] 
193
GO
194

    
195
ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD 
196
	CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED 
197
	(
198
		[nodeid]
199
	)  ON [PRIMARY] 
200
GO
201

    
202
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
203
	CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED 
204
	(
205
		[relationid]
206
	)  ON [PRIMARY] 
207
GO
208

    
209
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD 
210
	CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED 
211
	(
212
		[serverid]
213
	)  ON [PRIMARY] 
214
GO
215

    
216
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD 
217
	CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED 
218
	(
219
		[revisionid]
220
	)  ON [PRIMARY] 
221
GO
222

    
223
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD 
224
	CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time])
225
GO
226

    
227
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
228
	CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED 
229
	(
230
		[entry_type],
231
		[source_doctype],
232
		[target_doctype],
233
		[public_id]
234
	)  ON [PRIMARY] 
235
GO
236

    
237
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
238
	CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev]
239
GO
240

    
241
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
242
	CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED 
243
	(
244
		[subject],
245
		[relationship],
246
		[object]
247
	)  ON [PRIMARY] 
248
GO
249

    
250
 CREATE  INDEX [IX_xml_documents] ON [dbo].[xml_documents]([docid], [doctype])
251
                                                                    ON [PRIMARY]
252
GO
253

    
254
 CREATE  INDEX [IX_xml_index] ON [dbo].[xml_index]([path]) ON [PRIMARY]
255
GO
256

    
257
 CREATE  INDEX [IX1_xml_nodes] ON [dbo].[xml_nodes]([rootnodeid]) ON [PRIMARY]
258
GO
259

    
260
 CREATE  INDEX [IX2_xml_nodes] ON [dbo].[xml_nodes]([parentnodeid]) ON [PRIMARY]
261
GO
262

    
263
 CREATE  INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY]
264
GO
265

    
266
ALTER TABLE [dbo].[xml_access] ADD 
267
	CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY 
268
	(
269
		[accessfileid]
270
	) REFERENCES [dbo].[xml_documents] (
271
		[docid]
272
	)
273
GO
274

    
275
ALTER TABLE [dbo].[xml_documents] ADD 
276
	CONSTRAINT [FK_xml_documents_xml_catalog] FOREIGN KEY 
277
	(
278
		[catalog_id]
279
	) REFERENCES [dbo].[xml_catalog] (
280
		[catalog_id]
281
	),
282
	CONSTRAINT [FK_xml_documents_xml_nodes] FOREIGN KEY 
283
	(
284
		[rootnodeid]
285
	) REFERENCES [dbo].[xml_nodes] (
286
		[nodeid]
287
	),
288
	CONSTRAINT [FK_xml_documents_xml_replication] FOREIGN KEY 
289
	(
290
		[server_location]
291
	) REFERENCES [dbo].[xml_replication] (
292
		[serverid]
293
	)
294
GO
295

    
296
ALTER TABLE [dbo].[xml_index] ADD 
297
	CONSTRAINT [FK_xml_index_xml_documents] FOREIGN KEY 
298
	(
299
		[docid]
300
	) REFERENCES [dbo].[xml_documents] (
301
		[docid]
302
	),
303
	CONSTRAINT [FK_xml_index_xml_nodes] FOREIGN KEY 
304
	(
305
		[nodeid]
306
	) REFERENCES [dbo].[xml_nodes] (
307
		[nodeid]
308
	)
309
GO
310

    
311
ALTER TABLE [dbo].[xml_nodes] ADD 
312
	CONSTRAINT [FK_xml_nodes_parentnodeid] FOREIGN KEY 
313
	(
314
		[parentnodeid]
315
	) REFERENCES [dbo].[xml_nodes] (
316
		[nodeid]
317
	),
318
	CONSTRAINT [FK_xml_nodes_rootnodeid] FOREIGN KEY 
319
	(
320
		[rootnodeid]
321
	) REFERENCES [dbo].[xml_nodes] (
322
		[nodeid]
323
	)
324
GO
325

    
326
ALTER TABLE [dbo].[xml_relation] ADD 
327
	CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY 
328
	(
329
		[docid]
330
	) REFERENCES [dbo].[xml_documents] (
331
		[docid]
332
	)
333
GO
334

    
335
ALTER TABLE [dbo].[xml_revisions] ADD 
336
	CONSTRAINT [FK_xml_revisions_xml_catalog] FOREIGN KEY 
337
	(
338
		[catalog_id]
339
	) REFERENCES [dbo].[xml_catalog] (
340
		[catalog_id]
341
	),
342
	CONSTRAINT [FK_xml_revisions_xml_nodes] FOREIGN KEY 
343
	(
344
		[rootnodeid]
345
	) REFERENCES [dbo].[xml_nodes] (
346
		[nodeid]
347
	),
348
	CONSTRAINT [FK_xml_revisions_xml_r
349
eplication] FOREIGN KEY 
350
	(
351
		[server_location]
352
	) REFERENCES [dbo].[xml_replication] (
353
		[serverid]
354
	)
355
GO
356

    
(11-11/13)