Change History

You may also be interested in the SQLite release history and Python release history.

APSW changes by version

3.47.2.0

Added apsw.ext.page_usage_to_svg() which shows database usage as SVG (example). Available as shell .pages-svg command.

3.47.1.0

Documentation on how to build for packagers such as those maintaining Linux and BSD distributions.

Documentation on how to build for pyodide, the Python WASM implementation that runs in the browser and NPM. PyPI does not accept pyodide packages yet.

A command line tool apsw is defined which invokes the shell. This also allows using uvx apsw without having to explicitly install APSW.

Added apsw.ext.analyze_pages() which uses dbstat to provide useful information about the pages making up the database, and fragmentation. The shell .pages command shows it in a pretty form.

3.47.0.0

Support for Python 3.8 removed (APSW issue 539).

The readonly database statistics virtual table (dbstat) is enabled by default for PyPI builds, and when --enable-all-extensions is passed to manual builds.

Added recursive triggers and optimize to apsw.bestpractice.

Multiple callbacks can be present for Connection.trace_v2() with APSW ensuring they are all called (APSW issue 502)

Connection.trace_v2() callback information now has trigger, id, and total_changes fields.

Added Connection.data_version for getting a change counter. pragma data_version doesn’t update when changes are made on the same connection, only others.

Added apsw.ext.ShowResourceUsage() for getting resource and SQLite usage in a context block, and also use it for the shell timer command.

Added apsw.ext.Trace() for tracing SQL execution, row and change counting, and timing per statement for use in a context block.

Added FTS5 support including registering and calling tokenizers, and auxiliary functions. The apsw.fts5 module provides many additional classes and methods for working with FTS5, including tokenizers for HTML, JSON, regular expressions, support tokenizers for synonyms, stop words, transformers, and a Table class that wraps access to a FTS5 table (including creating one) with search(), more_like(), and query_suggest(). apsw.fts5query can parse, modify, and reconstruct queries. The shell gets a ftsq command for issuing queries.

Added apsw.unicode which implements Unicode algorithms for determining codepoint groups making up a user perceived character, word and sentence splitting, and where line breaks can be made. These are used to make provided FTS5 tokenizers and auxiliary functions fully Unicode aware. There are many additional methods such as getting categories, stripping diacritics, case folding, width when output to a terminal, text wrapping, and more.

apsw.ext.format_query_table() uses apsw.unicode to get widths and line breaks more accurate. As a side effect it loses the word_wrap parameter (breaking change).

3.46.1.0

The shell dump command outputs the application_id in addition to the user_version.

PyPI binary builds for Python 3.13 now available, as well as older Python ARM64 are available.

3.46.0.1

apsw.ext.query_info() provides the count and names of bindings parameters. (APSW issue 528)

Address how errors are handled in VFS xRandomness routine, that is only called once by SQLite to seed its random number generator. (APSW issue 526)

Added Connection.vfsname() and updated corresponding shell command to get the diagnostic names of the vfs stack for the connection. (APSW issue 525)

Do not cache Connection.pragma() statements to avoid encryption keys, or pragmas that run during prepare from being retained. (APSW issue 522)

Connection.pragma() adds keyword schema argument to run pragma against attached databases. (APSW issue 524)

3.46.0.0

Adjusted levels in apsw.ext.log_sqlite() to be lower for some SQLite messages like SQLITE_SCHEMA and SQLITE_NOTICE_RECOVER_WAL (APSW issue 518)

Previous source releases were signed with PGP. Starting with this release Sigstore’s cosign tool is used. (APSW issue 512)

3.45.3.0

No APSW changes.

3.45.2.0

Minor doc and tests change due to changed behaviour of sqlite3_serialize on an empty database, used by Connection.serialize().

3.45.1.0

No APSW changes.

3.45.0.0

Correctly handle NULL/None VFS filenames (APSW issue 506)

3.44.2.0

Added logger parameter to apsw.ext.log_sqlite() to use a specific logging.Logger (APSW issue 493)

Added apsw.ext.result_string() to turn an result code into a string, taking into account if it is extended or not.

Provide detail when C implemented objects are printed. For example connections include the filename. (APSW issue 494)

Added URIFilename.parameters() (APSW issue 496)

URIFilename are only valid for the duration of the VFS.xOpen() call. If you save and use the object later you will get an exception. (APSW issue 501)

3.44.0.0

Added virtual table VTTable.Integrity() support.

On 64 bit platforms with the amalgamation, SQLITE_MAX_MMAP_SIZE is set to 256 terabytes. SQLite’s default limit is 2GB. (APSW issue 491)

3.43.2.0

Connection.create_aggregate_function() can take a class with step and final methods. (APSW issue 421)

Corrected non PEP 8 compliant names. The old names remain as aliases to the new ones, and your code will not break.

3.43.1.1

Exception handling has been updated, with multiple exceptions in the same SQLite control flow being chained together. Previously more would have used the unraisable mechanism. (APSW issue 489)

Only use alloca with msvc because it doesn’t support VLA. The arrays are used for fastcall. (APSW issue 487)

3.43.1.0

All C code calling into Python and all C code called by Python uses vectorcall / fastcall (see PEP 590) which reduces the overhead of passing and receiving positional and keyword arguments. (APSW issue 477, APSW issue 446):

  • Conversion of arguments from Python values to C values drops generic PyArg_ParseTupleAndKeywords() in favour of direct processing which is more efficient and allows better exception messages.

  • Running speedtest with a VFS that inherits all methods went from being 17% slower than pure SQLite to 2% slower.

  • A virtual table benchmark takes 35% less time. (Remember that benchmarks are best case!)

The shell JSON output modes have been fixed. Mode ‘json’ outputs a json array, while mode ‘jsonl’ does newline delimited json objects, aka json lines. (APSW issue 483)

3.43.0.0

This is the last version that supports Python 3.6 and Python 3.7 (both end of life). The policy as stated in the about page is that there will be one more APSW release after a Python version goes end of life supporting that Python version. (APSW issue 471)

Added best practice module (APSW issue 460)

apsw.ext.log_sqlite() outputs SQLite warnings at warning level. (APSW issue 472)

sqlite3_stmt_explain is wrapped available as a explain keyword parameter on execute/executemany methods. (APSW issue 474)

Added documentation and helper class for implementing custom pragmas in your own Virtual File System (VFS) (APSW issue 464)

Reduced overhead of the Column method when using apsw.ext.make_virtual_module() (APSW issue 465)

3.42.0.1

Work with SQLite compiled with SQLITE_OMIT_DEPRECATED. Connection.set_profile() was changed from using the deprecated sqlite3_profile to sqlite3_trace_v2 giving the same results. When including the amalgamation, SQLITE_OMIT_DEPRECATED is defined. (APSW issue 443)

Shell updates adding various commands to match the SQLite shell, as well as code and documentation improvements. (APSW issue 397)

Added Connection.read() and apsw.ext.dbinfo() to provide information from the database and journal/wal files. The shell command .dbinfo displays it.

Added apsw.vfs_details(). The shell command .vfslist displays it.

Implemented VFS method xCurrentTimeInt64. The default SQLite VFS no longer provide xCurrentTime (floating point version) if SQLITE_OMIT_DEPRECATED is defined, so this is needed for inheritance to work. (APSW issue 451)

Backwards incompatible change: VFS If you override xCurrentTime, then you will need to override xCurrentTimeInt64 in the same way, or exclude xCurrentTimeInt64 in VFS, or use iVersion of 1.

speedtest now shows summary statistics, and improved help text. (APSW issue 444)

3.42.0.0

SQLITE_ENABLE_COLUMN_METADATA is enabled when installing APSW from PyPI (binary or source). (APSW issue 435)

Type stubs and typing information in the documentation use newer Python conventions such as | instead of Union and list instead of typing.List, being more concise and readable. (A recent Python is required to use them, but they have no effect at runtime.) (APSW issue 438)

Shell: Errors when SQLite are preparing a statement now show the relevant extract of the query, and where the error was detected.

