IMPORTANT INCOMPATIBILITY CHANGE – please read carefully:

Up to this version, the sqlite3 type was an untyped Pointer. This worked fine when used correctly, but caused hard to identify errors when not. Incompatible variable types are usually detected by the compiler's type checking. But as a matter of fact, Delphi does not generate type errors for untyped Pointer. Code would compile happily if sqlite3 was used instead of sqlite3_stmt, and Delphi would not even object to TDISQLite3Database in place of sqlite3.

Instead, problems surfaced at runtime. They often materialized as access violations with no meaningful error message and could drive helpless developers into despair. When contacting support, some of them even attributed the problem to a bug in DISQLite3. Even though this was not correct, they certainly had a point. So after careful consideration I decided to change all untyped Pointer types to pointers to record structures.

For record pointers, Delphi can detect type conflicts at compile time, so the likelihood of critical runtime errors is greatly reduced. For most stringent type checking, I recommend to compile with "Typed @ operator" always enabled. This can be set as a project option or in code using {$T+} or {$TYPEDADDRESS ON}. With typed @ operator enabled, Delphi also detects incompatible output variable types passed to sqlite3_open(), sqlite3_prepare_v2(), and similar routines.

Unfortunately, the modified strong typing might ask to change existing code before it compiles. Luckily, however, with {$TYPEDADDRESS ON} Delphi now catches all type incompatibilities so they can be fixed easily. For smaller projects, this is a simple search and replace (older IDEs) or code refactoring (newer ones).

Automatic conversion for larger projects is at hand here:

• http://www.yunqa.de/delphi/downloads/DISQLite3_Type_Converter.zip

DISQLite3_Type_Converter.zip contains a precompiled version of the JVCLConverter utility plus the conversion data file. It takes less than a minute to update the DISQLite3 Demos from previous versions: Run JVCLConverter.exe, select the source code folder, and convert. Converting other projects should be just as quickly.

Limit to auto-conversion: The PPointerArray type (mostly used in sqlite3_create_function() callbacks) is a standard Delphi type. Converting this automatically might cause unintended side effects if used outside the DISQLite3 context. Therefore PPointerArray must be manually changed to sqlite3_value_ptr_array_ptr.

This table lists all modified, renamed, or deleted type names and their new counterparts. Only those at the top of the list should be in frequent use:

Bugs fixed in this version:

• Problems that might cause incorrect query results:
  • Queries that have a WHERE clause of the form (x AND y) OR z where x and z come from one table of a join and y comes from a different table.
  • Queries where the use of the CAST operator in the WHERE clause could lead to incorrect results if the column being cast to a new datatype is also used in the same WHERE clause without being cast.
  • Expressions with different COLLATIONs could compare equal and lead to wrong query results.
• When reusing an instance of TDISQLite3Database, the internal transaction counter was not reset when opening another database so its InTransaction property could return wrong results. Fixed.
• Other minor bug fixes and performance enhancements, especially for the FTS3 full text search.

• The SQL output resulting from sqlite3_trace() is now modified to include the values of bound parameters.
• Optimizer enhancement: prepared statements are automatically re-compiled when a binding on the right hand side of a LIKE operator changes or when any range constraint changes for analyzed tables.
• Performance optimizations (12% reduction in the number of CPU operations) targetting specific use cases. Changes include the ifnull(), coalesce(), and substr() SQL functions plus various code refactorizations.
• More flexible Unicode tokenizer for the DISQLite3_Full_Text_Search demo. Added option for word stemming in 15 languages using YuStemmer.
• New FTS3 documentation is now available.
• Various minor bug fixes and documentation enhancements.
• New DISQLite3_Backup demo is a fully functional console application to back up any "hot" SQLite database.

