Tony CHEMIT pushed to branch master at ultreiaio / ird-observe Commits: 41491680 by Tony CHEMIT at 2018-05-04T16:01:00Z Fix migration - Closes #945 - - - - - 3 changed files: - persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_6_0.java - persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_7_0_RC_4.java - persistence/src/main/resources/db/migration/6.1/00_fix_trigger-PG.sql → persistence/src/main/resources/db/migration/6.0/00_fix_trigger-PG.sql Changes: ===================================== persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_6_0.java ===================================== --- a/persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_6_0.java +++ b/persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_6_0.java @@ -264,6 +264,74 @@ public class DataSourceMigrationForVersion_6_0 extends AbstractObserveMigrationC super(callBack, PGDataSourceMigration.TYPE); } + @Override + protected void prepareMigrationScript(TopiaSqlSupport topiaSqlSupport, List<String> queries, boolean showSql, boolean showProgression) { + Boolean withTriggers = topiaSqlSupport.findSingleResult(new TopiaSqlQuery<Boolean>() { + @Override + public PreparedStatement prepareQuery(Connection connection) throws SQLException { + return connection.prepareStatement("select exists(select * from pg_proc where proname = 'sync_activity_the_geom');"); + } + + @Override + public Boolean prepareResult(ResultSet resultSet) throws SQLException { + return resultSet.getBoolean(1); + } + }); + + if (withTriggers) { + queries.add("CREATE OR REPLACE function sync_activity_the_geom () returns trigger as '\n" + + "BEGIN\n" + + " IF (TG_OP = ''DELETE'') THEN\n" + + " RETURN OLD;\n" + + " END IF;\n" + + " IF (NEW.latitude IS NULL OR NEW.longitude IS NULL) THEN\n" + + " -- on ne calcule pas le point postgis si au moins une des -- coordonnees n est pas renseignee\n" + + " RAISE NOTICE ''No latitude or longitude, can not compute postgis field for id % '', NEW.topiaId;\n" + + " NEW.the_geom := NULL;\n" + + " return NEW;\n" + + " END IF;\n" + + " IF (TG_OP = ''UPDATE'' AND NEW.latitude = OLD.latitude AND NEW.longitude = OLD.longitude)\n" + + " THEN\n" + + " -- on ne calcule pas le point postgis si les coordonnées n''ont pas changées\n" + + " return NEW;\n" + + " END IF;\n" + + " RAISE NOTICE ''Will compute the_geom for activite % - latitude % and longitude %'', NEW.topiaId, NEW.latitude, NEW.longitude;\n" + + " -- affectation du point\n" + + " NEW.the_geom := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326);\n" + + " RAISE NOTICE ''Computed for activity % latitude % and longitude %, the_geom %'', NEW.topiaId, NEW.latitude, NEW.longitude, NEW.the_geom;\n" + + "\n" + + " RETURN NEW;\n" + + "END\n" + + "'\n" + + "LANGUAGE 'plpgsql';\n"); + queries.add("CREATE OR REPLACE function sync_harbour_the_geom () returns trigger as '\n" + + "BEGIN\n" + + " IF (TG_OP = ''DELETE'') THEN\n" + + " RETURN OLD;\n" + + " END IF;\n" + + " IF (NEW.latitude IS NULL OR NEW.longitude IS NULL) THEN\n" + + " -- on ne calcule pas le point postgis si au moins une des -- coordonnees n est pas renseignee\n" + + " RAISE NOTICE ''No latitude or longitude, can not compute postgis field for id % '', NEW.topiaId;\n" + + " NEW.the_geom := NULL;\n" + + " return NEW;\n" + + " END IF;\n" + + " IF (TG_OP = ''UPDATE'' AND NEW.latitude = OLD.latitude AND NEW.longitude = OLD.longitude)\n" + + " THEN\n" + + " -- on ne calcule pas le point postgis si les coordonnées n''ont pas changées\n" + + " return NEW;\n" + + " END IF;\n" + + " RAISE NOTICE ''Will compute the_geom for harbour % - latitude % and longitude %'', NEW.topiaId, NEW.latitude, NEW.longitude;\n" + + " -- affectation du point\n" + + " NEW.the_geom := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326);\n" + + " RAISE NOTICE ''Computed for harbour % latitude % and longitude %, the_geom %'', NEW.topiaId, NEW.latitude, NEW.longitude, NEW.the_geom;\n" + + " RETURN NEW;\n" + + "END\n" + + "'\n" + + "LANGUAGE 'plpgsql';"); + addScript("00", "fix_trigger", queries); + } + super.prepareMigrationScript(topiaSqlSupport, queries, showSql, showProgression); + } } } ===================================== persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_7_0_RC_4.java ===================================== --- a/persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_7_0_RC_4.java +++ b/persistence/src/main/java/fr/ird/observe/persistence/migration/DataSourceMigrationForVersion_7_0_RC_4.java @@ -22,13 +22,8 @@ package fr.ird.observe.persistence.migration; * #L% */ -import org.nuiton.topia.persistence.support.TopiaSqlQuery; import org.nuiton.topia.persistence.support.TopiaSqlSupport; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; import java.util.List; import java.util.Map; import java.util.Set; @@ -89,76 +84,6 @@ public class DataSourceMigrationForVersion_7_0_RC_4 extends AbstractObserveMigra super(callBack, PGDataSourceMigration.TYPE); } - - @Override - protected void prepareMigrationScript(TopiaSqlSupport topiaSqlSupport, List<String> queries, boolean showSql, boolean showProgression) { - - Boolean withTriggers = topiaSqlSupport.findSingleResult(new TopiaSqlQuery<Boolean>() { - @Override - public PreparedStatement prepareQuery(Connection connection) throws SQLException { - return connection.prepareStatement("select exists(select * from pg_proc where proname = 'sync_activity_the_geom');"); - } - - @Override - public Boolean prepareResult(ResultSet resultSet) throws SQLException { - return resultSet.getBoolean(1); - } - }); - - if (withTriggers) { - queries.add("CREATE OR REPLACE function sync_activity_the_geom () returns trigger as '\n" + - "BEGIN\n" + - " IF (TG_OP = ''DELETE'') THEN\n" + - " RETURN OLD;\n" + - " END IF;\n" + - " IF (NEW.latitude IS NULL OR NEW.longitude IS NULL) THEN\n" + - " -- on ne calcule pas le point postgis si au moins une des -- coordonnees n est pas renseignee\n" + - " RAISE NOTICE ''No latitude or longitude, can not compute postgis field for id % '', NEW.topiaId;\n" + - " NEW.the_geom := NULL;\n" + - " return NEW;\n" + - " END IF;\n" + - " IF (TG_OP = ''UPDATE'' AND NEW.latitude = OLD.latitude AND NEW.longitude = OLD.longitude)\n" + - " THEN\n" + - " -- on ne calcule pas le point postgis si les coordonnées n''ont pas changées\n" + - " return NEW;\n" + - " END IF;\n" + - " RAISE NOTICE ''Will compute the_geom for activite % - latitude % and longitude %'', NEW.topiaId, NEW.latitude, NEW.longitude;\n" + - " -- affectation du point\n" + - " NEW.the_geom := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326);\n" + - " RAISE NOTICE ''Computed for activity % latitude % and longitude %, the_geom %'', NEW.topiaId, NEW.latitude, NEW.longitude, NEW.the_geom;\n" + - "\n" + - " RETURN NEW;\n" + - "END\n" + - "'\n" + - "LANGUAGE 'plpgsql';\n"); - queries.add("CREATE OR REPLACE function sync_harbour_the_geom () returns trigger as '\n" + - "BEGIN\n" + - " IF (TG_OP = ''DELETE'') THEN\n" + - " RETURN OLD;\n" + - " END IF;\n" + - " IF (NEW.latitude IS NULL OR NEW.longitude IS NULL) THEN\n" + - " -- on ne calcule pas le point postgis si au moins une des -- coordonnees n est pas renseignee\n" + - " RAISE NOTICE ''No latitude or longitude, can not compute postgis field for id % '', NEW.topiaId;\n" + - " NEW.the_geom := NULL;\n" + - " return NEW;\n" + - " END IF;\n" + - " IF (TG_OP = ''UPDATE'' AND NEW.latitude = OLD.latitude AND NEW.longitude = OLD.longitude)\n" + - " THEN\n" + - " -- on ne calcule pas le point postgis si les coordonnées n''ont pas changées\n" + - " return NEW;\n" + - " END IF;\n" + - " RAISE NOTICE ''Will compute the_geom for harbour % - latitude % and longitude %'', NEW.topiaId, NEW.latitude, NEW.longitude;\n" + - " -- affectation du point\n" + - " NEW.the_geom := ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326);\n" + - " RAISE NOTICE ''Computed for harbour % latitude % and longitude %, the_geom %'', NEW.topiaId, NEW.latitude, NEW.longitude, NEW.the_geom;\n" + - " RETURN NEW;\n" + - "END\n" + - "'\n" + - "LANGUAGE 'plpgsql';"); - addScript("00", "fix_trigger", queries); - } - super.prepareMigrationScript(topiaSqlSupport, queries, showSql, showProgression); - } } } ===================================== persistence/src/main/resources/db/migration/6.1/00_fix_trigger-PG.sql → persistence/src/main/resources/db/migration/6.0/00_fix_trigger-PG.sql ===================================== View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/commit/414916806da3222e2d102b3ef5b2... --- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/commit/414916806da3222e2d102b3ef5b2... You're receiving this email because of your account on gitlab.com.