Sqlite BLOB column not supported.

[acidjunk]

When trying to convert a sqlite DB with some BLOB columns I run into a fatal error:
ERROR Database error 42704: type "blob" does not exist.

It now fails with:

isOyqMSSOyKymoyC+DmHFgy8eMIz9UD6pXDF08OnH0s59c0/NlwDDB/gxlE+n/3xP8A/qduy6/WcTPXf/3s27/DlAnKW2Acmy5SW3IyMomJaUWIunU6lyxdBAE0YoC6DV/5Qby8kG/K6BSToaKDGRVG3EoB3KDObHzQYxN5W0Qbsg2vdBkGogFgsAsmqH5K6DTVRWq7INp+6DKA/miA/dVW0UBdAPC6qMFnRmKm1WWoNeqqN5IrFBg11IMSPNUb8BEY2UUGH9VQDdBrINr3Qa3miMit3KiMyKBVDR+CgPzVG7IjN5IrWooP/9k="
is not of type
  (OR NULL (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (*))).

I've added a SQLite file with 'files' table including a 'file_data' BLOB field. You'll find two records, one PDF and one JPEG file.

https://dl.dropboxusercontent.com/u/20756661/storage.sqlite.tgz

[acidjunk]

A log with the complete error:
https://dl.dropboxusercontent.com/u/20756661/sqlite2postgres_blob.log

[dimitri]

I think it's a problem with the unique concept of manifest typing used in SQLite3, wherein I have the following quite strange result:

SQLITE> (let ((statement (sqlite:prepare-statement *sqlite-db*
                                                   "select file_data from files limit 1")))
          (sqlite:step-statement statement)
          (prog1
              (sqlite-ffi:sqlite3-column-type (sqlite::handle statement) 0)
            (sqlite:finalize-statement statement)))
:TEXT

Note that it's not the driver's fault:

sqlite> select typeof(file_data) from files limit 1;
text

I will see later what can be done here, given that SQLite happily returns text rather than blob as soon as the data itself fits into the text datatype rules, or something. Hopefully there's a way to force SQLite into returning blob data without having to parse and copy the text output into a PostgreSQL bytea representation.

That said it might be that just storing the string characters as their ascii bytes into the bytea is all we need to do here.

[dimitri]

Aha. After some tests, your data is base64 encoded, so returned as text for that reason.

[acidjunk]

I tried rebuilding pgloader, with make pgloader: but it seems to need an extra DEP:
Fatal MISSING-DEPENDENCY:
Component #:BASE64 not found, required by #<SYSTEM "pgloader">

[dimitri]

Sorry about that, went too fast again. Should be ok now (did compile a binary and used it against the new regression test here).

[acidjunk]

It works now. But when converting a very big DB (sqlite with 400Mb on Blob in a table) I run into memory shortage issues. Is there a smart way around this? (it's not a big issue for me, I can clean up the table before doing the convert)

[dimitri]

You can try the batch settings (see Batch behaviour options in the Reference documentation), or even try compiling with CCL (at http://ccl.clozure.com/) if you feel that way.