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.
No APSW changes - just a binary rebuild. Windows users are recommended to upgrade their SQLite version.
Adjust to different SQLite download URLs
Added SQLITE_CONSTRAINT_* and SQLITE_READONLY_ROLLBACK extended error codes
Removed CouchDB virtual table
No APSW changes - binary rebuild to pickup new SQLite version
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.
Work around changed semantics for error handling when the VFS xDelete method is asked to delete a file that does not exist.
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
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 ouput 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.
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.
The legacy Windows Compiled Help Format documentation is no longer produced - the help compiler setup program can’t cope with modern machines.
Do not free a structure on failure to register a virtual table module as SQLite does that anyway.
Added SQLITE_OPEN_MEMORY constant.
No changes to APSW. Binary rebuilds due to SQLite bugfixes.
Re-enabled the asyncvfs.
Added SQLITE_DBSTATUS_CACHE_WRITE and SQLITE_CANTOPEN_ISDIR constants.
Added SQLITE_ABORT_ROLLBACK and SQLITE_FCNTL_PRAGMA constants.
Changed Connection.filename which used to return the string used to open the database and now returns the absolute pathname.
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)
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().
Added SQLITE_DBSTATUS_CACHE_HIT, SQLITE_DBSTATUS_CACHE_MISS and SQLITE_FCNTL_OVERWRITE constants.
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)
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.
When invoking the shell by calling apsw.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.
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.
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.filecontrol() 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.
Binary downloads for Windows 64 bit Python versions 2.6 and above including Python 3 are now available.
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.
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
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:
Fixed issue when using a tracer and a context manager fails to commit.
Added several new constants.
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. (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. (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.
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.
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.
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. (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. (Issue 90).
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.
Support for Python 3.0 has been dropped as it has been end of lifed. Use Python 3.1 onwards.
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.
Backwards incompatible change Fixed 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!)
$ python -c "import apsw ; apsw.main()"
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 Asynchronous VFS extension for you. If the source is present when APSW is built then it will be automatically included and the API provided.
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. (Issue 67).
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.)
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.
Updated a test due to VFS xUnlock errors now being ignored sometimes by SQLite (SQLite ticket #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 issue 55 where FTS3 was unintentionally omitted from the Windows binary distribution.
Various documentation updates.
Windows binary distribution includes Python 3.1.
Trivial tweaks to keep MSVC happy.
Corrected spelling of option in apswtrace and only output CURSORFROM if SQL tracing is on.
Updated test code because SQLite 3.6.15 returns a different error code on trying to register a function with too many arguments (see SQLite ticket #3875).
Changed some internal symbol names so they won’t clash with similar new ones used by SQLite in the amalgamation.
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.)
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.
You can now use the hot backup functionality introduced in SQLite 3.6.11.
Changed close methods so that Connections can be released earlier.
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.
- 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.
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 issue 36 where Vista was not binary compatible with XP. Thanks to Rudolf Gaertner for assistance in detecting and diagnosing this issue.
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 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 SQLite ticket #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.
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 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.
The source structure and files were reorganized to make it clearer where things are implemented and to make automatic extraction of documentation easier.
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:
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 issue 24.
Added Connection.getsqlite3pointer() method to help with issue 26.
APSW now works with Python 3 (you need 3.0b1 or later).
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
Issue 4 which could lead to generic error messages was fixed in SQLite 3.5.9.
Fixed issue 1 error in example code for virtual tables which caused filename errors on Windows.
Fixed issue 15 releasing the GIL around calls to sqlite3_prepare.
Fixed 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.collationneeded() was implemented.
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:
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.
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.
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.
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.
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.
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.
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:
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.getdescription() 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.
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.
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).
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.
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 apswversion() 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:
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.
All remaining functionality in the C API for SQLite 3.0.8 is now available.
Finished this documentation.