Project

General

Profile

1 5371 berkley
CREATE TABLE systemMetadata (
2 6136 leinfelder
	guid   text,          -- the globally unique string identifier of the object that the system metadata describes
3 6561 leinfelder
	serial_version VARCHAR(256), --the serial version of the object
4 6136 leinfelder
	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 6766 leinfelder
	archived boolean,	 -- specifies whether this an archived object
15 6136 leinfelder
	replication_allowed boolean,	 -- replication allowed
16
	number_replicas INT8, 	-- the number of replicas allowed
17 6375 leinfelder
	obsoletes   text,       -- the identifier that this record obsoletes
18
	obsoleted_by   text,     -- the identifier of the record that replaces this record
19 6136 leinfelder
	CONSTRAINT systemMetadata_pk PRIMARY KEY (guid)
20 5371 berkley
);
21
22 6136 leinfelder
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 6933 jones
		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE
28 6136 leinfelder
);
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 6933 jones
		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE
37 6136 leinfelder
);
38
39 6583 leinfelder
/*
40
 * Remove the old Identifier table (pre-2.0)
41
 */
42 6945 leinfelder
DROP TABLE IF EXISTS identifier;
43 6583 leinfelder
/*
44
 * Create the new one (DataONE features)
45
 */
46 5276 jones
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 6012 leinfelder
 * 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 6542 leinfelder
/**
62
 * track the user-agent for the request
63
 */
64 6730 tao
ALTER TABLE access_log ADD COLUMN user_agent VARCHAR(512);
65 6542 leinfelder
66 6136 leinfelder
/*
67 6658 leinfelder
 * Register the new schema
68 5400 jones
 */
69
INSERT INTO xml_catalog (entry_type, public_id, system_id)
70 5709 leinfelder
  VALUES ('Schema', '@eml2_1_1namespace@', '/schema/eml-2.1.1/eml.xsd');
71 6744 leinfelder
72
/**
73
 * Generate GUIDs for docid.rev
74
 */
75
INSERT INTO identifier (docid, rev, guid)
76 6952 leinfelder
	SELECT docid, rev, docid || '.' || rev FROM xml_documents
77
	UNION
78
	SELECT docid, rev, docid || '.' || rev FROM xml_revisions;
79
--INSERT 0 156644
80 6946 leinfelder
81 6744 leinfelder
/**
82 6760 leinfelder
 *  Add guid in xml_access table
83 6744 leinfelder
 */
84
ALTER TABLE xml_access ADD COLUMN guid text;
85 6923 leinfelder
/**
86
 *  Expand accessfileid in xml_access table to hold guids
87
 */
88
ALTER TABLE xml_access ALTER COLUMN accessfileid TYPE text;
89 6744 leinfelder
90
/**
91
 * Upgrade xml_access records to use GUID from identifier table
92 6760 leinfelder
 * NOTE: This duplicates existing access rules for every revision of a document
93 6744 leinfelder
 */
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 6952 leinfelder
--INSERT 0 311224
104 6744 leinfelder
105
/**
106 6760 leinfelder
 * Include inline data access rows -- they have a special guid: 'scope.docid.rev.index'
107 6744 leinfelder
 */
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 6952 leinfelder
--INSERT 0 18
118 6744 leinfelder
119 6947 leinfelder
120 6744 leinfelder
/**
121 6762 leinfelder
 * Update the accessfileid to use their guid
122
 * NOTE: uses the last revision's guid as the new value for accessfileid
123 6947 leinfelder
 * 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 6949 leinfelder
GROUP BY docid;
133 6952 leinfelder
--INSERT 0 57841
134 6947 leinfelder
135 6952 leinfelder
/** 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 (docid);
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 6947 leinfelder
SET accessfileid = maxid.guid
143 6952 leinfelder
FROM max_identifier maxid
144 6947 leinfelder
WHERE xa.accessfileid = maxid.docid
145 6949 leinfelder
AND xa.guid IS NOT null;
146 6952 leinfelder
--UPDATE 310427
147 6744 leinfelder
148 6952 leinfelder
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 6947 leinfelder
DROP TABLE max_identifier;
154
155 6744 leinfelder
/**
156
 * Remove old access rules
157
 */
158
DELETE FROM xml_access WHERE guid is null;
159 6952 leinfelder
--DELETE 105432
160 6744 leinfelder
161
/**
162
 * clean up the xml_access table
163
 */
164
ALTER TABLE xml_access DROP COLUMN docid;
165
166 5400 jones
/*
167 5276 jones
 * update the database version
168
 */
169
UPDATE db_version SET status=0;
170
171
INSERT INTO db_version (version, status, date_created)
172 6550 leinfelder
  VALUES ('2.0.0', 1, CURRENT_DATE);