Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Simon has the correct idea.   If you have a function x(), you are free
> to define another in the same extension called function
> x_config(). 

Yes, of course. In fact, I mentioned this option already in my original post. 
The syntax for the user will be less intuitive than a pragma statement, but 
since it seems to be impossible to intercept pragma handling, it is most 
probably the easiest solution.  

> This x_config() function is free to change global runtime preference
> variables of the x() function based on the  passed into the last
> call of x_config().   SQLITE_DETERMINISTIC is merely a performance
> hint for expression evaluation within a single statement. 
> Deterministic functions may be called multiple times anyway and every
> distinct statement where output depends on a function, deterministic or
> not, will cause that function to be evaluated at least once. 

The functions in my extension will all be deterministic. The purpose of the 
parameters is to initialize the environment of my extension. The alternative 
would be that the user would pass the parameters to each call of one of the 
extension functions, but this is cumbersome and errorprone.  

> The only problem will be if a thread in your process calls the
> x_config() function while the x() function has a different thread
> context.  If overlapping multithreaded usage is anticipated, global
> configuration variable access must be serialized by the sqlite3 mutex
> API or other critical section mechanism. 

That's a valid point. I should better take care that different threads will not 
use different parameter settings. Thanks.  

Regards,  

Ulrich  


> On Tue, Feb 6, 2018 at 1:24 AM, Ulrich Telle wrote:
> 
> > > Simon Slavin wrote:
> > >
> > > On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> > >
> > > > Another possibility would be to add a user-defined function for the
> > > > configuration of the extension that could be called from a SELECT
> > > > statement:
> > > >
> > > > SELECT myextension_config('param-name', 'param-value');
> > >
> > > I've seen this done before.  Of course it means that your normal
> > function is not deterministic, so you may no longer use
> > SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters
> > are set during compilation.
> >
> > Well, actually my goal is not to have an extension with non-deterministic
> > functions. The parameters have mostly the purpose to initialize the
> > extension (things similar to what you do to SQLite itself with pragmas
> like
> > "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension
> would
> > accept changes to the parameters only before the first invocation of the
> > extension functions.
> >
> > Regards,
> >
> > Ulrich
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] question about covering index

[Simon Slavin]

On 7 Feb 2018, at 1:31am, Mark Wagner  wrote:

> Wow, I had no idea that the order of the columns in the index effects how
> they're used.  Must. Study. More.

Just like a phone directory.  If the order is (surname, firstname) then the 
first name in the directory is the one with the lowest surname, not the lowest 
firstname.  This is why your covering index doesn't make sense.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about threadsafe

[Nick]

>  (a) an error result of some kind or (b) a corrupt database. 
I did not see any info about errmsg.

>  Are your processes using the same database connection or does each one
> have its own ? 
Two processes have two sqlite3_open(). So each one has its own.

>  Are you checking the result codes returned by all the API calls ? 
Yes. I use speedtest1.c as model code. 
  speedtest1_exec("BEGIN");
  speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); --  %d times",
n);
  for(i=1; i<=n; i++){
rc = sqlite3_bind_int64(g.pStmt, 1, i);
rc = sqlite3_bind_int(g.pStmt, 2, i);
rc = sqlite3_bind_text(g.pStmt, 3, "hello", -1, SQLITE_STATIC);
speedtest1_run();
  }
  speedtest1_exec("COMMIT");
And I have checked rc = SQLITE_OK.

>  Can you reliably get less than 2 rows ? 
Yes, always less than 2.
Process A inserts 1-1 and process B inserts 10001-2. I found that
the first few rows is missing in the result. I mean there is no 10001-10xxx.

>  Does the problem go away if you use threadsafe = 2 ? 
The problem is still here.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[petern]

Simon has the correct idea.   If you have a function x(), you are free to
define another in the same extension called function x_config().
This x_config() function is free to change global runtime preference
variables of the x() function based on the  passed into the last call
of x_config().   SQLITE_DETERMINISTIC is merely a performance hint for
expression evaluation within a single statement.  Deterministic functions
may be called multiple times anyway and every distinct statement where
output depends on a function, deterministic or not, will cause that
function to be evaluated at least once.

The only problem will be if a thread in your process calls the x_config()
function while the x() function has a different thread context.  If
overlapping multithreaded usage is anticipated, global configuration
variable access must be serialized by the sqlite3 mutex API or other
critical section mechanism.

Peter

On Tue, Feb 6, 2018 at 1:24 AM, Ulrich Telle  wrote:

