I wrote my first SQLite article in 2011 while debugging an Adobe AIR app. The problem was a quoting edge case; the solution was a one-liner. SQLite was a curiosity — a file-based database you used when you didn’t want to run a server.
Fifteen years later, SQLite is having a moment that few databases get to have. It’s not just embedded tooling anymore. It’s in the browser, at the edge, in production analytics pipelines, and increasingly, in vector search. The same engine, radically new contexts.
So apart from needing to redesign/rebuild my site, I really need to start writing more about my experiments. More for my own record than a public one.
Recently started playing with the whole Nutch family (Hadoop, MapReduce, Hbase, Pig, Solr, Nutch,… etc). I finally got Nutch 2.1 set up with Cassandra 1.2 (that in itself should be another article) with the aim to run data extraction and post to Solr’s Lucene index. Initially I’ve just indexed my own site, but need to inspect the Cassandra data. Been playing with pycassa with some success (the library rocks I just suck at python), and looking at some gui’s. But now settling on CQL as a means to navigate the data. In this post I hope to record the queries I’m yet to use to inspect a Cassandra Nutch data store.
A note from a series of posts on AIR and SQLite. At the time, I needed to put together an analytics tracking library for an AIR application — Google Analytics only supported Flex apps with DOM access, so it wasn’t available in AIR. That constraint pushed me toward building a custom persistence layer to buffer and batch events locally before syncing them upstream.
Wanting a cleaner approach to persistent data in AIR, I started writing my own DataObject class to handle saving and retrieving records. While writing it I assumed someone had already solved this problem — and sure enough, they had.
Following on from the previous post, here’s a solution for INSERT or UPDATE if the row exists — a fairly desirable behaviour that saves the usual SELECT if exists, then UPDATE else INSERT routine.
This sounds like it would be a straightforward fix, but I couldn’t find a single article relating to Adobe AIR/Flex insert-or-update at the time. Reading the SQLite docs helped considerably.
I was trying to SELECT column FROM Table WHERE id="ID" and branching on the result to INSERT or UPDATE respectively. The answer turns out to be quite straightforward and works similarly to MySQL’s REPLACE:
Spoiler: Double quote string values in SQLite statements (Possible solution this error is raised by many misconfigurations).
With SQLite in Adobe AIR, I was running some initial tests to figure out a clever TRIGGER to UPDATE or INSERT appropriately.
So far I’ve been writing all my queries in Flex, writing statements manually for instant feedback. I encountered the #3115 Error. Searching through Google I see a lot of posts about this. The answer it seems is widely varied. Other possibilities include incorrect path to applicationStorageDirectory or invalid table names. My issue (to the best of my knowledge) was a reserved word inside single quotes.