Metacat

package edu.ucsb.nceas.metacat.admin.upgrade;

/**

 *  '$RCSfile$'

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

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Matthew Jones

 *

 *   '$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

 */

import java.io.IOException;

import java.sql.Connection;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import edu.ucsb.nceas.metacat.admin.AdminException;

import edu.ucsb.nceas.metacat.properties.PropertyService;

import edu.ucsb.nceas.metacat.shared.ServiceException;

import edu.ucsb.nceas.utilities.PropertyNotFoundException;

import edu.ucsb.nceas.utilities.SortedProperties;

/**

 * Optionally upgrade all of the existing identifiers in the tables to be official

 * DOIs that are assigned by a DOI registration authority.  This class first checks

 * if GUIDs should be converted to DOIs, and if so, updates all relevant

 * tables and resources in Metacat, and then registers each of the newly minted

 * identifiers with the EZID service. You must have an EZID account for this to

 * work properly.

 * 

 * Whether or not identifiers should be converted is determined by several metacat.properties.

 * First, only upgrade if 'guid.assignGUIDs' is true.  Next, for each server registered in 

 * the xml_replication table, only convert the documents belonging to that server if

 * there is a corresponding property that gives the 'shoulder' or 'prefix'' to be used to

 * convert existing IDs to GUIDs.  For example, if servers 1 & 2 are registered in 

 * xml_replication, then only convert the IDs for server 1 to GUIDs if the

 * property 'guid.ezid.doishoulder.1' defines a valid doi shoulder.

 * 

 * @author jones

 */

public class GenerateGlobalIdentifiers implements UpgradeUtilityInterface {

	private static Log log = LogFactory.getLog(GenerateGlobalIdentifiers.class);

    private String driver = null;

    private String url = null;

    private String user = null;

    private String password = null;

//    private String eziduser = null;

//    private String ezidPassword = null;

    /**

     * The main upgrade() procedure, which first upgrades identifiers in the database,

     * then registers these with EZID.

     */

    public boolean upgrade() throws AdminException {

        boolean success = false;

        log.debug("Upgrading identifiers to DOIs");

        // Check if assignDOIs is true, and if EZID is configured

        boolean shouldUpgrade = false;

        try {

            shouldUpgrade = new Boolean(PropertyService.getProperty("guid.assignGUIDs")).booleanValue();

            if (shouldUpgrade) {

                log.debug("Upgrading identifiers to DOIs");

                success = updateIdentifierReferences();                

            }

        } catch (PropertyNotFoundException e) {

            // When the guid properties are not found, do nothing

            shouldUpgrade = false;

            success = true;

        }

    	return success;

    }

    /**

     * Loop through the servers registered in this instance of Metacat, and for

     * each server determine if we should convert to GUIDs for the documents associated

     * with that server.  If so, convert the identifier table to use a GUID for all

     * documents for that server found in xml_documents and xml_replication.

     * @return true if all conversions succeed

     * @throws AdminException

     */

