1 |
4080
|
daigle
|
/**
|
2 |
|
|
* '$RCSfile$'
|
3 |
|
|
* Purpose: A Class that implements database configuration methods
|
4 |
|
|
* Copyright: 2008 Regents of the University of California and the
|
5 |
|
|
* National Center for Ecological Analysis and Synthesis
|
6 |
|
|
* Authors: Michael Daigle
|
7 |
|
|
*
|
8 |
|
|
* '$Author$'
|
9 |
|
|
* '$Date$'
|
10 |
|
|
* '$Revision$'
|
11 |
|
|
*
|
12 |
|
|
* This program is free software; you can redistribute it and/or modify
|
13 |
|
|
* it under the terms of the GNU General Public License as published by
|
14 |
|
|
* the Free Software Foundation; either version 2 of the License, or
|
15 |
|
|
* (at your option) any later version.
|
16 |
|
|
*
|
17 |
|
|
* This program is distributed in the hope that it will be useful,
|
18 |
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
19 |
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
20 |
|
|
* GNU General Public License for more details.
|
21 |
|
|
*
|
22 |
|
|
* You should have received a copy of the GNU General Public License
|
23 |
|
|
* along with this program; if not, write to the Free Software
|
24 |
|
|
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
|
25 |
|
|
*/
|
26 |
|
|
|
27 |
|
|
package edu.ucsb.nceas.metacat.admin;
|
28 |
|
|
|
29 |
|
|
import java.sql.Connection;
|
30 |
|
|
import java.sql.PreparedStatement;
|
31 |
|
|
import java.sql.SQLException;
|
32 |
|
|
import java.sql.ResultSet;
|
33 |
|
|
import java.sql.Statement;
|
34 |
|
|
import java.sql.Timestamp;
|
35 |
|
|
|
36 |
|
|
import java.io.BufferedReader;
|
37 |
|
|
import java.io.FileInputStream;
|
38 |
|
|
import java.io.IOException;
|
39 |
|
|
import java.io.InputStreamReader;
|
40 |
|
|
|
41 |
|
|
import java.util.Date;
|
42 |
4105
|
daigle
|
import java.util.HashMap;
|
43 |
4080
|
daigle
|
import java.util.HashSet;
|
44 |
4105
|
daigle
|
import java.util.Map;
|
45 |
4080
|
daigle
|
import java.util.TreeSet;
|
46 |
|
|
import java.util.Vector;
|
47 |
|
|
|
48 |
|
|
import javax.servlet.http.HttpServletRequest;
|
49 |
|
|
import javax.servlet.http.HttpServletResponse;
|
50 |
|
|
import javax.servlet.http.HttpSession;
|
51 |
|
|
|
52 |
5027
|
daigle
|
import edu.ucsb.nceas.metacat.MetacatVersion;
|
53 |
6012
|
leinfelder
|
import edu.ucsb.nceas.metacat.admin.upgrade.UpgradeUtilityInterface;
|
54 |
5015
|
daigle
|
import edu.ucsb.nceas.metacat.database.DBConnection;
|
55 |
|
|
import edu.ucsb.nceas.metacat.database.DBConnectionPool;
|
56 |
|
|
import edu.ucsb.nceas.metacat.database.DBVersion;
|
57 |
5030
|
daigle
|
import edu.ucsb.nceas.metacat.properties.PropertyService;
|
58 |
5076
|
daigle
|
import edu.ucsb.nceas.metacat.shared.MetacatUtilException;
|
59 |
4080
|
daigle
|
import edu.ucsb.nceas.metacat.util.DatabaseUtil;
|
60 |
|
|
import edu.ucsb.nceas.metacat.util.RequestUtil;
|
61 |
|
|
import edu.ucsb.nceas.metacat.util.SystemUtil;
|
62 |
|
|
|
63 |
|
|
import edu.ucsb.nceas.utilities.DBUtil;
|
64 |
|
|
import edu.ucsb.nceas.utilities.FileUtil;
|
65 |
|
|
import edu.ucsb.nceas.utilities.GeneralPropertyException;
|
66 |
|
|
import edu.ucsb.nceas.utilities.PropertyNotFoundException;
|
67 |
|
|
|
68 |
|
|
import org.apache.log4j.Logger;
|
69 |
|
|
|
70 |
|
|
/**
|
71 |
|
|
* Control the display of the database configuration page and the processing
|
72 |
|
|
* of the configuration values.
|
73 |
|
|
*/
|
74 |
5027
|
daigle
|
public class DBAdmin extends MetacatAdmin {
|
75 |
4080
|
daigle
|
// db statuses used by discovery code
|
76 |
|
|
public static final int DB_DOES_NOT_EXIST = 0;
|
77 |
|
|
public static final int TABLES_DO_NOT_EXIST = 1;
|
78 |
|
|
public static final int TABLES_EXIST = 2;
|
79 |
|
|
|
80 |
|
|
// db version statuses. This allows us to keep version history
|
81 |
|
|
// in the db. Only the latest version record should be active.
|
82 |
|
|
public static final int VERSION_INACTIVE = 0;
|
83 |
|
|
public static final int VERSION_ACTIVE = 1;
|
84 |
|
|
|
85 |
|
|
private TreeSet<DBVersion> versionSet = null;
|
86 |
|
|
|
87 |
|
|
private static DBAdmin dbAdmin = null;
|
88 |
|
|
private Logger logMetacat = Logger.getLogger(DBAdmin.class);
|
89 |
|
|
private HashSet<String> sqlCommandSet = new HashSet<String>();
|
90 |
4105
|
daigle
|
private Map<String, String> scriptSuffixMap = new HashMap<String, String>();
|
91 |
4080
|
daigle
|
private static DBVersion databaseVersion = null;
|
92 |
|
|
|
93 |
|
|
/**
|
94 |
|
|
* private constructor since this is a singleton
|
95 |
|
|
*/
|
96 |
|
|
private DBAdmin() throws AdminException {
|
97 |
|
|
sqlCommandSet.add("INSERT");
|
98 |
|
|
sqlCommandSet.add("UPDATE");
|
99 |
|
|
sqlCommandSet.add("DELETE");
|
100 |
|
|
sqlCommandSet.add("ALTER");
|
101 |
|
|
sqlCommandSet.add("CREATE");
|
102 |
|
|
sqlCommandSet.add("DROP");
|
103 |
|
|
sqlCommandSet.add("BEGIN");
|
104 |
|
|
sqlCommandSet.add("COMMIT");
|
105 |
|
|
|
106 |
|
|
// gets all the upgrade version objects
|
107 |
|
|
try {
|
108 |
|
|
versionSet = DatabaseUtil.getUpgradeVersions();
|
109 |
4105
|
daigle
|
scriptSuffixMap = DatabaseUtil.getScriptSuffixes();
|
110 |
4080
|
daigle
|
} catch (PropertyNotFoundException pnfe) {
|
111 |
5030
|
daigle
|
throw new AdminException("DBAdmin() - Could not retrieve database upgrade "
|
112 |
4080
|
daigle
|
+ "versions during instantiation" + pnfe.getMessage());
|
113 |
|
|
} catch (NumberFormatException nfe) {
|
114 |
5030
|
daigle
|
throw new AdminException("DBAdmin() - Bad version format numbering: "
|
115 |
4080
|
daigle
|
+ nfe.getMessage());
|
116 |
|
|
}
|
117 |
|
|
}
|
118 |
|
|
|
119 |
|
|
/**
|
120 |
|
|
* Get the single instance of DBAdmin.
|
121 |
|
|
*
|
122 |
|
|
* @return the single instance of DBAdmin
|
123 |
|
|
*/
|
124 |
|
|
public static DBAdmin getInstance() throws AdminException {
|
125 |
|
|
if (dbAdmin == null) {
|
126 |
|
|
dbAdmin = new DBAdmin();
|
127 |
|
|
}
|
128 |
|
|
return dbAdmin;
|
129 |
|
|
}
|
130 |
|
|
|
131 |
|
|
/**
|
132 |
|
|
* Handle configuration of the database the first time that Metacat starts
|
133 |
|
|
* or when it is explicitly called. Collect necessary update information
|
134 |
|
|
* from the administrator.
|
135 |
|
|
*
|
136 |
|
|
* @param request
|
137 |
|
|
* the http request information
|
138 |
|
|
* @param response
|
139 |
|
|
* the http response to be sent back to the client
|
140 |
|
|
*/
|
141 |
|
|
public void configureDatabase(HttpServletRequest request,
|
142 |
|
|
HttpServletResponse response) throws AdminException {
|
143 |
|
|
|
144 |
|
|
String processForm = request.getParameter("processForm");
|
145 |
|
|
String formErrors = (String) request.getAttribute("formErrors");
|
146 |
|
|
HttpSession session = request.getSession();
|
147 |
|
|
|
148 |
|
|
if (processForm == null || !processForm.equals("true") || formErrors != null) {
|
149 |
|
|
// The servlet configuration parameters have not been set, or there
|
150 |
|
|
// were form errors on the last attempt to configure, so redirect to
|
151 |
|
|
// the web form for configuring metacat
|
152 |
|
|
|
153 |
|
|
try {
|
154 |
|
|
// get the current metacat version and the database version. If
|
155 |
|
|
// the database version is older that the metacat version, run
|
156 |
|
|
// the appropriate scripts to get them synchronized.
|
157 |
|
|
|
158 |
|
|
databaseVersion = discoverDBVersion();
|
159 |
5027
|
daigle
|
MetacatVersion metacatVersion = SystemUtil.getMetacatVersion();
|
160 |
4080
|
daigle
|
|
161 |
5027
|
daigle
|
session.setAttribute("metacatVersion", MetacatVersion.getVersionID());
|
162 |
4080
|
daigle
|
|
163 |
|
|
// if the db version is already the same as the metacat
|
164 |
|
|
// version, update metacat.properties
|
165 |
|
|
if (databaseVersion.compareTo(metacatVersion) == 0) {
|
166 |
|
|
PropertyService.setProperty("configutil.databaseConfigured",
|
167 |
|
|
PropertyService.CONFIGURED);
|
168 |
|
|
}
|
169 |
|
|
|
170 |
5027
|
daigle
|
MetacatVersion metaCatVersion = SystemUtil.getMetacatVersion();
|
171 |
4080
|
daigle
|
request.setAttribute("metacatVersion", metaCatVersion);
|
172 |
|
|
DBVersion dbVersionString = getDBVersion();
|
173 |
|
|
request.setAttribute("databaseVersion", dbVersionString);
|
174 |
|
|
Vector<String> updateScriptList = getUpdateScripts();
|
175 |
|
|
request.setAttribute("updateScriptList", updateScriptList);
|
176 |
4406
|
daigle
|
String supportEmail = PropertyService.getProperty("email.recipient");
|
177 |
|
|
request.setAttribute("supportEmail", supportEmail);
|
178 |
4080
|
daigle
|
|
179 |
|
|
// Forward the request to the JSP page
|
180 |
|
|
RequestUtil.clearRequestMessages(request);
|
181 |
|
|
RequestUtil.forwardRequest(request, response,
|
182 |
5027
|
daigle
|
"/admin/database-configuration.jsp", null);
|
183 |
4084
|
daigle
|
} catch (GeneralPropertyException gpe) {
|
184 |
5030
|
daigle
|
throw new AdminException("DBAdmin.configureDatabase - Problem getting or " +
|
185 |
|
|
"setting property while initializing system properties page: " + gpe.getMessage());
|
186 |
5076
|
daigle
|
} catch (MetacatUtilException mue) {
|
187 |
|
|
throw new AdminException("DBAdmin.configureDatabase - utility problem while initializing "
|
188 |
|
|
+ "system properties page:" + mue.getMessage());
|
189 |
|
|
}
|
190 |
4080
|
daigle
|
} else {
|
191 |
|
|
// The configuration form is being submitted and needs to be
|
192 |
|
|
// processed, setting the properties in the configuration file
|
193 |
|
|
// then restart metacat
|
194 |
|
|
|
195 |
|
|
// The configuration form is being submitted and needs to be
|
196 |
|
|
// processed.
|
197 |
|
|
Vector<String> validationErrors = new Vector<String>();
|
198 |
|
|
Vector<String> processingSuccess = new Vector<String>();
|
199 |
|
|
|
200 |
|
|
try {
|
201 |
|
|
// Validate that the options provided are legitimate. Note that
|
202 |
|
|
// we've allowed them to persist their entries. As of this point
|
203 |
|
|
// there is no other easy way to go back to the configure form
|
204 |
|
|
// and
|
205 |
|
|
// preserve their entries.
|
206 |
|
|
validationErrors.addAll(validateOptions(request));
|
207 |
|
|
|
208 |
|
|
upgradeDatabase();
|
209 |
|
|
|
210 |
|
|
// Now that the options have been set, change the
|
211 |
|
|
// 'databaseConfigured' option to 'true' so that normal metacat
|
212 |
|
|
// requests will go through
|
213 |
|
|
PropertyService.setProperty("configutil.databaseConfigured",
|
214 |
|
|
PropertyService.CONFIGURED);
|
215 |
|
|
|
216 |
|
|
// Reload the main metacat configuration page
|
217 |
|
|
processingSuccess.add("Database successfully upgraded");
|
218 |
|
|
RequestUtil.clearRequestMessages(request);
|
219 |
|
|
RequestUtil.setRequestSuccess(request, processingSuccess);
|
220 |
|
|
RequestUtil.forwardRequest(request, response,
|
221 |
5027
|
daigle
|
"/admin?configureType=configure&processForm=false", null);
|
222 |
4080
|
daigle
|
// Write out the configurable properties to a backup file
|
223 |
|
|
// outside the install directory.
|
224 |
|
|
|
225 |
5028
|
daigle
|
PropertyService.persistMainBackupProperties();
|
226 |
4080
|
daigle
|
} catch (GeneralPropertyException gpe) {
|
227 |
5030
|
daigle
|
throw new AdminException("DBAdmin.configureDatabase - Problem getting or setting " +
|
228 |
|
|
"property while upgrading database: " + gpe.getMessage());
|
229 |
5076
|
daigle
|
} catch (MetacatUtilException mue) {
|
230 |
|
|
throw new AdminException("DBAdmin.configureDatabase - utility problem while upgrading database: "
|
231 |
|
|
+ mue.getMessage());
|
232 |
|
|
}
|
233 |
4080
|
daigle
|
}
|
234 |
|
|
}
|
235 |
|
|
|
236 |
|
|
|
237 |
|
|
/**
|
238 |
|
|
* Performs a status check on the database.
|
239 |
|
|
*
|
240 |
|
|
* @returns integer representing the status of the database. These can be:
|
241 |
|
|
* -- DB_DOES_NOT_EXIST = 0;
|
242 |
|
|
* -- TABLES_DO_NOT_EXIST = 1;
|
243 |
|
|
* -- TABLES_EXIST = 2;
|
244 |
|
|
*/
|
245 |
|
|
public int getDBStatus() throws SQLException, PropertyNotFoundException {
|
246 |
|
|
Connection connection = DBUtil.getConnection(PropertyService
|
247 |
|
|
.getProperty("database.connectionURI"), PropertyService
|
248 |
|
|
.getProperty("database.user"), PropertyService
|
249 |
|
|
.getProperty("database.password"));
|
250 |
|
|
|
251 |
|
|
if (DBUtil.tableExists(connection, "xml_documents")) {
|
252 |
|
|
return TABLES_EXIST;
|
253 |
|
|
}
|
254 |
|
|
|
255 |
|
|
return TABLES_DO_NOT_EXIST;
|
256 |
|
|
}
|
257 |
|
|
|
258 |
|
|
/**
|
259 |
|
|
* Get the version of the database as a string
|
260 |
|
|
*
|
261 |
|
|
* @returns string representing the version of the database.
|
262 |
|
|
*/
|
263 |
|
|
public DBVersion getDBVersion() throws AdminException {
|
264 |
|
|
|
265 |
|
|
// don't even try to search for a database version until system
|
266 |
|
|
// properties have been configured
|
267 |
|
|
try {
|
268 |
4561
|
daigle
|
if (!PropertyService.arePropertiesConfigured()) {
|
269 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getDBVersion - An attempt was made to get " +
|
270 |
|
|
"the database version before system properties were configured");
|
271 |
4561
|
daigle
|
}
|
272 |
5030
|
daigle
|
} catch (GeneralPropertyException gpe) {
|
273 |
|
|
throw new AdminException("DBAdmin.getDBVersion - Could not determine the database version: "
|
274 |
|
|
+ gpe.getMessage());
|
275 |
4080
|
daigle
|
}
|
276 |
|
|
if (databaseVersion == null) {
|
277 |
|
|
databaseVersion = discoverDBVersion();
|
278 |
|
|
}
|
279 |
|
|
|
280 |
|
|
if (databaseVersion == null) {
|
281 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getDBVersion - Could not find database version");
|
282 |
4080
|
daigle
|
}
|
283 |
|
|
|
284 |
|
|
return databaseVersion;
|
285 |
|
|
}
|
286 |
|
|
|
287 |
|
|
/**
|
288 |
|
|
* Try to discover the database version, first by calling
|
289 |
|
|
* getRegisteredDBVersion() to see if the database version is in a table in
|
290 |
|
|
* the database. If not, getUnRegisteredDBVersion() is called to see if we
|
291 |
|
|
* can devine the version by looking for unique changes made by scripts for
|
292 |
|
|
* each version update.
|
293 |
|
|
*
|
294 |
|
|
* @returns string representing the version of the database, null if none
|
295 |
|
|
* could be found.
|
296 |
|
|
*/
|
297 |
|
|
private DBVersion discoverDBVersion() throws AdminException {
|
298 |
|
|
try {
|
299 |
|
|
int dbStatus = getDBStatus();
|
300 |
|
|
if (dbStatus == DB_DOES_NOT_EXIST) {
|
301 |
5030
|
daigle
|
throw new AdminException("DBAdmin.discoverDBVersion - Database does not exist " +
|
302 |
|
|
"for connection" + PropertyService.getProperty("database.connectionURI"));
|
303 |
4080
|
daigle
|
} else if (dbStatus == TABLES_DO_NOT_EXIST) {
|
304 |
|
|
databaseVersion = new DBVersion("0.0.0");
|
305 |
|
|
return databaseVersion;
|
306 |
|
|
}
|
307 |
|
|
|
308 |
|
|
databaseVersion = getRegisteredDBVersion();
|
309 |
|
|
if (databaseVersion != null) {
|
310 |
|
|
return databaseVersion;
|
311 |
|
|
}
|
312 |
|
|
|
313 |
|
|
databaseVersion = getUnRegisteredDBVersion();
|
314 |
|
|
|
315 |
|
|
} catch (SQLException sqle) {
|
316 |
5030
|
daigle
|
String errorMessage = "DBAdmin.discoverDBVersion - SQL error during database version discovery: "
|
317 |
4080
|
daigle
|
+ sqle.getMessage();
|
318 |
|
|
logMetacat.error(errorMessage);
|
319 |
|
|
throw new AdminException(errorMessage);
|
320 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
321 |
5030
|
daigle
|
String errorMessage = "DBAdmin.discoverDBVersion - Property not found during database " +
|
322 |
|
|
"version discovery: " + pnfe.getMessage();
|
323 |
4080
|
daigle
|
logMetacat.error(errorMessage);
|
324 |
|
|
throw new AdminException(errorMessage);
|
325 |
|
|
} catch (NumberFormatException nfe) {
|
326 |
5030
|
daigle
|
throw new AdminException("DBAdmin.discoverDBVersion - Bad version format numbering: "
|
327 |
4080
|
daigle
|
+ nfe.getMessage());
|
328 |
|
|
}
|
329 |
|
|
|
330 |
|
|
if (databaseVersion == null) {
|
331 |
5030
|
daigle
|
throw new AdminException("DBAdmin.discoverDBVersion - Database version discovery returned null");
|
332 |
4080
|
daigle
|
}
|
333 |
|
|
return databaseVersion;
|
334 |
|
|
}
|
335 |
|
|
|
336 |
|
|
/**
|
337 |
|
|
* Gets the version of the database from the db_version table. Usually this
|
338 |
|
|
* is the same as the version of the product, however the db version could
|
339 |
|
|
* be more granular if we applied a maintenance patch for instance.
|
340 |
|
|
*
|
341 |
|
|
* @returns string representing the version of the database.
|
342 |
|
|
*/
|
343 |
|
|
private DBVersion getRegisteredDBVersion() throws AdminException, SQLException {
|
344 |
|
|
String dbVersionString = null;
|
345 |
|
|
PreparedStatement pstmt = null;
|
346 |
|
|
|
347 |
|
|
try {
|
348 |
|
|
// check out DBConnection
|
349 |
|
|
Connection connection =
|
350 |
|
|
DBUtil.getConnection(
|
351 |
|
|
PropertyService.getProperty("database.connectionURI"),
|
352 |
|
|
PropertyService.getProperty("database.user"),
|
353 |
|
|
PropertyService.getProperty("database.password"));
|
354 |
|
|
|
355 |
|
|
if (!DBUtil.tableExists(connection, "db_version")) {
|
356 |
|
|
return null;
|
357 |
|
|
}
|
358 |
|
|
|
359 |
|
|
pstmt =
|
360 |
|
|
connection.prepareStatement("SELECT version FROM db_version WHERE status = ?");
|
361 |
|
|
|
362 |
|
|
// Bind the values to the query
|
363 |
|
|
pstmt.setInt(1, VERSION_ACTIVE);
|
364 |
|
|
pstmt.execute();
|
365 |
|
|
ResultSet rs = pstmt.getResultSet();
|
366 |
|
|
boolean hasRows = rs.next();
|
367 |
|
|
if (hasRows) {
|
368 |
|
|
dbVersionString = rs.getString(1);
|
369 |
|
|
}
|
370 |
|
|
|
371 |
|
|
if (dbVersionString == null) {
|
372 |
|
|
return null;
|
373 |
|
|
}
|
374 |
|
|
|
375 |
|
|
return new DBVersion(dbVersionString);
|
376 |
|
|
|
377 |
|
|
} catch (SQLException sqle) {
|
378 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getRegisteredDBVersion - Could not run SQL to get registered db version: "
|
379 |
4080
|
daigle
|
+ sqle.getMessage());
|
380 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
381 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getRegisteredDBVersion - Could not get property for registered db version: "
|
382 |
4080
|
daigle
|
+ pnfe.getMessage());
|
383 |
|
|
} catch (NumberFormatException nfe) {
|
384 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getRegisteredDBVersion - Bad version format numbering: "
|
385 |
4080
|
daigle
|
+ nfe.getMessage());
|
386 |
|
|
} finally {
|
387 |
|
|
if (pstmt != null) {
|
388 |
|
|
pstmt.close();
|
389 |
|
|
}
|
390 |
|
|
}
|
391 |
|
|
}
|
392 |
|
|
|
393 |
|
|
/**
|
394 |
|
|
* Finds the version of the database for a database that does not have a
|
395 |
|
|
* dbVersion table yet. Work backwards with various clues found in update
|
396 |
|
|
* scripts to find the version.
|
397 |
|
|
*
|
398 |
|
|
* @returns string representing the version of the database.
|
399 |
|
|
*/
|
400 |
|
|
public DBVersion getUnRegisteredDBVersion() throws AdminException, SQLException {
|
401 |
|
|
Connection connection = null;
|
402 |
|
|
try {
|
403 |
|
|
connection = DBUtil.getConnection(PropertyService
|
404 |
|
|
.getProperty("database.connectionURI"), PropertyService
|
405 |
|
|
.getProperty("database.user"), PropertyService
|
406 |
|
|
.getProperty("database.password"));
|
407 |
|
|
|
408 |
|
|
String dbVersionString = null;
|
409 |
|
|
|
410 |
4975
|
daigle
|
if (is1_9_1(connection)) {
|
411 |
|
|
dbVersionString = "1.9.1";
|
412 |
|
|
} else if (is1_9_0(connection)) {
|
413 |
4080
|
daigle
|
dbVersionString = "1.9.0";
|
414 |
|
|
} else if (is1_8_0(connection)) {
|
415 |
|
|
dbVersionString = "1.8.0";
|
416 |
|
|
} else if (is1_7_0(connection)) {
|
417 |
|
|
dbVersionString = "1.7.0";
|
418 |
|
|
} else if (is1_6_0(connection)) {
|
419 |
|
|
dbVersionString = "1.6.0";
|
420 |
|
|
} else if (is1_5_0(connection)) {
|
421 |
|
|
dbVersionString = "1.5.0";
|
422 |
|
|
} else if (is1_4_0(connection)) {
|
423 |
|
|
dbVersionString = "1.4.0";
|
424 |
|
|
} else if (is1_3_0(connection)) {
|
425 |
|
|
dbVersionString = "1.3.0";
|
426 |
|
|
} else if (is1_2_0(connection)) {
|
427 |
|
|
dbVersionString = "1.2.0";
|
428 |
|
|
}
|
429 |
|
|
|
430 |
|
|
if (dbVersionString == null) {
|
431 |
|
|
return null;
|
432 |
|
|
} else {
|
433 |
|
|
return new DBVersion(dbVersionString);
|
434 |
|
|
}
|
435 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
436 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getUnRegisteredDBVersion - Could not get " +
|
437 |
|
|
"property for unregistered db version: " + pnfe.getMessage());
|
438 |
4080
|
daigle
|
} catch (NumberFormatException nfe) {
|
439 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getUnRegisteredDBVersion - Bad version format numbering: "
|
440 |
4080
|
daigle
|
+ nfe.getMessage());
|
441 |
|
|
}
|
442 |
|
|
}
|
443 |
|
|
|
444 |
|
|
/**
|
445 |
|
|
* Updates the version of the database. Typically this is done in the update
|
446 |
|
|
* scripts that get run when we upgrade the application. This method can be
|
447 |
|
|
* used if you are automating a patch on the database internally.
|
448 |
|
|
*
|
449 |
|
|
* @returns string representing the version of the database.
|
450 |
|
|
*/
|
451 |
|
|
public void updateDBVersion() throws SQLException {
|
452 |
|
|
DBConnection conn = null;
|
453 |
|
|
PreparedStatement pstmt = null;
|
454 |
|
|
int serialNumber = -1;
|
455 |
|
|
try {
|
456 |
|
|
|
457 |
|
|
// check out DBConnection
|
458 |
5030
|
daigle
|
conn = DBConnectionPool.getDBConnection("DBAdmin.updateDBVersion()");
|
459 |
4080
|
daigle
|
serialNumber = conn.getCheckOutSerialNumber();
|
460 |
|
|
conn.setAutoCommit(false);
|
461 |
|
|
|
462 |
|
|
pstmt = conn.prepareStatement("UPDATE db_version SET status = ?");
|
463 |
|
|
pstmt.setInt(1, VERSION_INACTIVE);
|
464 |
|
|
pstmt.execute();
|
465 |
|
|
pstmt.close();
|
466 |
|
|
|
467 |
|
|
pstmt = conn.prepareStatement("INSERT INTO db_version "
|
468 |
|
|
+ "(version, status, date_created) VALUES (?,?,?)");
|
469 |
5027
|
daigle
|
pstmt.setString(1, MetacatVersion.getVersionID());
|
470 |
4080
|
daigle
|
pstmt.setInt(2, VERSION_ACTIVE);
|
471 |
|
|
pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
|
472 |
|
|
pstmt.execute();
|
473 |
|
|
|
474 |
|
|
conn.commit();
|
475 |
|
|
} catch (SQLException e) {
|
476 |
|
|
conn.rollback();
|
477 |
5030
|
daigle
|
throw new SQLException("DBAdmin.updateDBVersion - sql error: " + e.getMessage());
|
478 |
4080
|
daigle
|
} catch (PropertyNotFoundException pnfe) {
|
479 |
|
|
conn.rollback();
|
480 |
5030
|
daigle
|
throw new SQLException("DBAdmin.updateDBVersion - property error" + pnfe.getMessage());
|
481 |
4080
|
daigle
|
}
|
482 |
|
|
finally {
|
483 |
|
|
try {
|
484 |
|
|
pstmt.close();
|
485 |
|
|
} finally {
|
486 |
|
|
DBConnectionPool.returnDBConnection(conn, serialNumber);
|
487 |
|
|
}
|
488 |
|
|
}
|
489 |
|
|
}
|
490 |
|
|
|
491 |
|
|
/**
|
492 |
|
|
* Validate connectivity to the database. Validation methods return a string
|
493 |
|
|
* error message if there is an issue. This allows the calling code to run
|
494 |
|
|
* several validations and compile the errors into a list that can be
|
495 |
|
|
* displayed on a web page if desired.
|
496 |
|
|
*
|
497 |
|
|
* @param dbDriver
|
498 |
|
|
* the database driver
|
499 |
|
|
* @param connection
|
500 |
|
|
* the jdbc connection string
|
501 |
|
|
* @param user
|
502 |
|
|
* the user name
|
503 |
|
|
* @param password
|
504 |
|
|
* the login password
|
505 |
|
|
* @return a string holding error message if validation fails.
|
506 |
|
|
*/
|
507 |
|
|
public String validateDBConnectivity(String dbDriver, String connection,
|
508 |
|
|
String user, String password) {
|
509 |
|
|
try {
|
510 |
|
|
DBConnection.testConnection(dbDriver, connection, user, password);
|
511 |
|
|
} catch (SQLException se) {
|
512 |
|
|
return "Invalid database credential was provided: "
|
513 |
|
|
+ se.getMessage();
|
514 |
|
|
}
|
515 |
|
|
|
516 |
|
|
return null;
|
517 |
|
|
}
|
518 |
|
|
|
519 |
|
|
/**
|
520 |
|
|
* Checks to see if this is a 1.9.0 database schema by looking for the
|
521 |
|
|
* db_version table which was created for 1.9.0. Note, there is no guarantee
|
522 |
|
|
* that this table will not be removed in subsequent versions. You should
|
523 |
|
|
* search for db versions from newest to oldest, only getting to this
|
524 |
|
|
* function when newer versions have not been matched.
|
525 |
|
|
*
|
526 |
|
|
* @param dbMetaData
|
527 |
|
|
* the meta data for this database.
|
528 |
|
|
* @returns boolean which is true if table is found, false otherwise
|
529 |
|
|
*/
|
530 |
|
|
private boolean is1_9_0(Connection connection) throws SQLException {
|
531 |
|
|
return DBUtil.tableExists(connection, "db_version");
|
532 |
|
|
}
|
533 |
4975
|
daigle
|
|
534 |
|
|
/**
|
535 |
|
|
* Checks to see if this is a 1.9.1 database schema by looking for the
|
536 |
|
|
* scheduled_job table which was created for 1.9.0. Note, there is no guarantee
|
537 |
|
|
* that this table will not be removed in subsequent versions. You should
|
538 |
|
|
* search for db versions from newest to oldest, only getting to this
|
539 |
|
|
* function when newer versions have not been matched.
|
540 |
|
|
*
|
541 |
|
|
* @param dbMetaData
|
542 |
|
|
* the meta data for this database.
|
543 |
|
|
* @returns boolean which is true if table is found, false otherwise
|
544 |
|
|
*/
|
545 |
|
|
private boolean is1_9_1(Connection connection) throws SQLException {
|
546 |
|
|
return DBUtil.tableExists(connection, "db_version");
|
547 |
|
|
}
|
548 |
4080
|
daigle
|
|
549 |
|
|
/**
|
550 |
|
|
* Checks to see if this is a 1.8.0 database schema by looking for the
|
551 |
|
|
* xml_nodes_idx4 index which was created for 1.8.0. Note, there is no
|
552 |
|
|
* guarantee that this index will not be removed in subsequent versions. You
|
553 |
|
|
* should search for db versions from newest to oldest, only getting to this
|
554 |
|
|
* function when newer versions have not been matched.
|
555 |
|
|
*
|
556 |
|
|
* @param dbMetaData
|
557 |
|
|
* the meta data for this database.
|
558 |
|
|
* @returns boolean which is true if index is found, false otherwise
|
559 |
|
|
*/
|
560 |
4561
|
daigle
|
private boolean is1_8_0(Connection connection) throws SQLException, PropertyNotFoundException {
|
561 |
|
|
String tableName = "xml_nodes";
|
562 |
|
|
String dbType = PropertyService.getProperty("database.type");
|
563 |
|
|
|
564 |
|
|
boolean isOracle = dbType.equals("oracle");
|
565 |
|
|
if(isOracle) {
|
566 |
|
|
tableName = "XML_NODES";
|
567 |
|
|
}
|
568 |
|
|
return DBUtil.indexExists(connection, tableName, "xml_nodes_idx4");
|
569 |
4080
|
daigle
|
}
|
570 |
|
|
|
571 |
|
|
/**
|
572 |
|
|
* Checks to see if this is a 1.7.0 database schema by looking for the
|
573 |
|
|
* xml_documents_idx2 index which was created for 1.7.0. Note, there is no
|
574 |
|
|
* guarantee that this index will not be removed in subsequent versions. You
|
575 |
|
|
* should search for db versions from newest to oldest, only getting to this
|
576 |
|
|
* function when newer versions have not been matched.
|
577 |
|
|
*
|
578 |
|
|
* @param dbMetaData
|
579 |
|
|
* the meta data for this database.
|
580 |
|
|
* @returns boolean which is true if index is found, false otherwise
|
581 |
|
|
*/
|
582 |
4561
|
daigle
|
private boolean is1_7_0(Connection connection) throws SQLException, PropertyNotFoundException {
|
583 |
|
|
String tableName = "xml_documents";
|
584 |
|
|
String dbType = PropertyService.getProperty("database.type");
|
585 |
|
|
|
586 |
|
|
boolean isOracle = dbType.equals("oracle");
|
587 |
|
|
if(isOracle) {
|
588 |
|
|
tableName = "XML_DOCUMENTS";
|
589 |
|
|
}
|
590 |
|
|
|
591 |
|
|
return DBUtil.indexExists(connection, tableName, "xml_documents_idx2");
|
592 |
4080
|
daigle
|
}
|
593 |
|
|
|
594 |
|
|
/**
|
595 |
|
|
* Checks to see if this is a 1.6.0 database schema by looking for the
|
596 |
|
|
* identifier table which was created for 1.6.0. Note, there is no guarantee
|
597 |
|
|
* that this table will not be removed in subsequent versions. You should
|
598 |
|
|
* search for db versions from newest to oldest, only getting to this
|
599 |
|
|
* function when newer versions have not been matched.
|
600 |
|
|
*
|
601 |
|
|
* @param dbMetaData
|
602 |
|
|
* the meta data for this database.
|
603 |
|
|
* @returns boolean which is true if table is found, false otherwise
|
604 |
|
|
*/
|
605 |
|
|
private boolean is1_6_0(Connection connection) throws SQLException {
|
606 |
|
|
return DBUtil.tableExists(connection, "identifier");
|
607 |
|
|
}
|
608 |
|
|
|
609 |
|
|
/**
|
610 |
|
|
* Checks to see if this is a 1.5.0 database schema by looking for the
|
611 |
|
|
* xml_returnfield table which was created for 1.5.0. Note, there is no
|
612 |
|
|
* guarantee that this table will not be removed in subsequent versions. You
|
613 |
|
|
* should search for db versions from newest to oldest, only getting to this
|
614 |
|
|
* function when newer versions have not been matched.
|
615 |
|
|
*
|
616 |
|
|
* @param dbMetaData
|
617 |
|
|
* the meta data for this database.
|
618 |
|
|
* @returns boolean which is true if table is found, false otherwise
|
619 |
|
|
*/
|
620 |
|
|
private boolean is1_5_0(Connection connection) throws SQLException {
|
621 |
|
|
return DBUtil.tableExists(connection, "xml_returnfield");
|
622 |
|
|
}
|
623 |
|
|
|
624 |
|
|
/**
|
625 |
|
|
* Checks to see if this is a 1.4.0 database schema by looking for the
|
626 |
|
|
* access_log table which was created for 1.4.0. Note, there is no guarantee
|
627 |
|
|
* that this table will not be removed in subsequent versions. You should
|
628 |
|
|
* search for db versions from newest to oldest, only getting to this
|
629 |
|
|
* function when newer versions have not been matched.
|
630 |
|
|
*
|
631 |
|
|
* @param dbMetaData
|
632 |
|
|
* the meta data for this database.
|
633 |
|
|
* @returns boolean which is true if table is found, false otherwise
|
634 |
|
|
*/
|
635 |
|
|
private boolean is1_4_0(Connection connection) throws SQLException {
|
636 |
|
|
return DBUtil.tableExists(connection, "access_log");
|
637 |
|
|
}
|
638 |
|
|
|
639 |
|
|
/**
|
640 |
|
|
* Checks to see if this is a 1.3.0 database schema by looking for the
|
641 |
|
|
* xml_accesssubtree table which was created for 1.3.0. Note, there is no
|
642 |
|
|
* guarantee that this table will not be removed in subsequent versions. You
|
643 |
|
|
* should search for db versions from newest to oldest, only getting to this
|
644 |
|
|
* function when newer versions have not been matched.
|
645 |
|
|
*
|
646 |
|
|
* @param dbMetaData
|
647 |
|
|
* the meta data for this database.
|
648 |
|
|
* @returns boolean which is true if table is found, false otherwise
|
649 |
|
|
*/
|
650 |
|
|
private boolean is1_3_0(Connection connection) throws SQLException {
|
651 |
|
|
return DBUtil.tableExists(connection, "xml_accesssubtree");
|
652 |
|
|
}
|
653 |
|
|
|
654 |
|
|
/**
|
655 |
|
|
* Checks to see if this is a 1.2.0 database schema by looking for the
|
656 |
|
|
* datareplicate column which was created on the xml_replication table for
|
657 |
|
|
* 1.2.0. Note, there is no guarantee that this column will not be removed
|
658 |
|
|
* in subsequent versions. You should search for db versions from newest to
|
659 |
|
|
* oldest, only getting to this function when newer versions have not been
|
660 |
|
|
* matched.
|
661 |
|
|
*
|
662 |
|
|
* @param dbMetaData
|
663 |
|
|
* the meta data for this database.
|
664 |
|
|
* @returns boolean which is true if column is found, false otherwise
|
665 |
|
|
*/
|
666 |
|
|
private boolean is1_2_0(Connection connection) throws SQLException {
|
667 |
|
|
return DBUtil.columnExists(connection, "xml_replication",
|
668 |
|
|
"datareplicate");
|
669 |
|
|
}
|
670 |
|
|
|
671 |
|
|
/**
|
672 |
|
|
* Creates a list of database update script names by looking at the database
|
673 |
|
|
* version and the metacat version and then getting any script that is
|
674 |
|
|
* inbetween the two (inclusive of metacat version).
|
675 |
|
|
*
|
676 |
|
|
* @returns a Vector of Strings holding the names of scripts that need to be
|
677 |
|
|
* run to get the database updated to this version of metacat
|
678 |
|
|
*
|
679 |
|
|
*/
|
680 |
|
|
public Vector<String> getUpdateScripts() throws AdminException {
|
681 |
|
|
Vector<String> updateScriptList = new Vector<String>();
|
682 |
|
|
String sqlFileLocation = null;
|
683 |
|
|
String databaseType = null;
|
684 |
5027
|
daigle
|
MetacatVersion metaCatVersion = null;
|
685 |
4080
|
daigle
|
|
686 |
|
|
// get the location of sql scripts
|
687 |
|
|
try {
|
688 |
|
|
metaCatVersion = SystemUtil.getMetacatVersion();
|
689 |
|
|
sqlFileLocation = SystemUtil.getSQLDir();
|
690 |
|
|
databaseType = PropertyService.getProperty("database.type");
|
691 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
692 |
5030
|
daigle
|
throw new AdminException("DBAdmin.getUpdateScripts - Could not get property while trying "
|
693 |
4080
|
daigle
|
+ "to retrieve database update scripts: " + pnfe.getMessage());
|
694 |
|
|
}
|
695 |
4105
|
daigle
|
|
696 |
|
|
// Each type of db has it's own set of scripts. For instance, Oracle
|
697 |
|
|
// scripts end in -oracle.sql. Postges end in -postgres.sql, etc
|
698 |
4155
|
daigle
|
String sqlSuffix = "-" + scriptSuffixMap.get("database.scriptsuffix." + databaseType);
|
699 |
4105
|
daigle
|
|
700 |
4080
|
daigle
|
// if either of these is null, we don't want to do anything. Just
|
701 |
|
|
// return an empty list.
|
702 |
|
|
if (metaCatVersion == null || databaseVersion == null) {
|
703 |
|
|
return updateScriptList;
|
704 |
|
|
}
|
705 |
|
|
|
706 |
|
|
// go through all the versions that the the software went through and
|
707 |
|
|
// figure out which ones need to be applied to the database
|
708 |
|
|
for (DBVersion nextVersion : versionSet) {
|
709 |
|
|
Vector<String> versionUpdateScripts = nextVersion
|
710 |
|
|
.getUpdateScripts();
|
711 |
|
|
|
712 |
|
|
// if the database version is 0.0.0, it is new.
|
713 |
|
|
// apply all scripts.
|
714 |
|
|
if (databaseVersion.getVersionString().equals("0.0.0")
|
715 |
|
|
&& nextVersion.getVersionString().equals("0.0.0")) {
|
716 |
|
|
for (String versionUpdateScript : versionUpdateScripts) {
|
717 |
|
|
updateScriptList.add(sqlFileLocation + FileUtil.getFS()
|
718 |
|
|
+ versionUpdateScript + sqlSuffix);
|
719 |
|
|
}
|
720 |
|
|
return updateScriptList;
|
721 |
|
|
}
|
722 |
|
|
|
723 |
|
|
// add every update script that is > than the db version
|
724 |
|
|
// but <= to the metacat version to the update list.
|
725 |
|
|
if (nextVersion.compareTo(databaseVersion) > 0
|
726 |
|
|
&& nextVersion.compareTo(metaCatVersion) <= 0
|
727 |
|
|
&& nextVersion.getUpdateScripts() != null) {
|
728 |
|
|
for (String versionUpdateScript : versionUpdateScripts) {
|
729 |
|
|
updateScriptList.add(sqlFileLocation + FileUtil.getFS()
|
730 |
|
|
+ versionUpdateScript + sqlSuffix);
|
731 |
|
|
}
|
732 |
|
|
}
|
733 |
|
|
}
|
734 |
|
|
|
735 |
|
|
// this should now hold all the script names that need to be run
|
736 |
|
|
// to bring the database up to date with this version of metacat
|
737 |
|
|
return updateScriptList;
|
738 |
|
|
}
|
739 |
|
|
|
740 |
6012
|
leinfelder
|
public Vector<String> getUpdateClasses() throws AdminException {
|
741 |
|
|
Vector<String> updateClassList = new Vector<String>();
|
742 |
|
|
MetacatVersion metaCatVersion = null;
|
743 |
|
|
|
744 |
|
|
// get the location of sql scripts
|
745 |
|
|
try {
|
746 |
|
|
metaCatVersion = SystemUtil.getMetacatVersion();
|
747 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
748 |
|
|
throw new AdminException("DBAdmin.getUpdateScripts - Could not get property while trying "
|
749 |
|
|
+ "to retrieve update utilities: " + pnfe.getMessage());
|
750 |
|
|
}
|
751 |
|
|
|
752 |
|
|
// if either of these is null, we don't want to do anything. Just
|
753 |
|
|
// return an empty list.
|
754 |
|
|
if (metaCatVersion == null || databaseVersion == null) {
|
755 |
|
|
return updateClassList;
|
756 |
|
|
}
|
757 |
|
|
|
758 |
|
|
// go through all the versions that the the software went through and
|
759 |
|
|
// figure out which ones need to be applied to the database
|
760 |
|
|
for (DBVersion nextVersion : versionSet) {
|
761 |
|
|
|
762 |
|
|
// add every update script that is > than the db version
|
763 |
|
|
// but <= to the metacat version to the update list.
|
764 |
|
|
if (nextVersion.compareTo(databaseVersion) > 0
|
765 |
|
|
&& nextVersion.compareTo(metaCatVersion) <= 0) {
|
766 |
|
|
String key = "database.upgradeUtility." + nextVersion.getVersionString();
|
767 |
|
|
String className = null;
|
768 |
|
|
try {
|
769 |
|
|
className = PropertyService.getProperty(key);
|
770 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
771 |
|
|
// there probably isn't a utility needed for this version
|
772 |
|
|
logMetacat.warn("No utility defined for version: " + key);
|
773 |
|
|
continue;
|
774 |
|
|
}
|
775 |
|
|
updateClassList.add(className);
|
776 |
|
|
}
|
777 |
|
|
}
|
778 |
|
|
|
779 |
|
|
// this should now hold all the script names that need to be run
|
780 |
|
|
// to bring the database up to date with this version of metacat
|
781 |
|
|
return updateClassList;
|
782 |
|
|
}
|
783 |
|
|
|
784 |
4080
|
daigle
|
/**
|
785 |
|
|
* Iterates through the list of scripts that need to be run to upgrade
|
786 |
|
|
* the database and calls runSQLFile on each.
|
787 |
|
|
*/
|
788 |
|
|
public void upgradeDatabase() throws AdminException {
|
789 |
|
|
try {
|
790 |
|
|
// get a list of the script names that need to be run
|
791 |
|
|
Vector<String> updateScriptList = getUpdateScripts();
|
792 |
|
|
|
793 |
|
|
// call runSQLFile on each
|
794 |
|
|
for (String updateScript : updateScriptList) {
|
795 |
|
|
runSQLFile(updateScript);
|
796 |
|
|
}
|
797 |
6012
|
leinfelder
|
|
798 |
|
|
// get the classes we need to execute in order to bring DB to current version
|
799 |
|
|
Vector<String> updateClassList = getUpdateClasses();
|
800 |
|
|
for (String className : updateClassList) {
|
801 |
|
|
UpgradeUtilityInterface utility = null;
|
802 |
|
|
try {
|
803 |
|
|
utility = (UpgradeUtilityInterface) Class.forName(className).newInstance();
|
804 |
|
|
utility.upgrade();
|
805 |
|
|
} catch (Exception e) {
|
806 |
|
|
throw new AdminException("DBAdmin.upgradeDatabase - error getting utility class: "
|
807 |
|
|
+ className + ". Error message: "
|
808 |
|
|
+ e.getMessage());
|
809 |
|
|
}
|
810 |
|
|
}
|
811 |
4080
|
daigle
|
|
812 |
|
|
// update the db version to be the metacat version
|
813 |
|
|
databaseVersion = new DBVersion(SystemUtil.getMetacatVersion().getVersionString());
|
814 |
|
|
} catch (SQLException sqle) {
|
815 |
5030
|
daigle
|
throw new AdminException("DBAdmin.upgradeDatabase - SQL error when running upgrade scripts: "
|
816 |
4080
|
daigle
|
+ sqle.getMessage());
|
817 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
818 |
5030
|
daigle
|
throw new AdminException("DBAdmin.upgradeDatabase - SQL error when running upgrade scripts: "
|
819 |
4080
|
daigle
|
+ pnfe.getMessage());
|
820 |
|
|
}catch (NumberFormatException nfe) {
|
821 |
5030
|
daigle
|
throw new AdminException("DBAdmin.upgradeDatabase - Bad version format numbering: "
|
822 |
4080
|
daigle
|
+ nfe.getMessage());
|
823 |
|
|
}
|
824 |
|
|
}
|
825 |
|
|
|
826 |
|
|
/**
|
827 |
|
|
* Runs the commands in a sql script. Individual commands are loaded into a
|
828 |
|
|
* string vector and run one at a time.
|
829 |
|
|
*
|
830 |
|
|
* @param sqlFileName
|
831 |
|
|
* the name of the file holding the sql statements that need to
|
832 |
|
|
* get run.
|
833 |
|
|
*/
|
834 |
|
|
public void runSQLFile(String sqlFileName) throws AdminException, SQLException {
|
835 |
|
|
|
836 |
|
|
// if update file does not exist, do not do the update.
|
837 |
|
|
if (FileUtil.getFileStatus(sqlFileName) < FileUtil.EXISTS_READABLE) {
|
838 |
|
|
throw new AdminException("Could not read sql update file: "
|
839 |
|
|
+ sqlFileName);
|
840 |
|
|
}
|
841 |
|
|
|
842 |
|
|
Connection connection = null;
|
843 |
|
|
try {
|
844 |
|
|
connection = DBUtil.getConnection(PropertyService
|
845 |
|
|
.getProperty("database.connectionURI"), PropertyService
|
846 |
|
|
.getProperty("database.user"), PropertyService
|
847 |
|
|
.getProperty("database.password"));
|
848 |
|
|
connection.setAutoCommit(false);
|
849 |
|
|
|
850 |
|
|
// load the sql from the file into a vector of individual statements
|
851 |
|
|
// and execute them.
|
852 |
5030
|
daigle
|
logMetacat.debug("DBAdmin.runSQLFile - processing File: " + sqlFileName);
|
853 |
4080
|
daigle
|
Vector<String> sqlCommands = loadSQLFromFile(sqlFileName);
|
854 |
|
|
for (String sqlStatement : sqlCommands) {
|
855 |
|
|
Statement statement = connection.createStatement();
|
856 |
|
|
logMetacat.debug("executing sql: " + sqlStatement);
|
857 |
4400
|
daigle
|
try {
|
858 |
|
|
statement.execute(sqlStatement);
|
859 |
|
|
} catch (SQLException sqle) {
|
860 |
|
|
// Oracle complains if we try and drop a sequence (ORA-02289) or a
|
861 |
|
|
// trigger (ORA-04098/ORA-04080) or a table/view (ORA-00942) or and index (ORA-01418)
|
862 |
|
|
// that does not exist. We don't care if this happens.
|
863 |
|
|
if (sqlStatement.toUpperCase().startsWith("DROP") &&
|
864 |
|
|
(sqle.getMessage().contains("ORA-02289") ||
|
865 |
|
|
sqle.getMessage().contains("ORA-04098") ||
|
866 |
|
|
sqle.getMessage().contains("ORA-04080") ||
|
867 |
|
|
sqle.getMessage().contains("ORA-00942"))) {
|
868 |
5030
|
daigle
|
logMetacat.warn("DBAdmin.runSQLFile - did not process sql drop statement: " + sqle.getMessage());
|
869 |
4400
|
daigle
|
} else {
|
870 |
|
|
throw sqle;
|
871 |
|
|
}
|
872 |
|
|
}
|
873 |
4080
|
daigle
|
}
|
874 |
|
|
connection.commit();
|
875 |
|
|
|
876 |
|
|
} catch (IOException ioe) {
|
877 |
5030
|
daigle
|
throw new AdminException("DBAdmin.runSQLFile - Could not read SQL file"
|
878 |
4080
|
daigle
|
+ ioe.getMessage());
|
879 |
|
|
} catch (PropertyNotFoundException pnfe) {
|
880 |
5030
|
daigle
|
throw new AdminException("DBAdmin.runSQLFile - Could not find property to run SQL file"
|
881 |
4080
|
daigle
|
+ pnfe.getMessage());
|
882 |
|
|
} catch (SQLException sqle) {
|
883 |
|
|
if (connection != null) {
|
884 |
|
|
connection.rollback();
|
885 |
|
|
}
|
886 |
|
|
throw sqle;
|
887 |
|
|
} finally {
|
888 |
|
|
if (connection != null) {
|
889 |
|
|
connection.close();
|
890 |
|
|
}
|
891 |
|
|
}
|
892 |
|
|
}
|
893 |
|
|
|
894 |
|
|
/**
|
895 |
|
|
* Very basic utility to read sql from a file and return a vector of the
|
896 |
|
|
* individual sql statements. This ignores any line that starts with /* or *.
|
897 |
|
|
* It strips anything following --. Sql is parsed by looking for lines that
|
898 |
|
|
* start with one of the following identifiers: INSERT, UPDATE, ALTER,
|
899 |
|
|
* CREATE, DROP, BEGIN and COMMIT. It then assumes that everything until the
|
900 |
|
|
* line that ends with ; is part of the sql, excluding comments.
|
901 |
|
|
*
|
902 |
|
|
* @param sqlFileName
|
903 |
|
|
* the name of the file to read.
|
904 |
|
|
* @return a vector holding the individual sql statements.
|
905 |
|
|
*/
|
906 |
|
|
public Vector<String> loadSQLFromFile(String sqlFileName)
|
907 |
|
|
throws IOException {
|
908 |
|
|
|
909 |
|
|
// this will end up holding individual sql statements
|
910 |
|
|
Vector<String> sqlCommands = new Vector<String>();
|
911 |
|
|
|
912 |
|
|
FileInputStream fin = null;
|
913 |
|
|
try {
|
914 |
|
|
fin = new FileInputStream(sqlFileName);
|
915 |
|
|
|
916 |
|
|
BufferedReader reader = new BufferedReader(new InputStreamReader(
|
917 |
|
|
fin));
|
918 |
|
|
|
919 |
|
|
// Read in file
|
920 |
|
|
String fileLine;
|
921 |
|
|
while ((fileLine = reader.readLine()) != null) {
|
922 |
4400
|
daigle
|
String endChar = ";";
|
923 |
4080
|
daigle
|
String trimmedLine = fileLine.trim();
|
924 |
|
|
|
925 |
|
|
// get the first word on the line
|
926 |
|
|
String firstWord = trimmedLine;
|
927 |
|
|
if (trimmedLine.indexOf(' ') > 0) {
|
928 |
|
|
firstWord = trimmedLine.substring(0, trimmedLine
|
929 |
|
|
.indexOf(' '));
|
930 |
|
|
}
|
931 |
4400
|
daigle
|
if (firstWord.endsWith(endChar)) {
|
932 |
|
|
firstWord = firstWord.substring(0, firstWord.indexOf(endChar));
|
933 |
4080
|
daigle
|
}
|
934 |
|
|
|
935 |
|
|
// if the first word is a known sql command, start creating a
|
936 |
|
|
// sql statement.
|
937 |
|
|
if (sqlCommandSet.contains(firstWord.toUpperCase())) {
|
938 |
4400
|
daigle
|
String sqlStatement = "";
|
939 |
4080
|
daigle
|
|
940 |
|
|
// keep reading lines until we find one that is not a
|
941 |
4400
|
daigle
|
// comment and ends with endChar
|
942 |
|
|
do {
|
943 |
|
|
String trimmedInnerLine = fileLine.trim();
|
944 |
|
|
|
945 |
|
|
// if there is a BEGIN or DECLARE statement, we are now in plsql and we're
|
946 |
|
|
// using the '/' character as our sql end delimiter.
|
947 |
|
|
if (trimmedInnerLine.toUpperCase().equals("BEGIN") ||
|
948 |
|
|
trimmedInnerLine.toUpperCase().startsWith("BEGIN ") ||
|
949 |
|
|
trimmedInnerLine.toUpperCase().equals("DECLARE") ||
|
950 |
|
|
trimmedInnerLine.toUpperCase().startsWith("DECLARE ")) {
|
951 |
|
|
endChar = "/";
|
952 |
|
|
}
|
953 |
|
|
|
954 |
4080
|
daigle
|
// ignore comments and empty lines
|
955 |
|
|
if (trimmedInnerLine.matches("^$")
|
956 |
|
|
|| trimmedInnerLine.matches("^\\*.*")
|
957 |
|
|
|| trimmedInnerLine.matches("/\\*.*")) {
|
958 |
|
|
continue;
|
959 |
|
|
}
|
960 |
|
|
|
961 |
|
|
// get rid of any "--" comments at the end of the line
|
962 |
|
|
if (trimmedInnerLine.indexOf("--") >= 0) {
|
963 |
|
|
trimmedInnerLine = trimmedInnerLine.substring(0,
|
964 |
|
|
trimmedInnerLine.indexOf("--")).trim();
|
965 |
|
|
}
|
966 |
4400
|
daigle
|
if (sqlStatement.length() > 0) {
|
967 |
|
|
sqlStatement += " ";
|
968 |
|
|
}
|
969 |
|
|
sqlStatement += trimmedInnerLine;
|
970 |
|
|
if (trimmedInnerLine.endsWith(endChar)) {
|
971 |
|
|
sqlStatement =
|
972 |
|
|
sqlStatement.substring(0, sqlStatement.length() - 1);
|
973 |
4080
|
daigle
|
sqlCommands.add(sqlStatement);
|
974 |
|
|
break;
|
975 |
|
|
}
|
976 |
4400
|
daigle
|
} while ((fileLine = reader.readLine()) != null);
|
977 |
4080
|
daigle
|
}
|
978 |
|
|
}
|
979 |
|
|
} finally {
|
980 |
|
|
// Close our input stream
|
981 |
|
|
fin.close();
|
982 |
|
|
}
|
983 |
|
|
|
984 |
|
|
return sqlCommands;
|
985 |
|
|
}
|
986 |
|
|
|
987 |
|
|
/**
|
988 |
|
|
* Validate the most important configuration options submitted by the user.
|
989 |
|
|
*
|
990 |
|
|
* @return a vector holding error message for any fields that fail
|
991 |
|
|
* validation.
|
992 |
|
|
*/
|
993 |
|
|
protected Vector<String> validateOptions(HttpServletRequest request) {
|
994 |
|
|
Vector<String> errorVector = new Vector<String>();
|
995 |
|
|
|
996 |
|
|
// TODO MCD validate options.
|
997 |
|
|
|
998 |
|
|
return errorVector;
|
999 |
|
|
}
|
1000 |
|
|
}
|