Release of SQLite 3.31 Database with support for generated columns

Published release SQLite 3.31.0, a lightweight DBMS designed as a plug-in library. The SQLite code is released into the public domain, meaning it can be used without restrictions and free of charge for any purpose. Financial support for SQLite developers comes from a consortium that includes companies such as Adobe, Oracle, Mozilla, Bentley, and Bloomberg.

Key changes:

• Support added generated columns (computed columns), allowing the creation of a table to define a column whose value is automatically calculated based on the contents of another column. Generated columns can be either virtual (computed on-the-fly during each access) or stored in the database (retained with each update of related columns). The content of generated columns is available only in read mode (changes are made only by modifying the value in another column involved in the calculation). For example:

  CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INT,
  c TEXT,
  d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
  e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
  );
• Added PRAGMA trusted_schema, setting SQLITE_DBCONFIG_TRUSTED_SCHEMA and the compilation option '-DSQLITE_TRUSTED_SCHEMA', allowing control over enabling protection against attacks through modification of the data schema in the database. With active protection, the use of SQL functions (not marked as SQLITE_INNOCUOUS) is restricted in triggers, views, CHECK and DEFAULT expressions, indexes, and generated columns. The use of virtual tables in triggers and views is also disabled unless the virtual table is explicitly declared with the SQLITE_VTAB_INNOCUOUS flag.
• The ability to assign certain properties to SQL functions in applications has been implemented SQLITE_INNOCUOUS (harmless functions that do not depend on external parameters and cannot be used for malicious actions) and SQLITE_DIRECTONLY (only direct call in SQL queries, with no possibility of use in triggers, views, and data structure schemas);
• A module has been added uuid implementing functions for handling UUID (RFC-4122);
• PRAGMA has been added hard_heap_limit and the function sqlite3_hard_heap_limit64() to control the maximum heap size;
• In PRAGMA function_list the output of type, properties, and number of arguments of each function has been added;
• In the virtual table DBSTAT implemented data aggregation mode;
• In sqlite3_open_v2(), the option SQLITE_OPEN_NOFOLLOW has been implemented, allowing the prohibition of opening symbolic links;
• For the argument PATH, passed to JSON functions, support for array notation '#-N' has been added;
• In the memory distribution system lookaside Support for two separate memory pools has been implemented, each of which can be used to allocate blocks of different sizes (this separation allows for the expanded use of the lookaside system while reducing the buffer size allocated per connection from 120 KB to 48 KB);
• Support for PRAGMA has been discontinued. legacy_file_format, which was incompatible with VACUUM, generated columns, and descending indexes (support for the legacy file format can be restored via the SQLITE_DBCONFIG_LEGACY_FILE_FORMAT flag in sqlite3_db_config()).

Source: opennet.ru