[Echobase-commits] r898 - trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb

12 Dec 2013

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;");
+    }
 }

    

[Echobase-commits] r898 - trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb

tchemit＠users.forge.codelutin.com