Author: tchemit Date: 2013-12-12 11:28:48 +0100 (Thu, 12 Dec 2013) New Revision: 898 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/898 Log: fixes #3856: Probl?\195?\168me de g?\195?\169n?\195?\169ration des donn?\195?\169es spatiales suite ?\195?\160 un import de base. fixes #3973: La colonne operationname n'est pas renseign?\195?\169e dans la table echobase_operation_spatial refs #3852: Ajout de vues spatiales (view sur les operations) Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java 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-12 09:27:01 UTC (rev 897) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5.java 2013-12-12 10:28:48 UTC (rev 898) @@ -49,59 +49,309 @@ boolean showSql, boolean showProgression) throws TopiaException { - // add spatial view (http://forge.codelutin.com/issues/3672) - updatePostgis(tx, queries); - } + boolean spatialAware; - protected void updatePostgis(TopiaContext tx, List<String> queries) { TopiaEchoBasePersistenceContext persistenceContext = new TopiaEchoBasePersistenceContext(tx); - boolean spatialAware = - persistenceContext.isSpatialAware(); - try { - if (spatialAware) { - // add cell data view - queries.add("DROP VIEW IF EXISTS echobase_cell_spatial_data;"); - 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;"); - 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 echobase_operation_spatial_data;"); - //TODO Add spatial table - } + 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;"); + 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;"); + 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;"); + queries.add("CREATE OR REPLACE VIEW TotalSampleEngrEnc AS\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" + + " echobase_operation_spatial, \n" + + " operation, \n" + + " sample, \n" + + " speciescategory, \n" + + " sizecategory, \n" + + " species, \n" + + " 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';"); + + queries.add("DROP VIEW IF EXISTS TotalSampleSardPil;"); + queries.add("CREATE OR REPLACE VIEW TotalSampleSardPil AS\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" + + " echobase_operation_spatial, \n" + + " operation, \n" + + " sample, \n" + + " speciescategory, \n" + + " sizecategory, \n" + + " species, \n" + + " 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;"); + queries.add("CREATE OR REPLACE VIEW TotalSampleTracTru AS\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" + + " echobase_operation_spatial, \n" + + " operation, \n" + + " sample, \n" + + " speciescategory, \n" + + " sizecategory, \n" + + " species, \n" + + " 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;"); + queries.add("CREATE OR REPLACE VIEW TotalSampleSpraSpr AS\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" + + " echobase_operation_spatial, \n" + + " operation, \n" + + " sample, \n" + + " speciescategory, \n" + + " sizecategory, \n" + + " species, \n" + + " 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;"); + queries.add("CREATE OR REPLACE VIEW TotalSampleScomSco AS\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" + + " echobase_operation_spatial, \n" + + " operation, \n" + + " sample, \n" + + " speciescategory, \n" + + " sizecategory, \n" + + " species, \n" + + " 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;"); + queries.add("CREATE OR REPLACE VIEW TotalSampleMicrPou AS\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" + + " echobase_operation_spatial, \n" + + " operation, \n" + + " sample, \n" + + " speciescategory, \n" + + " sizecategory, \n" + + " species, \n" + + " 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;"); + queries.add("CREATE OR REPLACE VIEW TotalCatchSpOpWide AS\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" + + "From echobase_operation_spatial;"); + } }