1DBD::SQLite::VirtualTabUlsee(r3)Contributed Perl DocumenDtBaDt:i:oSnQLite::VirtualTable(3)
2
6 DBD::SQLite::VirtualTable -- SQLite virtual tables implemented in Perl
7
9 # register the virtual table module within sqlite
10 $dbh->sqlite_create_module(mod_name => "DBD::SQLite::VirtualTable::Subclass");
11 # create a virtual table
13 $dbh->do("CREATE VIRTUAL TABLE vtbl USING mod_name(arg1, arg2, ...)")
14 # use it as any regular table
16 my $sth = $dbh->prepare("SELECT * FROM vtbl WHERE ...");
17 Note : VirtualTable subclasses or instances are not called directly
19 from Perl code; everything happens indirectly through SQL statements
20 within SQLite.
21
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 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 • 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 • 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 Other Perl virtual tables may also be published separately on CPAN.
44 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
51 Classes
52 A virtual table module for SQLite is implemented through a pair of
53 classes :
54 • the table class implements methods for creating or connecting a
56 virtual table, for destroying it, for opening new searches, etc.
57 • the cursor class implements methods for performing a specific SQL
59 statement
60 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
67 Class methods for registering the module
68 CREATE_MODULE
69 $class->CREATE_MODULE($sqlite_module_name);
71 Called when the client code invokes
73 $dbh->sqlite_create_module($sqlite_module_name => $class);
75 The default implementation is empty.
77 DESTROY_MODULE
79 $class->DESTROY_MODULE();
81 Called automatically when the database handle is disconnected. The
83 default implementation is empty.
84 Class methods for creating a vtable instance
86 CREATE
87 $class->CREATE($dbh_ref, $module_name, $db_name, $vtab_name, @args);
89 Called when sqlite receives a statement
91 CREATE VIRTUAL TABLE $db_name.$vtab_name USING $module_name(@args)
93 The default implementation just calls "NEW".
95 CONNECT
97 $class->CONNECT($dbh_ref, $module_name, $db_name, $vtab_name, @args);
99 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 The default implementation just calls "NEW".
105 _PREPARE_SELF
107 $class->_PREPARE_SELF($dbh_ref, $module_name, $db_name, $vtab_name, @args);
109 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 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 "dbh_ref"
121 a weak reference to the $dbh database handle (see Scalar::Util for
122 an explanation of weak references).
123 "module_name"
125 name of the module as declared to sqlite (not to be confounded with
126 the Perl class name).
127 "db_name"
129 name of the database (usuallly 'main' or 'temp'), but it may also
130 be an attached database
131 "vtab_name"
133 name of the virtual table
134 "columns"
136 arrayref of column declarations
137 "options"
139 hashref of option declarations
140 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 NEW
146 $class->NEW($dbh_ref, $module_name, $db_name, $vtab_name, @args);
148 Instantiates a virtual table.
150 Instance methods called from the sqlite kernel
152 DROP
153 Called whenever a virtual table is destroyed from the database through
155 the "DROP TABLE" SQL instruction.
156 Just after the DROP() call, the Perl instance will be destroyed (and
158 will therefore automatically call the DESTROY() method if such a method
159 is present).
160 The default implementation for DROP is empty.
162 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 DISCONNECT
168 Called for every virtual table just before the database handle is
170 disconnected.
171 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 The default implementation for DISCONNECT is empty.
177 VTAB_TO_DECLARE
179 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 The default implementation returns:
191 CREATE TABLE $self->{vtab_name}(@{$self->{columns}})
193 BEST_INDEX
195 my $index_info = $vtab->BEST_INDEX($constraints, $order_by)
197 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 a) by the sqlite kernel to decide about the best search strategy
204 b) by the cursor "FILTER" method to produce the desired subset of rows
206 from the virtual table.
207 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 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 Input constraints
221 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 "col"
227 the integer index of the column on the left-hand side of the
228 constraint
229 "op"
231 the comparison operator, expressed as string containing '=', '>',
232 '>=', '<', '<=' or 'MATCH'.
233 "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 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 "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 "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 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 "order_by" input information
265 The $order_by arrayref corresponds to the "ORDER BY" clauses in the SQL
267 query. Each entry is a hashref with keys :
268 "col"
270 the integer index of the column being ordered
271 "desc"
273 a boolean telling of the ordering is DESCending or ascending
274 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 Hashref information returned by BEST_INDEX
280 The method should return a hashref with the following keys :
282 "idxNum"
284 An arbitrary integer associated with that index; this information
285 will be passed back to "FILTER".
286 "idxStr"
288 An arbitrary str associated with that index; this information will
289 be passed back to "FILTER".
290 "orderByConsumed"
292 A boolean telling the sqlite core if the $order_by information has
293 been taken into account or not.
294 "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 "estimatedRows"
303 An integer giving the estimated number of rows returned by that
304 query.
305 OPEN
307 Called to instantiate a new cursor. The default implementation appends
309 "::Cursor" to the current classname and calls NEW() within that cursor
310 class.
311 _SQLITE_UPDATE
313 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 INSERT
321 my $rowid = $vtab->INSERT($new_rowid, @values);
323 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 DELETE
332 $vtab->INSERT($old_rowid);
334 This method should be overridden in subclasses to implement deletion of
336 a row from the virtual table.
337 UPDATE
339 $vtab->UPDATE($old_rowid, $new_rowid, @values);
341 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 UPDATE table SET rowid=rowid+1 WHERE ...;
348 FIND_FUNCTION
350 $vtab->FIND_FUNCTION($num_args, $func_name);
352 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 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 BEGIN_TRANSACTION
365 Called to begin a transaction on the virtual table.
367 SYNC_TRANSACTION
369 Called to signal the start of a two-phase commit on the virtual table.
371 SYNC_TRANSACTION
373 Called to commit a virtual table transaction.
375 ROLLBACK_TRANSACTION
377 Called to rollback a virtual table transaction.
379 RENAME
381 $vtab->RENAME($new_name)
383 Called to rename a virtual table.
385 SAVEPOINT
387 $vtab->SAVEPOINT($savepoint)
389 Called to signal the virtual table to save its current state at
391 savepoint $savepoint (an integer).
392 ROLLBACK_TO
394 $vtab->ROLLBACK_TO($savepoint)
396 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 RELEASE
402 $vtab->RELEASE($savepoint)
404 Called to invalidate all savepoints with values greater or equal to
406 $savepoint.
407 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 dbh
414 This method returns the database handle ($dbh) associated with the
416 current virtual table.
417
419 Class methods
420 NEW
421 my $cursor = $cursor_class->NEW($vtable, @args)
423 Instantiates a new cursor. The default implementation just returns a
425 blessed hashref with keys "vtable" and "args".
426 Instance methods
428 FILTER
429 $cursor->FILTER($idxNum, $idxStr, @values);
431 This method begins a search of a virtual table.
433 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 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 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 EOF
452 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 NEXT
458 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 COLUMN
467 my $value = $cursor->COLUMN($idxCol);
469 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 ROWID
475 my $value = $cursor->ROWID;
477 Returns the rowid of row that the cursor is currently pointing at.
479
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
488 Laurent Dami <dami@cpan.org>
489
491 Copyright Laurent Dami, 2014.
492 Parts of the code are borrowed from SQLite::VirtualTable, copyright (C)
494 2006, 2009 by Qindel Formacion y Servicios, S. L.
495 This library is free software; you can redistribute it and/or modify it
497 under the same terms as Perl itself.
498perl v5.36.0 2023-01-20 DBD::SQLite::VirtualTable(3)