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:

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

  

Also available in: Unified diff