Can sqlite3_blob_write be done through SQL?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Can sqlite3_blob_write be done through SQL?

Yuri
I have to call sqlite3_blob_write to partially modify blobs.


Wouldn't it have been better if there was a function, for example,
writeblob(tbl.blob_fld, offset, portion_data)? SQLite could do the same
that sqlite3_blob_write is doing when writeblob result is assigned to
the same row/column that is supplied as the argument, and rewrite the
whole blob otherwise?


Also, do sqlite3_blob_write calls participate in transactions?


Thanks,

Yuri


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Clemens Ladisch
Yuri wrote:
> I have to call sqlite3_blob_write to partially modify blobs.
>
> Wouldn't it have been better if there was a function, for example,
> writeblob(tbl.blob_fld, offset, portion_data)? SQLite could do the
> same that sqlite3_blob_write is doing when writeblob result is
> assigned to the same row/column that is supplied as the argument, and
> rewrite the whole blob otherwise?

The sqlite3_blob* interface is just an optimization, and it looks as if
it was included for some specific application.

It would certainly be possible to add your own user-defined SQL function
to call sqlite3_blob_write().

> Also, do sqlite3_blob_write calls participate in transactions?

Of course they do.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Yuri
On 03/01/2017 23:41, Clemens Ladisch wrote:

> It would certainly be possible to add your own user-defined SQL function
> to call sqlite3_blob_write().


I think this should be added to sqlite itself. Writing a portion of blob
is very much like updating a field. There is currently no way to do this
in SQL fashion in an efficient way. So such command can be chained with
other SQL statements and make the client code much simpler.


Yuri

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Clemens Ladisch
Yuri wrote:
> On 03/01/2017 23:41, Clemens Ladisch wrote:
>> It would certainly be possible to add your own user-defined SQL function
>> to call sqlite3_blob_write().
>
> I think this should be added to sqlite itself.

So far, nobody except you has requested it.

> Writing a portion of blob is very much like updating a field. There is
> currently no way to do this in SQL fashion in an efficient way.

Neither is there a way to update a text field efficiently; for both types,
you have to handle the pieces manually:

  UPDATE MyTable
  SET Field = substr(Field, 1, 10) || 'xxx' || substr(Field, 14);

And handling blob values does not make much sense in a pure SQL script;
binary data is usually handled in the application from which the SQLite
library is accessed.

> So such command can be chained with other SQL statements and make the
> client code much simpler.

Do you have a concrete example for such a statement?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

J Decker
In reply to this post by Yuri
On Fri, Mar 3, 2017 at 3:58 PM, Yuri <[hidden email]> wrote:

> On 03/01/2017 23:41, Clemens Ladisch wrote:
>
> It would certainly be possible to add your own user-defined SQL function
>> to call sqlite3_blob_write().
>>
>
>
> I think this should be added to sqlite itself. Writing a portion of blob
> is very much like updating a field. There is currently no way to do this in
> SQL fashion in an efficient way. So such command can be chained with other
> SQL statements and make the client code much simpler.
>
>
All blobs I've used in SQL are themselves atomic and partial updates would
never be useful.  (images, fingerprints, etc.)  If you're updating parts of
blobs then probably you mean that blob to be in multiple parts or broken
into separate fields.


> Yuri
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Jens Alfke-2

> On Mar 4, 2017, at 8:50 AM, J Decker <[hidden email]> wrote:
>
> All blobs I've used in SQL are themselves atomic and partial updates would
> never be useful.  (images, fingerprints, etc.)  If you're updating parts of
> blobs then probably you mean that blob to be in multiple parts or broken
> into separate fields.


I think you’re over-generalizing from your own experience.
One might be downloading a blob, and receiving the content-length up front but the data in chunks.
One might be storing a large mutable set of integers as a bitmap.

(I’m not thinking specifically of updating a blob via SQL, just of use cases for partial updates of blobs.)

A fun but impractical use case would be to implement a VFS that lets you store a SQLite database within a blob in another SQLite database. SQLinception!

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Yuri
In reply to this post by Clemens Ladisch
On 03/04/2017 01:22, Clemens Ladisch wrote:
> Do you have a concrete example for such a statement?


Yes. I need to re-assemble the large data packet that comes in portions.
I would like to use a blob field for this. I don't want to store parts
in separate records, because this is essentially one file.

So far sqlite is failing, because the handle returned by
sqlite3_blob_write is invalidated by the updates to the other fields,
and sqlite3_blob_open/sqlite3_blob_close are too slow to call them on
every fragment.

I would like to maintain the record like this:

