More Identity Moose

Its still identify day here in the Moose-Pen

Yesterday I made a good start with my 'identity' flag and today I carry on with this using this test;


{
caption => 'use identity option with exe array',
key => 'elements',
elements => [
{ name => 'id',
identity =>{'DBI::db'=>{DBM => {
name => 'NEXTVAL',
view => 'products_seq'}
}}
},
{ name => 'first_name', },
{ name => 'last_name', },
],
create => {
container => [
{first_name=>'Bill',id=>"",last_name =>'Bloggings'},
{first_name=>'Jane',id=>"",last_name =>'Doe'},
{first_name=>'John',id=>"",last_name =>'Doe'},
{first_name=>'Joe' ,id=>"",last_name =>'Blow'},
],
sql =>
"INSERT INTO Products ( first_name, id, last_name ) VALUES( ?, products_seq.NEXTVAL, ? )",
params => [
['Bill','Jane','John','Joe'],
['Bloggings','Doe','Doe','Blow'],
]
},

},


This time I am going to be using the 'array interface' which is the second half of the logic '_insert_update_container' sub.

The first thing I noticed was that I could re-factor this code stub;


if ($field->identity){
my $identity = $field->identity();
if (exists($identity->{$self->DB_Class}->{$self->dbh()->{Driver}->{Name}})){
my $new_field;
my $identity_element = $identity->{$self->DB_Class}->{$self->dbh()->{Driver}->{Name}};
if (exists($identity_element->{name})) {
$new_field = Database::Accessor::Element->new($identity_element);
$self->add_param($new_field);
}
$self->has_identity(1);
}
}

into this function;

sub _handle_identity {
my $self = shift;
my ($field) = @_;
my $identity = $field->identity();

if (
exists(
$identity->{ $self->DB_Class }->{ $self->dbh()->{Driver}->{Name} }
)
)
{
my $new_field;
my $identity_element =
$identity->{ $self->DB_Class }->{ $self->dbh()->{Driver}->{Name} };
if ( exists( $identity_element->{name} ) ) {
$new_field = Database::Accessor::Element->new($identity_element);
$self->add_param($new_field);
}
$self->has_identity(1);
}

}


and then add it into the first part of the if and take the stub out and replace it with the function call in then second part of the if statement

if ( ref($container) eq "ARRAY" ) {
my @fields = ();
$self->is_exe_array(1);
my $fields = $container->[0];
foreach my $key ( sort( keys( %{$fields} ) ) ) {
my $field = $self->get_element_by_name($key);
next
if ( !$field );
push( @fields, $field );
if ( $action eq Database::Accessor::Constants::UPDATE ) {
...
else {
++ if ( $field->identity ) {
++ $self->_handle_identity($field);
++ }
++ else {
push( @field_sql, $self->_field_sql($field) );
++ }

else {
foreach my $key ( sort( keys( %{$container} ) ) ) {
my $field = $self->get_element_by_name($key);
next
if ( !$field );


else {
if ( $field->identity ) {
++ $self->_handle_identity($field);
}


Before I get to far along I am going to re-run my test to make sure the re-factor did not break anything;

...
ok 11 - use identity option create SQL correct
ok 12 - use identity option create params correct


That is good it is still working;

Then next fix I have to add in is how I assemble my 'tuples' like this


foreach my $tuple ( @{$container} ) {
my $index = 0;
foreach my $field (@fields) {
++ if ($field->identity() and $self->has_identity()) {
++ $index++;
++ next;
++ }
my $param = Database::Accessor::Param->new(
{ value => $tuple->{ $field->name() } } );
push( @{ $self->params->[$index] }, $param );
$index++;
}
}

I check the field to see if it is an identity and the 'has_identity' flag is set and if that is so I bump the $index by one and then skip to the next tuple;

Finally I will have to account for that 'Database::Accessor::Element' in my params attribute and like yesterday


...
if ( $self->is_exe_array() ) {
my $params = $self->params();
foreach my $tuple ( @{$params} ) {
++ next
++ if (ref($tuple) eq "Database::Accessor::Element");
if ( ref($tuple) eq "ARRAY" ) {


Unfortunately even after all these changes I was still getting this;

INSERT INTO Products ( first_name, last_name ) VALUES( ?,?,?,?, products_seq.NEXTVAL, ?,?,?,? )

as the generated SQL so something is awry in my code;

After a good deal of debugging I traced the extra '?' back to my map call here


my $values_clause = Database::Accessor::Driver::DBI::SQL::VALUES
. join(
" ",
Database::Accessor::Driver::DBI::SQL::OPEN_PARENS,
join(
", ",
map( {
ref($_) eq 'Database::Accessor::Param'
++ or ref($_) eq 'ARRAY'
? Database::Accessor::Driver::DBI::SQL::PARAM
: $self->_field_sql( $_, 1 )
} @{ $self->params() } )
),
Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS
);

where I had to add in a little code to account for the case when my 'param' my be an Array-ref. On the next run I still get this on my generated SQL;

Products ( first_name, last_name )

so I am missing that 'id' in there;

The fix for that one was simple enough, seems I forgot to call the field SQL when handling an identity field


if ( $field->identity ) {
$self->_handle_identity($field);
}
else {
$self->add_param( [] );
-- push( @field_sql, $self->_field_sql($field) );
}
++ push( @field_sql, $self->_field_sql($field) );

and now I get that to come back correct


ok 13 - use identity option with exe array create SQL correct
ok 14 - use identity option with exe array create params correct

but now I am also getting;

not ok 11 - use identity option create SQL correct

with this generated SQL

VALUES( 1, products_seq.NEXTVAL, 1 )

Looks like another problem with that 'map' and having a look at it I forgot to wrap the 'or' with brackets so this is the correct way to do that;

map( {
(ref($_) eq 'Database::Accessor::Param'
or ref($_) eq 'ARRAY')


and now I get a full pass.

Well something different for tomorrow.

HPIM4474a.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations