in reply to Abstracting sql

What's wrong with having bits of SQL strewn around your code? It seems to be the same as saying that it is messy and wrong to have bits and pieces of regexes left and right in your program and that it would be better to have it abstracted away into some OO-package.

Whatever you do, somewhere in your program you will have SQL-statements to run the queries on your database. Maybe they are hidden inside Class::DBI or someplace else, but they are still there and I like to have them handy nearby so I can tinker with them as necessary. Of course, if you happen to use two times or more very similar SQL in your program, you better turn it into a subroutine which with the use of some well-chosen parameters can go very far in avoiding to having to type (almost) the same SQL-code again and again.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Replies are listed 'Best First'.
Re: Re: Abstracting sql
by BUU (Prior) on Jul 31, 2003 at 21:53 UTC
    >> It seems to be the same as saying that it is messy and wrong to have bits and pieces of regexes left [...]
    The key difference here is that regexen are not generally considered to be a seperate language, where as SQL quite definately is. It's fairly common to expect any perl programmer to be at least vaugely familiar with regexen but not necessairly familiar with SQL.

    Three advantages I can think of off the top of my head are:
    1. Familiarity - I can have someone who specializes in databases review my sql to make sure it is well formed and optimized without having to much with excessive amounts of perl, where as the perl programmer who doesn't know sql can just as easily only focus on the perl parts, as all he has to do is call some function if he wants data.
    2. Modularity - I could easily reuse sql statements and so on, a change in one place can instantly change everything that relies on this piece of code
    3. Portability - it becomes much easier to write applications to work on multiple databases when the SQL, the actual code that interfaces with the database, is hidden behind one or more abstraction layers.
[reply]
[d/l]
      ++BUU.

      Absolutely. In addition, it makes applying schema changes to the database much easier, because you know that the SQL that may be affected is all in one place.

      Michael

[reply]

      To each his/her own experience:

      1. Familiarity: I write as easy SQL as I do Perl, so there is no benefit in "sub-contracting" the SQL side to someone else.
      2. Modularity: You can of course only change the internals of your DB-access modules as the interface to the rest of your program must remain the same. Most of the time I find that I need to add some extra bit of functionality, which would break the interface anyhow (or make it horrendeously complicated to enable it to remain backward compatible for all things which depend on it already).
      3. Portability: Even with something like Class::DBI you have to set-up a lot of sub-classes for each database, so I hardly see the benefit of going through all that work for each database. I write as quickly my own code (with liberal use of the good ol' copy'n'paste).
      Which shows once again that there is no solution which fits and suits everybody. And that's the way it should be!

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]

In Section Meditations