view release on metacpan or  search on metacpan

README  view on Meta::CPAN

NAME
    DBD::PgLite - PostgreSQL emulation mode for SQLite

SUMMARY
      use DBI;
      my $dbh = DBI->connect('dbi:PgLite:dbname=file');
      # The following PostgreSQL-flavoured SQL is invalid 
      # in SQLite directly, but works using PgLite
      my $sql = q[
        SELECT
          news_id, title, cat_id, cat_name, sc_id sc_name,
          to_char(news_created,'FMDD.FMMM.YYYY') AS ndate
        FROM
          news
          NATURAL JOIN x_news_cat
          NATURAL JOIN cat
          NATURAL JOIN subcat
        WHERE
          news_active = TRUE
          AND news_created > NOW() - INTERVAL '7 days'
      ];
      my $res = $dbh->selectall_arrayref($sql,{Columns=>{}});
      # From v. 0.05 with full sequence function support
      my $get_nid = "SELECT NEXTVAL('news_news_id_seq')";
      my $news_id = $dbh->selectrow_array($get_nid);

DESCRIPTION
    The module automatically and transparently transforms a broad range of
    SQL statements typical of PostgreSQL into a form suitable for use in
    SQLite. This involves both (a) parsing and filtering of the SQL; and (b)
    the addition of several PostgreSQL-compatible functions to SQLite.

    Mainly because of datatype issues, support for many PostgreSQL features
    simply cannot be provided without elaborate planning and detailed
    metadata. Since this module is intended to be usable with any SQLite3
    database, it follows that the emulation is limited in several respects.
    An overview of what works and what doesn't is given in the following
    section on PostgreSQL Compatibility.

    DBD::PgLite has support of a sort for stored procedures. This is
    described in the Extras section below. So are the few database functions
    defined by this module which are not in PostgreSQL. Finally, the Extras
    section contains a brief mention of the DBD::PgLite::MirrorPgToSQLite
    companion module.

    If you do not want SQL filtering to be turned on by default for the
    entire session, you can connect setting the connection attribute
    *FilterSQL* to a false value:

      my $dbh = DBI->connect("dbi:PgLite:dbname=$fn",
                             undef, undef, {FilterSQL=>0});

    To turn filtering off (or on) for a single statement, you can specify
    *FilterSQL* option as a statement attribute, e.g.:

      $dbh->do($sql, {FilterSQL=>0}, @bind);
      my $sth = $dbh->prepare($sql, {FilterSQL=>0});
      $res = $dbh->selectall_arrayref($sql, {FilterSQL=>0}, @bind);

    It is possible to specify user-defined pre- and postfiltering routines,
    both globally (by specifying them as attributes of the database handle)
    and locally (by specifying them as statement attributes):

      $dbh = DBI->connect("dbi:PgLite:$file",undef,undef,
                          {prefilter=>\&prefilter});
      $res = $dbh->selectall_arrayref($sql,
                                      {postfilter=>\&postfilter},
                                      @bind_values);

    The pre-/postfiltering subroutine receives the SQL as parameter and is
    expected to return the changed SQL.

STATUS OF THE MODULE
    This module was initially developed using SQLite 3.0 and PostgreSQL 7.3,
    but it should be fully compatible with newer versions of both SQLite
    (3.1 and 3.2 have been tested) and PostgreSQL (8.1 has been tested).

    Support for SELECT statements and the WHERE-conditions of DELETE and
    UPDATE statements is rather good, though still incomplete. The module
    especially focuses on NATURAL JOIN differences and commonly used,
    built-in PostgreSQL functions.

    Support for inserted/updated values in INSERT and UPDATE statements
    could use some improvement but is useable for simple things.

    There is no support for differences in DDL.

    The SQL transformations used are not based on a formal grammar but on