Project

General

Profile

metacat / src / upgrade-db-to-2.0.0-postgres.sql @ 6800

1
CREATE TABLE systemMetadata (
2
        guid   text,          -- the globally unique string identifier of the object that the system metadata describes
3
        serial_version VARCHAR(256), --the serial version of the object
4
        date_uploaded TIMESTAMP, -- the date/time the document was first submitted
5
        rights_holder VARCHAR(250), --the user who has rights to the document, usually the first persons to upload it
6
        checksum VARCHAR(512), --the checksum of the doc using the given algorithm (see below)
7
        checksum_algorithm VARCHAR(250), --the algorithm used to calculate the checksum
8
        origin_member_node VARCHAR(250), --the member node where the document was first uploaded
9
        authoritive_member_node VARCHAR(250), --the member node that currently controls the document
10
        date_modified TIMESTAMP, -- the last date/time that the file was changed
11
        submitter VARCHAR(256), -- the user who originally submitted the doc
12
        object_format VARCHAR(256), --the format of the object
13
        size VARCHAR(256), --the size of the object
14
        archived boolean,         -- specifies whether this an archived object
15
        replication_allowed boolean,         -- replication allowed
16
        number_replicas INT8,         -- the number of replicas allowed
17
        obsoletes   text,       -- the identifier that this record obsoletes
18
        obsoleted_by   text,     -- the identifier of the record that replaces this record
19
        CONSTRAINT systemMetadata_pk PRIMARY KEY (guid)
20
);
21

    
22
CREATE TABLE systemMetadataReplicationPolicy (
23
        guid text,        -- the globally unique string identifier of the object that the system metadata describes
24
        member_node VARCHAR(250),         -- replication member node
25
        policy text,         -- the policy (preferred, blocked, etc...TBD)
26
        CONSTRAINT systemMetadataReplicationPolicy_fk 
27
                FOREIGN KEY (guid) REFERENCES systemMetadata
28
);
29

    
30
CREATE TABLE systemMetadataReplicationStatus (
31
        guid text,        -- the globally unique string identifier of the object that the system metadata describes
32
        member_node VARCHAR(250),         -- replication member node
33
        status VARCHAR(250),         -- replication status
34
        date_verified TIMESTAMP,         -- the date replication was verified   
35
        CONSTRAINT systemMetadataReplicationStatus_fk 
36
                FOREIGN KEY (guid) REFERENCES systemMetadata
37
);
38

    
39
/*
40
 * Remove the old Identifier table (pre-2.0)
41
 */
42
DROP TABLE identifier;
43
/*
44
 * Create the new one (DataONE features)
45
 */
46
CREATE TABLE identifier (
47
   guid   text,          -- the globally unique string identifier
48
   docid  VARCHAR(250),  -- the local document id #
49
   rev    INT8,          -- the revision part of the local identifier
50
   CONSTRAINT identifier_pk PRIMARY KEY (guid)
51
);
52

    
53
/*
54
 * add the nodedatadate column to the tables that need it 
55
 */
56
ALTER TABLE xml_nodes ADD COLUMN nodedatadate TIMESTAMP;
57
ALTER TABLE xml_nodes_revisions ADD COLUMN nodedatadate TIMESTAMP;
58
ALTER TABLE xml_path_index ADD COLUMN nodedatadate TIMESTAMP;
59
CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate);
60

    
61
/*
62
 * Replication changes to support DataONE System Metadata replication
63
 */
64
ALTER TABLE xml_replication ADD COLUMN systemmetadatareplicate INT8;
65

    
66
/**
67
 * track the user-agent for the request
68
 */
69
ALTER TABLE access_log ADD COLUMN user_agent VARCHAR(512);
70

    
71
/*
72
 * Register the new schema
73
 */
74
INSERT INTO xml_catalog (entry_type, public_id, system_id)
75
  VALUES ('Schema', '@eml2_1_1namespace@', '/schema/eml-2.1.1/eml.xsd');  
76

    
77
/**
78
 * Generate GUIDs for docid.rev
79
 */
80
INSERT INTO identifier (docid, rev, guid) 
81
        SELECT docid, rev, docid || '.' || rev FROM xml_documents;
82
INSERT INTO identifier (docid, rev, guid)
83
        SELECT docid, revisionid, docid || '.' || revisionid FROM xml_revisions;
84

    
85
/**
86
 *  Add guid in xml_access table
87
 */
88
ALTER TABLE xml_access ADD COLUMN guid text;
89

    
90
/**
91
 * Upgrade xml_access records to use GUID from identifier table
92
 * NOTE: This duplicates existing access rules for every revision of a document
93
 */
94
INSERT INTO xml_access (
95
        guid, principal_name, permission, perm_type, perm_order, 
96
        docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid
97
)
98
        SELECT 
99
                id.guid, xa.principal_name, xa.permission, xa.perm_type, xa.perm_order, 
100
                xa.docid, xa.accessfileid, xa.begin_time, xa.end_time, xa.ticket_count, xa.subtreeid, xa.startnodeid, xa.endnodeid
101
        FROM identifier id, xml_access xa
102
        WHERE id.docid = xa.docid;
103

    
104
/**
105
 * Include inline data access rows -- they have a special guid: 'scope.docid.rev.index'
106
 */
107
INSERT INTO xml_access (
108
        guid, principal_name, permission, perm_type, perm_order, 
109
        docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid
110
)
111
        SELECT 
112
                docid, principal_name, permission, perm_type, perm_order, 
113
                docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid
114
        FROM xml_access
115
        WHERE docid like '%.%.%.%';
116

    
117
/**
118
 * Update the accessfileid to use their guid
119
 * NOTE: uses the last revision's guid as the new value for accessfileid
120
 */
121
UPDATE xml_access
122
SET accessfileid = id.guid
123
FROM identifier id, xml_access xa
124
WHERE xa.accessfileid = id.docid
125
AND xa.guid IS NOT null
126
AND id.rev = 
127
        (SELECT MAX(id2.rev) FROM identifier id2 WHERE id.docid = id2.docid);
128

    
129
/**
130
 * Remove old access rules
131
 */
132
DELETE FROM xml_access WHERE guid is null;
133

    
134
/**
135
 * clean up the xml_access table
136
 */
137
ALTER TABLE xml_access DROP COLUMN docid;
138

    
139
/*
140
 * update the database version
141
 */
142
UPDATE db_version SET status=0;
143

    
144
INSERT INTO db_version (version, status, date_created) 
145
  VALUES ('2.0.0', 1, CURRENT_DATE);
146