• Native support for foreign key constraints. Foreign key constraints are disabled by default. Use the foreign_keys pragma to turn them on.
• Generalized the IS and IS NOT operators to take arbitrary expressions on their right-hand side.
• Recursive triggers support. The older non-recursive behavior of triggers is still the default behavior. Recursive triggers are activated using the recursive_triggers pragma. In addition to allowing triggers to call themselves (either directly or indirectly) the new capability also fires DELETE triggers on rows that are removed from a table as a result of REPLACE conflict resolution processing. Non-recursive triggers are still the default behavior since this is least likely to cause problems for existing applications. However, it is anticipated that triggers will become recursive by default sometime in the future. At that point, applications that want to continue using the older non- recursive trigger behavior will need to use the recursive_triggers pragma to disable recursive triggers.
• Refinements to the query planner that help to choose better plans for joins where in the past it was selecting suboptimal query plans. Running the ANALYZE command will collect histogram data on indices. The use of histograms improves the query planning performance even more.
• Added the SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_PRIVATECACHE flags for sqlite3_open_v2() used to override the global shared cache mode settings for individual database connections.
• Added improved version identification features: New sqlite3_sourceid() interface and SQL function sqlite_source_id().
• Obscure bug fix on triggers, plus other bux fixes.

• Delphi 2010 support.
• Fix a threading issue with TDISQLite3Database by wrapping all calls to its FStatements field with mutexes.
• Fix sqlite3_key() so that it successfully sets a correct password after an incorrect one.

• Mark sqlite3_prepare and sqlite3_prepare16 as deprecated. They are retained for backwards compatibility, but their use is discounraged. Instead, sqlite3_prepare_v2 and sqlite3_prepare16_v2 should be used, which work much better with SQLITE_SCHEMA errors.
• Expose the sqlite3_strnicmp interface for use by extensions and applications.
• Remove the restriction on virtual tables and shared cache mode. Virtual tables and shared cache can now be used at the same time.
• Modify FTS3 so that in terms like 'column_name:token' the column_name is interpreted in a case-insenstive fashion.
• Fix a double-free that can occur when using the FTS3 '-' operator.
• Other minor bug fixes.

• Fix a bug that occasionally causes INSERT or UPDATE operations to fail on an indexed table that has a self-modifying trigger.
• Internal representation of SQL expressions use less memory.
• Reduce the amount of stack space used.
• The sqlite3_create_function() family of interfaces now return SQLITE_MISUSE instead of SQLITE_ERROR when passed invalid parameter combinations.
• When new tables are created using CREATE TABLE … AS SELECT … the datatype of the columns is the simplified SQLite datatype (TEXT, INT, REAL, NUMERIC, or BLOB) instead of a copy of the original datatype from the source table.
• Resolve race conditions when checking for a hot rollback journal.
• The sqlite3_shutdown() interface frees all mutexes.
• Enhanced robustness against corrupt database files.
• Fix a code generator bug that can cause incorrect query results under obscure circumstances.
• Fix a bug in group_concat().
• Fix a performance bug in the pager cache.
• Fix a bug in the sqlite3_backup implementation that can lead to a corrupt backup database.
• Allow empty TDISQLite3DataBase.DatabaseName to create temporary databases. A warning is issued a design time.
• Other minor bug fixes and performance optimizations.

• New asynchronous backend module (DISQLite3 Pro only). It gives the illusion of faster response times by pushing slow write operations into a background thread. The tradeoff for faster response time is that more memory is required (to hold the content of the pending writes) and if a power failure or program crash occurs, some transactions that appeared to have committed might end up being rolled back upon restart.
• Performance enhancements in the btree and pager layers and in the query optimizer. Certain workloads can be as much as twice as fast as the previous release, though 10% faster is a more typical result.
• Enhanced the query optimizer so that queries against virtual tables that contain OR and IN operators in the WHERE clause are now able to use indexes.
• Many minor bug fixes.

• Fix a bug that causesd a segfault when running a count(*) on the sqlite_master table of an empty database.
• Fix a bug that causesd a segfault that when inserting into a table using a DEFAULT value where there is a function as part of the DEFAULT value expression.
• Other minor bug fixes.

• Fixed a bug that caused database corruption when an incremental_vacuum is rolled back in an in-memory database. If an incremental_vacuum was rolled back in an in-memory database, the database would often go corrupt. This only happened for in-memory databases. On-disk databases were unaffected. And the corruption only appeared if an incremental vacuum was rolled back. Upgrading is recommended for all applications, especially those that make use of in-memory databases and/or incremental vacuum.
• Fixed a bug in the lookaside memory allocator relating to shared cache mode. If a database connection closed before others, it could prematurely deallocate critical elements of the shared schema.
• Added the sqlite3_unlock_notify() interface (DISQLite3 Pro only).
• Added the reverse_unordered_selects pragma (DISQLite3 Pro only).
• The default page size is automatically adjusted to match the capabilities of the underlying filesystem.
• Performance improvements for "count(*)" queries (DISQLite3 Pro only).
• Reduce the amount of heap memory used, especially by TRIGGERs.

