DBI::DBD::SqlEngine(3pm)

1DBI::DBD::SqlEngine(3)User Contributed Perl DocumentationDBI::DBD::SqlEngine(3)
2
3
4

NAME

6       DBI::DBD::SqlEngine - Base class for DBI drivers without their own SQL
7       engine
8

SYNOPSIS

10           package DBD::myDriver;
11
12           use base qw(DBI::DBD::SqlEngine);
13
14           sub driver
15           {
16               ...
17               my $drh = $proto->SUPER::driver($attr);
18               ...
19               return $drh->{class};
20               }
21
22           package DBD::myDriver::dr;
23
24           @ISA = qw(DBI::DBD::SqlEngine::dr);
25
26           sub data_sources { ... }
27           ...
28
29           package DBD::myDriver::db;
30
31           @ISA = qw(DBI::DBD::SqlEngine::db);
32
33           sub init_valid_attributes { ... }
34           sub init_default_attributes { ... }
35           sub set_versions { ... }
36           sub validate_STORE_attr { my ($dbh, $attrib, $value) = @_; ... }
37           sub validate_FETCH_attr { my ($dbh, $attrib) = @_; ... }
38           sub get_myd_versions { ... }
39           sub get_avail_tables { ... }
40
41           package DBD::myDriver::st;
42
43           @ISA = qw(DBI::DBD::SqlEngine::st);
44
45           sub FETCH { ... }
46           sub STORE { ... }
47
48           package DBD::myDriver::Statement;
49
50           @ISA = qw(DBI::DBD::SqlEngine::Statement);
51
52           sub open_table { ... }
53
54           package DBD::myDriver::Table;
55
56           @ISA = qw(DBI::DBD::SqlEngine::Table);
57
58           sub new { ... }
59

DESCRIPTION

