$dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
Then, within SQL :
CREATE VIRTUAL TABLE atbl USING perl(foo, bar, etc, arrayrefs="some::global::var::aref") CREATE VIRTUAL TABLE htbl USING perl(foo, bar, etc, hashrefs="some::global::var::href") CREATE VIRTUAL TABLE ctbl USING perl(single_col colref="some::global::var::ref") SELECT foo, bar FROM atbl WHERE ...;
The only authorized (and mandatory) parameter is the one that specifies the Perl datastructure to which the virtual table is bound. It must be given as the fully qualified name of a global variable; the parameter can be one of three different kinds :
# connect to the database my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '', {RaiseError => 1, AutoCommit => 1}); # or any other options suitable to your needs # register the module $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
Then create a global arrayref variable, using "our" instead of "my", so that the variable is stored in the symbol table of the enclosing module.
package Foo::Bar; # could as well be just "main" our $rows = [ ... ];
Finally, create the virtual table and bind it to the global variable (here we assume that @$rows contains arrayrefs) :
$dbh->do('CREATE VIRTUAL TABLE temp.vtab' .' USING perl(col1 INT, col2 TEXT, etc, arrayrefs="Foo::Bar::rows');
In most cases, the virtual table will be for temporary use, which is the reason why this example prepends "temp." in front of the table name : this tells SQLite to cleanup that table when the database handle will be disconnected, without the need to emit an explicit DROP statement.
Column names (and optionally their types) are specified in the virtual table declaration, just like for any regular table.
my @files = ... ; # list of files to inspect # apply the L<stat> function to each file our $file_stats = [ map { [ $_, stat $_ ] } @files]; # create a temporary virtual table $dbh->do(<<""); CREATE VIRTUAL TABLE temp.file_stats' USING perl(path, dev, ino, mode, nlink, uid, gid, rdev, size, atime, mtime, ctime, blksize, blocks, arrayrefs="main::file_stats"); # search files my $sth = $dbh->prepare(<<""); SELECT * FROM file_stats WHERE mtime BETWEEN ? AND ? AND uid IN (...)
use Unicode::UCD 'charinfo'; our $chars = [map {charinfo($_)} 0x300..0x400]; # arbitrary subrange # create a temporary virtual table $dbh->do(<<""); CREATE VIRTUAL TABLE charinfo USING perl( code, name, block, script, category, hashrefs="main::chars" ) # search characters my $sth = $dbh->prepare(<<""); SELECT * FROM charinfo WHERE script='Greek' AND name LIKE '%SIGMA%'
A "colref" virtual table is designed to facilitate using an array of values as the right-hand side of an IN operator. The usual syntax for IN is to prepare a statement like this:
SELECT * FROM table WHERE x IN (?,?,?,...,?);
and then bind individual values to each of the ? slots; but this has the disadvantage that the number of values must be known in advance. Instead, we can store values in a Perl array, bind that array to a virtual table, and then write a statement like this
SELECT * FROM table WHERE x IN perl_array;
Here is how such a program would look like :
# connect to the database my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '', {RaiseError => 1, AutoCommit => 1}); # Declare a global arrayref containing the values. Here we assume # they are taken from @ARGV, but any other datasource would do. # Note the use of "our" instead of "my". our $values = \@ARGV; # register the module and declare the virtual table $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData"); $dbh->do('CREATE VIRTUAL TABLE temp.intarray' .' USING perl(i INT, colref="main::values'); # now we can SELECT from another table, using the intarray as a constraint my $sql = "SELECT * FROM some_table WHERE some_col IN intarray"; my $result = $dbh->selectall_arrayref($sql);
Beware that the virtual table is read-write, so the statement below would push 99 into @ARGV !
INSERT INTO intarray VALUES (99);
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.