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]

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.