|
NAMEDBD::PgLite - PostgreSQL emulation mode for SQLiteSUMMARYuse 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); DESCRIPTIONThe 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 MODULEThis 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 applying simple regular expressions. An obvious consequence of this is that they may depend excessively on the author's SQL style. YMMV. (I would however like you to contact me if you come across some SQL statements which you feel should work but that don't). The development of this module has been driven by personal needs, and so is likely to be even more one-sided than the above description suggests. POSTGRESQL COMPATIBILITYIn this section, the PostgreSQL functions and operators supported by the module are enumerated.Regex operators
Math Functions
String FunctionsThe only string functions which are present natively in SQLite are substr(), lower() and upper(). These have been left alone. Added functions are the following:
Except for convert(), where another input encoding can be specified explicitly, these functions all assume that the strings are in an 8-bit character set, preferably iso-8859-1. The little-used idiom "substring(string from pattern for escape)" (where 'pattern' is not a POSIX regular expression but a SQL pattern) is not supported. Otherwise support for string functions is pretty complete. Data Type Formatting FunctionsThe implementation of these functions is impeded by the sparse type system employed by SQLite. Workarounds are possible, however, so this area will probably be better covered in future.
Date/Time FunctionsAgain, SQLite's intrinsically bad support for dates and intervals makes this area somewhat hard to cover properly. Function support is as follows; also note the caveats below:
Versions of SQLite 3.1 and later support some of these functions, e.g. current_date. In these versions the built-in will be overridden. The module makes no distinction between time/timestamp with and without time zone. It is assumed that times and timestamps are either all GMT or all localtime; time zone information is silently discarded. This may change later. Support for calculations with dates and intervals is still very limited. Basically, what is supported are expressions of the form "expr +/- interval 'descr'" where expr reduces to a timestamp or date value. If a transaction is started with begin_work(), the time as represented by now() and friends is "frozen" in the same way as in PostgreSQL until commit() or rollback() are called. A transaction started by simply running the SQL statement "BEGIN" does not, however, trigger this behaviour. Nor is the time automatically "unfrozen" when an error occurs during a transaction; you need to catch exceptions and call rollback() manually. Sequence Manipulation Functions
Aggregate Functions
A Note on CastingCasting using the construct "::datatype" is not supported in general. However, "::int", "::date" and "::bool" should work as expected. All other casts are silently discarded.A Note on BooleansThis module assumes that booleans will be stored as numeric values in the SQLite database. SQLite interprets 0 as false and any non-zero numeric value as true. Accordingly, expressions such as "= TRUE" and "= 't'" are simply removed in SELECT and DELETE statements. Likewise, "expr = FALSE" is turned into "NOT expr" before being passed on to SQLite.In INSERT and DELETE statements, TRUE and FALSE (as well as 't'::bool and 'f'::bool - but not 't' and 'f' by themselves) are turned into 1 and 0. Current_user etc.The functions current_user(), session_user() and user() - with or without parentheses - all mean the same thing. They return the username of the effective uid.Other FunctionsThe main groups of other functions (not supported by this module at all) are:
EXTRASStored ProceduresIf the active database file contains a table called pglite_functions, the module assumes that it will have the following structure:CREATE TABLE pglite_functions ( name TEXT, -- name of the function argnum INT, -- number of arguments (-1 means any number) type TEXT, -- can be 'sql' or 'perl' sql TEXT, -- the body of the function PRIMARY KEY (name, argnum) ); In the case of a SQL-type function, it can contain syntax supported through the module (and not directly by SQLite). The numeric arguments ($1-$9) customary in PostgreSQL are supported, so that in many cases simple functions will be directly transferrable from pg_proc in a PostgreSQL database. An instance of a SQL snippet which would work as a function body both in PostgreSQL and PgLite (e.g. with the function name 'full_price_descr'): SELECT TRIM(group_name||': '||price_description) FROM price_group NATURAL JOIN price WHERE price_id = $1 As for perl-type functions, the function body is simply the text of a subroutine. Here is a simple example of a function body for the function 'commify', which takes two arguments: the number to be formatted and the desired number of decimal places: sub { my ($num,$dp) = @_; my $format = "%.${dp}f"; $num = scalar reverse(sprintf $format, $num); my $rest = $1 if $num =~ s/^(\d+)\.//; $num =~ s/(...)/$1,/g; $num = "$rest.$num" if $rest; return scalar reverse($num); } Non-Pg Functions
DBD::PgLite::MirrorPgToSQLiteThe companion module, DBD::PgLite::MirrorPgToSQLite, may be of use in conjunction with this module. It can be used for easily mirroring specific tables from a PostgreSQL database, moving views and (some) functions as well if desired.CAVEATSSome functions defined by the module are not suitable for use with UTF-8 data and/or in an UTF-8 locale. (This, however, would be rather easy to change if you're willing to sacrifice proper support for 8-bit locales such as iso-8859-1).Please do not make the mistake of using this module for an important production system - too much can go wrong. But as a development tool it can be useful, and as a toy it can be fun... TODOThere is a lot left undone. The next step is probably to handle non-SELECT statements better.SEE ALSODBI, DBD::SQLite, DBD::Pg, DBD::PgLite::MirrorPgToSQLite;THANKS TOJohan Vromans, for encouraging me to improve the sequence support.AUTHORBaldur Kristinsson (bk@mbl.is), 2006.Copyright (c) 2006 Baldur Kristinsson. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
Visit the GSP FreeBSD Man Page Interface. |