Additional Features

The SqliteExtDatabase accepts an initialization option to registersupport for a simple bloom filter.The bloom filter, once initialized, can then be used for efficient membershipqueries 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 SqliteExtDatabase can also register other useful functions:

• rankfunctions (enabled by default): registers functions for rankingsearch results, such as _bm25 and lucene.
• hash_functions: registers md5, sha1, sha256, adler32, crc32 andmurmurhash 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 compactstorage:

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