1DBD::SQLite::VirtualTabUlsee(r3)Contributed Perl DocumenDtBaDt:i:oSnQLite::VirtualTable(3)
2
3
4

NAME

6       DBD::SQLite::VirtualTable -- SQLite virtual tables implemented in Perl
7

SYNOPSIS

9         # register the virtual table module within sqlite
10         $dbh->sqlite_create_module(mod_name => "DBD::SQLite::VirtualTable::Subclass");
11
12         # create a virtual table
13         $dbh->do("CREATE VIRTUAL TABLE vtbl USING mod_name(arg1, arg2, ...)")
14
15         # use it as any regular table
16         my $sth = $dbh->prepare("SELECT * FROM vtbl WHERE ...");
17
18       Note : VirtualTable subclasses or instances are not called directly
19       from Perl code; everything happens indirectly through SQL statements
20       within SQLite.
21

DESCRIPTION

23       This module is an abstract class for implementing SQLite virtual
24       tables, written in Perl. Such tables look like regular tables, and are
25       accessed through regular SQL instructions and regular DBI API; but the
26       implementation is done through hidden calls to a Perl class.  This is
27       the same idea as Perl's tied variables, but at the SQLite level.
28
29       The current abstract class cannot be used directly, so the synopsis
30       above is just to give a general idea. Concrete, usable classes bundled
31       with the present distribution are :
32
33       •   DBD::SQLite::VirtualTable::FileContent : implements a virtual
34           column that exposes file contents. This is especially useful in
35           conjunction with a fulltext index; see
36           DBD::SQLite::Fulltext_search.
37
38       •   DBD::SQLite::VirtualTable::PerlData : binds to a Perl array within
39           the Perl program. This can be used for simple import/export
40           operations, for debugging purposes, for joining data from different
41           sources, etc.
42
43       Other Perl virtual tables may also be published separately on CPAN.
44
45       The following chapters document the structure of the abstract class and
46       explain how to write new subclasses; this is meant for module authors,
47       not for end users. If you just need to use a virtual table module,
48       refer to that module's documentation.
49

ARCHITECTURE

51   Classes
52       A virtual table module for SQLite is implemented through a pair of
53       classes :
54
55       •   the table class implements methods for creating or connecting a
56           virtual table, for destroying it, for opening new searches, etc.
57
58       •   the cursor class implements methods for performing a specific SQL
59           statement
60
61   Methods
62       Most methods in both classes are not called directly from Perl code :
63       instead, they are callbacks, called from the sqlite kernel.  Following
64       common Perl conventions, such methods have names in uppercase.
65

TABLE METHODS

