Project

General

Profile

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 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
INSERT INTO identifier (docid, rev, guid)
78
	SELECT docid, revisionid, docid || '.' || revisionid FROM xml_revisions;
79

    
80
/**
81
 *  Add guid in xml_access table
82
 */
83
ALTER TABLE xml_access ADD COLUMN guid text;
84
/**
85
 *  Expand accessfileid in xml_access table to hold guids
86
 */
87
ALTER TABLE xml_access ALTER COLUMN accessfileid TYPE text;
88

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

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

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

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

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

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

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

    
(49-49/53)