SQLite and BLOB type

[Thomas]

I need to store some byte encoded serialized objects (like protobuf encoded objects and similar) into an SQLite table. But I have seen in the forum that there was some issues with the support of BLOB type in SQLite by CN1. However, these posts are quite old and since then, some plugins like https://github.com/shannah/cn1-spatialite have appeared that seems to work with BLOB type with no issue (spatilite encode each Geography object as a wkb BLOB object in the SQLite database and, as this plugin is working with both Android and iOS, I assume the support of BLOB type on iOS is no longer problematic, exept if this plugin is shipping with its own SQLite engine...). 

So can I use BLOB type in SQLite with CN1 without issue now (I am targeting Android and iOS only) or is it still problematic? If so, how can I write and read a byte array into an SQLite table (do I just have to pass the object as a byte[] object for it to be writen into my BLOB table column ?)

If the support of BLOB type is still problematic, the alternative would be to encode my bytes buffer objects as String before storing them into the database (with base64 encoding or someting similar) but I would really avoid the unecessary bytes<->String encoding/decoding and storage overhead if I can... 

[Thomas]

After looking at the code of the DatabaseImpl from iOS it seems like BLOB won't work on iOS as arguments are all converted to String before beeing passed to the native interface. Indeed the code of the execute() function in this class is:

 @Override

    public void execute(String sql, Object... params) throws IOException{

        // temporary workaround, this will probably fail with blobs

        String[] val = new String[params.length];

        for(int iter = 0 ; iter < val.length ; iter++) {

            if(params[iter] == null) {

                val[iter] = null;

            } else {

                val[iter] = "" + params[iter];

            }

        }

        execute(sql, val);

    } 

So it seems like it is still using a "temporary" workaround that is incompatible with byte[] arguments for BLOB data. Kind of sucks that this workaround as not been fixed by the time...

[Shai Almog]

It's a bit problematic to implement that API on iOS and most developers used workarounds since blobs aren't as necessary on devices.

Unlike servers where the DB is distributed/clustered. On a device a blob provides no benefit and can impact performance. You are better off storing a file URI.

[Thomas]

Blob provide a clear benefit on devices too. It allows to reduce the size of the database when you have large objects to store (not necessarily pictures. It can be geographic shapes like in spatialite for example or any other large object) and these objects can also be deserialised more quickly than if beeing stored as plain text objects (like json or xml)

Nowadays, most developpers actually use byte buffers to handle objects communication and storage (see protobuf, thrift, avro, messagepack...) on the server AND on the device.

Competitors frameworks like flutter support BLOB SQLite type just well bytheway

[Shai Almog]

No.

We don't support byte buffers. Even if we did I'm talking about just using a binary file which will always be faster than any sqlite implementation. This will also allow reducing the database size which will make all queries much faster. You are talking about JSON/XML which isn't what I'm saying.

I'm saying the SQL's blob column should just be a text column pointing at a binary file on the device.

[Thomas]

A binary file with an index in the database would never be faster than storing the data directly into the database. Because for retreiving your data you would have first to request the database for the files indexes (=binary files names) and then to open the matching binary files to fetch the data (if you have 100 results, it means you would have to open and close 100 binary files...). And the size of the database does actually have no impact on the time of the queries. Only the number of stored objects does, not their size. An SQLite database is just an indexed binary file so requesting it is just as looking at the indexes (wich do not depend on the size of the objects stored) and then fetch the matching data from this file.  

Furthermore by keeping all the data in your database you reduce the risk of inconsistency in your data (with binary files and indexes into the DB the two might be out of sync) so as the number of files in your filesystem (because if I have thousands of objects to store it means I would have to keep thousands of different binary files...)

As for supporting BLOB with iOS on CN1 you don't need to support byte buffers. You just have to pass all your arguments as a concatenated byte[] array to the native interface along with an int[2*number of args] array that describe the length and type (which can only be of two types: String or byte[]) of each argument in this concatenated data byte[] array. And on the native side, you deserialize this data by converting parts of this byte[] array that are String args to Strings and keep part that are byte[] args (= BLOB args) to byte[]. 

 

[Shai Almog]

Several things here aren't true.

First this assumes you are always reading all the data, if that was the case then why have an SQL database in the first place. It also assumes only one table and other tables aren't impacted.

The second mistake is that randomly seeking through a large file is free if you have an index. This isn't true. It's true that it's MUCH faster than it was in the days of spinning platters but it's far from free and very costly on a mobile device. A memory mapped file would need caching both in RAM and in CPU caches. Both of these are limited resources that would cause thrashing to flash storage when depleted. Every time you change something in that large file you trigger disk fragmentation which has a lot of impact on an SSD in terms of performance. Yes modern filesystems are better at handling this but writing to a single large file is still way more expensive.

The problem is that these will impact all your queries for the other tables as well. RAM and CPU cache utilization is far more noticeable on devices than on the desktop where the differences might not be noticeable.

We'd need to detect the various types of arguments that are submitted and convert them to native calls for iOS. Passing arrays and other arbitrary objects to the C layer in the iOS port is painful so no one got around to do it for years. As I said, the demand for this was low and our resources are low as well.

[Thomas]

I never assumed that I am reading all the data nor that I have only one table. I only said that if you have 100 match for a query (you can have thousands of rows in your table. That is the point of having a DB, handling multiple objects not just one or two) then you would have to read 100 files. Disk fragmentation on an SSD, on the contrary to HDD, is not an issue and actually do not really exist (the SSD can acess each cell with the same access speed, it do not depend on the physical distance of the previously accessed cell) especially since all SSD now use Trim. So you are wrong again here. As for the DB indexes they are put into memory but the amount it takes into memory do not depend on the size of the records but only on their number (and the number of indexed columns) so here again you have no penality at inserting large objects into a DB if this data (=column) is not indexed  

[Thomas]

We'd need to detect the various types of arguments that are submitted and convert them to native calls for iOS. Passing arrays and other arbitrary objects to the C layer in the iOS port is painful so no one got around to do it for years. As I said, the demand for this was low and our resources are low as well.

It can be handled really easily. I posted an RFE on github that explains it. It shouldn't take more than 1h to implement the objC part and test it (unfortunatelly I can't test iOS native code locally else I would have done it and submited a pull request. There is also some mentions of the NEW_CODENAME_ONE_VM in the current code and I don't really know what it is (what is this new VM and what is the old one?) reason why I prefered to let you write that part of the code. But I would write it in my fork of CN1 if I have to...)

[Shai Almog]

Flash is slow especially in write. RAM/CPU cache are faster and they deplete with larger files. Feel free to benchmark this on a device. If it's easy feel free to implement a PR.