Project

General

Profile

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

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 DEFERRABLE
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 DEFERRABLE
37
);
38

    
39
/*
40
 * Remove the old Identifier table (pre-2.0)
41
 */
42
DROP TABLE IF EXISTS 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
 * track the user-agent for the request
63
 */
64
ALTER TABLE access_log ADD COLUMN user_agent VARCHAR(512);
65

    
66
/*
67
 * Register the new schema
68
 */
69
INSERT INTO xml_catalog (entry_type, public_id, system_id)
70
  VALUES ('Schema', '@eml2_1_1namespace@', '/schema/eml-2.1.1/eml.xsd');  
71

    
72
/**
73
 * Generate GUIDs for docid.rev
74
 */
75
INSERT INTO identifier (docid, rev, guid) 
76
        SELECT docid, rev, docid || '.' || rev FROM xml_documents
77
        UNION        
78
        SELECT docid, rev, docid || '.' || rev FROM xml_revisions;
79
--INSERT 0 156644
80

    
81
/**
82
 *  Add guid in xml_access table
83
 */
84
ALTER TABLE xml_access ADD COLUMN guid text;
85
/**
86
 *  Expand accessfileid in xml_access table to hold guids
87
 */
88
ALTER TABLE xml_access ALTER COLUMN accessfileid TYPE 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
--INSERT 0 311224
104

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

    
119

    
120
/**
121
 * Update the accessfileid to use their guid
122
 * NOTE: uses the last revision's guid as the new value for accessfileid
123
 * uses a temporary table
124
 **/
125

    
126
CREATE TABLE max_identifier (guid text, docid VARCHAR(250), rev INT8);
127

    
128
/** insert the max rev identifier for each document **/
129
INSERT INTO max_identifier (docid, rev, guid)
130
SELECT docid, MAX(rev), docid || '.' || MAX(rev)
131
FROM identifier
132
GROUP BY docid;
133
--INSERT 0 57841
134

    
135
/** create some indexes to speed up the join **/
136
CREATE INDEX maxid_docid_index ON max_identifier (docid);
137
CREATE INDEX maxid_guid_index ON max_identifier (guid);
138
CREATE INDEX accessfileid_index on xml_access (accessfileid);
139
CREATE INDEX xml_access_guid_index on xml_access (guid);
140

    
141
UPDATE xml_access xa
142
SET accessfileid = maxid.guid
143
FROM max_identifier maxid
144
WHERE xa.accessfileid = maxid.docid
145
AND xa.guid IS NOT null;
146
--UPDATE 310427
147

    
148
DROP INDEX maxid_docid_index;
149
DROP INDEX maxid_guid_index;
150
DROP INDEX accessfileid_index;
151
DROP INDEX xml_access_guid_index;
152

    
153
DROP TABLE max_identifier;
154

    
155
/**
156
 * Remove old access rules
157
 */
158
DELETE FROM xml_access WHERE guid is null;
159
--DELETE 105432
160

    
161
/**
162
 * clean up the xml_access table
163
 */
164
ALTER TABLE xml_access DROP COLUMN docid;
165

    
166

    
167
/**
168
 * expand xml_path_index 'path' column to hold larger strings 
169
 */
170
ALTER TABLE xml_path_index ALTER COLUMN path TYPE text;
171

    
172
/**
173
 * include 2.0.0beta4 DTD
174
 */
175
INSERT INTO xml_catalog (entry_type, public_id, system_id)
176
  VALUES ('DTD', '-//ecoinformatics.org//eml-access-@eml-beta4-version@//EN',
177
         '/dtd/eml-access-@eml-beta4-version@.dtd');
178
INSERT INTO xml_catalog (entry_type, public_id, system_id)
179
  VALUES ('DTD', '-//ecoinformatics.org//eml-attribute-@eml-beta4-version@//EN',
180
          '/dtd/eml-attribute-@eml-beta4-version@.dtd');
181
INSERT INTO xml_catalog (entry_type, public_id, system_id)
182
  VALUES ('DTD', '-//ecoinformatics.org//eml-constraint-@eml-beta4-version@//EN',
183
          '/dtd/eml-constraint-@eml-beta4-version@.dtd');
184
INSERT INTO xml_catalog (entry_type, public_id, system_id)
185
  VALUES ('DTD', '-//ecoinformatics.org//eml-coverage-@eml-beta4-version@//EN',
186
          '/dtd/eml-coverage-@eml-beta4-version@.dtd');
187
INSERT INTO xml_catalog (entry_type, public_id, system_id)
188
  VALUES ('DTD', '-//ecoinformatics.org//eml-dataset-@eml-beta4-version@//EN',
189
          '/dtd/eml-dataset-@eml-beta4-version@.dtd');
190
INSERT INTO xml_catalog (entry_type, public_id, system_id)
191
  VALUES ('DTD', '-//ecoinformatics.org//eml-entity-@eml-beta4-version@//EN',
192
          '/dtd/eml-entity-@eml-beta4-version@.dtd');
193
INSERT INTO xml_catalog (entry_type, public_id, system_id)
194
  VALUES ('DTD', '-//ecoinformatics.org//eml-literature-@eml-beta4-version@//EN',
195
          '/dtd/eml-literature-@eml-beta4-version@.dtd');
196
INSERT INTO xml_catalog (entry_type, public_id, system_id)
197
  VALUES ('DTD', '-//ecoinformatics.org//eml-physical-@eml-beta4-version@//EN',
198
          '/dtd/eml-physical-@eml-beta4-version@.dtd');
199
INSERT INTO xml_catalog (entry_type, public_id, system_id)
200
  VALUES ('DTD', '-//ecoinformatics.org//eml-project-@eml-beta4-version@//EN',
201
          '/dtd/eml-project-@eml-beta4-version@.dtd');
202
INSERT INTO xml_catalog (entry_type, public_id, system_id)
203
  VALUES ('DTD', '-//ecoinformatics.org//eml-protocol-@eml-beta4-version@//EN',
204
          '/dtd/eml-protocol-@eml-beta4-version@.dtd');
205
INSERT INTO xml_catalog (entry_type, public_id, system_id)
206
  VALUES ('DTD', '-//ecoinformatics.org//eml-software-@eml-beta4-version@//EN',
207
          '/dtd/eml-software-@eml-beta4-version@.dtd');
208

    
209
/*
210
 * update the database version
211
 */
212
UPDATE db_version SET status=0;
213

    
214
INSERT INTO db_version (version, status, date_created) 
215
  VALUES ('2.0.0', 1, CURRENT_DATE);
216