Java tutorial
/********************************************************************************** * $URL: https://source.sakaiproject.org/svn/kernel/trunk/kernel-util/src/main/java/org/sakaiproject/util/conversion/SchemaConversionController.java $ * $Id: SchemaConversionController.java 101634 2011-12-12 16:44:33Z aaronz@vt.edu $ *********************************************************************************** * * Copyright (c) 2003, 2004, 2005, 2006, 2007, 2008 Sakai Foundation * * Licensed under the Educational Community License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.opensource.org/licenses/ECL-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * **********************************************************************************/ package org.sakaiproject.util.conversion; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; /** * @author ieb */ public class SchemaConversionController { private boolean reportErrorsInTable = false; private static final Log log = LogFactory.getLog(SchemaConversionController.class); private long nrecords = 0; public void init(DataSource datasource, SchemaConversionHandler convert, SchemaConversionDriver driver) throws SchemaConversionException { // we need some way of identifying those records that have not been // convertd. // 1. Create a register table to map progress. Connection connection = null; try { connection = datasource.getConnection(); addColumns(connection, convert, driver); createRegisterTable(connection, convert, driver); createErrorTable(connection, convert, driver); connection.commit(); } catch (Exception e) { log.error("Failed to perform migration setup ", e); try { connection.rollback(); log.error("Rollback Sucessfull ", e); } catch (Exception ex) { log.error("Rollback Failed ", e); } throw new SchemaConversionException( "Schema Conversion has been aborted due to earlier errors, please investigate "); } finally { try { connection.close(); } catch (Exception ex) { log.debug("exception closing connection " + ex); } } } private void createErrorTable(Connection connection, SchemaConversionHandler convert, SchemaConversionDriver driver) { String errorReportSql = driver.getErrorReportSql(); String verifyErrorTable = driver.getVerifyErrorTable(); String createErrorTable = driver.getCreateErrorTable(); if (createErrorTable != null && errorReportSql != null && verifyErrorTable != null) { PreparedStatement verifyTable = null; PreparedStatement createTable = null; ResultSet rs = null; try { // reportErrorsInTable should be true if table already exists or is created verifyTable = connection.prepareStatement(verifyErrorTable); rs = verifyTable.executeQuery(); boolean tableExists = rs.next(); if (!tableExists) { createTable = connection.prepareStatement(createErrorTable); createTable.execute(); } reportErrorsInTable = true; } catch (SQLException e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { } } if (verifyTable != null) { try { verifyTable.close(); } catch (SQLException e) { } } if (createTable != null) { try { createTable.close(); } catch (SQLException e) { } } } //END Finally } //END if } public boolean migrate(DataSource datasource, SchemaConversionHandler convert, SchemaConversionDriver driver) throws SchemaConversionException { // issues: // Data size bigger than max size for this type? // Failure may cause rest of set to fail? boolean alldone = false; Connection connection = null; PreparedStatement selectNextBatch = null; PreparedStatement markNextBatch = null; PreparedStatement completeNextBatch = null; PreparedStatement selectRecord = null; PreparedStatement selectValidateRecord = null; PreparedStatement updateRecord = null; PreparedStatement reportError = null; ResultSet rs = null; try { connection = datasource.getConnection(); connection.setAutoCommit(false); selectNextBatch = connection.prepareStatement(driver.getSelectNextBatch()); markNextBatch = connection.prepareStatement(driver.getMarkNextBatch()); completeNextBatch = connection.prepareStatement(driver.getCompleteNextBatch()); String selectRecordStr = driver.getSelectRecord(); selectRecord = connection.prepareStatement(selectRecordStr); selectValidateRecord = connection.prepareStatement(driver.getSelectValidateRecord()); updateRecord = connection.prepareStatement(driver.getUpdateRecord()); if (reportErrorsInTable) { reportError = connection.prepareStatement(driver.getErrorReportSql()); } // log.info(" +++ updateRecord == " + driver.getUpdateRecord()); // 2. select x at a time rs = selectNextBatch.executeQuery(); List<String> l = new ArrayList<String>(); while (rs.next()) { l.add(rs.getString(1)); } rs.close(); log.info("Migrating " + l.size() + " records of " + nrecords); for (String id : l) { markNextBatch.clearParameters(); markNextBatch.clearWarnings(); markNextBatch.setString(1, id); if (markNextBatch.executeUpdate() != 1) { log.warn(" --> Failed to mark id [" + id + "][" + id.length() + "] for processing "); insertErrorReport(reportError, id, driver.getHandler(), "Unable to mark this record for processing"); } } int count = 1; for (String id : l) { selectRecord.clearParameters(); selectRecord.setString(1, id); rs = selectRecord.executeQuery(); Object source = null; if (rs.next()) { source = convert.getSource(id, rs); } else { log.warn(" --> Result-set is empty for id: " + id + " [" + count + " of " + l.size() + "]"); insertErrorReport(reportError, id, driver.getHandler(), "Result set empty getting source"); } rs.close(); if (source == null) { log.warn(" --> Source is null for id: " + id + " [" + count + " of " + l.size() + "]"); insertErrorReport(reportError, id, driver.getHandler(), "Source null"); } else { try { updateRecord.clearParameters(); if (convert.convertSource(id, source, updateRecord)) { if (updateRecord.executeUpdate() == 1) { selectValidateRecord.clearParameters(); selectValidateRecord.setString(1, id); rs = selectValidateRecord.executeQuery(); Object result = null; if (rs.next()) { result = convert.getValidateSource(id, rs); } convert.validate(id, source, result); } else { log.warn(" --> Failed to update record " + id + " [" + count + " of " + l.size() + "]"); insertErrorReport(reportError, id, driver.getHandler(), "Failed to update record"); } } else { log.warn(" --> Did not update record " + id + " [" + count + " of " + l.size() + "]"); insertErrorReport(reportError, id, driver.getHandler(), "Failed to write update to db"); } rs.close(); } catch (SQLException e) { String msg = " --> Failure converting or validating item " + id + " [" + count + " of " + l.size() + "] \n"; insertErrorReport(reportError, id, driver.getHandler(), "Exception while updating, converting or verifying item"); SQLWarning warnings = updateRecord.getWarnings(); while (warnings != null) { msg += "\t\t\t" + warnings.getErrorCode() + "\t" + warnings.getMessage() + "\n"; warnings = warnings.getNextWarning(); } log.warn(msg, e); updateRecord.clearWarnings(); updateRecord.clearParameters(); } } completeNextBatch.clearParameters(); completeNextBatch.setString(1, id); if (completeNextBatch.executeUpdate() != 1) { log.warn(" --> Failed to mark id " + id + " for processing [" + count + " of " + l.size() + "]"); insertErrorReport(reportError, id, driver.getHandler(), "Unable to complete next batch"); } count++; } if (l.size() == 0) { dropRegisterTable(connection, convert, driver); alldone = true; } connection.commit(); nrecords -= l.size(); } catch (Exception e) { log.error("Failed to perform migration ", e); try { connection.rollback(); log.error(" ==> Rollback Sucessful ", e); } catch (Exception ex) { log.error(" ==> Rollback Failed ", e); } throw new SchemaConversionException( "Schema Conversion has been aborted due to earlier errors, please investigate "); } finally { try { rs.close(); } catch (Exception ex) { log.debug("exception closing rs " + ex); } try { selectNextBatch.close(); } catch (Exception ex) { log.debug("exception closing selectNextBatch " + ex); } try { markNextBatch.close(); } catch (Exception ex) { log.debug("exception closing markNextBatch " + ex); } try { completeNextBatch.close(); } catch (Exception ex) { log.debug("exception closing completeNextBatch " + ex); } try { selectRecord.close(); } catch (Exception ex) { log.debug("exception closing selectRecord " + ex); } try { selectValidateRecord.close(); } catch (Exception ex) { log.debug("exception closing selectValidateRecord " + ex); } try { updateRecord.close(); } catch (Exception ex) { log.debug("exception closing updateRecord " + ex); } if (reportError != null) { try { reportError.close(); } catch (Exception ex) { log.debug("exception closing reportError " + ex); } } try { connection.close(); } catch (Exception ex) { log.debug("Exception closing connection " + ex); } } return !alldone; } private void insertErrorReport(PreparedStatement reportError, String id, String handler, String description) { if (reportError != null) { try { reportError.clearParameters(); reportError.setString(1, id); reportError.setString(2, handler); reportError.setString(3, description); reportError.execute(); } catch (SQLException e) { log.warn("Unable to insert error report [" + id + " " + handler + " \"" + description + "\" " + e); } } } /** * @throws SQLException */ private void dropRegisterTable(Connection connection, SchemaConversionHandler convert, SchemaConversionDriver driver) throws SQLException { Statement stmt = null; try { stmt = connection.createStatement(); String[] sql = driver.getDropMigrateTable(); if (sql == null) { log.info("No SQL provided to drop register table"); } else { for (String statement : sql) { if (statement == null || statement.trim().equals("")) { log.info("Encountered null SQL while dropping register table: " + statement); } else { try { log.info("Cleaning up: " + statement); stmt.execute(statement); } catch (Exception e) { log.info("Unable to execute SQL while dropping register table: " + statement); } } } log.info("Done cleaning up conversion step"); } } finally { try { stmt.close(); } catch (Exception ex) { log.debug("exception closing stmt " + ex); } } } private void addColumns(Connection connection, SchemaConversionHandler convert, SchemaConversionDriver driver) throws SQLException { String[] names = driver.getNewColumnNames(); String[] types = driver.getNewColumnTypes(); String[] qualifiers = driver.getNewColumnQualifiers(); if (names == null) { // do nothing } else { for (int i = 0; i < names.length; i++) { if (names[i] == null || names[i].trim().equals("")) { continue; } Statement stmt = null; ResultSet rs = null; try { stmt = connection.createStatement(); String sql = driver.getTestNewColumn(names[i]); rs = stmt.executeQuery(sql); if (!rs.next()) { stmt = connection.createStatement(); sql = driver.getAddNewColumn(names[i], types[i], qualifiers[i]); stmt.execute(sql); log.info("added column: " + sql); } else { log.info("column exists: " + sql); } } finally { try { stmt.close(); } catch (Exception ex) { log.debug("exception closing smt " + ex); } } } } } /** * @param connection * @param convert * @throws SQLException */ private void createRegisterTable(Connection connection, SchemaConversionHandler convert, SchemaConversionDriver driver) throws SQLException { Statement stmt = null; ResultSet rs = null; try { stmt = connection.createStatement(); nrecords = 0; try { // select count(*) from content_migrate; String sql = driver.getCheckMigrateTable(); rs = stmt.executeQuery(sql); if (rs.next()) { nrecords = rs.getLong(1); } } catch (SQLException sqle) { String[] sql = driver.getCreateMigrateTable(); if (sql == null) { log.info("No SQL provided to create register table"); } else { for (String statement : sql) { if (statement == null || statement.trim().equals("")) { log.info("Encountered null SQL while creating register table: " + statement); } else { try { stmt.execute(statement); log.info("Created register table: " + statement); } catch (Exception e) { log.info("Unable to execute SQL while creating register table: " + statement); } } } } } finally { try { rs.close(); } catch (Exception ex) { log.debug("exception closing rs " + ex); } } if (nrecords == 0) { String sql = driver.getPopulateMigrateTable(); if (sql == null) { log.info("No SQL to populate register table"); } else { log.info("Populating register table: " + sql); int count = stmt.executeUpdate(sql); log.info("Inserted " + count + " rows into register table"); } } try { // select count(*) from content_migrate; String sql = driver.getCheckMigrateTable(); rs = stmt.executeQuery(sql); if (rs.next()) { nrecords = rs.getLong(1); } log.info("Counted " + nrecords + " rows in register table"); } catch (Exception e) { log.debug("Unable to verify number of rows in register table"); } finally { try { rs.close(); } catch (Exception ex) { log.debug("exception closing rs " + ex); } } } finally { try { stmt.close(); } catch (Exception ex) { log.debug("exception closing stmt " + ex); } } } }