Tony CHEMIT pushed to branch develop at ultreiaio / ird-observe Commits: ab651f88 by tchemit at 2020-04-06T17:09:56+02:00 Vérifier la présence ou ajouter un champ PS trip.homeid - Closes #1383 - - - - - 13 changed files: - client-datasource-editor-ps/src/main/i18n/getters/jaxx.getter - client-datasource-editor-ps/src/main/java/fr/ird/observe/client/datasource/editor/content/data/ps/common/TripUI.jaxx - + persistence/src/main/java/fr/ird/observe/entities/migration/DataSourceMigrationForVersion_7_5_1.java - persistence/src/main/resources/db/migration/8.0/102_create_ps_common_schema-common.sql - persistence/src/main/resources/db/migration/8.0/110_add_home_id-common.sql - + test/src/main/resources/db/7.5.1/dataForTestLongline.sql.gz - + test/src/main/resources/db/7.5.1/dataForTestSeine.sql.gz - + test/src/main/resources/db/7.5.1/empty_h2.sql.gz - + test/src/main/resources/db/7.5.1/empty_pg.sql.gz - + test/src/main/resources/db/7.5.1/referentiel.sql.gz - test/src/main/resources/db/8.0/dataForTestLongline.sql.gz - test/src/main/resources/db/8.0/dataForTestSeine.sql.gz - test/src/main/resources/db/8.0/referentiel.sql.gz Changes: ===================================== client-datasource-editor-ps/src/main/i18n/getters/jaxx.getter ===================================== @@ -1,5 +1,6 @@ observe.Id.comment observe.Id.country +observe.Id.homeId observe.Id.ocean observe.Id.sex observe.Id.sizeMeasureType ===================================== client-datasource-editor-ps/src/main/java/fr/ird/observe/client/datasource/editor/content/data/ps/common/TripUI.jaxx ===================================== @@ -148,6 +148,15 @@ <BeanFilterableComboBox id='landingHarbour' genericType='HarbourReference' constructorParams='this'/> </cell> </row> + <!-- homeId --> + <row> + <cell anchor='west'> + <JLabel id='homeIdLabel'/> + </cell> + <cell anchor='east' weightx="1" fill="both"> + <NormalTextEditor id='homeId'/> + </cell> + </row> <!-- id ers --> <row> ===================================== persistence/src/main/java/fr/ird/observe/entities/migration/DataSourceMigrationForVersion_7_5_1.java ===================================== @@ -0,0 +1,78 @@ +package fr.ird.observe.entities.migration; + +/*- + * #%L + * ObServe :: Persistence + * %% + * Copyright (C) 2008 - 2020 IRD, Code Lutin, Ultreia.io + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +import com.google.auto.service.AutoService; +import org.apache.commons.lang3.tuple.Pair; +import org.nuiton.topia.persistence.support.TopiaSqlQuery; +import org.nuiton.topia.service.migration.resources.MigrationVersionResource; +import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor; +import org.nuiton.version.Versions; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Set; + +/** + * @author Tony Chemit - dev@tchemit.fr + * @since 7.5.1 + */ +@AutoService(MigrationVersionResource.class) +public class DataSourceMigrationForVersion_7_5_1 extends MigrationVersionResource { + + public DataSourceMigrationForVersion_7_5_1() { + super(Versions.valueOf("7.5.1")); + } + + @Override + public void generateSqlScript(MigrationVersionResourceExecutor executor) { + Set<Pair<String, String>> tripIdAndComment = executor.findMultipleResultAstSet(new TopiaSqlQuery<Pair<String, String>>() { + @SuppressWarnings("SqlDialectInspection") + @Override + public PreparedStatement prepareQuery(Connection connection) throws SQLException { + return connection.prepareStatement("SELECT topiaId, comment FROM observe_seine.trip WHERE comment LIKE '#%#%'"); + } + + @Override + public Pair<String, String> prepareResult(ResultSet set) throws SQLException { + return Pair.of(set.getString(1), set.getString(2)); + } + }); + + executor.writeSql("ALTER TABLE observe_seine.trip ADD COLUMN homeId VARCHAR(255)"); + + for (Pair<String, String> pair : tripIdAndComment) { + String tripId = pair.getKey(); + String comment = pair.getValue().trim(); + + int endIndex = comment.indexOf('#', 1); + String homeId = comment.substring(1, endIndex - 1); + String newComment = endIndex + 1 == comment.length() ? "NULL" : (String.format("'%s'", comment.substring(endIndex + 1).trim().replaceAll("'","''"))); + executor.writeSql(String.format("UPDATE observe_seine.trip t SET comment = %s, homeId = '%s', topiaVersion = topiaVersion + 1, lastUpdateDate = CURRENT_TIMESTAMP WHERE t.topiaId = '%s';", newComment, homeId, tripId)); + } + } + +} + ===================================== persistence/src/main/resources/db/migration/8.0/102_create_ps_common_schema-common.sql ===================================== @@ -27,7 +27,7 @@ CREATE TABLE ps_common.transmittingbuoyoperation(topiaid VARCHAR(255) NOT NULL C CREATE TABLE ps_common.transmittingbuoytype(topiaid VARCHAR(255) NOT NULL CONSTRAINT pk_ps_observation_transmittingbuoytype PRIMARY KEY, topiaversion BIGINT NOT NULL, topiacreatedate TIMESTAMP NOT NULL, code VARCHAR(255), needcomment BOOLEAN, uri VARCHAR(255), status INTEGER DEFAULT 1, label1 VARCHAR(255), label2 VARCHAR(255), label3 VARCHAR(255), label4 VARCHAR(255), label5 VARCHAR(255), label6 VARCHAR(255), label7 VARCHAR(255), label8 VARCHAR(255), lastupdatedate TIMESTAMP DEFAULT now() NOT NULL, technology VARCHAR(1024)); CREATE TABLE ps_common.vesselactivity(topiaid VARCHAR(255) NOT NULL CONSTRAINT pk_ps_observation_vesselactivity PRIMARY KEY, topiaversion BIGINT NOT NULL, topiacreatedate TIMESTAMP NOT NULL, code VARCHAR(255), uri VARCHAR(255), needcomment BOOLEAN, status INTEGER DEFAULT 1, label1 VARCHAR(255), label2 VARCHAR(255), label3 VARCHAR(255), label4 VARCHAR(255), label5 VARCHAR(255), label6 VARCHAR(255), label7 VARCHAR(255), label8 VARCHAR(255), lastupdatedate TIMESTAMP DEFAULT now() NOT NULL, allowfad BOOLEAN DEFAULT FALSE NOT NULL); -CREATE TABLE ps_common.trip( topiaid varchar(255) not null constraint pk_trip primary key, topiaversion BIGINT not null, topiacreatedate TIMESTAMP NOT NULL, startdate DATE, enddate DATE, comment VARCHAR(1025), checklevel INTEGER, formsurl varchar(255), reportsurl varchar(255), historicaldata BOOLEAN, ersid varchar(255), ocean varchar(255), observer varchar(255), vessel varchar(255), program varchar(255), captain varchar(255), dataentryoperator varchar(255), departureharbour varchar(255), landingharbour varchar(255), lastupdatedate TIMESTAMP default now() not null, dataquality varchar(255)); +CREATE TABLE ps_common.trip( topiaid varchar(255) not null constraint pk_trip primary key, topiaversion BIGINT not null, topiacreatedate TIMESTAMP NOT NULL, startdate DATE, enddate DATE, comment VARCHAR(1025), checklevel INTEGER, formsurl varchar(255), reportsurl varchar(255), historicaldata BOOLEAN, homeId varchar(255), ersid varchar(255), ocean varchar(255), observer varchar(255), vessel varchar(255), program varchar(255), captain varchar(255), dataentryoperator varchar(255), departureharbour varchar(255), landingharbour varchar(255), lastupdatedate TIMESTAMP default now() not null, dataquality varchar(255)); CREATE TABLE ps_common.gearusefeatures( topiaid varchar(255) not null constraint pk_gearusefeatures primary key, topiaversion BIGINT not null, topiacreatedate DATE, trip varchar(255), gear varchar(255) not null, number INTEGER not null, comment VARCHAR(1025), usedintrip BOOLEAN, lastupdatedate TIMESTAMP default now() not null ); CREATE TABLE ps_common.gearusefeaturesmeasurement( topiaid varchar(255) not null constraint pk_gearusefeaturesmeasurement primary key, topiaversion BIGINT not null, topiacreatedate DATE, gearcaracteristic varchar(255) not null, gearusefeatures varchar(255), measurementvalue varchar(255) not null, lastupdatedate TIMESTAMP default now() not null ); @@ -35,7 +35,7 @@ INSERT INTO ps_common.speciesfate(topiaid, topiaversion, topiacreatedate, code, INSERT INTO ps_common.transmittingbuoyoperation(topiaid, topiaversion, topiacreatedate, code, uri, status, label1, label2, label3, label4, label5, label6, label7, label8, needcomment, lastupdatedate) SELECT topiaid, topiaversion, topiacreatedate, code, uri, status, label1, label2, label3, label4, label5, label6, label7, label8, needcomment, lastupdatedate FROM observe_seine.transmittingbuoyoperation; INSERT INTO ps_common.transmittingbuoytype(topiaid, topiaversion, topiacreatedate, code, needcomment, uri, status, label1, label2, label3, label4, label5, label6, label7, label8, lastupdatedate, technology) SELECT topiaid, topiaversion, topiacreatedate, code, needcomment, uri, status, label1, label2, label3, label4, label5, label6, label7, label8, lastupdatedate, technology FROM observe_seine.transmittingbuoytype; INSERT INTO ps_common.vesselactivity(topiaid, topiaversion, topiacreatedate, code, uri, needcomment, status, label1, label2, label3, label4, label5, label6, label7, label8, lastupdatedate, allowfad) SELECT topiaid, topiaversion, topiacreatedate, code, uri, needcomment, status, label1, label2, label3, label4, label5, label6, label7, label8, lastupdatedate, allowfad FROM observe_seine.vesselactivity; -INSERT INTO ps_common.trip( topiaid, topiaversion, topiacreatedate, startdate , enddate, comment , checklevel, formsurl , reportsurl , historicaldata , ersid , ocean , observer , vessel , program , captain , dataentryoperator , departureharbour , landingharbour , lastupdatedate , dataquality ) SELECT topiaid, topiaversion, topiacreatedate, startdate , enddate, comment , checklevel, formsurl , reportsurl , historicaldata , ersid , ocean , observer , vessel , program , captain , dataentryoperator , departureharbour , landingharbour , lastupdatedate , dataquality FROM observe_seine.trip; +INSERT INTO ps_common.trip( topiaid, topiaversion, topiacreatedate, startdate , enddate, comment , checklevel, formsurl , reportsurl , historicaldata , homeId, ersid , ocean , observer , vessel , program , captain , dataentryoperator , departureharbour , landingharbour , lastupdatedate , dataquality ) SELECT topiaid, topiaversion, topiacreatedate, startdate , enddate, comment , checklevel, formsurl , reportsurl , historicaldata , homeId, ersid , ocean , observer , vessel , program , captain , dataentryoperator , departureharbour , landingharbour , lastupdatedate , dataquality FROM observe_seine.trip; INSERT INTO ps_common.gearusefeatures( topiaid, topiaversion, topiacreatedate, trip, gear , number , comment , usedintrip , lastupdatedate ) SELECT topiaid, topiaversion, topiacreatedate, trip, gear , number , comment , usedintrip , lastupdatedate FROM observe_seine.gearusefeatures; INSERT INTO ps_common.gearusefeaturesmeasurement( topiaid, topiaversion, topiacreatedate, gearcaracteristic , gearusefeatures , measurementvalue , lastupdatedate ) SELECT topiaid, topiaversion, topiacreatedate, gearcaracteristic , gearusefeatures , measurementvalue , lastupdatedate FROM observe_seine.gearusefeaturesmeasurement; ===================================== persistence/src/main/resources/db/migration/8.0/110_add_home_id-common.sql ===================================== @@ -106,7 +106,6 @@ ALTER TABLE ps_common.gearusefeaturesmeasurement ADD COLUMN homeId VARCHAR(255); ALTER TABLE ps_common.speciesfate ADD COLUMN homeId VARCHAR(255); ALTER TABLE ps_common.transmittingbuoyoperation ADD COLUMN homeId VARCHAR(255); ALTER TABLE ps_common.transmittingbuoytype ADD COLUMN homeId VARCHAR(255); -ALTER TABLE ps_common.trip ADD COLUMN homeId VARCHAR(255); ALTER TABLE ps_common.vesselactivity ADD COLUMN homeId VARCHAR(255); ALTER TABLE ps_observation.activity ADD COLUMN homeId VARCHAR(255); ALTER TABLE ps_observation.detectionmode ADD COLUMN homeId VARCHAR(255); ===================================== test/src/main/resources/db/7.5.1/dataForTestLongline.sql.gz ===================================== Binary files /dev/null and b/test/src/main/resources/db/7.5.1/dataForTestLongline.sql.gz differ ===================================== test/src/main/resources/db/7.5.1/dataForTestSeine.sql.gz ===================================== Binary files /dev/null and b/test/src/main/resources/db/7.5.1/dataForTestSeine.sql.gz differ ===================================== test/src/main/resources/db/7.5.1/empty_h2.sql.gz ===================================== Binary files /dev/null and b/test/src/main/resources/db/7.5.1/empty_h2.sql.gz differ ===================================== test/src/main/resources/db/7.5.1/empty_pg.sql.gz ===================================== Binary files /dev/null and b/test/src/main/resources/db/7.5.1/empty_pg.sql.gz differ ===================================== test/src/main/resources/db/7.5.1/referentiel.sql.gz ===================================== Binary files /dev/null and b/test/src/main/resources/db/7.5.1/referentiel.sql.gz differ ===================================== test/src/main/resources/db/8.0/dataForTestLongline.sql.gz ===================================== Binary files a/test/src/main/resources/db/8.0/dataForTestLongline.sql.gz and b/test/src/main/resources/db/8.0/dataForTestLongline.sql.gz differ ===================================== test/src/main/resources/db/8.0/dataForTestSeine.sql.gz ===================================== Binary files a/test/src/main/resources/db/8.0/dataForTestSeine.sql.gz and b/test/src/main/resources/db/8.0/dataForTestSeine.sql.gz differ ===================================== test/src/main/resources/db/8.0/referentiel.sql.gz ===================================== Binary files a/test/src/main/resources/db/8.0/referentiel.sql.gz and b/test/src/main/resources/db/8.0/referentiel.sql.gz differ View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/ab651f885af30e83ce64b5bc43... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/ab651f885af30e83ce64b5bc43... You're receiving this email because of your account on gitlab.com.