table file {

     id integer,

     num_done integer,

     num_total integer,

     data blob

}

File metadata and data all in one record. Blob handle gets invalidated
when metadata is updated, and it's too expensive to open/close the blob
every time.

Due to these two problems sqlite can't support this reasonable, IMO, use
case.


Yuri

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Clemens Ladisch
Yuri wrote:
> So far sqlite is failing, because the handle returned by
> sqlite3_blob_write is invalidated by the updates to the other fields,

This would happen regardless of whether you call sqlite3_blob_write()
yourself or through an SQL function.

> and sqlite3_blob_open/sqlite3_blob_close are too slow to call them on
> every fragment.

This would happen regardless of whether you call them yourself or
through an SQL function.


I think you can work around these problems by moving the blob value into
a separate table.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Yuri
On 03/04/2017 23:08, Clemens Ladisch wrote:
> I think you can work around these problems by moving the blob value into
> a separate table.


I thought so too. However, even updates to other tables invalidate the
blob handle. Please see the example below.

Variable breakMe breaks the second sqlite3_blob_write call, even though
the added update statement is for the other table.


Yuri


#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"

int breakMe = 1;

char *exec_errmsg;

void execSql(sqlite3 *db, const char *sql) {
   int rc;
   if (SQLITE_OK != (rc = sqlite3_exec(db, sql, NULL, NULL,
&exec_errmsg))) {
     fprintf(stderr, "Error while executing sql=%s, error=%d\n", sql, rc);
     exit(1);
   }
}

int main(int argc, char **argv) {
   sqlite3 *db = NULL;
   int rc;

   rc = sqlite3_open(argv[1], &db);
   if (SQLITE_OK != rc) {
     fprintf(stderr, "Can't open database %s (%i): %s\n", argv[1], rc,
sqlite3_errmsg(db));
     exit(1);
   }

   execSql(db, "CREATE TABLE fragmented_data ( friend_id INTEGER NOT
NULL, frags_id  INTEGER NOT NULL, message   BLOB, PRIMARY KEY (
friend_id, frags_id));");
   execSql(db, "CREATE TABLE fragmented_meta ( friend_id INTEGER NOT
NULL, frags_id        INTEGER NOT NULL, timestamp_first INTEGER NOT
NULL, timestamp_last  INTEGER NOT NULL, frags_done      INTEGER NOT
NULL, frags_num       INTEGER NOT NULL, message         BLOB, PRIMARY
KEY ( friend_id, frags_id));");

   execSql(db, "INSERT INTO fragmented_data (friend_id, frags_id,
message) SELECT 0, 1488703606591, zeroblob(2070) WHERE NOT EXISTS
(SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND
frags_id=1488703606591);");
   execSql(db, "INSERT INTO fragmented_meta (friend_id, frags_id,
timestamp_first, timestamp_last, frags_done, frags_num) SELECT 0,
1488703606591, 1488703647556, 1488703647556, 0, 2 WHERE NOT EXISTS
(SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND
frags_id=1488703606591);");

   sqlite3_blob *blob1;
   rc = sqlite3_blob_open(db, "main", "fragmented_data", "message", 1,
1, &blob1);
   if (SQLITE_OK != rc) {
     fprintf(stderr, "Couldn't get blob handle (%i): %s\n", rc,
sqlite3_errmsg(db));
     exit(1);
   }
   if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "1", 1, 0x10))) {
     fprintf(stderr, "Error writing to blob handle #1. rc=%d\n", rc);
     exit(1);
   }

   if (breakMe)
     execSql(db, "UPDATE fragmented_meta SET
timestamp_last=max(timestamp_last,1488703647556), frags_done =
frags_done+1 WHERE friend_id=0 AND frags_id=1488703606591;");

   if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "2", 1, 0x250))) {
     fprintf(stderr, "Error writing to blob handle #2. rc=%d\n", rc);
     exit(1);
   }

   sqlite3_blob_close(blob1);
   printf("Successfully wrote to blob\n");
   sqlite3_close(db);
   return 0;
}

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Keith Medcalf

