In a recent project, I've embarked upon the process of saving information in a quick and lightweight manner for which the serverless database, sqlite was ideal. This is not only a flexible database supporting dynamic data types, but also an extendible one used from embedded web browsers and mobile platforms to rails based websites(good riddiance, mysql) .The database is so lightweight that you'll find yourself exclaiming it as db. It is less than 100 kb in some cases and can be used as a memory resident database for the performance conscious folks and applications.
However, the support of transactions and scalability leave a lot to desire as the documentation cleary states the goals of this database is not these.
In my case, getting started was just the matter of finding the jdbc driver and it s documentation from its website. However, upon customization, there was a need for normalization which in turn required a sequential read/write operations. As soon as this was done, the multi-threaded application that was accessing started experiencing concurrency issues around the database.
This was resolved via using a little used jni wrapper that I discovered, sqlite4java. This not only solved the concurrency issues, but also enabled transactions. The beauty of this approach is also the fact that it uses a single connection as opposed to a pooled/fresh connection per every request, which tends to be a real performance bottleneck as we scale up our application.
For such a nice framework, it is a surprise that these changes have not permeated to the java/native jdbc drivers so far and the users have to cope up with its api at the moment. This could be probably because of its tight integration with the underlying C-based api that is quite performant as compared to its
alternatives in python and java, effectively making it non compliant with the jdbc specification.
As the single connection served entire applications, exposing it as a singleton was the easiest approach :
static SQLiteQueue queue = new SQLiteQueue(new File("BiddingsDB"));
static {
queue.start();
}
For providing a transaction-like support, there was a need to wrap the existing operations in this queue :
public static void databaseInsert(final List
