Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Considering future support for different databases.

by Cap'n Steve (Friar)
on Aug 15, 2008 at 08:13 UTC ( #704493=perlquestion: print w/ replies, xml ) Need Help??
Cap'n Steve has asked for the wisdom of the Perl Monks concerning the following question:

I'm working on a project where sometime in the future I'd like it to work with multiple database systems, and it seems like a good idea to lay the groundwork now. My question is, what do you think would be the best way to do this?

I was thinking about having a queries directory, with sub-directories below that for each system, and then text files containing the appropriate queries. Then, to run the query, I'd just need something like $db->fetch('get_newest_links') before running it through the standard DBI methods.

But how should I organize it? Should I have one query stored per file? Should I group some together or just read every query on every page load to save time?

Comment on Considering future support for different databases.
Replies are listed 'Best First'.
Re: Considering future support for different databases.
by dragonchild (Archbishop) on Aug 15, 2008 at 12:47 UTC
    First off, use DBIx::Class (or Rose::DB). An ORM solves most of the issues because the maintainers work really really hard to make sure it's DB-independent. While there are dozens of ORMs on CPAN, DBIC and Rose are the best two, imho. Jifty, Alzabo, and Tangram are written by good devs, but they don't have the team support that DBIC does or the single-minded insanity that the author of Rose has.

    Second, use a phrasebook for the stuff you absolutely have to hand-write SQL for. There's several on CPAN and one of the better ones was recommended already.

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
      Thanks for the suggestions. I'd actually never heard of ORM or a phrasebook before, but it sounds like a good thing to learn about.
        I also recommend using DBIx::Class - it is the most modern, robust Perl ORM available and was designed with this in mind.
Re: Considering future support for different databases.
by trwww (Priest) on Aug 15, 2008 at 09:52 UTC

    I've used Data::Phrasebook::SQL (or rather, what must have been an older version) with success.

    As pointed out, for what you want to do it starts to get really boring maintaining it. After using Phrasebook in my DBI evolution, I switched to ORM. The Perl Object-Oriented Persistence document helped me get started on sorting it all out. Though I think it hasn't been updated in a while, it dosen't mention DBIx::Class or Rose::DB.

    If you try the Phrasebook pattern (or something else) and decide it still isn't abstracted enough for you, check out ORM. I suggest starting with Class::DBI and then trying either Rose::DB or DBIx::Class, depending on your requirements.

    UPDATE: I guess I should explain what ORM is... simply put, it maps rows in your database to perl objects. And then the idea is when you need to change databases, you just reference a different underlying driver so you don't have to modify your code. It isn't perfect, but the communities behind some of these libraries are pretty large because, well, ORM is "the way".


      Thanks for the links, but I'm curious about your "depending on your requirements" statement. Which module would you recommend for which situation?
Re: Considering future support for different databases.
by jbert (Priest) on Aug 15, 2008 at 09:41 UTC

    Mogilefs is a significant perl app which is database-independent between MySQL, Postgres and SQLite.

    Basically, the approach is to have a 'store' base class which encapsulates all db access.

    There are capability flags set by each concrete derived store. e.g. 'can_replace' returns true in MogileFS::Store::MySQL, but false in MogileFS::Store::Postgres.

    This allows methods like:

    sub ignore_replace { my $self = shift; return "INSERT IGNORE " if $self->can_insertignore; return "REPLACE " if $self->can_replace; die "Can't INSERT IGNORE or REPLACE?"; }
    to try and do something sensible depending on the back-end.

    But frankly, you're going to have db-specific bugs unless you've got good test coverage. And db independence gets harder the more of the db features you use (both because you have to check for more variation and because such things are less standardised).

      That's an interesting example, but I don't think I'd like dealing with it that way. It looks like it could be more work (figuring out the individual features each database supports, especially for complicated queries), and I don't think it's very readable.
        It wouldn't have to be all methods. You could use a hash for each database, with keys for the capabilities and the actual SQL you need for your particular action. These could be complete with the placeholders already present. Then, you could assign a reference to the proper hash for your DBM to a global scalar. OTOH, a good reusable framework takes a lot of work, and there are a few to choose from that have that work already done.
Re: Considering future support for different databases.
by Krambambuli (Deacon) on Aug 15, 2008 at 08:30 UTC
    Are you sure all your queries will be static ?

    I'd probably put all SQL in a distinct module, using some conditioning inside the module to get the proper SQL back.

    It's better to have all related SQL grouped so that it is easy to overview when something should be changed later on. Having distinct directories and files would make maintenance difficult and error-prone.

    Good luck,

Re: Considering future support for different databases.
by psini (Deacon) on Aug 15, 2008 at 08:27 UTC

    I'm not sure I understand your question: do you want a cross-db app or an app which can be optimized for each db system?

    To make a cross-db app I would try and use only the most standard SQL queries, avoiding system or db-dependent syntax instead of adapting the queries to every SQL dialect.

    Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."

      I guess I'd always assumed that a cross-db app would have to be optimized for each system unless the database was extremely simple. Does anyone know of any examples of a project that manages to be compatible with several different databases with no special considerations for any of them?
        I'd guess that most systems that use a full-grown ORM library have all the db-specific code in the underlying (and reusable) ORM layer, and when they do use hand-written DB specific SQL, it's only to optimize a few "interesting" JOINs).

        Note that most ORM layers do rely on some standardization of the database schema (especially concerning naming of tables and columns, and limiting the available column types), but that doesn't mean the schemas themselves are simple at all.

Re: Considering future support for different databases.
by talexb (Canon) on Aug 15, 2008 at 13:47 UTC

    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

      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.
        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).

Re: Considering future support for different databases.
by wol (Hermit) on Aug 15, 2008 at 16:25 UTC
    This may not be the answer you're looking for, but...

    Unless you have firm requirements for your system running on top of multiple database servers, then you need to consider simply ignoring that angle for now. Just work on one database, and worry about supporting multiple systems when you need to (not before you need to).

    Alternatively, use an off the shelf framework to do all the abstraction for you.

    I'm currently working on a new legacy system (sic - not even finished, but already causing trouble and planned to be replaced) that's massively complicated because of unnecessary and/or unused in-house groundwork/preparation/abstraction/just-in-case-isms. If only my predecessors had chosen to use an existing library, I might be able to recommend one. Hopefully, some other monks will have opinions.

      True, but I can see it being a lot of work later to replace all the hardcoded queries with something else. Hopefully, one of the current frameworks will work.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://704493]
Approved by Arunbear
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2016-02-06 10:28 GMT
Find Nodes?
    Voting Booth?

    How many photographs, souvenirs, artworks, trophies or other decorative objects are displayed in your home?

    Results (223 votes), past polls