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 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 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
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 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 Consider what needs to happen to perform a SELECT query on our 'Foo'
50 data:
51 * 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 To perform operations like INSERT and DELETE, we also need to:
62 * convert rows from perl arrays into colon-separated format
64 * write rows
65 * delete rows
66 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 * 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 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 The real work is therefore done in the ::Table object, the ::Statement
82 subclass is required to deliver the right ::Table object.
83
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 package Foo;
91 package Foo::Statement;
93 use DBD::File;
94 use base qw(DBI::DBD::SqlEngine::Statement);
95 sub open_table {
97 my ($self, $sth, $table, $createMode, $lockMode) = @_;
98 my $class = ref $self;
100 $class =~ s/::Statement/::Table/;
101 return $class->new ($sth, $table, $createMode, $lockMode);
103 }
104 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
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 package Foo::Table;
123 use base qw(DBD::File::Table);
124 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 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 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 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
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 #!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 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
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 Here is a working DBD::Foo:
201 package DBD::Foo;
203 use base qw(DBD::File);
204 package DBD::Foo::dr;
206 $DBD::Foo::dr::imp_data_size = 0;
207 use base qw(DBD::File::dr);
208 package DBD::Foo::db;
210 $DBD::Foo::db::imp_data_size = 0;
211 use base qw(DBD::File::db);
212 package DBD::Foo::st;
214 $DBD::Foo::st::imp_data_size = 0;
215 use base qw(DBD::File::st);
216 package DBD::Foo::Statement;
218 use base qw(DBD::File::Statement);
219 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 package DBD::Foo::Table;
231 use base qw(DBD::File::Table);
232 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 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 #!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
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 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 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 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 And we will almost certainly want to fine-tune the DBI interface, see
301 DBI::DBD.
302
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 This document may be freely modified and distributed under the same
315 terms as Perl itself.
316perl v5.32.0 2020-10-22 SQL::Statement::Embed(3)