Shell: Output modes table (ASCII line drawing, lots of sanitization), box (Unicode line drawing) and qbox (box with quoted values) available. Python 3.7+ (APSW issue 420)

Shell: if started interactively then box is the default mode (list remains the default in non-interactive)

Added Connection.pragma() to execute pragmas and get results. (APSW issue 432)

Added Cursor.get returning query results with the least amount of structure. (APSW issue 389)

Fixed execution tracers should return comment text for comment only queries, and add Cursor.has_vdbe. (APSW issue 433)

Ensure that all applicable options are implemented for apsw.config(), Connection.config() and similar. (APSW issue 431)

Added apsw.sleep() (APSW issue 419)

Strings for apsw.VFS.xNextSystemCall() are interned avoiding memory leaks. (APSW issue 430)

Detect unbound recursion not handled by CPython, and handle better. (APSW issue 425)

3.41.2.0

Fixed APSW issue 412 in apsw.ext.make_virtual_module().

Added apsw.connections() to get all connections. (APSW issue 416)

sys.unraisablehook() is called correctly (APSW issue 410)

Be stricter where bool values are expected (eg VTTable.BestIndex()), only accepting int and bool. Previously you could for example supply strings and lists, which were almost certainly unintended errors.

3.41.0.0

Backwards incompatible change: Bindings using a dictionary with a missing key now result in a KeyError exception. You can use allow_missing_dict_bindings() to restore the old behaviour. (APSW issue 392)

Virtual table updates:

apsw.format_sql_value() now outputs floating point NaN, positive and negative infinity, and signed zero exactly as SQLite does (APSW issue 398)

Added apsw.ext.format_query_table() for handy table output with auto column sizes, colour, word wrap etc.

Added Connection.is_interrupted().

3.40.1.0

Implemented window functions (APSW issue 292)

Function flags can be specified to Connection.create_scalar_function() and Connection.create_aggregate_function(). Added apsw.mapping_function_flags. (APSW issue 384)

Added Connection.trace_v2() with apsw.mapping_trace_codes and apsw.mapping_statement_status (APSW issue 383)

Ensure all SQLite APIs are wrapped. Connection.system_errno, apsw.strlike(), apsw.strglob(), apsw.stricmp(), apsw.strnicmp(), Connection.filename_wal, Connection.filename_journal, Connection.table_exists(), Connection.column_metadata(), Error.error_offset, Connection.cache_flush(), Connection.release_memory(), apsw.hard_heap_limit(). Connection.drop_modules() (APSW issue 382)

When an unraisable exception happens, sqlite3_log is now called so you will have context within SQLite’s actions. sys.unraisablehook() is now called first, and if it doesn’t exist then sys.excepthook() as before. (APSW issue 385)

When the wrong type is given for a function argument, the error message now includes the parameter name and function signature. (APSW issue 358)

Let SQLite do size checking instead of APSW for strings and blobs. (APSW issue 387)

Added apsw.ext.log_sqlite() which installs a handler that forwards SQLite messages to the logging module.

Added set_default_vfs() and unregister_vfs() taking vfs names. The test suite also unregisters ZipVFS (APSW issue 394)

3.40.0.0

Fixed regression in statement cache update (version 3.38.1-r1) where trailing whitespace in queries would be incorrectly treated as incomplete execution (APSW issue 376)

Added Various interesting and useful bits of functionality (APSW issue 369)

Added more Pythonic attributes as an alternative to getters and setters, including Connection.in_transaction, Connection.exec_trace, Connection.row_trace, Cursor.exec_trace, Cursor.row_trace, Cursor.connection (APSW issue 371)

Completed: To the extent permitted by CPython APIs every item has the same docstring as this documentation. Every API can use named parameters. The type stubs cover everything including constants. The type stubs also include documentation for everything, which for example Visual Studio Code displays as you type or hover. There is a single source of documentation in the source code, which is then automatically extracted to make this documentation, docstrings, and docstrings in the type stubs.

Example/Tour updated and appearance improved (APSW issue 367).

3.39.4.0

Added Connection.cache_stats() to provide more information about the statement cache.

Cursor.execute() now uses sqlite_prepare_v3 which allows supplying flags.

Cursor.execute() has a new can_cache parameter to control whether the query can use the statement cache. One example use is with authorizers because they only run during prepare, which doesn’t happen with already cached statements.

(The Cursor.execute() additional parameters are keyword only and also present in Cursor.executemany(), and the corresponding Connection.execute() and Connection.executemany() methods.)

Added Cursor.is_readonly, Cursor.is_explain, and Cursor.expanded_sql.

Updated processing named bindings so that types registered with collections.abc.Mapping (such as collections.UserDict) will also be treated as dictionaries. (APSW issue 373)

3.39.3.0

Test no longer fails if APSW was compiled without SQLITE_ENABLE_COLUMN_METADATA but sqlite3 was separately compiled with it. APSW should be compiled with the same flags as sqlite3 to match functionality and APIs. (APSW issue 363)

–use-system-sqlite-config setup.py build_ext option added to allow build_ext. (APSW issue 364)

3.39.2.1

PyPI now includes Python 3.11 builds.

Instead of using scripts, you can now run several tools directly:

  • tests: python3 -m apsw.tests [options]

  • tracer: python3 -m apsw.trace [options]

  • speed tester: python3 -m apsw.speedtest [options]

  • shell: python3 -m apsw [options]

The shell class has moved from apsw.Shell to apsw.shell.Shell (APSW issue 356). You can still reference it via the old name (ie existing code will not break, except on Python 3.6).

Shell: On Windows the native console support for colour is now used (previously a third party module was supported).

You can use –definevalues in setup.py build_ext to provide compiler defines used for configuring SQLite. (APSW issue 357)

If SQLITE_ENABLE_COLUMN_METADATA is enabled then Cursor.description_full is available providing all the column metadata available. (APSW issue 354)

Connection.cursor_factory attribute is now present and is used when Connection.cursor() is called. Added Connection.execute() and Connection.executemany() which automatically obtain the underlying cursor. See customizing connections and cursors in the Tips. (APSW issue 361)

3.39.2.0

Version numbering scheme change: Instead of a -r1 style suffix, there is .0 style suffix (APSW issue 340)

Updated building for PyPI to include more compiled platforms, including aarch64 (Linux) and universal (MacOS). Windows binaries are no longer separately provided since PyPI has them.

When the amalgamation is included into APSW, SQLITE_MAX_ATTACHED is set to 125 if not defined, up from the default of 10.

Updated typing information stubs with more detail and include docstrings. This is still ongoing, but core functionality is well covered. (APSW issue 338) (APSW issue 381)

Corrected the tips log handler of extended result code (APSW issue 342)

Added Connection.db_names() (APSW issue 343)

3.38.5-r1

APSW is now on PyPI, so you can:

pip install apsw

(Thanks to several people behind the scenes who helped with the various pieces to make this happen.)

Removed support for setup.py downloading the in-development (aka fossil) version of SQLite.

Shell exit for –version etc cleanly exits (APSW issue 210)

Python 3.11 (APSW issue 326) now works.

PyPy3 compiles and mostly works (APSW issue 323).

3.38.1-r1

All items now have full docstrings including type information. (Previously just one line summaries). Note the C implemented functions and data (ie almost all of APSW) can’t provide the same level of type information as regular Python code.

A pyi file is included which does provide all the typing information in type stub format, and shown by most IDEs.

Removal of code, tests, and documentation only relevant to CPython before 3.7. (Python 3.6 does still work, but is end of life.)

Keyword arguments can be used everywhere.

The statement cache implementation changed from a dictionary to a list. This allows the cache to be used for the same query text multiple times. (The code is also a quarter of the size and simpler).

The default for setup.py’s fetch command is to get the SQLite version corresponding to APSW’s release. (Previously it got the latest release.)

Added constants:

  • SQLITE_INDEX_CONSTRAINT_OFFSET, SQLITE_INDEX_CONSTRAINT_LIMIT

3.37.0-r1

Allow breaking of reference cycles between objects that contain a Connection or Cursor, and also use callbacks from that object (eg busy handler). (APSW issue 314)

