Project

General

Profile

metacat / src / xmltables-sqlserver.sql @ 1424

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 772 2001-06-05 19:24:49Z 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) NULL ,
35
  [accessfileid]        [varchar] (250) NULL ,
36
  [principal_name]        [varchar] (100) NULL ,
37
  [permission]                [int] NULL ,
38
  [perm_type]                [varchar] (50) NULL ,
39
  [perm_order]                [varchar] (50) 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) NULL ,
52
  [source_doctype]        [varchar] (250) NULL ,
53
  [target_doctype]        [varchar] (250) NULL ,
54
  [public_id]                [varchar] (250) NULL ,
55
  [system_id]                [varchar] (512) 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) NOT NULL ,
64
  [rootnodeid]                [bigint] NULL ,
65
  [docname]                [varchar] (100) NULL ,
66
  [doctype]                [varchar] (100) NULL ,
67
  [user_owner]                [varchar] (100) NULL ,
68
  [user_updated]        [varchar] (100) 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) NOT NULL ,
85
  [docid]                [varchar] (250) NULL ,
86
  [doctype]                [varchar] (100) 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) NULL ,
98
  [nodename]                [varchar] (250) NULL ,
99
  [nodedata]                [varchar] (4000) NULL ,
100
  [parentnodeid]        [bigint] NULL ,
101
  [rootnodeid]                [bigint] NULL ,
102
  [docid]                [varchar] (250) 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) NULL ,
114
  [packagetype]                [varchar] (250) NULL ,
115
  [subject]                [varchar] (250) NOT NULL ,
116
  [subdoctype]                [varchar] (128) NULL ,
117
  [relationship]        [varchar] (128) NOT NULL ,
118
  [object]                [varchar] (250) NOT NULL ,
119
  [objdoctype]                [varchar] (128) 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) 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) NULL ,
145
  [rootnodeid]                [bigint] NULL ,
146
  [docname]                [varchar] (100) NULL ,
147
  [doctype]                [varchar] (100) NULL ,
148
  [user_owner]                [varchar] (100) NULL ,
149
  [user_updated]        [varchar] (100) 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