.\" Automatically generated by Pod::Man 2.28 (Pod::Simple 3.29)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBD::PgLite::MirrorPgToSQLite 3"
.TH DBD::PgLite::MirrorPgToSQLite 3 "2008-11-19" "perl v5.22.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBD::PgLite::MirrorPgToSQLite \- Mirror tables from PostgreSQL to SQLite
.SH "SUMMARY"
.IX Header "SUMMARY"
.Vb 11
\& use DBD::PgLite::MirrorPgToSQLite qw(pg_to_sqlite);
\& pg_to_sqlite(
\&     sqlite_file => \*(Aq/var/pg_mirror/news.sqlite\*(Aq,
\&     pg_dbh      => $dbh,
\&     schema      => \*(Aqnews\*(Aq,
\&     tables      => [ qw(news cat img /^x_news/)],
\&     views       => [ \*(Aqv_newslist\*(Aq ],
\&     indexes     => 1,
\&     verbose     => 1,
\&     snapshot    => 1,
\& );
.Ve
.SH "USAGE"
.IX Header "USAGE"
The purpose of this module is to facilitate mirroring of tables from a
PostgreSQL dataabse to a SQLite file. The module has only be tested
with PostgreSQL 7.3 and SQLite 3.0\-3.2. SQLite 2.x will probably not
work; as for PostgreSQL, any version after 7.2 is supposed to work. If
it doesn't, please let me know.
.PP
As seen above, options to the \fIpg_to_sqlite()\fR function (which is
exported on request) are passed in as a hash.  These options are
described below. The default values can be changed by overriding the
\&\fIDBD::PgLite::MirrorPgToSQLite::defaults()\fR subroutine.
.SS "Required options"
.IX Subsection "Required options"
Obviously, the mirroring function needs either a PosgtgreSQL database
connection or enough information to be able to connect to the database
by itself. It also needs the name of a target SQLite file, and a list
of tables to copy between the two databases.
.IP "pg_dbh, pg_user, pg_pass, pg_dsn" 4
.IX Item "pg_dbh, pg_user, pg_pass, pg_dsn"
If a database handle is specified in \fIpg_dbh\fR, it takes
precedence. Otherwise we try to connect using \fIpg_dsn\fR, \fIpg_user\fR,
and \fIpg_pass\fR (which are assigned defaults based on the environment
variables \s-1PGDATABASE, PGUSER\s0 and \s-1PGPASSWORD,\s0 if any of these is
present).
.IP "tables" 4
.IX Item "tables"
The value of the required \fItables\fR option should be an arrayref of
strings or a string containing a comma-separated list of tablenames
and tablename patterns. A tablename pattern is a string or distinct
string portion delimited by forward slashes. To clarify: Suppose that
a database contains the tables news, img, img_group, cat, users,
comments, news_read_log, x_news_cat, x_news_img, and x_img_group; and
that we want to mirror news, img, cat, x_news_img and x_news_cat,
leaving the other tables alone. To achieve this, you would set the
\&\fItables\fR option to any of the following (there are of course also
other possibilities):
.Sp
.Vb 4
\& (1) [qw(news img cat x_news_img x_news_cat)]
\& (2) \*(Aqnews, img, cat, x_news_img, x_news_cat\*(Aq
\& (3) [qw(news /img$/ /cat$/)]
\& (4) \*(Aqnews,/img$/,/cat/\*(Aq
.Ve
.Sp
The purpose of this seemingly unneccesary flexibility in how the table
list is specified is to make the functionality of the module more
easily accessible from the command line.
.Sp
Please note that the patterns between the slash delimiters are not
Perl regular expressions but rather \s-1POSIX\s0 regular expressions, used to
query the PostgreSQL system tables directly.
.IP "sqlite_file" 4
.IX Item "sqlite_file"
This should specify the full path to a SQLite file. While the
mirroring takes place, the incoming data is not written directly to
this file, but to a file with the same name except for a '.tmp'
extension. When the operation has finished, the previous file with the
name specified (if any) is renamed with a '.bak' extension, and the
\&.tmp file is renamed to the requested filename. Unless you use the
\&\fIappend\fR option, the information previously in the file will be
totally replaced.
.SS "Other options"
.IX Subsection "Other options"
.IP "schema" 4
.IX Item "schema"
This signifies the schema from which the tables on the PostgreSQL side
are to be fetched. Default: 'public'. Only one schema can be specified
at a time.
.IP "where" 4
.IX Item "where"
A WHERE-condition appended to the SELECT-statement used to get data
from the PostgreSQL tables.
.IP "views" 4
.IX Item "views"
A list of views, specified in the same manner as the list of tables
for the \fItables\fR option. An attempt is made to define corresponding
views on the SQLite side (though this functionality is far from
reliable).
.IP "indexes" 4
.IX Item "indexes"
A boolean option indicating whether to create indexes for the same
columns in SQLite as in PostgreSQL. Default: false. (Normally only the
primary key is created).
.IP "functions" 4
.IX Item "functions"
A boolean indicating whether to attempt to create functions on the
SQLite side corresponding to any \s-1SQL\s0 language (\s-1NOT\s0 PL/pgSQL or other
procedural language) functions in the PostgreSQL database. This is
for use with DBD::PgLite only, since these functions are put into the
pglite_functions table. Default: false.
.IP "page_limit" 4
.IX Item "page_limit"
Normally the information from the PostgreSQL tables is read into
memory in one go and transferred directly to the SQLite file. This is,
however, obviously not desireable for very large tables. If the
PostgreSQL system tables report that the page count for the table is
above the limit specified by \fIpage_limit\fR, the table is instead
transferred row-by-row. Default value: 5000; since each page normally
is 8K, this represents about 40 \s-1MB\s0 on disk and perhaps 70\-100 \s-1MB\s0 of
memory usage by the Perl process. For page_limit to work, the table
must have a primary key.
.Sp
\&\s-1NB\s0! Do not set this limit lower than necessary: it is orders of
magnitude slower than the default \*(L"slurp into memory\*(R" mode.
.IP "append" 4
.IX Item "append"
If this boolean option is true, then instead of creating a new SQLite
file, the current contents of the \fIsqlite_file\fR are added to. If a
table which is being mirrored existed previously in the file, it is
dropped and recreated, but any tables not being copied from PostgreSQL
in the current run are left alone. (This is primarily useful for
mirroring some tables in toto, and others only in part, into the same
file). Default: false. Incompatible with the \fIsnapshot\fR option.
.IP "snapshot" 4
.IX Item "snapshot"
If this is true, then the copying from PostgreSQL takes place in
serialized mode (transaction isolation level serializable), which
should ensure consistency of relations between tables linked by
foreign key constraints. Currently, foreign keys are not created on
the SQLite side, however. Default: false. Incompatible with the
\&\fIappend\fR option.
.IP "cachedir" 4
.IX Item "cachedir"
The current method for getting information about table structure in
PostgreSQL is somewhat slow, especially for databases with very many
tables. To offset this, table definitions are cached in a temporary
directory so that subsequent mirrorings of the same table will go
faster. The downside is, of course, that if the table structure
changes, the cache needs to be cleared manually. The cache directory
can be specified using this option; the default is
/tmp/sqlite_mirror_cache (with separate subdirectories for each user).
.IP "verbose" 4
.IX Item "verbose"
If this is true, a few messages will be output to stderr during the
mirroring process.
.SH "TODO"
.IX Header "TODO"
.IP "\(bu" 4
Support for foreign keys is missing.
.IP "\(bu" 4
The method used to read tables bigger than \fIpage_limit\fR needs to be
improved.
.IP "\(bu" 4
It would be nice to have a quick way of telling whether the cached
table definition of a specific table is still valid.
.IP "\(bu" 4
Tests.
.SH "AUTHOR"
.IX Header "AUTHOR"
Baldur Kristinsson (bk@mbl.is), 2004\-2006.
.PP
.Vb 3
\& 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.
.Ve