Author: tchemit Date: 2014-04-17 11:20:20 +0200 (Thu, 17 Apr 2014) New Revision: 1009 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/1009 Log: fixes #4948 Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6_1.java trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.6.1.sql trunk/echobase-domain/src/main/resources/postgis-structure.sql Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6_1.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6_1.java 2014-04-17 08:11:08 UTC (rev 1008) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6_1.java 2014-04-17 09:20:20 UTC (rev 1009) @@ -32,7 +32,7 @@ if (spatialAware) { - // fix add new postigs view (see https://forge.codelutin.com/issues/4194) + // fix add new postigs view (see https://forge.codelutin.com/issues/4947) updatePostgis(queries); } } Modified: trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.6.1.sql =================================================================== --- trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.6.1.sql 2014-04-17 08:11:08 UTC (rev 1008) +++ trunk/echobase-domain/src/main/resources/migration/postgis-structure-2.6.1.sql 2014-04-17 09:20:20 UTC (rev 1009) @@ -1,17 +1,76 @@ -CREATE OR REPLACE FUNCTION echobase_delete_cell() +-- +-- echobase_delete_voyage trigger +-- +DROP TRIGGER IF EXISTS echobase_delete_voyage ON voyage; + +CREATE OR REPLACE FUNCTION echobase_delete_voyage() RETURNS TRIGGER AS $$ BEGIN - RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + RAISE DEBUG 'Delete voyage % , delete cascade in echobase_cell_spatial_table, echobase_operation_spatial', OLD.topiaid; - DELETE FROM echobase_cell_spatial_temp WHERE cellid = OLD.topiaid; - DELETE FROM echobase_cell_spatial WHERE cellid = OLD.topiaid; + DELETE FROM echobase_cell_spatial WHERE voyageid = OLD.topiaid; + DELETE FROM echobase_operation_spatial WHERE voyageid = OLD.topiaid; RETURN OLD; END $$ LANGUAGE 'plpgsql'; +CREATE TRIGGER echobase_delete_voyage +BEFORE DELETE ON voyage +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_voyage(); + +-- +-- echobase_delete_transit trigger +-- +DROP TRIGGER IF EXISTS echobase_delete_transit ON transit; + +CREATE OR REPLACE FUNCTION echobase_delete_transit() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete transit % , delete cascade in echobase_cell_spatial_table, echobase_operation_spatial', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE transitid = OLD.topiaid; + DELETE FROM echobase_operation_spatial WHERE transitid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_transit +BEFORE DELETE ON transit +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_transit(); + +-- +-- echobase_delete_transect trigger +-- +DROP TRIGGER IF EXISTS echobase_delete_transect ON transect; + +CREATE OR REPLACE FUNCTION echobase_delete_transect() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete transect % , delete cascade in echobase_cell_spatial_table, echobase_operation_spatial', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE transectid = OLD.topiaid; + DELETE FROM echobase_operation_spatial WHERE transectid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_transect +BEFORE DELETE ON transect +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_transect(); + +-- +-- echobase_delete_operation trigger +-- +DROP TRIGGER IF EXISTS echobase_delete_operation ON operation; CREATE OR REPLACE FUNCTION echobase_delete_operation() RETURNS TRIGGER AS $$ BEGIN @@ -26,6 +85,77 @@ $$ LANGUAGE 'plpgsql'; +CREATE TRIGGER echobase_delete_operation +BEFORE DELETE ON operation +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_operation(); + +-- +-- echobase_delete_dataProcessing trigger +-- +DROP TRIGGER IF EXISTS echobase_delete_dataProcessing ON dataprocessing; + +CREATE OR REPLACE FUNCTION echobase_delete_dataProcessing() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete dataprocessing % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE dataprocessingid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_dataProcessing +BEFORE DELETE ON dataprocessing +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_dataProcessing(); + +-- +-- echobase_delete_dataAcquisition trigger +-- +DROP TRIGGER IF EXISTS echobase_delete_dataAcquisition ON dataAcquisition; + +CREATE OR REPLACE FUNCTION echobase_delete_dataAcquisition() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete dataAcquisition % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE dataacquisitionid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_dataAcquisition +BEFORE DELETE ON dataAcquisition +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_dataAcquisition(); + +-- +-- echobase_delete_cell trigger +-- +DROP TRIGGER IF EXISTS echobase_delete_cell ON cell; + +CREATE OR REPLACE FUNCTION echobase_delete_cell() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + DELETE FROM echobase_cell_spatial_temp WHERE cellid = OLD.topiaid; + DELETE FROM echobase_cell_spatial WHERE cellid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_cell +BEFORE DELETE ON cell +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell(); + CREATE OR REPLACE FUNCTION echobase_create_echobase_operation_spatial_row( operation_id VARCHAR, coordinateText VARCHAR) Modified: trunk/echobase-domain/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-domain/src/main/resources/postgis-structure.sql 2014-04-17 08:11:08 UTC (rev 1008) +++ trunk/echobase-domain/src/main/resources/postgis-structure.sql 2014-04-17 09:20:20 UTC (rev 1009) @@ -42,7 +42,17 @@ DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data CASCADE; DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table() CASCADE; DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_temp_table() CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_voyage ON cell CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_transit ON cell CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_transect ON cell CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_dataAcquisition ON cell CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_dataProcessing ON cell CASCADE; DROP TRIGGER IF EXISTS echobase_delete_cell ON cell CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_voyage() CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_transit() CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_transect() CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_dataAcquisition() CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_dataProcessing() CASCADE; DROP FUNCTION IF EXISTS echobase_delete_cell() CASCADE; DROP FUNCTION IF EXISTS echobase_compute_all_cell_spatial_data() CASCADE; DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_table() CASCADE; @@ -229,10 +239,117 @@ NULL) EXECUTE PROCEDURE echobase_fill_cell_spatial_temp_table(); -- --- Trigger qui met à jour la table des traitements spatiaux à faire lors d +-- Trigger qui met à jour la table des données spatiales lors d +-- 'une suppression de voyage +-- +CREATE OR REPLACE FUNCTION echobase_delete_voyage() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete voyage % , delete cascade in echobase_cell_spatial_table, echobase_operation_spatial', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE voyageid = OLD.topiaid; + DELETE FROM echobase_operation_spatial WHERE voyageid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_voyage +BEFORE DELETE ON voyage +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_voyage(); + +-- +-- Trigger qui met à jour la table des données spatiales lors d +-- 'une suppression de transit +-- +CREATE OR REPLACE FUNCTION echobase_delete_transit() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete transit % , delete cascade in echobase_cell_spatial_table, echobase_operation_spatial', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE transitid = OLD.topiaid; + DELETE FROM echobase_operation_spatial WHERE transitid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_transit +BEFORE DELETE ON transit +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_transit(); + +-- +-- Trigger qui met à jour la table des données spatiales lors d +-- 'une suppression de transect +-- +CREATE OR REPLACE FUNCTION echobase_delete_transect() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete transect % , delete cascade in echobase_cell_spatial_table, echobase_operation_spatial', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE transectid = OLD.topiaid; + DELETE FROM echobase_operation_spatial WHERE transectid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_transect +BEFORE DELETE ON transect +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_transect(); + +-- +-- Trigger qui met à jour la table des données spatiales lors d +-- 'une suppression de dataAcquisition +-- +CREATE OR REPLACE FUNCTION echobase_delete_dataProcessing() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete dataprocessing % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE dataprocessingid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_dataProcessing +BEFORE DELETE ON dataprocessing +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_dataProcessing(); + +-- +-- Trigger qui met à jour la table des données spatiales lors d +-- 'une suppression de dataProcessing +-- +CREATE OR REPLACE FUNCTION echobase_delete_dataAcquisition() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete dataAcquisition % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE dataacquisitionid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_dataAcquisition +BEFORE DELETE ON dataAcquisition +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_dataAcquisition(); + +-- +-- Trigger qui met à jour la table des données spatiales lors d -- 'une suppression de cellule -- - CREATE OR REPLACE FUNCTION echobase_delete_cell() RETURNS TRIGGER AS $$ BEGIN