> > Simon Slavin wrote:
> >
> > On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> >
> > > Another possibility would be to add a user-defined function for the
> > > configuration of the extension that could be called from a SELECT
> > > statement:
> > >
> > > SELECT myextension_config('param-name', 'param-value');
> >
> > I've seen this done before.  Of course it means that your normal
> function is not deterministic, so you may no longer use
> SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters
> are set during compilation.
>
> Well, actually my goal is not to have an extension with non-deterministic
> functions. The parameters have mostly the purpose to initialize the
> extension (things similar to what you do to SQLite itself with pragmas like
> "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension would
> accept changes to the parameters only before the first invocation of the
> extension functions.
>
> Regards,
>
> Ulrich
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Question about threadsafe

[Nick]

Yep, Hick. We have the same understanding. 
But all I found is that process B did not wait for the lock and began to run
directly.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_expanded_sql is reading freed heap memory

[Richard Hipp]

On 2/6/18, Jens Alfke  wrote:
> I've got a repeatable situation in my library's unit tests wherein the Clang
> Address Sanitizer catches sqlite3_expanded_sql() reading from a freed heap
> block. This is with SQLite 3.22 on MacOS 10.13.3.
>
> The background: I've added some code to my library to log warnings if the
> database is closed (via sqlite3_close_v2) while there are still open
> statements. So just before calling sqlite3_close_v2, I'm using
> sqlite3_next_stmt to iterate them, like so:
>
> sqlite3_stmt *stmt = nullptr;
> while (nullptr != (stmt = sqlite3_next_stmt(mpSQLite, stmt))) {
> callback(sqlite3_expanded_sql(stmt), sqlite3_stmt_busy(stmt));
> }
>
> In one of my unit tests, the call to sqlite3_expanded_sql() results in a
> breakpoint by the address sanitizer where it reports a read of a freed heap
> block, at sqlite3.c:79051:
> for(i=0; i   sqlite3XPrintf(, "%02x", pVar->z[i]&0xff);  // ⟵ 
> here; the
> variable 'i' is 0.
> }
> This is writing a blob value as hex, so it looks like the actual blob data
> was already freed (by fts3SegWriterFree, according to the dump.)
>
> The string already accumulated into `out` is:
>   REPLACE INTO 'main'.'kv_default::byStreet_segdir' VALUES(0,0,0,0,'0
> 1671',x'
> where `kv_default::byStreet` is the name of an FTS4 table.
>
> Let me know if there's more information I can provide.

The line numbers in your ASAN output below seem incorrect.  The
SHA3-256 hash for sqlite3.c version 3.22.0 should be
206df47ebc49cd1710ac0dd716ce5de5854826536993f4feab7a49d136b85069.
What is the hash on the "sqlite3.c" file you are using?  Can you post
the text of the sqlite3.c file that you are using?


>
> —Jens
>
> =
> ==5150==ERROR: AddressSanitizer: heap-use-after-free on address
> 0x621002808d00 at pc 0x0001025ef097 bp 0x7ffeefbfcc30 sp 0x7ffeefbfcc28
> READ of size 1 at 0x621002808d00 thread T0
> #0 0x1025ef096 in sqlite3VdbeExpandSql sqlite3.c:79051
> #1 0x1025edc9f in sqlite3_expanded_sql sqlite3.c:78609
> #2 ...
>
> 0x621002808d00 is located 0 bytes inside of 4064-byte region
> [0x621002808d00,0x621002809ce0)
> freed by thread T0 here:
> #0 0x100caefa4 in __sanitizer_mz_free
> (libclang_rt.asan_osx_dynamic.dylib:x86_64h+0x59fa4)
> #1 0x10287cdcb in sqlite3MemFree sqlite3.c:21428
> #2 0x1025cc9c2 in sqlite3_free sqlite3.c:25151
> #3 0x1028e1cf4 in fts3SegWriterFree sqlite3.c:159856
> #4 0x1028de3b1 in fts3SegmentMerge sqlite3.c:160727
> #5 0x1028fa66e in sqlite3Fts3PendingTermsFlush sqlite3.c:160741
> #6 0x1028a6174 in fts3SyncMethod sqlite3.c:150819
> #7 0x10268a702 in sqlite3VtabSync sqlite3.c:127211
> #8 0x10268553a in vdbeCommit sqlite3.c:74426
> #9 0x102682f80 in sqlite3VdbeHalt sqlite3.c:74890
> #10 0x1026a6aab in sqlite3VdbeExec sqlite3.c:82236
> #11 0x1025e8c41 in sqlite3Step sqlite3.c:77535
> #12 0x1025e7b79 in sqlite3_step sqlite3.c:77598
> #13 0x1025f8c3f in sqlite3_exec sqlite3.c:112187
> #14 ...
>
> previously allocated by thread T0 here:
> #0 0x100caea3c in __sanitizer_mz_malloc
> (libclang_rt.asan_osx_dynamic.dylib:x86_64h+0x59a3c)
> #1 0x7fff716b6200 in malloc_zone_malloc
> (libsystem_malloc.dylib:x86_64+0x2200)
> #2 0x10287cd7a in sqlite3MemMalloc sqlite3.c:21396
> #3 0x102615efb in mallocWithAlarm sqlite3.c:25040
> #4 0x1025cc812 in sqlite3Malloc sqlite3.c:25070
> #5 0x1025cc6a5 in sqlite3_malloc sqlite3.c:25088
> #6 0x1028df1b5 in fts3SegWriterAdd sqlite3.c:159702
> #7 0x1028de046 in fts3SegmentMerge sqlite3.c:160705
> #8 0x1028fa66e in sqlite3Fts3PendingTermsFlush sqlite3.c:160741
> #9 0x1028a6174 in fts3SyncMethod sqlite3.c:150819
> #10 0x10268a702 in sqlite3VtabSync sqlite3.c:127211
> #11 0x10268553a in vdbeCommit sqlite3.c:74426
> #12 0x102682f80 in sqlite3VdbeHalt sqlite3.c:74890
> #13 0x1026a6aab in sqlite3VdbeExec sqlite3.c:82236
> #14 0x1025e8c41 in sqlite3Step sqlite3.c:77535
> #15 0x1025e7b79 in sqlite3_step sqlite3.c:77598
> #16 0x1025f8c3f in sqlite3_exec sqlite3.c:112187
> #17 ...
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] question about covering index

[Keith Medcalf]

Note that if the _id column were not UNIQUE, then the SKIP-SCAN optimization 
might be used with index i if and only if you had (a) done an analyze and (b) 
the optimizer thought it might be worthwhile to do so.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner
>Sent: Tuesday, 6 February, 2018 18:32
>To: SQLite mailing list
>Subject: Re: [sqlite] question about covering index
>
>Wow, I had no idea that the order of the columns in the index effects
>how
>they're used.  Must. Study. More.
>
>On Tue, Feb 6, 2018 at 5:15 PM, Keith Medcalf 
>wrote:
>
>>
>> That said, however, the performance increase will be proportional
>to the
>> number of x values that are selected vs the number of rows in the
>table.
>> Unless the table is many orders of magnitude larger than the number
>of
>> similar x values you are searching for, the table scan will likely
>be
>> faster.  Of course, you will also "pay" the extra index maintenance
>and
>> storage fee's, which may or may not outweigh the increase
>conferred.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-Original Message-
>> >From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>> >Sent: Tuesday, 6 February, 2018 18:07
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] question about covering index
>> >
>> >
>> >Because your fields are backwards?
>> >
>> >x should come before _id (x is a row selector, _id is a grouping
>> >selector), and the y cannot be used to sort (obviously) but can be
>> >used to avoid the table lookup to feed the results into the temp
>b-
>> >tree sorter.
>> >
>> >sqlite> CREATE TABLE foo (_id integer primary key, x, y);
>> >sqlite> CREATE INDEX i on foo(_id, x, y);
>> >sqlite> CREATE INDEX j on foo(x, _id, y);
>> >sqlite> CREATE INDEX k on foo(y, x, _id);
>> >sqlite> .eqp on
>> >sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
>> >--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
>> >--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
>> >sqlite> .eqp full
>> >sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
>> >--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
>> >--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
>> >addr  opcode p1p2p3p4 p5  comment
>> >  -        -  --  
>---
>> >--
>> >0 Init   0 50000  Start at
>50
>> >1 SorterOpen 1 5 0 k(1,B) 00
>> >2 Noop   2 3 000
>> >3 Integer0 5 000  r[5]=0;
>> >clear abort flag
>> >4 Integer0 4 000  r[4]=0;
>> >indicate accumulator empty
>> >5 Null   0 8 800
>> >r[8..8]=NULL
>> >6 Gosub  7 39000
>> >7 OpenRead   3 4 0 k(4)   02  root=4
>> >iDb=0; j
>> >8 Noop   0 0 000  Begin
>> >WHERE-loop0: foo
>> >9 CursorHint 3 0 0 EQ(c0,1)   00
>> >10Integer1 10000  r[10]=1
>> >11SeekGE 3 27101  00
>key=r[10]
>> >12  IdxGT  3 27101  00
>key=r[10]
>> >13  Noop   0 0 000  Begin
>> >WHERE-core
>> >14  IdxRowid   3 9 000
>> >r[9]=rowid
>> >15  Compare8 9 1 k(1,B) 00  r[8]
><->
>> >r[9]
>> >16  Jump   172117   00
>> >17  Move   9 8 100
>r[8]=r[9]
>> >18  Gosub  6 32000  output
>> >one row
>> >19  IfPos  5 41000  if
>r[5]>0
>> >then r[5]-=0, goto 41; check abort flag
>> >20  Gosub  7 39000  reset
>> >accumulator
>> >21  IdxRowid   3 1 000
>> >r[1]=rowid
>> >22  Column 3 0 200
>> >r[2]=foo.x
>> >23  Column 3 2 300
>> >r[3]=foo.y
>> >24  Integer1 4 000
>r[4]=1;
>> >indicate data in accumulator
>> >25  Noop   0 0 000  End
>> >WHERE-core
>> >26Next   3 12000
>> >27Noop   0 0 000  End
>WHERE-
>> >loop0: foo
>> >28Gosub  6 32000  output
>> >final row
>> >29Goto   

Re: [sqlite] question about covering index

[Mark Wagner]

Wow, I had no idea that the order of the columns in the index effects how
they're used.  Must. Study. More.

On Tue, Feb 6, 2018 at 5:15 PM, Keith Medcalf  wrote:

>
> That said, however, the performance increase will be proportional to the
> number of x values that are selected vs the number of rows in the table.
> Unless the table is many orders of magnitude larger than the number of
> similar x values you are searching for, the table scan will likely be
> faster.  Of course, you will also "pay" the extra index maintenance and
> storage fee's, which may or may not outweigh the increase conferred.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
> >Sent: Tuesday, 6 February, 2018 18:07
> >To: SQLite mailing list
> >Subject: Re: [sqlite] question about covering index
> >
> >
> >Because your fields are backwards?
> >
> >x should come before _id (x is a row selector, _id is a grouping
> >selector), and the y cannot be used to sort (obviously) but can be
> >used to avoid the table lookup to feed the results into the temp b-
> >tree sorter.
> >
> >sqlite> CREATE TABLE foo (_id integer primary key, x, y);
> >sqlite> CREATE INDEX i on foo(_id, x, y);
> >sqlite> CREATE INDEX j on foo(x, _id, y);
> >sqlite> CREATE INDEX k on foo(y, x, _id);
> >sqlite> .eqp on
> >sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
> >--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
> >--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
> >sqlite> .eqp full
> >sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
> >--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
> >--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
> >addr  opcode p1p2p3p4 p5  comment
> >  -        -  --  ---
> >--
> >0 Init   0 50000  Start at 50
> >1 SorterOpen 1 5 0 k(1,B) 00
> >2 Noop   2 3 000
> >3 Integer0 5 000  r[5]=0;
> >clear abort flag
> >4 Integer0 4 000  r[4]=0;
> >indicate accumulator empty
> >5 Null   0 8 800
> >r[8..8]=NULL
> >6 Gosub  7 39000
> >7 OpenRead   3 4 0 k(4)   02  root=4
> >iDb=0; j
> >8 Noop   0 0 000  Begin
> >WHERE-loop0: foo
> >9 CursorHint 3 0 0 EQ(c0,1)   00
> >10Integer1 10000  r[10]=1
> >11SeekGE 3 27101  00  key=r[10]
> >12  IdxGT  3 27101  00  key=r[10]
> >13  Noop   0 0 000  Begin
> >WHERE-core
> >14  IdxRowid   3 9 000
> >r[9]=rowid
> >15  Compare8 9 1 k(1,B) 00  r[8] <->
> >r[9]
> >16  Jump   172117   00
> >17  Move   9 8 100  r[8]=r[9]
> >18  Gosub  6 32000  output
> >one row
> >19  IfPos  5 41000  if r[5]>0
> >then r[5]-=0, goto 41; check abort flag
> >20  Gosub  7 39000  reset
> >accumulator
> >21  IdxRowid   3 1 000
> >r[1]=rowid
> >22  Column 3 0 200
> >r[2]=foo.x
> >23  Column 3 2 300
> >r[3]=foo.y
> >24  Integer1 4 000  r[4]=1;
> >indicate data in accumulator
> >25  Noop   0 0 000  End
> >WHERE-core
> >26Next   3 12000
> >27Noop   0 0 000  End WHERE-
> >loop0: foo
> >28Gosub  6 32000  output
> >final row
> >29Goto   0 41000
> >30Integer1 5 000  r[5]=1; set
> >abort flag
> >31Return 6 0 000
> >32IfPos  4 34000  if r[4]>0
> >then r[4]-=0, goto 34; Groupby result generator entry point
> >33Return 6 0 000
> >34Copy   1 12100
> >r[12..13]=r[1..2]
> >35Copy   3 11000  r[11]=r[3]
> >36MakeRecord 113 15   00
> >r[15]=mkrec(r[11..13])
> >37SorterInsert   1 15113  00  key=r[15]
> >38

Re: [sqlite] question about covering index

[Keith Medcalf]

That said, however, the performance increase will be proportional to the number 
of x values that are selected vs the number of rows in the table.  Unless the 
table is many orders of magnitude larger than the number of similar x values 
you are searching for, the table scan will likely be faster.  Of course, you 
will also "pay" the extra index maintenance and storage fee's, which may or may 
not outweigh the increase conferred.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Tuesday, 6 February, 2018 18:07
>To: SQLite mailing list
>Subject: Re: [sqlite] question about covering index
>
>
>Because your fields are backwards?
>
>x should come before _id (x is a row selector, _id is a grouping
>selector), and the y cannot be used to sort (obviously) but can be
>used to avoid the table lookup to feed the results into the temp b-
>tree sorter.
>
>sqlite> CREATE TABLE foo (_id integer primary key, x, y);
>sqlite> CREATE INDEX i on foo(_id, x, y);
>sqlite> CREATE INDEX j on foo(x, _id, y);
>sqlite> CREATE INDEX k on foo(y, x, _id);
>sqlite> .eqp on
>sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
>--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
>--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
>sqlite> .eqp full
>sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
>--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
>--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
>addr  opcode p1p2p3p4 p5  comment
>  -        -  --  ---
>--
>0 Init   0 50000  Start at 50
>1 SorterOpen 1 5 0 k(1,B) 00
>2 Noop   2 3 000
>3 Integer0 5 000  r[5]=0;
>clear abort flag
>4 Integer0 4 000  r[4]=0;
>indicate accumulator empty
>5 Null   0 8 800
>r[8..8]=NULL
>6 Gosub  7 39000
>7 OpenRead   3 4 0 k(4)   02  root=4
>iDb=0; j
>8 Noop   0 0 000  Begin
>WHERE-loop0: foo
>9 CursorHint 3 0 0 EQ(c0,1)   00
>10Integer1 10000  r[10]=1
>11SeekGE 3 27101  00  key=r[10]
>12  IdxGT  3 27101  00  key=r[10]
>13  Noop   0 0 000  Begin
>WHERE-core
>14  IdxRowid   3 9 000
>r[9]=rowid
>15  Compare8 9 1 k(1,B) 00  r[8] <->
>r[9]
>16  Jump   172117   00
>17  Move   9 8 100  r[8]=r[9]
>18  Gosub  6 32000  output
>one row
>19  IfPos  5 41000  if r[5]>0
>then r[5]-=0, goto 41; check abort flag
>20  Gosub  7 39000  reset
>accumulator
>21  IdxRowid   3 1 000
>r[1]=rowid
>22  Column 3 0 200
>r[2]=foo.x
>23  Column 3 2 300
>r[3]=foo.y
>24  Integer1 4 000  r[4]=1;
>indicate data in accumulator
>25  Noop   0 0 000  End
>WHERE-core
>26Next   3 12000
>27Noop   0 0 000  End WHERE-
>loop0: foo
>28Gosub  6 32000  output
>final row
>29Goto   0 41000
>30Integer1 5 000  r[5]=1; set
>abort flag
>31Return 6 0 000
>32IfPos  4 34000  if r[4]>0
>then r[4]-=0, goto 34; Groupby result generator entry point
>33Return 6 0 000
>34Copy   1 12100
>r[12..13]=r[1..2]
>35Copy   3 11000  r[11]=r[3]
>36MakeRecord 113 15   00
>r[15]=mkrec(r[11..13])
>37SorterInsert   1 15113  00  key=r[15]
>38Return 6 0 000  end groupby
>result generator
>39Null   0 1 300
>r[1..3]=NULL
>40Return 7 0 000
>41OpenPseudo 4 16500  5 columns
>in r[16]
>42SorterSort 1 49000
>43  SorterData 1 16  

Re: [sqlite] question about covering index

[Keith Medcalf]

Because your fields are backwards?

x should come before _id (x is a row selector, _id is a grouping selector), and 
the y cannot be used to sort (obviously) but can be used to avoid the table 
lookup to feed the results into the temp b-tree sorter.

sqlite> CREATE TABLE foo (_id integer primary key, x, y);
sqlite> CREATE INDEX i on foo(_id, x, y);
sqlite> CREATE INDEX j on foo(x, _id, y);
sqlite> CREATE INDEX k on foo(y, x, _id);
sqlite> .eqp on
sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
sqlite> .eqp full
sqlite> SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER BY y;
--EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX j (x=?)
--EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 50000  Start at 50
1 SorterOpen 1 5 0 k(1,B) 00
2 Noop   2 3 000
3 Integer0 5 000  r[5]=0; clear abort 
flag
4 Integer0 4 000  r[4]=0; indicate 
accumulator empty
5 Null   0 8 800  r[8..8]=NULL
6 Gosub  7 39000
7 OpenRead   3 4 0 k(4)   02  root=4 iDb=0; j
8 Noop   0 0 000  Begin WHERE-loop0: foo
9 CursorHint 3 0 0 EQ(c0,1)   00
10Integer1 10000  r[10]=1
11SeekGE 3 27101  00  key=r[10]
12  IdxGT  3 27101  00  key=r[10]
13  Noop   0 0 000  Begin WHERE-core
14  IdxRowid   3 9 000  r[9]=rowid
15  Compare8 9 1 k(1,B) 00  r[8] <-> r[9]
16  Jump   172117   00
17  Move   9 8 100  r[8]=r[9]
18  Gosub  6 32000  output one row
19  IfPos  5 41000  if r[5]>0 then 
r[5]-=0, goto 41; check abort flag
20  Gosub  7 39000  reset accumulator
21  IdxRowid   3 1 000  r[1]=rowid
22  Column 3 0 200  r[2]=foo.x
23  Column 3 2 300  r[3]=foo.y
24  Integer1 4 000  r[4]=1; indicate 
data in accumulator
25  Noop   0 0 000  End WHERE-core
26Next   3 12000
27Noop   0 0 000  End WHERE-loop0: foo
28Gosub  6 32000  output final row
29Goto   0 41000
30Integer1 5 000  r[5]=1; set abort flag
31Return 6 0 000
32IfPos  4 34000  if r[4]>0 then 
r[4]-=0, goto 34; Groupby result generator entry point
33Return 6 0 000
34Copy   1 12100  r[12..13]=r[1..2]
35Copy   3 11000  r[11]=r[3]
36MakeRecord 113 15   00  r[15]=mkrec(r[11..13])
37SorterInsert   1 15113  00  key=r[15]
38Return 6 0 000  end groupby result 
generator
39Null   0 1 300  r[1..3]=NULL
40Return 7 0 000
41OpenPseudo 4 16500  5 columns in r[16]
42SorterSort 1 49000
43  SorterData 1 16400  r[16]=data
44  Column 4 0 14   00  r[14]=y
45  Column 4 2 13   00  r[13]=x
46  Column 4 1 12   00  r[12]=_id
47  ResultRow  123 000  output=r[12..14]
48SorterNext 1 43000
49Halt   0 0 000
50Transaction0 0 4 0  01  usesStmtJournal=0
51Goto   0 1 000
s


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner
>Sent: Tuesday, 6 February, 2018 17:44
>To: SQLite mailing list

Re: [sqlite] question about covering index

[Mark Wagner]

OK, I oversimplified trying to make it easier.

The real query has a join so I'm aggregating some of the columns.  But this
test case seemed to show the issue.  I could show something closer to what
I'm really doing if that explanation isn't sufficient.



On Tue, Feb 6, 2018 at 4:48 PM, Simon Slavin  wrote:

> On 7 Feb 2018, at 12:43am, Mark Wagner  wrote:
>
> > CREATE TABLE foo (_id integer primary key, x, y);
> > CREATE INDEX i on foo(_id, x, y);
> >
> > And the following query
> >
> > sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER
> > BY y;
>
> Why are you grouping on the primary key ?  Primary key values must be, by
> definition, unique.  Grouping by a unique value means every group has one
> entry.
>
> There's a similar problem with the index you created.  Since the primary
> key is first, there's no point in having the x and y in the index.
> Therefore there's no point in having the index since it just duplicates the
> primary key index for the table.
>
> I suspect that SQLite is acting weird because you fed it with weird things.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] question about covering index

[Simon Slavin]

On 7 Feb 2018, at 12:43am, Mark Wagner  wrote:

> CREATE TABLE foo (_id integer primary key, x, y);
> CREATE INDEX i on foo(_id, x, y);
> 
> And the following query
> 
> sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER
> BY y;

Why are you grouping on the primary key ?  Primary key values must be, by 
definition, unique.  Grouping by a unique value means every group has one entry.

There's a similar problem with the index you created.  Since the primary key is 
first, there's no point in having the x and y in the index.  Therefore there's 
no point in having the index since it just duplicates the primary key index for 
the table.

I suspect that SQLite is acting weird because you fed it with weird things.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] question about covering index

[Mark Wagner]

Given the following schema:

CREATE TABLE foo (_id integer primary key, x, y);
CREATE INDEX i on foo(_id, x, y);

And the following query

sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE x=1 GROUP BY _id ORDER
BY y;

I would have expected it (hoped?) that it would use the covering index for
the order by.  Any clue why it doesn't or what I could do differently to
get it to use an index for the selection, the grouping, and the ordering?

selectid = 0
   order = 0
from = 0
  detail = SCAN TABLE foo

selectid = 0
   order = 0
from = 0
  detail = USE TEMP B-TREE FOR ORDER BY
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I encounter a problem when build sqlite for iOS

[Jens Alfke]

If building for an Apple platform, it's much easier to just link with 
libSQLite3.dylib, as most apps do. It'll shrink your app binary by about 1MB 
too.

—Jens


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[Jens Alfke]

> On Feb 1, 2018, at 10:57 AM, Chris Green  wrote:
> 
> But systems have language variables which tell which set to use.

Your code that runs the query can use those system APIs to localize the day 
names. Just have the query return the raw date strings (or timestamps or 
whatever) and do the fancy manipulation in code.

Or you could write an extension function that looks up the localized day name, 
and call that function in your query.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_close_v2 leading to database corruption

[Jens Alfke]

> On Feb 6, 2018, at 4:13 PM, Simon Slavin  wrote:
> 
> Before you call sqlite3_close_v2(), do this:
>  >
> and deal with any problems.

Actually, I already do that, to log info about the open statements (see my 
earlier email today.)

But that doesn't directly help. This shows me the sqlite3_stmt pointers, but 
not the C++ objects of mine that own them. I'd need to find those and null out 
the statement pointers in them, which means I'd need a data structure that maps 
from a sqlite3_stmt to my object. This is basically the option (a) I sketched 
out. 

There's the additional problem that sometimes there are open statements that 
don't belong to me, like the one in my earlier email that belongs to the FTS4 
extension.

My current plan is to call sqlite3_close() first, then if it fails with 
SQLITE_BUSY I will call sqlite3_db_config() to enable 
SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, and then call sqlite3_close_v2(). From my 
reading of sqlite3PagerClose() and sqlite3WalClose(), that config flag should 
prevent the WAL from being deleted when the zombie db closes.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_close_v2 leading to database corruption

[Simon Slavin]

Before you call sqlite3_close_v2(), do this:



and deal with any problems.

But actually I think you'll somehow have to force the JVM to run finalizers on 
the relevant objects.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_close_v2 leading to database corruption

[Jens Alfke]

We've been trying to figure out a database corruption issue for a week, and 
think we've got it figured out. It involves the "zombie database handle" state 
induced by sqlite3_close_v2:

> If sqlite3_close_v2() is called on a database connection that still has 
> outstanding prepared statements, BLOB handles, and/orsqlite3_backup objects 
> then it returns SQLITE_OK and the deallocation of resources is deferred until 
> all prepared statements, BLOB handles, and sqlite3_backup objects are also 
> destroyed.

In general this is useful behavior for us, as using Java bindings to our API 
can result in statement handles hanging around longer than they ought to until 
the JVM runs finalizers. With sqlite3_close_v2, closing the database doesn't 
fail in these circumstances. (Which is, I believe, why this function was added 
to SQLite in the first place.)

The situation where things go wrong is:
1. Database handle A is opened
2. A compiled statement is created and run. There's a Java object abstracting 
this sqlite3_stmt.
3. The database is closed with sqlite3_close_v2. The statement is still alive, 
so the database handle secretly remains open in "zombie mode".
4. We delete the database's parent directory and all files in it.
5. We create a new directory (with the same path as the old one) and call 
sqlite3_open_v2 to create a new empty database with handle B.
6. The JVM gets around to running finalizers, causing the sqlite3_stmt to be 
freed, which makes the zombie handle closable.
7. The internal function sqlite3LeaveMutexAndCloseZombie() closes the file 
handle … and also deletes the -wal and -shm files.

Now we're in trouble: the -wal and -shm files that just got deleted happen to 
belong to the *newly created* database, not the old one, so SQLite just nuked a 
live WAL. At this point the next SQLite call tends to return error 522, "disk 
I/O error", and the new db is unreadable.

This scenario isn't covered in "How To Corrupt An SQLite Database File", 
although what sqlite3LeaveMutexAndCloseZombie() does is similar to sections 1.3 
or 2.4.

This sequence of events may look weird at this low level, but at a higher level 
they're not uncommon, especially in our own unit tests. The test suite is using 
our API to create a database (which we bundle in a directory), run a test on 
it, close it, then delete it. After that, the _next_ unit test does exactly the 
same thing, just with a different set of test functions. So the same database 
path ends up getting opened, closed, deleted, reopened over and over. (This 
pattern happens in real use too, not just in testing.)

At the moment I'm not sure what to do about this. The two options seem to be
(a) Stop using sqlite3_close_v2. This means we have to implement our own 
mechanism to forcibly close all open statements when requested to close a 
database, and clear the statement handles in the objects holding them.
(b) Avoid ever reusing the same path. This means we'd have to give the database 
file inside the wrapper directory a different name every time — instead of just 
"db.sqlite3" it'd include a UUID or timestamp or something. That of course 
complicates opening a database, necessitating a directory traversal to discover 
the file in the directory.

Anyone else run into this? Anyone agree it should be added to "How To Corrupt"? 
I don't know if it can be fixed inside SQLite...

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Microsoft.Data.SQLite was Vetting SQLite

[Drago, William @ CSG - NARDA-MITEQ]

> Microsoft's own .NET library is Microsoft.Data.SQLite but not all of 
> Microsoft's
> own tools use it since it is part of a long dependency chain which makes
> compiled apps rather large.
>
> Simon.
>

That's very interesting, thanks.

I couldn't find out much about it, though. I can't even tell what version of 
SQLite it uses. Nice to know it exists.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_expanded_sql is reading freed heap memory

[J Decker]

 can you use sqlite3_sql  instead; it won't be complete information... but
ya, the time that expanded_sql is valid is really only while the bound
parameters are still valid.   (could re-bind parameters when done I guess)


On Tue, Feb 6, 2018 at 1:07 PM, Jens Alfke  wrote:

> I've got a repeatable situation in my library's unit tests wherein the
> Clang Address Sanitizer catches sqlite3_expanded_sql() reading from a freed
> heap block. This is with SQLite 3.22 on MacOS 10.13.3.
>
> The background: I've added some code to my library to log warnings if the
> database is closed (via sqlite3_close_v2) while there are still open
> statements. So just before calling sqlite3_close_v2, I'm using
> sqlite3_next_stmt to iterate them, like so:
>
> sqlite3_stmt *stmt = nullptr;
> while (nullptr != (stmt = sqlite3_next_stmt(mpSQLite, stmt))) {
> callback(sqlite3_expanded_sql(stmt), sqlite3_stmt_busy(stmt));
> }
>
> In one of my unit tests, the call to sqlite3_expanded_sql() results in a
> breakpoint by the address sanitizer where it reports a read of a freed heap
> block, at sqlite3.c:79051:
> for(i=0; i   sqlite3XPrintf(, "%02x", pVar->z[i]&0xff);//
> ⟵ here; the variable 'i' is 0.
> }
> This is writing a blob value as hex, so it looks like the actual blob data
> was already freed (by fts3SegWriterFree, according to the dump.)
>
> The string already accumulated into `out` is:
> REPLACE INTO 'main'.'kv_default::byStreet_segdir'
> VALUES(0,0,0,0,'0 1671',x'
> where `kv_default::byStreet` is the name of an FTS4 table.
>
> Let me know if there's more information I can provide.
>
> —Jens
>
> =
> ==5150==ERROR: AddressSanitizer: heap-use-after-free on address
> 0x621002808d00 at pc 0x0001025ef097 bp 0x7ffeefbfcc30 sp 0x7ffeefbfcc28
> READ of size 1 at 0x621002808d00 thread T0
> #0 0x1025ef096 in sqlite3VdbeExpandSql sqlite3.c:79051
> #1 0x1025edc9f in sqlite3_expanded_sql sqlite3.c:78609
> #2 ...
>
> 0x621002808d00 is located 0 bytes inside of 4064-byte region
> [0x621002808d00,0x621002809ce0)
> freed by thread T0 here:
> #0 0x100caefa4 in __sanitizer_mz_free (libclang_rt.asan_osx_dynamic.
> dylib:x86_64h+0x59fa4)
> #1 0x10287cdcb in sqlite3MemFree sqlite3.c:21428
> #2 0x1025cc9c2 in sqlite3_free sqlite3.c:25151
> #3 0x1028e1cf4 in fts3SegWriterFree sqlite3.c:159856
> #4 0x1028de3b1 in fts3SegmentMerge sqlite3.c:160727
> #5 0x1028fa66e in sqlite3Fts3PendingTermsFlush sqlite3.c:160741
> #6 0x1028a6174 in fts3SyncMethod sqlite3.c:150819
> #7 0x10268a702 in sqlite3VtabSync sqlite3.c:127211
> #8 0x10268553a in vdbeCommit sqlite3.c:74426
> #9 0x102682f80 in sqlite3VdbeHalt sqlite3.c:74890
> #10 0x1026a6aab in sqlite3VdbeExec sqlite3.c:82236
> #11 0x1025e8c41 in sqlite3Step sqlite3.c:77535
> #12 0x1025e7b79 in sqlite3_step sqlite3.c:77598
> #13 0x1025f8c3f in sqlite3_exec sqlite3.c:112187
> #14 ...
>
> previously allocated by thread T0 here:
> #0 0x100caea3c in __sanitizer_mz_malloc (libclang_rt.asan_osx_dynamic.
> dylib:x86_64h+0x59a3c)
> #1 0x7fff716b6200 in malloc_zone_malloc (libsystem_malloc.dylib:x86_
> 64+0x2200)
> #2 0x10287cd7a in sqlite3MemMalloc sqlite3.c:21396
> #3 0x102615efb in mallocWithAlarm sqlite3.c:25040
> #4 0x1025cc812 in sqlite3Malloc sqlite3.c:25070
> #5 0x1025cc6a5 in sqlite3_malloc sqlite3.c:25088
> #6 0x1028df1b5 in fts3SegWriterAdd sqlite3.c:159702
> #7 0x1028de046 in fts3SegmentMerge sqlite3.c:160705
> #8 0x1028fa66e in sqlite3Fts3PendingTermsFlush sqlite3.c:160741
> #9 0x1028a6174 in fts3SyncMethod sqlite3.c:150819
> #10 0x10268a702 in sqlite3VtabSync sqlite3.c:127211
> #11 0x10268553a in vdbeCommit sqlite3.c:74426
> #12 0x102682f80 in sqlite3VdbeHalt sqlite3.c:74890
> #13 0x1026a6aab in sqlite3VdbeExec sqlite3.c:82236
> #14 0x1025e8c41 in sqlite3Step sqlite3.c:77535
> #15 0x1025e7b79 in sqlite3_step sqlite3.c:77598
> #16 0x1025f8c3f in sqlite3_exec sqlite3.c:112187
> #17 ...
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_expanded_sql is reading freed heap memory

[Jens Alfke]

I've got a repeatable situation in my library's unit tests wherein the Clang 
Address Sanitizer catches sqlite3_expanded_sql() reading from a freed heap 
block. This is with SQLite 3.22 on MacOS 10.13.3.

The background: I've added some code to my library to log warnings if the 
database is closed (via sqlite3_close_v2) while there are still open 
statements. So just before calling sqlite3_close_v2, I'm using 
sqlite3_next_stmt to iterate them, like so:

sqlite3_stmt *stmt = nullptr;
while (nullptr != (stmt = sqlite3_next_stmt(mpSQLite, stmt))) {
callback(sqlite3_expanded_sql(stmt), sqlite3_stmt_busy(stmt));
}

In one of my unit tests, the call to sqlite3_expanded_sql() results in a 
breakpoint by the address sanitizer where it reports a read of a freed heap 
block, at sqlite3.c:79051:
for(i=0; iz[i]&0xff);// ⟵ 
here; the variable 'i' is 0.
}
This is writing a blob value as hex, so it looks like the actual blob data was 
already freed (by fts3SegWriterFree, according to the dump.)