This is the last release supporting Python 2 and Python 3 before 3.7. If you still use those Python versions then you should pin to this APSW version. (More information).

Windows Python 3.10 binaries are available to download. The .exe format is no longer available with this Python version.

Fixed custom VFS extension loading failure could leave the error message unterminated.

Updated size of mutex array used by the fork checker

Connections are opened with SQLITE_OPEN_EXRESCODE so open errors will also include extended result codes.

Connection.changes() and Connection.total_changes() use the new SQLite APIs that return 64 bit values (ie can now return values greater than 2 billion).

Added Connection.autovacuum_pages().

Added constants:

  • SQLITE_CONSTRAINT_DATATYPE, SQLITE_OPEN_EXRESCODE

3.36.0-r1

Implemented Connection.serialize() and Connection.deserialize(). They turn a database into bytes, and bytes into a database respectively.

Allow any subclass of VFS to implement WAL, not just direct subclasses. (APSW issue 311)

Added constants:

  • SQLITE_FCNTL_EXTERNAL_READER, SQLITE_FCNTL_CKSM_FILE

3.35.4-r1

Updates for SQLite download url (the year is part of the urls).

Added enable flag for built-in SQL math functions, and enable it by default with –enable-all-extensions.

Use the newer buffer API for Python 3 (old API removed in Python 3.10).

3.34.0-r1

Windows MSI installer files are now provided in addition to the exe files (APSW issue 294), as well as wheels for Python 3.6+. Python 3.9 binaries are also now available. The wheels can be installed via pip.

Added Connection.txn_state()

Added constants:

  • SQLITE_IOERR_CORRUPTFS

3.33.0-r1

Small performance improvement in string handling

apsw module exposes Cursor, Blob, and Backup types (APSW issue 273)

pkg-config is used to detect International Components for Unicode (ICU) sdk when the SQLite ICU extension is enabled. It falls back to icu-config as before. (APSW issue 268).

Added constants:

  • SQLITE_OPEN_SUPER_JOURNAL

3.32.2-r1

Added constants:

  • SQLITE_IOERR_DATA, SQLITE_CORRUPT_INDEX, SQLITE_BUSY_TIMEOUT, SQLITE_FCNTL_CKPT_START, SQLITE_FCNTL_RESERVE_BYTES

Minor documentation updates

3.31.1-r1

Various updates due to year change

Fix deprecated universal newline use in shell (APSW issue 283)

Shell now uses pragma function_list to get list of functions for tab completion

Added constants:

  • SQLITE_DBCONFIG_TRUSTED_SCHEMA, SQLITE_DBCONFIG_LEGACY_FILE_FORMAT, SQLITE_CONSTRAINT_PINNED, SQLITE_OK_SYMLINK, SQLITE_CANTOPEN_SYMLINK, SQLITE_FCNTL_CKPT_DONE, SQLITE_OPEN_NOFOLLOW, SQLITE_VTAB_DIRECTONLY

3.30.1-r1

Added constants:

  • SQLITE_DBCONFIG_ENABLE_VIEW

Updated hashing of SQL statements (APSW issue 274)

Python 3.8 Windows binaries available.

3.29.0-r1

Added constants:

  • SQLITE_DBCONFIG_DQS_DML, SQLITE_DBCONFIG_DQS_DDL, SQLITE_DBCONFIG_LEGACY_ALTER_TABLE

Updated Connection.config() with all current SQLITE_DBCONFIG constants. Also fixes APSW issue 249.

3.28.0-r1

Added constant:

  • SQLITE_DBCONFIG_WRITABLE_SCHEMA

3.27.2-r1

Added constants:

  • SQLITE_CONFIG_MEMDB_MAXSIZE, SQLITE_FCNTL_SIZE_LIMIT

Added support for the geopoly extension (APSW issue 253)

Removed hash optimisation that isn’t useful any more (APSW issue 256)

3.26.0-r1

Added constant:

  • SQLITE_DBCONFIG_DEFENSIVE

3.25.2-r1

Added constants:

  • SQLITE_INDEX_CONSTRAINT_FUNCTION, SQLITE_CANTOPEN_DIRTYWAL, SQLITE_ERROR_SNAPSHOT, SQLITE_FCNTL_DATA_VERSION

Shell output mode now has lines and columns for compatibility (APSW issue 214)

Example now runs under both Python 2 and 3.

3.24.0-r1

Added constants:

  • SQLITE_DBCONFIG_RESET_DATABASE, and support for it in Connection.config()

  • SQLITE_LOCKED_VTAB, and SQLITE_CORRUPT_SEQUENCE extended result codes

Added keywords and updated the shell to use it.

Python 3.7 Windows binaries are provided.

3.23.1-r1

Added constants:

  • SQLITE_DBSTATUS_CACHE_SPILL, SQLITE_FCNTL_LOCK_TIMEOUT

3.22.0-r1

Added constants:

  • SQLITE_DBCONFIG_TRIGGER_EQP, SQLITE_DBCONFIG_MAX

  • SQLITE_READONLY_CANTINIT, SQLITE_ERROR_RETRY, SQLITE_ERROR_MISSING_COLLSEQ, SQLITE_READONLY_DIRECTORY

3.21.0-r1

Added constants:

  • SQLITE_INDEX_CONSTRAINT_ISNULL, SQLITE_INDEX_CONSTRAINT_ISNOT, SQLITE_INDEX_CONSTRAINT_ISNOTNULL, SQLITE_INDEX_CONSTRAINT_IS and SQLITE_INDEX_CONSTRAINT_NE

  • SQLITE_CONFIG_SMALL_MALLOC

  • SQLITE_IOCAP_BATCH_ATOMIC

  • SQLITE_IOERR_ROLLBACK_ATOMIC, SQLITE_IOERR_COMMIT_ATOMIC and SQLITE_IOERR_BEGIN_ATOMIC

  • SQLITE_FCNTL_COMMIT_ATOMIC_WRITE, SQLITE_FCNTL_ROLLBACK_ATOMIC_WRITE and SQLITE_FCNTL_BEGIN_ATOMIC_WRITE

Many spelling fixes (thanks to Edward Betts for the review)

3.20.1-r1

Added SQLITE_DBCONFIG_ENABLE_QPSG constant.

Added shell .open command (APSW issue 240)

3.19.3-r1

No APSW changes.

3.18.0-r1

Updated completions in shell (eg added pragmas).

Resumable Bulk Update (RBU) extension is now built by default for --enable-all-extensions.

Added Connection.set_last_insert_rowid().

3.17.0-r1

No APSW changes.

3.16.2-r1

Python 3.6 builds added.

Added SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE and SQLITE_FCNTL_PDB constants.

3.15.2-r1

No APSW changes.

3.15.1-r1

Added SQLITE_FCNTL_WIN32_GET_HANDLE constant.

3.15.0-r1

Added SQLITE_DBCONFIG_MAINDBNAME constant.

3.14.1-r1

Added SQLITE_DBSTATUS_CACHE_USED_SHARED and SQLITE_OK_LOAD_PERMANENTLY constants.

3.13.0-r1

Added SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION constant.

Added a pip command line in the download page.

3.12.2-r1

Call PyUnicode_READY for Python 3.3 onwards. Fixes APSW issue 208, APSW issue 132, APSW issue 168.

SQLite 3.12 completely changed the semantics of VFS.xGetLastError() in an incompatible way. This required a rewrite of the relevant C, Python and test code. If you implement or use this method then you have to rewrite your code too. Also note that running the test suite from an earlier version of APSW against this or future SQLite versions will result in consuming all memory, swap or address space (an underlying integer changed meaning).

Added SQLITE_CONFIG_STMTJRNL_SPILL and SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER constants.

Added support for SQLITE_CONFIG_STMTJRNL_SPILL in apsw.config().

3.11.1-r1

setup.py attempts to use setuptools if present, before falling back to distutils. This allows setuptools only commands such as bdist_wheel to work. You can force use of distutils by setting the environment variable APSW_FORCE_DISTUTILS to any value. Note that setuptools may also affect the output file names. (APSW issue 207)

