- Echobase-commits - mailman.cloud.codelutin.com

r923 - tags
by tchemit＠users.forge.codelutin.com Dec. 17, 2013

Dec. 17, 2013

Author: tchemit Date: 2013-12-17 09:30:43 +0100 (Tue, 17 Dec 2013) New Revision: 923 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/923 Log: [maven-release-plugin] copy for tag echobase-2.5.1 Added: tags/echobase-2.5.1/

1 0

r922 - in trunk: . echobase-domain echobase-services echobase-ui
by tchemit＠users.forge.codelutin.com Dec. 17, 2013

Dec. 17, 2013

Author: tchemit Date: 2013-12-17 09:30:33 +0100 (Tue, 17 Dec 2013) New Revision: 922 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/922 Log: [maven-release-plugin] prepare release echobase-2.5.1 Modified: trunk/echobase-domain/pom.xml trunk/echobase-services/pom.xml trunk/echobase-ui/pom.xml trunk/pom.xml Modified: trunk/echobase-domain/pom.xml =================================================================== --- trunk/echobase-domain/pom.xml 2013-12-17 07:50:53 UTC (rev 921) +++ trunk/echobase-domain/pom.xml 2013-12-17 08:30:33 UTC (rev 922) @@ -9,7 +9,7 @@ <parent> <groupId>fr.ifremer</groupId> <artifactId>echobase</artifactId> - <version>2.5.1-SNAPSHOT</version> + <version>2.5.1</version> </parent> <groupId>fr.ifremer.echobase</groupId> Modified: trunk/echobase-services/pom.xml =================================================================== --- trunk/echobase-services/pom.xml 2013-12-17 07:50:53 UTC (rev 921) +++ trunk/echobase-services/pom.xml 2013-12-17 08:30:33 UTC (rev 922) @@ -6,7 +6,7 @@ <parent> <groupId>fr.ifremer</groupId> <artifactId>echobase</artifactId> - <version>2.5.1-SNAPSHOT</version> + <version>2.5.1</version> </parent> <groupId>fr.ifremer.echobase</groupId> Modified: trunk/echobase-ui/pom.xml =================================================================== --- trunk/echobase-ui/pom.xml 2013-12-17 07:50:53 UTC (rev 921) +++ trunk/echobase-ui/pom.xml 2013-12-17 08:30:33 UTC (rev 922) @@ -7,7 +7,7 @@ <parent> <groupId>fr.ifremer</groupId> <artifactId>echobase</artifactId> - <version>2.5.1-SNAPSHOT</version> + <version>2.5.1</version> </parent> <groupId>fr.ifremer.echobase</groupId> Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2013-12-17 07:50:53 UTC (rev 921) +++ trunk/pom.xml 2013-12-17 08:30:33 UTC (rev 922) @@ -11,7 +11,7 @@ <groupId>fr.ifremer</groupId> <artifactId>echobase</artifactId> - <version>2.5.1-SNAPSHOT</version> + <version>2.5.1</version> <modules> <module>echobase-domain</module> @@ -84,12 +84,12 @@ </developers> <scm> - <url>https://forge.codelutin.com/repositories/browse/echobase/trunk</url> + <url>https://forge.codelutin.com/repositories/browse/echobase/tags/echobase-2.5.1</url> <connection> - scm:svn:https://forge.codelutin.com/svn/echobase/trunk + scm:svn:https://forge.codelutin.com/svn/echobase/tags/echobase-2.5.1 </connection> <developerConnection> - scm:svn:https://forge.codelutin.com/svn/echobase/trunk + scm:svn:https://forge.codelutin.com/svn/echobase/tags/echobase-2.5.1 </developerConnection> </scm>

1 0

r921 - trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb
by tchemit＠users.forge.codelutin.com Dec. 17, 2013

Dec. 17, 2013

