1DBD::SQLite::VirtualTabUlsee:r:PCeornltDraitbau(t3e)d PeDrBlD:D:oScQuLmietnet:a:tViiorntualTable::PerlData(3)
2
3
4

NAME

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

SYNOPSIS

10       Within Perl :
11
12         $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
13
14       Then, within SQL :
15
16         CREATE VIRTUAL TABLE atbl USING perl(foo, bar, etc,
17                                              arrayrefs="some::global::var::aref")
18
19         CREATE VIRTUAL TABLE htbl USING perl(foo, bar, etc,
20                                              hashrefs="some::global::var::href")
21
22         CREATE VIRTUAL TABLE ctbl USING perl(single_col
23                                              colref="some::global::var::ref")
24
25
26         SELECT foo, bar FROM atbl WHERE ...;
27

DESCRIPTION

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

PARAMETERS

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
39       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
44       "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
49       "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
54       "colref"
55           arrayref that contains a single scalar for each row; obviously,
56           this is a single-column virtual table.
57

USAGE

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
64         # 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
69         # register the module
70         $dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
71
72       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
76         package Foo::Bar; # could as well be just "main"
77         our $rows = [ ... ];
78
79       Finally, create the virtual table and bind it to the global variable
80       (here we assume that @$rows contains arrayrefs) :
81
82         $dbh->do('CREATE VIRTUAL TABLE temp.vtab'
83                 .'  USING perl(col1 INT, col2 TEXT, etc,
84                                arrayrefs="Foo::Bar::rows');
85
86       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
91       Column names (and optionally their types) are specified in the virtual
92       table declaration, just like for any regular table.
93
94   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
100         my @files = ... ; # list of files to inspect
101
102         # apply the L<stat> function to each file
103         our $file_stats = [ map { [ $_, stat $_ ] } @files];
104
105         # 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
112         # search files
113         my $sth = $dbh->prepare(<<"");
114           SELECT * FROM file_stats
115             WHERE mtime BETWEEN ? AND ?
116               AND uid IN (...)
117
118   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
123         use Unicode::UCD 'charinfo';
124         our $chars = [map {charinfo($_)} 0x300..0x400]; # arbitrary subrange
125
126         # 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
133         # search characters
134         my $sth = $dbh->prepare(<<"");
135           SELECT * FROM charinfo
136            WHERE script='Greek'
137              AND name LIKE '%SIGMA%'
138
139   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
144       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
148           SELECT * FROM table WHERE x IN (?,?,?,...,?);
149
150       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
155           SELECT * FROM table WHERE x IN perl_array;
156
157       Here is how such a program would look like :
158
159         # connect to the database
160         my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '',
161                                 {RaiseError => 1, AutoCommit => 1});
162
163         # 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
168         # 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
173         # 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
177       Beware that the virtual table is read-write, so the statement below
178       would push 99 into @ARGV !
179
180         INSERT INTO intarray VALUES (99);
181

AUTHOR

183       Laurent Dami <dami@cpan.org>
184
186       Copyright Laurent Dami, 2014.
187
188       This library is free software; you can redistribute it and/or modify it
189       under the same terms as Perl itself.
190
191
192
193perl v5.34.0                      2022-01D-B2D1::SQLite::VirtualTable::PerlData(3)
Impressum