3.11.0-r1

The shell dump command now outputs the page size and user version. They were both output before as comments.

Updated SQLite download logic for 2016 folder.

Updated VFS test suite due to changes in SQLite default VFS implemented methods.

Added SQLITE_INDEX_CONSTRAINT_LIKE, SQLITE_INDEX_CONSTRAINT_REGEXP, SQLITE_INDEX_CONSTRAINT_GLOB, SQLITE_IOERR_AUTH, SQLITE_FCNTL_JOURNAL_POINTER, and SQLITE_FCNTL_VFS_POINTER constants.

Allow Connection subclasses for backup api (APSW issue 199).

FTS5 and JSON1 extensions are now built by default for --enable-all-extensions. It is recommended you wait a few more releases for these extensions to mature.

Added a mapping for virtual table scan flags

Use SQLITE_ENABLE_API_ARMOR for extra error checking.

3.9.2-r1

Added SQLITE_IOERR_VNODE constant.

Windows builds for Python 3.5 are now provided.

3.8.11.1-r1

Added SQLITE_FCNTL_RBU and SQLITE_FCNTL_ZIPVFS constants.

setup’s fetch command can now get arbitrary fossil versions. For example specify fossil-e596a6b6.

Update tests due to a change in Python 3.5 (exception returned with invalid strings for system calls changed from TypeError to ValueError).

Adjusted some internal detection related to the fork checker

3.8.10.1-r1

Added deterministic parameter to Connection.create_scalar_function() (APSW issue 187)

Switched to new SQLite API returning 64 bit values for status() (APSW issue 191)

3.8.9-r1

Fixed column description caching which could be preserved between multiple statements in the same execution (APSW issue 186)

Updated documentation building tool to use new database of information from the SQLite site. This is simpler and more reliable. (Previously used site scraping.)

Added SQLITE_AUTH_USER, SQLITE_FCNTL_LAST_ERRNO, SQLITE_FCNTL_WAL_BLOCK, SQLITE_FCNTL_GET_LOCKPROXYFILE, and SQLITE_FCNTL_SET_LOCKPROXYFILE constants.

Removed SQLITE_GET_LOCKPROXYFILE, SQLITE_SET_LOCKPROXYFILE, SQLITE_LAST_ERRNO file control constants. These are deprecated - use the versions with FCNTL in their name (eg SQLITE_FCNTL_GET_LOCKPROXYFILE).

Added mappings for conflict resolution modes, virtual table configuration options and xShmLock VFS flags.

3.8.8.2-r1

No APSW changes.

3.8.8.1-r1

The column description is now cached on first request during a query so getting it is quick if called for every row.

Added SQLITE_CONFIG_PCACHE_HDRSZ and SQLITE_CONFIG_PMASZ constants, and support for them in config().

Added SQLITE_CHECKPOINT_TRUNCATE constant.

Update year in various places to 2015.

3.8.7.3-r1

No APSW changes.

3.8.7.2-r1

Fixed parsing of icu-config flags

3.8.7.1-r1

Added SQLITE_LIMIT_WORKER_THREADS constant

3.8.6-r1

Updated test suite for Python 3.4 unittest garbage collection changes (APSW issue 164 APSW issue 169)

Using the recommended build option of –enable-all-extensions turns on STAT4. Windows binaries include this too.

3.8.5-r1

Added SQLITE_IOCAP_IMMUTABLE and SQLITE_FCNTL_WIN32_SET_HANDLE constants.

3.8.4.3-r1

Added Cursor.fetchone()

3.8.4.2-r1

No APSW code changes. Rebuild due to updated SQLite version.

3.8.4.1-r1

Windows 64 bit binary builds for Python 3.3+ are back - thanks to Mike C. Fletcher for pointing the way

Correct detection of current SQLite version from download page for setup.py fetch command

Tested against Python 3.4 and binaries for Windows.

3.8.3.1-r1

Updated Shell completions for keywords, functions and pragmas.

3.8.3-r1

APSW is now hosted at Github - https://github.com/rogerbinns/apsw

Added SQLITE_RECURSIVE, SQLITE_READONLY_DBMOVED, SQLITE_FCNTL_COMMIT_PHASETWO, SQLITE_FCNTL_HAS_MOVED and SQLITE_FCNTL_SYNC constants.

3.8.2-r1

Added SQLITE_CONFIG_WIN32_HEAPSIZE, SQLITE_CONSTRAINT_ROWID and SQLITE_FCNTL_TRACE constants.

3.8.1-r1

Added SQLITE_CANTOPEN_CONVPATH and SQLITE_IOERR_CONVPATH extended error codes.

Updated pysqlite urls to point to github.

Various minor build/download documentation updates.

3.8.0.2-r1

No APSW code changes. Rebuild due to updated SQLite version.

Updated documentation tips to show how to get detailed diagnostics.

3.8.0.1-r1

No APSW changes. Rebuild due to updated SQLite version.

Windows binaries for Python 3.3 64 bit are no longer available as a Visual Studio update obliterated the ability to compile them, and I have no patience left to fight Microsoft’s tools.

3.8.0-r2

No APSW changes - updated checksums because SQLite changed the released archive to address an autoconf issue on some platforms

3.8.0-r1

Windows binaries for Python 3.3 64 bit are now available after managing to get several pieces of Microsoft software to cooperate.

Fixed shell dump issue when system routines (eg timestamp, username, hostname) couldn’t automatically be promoted to unicode. They are used in comments in the output. (APSW issue 142)

Added SQLITE_DBSTATUS_DEFERRED_FKS, SQLITE_IOERR_GETTEMPPATH, SQLITE_WARNING_AUTOINDEX and SQLITE_BUSY_SNAPSHOT constants.

3.7.17-r1

Removed tests that checked directly calling VFS read/write with negative offsets or amounts returns errors. This version of SQLite no longer returns errors in those circumstances and typically crashes instead.

Various new constants.

3.7.16.2-r1

No APSW changes - just a binary rebuild. Windows users are recommended to upgrade their SQLite version.

3.7.16.1-r1

Updated tables of functions and pragmas in the Shell to match current SQLite version.

3.7.16-r1

Adjust to different SQLite download URLs

Added SQLITE_CONSTRAINT_* and SQLITE_READONLY_ROLLBACK extended error codes

Removed CouchDB virtual table

3.7.15.2-r1

No APSW changes - binary rebuild to pickup new SQLite version

3.7.15.1-r1

Use https (SSL) for SQLite web site references (downloads and documentation links). On some platforms/versions/SSL libraries, Python’s SSL module doesn’t work with the SQLite website so a fallback to http is used - the downloads still have their checksum verified.

3.7.15-r1

Work around changed semantics for error handling when the VFS xDelete method is asked to delete a file that does not exist.

Completely removed all AsyncVFS related code. This extension is no longer maintained nor supported by the SQLite team. WAL is a good way of getting similar functionality.

Added config() support for SQLITE_CONFIG_COVERING_INDEX_SCAN.

Added several new constants: SQLITE_CONFIG_COVERING_INDEX_SCAN, SQLITE_CONFIG_SQLLOG, SQLITE_FCNTL_BUSYHANDLER, SQLITE_FCNTL_TEMPFILENAME, SQLITE_CANTOPEN_FULLPATH, SQLITE_IOERR_DELETE_NOENT

3.7.14.1-r1

Updated setup and test suite so that all files are explicitly closed instead of relying on garbage collection.

Added Windows binaries for Python 3.3. (Only 32 bit as Python doesn’t provide a free way of making 64 bit Windows binaries.)

Updated setup.py to work with changed SQLite download page formatting when detecting latest version.

Due to a Python 3.3.0 regression bug using the csv output mode in the shell can result in bad data or Python crashing. The bug has been fixed for Python 3.3.1 which is due in November 2012.

3.7.14-r2

Fixed an issue with the GIL in the destructor for functions. The bug would be encountered if you create a function with the same name as an existing function and are using an upcoming version of Python (eg 2.7.4). Thanks to Arfrever Frehtes Taifersar Arahesis for finding it (APSW issue 134).

