Project

General

Profile

« Previous | Next » 

Revision 6761

move latest postgres access upgrade statements to oracle script
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5560

View differences:

src/upgrade-db-to-2.0.0-oracle.sql
48 48
 * Replication changes to support DataONE System Metadata replication
49 49
 */
50 50
ALTER TABLE xml_replication ADD COLUMN systemmetadatareplicate NUMBER(1);
51
/*
52
 *  Allow guid in xml_access table (for system metadata)
53
*/
54
ALTER TABLE xml_access ADD COLUMN guid VARCHAR2(2000);
55 51

  
56 52
/**
57 53
 * track the user-agent for the request
......
73 69
INSERT INTO xml_catalog (entry_type, public_id, system_id)
74 70
  VALUES ('Schema', '@eml2_1_1namespace@', '/schema/eml-2.1.1/eml.xsd');
75 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

  
76 80
/*
81
 *  Allow guid in xml_access table (for system metadata)
82
*/
83
ALTER TABLE xml_access ADD COLUMN guid VARCHAR2(2000);
84

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

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

  
112
/**
113
 * Update the accessfileid to use guid
114
 * NOTE: uses the last revision's guid as the new accessfileid
115
 */
116
UPDATE xml_access
117
SET accessfileid = id.guid
118
FROM identifier id, xml_access xa
119
WHERE xa.accessfileid = id.docid
120
AND id.rev = 
121
	(SELECT MAX(id2.rev) FROM identifier id2 WHERE id.docid = id2.docid)
122

  
123
/**
124
 * Remove old access rules
125
 */
126
DELETE FROM xml_access WHERE guid is null;
127

  
128
/**
129
 * clean up the xml_access table
130
 */
131
ALTER TABLE xml_access DROP COLUMN docid;
132

  
133

  
134
/*
77 135
 * update the database version
78 136
 */
79 137
UPDATE db_version SET status=0;

Also available in: Unified diff