package LATMOS::Accounts::Bases::Sql::objects; use 5.010000; use strict; use warnings; use base qw(LATMOS::Accounts::Bases::Objects); use LATMOS::Accounts::Log; use Crypt::RSA; use Crypt::RSA::Key::Public::SSH; use DateTime; our $VERSION = (q$Rev$ =~ /^Rev: (\d+) /)[0]; =head1 NAME LATMOS::Accounts::Bases::Sql::objects - Parent class for SQL object =cut sub _attributes_table { $_[0]->_object_table . '_attributes_list' } sub list { my ($class, $base) = @_; my $sth = $base->db->prepare_cached( sprintf( q{select %s as k from %s %s order by %s}, $base->db->quote_identifier($class->_key_field), $base->db->quote_identifier($class->_object_table), ($base->{wexported} ? '' : 'where exported = true'), $base->db->quote_identifier($class->_key_field), ) ); $sth->execute; my @keys; while(my $res = $sth->fetchrow_hashref) { push(@keys, $res->{k}); } @keys } sub list_from_rev { my ($class, $base, $rev) = @_; my $sth = $base->db->prepare_cached( sprintf( q{select %s as k from %s where rev > ? %s order by %s}, $base->db->quote_identifier($class->_key_field), $base->db->quote_identifier($class->_object_table), ($base->{wexported} ? '' : 'and exported = true'), $base->db->quote_identifier($class->_key_field), ) ); $sth->execute($rev); my @keys; while(my $res = $sth->fetchrow_hashref) { push(@keys, $res->{k}); } @keys } sub _has_extended_attributes { 0 } sub _get_attr_schema { my ($class, $base, $info) = @_; $info ||= {}; if (!$base->{__cache}{$class->_object_table}{inline}) { $base->{__cache}{$class->_object_table}{inline} = []; my $sth = $base->db->prepare( q{SELECT column_name FROM information_schema.columns WHERE table_name = ?} ); $sth->execute($class->_object_table); while (my $res = $sth->fetchrow_hashref) { push(@{$base->{__cache}{$class->_object_table}{inline}}, $res->{column_name}); } } foreach (@{$base->{__cache}{$class->_object_table}{inline}}) { $info->{$_}{inline} = 1; if (m/^(rev|date|create|ikey)$/) { $info->{$_}{ro} = 1 } } # Common to all object attribute: my %commons = ( name => { inline => 1, ro => 1, }, create => { inline => 1, ro => 1, }, date => { inline => 1, ro => 1, }, exported => { inline => 1, formtype => 'CHECKBOX', hide => 1, monitored => 1 }, unexported => { inline => 1, managed => 1, formtype => 'CHECKBOX', get => sub { my ($self) = @_; return $self->object->get_field('exported') ? undef : 1; }, set => sub { my ($self, $data) = @_; $self->object->_set_c_fields('exported', $data ? 'false' : 'true'); }, }, services => { managed => 1, multiple => 1, reference => 'service' }, ); # Merging / overriding with common to all object attributes properties foreach my $attr (keys %commons) { foreach my $var (keys %{ $commons{$attr} }) { $info->{$attr}{$var} = $commons{$attr}{$var}; } } # TODO kill this code: useless since everything is declared in perl code if ($class->_has_extended_attributes) { if (!$base->{__cache}{$class->_object_table}{extend}) { $base->{__cache}{$class->_object_table}{extend} = []; my $sth = $base->db->prepare_cached( sprintf( q{select canonical from %s order by canonical}, $base->db->quote_identifier($class->_attributes_table), ) ); $sth->execute; while (my $res = $sth->fetchrow_hashref) { push(@{$base->{__cache}{$class->_object_table}{extend}}, $res->{canonical}); } } foreach (@{$base->{__cache}{$class->_object_table}{extend}}) { $base->log(LA_ERR, 'Attribute %s for %s not declared in code', $_, $class->type) if(!exists($info->{$_})); $info->{$_} ||= {}; } } $info } # Everything managed by the perl code sub _managed_fields { my ($class, $for, $base) = @_; return(); } sub new { my ($class, $base, $id) = @_; my $sth = $base->db->prepare_cached( sprintf(q{select 1 from %s where %s = ? %s}, $base->db->quote_identifier($class->_object_table), $base->db->quote_identifier($class->_key_field), ($base->{wexported} ? '' : 'and exported = true'), ), ); my $count = $sth->execute($id); $sth->finish; ($count || 0) == 1 or return; $class->SUPER::new($base, $id); } sub Iid { $_[0]->_get_ikey } sub _get_ikey { my ($class, $base, $id) = @_; $base ||= $class->base; $id ||= $class->id; my $sth = $base->db->prepare_cached( sprintf( q{select ikey from %s where %s = ?}, $base->db->quote_identifier($class->_object_table), $base->db->quote_identifier($class->_key_field), ) ); $sth->execute($id); my $res = $sth->fetchrow_hashref; $sth->finish; $res->{ikey} } sub _create { my ($class, $base, $id, %data) = @_; # splitting inline from extended my (%first, %second); # Ensure object is exported if not specified $data{exported} = 1 if (!exists($data{exported})); if (exists $data{unexported}) { $data{exported} = $data{unexported} ? 0 : 1; delete $data{unexported} } foreach (keys %data) { my $attr = $base->attribute($class->type, $_) or next; $_ =~ /^exported$/ and $data{$_} = $data{$_} ? 1 : 0; my $formatted = ref($data{$_}) ? [ map { $attr->input($_) } @{ $data{$_} } ] : $attr->input($data{$_}); if ($attr->{inline} && ! $attr->{delayed}) { $first{$_} = $formatted; } else { $second{$_} = $formatted; } } $first{$class->_key_field} = $id; my $lastid; { my $sthnextval = $base->db->prepare_cached("select nextval('ikey_seq') as c"); $sthnextval->execute; $lastid = $sthnextval->fetchrow_hashref()->{c}; $first{ikey} = $lastid; $sthnextval->finish; } my $sth = $base->db->prepare( sprintf( q{insert into %s (%s) values (%s)}, $base->db->quote_identifier($class->_object_table), join(', ', map { $base->db->quote_identifier($_) } sort keys %first), join(',', qw(?) x scalar(keys %first)), ) ); $sth->execute(map { $first{$_} || undef } sort keys %first) or return; my $sthid = $base->db->prepare_cached( sprintf(q{select %s as k from %s where ikey = ?}, $base->db->quote_identifier($class->_key_field), $base->db->quote_identifier($class->_object_table), ) ); $sthid->execute($lastid); my $res = $sthid->fetchrow_hashref() or return; my $obj = $class->new($base, $res->{k}) or return; $obj->set_fields(%second); return $res->{k}; } sub _delete { my ($class, $base, $id) = @_; my $obj = $base->get_object($class->type, $id) or return; my $sthd = $base->db->prepare_cached( sprintf( q{delete from %s where %s = ?}, $base->db->quote_identifier($class->_object_table), $base->db->quote_identifier($class->_key_field), ) ); $sthd->execute($id); } sub _rename { my ($class, $base, $id, $newid) = @_; my $sthr = $base->db->prepare_cached( sprintf( q{update %s set %s = ? where %s = ?}, $base->db->quote_identifier($class->_object_table), $base->db->quote_identifier($class->_key_field), $base->db->quote_identifier($class->_key_field), ) ); if (($sthr->execute($newid, $id) || 0) != 1) { $base->log(LA_ERR, "Erreur renaming %s %s to %s", $class->type, $id, $newid, ); return; } 1; } =head2 db Return reference to L object. =cut sub db { return $_[0]->base->db; } sub _quote_object_table { my ($self) = @_; my $table = $self->_object_table or return; $self->db->quote_identifier($table); } sub _quote_key_field { my ($self) = @_; my $key_field = $self->_key_field or return; $self->db->quote_identifier($key_field); } sub get_field { my ($self, $field) = @_; if ($field eq 'services') { my @services; my $sth = $self->db->prepare_cached( q{ select name from service join service_attributes on okey = ikey where service_attributes.attr = 'dependOn' and value = ? }); $sth->execute($self->type . '.' . $self->id); while(my $res = $sth->fetchrow_hashref) { push(@services, $res->{name}); } return \@services; } my $attr = $self->attribute($field) or return; if ($attr->{inline}) { my $sth = $self->db->prepare_cached( sprintf( q{select %s from %s where %s = ?}, $self->db->quote_identifier(lc($field)), $self->_quote_object_table, $self->_quote_key_field, ) ); $sth->execute($self->id); my $res = $sth->fetchrow_hashref; $sth->finish; return $res->{$field}; } elsif ($self->_has_extended_attributes) { # else, then we mandatory have extend attr $self->base->{__cache}{"_" . $self->type} ||= {}; my $__cache = $self->base->{__cache}{"_" . $self->type}; if (!(exists($__cache->{$self->id}) && $__cache->{$self->id}{__time} >= time - 1)) { my $sth = $self->db->prepare_cached( sprintf( q{ select attr, value from %s join %s on okey = ikey where %s = ? }, $self->db->quote_identifier($self->_object_table. '_attributes'), $self->db->quote_identifier($self->_object_table), $self->db->quote_identifier($self->_key_field), ) ); $sth->execute($self->id); delete($__cache->{$self->id}); $__cache->{$self->id}{__time} = time; while(my $res = $sth->fetchrow_hashref) { push(@{$__cache->{$self->id}{$res->{attr}}}, $res->{value}); } #return @values > 1 ? \@values : $values[0]; } my $val = $__cache->{$self->id}{$field}; return @{$val || []} > 1 ? $val : $val->[0]; } } sub set_fields { my ($self, %data) = @_; my @updated_attributes = (); my @fields; my @vals; my %ext; if (exists($data{services})) { my %old = map { $_ => 0 } $self->get_attributes('services'); foreach my $serv (grep { $_ } ref $data{services} ? @{ $data{services} } : $data{services}) { if (!exists($old{$serv})) { my $oserv = $self->base->get_object('service', $serv) or next; $oserv->addAttributeValue('dependOn', $self->type . '.' . $self->id); } $old{$serv} = 1; } foreach my $serv (keys %old) { if (!$old{$serv}) { my $oserv = $self->base->get_object('service', $serv) or next; $oserv->delAttributeValue('dependOn', $self->type . '.' . $self->id); } } delete($data{services}); } if (exists($data{services})) { my %old = map { $_ => 0 } $self->get_attributes('services'); foreach my $serv (grep { $_ } ref $data{services} ? @{ $data{services} } : $data{services}) { if (!exists($old{$serv})) { my $oserv = $self->base->get_object('service', $serv) or next; $oserv->addAttributeValue('dependOn', $self->type . '.' . $self->id); } $old{$serv} = 1; } foreach my $serv (keys %old) { if (!$old{$serv}) { my $oserv = $self->base->get_object('service', $serv) or next; $oserv->delAttributeValue('dependOn', $self->type . '.' . $self->id); } } delete($data{services}); } foreach my $field (keys %data) { my $attr = $self->attribute($field); my $oldval = $self->get_field($field); next if (($data{$field} || '') eq ($oldval || '')); if ($attr->{inline}) { # TODO check fields exists ! push(@fields, sprintf("%s = ?", $self->db->quote_identifier($field))); push(@vals, $data{$field}); push(@updated_attributes, $field); } else { $ext{$field} = $data{$field}; } } if (@fields) { my $sth = $self->db->prepare_cached( sprintf( q{update %s set %s where %s = ?}, $self->_quote_object_table, join(', ', @fields), $self->_quote_key_field, ) ); $sth->execute(@vals, $self->id) or do { $self->base->log(LA_ERR, "Cannot update inline field for object %s, %s: %s", $self->type, $self->id, $self->base->db->errstr); return; }; } if ($self->_has_extended_attributes) { my $sthd = $self->db->prepare_cached( sprintf( q{delete from %s where okey = ? and attr = ?}, $self->db->quote_identifier($self->_object_table. '_attributes'), ), ); my $sthd1 = $self->db->prepare_cached( sprintf( q{delete from %s where okey = ? and attr = ? and value = ?}, $self->db->quote_identifier($self->_object_table. '_attributes'), ), ); my $sthx = $self->db->prepare_cached( sprintf( q{insert into %s (okey, attr, value) values (?,?,?)}, $self->db->quote_identifier($self->_object_table. '_attributes'), ) ); my $sthu = $self->db->prepare_cached( sprintf( q{update %s set value = ? where okey = ? and attr = ?}, $self->db->quote_identifier($self->_object_table. '_attributes'), ) ); my $okey = $self->_get_ikey($self->base, $self->id); foreach my $uattr (keys %ext) { my $attr = $self->attribute($uattr); if ($ext{$uattr}) { if ($attr->{multiple}) { my $updated = 0; my $oldvalue = $self->get_field($uattr); my %newvalues = map { $_ => 1 } (ref $ext{$uattr} ? @{$ext{$uattr}} : $ext{$uattr}); foreach (grep { $_ } ref $oldvalue ? @{$oldvalue} : $oldvalue) { if(exists($newvalues{$_})) { $newvalues{$_} = 0; } else { defined($sthd1->execute($okey, $uattr, $_)) or do { $self->base->log(LA_ERR, "Error while updating attributes on %s/%s %s: %s", $self->type, $self->id, $uattr, $self->base->db->errstr ); return; }; $updated++; } } foreach (grep { $newvalues{$_} } keys %newvalues) { $sthx->execute($okey, $uattr, $_) or do { $self->base->log(LA_ERR, "Error while updating attributes: %s/%s %s: %s", $self->type, $self->id, $uattr, $self->base->db->errstr ); return; }; $updated++; } push(@updated_attributes, $uattr) if ($updated); } else { my $res = $sthu->execute($ext{$uattr}, $okey, $uattr); defined($res) or do { $self->base->log(LA_ERR, "Error while udapting attributes: %s/%s %s: %s", $self->type, $self->id, $uattr, $self->base->db->errstr ); return; }; if ($res == 0) { $res = $sthx->execute($okey, $uattr, $ext{$uattr}); defined($res) or do { $self->base->log(LA_ERR, "Error while updating attributes: %s/%s %s: %s", $self->type, $self->id, $uattr, $self->base->db->errstr ); return; }; } push(@updated_attributes, $uattr); } } else { defined($sthd->execute($okey, $uattr)) or do { $self->base->log(LA_ERR, "Error while deleting attributes: %s/%s %s: %s", $self->otype, $self->id, $uattr, $self->base->db->errstr ); return; }; push(@updated_attributes, $uattr); } } } delete($self->base->{__cache}{"_" . $self->type}{$self->id}); scalar(@updated_attributes); } sub attributes_summary { my ($class, $base, $attribute) = @_; my $attr = $base->attribute($class->type, $attribute); if ($attr->{managed}) { return $class->SUPER::attributes_summary($base, $attribute); } my $sth = $base->db->prepare_cached( $attr->{inline} ? sprintf( q{select %s as value from %s}, $base->db->quote_identifier($attr->iname), $base->db->quote_identifier($class->_object_table), ) : sprintf( q{select value from %s where attr = ? group by value}, $base->db->quote_identifier($class->_object_table . '_attributes'), ) ); $sth->execute($attr->{inline} ? () : ($attribute)); my @values; while (my $res = $sth->fetchrow_hashref) { push(@values, $res->{value}); } @values } sub _set_password { my ($self, $clear_pass) = @_; if (my $attr = $self->base->attribute($self->type, 'userPassword')) { my $field = $attr->iname; my @salt_char = (('a' .. 'z'), ('A' .. 'Z'), (0 .. 9), '/', '.'); my $salt = join('', map { $salt_char[rand(scalar(@salt_char))] } (1 .. 8)); my $res = $self->set_fields($field, crypt($clear_pass, '$1$' . $salt)); if ($res) { if ($self->base->get_global_value('rsa_public_key')) { $self->setCryptPassword($clear_pass) or return; } } $self->set_fields('passwordLastSet', DateTime->now->datetime); $self->base->log(LA_NOTICE, 'Mot de passe changé pour %s', $self->id ); return $res; } else { $self->log(LA_WARN, "Cannot set password: userPassword attributes is unsupported"); } } =head2 setCryptPassword($clear_pass) Store password encrypted using RSA encryption. =cut sub setCryptPassword { my ($self, $clear_pass) = @_; if (my $serialize = $self->base->get_global_value('rsa_public_key')) { my $public = Crypt::RSA::Key::Public->new; $public = $public->deserialize(String => [ $serialize ]); my $rsa = new Crypt::RSA ES => 'PKCS1v15'; my $rsa_password = $rsa->encrypt ( Message => $clear_pass, Key => $public, Armour => 1, ) || die $rsa->errstr(); if (!$self->_set_c_fields('encryptedPassword', $rsa_password)) { $self->log(LA_ERR, "Cannot set 'encryptedPassword' attribute for object %s/%s", $self->type, $self->id, ); return; } } $self->ReportChange('Password', 'Password stored using internal key'); return 1; } sub search { my ($class, $base, @filter) = @_; my %attrsql; my %attrbind; while (my $item = shift(@filter)) { # attr=foo => no extra white space ! # \W is false, it is possible to have two char my ($attr, $mode, $val) = $item =~ /^(\w+)(?:(\W)(.+))?$/ or next; if (!$mode) { $mode = '~'; $val = shift(@filter); } my $attribute = $base->attribute($class->type, $attr) or do { $base->log(LA_ERR, "Unknown attribute $attr"); return; }; defined($val) or $val = ''; # Invalid filter due to impossible value: $attribute->checkinput($val) or do { $base->log(LA_ERR, "Invalid value $val for attribute $attr"); return; }; $val = $attribute->input($val); my $sql; # Specific case for unexported attribute, comming from exported value if ($attribute->iname eq 'unexported') { $sql = sprintf( q{select ikey from %s where %s}, $base->db->quote_identifier($class->_object_table), $val ? q{exported='f'} : q{exported='t'} ) } elsif ($attribute->{inline}) { $sql = sprintf( q{select ikey from %s where %s %s}, $base->db->quote_identifier($class->_object_table), $base->db->quote_identifier($attribute->iname), $val eq '*' ? 'is not NULL' : $mode eq '~' ? 'ILIKE ?' : '= ?' ); push(@{$attrbind{$attr}}, $mode eq '~' ? '%' . $val . '%' : $val) unless($val eq '*'); } else { $sql = sprintf( q{select okey from %s where attr = ? %s}, $base->db->quote_identifier( $class->_object_table . '_attributes' ), $val eq '*' ? '' : $mode eq '~' ? q{and value ILIKE ?} : q{and value = ?} ); push(@{$attrbind{$attr}}, $attribute->iname); push(@{$attrbind{$attr}}, $mode eq '~' ? '%' . $val . '%' : $val) unless($val eq '*'); } push(@{ $attrsql{$attr} }, $sql); } # building the query my @sqlintersec; if (!$base->{wexported}) { push(@sqlintersec, sprintf( q{select ikey from %s where exported = true}, $base->db->quote_identifier($class->_object_table) ) ); } my @bind; foreach (keys %attrsql) { push(@sqlintersec, '(' . join(" union ", @{$attrsql{$_}}) . ")\n"); push(@bind, @{$attrbind{$_} || []}); } my $sth = $base->db->prepare( sprintf(q{ select name from %s %s order by name }, $base->db->quote_identifier($class->_object_table), @sqlintersec ? "where ikey in (\n" . join("\n intersect\n", @sqlintersec) . ")\n" : '', ) ); $sth->execute(@bind); my @results; while (my $res = $sth->fetchrow_hashref) { push(@results, $res->{name}); } return(@results); } =head2 register_attribute Register attribute into base =cut sub register_attribute { my ($class, $base, $attribute, $comment) = @_; $class->is_registered_attribute($base, $attribute) and do { $base->log(LA_ERR, "The attribute $attribute already exists"); return; }; my $sth = $base->db->prepare( sprintf(q{ insert into %s (canonical, description) values (?,?) }, $class->_attributes_table) ); my $res = $sth->execute($attribute, $comment); } =head2 is_registered_attribute ($base, $attribute) Return true is attribute is registered into base =cut sub is_registered_attribute { my ($class, $base, $attribute) = @_; my $sth = $base->db->prepare( sprintf(q{ select 1 from %s where canonical = ? }, $class->_attributes_table ) ); $sth->execute($attribute); my $res = $sth->fetchrow_hashref; return $res ? 1 : 0; } =head2 get_attribute_comment $base, $attribute) Return comment for C<$attribute> =cut # TODO: get_attribute_comment($attr, $base) { $base ||= $self->base ... sub get_attribute_comment { my ($class, $base, $attribute) = @_; $base->attribute($class->type, $attribute) or do { $base->log(LA_ERR, "The attribute $attribute does not exists"); return; }; my $sth = $base->db->prepare( sprintf(q{ select description from %s where canonical = ? }, $class->_attributes_table) ); $sth->execute($attribute); if (my $res = $sth->fetchrow_hashref) { $sth->finish; return $res->{description}; } else { return; } } =head2 set_attribute_comment ($base, $attribute, $comment) Set comment to attribute =cut sub set_attribute_comment { my ($class, $base, $attribute, $comment) = @_; my $attr = $base->attribute($class->type, $attribute) or do { $base->log(LA_ERR, "The attribute $attribute does not exists"); return; }; $attr->{inline} and do { $base->log(LA_ERR, "Cannot set comment to inline attribute, sorry, blame the author !" ); return; }; my $sth = $base->db->prepare( sprintf(q{ update %s set description = ? where canonical = ? }, $class->_attributes_table) ); my $res = $sth->execute($comment, $attribute); } 1; __END__ =head1 SEE ALSO L L =head1 AUTHOR Olivier Thauvin, Eolivier.thauvin@latmos.ipsl.frE =head1 COPYRIGHT AND LICENSE Copyright (C) 2008, 2009 CNRS SA/CETP/LATMOS This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.10.0 or, at your option, any later version of Perl 5 you may have available. =cut