Project

General

Profile

« Previous | Next » 

Revision 7387

View differences:

src/upgrade-db-to-2.0.0-oracle.sql
37 37
		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE
38 38
);
39 39

  
40
DROP TABLE IF EXISTS identifier;
40
BEGIN
41
   EXECUTE IMMEDIATE 'DROP TABLE identifier';
42
EXCEPTION
43
   WHEN OTHERS THEN
44
      IF SQLCODE != -942 THEN
45
         RAISE;
46
      END IF;
47
END;
41 48

  
49
/*
50
 * Create the new one (DataONE features)
51
 */
42 52
CREATE TABLE identifier (
43 53
   guid   VARCHAR2(2000), -- the globally unique string identifier
44 54
   docid  VARCHAR2(250),  -- the local document id #
45 55
   rev    NUMBER(8)       -- the revision part of the local identifier
46 56
);
47 57

  
58
/*
59
 * add the nodedatadate column to the tables that need it 
60
 */
61
ALTER TABLE xml_nodes ADD (nodedatadate TIMESTAMP);
62
ALTER TABLE xml_nodes_revisions ADD (nodedatadate TIMESTAMP);
63
ALTER TABLE xml_path_index ADD (nodedatadate TIMESTAMP);
64
CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate);
65

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

  
53 71
/*
54
 * add the nodedatadate column to xml_nodes 
55
 * TODO: load the data into it (java?)
56
 */
57
ALTER TABLE xml_nodes ADD COLUMN nodedatadate TIMESTAMP;
58
ALTER TABLE xml_nodes_revisions ADD COLUMN nodedatadate TIMESTAMP;
59
ALTER TABLE xml_path_index ADD COLUMN nodedatadate TIMESTAMP;
60
CREATE INDEX xml_path_index_idx4 ON xml_path_index (nodedatadate);
61

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

  
68 77
/**
69 78
 * Generate GUIDs for docid.rev
......
72 81
	SELECT docid, rev, docid || '.' || rev FROM xml_documents
73 82
	UNION	
74 83
	SELECT docid, rev, docid || '.' || rev FROM xml_revisions;
75
/*
76
 *  Allow guid in xml_access table (for system metadata)
77
*/
78
ALTER TABLE xml_access ADD COLUMN guid VARCHAR2(2000);
84
--INSERT 0 156644
85

  
79 86
/**
87
 *  Add guid in xml_access table
88
 */
89
ALTER TABLE xml_access ADD (guid VARCHAR2(2000));
90
/**
80 91
 *  Expand accessfileid in xml_access table to hold guids
81 92
 */
82
ALTER TABLE xml_access ALTER COLUMN accessfileid TYPE VARCHAR2(2000);
93
ALTER TABLE xml_access MODIFY (accessfileid VARCHAR2(2000));
83 94

  
84

  
85 95
/**
86 96
 * Upgrade xml_access records to use GUID from identifier table
87 97
 * NOTE: This duplicates existing access rules for every revision of a document
......
108 118
		docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid
109 119
	FROM xml_access
110 120
	WHERE docid like '%.%.%.%';
121
--INSERT 0 18
111 122

  
123

  
112 124
/**
113
 * Update the accessfileid to use guid
114
 * NOTE: uses the last revision's guid as the new accessfileid
115
 */
125
 * Update the accessfileid to use their guid
126
 * NOTE: uses the last revision's guid as the new value for accessfileid
127
 * uses a temporary table
128
 **/
116 129
CREATE TABLE max_identifier (guid VARCHAR2(2000), docid VARCHAR2(250), rev NUMBER(8));
117 130

  
118 131
/** insert the max rev identifier for each document **/
......
120 133
SELECT docid, MAX(rev), docid || '.' || MAX(rev)
121 134
FROM identifier
122 135
GROUP BY docid;
136
--INSERT 0 57841
123 137

  
124 138
/** create some indexes to speed up the join **/
125 139
CREATE INDEX maxid_docid_index ON max_identifier (docid);
126
CREATE INDEX maxid_guid_index ON max_identifier (docid);
140
CREATE INDEX maxid_guid_index ON max_identifier (guid);
127 141
CREATE INDEX accessfileid_index on xml_access (accessfileid);
128 142
CREATE INDEX xml_access_guid_index on xml_access (guid);
129 143

  
130
/** only need to join on docid since there is a single row for the max docid **/
131 144
UPDATE xml_access xa
132 145
SET accessfileid = maxid.guid
133 146
FROM max_identifier maxid
134 147
WHERE xa.accessfileid = maxid.docid
135 148
AND xa.guid IS NOT null;
149
--UPDATE 310427
136 150

  
137 151
DROP INDEX maxid_docid_index;
138 152
DROP INDEX maxid_guid_index;
......
145 159
 * Remove old access rules
146 160
 */
147 161
DELETE FROM xml_access WHERE guid is null;
162
--DELETE 105432
148 163

  
149 164
/**
150 165
 * clean up the xml_access table
151 166
 */
152
ALTER TABLE xml_access DROP COLUMN docid;
167
ALTER TABLE xml_access DROP (docid);
153 168

  
169

  
154 170
/**
155 171
 * expand xml_path_index 'path' column to hold larger strings 
156 172
 */

Also available in: Unified diff