byterock

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.