This is an automated email from the git hooks/post-receive script. New commit to branch feature/6944 in repository observe. See http://git.codelutin.com/observe.git commit c4dcfe0a04fefb68752677012a4c8f3c4fdbeb41 Author: Tony CHEMIT <chemit@codelutin.com> Date: Thu Apr 16 19:05:00 2015 +0200 refs #6964 migration 4.0-RC3 pour ajouter les index sur fk manquants + regeneration de ces index via hibernate --- .../migration/AbstractDataSourceMigration.java | 5 +- .../DataSourceMigrationForVersion_4_0_RC3.java | 254 +++++++++++++++++++++ .../db/4.0-RC3/add-foreign-key-indexes-PG.sql | 44 ++++ .../src/main/xmi/observe-common.properties | 3 +- .../src/main/xmi/observe-longline.properties | 2 +- .../src/main/xmi/observe-seine.properties | 2 +- 6 files changed, 306 insertions(+), 4 deletions(-) diff --git a/observe-entities/src/main/java/fr/ird/observe/entities/migration/AbstractDataSourceMigration.java b/observe-entities/src/main/java/fr/ird/observe/entities/migration/AbstractDataSourceMigration.java index 19d34b2..4cfa18c 100644 --- a/observe-entities/src/main/java/fr/ird/observe/entities/migration/AbstractDataSourceMigration.java +++ b/observe-entities/src/main/java/fr/ird/observe/entities/migration/AbstractDataSourceMigration.java @@ -74,11 +74,14 @@ public abstract class AbstractDataSourceMigration extends TopiaMigrationCallback public static final Version V_3_15 = Versions.valueOf("3.15"); public static final Version V_3_16 = Versions.valueOf("3.16"); + public static final Version V_4_0_RC2 = Versions.valueOf("4.0-RC2"); + public static final Version V_4_0_RC3 = Versions.valueOf("4.0-RC3"); + /** Les versions de mise à jour disponibles. */ public static final Version[] availableVersions = new Version[]{ - V_3_1, V_3_5, V_3_7, V_3_8, V_3_9, V_3_10, V_3_11, V_3_12, V_3_14, V_3_15, V_3_16,V_4_0_RC2 + V_3_1, V_3_5, V_3_7, V_3_8, V_3_9, V_3_10, V_3_11, V_3_12, V_3_14, V_3_15, V_3_16, V_4_0_RC2, V_4_0_RC3 }; private static Supplier<ApplicationConfig> applicationConfigSupplier; diff --git a/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_RC3.java b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_RC3.java new file mode 100644 index 0000000..e34b5b6 --- /dev/null +++ b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_0_RC3.java @@ -0,0 +1,254 @@ +package fr.ird.observe.entities.migration.versions; + +import fr.ird.observe.entities.migration.AbstractDataSourceMigration; +import fr.ird.observe.entities.migration.AbstractObserveMigrationCallBack; +import fr.ird.observe.entities.migration.H2DataSourceMigration; +import fr.ird.observe.entities.migration.PGDataSourceMigration; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.hibernate.jdbc.Work; +import org.nuiton.topia.TopiaException; +import org.nuiton.topia.framework.TopiaContextImplementor; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.HashSet; +import java.util.List; +import java.util.Set; + +/** + * Created on 4/16/15. + * + * @author Tony Chemit - chemit@codelutin.com + * @since 4.0-RC3 + */ +public abstract class DataSourceMigrationForVersion_4_0_RC3 extends AbstractObserveMigrationCallBack { + + /** Logger. */ + private static final Log log = LogFactory.getLog(DataSourceMigrationForVersion_4_0_RC3.class); + + protected abstract void addMissingForeignKeys(TopiaContextImplementor tx, List<String> queries) throws TopiaException; + + public DataSourceMigrationForVersion_4_0_RC3(AbstractDataSourceMigration callBack, String scriptSuffix) { + super(AbstractDataSourceMigration.V_4_0_RC3, callBack, scriptSuffix); + } + + @Override + protected void prepareMigrationScript(TopiaContextImplementor tx, + List<String> queries, + boolean showSql, + boolean showProgression) throws TopiaException { + + + addMissingForeignKeys(tx, queries); + + } + + public static class H2DataSourceMigrationForVersion extends DataSourceMigrationForVersion_4_0_RC3 { + + public H2DataSourceMigrationForVersion(AbstractDataSourceMigration callBack) { + super(callBack, H2DataSourceMigration.TYPE); + } + + @Override + protected void addMissingForeignKeys(TopiaContextImplementor tx, List<String> queries) { + // Nothing to do for h2 db + } + + } + + public static class PGDataSourceMigrationForVersion extends DataSourceMigrationForVersion_4_0_RC3 { + + public PGDataSourceMigrationForVersion(AbstractDataSourceMigration callBack) { + super(callBack, PGDataSourceMigration.TYPE); + } + + @Override + protected void addMissingForeignKeys(TopiaContextImplementor tx, List<String> queries) throws TopiaException { + + removeForeignKeyIndex(tx, queries, "OBSERVE_COMMON", "gear_gearcaracteristic", "gear"); + removeForeignKeyIndex(tx, queries, "OBSERVE_COMMON", "ocean_species", "species"); + removeForeignKeyIndex(tx, queries, "OBSERVE_COMMON", "species_specieslist", "speciesList"); + + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "activity", "trip"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "baitsComposition", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "basket", "section"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "branchline", "basket"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "branchlinesComposition", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "catch", "basket"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "catch", "branchline"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "catch_predator", "catch"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "catch", "section"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "catch", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "encounter", "activity"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "floatlinesComposition", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "hooksComposition", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "mitigationtype_set", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "section", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "sensorUsed", "activity"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "sizeMeasure", "catch"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "species_tdr", "tdr"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "tdr", "basket"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "tdr", "branchline"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "tdrRecord", "basket"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "tdrRecord", "tdr"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "tdr", "section"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "tdr", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_LONGLINE", "weightMeasure", "catch"); + + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "activity_observedsystem", "activity"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "activity", "route"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "floatingObject", "activity"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "gearUseFeaturesMeasurement", "gearUseFeatures"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "gearUseFeatures", "trip"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "nonTargetCatch", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "nonTargetLength", "nonTargetSample"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "nonTargetSample", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "objectObservedSpecies", "floatingObject"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "objectSchoolEstimate", "floatingObject"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "route", "trip"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "schoolEstimate", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "targetCatch", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "targetLength", "targetSample"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "targetSample", "set"); + removeForeignKeyIndex(tx, queries, "OBSERVE_SEINE", "transmittingBuoy", "floatingObject"); + + addScript("add-foreign-key-indexes", queries); + + } + + private void removeForeignKeyIndex(TopiaContextImplementor tx, final List<String> queries, final String schemaName, final String tableName, final String columnName) throws TopiaException { + + tx.getHibernate().doWork(new Work() { + + @Override + public void execute(Connection connection) throws SQLException { + + // get table oid + int oid = getTableOid(connection, schemaName, tableName); + + // get attribute num + int attNum = getAttributeNum(connection, oid, columnName); + + Set<Integer> indexIds = getIndexId(connection, oid, attNum); + + for (Integer indexId : indexIds) { + + String indexName = getIndexName(connection, indexId); + queries.add("DROP INDEX " + schemaName + "." + indexName + ";"); + + } + + } + + }); + + } + + protected Integer getTableOid(Connection connection, String schemaName, String tableName) throws SQLException { + + Integer oid = null; + + String sqlOid = "SELECT '" + schemaName + "." + tableName + "'::regclass::oid;"; + PreparedStatement ps = connection.prepareStatement(sqlOid); + try { + ResultSet set = ps.executeQuery(); + if (set.next()) { + oid = set.getInt(1); + if (log.isDebugEnabled()) + log.debug("found table oid " + schemaName + "." + tableName + ": " + oid); + } + } catch (Exception e) { + throw new SQLException("Could not obtain oid for table" + tableName, e); + } finally { + ps.close(); + } + + return oid; + + } + + protected Integer getAttributeNum(Connection connection, int oid, String columnName) throws SQLException { + + Integer attNum = null; + + String attNumSql = "SELECT attnum FROM pg_attribute WHERE attrelid = ? AND attname = ?"; + PreparedStatement ps = connection.prepareStatement(attNumSql); + ps.setInt(1, oid); + ps.setString(2, columnName.toLowerCase()); + try { + ResultSet set = ps.executeQuery(); + if (set.next()) { + attNum = set.getInt(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(); + } + + return attNum; + + } + + protected Set<Integer> getIndexId(Connection connection, int oid, int attNum) throws SQLException { + + Set<Integer> indexIds = new HashSet<Integer>(); + + String sql = "SELECT indexrelid FROM pg_index " + + "WHERE indrelid = ? " + + "AND indkey = '" + attNum + "' " + + "AND indisunique = FALSE " + + "AND indisprimary = FALSE;"; + PreparedStatement ps = connection.prepareStatement(sql); + ps.setInt(1, oid); +// ps.setString(2, String.valueOf(attNum)); + + try { + ResultSet set = ps.executeQuery(); + + while (set.next()) { + + indexIds.add(set.getInt(1)); + + } + } catch (Exception e) { + throw new SQLException("Could not obtain index Id unique for table oid " + oid + " and column attNum " + attNum, e); + } finally { + ps.close(); + } + return indexIds; + + } + + protected String getIndexName(Connection connection, int indexId) throws SQLException { + + String indexName = null; + + String sqlIndexName = "SELECT relname FROM pg_class WHERE oid = ?;"; + PreparedStatement ps = connection.prepareStatement(sqlIndexName); + ps.setInt(1, indexId); + try { + ResultSet set = ps.executeQuery(); + if (set.next()) { + indexName = set.getString(1); + } + } catch (Exception e) { + throw new SQLException("Could not obtain index name for indexId " + indexId, e); + } finally { + ps.close(); + } + + return indexName; + + } + + + } + + +} diff --git a/observe-entities/src/main/resources/db/4.0-RC3/add-foreign-key-indexes-PG.sql b/observe-entities/src/main/resources/db/4.0-RC3/add-foreign-key-indexes-PG.sql new file mode 100644 index 0000000..cc832ae --- /dev/null +++ b/observe-entities/src/main/resources/db/4.0-RC3/add-foreign-key-indexes-PG.sql @@ -0,0 +1,44 @@ +CREATE INDEX idx_observe_common_gear_gearcaracteristic_gear ON OBSERVE_COMMON.gear_gearcaracteristic(gear); +CREATE INDEX idx_observe_common_ocean_species_species ON OBSERVE_COMMON.ocean_species(species); +CREATE INDEX idx_observe_common_species_specieslist_specieslist ON OBSERVE_COMMON.species_specieslist(speciesList); +CREATE INDEX idx_observe_longline_activity_trip ON OBSERVE_LONGLINE.Activity(trip); +CREATE INDEX idx_observe_longline_baitscomposition_set ON OBSERVE_LONGLINE.baitsComposition(set); +CREATE INDEX idx_observe_longline_basket_section ON OBSERVE_LONGLINE.basket(section); +CREATE INDEX idx_observe_longline_branchline_basket ON OBSERVE_LONGLINE.branchline(basket); +CREATE INDEX idx_observe_longline_branchlinescomposition_set ON OBSERVE_LONGLINE.branchlinesComposition(set); +CREATE INDEX idx_observe_longline_catch_basket ON OBSERVE_LONGLINE.Catch(basket); +CREATE INDEX idx_observe_longline_catch_branchline ON OBSERVE_LONGLINE.Catch(branchline); +CREATE INDEX idx_observe_longline_catch_predator_catch ON OBSERVE_LONGLINE.catch_predator(catch); +CREATE INDEX idx_observe_longline_catch_section ON OBSERVE_LONGLINE.Catch(section); +CREATE INDEX idx_observe_longline_catch_set ON OBSERVE_LONGLINE.Catch(set); +CREATE INDEX idx_observe_longline_encounter_activity ON OBSERVE_LONGLINE.encounter(activity); +CREATE INDEX idx_observe_longline_floatlinescomposition_set ON OBSERVE_LONGLINE.floatlinesComposition(set); +CREATE INDEX idx_observe_longline_hookscomposition_set ON OBSERVE_LONGLINE.hooksComposition(set); +CREATE INDEX idx_observe_longline_mitigationtype_set_set ON OBSERVE_LONGLINE.mitigationtype_set(set); +CREATE INDEX idx_observe_longline_section_set ON OBSERVE_LONGLINE.section(set); +CREATE INDEX idx_observe_longline_sensorused_activity ON OBSERVE_LONGLINE.sensorUsed(activity); +CREATE INDEX idx_observe_longline_sizemeasure_catch ON OBSERVE_LONGLINE.sizeMeasure(catch); +CREATE INDEX idx_observe_longline_species_tdr_tdr ON OBSERVE_LONGLINE.species_tdr(tdr); +CREATE INDEX idx_observe_longline_tdr_basket ON OBSERVE_LONGLINE.tdr(basket); +CREATE INDEX idx_observe_longline_tdr_branchline ON OBSERVE_LONGLINE.tdr(branchline); +CREATE INDEX idx_observe_longline_tdrrecord_basket ON OBSERVE_LONGLINE.tdrRecord(basket); +CREATE INDEX idx_observe_longline_tdrrecord_tdr ON OBSERVE_LONGLINE.tdrRecord(tdr); +CREATE INDEX idx_observe_longline_tdr_section ON OBSERVE_LONGLINE.tdr(section); +CREATE INDEX idx_observe_longline_tdr_set ON OBSERVE_LONGLINE.tdr(set); +CREATE INDEX idx_observe_longline_weightmeasure_catch ON OBSERVE_LONGLINE.weightMeasure(catch); +CREATE INDEX idx_observe_seine_activity_observedsystem_activity ON OBSERVE_SEINE.activity_observedsystem(activity); +CREATE INDEX idx_observe_seine_activity_route ON OBSERVE_SEINE.Activity(route); +CREATE INDEX idx_observe_seine_floatingobject_activity ON OBSERVE_SEINE.floatingObject(activity); +CREATE INDEX idx_observe_seine_gearusefeaturesmeasurement_gearusefeatures ON OBSERVE_SEINE.GearUseFeaturesMeasurement(gearUseFeatures); +CREATE INDEX idx_observe_seine_gearusefeatures_trip ON OBSERVE_SEINE.GearUseFeatures(trip); +CREATE INDEX idx_observe_seine_nontargetcatch_set ON OBSERVE_SEINE.nonTargetCatch(set); +CREATE INDEX idx_observe_seine_nontargetlength_nontargetsample ON OBSERVE_SEINE.nonTargetLength(nonTargetSample); +CREATE INDEX idx_observe_seine_nontargetsample_set ON OBSERVE_SEINE.nonTargetSample(set); +CREATE INDEX idx_observe_seine_objectobservedspecies_floatingobject ON OBSERVE_SEINE.objectObservedSpecies(floatingObject); +CREATE INDEX idx_observe_seine_objectschoolestimate_floatingobject ON OBSERVE_SEINE.objectSchoolEstimate(floatingObject); +CREATE INDEX idx_observe_seine_route_trip ON OBSERVE_SEINE.route(trip); +CREATE INDEX idx_observe_seine_schoolestimate_set ON OBSERVE_SEINE.schoolEstimate(set); +CREATE INDEX idx_observe_seine_targetcatch_set ON OBSERVE_SEINE.targetCatch(set); +CREATE INDEX idx_observe_seine_targetlength_targetsample ON OBSERVE_SEINE.targetLength(targetSample); +CREATE INDEX idx_observe_seine_targetsample_set ON OBSERVE_SEINE.targetSample(set); +CREATE INDEX idx_observe_seine_transmittingbuoy_floatingobject ON OBSERVE_SEINE.transmittingBuoy(floatingObject); \ No newline at end of file diff --git a/observe-entities/src/main/xmi/observe-common.properties b/observe-entities/src/main/xmi/observe-common.properties index 23d3817..03a55d1 100644 --- a/observe-entities/src/main/xmi/observe-common.properties +++ b/observe-entities/src/main/xmi/observe-common.properties @@ -19,10 +19,11 @@ # <http://www.gnu.org/licenses/gpl-3.0.html>. # #L% ### -model.tagvalue.version=4.0-RC2 +model.tagvalue.version=4.0-RC3 model.tagvalue.notGenerateToString=true model.tagvalue.generateOperatorForDAOHelper=true model.tagvalue.generateStandaloneEnumForDAOHelper=true +model.tagvalue.indexForeignKeys=true model.tagvalue.String=java.lang.String model.tagvalue.Float=java.lang.Float model.tagvalue.Integer=java.lang.Integer diff --git a/observe-entities/src/main/xmi/observe-longline.properties b/observe-entities/src/main/xmi/observe-longline.properties index b13b38b..8f10a54 100644 --- a/observe-entities/src/main/xmi/observe-longline.properties +++ b/observe-entities/src/main/xmi/observe-longline.properties @@ -19,7 +19,7 @@ # <http://www.gnu.org/licenses/gpl-3.0.html>. # #L% ### -model.tagvalue.version=4.0-RC2 +model.tagvalue.version=4.0-RC3 model.tagvalue.notGenerateToString=true model.tagvalue.generateOperatorForDAOHelper=true model.tagvalue.generateStandaloneEnumForDAOHelper=true diff --git a/observe-entities/src/main/xmi/observe-seine.properties b/observe-entities/src/main/xmi/observe-seine.properties index 88335fb..ced24c5 100644 --- a/observe-entities/src/main/xmi/observe-seine.properties +++ b/observe-entities/src/main/xmi/observe-seine.properties @@ -20,7 +20,7 @@ # #L% ### -model.tagvalue.version=4.0-RC2 +model.tagvalue.version=4.0-RC3 model.tagvalue.notGenerateToString=true model.tagvalue.generateOperatorForDAOHelper=true model.tagvalue.generateStandaloneEnumForDAOHelper=true -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.