view release on metacpan or  search on metacpan

lib/DBD/PgLite.pm  view on Meta::CPAN

		$sql =~ s/(INTO\s+[\w\.]+\s+\()/$1$pk[0], /i;
		$sql =~ s/(VALUES\s+\()/$1$val, /i;
	}
	return $sql;
}


### Aggregate function: avg ####

package DBD::PgLite::Aggregate::avg;

sub new { bless {sum=>0,count=>0}, shift; }
sub step {
	my ($self,$val) = @_; 
	return unless defined $val; # don't count nulls as zero
	$self->{count}++;
	$self->{sum}+=$val;
}
sub finalize {
	my $self = shift;
	return undef unless $self->{count};
	return $self->{sum}/$self->{count};
}

1;
__END__


=pod

=head1 NAME

DBD::PgLite - PostgreSQL emulation mode for SQLite

=head1 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);

=head1 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
I<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
I<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.

=head1 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.