| 1 |
1827
|
jones
|
/*
|
| 2 |
|
|
* reviseformetacat13_postgres.sql -- Add three columns to xml_access tables
|
| 3 |
|
|
* and create a new table in Production Metacat
|
| 4 |
|
|
*
|
| 5 |
|
|
* Created: 07/14/2002
|
| 6 |
|
|
* Author: Jing Tao
|
| 7 |
|
|
* Organization: National Center for Ecological Analysis and Synthesis
|
| 8 |
|
|
* Copyright: 2000 Regents of the University of California and the
|
| 9 |
|
|
* National Center for Ecological Analysis and Synthesis
|
| 10 |
|
|
* For Details: http://www.nceas.ucsb.edu/
|
| 11 |
|
|
* File Info: '$Id$'
|
| 12 |
|
|
*
|
| 13 |
|
|
*/
|
| 14 |
|
|
|
| 15 |
|
|
|
| 16 |
|
|
/*
|
| 17 |
|
|
* Add tow columns - datareplicate and hub to xml_access
|
| 18 |
|
|
*/
|
| 19 |
|
|
ALTER TABLE xml_access ADD subtreeid VARCHAR(32);
|
| 20 |
|
|
ALTER TABLE xml_access ADD startnodeid INT8;
|
| 21 |
|
|
ALTER TABLE xml_access ADD endnodeid INT8;
|
| 22 |
|
|
|
| 23 |
|
|
/*
|
| 24 |
|
|
* accesssubtree -- table to store access subtree info
|
| 25 |
|
|
*/
|
| 26 |
|
|
|
| 27 |
|
|
CREATE TABLE xml_accesssubtree (
|
| 28 |
|
|
docid VARCHAR(250), -- the document id #
|
| 29 |
|
|
rev INT8 default 1, --the revision number of the docume
|
| 30 |
|
|
controllevel VARCHAR(50), -- the level it control -- document or subtree
|
| 31 |
|
|
subtreeid VARCHAR(250), -- the subtree id
|
| 32 |
|
|
startnodeid INT8, -- the start node id of access subtree
|
| 33 |
|
|
endnodeid INT8, -- the end node if of access subtree
|
| 34 |
|
|
CONSTRAINT xml_accesssubtree_docid_fk
|
| 35 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
| 36 |
|
|
);
|
| 37 |
|
|
|
| 38 |
|
|
/*
|
| 39 |
|
|
* We need to drop constraint(subject, relationship, object) and create new
|
| 40 |
|
|
* new (docid, subject, relationship, object). Unfortunately, progres doesn't
|
| 41 |
|
|
* remove the constrain directly and we should create a new one and copy the
|
| 42 |
|
|
* old data to new one, then rename them.
|
| 43 |
|
|
*/
|
| 44 |
|
|
ALTER TABLE xml_relation RENAME TO old_xml_relation;
|
| 45 |
|
|
DROP INDEX xml_relation_pkey;
|
| 46 |
|
|
/*DROP SEQUENCE xml_relation_id_seq;
|
| 47 |
|
|
*CREATE SEQUENCE xml_relation_id_seq;
|
| 48 |
|
|
*/
|
| 49 |
|
|
CREATE TABLE xml_relation (
|
| 50 |
|
|
relationid INT8 default nextval('xml_relation_id_seq') PRIMARY KEY,
|
| 51 |
|
|
-- unique id
|
| 52 |
|
|
docid VARCHAR(250) , -- the docid of the package file
|
| 53 |
|
|
-- that this relation came from
|
| 54 |
|
|
packagetype VARCHAR(250), -- the type of the package
|
| 55 |
|
|
subject VARCHAR(512) NOT NULL, -- the subject of the relation
|
| 56 |
|
|
subdoctype VARCHAR(128), -- the doctype of the subject
|
| 57 |
|
|
relationship VARCHAR(128) NOT NULL,-- the relationship type
|
| 58 |
|
|
object VARCHAR(512) NOT NULL, -- the object of the relation
|
| 59 |
|
|
objdoctype VARCHAR(128), -- the doctype of the object
|
| 60 |
|
|
CONSTRAINT xml_relation_uk1 UNIQUE (docid, subject, relationship, object),
|
| 61 |
|
|
CONSTRAINT xml_relation_docid_fk1
|
| 62 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
| 63 |
|
|
);
|
| 64 |
|
|
INSERT INTO xml_relation SELECT * FROM old_xml_relation;
|
| 65 |
|
|
|