Added shell .print command to match upcoming SQLite shell changes.

3.7.14-r1

Added support for Connection.status() (calls sqlite3_db_status).

The legacy Windows Compiled Help Format documentation is no longer produced - the help compiler setup program can’t cope with modern machines.

3.7.13-r1

Do not free a structure on failure to register a virtual table module as SQLite does that anyway.

Added SQLITE_OPEN_MEMORY constant.

3.7.12.1-r1

No changes to APSW. Binary rebuilds due to SQLite bugfixes.

3.7.12-r1

Re-enabled the asyncvfs.

Added Cursor.description to make DB API interoperability a little easier (APSW issue 131).

Added SQLITE_DBSTATUS_CACHE_WRITE and SQLITE_CANTOPEN_ISDIR constants.

3.7.11-r1

Added SQLITE_ABORT_ROLLBACK and SQLITE_FCNTL_PRAGMA constants.

Added Connection.readonly().

Changed Connection.filename which used to return the string used to open the database and now returns the absolute pathname.

Added Connection.db_filename().

3.7.10-r1

The default sector size returned in VFS routines is 4,096 to match SQLite’s new default.

Several links to SQLite tickets and documentation were updated (APSW issue 122).

The async vfs is disabled due to a bug in its code that leads to random memory reads when dealing with filenames.

Added SQLITE_CONFIG_GETPCACHE2, SQLITE_CONFIG_GETPCACHE2, SQLITE_FCNTL_POWERSAFE_OVERWRITE, SQLITE_FCNTL_VFSNAME and SQLITE_IOCAP_POWERSAFE_OVERWRITE constants.

Fix shell dumping when SQLite doesn’t strip trailing comments from view declarations (discussed here)

Added a URIFilename class to encapsulate how SQLite provides URI parameters to VFS routines (APSW issue 124).

Compatibility break: Depending on flags your VFS xOpen method may get a URIFilename or a string for the filename. You can still pass either to the VFSFile.

Compatibility break: The Virtual File System (VFS) code used to always run strings you provided through VFS.xFullPathname(). This isn’t possible with URI pathnames so that code has been removed. If you construct filenames for VFS.xOpen() directly (ie bypassing the SQLite database open call) then you must call VFS.xFullPathname() yourself first to ensure relative pathnames are turned into absolute pathnames. The SQLite API guarantees that filenames passed to VFS.xOpen() are exactly what was returned from VFS.xFullPathname().

3.7.9-r1

Added SQLITE_DBSTATUS_CACHE_HIT, SQLITE_DBSTATUS_CACHE_MISS and SQLITE_FCNTL_OVERWRITE constants.

3.7.8-r1

Updated documentation and tests due to an undocumented change in VFS xDelete semantics.

Added SQLITE3_FCNTL_PERSIST_WAL and SQLITE3_FCNTL_WIN32_AV_RETRY file controls.

Wrapped sqlite3_sourceid (APSW issue 120)

3.7.7.1-r1

Added SQLITE_CONFIG_URI and support for it in config(), and the open flag SQLITE_OPEN_URI. This makes it easy to use URI filenames.

The Shell now uses URI filenames by default.

New extended error constants: SQLITE_CORRUPT_VTAB, SQLITE_IOERR_SEEK, SQLITE_IOERR_SHMMAP, SQLITE_READONLY_CANTLOCK and SQLITE_READONLY_RECOVERY.

64 bit platforms (LP64 - most non-Windows) and Python 2: The Python int type is returned for 64 bit integers instead of Python long type.

3.7.6.3-r1

When invoking the shell by calling apsw.shell.main() it will not become interactive if you supply SQL commands as command line arguments. This is to have the same behaviour as the SQLite shell (APSW issue 115).

The shell has a .find command making it easy to search for values across all columns of some or all tables.

The shell has a .autoimport command making it easy to import a data file automatically deducing separators, column names and data types.

Detect attempted use of a cursor as input data for itself.

3.7.6.2-r1

Fixed APSW issue 117 where the shell could report an I/O error on changing output target for some operating systems. Thanks to Edzard Pasma for finding and diagnosing this.

Added support for VFS version 3 which allows redirecting system calls used by some VFS implementations (eg for testing or sandboxing).

NotFoundError exception added.

Added Connection.config().

Updated Connection.wal_checkpoint() to use sqlite3_wal_checkpoint_v2 which provides more fine grained control over checkpointing and returns useful information.

3.7.5-r1

Backwards incompatible change in SQLite 3.7.5 for handling of xFileControl(). If you implement this method in a VFS then you must return True or False to indicate if the operation was understood. Connection.file_control() now returns that value. (Previously you could not tell the difference between an op being understood and an error resulting, or the op not being understood at all.)

Windows Python 3.2 binaries now available.

3.7.4-r1

Binary downloads for Windows 64 bit Python versions 2.6 and above including Python 3 are now available.

apsw.soft_heap_limit() now uses sqlite3_soft_heap_limit64 so you can provide values larger than 2GB. It is now also able to return the previous value instead of None.

Improve getting shell timer information for 64 bit Windows.

Blob.reopen() is implemented.

FTS4 is enabled and in the binary builds. Note that it is an augmentation of FTS3 rather than totally separate code and described in the SQLite documentation.

3.7.3-r1

You can read blobs into pre-existing buffers using Blob.read_into(). (This is more efficient than allocating new buffers as Blob.read() does and then copying.) (APSW issue 109).

Fixed bug with unicode output in CSV mode in the shell.

sqlite_create_function_v2 now means that some housekeeping APSW did can be pushed back onto SQLite and the consequent deletion of some code

3.7.2-r1

No changes to APSW. Upgrading to this version of SQLite is recommended.

3.7.1-r1

Updated various constants including SQLITE_FCNTL_CHUNK_SIZE used with Connection.file_control().

Fixed Unicode output with some file objects from the shell (APSW issue 108).

With the shell, you can specify handling of characters not present in the output encoding (eg replace to use ‘?’ or similar, ignore, xmlcharrefreplace etc). For example:

.encoding cp437:replace

3.7.0.1-r1

Fixed issue when using a tracer and a context manager fails to commit.

3.7.0-r1

Added several new constants.

Write Ahead Logging is supported. You can make all databases automatically use WAL mode if available by using connection hooks.

Added format_sql_value() for generating a SQL syntax string from a value. This is implemented in C and is significantly faster than doing the same formatting in Python.

Using the above function and other tweaks the Shell dumper is now three to four times faster. Thanks to Nikolaus Rath for pointing out the problem and providing test data.

The shell now does colour highlighting making it easy to visually distinguish prompts, errors, headers and value types when outputting to a terminal. See the –no-colour argument and .colour command. Those of you in the two countries that have not adopted the metric system may also omit the ‘u’. For Windows users you won’t get colour output unless you install colorama

When using the context manager (with statement) of a Connection and the exit commit had an error, then the transaction is rolled back. This could occur if SQLite had buffered the transaction entirely in memory and a non-eager transaction lock had been obtained. Thanks to Geoff Ness for finding the problem. (APSW issue 98).

Fixed bug when an error is returned creating an instance of a virtual table (eg an invalid column name). Before the fix you would get the previous error message or a crash. Thanks to Jose Gomes for finding the problem. APSW issue 103

There is now a PPA for Ubuntu users that is kept up to date with APSW and SQLite at https://launchpad.net/~ubuntu-rogerbinns/+archive/apsw which has the latest SQLite embedded statically inside (ie system SQLite is ignored) and has all the extensions enabled: FTS3, RTree, ICU, asyncvfs

If you open VFS files directly then the filename is always run through xFullPathname first. SQLite guarantees this behaviour but the existing VFS code was not doing that for direct opens. Opens from SQLite were doing it.

Fixed error where apsw.connection_hooks were being run before the statement cache was initialised which would result in a crash if any hooks executed SQL code.

3.6.23.1-r1

Shell CSV output under Python 3.1 is corrected (work around Python 3.1 StringIO bug/incompatibility with other Python versions).

Simplified access to the shell’s database from the API.

Added a shell example.

