Time to Put my Thinking Moose Cap On

It test postette day here in the Moose-Pen.

Today I was rerunning all my test cases to seen which ones where breaking and I got though all of the test on Database::Accessor and was most of the way though Driver::DBI and then I got stuck on '40_joins.t' with this error;


# Expected SQL--> SELECT people.first_name,
people.last_name, people.id, address.street FROM people LEFT JOIN address ON
people.id = address.user_id WHERE people.first_name = ?

# Generated SQL-> SELECT people.first_name, people.last_name, people.id,
address.street FROM people WHERE people.first_name = ?

Hmm, missing the complete JOIN clause there. Now I do not think I made such a basic error as forgetting to call the '_join_clause' sub in the Driver::DBI so the first place I looked was my test and fortunately the test hash was just a little out of date.

Now with the new test hash;


my $tests = [
{
caption => "Left Link with 1 param",
index => 0,
key => 'links',
links => {
type => 'LEFT',
to => { name => 'address' },
conditions => [
{
left => { name => 'id' },
right => {
name => 'user_id',
view => 'address'
}
}
]
},
retrieve => {
sql =>
"SELECT people.first_name, people.last_name, people.id, address.street FROM people LEFT JOIN address ON people.id = address.user_id WHERE people.first_name = ?",
params => ['test1']
},
create => {
container => $container,
sql =>
"INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )",
params => [ 'Bill', 'Bloggings','test1' ]
},
update => {
container => $container,
sql =>
"UPDATE people SET first_name = ?, last_name = ? WHERE people.first_name = ?",
params => [ 'Bill', 'Bloggings', 'test1' ]
},
delete => {
sql => "DELETE FROM people WHERE people.first_name = ?",
params => ['test1']
},
}
];

I get these results;

ok 1 - Left Link with 1 param create SQL correct

not ok 2 - Left Link with 1 param create params correct

ok 3 - Left Link with 1 param retrieve SQL correct

ok 4 - Left Link with 1 param retrieve params correct

not ok 5 - Left Link with 1 param update SQL correct

ok 6 - Left Link with 1 param update params correct

not ok 7 - Left Link with 1 param delete SQL correct

ok 8 - Left Link with 1 param delete params correct

What is happening is my SQL are not matching up as I am getting errors like this;


# Expected SQL--> DELETE FROM people WHERE people.first_name = ?
# Generated SQL-> DELETE FROM people LEFT JOIN address ON people.id = address.user_id WHERE people.first_name = ?

So my “_join_clause” is being called for each of the CRUD actions but only my 'retrieve' expected SQL is correct.

This leads me to do some thinking as it is quite rare even unheard of to do a link on something other than a 'retrieve' but not strictly against the SQL standard. I better go review my design notes to see what I want to do in this situation.

At least I go the retrieve test to work.

IMG_0436a.jpg

4 Comments

Why not use DBIx::Class to manage all your database access (including autogeneration of queries) rather than building them manually?

Or if you don't want to go down the whole DBIx::Class rabbit hole, SQL::Abstract is nice too (it's what dbic uses to generate queries)

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