| 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__ |