On Mon, Mar 6, 2017 at 11:39 AM, Yuri <y...@rawbw.com> wrote: > On 03/06/2017 01:00, Dominique Devienne wrote: > >> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html >> though, so I'm afraid this is "by design". --DD >> > > Even though this is documented, parts of this limitation don't appear to > be reasonable. Updating an integer field in the same row shouldn't affect > the blob field. Rows can be very large and shouldn't move when individual > fields are updated.
Don't get me wrong, I'm not fond of SQLite blobs as they stand, and I've said so before on this list. But modulo the bug you found (mixup of rowids across tables, as mentioned by Clemens), there's not much any of us can do, this is in DRH's hands, and from my past threads here, not much is likely to happen in this regard. Changing the way blobs work in SQLite would probably require a format change (I think), and that alone is a big enough change that makes it rather unlikely I'm afraid. FWIW, there are two main issues with blobs IMHO: 1) they are always stored "in-row". Oracle for example only stores the blob "index" in-row, i.e. which "blob" pages make up the blob. This makes accessing columns (notably later added columns) after the blobs very expensive with large blobs. But makes schema evolution more difficult than it should be. And the advice to putting blobs in separate tables is just a workaround, requires joins, and makes it more difficult to ensure 1-to-1 relationship between the "meta" row and "bulk-data" row, and associated lifetime management. 2) they can't be updated incrementally generally, except in very limited cases, and incurring more IO than should be. with "dual stage" blobs (or the proverbial level of indirection), if you want to change a few bytes in the middle, the IO cost if updating the blob index and update that one "blob" page that needs updating. Growing or shrinking can similarly be supported transactionally, w/o having to copy the whole blob. SQLite works well with blobs, as long as they stay small, or they are never updated. Outside these use cases, things start the break down and you must either resort to work-arounds, and stop using SQLite for those bulk-data. And that's a pity/shame IMHO. My $0.02 :) --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
