Baby Moose Starts Yet Again

Well is bug squish day here in the Moost-Pen

Yesterday I manged to get my test to fail properly I guess today I better start looking at fixing the code that is breaking starting with my update;

I left print warn on and in my output I am getting this


DBD::DBM::db prepare failed: Couldn't parse at C:/Dwimperl/perl/site/lib/DBI/DBD/SqlEngine.pm line 340
[for Statement "UPDATE user SET address = ?"] at

and a little playing about with a small test script I discovered that I have to add in a 'where' clause to make an update work. I first have to change my test a little;

delete($container->{username});
$container->{address} ='Achanged';
++$user->add_condition({left =>{ name => 'username',
++ view => 'user'},
++ right =>{ value => 'user_new'}
++ });
ok($user->update($utils->connect(),$container),"Update function");

That should add a 'predicate' class in to the 'dynamic_conditions' array that I will use in my where.
Now to create the where clause;

I know from playing with SQL for many years there is no difference in the format of a 'where' and a 'having' clause except for the SQL keyword. Keeping that in mind I created the '_where' sub like this;


sub _where_clause {
my $self = shift;
return ""
unless ( $self->condition_count );
return
$self->_predicate_clause( Database::Accessor::Driver::DBI::SQL::WHERE,
$self->conditions );
}

a simple wrapper sub that either returns empty string if there are no conditions or the results of the '_predicate_clause' sub below;

sub _predicate_clause {
my $self = shift;
my ( $clause_type, $conditions ) = @_;
my $clause = " $clause_type ";
my $predicate_clause = "";

foreach my $condition ( @{$conditions} ) {
foreach my $predicate ( @{ $condition->{predicates} } ) {
$predicate_clause .= join( " ",
$predicate->left->view . "." . $predicate->left->name,
$predicate->operator,
"'" . $predicate->right->value . "'",
$predicate->condition )

}
}
$self->da_warn( "__predicate_clause",
$clause_type . " clause='$predicate_clause'" )
if $self->da_warning() >= 5;
return join( " ", $clause, $predicate_clause );
}


Like the CRUD subs I have already created I am keeping this one deliberately obtuse for now. All I do is an iteration over the passed in 'conditions' array. I will allow end users to very complex conditional operations where you can have one or more conditions each with one or more predicates. Hence I have to do a double iteration over the conditions and then predicates in that condition.

To keep it simple for now I just assume I am entering an element and then a param. I even commit a very grievous sin by warping the value with “'” but this is just a temporary kludge. Like the other CRUD subs I added in a waring of the clause, I may increase the 5 to a 6 though at a later time.

In the end I generate this SQL


UPDATE user SET user.address = ? WHERE user.username = 'user_new'

Perfectly cromulent SQL, unfortunately my test still fails as I get this;

DBD::DBM::st execute failed: No such column 'user.username' at

Hmm I guess 'DBI::SQL::Nano' does not like table names on it fields.

I was just about to make a terrible curse on 'Jens Rehsack', his children, his children's children and his children's children's children when I saw in his code that the data store for Nano is just a simple hash and I came up with this quick fix all I needed to do is change this sub in 'Test::Utils'


sub create_users_table {
my $self = shift;
my @sql = ("DROP TABLE IF EXISTS user",
-- "CREATE TABLE user ( username TEXT, address TEXT)",
++ "CREATE TABLE user ( user.username TEXT, user.address TEXT)",
-- "INSERT INTO user VALUES ( 'user1', 1)");
++ "INSERT INTO user VALUES ( 'user.user1', 1)");
$self->do_sql(@sql);
}

and then a minor change to include the view name on insert and delete, then clean up some of the numbers in my test case as well as drop a few tests and now I get;

ok 1 - Create function
ok 2 - One row effected
ok 3 - Update function
ok 4 - one row effected
ok 5 - retrieve function
ok 6 - One row returned
ok 7 - address changed
ok 8 - delete function
ok 9 - One row Deleted
ok 10 - retrieve function
ok 11 - one row in DB

I also fixed the problem that came up on the select as well. Bonus! So back on track.

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