# 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