In Praise of SQLite
In Praise Of SQLite


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:


PerformanceAn intensively used low-level library like SQLite needs to be fast.
CompatibilityNearly all systems have the ability to call libraries written in C. This is not true of other implementation languages.
Low-dependencyLibraries 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
StabilityThe 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 hashesfileio: read and write filesfuzzy: fuzzy string matching and phoneticsipaddr: IP address manipulationjson1: JSON functionsmath: math functionsre: regular expressionsstats: math statisticstext: string functionsunicode: Unicode supportuuid: Universally Unique IDentifiersvsv: 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 arraysbesttype: convert string value to numericbloom: a fast way to tell if a value is already in a tablebtreeinfo, memstat, recsize and stmt: various database introspection featurescbrt and math2: additional math functions and bit arithmeticsclassifier: binary classifier via logistic regressionclosure: navigate hierarchic tables with parent/child relationshipscompress and sqlar: compress / uncompress datacron: match dates against cron patternsdbdump: export database as SQLdecimal, fcmp and ieee754: decimal and floating-point arithmeticdefine: create scalar and table-valued functions from SQLenvfuncs: read environment variableseval: run arbitrary SQL statementsisodate: additional date and time functionspearson: Pearson correlation coefficient between two data setspivotvtab: pivot tablesprefixes: generate string prefixesrotate: string obfuscationspellfix: search a large vocabulary for close matchesstats2 and stats3: additional math statistics functionstext2: additional string functionsuint: natural string sorting and comparisonunhex: reverse for hex()unionvtab: union similar tables into onexmltojson: convert XML to JSON stringzipfile: read and write zip fileszorder: 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 :


LiteFSa FUSE-based file system for replicating SQLite databases across a cluster of machines
SpatiaLitean open source library intended to extend the SQLite core to support fully fledged Spatial SQL capabilities.
LiteTreea modification of the SQLite engine to support branching, like git!
Advanced command line shell historysave your bash history to sqlite3 automatically!
SQLSitea tool for serving simple websites, static files and JSON APIs directly from a SQLite database.
Sqlite To RestKoa routing middleware allowing you to expose a sqlite database via RESTful CRUD
db-to-sqliteCLI tool for exporting tables or queries from any SQL database to a SQLite file.
sql.jsSQLite 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?


More Information
SQLite Release 3.39.2sqlean
Related Articles
LiteCLI - SQLite Client with Autocomplete
SQLite 3.33 Increases Maximum Database Size

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.





Comments

or email your comment to: comments@i-programmer.info


