FusqlFS/Backend/PgSQL/Functions.pm untested

filter: Statementstmt 0.0% 0 / 81 Branchbran 0.0% 0 / 14 Conditioncond 0.0% 0 / 7 MC/DCmcdc 0.0% 0 / 4 Subroutinesub 0.0% 0 / 13 Podpod 0.0% 0 / 7 totaltotal 0.0% 0 / 126 CC 8 SCAR 19.3
CC
8
Cov
0%
get
12.0
store
12.0
rename
12.0
CRAP 6.9
1 use strict;
2 use 5.010;
3  
4 package FusqlFS::Backend::PgSQL::Functions;
5 use FusqlFS::Version;
6 our $VERSION = $FusqlFS::Version::VERSION;
7 use parent 'FusqlFS::Artifact';
8  
9 =head1 NAME
10  
11 FusqlFS::Backend::PgSQL::Functions - FusqlFS PostgreSQL database functions
12 (a.k.a. stored procedures) interface
13  
14 =head1 SYNOPSIS
15  
16 use FusqlFS::Backend::PgSQL::Functions;
17  
18 my $funcs = FusqlFS::Backend::PgSQL::Functions->new();
19  
20 =head1 DESCRIPTION
21  
22 This is FusqlFS an interface to PostgreSQL database functions (or stored
23 procedures as they are called in "big DBMS world"). This class is not to be
24 used by itself.
25  
26 See L<FusqlFS::Artifact> for description of interface methods,
27 L<FusqlFS::Backend> to learn more on backend initialization and
28 L<FusqlFS::Backend::Base> for more info on database backends writing.
29  
30 =head1 EXPOSED STRUCTURE
31  
32 =over
33  
34 =item F<./language>
35  
36 Symlink to language used to write this function in F<../../languages>.
37 See L<FusqlFS::Backend::PgSQL::Languages> for details.
38  
39 =item F<./content.*>
40  
41 This file have suffix equal to language name used to write this function and
42 contains this function's body. You can edit it in order to change function's
43 definition.
44  
45 =item F<./owner>
46  
47 Symlink to sequence's owner in F<../../roles>.
48  
49 =item F<./acl>
50  
51 Functions's ACL with permissions given to different roles. See
52 L<FusqlFS::Backend::PgSQL::Role::Acl> for details.
53  
54 =back
55  
56 =cut
57  
58 use FusqlFS::Backend::PgSQL::Role::Owner;
59 use FusqlFS::Backend::PgSQL::Role::Acl;
60  
61 sub init
62 {
63 my $self = shift;
64  
65 my $pgver = $self->dbh->{pg_server_version};
66 my $get_func_args = 'pg_catalog.pg_get_function_arguments(p.oid)';
67 my $get_func_res = 'pg_catalog.pg_get_function_result(p.oid)';
68  
69 if ($pgver < 80400)
70 {
71 $get_func_args = q{CASE WHEN p.proallargtypes IS NOT NULL THEN
72 pg_catalog.array_to_string(ARRAY(
73 SELECT
74 CASE
75 WHEN p.proargmodes[s.i] = 'i' THEN ''
76 WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
77 WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
78 WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '
79 END ||
80 CASE
81 WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
82 ELSE p.proargnames[s.i] || ' '
83 END ||
84 pg_catalog.format_type(p.proallargtypes[s.i], NULL)
85 FROM
86 pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)
87 ), ', ')
88 ELSE
89 pg_catalog.array_to_string(ARRAY(
90 SELECT
91 CASE
92 WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''
93 ELSE p.proargnames[s.i+1] || ' '
94 END ||
95 pg_catalog.format_type(p.proargtypes[s.i], NULL)
96 FROM
97 pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
98 ), ', ')
99 END};
100 $get_func_res = 'pg_catalog.format_type(p.prorettype, NULL)';
101 }
102  
103 $self->{list_expr} = $self->expr("SELECT DISTINCT p.proname||'('||$get_func_args||')' FROM pg_catalog.pg_proc AS p
104 LEFT JOIN pg_catalog.pg_namespace AS ns ON ns.oid = p.pronamespace
105 WHERE ns.nspname = 'public'");
106  
107 #CASE WHEN p.proisagg THEN NULL ELSE pg_catalog.pg_get_functiondef(p.oid) END AS struct
108 $self->{get_expr} = $self->expr("SELECT $get_func_res AS result,
109 trim(both from p.prosrc) AS content, l.lanname AS lang,
110 CASE p.provolatile WHEN 'i' THEN 'immutable' WHEN 's' THEN 'stable' WHEN 'v' THEN 'volatile' END AS volatility,
111 CASE WHEN p.proisagg THEN 'aggregate' WHEN p.proiswindow THEN 'window' WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' ELSE 'normal' END AS type
112 FROM pg_catalog.pg_proc AS p
113 LEFT JOIN pg_catalog.pg_language AS l ON l.oid = p.prolang
114 WHERE p.proname = ? AND $get_func_args = ?");
115  
116 $self->{create_expr} = 'CREATE OR REPLACE FUNCTION %s RETURNS integer LANGUAGE sql AS $function$ SELECT 1; $function$';
117 $self->{rename_expr} = 'ALTER FUNCTION %s RENAME TO %s';
118 $self->{drop_expr} = 'DROP FUNCTION %s';
119 $self->{store_expr} = 'CREATE OR REPLACE FUNCTION %s RETURNS %s LANGUAGE %s AS $function$ %s $function$ %s';
120  
121 $self->{owner} = FusqlFS::Backend::PgSQL::Role::Owner->new('_F');
122 $self->{acl} = FusqlFS::Backend::PgSQL::Role::Acl->new('_F');
123 }
124  
125 =begin testing get
126  
127 my $row = $_tobj->get('xxxxx');
128 is $row, undef, '->get() result is sane';
129  
130 =end testing
131 =cut
132 sub get
133 {
134 my $self = shift;
135 my ($name, $args) = split(/\(/, $_[0], 2);
136 return unless $args;
137 $args =~ s/\)$//;
138 my $data = $self->one_row($self->{get_expr}, $name, $args);
139 return unless $data;
140  
141 my $result = {};
142 $result->{language} = \"languages/$data->{lang}";
143 $result->{'content.'.delete($data->{lang})} = delete($data->{content});
144 $result->{struct} = $self->dump($data);
145  
146 $result->{owner} = $self->{owner};
147 $result->{acl} = $self->{acl};
148  
149 return $result;
150 }
151  
152 =begin testing list
153  
154 my $list = $_tobj->list();
155 isa_ok $list, 'ARRAY', '->list() result is an array';
156 cmp_ok scalar(@$list), '==', 0, '->list() result is empty';
157  
158 =end testing
159 =cut
160 sub list
161 {
162 my $self = shift;
163 return $self->all_col($self->{list_expr});
164 }
165  
166 =begin testing drop after store
167  
168 is $_tobj->drop('fusqlfs_func(integer)'), undef;
169 isnt $_tobj->drop('fusqlfs_func(integer, integer)'), undef;
170 is_deeply $_tobj->list(), [];
171 is $_tobj->get('fusqlfs_func(integer, integer)'), undef;
172  
173 =end testing
174 =cut
175 sub drop
176 {
177 my $self = shift;
178 my ($name) = @_;
179 $self->do($self->{drop_expr}, [$name]);
180 }
181  
182 =begin testing store after rename
183  
184 isnt $_tobj->store('fusqlfs_func(integer, integer)', $new_func), undef;
185 is_deeply $_tobj->get('fusqlfs_func(integer, integer)'), $new_func;
186  
187 =end testing
188 =cut
189 sub store
190 {
191 my $self = shift;
192 my ($name, $data) = @_;
193 return unless $data;
194  
195 my $struct = $self->validate($data, {
196 struct => {
197 volatility => qr/^(volatile|immutable|stable)$/i,
198 type => qr/^(window|trigger|normal)$/i,
199 result => '',
200 },
201 language => ['SCALAR', sub{ $$_ =~ /^languages\/(.+)$/ && $1 }],
202 }, sub{ $_->{content} = $_[0]->{'content.'.$_->{language}}||undef })
203 or return;
204  
205 my $opts = ' ';
206 $opts .= 'WINDOW ' if $struct->{struct}->{type} eq 'window';
207 $opts .= uc $struct->{struct}->{volatility};
208  
209 $self->do($self->{store_expr}, [$name, $struct->{struct}->{result}, $struct->{language}, $struct->{content}, $opts]);
210 }
211  
212 =begin testing rename after create
213  
214 isnt $_tobj->rename('fusqlfs_func(integer)', 'fusqlfs_func(integer, integer)'), undef;
215 is_deeply $_tobj->get('fusqlfs_func(integer, integer)', $created_func), $created_func;
216 is $_tobj->get('fusqlfs_func(integer)'), undef;
217 is_deeply $_tobj->list(), [ 'fusqlfs_func(integer, integer)' ];
218  
219 =end testing
220 =cut
221 sub rename
222 {
223 my $self = shift;
224 my ($name, $newname) = @_;
225 return if $name eq $newname;
226  
227 my ($fname, $fargs) = split /\(/, $newname, 2;
228 my ($aname, $aargs) = split /\(/, $name, 2;
229 if ($fargs eq $aargs)
230 {
231 $self->do($self->{rename_expr}, [$name, $fname]);
232 }
233 else
234 {
235 my $data = $self->get($name);
236 $self->drop($name);
237 $self->store($newname, $data);
238 }
239 }
240  
241 =begin testing create after get list
242  
243 isnt $_tobj->create('fusqlfs_func(integer)'), undef;
244 is_deeply $_tobj->list(), [ 'fusqlfs_func(integer)' ];
245 is_deeply $_tobj->get('fusqlfs_func(integer)'), $created_func;
246  
247 =end testing
248 =cut
249 sub create
250 {
251 my $self = shift;
252 my ($name) = @_;
253 $self->do($self->{create_expr}, [$name]);
254 }
255  
256 1;
257  
258 __END__