1
2
3
4
5
6
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
|
# frozen_string_literal: true
module TriggerHelpers
def expect_function_to_exist(name)
expect(find_function_def(name)).not_to be_nil
end
def expect_function_not_to_exist(name)
expect(find_function_def(name)).to be_nil
end
def expect_function_to_contain(name, *statements)
return_stmt, *body_stmts = parsed_function_statements(name).reverse
expect(return_stmt).to eq('return old')
expect(body_stmts).to contain_exactly(*statements)
end
def expect_trigger_not_to_exist(table_name, name)
expect(find_trigger_def(table_name, name)).to be_nil
end
def expect_valid_function_trigger(table_name, name, fn_name, fires_on)
events, timing, definition = cleaned_trigger_def(table_name, name)
events = events&.split(',')
expected_timing, expected_events = fires_on.first
expect(timing).to eq(expected_timing.to_s)
expect(events).to match_array(Array.wrap(expected_events))
# TODO: Update CREATE TRIGGER syntax to use EXECUTE FUNCTION
# https://gitlab.com/gitlab-org/gitlab/-/issues/227089
expect(definition).to match(%r{execute (?:procedure|function) #{fn_name}()})
end
private
def parsed_function_statements(name)
cleaned_definition = find_function_def(name)['body'].downcase.gsub(/\s+/, ' ')
statements = cleaned_definition.sub(/\A\s*begin\s*(.*)\s*end\s*\Z/, "\\1")
statements.split(';').map! { |stmt| stmt.strip.presence }.compact!
end
def find_function_def(name)
connection.select_one(<<~SQL)
SELECT prosrc AS body
FROM pg_proc
WHERE proname = '#{name}'
SQL
end
def cleaned_trigger_def(table_name, name)
find_trigger_def(table_name, name).values_at('event', 'action_timing', 'action_statement').map!(&:downcase)
end
def find_trigger_def(table_name, name)
connection.select_one(<<~SQL)
SELECT
string_agg(event_manipulation, ',') AS event,
action_timing,
action_statement
FROM information_schema.triggers
WHERE event_object_table = '#{table_name}'
AND trigger_name = '#{name}'
GROUP BY 2, 3
SQL
end
end
|
