DISQLite3 5.26.0 » Developer Team :: Best choice for developers!

DISQLite3 5.26.0

DISQLite3 5.26.0
DISQLite3 5.26.0


DISQLite3 implements a self-contained, embeddable, zero-configuration SQL database engine for Delphi (Embarcadero / CodeGear / Borland).

Overview
ACID transactions, even after system crashes and power failures.
Zero-configuration – no setup or administration needed.
Implements most of SQL-92.
A complete database is stored in a single disk file.
Supports terabyte-sized databases and gigabyte-sized strings and blobs. Self-contained: no external dependencies, no DLLs.
Small footprint and smart linking: Only required code is compiled in, adding as little as 300 KB code space only.
Full Text Search (FTS) with customizable tokenizer, prefix matching, and optional word stemming for 15 languages.
Database AES encryption with SHA256 key generator.
Db.pas is not required, which allows DISQLite3 to compile with all flavours of Delphi, including Delphi Standard and Delphi Personal.
Faster than popular database engines for most common operations.
Simple, easy to use API.
Database files created with DISQLite3 can also be accessed by Linux and MacOS using the SQLite3 libraries.

SQL-92 Support
DISQLite3 Drive Catalog Demo ApplicationDISQLite3 understands most of the SQL-92 language standard:

ALTER TABLE
ANALYZE
ATTACH DATABASE
BEGIN TRANSACTION
Comments
COMMIT TRANSACTION
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DELETE
DETACH DATABASE
DROP INDEX
DISQLite3 Mathematical Expression Evaluator Demo Applicationdrop TABLE
DROP TRIGGER
DROP VIEW
END TRANSACTION
EXPLAIN
Expressions
INSERT
ON CONFLICT clause
PRAGMA
REINDEX
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE
VACUUM
The complete SQL syntax is described in full length in the DISQLite3 HTML Help, part of the installation package.

Simple Programming Interface
DISQLite3 Database Encryption Demo ApplicationDISQLite3 provides a comprehensive list of functions and procedures to manage database records with ease and efficiency. It includes the full SQLite3 functionality, plus a number of Delphi specific extras:

AnsiString, UnicodeString / WideString, and Variant support.
Database and Statement wrapper classes.
TDataSet support.
TStream support for BLOBs.
Growing number of Delphi example projects.
Despite of its rich set of features, a DISQLite3 database application can be realized with just three different function calls.

DISQLite3 5.26.0 – 24 Dec 2018
Support Delphi 10.3 Rio Win32 and Win64.

Enhancements:

