File Coverage

blib/lib/Test/DBChanges/Role/Pg.pm
Criterion Covered Total %
statement 8 21 38.1
branch 0 4 0.0
condition n/a
subroutine 3 4 75.0
pod 0 1 0.0
total 11 30 36.6


line stmt bran cond sub pod time code
1             package Test::DBChanges::Role::Pg;
2 2     2   1035 use Moo::Role;
  2         4  
  2         10  
3 2     2   666 use 5.024;
  2         7  
4 2     2   20 use namespace::autoclean;
  2         5  
  2         11  
5              
6             with 'Test::DBChanges::Role::Base',
7             'Test::DBChanges::Role::Triggers',
8             'Test::DBChanges::Role::JSON';
9              
10             our $VERSION = '1.0.1'; # VERSION
11             # ABSTRACT: installs triggers for PostgreSQL
12              
13              
14             sub maybe_prepare_db {
15 0     0 0   my ($self) = @_;
16              
17 0           my $prefix = $self->db_item_prefix;
18 0           my $tablename = $self->_table_name;
19 0           $self->_db_do(<<"SQL");
20             CREATE TABLE IF NOT EXISTS ${tablename} (
21             id SERIAL PRIMARY KEY,
22             table_name VARCHAR NOT NULL,
23             operation VARCHAR NOT NULL,
24             data JSONB NOT NULL,
25             done_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
26             )
27             SQL
28              
29 0           my $procname = "${prefix}_proc";
30 0           my $already_there = $self->_db_fetch(<<'SQL',$procname);
31             SELECT 1 AS ok
32             FROM information_schema.routines
33             WHERE routine_name = ?;
34             SQL
35              
36 0 0         unless ($already_there->[0]{ok}) {
37 0           $self->_db_do(<<"SQL");
38             CREATE FUNCTION ${procname}() RETURNS TRIGGER AS \$\$
39             BEGIN
40             IF (TG_OP = 'DELETE') THEN
41             INSERT INTO ${tablename} (table_name,operation,data)
42             VALUES (TG_TABLE_NAME,TG_OP,row_to_json(OLD));
43             ELSE
44             INSERT INTO ${tablename} (table_name,operation,data)
45             VALUES (TG_TABLE_NAME,TG_OP,row_to_json(NEW));
46             END IF;
47             RETURN NULL;
48             END;
49             \$\$ LANGUAGE plpgsql
50             SQL
51             }
52              
53             # notice that, since we install all triggers we need that are not
54             # already there, multiple instances of DBChanges should co-exist
55             # peacefully in the same schema
56 0           for my $table (sort keys $self->_table_source_map->%*) {
57 0           my $trigger_name = "${prefix}_${table}_trig";
58 0           my $already_there = $self->_db_fetch(<<'SQL',$trigger_name, $table);
59             SELECT 1 AS ok
60             FROM information_schema.triggers
61             WHERE trigger_name = ? AND event_object_table = ?
62             SQL
63 0 0         next if $already_there->[0]{ok};
64              
65 0           $self->_db_do(<<"SQL");
66             CREATE TRIGGER ${trigger_name}
67             AFTER INSERT OR UPDATE OR DELETE ON ${table}
68             FOR EACH ROW EXECUTE PROCEDURE ${procname}();
69             SQL
70             }
71             }
72              
73             1;
74              
75             __END__