• New Hot Backup Interface (DISQLite3 Pro only).
• Improve TDISQLite3UniDirQuery Provider Support when inserting and updating BLOB fields with #0 values and NULL BLOBs.
• For PRIMATY KEY columns, Use the ftAutoInc field type and exclude pfInUpdate from ProviderFlags in TDISQLite3UniDirQuery. This helps to handle multiple insert problems with TClientDataSet.
• Update DISQLite3_ClientDataSet_Grid.dpr demo to use decreasing negative RowIDs so multiple inserts do not conflict.
• Various minor bug fixes.
• Type changes to sqlite3_context and sqlite3_context_ptr. This an unfortunate incompatibility change. To find out if you are affected, recompile your code and watch for compiler errors. If so, update your code by replacing "sqlite3_context" with "sqlite3_context_ptr".
• Improvements to the syntax bubble diagrams.

• Added support for nested transactions.
• Enhanced the query optimizer so that it is able to use multiple indices to efficiently process OR-connected constraints in a WHERE clause.
• Added support for parentheses in FTS3 query patterns using the SQLITE_ENABLE_FTS3_PARENTHESIS compile-time option (source code only).
• Fix a cache coherency problem that could lead to database corruption.
• Fix two bugs, which when combined might result in incorrect query results. Both bugs were harmless by themselves; only when they team up do they cause problems.

• Add TDISQLite3UniDirQuery support for TClientDataSet.RefreshRecord.
• Fixes for various minor bugs.

• Fix a bug in the b-tree delete algorithm that seems like it might be able to cause database corruption.
• Fix a memory leak that can occur following a disk I/O error.
• Fix a bug in the page cache that can lead database corruption following a rollback.
• Add the application-defined page cache.
• TDISQLite3UniDirQuery now sets the TField.Required property for columns declared as NOT NULL.
• Add the TDISQLite3DataSetImporter.OnImportError event to catch and log exceptions (which may be caused by, for example, invalid data conversions on certain records) and continue the import.
• Also in TDISQLite3DataSetImporter, call the progress event for every single record instead of every 16th one only.
• Fix a memory overrun in TDISQLite3DataSetImporter in the line wrap functions for textual blob data.
• Other minor bug fixes and optimizations.

• Delphi 2009 support.
• Add the MEMORY option to PRAGMA journal_mode.
• Added the sqlite3_db_mutex interface.
• Added the sqlite3_extended_errcode interface.
• The COMMIT command now succeeds even if there are pending queries. It returns SQLITE_BUSY if there are pending incremental BLOB I/O requests.
• The error code is changed to SQLITE_BUSY (instead of SQLITE_ERROR) when an attempt is made to ROLLBACK while one or more queries are still pending.
• Add option support for LIMIT and ORDER BY clauses on DELETE and UPDATE statements (DISQLite3 Professional only).
• Added the sqlite3_stmt_status interface for performance monitoring.
• Add the INDEXED BY clause.
• Added the TRUNCATE option to PRAGMA journal_mode
• Performance enhancements to tree balancing logic in the B-Tree layer.
• The SQL language documentation is converted to use syntax diagrams instead of BNF.
• Bug fixes

• Added the lookaside memory allocator for a speed improvement in excess of 15% on some workloads. (Your mileage may vary.)
• Added the SQLITE_CONFIG_LOOKASIDE verb to sqlite3_config() to control the default lookaside configuration.
• Added verbs SQLITE_STATUS_PAGECACHE_SIZE and SQLITE_STATUS_SCRATCH_SIZE to the sqlite3_status() interface.
• Modified SQLITE_CONFIG_PAGECACHE and SQLITE_CONFIG_SCRATCH to remove the "+4" magic number in the buffer size computation.
• Added the sqlite3_db_config() and sqlite3_db_status() interfaces for controlling and monitoring the lookaside allocator separately on each database connection.
• Numerious other performance enhancements
• Miscellaneous minor bug fixes