Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DBD::SQLite::VirtualTaUser:Contributed PDBD::SQLite::VirtualTable::PerlData(3)

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

       Within Perl :

	 $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");

       Then, within SQL	:

	 CREATE	VIRTUAL	TABLE atbl USING perl(foo, bar,	etc,

	 CREATE	VIRTUAL	TABLE htbl USING perl(foo, bar,	etc,

	 CREATE	VIRTUAL	TABLE ctbl USING perl(single_col

	 SELECT	foo, bar FROM atbl WHERE ...;

       A "PerlData" virtual table is a database	view on	some datastructure
       within a	Perl program. The data can be read or modified both from SQL
       and from	Perl. This is useful for simple	import/export operations, for
       debugging purposes, for joining data from different sources, etc.

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

       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 :

	   arrayref that contains an arrayref for each row.  Each such row
	   will	have a size equivalent to the number of	columns	declared for
	   the virtual table.

	   arrayref that contains a hashref for	each row.  Keys	in each
	   hashref should correspond to	the columns declared for the virtual

	   arrayref that contains a single scalar for each row;	obviously,
	   this	is a single-column virtual table.

   Common part of all examples : declaring the module
       In all examples below, the common part is that the Perl program 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

	 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,

       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.

   Arrayref example : statistics from files
       Let's suppose we	want to	perform	some searches over a collection	of
       files, where search constraints may be based on some of the fields
       returned	by stat, such as the size of the file or its last modify time.
       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
	    CREATE VIRTUAL TABLE temp.file_stats'
	       USING perl(path,	dev, ino, mode,	nlink, uid, gid, rdev, size,
				atime, mtime, ctime, blksize, blocks,

	 # 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 function
       returns 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
	     code, name, block,	script,	category,

	 # 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

       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);

       Laurent Dami <>

       Copyright Laurent Dami, 2014.

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

perl v5.32.1			  2019-0DBD::SQLite::VirtualTable::PerlData(3)


Want to link to this manual page? Use this URL:

home | help