3.6.23-r1

If setup is downloading files and an error occurs then it retries up to 5 times.

Added SQLITE_CONFIG_LOG and SQLITE_OPEN_AUTOPROXY constants.

Added compile_options which tells you what compilation defines SQLite was compiled with.

Added log() to call the SQLite logging interface, and updated config() so you can set log destination function.

3.6.22-r1

Made it possible to run distutils ‘sdist’ from an already produced source that was made from ‘sdist’. This was necessary for some Python virtual package environments. Note that the recursive result does not include the HTML help as distutils has no way of including content in a reparented location. APSW issue 89

Various settings are output as pragma statements when making a dump such as page size, encoding, auto_vacuum etc. The pragmas are commented out. APSW issue 90

3.6.21-r1

Source and binary files are now digitally signed which means you can verify they have not been tampered with. See Verifying your download for instructions.

The pragmas generated for a shell dump are emitted outside the transaction as they have no effect inside the transaction.

Removed some unintentional logging code left in CouchDB virtual table code.

3.6.20-r1

Support for Python 3.0 has been dropped as it has been end of lifed. Use Python 3.1 onwards.

Changes to how some statements are prepared to allow the new RANGE and LIKE optimisations with bound variables introduced in SQLite 3.6.20 to be used. See APSW issue 85 for the long and gory details.

You can now access CouchDB using a virtual table. This lets you easily bidirectionally transfer data between SQLite and CouchDB as well as work on data in both sources at the same time. Other example uses are in the documentation.

Shell changes:

  • .dump command now outputs views in the order they were created rather than alphabetical as views could reference each other. APSW issue 82

  • .dump command now outputs the user_version as a comment. It is used by some programs (such as Firefox) to keep track of the schema version.

  • Can now output in JSON.

  • Fixed APSW issue 83 - exception if history file didn’t exist

  • You can right justify output in column mode by specifying negative widths. APSW issue 84

  • You no longer get a traceback doing completions if there is a virtual table in the database but the module is not loaded. APSW issue 86

  • You can now get detailed tracebacks including local variables using the “.exception ON” command. This is useful when developing virtual tables and similar functionality.

  • You can now terminate a SQL statement with “go” or “/” on a line by itself.

3.6.19-r1

Backwards incompatible change Fixed APSW issue 72 where APSW wasn’t zero basing virtual table BestIndex() constraints returned as documented. If you have working BestIndex code then you need to update it for this release. Thanks to Lefteris for finding this issue.

Backwards incompatible change The complete() method has moved from Connection to apsw where it should have been all along. You should now call apsw.complete() instead. (It even had an example showing it to be part of the module and not a specific connection!)

There is now an interactive shell very similar to that provided by SQLite. You can embed it in your own program, inherit from it to provide more commands and output modes, or just run it like this:

$ python -c "import apsw ; apsw.main()"

Added the SQLITE_LIMIT_TRIGGER_DEPTH, SQLITE_OPEN_PRIVATECACHE and SQLITE_OPEN_SHAREDCACHE constants.

The setup.py file now has the various options available made applicable to appropriate commands only. Read the updated documentation.

You can now specify build –enable=stat2 to setup.py to enable advanced statistics gathering for query planning.

setup.py can automatically fetch the asyncvfs extension for you. If the source is present when APSW is built then it will be automatically included and async_initialize called.

A fork_checker() is available which turns on detection when you have used SQLite objects across a fork (a very bad thing). This is possible on Unix like operating systems, especially if you use the multiprocessing module.

Extension loading is now compiled in by default when using the amalgamation and compiled out when using existing libraries. This is more likely to match your machine. You can use –omit=load_extension or –enable=load_extension to the build/build_ext commands to explicitly disable/enable extension loading. APSW issue 67

setup.py will now abort on a download that has no checksum. See more information on checksums.

setup.py can also fetch the version of SQLite currently under development before a release. Use –version=fossil.

Updated which code uses experimental SQLite APIs based on changes in SQLite. The test suite will also work correctly with experimental on or off. (It is on by default.)

3.6.18-r1

The APSW license has been updated to allow you (at your option) to use any OSI approved license.

The speedtest has been updated to (optionally) use unicode characters and to (optionally) increase the sizes of data items.

Fixed error handling code to not record SQLite error strings in some situations where it was not necessary. This results in the code executing a little faster.

3.6.17-r1

APSW has migrated from Subversion to Mercurial for source code control. Hosting remains at Google Code

Updated a test due to VFS xUnlock errors now being ignored sometimes by SQLite (cvstrac 3946).

The downloads page in the help didn’t mention the Windows Python 3.1 installer.

Running the test suite is now integrated into setup.py so you can do the building and testing all in one go. Sample command line:

$ python setup.py install test

The test suite will now check the functionality of the FTS3, RTree and ICU extensions if they were included. (The Windows binary distribution includes FTS3 and RTree by default.)

Fixed APSW issue 55 where FTS3 was unintentionally omitted from the Windows binary distribution.

Various documentation updates.

3.6.16-r1

Windows binary distribution includes Python 3.1.

Trivial tweaks to keep MSVC happy.

3.6.15-r1

Fixed APSW issue 50 where Blob.read() was returning None on end of file instead of the documented (and correct) empty string/bytes.

Corrected spelling of option in apswtrace and only output CURSORFROM if SQL tracing is on.

3.6.14.2-r1

Updated test code because SQLite 3.6.15 returns a different error code on trying to register a function with too many arguments (see cvstrac 3875).

3.6.14.1-r1

Changed some internal symbol names so they won’t clash with similar new ones used by SQLite in the amalgamation.

Added apsw.using_amalgamation so you can tell if APSW was compiled using the SQLite amalgamation. Using the amalgamation means that SQLite shared libraries are not used and will not affect your code.

Added a checksums file so that when setup.py downloads SQLite, we know it hasn’t been tampered with. (The –fetch-sqlite argument can be used to automatically download SQLite.)

3.6.13-r1

Added SQLITE_LOCKED_SHAREDCACHE extended error code.

Updated tests as the VFS delete error handling code in SQLite now returns the same high level error code between Windows and non-Windows.

The CHM format help file produced by the Windows HTML Help Compiler is viewable again under Windows HTML Help Viewer.

3.6.11-r1

You can now use the hot backup functionality introduced in SQLite 3.6.11.

Updated a VFS test to reflect changes in SQLite underlying error handling. (Previously SQLite almost always returned FullError on any write that had an error but now returns SQLError.)

Changed close methods so that Connections can be released earlier.

In prior releases a closed cursor could still be used (reincarnated). That is no longer the case and you will get CursorClosedError.

3.6.10-r1

You can use the database as a context manager as defined in PEP 0343. When you use with a transaction is started. If the block finishes with an exception then the transaction is rolled back, otherwise it is committed. See Connection.__enter__() for an example.

Behind the scenes the savepoint functionality introduced in SQLite 3.6.8 is used. Consequently Connection with blocks can be nested. If you use Connection level execution tracers then they will be called with the savepoint SQL statements.

You can also use blobs as a context manager which ensures it is always closed when finished using it. See Blob.__enter__() for an example.

Added constants:

  • SQLITE_SAVEPOINT (authorizer code)

  • SQLITE_IOERR_CLOSE (extended result code)

  • SQLITE_IOERR_DIR_CLOSE (extended result code)

  • New mapping: SQLITE_FCNTL_LOCKSTATE, SQLITE_GET_LOCKPROXYFILE, SQLITE_SET_LOCKPROXYFILE, SQLITE_LAST_ERRNO. SQLite does not document the purpose of these except the first one.

Updated Virtual File System (VFS) test code. SQLite’s routines that call VFSFile.xTruncate() used to ignore errors but now return an error to the caller. VFSFile.xFileControl() is now called so a user implemented one must call any base it inherits from for SQLite to function normally.

Updated the xDlSym VFS routine to have the different but compatible type signature as changed in SQLite 3.6.7 to deal with pedantic compiler warnings.

Fixed bug in apswtrace that could result in poorly formatted times. Leading comments are also stripped for queries printed in the final reports. You can also request subsets of the reports.

