[Echobase-commits] 04/05: Create the migration

29 Apr 2016

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 d4bd7c5e71fa02549c99bda33f9979b0fb11e0a2
Author: Julien Ruchaud <julien.ruchaud@debux.org>
Date:   Wed Apr 27 16:29:31 2016 +0200

    Create the migration
---
 .../workingDb/3.910-1-add-new-attributes.sql       | 131 +++++++++++++++++++++
 1 file changed, 131 insertions(+)

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
new file mode 100644
index 0000000..bbe1aae
--- /dev/null
+++ b/echobase-domain/src/main/resources/migration/workingDb/3.910-1-add-new-attributes.sql
@@ -0,0 +1,131 @@
+-- VOYAGE
+alter table voyage add column id 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_8nthgerjt9a0cpjch4p1wpyk3 foreign key (startPort) references port;
+alter table voyage add constraint FK_7ob93mebw1vypuis77py37imy 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, 
+    id varchar(255) not null, 
+    topiaCreateDate date, 
+    name varchar(255) not null, 
+    shortName varchar(255), 
+    lastModification date, 
+    primary key (topiaId)
+);
+
+alter table port add constraint UK_dvgc32pm231nt5p6hnjjpbo9s unique (id);
+
+-- 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_in7bfe4lrs9g9leur9e571efw foreign key (ancillaryInstrumentation) references ancillaryInstrumentation;
+alter table ancillaryinstrumentation_transect add constraint FK_18is89y0wl1dw9yve9ou7hyoc 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_c7xf355ysbxdkx2gce119wpug 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 UK_9740vy6d0qkxwp9f68fckt6hh 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, 
+    primary key (topiaId)
+);
+
+alter table mooring add constraint FK_2wg40f1ueb7vqi573i78fjc8v 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_qjtr5y6ape0bessp8qabd9tv0 foreign key (ancillaryInstrumentation) references ancillaryInstrumentation;
+alter table ancillaryinstrumentation_mooring add constraint FK_si61fa8ai4fm7tm9tlbga9dtb foreign key (mooring) references mooring;
+
+alter table mooring add constraint UK_3ibgwx773wxpe9i5as9w5elqn unique (code);

-- 
To stop receiving notification emails like this one, please contact
codelutin.com SCM administrator <admin+scm@codelutin.com>.

    

[Echobase-commits] 04/05: Create the migration

codelutin.com scm