[Echobase-commits] 10/24: Add inserts for the ports

This is an automated email from the git hooks/post-receive script. New commit to branch feature/#8171 in repository echobase. See https://gitlab.nuiton.org/codelutin/echobase.git commit 257513184f9cf1dd5b16e50e511799582fd23456 Author: Julien Ruchaud <julien.ruchaud@debux.org> Date: Fri Apr 29 15:50:13 2016 +0200 Add inserts for the ports --- .../migration/workingDb/3.902-0-update-model.sql | 87 ++++++++++++++++++++++ .../migration/workingDb/3.902-1-insert-ports.sql | 10 +++ 2 files changed, 97 insertions(+) diff --git a/echobase-domain/src/main/resources/migration/workingDb/3.902-0-update-model.sql b/echobase-domain/src/main/resources/migration/workingDb/3.902-0-update-model.sql new file mode 100644 index 0000000..8031119 --- /dev/null +++ b/echobase-domain/src/main/resources/migration/workingDb/3.902-0-update-model.sql @@ -0,0 +1,87 @@ +-- VOYAGE +alter table voyage add column code varchar(255); +alter table voyage add column northLimit double; +alter table voyage add column eastLimit double; +alter table voyage add column southLimit double; +alter table voyage add column westLimit double; +alter table voyage add column upLimit double; +alter table voyage add column downLimit double; +alter table voyage add column units varchar(255); +alter table voyage add column zUnits varchar(255); +alter table voyage add column comments varchar(255); + +-- Change ports name +alter table voyage alter column startPort rename to startPortName; +alter table voyage alter column endPort rename to endPortName; + +alter table voyage add column startPort varchar(255); +alter table voyage add column endPort varchar(255); + +alter table voyage add constraint FK_VOYAGE_STARTPORT foreign key (startPort) references port; +alter table voyage add constraint FK_VOYAGE_ENDPORT foreign key (endPort) references port; + +-- PORT +create table port (topiaId varchar(255) not null, topiaVersion bigint not null, code varchar(255) not null, topiaCreateDate date, name varchar(255) not null, shortName varchar(255), lastModification date); + +alter table port add constraint PK_PORT primary key (topiaId); +alter table port add constraint UK_PORT_CODE unique (code); + +-- TRANSECT +alter table transect alter column comment rename to comments; +alter table transect add column relatedActivity varchar(255); +alter table transect add column units varchar(255); +alter table transect add column zUnits varchar(255); + +create table ancillaryinstrumentation_transect (transect varchar(255) not null, ancillaryInstrumentation varchar(255) not null); +alter table ancillaryinstrumentation_transect add constraint FK_ANCILLARYINSTTRANSECT_ANCILLARYINST foreign key (ancillaryInstrumentation) references ancillaryInstrumentation; +alter table ancillaryinstrumentation_transect add constraint FK_ANCILLARYINSTTRANSECT_TRANSECT foreign key (transect) references transect; + +-- VESSEL +alter table vessel add column breadth double; +alter table vessel add column comments varchar(255); + +-- DATA ACQUISITION +alter table dataAcquisition add column softwareName varchar(255); +alter table dataAcquisition add column storedDataFormat varchar(255); +alter table dataAcquisition add column comments varchar(255); +alter table dataAcquisition add column mooring varchar(255); + +alter table dataAcquisition add constraint FK_DATAACQUISITION_MOORING foreign key (mooring) references mooring; + +-- ACOUSTIC INSTRUMENT +alter table acousticInstrument add column transducerFrequency double; +alter table acousticInstrument add column transducerPsi double; +alter table acousticInstrument add column transducerBeamAngleMajor double; +alter table acousticInstrument add column transducerBeamAngleMinor double; +alter table acousticInstrument add column transducerBeamManufactuer varchar(255); +alter table acousticInstrument add column comments varchar(255); + +-- DATA PROCESSING +alter table dataProcessing add column softwareName varchar(255); +alter table dataProcessing add column channelId varchar(255); +alter table dataProcessing add column bandWith double; +alter table dataProcessing add column frequency double; +alter table dataProcessing add column transceiverPower double; +alter table dataProcessing add column transmitPulseLength double; +alter table dataProcessing add column transceiverGainUnits varchar(255); +alter table dataProcessing add column comments varchar(255); + +-- CALIBRATION +alter table calibration add column comments varchar(255); + +-- ANCILLARY INSTRUMENTATION +create table ancillaryInstrumentation (topiaId varchar(255) not null, topiaVersion bigint not null, serialNumber varchar(255) not null, topiaCreateDate date, name varchar(255) not null); + +alter table ancillaryInstrumentation add constraint PK_ANCILLARYINST primary key (topiaId); +alter table ancillaryInstrumentation add constraint UK_ANCILLARYINST_SERIALNUMBER unique (serialNumber); + +-- MOORING +create table mooring (topiaId varchar(255) not null, topiaVersion bigint not null, code varchar(255) not null, topiaCreateDate date, description varchar(255) not null, depth double, northLimit double, eastLimit double, southLimit double, westLimit double, upLimit double, downLimit double, units varchar(255), zUnits varchar(255), projection varchar(255), deploymentDate date, retrievalDate date, siteName varchar(255), operator varchar(255), comments varchar(255), mission varchar(255) not null); + +alter table mooring add constraint PK_MOORING primary key (topiaId); +alter table mooring add constraint UK_MOORING_CODE unique (code); +alter table mooring add constraint FK_MOORING_MISSION foreign key (mission) references mission; + +create table ancillaryinstrumentation_mooring (mooring varchar(255) not null, ancillaryInstrumentation varchar(255) not null); +alter table ancillaryinstrumentation_mooring add constraint FK_ANCILLARYINSTMOORING_ANCILLARYINST foreign key (ancillaryInstrumentation) references ancillaryInstrumentation; +alter table ancillaryinstrumentation_mooring add constraint FK_ANCILLARYINSTMOORING_MOORING foreign key (mooring) references mooring; diff --git a/echobase-domain/src/main/resources/migration/workingDb/3.902-1-insert-ports.sql b/echobase-domain/src/main/resources/migration/workingDb/3.902-1-insert-ports.sql new file mode 100644 index 0000000..d190787 --- /dev/null +++ b/echobase-domain/src/main/resources/migration/workingDb/3.902-1-insert-ports.sql @@ -0,0 +1,10 @@ +--=CONCATENER("('fr.ifremer.echobase.entities.references.Port#5553610280597#0.9322615025"; E2; "', 0, '2016-04-29 00:00:00.000', '"; A2; "', '"; B2; "', '"; C2; "', '"; D2; " 00:00:00.000'),") + +insert into port (topiaId, topiaVersion, topiaCreateDate, code, name, shortName, lastModification) values ('fr.ifremer.echobase.entities.references.Port#5553610280597#0.93226150250', 0, '2016-04-29 00:00:00.000', 'BSH10', 'Antofagasta', 'Antofagasta', '2007-08-02 00:00:00.000'),('fr.ifremer.echobase.entities.references.Port#5553610280597#0.93226150251', 0, '2016-04-29 00:00:00.000', 'BSH100', 'Jakarta', 'Jakarta', '2007-08-02 00:00:00.000'),('fr.ifremer.echobase.entities.references.Port# [...] + +-- Change ports name +update voyage v SET (startPort) = (select p.topiaId from port p where upper(p.name) = upper(v.startPortName)); +update voyage v SET (endPort) = (select p.topiaId from port p where upper(p.name) = upper(v.endPortName)); + +alter table voyage drop column startPortName; +alter table voyage drop column endPortName; -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.