MAN page from Fedora 23 perl-DBD-SQLite-1.48-2.fc23.i686.rpm

DBD::SQLite::VirtualTable::PerlData

Section: User Contributed Perl Documentation (3)
Updated: 2014-11-26
Index

NAME

DBD::SQLite::VirtualTable::PerlData -- virtual table hooked to Perl data

SYNOPSIS

Within Perl :

  $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 ...;

DESCRIPTION

A "PerlData" virtual table is a database view on some datastructurewithin a Perl program. The data can be read or modified both from SQLand from Perl. This is useful for simple import/exportoperations, for debugging purposes, for joining data from differentsources, etc.

PARAMETERS

Parameters for creating a "PerlData" virtual table are specifiedwithin the "CREATE VIRTUAL TABLE" statement, mixed with regularcolumn declarations, but with an '=' sign.

The only authorized (and mandatory) parameter is the one thatspecifies 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 :

arrayrefs: arrayref that contains an arrayref for each row.Each such row will have a size equivalent to the numberof columns declared for the virtual table.
hashrefs: arrayref that contains a hashref for each row.Keys in each hashref should correspond to thecolumns declared for the virtual table.
colref: arrayref that contains a single scalar for each row;obviously, this is a single-column virtual table.

USAGE

Common part of all examples : declaring the module

In all examples below, the common part is that the Perlprogram should connect to the database and then declare the"PerlData" virtual table module, like this

  # 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 globalvariable (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 isthe reason why this example prepends "temp." in front of the tablename : this tells SQLite to cleanup that table when the databasehandle will be disconnected, without the need to emit an explicit DROPstatement.

Column names (and optionally their types) are specified in thevirtual table declaration, just like for any regular table.

Arrayref example : statistics from files

Let's suppose we want to perform some searches over a collection offiles, where search constraints may be based on some of the fieldsreturned by stat, such as the size of the file or its last modifytime. Here is a way to do it with a virtual 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 (...)

Hashref example : unicode characters

Given any unicode character, the ``charinfo'' in Unicode::UCD functionreturns a hashref with various bits of information about that character.So this can be exploited in a virtual table :

  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%'

Colref example: SELECT WHERE ... IN ...

Note: The idea for the following example is borrowed from the"test_intarray.h" file in SQLite's source(<http://www.sqlite.org/src>).

A "colref" virtual table is designed to facilitate using anarray of values as the right-hand side of an IN operator. Theusual 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 hasthe disadvantage that the number of values must be known inadvance. Instead, we can store values in a Perl array, bind that arrayto 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 belowwould push 99 into @ARGV !

  INSERT INTO intarray VALUES (99);

AUTHOR

Laurent Dami <damiAATTcpan.org>

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modifyit under the same terms as Perl itself.

Index

Common part of all examples : declaring the module
Arrayref example : statistics from files
Hashref example : unicode characters
Colref example: SELECT WHERE ... IN ...

AUTHOR

COPYRIGHT AND LICENSE

This document was created byman2html,using the manual pages.