Project

General

Profile

Revision 755

Added by bojilova almost 20 years ago

- made xmltables-sqlserver.sql from SQL Server running here on rincon machine
- included db index on xml_nodes.nodename which was missing

View differences:

src/xmltables_postgres.sql
1 1
/*
2
 * xmltables.sql -- Create or replace tables for storing XML in the db
2
 * xmltables-postgres.sql
3
 *             : Create or replace tables for storing XML in PostgreSQL
3 4
 *
4
 *      Purpose: creates tables needed for XML database
5
 *      Purpose: creates tables needed for storing XML in PostgreSQL database
5 6
 * 
6 7
 *      Created: 08 May 2001 
7 8
 *       Author: John Harris
8 9
 * Organization: National Center for Ecological Analysis and Synthesis
9 10
 *    Copyright: 2000 Regents of the University of California and the
10 11
 *               National Center for Ecological Analysis and Synthesis
12
 *  For Details: http://www.nceas.ucsb.edu/
13
 *    File Info: '$Id$'
11 14
 *
12 15
 *	this is sql script does the same as the sql script named 
13 16
 *	xmltables.sql except that this script is to be use to 
......
79 82
 */
80 83
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
81 84
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
85
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
82 86
CREATE SEQUENCE xml_nodes_id_seq;
83 87

  
84 88

  
src/xmltables.sql
91 91
 */
92 92
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
93 93
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
94
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
94 95

  
95 96
CREATE SEQUENCE xml_nodes_id_seq;
96 97

  
src/xmltables-sqlserver.sql
1 1
/*
2
 * xmltables.sql -- Create or replace tables for storing XML in the db
2
 * xmltables-sqlserver.sql
3
 *             : Create or replace tables for storing XML in MS SQL Server
3 4
 *
4
 *      Purpose: creates tables needed for XML database
5
 *      Purpose: creates tables needed for storing XML in MS SQL Server database
5 6
 * 
6
 *      Created: 12 September 1999
7
 *       Author: Matt Jones
7
 *      Created: 25 May 2001
8
 *       Author: Jivka Bojilova
8 9
 * Organization: National Center for Ecological Analysis and Synthesis
9 10
 *    Copyright: 2000 Regents of the University of California and the
10 11
 *               National Center for Ecological Analysis and Synthesis
......
16 17
/*
17 18
 * Drop all of the objects in proper order
18 19
 */
19
/*set echo off*/
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
20 25

  
21
CREATE TRIGGER xml_replication_before_insert BEFORE INSERT
22
ON xml_replication FOR EACH ROW
23
BEGIN
24
  SELECT xml_replication_id_seq.nextval
25
    INTO :new.serverid
26
    FROM dual;
27
END;
28
/
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
29 31

  
30
/*CREATE SEQUENCE xml_replication_id_seq;*/
31
/*
32
 *Replication -- table to store servers that metacat is replicated to
33
 */
34
CREATE TABLE xml_replication (
35
  serverid      NUMBER(20),
36
  server        VARCHAR2(512),
37
  last_checked  DATE,
38
  replicate     NUMBER(1), 
39
  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
40
);  
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
41 37

  
42
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
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 43

  
44
/* 
45
 * Nodes -- table to store XML Nodes (both elements and attributes)
46
 */
47
CREATE TABLE xml_nodes (
48
	nodeid		NUMBER(20),	-- the unique node id (pk)
49
	nodeindex	NUMBER(10),	-- order of nodes within parent
50
	nodetype	VARCHAR2(20),	-- type (DOCUMENT, COMMENT, PI,
51
					-- ELEMENT, ATTRIBUTE, TEXT)
52
	nodename	VARCHAR2(250),	-- the name of an element or attribute
53
	nodedata	VARCHAR2(4000), -- the data for this node (e.g., 
54
					-- for TEXT it is the content)
55
	parentnodeid	NUMBER(20),	-- index of the parent of this node
56
	rootnodeid	NUMBER(20),	-- index of the root node of this tree
57
	docid		VARCHAR2(250),	-- index to the document id
58
	date_created	DATE,
59
	date_updated	DATE,
60
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
61
   CONSTRAINT xml_nodes_root_fk 
62
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
63
   CONSTRAINT xml_nodes_parent_fk 
64
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
65
);
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
66 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

  
67 142
/* 
68
 * Indexes of rootnodeid & parentnodeid in xml_nodes
143
 * ACL -- table to store ACL for XML documents by principals
69 144
 */
