r954 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-domain/src/main/resources/META-INF/services echobase-domain/src/main/resources/migration echobase-domain/src/main/xmi echobase-services/src/main/resources - Echobase-commits

27 Feb 2014

Author: tchemit
Date: 2014-02-27 16:03:00 +0100 (Thu, 27 Feb 2014)
New Revision: 954

Url: http://codelutin.com/projects/echobase/repository/revisions/954

Log:
refs #4194: Visualisation des donn?\195?\169es spatiales

Added:
   trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java
   trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql
Modified:
   trunk/echobase-domain/src/main/resources/META-INF/services/fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion
   trunk/echobase-domain/src/main/xmi/echobase.properties
   trunk/echobase-services/src/main/resources/postgis-view.sql

Added: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java
===================================================================

--- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java	                        (rev 0)
+++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java	2014-02-27 15:03:00 UTC (rev 954)
@@ -0,0 +1,43 @@
+package fr.ifremer.echobase.persistence.migration.workingDb;
+
+import fr.ifremer.echobase.entities.EchoBaseUserTopiaPersistenceContext;
+import fr.ifremer.echobase.io.EchoBaseIOUtil;
+import org.nuiton.topia.persistence.TopiaException;
+import org.nuiton.util.Version;
+
+import java.util.List;
+
+/**
+ * Created on 2/26/14.
+ *
+ * @author Tony Chemit <chemit@codelutin.com>
+ * @since 2.6
+ */
+public class MigrationCallBackForVersion2_6 extends MigrationCallBackForVersion {
+
+    public static final String POSTGIS_VIEW_SQL = "/migration/postgis-view-2.6.sql";
+
+    @Override
+    public Version getVersion() {
+        return new Version("2.6");
+    }
+
+    @Override
+    protected void prepareMigrationScript(EchoBaseUserTopiaPersistenceContext tx,
+                                          List<String> queries,
+                                          boolean showSql,
+                                          boolean showProgression) throws TopiaException {
+
+        boolean spatialAware = tx.isSpatialAware();
+
+        if (spatialAware) {
+
+            // repass postgis scripts (http://forge.codelutin.com/issues/4018)
+            updatePostgis(queries);
+        }
+    }
+
+    protected void updatePostgis(List<String> queries) {
+        queries.add(EchoBaseIOUtil.loadScript(POSTGIS_VIEW_SQL));
+    }
+}
\ No newline at end of file


Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java
___________________________________________________________________
Added: svn:keywords
   + Author Date Id Revision
Added: svn:eol-style
   + native

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	2014-02-25 17:06:08 UTC (rev 953)
+++ trunk/echobase-domain/src/main/resources/META-INF/services/fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion	2014-02-27 15:03:00 UTC (rev 954)
@@ -1,2 +1,3 @@
 fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion2_2
-fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion2_5_1
\ No newline at end of file
+fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion2_5_1
+fr.ifremer.echobase.persistence.migration.workingDb.MigrationCallBackForVersion2_6
\ No newline at end of file