Perhaps because you are committing the transaction?

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Yuri
> Sent: Sunday, 5 March, 2017 02:27
> To: SQLite mailing list
> Subject: Re: [sqlite] Can sqlite3_blob_write be done through SQL?
>
> On 03/04/2017 23:08, Clemens Ladisch wrote:
> > I think you can work around these problems by moving the blob value into
> > a separate table.
>
>
> I thought so too. However, even updates to other tables invalidate the
> blob handle. Please see the example below.
>
> Variable breakMe breaks the second sqlite3_blob_write call, even though
> the added update statement is for the other table.
>
>
> Yuri
>
>
> #include <stdio.h>
> #include <stdlib.h>
> #include "sqlite3.h"
>
> int breakMe = 1;
>
> char *exec_errmsg;
>
> void execSql(sqlite3 *db, const char *sql) {
>    int rc;
>    if (SQLITE_OK != (rc = sqlite3_exec(db, sql, NULL, NULL,
> &exec_errmsg))) {
>      fprintf(stderr, "Error while executing sql=%s, error=%d\n", sql, rc);
>      exit(1);
>    }
> }
>
> int main(int argc, char **argv) {
>    sqlite3 *db = NULL;
>    int rc;
>
>    rc = sqlite3_open(argv[1], &db);
>    if (SQLITE_OK != rc) {
>      fprintf(stderr, "Can't open database %s (%i): %s\n", argv[1], rc,
> sqlite3_errmsg(db));
>      exit(1);
>    }
>
>    execSql(db, "CREATE TABLE fragmented_data ( friend_id INTEGER NOT
> NULL, frags_id  INTEGER NOT NULL, message   BLOB, PRIMARY KEY (
> friend_id, frags_id));");
>    execSql(db, "CREATE TABLE fragmented_meta ( friend_id INTEGER NOT
> NULL, frags_id        INTEGER NOT NULL, timestamp_first INTEGER NOT
> NULL, timestamp_last  INTEGER NOT NULL, frags_done      INTEGER NOT
> NULL, frags_num       INTEGER NOT NULL, message         BLOB, PRIMARY
> KEY ( friend_id, frags_id));");
>
>    execSql(db, "INSERT INTO fragmented_data (friend_id, frags_id,
> message) SELECT 0, 1488703606591, zeroblob(2070) WHERE NOT EXISTS
> (SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND
> frags_id=1488703606591);");
>    execSql(db, "INSERT INTO fragmented_meta (friend_id, frags_id,
> timestamp_first, timestamp_last, frags_done, frags_num) SELECT 0,
> 1488703606591, 1488703647556, 1488703647556, 0, 2 WHERE NOT EXISTS
> (SELECT 1 FROM fragmented_meta WHERE friend_id=0 AND
> frags_id=1488703606591);");
>
>    sqlite3_blob *blob1;
>    rc = sqlite3_blob_open(db, "main", "fragmented_data", "message", 1,
> 1, &blob1);
>    if (SQLITE_OK != rc) {
>      fprintf(stderr, "Couldn't get blob handle (%i): %s\n", rc,
> sqlite3_errmsg(db));
>      exit(1);
>    }
>    if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "1", 1, 0x10))) {
>      fprintf(stderr, "Error writing to blob handle #1. rc=%d\n", rc);
>      exit(1);
>    }
>
>    if (breakMe)
>      execSql(db, "UPDATE fragmented_meta SET
> timestamp_last=max(timestamp_last,1488703647556), frags_done =
> frags_done+1 WHERE friend_id=0 AND frags_id=1488703606591;");
>
>    if (SQLITE_OK != (rc = sqlite3_blob_write(blob1, "2", 1, 0x250))) {
>      fprintf(stderr, "Error writing to blob handle #2. rc=%d\n", rc);
>      exit(1);
>    }
>
>    sqlite3_blob_close(blob1);
>    printf("Successfully wrote to blob\n");
>    sqlite3_close(db);
>    return 0;
> }
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Gerry Snyder-4
In reply to this post by Yuri
Would it be possible for you to write the other columns just once, after
all the blob has been inserted?

On Mar 4, 2017 4:34 PM, "Yuri" <[hidden email]> wrote:

> On 03/04/2017 01:22, Clemens Ladisch wrote:
>
>> Do you have a concrete example for such a statement?
>>
>
>
> Yes. I need to re-assemble the large data packet that comes in portions. I
> would like to use a blob field for this. I don't want to store parts in
> separate records, because this is essentially one file.
>
> So far sqlite is failing, because the handle returned by
> sqlite3_blob_write is invalidated by the updates to the other fields, and
> sqlite3_blob_open/sqlite3_blob_close are too slow to call them on every
> fragment.
>
> I would like to maintain the record like this:
>
> table file {
>
>     id integer,
>
>     num_done integer,
>
>     num_total integer,
>
>     data blob
>
> }
>
> File metadata and data all in one record. Blob handle gets invalidated
> when metadata is updated, and it's too expensive to open/close the blob
> every time.
>
> Due to these two problems sqlite can't support this reasonable, IMO, use
> case.
>
>
> Yuri
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Yuri
In reply to this post by Keith Medcalf
On 03/05/2017 03:48, Keith Medcalf wrote:
> Perhaps because you are committing the transaction?

