1SQL::Statement::Embed(3U)ser Contributed Perl DocumentatiSoQnL::Statement::Embed(3)
2
6 SQL::Statement::Embed - embed a SQL engine in a DBD or module
7
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 SQL::Statement is the basis for eight existing DBDs (DBI database driv‐
21 ers). If you have a new data source, you too can create a DBD without
22 having to reinvent the SQL wheel. It's fun, it's easy, become a DBD
23 author today!
24 SQL::Statement can be also be embedded without DBI. We'll explore that
26 first since developing a DBD uses most of the same methods and tech‐
27 niques.
28
30 SQL::Statement provides a SQL parsing and execution engine. It does
31 not provide a data source or storage mechanism other than in-memory
32 tables. The DBD::File module is a subclass of SQL::Statement that pro‐
33 vides access to file-based storage mechanisms. It's quite possible to
34 use things other than files as data souces, in which case we wouldn't
35 use DBD::File, instead we'd replace DBD::File's methods with our own.
36 In the examples below, we'll use DBD::File, replacing only a few meth‐
37 ods.
38 SQL::Statement provides SQL parsing and evaluation and DBD::File pro‐
40 vides file-based storage. The only thing missing is a data source -
41 what we actually want to store and query. As an example suppose we
42 are going to create a subclass called 'Foo' that will provide as a data
43 source a simple file similar to a passwd file - one record per line,
44 fields separated by colons, with only three fields "username, uid,
45 gid".
46 Consider what needs to happen to perform a SELECT query on our 'Foo'
48 data:
49 * recieve a SQL string
51 * parse the SQL string into a request structure
52 * open the table(s) specified in the request
53 * define column names and postions for the table
54 * read rows from the table
55 * convert the rows from colon-separated format into perl arrays
56 * match the columns and rows against the requested selection criteria
57 * return requested rows and columns to the user
58 To perform operations like INSERT and DELETE, we also need to:
60 * convert rows from perl arrays into colon-separated format
62 * write rows
63 * delete rows
64 SQL::Statement takes care of all of the SQL parsing and evaluation.
66 DBD::File takes care of file opening, reading, writing, and deleting.
67 So the only things 'Foo' is really responsible for are:
68 * define column names and postions for the table
70 * convert rows from colon-separated format into perl arrays
71 * convert rows from perl arrays into colon-separated format
72 In SQL::Statement subclasses these responsibilities are assigned to two
74 objects, a ::Statement object is responsible for opening the table,
75 defining the column names and positions, and for creating new ::Table
76 objects. A ::Table object is responsible for reading, converting,
77 writing, and deleting data.
78
80 A subclass of SQL::Statement must provide at least one method called
81 open_table(). The method should open a new Table object and define the
82 table's columns. For our 'Foo' module, here's the complete object def‐
83 inition:
84 package Foo;
86 package Foo::Statement;
88 use DBD::File;
89 use base qw(DBD::File::Statement);
90 sub open_table {
92 my $self = shift @_;
93 my $data = shift @_;
94 $data->{Database}->{f_dir} = './';
95 my $tbl = $self->SUPER::open_table($data,@_);
96 $tbl->{col_names} = [qw(username uid gid)];
97 $tbl->{col_nums} = {username=>0,uid=>1,gid=>2};
98 return $tbl;
99 }
100 Since 'Foo' is a file-based data source, we subclass SQL::Statement
102 indirectly through DBD::File::Statement. The open_table() method lets
103 DBD::File do the actual table opening. All we do is define the files
104 directory (f_dir) and the names of the columns (col_names) and the
105 positions of the columns (col_nums). DBD::File creates and returns a
106 $tbl object. It names that object according to the module that calls
107 it, so in our case the object will be a Foo::Table object.
108
110 Table objects are responsible for reading, converting, writing, and
111 deleting data. Since DBD::File provides most of those services, our
112 'Foo' subclass only needs to deinfe three methods - fetch_row() to read
113 data, push_row() to write data, and push_names() to store column names.
114 We'll leave deleting to DBD::File, since deleting a record in the 'Foo'
115 format is the same process as deleting a record in any other simple
116 file-based format. Here is the complete object defintion:
117 package Foo::Table;
119 use base qw(DBD::File::Table);
120 sub fetch_row {
122 my($self, $data) = @_;
123 my $fieldstr = $self->{fh}->getline;
124 return undef unless $fieldstr;
125 chomp $fieldstr;
126 my @fields = split /:/,$fieldstr;
127 $self->{row} = (@fields ? \@fields : undef);
128 }
129 sub push_row {
130 my($self, $data, $fields) = @_;
131 my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
132 $self->{fh}->print( $str."\n");
133 1;
134 }
135 sub push_names {}
136 1;
137 The fetch_row() method uses DBD::File's getline() method to physically
139 read a row of data, then we convert it from native colon-separated for‐
140 mat into a perl perl arrayref.
141 The push_row() method converts from a perl arrayref back to colon-sepa‐
143 rated format, then uses DBD::File's print() method to print it to file.
144 The push_names method does nothing. That's because it's purpose is to
146 store column names in a file. But in our 'Foo' subclass, we are defin‐
147 ing the column names ourselves, not storing them in a file, so we don't
148 need push_names to actually do anything.
149
151 Here's a script which should create and query a file in our 'Foo' for‐
152 mat. It assumes you have saved the Foo, Foo::Statement, and Foo::Table
153 classes shown above into a file called Foo.pm.
154 #!perl -w
156 use strict;
157 use Foo;
158 my $parser = SQL::Parser->new();
159 $parser->{RaiseError}=1;
160 $parser->{PrintError}=0;
161 for my $sql(split /\n/,
162 " DROP TABLE IF EXISTS group_id
163 CREATE TABLE group_id (username CHAR,uid INT, gid INT)
164 INSERT INTO group_id VALUES('joe',1,1)
165 INSERT INTO group_id VALUES('sue',2,1)
166 INSERT INTO group_id VALUES('bob',3,2)
167 SELECT * FROM group_id "
168 ){
169 my $stmt = Foo::Statement->new($sql,$parser);
170 $stmt->execute;
171 next unless $stmt->command eq 'SELECT';
172 while (my $row=$stmt->fetch) {
173 print "@$row\n";
174 }
175 }
176 This is the same script as shown in the section on executing and fetch‐
178 ing in SQL::Statement::Structure except that instead of SQL::State‐
179 ment->new(), we are using Foo::Statement->new(). The other difference
180 is that the execute/fetch example was using in-memory storage while
181 this script is using file-based storage and the 'Foo' format we
182 defined. When you run this script, you will be creating a file called
183 "group_id" and it will contain the specified data in colon-separated
184 format.
185
187 Moving from a subclass to a DBD
188 A DBD based on SQL::Statement uses the same two subclasses that are
190 shown above. They would be called DBD::Foo::Statement and
191 DBD::Foo::Table, but would otherwise be identical to the non-DBD sub‐
192 class illustrated above. To turn it into a full DBD, you'd have to
193 sublass DBD::File, DBD::File::dr, DBD::File::db, and DBD::File::st. In
194 many cases a simple sublcass with few or no methods overridden is suf‐
195 ficient.
196 Here is a working DBD::Foo:
198 package DBD::Foo;
200 use base qw(DBD::File);
201 package DBD::Foo::dr;
203 $DBD::Foo::dr::imp_data_size = 0;
204 use base qw(DBD::File::dr);
205 package DBD::Foo::db;
207 $DBD::Foo::db::imp_data_size = 0;
208 use base qw(DBD::File::db);
209 package DBD::Foo::st;
211 $DBD::Foo::st::imp_data_size = 0;
212 use base qw(DBD::File::st);
213 package DBD::Foo::Statement;
215 use base qw(DBD::File::Statement);
216 sub open_table {
218 my $self = shift @_;
219 my $data = shift @_;
220 $data->{Database}->{f_dir} = './';
221 my $tbl = $self->SUPER::open_table($data,@_);
222 $tbl->{col_names} = [qw(username uid gid)];
223 $tbl->{col_nums} = {username=>0,uid=>1,gid=>2};
224 return $tbl;
225 }
226 package DBD::Foo::Table;
228 use base qw(DBD::File::Table);
229 sub fetch_row {
231 my($self, $data) = @_;
232 my $fieldstr = $self->{fh}->getline;
233 return undef unless $fieldstr;
234 chomp $fieldstr;
235 my @fields = split /:/,$fieldstr;
236 $self->{row} = (@fields ? \@fields : undef);
237 }
238 sub push_row {
239 my($self, $data, $fields) = @_;
240 my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
241 $self->{fh}->print( $str."\n");
242 1;
243 }
244 sub push_names {}
245 1;
246 A sample script to test our new DBD
248 Assuming you saved the DBD::Foo shown above as a file called "Foo.pm"
250 in a directory called "DBD", this script will work, so will most other
251 DBI methods such as selectall_arrayref, fetchrow_hashref, etc.
252 #!perl -w
254 use strict;
255 use lib qw(/home/jeff/data/module/lib); # or wherever you stored DBD::Foo
256 use DBI;
257 my $dbh=DBI->connect('dbi:Foo:');
258 $dbh->{RaiseError}=1;
259 $dbh->{PrintError}=0;
260 for my $sql(split /\n/,
261 " DROP TABLE IF EXISTS group_id
262 CREATE TABLE group_id (username CHAR,uid INT, gid INT)
263 INSERT INTO group_id VALUES('joe',1,1)
264 INSERT INTO group_id VALUES('sue',2,1)
265 INSERT INTO group_id VALUES('bob',3,2)
266 SELECT * FROM group_id "
267 ){
268 my $stmt = $dbh->prepare($sql);
269 $stmt->execute;
270 next unless $stmt->{NUM_OF_FIELDS};
271 while (my $row=$stmt->fetch) {
272 print "@$row\n";
273 }
274 }
275
277 Now that we have a basic DBD operational, there are several directions
278 for expansion. In the first place, we might want to override some or
279 all of DBD::File::Table to provide alternate means of reading, writing,
280 and deleting from our data source. We might want to override the
281 open_table() method to provide a different means of identifying column
282 names (e.g. reading them from the file itself) or to provide other
283 kinds of metadata. See SQL::Eval for documentation of the API for
284 ::Table objects and see DBD::File for an example subclass.
285 We might want to create extensions to the SQL syntax specific to our
287 DBD. See the section on extending SQL syntax in SQL::Statement::Syntax.
288 We might want to provide a completely different kind of data source.
290 See DBD::DBM (whose source code includes documentation on subclassing
291 SQL::Statement and DBD::File), and other DBD::File subclasses such as
292 DBD::CSV.
293 We might also want to provide a completely differernt storage mecha‐
295 nism, something not based on files at all. See DBD::Amazon and
296 DBD::AnyData.
297 And we will almost certainly want to fine-tune the DBI interface, see
299 DBI::DBD.
300
302 The dbi-devATperl.org mailing list should be your first stop in creat‐
303 ing a new DBD. Tim Bunce, the author of DBI and many DBD authors hang
304 out there. Tell us what you are planning and we'll offer suggestions
305 about similar modules or other people working on similar issues, or on
306 how to proceed.
307
309 Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
310 reserved.
311 This document may be freely modified and distributed under the same
313 terms as Perl itself.
314perl v5.8.8 2005-04-18 SQL::Statement::Embed(3)