SQLite_File - Tie to SQLite, with DB_File emulation


 # tie a simple hash to a SQLite DB file
 my %db;
 tie(%db, 'SQLite_File', 'my.db');

 # tie an array
 my @db;
 tie(@db, 'SQLite_File', 'my.db');

 # tie to a tempfile
 tie(%db, 'SQLite_File', undef);

 # get attributes of the tied object

 $SQLite_handle = (tied %db)->dbh;
 $db_file = (tied %db)->file;

 # use as an option in AnyDBM_File
 @AnyDBM_File::ISA = qw( DB_File SQLite_File SDBM );
 my %db;
 tie(%db, 'AnyDBM_File', 'my.db', @dbmargs)

 # Filter with DBM_Filter

 use DBM_Filter;
 tie(%db, 'SQLite_File', 'my.db');
 (tied %db)->Filter_Push('utf8');


This module allows a hash or an array to be tied to a SQLite DB via DBI plus DBD::SQLite, in a way that emulates many features of Berkeley-DB-based DB_File. In particular, this module offers another choice for ActiveState users, who may find it difficult to get a working DB_File installed, but can't failover to SDBM due to its record length restrictions. SQLite_File requires DBD::SQLite, which has SQLite built-in -- no external application install required.

Key/Value filters

The filter hooks fetch_key_filter, fetch_value_filter, store_key_filter, and store_value_filter are honored. DBM_Filter can be used as an API.

DB_File Emulation

The intention was to create a DBM that could almost completely substitute for DB_File, so that DB_File could be replaced everywhere in code by AnyDBM_File, and things would just work. Currently, it is slightly more complicated than that, but not too much more.

Versions of $DB_HASH, $DB_BTREE, and $DB_RECNO, as well as the necessary flags (R_DUP, R_FIRST, R_NEXT, etc.) are imported by using the AnyDBM_File::Importer module. The desired constants need to be declared global in the calling program, as well as imported, to avoid compilation errors (at this point). See "Converting from DB_File" below.

Arguments to the tie function mirror those of DB_File, and all should work the same way. See "Converting from DB_File".

All of DB_File's random and sequential access functions work:


as well as the duplicate key handlers