There is no COMMIT statement there.


Yuri

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Simon Slavin-3

On 5 Mar 2017, at 3:06pm, Yuri <[hidden email]> wrote:

> On 03/05/2017 03:48, Keith Medcalf wrote:
>> Perhaps because you are committing the transaction?
>
> There is no COMMIT statement there.

If you try to execute a command without putting it in a transaction (i.e. using BEGIN) then SQLite wraps it in a transaction of its own.  So a command like

UPDATE …

is processed as if it is

BEGIN
UPDATE …
COMMIT

If you want to delay the ending of the transaction you have to put the BEGIN in before it, and then you must put the COMMIT in after it or it won’t be committed.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Yuri
On 03/05/2017 07:20, Simon Slavin wrote:
> If you want to delay the ending of the transaction you have to put the BEGIN in before it, and then you must put the COMMIT in after it or it won’t be committed.

The second blob write fails even after adding BEGIN before
sqlite3_blob_open and COMMIT after sqlite3_blob_close.


Yuri

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Simon Slavin-3

On 5 Mar 2017, at 4:11pm, Yuri <[hidden email]> wrote:

> On 03/05/2017 07:20, Simon Slavin wrote:
>> If you want to delay the ending of the transaction you have to put the BEGIN in before it, and then you must put the COMMIT in after it or it won’t be committed.
>
> The second blob write fails even after adding BEGIN before sqlite3_blob_open and COMMIT after sqlite3_blob_close.

Yes, as we already established it’s because you changed the values of other columns in the row.  I think you’re using SQLite’s blob calls in an unexpected way.  They’re not meant to be used as an ongoing editor.  Adding the kind of support you’ve asked for would involve slowing down the routines for all the existing users.  Not really worth it.

I think you’re better off reading the existing BLOB value into memory using SELECT, editing it using standard memory-manipulation routines, then writing it back with an UPDATE when you’re ready.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Yuri
On 03/05/2017 08:22, Simon Slavin wrote:
> Yes, as we already established it’s because you changed the values of other columns in the row.  I think you’re using SQLite’s blob calls in an unexpected way.  They’re not meant to be used as an ongoing editor.  Adding the kind of support you’ve asked for would involve slowing down the routines for all the existing users.  Not really worth it.

So, to be clear, blob writes can only be used when no other statements
are executed in the middle? Because the latest testcase only changes the
other table, not the table where the blob is.

> I think you’re better off reading the existing BLOB value into memory using SELECT, editing it using standard memory-manipulation routines, then writing it back with an UPDATE when you’re ready.


This is very slow.


Yuri

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG: blob handles are invalidated in too many tables (was: Can sqlite3_blob_write be done through SQL?)

Clemens Ladisch
In reply to this post by Yuri
Yuri wrote:
> even updates to other tables invalidate the blob handle. Please see the example below.

This is a bug.

Apparently, it was introduced in commit f17ef37897da9bca, where the
function invalidateIncrblobCursors() forgets to check the pgnoRoot
parameter, which means that blob cursors are invalidated by changes to
any row with the same rowid, even in some other table.  (Commit
257e9b575029f632 then removed that unused parameter without noticing
that it should have been used.)


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Simon Slavin-3
In reply to this post by Yuri

On 5 Mar 2017, at 4:28pm, Yuri <[hidden email]> wrote:

> So, to be clear, blob writes can only be used when no other statements are executed in the middle? Because the latest testcase only changes the other table, not the table where the blob is.

I’m glad to see that someone else has identified this as a bug.  Whether you’ll be able to do what you want when the bug is fixed I can’t tell.  You’re going to have to wait for the developer team to fix the bug, then test again.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Yuri
On 03/05/2017 09:49, Simon Slavin wrote:
> On 5 Mar 2017, at 4:28pm, Yuri <[hidden email]> wrote:
>
>> So, to be clear, blob writes can only be used when no other statements are executed in the middle? Because the latest testcase only changes the other table, not the table where the blob is.
> I’m glad to see that someone else has identified this as a bug.  Whether you’ll be able to do what you want when the bug is fixed I can’t tell.  You’re going to have to wait for the developer team to fix the bug, then test again.


Thanks for your help!

Yuri
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can sqlite3_blob_write be done through SQL?

Clemens Ladisch
In reply to this post by Simon Slavin-3
Simon Slavin wrote:
> You’re going to have to wait for the developer team to fix the bug,

or change the row IDs so that they are unique in the entire database.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12
Loading...