SQLite, the RDBMS that never ceases to amaze, has reached version 3.39.2. While this release contains just bug fixes, it builds on major changes that debuted in 3.39.0.
SQLite "never ceases to amaze", or more specifically, the imaginative uses people put it under, since while SQLite is a relational database management system like MySQL or Postgres, it does not fall under the client-server model. Instead it embeds itself inside applications that need query access to a data store. The most prevalent examples are its embedding in Android applications or web browsers such as Firefox.
Despite its compact size and absence of the client server model, SQLite is a RDBMS with all the features that make something relational - that is tables, indexes, constraints, triggers, transactions and the like. However, compared to fully-fledged RDBMSs like PostgreSQL, some SQL-based features are absent like support for RIGHT and FULL OUTER JOIN, but we will see that this newest version at last implements them, and that its built-in functions are a few.This doesn't mean that SQLite is incapable of more complex abilities.What about support for the JSON query syntax using -> and ->> operators that are similar to PostgreSQL JSON functions?
Then programming language support. There's almost an API/library for every programming language that needs to speak SQLite.Take for example Python's standard library sqlite3 under which you can interact with SQLite using Python objects and syntax.
Another boon is its availability.It is open source, however not open-to-contribution, and there's pre-built binaries for Linux, Windows and Mac.Performance wise,SQLite is written in C,with no plans to rewrite it in another language.As to why is that,the authors make it clear why C is the best language to implement SQLite:
Performance An intensively used low-level library like SQLite needs to be fast.
Compatibility Nearly all systems have the ability to call libraries written in C. This is not true of other implementation languages.
Low-dependency Libraries written in C do not have a huge run-time dependency. In its minimum configuration, SQLite requires only the following routines from the standard C library
Stability The C language is old and boring. It is a well-known and well-understood language. This is exactly what one wants when developing a module like SQLite. Writing a small, fast, and reliable database engine is hard enough as it is without the implementation language changing out from under you with each update to the implementation language specification.
Note that the authors refer to SQLite as a "module" or "library".
All that said, but as noted above SQLite has a minor drawback.
There are few built-in functions compared to PostgreSQL or Oracle. Fortunately, the authors provided an extension mechanism, which allows doing almost anything. As a result, there are a lot of SQLite extensions out there, but they are incomplete, inconsistent and scattered across the internet.
Fortunately there's attempts to organize them, as sqlean, a Github repo that collects all the missing SQLite functions under one roof does, split into two categories:
The main set-The most popular functions.They are tested, documented and organized into the domain modules with clear API.
crypto: secure hashes fileio: read and write files fuzzy: fuzzy string matching and phonetics ipaddr: IP address manipulation json1: JSON functions math: math functions re: regular expressions stats: math statistics text: string functions unicode: Unicode support uuid: Universally Unique IDentifiers vsv: CSV files as virtual tables
The incubator-These extensions haven't yet made their way to the main set. They may be untested, poorly documented, too broad, too narrow, or without a well-thought API.
array: one-dimensional arrays besttype: convert string value to numeric bloom: a fast way to tell if a value is already in a table btreeinfo, memstat, recsize and stmt: various database introspection features cbrt and math2: additional math functions and bit arithmetics classifier: binary classifier via logistic regression closure: navigate hierarchic tables with parent/child relationships compress and sqlar: compress / uncompress data cron: match dates against cron patterns dbdump: export database as SQL decimal, fcmp and ieee754: decimal and floating-point arithmetic define: create scalar and table-valued functions from SQL envfuncs: read environment variables eval: run arbitrary SQL statements isodate: additional date and time functions pearson: Pearson correlation coefficient between two data sets pivotvtab: pivot tables prefixes: generate string prefixes rotate: string obfuscation spellfix: search a large vocabulary for close matches stats2 and stats3: additional math statistics functions text2: additional string functions uint: natural string sorting and comparison unhex: reverse for hex() unionvtab: union similar tables into one xmltojson: convert XML to JSON string zipfile: read and write zip files zorder: map multidimensional data to a single dimension
As in the case of Postgres, in a sense extensibility is SQLite's biggest power;it adds unforeseen utility and value for its users, and we can witness many imaginative uses for it, provided by the community.For instance :
LiteFS a FUSE-based file system for replicating SQLite databases across a cluster of machines
SpatiaLite an open source library intended to extend the SQLite core to support fully fledged Spatial SQL capabilities.
LiteTree a modification of the SQLite engine to support branching, like git!
Advanced command line shell history save your bash history to sqlite3 automatically!
SQLSite a tool for serving simple websites, static files and JSON APIs directly from a SQLite database.
Sqlite To Rest Koa routing middleware allowing you to expose a sqlite database via RESTful CRUD
db-to-sqlite CLI tool for exporting tables or queries from any SQL database to a SQLite file.
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
And of course mainstream applications like note taking Joplin which uses an underlying SQLite for imposing full-text search on your notes.
After going through its virtues and drawbacks let's now turn our attention to what the latest version brings:
3.39.1 and 3.39.2 are minor improvements and bug fixes over major release 3.39.0.That leaves us with a look at 3.39.0 changes:
Add (long overdue) support for RIGHT and FULL OUTER JOIN.
Add new binary comparison operators IS NOT DISTINCT FROM and IS DISTINCT FROM that are equivalent to IS and IS NOT, respective, for compatibility with PostgreSQL and SQL standards.
Add a new return code (value "3") from the sqlite3_vtab_distinct() interface that indicates a query that has both DISTINCT and ORDER BY clauses.
Added the sqlite3_db_name() interface.
The unix os interface resolves all symbolic links in database filenames to create a canonical name for the database before the file is opened. If the SQLITE_OPEN_NOFOLLOW flag is used with sqlite3_open_v2() or similar, the open will fail if any element of the path is a symbolic link.
Defer materializing views until the materialization is actually needed, thus avoiding unnecessary work if the materialization turns out to never be used.
The HAVING clause of a SELECT statement is now allowed on any aggregate query, even queries that do not have a GROUP BY clause.
Many microoptimizations collectively reduce CPU cycles by about 2.3%.
So much power in an under 1MB zip file.What more could you ask?
Select AI is a new feature of the Oracle Autonomous Database that transforms your mother language to SQL. This is a big boon for non-developers in extracting value out of their data silos.
The SIGBOVIK conference has just finished and its proceedings can be downloaded, but only at your peril. You might never see computer science in the same way ever again.
