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.
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
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)
The hole point of this can be summed up in this post
https://blogs.perl.org/users/byterock/2017/11/and-here-we-go.html
The SQL is only one part of it. The other is to generation Mongo queries as well on the fly.
I suppose I could use SQL::Abstract to generate the SQL I would have to white some sort of transmogifier code to go from the Database::Accessor abstration to the SQL::Abstract version.
Just as much code me thinks.