Metacat

application.metacatVersion=2.0.0

application.metacatReleaseInfo=-1

############# Global Identifiers Assignment Section ######################

guid.assignGUIDs=false

guid.ezid.username=apitest

guid.ezid.password=apitest

guid.ezid.doishoulder.1=doi:10.5072/FK2/KNB/

guid.ezid.doishoulder.2=doi:10.5072/FK2/ESA/

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

		FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE

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();

                // TODO: Query for local GUID identifiers & metadata, and for each 

                //       register with EZID; in a separate thread; including relevant metadata

            }

        } 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();

        }

    }

}

        systemMetadataUpgrade.upgrade();  // TODO:  why aren't we checking success return value here?

        // Optionally upgrade GUIDs to use DOI syntax if configured

        GenerateGlobalIdentifiers globalIDUpgrade = new GenerateGlobalIdentifiers();

        success = success && globalIDUpgrade.upgrade();