The speedtest script will now fallback to the Python builtin sqlite3 module if it can’t find an externally installed pysqlite.

3.6.6.2-r1

Windows binary download for Python 3.0 is available.

Various changes in data structures and containers to reduce code size.

Changed the code to handle SQLite errors to only use Python functionality and no operating system functionality (thread local storage). This also addresses APSW issue 36 where Vista was not binary compatible with XP. Thanks to Rudolf Gaertner for assistance in detecting and diagnosing this issue.

Connections, cursors and blobs can be used by weak references.

You can now install Connection wide execution and row tracers.

The callbacks for execution and row tracers have a different signature to include the cursor the execution or row happened on. This is a backwards incompatible change. See tracing for details.

Due to popular demand, added Cursor.fetchall(). This is a longer way of typing list(cursor).

Added attributes to the Connection class - filename, open_flags and open_vfs. These let you track how the database was opened.

Added a apswtrace script to allow easy SQL tracing without having to modify your code.

Revert to using older SQLite APIs in order to work around cvstrac 2158. (This also saves a little bit of SQLite memory usage). The user visible effect was that you could get different exceptions and error text depending on whether a query was already in the statement cache or if you were multi-threading. As an example, if you have a query that used an unknown collation then SQLite’s prepare returns SQLITE_ERROR with error text about the bad collation. If a query had already been prepared, the collation removed and then run the new SQLite routines are returning SQLITE_SCHEMA and generic schema changed error text. Changing user defined functions could also cause a previously correct query to become invalid.

3.6.5-r1

The distribution now includes a speedtest script. You can use this to see how APSW performs relative to pysqlite, or to track performance differences between SQLite versions. The underlying queries are derived from SQLite’s speed test

The statement cache was completely rewritten. It uses less memory and scales significantly better.

It was possible to get a deadlock between the Python GIL and the SQLite database mutex when using the same Connection across multiple threads. Fixed by releasing the GIL in more places and added test that inspects the source to verify GIL/mutex handling. Thanks to amicitas reporting this as APSW issue 31

SQLite’s API has been extended in 3.6.5 so that errors can be retrieved in a thread safe manner. APSW now uses this API.

As a consequence of the prior two changes it is now possible and safe to use the same Connection across as many threads as you want concurrently.

Documentation is now done using Sphinx which was adopted by Python 2.6 and 3. This has allowed for richer documentation and more output formats such as PDF and Windows CHM format.

The binary distribution for Windows includes the full text search (FTS) and Rtree extensions. See also setup.py commands and their options.

The source structure and files were reorganized to make it clearer where things are implemented and to make automatic extraction of documentation easier.

3.6.3-r1

You can now write your own Virtual File System (VFS) in Python. You can also inherit from an existing VFS making it easy to augment or override small bits of behaviour without having to code everything else. See the example where database files are obfuscated by XORing their contents.

setup.py now takes an optional –fetch-sqlite[=ver] argument to automatically download and use the latest SQLite amalgamation (or a specified version). On non-Windows platforms it will also work out what compile flags SQLite needs (for example HAVE_USLEEP, HAVE_LOCALTIME_R). Several other options to setup.py are also available to control enabling/omitting certains features and functionality. See building for further details.

APSW checks that SQLite was compiled to be threadsafe

Added new constants:

  • SQLITE_IOERR_ACCESS, SQLITE_IOERR_CHECKRESERVEDLOCK and SQLITE_IOERR_LOCK extended result codes

  • SQLITE_OPEN_NOMUTEX and SQLITE_OPEN_FULLMUTEX open flags

  • Several new SQLITE_CONFIG and SQLITE_STATUS codes

Wrapped several new SQLite apis:

The following experimental apis are not wrapped as there is nothing useful you can do with them (yet):

Restored prior behaviour regarding Python ints and longs returning int for numbers fitting in signed 32 bit. This only affects Python 2 as Python 3 uses long exclusively. Thanks to Joe Pham for reporting this as APSW issue 24

Added Connection.sqlite3_pointer() method to help with APSW issue 26

3.5.9-r2

APSW now works with Python 3 (you need 3.0b1 or later).

(APSW issue 17) Removed the SQLITE_MAX_ constants since they could be unreliable (eg APSW can’t tell what a shared library was compiled with). A workaround is documented in Connection.limit().

3.5.9-r1

APSW is now hosted at https://code.google.com/p/apsw

You can use this with SQLite 3.5.9 onwards.

SQLite now provides the source all amalgamated into one file which improves performance and makes compilation and linking of SQLite far easier. The build instructions are updated.

SQLITE_COPY authorizer code and SQLITE_PROTOCOL error code are no longer used by SQLite, but the values are left in apsw for backwards compatibility

SQLITE_IOERR_DELETE, SQLITE_IOERR_BLOCKED and SQLITE_IOERR_NOMEM

Connection.interrupt() can be called from any thread

SQLite has implementation limits on string and blob lengths (roughly constrained to fitting within a signed 32 bit integer - less than 2GB) which weren’t checked. Using a 64 bit Python 2.5+ (as I do) it would have been possible to destroy memory and crash the program. Consequently APSW has length checks to ensure it doesn’t happen. SQLite now has further limits checking which cover other things as well such as maximum number of virtual machine opcodes, maximum number of variables etc. These are very useful if you are taking in SQL statements from elsewhere. Call Connection.limit()

A rename method was added for virtual tables.

SQLite 3.5 removed the requirement that all operations on a connection be done in the same thread. Consequently all code that enforced the restriction has been removed from APSW.

You no longer have to call Connection.close(). This was previously a requirement to ensure that the correct thread was used (destructors otherwise run in any thread). It is however still a good idea to do so since you can catch exceptions when close is called but not if you let the destructor do the closing.

SQLite now has incremental blob I/O

APSW issue 4 which could lead to generic error messages was fixed in SQLite 3.5.9.

Fixed APSW issue 1 error in example code for virtual tables which caused filename errors on Windows.

Fixed APSW issue 15 releasing the GIL around calls to sqlite3_prepare.

Fixed APSW issue 7 ensuring that extension module filenames are converted to utf8.

Use the sqlite3_open_v2 interface which allows specifying which vfs to use. This release does not allow you to write your own vfs as the SQLite vfs interface is being changed for SQLite 3.6.

Used new SQLite functions that keep track of when virtual tables and collations are no longer used so they can be released. Previously APSW also had to keep track duplicating effort.

Improved test coverage a few more percent.

The statement cache now defaults to the same number of entries as pysqlite (100). You can however specify more or less as needed.

Connection.collation_needed() was implemented.

3.3.13-r1

As of this release, APSW is now co-hosted with pysqlite meaning there is one site to go to for your Python SQLite bindings. (Both projects subsequently moved to Google Code.)

You can use this with SQLite 3.3.13 onwards. There were no API changes in SQLite 3.3.10 to 3.3.13 although some internal bugs were fixed and the 3.3.13 release is recommended over the earlier version.

Thanks to Ed Pasma for highlighting these issues:

  • Connection.interrupt() can be safely called from any thread.

  • Empty statements or those consisting entirely of whitespace do not cause misuse errors (internally SQLite started returned NULL pointers for those statements, and sqlite3_step didn’t like being passed the NULL pointer).

  • Changed special handling of SQLITE_BUSY error to be the same as other errors. The special handling previously let you restart on receiving busy, but also hung onto statements which could result in other statements getting busy errors.

3.3.10-r1

You can use this with SQLite 3.3.10 onwards.

Added a statement cache that works in conjunction with the sqlite3_prepare_v2 API. A few issues were exposed in SQLite and hence you must use SQLite 3.3.10 or later.

3.3.9-r1

You can use this with SQLite 3.3.9 onwards.

SQLite added sqlite3_prepare_v2 API. The net effect of this API update is that you will not get SQLITE_SCHEMA any more. SQLite will handle it internally.

3.3.8-r1

You can use this with SQLite 3.3.8 onwards. There was an incompatible API change for virtual tables in SQLite 3.3.8.

Virtual tables updated for new api.