Author: tchemit Date: 2013-12-17 08:50:53 +0100 (Tue, 17 Dec 2013) New Revision: 921 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/921 Log: remvoe old migration Removed: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java Deleted: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java 2013-12-17 02:03:00 UTC (rev 920) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java 2013-12-17 07:50:53 UTC (rev 921) @@ -1,371 +0,0 @@ -package fr.ifremer.echobase.persistence.migration.workingDb; - -/* - * #%L - * EchoBase :: Domain - * $Id$ - * $HeadURL$ - * %% - * Copyright (C) 2011 - 2013 Ifremer, Codelutin - * %% - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero 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 Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - * #L% - */ - -import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; -import org.nuiton.topia.TopiaContext; -import org.nuiton.topia.TopiaException; -import org.nuiton.util.Version; - -import java.util.List; - -/** - * Migrate workgin db to version {@code 2.5}. - * - * @author Tony Chemit <chemit(a)codelutin.com> - * @since 2.5 - */ -public class MigrationCallBackForVersion2_5 extends MigrationCallBackForVersion { - - @Override - public Version getVersion() { - return new Version("2.5"); - } - - @Override - protected void prepareMigrationScript(TopiaContext tx, - List<String> queries, - boolean showSql, - boolean showProgression) throws TopiaException { - - boolean spatialAware; - - TopiaEchoBasePersistenceContext persistenceContext = - new TopiaEchoBasePersistenceContext(tx); - try { - - spatialAware = persistenceContext.isSpatialAware(); - } finally { - persistenceContext.rollbackTransaction(); - } - - if (spatialAware) { - - // add spatial view (http://forge.codelutin.com/issues/3672) - addPostgisView(queries); - - // fix http://forge.codelutin.com/issues/3856 - fixechobase_fill_cell_spatial_tableFunction(queries); - - // fix http://forge.codelutin.com/issues/3973 - fixOperationNameNotFilled(queries); - } - } - - private void fixOperationNameNotFilled(List<String> queries) { - - // fix http://forge.codelutin.com/issues/3973 - queries.add("update echobase_operation_spatial set operationname = (select op.id from operation op where op.topiaid = operationid);"); - } - - private void fixechobase_fill_cell_spatial_tableFunction(List<String> queries) { - - queries.add("CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR)\n" + - " RETURNS VOID AS $$\n" + - " DECLARE cell_type VARCHAR;\n" + - "BEGIN\n" + - "\n" + - " cell_type = echobase_get_cell_type(cell_id);\n" + - "\n" + - " RAISE DEBUG 'Treat spatial cell % [type %] ...', cell_id, cell_type;\n" + - " CASE cell_type\n" + - " WHEN 'ESDU'\n" + - " THEN\n" + - " EXECUTE echobase_fill_esdu_cell_spatial_table(cell_id);\n" + - " WHEN 'ELEMENTARY'\n" + - " THEN\n" + - " EXECUTE echobase_fill_elementary_cell_spatial_table(cell_id);\n" + - " WHEN 'REGION'\n" + - " THEN\n" + - " EXECUTE echobase_fill_region_cell_spatial_table(cell_id);\n" + - " WHEN 'MAP'\n" + - " THEN\n" + - " EXECUTE echobase_fill_map_cell_spatial_table(cell_id);\n" + - " ELSE\n" + - " RAISE LOG 'Can not deal with celltype % (cell %)', cell_type, cell_id;\n" + - " END CASE;\n" + - "END\n" + - "$$\n" + - "LANGUAGE plpgsql;"); - - queries.add("CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_table()\n" + - " RETURNS VOID AS $$\n" + - "DECLARE\n" + - " cellRow RECORD;\n" + - "BEGIN\n" + - " FOR cellRow IN SELECT\n" + - " *\n" + - " FROM echobase_cell_spatial_temp LOOP\n" + - "\n" + - " PERFORM echobase_fill_cell_spatial_row(cellRow.cellid);\n" + - "\n" + - " END LOOP;\n" + - "\n" + - " DELETE FROM echobase_cell_spatial_temp;\n" + - "\n" + - "END\n" + - "$$\n" + - "LANGUAGE plpgsql;"); - } - - private void addPostgisView(List<String> queries) { - - // add cell data view - queries.add("DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE;"); - queries.add("CREATE OR REPLACE VIEW echobase_cell_spatial_data AS\n" + - "SELECT\n" + - " s.*,\n" + - " m.topiaId as metaDataId,\n" + - " m.name as metaDataName,\n" + - " d.datavalue as dataValue,\n" + - " d.topiaid as dataId\n" + - "FROM\n" + - " echobase_cell_spatial s,\n" + - " data d,\n" + - " datametadata m\n" + - "WHERE\n" + - " s.cellid = d.cell AND\n" + - " d.datametadata = m.topiaid;"); - - // add cell result view - queries.add("DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE;"); - queries.add("CREATE OR REPLACE VIEW echobase_cell_spatial_result AS\n" + - "SELECT\n" + - " s.*,\n" + - " m.topiaId as metaDataId,\n" + - " m.name as metaDataName,\n" + - " r.resultvalue as resultValue,\n" + - " r.topiaid as resultId\n" + - "FROM\n" + - " echobase_cell_spatial s,\n" + - " result r,\n" + - " datametadata m\n" + - "WHERE\n" + - " s.cellid = r.cell AND\n" + - " r.datametadata = m.topiaid;"); - - // add operation data view - queries.add("DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE;"); - queries.add("CREATE OR REPLACE VIEW TotalSampleEngrEnc AS\n" + - "\n" + - "SELECT \n" + - " echobase_operation_spatial.voyageid, \n" + - " operation.id AS operation_id, \n" + - " echobase_operation_spatial.coordinate, \n" + - " sampletype.name AS sampletype_name, \n" + - " sample.sampleweight, \n" + - " sizecategory.name AS sizecategory_name, \n" + - " species.baracoudacode, \n" + - " sample.topiaid\n" + - "FROM\n" + - " public.echobase_operation_spatial, \n" + - " public.operation, \n" + - " public.sample, \n" + - " public.speciescategory, \n" + - " public.sizecategory, \n" + - " public.species, \n" + - " public.sampletype\n" + - "WHERE \n" + - " echobase_operation_spatial.operationid = operation.topiaid AND\n" + - " operation.topiaid = sample.operation AND\n" + - " sample.speciescategory = speciescategory.topiaid AND\n" + - " sample.sampletype = sampletype.topiaid AND\n" + - " speciescategory.sizecategory = sizecategory.topiaid AND\n" + - " speciescategory.species = species.topiaid AND\n" + - " sampletype.name = 'Total' AND\n" + - " species.baracoudacode='ENGR-ENC';\n"); - - queries.add("DROP VIEW IF EXISTS TotalSampleSardPil CASCADE;"); - queries.add("CREATE OR REPLACE VIEW TotalSampleSardPil AS\n" + - "\n" + - "SELECT \n" + - " echobase_operation_spatial.voyageid, \n" + - " operation.id AS operation_id, \n" + - " echobase_operation_spatial.coordinate, \n" + - " sampletype.name AS sampletype_name, \n" + - " sample.sampleweight, \n" + - " sizecategory.name AS sizecategory_name, \n" + - " species.baracoudacode, \n" + - " sample.topiaid\n" + - "FROM \n" + - " public.echobase_operation_spatial, \n" + - " public.operation, \n" + - " public.sample, \n" + - " public.speciescategory, \n" + - " public.sizecategory, \n" + - " public.species, \n" + - " public.sampletype\n" + - "WHERE \n" + - " echobase_operation_spatial.operationid = operation.topiaid AND\n" + - " operation.topiaid = sample.operation AND\n" + - " sample.speciescategory = speciescategory.topiaid AND\n" + - " sample.sampletype = sampletype.topiaid AND\n" + - " speciescategory.sizecategory = sizecategory.topiaid AND\n" + - " speciescategory.species = species.topiaid AND\n" + - " sampletype.name = 'Total' AND\n" + - " species.baracoudacode='SARD-PIL';"); - - queries.add("DROP VIEW IF EXISTS TotalSampleTracTru CASCADE;"); - queries.add("CREATE OR REPLACE VIEW TotalSampleTracTru AS\n" + - "\n" + - "SELECT \n" + - " echobase_operation_spatial.voyageid, \n" + - " operation.id AS operation_id, \n" + - " echobase_operation_spatial.coordinate, \n" + - " sampletype.name AS sampletype_name, \n" + - " sample.sampleweight, \n" + - " sizecategory.name AS sizecategory_name, \n" + - " species.baracoudacode, \n" + - " sample.topiaid\n" + - "FROM \n" + - " public.echobase_operation_spatial, \n" + - " public.operation, \n" + - " public.sample, \n" + - " public.speciescategory, \n" + - " public.sizecategory, \n" + - " public.species, \n" + - " public.sampletype\n" + - "WHERE \n" + - " echobase_operation_spatial.operationid = operation.topiaid AND\n" + - " operation.topiaid = sample.operation AND\n" + - " sample.speciescategory = speciescategory.topiaid AND\n" + - " sample.sampletype = sampletype.topiaid AND\n" + - " speciescategory.sizecategory = sizecategory.topiaid AND\n" + - " speciescategory.species = species.topiaid AND\n" + - " sampletype.name = 'Total' AND\n" + - " species.baracoudacode='TRAC-TRU';"); - - queries.add("DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE;"); - queries.add("CREATE OR REPLACE VIEW TotalSampleSpraSpr AS\n" + - "\n" + - "SELECT \n" + - " echobase_operation_spatial.voyageid, \n" + - " operation.id AS operation_id, \n" + - " echobase_operation_spatial.coordinate, \n" + - " sampletype.name AS sampletype_name, \n" + - " sample.sampleweight, \n" + - " sizecategory.name AS sizecategory_name, \n" + - " species.baracoudacode, \n" + - " sample.topiaid\n" + - "FROM \n" + - " public.echobase_operation_spatial, \n" + - " public.operation, \n" + - " public.sample, \n" + - " public.speciescategory, \n" + - " public.sizecategory, \n" + - " public.species, \n" + - " public.sampletype\n" + - "WHERE \n" + - " echobase_operation_spatial.operationid = operation.topiaid AND\n" + - " operation.topiaid = sample.operation AND\n" + - " sample.speciescategory = speciescategory.topiaid AND\n" + - " sample.sampletype = sampletype.topiaid AND\n" + - " speciescategory.sizecategory = sizecategory.topiaid AND\n" + - " speciescategory.species = species.topiaid AND\n" + - " sampletype.name = 'Total' AND\n" + - " species.baracoudacode='SPRA-SPR';"); - - queries.add("DROP VIEW IF EXISTS TotalSampleScomSco CASCADE;"); - queries.add("CREATE OR REPLACE VIEW TotalSampleScomSco AS\n" + - "\n" + - "SELECT \n" + - " echobase_operation_spatial.voyageid, \n" + - " operation.id AS operation_id, \n" + - " echobase_operation_spatial.coordinate, \n" + - " sampletype.name AS sampletype_name, \n" + - " sample.sampleweight, \n" + - " sizecategory.name AS sizecategory_name, \n" + - " species.baracoudacode, \n" + - " sample.topiaid\n" + - "FROM \n" + - " public.echobase_operation_spatial, \n" + - " public.operation, \n" + - " public.sample, \n" + - " public.speciescategory, \n" + - " public.sizecategory, \n" + - " public.species, \n" + - " public.sampletype\n" + - "WHERE \n" + - " echobase_operation_spatial.operationid = operation.topiaid AND\n" + - " operation.topiaid = sample.operation AND\n" + - " sample.speciescategory = speciescategory.topiaid AND\n" + - " sample.sampletype = sampletype.topiaid AND\n" + - " speciescategory.sizecategory = sizecategory.topiaid AND\n" + - " speciescategory.species = species.topiaid AND\n" + - " sampletype.name = 'Total' AND\n" + - " species.baracoudacode='SCOM-SCO';"); - - queries.add("DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE;"); - queries.add("CREATE OR REPLACE VIEW TotalSampleMicrPou AS\n" + - "\n" + - "SELECT \n" + - " echobase_operation_spatial.voyageid, \n" + - " operation.id AS operation_id, \n" + - " echobase_operation_spatial.coordinate, \n" + - " sampletype.name AS sampletype_name, \n" + - " sample.sampleweight, \n" + - " sizecategory.name AS sizecategory_name, \n" + - " species.baracoudacode, \n" + - " sample.topiaid\n" + - "FROM \n" + - " public.echobase_operation_spatial, \n" + - " public.operation, \n" + - " public.sample, \n" + - " public.speciescategory, \n" + - " public.sizecategory, \n" + - " public.species, \n" + - " public.sampletype\n" + - "WHERE \n" + - " echobase_operation_spatial.operationid = operation.topiaid AND\n" + - " operation.topiaid = sample.operation AND\n" + - " sample.speciescategory = speciescategory.topiaid AND\n" + - " sample.sampletype = sampletype.topiaid AND\n" + - " speciescategory.sizecategory = sizecategory.topiaid AND\n" + - " speciescategory.species = species.topiaid AND\n" + - " sampletype.name = 'Total' AND\n" + - " species.baracoudacode='MICR-POU';"); - - queries.add("DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE;"); - queries.add("CREATE OR REPLACE VIEW TotalCatchSpOpWide AS\n" + - "\n" + - "SELECT\n" + - " echobase_operation_spatial.voyagename,\n" + - " echobase_operation_spatial.operationname,\n" + - " echobase_operation_spatial.coordinate,\n" + - " (Select SUM(totalsampleengrenc.sampleweight) From totalsampleengrenc Where totalsampleengrenc.operation_id = echobase_operation_spatial.operationname) as TotalCatchENGRENC,\n" + - " (Select SUM(totalsamplesardpil.sampleweight) From totalsamplesardpil Where totalsamplesardpil.operation_id = echobase_operation_spatial.operationname) as TotalCatchSARDPIL,\n" + - " (Select SUM(totalsamplespraspr.sampleweight) From totalsamplespraspr Where totalsamplespraspr.operation_id = echobase_operation_spatial.operationname) as TotalCatchSPRASPR,\n" + - " (Select SUM(totalsamplemicrpou.sampleweight) From totalsamplemicrpou Where totalsamplemicrpou.operation_id = echobase_operation_spatial.operationname) as TotalCatchMICRPOU,\n" + - " (Select SUM(totalsamplescomsco.sampleweight) From totalsamplescomsco Where totalsamplescomsco.operation_id = echobase_operation_spatial.operationname) as TotalCatchSCOMSCO,\n" + - " (Select SUM(totalsampletractru.sampleweight) From totalsampletractru Where totalsampletractru.operation_id = echobase_operation_spatial.operationname) as TotalCatchTRACTRU,\n" + - " echobase_operation_spatial.operationid\n" + - "From echobase_operation_spatial;"); - - queries.add("DROP VIEW IF EXISTS cellmapview CASCADE;"); - queries.add("CREATE OR REPLACE VIEW cellmapview AS \n" + - " SELECT echobase_cell_spatial.voyagename, echobase_cell_spatial.coordinate, echobase_cell_spatial.shape, datametadata.name, result.resultvalue, species.baracoudacode, echobase_cell_spatial.cellname, result.topiaid AS resultid\n" + - " FROM echobase_cell_spatial, result, datametadata, category, speciescategory, species\n" + - " WHERE echobase_cell_spatial.cellid::text = result.cell::text AND result.datametadata::text = datametadata.topiaid::text AND result.category::text = category.topiaid::text AND category.speciescategory::text = speciescategory.topiaid::text AND speciescategory.species::text = species.topiaid::text AND echobase_cell_spatial.celltypename::text = 'Map cell'::text;"); - } -}

1 0

r920 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/entities echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-domain/src/main/resources echobase-domain/src/main/resources/migration echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com Dec. 17, 2013

Dec. 17, 2013

Author: tchemit Date: 2013-12-17 03:03:00 +0100 (Tue, 17 Dec 2013) New Revision: 920 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/920 Log: fix migration + isolate migration script Added: trunk/echobase-domain/src/main/resources/migration/ trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.5.1.sql trunk/echobase-domain/src/main/resources/migration/postgis-view-2.5.1.sql Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/EchoBasePersistenceContext.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java trunk/echobase-services/src/main/resources/postgis-structure.sql Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/EchoBasePersistenceContext.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/EchoBasePersistenceContext.java 2013-12-17 01:53:04 UTC (rev 919) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/EchoBasePersistenceContext.java 2013-12-17 02:03:00 UTC (rev 920) @@ -36,9 +36,5 @@ */ public interface EchoBasePersistenceContext extends TopiaTransaction, TopiaPersistenceContext, TopiaSqlSupport { - String POSTGIS_STRUCTURE_SQL = "/postgis-structure.sql"; - - String POSTGIS_VIEW_SQL = "/postgis-view.sql"; - TopiaContext getContext(); } Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java 2013-12-17 01:53:04 UTC (rev 919) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java 2013-12-17 02:03:00 UTC (rev 920) @@ -40,6 +40,10 @@ */ public class MigrationCallBackForVersion2_5_1 extends MigrationCallBackForVersion { + public static final String POSTGIS_STRUCTURE_SQL = "/migration/postgis-structure-2.5.1.sql"; + + public static final String POSTGIS_VIEW_SQL = "/migration/postgis-view-2.5.1.sql"; + @Override public Version getVersion() { return new Version("2.5.1"); @@ -70,7 +74,7 @@ } protected void updatePostgis(List<String> queries) { - queries.add(EchoBaseIOUtil.loadScript(EchoBasePersistenceContext.POSTGIS_STRUCTURE_SQL)); - queries.add(EchoBaseIOUtil.loadScript(EchoBasePersistenceContext.POSTGIS_VIEW_SQL)); + queries.add(EchoBaseIOUtil.loadScript(POSTGIS_STRUCTURE_SQL)); + queries.add(EchoBaseIOUtil.loadScript(POSTGIS_VIEW_SQL)); } } Copied: trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.5.1.sql (from rev 919, trunk/echobase-services/src/main/resources/postgis-structure.sql) =================================================================== --- trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.5.1.sql (rev 0) +++ trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.5.1.sql 2013-12-17 02:03:00 UTC (rev 920) @@ -0,0 +1,1218 @@ +--- +-- #%L +-- EchoBase :: UI +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2013 Ifremer, Codelutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero 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 Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- +CREATE EXTENSION IF NOT EXISTS postgis; + +DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; +DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE; +DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; +DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; +DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; +DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; +DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; +DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; +DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; +DROP VIEW IF EXISTS cellmapview CASCADE; + +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate3D_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_shape_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_voyageid_idx CASCADE; +DROP TABLE IF EXISTS echobase_cell_spatial CASCADE; +DROP TABLE IF EXISTS echobase_cell_spatial_temp CASCADE; + +DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_temp_table() CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_cell ON cell CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_cell() CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_cell_spatial_data() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_row(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_esdu_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_elementary_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_region_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_map_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_create_echobase_cell_spatial_row( +cell_id VARCHAR, +coordinateText VARCHAR, +coordinate3dText VARCHAR, +shapeText VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_get_cell_type(cell_id VARCHAR) CASCADE; + + +DROP INDEX IF EXISTS echobase_operation_spatial_coordinate_gix; +DROP INDEX IF EXISTS echobase_operation_spatial_voyageid_idx; +DROP TABLE IF EXISTS echobase_operation_spatial CASCADE ; +DROP TABLE IF EXISTS echobase_operation_spatial_temp CASCADE; +DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_temp_table() CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_operation ON operation CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_operation() CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_operation_spatial_data() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_row(operation_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_create_echobase_operation_spatial_row( +operation_id VARCHAR, +coordinateText VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_latitudetext_to_dd(latitude VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_longitudetext_to_dd(longitude VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_dms2dd(D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)) CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_spatial_data() CASCADE; + +-------------------------------------------------------------------------------- +-- Table spatial pour les cellules --------------------------------------------- +-------------------------------------------------------------------------------- + +CREATE TABLE echobase_cell_spatial ( + voyageid VARCHAR(256) NOT NULL, + voyagename VARCHAR(256), + transitid VARCHAR(256), + transitname VARCHAR(256), + transectid VARCHAR(256), + transectname VARCHAR(256), + dataAcquisitionid VARCHAR(256), + dataAcquisitionname VARCHAR(256), + dataProcessingid VARCHAR(256), + dataProcessingname VARCHAR(256), + celltypeid VARCHAR(256) NOT NULL, + celltypename VARCHAR(256) NOT NULL, + cellid VARCHAR(256) PRIMARY KEY, + cellname VARCHAR(256) NOT NULL, + lastUpdateDate TIMESTAMP NOT NULL, + FOREIGN KEY (cellid) REFERENCES cell (topiaid), + FOREIGN KEY (celltypeid) REFERENCES celltype (topiaid), + FOREIGN KEY (dataacquisitionid) REFERENCES dataacquisition (topiaid), + FOREIGN KEY (dataprocessingid) REFERENCES dataprocessing (topiaid), + FOREIGN KEY (transectid) REFERENCES transect (topiaid), + FOREIGN KEY (transitid) REFERENCES transit (topiaid), + FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) +); + +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate', 4326, 'POINT',2 ); +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3d', 4326, 'POINT',3); +SELECT AddGeometryColumn('echobase_cell_spatial', 'shape', 4326, 'POLYGON',2 ); + +CREATE INDEX echobase_cell_spatial_coordinate_gix ON echobase_cell_spatial USING GIST (coordinate); +CREATE INDEX echobase_cell_spatial_coordinate3D_gix ON echobase_cell_spatial USING GIST (coordinate3d); +CREATE INDEX echobase_cell_spatial_shape_gix ON echobase_cell_spatial USING GIST (shape); +CREATE INDEX echobase_cell_spatial_voyageid_idx ON echobase_cell_spatial USING BTREE(voyageid); + +-- pour stoquer les traitements spatiaux a effectuer + +CREATE TABLE echobase_cell_spatial_temp ( + cellid VARCHAR(256) PRIMARY KEY, + celltype VARCHAR(256) NOT NULL, + FOREIGN KEY (cellid) REFERENCES cell (topiaid) +); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire +-- + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + cell_id VARCHAR; + data_type_id VARCHAR; + cellType VARCHAR; + dataType VARCHAR; + doInsert BOOLEAN; + result RECORD; +BEGIN + + IF (TG_OP = 'DELETE') + THEN + result = OLD; + ELSE + result = NEW; + END IF; + + cell_id = result.cell; + data_type_id = result.datametadata; + doInsert = FALSE; +-- recuperation du type de la cellule + cellType = echobase_get_cell_type(cell_id); + + IF (SELECT + count(*) + FROM echobase_cell_spatial_temp c + WHERE c.cellid = cell_id) > 0 + THEN +-- la cellule est deja a traitee + RETURN result; + END IF; +-- recuperation du type de la la data + SELECT + dt.name + INTO dataType + FROM datametadata dt + WHERE dt.topiaId = data_type_id; + + CASE cellType + WHEN 'ESDU' + THEN +-- Cell of type Esdu + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'ELEMENTARY' + THEN +-- Cell of type Elementary + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'REGION', 'REGIONSURF', 'REGIONCLAS' + THEN +-- Cell of type Region + CASE dataType + WHEN 'RegionEnvCoordinates' + THEN + doInsert = TRUE; + cellType = 'REGION'; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'MAP' + THEN +-- Cell of type Map + CASE dataType + WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + END CASE; + + IF doInsert = TRUE + THEN +--- Ajout de la cellule dans la table des traitements à effectuer + RAISE DEBUG 'Add cell % [type %] to echobase_cell_spatial_temp', cell_id, cellType; + INSERT INTO echobase_cell_spatial_temp (cellid, celltype) VALUES (cell_id, cellType); + END IF; + + RETURN result; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_fill_cell_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON data +FOR EACH ROW WHEN (NEW.cell IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_cell_spatial_temp_table(); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire lors d +-- 'une suppression de cellule +-- + +CREATE OR REPLACE FUNCTION echobase_delete_cell() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE cellid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_cell +BEFORE DELETE ON cell +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell(); + +-- +-- Mettre a jour toutes les données spatiales de cellule +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_cell_spatial_data() + RETURNS VOID AS $$ +DECLARE cell_id VARCHAR; +BEGIN + DELETE FROM echobase_cell_spatial; + + FOR cell_id IN SELECT topiaid FROM cell LOOP + PERFORM echobase_fill_cell_spatial_row(cell_id); + END LOOP; +END +$$ +LANGUAGE plpgsql; + +-- +-- Mettre a jour la table echobase_cell_spatial depuis echobase_cell_spatial_temp +-- + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_table() + RETURNS VOID AS $$ +DECLARE + cellRow RECORD; +BEGIN + FOR cellRow IN SELECT + * + FROM echobase_cell_spatial_temp LOOP + + PERFORM echobase_fill_cell_spatial_row(cellRow.cellid); + + END LOOP; + + DELETE FROM echobase_cell_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR) + RETURNS VOID AS $$ + DECLARE cell_type VARCHAR; +BEGIN + + cell_type = echobase_get_cell_type(cell_id); + + RAISE DEBUG 'Treat spatial cell % [type %] ...', cell_id, cell_type; + CASE cell_type + WHEN 'ESDU' + THEN + EXECUTE echobase_fill_esdu_cell_spatial_table(cell_id); + WHEN 'ELEMENTARY' + THEN + EXECUTE echobase_fill_elementary_cell_spatial_table(cell_id); + WHEN 'REGION' + THEN + EXECUTE echobase_fill_region_cell_spatial_table(cell_id); + WHEN 'MAP' + THEN + EXECUTE echobase_fill_map_cell_spatial_table(cell_id); + ELSE + RAISE LOG 'Can not deal with celltype % (cell %)', cell_type, cell_id; + END CASE; +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table( + cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth RECORD; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE DEBUG 'Treat esdu cell % ', cell_id; +-- test if start / bary / end event + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; + IF FOUND + THEN +-- start data + RAISE DEBUG 'Treat esdu Start cell % ', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; + ELSE + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; + IF FOUND + THEN +-- bary data + RAISE DEBUG 'Treat esdu Bary cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; + ELSE + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; + IF FOUND + THEN +-- end data + RAISE DEBUG 'Treat End cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; + ELSE +-- no spatial data + RAISE LOG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; + + IF latitude IS NOT NULL AND latitudeNumber IS NULL + THEN + latitudeNumber := latitude :: REAL; + END IF; + +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + IF longitude IS NOT NULL AND longitudeNumber IS NULL + THEN + longitudeNumber := longitude :: REAL; + END IF; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + spatialText, + NULL, + NULL); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_elementary_cell_spatial_table( + cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth VARCHAR; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE DEBUG 'Treat elementary cell % ', cell_id; +-- try start elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; + IF FOUND + THEN +-- this is a start elementary + RAISE DEBUG 'Treat elementary Start cell % ', cell_id; +-- get longitude + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; +-- get depth (try first surface one) + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceStart'; + IF NOT FOUND + THEN +-- try then bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomStart'; + END IF; + ELSE +-- try bary elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; + IF FOUND + THEN +-- this is a bary elementary + RAISE DEBUG 'Treat elementary Bary cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceBary'; + IF NOT FOUND + THEN +-- use depth bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomBary'; + END IF; + ELSE +-- try end elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; + IF FOUND + THEN +-- this is a end elementary + RAISE DEBUG 'Treat elementary End cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceEnd'; + IF NOT FOUND + THEN +-- use depth bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomEnd'; + END IF; + ELSE +-- no spatial data + RAISE DEBUG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; + + IF latitude IS NOT NULL AND latitudeNumber IS NULL + THEN + latitudeNumber := latitude :: REAL; + END IF; + +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + IF longitude IS NOT NULL AND longitudeNumber IS NULL + THEN + longitudeNumber := longitude :: REAL; + END IF; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ' ' || + depth || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + spatialText, + NULL); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_region_cell_spatial_table(cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude REAL; + longitude REAL; + depth REAL; + dataMetadataId VARCHAR; + dataValue VARCHAR; + spatialText VARCHAR := 'POLYGON(('; +BEGIN + RAISE DEBUG 'Treat region cell %', cell_id; + SELECT + topiaid + INTO dataMetadataId + FROM datametadata + WHERE name = 'RegionEnvCoordinates'; + FOR dataValue IN SELECT + d.datavalue + FROM data d + WHERE d.cell = cell_id AND d.datametadata = dataMetadataId + ORDER BY d.topiacreatedate LOOP +-- split dataValue in lat - long - depth + SELECT + split_part(dataValue, ' ', 1) :: REAL + INTO latitude; + SELECT + split_part(dataValue, ' ', 2) :: REAL + INTO longitude; + SELECT + split_part(dataValue, ' ', 3) :: REAL + INTO depth; + SELECT + spatialText || longitude || ' ' || latitude || ',' + INTO spatialText; + END LOOP; + SELECT + left(spatialText, -1) || '))' + INTO spatialText; + + SELECT 'SRID=4326;' || spatialText INTO spatialText; + + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + NULL, + spatialText); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_map_cell_spatial_table(cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude REAL; + longitude REAL; + deltaLatitude REAL; + deltaLongitude REAL; + P0 VARCHAR; + P1 VARCHAR; + P2 VARCHAR; + P3 VARCHAR; + spatialText VARCHAR; +BEGIN + RAISE DEBUG 'Treat map cell % ', cell_id; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridCellLatitude'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridCellLongitude'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO deltaLatitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridLatitudeLag'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO deltaLongitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridLongitudeLag'; + + IF + latitude IS NOT NULL AND longitude IS NOT NULL AND deltaLatitude IS NOT NULL + AND deltaLongitude IS NOT NULL + THEN + SELECT + longitude || ' ' || latitude + INTO P0; + SELECT + longitude || ' ' || latitude + deltaLatitude + INTO P1; + SELECT + longitude + deltaLongitude || ' ' || latitude + deltaLatitude + INTO P2; + SELECT + longitude + deltaLongitude || ' ' || latitude + INTO P3; + + SELECT + 'SRID=4326;POLYGON((' || p0 || ',' || P1 || ',' || P2 || ',' || P3 || ',' || P0 || + '))' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + NULL, + spatialText); + END IF; +END +$$ LANGUAGE plpgsql; + +-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial + +CREATE OR REPLACE FUNCTION echobase_create_echobase_cell_spatial_row( + cell_id VARCHAR, + coordinateText VARCHAR, + coordinate3dText VARCHAR, + shapeText VARCHAR) + RETURNS VOID AS $$ +DECLARE + cellSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + dataAcquisitionId VARCHAR; + dataAcquisitionName VARCHAR; + dataProcessingId VARCHAR; + dataProcessingName VARCHAR; + cellName VARCHAR; + cellTypeId VARCHAR; + cellTypeName VARCHAR; + cellParentId VARCHAR; + cellRow RECORD; + coordinateData GEOMETRY; + coordinate3dData GEOMETRY; + shapeData GEOMETRY; +BEGIN + IF coordinateText IS NULL AND coordinate3dText IS NULL AND + shapeText IS NULL + THEN + RAISE DEBUG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + IF coordinateText IS NOT NULL + THEN + BEGIN + coordinateData = ST_GeomFromEWKT(coordinateText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create coordinate spatial data %', coordinateText; + RETURN; + END; + ELSEIF coordinate3dText IS NOT NULL + THEN + BEGIN + coordinate3dData = ST_GeomFromEWKT(coordinate3dText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create coordinate3D spatial data %', coordinate3dText; + RETURN; + END; + ELSEIF shapeText IS NOT NULL + THEN + BEGIN + shapeData = ST_GeomFromEWKT(shapeText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create shape spatial data %', shapeText; + RETURN; + END; + END IF; + + SELECT + * + INTO cellSpatialRow + FROM echobase_cell_spatial cs + WHERE cs.cellid = cell_id; + IF NOT FOUND + THEN +-- create row + RAISE DEBUG 'Will create spatial cell %', cell_id; + SELECT + cell_id + INTO cellParentId; + LOOP + IF dataProcessingId IS NULL + THEN +-- try to get dataprocessingId from this cell + SELECT + dp.topiaid, + dp.processingdescription + INTO dataProcessingId + FROM dataprocessing dp, cell c + WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing; + END IF; + SELECT + topiaid, + cell + INTO cellRow + FROM cell + WHERE topiaid = cellParentId; + EXIT WHEN cellRow.cell IS NULL; + SELECT + cellRow.cell + INTO cellParentId; + END LOOP; + RAISE DEBUG 'use cell parentId %', cellParentId; +-- get cell infos + SELECT + c.name, + ct.name, + ct.topiaid + INTO cellName, cellTypeName, cellTypeId + FROM cell c, celltype ct + WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid; + + IF dataProcessingId IS NULL + THEN +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, cell c + WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage; + ELSE + +-- get dataAcquisition infos + SELECT + da.topiaid, + da.acousticinstrument + INTO dataAcquisitionId, dataAcquisitionName + FROM dataacquisition da, dataprocessing dp + WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, dataacquisition da + WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect; +-- get transit infos + SELECT + t.topiaid, + (t.starttime || ' - ' || t.endtime) + INTO transitId, transitName + FROM transit t, transect tt + WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, transit t + WHERE t.topiaid = transitId AND v.topiaid = t.voyage; + END IF; + INSERT INTO echobase_cell_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3d, shape) + VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cellTypeId, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial cell % ', cell_id; + UPDATE echobase_cell_spatial + SET coordinate = coordinateData, + coordinate3d = coordinate3dData, + shape = shapeData + WHERE cellid = cell_id; + END IF; +END +$$ LANGUAGE plpgsql; + +-- pour obtenir le type d'une cellule sous forme ESDU - ELEMENTARY - REGION ou MAP +CREATE OR REPLACE FUNCTION echobase_get_cell_type(cell_id VARCHAR) + RETURNS VARCHAR AS $$ +DECLARE result VARCHAR; +BEGIN + SELECT + UPPER(ct.id) + INTO result + FROM celltype ct, cell ce + WHERE ce.topiaid = cell_id AND ct.topiaId = ce.celltype; + IF 'REGIONCLAS' = result OR 'REGIONSURF' = result + THEN + result = 'REGION'; + END IF; + RETURN result; +END +$$ +LANGUAGE plpgsql; + +-------------------------------------------------------------------------------- +-- Table spatial pour les operations ------------------------------------------- +-------------------------------------------------------------------------------- + +CREATE TABLE echobase_operation_spatial ( + voyageid VARCHAR(256) NOT NULL, + voyagename VARCHAR(256), + transitid VARCHAR(256), + transitname VARCHAR(256), + transectid VARCHAR(256), + transectname VARCHAR(256), + operationid VARCHAR(256) PRIMARY KEY, + operationname VARCHAR(256) NOT NULL, + lastUpdateDate TIMESTAMP NOT NULL, + FOREIGN KEY (operationid) REFERENCES operation (topiaid), + FOREIGN KEY (transectid) REFERENCES transect (topiaid), + FOREIGN KEY (transitid) REFERENCES transit (topiaid), + FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) +); + +SELECT AddGeometryColumn('echobase_operation_spatial', 'coordinate', 4326, 'POINT',2 ); + +CREATE INDEX echobase_operation_spatial_coordinate_gix ON echobase_operation_spatial USING GIST (coordinate); +CREATE INDEX echobase_operation_spatial_voyageid_idx ON echobase_operation_spatial USING BTREE(voyageid); + +-- pour stoquer les traitements spatiaux a effectuer + +CREATE TABLE echobase_operation_spatial_temp ( + operationid VARCHAR(256) PRIMARY KEY, + FOREIGN KEY (operationid) REFERENCES operation (topiaid) +); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + operation_id VARCHAR; + result RECORD; +BEGIN + + IF (TG_OP = 'DELETE') + THEN + result = OLD; + ELSE + result = NEW; + END IF; + + operation_id = result.topiaid; + + IF (SELECT count(*) FROM echobase_operation_spatial_temp c + WHERE c.operationid = operation_id) > 0 + THEN +-- operation est deja a traitee + RETURN result; + END IF; +--- Ajout de l'operation dans la table des traitements à effectuer + RAISE DEBUG 'Add operation % to echobase_operation_spatial_temp', operation_id; + INSERT INTO echobase_operation_spatial_temp (operationid) VALUES (operation_id); + RETURN result; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_fill_operation_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON operation +FOR EACH ROW WHEN (NEW.topiaid IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_operation_spatial_temp_table(); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire lors de la +-- suppression d'une operation +-- + +CREATE OR REPLACE FUNCTION echobase_delete_operation() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete operation % , delete cascade in echobase_operation_spatial_table', OLD.topiaid; + + DELETE FROM echobase_operation_spatial WHERE operationid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_operation +BEFORE DELETE ON operation +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_operation(); + +-- +-- Mettre a jour toutes les données spatiales d'operation +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_operation_spatial_data() + RETURNS VOID AS $$ +DECLARE operation_id VARCHAR; +BEGIN + DELETE FROM echobase_operation_spatial; + + FOR operation_id IN SELECT topiaid FROM operation LOOP + PERFORM echobase_fill_operation_spatial_row(operation_id); + END LOOP; +END +$$ +LANGUAGE plpgsql; + +-- +-- Mettre a jour la table echobase_cell_spatial depuis echobase_operation_spatial_temp +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_table() + RETURNS VOID AS $$ +DECLARE + operationRow RECORD; +BEGIN + FOR operationRow IN SELECT * FROM echobase_operation_spatial_temp LOOP + + PERFORM echobase_fill_operation_spatial_row(operationRow.operationid); + END LOOP; + + DELETE FROM echobase_operation_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_row(operation_id VARCHAR) + RETURNS VOID AS $$ + DECLARE + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE NOTICE 'Treat spatial operation % ...', operation_id; + SELECT o.midHaulLatitude, o.midHaulLongitude + INTO latitudeNumber, longitudeNumber FROM operation o + WHERE o.topiaid = operation_id; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for operation %', spatialText, operation_id; + + PERFORM echobase_create_echobase_operation_spatial_row(operation_id, + spatialText); +END +$$ +LANGUAGE plpgsql; + +-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial + +CREATE OR REPLACE FUNCTION echobase_create_echobase_operation_spatial_row( + operation_id VARCHAR, + coordinateText VARCHAR) + RETURNS VOID AS $$ +DECLARE + operationSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + operationId VARCHAR; + operationName VARCHAR; + operationRow RECORD; + coordinateData GEOMETRY; +BEGIN + IF coordinateText IS NULL + THEN + RAISE DEBUG 'Could not find spatial data for operation %', operation_id; + RETURN; + END IF; + BEGIN + coordinateData = ST_GeomFromEWKT(coordinateText); + EXCEPTION WHEN internal_error + THEN + RAISE LOG 'Could not create coordinate operation spatial data %', coordinateText; + RETURN; + END; + + SELECT * INTO operationSpatialRow FROM echobase_operation_spatial os + WHERE os.operationid = operation_id; + IF NOT FOUND + THEN +-- create row + RAISE DEBUG 'Will create spatial operation %', operation_id; +-- get operation infos + SELECT op.id, op.topiaid + INTO operationName, operationId + FROM operation op + WHERE op.topiaid = operation_id; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, operation op + WHERE op.topiaid = operation_id AND t.topiaid = op.transect; +-- get transit infos + SELECT + t.topiaid, + (t.starttime || ' - ' || t.endtime) + INTO transitId, transitName + FROM transit t, transect tt + WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, transit t + WHERE t.topiaid = transitId AND v.topiaid = t.voyage; + + INSERT INTO echobase_operation_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, operationid, operationname, coordinate) + VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, operationId, operationName, coordinateData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial operation % ', operation_id; + UPDATE echobase_operation_spatial eos + SET eos.coordinate = coordinateData + WHERE eos.operationid = operation_id; + END IF; +END +$$ LANGUAGE plpgsql; + +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- + +-- pour convertir des latitudes en dms (+ hemi) en dd +CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +BEGIN + SELECT + right(latitude, 1), + left(latitude, 2) :: INTEGER, + substring(latitude FROM 3 FOR 2) :: INTEGER, + substring(latitude FROM 6 FOR 3) :: INTEGER + INTO hemi_, degre_, minute_, second_; + + RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +-- pour convertir des longitudes en dms (+ hemi) en dd +CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +BEGIN + SELECT + right(longitude, 1), + left(longitude, 3) :: INTEGER, + substring(longitude FROM 4 FOR 2) :: INTEGER, + substring(longitude FROM 7 FOR 3) :: INTEGER + INTO hemi_, degre_, minute_, second_; + + RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_dms2dd( + D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1) +) + RETURNS DOUBLE PRECISION AS $$ +DECLARE + ret DOUBLE PRECISION; + dir INTEGER; +BEGIN + dir := 1; +--init to 1 for default positive return + ret := 0; +--init to zero. + --ONLY S or W will trip this. Any other letter or NULL will result in positive return value + IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W' + THEN + dir := -1; --then southern or western hemisphere + END IF; +--SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three. + ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) + + (ABS((CAST(S AS + DOUBLE PRECISION)) + / 60))) / 60))); + ret := ret * dir; + RETURN ret; + +END; +$$ LANGUAGE plpgsql; + +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- + +-- +-- Mettre a jour toutes les données spatiales +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() + RETURNS VOID AS $$ +DECLARE cell_id VARCHAR; +BEGIN + EXECUTE echobase_compute_all_cell_spatial_data(); + EXECUTE echobase_compute_all_operation_spatial_data(); +END +$$ +LANGUAGE plpgsql; + + +-- Compute all data +SELECT echobase_compute_all_cell_spatial_data(); +SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file Copied: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.5.1.sql (from rev 914, trunk/echobase-services/src/main/resources/postgis-view.sql) =================================================================== --- trunk/echobase-domain/src/main/resources/migration/postgis-view-2.5.1.sql (rev 0) +++ trunk/echobase-domain/src/main/resources/migration/postgis-view-2.5.1.sql 2013-12-17 02:03:00 UTC (rev 920) @@ -0,0 +1,291 @@ +--- +-- #%L +-- EchoBase :: Services +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2013 Ifremer, Codelutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero 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 Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- +-- ajout de vues liés aux tables spatiales + +DROP VIEW IF EXISTS echobase_cell_spatial_result; +CREATE OR REPLACE VIEW echobase_cell_spatial_result AS + SELECT + s.*, + m.topiaId AS metaDataId, + m.name AS metaDataName, + r.resultvalue AS resultValue, + r.topiaid AS resultId + FROM + echobase_cell_spatial s, + result r, + datametadata m + WHERE + s.cellid = r.cell AND + r.datametadata = m.topiaid; + +DROP VIEW IF EXISTS echobase_cell_spatial_data; +CREATE OR REPLACE VIEW echobase_cell_spatial_data AS + SELECT + s.*, + m.topiaId AS metaDataId, + m.name AS metaDataName, + d.datavalue AS dataValue, + d.topiaid AS dataId + FROM + echobase_cell_spatial s, + data d, + datametadata m + WHERE + s.cellid = d.cell AND + d.datametadata = m.topiaid; + +DROP VIEW IF EXISTS TotalSampleEngrEnc; +CREATE OR REPLACE VIEW TotalSampleEngrEnc AS + + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'ENGR-ENC'; + +DROP VIEW IF EXISTS TotalSampleSardPil; +CREATE OR REPLACE VIEW TotalSampleSardPil AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'SARD-PIL'; + +DROP VIEW IF EXISTS TotalSampleTracTru; +CREATE OR REPLACE VIEW TotalSampleTracTru AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'TRAC-TRU'; + +DROP VIEW IF EXISTS TotalSampleSpraSpr; +CREATE OR REPLACE VIEW TotalSampleSpraSpr AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'SPRA-SPR'; + +DROP VIEW IF EXISTS TotalSampleScomSco; +CREATE OR REPLACE VIEW TotalSampleScomSco AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'SCOM-SCO'; + +DROP VIEW IF EXISTS TotalSampleMicrPou; +CREATE OR REPLACE VIEW TotalSampleMicrPou AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'MICR-POU'; + +-- Create a view with species catches as columns + +DROP VIEW IF EXISTS TotalCatchSpOpWide; +CREATE OR REPLACE VIEW TotalCatchSpOpWide AS + SELECT + echobase_operation_spatial.voyagename, + echobase_operation_spatial.operationname, + echobase_operation_spatial.coordinate, + (SELECT + SUM(totalsampleengrenc.sampleweight) + FROM totalsampleengrenc + WHERE totalsampleengrenc.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchENGRENC, + (SELECT + SUM(totalsamplesardpil.sampleweight) + FROM totalsamplesardpil + WHERE totalsamplesardpil.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchSARDPIL, + (SELECT + SUM(totalsamplespraspr.sampleweight) + FROM totalsamplespraspr + WHERE totalsamplespraspr.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchSPRASPR, + (SELECT + SUM(totalsamplemicrpou.sampleweight) + FROM totalsamplemicrpou + WHERE totalsamplemicrpou.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchMICRPOU, + (SELECT + SUM(totalsamplescomsco.sampleweight) + FROM totalsamplescomsco + WHERE totalsamplescomsco.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchSCOMSCO, + (SELECT + SUM(totalsampletractru.sampleweight) + FROM totalsampletractru + WHERE totalsampletractru.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchTRACTRU, + echobase_operation_spatial.operationid + FROM echobase_operation_spatial; + +DROP VIEW IF EXISTS cellmapview; +CREATE OR REPLACE VIEW cellmapview AS + SELECT + echobase_cell_spatial.voyagename, + echobase_cell_spatial.coordinate, + echobase_cell_spatial.shape, + datametadata.name, + result.resultvalue, + species.baracoudacode, + echobase_cell_spatial.cellname, + result.topiaid AS resultid + FROM echobase_cell_spatial, result, datametadata, category, speciescategory, + species + WHERE echobase_cell_spatial.cellid = result.cell + AND result.datametadata = datametadata.topiaid + AND result.category = category.topiaid + AND category.speciescategory = speciescategory.topiaid + AND speciescategory.species = species.topiaid + AND echobase_cell_spatial.celltypename = 'Map cell'; \ No newline at end of file Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java 2013-12-17 01:53:04 UTC (rev 919) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java 2013-12-17 02:03:00 UTC (rev 920) @@ -34,7 +34,6 @@ import com.google.common.collect.Sets; import fr.ifremer.echobase.EchoBaseFunctions; import fr.ifremer.echobase.EchoBaseTechnicalException; -import fr.ifremer.echobase.entities.EchoBasePersistenceContext; import fr.ifremer.echobase.entities.data.Category; import fr.ifremer.echobase.entities.data.Echotype; import fr.ifremer.echobase.entities.data.Result; @@ -72,6 +71,10 @@ public static final TimeLog TILE_LOG = new TimeLog(SpatialService.class); + public static final String POSTGIS_STRUCTURE_SQL = "/postgis-structure.sql"; + + public static final String POSTGIS_VIEW_SQL = "/postgis-view.sql"; + private WorkingDbPersistenceService persistenceService; @Override @@ -83,10 +86,10 @@ public void addSpatialSupport() { // add spatial structure - executeSqlScript(EchoBasePersistenceContext.POSTGIS_STRUCTURE_SQL); + executeSqlScript(POSTGIS_STRUCTURE_SQL); // add spatial views - executeSqlScript(EchoBasePersistenceContext.POSTGIS_VIEW_SQL); + executeSqlScript(POSTGIS_VIEW_SQL); // do commit persistenceService.commit(); Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-17 01:53:04 UTC (rev 919) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-17 02:03:00 UTC (rev 920) @@ -1215,7 +1215,4 @@ -- Compute all data SELECT echobase_compute_all_cell_spatial_data(); -SELECT echobase_compute_all_operation_spatial_data(); - --- Add an update query as the migration service will execute this script and can't finish with a query instruction (see PreparedStatement#executeUpdate) --- UPDATE echobase_operation_spatial SET lastUpdateDate = now() WHERE operationid IS NULL; \ No newline at end of file +SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file

1 0

r919 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com Dec. 17, 2013

Dec. 17, 2013

Author: tchemit Date: 2013-12-17 02:53:04 +0100 (Tue, 17 Dec 2013) New Revision: 919 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/919 Log: change migration callback Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallback.java trunk/echobase-services/src/main/resources/postgis-structure.sql Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallback.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallback.java 2013-12-17 01:45:38 UTC (rev 918) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallback.java 2013-12-17 01:53:04 UTC (rev 919) @@ -25,10 +25,19 @@ import fr.ifremer.echobase.entities.EchoBaseDAOHelper; import fr.ifremer.echobase.persistence.migration.EchoBaseMigrationCallBackResolver; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.hibernate.jdbc.Work; +import org.nuiton.topia.TopiaContext; +import org.nuiton.topia.TopiaException; import org.nuiton.topia.migration.TopiaMigrationCallbackByClassNG; +import org.nuiton.util.StringUtil; import org.nuiton.util.Version; import org.nuiton.util.VersionUtil; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; import java.util.List; /** @@ -39,6 +48,9 @@ */ public class MigrationCallback extends TopiaMigrationCallbackByClassNG { + /** Logger. */ + private static final Log log = LogFactory.getLog(MigrationCallback.class); + public MigrationCallback() { super(EchoBaseMigrationCallBackResolver.newResolver( fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion.class)); @@ -53,4 +65,62 @@ public boolean askUser(Version version, List<Version> versions) { return true; } + + @Override + public void executeSQL(TopiaContext tx, final boolean showSql, final boolean showProgression, final String... sqls) throws TopiaException { + if (log.isInfoEnabled()) { + + log.info(String.format("Will execute %1$s requests...", sqls.length)); + } + if (showSql) { + StringBuilder buffer = new StringBuilder(); + for (String s : sqls) { + buffer.append(s).append("\n"); + } + log.info("SQL TO EXECUTE :\n" + + "--------------------------------------------------------------------------------\n" + + "--------------------------------------------------------------------------------\n" + + buffer.toString() + + "--------------------------------------------------------------------------------\n" + + "--------------------------------------------------------------------------------\n" + ); + } + tx.getHibernateSession().doWork(new Work() { + + @Override + public void execute(Connection connection) throws SQLException { + int index = 0; + int max = sqls.length; + for (String sql : sqls) { + index++; + long t0 = System.nanoTime(); + if (log.isInfoEnabled()) { + String message = ""; + + if (showProgression) { + message = String.format("Executing request [%1$-4s/%2$-4s]", index, max); + } + if (showSql) { + message += "\n" + sql; + } + if (showProgression || showSql) { + + log.info(message); + } + } + PreparedStatement sta = connection.prepareStatement(sql); + try { + sta.execute(); + } finally { + sta.close(); + } + if (log.isDebugEnabled()) { + String message; + message = String.format("Request [%1$-4s/%2$-4s] executed in %3$s.", index, max, StringUtil.convertTime(System.nanoTime() - t0)); + log.debug(message); + } + } + } + }); + } } Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-17 01:45:38 UTC (rev 918) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-17 01:53:04 UTC (rev 919) @@ -1214,8 +1214,8 @@ -- Compute all data --- SELECT echobase_compute_all_cell_spatial_data(); --- SELECT echobase_compute_all_operation_spatial_data(); +SELECT echobase_compute_all_cell_spatial_data(); +SELECT echobase_compute_all_operation_spatial_data(); -- Add an update query as the migration service will execute this script and can't finish with a query instruction (see PreparedStatement#executeUpdate) -- UPDATE echobase_operation_spatial SET lastUpdateDate = now() WHERE operationid IS NULL; \ No newline at end of file

1 0

r918 - trunk/echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com Dec. 17, 2013

Dec. 17, 2013

Author: tchemit Date: 2013-12-17 02:45:38 +0100 (Tue, 17 Dec 2013) New Revision: 918 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/918 Log: fix on sql migration Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 22:34:04 UTC (rev 917) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-17 01:45:38 UTC (rev 918) @@ -1214,8 +1214,8 @@ -- Compute all data -SELECT echobase_compute_all_cell_spatial_data(); -SELECT echobase_compute_all_operation_spatial_data(); +-- SELECT echobase_compute_all_cell_spatial_data(); +-- SELECT echobase_compute_all_operation_spatial_data(); -- Add an update query as the migration service will execute this script and can't finish with a query instruction (see PreparedStatement#executeUpdate) -UPDATE echobase_operation_spatial SET lastUpdateDate = now() WHERE operationid IS NULL; \ No newline at end of file +-- UPDATE echobase_operation_spatial SET lastUpdateDate = now() WHERE operationid IS NULL; \ No newline at end of file

1 0

r917 - trunk/echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com Dec. 16, 2013

Dec. 16, 2013

Author: tchemit Date: 2013-12-16 23:34:04 +0100 (Mon, 16 Dec 2013) New Revision: 917 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/917 Log: fix on sql migration Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 22:25:41 UTC (rev 916) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 22:34:04 UTC (rev 917) @@ -1216,5 +1216,6 @@ -- Compute all data SELECT echobase_compute_all_cell_spatial_data(); SELECT echobase_compute_all_operation_spatial_data(); -SELECT count(*) from echobase_cell_spatial; -SELECT count(*) from echobase_operation_spatial; + +-- Add an update query as the migration service will execute this script and can't finish with a query instruction (see PreparedStatement#executeUpdate) +UPDATE echobase_operation_spatial SET lastUpdateDate = now() WHERE operationid IS NULL; \ No newline at end of file

1 0

r916 - trunk/echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com Dec. 16, 2013

Dec. 16, 2013

Author: tchemit Date: 2013-12-16 23:25:41 +0100 (Mon, 16 Dec 2013) New Revision: 916 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/916 Log: remove migration 2.5 + try some fix on sql migration Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 22:20:26 UTC (rev 915) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 22:25:41 UTC (rev 916) @@ -1206,14 +1206,15 @@ RETURNS VOID AS $$ DECLARE cell_id VARCHAR; BEGIN + EXECUTE echobase_compute_all_cell_spatial_data(); EXECUTE echobase_compute_all_operation_spatial_data(); - EXECUTE echobase_compute_all_cell_spatial_data(); END $$ LANGUAGE plpgsql; -- Compute all data --- SELECT echobase_compute_all_cell_spatial_data(); --- SELECT echobase_compute_all_operation_spatial_data(); -SELECT echobase_compute_all_spatial_data(); +SELECT echobase_compute_all_cell_spatial_data(); +SELECT echobase_compute_all_operation_spatial_data(); +SELECT count(*) from echobase_cell_spatial; +SELECT count(*) from echobase_operation_spatial;

1 0

r915 - in trunk/echobase-domain/src/main: java/fr/ifremer/echobase/persistence/migration/workingDb resources/META-INF/services
by tchemit＠users.forge.codelutin.com Dec. 16, 2013

Dec. 16, 2013

Author: tchemit Date: 2013-12-16 23:20:26 +0100 (Mon, 16 Dec 2013) New Revision: 915 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/915 Log: remove migration 2.5 + try some fix on sql migration Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java trunk/echobase-domain/src/main/resources/META-INF/services/fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java 2013-12-16 22:12:34 UTC (rev 914) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java 2013-12-16 22:20:26 UTC (rev 915) @@ -64,7 +64,7 @@ if (spatialAware) { - // repass postgis scripts (http://forge.codelutin.com/issues/3672) + // repass postgis scripts (http://forge.codelutin.com/issues/4018) updatePostgis(queries); } } @@ -72,9 +72,5 @@ protected void updatePostgis(List<String> queries) { queries.add(EchoBaseIOUtil.loadScript(EchoBasePersistenceContext.POSTGIS_STRUCTURE_SQL)); queries.add(EchoBaseIOUtil.loadScript(EchoBasePersistenceContext.POSTGIS_VIEW_SQL)); - - // fix http://forge.codelutin.com/issues/3973 - queries.add("update echobase_operation_spatial set operationname = (select op.id from operation op where op.topiaid = operationid);"); - } } Modified: trunk/echobase-domain/src/main/resources/META-INF/services/fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion =================================================================== --- trunk/echobase-domain/src/main/resources/META-INF/services/fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion 2013-12-16 22:12:34 UTC (rev 914) +++ trunk/echobase-domain/src/main/resources/META-INF/services/fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion 2013-12-16 22:20:26 UTC (rev 915) @@ -1,3 +1,2 @@ fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion2_2 -fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion2_5 fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion2_5_1 \ No newline at end of file

1 0

r914 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/entities/data echobase-domain/src/main/java/fr/ifremer/echobase/entities/references echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-services/src/main/resources echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery echobase-ui/src/main/webapp/images
by tchemit＠users.forge.codelutin.com Dec. 16, 2013

Dec. 16, 2013

Author: tchemit Date: 2013-12-16 23:12:34 +0100 (Mon, 16 Dec 2013) New Revision: 914 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/914 Log: prepare release + try some fix on sql migration Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CategoryDAOImpl.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/ResultDAOImpl.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/CellTypeImpl.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/DataMetadataImpl.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/SampleDataTypeImpl.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java trunk/echobase-services/src/main/resources/postgis-structure.sql trunk/echobase-services/src/main/resources/postgis-view.sql trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ConfirmDeleteExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java trunk/echobase-ui/src/main/webapp/images/menu3.jpg Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CategoryDAOImpl.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CategoryDAOImpl.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CategoryDAOImpl.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: Domain * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CategoryDAOImpl.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/ResultDAOImpl.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/ResultDAOImpl.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/ResultDAOImpl.java 2013-12-16 22:12:34 UTC (rev 914) @@ -1,5 +1,28 @@ package fr.ifremer.echobase.entities.data; +/* + * #%L + * EchoBase :: Domain + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 - 2013 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero 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 Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + import fr.ifremer.echobase.entities.references.DataMetadata; import java.util.List; Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/ResultDAOImpl.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/CellTypeImpl.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/CellTypeImpl.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/CellTypeImpl.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: Domain * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/CellTypeImpl.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/DataMetadataImpl.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/DataMetadataImpl.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/DataMetadataImpl.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: Domain * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/DataMetadataImpl.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/SampleDataTypeImpl.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/SampleDataTypeImpl.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/SampleDataTypeImpl.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: Domain * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/references/SampleDataTypeImpl.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: Domain * $Id$ - * $HeadURL: http://svn.forge.codelutin.com/svn/echobase/trunk/echobase-domain/src/main/… $ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% @@ -132,7 +132,7 @@ private void addPostgisView(List<String> queries) { // add cell data view - queries.add("DROP VIEW IF EXISTS echobase_cell_spatial_data;"); + queries.add("DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE;"); queries.add("CREATE OR REPLACE VIEW echobase_cell_spatial_data AS\n" + "SELECT\n" + " s.*,\n" + @@ -149,7 +149,7 @@ " d.datametadata = m.topiaid;"); // add cell result view - queries.add("DROP VIEW IF EXISTS echobase_cell_spatial_result;"); + queries.add("DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE;"); queries.add("CREATE OR REPLACE VIEW echobase_cell_spatial_result AS\n" + "SELECT\n" + " s.*,\n" + @@ -166,7 +166,7 @@ " r.datametadata = m.topiaid;"); // add operation data view - queries.add("DROP VIEW IF EXISTS TotalSampleEngrEnc;"); + queries.add("DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE;"); queries.add("CREATE OR REPLACE VIEW TotalSampleEngrEnc AS\n" + "\n" + "SELECT \n" + @@ -196,7 +196,7 @@ " sampletype.name = 'Total' AND\n" + " species.baracoudacode='ENGR-ENC';\n"); - queries.add("DROP VIEW IF EXISTS TotalSampleSardPil;"); + queries.add("DROP VIEW IF EXISTS TotalSampleSardPil CASCADE;"); queries.add("CREATE OR REPLACE VIEW TotalSampleSardPil AS\n" + "\n" + "SELECT \n" + @@ -226,7 +226,7 @@ " sampletype.name = 'Total' AND\n" + " species.baracoudacode='SARD-PIL';"); - queries.add("DROP VIEW IF EXISTS TotalSampleTracTru;"); + queries.add("DROP VIEW IF EXISTS TotalSampleTracTru CASCADE;"); queries.add("CREATE OR REPLACE VIEW TotalSampleTracTru AS\n" + "\n" + "SELECT \n" + @@ -256,7 +256,7 @@ " sampletype.name = 'Total' AND\n" + " species.baracoudacode='TRAC-TRU';"); - queries.add("DROP VIEW IF EXISTS TotalSampleSpraSpr;"); + queries.add("DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE;"); queries.add("CREATE OR REPLACE VIEW TotalSampleSpraSpr AS\n" + "\n" + "SELECT \n" + @@ -286,7 +286,7 @@ " sampletype.name = 'Total' AND\n" + " species.baracoudacode='SPRA-SPR';"); - queries.add("DROP VIEW IF EXISTS TotalSampleScomSco;"); + queries.add("DROP VIEW IF EXISTS TotalSampleScomSco CASCADE;"); queries.add("CREATE OR REPLACE VIEW TotalSampleScomSco AS\n" + "\n" + "SELECT \n" + @@ -316,7 +316,7 @@ " sampletype.name = 'Total' AND\n" + " species.baracoudacode='SCOM-SCO';"); - queries.add("DROP VIEW IF EXISTS TotalSampleMicrPou;"); + queries.add("DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE;"); queries.add("CREATE OR REPLACE VIEW TotalSampleMicrPou AS\n" + "\n" + "SELECT \n" + @@ -346,7 +346,7 @@ " sampletype.name = 'Total' AND\n" + " species.baracoudacode='MICR-POU';"); - queries.add("DROP VIEW IF EXISTS TotalCatchSpOpWide;"); + queries.add("DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE;"); queries.add("CREATE OR REPLACE VIEW TotalCatchSpOpWide AS\n" + "\n" + "SELECT\n" + @@ -362,7 +362,7 @@ " echobase_operation_spatial.operationid\n" + "From echobase_operation_spatial;"); - queries.add("DROP VIEW IF EXISTS cellmapview;"); + queries.add("DROP VIEW IF EXISTS cellmapview CASCADE;"); queries.add("CREATE OR REPLACE VIEW cellmapview AS \n" + " SELECT echobase_cell_spatial.voyagename, echobase_cell_spatial.coordinate, echobase_cell_spatial.shape, datametadata.name, result.resultvalue, species.baracoudacode, echobase_cell_spatial.cellname, result.topiaid AS resultid\n" + " FROM echobase_cell_spatial, result, datametadata, category, speciescategory, species\n" + Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java 2013-12-16 22:12:34 UTC (rev 914) @@ -1,5 +1,28 @@ package fr.ifremer.echobase.persistence.migration.workingDb; +/* + * #%L + * EchoBase :: Domain + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 - 2013 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero 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 Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + import fr.ifremer.echobase.entities.EchoBasePersistenceContext; import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; import fr.ifremer.echobase.io.EchoBaseIOUtil; Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 22:12:34 UTC (rev 914) @@ -1214,5 +1214,6 @@ -- Compute all data -SELECT echobase_compute_all_cell_spatial_data(); -SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file +-- SELECT echobase_compute_all_cell_spatial_data(); +-- SELECT echobase_compute_all_operation_spatial_data(); +SELECT echobase_compute_all_spatial_data(); Modified: trunk/echobase-services/src/main/resources/postgis-view.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-view.sql 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-services/src/main/resources/postgis-view.sql 2013-12-16 22:12:34 UTC (rev 914) @@ -1,3 +1,25 @@ +--- +-- #%L +-- EchoBase :: Services +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2013 Ifremer, Codelutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero 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 Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- -- ajout de vues liés aux tables spatiales DROP VIEW IF EXISTS echobase_cell_spatial_result; Property changes on: trunk/echobase-services/src/main/resources/postgis-view.sql ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: UI * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: UI * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ConfirmDeleteExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ConfirmDeleteExportQuery.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ConfirmDeleteExportQuery.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: UI * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ConfirmDeleteExportQuery.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: UI * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java 2013-12-16 22:12:34 UTC (rev 914) @@ -4,7 +4,7 @@ * #%L * EchoBase :: UI * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java 2013-12-16 21:43:46 UTC (rev 913) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java 2013-12-16 22:12:34 UTC (rev 914) @@ -3,7 +3,7 @@ * EchoBase :: UI * * $Id$ - * $HeadURL: http://svn.forge.codelutin.com/svn/echobase/trunk/echobase-ui/src/main/java… $ + * $HeadURL$ * %% * Copyright (C) 2011 Ifremer, Codelutin * %% Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Property changes on: trunk/echobase-ui/src/main/webapp/images/menu3.jpg ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL

1 0