seq() works by finding partial matches, like DB_File::seq(). The extra array functions ( shift(), pop(), etc. ) are not yet implemented as method calls, though all these functions (including splice are available on the tied arrays.

Some HASHINFO fields are functional:

 $DB_BTREE->{'compare'} = sub { - shift cmp shift };

will provide sequential access in reverse lexographic order, for example.

 $DB_HASH->{'cachesize'} = 20000;

will enforce PRAGMA cache_size = 20000.

Converting from DB_File

To failover to SQLite_File from DB_File, go from this:

 use DB_File;
 # ...
 $DB_BTREE->{cachesize} = 100000;
 $DB_BTREE->{flags} = R_DUP;
 my %db;
 my $obj = tie( %db, 'DB_File', 'my.db', $flags, 0666, $DB_BTREE);

to this:

  use vars qw( $DB_HASH &R_DUP );
    @AnyDBM_File::ISA = qw( DB_File SQLite_File )
      unless @AnyDBM_File::ISA == 1; # 
  use AnyDBM_File;
  use AnyDBM_File::Importer qw(:bdb);
  # ...

  $DB_BTREE->{cachesize} = 100000;
  $DB_BTREE->{flags} = R_DUP;
  my %db;
  my $obj = tie( %db, 'AnyDBM_File', 'my.db', $flags, 0666, $DB_BTREE);


AnyDBM_File::Importer, DBD::SQLite, DB_File, AnyDBM_File


 Mark A. Jensen < MAJENSEN -at- cpan -dot- org >


This code owes an intellectual debt to Lincoln Stein. Inelegancies and bugs are mine.

Thanks to Barry C. and "Justin Case".


(c) 2009-2017 Mark A. Jensen

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

The full text of the license can be found in the LICENSE file included with this module.

Attribute Accessors


 Title   : file
 Usage   : $db->file()
 Function: filename for the SQLite db
 Example : 
 Returns : value of file (a scalar)
 Args    : 


 Title   : _fh
 Usage   : $db->_fh()
 Function: holds the temp file handle
 Example : 
 Returns : value of _fh (a scalar)
 Args    : 


 Title   : keep
 Usage   : $db->keep()
 Function: flag allows preservation of db file when set
 Returns : value of keep (a scalar)
 Args    : 


 Title   : ref
 Usage   : $db->ref
 Function: HASH or ARRAY? Find out.
 Returns : scalar string : 'HASH' or 'ARRAY'
 Args    : none


 Title   : index
 Usage   : $db->index()
 Function: access the index type structure ($DB_BTREE, $DB_HASH, 
           $DB_RECNO) that initialized this instance
 Returns : value of index (a hashref)
 Args    : 

BDB API Emulation : random access


 Title   : get
 Usage   : $db->get($key, $value)
 Function: Get value associated with key
 Returns : 0 on success, 1 on fail; 
           value in $value
 Args    : as in DB_File


 Title   : put
 Usage   : $db->put($key, $value, $flags)
 Function: Replace a key's value, or
           put a key-value pair
 Returns : 0 on success, 1 on fail;
           value in $value
           key in $key if $flags == R_CURSOR
 Args    : as in DB_File


 Title   : del
 Usage   : $db->del($key)
 Function: delete key-value pairs corresponding to $key
 Returns : 0 on success, 1 on fail
 Args    : as in DB_File

BDB API Emulation : sequential access


 Title   : seq
 Usage   : $db->seq($key, $value, $flags)
 Function: retrieve key-value pairs sequentially,
           according to $flags, with partial matching
           on $key; see DB_File
 Returns : 0 on success, 1 on fail;
           key in $key,
           value in $value
 Args    : as in DB_File


 Title   : sync
 Usage   : $db->sync
 Function: stub for BDB sync 
 Returns : 0
 Args    : none

BDB API Emulation : dup


 Title   : dup
 Usage   : $db->dup()
 Function: Get/set flag indicating whether duplicate keys
           are preserved
 Returns : boolean
 Args    : [optional] on set, new value (a scalar or undef, optional)


 Title   : get_dup
 Usage   : $db->get_dup($key, $want_hash)
 Function: retrieve all records associated with a key
 Returns : scalar context: scalar number of records
           array context, !$want_hash: array of values
           array context, $want_hash: hash of value-count pairs
 Args    : as in DB_File


 Title   : find_dup
 Usage   : $db->find_dup($key, $value)
 Function: set the cursor to an instance of 
           the $key-$value pair, if one 
 Returns : 0 on success, 1 on fail
 Args    : as in DB_File


 Title   : del_dup
 Usage   : $db->del_dup($key, $value)
 Function: delete all instances of the $key-$value pair
 Returns : 0 on success, 1 on fail
 Args    : as in DB_File

SQL Interface


 Title   : dbh
 Usage   : $db->dbh()
 Function: Get/set DBI database handle
 Example : 
 Returns : DBI database handle
 Args    : 


 Title   : sth
 Usage   : $obj->sth($stmt_descriptor)
 Function: DBI statement handle generator
 Returns : a prepared DBI statement handle
 Args    : scalar string (statement descriptor)
 Note    : Calls such as $db->put_sth are autoloaded through
           this method; please see source for valid descriptors


 Title   : commit
 Usage   : $db->commit()
 Function: commit transactions
 Returns : 
 Args    : commit(1) forces, commit() commits when
           number of pending transactions > $SQLite::MAXPEND


 Title   : pending
 Usage   : $db->pending
 Function: Get count of pending (uncommitted) transactions
 Returns : scalar int
 Args    : none


 Title   : trace
 Usage   : $db->trace($TraceLevel)
 Function: invoke the DBI trace logging service
 Returns : the trace level
 Args    : scalar int trace level