support for BLOBs

[jgoerzen]

Added by Gour D. 2009-01-09

http://software.complete.org/software/issues/show/132

Hi!

According to your reply in http://software.complete.org/software/boards/6/topics/show/55 there is no suppport fro BLOBs in HDBC's API, so it would be nice to have it.

Sincerely,
Gour

p.s. btw, Forums are broken in your Redmine, i.e. Forums link leads to http://software.complete.org/software/projects/hdbc/boards which gives Intrnal error.

[jgoerzen]

Updated by John Goerzen 463 days ago

Comment Edit

The forums issue should be fixed now that I have updated Redmine.

[gour]

Hello!

Some further contemplation of the needs for our project has led me to the conclusion that we want to stay focused on using sqlite3 back-end since it provides the most simple setup for the end-user without any need to fiddle with configuring separate (e.b. PostgreSQL) server.

That's why we took (again) look on available Haskell bindings for sqlite3 and (again) HDBC looks as the most nicely designed package providing the right balance between the high(er) & low(er) level API.

The only gotcha is this issue, i.e. lack of support for handling BLOBs.

Of course, there are two camps...1st one is suggesting to save only pathname in the database and store BLOB (image, in our case) in the filesystem.

Otoh, storing BLOBs in the database greatly simplifies backup and/or portability for the end-user who can just cp sqlite3 db file to the stick and put on another machine easily.

Now, the question is, since I do not see (atm) some nice way to abstract dealing with BLOBs on the HDBC level and considering that we desire to use HDBC-sqlite3 in our project, what can you recommend to insert BLOBs into our database?

One solution could be using base64_encoding and inserting as text, but this increases the size of the 'image'...anything else?

Sincerely,
Gour

[jgoerzen]

It would be good to have a generic HDBC API to BLOBs. I know that at least PostgreSQL has some support for them. I haven't put the time into researching BLOB support enough to formulate a generic API for them. If you wanted to do that, though, that would be great.

Even if there isn't a generic API, a place to start might be adding a BLOB module to the Sqlite3 backend.

All of the encoding options have various drawbacks, mainly related to not being seekable.

[gour]

It would be good to have a generic HDBC API to BLOBs.

I'm glad to hear you are for it.

I know that at least PostgreSQL has some support for them. I haven't put the time into researching BLOB support enough to formulate a generic API for them. If you wanted to do that, though, that would be great.

I'll try to do something 'cause having 'native' BLOB support in HDBC would be better than fiddling with encoding & decoding.

Even if there isn't a generic API, a place to start might be adding a BLOB module to the Sqlite3 backend.

I see...

Here is some snippt which I've found which shows how is sqlite3 support for BLOB(s), although I'm not sure if there is some more modern API available...

import sqlite3 as db

c = db.connect(database="images.db")

cu = c.cursor()
cu.execute("CREATE TABLE  images (FileName TEXT, FileContent BLOB)")

query="INSERT INTO images (FileName, FileContent) VALUES ('1', ?)"

f=file(u'image.jpg', 'rb')

k=f.read()
f.close()

cu.execute(query, [db.Binary(k)])
c.commit()

cu.execute("SELECT FileContent FROM images")

k=0
for i in cu.fetchall():
    k+=1
    f=file(str(k)+'.jpg', 'wb')

f.write(i[0])
f.close()

All of the encoding options have various drawbacks, mainly related to not being seekable.

Well, I was/am considering base64 which apparently has the best
support but creates bigger overhead. Another option was yEnc (less
overhead and weaker support).

Otoh, considering we plan to store images, not being seekable is not
so serious drawback, but let’s try to bring BLOBs to HDBC.

Sincerely,
Gour

[gour]

Hello,

direct-sqlite package says it "Includes UTF8 and BLOB support."

By looking at direct-sqlite3 we can see they use sqlite3_bind_blob which has the following signature in Haskell:

bindBlob :: Statement  -> Int  -> ByteString  -> IO  ()

Result is handled with sqlite3_column_blob.

By looking at Sqlite3's news, I see that the only new thing in regard to BLOBs is the one arrived in 3.4.0 which brought support for Incremental BLOB I/O.

Too bad, that auuthor of direct-sqlite didn't want to add BLOB support to HDBC, but went creating (another) new bindings. :-/

Sincerely,
Gour
Sincerely,
Gour