You must call close() on connections. You can also call close() on cursors, but it usually isn’t necessary.

All strings are returned as unicode.

PyErr_WriteUnraisable() was used for errors in destructors. Unfortunately it is almost completely useless, merely printing str of the object and exception. This doesn’t help in finding where in your code the issue arose so you could fix it. An internal APSW implementation generates a traceback and calls sys.excepthook(), the default implementation of which prints the exception and the traceback to sys.stderr.

Note

The line number reported in the traceback is often off by 1. This is because the destructors run “between” lines of code and so the following line is reported as the current location.

Authorizer codes SQLITE_CREATE_VTABLE, SQLITE_DROP_VTABLE and SQLITE_FUNCTION added.

SQLite extended result codes are available - see Exceptions and Errors for more detail.

apsw.connection_hooks added so you can easily register functions, virtual tables or similar items with each Connection as it is created.

Added mapping dicts which makes it easy to map the various constants between strings and ints.

3.3.7-r1

Never released as 3.3.8 came along.

You can use this release against SQLite 3.3.7. There were no changes in the SQLite 3.3.6 API from 3.3.5. In SQLite 3.3.7 an API was added that allowed removing a chunk of duplicate code. Also added were Virtual Tables and loading of external modules (shared libraries).

APSW had the following changes:

  • Even more test cases added (you can’t have too many tests :-)

  • When exceptions occur, dummy frames are added to the traceback in the C code. This makes it a lot easier to tell why code was called if you encounter an exception. See augmented stack traces for details.

  • String values (traditional and Unicode) work correctly if they have embedded NULL characters (ie not truncated at the NULL).

  • You can load SQLite shared library extensions.

3.3.5-r1

You can use this release against any release of SQLite 3 from 3.3.5 onwards. A bug was also fixed when reporting an error during the cleanup of an aggregate function if there had also been an error in the step function. (PyErr_WriteUnraisable(NULL) crashed on some versions of Python but not others.)

SQLite added several functions for returning metadata about result column sets. You have to compile SQLite with SQLITE_ENABLE_COLUMN_METADATA to get them. This is not the default for SQLite. I don’t believe these are generally useful except in some corner cases and so they aren’t wrapped. However please shout if you do need them. Note that Cursor.get_description() will already give you generally useful information. (Also see the pragmas)

The test code has been converted into using the unittest module. Run python tests.py -v to get the tests run. There should be no errors.

Updated code to work correctly with new Py_ssize_t introduced in Python 2.5. See 64 bit hosts, Python 2.5+ for more details on how Python and SQLite handle 64 bit sized items.

The following functions were added to SQLite and are wrapped. They are all functions defined on the Connection object or apsw module:

3.2.7-r1

You can use this release against any release of SQLite 3.

SQLite 3.2.7 has several bug fixes. The undocumented experimental function sqlite3_profile was added, but it not present in apsw yet.

The author of pysqlite has improved it considerably since APSW was originally written. The differences section has been updated to reflect those improvements in pysqlite.

SQLITE_INTERNAL and SQLITE_NOTFOUND error codes are not used according to 3.2.7 header file. They are still present in APSW for backwards compatibility.

Changed the build instructions so configure is run on non-Windows platforms.

Fixed a bug caused by an overly helpful error message trying to tell you how many bindings you supplied that crashed if you didn’t supply any.

Changed when an error in the step function for an aggregate is reported due to limitations in SQLite.

3.2.2-r1

You can use this release against any release of SQLite 3.

SQLite 3.2.2 API removed sqlite3_global_recover. That function was not wrapped in APSW. Note that SQLite 3.2.2 contains a bug fix that applies when you use 64 bit integer primary keys (32 bit ints are fine).

3.2.1-r1

You can use this release against any release of SQLite 3.

There are no changes in APSW except to correct an error in the example code (collations are registered against the connection not the cursor)

SQLite 3.2.1 had one addition in the stable C API, which was a new function named sqlite3_global_recover. That function is not applicable for wrapping in APSW.

3.1.3-r1

You can use this release against any release of SQLite 3.

The text string returned by apsw.Error used to say “apsw.APSWException” and has been changed to “apsw.Error”. This is purely cosmetic and helps make clear what the class is. (The old string was what the original class name was in an earlier version of the code.)

Added SQLITE_ALTER_TABLE and SQLITE_REINDEX constants for the authorizer function. (These constants were introduced in SQLite 3.1.3).

Changed various C++-isms into standard C (eg // comments and the placing of some CHECK_THREAD macro calls).

Added module level function apsw_version() which returns the version of APSW.

SQLite 3.1.3 had no changes in the stable C API other than what is mentioned above. There were some new experimental functions added which are not currently documented on the SQLite website, which are not wrapped by APSW. Please contact me if you believe they will remain in SQLite and you would like them wrapped:

  • sqlite3_sleep An alternative function which sleeps for a specified number of milliseconds can be provided. By default SQLite just uses the standard operating system call.

  • sqlite3_expired This function is internal to statement execution. It would apply to the implementation of Cursor.executemany() and could in theory provide a marginal improvement in performance.

  • A global variable sqlite3_temp_directory can be used before any databases are opened to set where temporary files are created. By default SQLite just uses the standard operating system mechanisms.

3.0.8-r3

There are no functional changes. The only changes were to correct some variable names in the example code (they were cut and pasted from the test code which used different names) and to make the source zip file extract its contents into a sub-directory which is the more typical way of packaging that sort of thing.

3.0.8-r2

All remaining functionality in the C API for SQLite 3.0.8 is now available.

Finished this documentation.

3.0.8-r1

Initial release

Renaming

Early APSW exposed some method and attribute names not complying with Python naming conventions as documented in PEP 8. For example exceptionfor instead of exception_for. This has now been corrected with the compliant names being documented and used in examples. The old names are still available maintaining backwards compatibility.

The change happened in version 3.43.2.0.

Class

Name

Old name

apsw

apsw.apsw_version()

apswversion

apsw.enable_shared_cache()

enablesharedcache

apsw.exception_for()

exceptionfor

apsw.memory_high_water()

memoryhighwater

apsw.memory_used()

memoryused

apsw.release_memory()

releasememory

apsw.soft_heap_limit()

softheaplimit

apsw.sqlite_lib_version()

sqlitelibversion

apsw.vfs_names()

vfsnames

Backup

Backup.page_count

pagecount

Blob

Blob.read_into()

readinto

Connection

Connection.blob_open()

blobopen

Connection.cache_flush()

cacheflush

Connection.collation_needed()

collationneeded

Connection.create_aggregate_function()

createaggregatefunction

Connection.create_collation()

createcollation

Connection.create_module()

createmodule

Connection.create_scalar_function()

createscalarfunction

Connection.enable_load_extension()

enableloadextension

Connection.exec_trace

exectrace

Connection.file_control()

filecontrol

Connection.get_autocommit()

getautocommit

Connection.get_exec_trace()

getexectrace

Connection.get_row_trace()

getrowtrace

Connection.load_extension()

loadextension

Connection.overload_function()

overloadfunction

Connection.row_trace

rowtrace

Connection.set_authorizer()

setauthorizer

Connection.set_busy_handler()

setbusyhandler

Connection.set_busy_timeout()

setbusytimeout

Connection.set_commit_hook()

setcommithook

Connection.set_exec_trace()

setexectrace

Connection.set_profile()

setprofile

Connection.set_progress_handler()

setprogresshandler

Connection.set_rollback_hook()

setrollbackhook

Connection.set_row_trace()

setrowtrace

Connection.set_update_hook()

setupdatehook

Connection.set_wal_hook()

setwalhook

Connection.sqlite3_pointer()

sqlite3pointer

Connection.total_changes()

totalchanges

Cursor

Cursor.exec_trace

exectrace

Cursor.get_connection()

getconnection

Cursor.get_description()

getdescription

Cursor.get_exec_trace()

getexectrace

Cursor.get_row_trace()

getrowtrace

Cursor.row_trace

rowtrace

Cursor.set_exec_trace()

setexectrace

Cursor.set_row_trace()

setrowtrace