.\" Automatically generated by Pod::Man 2.27 (Pod::Simple 3.28)
.\"
.\" 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 "SQL::Statement::Embed 3"
.TH SQL::Statement::Embed 3 "2014-12-02" "perl v5.16.3" "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"
SQL::Statement::Embed \- embed a SQL engine in a DBD or module
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
SQL::Statement is designed to be easy to embed in other modules and to be
especially easy to embed in \s-1DBI\s0 drivers.  It provides a \s-1SQL\s0 Engine and the
other module needs to then provide a data source and a storage mechanism.
For example, the \s-1DBD::CSV\s0 module uses SQL::Statement as an embedded \s-1SQL\s0
engine by implementing a file-based data source and by using \s-1DBI\s0 as the
user interface.  Similarly DBD::Amazon uses SQL::Statement as its \s-1SQL\s0
engine, provides its own extensions to the supported \s-1SQL\s0 syntax, and uses
on-the-fly searches of Amazon.com as its data source.
.PP
SQL::Statement is the basis for at least eight existing DBDs (\s-1DBI\s0 database
drivers).  If you have a new data source, you too can create a \s-1DBD\s0 without
having to reinvent the \s-1SQL\s0 wheel.  It is fun and easy so become a \s-1DBD\s0
author today!
.PP
SQL::Statement can be also be embedded without \s-1DBI. \s0 We will explore that
first since developing a \s-1DBD\s0 uses most of the same methods and techniques.
.SH "The role of SQL::Statement subclasses"
.IX Header "The role of SQL::Statement subclasses"
SQL::Statement provides a \s-1SQL\s0 parsing and execution engine.  It neither
provides a data source nor storage mechanism other than in-memory tables.
The DBI::DBD::SqlEngine contains a subclass of SQL::Statement to
abstract from embedding SQL::Statement into a \s-1DBD\s0 and lets you concentrate
on the extensions you need to make. DBD::File extends DBI::DBD::SqlEngine
by providing access to file-based storage mechanisms.  It is quite possible
to use things other than files as data sources, in which case you would not
use DBD::File, instead you would replace DBD::File's methods with your
own.  In the examples below, we use DBD::File, replacing only a few
methods.
.PP
SQL::Statement provides \s-1SQL\s0 parsing and evaluation and DBI::DBD::SqlEngine
provides \s-1DBI\s0 integration.  The only thing missing is a data source \- what we
actually want to store and query.   As an example suppose we are going to
create a subclass called 'Foo' that will provide as a data source the
in-memory storage which is used in \s-1SQL::RAM\s0 to provide the \f(CW\*(C`TEMP\*(C'\fR
tables in SQL::Statement, but the rows are stored as a string using a
serializer (Storable).
.PP
Consider what needs to happen to perform a \s-1SELECT\s0 query on our 'Foo' data:
.PP
.Vb 8
\& * receive a SQL string
\& * parse the SQL string into a request structure
\& * open the table(s) specified in the request
\& * define column names and positions for the table
\& * read rows from the table
\& * convert the rows from colon\-separated format into perl arrays
\& * match the columns and rows against the requested selection criteria
\& * return requested rows and columns to the user
.Ve
.PP
To perform operations like \s-1INSERT\s0 and \s-1DELETE,\s0 we also need to:
.PP
.Vb 3
\& * convert rows from perl arrays into colon\-separated format
\& * write rows
\& * delete rows
.Ve
.PP
SQL::Statement takes care of all of the \s-1SQL\s0 parsing and evaluation.
DBD::File takes care of file opening, reading, writing, and deleting.
So the only things 'Foo' is really responsible for are:
.PP
.Vb 3
\& * define column names and positions for the table
\& * convert rows from colon\-separated format into perl arrays
\& * convert rows from perl arrays into colon\-separated format
.Ve
.PP
In SQL::Statement subclasses these responsibilities are assigned to two
objects.  A ::Statement object is responsible for opening the table by
creating new ::Table objects.  A ::Table object is responsible for
defining the column names and positions, opening data sources, reading,
converting, writing and deleting data.
.PP
The real work is therefore done in the ::Table object, the ::Statement
subclass is required to deliver the right ::Table object.
.SH "Creating a ::Statement object"
.IX Header "Creating a ::Statement object"
A subclass of SQL::Statement must provide at least one method called
\&\fIopen_table()\fR.  The method should open a new Table object and define the
table's columns.  For our 'Foo' module, here is the complete object
definition:
.PP
.Vb 1
\&    package Foo;
\&
\&    package Foo::Statement;
\&    use DBD::File;
\&    use base qw(DBI::DBD::SqlEngine::Statement);
\&
\&    sub open_table {
\&        my ($self, $sth, $table, $createMode, $lockMode) = @_;
\&
\&        my $class = ref $self;
\&        $class =~ s/::Statement/::Table/;
\&
\&        return $class\->new ($sth, $table, $createMode, $lockMode);
\&    }
.Ve
.PP
Since 'Foo' is an in-memory data source, we subclass SQL::Statement
indirectly through DBD::File::Statement.  The \fIopen_table()\fR method lets
DBD::File do the actual table opening.  All we do is define the files
directory (f_dir), the names of the columns (col_names) and the positions
of the columns (col_nums).  DBD::File creates and returns a \f(CW$tbl\fR object.
It names that object according to the module that calls it, so in our
case the object will be a Foo::Table object.
.SH "Creating a ::Table object"
.IX Header "Creating a ::Table object"
Table objects are responsible for reading, converting, writing, and
deleting data. Since DBD::File provides most of those services, our 'Foo'
subclass only needs to define three methods \- \fIfetch_row()\fR to read data,
\&\fIpush_row()\fR to write data, and \fIpush_names()\fR to store column names.  We will
leave deleting to DBD::File, since deleting a record in the 'Foo' format
is the same process as deleting a record in any other simple file-based
format.  Here is the complete object definition:
.PP
.Vb 2
\& package Foo::Table;
\& use base qw(DBD::File::Table);
\&
\& sub fetch_row {
\&    my($self, $data) = @_;
\&    my $fieldstr = $self\->{fh}\->getline;
\&    return undef unless $fieldstr;
\&    chomp $fieldstr;
\&    my @fields   = split /:/,$fieldstr;
\&    $self\->{row} = (@fields ? \e@fields : undef);
\& }
\& sub push_row {
\&    my($self, $data, $fields) = @_;
\&    my $str = join \*(Aq:\*(Aq, map { defined $_ ? $_ : \*(Aq\*(Aq } @$fields;
\&    $self\->{fh}\->print( $str."\en");
\&    1;
\& }
\& sub push_names {}
\& 1;
.Ve
.PP
The \fIfetch_row()\fR method uses DBD::File's \fIgetline()\fR method to physically
read a row of data, then we convert it from native colon-separated format
into a perl arrayref.
.PP
The \fIpush_row()\fR method converts from a perl arrayref back to colon-separated
format then uses DBD::File's \fIprint()\fR method to print it to file.
.PP
The push_names method does nothing because it's purpose is to store column
names in a file and in our 'Foo' subclass, we are defining the column names
ourselves, not storing them in a file.
.SH "Trying out our new subclass"
.IX Header "Trying out our new subclass"
Here is a script which should create and query a file in our 'Foo' format.
It assumes you have saved the Foo, Foo::Statement, and Foo::Table classes
shown above into a file called Foo.pm.
.PP
.Vb 10
\& #!perl \-w
\& use strict;
\& use Foo;
\& my $parser = SQL::Parser\->new();
\& $parser\->{RaiseError}=1;
\& $parser\->{PrintError}=0;
\& for my $sql(split /\en/,
\& "  DROP TABLE IF EXISTS group_id
\&    CREATE TABLE group_id (username CHAR,uid INT, gid INT)
\&    INSERT INTO group_id VALUES(\*(Aqjoe\*(Aq,1,1)
\&    INSERT INTO group_id VALUES(\*(Aqsue\*(Aq,2,1)
\&    INSERT INTO group_id VALUES(\*(Aqbob\*(Aq,3,2)
\&    SELECT * FROM group_id             "
\& ){
\&    my $stmt = Foo::Statement\->new($sql,$parser);
\&    $stmt\->execute;
\&    next unless $stmt\->command eq \*(AqSELECT\*(Aq;
\&    while (my $row=$stmt\->fetch) {
\&        print "@$row\en";
\&    }
\& }
.Ve
.PP
This is the same script as shown in the section on executing and fetching
in SQL::Statement::Structure except that instead of
SQL::Statement\->\fInew()\fR, we are using Foo::Statement\->\fInew()\fR.   The other
difference is that the execute/fetch example was using in-memory storage
while this script is using file-based storage and the 'Foo' format we
defined.  When you run this script, you will be creating a file called
\&\*(L"group_id\*(R" and it will contain the specified data in colon-separated
format.
.SH "Developing a new DBD"
.IX Header "Developing a new DBD"
.SS "Moving from a subclass to a \s-1DBD\s0"
.IX Subsection "Moving from a subclass to a DBD"
A \s-1DBD\s0 based on SQL::Statement uses the same two subclasses that are shown
above.  They should be called DBD::Foo::Statement and DBD::Foo::Table, but
would otherwise be identical to the non-DBD subclass illustrated above.
To turn it into a full \s-1DBD,\s0 you have to subclass DBD::File, DBD::File::dr,
DBD::File::db, and DBD::File::st.  In many cases a simple subclass with
few or no methods overridden is sufficient.
.PP
Here is a working DBD::Foo:
.PP
.Vb 2
\& package DBD::Foo;
\& use base qw(DBD::File);
\&
\& package DBD::Foo::dr;
\& $DBD::Foo::dr::imp_data_size = 0;
\& use base qw(DBD::File::dr);
\&
\& package DBD::Foo::db;
\& $DBD::Foo::db::imp_data_size = 0;
\& use base qw(DBD::File::db);
\&
\& package DBD::Foo::st;
\& $DBD::Foo::st::imp_data_size = 0;
\& use base qw(DBD::File::st);
\&
\& package DBD::Foo::Statement;
\& use base qw(DBD::File::Statement);
\&
\& sub open_table {
\&     my $self = shift @_;
\&     my $data = shift @_;
\&     $data\->{Database}\->{f_dir} = \*(Aq./\*(Aq;
\&     my $tbl  = $self\->SUPER::open_table($data,@_);
\&     $tbl\->{col_names} = [qw(username uid gid)];
\&     $tbl\->{col_nums}  = {username=>0,uid=>1,gid=>2};
\&     return $tbl;
\& }
\&
\& package DBD::Foo::Table;
\& use base qw(DBD::File::Table);
\&
\& sub fetch_row {
\&    my($self, $data) = @_;
\&    my $fieldstr = $self\->{fh}\->getline;
\&    return undef unless $fieldstr;
\&    chomp $fieldstr;
\&    my @fields   = split /:/,$fieldstr;
\&    $self\->{row} = (@fields ? \e@fields : undef);
\& }
\& sub push_row {
\&     my($self, $data, $fields) = @_;
\&     my $str = join \*(Aq:\*(Aq, map { defined $_ ? $_ : \*(Aq\*(Aq } @$fields;
\&     $self\->{fh}\->print( $str."\en");
\&     1;
\& }
\& sub push_names {}
\& 1;
.Ve
.SS "A sample script to test our new \s-1DBD\s0"
.IX Subsection "A sample script to test our new DBD"
Assuming you saved the DBD::Foo shown above as a file called \*(L"Foo.pm\*(R" in
a directory called \*(L"\s-1DBD\*(R",\s0 this script will work, so will most other \s-1DBI\s0
methods such as selectall_arrayref, fetchrow_hashref, etc.
.PP
.Vb 10
\& #!perl \-w
\& use strict;
\& use lib qw(/home/jeff/data/module/lib); # or wherever you stored DBD::Foo
\& use DBI;
\& my $dbh=DBI\->connect(\*(Aqdbi:Foo:\*(Aq);
\& $dbh\->{RaiseError}=1;
\& $dbh\->{PrintError}=0;
\& for my $sql(split /\en/,
\& "  DROP TABLE IF EXISTS group_id
\&    CREATE TABLE group_id (username CHAR,uid INT, gid INT)
\&    INSERT INTO group_id VALUES(\*(Aqjoe\*(Aq,1,1)
\&    INSERT INTO group_id VALUES(\*(Aqsue\*(Aq,2,1)
\&    INSERT INTO group_id VALUES(\*(Aqbob\*(Aq,3,2)
\&    SELECT * FROM group_id             "
\& ){
\&    my $stmt = $dbh\->prepare($sql);
\&    $stmt\->execute;
\&    next unless $stmt\->{NUM_OF_FIELDS};
\&    while (my $row=$stmt\->fetch) {
\&        print "@$row\en";
\&    }
\& }
.Ve
.SH "Expanding the DBD"
.IX Header "Expanding the DBD"
Now that we have a basic \s-1DBD\s0 operational, there are several directions for
expansion.  In the first place, we might want to override some or all of
DBD::File::Table to provide alternate means of reading, writing, and
deleting from our data source.  We might want to override the \fIopen_table()\fR
method to provide a different means of identifying column names (e.g.
reading them from the file itself) or to provide other kinds of metadata.
See SQL::Eval for documentation of the \s-1API\s0 for ::Table objects and see
DBD::File for an example subclass.
.PP
We might want to create extensions to the \s-1SQL\s0 syntax specific to our \s-1DBD.\s0
See the section on extending \s-1SQL\s0 syntax in SQL::Statement::Syntax.
.PP
We might want to provide a completely different kind of data source.  See
\&\s-1DBD::DBM\s0 (whose source code includes documentation on subclassing
SQL::Statement and DBD::File), and other DBD::File subclasses such as
\&\s-1DBD::CSV\s0.
.PP
We might also want to provide a completely different storage mechanism,
something not based on files at all.  See DBD::Amazon and
DBD::AnyData.
.PP
And we will almost certainly want to fine-tune the \s-1DBI\s0 interface, see
\&\s-1DBI::DBD\s0.
.SH "Getting help with a new DBD"
.IX Header "Getting help with a new DBD"
The dbi\-devATperl.org mailing list should be your first stop in creating a
new \s-1DBD. \s0 Tim Bunce, the author of \s-1DBI\s0 and many \s-1DBD\s0 authors hang out there.
Tell us what you are planning and we will offer suggestions about similar
modules or other people working on similar issues, or on how to proceed.
.SH "AUTHOR & COPYRIGHT"
.IX Header "AUTHOR & COPYRIGHT"
.Vb 2
\&  Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved.
\&  Copyright (c) 2010, Jens Rehsack <rehsackATcpan.org>, all rights reserved.
.Ve
.PP
This document may be freely modified and distributed under the same terms
as Perl itself.