Java tutorial
/* This file is part of Cyclos (www.cyclos.org). A project of the Social Trade Organisation (www.socialtrade.org). Cyclos 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. Cyclos 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 Cyclos; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ package nl.strohalm.cyclos.setup.migrations.version3_5; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Arrays; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.Map; import nl.strohalm.cyclos.entities.customization.fields.PaymentCustomField; import nl.strohalm.cyclos.setup.UntraceableMigration; import nl.strohalm.cyclos.utils.JDBCWrapper; import org.apache.commons.lang.StringUtils; /** * Migrates loan custom fields into payment custom fields * @author luis */ @SuppressWarnings("deprecation") public class LoanCustomFieldMigration implements UntraceableMigration { public static void main(final String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); final Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/cyclos3_new", "root", ""); try { conn.setAutoCommit(false); final JDBCWrapper jdbc = new JDBCWrapper(conn); new LoanCustomFieldMigration().execute(jdbc); conn.commit(); } catch (final Exception e) { conn.rollback(); throw e; } finally { conn.close(); } } public void execute(final JDBCWrapper jdbc) throws SQLException { final ResultSet loanCustomFields = jdbc.query("select * from custom_fields where subclass = ?", "loan"); try { while (loanCustomFields.next()) { final long loanCustomFieldId = loanCustomFields.getLong("id"); // Map by transfer type the new field id final Map<Long, Long> loanCustomFieldMap = new HashMap<Long, Long>(); // Map by old custom field id the old / new custom field possible values final Map<Long, Long> possibleValuesMap = new HashMap<Long, Long>(); // For each loan type, create a custom field for it final ResultSet loanTransferTypes = jdbc .query("select id from transfer_types where loan_type is not null"); try { while (loanTransferTypes.next()) { final long transferTypeId = loanTransferTypes.getLong("id"); final Map<String, Object> values = new LinkedHashMap<String, Object>(); values.put("subclass", "pmt"); values.put("internal_name", loanCustomFields.getString("internal_name")); values.put("name", loanCustomFields.getString("name")); values.put("order_number", loanCustomFields.getString("order_number")); values.put("type", loanCustomFields.getString("type")); values.put("control", loanCustomFields.getString("control")); values.put("size", loanCustomFields.getString("size")); values.put("val_required", loanCustomFields.getBoolean("val_required")); values.put("val_unique", loanCustomFields.getBoolean("val_unique")); values.put("val_min_length", loanCustomFields.getString("val_min_length")); values.put("val_max_length", loanCustomFields.getString("val_max_length")); values.put("all_selected_label", loanCustomFields.getString("all_selected_label")); values.put("pattern", loanCustomFields.getString("pattern")); values.put("description", loanCustomFields.getString("description")); values.put("parent_id", loanCustomFields.getString("parent_id")); values.put("transfer_type_id", transferTypeId); values.put("payment_search_access", loanCustomFields.getBoolean("loan_show_in_search") ? PaymentCustomField.Access.BOTH_ACCOUNTS.getValue() : PaymentCustomField.Access.NONE.getValue()); values.put("payment_list_access", loanCustomFields.getBoolean("loan_show_in_payment_results") ? PaymentCustomField.Access.BOTH_ACCOUNTS.getValue() : PaymentCustomField.Access.NONE.getValue()); final String[] placeHolders = new String[values.size()]; Arrays.fill(placeHolders, "?"); jdbc.execute( "insert into custom_fields (" + StringUtils.join(values.keySet().iterator(), ',') + ") values (" + StringUtils.join(placeHolders, ',') + ")", values.values().toArray()); // Add the new id to the mapping final long newCustomFieldId = jdbc.readScalarAsLong("select last_insert_id()"); loanCustomFieldMap.put(transferTypeId, newCustomFieldId); // Insert the new the possible values final ResultSet possibleValues = jdbc.query( "select * from custom_field_possible_values where field_id = ?", loanCustomFieldId); try { while (possibleValues.next()) { final long oldPossibleValueId = possibleValues.getLong("id"); final String value = possibleValues.getString("value"); jdbc.execute( "insert into custom_field_possible_values (field_id, value) values (?, ?)", newCustomFieldId, value); final long newPossibleValueId = jdbc.readScalarAsLong("select last_insert_id()"); possibleValuesMap.put(oldPossibleValueId, newPossibleValueId); } } finally { JDBCWrapper.closeQuietly(possibleValues); } } } finally { JDBCWrapper.closeQuietly(loanTransferTypes); } // Replace the field values to the new field final ResultSet fieldValues = jdbc.query( "select fv.*, t.type_id as transfer_type_id from custom_field_values fv inner join loans l on fv.loan_id = l.id inner join transfers t on l.transfer_id = t.id where fv.field_id = ?", loanCustomFieldId); try { while (fieldValues.next()) { final long fieldValueId = fieldValues.getLong("id"); final long loanId = fieldValues.getLong("loan_id"); final long newCustomFieldId = loanCustomFieldMap .get(fieldValues.getLong("transfer_type_id")); final Long newPossibleValueId = possibleValuesMap .get(fieldValues.getLong("possible_value_id")); // Get the loan's transfer final long transferId = jdbc.readScalarAsLong("select transfer_id from loans where id=?", loanId); jdbc.execute( "update custom_field_values set subclass=?, loan_id=null, transfer_id=?, field_id=?, possible_value_id=? where id=?", "pmt", transferId, newCustomFieldId, newPossibleValueId, fieldValueId); } } finally { JDBCWrapper.closeQuietly(fieldValues); } // Remove the old possible values jdbc.execute("delete from custom_field_possible_values where field_id=?", loanCustomFieldId); // Remove the old custom field jdbc.execute("delete from custom_fields where id=?", loanCustomFieldId); } } finally { JDBCWrapper.closeQuietly(loanCustomFields); } // Drop the unused columns try { jdbc.execute("alter table custom_field_values drop foreign key FK8AE18A15F9B21025"); } catch (final Exception e) { // ignore } try { jdbc.execute("alter table custom_field_values drop column loan_id"); } catch (final Exception e) { // ignore } try { jdbc.execute("alter table custom_fields drop column loan_show_in_search"); } catch (final Exception e) { // ignore } try { jdbc.execute("alter table custom_fields drop column loan_show_in_payment_results"); } catch (final Exception e) { // ignore } } }