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.
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment