Re: [sqlite] VT table behavior change between 3.10 and 3.17

Can you support this notion with the byte code program of the affected statement? In the sqlite shell type .explain explain and post the output or send it to me off list. NOTE: I am not an SQLite developer, but will need to upgrade sometime later, so getting ahead of possible changes would

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

Le 29 mars 2017 à 02:38, Simon Slavin a écrit : >> It seems sqlite look first if there is a temp.table before main.table and >> without qualification temp.table is used. > > You got it. It’s not obvious that this is what SQLite would do. But now you > know it you

Re: [sqlite] VT table behavior change between 3.10 and 3.17

On Wed, 29 Mar 2017, Hick Gunter wrote: Can you provide an example of the bytecode produced? sqlite> .explain sqlite> explain delete from device_cfgrecord where name == 'bar'; addr opcode p1p2p3p4 p5 comment -

Re: [sqlite] VT table behavior change between 3.10 and 3.17

On 03/29/2017 08:41 PM, Hick Gunter wrote: Can you support this notion with the byte code program of the affected statement? In the sqlite shell type .explain explain and post the output or send it to me off list. NOTE: I am not an SQLite developer, but will need to upgrade sometime later,

Re: [sqlite] VT table behavior change between 3.10 and 3.17

> [...] Why xOpen-ed cursor cannot be used to write to a table? > The xUpdate method alone is used to perform updates to virtual tables. > It does not take a cursor argument. > Also, there is no method taking a cursor argument that allows data to be > changed. > The documentation is correct

Re: [sqlite] VT table behavior change between 3.10 and 3.17

On Wed, 29 Mar 2017, Hick Gunter wrote: To avoid anomalies when changing "key fields", SQLite will scan through the whole cursor first, saving the rowids and new contents of the record(s) satisfying the WHERE clause. It will then close the cursor and call xUpdate for the affected records.

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Seems like the Close (0) Opcode (expected at line 10.5 and 16.5 respectively) is suspiciously missing from the generated program. I guess this will be handled in Halt (where it was probably checked anyway all along). But the VUpdate opcode is still outside the VNext loop. -Ursprüngliche

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Hello, On 2017-03-29 15:34, Bob Friesenhahn wrote: If this is supposed to be the case, then it seems that xClose() is not being called before xUpdate() with 3.17. BTW. Why (while in UPDATE) there is still emitted Close just before Halt? Halt closes opened cursors at the very beginning. Maybe

Re: [sqlite] VT table behavior change between 3.10 and 3.17

The Close (1) in the second program is closing (=deleting) the ephemeral table. I have no idea if this would also be handled in Halt or not. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cezary H. Noweta Gesendet:

Re: [sqlite] VT table behavior change between 3.10 and 3.17

On 3/29/17, Hick Gunter wrote: > The Close (1) in the second program is closing (=deleting) the ephemeral > table. I have no idea if this would also be handled in Halt or not. OP_Halt always closes all open cursors. Calling sqlite3_reset() does too. In fact, it's the same

[sqlite] Questions on big blobs and blob I/O

I’m thinking of transitioning from external to internal storage of large blobs. That is, currently I store these as individual files in a directory next to the SQLite database. But it sounds like today’s new improved SQLite is better at managing big blobs, and supports streaming reads; and it

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Hello, On 2017-03-29 18:00, Richard Hipp wrote: One could omit all OP_Close opcodes and I think everything would still work. But sometimes an OP_Close can free up resources sooner rather than later. Also, some b-tree operations are faster if there is only a single open cursor on that b-tree,

Re: [sqlite] Questions on big blobs and blob I/O

> Le 29 mars 2017 à 19:40, Simon Slavin a écrit : > >> Since I’m using WAL, will this db handle keep a snapshot of the time before >> the blob’s row was deleted, allowing the blob handle to keep working? As Simon said wisely, a word of the developers would clarify this

Re: [sqlite] Is FTS5 still experimental?

On 03/30/2017 12:26 AM, Jen Pollock wrote: The release notes for 3.13.0 say that FTS5 will be "experimental" for at least one more release cycle, and more recent release notes don't say anything about that having changed, but the FTS5 documentation doesn't mention anything about it being

[sqlite] Is FTS5 still experimental?

The release notes for 3.13.0 say that FTS5 will be "experimental" for at least one more release cycle, and more recent release notes don't say anything about that having changed, but the FTS5 documentation doesn't mention anything about it being experimental. Is it still experimental now or not?

Re: [sqlite] Questions on big blobs and blob I/O

Remember to make the blob field the very last field in your table schema, and avoid "select * from", otherwise you can create performance problems. The data for a record is stored in the same order as the fields are defined, and overflow pages are a linked list. So if you have your 100 MB blob

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Hello, On 2017-03-29 18:00, Richard Hipp wrote: One could omit all OP_Close opcodes and I think everything would still work. But sometimes an OP_Close can free up resources sooner rather than later. Also, some b-tree operations are faster if there is only a single open cursor on that b-tree,

Re: [sqlite] Questions on big blobs and blob I/O

On 29 Mar 2017, at 6:14pm, Jens Alfke wrote: > * Do big blobs cause any performance problems for the db as a whole, like > fragmentation? In particular, is the blob data stored contiguously in the > file? Blobs are stored in the same pages that other values are stored in.

Re: [sqlite] Questions on big blobs and blob I/O

On 03/30/2017 12:52 AM, Olivier Mascia wrote: Le 29 mars 2017 à 19:40, Simon Slavin a écrit : Since I’m using WAL, will this db handle keep a snapshot of the time before the blob’s row was deleted, allowing the blob handle to keep working? As Simon said wisely, a word

Re: [sqlite] Questions on big blobs and blob I/O

On 3/29/17, David Raymond wrote: > Remember to make the blob field the very last field in your table schema, > and avoid "select * from", otherwise you can create performance problems. > The data for a record is stored in the same order as the fields are defined, > and

Re: [sqlite] Questions on big blobs and blob I/O

@Jens; I'd take what David suggested one step further and just remove the BLOB from the tables of concern and keep the blob on a 1:1 PK:FK relationship and only look at the BLOB tables when required. That way if you do an occasional [select * from ...] you're not reading the BLOB because it's

Re: [sqlite] VT table behavior change between 3.10 and 3.17

According to the documentation of Virtual Tables and Opcodes: xBegin( table ) is called to announce that SQlite intends to write to the table. There is no cursor involved. Do whatever is necessary to write to the backing store and set any VT implementation specific information in the table

[sqlite] Goto's in .explain output

Gunter posted this explain VDBE code: asql> explain update mytable set myfield=2 where myconst=7; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000

Re: [sqlite] Goto's in .explain output

The section at the end, which is executed first, contains the necessary database locking and schema checking opcodes. These cannot be generated before the rest of the program has analysed which operations are performed on which tables and therefore which of the attached database(s) needs to be

Re: [sqlite] VT table behavior change between 3.10 and 3.17

On 03/29/2017 02:48 AM, Bob Friesenhahn wrote: We are trying to update from sqlite3 3.10 to 3.17. Our virtual table modules are encountering problems with 3.17 since the 'xOpen' callback is now being called for value change and row deletion operations. Previously it was only being called for

Re: [sqlite] Goto's in .explain output

On Wed, Mar 29, 2017 at 9:58 AM, Hick Gunter wrote: > The section at the end, which is executed first, contains the necessary > database locking and schema checking opcodes. These cannot be generated > before the rest of the program has analysed which operations are performed >

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Can you provide an example of the bytecode produced? In the SQLite shell type: .explain explain ; Typical output (with SQLite version 3.7.14.1): asql> .explain asql> explain update mytable set myfield=2 where myconst=7; addr opcode p1p2p3p4 p5 comment

Re: [sqlite] operation unexpectedly applied to both main and temp?

I find the .explain/explain functionality very helpful in clearing up what happens and why. Note the error when creating main.t. Note the database number 1 (for temp) vs. 0 (for main) in the OpenWrite and TableLock instructions. asql> create temp table t (db, val); asql> create table main.t

Re: [sqlite] operation unexpectedly applied to both main and temp?

Ignore the first note. Table main.t is persistent, whrereas temp.t is automatically dropped on closing the connection. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hick Gunter Gesendet: Mittwoch, 29. März 2017 08:40 An:

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Hello, On 2017-03-29 10:48, Hick Gunter wrote: According to the documentation of Virtual Tables and Opcodes: [...] xOpen( table, cursor) is called to announce that SQLite intends to read from a table. A cursor cannot be used to write to a table. Do whatever is necessary to read from the backing

Re: [sqlite] VT table behavior change between 3.10 and 3.17

-Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cezary H. Noweta Gesendet: Mittwoch, 29. März 2017 12:37 An: SQLite mailing list Betreff: Re: [sqlite] VT table behavior change between

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Hello, On 2017-03-29 13:07, Hick Gunter wrote: -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cezary H. Noweta Gesendet: Mittwoch, 29. März 2017 12:37 An: SQLite mailing list

Re: [sqlite] VT table behavior change between 3.10 and 3.17

On Wed, Mar 29, 2017 at 2:33 PM, Cezary H. Noweta wrote: > My questions concerned a connection between your first sentence > (``According to the documentation...'') and the remaining part of your post. Now, I see that you have explained the behavior of SQLite rather then >