SQLite, the RDBMS that never ceases to amaze, has arrived in version 3.39.2. Although this version contains only bugfixes, it builds on major changes that debuted in version 3.39.0.
SQLiteName “never ceases to amaze”or more precisely, imagination uses people, because while SQLite is a relational database management system like MySQL or Postgres, it does not fall under the client-server model. Instead, it integrates into applications that need query access to a data store. The most common examples are its integration into Android applications or web browsers such as Firefox.
Despite its compact size and lack of the client-server model, SQLite is an RDBMS with all the functionality that does something relational – i.e. tables, indexes, constraints, triggers, transactions , etc. However, compared to full-fledged RDBMS like PostgreSQL, some SQL-based features are missing like RIGHT and FULL OUTER JOIN support, but we will see that this latest version finally implements them, and its built-in functions are few- one. This does not mean that SQLite is incapable of more complex capabilities. What about support for JSON query syntax using -> and ->> operators similar to PostgreSQL JSON functions?
Then programming language support. There is almost an API/library for every programming language that needs to speak SQLite. Take for example Python’s sqlite3 standard library under which you can interact with SQLite using Python objects and syntax.
Another advantage is its availability. It’s open source, but not open for contribution, and there are pre-built binaries for Linux, Windows, and Mac. In terms of performance, SQLite is written in C, with no intention of rewriting it in another language. As for why, the authors clearly explain why C is the best language to implement SQLite:
- Performance
A heavily used low-level library like SQLite should be fast. - Compatibility
Almost all systems have the ability to call libraries written in C. Other implementation languages do not. - Low dependency
Libraries written in C don’t have a huge runtime dependency. In its minimal configuration, SQLite only requires 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 you want when developing a module like SQLite. Writing a small, fast, and reliable database engine is hard enough without the implementation language changing under you with each update of the implementation language specification.
Note that the authors refer to SQLite as “module” or “library”.
That said, but as stated above, SQLite has a minor drawback.
There are few built-in functions compared to PostgreSQL or Oracle. Fortunately, the authors have provided an extension mechanism, which allows you to do almost anything. As a result, there are many SQLite extensions, but they are incomplete, inconsistent, and scattered across the internet.
Fortunately, there are attempts to organize them, as does sqlean, a Github repository that brings together all missing SQLite functions under one roof, divided into two categories:
The main set-The most popular functions. They are tested, documented and organized into domain modules with a clear API.
crypto: secure hashes
fileio: read and write files
fuzzy: fuzzy and phonetic string matching
ipaddr: IP address manipulation
json1: JSON functions
mathematics: mathematical functions
topic: regular expressions
statistics: mathematical statistics
text: string functions
unicode: Unicode support
uuid: universally unique identifiers
vsv: CSV files as virtual tables
The incubator-These expansions have yet to make their way to the main set. They may be untested, poorly documented, too broad, too narrow, or without a well-thought-out API.
array: one-dimensional arrays
besttype: convert string value to numeric
bloom: a quick way to find out 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 arithmetic
classifier: binary classifier via logistic regression
closure: navigate in hierarchical tables with parent/child relationships
compress and sqlar: compress/decompress data
cron: match dates to cron patterns
dbdump: exports the database in SQL
decimal, fcmp and ieee754: decimal and floating point arithmetic
define: create scalar and table functions from SQL
envfuncs: read environment variables
eval: execute arbitrary SQL statements
isodate: additional date and time functions
pearson: Pearson’s correlation coefficient between two sets of data
pivot table: pivot tables
prefixes: generate string prefixes
rotation: obfuscation of strings
spellfix: search a large vocabulary for close matches
stats2 and stats3: additional mathematical statistics functions
text2: additional string functions
uint: sorting and comparing natural strings
unhex: reverse for hex()
unionvtab: merge 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 with Postgres, in a sense extensibility is SQLite’s greatest power; it adds unintended utility and value for its users,
and we can witness many imaginative uses of it, provided by the community. For instance :
- LiteFS
a FUSE-based filesystem to replicate SQLite databases across a cluster of machines - SpatiaLite
an open source library intended to extend the SQLite core to support full-fledged spatial SQL features. - LiteTree
a modification of the SQLite engine to support branching, like git! - Advanced command line shell history
automatically save your bash history in sqlite3! - SQLSite
a tool to serve simple websites, static files and JSON APIs directly from an SQLite database. - Sqlite to rest
Koa routing middleware to expose sqlite database via RESTful CRUD - db to sqlite
CLI tool to export tables or queries from any SQL database to an 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 consumer apps like Joplin note taking which uses an underlying SQLite to enforce full-text search on your notes.
After reviewing its virtues and disadvantages, let’s now turn our attention to what the latest version brings:
3.39.1 and 3.39.2 are minor enhancements and bugfixes over major release 3.39.0. This leaves us with an overview of the changes in 3.39.0:
- Added (long overdue) support for RIGHT and FULL OUTER JOIN.
- Add new binary comparison operators IS NOT DISTINCT FROM and IS DISTINCT FROM which are equivalent to IS and IS NOT, respectively, for compatibility with PostgreSQL and SQL standards.
- Add a new return code (value “3”) from the sqlite3_vtab_distinct() interface that indicates a query containing both DISTINCT and ORDER BY clauses.
- Added 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 item in the path is a symbolic link.
- Defer materialization views until the materialization is actually needed, thus avoiding unnecessary work if the materialization never turns out to 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 micro-optimizations collectively reduce CPU cycles by approximately 2.3%.
So much power in a zip file of less than 1 MB. What more could you ask for?
More information
Related Articles
LiteCLI – SQLite client with autocomplete
SQLite 3.33 increases maximum database size
To be informed of new articles on I Programmer, subscribe to our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook Where LinkedIn.
comments
or send your comment to: [email protected]
Comments are closed.