Regex operators

• The regex operators "~", "~*", "!~" and "!~*" are transformed into calls to the user-defined function matches(). The regex flavour supported is Perl, not plain vanilla POSIX, so some incompatibilities may arise.
• Note that for ease of parsing, whitespace before and after the operator is required for the filtering to succeed. So "col ~ 'pat'" works, but "col~'pat'" doesn't.
• "SIMILAR TO" is not supported.
• ILIKE is quietly changed to LIKE. LIKE in SQLite is case-insensitive for 7-bit characters. In future, ILIKE will probably be handled more elegantly, and LIKE will be redefined so as to be more like PostgreSQL.

Math Functions

• Added: abs, cbrt, ceil, degrees, exp, floor, ln, log (1- and 2-argument forms), mod, pi, pow, radians, sign, sqrt, trunc (1- and 2-argument forms), acos, asin, atan, atan2, cos, cot, sin, tan.
• random() exists in SQLite but was redefined to conform better with PostgreSQL in terms of value range. setseed() was also added, but is not entirely compatible with PostgreSQL in the sense that setting the random seed does not engender the same sequence of pseudo-random numbers as it would in PostgreSQL.
• SQLite already has a handful of mathematical functions which have been left alone, notably round() (1- and 2-argument forms).

String Functions

•

ascii, bit_length, btrim, char_length, character_length, chr, convert (1- and 2-arg), decode, encode, initcap, length, lpad, ltrim, md5, octet_length, position, pg_client_encoding (always 'SQL_ASCII'), quote_ident, quote_literal, repeat, replace, rpad, rtrim, split_part, strpos, substring(string,offset,length), substring(string from pattern), to_ascii (assumes latin-1 input), to_hex, translate, trim.

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 Functions

• to_char(timestamp, format) is mostly supported. There is support for most formatting strings (all except 'Y,YYY', 'IYYY', 'IYY', 'IY', 'I', 'J', 'TZ', and 'tz'). The FM prefix is supported for 'MM', 'DD' and 'HH*', but not otherwise. Other prefixes are not supported.
• to_char(interval, format) and to_char(number, format) are not currently supported. Nor are to_date(), to_timestamp() and to_number() (yet).

Date/Time Functions

• Supported: now, current_date, current_time, current_datetime, date_part (with timestamps, not intervals), date_trunc, extract (with timestamps, not intervals), localtime, localtimestamp, timeofday.
• Not supported: age, isfinite, overlaps.

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

•

There is now full support for all explicit invocations of the sequence functions nextval(), setval(), currval() and lastval(). Sequences are emulated using the table pglite_seq. (This works even with multiple connections to the same database file, some of which are using transactions, since SQLite transactions lock the whole database file, luckily eliminating any risk of two connections getting the same value from a nextval() call).

Please be aware that sequences are autogenerated if they do not exist. Be careful to specify the appropriate sequence names or you will get unexpected results.

If a sequence being autogenerated ends with '_seq' and has a name which seems to match an existing table + an integer column from that table (tablename_colname_seq), it is given an initial value based on the maximum value in the column in question.

There is as yet no support for CREATE SEQUENCE statements. Use the autogeneration feature to create sequences.

Implicit calls to NEXTVAL() by omitting the serial column from the column list in an INSERT are caught in most cases. The main conditions that must be fulfilled for this to work are: (1) that the column in question is an integer column which is the sole primary key on the table; and (2) that the statement is a normal INSERT with a column list and a VALUES clause (and not, e.g., a statement of the form INSERT INTO x SELECT * FROM y).

There is as yet no interaction with the SQLite builtin autoincrement/last_insert_rowid() functionality in connection with the sequence function support.

Aggregate Functions

• max(), min(), count() and sum() are already supported by SQLite and have been left alone. Note that the construct "count(distinct colname)" is not supported unless the SQLite version being used supports it (3.2.6 and later).
• avg() has been added.
• stddev() and variance() are not supported.

A Note on Casting

A Note on Booleans

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.

Other Functions

• Database/user information functions: Aside from current_user/session_user/user, which were mentioned above, no functions in this group are supported. This includes current_database(), current_schema(), all functions with names starting with 'pg_' and 'has_', obj_description and col_description. See http://www.postgresql.org/docs/current/static/functions-misc.html
• Array functions are not implemented - see http://www.postgresql.org/docs/current/static/functions-array.html
• Binary string (BYTEA) functions are not implemented - see http://www.postgresql.org/docs/current/static/functions-binarystring.html
• Geometric functions are not implemented - see http://www.postgresql.org/docs/current/static/functions-geometry.html
• Network Address Functions are not implemented - see http://www.postgresql.org/docs/current/static/functions-net.html

Stored Procedures

  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

matches(), imatches():

These functions are used behind the scenes to implement support for the '~' regex-matching operator and its variants. They take two arguments, a string and a regular expression. matches() is case sensitive, imatches() isn't.

matches_safe(), imatches_safe():

These work in the same way as matches() and imatches() except that metacharacters are escaped in the regex argument. They are therefore in many cases more suitable for user input and other untrusted sources.

lower_latin1():

Depending on platform, lower() and upper() may not transform the case of non-ascii characters despite a proper locale being defined in the environment. This functions assumes that a Latin-1 locale is active and returns a lower-case version of the input given this assumption.

localeorder():

DBD::SQLite does not provide access to defining SQLite collation functions. This is a workaround for a specific case where this limitation can be an issue. Given a Latin-1 encoded string, it returns a string of hex digits which can be ascii-sorted in the ordinary way. The resulting row order will be in accordance with the currently active locele - but only if the locale is Latin-1 based. The sort is case-insensitive.

locale():

An information function simply returning the name of the current locale. The module sets the locale based on the environment variables $ENV{LC_COLLATE}, $ENV{LC_ALL}, $ENV{LANG}, and $ENV{LC_CTYPE}, in that order. Currently it is not possible to use different locales for character type and collation, as far as the module is concerned.

DBD::PgLite::MirrorPgToSQLite