Added: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql
===================================================================
--- trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql	                        (rev 0)
+++ trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql	2014-02-27 15:03:00 UTC (rev 954)
@@ -0,0 +1,452 @@
+-----------------------------------------------------------------------------------------------------------------------
+---- DROP EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE;
+DROP VIEW IF EXISTS cellmapview CASCADE;
+
+DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE ;
+DROP VIEW IF EXISTS meanMapcellBiomassEngrEnc CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassSardPil CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassTracTru CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassSpraSpr CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassScomSco CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassMicrPou CASCADE;
+DROP VIEW IF EXISTS TotalCatchSpOpWide 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 INDEX IF EXISTS cellEsduViewSpeciesBiomass_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpeciesAbundance_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpeciesMeanLength_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewEchotypeNasc_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewEchotype_idx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpecies_idx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpeciesResultGrouped_idx CASCADE;
+DROP INDEX IF EXISTS cellmapview_idx CASCADE;
+DROP INDEX IF EXISTS cellmapview_uidx CASCADE;
+DROP INDEX IF EXISTS TotalSampleView_idx CASCADE;
+
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotypeNasc CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesBiomass CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesAbundance CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesMeanLength CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotype CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesResultGrouped CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpecies CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellmapview CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS TotalSampleView CASCADE;
+
+DROP TRIGGER IF EXISTS echobase_refresh_views_trigger ON entitymodificationlog CASCADE;
+DROP FUNCTION IF EXISTS echobase_to_numeric(string VARCHAR) CASCADE;
+DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE;
+
+-----------------------------------------------------------------------------------------------------------------------
+---- CREATE FUNCTION - TRIGGER ----------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR)
+  RETURNS REAL AS $$
+BEGIN
+  RETURN string::real;
+  EXCEPTION WHEN invalid_text_representation
+  THEN
+    RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION echobase_refresh_views()
+  RETURNS TRIGGER AS $$
+DECLARE
+  operationtype VARCHAR;
+  result       RECORD;
+BEGIN
+
+  IF (TG_OP = 'DELETE')
+  THEN
+    result = OLD;
+  ELSE
+    result = NEW;
+  END IF;
+
+  operationtype = result.entitytype;
+
+  IF (operationtype ILIKE '%Import%' OR operationtype ILIKE '%Removed%')
+  THEN
+    -- Can update views
+    RAISE NOTICE 'reload cellEsduViewEchotype';
+    REFRESH MATERIALIZED VIEW cellEsduViewEchotype;
+    REINDEX INDEX cellEsduViewEchotype_idx;
+
+    RAISE NOTICE 'reload cellEsduViewSpecies';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpecies;
+    REINDEX INDEX cellEsduViewSpecies_idx;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesResultGrouped';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped;
+    REINDEX INDEX cellEsduViewSpeciesResultGrouped_idx;
+
+    RAISE NOTICE 'reload cellEsduViewEchotypeNasc';
+    REFRESH MATERIALIZED VIEW cellEsduViewEchotypeNasc;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesBiomass';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesBiomass;
+    REINDEX INDEX cellEsduViewSpeciesBiomass_uidx;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesAbundance';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesAbundance;
+    REINDEX INDEX cellEsduViewSpeciesAbundance_uidx;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesMeanLength';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesMeanLength;
+    REINDEX INDEX cellEsduViewSpeciesMeanLength_uidx;
+
+    RAISE NOTICE 'reload cellmapview';
+    REFRESH MATERIALIZED VIEW cellmapview;
+    REINDEX INDEX cellmapview_idx;
+    REINDEX INDEX cellmapview_uidx;
+  END IF;
+
+  RETURN result;
+END
+$$
+LANGUAGE 'plpgsql';
+
+CREATE TRIGGER echobase_refresh_views_trigger
+AFTER INSERT OR UPDATE ON entitymodificationlog
+EXECUTE PROCEDURE echobase_refresh_views();
+
+-----------------------------------------------------------------------------------------------------------------------
+---- CELL RESULT VIEW -------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+CREATE MATERIALIZED VIEW cellEsduViewEchotype AS
+  SELECT
+    c.voyagename,
+    c.coordinate,
+    c.shape,
+    c.cellname,
+    d.name,
+    echobase_to_numeric(r.resultvalue) as resultvalue,
+    e.name as echotypeName,
+    e.meaning as echotypeMeaning,
+    r.topiaid AS resultid
+  FROM
+    echobase_cell_spatial c,
+    result r,
+    datametadata d,
+    category cat,
+    echotype e
+  WHERE
+    c.cellid = r.cell
+    AND r.datametadata  = d.topiaid
+    AND r.category = cat.topiaid
+    AND cat.echotype IS NOT NULL
+    AND cat.echotype = e.topiaid
+    AND c.celltypename = 'Elementary Distance Sampling Unit';
+
+CREATE INDEX cellEsduViewEchotype_idx ON cellEsduViewEchotype(name);
+
+CREATE MATERIALIZED VIEW cellEsduViewEchotypeNasc AS
+  SELECT
+    *
+  FROM
+    cellEsduViewEchotype v
+  WHERE
+    v.name = 'NASC';
+
+CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(resultid);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpecies AS
+  SELECT
+    c.voyagename,
+    c.coordinate,
+    c.shape,
+    c.cellname,
+    d.name,
+    echobase_to_numeric(r.resultvalue) as resultvalue,
+    s.baracoudacode,
+    r.topiaid AS resultid
+  FROM
+    echobase_cell_spatial c,
+    result r,
+    datametadata d,
+    category cat,
+    speciescategory scat,
+    species s
+  WHERE
+    c.cellid = r.cell
+    AND r.datametadata  = d.topiaid
+    AND r.category = cat.topiaid
+    AND cat.speciescategory IS NOT NULL
+    AND cat.speciescategory = scat.topiaid
+    AND scat.species = s.topiaid
+    AND c.celltypename = 'Elementary Distance Sampling Unit';
+
+CREATE INDEX cellEsduViewSpecies_idx ON cellEsduViewSpecies(name);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped AS
+  SELECT
+    count(*) AS nbResults,
+    name,
+    cellname,
+    baracoudacode,
+    sum(resultvalue) as resultvalue,
+    cellname || '-' || baracoudacode || '-' || name as id
+  FROM cellEsduViewSpecies
+  WHERE
+    baracoudacode in ('ENGR-ENC', 'SARD-PIL', 'TRAC-TRU', 'SPRA-SPR', 'SCOM-SCO', 'MICR-POU')
+    AND name in ('Biomass','Abundance','MeanLength')
+  GROUP BY baracoudacode, cellname, name;
+
+CREATE INDEX cellEsduViewSpeciesResultGrouped_idx ON cellEsduViewSpecies(baracoudacode, cellname, name);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesBiomass AS
+  SELECT
+    distinct v2.coordinate,
+    v2.voyagename,
+    v.*
+  FROM
+    cellEsduViewSpeciesResultGrouped v
+    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
+  WHERE
+    v.name = 'Biomass';
+
+CREATE UNIQUE INDEX cellEsduViewSpeciesBiomass_uidx ON cellEsduViewSpeciesBiomass(id);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesAbundance AS
+  SELECT
+    distinct v2.coordinate,
+    v2.voyagename,
+    v.*
+  FROM
+    cellEsduViewSpeciesResultGrouped v
+    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
+  WHERE
+    v.name = 'Abundance';
+
+CREATE UNIQUE INDEX cellEsduViewSpeciesAbundance_uidx ON cellEsduViewSpeciesAbundance(id);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesMeanLength AS
+  SELECT
+    distinct v2.coordinate,
+    v2.voyagename,
+    v.*
+  FROM
+    cellEsduViewSpeciesResultGrouped v
+    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
+  WHERE
+    v.name = 'MeanLength';
+
+CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id);
+
+-----------------------------------------------------------------------------------------------------------------------
+---- CELL DATA VIEW ---------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+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;
+
+-----------------------------------------------------------------------------------------------------------------------
+---- MAP CELL VIEW ----------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+CREATE MATERIALIZED VIEW cellmapview AS
+  SELECT
+    c.voyagename,
+    c.coordinate,
+    c.shape,
+    c.cellname,
+    d.name,
+    s.baracoudacode,
+    echobase_to_numeric(r.resultvalue) as resultvalue,
+    r.topiaid AS resultid
+  FROM
+    echobase_cell_spatial c,
+    result r,
+    datametadata d,
+    category cat,
+    speciescategory scat,
+    species s
+  WHERE
+    c.cellid = r.cell
+    AND r.datametadata  = d.topiaid
+    AND r.category = cat.topiaid
+    AND cat.speciescategory = scat.topiaid
+    AND scat.species = s.topiaid
+    AND c.celltypename = 'Map cell';
+
+CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name);
+CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(resultid);
+
+CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'ENGR-ENC'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassSardPil AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'SARD-PIL'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassTracTru AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'TRAC-TRU'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassSpraSpr AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'SPRA-SPR'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassScomSco AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'SCOM-SCO'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassMicrPou AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'MICR-POU'
+    AND v.name = 'meanMapcellBiomass';
+
+-----------------------------------------------------------------------------------------------------------------------
+---- OPERATION VIEW ---------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+CREATE MATERIALIZED VIEW TotalSampleView 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;
+
+CREATE INDEX  TotalSampleView_idx ON TotalSampleView(baracoudacode, sampletype_name);
+
+CREATE OR REPLACE VIEW TotalSampleEngrEnc AS
+  SELECT
+    *
+  FROM
+    TotalSampleView v
+  WHERE
+    v.sampletype_name = 'Total' AND
+    v.baracoudacode = 'ENGR-ENC';
+
+CREATE OR REPLACE VIEW TotalSampleSardPil AS
+  SELECT
+    *
+  FROM
+    TotalSampleView v
+  WHERE
+    v.sampletype_name = 'Total' AND
+    v.baracoudacode = 'SARD-PIL';
+
+CREATE OR REPLACE VIEW TotalSampleTracTru AS
+  SELECT
+    *
+  FROM
+    TotalSampleView v
+  WHERE
+    v.sampletype_name = 'Total' AND
+    v.baracoudacode = 'TRAC-TRU';
+
+CREATE OR REPLACE VIEW TotalSampleSpraSpr AS
+  SELECT
+    *
+  FROM
+    TotalSampleView v
+  WHERE
+    v.sampletype_name = 'Total' AND
+    v.baracoudacode = 'SPRA-SPR';
+
+CREATE OR REPLACE VIEW TotalSampleScomSco AS
+  SELECT
+    *
+  FROM
+    TotalSampleView v
+  WHERE
+    v.sampletype_name = 'Total' AND
+    v.baracoudacode = 'SCOM-SCO';
+
+CREATE OR REPLACE VIEW TotalSampleMicrPou AS
+  SELECT
+    *
+  FROM
+    TotalSampleView v
+  WHERE
+    v.sampletype_name = 'Total' AND
+    v.baracoudacode = 'MICR-POU';
+
+-- Create a view with species catches as columns
+
+CREATE OR REPLACE VIEW TotalCatchSpOpWide AS
+  SELECT
+    e.voyagename,
+    e.operationname,
+    e.coordinate,
+    (SELECT SUM(t.sampleweight) FROM totalsampleengrenc t WHERE t.operation_id = e.operationname) AS TotalCatchENGRENC,
+    (SELECT SUM(t.sampleweight) FROM totalsamplesardpil t WHERE t.operation_id = e.operationname) AS TotalCatchSARDPIL,
+    (SELECT SUM(t.sampleweight) FROM totalsamplespraspr t WHERE t.operation_id = e.operationname) AS TotalCatchSPRASPR,
+    (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU,
+    (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO,
+    (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU,
+    e.operationid
+  FROM echobase_operation_spatial e;
\ No newline at end of file

Modified: trunk/echobase-domain/src/main/xmi/echobase.properties
===================================================================
--- trunk/echobase-domain/src/main/xmi/echobase.properties	2014-02-25 17:06:08 UTC (rev 953)
+++ trunk/echobase-domain/src/main/xmi/echobase.properties	2014-02-27 15:03:00 UTC (rev 954)
@@ -24,7 +24,7 @@
 model.tagValue.notGenerateToString=true
 model.tagValue.generateOperatorForDAOHelper=true
 model.tagValue.constantPrefix=PROPERTY_
-model.tagValue.version=2.5.1
+model.tagValue.version=2.6
 model.tagValue.noPCS=true
 model.tagValue.doNotGenerateBooleanGetMethods=true
 model.tagValue.indexForeignKeys=true

Modified: trunk/echobase-services/src/main/resources/postgis-view.sql
===================================================================
--- trunk/echobase-services/src/main/resources/postgis-view.sql	2014-02-25 17:06:08 UTC (rev 953)
+++ trunk/echobase-services/src/main/resources/postgis-view.sql	2014-02-27 15:03:00 UTC (rev 954)
@@ -20,25 +20,258 @@
 -- 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
+-----------------------------------------------------------------------------------------------------------------------
+---- DROP EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE;
+DROP VIEW IF EXISTS cellmapview CASCADE;
+
+DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE ;
+DROP VIEW IF EXISTS meanMapcellBiomassEngrEnc CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassSardPil CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassTracTru CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassSpraSpr CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassScomSco CASCADE;
+DROP VIEW IF EXISTS meanMapcellBiomassMicrPou CASCADE;
+DROP VIEW IF EXISTS TotalCatchSpOpWide 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 INDEX IF EXISTS cellEsduViewSpeciesBiomass_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpeciesAbundance_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpeciesMeanLength_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewEchotypeNasc_uidx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewEchotype_idx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpecies_idx CASCADE;
+DROP INDEX IF EXISTS cellEsduViewSpeciesResultGrouped_idx CASCADE;
+DROP INDEX IF EXISTS cellmapview_idx CASCADE;
+DROP INDEX IF EXISTS cellmapview_uidx CASCADE;
+DROP INDEX IF EXISTS TotalSampleView_idx CASCADE;
+
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotypeNasc CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesBiomass CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesAbundance CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesMeanLength CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotype CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesResultGrouped CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpecies CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS cellmapview CASCADE;
+DROP MATERIALIZED VIEW IF EXISTS TotalSampleView CASCADE;
+
+DROP TRIGGER IF EXISTS echobase_refresh_views_trigger ON entitymodificationlog CASCADE;
+DROP FUNCTION IF EXISTS echobase_to_numeric(string VARCHAR) CASCADE;
+DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE;
+
+-----------------------------------------------------------------------------------------------------------------------
+---- CREATE FUNCTION - TRIGGER ----------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR)
+  RETURNS REAL AS $$
+BEGIN
+  RETURN string::real;
+  EXCEPTION WHEN invalid_text_representation
+  THEN
+    RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION echobase_refresh_views()
+  RETURNS TRIGGER AS $$
+DECLARE
+  operationtype VARCHAR;
+  result       RECORD;
+BEGIN
+
+  IF (TG_OP = 'DELETE')
+  THEN
+    result = OLD;
+  ELSE
+    result = NEW;
+  END IF;
+
+  operationtype = result.entitytype;
+
+  IF (operationtype ILIKE '%Import%' OR operationtype ILIKE '%Removed%')
+  THEN
+    -- Can update views
+    RAISE NOTICE 'reload cellEsduViewEchotype';
+    REFRESH MATERIALIZED VIEW cellEsduViewEchotype;
+    REINDEX INDEX cellEsduViewEchotype_idx;
+
+    RAISE NOTICE 'reload cellEsduViewSpecies';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpecies;
+    REINDEX INDEX cellEsduViewSpecies_idx;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesResultGrouped';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped;
+    REINDEX INDEX cellEsduViewSpeciesResultGrouped_idx;
+
+    RAISE NOTICE 'reload cellEsduViewEchotypeNasc';
+    REFRESH MATERIALIZED VIEW cellEsduViewEchotypeNasc;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesBiomass';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesBiomass;
+    REINDEX INDEX cellEsduViewSpeciesBiomass_uidx;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesAbundance';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesAbundance;
+    REINDEX INDEX cellEsduViewSpeciesAbundance_uidx;
+
+    RAISE NOTICE 'reload cellEsduViewSpeciesMeanLength';
+    REFRESH MATERIALIZED VIEW cellEsduViewSpeciesMeanLength;
+    REINDEX INDEX cellEsduViewSpeciesMeanLength_uidx;
+
+    RAISE NOTICE 'reload cellmapview';
+    REFRESH MATERIALIZED VIEW cellmapview;
+    REINDEX INDEX cellmapview_idx;
+    REINDEX INDEX cellmapview_uidx;
+  END IF;
+
+  RETURN result;
+END
+$$
+LANGUAGE 'plpgsql';
+
+CREATE TRIGGER echobase_refresh_views_trigger
+AFTER INSERT OR UPDATE ON entitymodificationlog
+EXECUTE PROCEDURE echobase_refresh_views();
+
+-----------------------------------------------------------------------------------------------------------------------
+---- CELL RESULT VIEW -------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+CREATE MATERIALIZED VIEW cellEsduViewEchotype AS
   SELECT
-    s.*,
-    m.topiaId     AS metaDataId,
-    m.name        AS metaDataName,
-    r.resultvalue AS resultValue,
-    r.topiaid     AS resultId
+    c.voyagename,
+    c.coordinate,
+    c.shape,
+    c.cellname,
+    d.name,
+    echobase_to_numeric(r.resultvalue) as resultvalue,
+    e.name as echotypeName,
+    e.meaning as echotypeMeaning,
+    r.topiaid AS resultid
   FROM
-    echobase_cell_spatial s,
+    echobase_cell_spatial c,
     result r,
-    datametadata m
+    datametadata d,
+    category cat,
+    echotype e
   WHERE
-    s.cellid = r.cell AND
-    r.datametadata = m.topiaid;
+    c.cellid = r.cell
+    AND r.datametadata  = d.topiaid
+    AND r.category = cat.topiaid
+    AND cat.echotype IS NOT NULL
+    AND cat.echotype = e.topiaid
+    AND c.celltypename = 'Elementary Distance Sampling Unit';
 
-DROP VIEW IF EXISTS echobase_cell_spatial_data;
+CREATE INDEX cellEsduViewEchotype_idx ON cellEsduViewEchotype(name);
+
+CREATE MATERIALIZED VIEW cellEsduViewEchotypeNasc AS
+  SELECT
+    *
+  FROM
+    cellEsduViewEchotype v
+  WHERE
+    v.name = 'NASC';
+
+CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(resultid);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpecies AS
+  SELECT
+    c.voyagename,
+    c.coordinate,
+    c.shape,
+    c.cellname,
+    d.name,
+    echobase_to_numeric(r.resultvalue) as resultvalue,
+    s.baracoudacode,
+    r.topiaid AS resultid
+  FROM
+    echobase_cell_spatial c,
+    result r,
+    datametadata d,
+    category cat,
+    speciescategory scat,
+    species s
+  WHERE
+    c.cellid = r.cell
+    AND r.datametadata  = d.topiaid
+    AND r.category = cat.topiaid
+    AND cat.speciescategory IS NOT NULL
+    AND cat.speciescategory = scat.topiaid
+    AND scat.species = s.topiaid
+    AND c.celltypename = 'Elementary Distance Sampling Unit';
+
+CREATE INDEX cellEsduViewSpecies_idx ON cellEsduViewSpecies(name);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped AS
+  SELECT
+    count(*) AS nbResults,
+    name,
+    cellname,
+    baracoudacode,
+    sum(resultvalue) as resultvalue,
+    cellname || '-' || baracoudacode || '-' || name as id
+  FROM cellEsduViewSpecies
+  WHERE
+    baracoudacode in ('ENGR-ENC', 'SARD-PIL', 'TRAC-TRU', 'SPRA-SPR', 'SCOM-SCO', 'MICR-POU')
+    AND name in ('Biomass','Abundance','MeanLength')
+  GROUP BY baracoudacode, cellname, name;
+
+CREATE INDEX cellEsduViewSpeciesResultGrouped_idx ON cellEsduViewSpecies(baracoudacode, cellname, name);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesBiomass AS
+  SELECT
+    distinct v2.coordinate,
+    v2.voyagename,
+    v.*
+  FROM
+    cellEsduViewSpeciesResultGrouped v
+    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
+  WHERE
+    v.name = 'Biomass';
+
+CREATE UNIQUE INDEX cellEsduViewSpeciesBiomass_uidx ON cellEsduViewSpeciesBiomass(id);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesAbundance AS
+  SELECT
+    distinct v2.coordinate,
+    v2.voyagename,
+    v.*
+  FROM
+    cellEsduViewSpeciesResultGrouped v
+    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
+  WHERE
+    v.name = 'Abundance';
+
+CREATE UNIQUE INDEX cellEsduViewSpeciesAbundance_uidx ON cellEsduViewSpeciesAbundance(id);
+
+CREATE MATERIALIZED VIEW cellEsduViewSpeciesMeanLength AS
+  SELECT
+    distinct v2.coordinate,
+    v2.voyagename,
+    v.*
+  FROM
+    cellEsduViewSpeciesResultGrouped v
+    JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname)
+  WHERE
+    v.name = 'MeanLength';
+
+CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id);
+
+-----------------------------------------------------------------------------------------------------------------------
+---- CELL DATA VIEW ---------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
 CREATE OR REPLACE VIEW echobase_cell_spatial_data AS
   SELECT
     s.*,
