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