This is an automated email from the git hooks/post-receive script. New commit to branch develop in repository observe. See http://git.codelutin.com/observe.git commit 983f525242e6b817fdfcd4665f94c925c6ff624c Author: Tony CHEMIT <chemit@codelutin.com> Date: Tue May 26 16:22:20 2015 +0200 fix migration 4.0-RC6 (refs #7145) --- .../entities/migration/H2DataSourceMigration.java | 46 +++++++++++ .../entities/migration/PGDataSourceMigration.java | 88 ++++++++++++++++++++++ .../DataSourceMigrationForVersion_4_0_RC6.java | 22 +++++- .../db/4.0-RC6/fix-gearUseFeatures-topiaIds-H2.sql | 2 +- .../db/4.0-RC6/fix-gearUseFeatures-topiaIds-PG.sql | 2 +- 5 files changed, 157 insertions(+), 3 deletions(-) diff --git a/observe-entities/src/main/java/fr/ird/observe/entities/migration/H2DataSourceMigration.java b/observe-entities/src/main/java/fr/ird/observe/entities/migration/H2DataSourceMigration.java index 18017a0..cf125fb 100644 --- a/observe-entities/src/main/java/fr/ird/observe/entities/migration/H2DataSourceMigration.java +++ b/observe-entities/src/main/java/fr/ird/observe/entities/migration/H2DataSourceMigration.java @@ -183,6 +183,41 @@ public class H2DataSourceMigration extends AbstractDataSourceMigration { return result; } + public static String getForeignKeyConstraintName(TopiaContextImplementor tx, final String schemaName, final String tableName, final String columnName) throws TopiaException { + + final List<String> result = new ArrayList<String>(); + + tx.getHibernate().doWork(new Work() { + + @Override + public void execute(Connection connection) throws SQLException { + String sql = String.format("SELECT DISTINCT constraint_name FROM INFORMATION_SCHEMA.Constraints " + + "WHERE CONSTRAINT_TYPE='REFERENTIAL' " + + "AND CONSTRAINT_SCHEMA='%S' " + + "AND TABLE_NAME='%S' " + + "AND COLUMN_LIST='%S';" , schemaName, tableName, columnName); + PreparedStatement ps = connection.prepareStatement(sql); + try { + ResultSet set = ps.executeQuery(); + while (set.next()) { + String constraintName = set.getString(1); + result.add(constraintName); + } + } catch (Exception e) { + throw new SQLException("Could not obtain constraints for table " + tableName, e); + } finally { + ps.close(); + } + } + }); + + if (result.isEmpty()) { + throw new IllegalStateException("Could not find constaint name for "+schemaName+"."+tableName+"."+columnName); + } + return result.get(0); + + } + public static Set<String> getUniqueKeyConstraintNames(TopiaContextImplementor tx, final String tableName) throws TopiaException { @@ -223,6 +258,17 @@ public class H2DataSourceMigration extends AbstractDataSourceMigration { } + public static void removeFK(TopiaContextImplementor tx, String schemaName, String tableName,String columnName, List<String> queries) throws TopiaException { + + // Get fk constraints + String contrainstName = getForeignKeyConstraintName(tx, schemaName, tableName, columnName); + + // remove constraints + queries.add(String.format("ALTER TABLE %s.%s DROP CONSTRAINT %s;", schemaName, tableName, contrainstName)); + + } + + public static void removeUK(TopiaContextImplementor tx, String tableName, List<String> queries) throws TopiaException { // Get uk constraints diff --git a/observe-entities/src/main/java/fr/ird/observe/entities/migration/PGDataSourceMigration.java b/observe-entities/src/main/java/fr/ird/observe/entities/migration/PGDataSourceMigration.java index f4742cb..99a233b 100644 --- a/observe-entities/src/main/java/fr/ird/observe/entities/migration/PGDataSourceMigration.java +++ b/observe-entities/src/main/java/fr/ird/observe/entities/migration/PGDataSourceMigration.java @@ -227,6 +227,84 @@ public class PGDataSourceMigration extends AbstractDataSourceMigration { } + public static String getForeignKeyConstraintName(TopiaContextImplementor tx, final String schemaName, final String tableName, final String columnName) throws TopiaException { + + final List<String> result = new ArrayList<String>(); + + tx.getHibernate().doWork(new Work() { + + @Override + public void execute(Connection connection) throws SQLException { + + + // get table oid + String oid = null; + + String sqlOid = "select oid from pg_class where relnamespace = (select oid from pg_catalog.pg_namespace where nspname=?) AND relname=?;"; + { + PreparedStatement ps = connection.prepareStatement(sqlOid); + ps.setString(1, schemaName.toLowerCase()); + ps.setString(2, tableName.toLowerCase()); + try { + ResultSet set = ps.executeQuery(); + if (set.next()) { + oid = set.getString(1); + if (log.isDebugEnabled()) + log.debug("found table oid " + tableName + ": " + oid); + } + } catch (Exception e) { + throw new SQLException("Could not obtain oid for table" + tableName, e); + } finally { + ps.close(); + } + } + + // get attribute num + String attNumSql = "select attnum from pg_attribute where attrelid=? AND attname =?"; + String attNum; + { + PreparedStatement ps = connection.prepareStatement(attNumSql); + ps.setInt(1, Integer.valueOf(oid)); + ps.setString(2, columnName.toLowerCase()); + try { + ResultSet set = ps.executeQuery(); + if (set.next()) { + attNum = set.getString(1); + if (log.isDebugEnabled()) + log.debug("found attribute " + columnName + " attNum : " + attNum); + } + } catch (Exception e) { + throw new SQLException("Could not obtain attNum for column" + columnName, e); + } finally { + ps.close(); + } + } + + String sql = "SELECT DISTINCT conname FROM pg_constraint WHERE contype='f' AND conrelid = ? AND conkey = '{\" + attNum + \"}';"; + PreparedStatement ps = connection.prepareStatement(sql); + ps.setInt(1, Integer.valueOf(oid)); + + try { + ResultSet set = ps.executeQuery(); + while (set.next()) { + String constraintName = set.getString(1); + result.add(constraintName); + } + } catch (Exception e) { + throw new SQLException("Could not obtain constraints for table " + tableName, e); + } finally { + ps.close(); + } + } + }); + + if (result.isEmpty()) { + throw new IllegalStateException("Could not find constaint name for "+schemaName+"."+tableName+"."+columnName); + } + return result.get(0); + + } + public static Set<String> getUniqueKeyConstraintNames(TopiaContextImplementor tx, final String tableName) throws TopiaException { final Set<String> result = new HashSet<String>(); @@ -266,6 +344,16 @@ public class PGDataSourceMigration extends AbstractDataSourceMigration { } + public static void removeFK(TopiaContextImplementor tx, String schemaName, String tableName,String columnName, List<String> queries) throws TopiaException { + + // Get fk constraints + String contrainstName = getForeignKeyConstraintName(tx, schemaName, tableName, columnName); + + // remove constraints + queries.add(String.format("ALTER TABLE %s.%s DROP CONSTRAINT %s;", schemaName, tableName, contrainstName)); + + } + public static void removeUK(TopiaContextImplementor tx, String tableName, List<String> queries) throws TopiaException { // Get uk constraints diff --git a/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_RC6.java b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_RC6.java index 7cd1a59..9b23014 100644 --- a/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_RC6.java +++ b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_RC6.java @@ -37,29 +37,42 @@ import java.util.List; * @author Tony Chemit - chemit@codelutin.com * @since 4.0-RC6 */ -public class DataSourceMigrationForVersion_4_0_RC6 extends AbstractObserveMigrationCallBack { +public abstract class DataSourceMigrationForVersion_4_0_RC6 extends AbstractObserveMigrationCallBack { public DataSourceMigrationForVersion_4_0_RC6(AbstractDataSourceMigration callBack, String scriptSuffix) { super(AbstractDataSourceMigration.V_4_0_RC6, callBack, scriptSuffix); } + protected abstract void removeForeignKeyOnGearFseFeaturesMeasurementSeine(TopiaContextImplementor tx, List<String> queries); + @Override protected void prepareMigrationScript(TopiaContextImplementor tx, List<String> queries, boolean showSql, boolean showProgression) throws TopiaException { + // See https://forge.codelutin.com/issues/7145 + removeForeignKeyOnGearFseFeaturesMeasurementSeine(tx, queries); + // See https://forge.codelutin.com/issues/7142 addScript("fix-gearUseFeatures-topiaIds", queries); } + public static class H2DataSourceMigrationForVersion extends DataSourceMigrationForVersion_4_0_RC6 { public H2DataSourceMigrationForVersion(AbstractDataSourceMigration callBack) { super(callBack, H2DataSourceMigration.TYPE); } + @Override + protected void removeForeignKeyOnGearFseFeaturesMeasurementSeine(TopiaContextImplementor tx, List<String> queries) { + + H2DataSourceMigration.removeFK(tx, "observe_seine", "GEARUSEFEATURESMEASUREMENT", "GEARUSEFEATURES", queries); + + } + } public static class PGDataSourceMigrationForVersion extends DataSourceMigrationForVersion_4_0_RC6 { @@ -68,5 +81,12 @@ public class DataSourceMigrationForVersion_4_0_RC6 extends AbstractObserveMigrat super(callBack, PGDataSourceMigration.TYPE); } + @Override + protected void removeForeignKeyOnGearFseFeaturesMeasurementSeine(TopiaContextImplementor tx, List<String> queries) { + + PGDataSourceMigration.removeFK(tx, "observe_seine", "GEARUSEFEATURESMEASUREMENT", "GEARUSEFEATURES", queries); + + } + } } diff --git a/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-H2.sql b/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-H2.sql index f3be214..37968f3 100644 --- a/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-H2.sql +++ b/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-H2.sql @@ -20,7 +20,7 @@ -- #L% --- UPDATE OBSERVE_SEINE.gearusefeaturesmeasurement SET topiaid = replace(topiaid, 'GearUseFeaturesMeasurement#', 'GearUseFeaturesMeasurementSeine#'); -ALTER TABLE OBSERVE_SEINE.gearusefeaturesmeasurement DROP CONSTRAINT fk_gearusefeaturesmeasurement_gearusefeatures; +--ALTER TABLE OBSERVE_SEINE.gearusefeaturesmeasurement DROP CONSTRAINT fk_gearusefeaturesmeasurement_gearusefeatures; UPDATE OBSERVE_SEINE.gearusefeaturesmeasurement SET gearUseFeatures = replace(gearUseFeatures, 'GearUseFeatures#', 'GearUseFeaturesSeine#'); UPDATE OBSERVE_SEINE.gearusefeatures SET topiaid = replace(topiaid, 'GearUseFeatures#', 'GearUseFeaturesSeine#'); ALTER TABLE OBSERVE_SEINE.GEARUSEFEATURESMEASUREMENT ADD CONSTRAINT fk_gearusefeaturesmeasurement_gearusefeatures FOREIGN KEY(gearUseFeatures) REFERENCES OBSERVE_SEINE.GEARUSEFEATURES(topiaid); \ No newline at end of file diff --git a/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-PG.sql b/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-PG.sql index f3be214..37968f3 100644 --- a/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-PG.sql +++ b/observe-entities/src/main/resources/db/4.0-RC6/fix-gearUseFeatures-topiaIds-PG.sql @@ -20,7 +20,7 @@ -- #L% --- UPDATE OBSERVE_SEINE.gearusefeaturesmeasurement SET topiaid = replace(topiaid, 'GearUseFeaturesMeasurement#', 'GearUseFeaturesMeasurementSeine#'); -ALTER TABLE OBSERVE_SEINE.gearusefeaturesmeasurement DROP CONSTRAINT fk_gearusefeaturesmeasurement_gearusefeatures; +--ALTER TABLE OBSERVE_SEINE.gearusefeaturesmeasurement DROP CONSTRAINT fk_gearusefeaturesmeasurement_gearusefeatures; UPDATE OBSERVE_SEINE.gearusefeaturesmeasurement SET gearUseFeatures = replace(gearUseFeatures, 'GearUseFeatures#', 'GearUseFeaturesSeine#'); UPDATE OBSERVE_SEINE.gearusefeatures SET topiaid = replace(topiaid, 'GearUseFeatures#', 'GearUseFeaturesSeine#'); ALTER TABLE OBSERVE_SEINE.GEARUSEFEATURESMEASUREMENT ADD CONSTRAINT fk_gearusefeaturesmeasurement_gearusefeatures FOREIGN KEY(gearUseFeatures) REFERENCES OBSERVE_SEINE.GEARUSEFEATURES(topiaid); \ No newline at end of file -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.