{
    "mode": "perldoc",
    "parameter": "DBD::SQLite::VirtualTable::PerlData",
    "section": "",
    "url": "https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3ASQLite%3A%3AVirtualTable%3A%3APerlData/json",
    "generated": "2026-06-09T12:54:24Z",
    "synopsis": "Within Perl :\n$dbh->sqlitecreatemodule(perl => \"DBD::SQLite::VirtualTable::PerlData\");\nThen, within SQL :\nCREATE VIRTUAL TABLE atbl USING perl(foo, bar, etc,\narrayrefs=\"some::global::var::aref\")\nCREATE VIRTUAL TABLE htbl USING perl(foo, bar, etc,\nhashrefs=\"some::global::var::href\")\nCREATE VIRTUAL TABLE ctbl USING perl(singlecol\ncolref=\"some::global::var::ref\")\nSELECT foo, bar FROM atbl WHERE ...;",
    "sections": {
        "NAME": {
            "content": "DBD::SQLite::VirtualTable::PerlData -- virtual table hooked to Perl data\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "Within Perl :\n\n$dbh->sqlitecreatemodule(perl => \"DBD::SQLite::VirtualTable::PerlData\");\n\nThen, within SQL :\n\nCREATE VIRTUAL TABLE atbl USING perl(foo, bar, etc,\narrayrefs=\"some::global::var::aref\")\n\nCREATE VIRTUAL TABLE htbl USING perl(foo, bar, etc,\nhashrefs=\"some::global::var::href\")\n\nCREATE VIRTUAL TABLE ctbl USING perl(singlecol\ncolref=\"some::global::var::ref\")\n\n\nSELECT foo, bar FROM atbl WHERE ...;\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "A \"PerlData\" virtual table is a database view on some datastructure within a Perl program. The\ndata can be read or modified both from SQL and from Perl. This is useful for simple\nimport/export operations, for debugging purposes, for joining data from different sources, etc.\n",
            "subsections": []
        },
        "PARAMETERS": {
            "content": "Parameters for creating a \"PerlData\" virtual table are specified within the \"CREATE VIRTUAL\nTABLE\" statement, mixed with regular column declarations, but with an '=' sign.\n\nThe only authorized (and mandatory) parameter is the one that specifies the Perl datastructure\nto which the virtual table is bound. It must be given as the fully qualified name of a global\nvariable; the parameter can be one of three different kinds :\n\n\"arrayrefs\"\narrayref that contains an arrayref for each row. Each such row will have a size equivalent\nto the number of columns declared for the virtual table.\n\n\"hashrefs\"\narrayref that contains a hashref for each row. Keys in each hashref should correspond to the\ncolumns declared for the virtual table.\n\n\"colref\"\narrayref that contains a single scalar for each row; obviously, this is a single-column\nvirtual table.\n",
            "subsections": []
        },
        "USAGE": {
            "content": "",
            "subsections": [
                {
                    "name": "Common part of all examples : declaring the module",
                    "content": "In all examples below, the common part is that the Perl program should connect to the database\nand then declare the \"PerlData\" virtual table module, like this\n\n# connect to the database\nmy $dbh = DBI->connect(\"dbi:SQLite:dbname=$dbfile\", '', '',\n{RaiseError => 1, AutoCommit => 1});\n# or any other options suitable to your needs\n\n# register the module\n$dbh->sqlitecreatemodule(perl => \"DBD::SQLite::VirtualTable::PerlData\");\n\nThen create a global arrayref variable, using \"our\" instead of \"my\", so that the variable is\nstored in the symbol table of the enclosing module.\n\npackage Foo::Bar; # could as well be just \"main\"\nour $rows = [ ... ];\n\nFinally, create the virtual table and bind it to the global variable (here we assume that @$rows\ncontains arrayrefs) :\n\n$dbh->do('CREATE VIRTUAL TABLE temp.vtab'\n.'  USING perl(col1 INT, col2 TEXT, etc,\narrayrefs=\"Foo::Bar::rows');\n\nIn most cases, the virtual table will be for temporary use, which is the reason why this example\nprepends \"temp.\" in front of the table name : this tells SQLite to cleanup that table when the\ndatabase handle will be disconnected, without the need to emit an explicit DROP statement.\n\nColumn names (and optionally their types) are specified in the virtual table declaration, just\nlike for any regular table.\n"
                },
                {
                    "name": "Arrayref example : statistics from files",
                    "content": "Let's suppose we want to perform some searches over a collection of files, where search\nconstraints may be based on some of the fields returned by stat, such as the size of the file or\nits last modify time. Here is a way to do it with a virtual table :\n\nmy @files = ... ; # list of files to inspect\n\n# apply the L<stat> function to each file\nour $filestats = [ map { [ $, stat $ ] } @files];\n\n# create a temporary virtual table\n$dbh->do(<<\"\");\nCREATE VIRTUAL TABLE temp.filestats'\nUSING perl(path, dev, ino, mode, nlink, uid, gid, rdev, size,\natime, mtime, ctime, blksize, blocks,\narrayrefs=\"main::filestats\");\n\n# search files\nmy $sth = $dbh->prepare(<<\"\");\nSELECT * FROM filestats\nWHERE mtime BETWEEN ? AND ?\nAND uid IN (...)\n"
                },
                {
                    "name": "Hashref example : unicode characters",
                    "content": "Given any unicode character, the \"charinfo\" in Unicode::UCD function returns a hashref with\nvarious bits of information about that character. So this can be exploited in a virtual table :\n\nuse Unicode::UCD 'charinfo';\nour $chars = [map {charinfo($)} 0x300..0x400]; # arbitrary subrange\n\n# create a temporary virtual table\n$dbh->do(<<\"\");\nCREATE VIRTUAL TABLE charinfo USING perl(\ncode, name, block, script, category,\nhashrefs=\"main::chars\"\n)\n\n# search characters\nmy $sth = $dbh->prepare(<<\"\");\nSELECT * FROM charinfo\nWHERE script='Greek'\nAND name LIKE '%SIGMA%'\n"
                },
                {
                    "name": "Colref example: SELECT WHERE ... IN ...",
                    "content": "*Note: The idea for the following example is borrowed from the \"testintarray.h\" file in\nSQLite's source (<http://www.sqlite.org/src>).*\n\nA \"colref\" virtual table is designed to facilitate using an array of values as the right-hand\nside of an IN operator. The usual syntax for IN is to prepare a statement like this:\n\nSELECT * FROM table WHERE x IN (?,?,?,...,?);\n\nand then bind individual values to each of the ? slots; but this has the disadvantage that the\nnumber of values must be known in advance. Instead, we can store values in a Perl array, bind\nthat array to a virtual table, and then write a statement like this\n\nSELECT * FROM table WHERE x IN perlarray;\n\nHere is how such a program would look like :\n\n# connect to the database\nmy $dbh = DBI->connect(\"dbi:SQLite:dbname=$dbfile\", '', '',\n{RaiseError => 1, AutoCommit => 1});\n\n# Declare a global arrayref containing the values. Here we assume\n# they are taken from @ARGV, but any other datasource would do.\n# Note the use of \"our\" instead of \"my\".\nour $values = \\@ARGV;\n\n# register the module and declare the virtual table\n$dbh->sqlitecreatemodule(perl => \"DBD::SQLite::VirtualTable::PerlData\");\n$dbh->do('CREATE VIRTUAL TABLE temp.intarray'\n.'  USING perl(i INT, colref=\"main::values');\n\n# now we can SELECT from another table, using the intarray as a constraint\nmy $sql    = \"SELECT * FROM sometable WHERE somecol IN intarray\";\nmy $result = $dbh->selectallarrayref($sql);\n\nBeware that the virtual table is read-write, so the statement below would push 99 into @ARGV !\n\nINSERT INTO intarray VALUES (99);\n"
                }
            ]
        },
        "AUTHOR": {
            "content": "Laurent Dami <dami@cpan.org>\n",
            "subsections": []
        },
        "COPYRIGHT AND LICENSE": {
            "content": "Copyright Laurent Dami, 2014.\n\nThis library is free software; you can redistribute it and/or modify it under the same terms as\nPerl itself.\n",
            "subsections": []
        }
    },
    "summary": "DBD::SQLite::VirtualTable::PerlData -- virtual table hooked to Perl data",
    "flags": [],
    "examples": [],
    "see_also": []
}