FusqlFS/Backend/MySQL/Functions.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::Functions;
5 use FusqlFS::Version;
6 our $VERSION = $FusqlFS::Version::VERSION;
7 use parent 'FusqlFS::Artifact';
8  
9 =head1 NAME
10  
11 FusqlFS::Backend::MySQL::Functions - FusqlFS MySQL database stored functions 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 FUNCTION> statement to create the function.
24  
25 =item F<./comment>
26  
27 Procedure comment
28  
29 =item F<./struct>
30  
31 Additional stored function 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 function parameters.
46  
47 =item C<returns>
48  
49 Return type.
50  
51 =back
52  
53 =item F<./code>
54  
55 Stored function code.
56  
57 =item F<./definer>
58  
59 Symlink to user who defined the function.
60  
61 =back
62  
63 =cut
64  
65 sub init
66 {
67 my $self = shift;
68 $self->{create_expr} = 'CREATE FUNCTION `%(name)$s` (%(params)$s) RETURNS %(returns)$s %(sql_mode)$s SQL SECURITY %(security)$s %(mutable)$s DETERMINISTIC %(code)$s';
69 $self->{drop_expr} = 'DROP FUNCTION `%s`';
70 $self->{list_expr} = $self->expr('SELECT name FROM mysql.proc WHERE db = DATABASE() AND type = "FUNCTION"');
71 $self->{get_expr} = $self->expr('SELECT * FROM mysql.proc WHERE db = DATABASE() AND type = "FUNCTION" AND name = ?');
72 $self->{get_create_expr} = 'SHOW CREATE FUNCTION `%s`';
73 }
74  
75 sub drop
76 {
77 my $self = shift;
78 my $name = shift;
79 $self->do($self->{drop_expr}, [$name]);
80 }
81  
82 sub create
83 {
84 my $self = shift;
85 $self->store($_[0], {
86 struct => {
87 sql => 'contains',
88 security => 'definer',
89 immutable => 'yes',
90 parameters => [],
91 returns => 'int',
92 },
93 code => 'BEGIN RETURN 1; END',
94 definer => \('users/' . $self->dbh()->{Username} . '@%'),
95 comment => '',
96 })
97 }
98  
99 sub store
100 {
101 my $self = shift;
102 my ($name, $data) = @_;
103 my $struct = $self->validate($data, {
104 struct => {
105 sql => qr/^(?:|no|contains|reads|modifies)$/i,
106 security => qr/^(?:definer|invoker)$/i,
107 immutable => '',
108 parameters => ['ARRAY', sub {
109 foreach my $v (@$_) {
110 die 'INVALID' if $v !~ /^\w+\s+[\w\s(),]+$/i;
111 }
112 return $_;
113 }],
114 returns => qr/^[\w\s(),]+$/,
115 },
116 code => undef,
117 definer => ['SCALAR', sub{ $$_ =~ m{^users/\w+} }],
118 comment => undef,
119 }) or return;
120  
121 my %sql_modes = {
122 no => 'NO SQL',
123 contains => 'CONTAINS SQL',
124 reads => 'READS SQL DATA',
125 modifies => 'MODIFIES SQL DATA',
126 };
127  
128 $self->drop($name);
129 $self->do($self->{create_expr}, {
130 name => $name,
131 params => join(', ', @{$struct->{struct}->{parameters}||[]}),
132 returns => $struct->{struct}->{returns},
133 sql_mode => $sql_modes{lc($struct->{struct}->{sql})} || '',
134 security => uc($struct->{struct}->{security}),
135 mutable => $struct->{struct}->{immutable}? '': 'NOT',
136 code => $struct->{code},
137 });
138 }
139  
140 sub rename
141 {
142 my $self = shift;
143 my ($name, $newname) = @_;
144  
145 my $data = $self->get($name);
146 $self->drop($name);
147 $self->store($newname, $data);
148 }
149  
150 sub list
151 {
152 my $self = shift;
153 return $self->all_col($self->{list_expr});
154 }
155  
156 sub get
157 {
158 my $self = shift;
159 my $name = shift;
160  
161 my $data = $self->one_row($self->{get_expr}, $name);
162 return unless $data;
163  
164 my %sql_modes = (
165 NO_SQL => 'no',
166 CONTAINS_SQL => 'contains',
167 READS_SQL_DATA => 'reads',
168 MODIFIES_SQL_DATA => 'modifies',
169 );
170  
171 my $param_list = $data->{param_list};
172 $param_list =~ s/^\s+//;
173 $param_list =~ s/\s+$//;
174 $param_list =~ s/\s+/ /g;
175  
176 return {
177 struct => $self->dump({
178 sql => $sql_modes{$data->{sql_data_access}},
179 security => lc($data->{security_type}),
180 immutable => $data->{is_deterministic} eq 'YES',
181 parameters => [ split(', ', $param_list) ],
182 returns => $data->{returns},
183 }),
184 code => $data->{body},
185 definer => \"users/$data->{definer}",
186 comment => $data->{comment},
187 };
188 }
189  
190 1;