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

> 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,

Re: [sqlite] question about covering index

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

Re: [sqlite] Question about threadsafe

> (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

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

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().

Re: [sqlite] [EXTERNAL] Question about threadsafe

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

Re: [sqlite] sqlite3_expanded_sql is reading freed heap memory

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

Re: [sqlite] question about covering index

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

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

Re: [sqlite] question about covering index

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

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

Re: [sqlite] question about covering index

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

Re: [sqlite] question about covering index

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

[sqlite] question about covering index

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

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

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

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

> 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

Re: [sqlite] sqlite3_close_v2 leading to database corruption

> 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

Re: [sqlite] sqlite3_close_v2 leading to database corruption

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] sqlite3_close_v2 leading to database corruption

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,

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

> 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

Re: [sqlite] sqlite3_expanded_sql is reading freed heap memory

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: >

[sqlite] sqlite3_expanded_sql is reading freed heap memory

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

Re: [sqlite] Header corruption

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

Re: [sqlite] Header corruption

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

[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

Re: [sqlite] Question about threadsafe

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

Re: [sqlite] Question about threadsafe

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: >

Re: [sqlite] [EXTERNAL] Question about threadsafe

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

[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

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

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

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

> 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

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

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

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

> 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

Re: [sqlite] Vetting SQLite

> 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

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

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

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

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

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

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

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

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 > >

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

> 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',

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

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

[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

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

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