applied a more structured approach to taxon concepts triggers

Agnieszka Figiel · Agnieszka Figiel · commit 10e88c1869dd · 2013-01-16T11:01:43.000Z
diff --git a/db/plpgsql/012_rebuild_mviews.sql b/db/plpgsql/012_rebuild_mviews.sql
@@ -1 +1,27 @@
+DROP FUNCTION IF EXISTS taxon_concepts_refresh_row(id INTEGER);
+CREATE OR REPLACE FUNCTION taxon_concepts_refresh_row(row_id INTEGER) RETURNS VOID
+SECURITY DEFINER
+LANGUAGE 'plpgsql' AS $$
+BEGIN
+  DELETE
+  FROM taxon_concepts_mview tc
+  WHERE tc.id = row_id;
 
+  INSERT INTO taxon_concepts_mview
+  SELECT *, FALSE, NULL
+  FROM taxon_concepts_view tc
+  WHERE tc.id = row_id;
+END
+$$;
+
+DROP FUNCTION IF EXISTS taxon_concepts_invalidate_row(id INTEGER);
+CREATE OR REPLACE FUNCTION taxon_concepts_invalidate_row(row_id INTEGER) RETURNS VOID
+SECURITY DEFINER
+LANGUAGE 'plpgsql' AS $$
+BEGIN
+  UPDATE taxon_concepts_mview tc
+  SET dirty = TRUE
+  WHERE tc.id = row_id;
+  RETURN;
+END
+$$;
diff --git a/db/plpgsql/014_taxon_concept_triggers.sql b/db/plpgsql/014_taxon_concept_triggers.sql
@@ -1,34 +1,53 @@
-CREATE OR REPLACE FUNCTION trg_taxonomic_positions() RETURNS trigger AS $trg_taxonomic_positions$
-  BEGIN
-    IF TG_OP = 'INSERT' AND NEW.parent_id IS NOT NULL THEN
+CREATE OR REPLACE FUNCTION trg_taxon_concepts_u() RETURNS TRIGGER
+SECURITY DEFINER LANGUAGE 'plpgsql' AS $$
+BEGIN
+  IF OLD.taxonomic_position <> NEW.taxonomic_position OR OLD.parent_id <> NEW.parent_id THEN
+    IF NEW.parent_id IS NOT NULL THEN
       PERFORM rebuild_taxonomic_positions_from_root(NEW.parent_id);
     ELSE
       PERFORM rebuild_taxonomic_positions_from_root(NEW.id);
     END IF;
-    RETURN NEW;
-  END;
-$trg_taxonomic_positions$ LANGUAGE plpgsql;
+  END IF;
+  IF OLD.taxon_name_id <> NEW.taxon_name_id OR OLD.rank_id <> NEW.rank_id THEN
+    PERFORM taxon_concepts_refresh_row(NEW.id);
+  END IF;
+  RETURN NULL;
+END
+$$;
 
-DROP TRIGGER IF EXISTS trg_taxonomic_positions ON taxon_concepts;
+CREATE OR REPLACE FUNCTION trg_taxon_concepts_d() RETURNS TRIGGER
+SECURITY DEFINER LANGUAGE 'plpgsql' AS $$
+BEGIN
+  PERFORM taxon_concepts_refresh_row(OLD.id);
+  RETURN NULL;
+END
+$$;
 
-CREATE TRIGGER trg_taxonomic_positions
-  AFTER INSERT OR UPDATE OF taxonomic_position
-  ON taxon_concepts
-  FOR EACH ROW
-  WHEN (pg_trigger_depth() = 0)
-    EXECUTE PROCEDURE trg_taxonomic_positions();
+CREATE OR REPLACE FUNCTION trg_taxon_concepts_i() RETURNS TRIGGER
+SECURITY DEFINER LANGUAGE 'plpgsql' AS $$
+BEGIN
+  IF NEW.parent_id IS NOT NULL THEN
+    PERFORM rebuild_taxonomic_positions_from_root(NEW.parent_id);
+  ELSE
+    PERFORM rebuild_taxonomic_positions_from_root(NEW.id);
+  END IF;
+  PERFORM rebuild_names_and_ranks_for_node(NEW.id);
+  PERFORM taxon_concepts_refresh_row(NEW.id);
+  RETURN NULL;
+END
+$$;
 
-CREATE OR REPLACE FUNCTION trg_names_and_ranks() RETURNS trigger AS $trg_names_and_ranks$
-  BEGIN
-    PERFORM rebuild_names_and_ranks_for_node(NEW.id);
-    RETURN NEW;
-  END;
-$trg_names_and_ranks$ LANGUAGE plpgsql;
+DROP TRIGGER IF EXISTS trg_taxon_concepts_u ON taxon_concepts;
+CREATE TRIGGER trg_taxon_concepts_u AFTER UPDATE ON taxon_concepts
+FOR EACH ROW EXECUTE PROCEDURE trg_taxon_concepts_u();
+DROP TRIGGER IF EXISTS trg_taxon_concepts_d ON taxon_concepts;
+CREATE TRIGGER trg_taxon_concepts_d AFTER DELETE ON taxon_concepts
+FOR EACH ROW EXECUTE PROCEDURE trg_taxon_concepts_d();
+DROP TRIGGER IF EXISTS trg_taxon_concepts_i ON taxon_concepts;
+CREATE TRIGGER trg_taxon_concepts_i AFTER INSERT ON taxon_concepts
+FOR EACH ROW EXECUTE PROCEDURE trg_taxon_concepts_i();
 
+DROP TRIGGER IF EXISTS trg_taxonomic_positions ON taxon_concepts;
+DROP FUNCTION IF EXISTS trg_taxonomic_positions();
 DROP TRIGGER IF EXISTS trg_names_and_ranks ON taxon_concepts;
-
-CREATE TRIGGER trg_names_and_ranks
-  AFTER INSERT OR UPDATE OF taxon_name_id, rank_id
-  ON taxon_concepts
-  FOR EACH ROW
-    EXECUTE PROCEDURE trg_names_and_ranks();
+DROP FUNCTION IF EXISTS trg_names_and_ranks();
diff --git a/doc/sapi_triggers.doc b/doc/sapi_triggers.doc