67   Class methods for registering the module
68       CREATE_MODULE
69
70         $class->CREATE_MODULE($sqlite_module_name);
71
72       Called when the client code invokes
73
74         $dbh->sqlite_create_module($sqlite_module_name => $class);
75
76       The default implementation is empty.
77
78       DESTROY_MODULE
79
80         $class->DESTROY_MODULE();
81
82       Called automatically when the database handle is disconnected.  The
83       default implementation is empty.
84
85   Class methods for creating a vtable instance
86       CREATE
87
88         $class->CREATE($dbh_ref, $module_name, $db_name, $vtab_name, @args);
89
90       Called when sqlite receives a statement
91
92         CREATE VIRTUAL TABLE $db_name.$vtab_name USING $module_name(@args)
93
94       The default implementation just calls "NEW".
95
96       CONNECT
97
98         $class->CONNECT($dbh_ref, $module_name, $db_name, $vtab_name, @args);
99
100       Called when attempting to access a virtual table that had been created
101       during previous database connection. The creation arguments were stored
102       within the sqlite database and are passed again to the CONNECT method.
103
104       The default implementation just calls "NEW".
105
106       _PREPARE_SELF
107
108         $class->_PREPARE_SELF($dbh_ref, $module_name, $db_name, $vtab_name, @args);
109
110       Prepares the datastructure for a virtual table instance.  @args is
111        just the collection of strings (comma-separated) that were given
112        within the "CREATE VIRTUAL TABLE" statement; each subclass should
113        decide what to do with this information,
114
115       The method parses @args to differentiate between options (strings of
116       shape $key=$value or $key="$value", stored in "$self->{options}"), and
117       columns (other @args, stored in "$self->{columns}"). It creates a
118       hashref with the following fields :
119
120       "dbh_ref"
121           a weak reference to the $dbh database handle (see Scalar::Util for
122           an explanation of weak references).
123
124       "module_name"
125           name of the module as declared to sqlite (not to be confounded with
126           the Perl class name).
127
128       "db_name"
129           name of the database (usuallly 'main' or 'temp'), but it may also
130           be an attached database
131
132       "vtab_name"
133           name of the virtual table
134
135       "columns"
136           arrayref of column declarations
137
138       "options"
139           hashref of option declarations
140
141       This method should not be redefined, since it performs general work
142       which is supposed to be useful for all subclasses.  Instead, subclasses
143       may override the "NEW" method.
144
145       NEW
146
147         $class->NEW($dbh_ref, $module_name, $db_name, $vtab_name, @args);
148
149       Instantiates a virtual table.
150
151   Instance methods called from the sqlite kernel
152       DROP
153
154       Called whenever a virtual table is destroyed from the database through
155       the "DROP TABLE" SQL instruction.
156
157       Just after the "DROP()" call, the Perl instance will be destroyed (and
158       will therefore automatically call the "DESTROY()" method if such a
159       method is present).
160
161       The default implementation for DROP is empty.
162
163       Note : this corresponds to the "xDestroy" method in the SQLite
164       documentation; here it was not named "DESTROY", to avoid any confusion
165       with the standard Perl method "DESTROY" for object destruction.
166
167       DISCONNECT
168
169       Called for every virtual table just before the database handle is
170       disconnected.
171
172       Just after the "DISCONNECT()" call, the Perl instance will be destroyed
173       (and will therefore automatically call the "DESTROY()" method if such a
174       method is present).
175
176       The default implementation for DISCONNECT is empty.
177
178       VTAB_TO_DECLARE
179
180       This method is called automatically just after "CREATE" or "CONNECT",
181       to register the columns of the virtual table within the sqlite kernel.
182       The method should return a string containing a SQL "CREATE TABLE"
183       statement; but only the column declaration parts will be considered.
184       Columns may be declared with the special keyword "HIDDEN", which means
185       that they are used internally for the the virtual table implementation,
186       and are not visible to users -- see
187       <http://sqlite.org/c3ref/declare_vtab.html> and
188       <http://www.sqlite.org/vtab.html#hiddencol> for detailed explanations.
189
190       The default implementation returns:
191
192         CREATE TABLE $self->{vtab_name}(@{$self->{columns}})
193
194       BEST_INDEX
195
196         my $index_info = $vtab->BEST_INDEX($constraints, $order_by)
197
198       This is the most complex method to redefined in subclasses.  This
199       method will be called at the beginning of a new query on the virtual
200       table; the job of the method is to assemble some information that will
201       be used
202
203       a)  by the sqlite kernel to decide about the best search strategy
204
205       b)  by the cursor "FILTER" method to produce the desired subset of rows
206           from the virtual table.
207
208       By calling this method, the SQLite core is saying to the virtual table
209       that it needs to access some subset of the rows in the virtual table
210       and it wants to know the most efficient way to do that access. The
211       "BEST_INDEX" method replies with information that the SQLite core can
212       then use to conduct an efficient search of the virtual table.
213
214       The method takes as input a list of $constraints and a list of
215       $order_by instructions. It returns a hashref of indexing properties,
216       described below; furthermore, the method also adds supplementary
217       information within the input $constraints.  Detailed explanations are
218       given in <http://sqlite.org/vtab.html#xbestindex>.
219
220       Input constraints
221
222       Elements of the $constraints arrayref correspond to specific clauses of
223       the "WHERE ..." part of the SQL query.  Each constraint is a hashref
224       with keys :
225
226       "col"
227           the integer index of the column on the left-hand side of the
228           constraint
229
230       "op"
231           the comparison operator, expressed as string containing '=', '>',
232           '>=', '<', '<=' or 'MATCH'.
233
234       "usable"
235           a boolean indicating if that constraint is usable; some constraints
236           might not be usable because of the way tables are ordered in a
237           join.
238
239       The $constraints arrayref is used both for input and for output.  While
240       iterating over the array, the method should add the following keys into
241       usable constraints :
242
243       "argvIndex"
244           An index into the @values array that will be passed to the cursor's
245           "FILTER" method. In other words, if the current constraint
246           corresponds to the SQL fragment "WHERE ... AND foo < 123 ...", and
247           the corresponding "argvIndex" takes value 5, this means that the
248           "FILTER" method will receive 123 in $values[5].
249
250       "omit"
251           A boolean telling to the sqlite core that it can safely omit to
252           double check that constraint before returning the resultset to the
253           calling program; this means that the FILTER method has fulfilled
254           the filtering job on that constraint and there is no need to do any
255           further checking.
256
257       The "BEST_INDEX" method will not necessarily receive all constraints
258       from the SQL "WHERE" clause : for example a constraint like "col1 <
259       col2 + col3" cannot be handled at this level.  Furthemore, the
260       "BEST_INDEX" might decide to ignore some of the received constraints.
261       This is why a second pass over the results will be performed by the
262       sqlite core.
263
264       "order_by" input information
265
266       The $order_by arrayref corresponds to the "ORDER BY" clauses in the SQL
267       query. Each entry is a hashref with keys :
268
269       "col"
270           the integer index of the column being ordered
271
272       "desc"
273           a boolean telling of the ordering is DESCending or ascending
274
275       This information could be used by some subclasses for optimizing the
276       query strategfy; but usually the sqlite core will perform another
277       sorting pass once all results are gathered.
278
279       Hashref information returned by BEST_INDEX
280
281       The method should return a hashref with the following keys :
282
283       "idxNum"
284           An arbitrary integer associated with that index; this information
285           will be passed back to "FILTER".
286
287       "idxStr"
288           An arbitrary str associated with that index; this information will
289           be passed back to "FILTER".
290
291       "orderByConsumed"
292           A boolean telling the sqlite core if the $order_by information has
293           been taken into account or not.
294
295       "estimatedCost"
296           A float that should be set to the estimated number of disk access
297           operations required to execute this query against the virtual
298           table. The SQLite core will often call BEST_INDEX multiple times
299           with different constraints, obtain multiple cost estimates, then
300           choose the query plan that gives the lowest estimate.
301
302       "estimatedRows"
303           An integer giving the estimated number of rows returned by that
304           query.
305
306       OPEN
307
308       Called to instantiate a new cursor.  The default implementation appends
309       "::Cursor" to the current classname and calls "NEW()" within that
310       cursor class.
311
312       _SQLITE_UPDATE
313
314       This is the dispatch method implementing the "xUpdate()" callback for
315       virtual tables. The default implementation applies the algorithm
316       described in <http://sqlite.org/vtab.html#xupdate> to decide to call
317       "INSERT", "DELETE" or "UPDATE"; so there is no reason to override this
318       method in subclasses.
319
320       INSERT
321
322         my $rowid = $vtab->INSERT($new_rowid, @values);
323
324       This method should be overridden in subclasses to implement insertion
325       of a new row into the virtual table.  The size of the @values array
326       corresponds to the number of columns declared through
327       "VTAB_TO_DECLARE".  The $new_rowid may be explicitly given, or it may
328       be "undef", in which case the method must compute a new id and return
329       it as the result of the method call.
330
331       DELETE
332
333         $vtab->INSERT($old_rowid);
334
335       This method should be overridden in subclasses to implement deletion of
336       a row from the virtual table.
337
338       UPDATE
339
340         $vtab->UPDATE($old_rowid, $new_rowid, @values);
341
342       This method should be overridden in subclasses to implement a row
343       update within the virtual table. Usually $old_rowid is equal to
344       $new_rowid, which is a regular update; however, the rowid could be
345       changed from a SQL statement such as
346
347         UPDATE table SET rowid=rowid+1 WHERE ...;
348
349       FIND_FUNCTION
350
351         $vtab->FIND_FUNCTION($num_args, $func_name);
352
353       When a function uses a column from a virtual table as its first
354       argument, this method is called to see if the virtual table would like
355       to overload the function. Parameters are the number of arguments to the
356       function, and the name of the function. If no overloading is desired,
357       this method should return false. To overload the function, this method
358       should return a coderef to the function implementation.
359
360       Each virtual table keeps a cache of results from FIND_FUNCTION calls,
361       so the method will be called only once for each pair "($num_args,
362       $func_name)".
363
364       BEGIN_TRANSACTION
365
366       Called to begin a transaction on the virtual table.
367
368       SYNC_TRANSACTION
369
370       Called to signal the start of a two-phase commit on the virtual table.
371
372       SYNC_TRANSACTION
373
374       Called to commit a virtual table transaction.
375
376       ROLLBACK_TRANSACTION
377
378       Called to rollback a virtual table transaction.
379
380       RENAME
381
382         $vtab->RENAME($new_name)
383
384       Called to rename a virtual table.
385
386       SAVEPOINT
387
388         $vtab->SAVEPOINT($savepoint)
389
390       Called to signal the virtual table to save its current state at
391       savepoint $savepoint (an integer).
392
393       ROLLBACK_TO
394
395         $vtab->ROLLBACK_TO($savepoint)
396
397       Called to signal the virtual table to return to the state $savepoint.
398       This will invalidate all savepoints with values greater than
399       $savepoint.
400
401       RELEASE
402
403         $vtab->RELEASE($savepoint)
404
405       Called to invalidate all savepoints with values greater or equal to
406       $savepoint.
407
408   Utility instance methods
409       Methods in this section are in lower case, because they are not called
410       directly from the sqlite kernel; these are utility methods to be called
411       from other methods described above.
412
413       dbh
414
415       This method returns the database handle ($dbh) associated with the
416       current virtual table.
417

