SQLite extra

In addition to the main library, SQLite has many additional programs and loadable extensions. However these need to be separately compiled and installed. They do not have the same level of testing and documentation as the main library.

Full APSW builds such as those on PyPI include all the ones that compile for that platform, without any modifications. This is for convenience and to help promote these great extras. They add just over 1MB to the download and 3MB of disk space.

Access is provided via an API, command line, and shell

vec1

There is an experimental under development vector search library by the SQLite team. It is also included with the extras if possible.

• Forum post to provide feedback

• Site with tutorial and reference documentation

Dependencies

There are no dependencies for the extensions and programs. That means they can be used on other compatible systems. Some of the programs require the SQLite library alongside the program which must be placed in the same directory as the program if you copy the program elsewhere. (The SQLite library in that directory deliberately has a different name to avoid interactions with the standard system SQLite library.)

Extensions and programs that require third party libraries (eg compression), or TCL are not included, and optional third party libraries (eg readline) are not used.

Marking

The extensions and programs are all marked as packaged by APSW. Under Windows this is indicated in the detailed properties listing. On other platforms running strings should show it, with ELF binaries having a note.apsw section and MacOS (Mach-O) binaries having a apsw section.

The marking includes the version details.

Programs

sqlite3_dbdump (doc)

Converts the content of a SQLite database into UTF-8 text SQL statements that can be used to exactly recreate the original database

sqlite3_dbhash (doc)

Computes SHA1 of the contents of a SQLite database

sqlite3_dbtotxt (doc)

Converts a binary file like a database into a friendly human readable text format

sqlite3_diff (doc)

Displays content differences between SQLite databases

sqlite3_expert (doc)

A simple system to propose useful indexes given a database and a set of SQL queries

sqlite3_getlock (doc)

Unix only shows if and who is holding a database lock

sqlite3_index_usage (doc)

Given a database and a log database, shows how many times each index is used

sqlite3_normalize (doc)

Normalizes SQL text so private information can be removed, and to identify structurally identical queries

sqlite3_offsets (doc)

Shows length and offset for every TEXT or BLOB for a column of a table

sqlite3_rsync (doc)

Database Remote-Copy Tool

sqlite3_scrub (doc)

Makes a backup zeroing out all deleted content

sqlite3_shell (doc)

Command line shell

sqlite3_showdb (doc)

Prints low level details about a database file

sqlite3_showjournal (doc)

Prints low level content of a journal

sqlite3_showlocks (doc)

Shows all posix advisory locks on a file

sqlite3_showshm (doc)

Shows low level content of shm and wal-index files

sqlite3_showstat4 (doc)

Shows contents of stat4 index of a database

sqlite3_showtmlog (doc)

Makes human/csv readable output from a tmstmpvfs log file

sqlite3_showwal (doc)

Shows low level content of a WAL file

Extensions

