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 6ab915f0a79629020009cf5aafa71ae471cdac41 Author: Tony CHEMIT <chemit@codelutin.com> Date: Tue Jun 16 11:56:24 2015 +0200 ajout des scripts qui rajoute les fk manquantes (refs #7229) --- .../entities/migration/H2DataSourceMigration.java | 39 ++++++++++++++++++---- .../entities/migration/PGDataSourceMigration.java | 39 ++++++++++++++++++---- .../DataSourceMigrationForVersion_4_0_1.java | 31 +++++++++++++++-- .../main/resources/db/4.0.1/add-missing-fk-H2.sql | 5 +++ .../main/resources/db/4.0.1/add-missing-fk-PG.sql | 5 +++ 5 files changed, 105 insertions(+), 14 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 cf125fb..a3d92ae 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,7 +183,7 @@ public class H2DataSourceMigration extends AbstractDataSourceMigration { return result; } - public static String getForeignKeyConstraintName(TopiaContextImplementor tx, final String schemaName, final String tableName, final String columnName) throws TopiaException { + public static String getForeignKeyConstraintName(TopiaContextImplementor tx, final String schemaName, final String tableName, final String columnName, final boolean mustExists) throws TopiaException { final List<String> result = new ArrayList<String>(); @@ -195,7 +195,7 @@ public class H2DataSourceMigration extends AbstractDataSourceMigration { "WHERE CONSTRAINT_TYPE='REFERENTIAL' " + "AND CONSTRAINT_SCHEMA='%S' " + "AND TABLE_NAME='%S' " + - "AND COLUMN_LIST='%S';" , schemaName, tableName, columnName); + "AND COLUMN_LIST='%S';", schemaName, tableName, columnName); PreparedStatement ps = connection.prepareStatement(sql); try { ResultSet set = ps.executeQuery(); @@ -211,10 +211,23 @@ public class H2DataSourceMigration extends AbstractDataSourceMigration { } }); + String constraintName; + if (result.isEmpty()) { - throw new IllegalStateException("Could not find constaint name for "+schemaName+"."+tableName+"."+columnName); + + if (mustExists) { + throw new IllegalStateException("Could not find constraint name for " + schemaName + "." + tableName + "." + columnName); + } + + constraintName = null; + + } else { + + constraintName = result.get(0); + } - return result.get(0); + + return constraintName; } @@ -258,16 +271,30 @@ public class H2DataSourceMigration extends AbstractDataSourceMigration { } - public static void removeFK(TopiaContextImplementor tx, String schemaName, String tableName,String columnName, List<String> queries) throws TopiaException { + 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); + String contrainstName = getForeignKeyConstraintName(tx, schemaName, tableName, columnName, true); // remove constraints queries.add(String.format("ALTER TABLE %s.%s DROP CONSTRAINT %s;", schemaName, tableName, contrainstName)); } + public static void removeFKIfExists(TopiaContextImplementor tx, String schemaName, String tableName, String columnName, List<String> queries) throws TopiaException { + + // Get fk constraints + String contrainstName = getForeignKeyConstraintName(tx, schemaName, tableName, columnName, false); + + if (contrainstName != null) { + + // 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 { 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 ad6f8f3..6d0ce20 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,7 +227,7 @@ public class PGDataSourceMigration extends AbstractDataSourceMigration { } - public static String getForeignKeyConstraintName(TopiaContextImplementor tx, final String schemaName, final String tableName, final String columnName) throws TopiaException { + public static String getForeignKeyConstraintName(TopiaContextImplementor tx, final String schemaName, final String tableName, final String columnName, final boolean mustExists) throws TopiaException { final List<String> result = new ArrayList<String>(); @@ -298,10 +298,23 @@ public class PGDataSourceMigration extends AbstractDataSourceMigration { } }); + String constraintName; + if (result.isEmpty()) { - throw new IllegalStateException("Could not find constaint name for "+schemaName+"."+tableName+"."+columnName); + + if (mustExists) { + throw new IllegalStateException("Could not find constraint name for " + schemaName + "." + tableName + "." + columnName); + } + + constraintName = null; + + } else { + + constraintName = result.get(0); + } - return result.get(0); + + return constraintName; } @@ -344,17 +357,31 @@ public class PGDataSourceMigration extends AbstractDataSourceMigration { } - public static void removeFK(TopiaContextImplementor tx, String schemaName, String tableName,String columnName, List<String> queries) throws TopiaException { + 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); + String contrainstName = getForeignKeyConstraintName(tx, schemaName, tableName, columnName, true); // 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 { + public static void removeFKIfExists(TopiaContextImplementor tx, String schemaName, String tableName, String columnName, List<String> queries) throws TopiaException { + + // Get fk constraints + String contrainstName = getForeignKeyConstraintName(tx, schemaName, tableName, columnName, false); + + if (contrainstName != null) { + + // 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 Set<String> uKNames = getUniqueKeyConstraintNames(tx, tableName); diff --git a/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_1.java b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_1.java index 1ac4f07..7e7f3c1 100644 --- a/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_1.java +++ b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_1.java @@ -37,7 +37,9 @@ import java.util.List; * @author Tony Chemit - chemit@codelutin.com * @since 4.0.1 */ -public class DataSourceMigrationForVersion_4_0_1 extends AbstractObserveMigrationCallBack { +public abstract class DataSourceMigrationForVersion_4_0_1 extends AbstractObserveMigrationCallBack { + + protected abstract void removeForeignKeys(TopiaContextImplementor tx, List<String> queries); public DataSourceMigrationForVersion_4_0_1(AbstractDataSourceMigration callBack, String scriptSuffix) { super(AbstractDataSourceMigration.V_4_0_1, callBack, scriptSuffix); @@ -52,6 +54,10 @@ public class DataSourceMigrationForVersion_4_0_1 extends AbstractObserveMigratio // See https://forge.codelutin.com/issues/7226 addScript("remove-gender-column", queries); + // See https://forge.codelutin.com/issues/7229 + removeForeignKeys(tx, queries); + addScript("add-missing-fk", queries); + } public static class H2DataSourceMigrationForVersion extends DataSourceMigrationForVersion_4_0_1 { @@ -60,6 +66,17 @@ public class DataSourceMigrationForVersion_4_0_1 extends AbstractObserveMigratio super(callBack, H2DataSourceMigration.TYPE); } + @Override + protected void removeForeignKeys(TopiaContextImplementor tx, List<String> queries) { + + H2DataSourceMigration.removeFKIfExists(tx, "observe_seine", "FLOATINGOBJECT", "OBJECTTYPE", queries); + H2DataSourceMigration.removeFKIfExists(tx, "observe_seine", "FLOATINGOBJECT", "OBJECTFATE", queries); + H2DataSourceMigration.removeFKIfExists(tx, "observe_seine", "FLOATINGOBJECT", "OBJECTOPERATION", queries); + H2DataSourceMigration.removeFKIfExists(tx, "observe_seine", "ACTIVITY_OBSERVEDSYSTEM", "OBSERVEDSYSTEM", queries); + H2DataSourceMigration.removeFKIfExists(tx, "observe_seine", "TRIP", "OBSERVER", queries); + + } + } public static class PGDataSourceMigrationForVersion extends DataSourceMigrationForVersion_4_0_1 { @@ -68,7 +85,17 @@ public class DataSourceMigrationForVersion_4_0_1 extends AbstractObserveMigratio super(callBack, PGDataSourceMigration.TYPE); } - } + @Override + protected void removeForeignKeys(TopiaContextImplementor tx, List<String> queries) { + + PGDataSourceMigration.removeFKIfExists(tx, "observe_seine", "FLOATINGOBJECT", "OBJECTTYPE", queries); + PGDataSourceMigration.removeFKIfExists(tx, "observe_seine", "FLOATINGOBJECT", "OBJECTFATE", queries); + PGDataSourceMigration.removeFKIfExists(tx, "observe_seine", "FLOATINGOBJECT", "OBJECTOPERATION", queries); + PGDataSourceMigration.removeFKIfExists(tx, "observe_seine", "ACTIVITY_OBSERVEDSYSTEM", "OBSERVEDSYSTEM", queries); + PGDataSourceMigration.removeFKIfExists(tx, "observe_seine", "TRIP", "OBSERVER", queries); + } + + } } diff --git a/observe-entities/src/main/resources/db/4.0.1/add-missing-fk-H2.sql b/observe-entities/src/main/resources/db/4.0.1/add-missing-fk-H2.sql new file mode 100644 index 0000000..2eaf2f9 --- /dev/null +++ b/observe-entities/src/main/resources/db/4.0.1/add-missing-fk-H2.sql @@ -0,0 +1,5 @@ +ALTER TABLE OBSERVE_SEINE.FLOATINGOBJECT ADD CONSTRAINT FK_FLOATINGOBJECT_OBJECTTYPE FOREIGN KEY(OBJECTTYPE) REFERENCES OBSERVE_SEINE.OBJECTTYPE(TOPIAID) CHECK; +ALTER TABLE OBSERVE_SEINE.FLOATINGOBJECT ADD CONSTRAINT FK_FLOATINGOBJECT_OBJECTFATE FOREIGN KEY(OBJECTFATE) REFERENCES OBSERVE_SEINE.OBJECTFATE(TOPIAID) CHECK; +ALTER TABLE OBSERVE_SEINE.FLOATINGOBJECT ADD CONSTRAINT FK_FLOATINGOBJECT_OBJECTOPERATION FOREIGN KEY(OBJECTOPERATION) REFERENCES OBSERVE_SEINE.OBJECTOPERATION(TOPIAID) CHECK; +ALTER TABLE OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM ADD CONSTRAINT FK_ACTIVITY_OBSERVEDSYSTEM_OBSERVEDSYSTEM FOREIGN KEY(OBSERVEDSYSTEM) REFERENCES OBSERVE_SEINE.OBSERVEDSYSTEM(TOPIAID) CHECK; +ALTER TABLE OBSERVE_SEINE.TRIP ADD CONSTRAINT FK_TRIP_OBSERVER FOREIGN KEY(OBSERVER) REFERENCES OBSERVE_COMMON.PERSON(TOPIAID) CHECK; \ No newline at end of file diff --git a/observe-entities/src/main/resources/db/4.0.1/add-missing-fk-PG.sql b/observe-entities/src/main/resources/db/4.0.1/add-missing-fk-PG.sql new file mode 100644 index 0000000..457b5f5 --- /dev/null +++ b/observe-entities/src/main/resources/db/4.0.1/add-missing-fk-PG.sql @@ -0,0 +1,5 @@ +ALTER TABLE OBSERVE_SEINE.FLOATINGOBJECT ADD CONSTRAINT FK_FLOATINGOBJECT_OBJECTTYPE FOREIGN KEY(OBJECTTYPE) REFERENCES OBSERVE_SEINE.OBJECTTYPE(TOPIAID); +ALTER TABLE OBSERVE_SEINE.FLOATINGOBJECT ADD CONSTRAINT FK_FLOATINGOBJECT_OBJECTFATE FOREIGN KEY(OBJECTFATE) REFERENCES OBSERVE_SEINE.OBJECTFATE(TOPIAID); +ALTER TABLE OBSERVE_SEINE.FLOATINGOBJECT ADD CONSTRAINT FK_FLOATINGOBJECT_OBJECTOPERATION FOREIGN KEY(OBJECTOPERATION) REFERENCES OBSERVE_SEINE.OBJECTOPERATION(TOPIAID); +ALTER TABLE OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM ADD CONSTRAINT FK_ACTIVITY_OBSERVEDSYSTEM_OBSERVEDSYSTEM FOREIGN KEY(OBSERVEDSYSTEM) REFERENCES OBSERVE_SEINE.OBSERVEDSYSTEM(TOPIAID); +ALTER TABLE OBSERVE_SEINE.TRIP ADD CONSTRAINT FK_TRIP_OBSERVER FOREIGN KEY(OBSERVER) REFERENCES OBSERVE_COMMON.PERSON(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>.