CURSOR METHODS

419   Class methods
420       NEW
421
422         my $cursor = $cursor_class->NEW($vtable, @args)
423
424       Instantiates a new cursor.  The default implementation just returns a
425       blessed hashref with keys "vtable" and "args".
426
427   Instance methods
428       FILTER
429
430         $cursor->FILTER($idxNum, $idxStr, @values);
431
432       This method begins a search of a virtual table.
433
434       The $idxNum and $idxStr arguments correspond to values returned by
435       "BEST_INDEX" for the chosen index. The specific meanings of those
436       values are unimportant to SQLite, as long as "BEST_INDEX" and "FILTER"
437       agree on what that meaning is.
438
439       The "BEST_INDEX" method may have requested the values of certain
440       expressions using the "argvIndex" values of the $constraints list.
441       Those values are passed to "FILTER" through the @values array.
442
443       If the virtual table contains one or more rows that match the search
444       criteria, then the cursor must be left point at the first row.
445       Subsequent calls to "EOF" must return false. If there are no rows
446       match, then the cursor must be left in a state that will cause "EOF" to
447       return true. The SQLite engine will use the "COLUMN" and "ROWID"
448       methods to access that row content. The "NEXT" method will be used to
449       advance to the next row.
450
451       EOF
452
453       This method must return false if the cursor currently points to a valid
454       row of data, or true otherwise. This method is called by the SQL engine
455       immediately after each "FILTER" and "NEXT" invocation.
456
457       NEXT
458
459       This method advances the cursor to the next row of a result set
460       initiated by "FILTER". If the cursor is already pointing at the last
461       row when this method is called, then the cursor no longer points to
462       valid data and a subsequent call to the "EOF" method must return true.
463       If the cursor is successfully advanced to another row of content, then
464       subsequent calls to "EOF" must return false.
465
466       COLUMN
467
468         my $value = $cursor->COLUMN($idxCol);
469
470       The SQLite core invokes this method in order to find the value for the
471       N-th column of the current row. N is zero-based so the first column is
472       numbered 0.
473
474       ROWID
475
476         my $value = $cursor->ROWID;
477
478       Returns the rowid of row that the cursor is currently pointing at.
479

SEE ALSO

481       SQLite::VirtualTable is another module for virtual tables written in
482       Perl, but designed for the reverse use case : instead of starting a
483       Perl program, and embedding the SQLite library into it, the intended
484       use is to start an sqlite program, and embed the Perl interpreter into
485       it.
486

AUTHOR

488       Laurent Dami <dami@cpan.org>
489
491       Copyright Laurent Dami, 2014.
492
493       Parts of the code are borrowed from SQLite::VirtualTable, copyright (C)
494       2006, 2009 by Qindel Formacion y Servicios, S. L.
495
496       This library is free software; you can redistribute it and/or modify it
497       under the same terms as Perl itself.
498
499
500
501perl v5.34.0                      2022-01-21      DBD::SQLite::VirtualTable(3)
Impressum