Cross posted from my blog: Analyze the Data not the Drivel
SQLite, by many estimates, is the most widely deployed SQL database system on Earth. It's everywhere. It's in your phone, your laptop, your cameras, your car, your cloud, and your breakfast cereal. SQLite's global triumph is a gratifying testament to the virtues of technical excellence and the philosophy of "less is more."
Readers of this blog will know that I admire the set-theoretic basis of SQL while despising the IT administrative state that has grown up, like septic tank scum, around it. You'll know what I'm talking about if you have ever:
Requested access to a particular corporate database.
Waited for DBAs to sign off on a query.
Asked for a new index.
Had the gall to install "unauthorized" software tools in
"production."
There is so much administrative shit covering big Oracle, SQLServer, or DB2 systems that it's almost impossible to get anything done without constantly fellating administrative dick!
SQLite saves SQL
The creators of SQLite had another idea. Let's drop the administrative crap and provide a simple stand-alone implementation of SQL. Let's also structure our system as a single highly portable C source file that can be compiled anywhere. Furthermore, let's test the shit out of our code with comprehensive test suites that hit every line. Finally, let's fix
our damn bugs. The SQLite developers did all this so well that SQLite slowly, then all at once, conquered the whole damn world. The SQLiters earned their dominance.
So, what does this have to do with J?
J provides a useful SQLite addon that lets J programmers navigate the vast cosmos of SQLite database files. I mainly use J's SQLite addon to access ThumbsPlus and Darktable image databases, but it's surprisingly handy in many contexts simply because SQLite database files are everywhere. SQLite database files are so pervasive that they've become a de facto binary data exchange format. If you have data in dire need of distribution, stuff them in a SQLite database file.
It's still SQL
SQLite is a superb tool, but it's still SQL, and SQL has an ancient deficiency. Loading data into SQL systems has always been a pain. So much so that a plethora of grisly, kludge-infested hacks, charitably labeled ETL tools (Extract Transform and Load), have taken root in the SQLverse. ETL tools, no matter how polished or optimized, all seem like afterthoughts.
"Hey, this set-theoretic SQL shit
would be so much better if there was
some data in our database!"
If you "manage" SQL systems, you must slay ETL dragons. SQLite hasn't fixed this, but its almost universal adoption means you can slay ETL dragons with your favorite programming spears. One of my favorite spearsis the J programming language.
Toy or tool
When I started writing this post, I considered composing a short
tutorial on how to create, load, and query a simple SQLite database with the J addon. Then I realized the Internet drips with simple SQLite toytutorials. Do we really need another one? Simple SQL tutorials help when getting started, but they invariably gloss over the ETL problem. The ETL problem never goes away and it's always more complex than expected. A realistic introduction to using SQLite, or any SQL system, would keep this nasty fact front and center. So, in a following post, I will outline a small but nontrivial SQLite-based tool I use to manage messy data I care about.
Latest comments (0)