70
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
71
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
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
72 157

  
73
CREATE SEQUENCE xml_nodes_id_seq;
74

  
75 158
/* 
76 159
 * XML Catalog -- table to store all external sources for XML documents
77 160
 */
78
CREATE TABLE xml_catalog (
79
	catalog_id	NUMBER(20),	-- the id for this catalog entry
80
	entry_type	VARCHAR2(500),	-- the type of this catalog entry
81
					-- (e.g., DTD, XSD, XSL)
82
	source_doctype	VARCHAR2(500),	-- the source public_id for transforms
83
	target_doctype	VARCHAR2(500),	-- the target public_id for transforms
84
	public_id	VARCHAR2(500),	-- the unique id for this type
85
	system_id	VARCHAR2(1000),	-- the local location of the object
86
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
87
   CONSTRAINT xml_catalog_uk UNIQUE 
88
		(entry_type, source_doctype, target_doctype, public_id)
89
);
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
90 170

  
91
CREATE SEQUENCE xml_catalog_id_seq;
92

  
93
CREATE TRIGGER xml_catalog_before_insert
94
BEFORE INSERT ON xml_catalog FOR EACH ROW
95
BEGIN
96
  SELECT xml_catalog_id_seq.nextval
97
    INTO :new.catalog_id
98
    FROM dual;
99
END;
100
/
101

  
102 171
/* 
103 172
 * Documents -- table to store XML documents
104 173
 */
105
CREATE TABLE xml_documents (
106
	docid		VARCHAR2(250),	-- the document id #
107
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
108
	docname		VARCHAR2(100),	-- usually the root element name
109
	doctype		VARCHAR2(100),	-- public id indicating document type
110
	user_owner	VARCHAR2(100),	-- the user owned the document
111
	user_updated	VARCHAR2(100),	-- the user updated the document
112
	server_location NUMBER(20),	-- the server on which this document resides
113
	rev 		NUMBER(10) DEFAULT 1,--the revision number of the document
114
	date_created	DATE,
115
	date_updated	DATE,
116
	public_access	NUMBER(1),	-- flag for public access
117
        catalog_id      NUMBER(20),	-- reference to xml_catalog 
118
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
119
   CONSTRAINT xml_documents_rep_fk
120
    		FOREIGN KEY (server_location) REFERENCES xml_replication, 
121
   CONSTRAINT xml_documents_root_fk 
122
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
123
   CONSTRAINT xml_documents_catalog_fk 
124
		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
125
);
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
126 189

  
127 190
/* 
128
 * Index of <docid,doctype> in xml_document
191
 * Index of Nodes -- table to store precomputed paths through tree for 
192
 * quick searching in structured searches
129 193
 */
130
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
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
131 202

  
132 203
/* 
133
 * Revised Documents -- table to store XML documents saved after an UPDATE
134
 *                    or DELETE
204
 * Nodes -- table to store XML Nodes (both elements and attributes)
135 205
 */
136
CREATE TABLE xml_revisions (
137
	revisionid	NUMBER(20),	-- the revision number we are saving
138
	docid		VARCHAR2(250),	-- the document id #
139
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
140
	docname		VARCHAR2(100),	-- usually the root element name
141
	doctype		VARCHAR2(100),	-- public id indicating document type
142
	user_owner	VARCHAR2(100),
143
	user_updated	VARCHAR2(100),
144
	server_location NUMBER(20),
145
	rev		NUMBER(10),
146
	date_created	DATE,
147
	date_updated	DATE,
148
	public_access	NUMBER(1),	-- flag for public access
149
        catalog_id      NUMBER(20),	-- reference to xml_catalog 
150
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
151
   CONSTRAINT xml_revisions_rep_fk
152
		FOREIGN KEY (server_location) REFERENCES xml_replication,
153
   CONSTRAINT xml_revisions_root_fk 
154
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
155
   CONSTRAINT xml_revisions_catalog_fk 
156
		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
157
);
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
158 219

  
159
CREATE SEQUENCE xml_revisions_id_seq;
160

  
161
CREATE TRIGGER xml_revisions_before_insert
162
BEFORE INSERT ON xml_revisions FOR EACH ROW
163
BEGIN
164
  SELECT xml_revisions_id_seq.nextval
