Project

General

Profile

« Previous | Next » 

Revision 5283

Added by Aaron Marcuse-Kubitza about 12 years ago

schemas/tree_cross-links.sql: Synced with schema, updating with new table names

View differences:

tree_cross-links.sql
1 
/* To get the namedplace trigger, just search and replace "plantname" with
2 
"namedplace". */
1 
/* To get the place trigger, just search and replace taxonconcept with place. */
3 2 

  		




  4
  3
  
    
----- Trigger function


  		




  5
  4
  
    

  		




  6
  
  
    
-- Function: plantname_update_ancestors()


  		




  
  5
  
    
-- Function: taxonconcept_update_ancestors()


  		




  7
  6
  
    

  		




  8
  
  
    
-- DROP FUNCTION plantname_update_ancestors();


  		




  
  7
  
    
-- DROP FUNCTION taxonconcept_update_ancestors();


  		




  9
  8
  
    

  		




  10
  
  
    
CREATE OR REPLACE FUNCTION plantname_update_ancestors()


  		




  
  9
  
    
CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors()


  		




  11
  10
  
    
  RETURNS trigger AS


  		




  12
  11
  
    
$BODY$


  		




  13
  12
  
    
BEGIN


  		




  14
  13
  
    
    -- Delete existing ancestors


  		




  15
  
  
    
    DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;


  		




  
  14
  
    
    DELETE FROM taxonconcept_ancestor


  		




  
  15
  
    
    WHERE taxonconcept_id = new.taxonconcept_id;


  		




  16
  16
  
    
    


  		




  17
  17
  
    
    IF new.parent_id IS NOT NULL THEN


  		




  18
  18
  
    
        -- Copy parent's ancestors to this node's ancestors


  		




  19
  19
  
    
        INSERT


  		




  20
  
  
    
        INTO plantname_ancestor


  		




  21
  
  
    
        (plantname_id, ancestor_id)


  		




  
  20
  
    
        INTO taxonconcept_ancestor


  		




  
  21
  
    
        (taxonconcept_id, ancestor_id)


  		




  22
  22
  
    
        SELECT


  		




  23
  
  
    
            new.plantname_id, ancestor_id


  		




  24
  
  
    
        FROM plantname_ancestor


  		




  25
  
  
    
        WHERE plantname_id = new.parent_id


  		




  
  23
  
    
            new.taxonconcept_id, ancestor_id


  		




  
  24
  
    
        FROM taxonconcept_ancestor


  		




  
  25
  
    
        WHERE taxonconcept_id = new.parent_id


  		




  26
  26
  
    
        ;


  		




  27
  27
  
    
    END IF;


  		




  28
  28
  
    
    


  		




  ......




  31
  31
  
    
    the leaf node is the one you're looking for, in addition to that leaf node's


  		




  32
  32
  
    
    ancestors. */


  		




  33
  33
  
    
    INSERT


  		




  34
  
  
    
    INTO plantname_ancestor


  		




  35
  
  
    
    (plantname_id, ancestor_id)


  		




  36
  
  
    
    VALUES (new.plantname_id, new.plantname_id)


  		




  
  34
  
    
    INTO taxonconcept_ancestor


  		




  
  35
  
    
    (taxonconcept_id, ancestor_id)


  		




  
  36
  
    
    VALUES (new.taxonconcept_id, new.taxonconcept_id)


  		




  37
  37
  
    
    ;


  		




  38
  38
  
    
    


  		




  39
  39
  
    
    -- Tell immediate children to update their ancestors lists, which will


  		




  40
  40
  
    
    -- recursively tell all descendants


  		




  41
  
  
    
    UPDATE plantname


  		




  42
  
  
    
    SET plantname_id = plantname_id -- need at least one SET statement


  		




  43
  
  
    
    -- Add COALESCE() to enable using plantname_unique index for lookup


  		




  44
  
  
    
    WHERE COALESCE(parent_id, 2147483647) = new.plantname_id


  		




  
  41
  
    
    UPDATE taxonconcept


  		




  
  42
  
    
    SET taxonconcept_id = taxonconcept_id -- need at least one SET statement


  		




  
  43
  
    
    -- Add COALESCE() to enable using taxonconcept_unique index for lookup


  		




  
  44
  
    
    WHERE COALESCE(parent_id, 2147483647) = new.taxonconcept_id


  		




  45
  45
  
    
    ;


  		




  46
  46
  
    
    


  		




  47
  47
  
    
    /* Note: We don't need an ON DELETE trigger to update the descendants'


  		




  48
  
  
    
    ancestors when a node is deleted, because the plantname.plantname_parent_id


  		




  49
  
  
    
    foreign key is set to ON DELETE CASCADE, which just removes all the


  		




  50
  
  
    
    descendants anyway. */


  		




  
  48
  
    
    ancestors when a node is deleted, because the taxonconcept.parent_id foreign


  		




  
  49
  
    
    key is ON DELETE CASCADE, which just removes all the descendants anyway. */


  		




  51
  50
  
    
    


  		




  52
  51
  
    
    RETURN new;


  		




  53
  52
  
    
