Little Moose Moves On.

Well test expand posette day here in the Moose-pen.

Not that have the first test in 20_where_basic.t working I figured I might as well expand on that tests and ensure the other SQL clauses are coming out in the format I like. So I added the following in;

my $container =  {first_name=>'Bill',
                  last_name =>'Bloggings'};
my $da     = Database::Accessor->new($in_hash);
ok($da->create( $utils->connect(),$container),"created something");
ok($da->result()->query() eq "INSERT INTO people ( people.first_name, people.last_name ) VALUES( ?, ? )","create SQL correct");
ok($da->retrieve( $utils->connect() ),"selected something");
ok($da->result()->query() eq "SELECT people.first_name, people.last_name, people.user_id FROM people WHERE ( people.first_name = ? AND people.last_name = ? )","Select SQL correct");
ok($da->update( $utils->connect(),$container),"updated something");
ok($da->result()->query() eq "UPDATE people SET people.first_name = ?, people.last_name = ? WHERE ( people.first_name = ? AND people.last_name = ? )","Update SQL correct");
ok($da->delete( $utils->connect() ),"deleted something");
ok($da->result()->query() eq "DELETE FROM people WHERE ( people.first_name = ? AND people.last_name = ? )","Delete SQL correct");
and on my first run I got;

ok 1 - created something
not ok 2 - create SQL correct
ok 3 - selected something
ok 4 - Select SQL correct
ok 5 - updated something
not ok 6 - Update SQL correct
ok 7 - deleted something
ok 8 - Delete SQL correct
using that handy ' da_warning=>1' flag I was very quickly able to see my create sql was coming out as

INSERT INTO people (people.last_name,people.first_name) VALUES(?,?) WHERE ( people.first_name = ? AND people.last_name = ? )
so the first thing to do is drop the where clause;

--    $sql .= $self->_where_clause();
++    $sql .= $self->_where_clause()
++       if ($action ne Database::Accessor::Constants::CREATE);
and then a little clean up on the spacing here and there I get;

ok 2 - create SQL correct
and then for the Update;

UPDATE people SET people.first_name = ?,people.last_name = ? WHERE ( people.first_name = ? AND people.last_name = ? )
this time just an add in of a single “ “ fixed it and I get;

…
not ok 2 - create SQL correct
...
ok 6 - Update SQL correct
…
opps now create is failing again. This one I caught quite quickly, though I did have to run the test about 4 times to replicate it. Since my $container is a hash-ref so there is no order guaranteed when you do a 'keys' on it, so in one run I got

INSERT INTO people (people.first_name, people.last_name) VALUES( ? , ?)","create SQL correct");
and in another

INSERT INTO people (people.last_name, people.first_name) VALUES( ? , ?)","create SQL correct");
so I added in a 'sort' to where I make a call to 'keys' on a container and now they always iterate in the same order and now I get;

ok 1 - created something
ok 2 - create SQL correct
ok 3 - selected something
ok 4 - Select SQL correct
ok 5 - updated something
ok 6 - Update SQL correct
ok 7 - deleted something
ok 8 - Delete SQL correct
onto other things;

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