In Praise Of SQLite
Written by Nikos Vaggalis   
Thursday, 28 July 2022

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.


sqlite

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?


sqlite

 

More Information

SQLite Release 3.39.2
sqlean

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.

Banner


Firefox Loses Users and Websites
08/08/2022

Over the past few weeks Firefox has seen a steeper than usual decline in Monthly Active Users. Now the Canadian Credit Card company MBNA no longer lets its card holders log in to their accounts from F [ ... ]



Generating Sentences Is Not Evidence of Sentience
25/07/2022

Blake Lemoine, the Google Engineer who was placed on administrative leave in June after claiming that LaMDA, Google's conversational AI is sentient, has now been fired after Google cond [ ... ]


More News

pythondata

 



 

Comments




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

 

 

Last Updated ( Thursday, 28 July 2022 )