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

SQL ENGINES

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

SUPPORT

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

ACKNOWLEDGEMENTS

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

AUTHOR

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

COPYRIGHT AND LICENSE

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