Speaking as an IT professional with 20+ years in the field, I would have to say adding any kind of "file system" support to SQLite would be a horrible thing. Yes, I've used Oracle. I've also used the only real product Oracle has, RDB on OpenVMS. I've written books covering MySQL, PostgreSQL, and Object databases like POET.
Lite is called lite for a reason. Bare functionality with a lot of speed. The architects for this product need to take a lesson from the old DOS xBase systems. Blobs should not be handled as one big unit. They need to be given unique ID's and stored in fixed size chunks off in a hidden table much like a "memo" field was back in the day. The "hidden" or child table supporting the blob column would have a key of ID + sequence. The actual row should be ID, Sequence, BytesUsed, Chunk. They BytesUsed allows you to keep exact byte sizes. All Chunk data types should be a raw byte data type. There should be multiple chunk column types: chunk24, chunkM, and chunkXM for 1024 bytes, 1Meg, and 10Meg chunk column types. On Tue, 2013-04-23 at 09:50 +0200, Dominique Devienne wrote: > On Mon, Apr 22, 2013 at 2:10 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > > On 22 Apr 2013, at 12:39pm, Paolo Bolzoni wrote: > > > But I noticed that sqlite3_blob_write cannot increase the size of the > > pointed > > > open blob. So I ask, there is a way to treat a blob as a stream so I can > > write > > > or read values in it with ease? > > > > Unfortunately the size (length) of the BLOB is very significant to the > > larger space-handling aspects of SQLite's file format. Making a BLOB > > longer could force SQLite to move the data from page to page and do lots of > > other internal reorganisation. So you can reserve extra space when you > > write the BLOB, and you can read whatever you want, but the documentation > > is accurate. > > > > I also really wish SQLite blobs would map directly to the usual FILE* > semantic, both in being able to grow a blob via writing (and truncate it > too), but also and more importantly not rewriting the whole row or blob > when modifying only a few bytes of the blob, but only affected pages. > Basically another level of indirection, where the row holds only a blob > locator (like in Oracle), and the blob value is in separate, not > necessarily contiguous pages, as described here for example: > http://jonathanlewis.wordpress.com/2013/03/22/lob-update/. That way only > modified blob pages would need to participate in the transaction. SQLite is > not MVCC like Oracle, but the ability to at least not overwrite the whole > blob when changing 1 byte would be great. (I'm assuming there isn't, but > I'm no SQLite expert). My $0.02. --DD > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Roland Hughes, President Logikal Solutions (630)-205-1593 http://www.theminimumyouneedtoknow.com http://www.infiniteexposure.net No U.S. troops have ever lost their lives defending our ethanol reserves. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