@@ -54,10 +287,98 @@
     s.cellid = d.cell AND
     d.datametadata = m.topiaid;
 
-DROP VIEW IF EXISTS TotalSampleEngrEnc;
-CREATE OR REPLACE VIEW TotalSampleEngrEnc AS
+-----------------------------------------------------------------------------------------------------------------------
+---- MAP CELL VIEW ----------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
 
+CREATE MATERIALIZED VIEW cellmapview AS
   SELECT
+    c.voyagename,
+    c.coordinate,
+    c.shape,
+    c.cellname,
+    d.name,
+    s.baracoudacode,
+    echobase_to_numeric(r.resultvalue) as resultvalue,
+    r.topiaid AS resultid
+  FROM
+    echobase_cell_spatial c,
+    result r,
+    datametadata d,
+    category cat,
+    speciescategory scat,
+    species s
+  WHERE
+    c.cellid = r.cell
+    AND r.datametadata  = d.topiaid
+    AND r.category = cat.topiaid
+    AND cat.speciescategory = scat.topiaid
+    AND scat.species = s.topiaid
+    AND c.celltypename = 'Map cell';
+
+CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name);
+CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(resultid);
+
+CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'ENGR-ENC'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassSardPil AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'SARD-PIL'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassTracTru AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'TRAC-TRU'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassSpraSpr AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'SPRA-SPR'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassScomSco AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'SCOM-SCO'
+    AND v.name = 'meanMapcellBiomass';
+
+CREATE OR REPLACE VIEW meanMapcellBiomassMicrPou AS
+  SELECT
+    *
+  FROM
+    cellmapview v
+  WHERE
+    v.baracoudacode = 'MICR-POU'
+    AND v.name = 'meanMapcellBiomass';
+
+-----------------------------------------------------------------------------------------------------------------------
+---- OPERATION VIEW ---------------------------------------------------------------------------------------------------
+-----------------------------------------------------------------------------------------------------------------------
+
+CREATE MATERIALIZED VIEW TotalSampleView AS
+  SELECT
     echobase_operation_spatial.voyageid,
     operation.id      AS operation_id,
     echobase_operation_spatial.coordinate,
