Running Down Moose
It running out of things to do day here in the Moose-Pen
So I am getting very close to being code complete on both Database::Accessor and Driver::DBI I thing I only have a few more little things to add. One that I am going to look at today is the problem of 'identity' fields.
97.654321% (a number pulled out of me arse) of SQL DB have some sort of auto-sequence field or a flag on the field to make the primary key auto increment on insert. There is only one main-stream DB that does not have this and that is ORACLE though there are some other out there I might not know about.
So in most DBs out there on an insert I would just skip that field when doing an insert and let the underling DB hanle it. For that small number of cases out of the norm I do have an attribute in the 'Elememt' class to flag such an outlier, though I have not used it yet.
has [
qw(is_identity
)
] => (
is => 'rw',
isa => 'Bool',
);
If I add in this test;
{
caption => 'is_identity Flag',
key => 'elements',
elements => [
{ name => 'id', is_identity => 1 },
{ name => 'first_name', },
{ name => 'last_name', },
],
create => {
container => {
last_name => 'Bloggings',
first_name => 'Bill',
},
sql =>
"INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )",
params => [ 'Bill', 'Bloggings' ]
},
},
it will pass as I only have two items in the 'container' hash and no code to handel that is_identity so not much of a test. If I where to run this on an Oracle DB it would fail as it would be expecting something like this;
INSERT INTO people ( id, first_name, last_name )
VALUES( ?,?, ? )
in its most simplest form. You will have to supply that 'id' field which you could do in the container, but not something you would want to do in the long term.
Oracle does allow one to do this;
INSERT INTO people ( id, first_name, last_name )
VALUES( people_seq.NEXTVAL,?, ? )
as long are your permissions are set up correctly.
Now how to set this up using my API language?? I think this may work
elements => [
{ name => 'id',
is_identity =>{ORACLE=>{name=>'NEXTVAL',
view=>'people_seq'} },
{ name => 'first_name', },
{ name => 'last_name', },
],
Now the only problem is creating a test for the above as I can't assume there is an Oracle DB hanging about but there is of course the default 'dbi:DBM' that is handing about I could just use that as a 'key' flag. I do not really care if it works as I never hit the DB in 99% of my testing.
Here is the revised test;
{
caption => 'Use identity option',
key => 'elements',
elements => [
{ name => 'id',
identity => {DBM=>{name=>'NEXTVAL',
view=>'people_seq'}}
},
{ name => 'first_name', },
{ name => 'last_name', },
],
create => {
container => {
last_name => 'Bloggings',
first_name => 'Bill',
},
sql =>
"INSERT INTO people ( id,first_name, last_name ) VALUES( people_seq.NEXTVAL,?, ? )",
params => [ 'Bill', 'Bloggings' ]
},
},
To get this to work I first have to change that attribute on the 'Element' class to;
has 'identity' => (
is => 'rw',
isa => 'HashRef',
);
Which will open up my Accessor API. I will have to test this as well so I just added in a little to '31_elements.t' case;
elements => [
{
++ name => 'id',
++ identity => {
++ DBM => {
++ name => 'NEXTVAL',
++ view => 'people_seq'
++ }
++ }
},
{
name => 'first_name',
…
Test::Database::Accessor::Utils::deep_element( $in_hash->{elements},
$da->elements, $dad->elements, 'Element' );
shift(@{$in_hash->{elements}});
…
Which passes
ok 2 - DA Element 0 correct
ok 3 - DAD Element 0 correct
as that test checks to see if the in-hash matches the attributes, now onto Driver::DBI.
What I have set up here is just a window in my API to pass down instructions to my various DADs and looking at my test I will have to make this little change for that and a change to the 'container' so my test now looks like this;
{
caption => 'use identity option',
key => 'elements',
elements => [
{ name => 'id',
identity =>{'DBI::db'=>{
DBM => {name => 'NEXTVAL',
view => 'products_seq'}}}
},
{ name => 'first_name', },
{ name => 'last_name', },
],
create => {
container => {
id => '',
last_name => 'Bloggings',
first_name => 'Bill',
},
sql =>
"INSERT INTO Products ( first_name, id, last_name )
VALUES( ?, products_seq.NEXTVAL, ? )",
params => [ 'Bill', 'Bloggings' ]
},
},
Notice how the 'identity' give me both a way to tell which DAD I am using (DBI::db) and which type of $dbh I am using.
After a good deal of playing about I finally came up with a first round patch for the above. I first had to attack the '_insert_update_container' sub where I prep the objects for an insert or an update. In the update path I added this;
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_idenity(1);
}
}
else {
my $param = Database::Accessor::Param->new({value=> $container->{$key}});
$self->add_param($param);
}
push(@field_sql, $self->_field_sql($field));
where I check to see if the field from the input hash has an identity attribute set and it matches up with the 'DB_Class' and Driver name on the $dbh. If it does I take that value and add it to the params attribute. I found that I had to add a new attribute flag 'has_idenity' as well;
has has_idenity => (
is => 'rw',
isa => 'Bool',
default => 0,
);
which I will use later on.
Next in the '_insert' sub instead of just adding a '?' for each field I use a map on the 'params' attribute to add a ? when the value is a 'Database::Accessor::Param' or I call the '_field_sql' with the value and return that;
my $values_clause =
Database::Accessor::Driver::DBI::SQL::VALUES
.join(" ",
Database::Accessor::Driver::DBI::SQL::OPEN_PARENS,
join(", ",
-- map(Database::Accessor::Driver::DBI::SQL::PARAM,@field_sql),
++ map({ref($_) eq 'Database::Accessor::Param'
++ ? Database::Accessor::Driver::DBI::SQL::PARAM
++ : $self->_field_sql($_,1)}
++ @{$self->params()})
++ ),
Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS);
finally in the 'execute' sub I have to account for that 'element' in my 'params' attribute, with the 'has_idenity' attribute like this;
else {
if ($self->has_idenity){
my @params = grep({ref($_) eq "Database::Accessor::Param"} @{$self->params});
$self->params(\@params);
}
$result->params([map({$_->value} @{$self->params} )]);
}
and when I run my tests I get;
ok 10 - Retrieve with case in a case retrieve params correct
ok 11 - use identity option create SQL correct
ok 12 - use identity option create params correct
a good days work.
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment