1SQL::Statement::Embed(3U)ser Contributed Perl DocumentatiSoQnL::Statement::Embed(3)
2
3
4

NAME

6       SQL::Statement::Embed - embed a SQL engine in a DBD or module
7

SYNOPSIS

DESCRIPTION

10       SQL::Statement is designed to be easy to embed in other modules and to
11       be especially easy to embed in DBI drivers.  It provides a SQL Engine
12       and the other module needs to then provide a data source and a storage
13       mechanism.  For example, the DBD::CSV module uses SQL::Statement as an
14       embedded SQL engine by implementing a file-based data source and by
15       using DBI as the user interface.  Similarly DBD::Amazon uses
16       SQL::Statement as its SQL engine, provides its own extensions to the
17       supported SQL syntax, and uses on-the-fly searches of Amazon.com as its
18       data source.
19
20       SQL::Statement is the basis for at least eight existing DBDs (DBI
21       database drivers).  If you have a new data source, you too can create a
22       DBD without having to reinvent the SQL wheel.  It is fun and easy so
23       become a DBD author today!
24
25       SQL::Statement can be also be embedded without DBI.  We will explore
26       that first since developing a DBD uses most of the same methods and
27       techniques.
28

The role of SQL::Statement subclasses

30       SQL::Statement provides a SQL parsing and execution engine.  It neither
31       provides a data source nor storage mechanism other than in-memory
32       tables.  The DBI::DBD::SqlEngine contains a subclass of SQL::Statement
33       to abstract from embedding SQL::Statement into a DBD and lets you
34       concentrate on the extensions you need to make. DBD::File extends
35       DBI::DBD::SqlEngine by providing access to file-based storage
36       mechanisms.  It is quite possible to use things other than files as
37       data sources, in which case you would not use DBD::File, instead you
38       would replace DBD::File's methods with your own.  In the examples
39       below, we use DBD::File, replacing only a few methods.
40
41       SQL::Statement provides SQL parsing and evaluation and
42       DBI::DBD::SqlEngine provides DBI integration.  The only thing missing
43       is a data source - what we actually want to store and query.   As an
44       example suppose we are going to create a subclass called 'Foo' that
45       will provide as a data source the in-memory storage which is used in
46       SQL::RAM to provide the "TEMP" tables in SQL::Statement, but the rows
47       are stored as a string using a serializer (Storable).
48
49       Consider what needs to happen to perform a SELECT query on our 'Foo'
50       data:
51
52        * receive a SQL string
53        * parse the SQL string into a request structure
54        * open the table(s) specified in the request
55        * define column names and positions for the table
56        * read rows from the table
57        * convert the rows from colon-separated format into perl arrays
58        * match the columns and rows against the requested selection criteria
59        * return requested rows and columns to the user
60
61       To perform operations like INSERT and DELETE, we also need to:
62
63        * convert rows from perl arrays into colon-separated format
64        * write rows
65        * delete rows
66
67       SQL::Statement takes care of all of the SQL parsing and evaluation.
68       DBD::File takes care of file opening, reading, writing, and deleting.
69       So the only things 'Foo' is really responsible for are:
70
71        * define column names and positions for the table
72        * convert rows from colon-separated format into perl arrays
73        * convert rows from perl arrays into colon-separated format
74
75       In SQL::Statement subclasses these responsibilities are assigned to two
76       objects.  A ::Statement object is responsible for opening the table by
77       creating new ::Table objects.  A ::Table object is responsible for
78       defining the column names and positions, opening data sources, reading,
79       converting, writing and deleting data.
80
81       The real work is therefore done in the ::Table object, the ::Statement
82       subclass is required to deliver the right ::Table object.
83

Creating a ::Statement object

85       A subclass of SQL::Statement must provide at least one method called
86       open_table().  The method should open a new Table object and define the
87       table's columns.  For our 'Foo' module, here is the complete object
88       definition:
89
90           package Foo;
91
92           package Foo::Statement;
93           use DBD::File;
94           use base qw(DBI::DBD::SqlEngine::Statement);
95
96           sub open_table {
97               my ($self, $sth, $table, $createMode, $lockMode) = @_;
98
99               my $class = ref $self;
100               $class =~ s/::Statement/::Table/;
101
102               return $class->new ($sth, $table, $createMode, $lockMode);
103           }
104
105       Since 'Foo' is an in-memory data source, we subclass SQL::Statement
106       indirectly through DBD::File::Statement.  The open_table() method lets
107       DBD::File do the actual table opening.  All we do is define the files
108       directory (f_dir), the names of the columns (col_names) and the
109       positions of the columns (col_nums).  DBD::File creates and returns a
110       $tbl object.  It names that object according to the module that calls
111       it, so in our case the object will be a Foo::Table object.
112

Creating a ::Table object

114       Table objects are responsible for reading, converting, writing, and
115       deleting data. Since DBD::File provides most of those services, our
116       'Foo' subclass only needs to define three methods - fetch_row() to read
117       data, push_row() to write data, and push_names() to store column names.
118       We will leave deleting to DBD::File, since deleting a record in the
119       'Foo' format is the same process as deleting a record in any other
120       simple file-based format.  Here is the complete object definition:
121
122        package Foo::Table;
123        use base qw(DBD::File::Table);
124
125        sub fetch_row {
126           my($self, $data) = @_;
127           my $fieldstr = $self->{fh}->getline;
128           return undef unless $fieldstr;
129           chomp $fieldstr;
130           my @fields   = split /:/,$fieldstr;
131           $self->{row} = (@fields ? \@fields : undef);
132        }
133        sub push_row {
134           my($self, $data, $fields) = @_;
135           my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
136           $self->{fh}->print( $str."\n");
137           1;
138        }
139        sub push_names {}
140        1;
141
142       The fetch_row() method uses DBD::File's getline() method to physically
143       read a row of data, then we convert it from native colon-separated
144       format into a perl arrayref.
145
146       The push_row() method converts from a perl arrayref back to colon-
147       separated format then uses DBD::File's print() method to print it to
148       file.
149
150       The push_names method does nothing because it's purpose is to store
151       column names in a file and in our 'Foo' subclass, we are defining the
152       column names ourselves, not storing them in a file.
153

Trying out our new subclass

155       Here is a script which should create and query a file in our 'Foo'
156       format.  It assumes you have saved the Foo, Foo::Statement, and
157       Foo::Table classes shown above into a file called Foo.pm.
158
159        #!perl -w
160        use strict;
161        use Foo;
162        my $parser = SQL::Parser->new();
163        $parser->{RaiseError}=1;
164        $parser->{PrintError}=0;
165        for my $sql(split /\n/,
166        "  DROP TABLE IF EXISTS group_id
167           CREATE TABLE group_id (username CHAR,uid INT, gid INT)
168           INSERT INTO group_id VALUES('joe',1,1)
169           INSERT INTO group_id VALUES('sue',2,1)
170           INSERT INTO group_id VALUES('bob',3,2)
171           SELECT * FROM group_id             "
172        ){
173           my $stmt = Foo::Statement->new($sql,$parser);
174           $stmt->execute;
175           next unless $stmt->command eq 'SELECT';
176           while (my $row=$stmt->fetch) {
177               print "@$row\n";
178           }
179        }
180
181       This is the same script as shown in the section on executing and
182       fetching in SQL::Statement::Structure except that instead of
183       SQL::Statement->new(), we are using Foo::Statement->new().   The other
184       difference is that the execute/fetch example was using in-memory
185       storage while this script is using file-based storage and the 'Foo'
186       format we defined.  When you run this script, you will be creating a
187       file called "group_id" and it will contain the specified data in colon-
188       separated format.
189

Developing a new DBD

191   Moving from a subclass to a DBD
192       A DBD based on SQL::Statement uses the same two subclasses that are
193       shown above.  They should be called DBD::Foo::Statement and
194       DBD::Foo::Table, but would otherwise be identical to the non-DBD
195       subclass illustrated above.  To turn it into a full DBD, you have to
196       subclass DBD::File, DBD::File::dr, DBD::File::db, and DBD::File::st.
197       In many cases a simple subclass with few or no methods overridden is
198       sufficient.
199
200       Here is a working DBD::Foo:
201
202        package DBD::Foo;
203        use base qw(DBD::File);
204
205        package DBD::Foo::dr;
206        $DBD::Foo::dr::imp_data_size = 0;
207        use base qw(DBD::File::dr);
208
209        package DBD::Foo::db;
210        $DBD::Foo::db::imp_data_size = 0;
211        use base qw(DBD::File::db);
212
213        package DBD::Foo::st;
214        $DBD::Foo::st::imp_data_size = 0;
215        use base qw(DBD::File::st);
216
217        package DBD::Foo::Statement;
218        use base qw(DBD::File::Statement);
219
220        sub open_table {
221            my $self = shift @_;
222            my $data = shift @_;
223            $data->{Database}->{f_dir} = './';
224            my $tbl  = $self->SUPER::open_table($data,@_);
225            $tbl->{col_names} = [qw(username uid gid)];
226            $tbl->{col_nums}  = {username=>0,uid=>1,gid=>2};
227            return $tbl;
228        }
229
230        package DBD::Foo::Table;
231        use base qw(DBD::File::Table);
232
233        sub fetch_row {
234           my($self, $data) = @_;
235           my $fieldstr = $self->{fh}->getline;
236           return undef unless $fieldstr;
237           chomp $fieldstr;
238           my @fields   = split /:/,$fieldstr;
239           $self->{row} = (@fields ? \@fields : undef);
240        }
241        sub push_row {
242            my($self, $data, $fields) = @_;
243            my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
244            $self->{fh}->print( $str."\n");
245            1;
246        }
247        sub push_names {}
248        1;
249
250   A sample script to test our new DBD
251       Assuming you saved the DBD::Foo shown above as a file called "Foo.pm"
252       in a directory called "DBD", this script will work, so will most other
253       DBI methods such as selectall_arrayref, fetchrow_hashref, etc.
254
255        #!perl -w
256        use strict;
257        use lib qw(/home/jeff/data/module/lib); # or wherever you stored DBD::Foo
258        use DBI;
259        my $dbh=DBI->connect('dbi:Foo:');
260        $dbh->{RaiseError}=1;
261        $dbh->{PrintError}=0;
262        for my $sql(split /\n/,
263        "  DROP TABLE IF EXISTS group_id
264           CREATE TABLE group_id (username CHAR,uid INT, gid INT)
265           INSERT INTO group_id VALUES('joe',1,1)
266           INSERT INTO group_id VALUES('sue',2,1)
267           INSERT INTO group_id VALUES('bob',3,2)
268           SELECT * FROM group_id             "
269        ){
270           my $stmt = $dbh->prepare($sql);
271           $stmt->execute;
272           next unless $stmt->{NUM_OF_FIELDS};
273           while (my $row=$stmt->fetch) {
274               print "@$row\n";
275           }
276        }
277

Expanding the DBD

279       Now that we have a basic DBD operational, there are several directions
280       for expansion.  In the first place, we might want to override some or
281       all of DBD::File::Table to provide alternate means of reading, writing,
282       and deleting from our data source.  We might want to override the
283       open_table() method to provide a different means of identifying column
284       names (e.g.  reading them from the file itself) or to provide other
285       kinds of metadata.  See SQL::Eval for documentation of the API for
286       ::Table objects and see DBD::File for an example subclass.
287
288       We might want to create extensions to the SQL syntax specific to our
289       DBD.  See the section on extending SQL syntax in
290       SQL::Statement::Syntax.
291
292       We might want to provide a completely different kind of data source.
293       See DBD::DBM (whose source code includes documentation on subclassing
294       SQL::Statement and DBD::File), and other DBD::File subclasses such as
295       DBD::CSV.
296
297       We might also want to provide a completely different storage mechanism,
298       something not based on files at all.  See DBD::Amazon and DBD::AnyData.
299
300       And we will almost certainly want to fine-tune the DBI interface, see
301       DBI::DBD.
302

Getting help with a new DBD

304       The dbi-devATperl.org mailing list should be your first stop in
305       creating a new DBD.  Tim Bunce, the author of DBI and many DBD authors
306       hang out there.  Tell us what you are planning and we will offer
307       suggestions about similar modules or other people working on similar
308       issues, or on how to proceed.
309
311         Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved.
312         Copyright (c) 2010-2020, Jens Rehsack <rehsackATcpan.org>, all rights reserved.
313
314       This document may be freely modified and distributed under the same
315       terms as Perl itself.
316
317
318
319perl v5.36.0                      2023-01-20          SQL::Statement::Embed(3)
Impressum