sqlite creating zeroblobs is VERY slow on WR703N hardware

[gardners]

It can take minutes to create an empty blob, presumably because sqlite zeroes out the storage, during which time servald is unresponsive. This is one of the causes of #46.

It seems that in this situation storing large blobs as regular files would be much, much faster. Should probably be a configurable option.

It may also make sense to adjust the database page size to suit the native page size of the underlying flash storage we are using (a USB memory stick in this case).

[gardners]

One way to implement this is to use the column affinity rules and value types in sqlite. A blob column can have non-blob values inserted, e.g., a filename of type TEXT. When we go to open the blob, we can detect the type, and realise that we need to open a real file instead.

[gardners]

To clarify just how slow the current process is: Creating a 33MB blob requires 2 minutes 18 seconds. This equates to 240KB/sec, just to create the blob. This suggests that any blob > about 64KB should be stored externally, at least on these devices.

[gardners]

This issue impacts on #10, as asking sqlite how much space it is using will no longer be sufficient. Commit 4b8696c addresses part of the interaction by removing external blob files when the file is dropped from the database.

[gardners]

Now seems largely complete, and passes all but one rhizome test. The failing test is related to removing stale rows from fileblobs, and will not affect our immediate needs for KiwiEx, still needs to be fixed.

[gardners]

Closing issue, and creating new issue for the outstanding issue (removing stale blob files).