I just posted a discussion about fear-based development and how decoupling your application from the database implementation can avoid this. It's more of a discussion of a talk I'll be giving next month. I'd appreciate it if anyone would care to read and comment on it. The more info (particularly code examples!), the better.
We have moved to using an SQL phrasebook (all the queries stored in an xml file, later to seperated into several xml files) which allows us to do stuff like my $results = Query->fetch/execute( $queryname, { inarg => $inarg, }, {outarg => \$outarg} );
Combined with named column binding and placeholders, it provides all the power and flexibility of 'real' sql, while both abstracting the query out of the application and allowing the DBA to manage the queries and databases without having to dig into perl code.
I think this is definately the way to go, you can always wrap it again in objects or whatever if you need to, but so far it has worked fine.
We should have 50% of our SQL and PL/SQL in these classes by our next release and slowly move the rest in as we refactor code as we work.
Re:no mention of phrasebooks
gnat on 2003-03-26T23:14:08
Oooh, I'd like to put a recipe about that into the Perl Cookbook. Are there modules for doing this, or are you rolling everything by hand?--Nat
Re:no mention of phrasebooks
acme on 2003-03-27T10:08:58
Well, there's Class::Phrasebook::SQL. (which I haven't used).Re:no mention of phrasebooks
dws on 2003-03-26T23:25:04
I've been doing something similar to phrasebooks, though I'm now leaning toward your approach. XMLwith named parameters seems like a much better way to go.I've tried abstraction layers that generate SQL, but keep running into the need for a handful of fairly complicated queries (e.g., non-equi-outer joins) that I can craft by hand, but elude straightforward auto-generation.
Pareto Rule
Ovid on 2003-03-26T23:34:20
Just remember the Pareto Rule: 80% of the results stem from 20% of the actions. If you have a system in place that removes the bulk of the grunt work, you can focus on hand-implementing the tough stuff. In one of our older applications, we have the following SQL:
SELECT p.first_name+' '+p.last_name AS name, p.people_id
FROM people p
WHERE p.companyID = ? AND NOT EXISTS
(
SELECT 1
FROM product_contact_currency pcc
WHERE p.people_id = pcc.people_id
and pcc.currencyID = ?
and pcc.productID = ?
)
ORDER BY p.nameI would be utterly astonished by any abstraction layer generating that on the fly. Why worry about it? The key is to take out the grunt work and only do stuff like this when necessary. It sounds to me like you're definitely on the right track. (yeah, like I'm some sort of programming god worth listening to
:) Re:Pareto Rule
lachoy on 2003-03-27T12:32:38
This rule is key -- I never knew it had a name, BTW. I designed SPOPS to do *most* of the work for you, but nobody can write a system to do everything, although people certainly try. The trick is finding the right balance between configurability, features and complexity. So you make a list of the common operations (what to declare, what sort of relationships to deal with, how to allow field mapping and value transformations, etc.) and provide an easy way to take care of the rest as it comes along.Re:no mention of phrasebooks
TeeJay on 2003-03-27T00:18:11
There is Class::Phrasebook, and a good article on perl.com about doing it, as well as example designs at some the perl patterns websites and wiki's.I disremember if we rolled our own - I suspect we did. I will check tomorrow. Its not hard though.
There is no reason you can't have a nice module that provides a combination of dynamic and phrasebook based queries - the best of both worlds and you don't have to fit a hexagonal 'Object-Relational-Persistance' peg into square and round 'real world data structures' holes.
I have written a dynamic query generator or 3 already, and don't see any reason why you can't transparently provide a mix of on-the-fly, hard-coded, and phrasebook based queries. Have your cake and eat it.
I think the whole object persistance solution is only really applicable heavily OO systems that require solutions like Pixie and Tangram but much of what we are doing isn't OO and we don't need to build and rebuild objects - just insert, update and extract very complex data structures.
Re:no mention of phrasebooks
tinman on 2003-03-27T07:34:03
I've rolled my own phrase book as well (a fair amount of Perl code I write has to do with databases and SQL)
Not sure if I can publish the code (will check with my boss), but I load the SQL from a file into a phrasebook object and then the first time you called that SQL statement, I "prepared" it and returned the statement handle.. All subsequent calls, of course, just returned the statement handle that I had prepared previously. Worked pretty well, when you combine it with placeholders (as TeeJay mentioned)
Re:Tighter?
Ovid on 2003-03-26T23:24:23
The thrust of my talk is going to be decoupling the application from the specific implementation of the database. Class::DBI is merely one way to handle this decoupling. At my work, we've added a base class that automatically handles much of the decoupling for you.
package Foo::Company;
use base 'Foo::Class';
use strict;
use warnings;
my $map = {
'code' => 'code',
'name' => 'company_name',
};
my $data = {
id => 'company_id',
table => 'companies',
fields => $map,
sequence => 1
};
my $class = __PACKAGE__->initialize( $data );With that code, we have automatically get accessors and mutators for all fields. Further, by changing the column name or table name in this one little snippet, we can ensure that other objects that rely on this data can access it dynamically without worrying about what it really is. This has freed us to do massive refactoring in our database with relative impunity.
Since most of the code is generated for us merely by specifying a few lines of metadata, we've discovered massive productivity gains in our initial implementation.
Transactions are handled directly by the programmer (you can't automate what is essentially a business rule). However, one nifty thing about this is that we can specify that our objects are persisted in different databases on different servers. If I'm so inclined, I can have customer authentication information stored on a separate database, orders on another database and product information on a third, but it's completely transparent to the programmer. I'm not saying I would do that, but if we needed to migrate this to a different server (and even a different database product), it's very, very helpful.
Further, we do get to implement the SQL. Virtually anything can be overridden. Class::DBI has a set_sql() method which makes this a piece of cake.
All in all, the incredible time savings we've realized by switching to Class::DBI has saved us a ton of money and made our applications more robust. perrin on Perlmonks has pointed out that SPOPS is even more extensible and also pointed out XML::Comma, which is also interesting. There's a lot of great stuff out there.
