As a hobbyist Perl programmer, SQL syntax makes my head hurt. I don't use it a lot and every time I do I have to relearn it. It makes it hurt so much I even tried writing a program that used store to free complex, nested variables in files. That was probably a big mistake. So I'm going to rewrite it properly this time using a database.

So I'm wondering what Monks out there think of SQL query builders. Do they actually save any work? I've taken a quick peek at some out there like SQL::Maker.pm but they don't seem to save much time or thought I'm guessing they are probably kind of inflexible. Plus, aren't I just learning one arcane syntax for another?

Over the weekend, I was dabbling with rolling my own SQL builder to query some data I need out of a database. It's not professional in any sense. Here's an example:

sub number_members_for_year_for_state {
  my $self = shift;
  my $year = shift;
  my $state = shift;

  my $col = 'members';
  my $table = 'lm_data';
  my $criteria = {state => $state, year => $year, and => {or => {desig
+_name => ['LU', 'LLG', 'CH', 'BR', 'LG', 'SLG', 'UNIT', 'LDIV']}}};

  my $result = $self->data->sum_column($col, $table, $criteria);
  return $result->[0][0];
}

[download]

My builder is arcane and functions are highly specialized like the sum_column function used here but at least I know how it works and after I build up a big enough library up, it will save me a lot of typing and researching how to make the different kinds of queries. I can't tell you how many times I've looked up they syntax to take a SUM a column and have forgotten. With my sum_column function I never will have to again. Also, I can customize my queries just so. For example, my database object will automatically look up the column type to see if an argument needs apostrophes around it. And it also makes every insert a multiple row insert to speed things up. However, of course, it will be a lot of work up front. The danger is, I think, I will have to write more and more complicated queries for all the different types of SQL queries. In that case, I guess I just bite the bullet and agonize for an hour trying to figure out how to write the proper SQL query without my builder.

Anyway, I'd appreciate some tips and advice from the pros for making database queries less painful.

$PM = "Perl Monk's";
$MCF = "Most Clueless Friar Abbot Bishop Pontiff Deacon Curate";
$nysus = $PM . ' ' . $MCF;
Click here if you love Perl Monks

In reply to What Do Monks Think of SQL Query Builders? by nysus

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.