This is an automated email from the git hooks/post-receive script. New commit to branch feature/8124-VerifAdmin in repository tutti. See https://gitlab.nuiton.org/codelutin/tutti.git commit 562c622515a1e6906e56540bc0eab00935eb95a5 Author: Sylvain Bavencoff <bavencoff@codelutin.com> Date: Tue Mar 29 10:58:23 2016 +0200 Ajout d'un script de migration pour supprimer les doublons dans la table OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM (see #8124). --- .../DataSourceMigrationForVersion_4_903.java | 2 +- .../V4_903_03_remove_duplicate_rows-H2.sql | 21 +++++++++++++++++++++ .../V4_903_03_remove_duplicate_rows-PG.sql | 21 +++++++++++++++++++++ 3 files changed, 43 insertions(+), 1 deletion(-) diff --git a/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_903.java b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_903.java index 37450d0..afc4088 100644 --- a/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_903.java +++ b/observe-entities/src/main/java/fr/ird/observe/entities/migration/versions/DataSourceMigrationForVersion_4_903.java @@ -51,7 +51,7 @@ public class DataSourceMigrationForVersion_4_903 extends AbstractObserveMigratio // See https://forge.codelutin.com/issues/7470 addScript("01", "remove_unit_field", queries); addScript("02", "remove_gender_field", queries); - + addScript("03", "remove_duplicate_rows", queries); } public static class H2DataSourceMigrationForVersion extends DataSourceMigrationForVersion_4_903 { diff --git a/observe-entities/src/main/resources/db/migration/V4_903_03_remove_duplicate_rows-H2.sql b/observe-entities/src/main/resources/db/migration/V4_903_03_remove_duplicate_rows-H2.sql new file mode 100644 index 0000000..d6d65b3 --- /dev/null +++ b/observe-entities/src/main/resources/db/migration/V4_903_03_remove_duplicate_rows-H2.sql @@ -0,0 +1,21 @@ +-- suppression des doublons dans la table observe_seine.activity_observedsystem +------------------------------------------------------------------------------- + + +-- creation d'un table temporaire +CREATE TABLE ACTIVITY_OBSERVEDSYSTEM_TMP(ACTIVITY VARCHAR(255) NOT NULL, OBSERVEDSYSTEM VARCHAR(255) NOT NULL); + +-- copie les données de la table vers la table temporaire +INSERT INTO ACTIVITY_OBSERVEDSYSTEM_TMP (ACTIVITY, OBSERVEDSYSTEM) SELECT DISTINCT ACTIVITY, OBSERVEDSYSTEM FROM OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM; + +-- suppession des enregistrements de la table +DELETE FROM OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM; + +-- Ajout de la contrainte +ALTER TABLE OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM ADD CONSTRAINT PK_ACTIVITY_OBSERVEDSYSTEM PRIMARY KEY (ACTIVITY, OBSERVEDSYSTEM); + +-- on remet les enregistrement dans la table +INSERT INTO OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM (ACTIVITY, OBSERVEDSYSTEM) SELECT ACTIVITY, OBSERVEDSYSTEM FROM ACTIVITY_OBSERVEDSYSTEM_TMP; + +-- suppression de la table temporaire +DROP TABLE ACTIVITY_OBSERVEDSYSTEM_TMP; diff --git a/observe-entities/src/main/resources/db/migration/V4_903_03_remove_duplicate_rows-PG.sql b/observe-entities/src/main/resources/db/migration/V4_903_03_remove_duplicate_rows-PG.sql new file mode 100644 index 0000000..5ce44ed --- /dev/null +++ b/observe-entities/src/main/resources/db/migration/V4_903_03_remove_duplicate_rows-PG.sql @@ -0,0 +1,21 @@ +-- suppression des doublons dans la table observe_seine.activity_observedsystem +------------------------------------------------------------------------------- + + +-- creation d'un table temporaire +CREATE TABLE OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM_TMP(ACTIVITY VARCHAR(255) NOT NULL, OBSERVEDSYSTEM VARCHAR(255) NOT NULL); + +-- copie les données de la table vers la table temporaire +INSERT INTO OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM_TMP (ACTIVITY, OBSERVEDSYSTEM) SELECT DISTINCT ACTIVITY, OBSERVEDSYSTEM FROM OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM; + +-- suppession des enregistrements de la table +DELETE FROM OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM; + +-- Ajout de la contrainte +ALTER TABLE OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM ADD CONSTRAINT PK_ACTIVITY_OBSERVEDSYSTEM PRIMARY KEY (ACTIVITY, OBSERVEDSYSTEM); + +-- on remet les enregistrement dans la table +INSERT INTO OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM (ACTIVITY, OBSERVEDSYSTEM) SELECT ACTIVITY, OBSERVEDSYSTEM FROM OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM_TMP; + +-- suppression de la table temporaire +DROP TABLE OBSERVE_SEINE.ACTIVITY_OBSERVEDSYSTEM_TMP; -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.