Revision 898
Added by berkley over 22 years ago
| src/xmltables_postgres.sql | ||
|---|---|---|
| 21 | 21 |
/* |
| 22 | 22 |
* Drop all of the objects in proper order |
| 23 | 23 |
*/ |
| 24 |
set echo off |
|
| 25 | 24 |
|
| 26 | 25 |
DROP SEQUENCE xml_nodes_id_seq; |
| 27 | 26 |
DROP SEQUENCE xml_revisions_id_seq; |
| 28 | 27 |
DROP SEQUENCE xml_catalog_id_seq; |
| 29 | 28 |
DROP SEQUENCE xml_relation_id_seq; |
| 30 | 29 |
DROP SEQUENCE xml_replication_id_seq; |
| 31 |
DROP SEQUENCE accnum_uniqueid_seq; |
|
| 32 | 30 |
DROP SEQUENCE xml_documents_id_seq; |
| 33 | 31 |
DROP SEQUENCE accession_number_id_seq; |
| 34 | 32 |
|
| ... | ... | |
| 47 | 45 |
*/ |
| 48 | 46 |
CREATE SEQUENCE xml_replication_id_seq; |
| 49 | 47 |
CREATE TABLE xml_replication ( |
| 50 |
serverid INT8 default nextval('xml_replication_id_seq'),
|
|
| 51 |
server VARCHAR(512),
|
|
| 52 |
last_checked DATE,
|
|
| 53 |
replicate INT8,
|
|
| 48 |
serverid INT8 default nextval('xml_replication_id_seq'),
|
|
| 49 |
server VARCHAR(512),
|
|
| 50 |
last_checked DATE, |
|
| 51 |
replicate INT8, |
|
| 54 | 52 |
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid) |
| 55 | 53 |
); |
| 56 | 54 |
|
| ... | ... | |
| 61 | 59 |
*/ |
| 62 | 60 |
CREATE SEQUENCE xml_nodes_id_seq; |
| 63 | 61 |
CREATE TABLE xml_nodes ( |
| 64 |
nodeid INT8 default nextval('xml_nodes_id_seq'),
|
|
| 62 |
nodeid INT8 default nextval('xml_nodes_id_seq'),
|
|
| 65 | 63 |
-- the unique node id (pk) |
| 66 |
nodeindex INT8, -- order of nodes within parent
|
|
| 67 |
nodetype VARCHAR(20), -- type (DOCUMENT, COMMENT, PI,
|
|
| 64 |
nodeindex INT8, -- order of nodes within parent
|
|
| 65 |
nodetype VARCHAR(20), -- type (DOCUMENT, COMMENT, PI,
|
|
| 68 | 66 |
-- ELEMENT, ATTRIBUTE, TEXT) |
| 69 |
nodename VARCHAR(250), -- the name of an element or attribute |
|
| 70 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
|
| 67 |
nodename VARCHAR(250), -- the name of an element or attribute |
|
| 68 |
nodeprefix VARCHAR(50), -- the namespace prefix of the node |
|
| 69 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
|
| 71 | 70 |
-- for TEXT it is the content) |
| 72 |
parentnodeid INT8, -- index of the parent of this node
|
|
| 73 |
rootnodeid INT8, -- index of the root node of this tree
|
|
| 74 |
docid VARCHAR(250), -- index to the document id
|
|
| 75 |
date_created DATE,
|
|
| 76 |
date_updated DATE,
|
|
| 71 |
parentnodeid INT8, -- index of the parent of this node
|
|
| 72 |
rootnodeid INT8, -- index of the root node of this tree
|
|
| 73 |
docid VARCHAR(250), -- index to the document id
|
|
| 74 |
date_created DATE,
|
|
| 75 |
date_updated DATE,
|
|
| 77 | 76 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
| 78 | 77 |
CONSTRAINT xml_nodes_root_fk |
| 79 | 78 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
| ... | ... | |
| 92 | 91 |
*/ |
| 93 | 92 |
CREATE SEQUENCE xml_catalog_id_seq; |
| 94 | 93 |
CREATE TABLE xml_catalog ( |
| 95 |
catalog_id INT8 default nextval('xml_catalog_id_seq'),
|
|
| 94 |
catalog_id INT8 default nextval('xml_catalog_id_seq'),
|
|
| 96 | 95 |
-- the id for this catalog entry |
| 97 |
entry_type VARCHAR(500), -- the type of this catalog entry
|
|
| 96 |
entry_type VARCHAR(500), -- the type of this catalog entry
|
|
| 98 | 97 |
-- (e.g., DTD, XSD, XSL) |
| 99 |
source_doctype VARCHAR(500), -- the source public_id for transforms
|
|
| 100 |
target_doctype VARCHAR(500), -- the target public_id for transforms
|
|
| 101 |
public_id VARCHAR(500), -- the unique id for this type
|
|
| 102 |
system_id VARCHAR(1000), -- the local location of the object
|
|
| 98 |
source_doctype VARCHAR(500), -- the source public_id for transforms
|
|
| 99 |
target_doctype VARCHAR(500), -- the target public_id for transforms
|
|
| 100 |
public_id VARCHAR(500), -- the unique id for this type
|
|
| 101 |
system_id VARCHAR(1000), -- the local location of the object
|
|
| 103 | 102 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
| 104 | 103 |
CONSTRAINT xml_catalog_uk UNIQUE |
| 105 | 104 |
(entry_type, source_doctype, target_doctype, public_id) |
| ... | ... | |
| 113 | 112 |
* Documents -- table to store XML documents |
| 114 | 113 |
*/ |
| 115 | 114 |
CREATE TABLE xml_documents ( |
| 116 |
docid VARCHAR(250), -- the document id #
|
|
| 117 |
rootnodeid INT8, -- reference to root node of the DOM
|
|
| 118 |
docname VARCHAR(100), -- usually the root element name
|
|
| 119 |
doctype VARCHAR(100), -- public id indicating document type
|
|
| 120 |
user_owner VARCHAR(100), -- the user owned the document
|
|
| 121 |
user_updated VARCHAR(100), -- the user updated the document
|
|
| 115 |
docid VARCHAR(250), -- the document id #
|
|
| 116 |
rootnodeid INT8, -- reference to root node of the DOM
|
|
| 117 |
docname VARCHAR(100), -- usually the root element name
|
|
| 118 |
doctype VARCHAR(100), -- public id indicating document type
|
|
| 119 |
user_owner VARCHAR(100), -- the user owned the document
|
|
| 120 |
user_updated VARCHAR(100), -- the user updated the document
|
|
| 122 | 121 |
server_location INT8, -- the server on which this document resides |
| 123 |
rev INT8, -- the revision number of the document
|
|
| 124 |
date_created DATE,
|
|
| 125 |
date_updated DATE,
|
|
| 126 |
public_access INT8, -- flag for public access
|
|
| 127 |
catalog_id INT8, -- reference to xml_catalog
|
|
| 122 |
rev INT8, -- the revision number of the document |
|
| 123 |
date_created DATE,
|
|
| 124 |
date_updated DATE,
|
|
| 125 |
public_access INT8, -- flag for public access
|
|
| 126 |
catalog_id INT8, -- reference to xml_catalog |
|
| 128 | 127 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
| 129 | 128 |
CONSTRAINT xml_documents_rep_fk |
| 130 | 129 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
| ... | ... | |
| 145 | 144 |
*/ |
| 146 | 145 |
CREATE SEQUENCE xml_revisions_id_seq; |
| 147 | 146 |
CREATE TABLE xml_revisions ( |
| 148 |
revisionid INT8 default nextval('xml_revisions_id_seq'),
|
|
| 147 |
revisionid INT8 default nextval('xml_revisions_id_seq'),
|
|
| 149 | 148 |
-- the revision number we are saving |
| 150 |
docid VARCHAR(250), -- the document id #
|
|
| 151 |
rootnodeid INT8, -- reference to root node of the DOM
|
|
| 152 |
docname VARCHAR(100), -- usually the root element name
|
|
| 153 |
doctype VARCHAR(100), -- public id indicating document type
|
|
| 154 |
user_owner VARCHAR(100),
|
|
| 155 |
user_updated VARCHAR(100),
|
|
| 149 |
docid VARCHAR(250), -- the document id #
|
|
| 150 |
rootnodeid INT8, -- reference to root node of the DOM
|
|
| 151 |
docname VARCHAR(100), -- usually the root element name
|
|
| 152 |
doctype VARCHAR(100), -- public id indicating document type
|
|
| 153 |
user_owner VARCHAR(100),
|
|
| 154 |
user_updated VARCHAR(100),
|
|
| 156 | 155 |
server_location INT8, |
| 157 |
rev INT8,
|
|
| 158 |
date_created DATE,
|
|
| 159 |
date_updated DATE,
|
|
| 160 |
public_access INT8, -- flag for public access
|
|
| 161 |
catalog_id INT8, -- reference to xml_catalog
|
|
| 156 |
rev INT8,
|
|
| 157 |
date_created DATE,
|
|
| 158 |
date_updated DATE,
|
|
| 159 |
public_access INT8, -- flag for public access
|
|
| 160 |
catalog_id INT8, -- reference to xml_catalog |
|
| 162 | 161 |
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid), |
| 163 | 162 |
CONSTRAINT xml_revisions_rep_fk |
| 164 | 163 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
| ... | ... | |
| 173 | 172 |
* ACL -- table to store ACL for XML documents by principals |
| 174 | 173 |
*/ |
| 175 | 174 |
CREATE TABLE xml_access ( |
| 176 |
docid VARCHAR(250), -- the document id #
|
|
| 177 |
accessfileid VARCHAR(250), -- the document id # for the access file
|
|
| 178 |
principal_name VARCHAR(100), -- name of user, group, etc.
|
|
| 179 |
permission INT8, -- "read", "write", "all"
|
|
| 180 |
perm_type VARCHAR(32), -- "allowed" or "denied"
|
|
| 181 |
perm_order VARCHAR(32), -- "allow first" or "deny first"
|
|
| 182 |
begin_time DATE, -- the time that permission begins
|
|
| 183 |
end_time DATE, -- the time that permission ends
|
|
| 184 |
ticket_count INT8, -- ticket counter for that permission
|
|
| 175 |
docid VARCHAR(250), -- the document id #
|
|
| 176 |
accessfileid VARCHAR(250), -- the document id # for the access file
|
|
| 177 |
principal_name VARCHAR(100), -- name of user, group, etc.
|
|
| 178 |
permission INT8, -- "read", "write", "all"
|
|
| 179 |
perm_type VARCHAR(32), -- "allowed" or "denied"
|
|
| 180 |
perm_order VARCHAR(32), -- "allow first" or "deny first"
|
|
| 181 |
begin_time DATE, -- the time that permission begins
|
|
| 182 |
end_time DATE, -- the time that permission ends
|
|
| 183 |
ticket_count INT8, -- ticket counter for that permission
|
|
| 185 | 184 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time), |
| 186 | 185 |
CONSTRAINT xml_access_accessfileid_fk |
| 187 | 186 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents |
| ... | ... | |
| 192 | 191 |
* quick searching in structured searches |
| 193 | 192 |
*/ |
| 194 | 193 |
CREATE TABLE xml_index ( |
| 195 |
nodeid INT8, -- the unique node id
|
|
| 196 |
path VARCHAR(200), -- precomputed path through tree
|
|
| 197 |
docid VARCHAR(250), -- index to the document id
|
|
| 198 |
doctype VARCHAR(100), -- public id indicating document type
|
|
| 199 |
parentnodeid INT8, -- id of the parent of the node represented
|
|
| 194 |
nodeid INT8, -- the unique node id
|
|
| 195 |
path VARCHAR(200), -- precomputed path through tree
|
|
| 196 |
docid VARCHAR(250), -- index to the document id
|
|
| 197 |
doctype VARCHAR(100), -- public id indicating document type
|
|
| 198 |
parentnodeid INT8, -- id of the parent of the node represented |
|
| 200 | 199 |
-- by this row |
| 201 | 200 |
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path), |
| 202 | 201 |
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes, |
| ... | ... | |
| 212 | 211 |
|
| 213 | 212 |
CREATE SEQUENCE xml_relation_id_seq; |
| 214 | 213 |
CREATE TABLE xml_relation ( |
| 215 |
relationid INT8 default nextval('xml_relation_id_seq') PRIMARY KEY,
|
|
| 214 |
relationid INT8 default nextval('xml_relation_id_seq') PRIMARY KEY,
|
|
| 216 | 215 |
-- unique id |
| 217 |
docid VARCHAR(250) , -- the docid of the package file
|
|
| 216 |
docid VARCHAR(250) , -- the docid of the package file |
|
| 218 | 217 |
-- that this relation came from |
| 219 |
packagetype VARCHAR(250), -- the type of the package
|
|
| 220 |
subject VARCHAR(512) NOT NULL, -- the subject of the relation
|
|
| 221 |
subdoctype VARCHAR(128), -- the doctype of the subject
|
|
| 222 |
relationship VARCHAR(128) NOT NULL,-- the relationship type
|
|
| 223 |
object VARCHAR(512) NOT NULL, -- the object of the relation
|
|
| 224 |
objdoctype VARCHAR(128), -- the doctype of the object
|
|
| 218 |
packagetype VARCHAR(250), -- the type of the package |
|
| 219 |
subject VARCHAR(512) NOT NULL, -- the subject of the relation |
|
| 220 |
subdoctype VARCHAR(128), -- the doctype of the subject |
|
| 221 |
relationship VARCHAR(128) NOT NULL,-- the relationship type |
|
| 222 |
object VARCHAR(512) NOT NULL, -- the object of the relation |
|
| 223 |
objdoctype VARCHAR(128), -- the doctype of the object |
|
| 225 | 224 |
CONSTRAINT xml_relation_uk UNIQUE (subject, relationship, object), |
| 226 | 225 |
CONSTRAINT xml_relation_docid_fk |
| 227 | 226 |
FOREIGN KEY (docid) REFERENCES xml_documents |
| 228 |
);
|
|
| 227 |
); |
|
| 229 | 228 |
|
| 230 | 229 |
/* |
| 231 | 230 |
* Table used as Unique ID generator for the uniqueid part of Accession# |
| 232 | 231 |
*/ |
| 233 | 232 |
CREATE SEQUENCE accession_number_id_seq; |
| 234 | 233 |
CREATE TABLE accession_number ( |
| 235 |
uniqueid INT8 default nextval('accession_number_id_seq') PRIMARY KEY,
|
|
| 236 |
site_code VARCHAR(100),
|
|
| 237 |
date_created DATE,
|
|
| 234 |
uniqueid INT8 default nextval('accession_number_id_seq') PRIMARY KEY,
|
|
| 235 |
site_code VARCHAR(100),
|
|
| 236 |
date_created DATE
|
|
| 238 | 237 |
); |
| 239 | 238 |
|
| 240 | 239 |
|
Also available in: Unified diff
updated postgres sql script so that it creates the same table structure as the oracle script. removed a bunch of \t tabs that were messing things up