Project

General

Profile

Revision 759

Added by bojilova almost 20 years ago

several changes were needed related to the change of the unique id generation and use

View differences:

src/xmltables_postgres.sql
30 30
DROP SEQUENCE xml_replication_id_seq;
31 31
DROP SEQUENCE accnum_uniqueid_seq;
32 32
DROP SEQUENCE xml_documents_id_seq;
33
DROP SEQUENCE accession_number_id_seq;
33 34

  
34 35
DROP TABLE xml_index;
35 36
DROP TABLE xml_access;
......
39 40
DROP TABLE xml_nodes;
40 41
DROP TABLE xml_replication;
41 42
DROP TABLE xml_catalog;
43
DROP TABLE accession_number;
42 44

  
43 45
/*
44 46
 *Replication -- table to store servers that metacat is replicated to
45 47
 */
46 48
CREATE SEQUENCE xml_replication_id_seq;
47 49
CREATE TABLE xml_replication (
48
  serverid     INT8  default nextval('xml_replication_id_seq'), 
49
  server        VARCHAR(512),
50
  serverid	INT8  default nextval('xml_replication_id_seq'), 
51
  server	VARCHAR(512),
50 52
  last_checked  DATE,
51 53
  replicate     INT8, 
52 54
  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
......
57 59
/* 
58 60
 * Nodes -- table to store XML Nodes (both elements and attributes)
59 61
 */
62
CREATE SEQUENCE xml_nodes_id_seq;
60 63
CREATE TABLE xml_nodes (
61
	nodeid		INT8, 		-- the unique node id (pk)
64
	nodeid		INT8 default nextval('xml_nodes_id_seq'),
65
					-- the unique node id (pk)
62 66
	nodeindex	INT8,		-- order of nodes within parent
63 67
	nodetype	VARCHAR(20),	-- type (DOCUMENT, COMMENT, PI,
64 68
					-- ELEMENT, ATTRIBUTE, TEXT)
......
76 80
   CONSTRAINT xml_nodes_parent_fk 
77 81
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
78 82
);
79

  
80 83
/* 
81
 * Indexes of rootnodeid & parentnodeid in xml_nodes
84
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes
82 85
 */
83 86
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
84 87
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
85 88
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
86
CREATE SEQUENCE xml_nodes_id_seq;
87 89

  
88

  
89 90
/* 
90 91
 * XML Catalog -- table to store all external sources for XML documents
91 92
 */
92 93
CREATE SEQUENCE xml_catalog_id_seq;
93 94
CREATE TABLE xml_catalog (
94
	catalog_id INT8 default nextval('xml_catalog_id_seq'), -- the id for this catalog entry
95
	catalog_id	INT8 default nextval('xml_catalog_id_seq'),
96
                                        -- the id for this catalog entry
95 97
	entry_type	VARCHAR(500),	-- the type of this catalog entry
96 98
					-- (e.g., DTD, XSD, XSL)
97 99
	source_doctype	VARCHAR(500),	-- the source public_id for transforms
......
99 101
	public_id	VARCHAR(500),	-- the unique id for this type
100 102
	system_id	VARCHAR(1000),	-- the local location of the object
101 103
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
102
   CONSTRAINT xml_catalog_uk UNIQUE  (entry_type, source_doctype, target_doctype, public_id)
104
   CONSTRAINT xml_catalog_uk UNIQUE  
105
              (entry_type, source_doctype, target_doctype, public_id)
103 106
);
104 107

  
105

  
106 108
/* 
107 109
 * Sequence to get uniqueID for Accession #
108 110
 */
......
112 114
 */
113 115
CREATE TABLE xml_documents (
114 116
	docid		VARCHAR(250),	-- the document id #
115
	rootnodeid	INT8,	-- reference to root node of the DOM
117
	rootnodeid	INT8,		-- reference to root node of the DOM
116 118
	docname		VARCHAR(100),	-- usually the root element name
117 119
	doctype		VARCHAR(100),	-- public id indicating document type
118 120
	user_owner	VARCHAR(100),	-- the user owned the document
119 121
	user_updated	VARCHAR(100),	-- the user updated the document
120 122
	server_location INT8,	-- the server on which this document resides
121
	rev 		INT8,   --the revision number of the document
123
	rev 		INT8,   -- the revision number of the document
122 124
	date_created	DATE,
123 125
	date_updated	DATE,
124 126
	public_access	INT8,	-- flag for public access
......
143 145
 */
144 146
CREATE SEQUENCE xml_revisions_id_seq;
145 147
CREATE TABLE xml_revisions (
146
	revisionid	INT8  default nextval('xml_revisions_id_seq'), -- the revision number we are saving
148
	revisionid	INT8  default nextval('xml_revisions_id_seq'),
149
                                        -- the revision number we are saving
147 150
	docid		VARCHAR(250),	-- the document id #
148
	rootnodeid	INT8,	-- reference to root node of the DOM
151
	rootnodeid	INT8,		-- reference to root node of the DOM
149 152
	docname		VARCHAR(100),	-- usually the root element name
150 153
	doctype		VARCHAR(100),	-- public id indicating document type
151 154
	user_owner	VARCHAR(100),
......
209 212

  
210 213
CREATE SEQUENCE xml_relation_id_seq;
211 214
CREATE TABLE xml_relation (
212
	relationid    INT8 default nextval('xml_relation_id_seq')  PRIMARY KEY, -- unique id
215
	relationid    INT8 default nextval('xml_relation_id_seq') PRIMARY KEY,
216
					     -- unique id
213 217
	docid         VARCHAR(250) ,         -- the docid of the package file
214
	                                      -- that this relation came from
218
	                                     -- that this relation came from
215 219
        packagetype   VARCHAR(250),          -- the type of the package
216 220
	subject       VARCHAR(512) NOT NULL, -- the subject of the relation
217 221
	subdoctype    VARCHAR(128),         	-- the doctype of the subject
......
223 227
		FOREIGN KEY (docid) REFERENCES xml_documents
224 228
  );
225 229

  
230
/* 
231
 * Table used as Unique ID generator for the uniqueid part of Accession#
232
 */
233
CREATE SEQUENCE accession_number_id_seq;
234
CREATE TABLE accession_number (
235
   uniqueid	INT8    default nextval('accession_number_id_seq') PRIMARY KEY,
236
   site_code	VARCHAR(100),
237
   date_created	DATE,
238
);
226 239

  
240

  
241

  
src/xmltables.sql
25 25
DROP SEQUENCE xml_replication_id_seq;
26 26
DROP SEQUENCE accnum_uniqueid_seq;
27 27
DROP SEQUENCE xml_documents_id_seq;
28

  
28
DROP SEQUENCE accession_number_id_seq;
29
               
30
/* Drop triggers are not necessary */
31
DROP TRIGGER xml_nodes_before_insert;
29 32
DROP TRIGGER xml_revisions_before_insert;
30 33
DROP TRIGGER xml_catalog_before_insert;
31 34
DROP TRIGGER xml_relation_before_insert;
32 35
DROP TRIGGER xml_replication_before_insert;
36
DROP TRIGGER accession_number_before_insert;
33 37

  
34 38
DROP TABLE xml_index;
35 39
DROP TABLE xml_access;
......
39 43
DROP TABLE xml_nodes;
40 44
DROP TABLE xml_replication;
41 45
DROP TABLE xml_catalog;
46
DROP TABLE accession_number;
42 47

  
43 48
/*
44 49
 *Replication -- table to store servers that metacat is replicated to
......
66 71
/* 
67 72
 * Nodes -- table to store XML Nodes (both elements and attributes)
68 73
 */
74
CREATE SEQUENCE xml_nodes_id_seq;
69 75
CREATE TABLE xml_nodes (
70 76
	nodeid		NUMBER(20),	-- the unique node id (pk)
71 77
	nodeindex	NUMBER(10),	-- order of nodes within parent
......
85 91
   CONSTRAINT xml_nodes_parent_fk 
86 92
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
87 93
);
88

  
94
CREATE TRIGGER xml_nodes_before_insert
95
BEFORE INSERT ON xml_nodes FOR EACH ROW
96
BEGIN
97
  SELECT xml_nodes_id_seq.nextval
98
    INTO :new.nodeid
99
    FROM dual;     
100
END;    
101
/
102
                               
89 103
/* 
90
 * Indexes of rootnodeid & parentnodeid in xml_nodes
104
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes
91 105
 */
92 106
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
93 107
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
94 108
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
95 109

  
96
CREATE SEQUENCE xml_nodes_id_seq;
97

  
98 110
/* 
99 111
 * XML Catalog -- table to store all external sources for XML documents
100 112
 */
......
148 160
);
149 161

  
150 162
/* 
151
 * Sequence to get uniqueID for Accession #
152
 */
153
CREATE SEQUENCE xml_documents_id_seq;
154

  
155
/* 
156 163
 * Index of <docid,doctype> in xml_document
157 164
 */
158 165
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
......
258 265
  SELECT xml_relation_id_seq.nextval
259 266
    INTO :new.relationid
260 267
    FROM dual;
261
END;
268
END;                                   
262 269
/
263 270

  
271
/* 
272
 * Table used as Unique ID generator for the uniqueid part of Accession#
273
 */
274
CREATE SEQUENCE accession_number_id_seq;
275
CREATE TABLE accession_number (
276
	uniqueid	NUMBER(20) PRIMARY KEY,
277
	site_code	VARCHAR2(100),
278
	date_created	DATE
279
);
280
CREATE TRIGGER accession_number_before_insert
281
BEFORE INSERT ON accession_number FOR EACH ROW
282
BEGIN
283
  SELECT accession_number_id_seq.nextval
284
    INTO :new.uniqueid
285
    FROM dual;
286
END;                                   
287
/
288

  
src/xmltables-sqlserver.sql
17 17
/*
18 18
 * Drop all of the objects in proper order
19 19
 */
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
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];
25 29

  
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
31

  
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
37

  
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

  
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
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

  
142 30
/* 
143 31
 * ACL -- table to store ACL for XML documents by principals
144 32
 */
......
262 150
  [public_access] [bit] NULL ,
263 151
  [catalog_id] [bigint] NULL 
264 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]
265 164
GO
266 165

  
267 166
/* 

Also available in: Unified diff