FusqlFS/Backend/MySQL/Procedures.pm untested

filter: Statementstmt 0.0% 0 / 57 Branchbran 0.0% 0 / 6 Conditioncond 0.0% 0 / 6 MC/DCmcdc 0.0% 0 / 3 Subroutinesub 0.0% 0 / 11 Podpod 0.0% 0 / 7 totaltotal 0.0% 0 / 90 CC 4 SCAR 15.5
CC
4
Cov
0%
store
12.0
get
6.0
init
2.0
CRAP 4.7
1 use strict;
2 use 5.010;
3  
4 package FusqlFS::Backend::MySQL::Procedures;
5 use FusqlFS::Version;
6 our $VERSION = $FusqlFS::Version::VERSION;
7 use parent 'FusqlFS::Artifact';
8  
9 =head1 NAME
10  
11 FusqlFS::Backend::MySQL::Procedures - FusqlFS MySQL database stored procedures interface
12  
13 =head1 SYNOPSIS
14  
15 =head1 DESCRIPTION
16  
17 =head1 EXPOSED STRUCTURE
18  
19 =over
20  
21 =item F<./create.sql>
22  
23 C<CREATE PROCEDURE> statement to create the procedure.
24  
25 =item F<./comment>
26  
27 Procedure comment
28  
29 =item F<./struct>
30  
31 Additional stored procedure info with following fields:
32  
33 =over
34  
35 =item C<sql>
36  
37 I<one of contains, no, reads, modifies> an SQL mode
38  
39 =item C<security>
40  
41 I<one of definer or invoker> security context
42  
43 =item C<parameters>
44  
45 List of procedure parameters.
46  
47 =back
48  
49 =item F<./code>
50  
51 Stored procedure code.
52  
53 =item F<./definer>
54  
55 Symlink to user who defined the procedure.
56  
57 =back
58  
59 =cut
60  
61 sub init
62 {
63 my $self = shift;
64 $self->{create_expr} = 'CREATE PROCEDURE `%(name)$s` (%(params)$s) %(sql_mode)$s SQL SECURITY %(security)$s %(mutable)$s DETERMINISTIC %(code)$s';
65 $self->{drop_expr} = 'DROP PROCEDURE `%s`';
66 $self->{list_expr} = $self->expr('SELECT name FROM mysql.proc WHERE db = DATABASE() AND type = "PROCEDURE"');
67 $self->{get_expr} = $self->expr('SELECT * FROM mysql.proc WHERE db = DATABASE() AND type = "PROCEDURE" AND name = ?');
68 $self->{get_create_expr} = 'SHOW CREATE PROCEDURE `%s`';
69 }
70  
71 sub drop
72 {
73 my $self = shift;
74 my $name = shift;
75 $self->do($self->{drop_expr}, [$name]);
76 }
77  
78 sub create
79 {
80 my $self = shift;
81 $self->store($_[0], {
82 struct => {
83 sql => 'contains',
84 security => 'definer',
85 immutable => 'yes',
86 parameters => [],
87 },
88 code => 'BEGIN SELECT 1; END',
89 definer => \('users/' . $self->dbh()->{Username} . '@%'),
90 comment => '',
91 })
92 }
93  
94 sub store
95 {
96 my $self = shift;
97 my ($name, $data) = @_;
98 my $struct = $self->validate($data, {
99 struct => {
100 sql => qr/^(?:|no|contains|reads|modifies)$/i,
101 security => qr/^(?:definer|invoker)$/i,
102 immutable => '',
103 parameters => ['ARRAY', sub {
104 foreach my $v (@$_) {
105 die 'INVALID' if $v !~ /^(?:in|out)\s+\w+\s+[\w\s(),]+$/i;
106 }
107 return $_;
108 }],
109 },
110 code => undef,
111 definer => ['SCALAR', sub{ $$_ =~ m{^users/\w+} }],
112 comment => undef,
113 }) or return;
114  
115 my %sql_modes = {
116 no => 'NO SQL',
117 contains => 'CONTAINS SQL',
118 reads => 'READS SQL DATA',
119 modifies => 'MODIFIES SQL DATA',
120 };
121  
122 $self->drop($name);
123 $self->do($self->{create_expr}, {
124 name => $name,
125 params => join(', ', @{$struct->{struct}->{parameters}||[]}),
126 sql_mode => $sql_modes{lc($struct->{struct}->{sql})} || '',
127 security => uc($struct->{struct}->{security}),
128 mutable => $struct->{struct}->{immutable}? '': 'NOT',
129 code => $struct->{code},
130 });
131 }
132  
133 sub rename
134 {
135 my $self = shift;
136 my ($name, $newname) = @_;
137  
138 my $data = $self->get($name);
139 $self->drop($name);
140 $self->store($newname, $data);
141 }
142  
143 sub list
144 {
145 my $self = shift;
146 return $self->all_col($self->{list_expr});
147 }
148  
149 sub get
150 {
151 my $self = shift;
152 my $name = shift;
153  
154 my $data = $self->one_row($self->{get_expr}, $name);
155 return unless $data;
156  
157 my %sql_modes = (
158 NO_SQL => 'no',
159 CONTAINS_SQL => 'contains',
160 READS_SQL_DATA => 'reads',
161 MODIFIES_SQL_DATA => 'modifies',
162 );
163  
164 my $param_list = $data->{param_list};
165 $param_list =~ s/^\s+//;
166 $param_list =~ s/\s+$//;
167 $param_list =~ s/\s+/ /g;
168  
169 return {
170 struct => $self->dump({
171 sql => $sql_modes{$data->{sql_data_access}},
172 security => lc($data->{security_type}),
173 immutable => $data->{is_deterministic} eq 'YES',
174 parameters => [ split(', ', $param_list) ],
175 }),
176 code => $data->{body},
177 definer => \"users/$data->{definer}",
178 comment => $data->{comment},
179 };
180 }
181  
182 1;