# perldoc > DBD::SQLite::VirtualTable

---
type: CommandReference
command: DBD::SQLite::VirtualTable
mode: perldoc
section: 
source: perldoc
---

## Quick Reference

- `$dbh->sqlite_create_module(mod_name => "MyModule")` — register virtual table module
- `$dbh->do("CREATE VIRTUAL TABLE vtbl USING mod_name(arg1, arg2)")` — create a virtual table
- `$sth = $dbh->prepare("SELECT * FROM vtbl WHERE ...")` — use it as regular table
- VirtualTable subclasses are not called directly; everything happens through SQL statements.

## Name

DBD::SQLite::VirtualTable — SQLite virtual tables implemented in Perl

## Synopsis

perl
# register the virtual table module within sqlite
$dbh->sqlite_create_module(mod_name => "DBD::SQLite::VirtualTable::Subclass");

# create a virtual table
$dbh->do("CREATE VIRTUAL TABLE vtbl USING mod_name(arg1, arg2, ...)");

# use it as any regular table
my $sth = $dbh->prepare("SELECT * FROM vtbl WHERE ...");
## Options

### Table Methods — Class Methods for Registering

- `CREATE_MODULE($sqlite_module_name)` — called when the client code invokes `sqlite_create_module`. Default empty.
- `DESTROY_MODULE()` — called when the database handle is disconnected. Default empty.

### Table Methods — Class Methods for Creating a Vtable Instance

- `CREATE($dbh_ref, $module_name, $db_name, $vtab_name, @args)` — called when SQLite receives `CREATE VIRTUAL TABLE`. Default calls `NEW`.
- `CONNECT($dbh_ref, $module_name, $db_name, $vtab_name, @args)` — called when accessing a previously created virtual table. Default calls `NEW`.
- `_PREPARE_SELF($dbh_ref, $module_name, $db_name, $vtab_name, @args)` — prepares the datastructure for a virtual table instance. Parses args into `options` and `columns` hashrefs. Should not be redefined.
- `NEW($dbh_ref, $module_name, $db_name, $vtab_name, @args)` — instantiates a virtual table. Override this in subclasses.

### Table Methods — Instance Methods Called from SQLite Kernel

- `DROP()` — called when `DROP TABLE` is executed. Default empty.
- `DISCONNECT()` — called just before the database handle is disconnected. Default empty.
- `VTAB_TO_DECLARE()` — returns a `CREATE TABLE` statement to register columns. Default: `CREATE TABLE $self->{vtab_name}(@{$self->{columns}})`. Columns may be declared `HIDDEN`.
- `BEST_INDEX($constraints, $order_by)` — determines search strategy. Input: `$constraints` arrayref (each with `col`, `op`, `usable`) and `$order_by` arrayref (each with `col`, `desc`). Output: hashref with `idxNum`, `idxStr`, `orderByConsumed`, `estimatedCost`, `estimatedRows`. Also modifies `$constraints` by adding `argvIndex` and `omit`.
- `OPEN()` — instantiates a new cursor. Default appends `::Cursor` to the classname and calls `NEW`.
- `INSERT($new_rowid, @values)` — inserts a new row. Should be overridden. Returns the new rowid (or computes one if `$new_rowid` is undef).
- `DELETE($old_rowid)` — deletes a row. Should be overridden.
- `UPDATE($old_rowid, $new_rowid, @values)` — updates a row. Should be overridden.
- `FIND_FUNCTION($num_args, $func_name)` — overloads a function whose first argument is a virtual table column. Returns a coderef or false. Results are cached.
- `BEGIN_TRANSACTION()` — begins a transaction.
- `SYNC_TRANSACTION()` — signals start of two-phase commit.
- `COMMIT_TRANSACTION()` — commits a transaction.
- `ROLLBACK_TRANSACTION()` — rolls back a transaction.
- `RENAME($new_name)` — renames the virtual table.
- `SAVEPOINT($savepoint)` — saves state at savepoint `$savepoint`.
- `ROLLBACK_TO($savepoint)` — returns to state `$savepoint`.
- `RELEASE($savepoint)` — invalidates all savepoints with values >= `$savepoint`.

### Utility Instance Methods

- `dbh()` — returns the database handle `$dbh` associated with the virtual table.

### Cursor Methods — Class Methods

- `NEW($vtable, @args)` — instantiates a new cursor. Default returns a blessed hashref with keys `vtable` and `args`.

### Cursor Methods — Instance Methods

- `FILTER($idxNum, $idxStr, @values)` — begins a search of a virtual table. Uses `$idxNum` and `$idxStr` from `BEST_INDEX`. `@values` corresponds to `argvIndex` entries. Leaves cursor at first matching row or sets `EOF` to true.
- `EOF()` — returns false if cursor points to a valid row, true otherwise.
- `NEXT()` — advances cursor to the next row. Updates `EOF` accordingly.
- `COLUMN($idxCol)` — returns the value for the N-th column (0‑based) of the current row.
- `ROWID()` — returns the rowid of the current row.

## Examples

perl
# Register a virtual table module (e.g., DBD::SQLite::VirtualTable::PerlData)
$dbh->sqlite_create_module(perl_data => "DBD::SQLite::VirtualTable::PerlData");

# Create a virtual table that binds to a Perl array
$dbh->do("CREATE VIRTUAL TABLE mydata USING perl_data(data=@array)");

# Use it in a query
my $sth = $dbh->prepare("SELECT * FROM mydata WHERE value > 10");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
    print $row->{value};
}
## See Also

- [DBD::SQLite::VirtualTable::FileContent](http://localhost/phpMan.php/perldoc/DBD%3A%3ASQLite%3A%3AVirtualTable%3A%3AFileContent/markdown) — virtual column exposing file contents
- [DBD::SQLite::VirtualTable::PerlData](http://localhost/phpMan.php/perldoc/DBD%3A%3ASQLite%3A%3AVirtualTable%3A%3APerlData/markdown) — bind to a Perl array
- [DBD::SQLite::Fulltext_search](http://localhost/phpMan.php/perldoc/DBD%3A%3ASQLite%3A%3AFulltextsearch/markdown) — fulltext search with virtual tables
- [SQLite::VirtualTable](http://localhost/phpMan.php/perldoc/SQLite%3A%3AVirtualTable/markdown) — alternative Perl virtual table module (embed Perl in SQLite)