Class: DBViewCTI::SQLGeneration::Migration::PostgreSQL

Inherits:
Base
  • Object
show all
Defined in:
lib/db_view_cti/sql_generation/migration/postgresql.rb

Instance Method Summary collapse

Methods inherited from Base

#create_view_sql, #drop_view_sql, #initialize

Constructor Details

This class inherits a constructor from DBViewCTI::SQLGeneration::Migration::Base

Instance Method Details

#create_trigger_sql ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
# File 'lib/db_view_cti/sql_generation/migration/postgresql.rb', line 7

def create_trigger_sql
  # trigger function
  @trigger_func_name = DBViewCTI::Names.trigger_function_name(@derived_class)
  
  insert_trigger_func = <<-eos
    CREATE OR REPLACE FUNCTION #{trigger_func_name}()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $function$
      DECLARE
        base_id integer;
        derived_id integer;
        return_row RECORD;
      BEGIN
        IF TG_OP = 'INSERT' THEN
          -- insert into base class and return id
          INSERT INTO #{@base_class_table} (#{ @insert_base_class_columns.join(', ') })
          VALUES (#{ add_table_name(@insert_base_class_columns, 'NEW').join(', ') })
          RETURNING #{@base_class_key}
          INTO base_id;
          -- insert into derived class, including foreign key
          INSERT INTO #{@derived_class_table} (#{ (@insert_derived_class_columns + [@derived_class_key]).join(', ') })
          VALUES (#{ add_table_name(@insert_derived_class_columns, 'NEW').join(', ') },
                  base_id)
          RETURNING id
          INTO derived_id;
          -- return correct record from view
          SELECT * 
          INTO return_row
          FROM #{@view_name}
          WHERE id = derived_id
          LIMIT 1;
          RETURN return_row;
  
        ELSIF TG_OP = 'UPDATE' THEN
          -- update base class
          UPDATE #{@base_class_table}
          SET #{ update_notation(@update_base_class_columns, 'NEW') }
          WHERE #{@base_class_table}.#{@base_class_key} = 
                (SELECT #{@derived_class_key} FROM #{@derived_class_table}
                 WHERE #{@derived_class_table}.id = OLD.id); 
          -- update derived class
          UPDATE #{@derived_class_table}
          SET #{ update_notation(@update_derived_class_columns, 'NEW') }
          WHERE #{@derived_class_table}.id = OLD.id;
          -- return correct record from view
          SELECT * 
          INTO return_row
          FROM #{@view_name}
          WHERE id = OLD.id
          LIMIT 1;
          RETURN return_row;
  
        ELSIF TG_OP = 'DELETE' THEN
          -- find foreign key (not present in view!) in derived class
          SELECT #{@derived_class_key}
          INTO base_id
          FROM #{@derived_class_table}
          WHERE id = OLD.id;
          -- due to possible key constraints we first delete the derived class
          DELETE FROM #{@derived_class_table}
          WHERE #{@derived_class_table}.id = OLD.id;
          -- delete base class
          DELETE FROM #{@base_class_table}
          WHERE #{@base_class_table}.#{@base_class_key} = base_id;
          RETURN NULL; 
          
        END IF;
        RETURN NEW;
      END;          
    $function$;
  eos
  
  # trigger:
  insert_trigger = <<-eos
    CREATE TRIGGER #{@trigger_name}
    INSTEAD OF INSERT OR UPDATE OR DELETE ON #{@view_name}
    FOR EACH ROW
    EXECUTE PROCEDURE #{trigger_func_name}(); 
  eos
  insert_trigger_func + insert_trigger
end

#drop_trigger_sql ⇒ Object 



94
95
96
97
98
99
 
# File 'lib/db_view_cti/sql_generation/migration/postgresql.rb', line 94

def drop_trigger_sql
  query = <<-eos
    DROP TRIGGER IF EXISTS #{@trigger_name} ON #{@view_name};
    DROP FUNCTION IF EXISTS #{trigger_func_name}();
  eos
end

#view_exists_sql ⇒ Object 



90
91
92
 
# File 'lib/db_view_cti/sql_generation/migration/postgresql.rb', line 90

def view_exists_sql
  "SELECT count(*) FROM pg_views where viewname='#{@view_name}';"
end