61       DBI::DBD::SqlEngine abstracts the usage of SQL engines from the DBD.
62       DBD authors can concentrate on the data retrieval they want to provide.
63
64       It is strongly recommended that you read DBD::File::Developers and
65       DBD::File::Roadmap, because many of the DBD::File API is provided by
66       DBI::DBD::SqlEngine.
67
68       Currently the API of DBI::DBD::SqlEngine is experimental and will
69       likely change in the near future to provide the table meta data basics
70       like DBD::File.
71
72   Metadata
73       The following attributes are handled by DBI itself and not by
74       DBI::DBD::SqlEngine, thus they all work as expected:
75
76           Active
77           ActiveKids
78           CachedKids
79           CompatMode             (Not used)
80           InactiveDestroy
81           AutoInactiveDestroy
82           Kids
83           PrintError
84           RaiseError
85           Warn                   (Not used)
86
87       The following DBI attributes are handled by DBI::DBD::SqlEngine:
88
89       AutoCommit
90
91       Always on.
92
93       ChopBlanks
94
95       Works.
96
97       NUM_OF_FIELDS
98
99       Valid after "$sth->execute".
100
101       NUM_OF_PARAMS
102
103       Valid after "$sth->prepare".
104
105       NAME
106
107       Valid after "$sth->execute"; probably undef for Non-Select statements.
108
109       NULLABLE
110
111       Not really working, always returns an array ref of ones, as DBD::CSV
112       does not verify input data. Valid after "$sth->execute"; undef for non-
113       select statements.
114
115       The following DBI attributes and methods are not supported:
116
117       bind_param_inout
118       CursorName
119       LongReadLen
120       LongTruncOk
121
122       DBI::DBD::SqlEngine specific attributes
123
124       In addition to the DBI attributes, you can use the following dbh
125       attributes:
126
127       sql_engine_version
128
129       Contains the module version of this driver (readonly)
130
131       sql_nano_version
132
133       Contains the module version of DBI::SQL::Nano (readonly)
134
135       sql_statement_version
136
137       Contains the module version of SQL::Statement, if available (readonly)
138
139       sql_handler
140
141       Contains the SQL Statement engine, either DBI::SQL::Nano or
142       SQL::Statement (readonly).
143
144       sql_parser_object
145
146       Contains an instantiated instance of SQL::Parser (readonly).  This is
147       filled when used first time (only when used with SQL::Statement).
148
149       sql_sponge_driver
150
151       Contains an internally used DBD::Sponge handle (readonly).
152
153       sql_valid_attrs
154
155       Contains the list of valid attributes for each DBI::DBD::SqlEngine
156       based driver (readonly).
157
158       sql_readonly_attrs
159
160       Contains the list of those attributes which are readonly (readonly).
161
162       sql_identifier_case
163
164       Contains how DBI::DBD::SqlEngine deals with non-quoted SQL identifiers:
165
166         * SQL_IC_UPPER (1) means all identifiers are internally converted
167           into upper-cased pendants
168         * SQL_IC_LOWER (2) means all identifiers are internally converted
169           into lower-cased pendants
170         * SQL_IC_MIXED (4) means all identifiers are taken as they are
171
172       These conversions happen if (and only if) no existing identifier
173       matches.  Once existing identifier is used as known.
174
175       The SQL statement execution classes doesn't have to care, so don't
176       expect "sql_identifier_case" affects column names in statements like
177
178         SELECT * FROM foo
179
180       sql_quoted_identifier_case
181
182       Contains how DBI::DBD::SqlEngine deals with quoted SQL identifiers
183       (readonly). It's fixated to SQL_IC_SENSITIVE \fIs0(3), which is
184       interpreted as SQL_IC_MIXED.
185
186       sql_flags
187
188       Contains additional flags to instantiate an SQL::Parser. Because an
189       SQL::Parser is instantiated only once, it's recommended to set this
190       flag before any statement is executed.
191
192       sql_dialect
193
194       Controls the dialect understood by SQL::Parser. Possible values
195       (delivery state of SQL::Statement):
196
197         * ANSI
198         * CSV
199         * AnyData
200
201       Defaults to "CSV".  Because an SQL::Parser is instantiated only once
202       and SQL::Parser doesn't allow to modify the dialect once instantiated,
203       it's strongly recommended to set this flag before any statement is
204       executed (best place is connect attribute hash).
205
206       sql_engine_in_gofer
207
208       This value has a true value in case of this driver is operated via
209       DBD::Gofer. The impact of being operated via Gofer is a read-only
210       driver (not read-only databases!), so you cannot modify any attributes
211       later - neither any table settings. But you won't get an error in cases
212       you modify table attributes, so please carefully watch
213       "sql_engine_in_gofer".
214
215       sql_meta
216
217       Private data area which contains information about the tables this
218       module handles. Table meta data might not be available until the table
219       has been accessed for the first time e.g., by issuing a select on it
220       however it is possible to pre-initialize attributes for each table you
221       use.
222
223       DBI::DBD::SqlEngine recognizes the (public) attributes "col_names",
224       "table_name", "readonly", "sql_data_source" and "sql_identifier_case".
225       Be very careful when modifying attributes you do not know, the
226       consequence might be a destroyed or corrupted table.
227
228       While "sql_meta" is a private and readonly attribute (which means, you
229       cannot modify it's values), derived drivers might provide restricted
230       write access through another attribute. Well known accessors are
231       "csv_tables" for DBD::CSV, "ad_tables" for DBD::AnyData and
232       "dbm_tables" for DBD::DBM.
233
234       sql_table_source
235
236       Controls the class which will be used for fetching available tables.
237
238       See "DBI::DBD::SqlEngine::TableSource" for details.
239
240       sql_data_source
241
242       Contains the class name to be used for opening tables.
243
244       See "DBI::DBD::SqlEngine::DataSource" for details.
245
246   Driver private methods
247       Default DBI methods
248
249       data_sources
250
251       The "data_sources" method returns a list of subdirectories of the
252       current directory in the form "dbi:CSV:f_dir=$dirname".
253
254       If you want to read the subdirectories of another directory, use
255
256           my ($drh)  = DBI->install_driver ("CSV");
257           my (@list) = $drh->data_sources (f_dir => "/usr/local/csv_data");
258
259       list_tables
260
261       This method returns a list of file names inside $dbh->{f_dir}.
262       Example:
263
264           my ($dbh)  = DBI->connect ("dbi:CSV:f_dir=/usr/local/csv_data");
265           my (@list) = $dbh->func ("list_tables");
266
267       Note that the list includes all files contained in the directory, even
268       those that have non-valid table names, from the view of SQL.
269
270       Additional methods
271
272       The following methods are only available via their documented name when
273       DBI::DBD::SQlEngine is used directly. Because this is only reasonable
274       for testing purposes, the real names must be used instead. Those names
275       can be computed by replacing the "sql_" in the method name with the
276       driver prefix.
277
278       sql_versions
279
280       Signature:
281
282         sub sql_versions (;$) {
283           my ($table_name) = @_;
284           $table_name ||= ".";
285           ...
286           }
287
288       Returns the versions of the driver, including the DBI version, the Perl
289       version, DBI::PurePerl version (if DBI::PurePerl is active) and the
290       version of the SQL engine in use.
291
292           my $dbh = DBI->connect ("dbi:File:");
293           my $sql_versions = $dbh->func( "sql_versions" );
294           print "$sql_versions\n";
295           __END__
296           # DBI::DBD::SqlEngine  0.05 using SQL::Statement 1.402
297           # DBI                  1.623
298           # OS                   netbsd (6.99.12)
299           # Perl                 5.016002 (x86_64-netbsd-thread-multi)
300
301       Called in list context, sql_versions will return an array containing
302       each line as single entry.
303
304       Some drivers might use the optional (table name) argument and modify
305       version information related to the table (e.g. DBD::DBM provides
306       storage backend information for the requested table, when it has a
307       table name).
308
309       sql_get_meta
310
311       Signature:
312
313           sub sql_get_meta ($$)
314           {
315               my ($table_name, $attrib) = @_;
316               ...
317           }
318
319       Returns the value of a meta attribute set for a specific table, if any.
320       See sql_meta for the possible attributes.
321
322       A table name of "." (single dot) is interpreted as the default table.
323       This will retrieve the appropriate attribute globally from the dbh.
324       This has the same restrictions as "$dbh->{$attrib}".
325
326       sql_set_meta
327
328       Signature:
329
330           sub sql_set_meta ($$$)
331           {
332               my ($table_name, $attrib, $value) = @_;
333               ...
334           }
335
336       Sets the value of a meta attribute set for a specific table.  See
337       sql_meta for the possible attributes.
338
339       A table name of "." (single dot) is interpreted as the default table
340       which will set the specified attribute globally for the dbh.  This has
341       the same restrictions as "$dbh->{$attrib} = $value".
342
343       sql_clear_meta
344
345       Signature:
346
347           sub sql_clear_meta ($)
348           {
349               my ($table_name) = @_;
350               ...
351           }
352
353       Clears the table specific meta information in the private storage of
354       the dbh.
355
356   Extensibility
357       DBI::DBD::SqlEngine::TableSource
358
359       Provides data sources and table information on database driver and
360       database handle level.
361
362         package DBI::DBD::SqlEngine::TableSource;
363
364         sub data_sources ($;$)
365         {
366           my ( $class, $drh, $attrs ) = @_;
367           ...
368         }
369
370         sub avail_tables
371         {
372           my ( $class, $drh ) = @_;
373           ...
374         }
375
376       The "data_sources" method is called when the user invokes any of the
377       following:
378
379         @ary = DBI->data_sources($driver);
380         @ary = DBI->data_sources($driver, \%attr);
381
382         @ary = $dbh->data_sources();
383         @ary = $dbh->data_sources(\%attr);
384
385       The "avail_tables" method is called when the user invokes any of the
386       following:
387
388         @names = $dbh->tables( $catalog, $schema, $table, $type );
389
390         $sth = $dbh->table_info( $catalog, $schema, $table, $type );
391         $sth = $dbh->table_info( $catalog, $schema, $table, $type, \%attr );
392
393         $dbh->func( "list_tables" );
394
395       Everytime where an "\%attr" argument can be specified, this "\%attr"
396       object's "sql_table_source" attribute is preferred over the $dbh
397       attribute or the driver default, eg.
398
399         @ary = DBI->data_sources("dbi:CSV:", {
400           f_dir => "/your/csv/tables",
401           # note: this class doesn't comes with DBI
402           sql_table_source => "DBD::File::Archive::Tar::TableSource",
403           # scan tarballs instead of directories
404         });
405
406       When you're going to implement such a
407       DBD::File::Archive::Tar::TableSource class, remember to add correct
408       attributes (including "sql_table_source" and "sql_data_source") to the
409       returned DSN's.
410
411       DBI::DBD::SqlEngine::DataSource
412
413       Provides base functionality for dealing with tables. It is primarily
414       designed for allowing transparent access to files on disk or already
415       opened (file-)streams (eg. for DBD::CSV).
416
417       Derived classes shall be restricted to similar functionality, too (eg.
418       opening streams from an archive, transparently compress/uncompress log
419       files before parsing them,
420
421         package DBI::DBD::SqlEngine::DataSource;
422
423         sub complete_table_name ($$;$)
424         {
425           my ( $self, $meta, $table, $respect_case ) = @_;
426           ...
427         }
428
429       The method "complete_table_name" is called when first setting up the
430       meta information for a table:
431
432         "SELECT user.id, user.name, user.shell FROM user WHERE ..."
433
434       results in opening the table "user". First step of the table open
435       process is completing the name. Let's imagine you're having a DBD::CSV
436       handle with following settings:
437
438         $dbh->{sql_identifier_case} = SQL_IC_LOWER;
439         $dbh->{f_ext} = '.lst';
440         $dbh->{f_dir} = '/data/web/adrmgr';
441
442       Those settings will result in looking for files matching
443       "[Uu][Ss][Ee][Rr](\.lst)?$" in "/data/web/adrmgr/". The scanning of the
444       directory "/data/web/adrmgr/" and the pattern match check will be done
445       in "DBD::File::DataSource::File" by the "complete_table_name" method.
446
447       If you intend to provide other sources of data streams than files, in
448       addition to provide an appropriate "complete_table_name" method, a
449       method to open the resource is required:
450
451         package DBI::DBD::SqlEngine::DataSource;
452
453         sub open_data ($)
454         {
455           my ( $self, $meta, $attrs, $flags ) = @_;
456           ...
457         }
458
459       After the method "open_data" has been run successfully, the table's
460       meta information are in a state which allowes the table's data accessor
461       methods will be able to fetch/store row information. Implementation
462       details heavily depends on the table implementation, whereby the most
463       famous is surely DBD::File::Table.
464

SQL ENGINES

466       DBI::DBD::SqlEngine currently supports two SQL engines: SQL::Statement
467       and DBI::SQL::Nano::Statement_. DBI::SQL::Nano supports a very limited
468       subset of SQL statements, but it might be faster for some very simple
469       tasks. SQL::Statement in contrast supports a much larger subset of ANSI
470       SQL.
471
472       To use SQL::Statement, you need at least version 1.401 of
473       SQL::Statement and the environment variable "DBI_SQL_NANO" must not be
474       set to a true value.
475

SUPPORT

477       You can find documentation for this module with the perldoc command.
478
479           perldoc DBI::DBD::SqlEngine
480
481       You can also look for information at:
482
483       ·   RT: CPAN's request tracker
484
485           <http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBI>
486           <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Statement>
487
488       ·   AnnoCPAN: Annotated CPAN documentation
489
490           <http://annocpan.org/dist/DBI>
491           <http://annocpan.org/dist/SQL-Statement>
492
493       ·   CPAN Ratings
494
495           <http://cpanratings.perl.org/d/DBI>
496
497       ·   Search CPAN
498
499           <http://search.cpan.org/dist/DBI/>
500
501   Where can I go for more help?
502       For questions about installation or usage, please ask on the
503       dbi-dev@perl.org mailing list.
504
505       If you have a bug report, patch or suggestion, please open a new report
506       ticket on CPAN, if there is not already one for the issue you want to
507       report. Of course, you can mail any of the module maintainers, but it
508       is less likely to be missed if it is reported on RT.
509
510       Report tickets should contain a detailed description of the bug or
511       enhancement request you want to report and at least an easy way to
512       verify/reproduce the issue and any supplied fix. Patches are always
513       welcome, too.
514

ACKNOWLEDGEMENTS

516       Thanks to Tim Bunce, Martin Evans and H.Merijn Brand for their
517       continued support while developing DBD::File, DBD::DBM and
518       DBD::AnyData.  Their support, hints and feedback helped to design and
519       implement this module.
520

AUTHOR

522       This module is currently maintained by
523
524       H.Merijn Brand < h.m.brand at xs4all.nl > and Jens Rehsack  < rehsack
525       at googlemail.com >
526
527       The original authors are Jochen Wiedmann and Jeff Zucker.
528

COPYRIGHT AND LICENSE

530        Copyright (C) 2009-2013 by H.Merijn Brand & Jens Rehsack
531        Copyright (C) 2004-2009 by Jeff Zucker
532        Copyright (C) 1998-2004 by Jochen Wiedmann
533
534       All rights reserved.
535
536       You may freely distribute and/or modify this module under the terms of
537       either the GNU General Public License (GPL) or the Artistic License, as
538       specified in the Perl README file.
539