Release of DBMS SQLite 3.31 with support for generated columns

Published Release SQLite 3.31.0, a lightweight DBMS packaged as a plug-in library. The SQLite code is distributed in the public domain, i.e. can be used without restrictions and free of charge for any purpose. Financial support for SQLite developers is provided by a specially created consortium, which includes companies such as Adobe, Oracle, Mozilla, Bentley and Bloomberg.

All changes:

• Added support generated columns (calculated columns), which allows you to define a column when you create a table, the value of which is automatically calculated based on the contents of another column. Generated columns can be either virtual (formed on the fly with each access) or stored in the database (saved with each update of related columns). The contents of the generated columns are available only in read mode (the change is made only by modifying the value in another column involved in the calculation). For example:

  CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INT,
  with 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 "-DSQLITE_TRUSTED_SCHEMA" build option, which allow you to control whether protection against attacks through modification of the data schema in the database. Active protection restricts the use of SQL functions (not marked as SQLITE_INNOCUOUS) in triggers, views, CHECK and DEFAULT statements, indexes, and generated columns. It also disables the use of virtual tables in triggers and views unless the virtual table is explicitly declared with the SQLITE_VTAB_INNOCUOUS flag.
• Implemented the ability to assign properties to SQL functions defined in applications SQLITE_INNOCUOUS (harmless functions that do not depend on external parameters and cannot be used to perform malicious actions) and SQLITE_DIRECTONLY (only a direct call in SQL queries, without the possibility of using it in triggers, views and data structure schemas);
• Added module uuid with the implementation of functions for handling UUIDs (RFC-4122);
• Added PRAGMA hard_heap_limit and function sqlite3_hard_heap_limit64() to control the maximum heap size;
• At PRAGMA function_list added output of the type, properties and number of arguments of each function;
• To virtual table DBSTAT added data aggregation mode;
• sqlite3_open_v2() implements the SQLITE_OPEN_NOFOLLOW option to disable the opening of symbolic links;
• For argument PATH, passed to the JSON function, support for the "#-N" array notation has been added;
• In a memory allocation system lookaside implemented support for two separate memory pools, each of which can be used to allocate blocks of different sizes (separation allows you to expand the use of the lookaside system, while reducing the size of the buffer allocated per connection from 120 to 48 KB);
• PRAGMA support dropped legacy_file_format, which was incompatible with VACUUM, generated columns, and descending indexes (legacy format support can be reverted via the SQLITE_DBCONFIG_LEGACY_FILE_FORMAT flag in sqlite3_db_config()).

Source: opennet.ru