In Praise of SQLite

Just learned of some interesting things about SQLite in this article that popped up in my Google News feed: In Praise of SQLite by Nikos Vaggalis, dated 28 July 2022. The latest version of SQLite is 3.39.2. I don’t monitor releases so this story made me aware of some significant changes delivered in 3.39.0 and other developments in the library and in extensions. I’ll highlight some that I think will be of potential interest to RootsMagic Toolmakers but recommend you read the original report. Check your current SQLite manager for updates that may bring it up to using the 3.39.x library.

• Changes:
  • Added (long overdue) support for RIGHT and FULL OUTER JOIN
  • Added the sqlite3_db_name() interface
  • The HAVING clause of a SELECT statement is now allowed on any aggregate query, even queries that do not have a GROUP BY clause
• sqlean, a Github repo that collects extensions covering all the missing SQLite functions:
  • fileio: read and write files
  • fuzzy: fuzzy string matching and phonetics
  • re: regular expressions
  • text: string functions
  • unicode: Unicode support
  • uuid: Universally Unique IDentifiers
  • vsv: CSV files as virtual tables
  • closure: navigate hierarchic tables with parent/child relationships
  • envfuncs: read environment variables
  • eval: run arbitrary SQL statements
  • isodate: additional date and time functions
  • text2: additional string functions
  • xmltojson: convert XML to JSON string
  • zipfile: read and write zip files
• Utilities:
  • LiteTree, a modification of the SQLite engine to support branching, like git!
  • SQLSite, a tool for serving simple websites, static files and JSON APIs directly from a SQLite database.
  • sql.js, SQLite compiled to JavaScript.sql.js is a javascript SQL database. It allows you to create a relational database and query it entirely in the browser