There are a significant number of database-specific ideosyncracies both in SQL syntax and in driver functionality. A basic select-one-row-by-primary-key should be pretty close to universal, but as your application gets more complicated, more and more of these cases crop up. (For example, limits, joins, unions, sub-queries, blob fields, transactions, schema detection, triggers, procedures, and a dozen other things.)

A number of CPAN modules already address this issue. You could start with the items marked "Y" for Portability in my brief feature matrix of DBI wrappers.

Given how open-ended a task this is, I would encourage you to pool your efforts with other developers rather than building another mousetrap from scratch.

For example, if you think your interface is particularly nice, consider setting it up as an adaptor that drives an underlying layer like DBIx::SQLEngine. Or if there's some feature you need that the other engines lack, think about adding it as a patch.

Update: If you do decide to continue building your own solution, do at least take advantage of DBIx::AnyDBD, which handles automatically loading the necessary subclasses based on which DBI driver you're using.

If you think you'd like to press ahead, perhaps you could clarify why you think your module's strength or focus is going to be... What might some example code look like for the types of queries you support?

As a point of comparison, here's the DBIx::SQLEngine interface for the query types you've described -- in what ways is your module different? 

  my $sqldb = DBIx::SQLEngine->new( $dbi_handle_or_dsn );

  $sqldb->do_insert( 
    table => 'mytable', 
    values => { 'name'=>'Dave', 'color'=>'Blue' } 
  );

  $sqldb->do_update( 
    table => 'mytable', 
    values => { 'color'=>'Green' }, 
    where => { 'name'=>'Dave' } 
  );

  my $row = $sqldb->fetch_one_row( 
    table => 'mytable', 
    where => { 'name'=>'Dave' } 
  );

[download]

In reply to Re^3: Differences in SQL syntax when using DBI (are there any)? And help on publishing module on CPAN. by simonm
in thread Differences in SQL syntax when using DBI (are there any)? And help on publishing module on CPAN. by techcode

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.