Add support for window functions.
Enhancements the ALTER TABLE command:
Add support for renaming columns within a table using ALTER TABLE table RENAME COLUMN oldname TO newname.
Add support for PostgreSQL-style UPSERT.
Fix table rename feature so that it also updates references to the renamed table in triggers and views.
Query optimizer improvements:
Avoid unnecessary loads of columns in an aggregate query that are not within an aggregate function and that are not part of the GROUP BY clause.
The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column other than the left-most column, then if no rows match against the first IN value, check to make sure there exist rows that match the columns to the right before continuing with the next IN value.
Use the transitive property to try to propagate constant values within the WHERE clause. For example, convert “a=99 AND b=a” into “a=99 AND b=99”.
Enhance the PRAGMA integrity_check command for improved detection of problems on the page freelist.
Allow the xBestIndex method of virtual table implementations to return SQLITE_CONSTRAINT to indicate that the proposed query plan is unusable and should not be given further consideration.
Added the SQLITE_DBCONFIG_DEFENSIVE option which disables the ability to create corrupt database files using ordinary SQL.
Added support for read-only shadow tables when the SQLITE_DBCONFIG_DEFENSIVE option is enabled.
Added the PRAGMA legacy_alter_table command, which if enabled causes the ALTER TABLE command to behave like older version of SQLite (prior to version 3.25.0) for compatibility.
Added PRAGMA table_xinfo that works just like PRAGMA table_info except that it also shows hidden columns in virtual tables.
Added the EXPLAIN virtual table as a run-time loadable extension.
Add a limit counter to the query planner to prevent excessive sqlite3_prepare times for certain pathological SQL inputs.
Added support for the sqlite3_normalized_sql interface.
Enhanced triggers so that they can use table-valued functions that exist in schemas other than the schema where the trigger is defined.
Added the SQLITE_FCNTL_DATA_VERSION file-control.
Added the Geopoly module.
Add support for auxiliary columns in r-tree tables.
Add APIs for discovering SQL keywords used by SQLite: sqlite3_keyword_count, sqlite3_keyword_name, and sqlite3_keyword_check.
Add APIs for dynamic strings based on the sqlite3_str object.
Enhance ALTER TABLE so that it recognizes “true” and “false” as valid arguments to DEFAULT.
Add the sorter-reference optimization as a compile-time option.
Improve the format of the EXPLAIN QUERY PLAN raw output, so that it gives better information about the query plan and about the relationships between the various components of the plan.
Added the SQLITE_DBCONFIG_RESET_DATABASE option to the sqlite3_db_config API.
Enhancements to the session extension:
Added the SQLITE_CHANGESETAPPLY_INVERT flag
Added the sqlite3changeset_start_v2 interface and the SQLITE_CHANGESETSTART_INVERT flag.

Performance:

Optimization: When doing an UPDATE on a table with indexes on expressions, do not update the expression indexes if they do not refer to any of the columns of the table being updated.
UPDATE avoids unnecessary low-level disk writes when the contents of the database file do not actually change. For example, “UPDATE t1 SET x=25 WHERE y=?” generates no extra disk I/O if the value in column x is already 25. Similarly, when doing UPDATE on records that span multiple pages, only the subset of pages that actually change are written to disk. This is a low-level performance optimization only and does not affect the behavior of TRIGGERs or other higher level SQL structures.
Queries that use ORDER BY and LIMIT now try to avoid computing rows that cannot possibly come in under the LIMIT. This can greatly improve performance of ORDER BY LIMIT queries, especially when the LIMIT is small relative to the number of unrestricted output rows.
The OR optimization is allowed to proceed even if the OR expression has also been converted into an IN expression. Uses of the OR optimization are now also more clearly shown in the EXPLAIN QUERY PLAN output.
The query planner is more aggressive about using automatic indexes for views and subqueries for which it is not possible to create a persistent index.
Make use of the one-pass UPDATE and DELETE query plans in the R-Tree extension where appropriate.
Performance improvements in the parser.

Bug fixes:

The ORDER BY LIMIT optimization might have caused an infinite loop in the byte code of the prepared statement under very obscure circumstances, due to a confluence of minor defects in the query optimizer.
On an UPSERT when the order of constraint checks is rearranged, ensure that the affinity transformations on the inserted content occur before any of the constraint checks.
The LIKE optimization was generating incorrect byte-code and hence getting the wrong answer if the left-hand operand has numeric affinity and the right-hand-side pattern is '/%' or if the pattern begins with the ESCAPE character.
For the right-hand table of a LEFT JOIN, compute the values of expressions directly rather than loading precomputed values out of an expression index as the expression index might not contain the correct value.
Do not attempt to use terms from the WHERE clause to enable indexed lookup of the right-hand table of a LEFT JOIN.
Fix a memory leak that can occur following a failure to open error in the CSV virtual table.
Fix a long-standing problem wherein a corrupt schema on the sqlite_sequence table used by AUTOINCREMENT can lead to a crash.
Fix the json_each() function so that it returns valid results on its “fullkey” column when the input is a simple value rather than an array or object.
Fix a memory leak when reading TDISQLite3UniDirQuery string fields.


Only for V.I.P
Warning! You are not allowed to view this text.


Information
Users of Guests are not allowed to comment this publication.