SQLite is a lightweight database engine
SQLite 3.45 release has been released, the lightweight relational database engine accessible through the SQL language. Unlike traditional database servers, such as MySQL or PostgreSQL, its peculiarity is not to reproduce the usual client-server scheme, but to be integrated directly into the programs.
Thanks to its extreme lightness, SQLite is one of the most used database engines in the world. It is used in many consumer programs and is also very popular in embedded systems, including most modern smartphones.
Main new features of SQLite 3.45
In this new version of SQLite 3.45 that is presented, it is highlighted that Major feature updates have been made SQL related to data management in JSON format. These functions have been rewritten and migrated to the new internal JSONB parse tree format, which is serialized and can be stored directly in the database, in addition to this update avoiding the need for repeated analysis when working with JSON values, which significantly improves the performance and efficiency of queries involving data in format JSON.
In new versions of the JSON generation functions, an important change has been implemented: they now generate data in JSONB binary format instead of text format. This represents an improvement in the optimization and manipulation of JSON data in the system.
In addition, An optional second argument has been introduced in the json_valid() function. This argument allows you to specify a bitmask that defines the characteristics of valid JSON. For example, specifying 0x04 will recognize a JSONB object as valid. Also, when you specify 0x01, JSON text in RFC-8259 format will be considered valid. Option 0x05 allows both options to be recognized.
Default, SQLITE_DIRECT_OVERFLOW_READ optimization is enabled, which allows overflow pages that are larger than the standard b-tree page size to be read directly from the file, bypassing the cache. However, if you want to disable this optimization during compilation, you can use the "-DSQLITE_DIRECT_OVERFLOW_READ=0" option.
El Query planner has seen significant improvements on the efficiency of transitive constraint optimization. This allows the query engine to better optimize queries by considering transitive constraints and improve the ability to ignore indexes that are considered low quality during the "ANALYZE" operation. These enhancements help ensure optimal performance and efficient execution of queries against the SQLite database.
On the other hand, on Windows, the command line interface has been updated to improve the display of UTF-8 encoded content. This ensures a more consistent and readable experience when interacting with the database via the command line in Windows environments. Additionally, automatic detection of CLI usage when playing ".dump" scripts has been enabled and necessary changes have been made to configurations, such as ".dbconfig defensive off" and ".dbconfig dqs_dll on", to ensure smooth operation. optimum.
Finally, SQLITE_RESULT_SUBTYPE property has been introduced to defined SQL functions on the application side. This allows sqlite3_result_subtype() to be called from the function to inspect argument subtypes, providing greater flexibility and inspection capability in manipulating data in the application.
Of the other changes that stand out:
- FTS5 with tokendata option: The tokendata option has been added to the FTS5 virtual table, which is used for full-text search.
- WAL2 transaction log mode: Experimental versions of SQLite have been introduced with a new transaction log mode called WAL2 ("PRAGMA journal_mode = wal2"). This mode uses two wal files ("database-wal" and "database-wal2") instead of one, which addresses the problem of uncontrolled wal file growth in situations where transactions are not committed for a long time or there are backup processes. prolonged reading.
- In this mode, when writing data to the database, the new data is added to the first wal file. When this file reaches a large enough size, writing to the second file begins. After switching to the second wal file, the first one is available for changes and overwrites. This cycle repeats itself, which solves the problem of uncontrolled growth of the wal file.
- Changes to JSON processing: Changes to JSON data manipulation have caused backward compatibility and led to the termination of certain constructs for loading JSON data from files.
Finally, if you are interested in being able to know more about it, you can consult the details in the following link