Re: Considering future support for different databases.

I'm not sure building queries for each database is going to be productive. From a 'Get it done' approach, I'd suggest just using DBI and standard, no frills, Lowest Common Denominator SQL.

For the more adventurous approach, you could use Class::DBI or DBIx::Class, and write the SQL programmatically.

A third approach would be to build a class that gives you the appropriate query or command for a particular search or action. The default would be the plain SQL, and the exception would be the SQL coded for a specific database. But that sounds like a fair bit of work.

So let my ask one of my favourite questions: What problem are you trying to solve?

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Comment on Re: Considering future support for different databases.

Replies are listed 'Best First'.
Re^2: Considering future support for different databases. by Cap'n Steve (Friar) on Aug 15, 2008 at 20:24 UTC
I don't know if completely cross-platform SQL is really an option. I know I've already used one MySQL specific function (GROUP_CONCAT), although I think most systems have a similar function. As for the problem I'm trying to solve? Well, I just want to make future changes easier, so not only will it be able to support multiple databases, but then I won't have to worry about identical queries hardcoded in two different places.	[reply]
Re^3: Considering future support for different databases. by Joost (Canon) on Aug 15, 2008 at 20:31 UTC
As others have already mentioned, at the very least, you want to separate out all SQL (or at least all the non-standard SQL) to some low level layer. IMHO instead of writing this layer yourself, usually it's a good idea to use an ORM layer like DBIx::Class, which should also make many common database tasks much more convenient. Your own design will end up looking similar anyway. In other words, you'll want to write as little SQL by hand as possible, and use whatever mechanism is convenient to handle the few DB specific variants (and ORM layers make that especially easy, since it's just a question of overriding the relevant methods). "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]