165
    INTO :new.revisionid
166
    FROM dual;
167
END;
168
/
169

  
170 220
/* 
171
 * ACL -- table to store ACL for XML documents by principals
221
 * Relations -- table to store relations of form <subject,relationship,object>
172 222
 */
173
CREATE TABLE xml_access (
174
	docid		VARCHAR2(250),	-- the document id #
175
	accessfileid	VARCHAR2(250),	-- the document id # for the access file
176
	principal_name	VARCHAR2(100),	-- name of user, group, etc.
177
	permission	NUMBER(1),	-- "read", "write", "all"
178
	perm_type	VARCHAR2(32),	-- "allowed" or "denied"
179
	perm_order	VARCHAR2(32),	-- "allow first" or "deny first"
180
	begin_time	DATE,		-- the time that permission begins
181
	end_time	DATE,		-- the time that permission ends
182
	ticket_count	NUMBER(5),	-- ticket counter for that permission
183
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
184
   CONSTRAINT xml_access_accessfileid_fk 
185
		FOREIGN KEY (accessfileid) REFERENCES xml_documents
186
);
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
187 234

  
188 235
/* 
189
 * Index of Nodes -- table to store precomputed paths through tree for 
190
 * quick searching in structured searches
236
 * Replication -- table to store servers that metacat is replicated to
191 237
 */
192
CREATE TABLE xml_index (
193
	nodeid		NUMBER(20),	-- the unique node id
194
	path		VARCHAR2(200),	-- precomputed path through tree
195
	docid		VARCHAR2(250),	-- index to the document id
196
	doctype		VARCHAR2(100),	-- public id indicating document type
197
        parentnodeid    NUMBER(20),     -- id of the parent of the node represented
198
					-- by this row
199
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
200
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
201
   CONSTRAINT xml_index_docid_fk 
202
		FOREIGN KEY (docid) REFERENCES xml_documents
203
);
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
204 245

  
205 246
/* 
206
 * Index of the paths in xml_index 
247
 * Revised Documents -- table to store XML documents saved after an UPDATE
248
 *                    or DELETE
207 249
 */
208
CREATE INDEX xml_index_idx1 ON xml_index (path);
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
209 266

  
210 267
/* 
211
 * Sequence to get uniqueID for Accession #
268
 * Constraints and indexes
212 269
 */
213
CREATE SEQUENCE accnum_uniqueid_seq;
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
214 276

  
215
CREATE TABLE xml_relation (
216
	relationid    NUMBER(20) PRIMARY KEY, -- unique id
217
	docid         VARCHAR2(250) ,         -- the docid of the package file
218
	                                      -- that this relation came from
219
        packagetype   VARCHAR2(250),          -- the type of the package
220
	subject       VARCHAR2(512) NOT NULL, -- the subject of the relation
221
	subdoctype    VARCHAR2(128),         	-- the doctype of the subject
222
	relationship  VARCHAR2(128)  NOT NULL,-- the relationship type
223
	object        VARCHAR2(512) NOT NULL, -- the object of the relation
224
	objdoctype    VARCHAR2(128),          -- the doctype of the object
225
	CONSTRAINT xml_relation_uk UNIQUE (subject, relationship, object),
226
	CONSTRAINT xml_relation_docid_fk 
227
		FOREIGN KEY (docid) REFERENCES xml_documents
228
  );
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
229 283

  
230
CREATE SEQUENCE xml_relation_id_seq;
231
  
232
CREATE TRIGGER xml_relation_before_insert
233
BEFORE INSERT ON xml_relation FOR EACH ROW
234
BEGIN
235
  SELECT xml_relation_id_seq.nextval
236
    INTO :new.relationid
237
    FROM dual;
238
END;
239
/
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
240 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

  

Also available in: Unified diff