The string already accumulated into `out` is:
REPLACE INTO 'main'.'kv_default::byStreet_segdir' VALUES(0,0,0,0,'0 
1671',x'
where `kv_default::byStreet` is the name of an FTS4 table.

Let me know if there's more information I can provide.

—Jens

=
==5150==ERROR: AddressSanitizer: heap-use-after-free on address 0x621002808d00 
at pc 0x0001025ef097 bp 0x7ffeefbfcc30 sp 0x7ffeefbfcc28
READ of size 1 at 0x621002808d00 thread T0
#0 0x1025ef096 in sqlite3VdbeExpandSql sqlite3.c:79051
#1 0x1025edc9f in sqlite3_expanded_sql sqlite3.c:78609
#2 ...

0x621002808d00 is located 0 bytes inside of 4064-byte region 
[0x621002808d00,0x621002809ce0)
freed by thread T0 here:
#0 0x100caefa4 in __sanitizer_mz_free 
(libclang_rt.asan_osx_dynamic.dylib:x86_64h+0x59fa4)
#1 0x10287cdcb in sqlite3MemFree sqlite3.c:21428
#2 0x1025cc9c2 in sqlite3_free sqlite3.c:25151
#3 0x1028e1cf4 in fts3SegWriterFree sqlite3.c:159856
#4 0x1028de3b1 in fts3SegmentMerge sqlite3.c:160727
#5 0x1028fa66e in sqlite3Fts3PendingTermsFlush sqlite3.c:160741
#6 0x1028a6174 in fts3SyncMethod sqlite3.c:150819
#7 0x10268a702 in sqlite3VtabSync sqlite3.c:127211
#8 0x10268553a in vdbeCommit sqlite3.c:74426
#9 0x102682f80 in sqlite3VdbeHalt sqlite3.c:74890
#10 0x1026a6aab in sqlite3VdbeExec sqlite3.c:82236
#11 0x1025e8c41 in sqlite3Step sqlite3.c:77535
#12 0x1025e7b79 in sqlite3_step sqlite3.c:77598
#13 0x1025f8c3f in sqlite3_exec sqlite3.c:112187
#14 ...

previously allocated by thread T0 here:
#0 0x100caea3c in __sanitizer_mz_malloc 
(libclang_rt.asan_osx_dynamic.dylib:x86_64h+0x59a3c)
#1 0x7fff716b6200 in malloc_zone_malloc 
(libsystem_malloc.dylib:x86_64+0x2200)
#2 0x10287cd7a in sqlite3MemMalloc sqlite3.c:21396
#3 0x102615efb in mallocWithAlarm sqlite3.c:25040
#4 0x1025cc812 in sqlite3Malloc sqlite3.c:25070
#5 0x1025cc6a5 in sqlite3_malloc sqlite3.c:25088
#6 0x1028df1b5 in fts3SegWriterAdd sqlite3.c:159702
#7 0x1028de046 in fts3SegmentMerge sqlite3.c:160705
#8 0x1028fa66e in sqlite3Fts3PendingTermsFlush sqlite3.c:160741
#9 0x1028a6174 in fts3SyncMethod sqlite3.c:150819
#10 0x10268a702 in sqlite3VtabSync sqlite3.c:127211
#11 0x10268553a in vdbeCommit sqlite3.c:74426
#12 0x102682f80 in sqlite3VdbeHalt sqlite3.c:74890
#13 0x1026a6aab in sqlite3VdbeExec sqlite3.c:82236
#14 0x1025e8c41 in sqlite3Step sqlite3.c:77535
#15 0x1025e7b79 in sqlite3_step sqlite3.c:77598
#16 0x1025f8c3f in sqlite3_exec sqlite3.c:112187
#17 ...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header corruption

[Dan Kennedy]

On 02/06/2018 11:57 PM, Deon Brewis wrote:

I’m trying to track down SQLITE corruptions that seems to corrupt our databases 
in a consistent way. (Running on 3.20.1).

This isn’t related to a crash or hardware failure. The app is running and 
reading and writing to the database fine, and then suddenly we start getting a 
SQLITE_NOTADB errors. The first error we got was on trying to execute a ‘BEGIN 
TRANSACTION’, and everything thereafter started failing.

When our database headers are fine, I see this:
h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3.
0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@  ...›..4B

On corruption #1, I see this:
h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ;  .‘!…D.,í¾!ú
0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; žtçêš..,™°·;Æð5û

On corruption #2, I see this:
h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ;  ..˱ÿœ.ÐÖ»"
0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; .dÓôÝ8ûߣàG¸×ö!¼


This bit pattern of “15 03 01 00” for the first 4 characters where it should be 
“SQLi” is a bit too much of a coincidence to ignore or write off as disk error.

The rest of the header and file after the first 25-ish bytes or so, seem fine. 
It’s just these first 25  bytes that gets corrupted. If I restore Offsets 0 to 
Offsets 27 out of a “working” database, I get a usable file.

Under what circumstances would SQLITE even attempt to re-write the header 
string?


Is this on UNIX?

Quite often this sort of thing occurs because some other module is 
writing to a file-descriptor owned by SQLite. Usually because it closed 
its own fd, then SQLite opened the db file and was assigned the same 
integer fd value, then the rogue module wrote to the fd anyway. In other 
words, some other module is doing:


  close(fd);
  write(fd, "1503010020...", 25);

and between those two calls SQLite is calling open() and is being 
assigned a file-descriptor with the same integer value as fd.


Dan.







- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header corruption

[David Raymond]

Things stored in the first 25 bytes include page size, WAL status, and the file 
change counter. So at least part of the header there gets changed with every 
committed write transaction.

http://www.sqlite.org/fileformat2.html


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Deon Brewis
Sent: Tuesday, February 06, 2018 11:57 AM
To: SQLite mailing list
Subject: [sqlite] Header corruption

I’m trying to track down SQLITE corruptions that seems to corrupt our databases 
in a consistent way. (Running on 3.20.1).

This isn’t related to a crash or hardware failure. The app is running and 
reading and writing to the database fine, and then suddenly we start getting a 
SQLITE_NOTADB errors. The first error we got was on trying to execute a ‘BEGIN 
TRANSACTION’, and everything thereafter started failing.

When our database headers are fine, I see this:
h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3.
0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@  ...›..4B

On corruption #1, I see this:
h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ;  .‘!…D.,í¾!ú
0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; žtçêš..,™°·;Æð5û

On corruption #2, I see this:
h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ;  ..˱ÿœ.ÐÖ»"
0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; .dÓôÝ8ûߣàG¸×ö!¼


This bit pattern of “15 03 01 00” for the first 4 characters where it should be 
“SQLi” is a bit too much of a coincidence to ignore or write off as disk error.

The rest of the header and file after the first 25-ish bytes or so, seem fine. 
It’s just these first 25  bytes that gets corrupted. If I restore Offsets 0 to 
Offsets 27 out of a “working” database, I get a usable file.

Under what circumstances would SQLITE even attempt to re-write the header 
string?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Header corruption

[Deon Brewis]

I’m trying to track down SQLITE corruptions that seems to corrupt our databases 
in a consistent way. (Running on 3.20.1).

This isn’t related to a crash or hardware failure. The app is running and 
reading and writing to the database fine, and then suddenly we start getting a 
SQLITE_NOTADB errors. The first error we got was on trying to execute a ‘BEGIN 
TRANSACTION’, and everything thereafter started failing.

When our database headers are fine, I see this:
h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3.
0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@  ...›..4B

On corruption #1, I see this:
h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ;  .‘!…D.,í¾!ú
0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; žtçêš..,™°·;Æð5û

On corruption #2, I see this:
h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ;  ..˱ÿœ.ÐÖ»"
0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; .dÓôÝ8ûߣàG¸×ö!¼


This bit pattern of “15 03 01 00” for the first 4 characters where it should be 
“SQLi” is a bit too much of a coincidence to ignore or write off as disk error.

The rest of the header and file after the first 25-ish bytes or so, seem fine. 
It’s just these first 25  bytes that gets corrupted. If I restore Offsets 0 to 
Offsets 27 out of a “working” database, I get a usable file.

Under what circumstances would SQLITE even attempt to re-write the header 
string?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about threadsafe

[x]

Don’t suppose you used ‘INSERT IGNORE’ and the inserts contained duplicate keys?

From: Nick
Sent: 06 February 2018 11:52
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Question about threadsafe

I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL,
threadsafe=1.

My understanding is that:
WAL => readers and only one writer can run at the same time.
threadsafe=1 => mutex is used in serialized mode so that two writers is
supported.
Is it correct?

But I ran a simple test:
Two processes will run sqlite3_open() respectively to open the same db. Then
both of the two processes will insert 1 records(in Transaction) into the
db simultaneously.
But I find that:

Process A begin
Process A insert
Process B begin
Process B insert
Process A end
Process B end

Which I guess the Process B did not sleep at all?
And the count of records is less than 2 at last.

So I think multiple write threads is not allowed even though I use
threadsafe=1 in wal mode?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about threadsafe

[Simon Slavin]

On 6 Feb 2018, at 11:52am, Nick  wrote:

> But I ran a simple test:
> Two processes will run sqlite3_open() respectively to open the same db. Then
> both of the two processes will insert 1 records(in Transaction) into the
> db simultaneously. 
> But I find that:
> 
> Process A begin
> Process A insert
>Process B begin
>Process B insert
> Process A end
>Process B end
> 
> Which I guess the Process B did not sleep at all?
> And the count of records is less than 2 at last.

You should not be able to get less than 2 rows without either (a) an error 
result of some kind or (b) a corrupt database.

Are your processes using the same database connection or does each one have its 
own ?

Are you checking the result codes returned by all the API calls ?

Can you reliably get less than 2 rows ?

Does the problem go away if you use threadsafe = 2 ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Question about threadsafe

[Hick Gunter]

More than one concurrent writer is not supported. WAL mode only allows readers 
to see the state of the db as it was at the start of their transaction while 
writers' changes are written to the WAL file.

Threadsafe refers to the interoperation of multiple threads within a single 
process. Single thread means that only one thread of a process can use SQLite 
at any time. Multithread means that several threads within a single proces may 
cal SQLite, but only one thread at a time is allowed per connection. Serilaized 
means that any thread can call SQLite for any connection at any time, but the 
second caller will block until the first one returns from the library.

What you tested is multiple processes accessing the same db file. This uses the 
normal locking protocol. The first process to establish a write lock will 
proceed with its work until the transaction ends (commit or rollback), 
releasing the lock that the second process was waiting for. Only then will the 
second process be able to continue.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nick
Gesendet: Dienstag, 06. Februar 2018 12:52
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Question about threadsafe

I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL, 
threadsafe=1.

My understanding is that:
WAL => readers and only one writer can run at the same time.
threadsafe=1 => mutex is used in serialized mode so that two writers is 
supported.
Is it correct?

But I ran a simple test:
Two processes will run sqlite3_open() respectively to open the same db. Then 
both of the two processes will insert 1 records(in Transaction) into the db 
simultaneously.
But I find that:

Process A begin
Process A insert
Process B begin
Process B insert
Process A end
Process B end

Which I guess the Process B did not sleep at all?
And the count of records is less than 2 at last.

So I think multiple write threads is not allowed even though I use
threadsafe=1 in wal mode?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about threadsafe

[Nick]

I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL,
threadsafe=1.

My understanding is that:
WAL => readers and only one writer can run at the same time.
threadsafe=1 => mutex is used in serialized mode so that two writers is
supported.
Is it correct?

But I ran a simple test:
Two processes will run sqlite3_open() respectively to open the same db. Then
both of the two processes will insert 1 records(in Transaction) into the
db simultaneously. 
But I find that:

Process A begin
Process A insert
Process B begin
Process B insert
Process A end
Process B end

Which I guess the Process B did not sleep at all?
And the count of records is less than 2 at last.

So I think multiple write threads is not allowed even though I use
threadsafe=1 in wal mode?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

Dominique Devienne wrote:
>
> On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle  wrote:
> 
> > > An alternative is to expose a virtual table with a fixed set of rows, and
> > > accepting updates on the values, which can also then be "typed" too.
> > > But that's a lot more complicated though.
> > > (and refusing inserts/deletes too, of course).
> > >
> > > That vtable could also expose version information for the extension, for
> > > example, and those would be read-only. Just thinking aloud.
> > > Avoids non-deterministic functions.
> >
> > A vtable with a fixed number of rows, one for each config parameter - this
> > approach sounds interesting.
> > I'll have to investigate how complicated it will be to implement such an
> > approach.
> >
> 
> This approach could IMHO be one of the contributed vtable impls in ext/misc
> [1] to be reused by other loadable extension authors, and could become the
> "semi official" way to solve that problem, lacking extension specific
> pragmas that is. My $0.02c. --DD

In case I'll implement the vtable approach, I might consider to make it 
available.

> PS: There's also always environment variables, especially for 1-time at
> startup settings.
>   My main beef against env.vars. though is that they are not discoverable
> and often hidden.

For my purpose environment variables are not suitable. The user should be able 
to alter the configuration parameters for each database connection.

Regards,

Ulrich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Hick Gunter wrote:
>
> You can pass parameters to the xCreate function in the CREATE VIRTUAL TABLE 
> statement, if the setting you desire remains unchanged during the lifetime of 
> the table.
> 
> CREATE VIRTUAL TABLE  USING  [ ( ,...) ];
> 
> You can declare hidden fields in the call to sqlite3_declare_vtab() call 
> within your xCreate function if the setting you desire are specific to a 
> query. The constraint will be passed to your xBestIndex function, and (if the 
> query plan is selected) the value will be passed to your xFilter function.
> 
> CREATE VIRTUAL TABLE with_foo USING handle_foo;
> 
> Sqlite3_declare_vtab(db_hanlde, "CREATE TABLE x ( ..., foo integer hidden, 
> ...);");
> 
> SELECT  FROM with_foo wf  WHERE wf.foo = 'bar';

I have to admit that I don't have much experience with the vtable concept. My 
extensions consist of a set of functions that can be used in SQL statements. 
During a single database connection the behaviour of the functions will be 
deterministic, but the user should be able to set certain initialization 
parameters.

Dominique Devienne proposed in his answer to implement a vtable with a fixed 
set of rows for the configuration parameters. That approach seems to be a bit 
simpler to implement than a fully fledged vtable solution.

> Or, for "none of the above", create a user defined function that will handle 
> storing/retrieving the settings and provide a C interface for your virtual 
> table implementation to access them directly
> 
> SELECT param('foo','bar') AS p;
> p
> --
> NULL
> 
> SELECT param('foo') AS foo;
> foo
> -
> bar

Yes, that's the approach I already mentioned in my original post. Adding a C 
interface is probably a good idea, too. However, setting parameters should be 
possible without calling a C interface function, for example, if a user loads 
the extension from the default SQLite shell coming with the SQLite distribution.

I would have preferred a more PRAGMA like syntax, but that could probably be 
called "syntactic sugar" - a SELECT with a user-defined function will work and 
the syntax is simple enough.

Regards,

Ulrich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[Dominique Devienne]

On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle  wrote:

> > An alternative is to expose a virtual table with a fixed set of rows, and
> > accepting updates on the values, which can also then be "typed" too.
> > But that's a lot more complicated though.
> > (and refusing inserts/deletes too, of course).
> >
> > That vtable could also expose version information for the extension, for
> > example, and those would be read-only. Just thinking aloud.
> > Avoids non-deterministic functions.
>
> A vtable with a fixed number of rows, one for each config parameter - this
> approach sounds interesting.
> I'll have to investigate how complicated it will be to implement such an
> approach.
>

This approach could IMHO be one of the contributed vtable impls in ext/misc
[1]
to be reused by other loadable extension authors, and could become the
"semi official"
way to solve that problem, lacking extension specific pragmas that is. My
$0.02c. --DD

PS: There's also always environment variables, especially for 1-time at
startup settings.
  My main beef against env.vars. though is that they are not discoverable
and often hidden.

[1] https://www.sqlite.org/src/tree?name=ext/misc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Dominique Devienne wrote:
> 
> An alternative is to expose a virtual table with a fixed set of rows, and
> accepting updates on the values, which can also then be "typed" too. 
> But that's a lot more complicated though.
> (and refusing inserts/deletes too, of course).
> 
> That vtable could also expose version information for the extension, for
> example, and those would be read-only. Just thinking aloud.
> Avoids non-deterministic functions.

A vtable with a fixed number of rows, one for each config parameter - this 
approach sounds interesting.

I'll have to investigate how complicated it will be to implement such an 
approach.

Regards,

Ulrich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[Olivier Mascia]

> Le 6 févr. 2018 à 00:30, Simon Slavin  a écrit :
> 
>> You know that every copy of Windows comes with SQLite preinstalled,
>> right?  C:\Windows\System32\winsqlite3.dll
> 
> And SQLite is used internally in several parts of Microsoft Office.  For 
> example, Outlook's database in Mac Office 365 is a SQLite database[1].

Visual Studio 2017 (it started with Visual Studio 2015 and was an opt-in with 
the next before release) uses it too. Have a look in the (hidden-attributed) 
folder .vs where you have .sln files...

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Auto Index Warnings; key on deterministic functions

[Dominique Devienne]

On Tue, Feb 6, 2018 at 2:24 AM, J Decker  wrote:

> create table tableA ( pk PRIMARY KEY, dataA )
> create table tableB ( fk, dataB,

  FOREIGN KEY (fk) REFERENCES tableA(pk)

  ON DELETE CASCADE )
>
> if the table was also ON UPDATE CASCADE could it slave to the same index
> as primary key?
>

An index relates a value to a rowid in the associated table (or a set of
rowids if non-unique index).
So the index behind the tableA.pk column maps pk-values to tableA rowids.
While the automatic index on tableB.fk relates to tableB rowids (and is
non-unique too).
Sure the fk values are necessarily a subset of the pk values (*) for both
indexes,
but obviously the rowids are completely different, since from different
tables.
So both indexes serve different purposes, and neither can "slave" for each
other.

But perhaps I didn't understand your question correctly? Hopefully that's
useful. --DD

PS: You want an "explicit" index on tableB.fk in any case, since all FKs
should be indexed in general.
  Otherwise all your ON DELETE, ON UPDATE clauses would yields full scans
when changes
  in the parent table (tableA here) need to be propagated to child tables.
(it needs to find the child rows
  to delete or update, based on the affected tableA.pk values)

(*) if FKs are on, which they are not by default...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[Simon Slavin]

On 6 Feb 2018, at 9:24am, Ulrich Telle  wrote:

> Well, actually my goal is not to have an extension with non-deterministic 
> functions. The parameters have mostly the purpose to initialize the extension 
> (things similar to what you do to SQLite itself with pragmas like "PRAGMA 
> cache_size", or "PRAGMA data_store_directory"). The extension would accept 
> changes to the parameters only before the first invocation of the extension 
> functions.

Well, you seem to know what you're doing.  So you could follow your proposed 
plan and mark the function as deterministic even though you know that you could 
abuse it by changing its parameters.  This seems to be simpler than any other 
plan I've seen.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] How to parameterize a loadable extension at runtime

[Hick Gunter]

You can pass parameters to the xCreate function in the CREATE VIRTUAL TABLE 
statement, if the setting you desire remains unchanged during the lifetime of 
the table.

CREATE VIRTUAL TABLE  USING  [ ( ,...) ];

You can declare hidden fields in the call to sqlite3_declare_vtab() call within 
your xCreate function if the setting you desire are specific to a query. The 
constraint will be passed to your xBestIndex function, and (if the query plan 
is selected) the value will be passed to your xFilter function.

CREATE VIRTUAL TABLE with_foo USING handle_foo;

Sqlite3_declare_vtab(db_hanlde, "CREATE TABLE x ( ..., foo integer hidden, 
...);");

SELECT  FROM with_foo wf  WHERE wf.foo = 'bar';

Or, for "none of the above", create a user defined function that will handle 
storing/retrieving the settings and provide a C interface for your virtual 
table implementation to access them directly

SELECT param('foo','bar') AS p;
p
--
NULL

SELECT param('foo') AS foo;
foo
-
bar



-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ulrich Telle
Gesendet: Dienstag, 06. Februar 2018 09:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] How to parameterize a loadable extension at runtime

I have implemented a loadable SQLite extension. The behaviour of the extension 
can be configured by setting various parameters. Currently I select the 
parameter settings at compile time. However this is not very flexible. I would 
like to be able to modify the parameters at runtime.

The most logical way would be to add extension-specific pragmas, but it doesn't 
seem to be possible to intercept the pragma handling of SQLite without 
modifying the SQLite source.

Another possibility would be to add a user-defined function for the 
configuration of the extension that could be called from a SELECT
statement:

SELECT myextension_config('param-name', 'param-value');

Is there a better (or even recommended) way how to accomplish such 
parameterization at runtime?

Regards,

Ulrich
--
E-Mail privat:  ulrich.te...@gmx.de
World Wide Web: http://www.telle-online.de


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[Dominique Devienne]

On Tue, Feb 6, 2018 at 9:44 AM, Simon Slavin  wrote:

> On 6 Feb 2018, at 8:33am, Ulrich Telle  wrote:
>
> > Another possibility would be to add a user-defined function for the
> > configuration of the extension that could be called from a SELECT
> > statement:
> >
> > SELECT myextension_config('param-name', 'param-value');
>
> I've seen this done before.  Of course it means that your normal function
> is not deterministic,

so you may no longer use SQLITE_DETERMINISTIC .

This is in contrast to a function where parameters are set during
> compilation.
>

An alternative is to expose a virtual table with a fixed set of rows, and
accepting
updates on the values, which can also then be "typed" too. But that's a lot
more
complicated though. (and refusing inserts/deletes too, of course).

That vtable could also expose version information for the extension, for
example,
and those would be read-only. Just thinking aloud. Avoids non-deterministic
functions. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Simon Slavin wrote:
> 
> On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> 
> > Another possibility would be to add a user-defined function for the 
> > configuration of the extension that could be called from a SELECT 
> > statement:
> > 
> > SELECT myextension_config('param-name', 'param-value');
> 
> I've seen this done before.  Of course it means that your normal function is 
> not deterministic, so you may no longer use SQLITE_DETERMINISTIC .  This is 
> in contrast to a function where parameters are set during compilation.

Well, actually my goal is not to have an extension with non-deterministic 
functions. The parameters have mostly the purpose to initialize the extension 
(things similar to what you do to SQLite itself with pragmas like "PRAGMA 
cache_size", or "PRAGMA data_store_directory"). The extension would accept 
changes to the parameters only before the first invocation of the extension 
functions.

Regards,

Ulrich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parameterize a loadable extension at runtime

[Simon Slavin]

On 6 Feb 2018, at 8:33am, Ulrich Telle  wrote:

> Another possibility would be to add a user-defined function for the 
> configuration of the extension that could be called from a SELECT 
> statement:
> 
> SELECT myextension_config('param-name', 'param-value');

I've seen this done before.  Of course it means that your normal function is 
not deterministic, so you may no longer use SQLITE_DETERMINISTIC .  This is in 
contrast to a function where parameters are set during compilation.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

I have implemented a loadable SQLite extension. The behaviour of the 
extension can be configured by setting various parameters. Currently I select 
the parameter settings at compile time. However this is not very flexible. I 
would like to be able to modify the parameters at runtime.

The most logical way would be to add extension-specific pragmas, but it 
doesn't seem to be possible to intercept the pragma handling of SQLite 
without modifying the SQLite source.

Another possibility would be to add a user-defined function for the 
configuration of the extension that could be called from a SELECT 
statement:

SELECT myextension_config('param-name', 'param-value');

Is there a better (or even recommended) way how to accomplish such 
parameterization at runtime?

Regards,

Ulrich
-- 
E-Mail privat:  ulrich.te...@gmx.de
World Wide Web: http://www.telle-online.de


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.22.0 walro2 test failures on ppc64

[Petr Kubat]

Tests are passing now, thanks!

Had to make one small modification to the patch to get it working though:

@@ -17,7 +17,7 @@ Index: test/walro2.test
 +do_execsql_test 0.0 {
 +  PRAGMA journal_mode = wal;
 +  CREATE TABLE t1(x);
-+}
++} {wal}
 +set MINSHMSZ [file size test.db-shm]
 +
  foreach bZeroShm {0 1} {

Petr

On 02/05/2018 02:29 PM, Dan Kennedy wrote:

On 02/05/2018 04:22 PM, Petr Kubat wrote:

On 02/05/2018 08:41 AM, Petr Kubat wrote:


Hi all,

hitting some failures when building 3.22.0 on ppc64 boxes (both 
big-endian and little-endian) running Fedora Rawhide (full logs in 
[1][2]):


Time: walro.test 135 ms
! walro2-1.3.2.2 expected: [0 32768]
! walro2-1.3.2.2 got:  [0 65536]
! walro2-1.3.3.0 expected: [4224 32768]
! walro2-1.3.3.0 got:  [4224 65536]
! walro2-1.3.3.2 expected: [4224 32768]
! walro2-1.3.3.2 got:  [4224 65536]
! walro2-2.3.2.2 expected: [0 32768]
! walro2-2.3.2.2 got:  [0 65536]
! walro2-2.3.3.0 expected: [4224 32768]
! walro2-2.3.3.0 got:  [4224 65536]
! walro2-2.3.3.2 expected: [4224 32768]
! walro2-2.3.3.2 got:  [4224 65536]
Time: walro2.test 513 ms

From what I can see the expected sizes of the database files seem to 
be half of what is the actual size.


To correct myself here - its the "-wal" file that gets twice as big 
as expected. btw the starting size of the "-wal" file on ppc64 is the 
same (65536 bytes) even in older versions of sqlite (looking at 
3.20.1 right now).



Thanks for reporting this. I think it's just a problem with the test 
script. Now fixed here:


  http://www.sqlite.org/src/info/d9e59cfb8476e1ec

Dan.







Petr

[1]: 
https://kojipkgs.fedoraproject.org//work/tasks/9900/24679900/build.log
[2]: 
https://kojipkgs.fedoraproject.org//work/tasks/9898/24679898/build.log


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users