Project

General

Profile

metacat / src / xmltables-sqlserver.sql @ 759

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 759 2001-06-01 00:26:32Z 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
/* 
135
 * Revised Documents -- table to store XML documents saved after an UPDATE
136
 *                    or DELETE
137
 */
138
CREATE TABLE [dbo].[xml_revisions] (
139
  [revisionid] [bigint] IDENTITY (1, 1) NOT NULL ,
140
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
141
  [rootnodeid] [bigint] NULL ,
142
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
143
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
144
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
145
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
146
  [server_location] [bigint] NULL ,
147
  [rev] [int] NULL ,
148
  [date_created] [datetime] NULL ,
149
  [date_updated] [datetime] NULL ,
150
  [public_access] [bit] NULL ,
151
  [catalog_id] [bigint] NULL 
152
) ON [PRIMARY]
153
GO                                
154

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

    
166
/* 
167
 * Constraints and indexes
168
 */
169
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
170
        CONSTRAINT [PK_xml_catalog] PRIMARY KEY  CLUSTERED 
171
        (
172
                [catalog_id]
173
        )  ON [PRIMARY] 
174
GO
175

    
176
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
177
        CONSTRAINT [PK_xml_documents] PRIMARY KEY  CLUSTERED 
178
        (
179
                [docid]
180
        )  ON [PRIMARY] 
181
GO
182

    
183
ALTER TABLE [dbo].[xml_index] WITH NOCHECK ADD 
184
        CONSTRAINT [PK_xml_index] PRIMARY KEY  CLUSTERED 
185
        (
186
                [nodeid],
187
                [path]
188
        )  ON [PRIMARY] 
189
GO
190

    
191
ALTER TABLE [dbo].[xml_nodes] WITH NOCHECK ADD 
192
        CONSTRAINT [PK_xml_nodes] PRIMARY KEY  CLUSTERED 
193
        (
194
                [nodeid]
195
        )  ON [PRIMARY] 
196
GO
197

    
198
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
199
        CONSTRAINT [PK_xml_relation] PRIMARY KEY  CLUSTERED 
200
        (
201
                [relationid]
202
        )  ON [PRIMARY] 
203
GO
204

    
205
ALTER TABLE [dbo].[xml_replication] WITH NOCHECK ADD 
206
        CONSTRAINT [PK_xml_replication] PRIMARY KEY  CLUSTERED 
207
        (
208
                [serverid]
209
        )  ON [PRIMARY] 
210
GO
211

    
212
ALTER TABLE [dbo].[xml_revisions] WITH NOCHECK ADD 
213
        CONSTRAINT [PK_xml_revisions] PRIMARY KEY  CLUSTERED 
214
        (
215
                [revisionid]
216
        )  ON [PRIMARY] 
217
GO
218

    
219
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD 
220
        CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time])
221
GO
222

    
223
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD 
224
        CONSTRAINT [IX_xml_catalog] UNIQUE  NONCLUSTERED 
225
        (
226
                [entry_type],
227
                [source_doctype],
228
                [target_doctype],
229
                [public_id]
230
        )  ON [PRIMARY] 
231
GO
232

    
233
ALTER TABLE [dbo].[xml_documents] WITH NOCHECK ADD 
234
        CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev]
235
GO
236

    
237
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD 
238
        CONSTRAINT [IX_xml_relation] UNIQUE  NONCLUSTERED 
239
        (
240
                [subject],
241
                [relationship],
242
                [object]
243
        )  ON [PRIMARY] 
244
GO
245

    
246
 CREATE  INDEX [IX_xml_documents] ON [dbo].[xml_documents]([docid], [doctype])
247
                                                                    ON [PRIMARY]
248
GO
249

    
250
 CREATE  INDEX [IX_xml_index] ON [dbo].[xml_index]([path]) ON [PRIMARY]
251
GO
252

    
253
 CREATE  INDEX [IX1_xml_nodes] ON [dbo].[xml_nodes]([rootnodeid]) ON [PRIMARY]
254
GO
255

    
256
 CREATE  INDEX [IX2_xml_nodes] ON [dbo].[xml_nodes]([parentnodeid]) ON [PRIMARY]
257
GO
258

    
259
 CREATE  INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY]
260
GO
261

    
262
ALTER TABLE [dbo].[xml_access] ADD 
263
        CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY 
264
        (
265
                [accessfileid]
266
        ) REFERENCES [dbo].[xml_documents] (
267
                [docid]
268
        )
269
GO
270

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

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

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

    
322
ALTER TABLE [dbo].[xml_relation] ADD 
323
        CONSTRAINT [FK_xml_relation_xml_documents] FOREIGN KEY 
324
        (
325
                [docid]
326
        ) REFERENCES [dbo].[xml_documents] (
327
                [docid]
328
        )
329
GO
330

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