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