END;


  		




  54
  53
  
    
$BODY$


  		




  55
  54
  
    
  LANGUAGE plpgsql VOLATILE


  		




  56
  55
  
    
  COST 100;


  		




  57
  
  
    
ALTER FUNCTION plantname_update_ancestors()


  		




  
  56
  
    
ALTER FUNCTION taxonconcept_update_ancestors()


  		




  58
  57
  
    
  OWNER TO bien;


  		




  59
  58
  
    

  		




  60
  59
  
    
----- Ancestors table


  		




  61
  60
  
    

  		




  62
  
  
    
-- Table: plantname_ancestor


  		




  
  61
  
    
-- Table: taxonconcept_ancestor


  		




  63
  62
  
    

  		




  64
  
  
    
-- DROP TABLE plantname_ancestor;


  		




  
  63
  
    
-- DROP TABLE taxonconcept_ancestor;


  		




  65
  64
  
    

  		




  66
  
  
    
CREATE TABLE plantname_ancestor


  		




  
  65
  
    
CREATE TABLE taxonconcept_ancestor


  		




  67
  66
  
    
(


  		




  68
  
  
    
  plantname_id integer NOT NULL,


  		




  
  67
  
    
  taxonconcept_id integer NOT NULL,


  		




  69
  68
  
    
  ancestor_id integer NOT NULL,


  		




  70
  
  
    
  CONSTRAINT plantname_ancestor_pkey PRIMARY KEY (plantname_id , ancestor_id ),


  		




  71
  
  
    
  CONSTRAINT plantname_ancestor_ancestor_id FOREIGN KEY (ancestor_id)


  		




  72
  
  
    
      REFERENCES plantname (plantname_id) MATCH SIMPLE


  		




  
  69
  
    
  CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id , ancestor_id ),


  		




  
  70
  
    
  CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id)


  		




  
  71
  
    
      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE


  		




  73
  72
  
    
      ON UPDATE CASCADE ON DELETE CASCADE,


  		




  74
  
  
    
  CONSTRAINT plantname_ancestor_plantname_id FOREIGN KEY (plantname_id)


  		




  75
  
  
    
      REFERENCES plantname (plantname_id) MATCH SIMPLE


  		




  
  73
  
    
  CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id)


  		




  
  74
  
    
      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE


  		




  76
  75
  
    
      ON UPDATE CASCADE ON DELETE CASCADE


  		




  77
  76
  
    
)


  		




  78
  77
  
    
WITH (


  		




  79
  78
  
    
  OIDS=FALSE


  		




  80
  79
  
    
);


  		




  81
  
  
    
ALTER TABLE plantname_ancestor


  		




  
  80
  
    
ALTER TABLE taxonconcept_ancestor


  		




  82
  81
  
    
  OWNER TO bien;


  		




  83
  82
  
    

  		




  84
  83
  
    
----- Trigger


  		




  85
  84
  
    

  		




  86
  
  
    
-- Trigger: plantname_update_ancestors on plantname


  		




  
  85
  
    
-- Trigger: taxonconcept_update_ancestors on taxonconcept


  		




  87
  86
  
    

  		




  88
  
  
    
-- DROP TRIGGER plantname_update_ancestors ON plantname;


  		




  
  87
  
    
-- DROP TRIGGER taxonconcept_update_ancestors ON taxonconcept;


  		




  89
  88
  
    

  		




  90
  
  
    
CREATE TRIGGER plantname_update_ancestors


  		




  
  89
  
    
CREATE TRIGGER taxonconcept_update_ancestors


  		




  91
  90
  
    
  AFTER INSERT OR UPDATE


  		




  92
  
  
    
  ON plantname


  		




  
  91
  
    
  ON taxonconcept


  		




  93
  92
  
    
  FOR EACH ROW


  		




  94
  
  
    
  EXECUTE PROCEDURE plantname_update_ancestors();


  		




  
  93
  
    
  EXECUTE PROCEDURE taxonconcept_update_ancestors();


  		












Also available in:  Unified diff