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