Use one of the many execellent solutions on CPAN. This is not only the correct solution, in terms of bug-free and maintainable code, it's also, in most cases, the faster solution. There are at least 5-7 different solutions, from
Class::DBI to various SQL dictionaries to all sorts of options.
Now, if you're determined to re-invent the wheel, I would suggest the following decomposition:
- Separate the actual DBI framework from the query creation. You already seem to have done this, but I would go further. Create a selector and a other-than-selector. Usually, you will use fetch() with selects and do() if you're not selecting. (Usually, but not always.)
- When creating the query, what sort of validation do you want to do on it? For example, it's quite reasonable to limit which tables a query can be built against, especially if you're building the query from some user-supplied data. The same goes for columns, joins (of all kinds), and limits.
- Speaking of joins, how do you want to handle outer joins? (Inner joins are what most of us consider to be standard joins.) For example, let's say you want to get a list of all the merchants in the database and, if any exist, all the unpaid bills that merchant owes you. That could look something like:
SELECT merch.id,
IS_NULL(bill.id, 'NONE')
FROM merchants merch,
merchant_unpaid bill
WHERE merch.id = bill.merch_id(+)
ORDER BY 1,2
Does your SQL-building methodology handle this? Class::DBI does ...
- Another question is how do your data structures map to your tables. In pathologically simple cases, they are the same. However, if you have any complexity whatsoever, a well-normalized database looks very different from the business logic data structures used in the application. I've seen cases where a data structure maps to a 60++ line sub-query across 5 tables. How are you planning on handling that? (Again, Class::DBI handles this for you ...)
- Yet another issue is performance. When dealing with a RDBMS, the bottleneck immediately shifts from your code to your SQL and how the database is tuned. Adding (or removing!) an index can speed up a cgi script's performance 90% or more! (Yes, I've seen this.) Adjusting the order of the WHERE clauses can contribute 30%-50% of a script's performance. And, there are innumerable other variables. (Again, Class::DBI provides for this ...)
And, I'm just referring to the one whose docs I've actually glanced at. Solutions suggested by other monks, IIRC, do even more than Class::DBI. So, why do you want to re-invent the wheel again?
------
We are the carpenters and bricklayers of the Information Age.
The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6
Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified
