Project

General

Profile

1
/*
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: upgrade-db-to-1.3-postgres.sql 4080 2008-07-07 04:25:34Z daigle $'
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

    
66

    
(25-25/53)