blib/lib/DBIx/EnumConstraints.pm untested

filter: Statementstmt 0.0% 0 / 80 Branchbran 0.0% 0 / 4 Conditioncond 0.0% 0 / 6 MC/DCmcdc 0.0% 0 / 3 Subroutinesub 0.0% 0 / 7 Podpod 0.0% 0 / 4 totaltotal 0.0% 0 / 104 CC 3 SCAR 13.3
CC
3
Cov
0%
new
6.0
load_fields_from_db
6.0
for_each_kind
2.0
CRAP 3.8
1 use strict;
2 use warnings FATAL => 'all';
3  
4 =head1 NAME
5  
6 DBIx::EnumConstraints - generates enum-like SQL constraints.
7  
8 =head1 SYNOPSIS
9  
10 use DBIx::EnumConstraints;
11  
12 my $ec = DBIx::EnumConstraints->new({
13 table => 'the_table'
14 , name => 'kind', fields => [ [ 'a', 'b' ]
15 , [ 'b' ] ]
16 });
17  
18 =head1 DESCRIPTION
19  
20 This module generates SQL statements for enforcing enum semantics on the
21 database columns.
22  
23 Enum columns is the column which can get one of 1 .. k values. For each of
24 those values there are other columns which should or should not be null.
25  
26 For example in the SYNOPSIS above, when C<kind> column is 1 the row should have
27 both of C<a> and C<b> columns not null. When C<kind> column is 2 the row should
28 have C<a> but no C<b> columns.
29  
30 =cut
31  
32 package DBIx::EnumConstraints;
33 use base 'Class::Accessor';
34 __PACKAGE__->mk_accessors(qw(name fields optionals table));
35  
36 our $VERSION = '0.05';
37  
38 =head1 CONSTRUCTORS
39  
40 =head2 $class->new($args)
41  
42 C<$args> should be HASH reference containing the following parameters:
43  
44 =over
45  
46 =item table
47  
48 The table for which to generate the constraints.
49  
50 =item name
51  
52 The name of the enum.
53  
54 =item fields
55  
56 Array of arrays describing columns dependent on the enum. Each row is index
57 is the possible value of enum minus 1 (e.g. row number 1 is for enum value 2).
58  
59 The items are column names. There is a possibility to mark optional columns by
60 using trailing C<?> (e.g. C<b?> denotes an optional C<b> field.
61  
62 =item column_groups
63  
64 Hash of columns dependent on other columns. E.g. a => [ 'b', 'c' ] means that
65 when C<a> is present C<b>, C<c> columns should be present as well.
66  
67 The key column should be given in C<fields> parameter above.
68  
69 =back
70  
71 =cut
72 sub new {
73 my ($class, $args) = @_;
74 my $self = $class->SUPER::new($args);
75 my $cgs = $args->{column_groups} || {};
76 $self->optionals({});
77 my $i = 1;
78 for my $f (@{ $self->fields || [] }) {
79 my @cfs = map { @{ $cgs->{$_ } || [] } } @$f;
80 push @$f, @cfs;
81 for my $in (@$f) {
82 $self->optionals->{$i}->{$in} = 1 if ($in =~ s/\?$//);
83 }
84 $i++;
85 }
86 return $self;
87 }
88  
89 =head1 METHODS
90  
91 =head2 $self->for_each_kind($callback)
92  
93 Runs C<$callback> over registered enum states. For each state passes state
94 index, fields which are in the state and fields which are out of the state.
95  
96 The fields are passed as ARRAY references.
97  
98 =cut
99 sub for_each_kind {
100 my ($self, $cb) = @_;
101 my $fs = $self->fields;
102 my %all;
103 for my $f (@$fs) {
104 $all{$_} = 1 for @$f;
105 }
106 my $i = 1;
107 for my $f (@$fs) {
108 my %not = %all;
109 delete $not{$_} for @$f;
110 $cb->($i, $f, [ sort keys %not ]);
111 $i++;
112 }
113 }
114  
115 =head2 $self->make_constraints
116  
117 Generates suitable PostgreSQL constraints using the fields.
118  
119 Also generates drop plpgsql function to automate dropping of the constraints.
120  
121 =cut
122 sub make_constraints {
123 my $self = shift;
124 my ($n, $t, $fc) = ($self->name, $self->table, @{ $self->fields } + 1);
125 my (%fins, %fouts);
126 $self->for_each_kind(sub {
127 my ($i, $ins, $outs) = @_;
128 push @{ $fins{$_} }, $i for @$ins;
129 push @{ $fouts{$_} }, $i for @$outs;
130 push @{ $fouts{$_} }, $i for grep {
131 $self->optionals->{$i}->{$_} } @$ins;
132 });
133 my $inconstrs = join("\n", map { sprintf(<<ENDS
134 alter table $t add constraint $t\_$n\_$_\_out_chk check (
135 $_ is null or $n in (%s));
136 ENDS
137 , join(", ", @{ $fins{$_} })) } keys %fins);
138 my $outconstrs = join("\n", map { sprintf(<<ENDS
139 alter table $t add constraint $t\_$n\_$_\_in_chk check (
140 $_ is not null or $n in (%s));
141 ENDS
142 , join(", ", @{ $fouts{$_} })) } keys %fouts);
143 my $incodro = join("\n", map {
144 "alter table $t drop constraint $t\_$n\_$_\_out_chk;"
145 } keys %fins);
146 my $outcodro = join("\n", map {
147 "alter table $t drop constraint $t\_$n\_$_\_in_chk;"
148 } keys %fouts);
149  
150 my $res = <<ENDS
151 create function drop_$t\_$n\_constraints() returns void as \$\$
152 begin
153 alter table $t drop constraint $t\_$n\_size_chk;
154 $incodro
155 $outcodro
156 drop function drop_$t\_$n\_constraints();
157 end;
158 \$\$ language plpgsql;
159 alter table $t add constraint $t\_$n\_size_chk check ($n > 0 and $n < $fc);
160 $inconstrs
161 $outconstrs
162 ENDS
163 }
164  
165 =head2 $self->load_fields_from_db($dbh)
166  
167 Loads fields configuration from the database using current constraints.
168  
169 =cut
170 sub load_fields_from_db {
171 my ($self, $dbh) = @_;
172 my ($t, $n) = ($self->table, $self->name);
173 my $arr = $dbh->selectcol_arrayref(<<ENDS);
174 select check_clause from information_schema.check_constraints
175 where constraint_name = '$t\_$n\_size_chk'
176 ENDS
177 my ($upto) = ($arr->[0] =~ /< (\d+)/);
178  
179 $arr = $dbh->selectall_arrayref(<<ENDS);
180 select constraint_name, check_clause from information_schema.check_constraints
181 where constraint_name like '$t\_$n\_%_out_chk'
182 ENDS
183 my @fields = map { [] } (1 .. ($upto - 1));
184 for my $a (@$arr) {
185 my ($c) = ($a->[0] =~ /$t\_$n\_(\w+)_out_chk$/);
186 my @no = ($a->[1] =~ /$n = (\d+)/);
187 if (!@no) {
188 $a->[1] =~ /$n = .*\[([\d, ]+)/;
189 @no = split(', ', $1);
190 }
191 push(@{ $fields[ $_ - 1 ] }, $c) for @no;
192 }
193 $self->fields(\@fields);
194 }
195  
196 1;
197  
198 =head1 AUTHOR
199  
200 Boris Sukholitko
201 CPAN ID: BOSU
202
203 boriss@gmail.com
204
205 =head1 COPYRIGHT
206  
207 This program is free software; you can redistribute
208 it and/or modify it under the same terms as Perl itself.
209  
210 The full text of the license can be found in the
211 LICENSE file included with this module.
212  
213 =cut
214