The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
SQL-Dialect-0.02
River stage zero No dependents
++
/ SQL::Dialect

NAME

SQL::Dialect - Auto-detection of SQL quirks.

SYNOPSIS

    use SQL::Dialect;
    
    my $dialect = SQL::Dialect->new( $dbh );
    
    if ($dialect->supports('limit-xy')) { ... }
    print $dialect->quote_char();
    ...

DESCRIPTION

This module detects the SQL dialect of a DBI database handle and exposes a handful of properties describing the features and quirks of that dialect.

CONSTRUCTOR

    # Auto-detect the appropriate dialect from a DBI handle:
    my $dialect = SQL::Dialect->new( $dbh );
    
    # Explicitly set the dialect that you want:
    my $dialect = SQL::Dialect->new( 'oracle' );
    
    # The "default" dialect is the default:
    my $dialect = SQL::Dialect->new();

Each implementation, or dialect, of SQL has quirks that slightly (or in some cases drastically) change the way that the SQL must be written to get a particular task done. In order for this module to work a dialect must be declared. The dialect will default to "default" which is very limited and only declares the bare minimum of features.

Currently a dialect type can be one of:

    default
    mysql
    oracle
    postgresql
    sqlite

When declaring the dialect type that you want you can either specify one of the dialects above, or you can just pass a DBI handle ($dbh) and it will be auto-detected. Currently the list of supported DBI Driver is limited to:

    DBD::mysql  (mysql)
    DBD::Oracle (oracle)
    DBD::Pg     (postgresql)
    DBD::PgPP   (postgresql)
    DBD::SQLite (sqlite)

If the driver that you are using is not in the above list then please contact the author and work with them to get it added.

STATEMENT PROPERTIES

limit

The dialect of the LIMIT clause.

    offset (postgresql, sqlite)
    xy     (mysql)

returning

The dialect of INSERT/UPDATE/DELETE ... RETURNING syntax.

    into   (oracle)
    select (postgresql)

DATABASE PROPERTIES

sequences

Whether the database supports sequences.

    postgresql
    oracle

FUNCTION PROPERTIES

last_insert_id

Whether the LAST_INSERT_ID() function is supported.

    mysql

last_insert_rowid

Whether the LAST_INSERT_ROWID() function is supported.

    sqlite

OTHER PROPERTIES

rownum

Returns true if the dialect supports the rownum pseudo column.

    oracle

quote_char

The character that is used to quote identifiers, such as table and column names.

sep_char

The character that is used to separate linked identifiers, such as a table name followed by a column name.

METHODS

supports

    # Do something if the dialect supports any form of limit and
    # only the select flavor of returning:
    if ($dialect->supports('limit', 'returning-select')) { ... }

Given a list of feature names, optionally dash-suffixed with a specific quirk, this will return true or false if the dialect supports them all.

TODO

  • A more complete test suite.

  • Add more dialects and supported DBI drivers! If anyone wants to help with this I'd greatly appreciate it.

  • Add more information about other quirks that the dialects have, such as whether PLSQL is supported, what kind of bulk loading interface is available (MySQL's LOAD INFILE versus O;racle SQL*Loader, etc), information about what functions to use for date math, which DBD drivers return the number of records inserted/deleted/selected, etc.

CONTRIBUTING

If you'd like to contribute bug fixes, enhancements, additional test covergage, or documentation to this module then by all means do so. You can fork this repository using github and then send the author a pull request.

Please contact the author if you are considering doing this and discuss your ideas.

SUPPORT

Currently there is no particular mailing list or IRC channel for this project. You can shoot the author an e-mail if you have a question.

If you'd like to report an issue you can use github's issue tracker.

REFERENCES

AUTHOR

Aran Clary Deltac <bluefeet@gmail.com>

LICENSE

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.