Skip to content

Latest commit

 

History

History
2056 lines (1465 loc) · 77.2 KB

sqlite_ext.rst

File metadata and controls

2056 lines (1465 loc) · 77.2 KB
Raw

SQLite Extensions

The default :pySqliteDatabase already includes many SQLite-specific features:

  • General notes on using SQLite <using_sqlite>.
  • Configuring SQLite using PRAGMA statements <sqlite-pragma>.
  • User-defined functions, aggregate and collations <sqlite-user-functions>.
  • Locking modes for transactions <sqlite-locking>.

The playhouse.sqlite_ext includes even more SQLite features, including:

  • Full-text search <sqlite-fts>
  • JSON extension integration <sqlite-json1>
  • Closure table extension support <sqlite-closure-table>
  • LSM1 extension support <sqlite-lsm1>
  • User-defined table functions <sqlite-vtfunc>
  • Support for online backups using backup API: :py~CSqliteExtDatabase.backup_to_file
  • BLOB API support, for efficient binary data storage <sqlite-blob>.
  • Additional helpers <sqlite-extras>, including bloom filter, more.

Getting started

To get started with the features described in this document, you will want to use the :pySqliteExtDatabase class from the playhouse.sqlite_ext module. Furthermore, some features require the playhouse._sqlite_ext C extension -- these features will be noted in the documentation.

Instantiating a :pySqliteExtDatabase:

from playhouse.sqlite_ext import SqliteExtDatabase

db = SqliteExtDatabase('my_app.db', pragmas=(
    ('cache_size', -1024 * 64),  # 64MB page-cache.
    ('journal_mode', 'wal'),  # Use WAL-mode (you should always use this!).
    ('foreign_keys', 1)))  # Enforce foreign-key constraints.

APIs

Additional Features

The :pySqliteExtDatabase accepts an initialization option to register support for a simple bloom filter. The bloom filter, once initialized, can then be used for efficient membership queries on large set of data.

Here's an example:

db = CSqliteExtDatabase(':memory:', bloomfilter=True)

# Create and define a table to store some data.
db.execute_sql('CREATE TABLE "register" ("data" TEXT)')
Register = Table('register', ('data',)).bind(db)

# Populate the database with a bunch of text.
with db.atomic():
    for i in 'abcdefghijklmnopqrstuvwxyz':
        keys = [i * j for j in range(1, 10)]  # a, aa, aaa, ... aaaaaaaaa
        Register.insert([{'data': key} for key in keys]).execute()

# Collect data into a 16KB bloomfilter.
query = Register.select(fn.bloomfilter(Register.data, 16 * 1024).alias('buf'))
row = query.get()
buf = row['buf']

# Use bloomfilter buf to test whether other keys are members.
test_keys = (
    ('aaaa', True),
    ('abc', False),
    ('zzzzzzz', True),
    ('zyxwvut', False))
for key, is_present in test_keys:
    query = Register.select(fn.bloomfilter_contains(key, buf).alias('is_member'))
    answer = query.get()['is_member']
    assert answer == is_present

The :pySqliteExtDatabase can also register other useful functions:

  • rank_functions (enabled by default): registers functions for ranking search results, such as bm25 and lucene.
  • hash_functions: registers md5, sha1, sha256, adler32, crc32 and murmurhash functions.
  • regexp_function: registers a regexp function.

Examples:

def create_new_user(username, password):
    # DO NOT DO THIS IN REAL LIFE. PLEASE.
    query = User.insert({'username': username, 'password': fn.sha1(password)})
    new_user_id = query.execute()

You can use the murmurhash function to hash bytes to an integer for compact storage:

>>> db = SqliteExtDatabase(':memory:', hash_functions=True)
>>> db.execute_sql('SELECT murmurhash(?)', ('abcdefg',)).fetchone()
(4188131059,)