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