# 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 or tools.

### zlib

Some of the extensions and programs use the zlib compression library (license) which is included statically for those extensions and programs.

### TCL

Some of the programs require TCL and are omitted.

### Readline

The shell can optionally use the readline (or editline) library for editing at the shell prompt. This is disabled to avoid the dependency. You can use the APSW Shell, or the extra provided one with rlwrap if you need significant editing functionality.

## 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_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

sqlite3_sqlar (doc)

> Command line SQL archive tool

## Extensions

Name | Description | Registers
--- | --- | ---

amatch (doc)

Approximate matches virtual table

- VTable: `approximate_match`

anycollseq (doc) | Fake fallback collating function for any unknown collating sequence | Fallback collation

appendvfs (doc)

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 (doc)

Convert either direction between base64 blob and text

- Function: `base64`

base85 (doc)

Convert either direction between base85 blob and text

- Function: `base85` `is_base85`

btreeinfo (doc)

btreeinfo virtual table that shows information about all btrees in an SQLite database file

- VTable: `sqlite_btreeinfo`

cksumvfs (doc)

A VFS shim that writes a checksum on each page of an SQLite database file

- Function: `verify_checksum`

- VFS: `cksmvfs`

completion (doc)

A virtual table that returns suggested completions for a partial SQL input

- VTable: `completion`

compress (doc)

SQL compression functions

- Function: `compress` `uncompress`

csv (doc)

A virtual table for reading CSV files

- VTable: `csv`

decimal (doc)

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 (doc)

Implements SQL function eval() which runs SQL statements recursively

- Function: `eval`

fileio (doc)

Implements SQL functions readfile() and writefile(), and eponymous virtual type ‘fsdir’

- Function: `lsmode` `readfile` `realpath` `writefile`

- VTable: `fsdir`

fuzzer (doc)

Virtual table that generates variations on an input word at increasing edit distances from the original

- VTable: `fuzzer`

ieee754 (doc)

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 (doc)

Provides SQL access to the sqlite3_status64() and sqlite3_db_status() interfaces

- VTable: `sqlite_memstat`

nextchar (doc)

Finds all valid ‘next’ characters for a string given a vocabulary

- Function: `next_char`

noop (doc)

Implements noop() functions useful for testing

- Function: `multitype_text` `noop` `noop_do` `noop_i` `noop_nd`

prefixes (doc)

Table valued function providing all prefixes of a string

- Function: `prefix_length`

- VTable: `prefixes`

randomjson (doc)

Generates random json objects

- Function: `random_json` `random_json5`

regexp (doc)

Compact reasonably efficient posix extended regular expression matcher

- Function: `regexpi`

rot13 (doc)

rot13 function and collating sequence

- Collation: `rot13`

- Function: `rot13`

sha1 (doc)

SHA1 hash and query results hash

- Function: `sha1` `sha1_query` `sha1b`

shathree (doc)

SHA3 hash and query results hash

- Function: `sha3` `sha3_agg` `sha3_query`

spellfix (doc)

Search a large vocabulary for close matches

- Function: `editdist3` `spellfix1_editdist` `spellfix1_phonehash` `spellfix1_scriptcode` `spellfix1_translit`

- VTable: `spellfix1`

sqlar (doc)

Utility functions for SQL archives

- Function: `sqlar_compress` `sqlar_uncompress`

stmt (doc)

Virtual table with information about all prepared statements on a connection

- VTable: `sqlite_stmt`

stmtrand (doc)

Function that returns the same sequence of random integers is returned for each invocation of the statement

- Function: `stmtrand`

tmstmpvfs (doc)

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 (doc)

UINT collating sequence

- Collation: `uint`

unionvtab (doc)

Virtual table combining underlying tables from other databases

- VTable: `swarmvtab` `unionvtab`

uuid (doc)

uuid functions

- Function: `uuid` `uuid_blob` `uuid_str`

vec1 (doc)

Vector search. !Experimental! !Under development!

- Function: `vec1_config` `vec1_cos_distance` `vec1_from_json` `vec1_info` `vec1_l2_distance` `vec1_to_json` `vec1_to_json_f` `vec1_to_json_i` `vec1_train`

- VTable: `vec1` `vec1cat`

vfsstat (doc)

VFS shim tracking call statistics

- VFS: `vfslog`

- VTable: `vfsstat`

vtablog (doc)

Virtual table printing diagnostic information for interactive analysis and debugging

- VTable: `vtablog`

zipfile (doc)

Read/Write access to simple archives

- Function: `zipfile` `zipfile_cds`

- VTable: `zipfile`

zorder (doc)

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_showstat4` program:

    python3 -m apsw.sqlite_extra sqlite3_showstat4 source.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