Name	Doc	Description	Registers
amatch	link	Approximate matches virtual table	VTable: approximate_match
anycollseq	link	Fake fallback collating function for any unknown collating sequence	Fallback collation
appendvfs	link	A VFS shim that allows an SQLite database to be appended onto the end of some other file, such as an executable	VFS: apndvfs
base64	link	Convert either direction between base64 blob and text	Function: base64
base85	link	Convert either direction between base85 blob and text	Function: base85 is_base85
btreeinfo	link	btreeinfo virtual table that shows information about all btrees in an SQLite database file	VTable: sqlite_btreeinfo
cksumvfs	link	A VFS shim that writes a checksum on each page of an SQLite database file	Function: verify_checksum VFS: cksmvfs
closure	link	A virtual table that finds the transitive closure of a parent/child relationship in a real table	VTable: transitive_closure
completion	link	A virtual table that returns suggested completions for a partial SQL input	VTable: completion
csv	link	A virtual table for reading CSV files	VTable: csv
decimal	link	Routines to implement arbitrary-precision decimal math	Collation: decimal Function: decimal decimal_add decimal_cmp decimal_exp decimal_mul decimal_pow2 decimal_sub decimal_sum
eval	link	Implements SQL function eval() which runs SQL statements recursively	Function: eval
fileio	link	Implements SQL functions readfile() and writefile(), and eponymous virtual type ‘fsdir’	Function: lsmode readfile realpath writefile VTable: fsdir
fuzzer	link	Virtual table that generates variations on an input word at increasing edit distances from the original	VTable: fuzzer
ieee754	link	functions for the exact display* and input of IEEE754 Binary64 floating-point numbers	Function: ieee754 ieee754_exponent ieee754_from_blob ieee754_from_int ieee754_inc ieee754_mantissa ieee754_to_blob ieee754_to_int
memstat	link	Provides SQL access to the sqlite3_status64() and sqlite3_db_status() interfaces	VTable: sqlite_memstat
nextchar	link	Finds all valid ‘next’ characters for a string given a vocabulary	Function: next_char
noop	link	Implements noop() functions useful for testing	Function: multitype_text noop noop_do noop_i noop_nd
prefixes	link	Table valued function providing all prefixes of a string	Function: prefix_length VTable: prefixes
randomjson	link	Generates random json objects	Function: random_json random_json5
regexp	link	Compact reasonably efficient posix extended regular expression matcher	Function: regexpi
rot13	link	rot13 function and collating sequence	Collation: rot13 Function: rot13
sha1	link	SHA1 hash and query results hash	Function: sha1 sha1_query sha1b
shathree	link	SHA3 hash and query results hash	Function: sha3 sha3_agg sha3_query
spellfix	link	Search a large vocabulary for close matches	Function: editdist3 spellfix1_editdist spellfix1_phonehash spellfix1_scriptcode spellfix1_translit VTable: spellfix1
stmt	link	Virtual table with information about all prepared statements on a connection	VTable: sqlite_stmt
stmtrand	link	Function that returns the same sequence of random integers is returned for each invocation of the statement	Function: stmtrand
tmstmpvfs	link	VFS shim that writes timestamps and other tracing information to the reserved bytes of each page, and also generates corresponding log files	VFS: tmstmpvfs
uint	link	UINT collating sequence	Collation: uint
unionvtab	link	Virtual table combining underlying tables from other databases	VTable: swarmvtab unionvtab
uuid	link	uuid functions	Function: uuid uuid_blob uuid_str
vec1	link	Vector search. !Experimental! !Under development!	Function: vec1_cos_distance vec1_from_json vec1_info vec1_l2_distance vec1_qinstr vec1_to_json vec1_to_json_f vec1_to_json_i vec1_train VTable: vec1
vfsstat	link	VFS shim tracking call statistics	VFS: vfslog VTable: vfsstat
vtablog	link	Virtual table printing diagnostic information for interactive analysis and debugging	VTable: vtablog
zorder	link	Functions for z-order (Morton code) transformations	Function: unzorder zorder

Command line

Programs can be run by giving their name and parameters. For example sqlite3_scrub program:

python3 -m apsw.sqlite_extra sqlite3_scrub source.db dest.db

You can also get the filename for any program or extension. For example the sqlite3_rsync program:

python3 -m apsw.sqlite_extra --path sqlite3_rsync

The csv extension path.:

python3 -m apsw.sqlite_extra --path csv

List what is available:

python3 -m apsw.sqlite_extra --list

Shell

The shell is also integrated. Use .load with --list to see all available extensions, and you can load just giving the name.

sqlite> .load --list
sqlite> .load csv

API

exception apsw.sqlite_extra.NotAvailable

Raised if a specified item is not available in this installation

It can be because it did not compile, the APSW installation didn’t include it, or APSW is a zipped / frozen package but requires filesystem installation.

apsw.sqlite_extra.load(db: Connection, extension: str)

Loads the extension into the provided database

Extension loading will also be turned on.

apsw.sqlite_extra.has(name: str) → str | None

Returns ‘executable’ or ‘extension’ if extra name is available else None

apsw.sqlite_extra.path(name: str)

Filesystem path for named extra including extension