    private boolean updateIdentifierReferences() throws AdminException {

        log.debug("Updating identifier references...");

        try {

            driver = PropertyService.getProperty("database.driver");

            url = PropertyService.getProperty("database.connectionURI");

            user = PropertyService.getProperty("database.user");

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

//            eziduser = PropertyService.getProperty("guid.ezid.username");

//            ezidPassword = PropertyService.getProperty("guid.ezid.password");

        } catch (PropertyNotFoundException e) {

            String msg = "PropertyNotFound while trying to convert identifiers: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        }

        // Create a connection, and start a transaction for which FK constraint checking is deferred

        Connection con = openDatabaseConnection();

        deferConstraints(con);

        // Copy the identifier.guid column for later use

        createDuplicateGUIDColumn(con);

        // Look up the maximum server number from the database

        int maxServerId = lookupMaxServers(con);

        // Loop across each replica server, checking if it is to have DOIs assigned

        // This is determined by the presence of a shoulder in the configuration file for

        // that server number

        for (int server = 1; server <= maxServerId; server++) {

            // Get the server's shoulder, or skip if not found

            try {

                String shoulder = PropertyService.getProperty("guid.ezid.doishoulder." + server);

                log.debug("Processing shoulder for server " + server + ": " + shoulder);

                // Update identifiers table (but only for local documents, not replicated documents)

                convertIdentifierTable(con, server, shoulder);

            } catch (PropertyNotFoundException e) {

                // The shoulder was not found for this server, so we just skip it

                log.debug("No shoulder found: " + e.getMessage());

            }

        }

        // Convert other tables once, after the identifier table conversion has established the new

        // global identifiers to be used

        // This incudes: xml_access table, including accessfileid field, systemmetadata, 

        // systemmetadatareplicationpolicy,and systemmetadatareplicationstatus tables

        convertIdentifierField(con, "xml_access", "guid");

        convertIdentifierField(con, "xml_access", "accessfileid");

        convertIdentifierField(con, "systemmetadata", "guid");

        convertIdentifierField(con, "systemmetadatareplicationpolicy", "guid");

        convertIdentifierField(con, "systemmetadatareplicationstatus", "guid");

        // Now clean up the temporary column we created, but note this makes it much

        // harder to revert our changes

        dropDuplicateGUIDColumn(con);

        commitAndCloseConnection(con);

        /*      

        // The following SQL commands will revert the changes made by this script as long as the identifier.old_guid column is intact

        start transaction;

        update systemmetadatareplicationstatus set guid = id.old_guid FROM identifier id WHERE systemmetadatareplicationstatus.guid = id.guid;

        update systemmetadatareplicationpolicy set guid = id.old_guid FROM identifier id WHERE systemmetadatareplicationpolicy.guid = id.guid;

        update systemmetadata set guid = id.old_guid FROM identifier id WHERE systemmetadata.guid = id.guid;

        update xml_access set accessfileid = id.old_guid FROM identifier id WHERE xml_access.accessfileid = id.guid;

        update xml_access set guid = id.old_guid FROM identifier id WHERE xml_access.guid = id.guid;

        update identifier set guid = old_guid;

        alter table identifier drop column old_guid;

        commit;

        */

        return true;

    }

    /**

     * Open a JDBC connection that will be used for all of the subsequent methods that

     * access or modify the database.  All changes are done in one transaction, so need

     * to be done using a single connection.

     * @return the Connection created

     * @throws AdminException

     */

    private Connection openDatabaseConnection() throws AdminException {

        Connection con = null;

            try {

                Driver d = (Driver) Class.forName(driver).newInstance();

                DriverManager.registerDriver(d);

                con = DriverManager.getConnection(url, user, password);

            } catch (InstantiationException e) {

                String msg = "InstantiationException updating creating DB connection: " + e.getMessage();

                log.error(msg, e);

                throw new AdminException(msg);

            } catch (IllegalAccessException e) {

                String msg = "IllegalAccessException updating creating DB connection: " + e.getMessage();

                log.error(msg, e);

                throw new AdminException(msg);

            } catch (ClassNotFoundException e) {

                String msg = "ClassNotFoundException updating creating DB connection: " + e.getMessage();

                log.error(msg, e);

                throw new AdminException(msg);

            } catch (SQLException e) {

                String msg = "SQLException updating creating DB connection: " + e.getMessage();

                log.error(msg, e);

                throw new AdminException(msg);

            }

            return con;

    }

    /**

     * Start a transaction on the given connection, and defer all constraints so

     * that changes to foreign keys will not be checked until after all of the 

     * identifiers have been updated in all tables.  Without this, we would get

     * FK constraint violations as we changed the tables.

     * @param con the connection to the database

     * @throws AdminException

     */

    private void deferConstraints(Connection con) throws AdminException {

        try {

            con.setAutoCommit(false);

            Statement sqlStatement = con.createStatement();

            boolean hasResults = sqlStatement.execute("SET CONSTRAINTS ALL DEFERRED;");

            log.debug("Constraints Deferred.");

        } catch (SQLException e) {

            String msg = "SQLException while disabling constraints: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        }

    }

    /**

     * Temporarily save the current identifier.guid in a backup column (old_guid), to be used

     * for updating additional tables. 

     * @param con the connection to the database with an open transaction

     * @throws AdminException

     */

    private void createDuplicateGUIDColumn(Connection con) throws AdminException {

        try {

            Statement sqlStatement = con.createStatement();

            boolean hasResults = sqlStatement.execute("ALTER TABLE identifier ADD COLUMN old_guid TEXT;");

            log.debug("old_guid column added.");

            sqlStatement = con.createStatement();

            hasResults = sqlStatement.execute("UPDATE identifier SET old_guid = guid;");

            log.debug("Copied original identifiers to old_guid column.");

        } catch (SQLException e) {

            String msg = "SQLException while duplicating GUID column: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        }        

    }

    /**

     * Drop the temporary GUID backup column (old_guid) once upgrading identifiers

     * is complete. 

     * @param con the connection to the database with an open transaction

     * @throws AdminException

     */

    private void dropDuplicateGUIDColumn(Connection con) throws AdminException {

        try {

            Statement sqlStatement = con.createStatement();

            boolean hasResults = sqlStatement.execute("ALTER TABLE identifier DROP COLUMN old_guid;");

            log.debug("old_guid column dropped.");

        } catch (SQLException e) {

            String msg = "SQLException dropping GUID column: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        }        

    }

    /**

     * Query the database to determine which servers are registered and can be

     * inspected for potential identifier upgrades.

     * @param con the connection to the database with an open transaction

     * @throws AdminException

     */

    private int lookupMaxServers(Connection con) throws AdminException {

        int maxServers = 0;

        try {

            Statement sqlStatement = con.createStatement();

            boolean hasResults = sqlStatement.execute("SELECT max(serverid) AS maxserverid FROM xml_replication;");

            if (hasResults) {

                ResultSet rs = sqlStatement.getResultSet();

                if (rs.next()) {

                    maxServers = rs.getInt(1);

                    log.debug("MaxServerID: " + maxServers);

                } else {

                    String msg = "Could not determine max serverid; database query cursor had zero rows.";

                    log.error(msg);

                    throw new AdminException(msg);

                }

            } else {

                String msg = "Could not determine max serverid; database query failed to return results.";

                log.error(msg);

                throw new AdminException(msg);

            }

        } catch (SQLException e) {

            String msg = "SQLException while looking up serverId: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        }        

        return maxServers;

    }

    /**

     * Convert identifiers to DOI GUIDs in the identifier table for any documents found in

     * xml_documents or xml_revisions that reside on a server for which a DOI shoulder prefix

     * has been assigned.

     * 

     * @param con the connection to the database with an open transaction

     * @param server the id of the server whose documents are to be converted

     * @param shoulder the DOI shoulder to be used for converting

     * @return true if conversion is successful

     * @throws AdminException

     */

    private boolean convertIdentifierTable(Connection con, int server, String shoulder) throws AdminException {

        PreparedStatement pstmt = null;

        try {

            String sql_1 =

                "UPDATE identifier " + 

                "SET    guid = p.doi " + 

                "FROM (" +

                       "SELECT  guid, ? || guid as doi " + 

                       "FROM    identifier id2, ";

            String sql_2 =

                " xd " +

                       "WHERE   id2.docid = xd.docid AND id2.rev = xd.rev " +   

                       "AND xd.server_location = ? " +

                     ") p " +

                "WHERE identifier.guid = p.guid;";         

            // Convert identifiers found in xml_documents

            pstmt = con.prepareStatement(sql_1 + "xml_documents" + sql_2);

            pstmt.setString(1, shoulder);

            pstmt.setInt(2, server);

            pstmt.execute();

            // Convert identifiers found in xml_revisions

            pstmt = con.prepareStatement(sql_1 + "xml_revisions" + sql_2);

            pstmt.setString(1, shoulder);

            pstmt.setInt(2, server);

            pstmt.execute();

            log.debug("Finished shoulder for server " + server + ": " + shoulder);

        } catch (SQLException e) {

            String msg = "SQLException updating identifier table: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        }

        return true;

    }

    /**

     * Convert the given field in the given table to use the new identifier.guid that

     * has been assigned.

     * 

     * @param con the connection to the database with an open transaction

     * @param table the name of the table with identifiers to be converted

     * @param field the name of the field containing identifiers to be converted

     * @return true if conversion was successful

     * @throws AdminException

     */

    private boolean convertIdentifierField(Connection con, String table, String field) throws AdminException {

        PreparedStatement pstmt = null;

        try {

            String sql_1 =

                "UPDATE " + table + " " +

                "SET " + field + " = id.guid " + 

                "FROM identifier id " +

                "WHERE " + table + "."+ field + " = id.old_guid;";

            // Convert identifiers found in the given table and field

            pstmt = con.prepareStatement(sql_1);

            pstmt.execute();

            log.debug("Finished converting table " + table + " (" + field +")" );

        } catch (SQLException e) {

            String msg = "SQLException while converting identifier field: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        }

        return true;

    }

    /**

     * Commit the current transaction on the connection, and close the connection.

     * @param con the connection to the database with an open transaction

     * @return true if the connection is successfully closed

     * @throws AdminException

     */

    private boolean commitAndCloseConnection(Connection con) throws AdminException {

        try {

            log.debug("Committing and closing connection.");

            con.commit();

            con.close();

        } catch (SQLException e) {

            String msg = "SQLException while commiting and closing connection: " + e.getMessage();

            log.error(msg, e);

            throw new AdminException(msg);

        } 

        return true;

    }

    /**

     * Main method, solely for testing.  Not used in ordinary operation.

     */

    public static void main(String [] ags){

        try {

        	// set up the properties based on the test/deployed configuration of the workspace

        	SortedProperties testProperties = 

				new SortedProperties("test/test.properties");

			testProperties.load();

			String metacatContextDir = testProperties.getProperty("metacat.contextDir");

			PropertyService.getInstance(metacatContextDir + "/WEB-INF");

			// now run it

            GenerateGlobalIdentifiers upgrader = new GenerateGlobalIdentifiers();

	        upgrader.upgrade();

        } catch (IOException e) {

            e.printStackTrace();

        } catch (PropertyNotFoundException e) {

            e.printStackTrace();

        } catch (ServiceException e) {

            e.printStackTrace();

        } catch (AdminException e) {

            e.printStackTrace();

        }

    }

}