1DBD::SQLite::VirtualTabUlsee:r:PCeornltDraitbau(t3e)d PeDrBlD:D:oScQuLmietnet:a:tViiorntualTable::PerlData(3)
2
6 DBD::SQLite::VirtualTable::PerlData -- virtual table hooked to Perl
7 data
8
10 Within Perl :
11 $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
13 Then, within SQL :
15 CREATE VIRTUAL TABLE atbl USING perl(foo, bar, etc,
17 arrayrefs="some::global::var::aref")
18 CREATE VIRTUAL TABLE htbl USING perl(foo, bar, etc,
20 hashrefs="some::global::var::href")
21 CREATE VIRTUAL TABLE ctbl USING perl(single_col
23 colref="some::global::var::ref")
24 SELECT foo, bar FROM atbl WHERE ...;
27
29 A "PerlData" virtual table is a database view on some datastructure
30 within a Perl program. The data can be read or modified both from SQL
31 and from Perl. This is useful for simple import/export operations, for
32 debugging purposes, for joining data from different sources, etc.
33
35 Parameters for creating a "PerlData" virtual table are specified within
36 the "CREATE VIRTUAL TABLE" statement, mixed with regular column
37 declarations, but with an '=' sign.
38 The only authorized (and mandatory) parameter is the one that specifies
40 the Perl datastructure to which the virtual table is bound. It must be
41 given as the fully qualified name of a global variable; the parameter
42 can be one of three different kinds :
43 "arrayrefs"
45 arrayref that contains an arrayref for each row. Each such row
46 will have a size equivalent to the number of columns declared for
47 the virtual table.
48 "hashrefs"
50 arrayref that contains a hashref for each row. Keys in each
51 hashref should correspond to the columns declared for the virtual
52 table.
53 "colref"
55 arrayref that contains a single scalar for each row; obviously,
56 this is a single-column virtual table.
57
59 Common part of all examples : declaring the module
60 In all examples below, the common part is that the Perl program should
61 connect to the database and then declare the "PerlData" virtual table
62 module, like this
63 # connect to the database
65 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '',
66 {RaiseError => 1, AutoCommit => 1});
67 # or any other options suitable to your needs
68 # register the module
70 $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
71 Then create a global arrayref variable, using "our" instead of "my", so
73 that the variable is stored in the symbol table of the enclosing
74 module.
75 package Foo::Bar; # could as well be just "main"
77 our $rows = [ ... ];
78 Finally, create the virtual table and bind it to the global variable
80 (here we assume that @$rows contains arrayrefs) :
81 $dbh->do('CREATE VIRTUAL TABLE temp.vtab'
83 .' USING perl(col1 INT, col2 TEXT, etc,
84 arrayrefs="Foo::Bar::rows');
85 In most cases, the virtual table will be for temporary use, which is
87 the reason why this example prepends "temp." in front of the table name
88 : this tells SQLite to cleanup that table when the database handle will
89 be disconnected, without the need to emit an explicit DROP statement.
90 Column names (and optionally their types) are specified in the virtual
92 table declaration, just like for any regular table.
93 Arrayref example : statistics from files
95 Let's suppose we want to perform some searches over a collection of
96 files, where search constraints may be based on some of the fields
97 returned by stat, such as the size of the file or its last modify time.
98 Here is a way to do it with a virtual table :
99 my @files = ... ; # list of files to inspect
101 # apply the L<stat> function to each file
103 our $file_stats = [ map { [ $_, stat $_ ] } @files];
104 # create a temporary virtual table
106 $dbh->do(<<"");
107 CREATE VIRTUAL TABLE temp.file_stats'
108 USING perl(path, dev, ino, mode, nlink, uid, gid, rdev, size,
109 atime, mtime, ctime, blksize, blocks,
110 arrayrefs="main::file_stats");
111 # search files
113 my $sth = $dbh->prepare(<<"");
114 SELECT * FROM file_stats
115 WHERE mtime BETWEEN ? AND ?
116 AND uid IN (...)
117 Hashref example : unicode characters
119 Given any unicode character, the "charinfo" in Unicode::UCD function
120 returns a hashref with various bits of information about that
121 character. So this can be exploited in a virtual table :
122 use Unicode::UCD 'charinfo';
124 our $chars = [map {charinfo($_)} 0x300..0x400]; # arbitrary subrange
125 # create a temporary virtual table
127 $dbh->do(<<"");
128 CREATE VIRTUAL TABLE charinfo USING perl(
129 code, name, block, script, category,
130 hashrefs="main::chars"
131 )
132 # search characters
134 my $sth = $dbh->prepare(<<"");
135 SELECT * FROM charinfo
136 WHERE script='Greek'
137 AND name LIKE '%SIGMA%'
138 Colref example: SELECT WHERE ... IN ...
140 Note: The idea for the following example is borrowed from the
141 "test_intarray.h" file in SQLite's source
142 (<http://www.sqlite.org/src>).
143 A "colref" virtual table is designed to facilitate using an array of
145 values as the right-hand side of an IN operator. The usual syntax for
146 IN is to prepare a statement like this:
147 SELECT * FROM table WHERE x IN (?,?,?,...,?);
149 and then bind individual values to each of the ? slots; but this has
151 the disadvantage that the number of values must be known in advance.
152 Instead, we can store values in a Perl array, bind that array to a
153 virtual table, and then write a statement like this
154 SELECT * FROM table WHERE x IN perl_array;
156 Here is how such a program would look like :
158 # connect to the database
160 my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '',
161 {RaiseError => 1, AutoCommit => 1});
162 # Declare a global arrayref containing the values. Here we assume
164 # they are taken from @ARGV, but any other datasource would do.
165 # Note the use of "our" instead of "my".
166 our $values = \@ARGV;
167 # register the module and declare the virtual table
169 $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
170 $dbh->do('CREATE VIRTUAL TABLE temp.intarray'
171 .' USING perl(i INT, colref="main::values');
172 # now we can SELECT from another table, using the intarray as a constraint
174 my $sql = "SELECT * FROM some_table WHERE some_col IN intarray";
175 my $result = $dbh->selectall_arrayref($sql);
176 Beware that the virtual table is read-write, so the statement below
178 would push 99 into @ARGV !
179 INSERT INTO intarray VALUES (99);
181
183 Laurent Dami <dami@cpan.org>
184
186 Copyright Laurent Dami, 2014.
187 This library is free software; you can redistribute it and/or modify it
189 under the same terms as Perl itself.
190perl v5.30.0 2019-08D-B1D3::SQLite::VirtualTable::PerlData(3)