@@ -80,212 +401,76 @@
     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';
+    speciescategory.species = species.topiaid;
 
-DROP VIEW IF EXISTS TotalSampleSardPil;
+CREATE INDEX  TotalSampleView_idx ON TotalSampleView(baracoudacode, sampletype_name);
+
+CREATE OR REPLACE VIEW TotalSampleEngrEnc AS
+  SELECT
+    *
+  FROM
+    TotalSampleView v
+  WHERE
+    v.sampletype_name = 'Total' AND
+    v.baracoudacode = 'ENGR-ENC';
+
 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
+    TotalSampleView v
   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';
+    v.sampletype_name = 'Total' AND
+    v.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
+    TotalSampleView v
   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';
+    v.sampletype_name = 'Total' AND
+    v.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
+    TotalSampleView v
   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';
+    v.sampletype_name = 'Total' AND
+    v.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
+    TotalSampleView v
   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';
+    v.sampletype_name = 'Total' AND
+    v.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
+    TotalSampleView v
   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';
+    v.sampletype_name = 'Total' AND
+    v.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
+    e.voyagename,
+    e.operationname,
+    e.coordinate,
+    (SELECT SUM(t.sampleweight) FROM totalsampleengrenc t WHERE t.operation_id = e.operationname) AS TotalCatchENGRENC,
+    (SELECT SUM(t.sampleweight) FROM totalsamplesardpil t WHERE t.operation_id = e.operationname) AS TotalCatchSARDPIL,
+    (SELECT SUM(t.sampleweight) FROM totalsamplespraspr t WHERE t.operation_id = e.operationname) AS TotalCatchSPRASPR,
+    (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU,
+    (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO,
+    (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU,
+    e.operationid
+  FROM echobase_operation_spatial e;
\ No newline at end of file

    

r954 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-domain/src/main/resources/META-INF/services echobase-domain/src/main/resources/migration echobase-domain/src/main/xmi echobase-services/src/main/resources

tchemit＠users.forge.codelutin.com

tags

participants (1)