[Echobase-commits] 03/05: Create migration for postgresql

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 e749b0bb203376648269a734651f195a3ee31b93 Author: Julien Ruchaud <julien.ruchaud@debux.org> Date: Fri Apr 29 15:57:55 2016 +0200 Create migration for postgresql --- .../workingDb/3.910-1-add-new-attributes.sql | 94 ---------------------- .../workingDb/{ => h2}/3.902-0-update-model.sql | 32 ++++---- .../workingDb/pg/3.902-0-update-model.sql | 87 ++++++++++++++++++++ 3 files changed, 103 insertions(+), 110 deletions(-) diff --git a/echobase-domain/src/main/resources/migration/workingDb/3.910-1-add-new-attributes.sql b/echobase-domain/src/main/resources/migration/workingDb/3.910-1-add-new-attributes.sql deleted file mode 100644 index 5c896e4..0000000 --- a/echobase-domain/src/main/resources/migration/workingDb/3.910-1-add-new-attributes.sql +++ /dev/null @@ -1,94 +0,0 @@ --- 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; - --- /!\ /!\ /!\ /!\ /!\ /!\ Need to import port data /!\ /!\ /!\ /!\ /!\ /!\ -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; - --- 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, primary key (topiaId)); - -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, primary key (topiaId)); - -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 n [...] - -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-0-update-model.sql b/echobase-domain/src/main/resources/migration/workingDb/h2/3.902-0-update-model.sql similarity index 78% rename from echobase-domain/src/main/resources/migration/workingDb/3.902-0-update-model.sql rename to echobase-domain/src/main/resources/migration/workingDb/h2/3.902-0-update-model.sql index 8031119..626fa40 100644 --- a/echobase-domain/src/main/resources/migration/workingDb/3.902-0-update-model.sql +++ b/echobase-domain/src/main/resources/migration/workingDb/h2/3.902-0-update-model.sql @@ -1,11 +1,11 @@ -- 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 northLimit real; +alter table voyage add column eastLimit real; +alter table voyage add column southLimit real; +alter table voyage add column westLimit real; +alter table voyage add column upLimit real; +alter table voyage add column downLimit real; alter table voyage add column units varchar(255); alter table voyage add column zUnits varchar(255); alter table voyage add column comments varchar(255); @@ -37,7 +37,7 @@ alter table ancillaryinstrumentation_transect add constraint FK_ANCILLARYINSTTRA 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 breadth real; alter table vessel add column comments varchar(255); -- DATA ACQUISITION @@ -49,20 +49,20 @@ 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 transducerFrequency real; +alter table acousticInstrument add column transducerPsi real; +alter table acousticInstrument add column transducerBeamAngleMajor real; +alter table acousticInstrument add column transducerBeamAngleMinor real; 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 bandWith real; +alter table dataProcessing add column frequency real; +alter table dataProcessing add column transceiverPower real; +alter table dataProcessing add column transmitPulseLength real; alter table dataProcessing add column transceiverGainUnits varchar(255); alter table dataProcessing add column comments varchar(255); @@ -76,7 +76,7 @@ alter table ancillaryInstrumentation add constraint PK_ANCILLARYINST primary key 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); +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 real, northLimit real, eastLimit real, southLimit real, westLimit real, upLimit real, downLimit real, 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); diff --git a/echobase-domain/src/main/resources/migration/workingDb/pg/3.902-0-update-model.sql b/echobase-domain/src/main/resources/migration/workingDb/pg/3.902-0-update-model.sql new file mode 100644 index 0000000..edf0a11 --- /dev/null +++ b/echobase-domain/src/main/resources/migration/workingDb/pg/3.902-0-update-model.sql @@ -0,0 +1,87 @@ +-- VOYAGE +alter table voyage add column code character varying(255); +alter table voyage add column northLimit real; +alter table voyage add column eastLimit real; +alter table voyage add column southLimit real; +alter table voyage add column westLimit real; +alter table voyage add column upLimit real; +alter table voyage add column downLimit real; +alter table voyage add column units character varying(255); +alter table voyage add column zUnits character varying(255); +alter table voyage add column comments character varying(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 character varying(255); +alter table voyage add column endPort character varying(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 character varying(255) not null, topiaVersion bigint not null, code character varying(255) not null, topiaCreateDate date, name character varying(255) not null, shortName character varying(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 character varying(255); +alter table transect add column units character varying(255); +alter table transect add column zUnits character varying(255); + +create table ancillaryinstrumentation_transect (transect character varying(255) not null, ancillaryInstrumentation character varying(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 real; +alter table vessel add column comments character varying(255); + +-- DATA ACQUISITION +alter table dataAcquisition add column softwareName character varying(255); +alter table dataAcquisition add column storedDataFormat character varying(255); +alter table dataAcquisition add column comments character varying(255); +alter table dataAcquisition add column mooring character varying(255); + +alter table dataAcquisition add constraint FK_DATAACQUISITION_MOORING foreign key (mooring) references mooring; + +-- ACOUSTIC INSTRUMENT +alter table acousticInstrument add column transducerFrequency real; +alter table acousticInstrument add column transducerPsi real; +alter table acousticInstrument add column transducerBeamAngleMajor real; +alter table acousticInstrument add column transducerBeamAngleMinor real; +alter table acousticInstrument add column transducerBeamManufactuer character varying(255); +alter table acousticInstrument add column comments character varying(255); + +-- DATA PROCESSING +alter table dataProcessing add column softwareName character varying(255); +alter table dataProcessing add column channelId character varying(255); +alter table dataProcessing add column bandWith real; +alter table dataProcessing add column frequency real; +alter table dataProcessing add column transceiverPower real; +alter table dataProcessing add column transmitPulseLength real; +alter table dataProcessing add column transceiverGainUnits character varying(255); +alter table dataProcessing add column comments character varying(255); + +-- CALIBRATION +alter table calibration add column comments character varying(255); + +-- ANCILLARY INSTRUMENTATION +create table ancillaryInstrumentation (topiaId character varying(255) not null, topiaVersion bigint not null, serialNumber character varying(255) not null, topiaCreateDate date, name character varying(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 character varying(255) not null, topiaVersion bigint not null, code character varying(255) not null, topiaCreateDate date, description character varying(255) not null, depth real, northLimit real, eastLimit real, southLimit real, westLimit real, upLimit real, downLimit real, units character varying(255), zUnits character varying(255), projection character varying(255), deploymentDate date, retrievalDate date, siteName character varying(255), operator charact [...] + +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 character varying(255) not null, ancillaryInstrumentation character varying(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; -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.