Metacat

/**

 *  '$RCSfile$'

 *    Purpose: A Class that implements database configuration methods

 *  Copyright: 2008 Regents of the University of California and the

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Michael Daigle

 *

 *   '$Author$'

 *     '$Date$'

 * '$Revision$'

 *

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

package edu.ucsb.nceas.metacat.admin;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Timestamp;

import java.io.BufferedReader;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStreamReader;

import java.util.Date;

import java.util.Vector;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import edu.ucsb.nceas.metacat.database.DBConnectionPool;

import edu.ucsb.nceas.metacat.database.DBVersion;

import edu.ucsb.nceas.metacat.util.RequestUtil;

import edu.ucsb.nceas.metacat.util.SystemUtil;

import edu.ucsb.nceas.utilities.DBUtil;

import edu.ucsb.nceas.utilities.FileUtil;

import edu.ucsb.nceas.utilities.GeneralPropertyException;

import edu.ucsb.nceas.utilities.PropertyNotFoundException;

import org.apache.log4j.Logger;

/**

 * Control the display of the database configuration page and the processing

 * of the configuration values.

 */

	public static final int DB_DOES_NOT_EXIST = 0;

	public static final int TABLES_DO_NOT_EXIST = 1;

	public static final int TABLES_EXIST = 2;

	// db version statuses. This allows us to keep version history

	// in the db. Only the latest version record should be active.

	public static final int VERSION_INACTIVE = 0;

	public static final int VERSION_ACTIVE = 1;

	private TreeSet<DBVersion> versionSet = null;

	private static DBAdmin dbAdmin = null;

	private Logger logMetacat = Logger.getLogger(DBAdmin.class);

	private HashSet<String> sqlCommandSet = new HashSet<String>();

	/**

	 * private constructor since this is a singleton

	 */

	private DBAdmin() throws AdminException {

		sqlCommandSet.add("INSERT");

		sqlCommandSet.add("UPDATE");

		sqlCommandSet.add("DELETE");

		sqlCommandSet.add("ALTER");

		sqlCommandSet.add("CREATE");

		sqlCommandSet.add("DROP");

		sqlCommandSet.add("BEGIN");

		sqlCommandSet.add("COMMIT");

		// gets all the upgrade version objects

		try {

			versionSet = DatabaseUtil.getUpgradeVersions();

			throw new AdminException("Could not retrieve database upgrade "

					+ "versions during instantiation" + pnfe.getMessage());

		} catch (NumberFormatException nfe) {

			throw new AdminException("Bad version format numbering: "

					+ nfe.getMessage());

		}

	}

	/**

	 * Get the single instance of DBAdmin.

	 *

	 * @return the single instance of DBAdmin

	 */

	public static DBAdmin getInstance() throws AdminException {

		if (dbAdmin == null) {

			dbAdmin = new DBAdmin();

		}

		return dbAdmin;

	}

	/**

	 * Handle configuration of the database the first time that Metacat starts

	 * or when it is explicitly called. Collect necessary update information

	 * from the administrator.

	 *

	 * @param request

	 *            the http request information

	 * @param response

	 *            the http response to be sent back to the client

	 */

	public void configureDatabase(HttpServletRequest request,

			HttpServletResponse response) throws AdminException {

		String processForm = request.getParameter("processForm");

		String formErrors = (String) request.getAttribute("formErrors");

		HttpSession session = request.getSession();

		if (processForm == null || !processForm.equals("true") || formErrors != null) {

			// The servlet configuration parameters have not been set, or there

			// were form errors on the last attempt to configure, so redirect to

			// the web form for configuring metacat

			try {

				// get the current metacat version and the database version. If

				// the database version is older that the metacat version, run

				// the appropriate scripts to get them synchronized.

				databaseVersion = discoverDBVersion();

				// if the db version is already the same as the metacat

				// version, update metacat.properties

				if (databaseVersion.compareTo(metacatVersion) == 0) {

					PropertyService.setProperty("configutil.databaseConfigured",

							PropertyService.CONFIGURED);

				}

				DBVersion dbVersionString = getDBVersion();

				request.setAttribute("databaseVersion", dbVersionString);

				Vector<String> updateScriptList = getUpdateScripts();

				request.setAttribute("updateScriptList", updateScriptList);

				request.setAttribute("supportEmail", supportEmail);

				// Forward the request to the JSP page

				RequestUtil.clearRequestMessages(request);

				RequestUtil.forwardRequest(request, response,

				throw new AdminException("Problem getting or setting property while "

						+ "initializing system properties page: " + gpe.getMessage());

				throw new AdminException("IO problem while initializing "

						+ "system properties page:" + ioe.getMessage());

			} catch (ServletException se) {

				throw new AdminException("problem forwarding request while "

						+ "initializing system properties page: " + se.getMessage());

			}

		} else {

			// The configuration form is being submitted and needs to be

			// processed, setting the properties in the configuration file

			// then restart metacat

			// The configuration form is being submitted and needs to be

			// processed.

			Vector<String> validationErrors = new Vector<String>();

			Vector<String> processingSuccess = new Vector<String>();

			try {

				// Validate that the options provided are legitimate. Note that

				// we've allowed them to persist their entries. As of this point

				// there is no other easy way to go back to the configure form

				// and

				// preserve their entries.

				validationErrors.addAll(validateOptions(request));

				upgradeDatabase();

				// Now that the options have been set, change the

				// 'databaseConfigured' option to 'true' so that normal metacat

				// requests will go through

				PropertyService.setProperty("configutil.databaseConfigured",

						PropertyService.CONFIGURED);

				// Reload the main metacat configuration page

				processingSuccess.add("Database successfully upgraded");

				RequestUtil.clearRequestMessages(request);

				RequestUtil.setRequestSuccess(request, processingSuccess);

				RequestUtil.forwardRequest(request, response,

				// outside the install directory.

				throw new AdminException("Problem getting or setting property while "

						+ "upgrading database: " + gpe.getMessage());

			}  catch (IOException ioe) {

				throw new AdminException("IO problem while upgrading database: "

						 + ioe.getMessage());

			} catch (ServletException se) {

				throw new AdminException("problem forwarding request while "

						+ "upgrading database: " + se.getMessage());

			}

		}

	}

	/**

	 * Performs a status check on the database.

	 *

	 * @returns integer representing the status of the database. These can be:

	 * 		-- DB_DOES_NOT_EXIST = 0;

	 *      -- TABLES_DO_NOT_EXIST = 1;

	 *      -- TABLES_EXIST = 2;

	 */

	public int getDBStatus() throws SQLException, PropertyNotFoundException {

		Connection connection = DBUtil.getConnection(PropertyService

				.getProperty("database.connectionURI"), PropertyService

				.getProperty("database.user"), PropertyService

				.getProperty("database.password"));

		if (DBUtil.tableExists(connection, "xml_documents")) {

			return TABLES_EXIST;

		}

		return TABLES_DO_NOT_EXIST;

	}

	/**

	 * Get the version of the database as a string

	 *

	 * @returns string representing the version of the database.

	 */

	public DBVersion getDBVersion() throws AdminException {

		// don't even try to search for a database version until system

		// properties have been configured

		try {

				throw new AdminException("An attempt was made to get the database version "

								+ "before system properties were configured");

			}

					+ ue.getMessage());

		if (databaseVersion == null) {

			databaseVersion = discoverDBVersion();

		}

		if (databaseVersion == null) {

			throw new AdminException("Could not find database version");

		}

		return databaseVersion;

	}

	/**

	 * Try to discover the database version, first by calling

	 * getRegisteredDBVersion() to see if the database version is in a table in

	 * the database. If not, getUnRegisteredDBVersion() is called to see if we

	 * can devine the version by looking for unique changes made by scripts for

	 * each version update.

	 *

	 * @returns string representing the version of the database, null if none

	 *          could be found.

	 */

	private DBVersion discoverDBVersion() throws AdminException {

		try {

			int dbStatus = getDBStatus();

			if (dbStatus == DB_DOES_NOT_EXIST) {

				throw new AdminException(

						"Database does not exist for connection"

						+ PropertyService.getProperty("database.connectionURI"));

			} else if (dbStatus == TABLES_DO_NOT_EXIST) {

				databaseVersion = new DBVersion("0.0.0");

				return databaseVersion;

			}

			databaseVersion = getRegisteredDBVersion();

			if (databaseVersion != null) {

				return databaseVersion;

			}

			databaseVersion = getUnRegisteredDBVersion();

		} catch (SQLException sqle) {

			String errorMessage = "SQL error during  database version discovery: "

				+ sqle.getMessage();

			logMetacat.error(errorMessage);

			throw new AdminException(errorMessage);

		} catch (PropertyNotFoundException pnfe) {

			String errorMessage = "Property not found during  database version discovery: "

					+ pnfe.getMessage();

			logMetacat.error(errorMessage);

			throw new AdminException(errorMessage);

		} catch (NumberFormatException nfe) {

			throw new AdminException("Bad version format numbering: "

					+ nfe.getMessage());

		}

		if (databaseVersion == null) {

			throw new AdminException("Database version discovery returned null");

		}

		return databaseVersion;

	}

	/**

	 * Gets the version of the database from the db_version table. Usually this

	 * is the same as the version of the product, however the db version could

	 * be more granular if we applied a maintenance patch for instance.

	 *

	 * @returns string representing the version of the database.

	 */

	private DBVersion getRegisteredDBVersion() throws AdminException, SQLException {

		String dbVersionString = null;

		PreparedStatement pstmt = null;

		try {

			// check out DBConnection

			Connection connection =

				DBUtil.getConnection(

						PropertyService.getProperty("database.connectionURI"),

						PropertyService.getProperty("database.user"),

						PropertyService.getProperty("database.password"));

			if (!DBUtil.tableExists(connection, "db_version")) {

				return null;

			}

			pstmt =

				connection.prepareStatement("SELECT version FROM db_version WHERE status = ?");

			// Bind the values to the query

			pstmt.setInt(1, VERSION_ACTIVE);

			pstmt.execute();

			ResultSet rs = pstmt.getResultSet();

			boolean hasRows = rs.next();

			if (hasRows) {

				dbVersionString = rs.getString(1);

			}

			if (dbVersionString == null) {

				return null;

			}

			return new DBVersion(dbVersionString);

		} catch (SQLException sqle) {

			throw new AdminException("Could not run SQL to get registered db version: "

					+ sqle.getMessage());

		} catch (PropertyNotFoundException pnfe) {

			throw new AdminException("Could not get property for registered db version: "

					+ pnfe.getMessage());

		} catch (NumberFormatException nfe) {

			throw new AdminException("Bad version format numbering: "

					+ nfe.getMessage());

		} finally {

			if (pstmt != null) {

				pstmt.close();

			}

		}

	}

	/**

	 * Finds the version of the database for a database that does not have a

	 * dbVersion table yet. Work backwards with various clues found in update

	 * scripts to find the version.

	 *

	 * @returns string representing the version of the database.

	 */

	public DBVersion getUnRegisteredDBVersion() throws AdminException, SQLException {

		Connection connection = null;

		try {

			connection = DBUtil.getConnection(PropertyService

					.getProperty("database.connectionURI"), PropertyService

					.getProperty("database.user"), PropertyService

					.getProperty("database.password"));

			String dbVersionString = null;

				dbVersionString = "1.9.1";

			} else if (is1_9_0(connection)) {

			} else if (is1_8_0(connection)) {

				dbVersionString = "1.8.0";

			} else if (is1_7_0(connection)) {

				dbVersionString = "1.7.0";

			} else if (is1_6_0(connection)) {

				dbVersionString = "1.6.0";

			} else if (is1_5_0(connection)) {

				dbVersionString = "1.5.0";

			} else if (is1_4_0(connection)) {

				dbVersionString = "1.4.0";

			} else if (is1_3_0(connection)) {

				dbVersionString = "1.3.0";

			} else if (is1_2_0(connection)) {

				dbVersionString = "1.2.0";

			}

			if (dbVersionString == null) {

				return null;

			} else {

				return new DBVersion(dbVersionString);

			}

		} catch (PropertyNotFoundException pnfe) {

			throw new AdminException(

					"Could not get property for unregistered db version: "

							+ pnfe.getMessage());

		} catch (NumberFormatException nfe) {

			throw new AdminException("Bad version format numbering: "

					+ nfe.getMessage());

		}

	}

	/**

	 * Updates the version of the database. Typically this is done in the update

	 * scripts that get run when we upgrade the application. This method can be

	 * used if you are automating a patch on the database internally.

	 *

	 * @returns string representing the version of the database.

	 */

	public void updateDBVersion() throws SQLException {

		DBConnection conn = null;

		PreparedStatement pstmt = null;

		int serialNumber = -1;

		try {

			// check out DBConnection

			conn = DBConnectionPool

					.getDBConnection("DBAdmin.updateDBVersion()");

			serialNumber = conn.getCheckOutSerialNumber();

			conn.setAutoCommit(false);

			pstmt = conn.prepareStatement("UPDATE db_version SET status = ?");

			pstmt.setInt(1, VERSION_INACTIVE);

			pstmt.execute();

			pstmt.close();

			pstmt = conn.prepareStatement("INSERT INTO db_version "

					+ "(version, status, date_created) VALUES (?,?,?)");

			pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));

			pstmt.execute();

			conn.commit();

		} catch (SQLException e) {

			conn.rollback();

			throw new SQLException("DBAdmin.getDBVersion(). " + e.getMessage());

		} catch (PropertyNotFoundException pnfe) {

			conn.rollback();

			throw new SQLException("DBAdmin.getDBVersion(). " + pnfe.getMessage());

		}

		finally {

			try {

				pstmt.close();

			} finally {

				DBConnectionPool.returnDBConnection(conn, serialNumber);

			}

		}

	}

	/**

	 * Validate connectivity to the database. Validation methods return a string

	 * error message if there is an issue. This allows the calling code to run

	 * several validations and compile the errors into a list that can be

	 * displayed on a web page if desired.

	 *

	 * @param dbDriver

	 *            the database driver

	 * @param connection

	 *            the jdbc connection string

	 * @param user

	 *            the user name

	 * @param password

	 *            the login password

	 * @return a string holding error message if validation fails.

	 */

	public String validateDBConnectivity(String dbDriver, String connection,

			String user, String password) {

		try {

			DBConnection.testConnection(dbDriver, connection, user, password);

		} catch (SQLException se) {

			return "Invalid database credential was provided: "

					+ se.getMessage();

		}

		return null;

	}

	/**

	 * Checks to see if this is a 1.9.0 database schema by looking for the

	 * db_version table which was created for 1.9.0. Note, there is no guarantee

	 * that this table will not be removed in subsequent versions. You should

	 * search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if table is found, false otherwise

	 */

	private boolean is1_9_0(Connection connection) throws SQLException {

		return DBUtil.tableExists(connection, "db_version");

	}

	/**

	 * Checks to see if this is a 1.9.1 database schema by looking for the

	 * scheduled_job table which was created for 1.9.0. Note, there is no guarantee

	 * that this table will not be removed in subsequent versions. You should

	 * search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if table is found, false otherwise

	 */

	private boolean is1_9_1(Connection connection) throws SQLException {

		return DBUtil.tableExists(connection, "db_version");

	}

	/**

	 * Checks to see if this is a 1.8.0 database schema by looking for the

	 * xml_nodes_idx4 index which was created for 1.8.0. Note, there is no

	 * guarantee that this index will not be removed in subsequent versions. You

	 * should search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if index is found, false otherwise

	 */

		String tableName = "xml_nodes";

		String dbType = PropertyService.getProperty("database.type");

		boolean isOracle = dbType.equals("oracle");

		if(isOracle) {

			tableName = "XML_NODES";

		}

		return DBUtil.indexExists(connection, tableName, "xml_nodes_idx4");

	/**

	 * Checks to see if this is a 1.7.0 database schema by looking for the

	 * xml_documents_idx2 index which was created for 1.7.0. Note, there is no

	 * guarantee that this index will not be removed in subsequent versions. You

	 * should search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if index is found, false otherwise

	 */

		String tableName = "xml_documents";

		String dbType = PropertyService.getProperty("database.type");

		boolean isOracle = dbType.equals("oracle");

		if(isOracle) {

			tableName = "XML_DOCUMENTS";

		}

		return DBUtil.indexExists(connection, tableName, "xml_documents_idx2");

	/**

	 * Checks to see if this is a 1.6.0 database schema by looking for the

	 * identifier table which was created for 1.6.0. Note, there is no guarantee

	 * that this table will not be removed in subsequent versions. You should

	 * search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if table is found, false otherwise

	 */

	private boolean is1_6_0(Connection connection) throws SQLException {

		return DBUtil.tableExists(connection, "identifier");

	}

	/**

	 * Checks to see if this is a 1.5.0 database schema by looking for the

	 * xml_returnfield table which was created for 1.5.0. Note, there is no

	 * guarantee that this table will not be removed in subsequent versions. You

	 * should search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if table is found, false otherwise

	 */

	private boolean is1_5_0(Connection connection) throws SQLException {

		return DBUtil.tableExists(connection, "xml_returnfield");

	}

	/**

	 * Checks to see if this is a 1.4.0 database schema by looking for the

	 * access_log table which was created for 1.4.0. Note, there is no guarantee

	 * that this table will not be removed in subsequent versions. You should

	 * search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if table is found, false otherwise

	 */

	private boolean is1_4_0(Connection connection) throws SQLException {

		return DBUtil.tableExists(connection, "access_log");

	}

	/**

	 * Checks to see if this is a 1.3.0 database schema by looking for the

	 * xml_accesssubtree table which was created for 1.3.0. Note, there is no

	 * guarantee that this table will not be removed in subsequent versions. You

	 * should search for db versions from newest to oldest, only getting to this

	 * function when newer versions have not been matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if table is found, false otherwise

	 */

	private boolean is1_3_0(Connection connection) throws SQLException {

		return DBUtil.tableExists(connection, "xml_accesssubtree");

	}

	/**

	 * Checks to see if this is a 1.2.0 database schema by looking for the

	 * datareplicate column which was created on the xml_replication table for

	 * 1.2.0. Note, there is no guarantee that this column will not be removed

	 * in subsequent versions. You should search for db versions from newest to

	 * oldest, only getting to this function when newer versions have not been

	 * matched.

	 *

	 * @param dbMetaData

	 *            the meta data for this database.

	 * @returns boolean which is true if column is found, false otherwise

	 */

	private boolean is1_2_0(Connection connection) throws SQLException {

		return DBUtil.columnExists(connection, "xml_replication",

				"datareplicate");

	}

	/**

	 * Creates a list of database update script names by looking at the database

	 * version and the metacat version and then getting any script that is

	 * inbetween the two (inclusive of metacat version).

	 *

	 * @returns a Vector of Strings holding the names of scripts that need to be

	 *          run to get the database updated to this version of metacat

	 *

	 */

	public Vector<String> getUpdateScripts() throws AdminException {

		Vector<String> updateScriptList = new Vector<String>();

		String sqlFileLocation = null;

		String databaseType = null;

		// get the location of sql scripts

		try {

			metaCatVersion = SystemUtil.getMetacatVersion();

			sqlFileLocation = SystemUtil.getSQLDir();

			databaseType = PropertyService.getProperty("database.type");

		} catch (PropertyNotFoundException pnfe) {

			throw new AdminException("Could not get property while trying "

					+ "to retrieve database update scripts: " + pnfe.getMessage());

		}

		// Each type of db has it's own set of scripts.  For instance, Oracle

		// scripts end in -oracle.sql.  Postges end in -postgres.sql, etc

		// return an empty list.

		if (metaCatVersion == null || databaseVersion == null) {

			return updateScriptList;

		}

		// go through all the versions that the the software went through and

		// figure out which ones need to be applied to the database

		for (DBVersion nextVersion : versionSet) {

			Vector<String> versionUpdateScripts = nextVersion

					.getUpdateScripts();

			// if the database version is 0.0.0, it is new.

			// apply all scripts.

			if (databaseVersion.getVersionString().equals("0.0.0")

					&& nextVersion.getVersionString().equals("0.0.0")) {

				for (String versionUpdateScript : versionUpdateScripts) {

					updateScriptList.add(sqlFileLocation + FileUtil.getFS()

							+ versionUpdateScript + sqlSuffix);

				}

				return updateScriptList;

			}

			// add every update script that is > than the db version

			// but <= to the metacat version to the update list.

			if (nextVersion.compareTo(databaseVersion) > 0

					&& nextVersion.compareTo(metaCatVersion) <= 0

					&& nextVersion.getUpdateScripts() != null) {

				for (String versionUpdateScript : versionUpdateScripts) {

					updateScriptList.add(sqlFileLocation + FileUtil.getFS()

							+ versionUpdateScript + sqlSuffix);

				}

			}

		}

		// this should now hold all the script names that need to be run

		// to bring the database up to date with this version of metacat

		return updateScriptList;

	}

	/**

	 * Iterates through the list of scripts that need to be run to upgrade

	 * the database and calls runSQLFile on each.

	 */

	public void upgradeDatabase() throws AdminException {

		try {

			// get a list of the script names that need to be run

			Vector<String> updateScriptList = getUpdateScripts();

			// call runSQLFile on each

			for (String updateScript : updateScriptList) {

				runSQLFile(updateScript);

			}

			// update the db version to be the metacat version

			databaseVersion = new DBVersion(SystemUtil.getMetacatVersion().getVersionString());

		} catch (SQLException sqle) {

			throw new AdminException("SQL error when running upgrade scripts: "