[sqlite] eponymous vtables, xBestIndex, and required parameters...

2017-10-04 Thread dave
Folks; I am building a system which uses the virtual tables facilityto join some non-relational data (coming from system APIs) to relational data. I am using the virtual table mechanism in sqlite, and in particular am creating 'eponymous' virtual tables to create 'table valued functions'. I

Re: [sqlite] eponymous vtables, xBestIndex, and required parameters...

2017-10-04 Thread Richard Hipp
On 10/4/17, dave wrote: > 1) is there an orthodox method of indicating that a query plan request from > xBestIndex is a no-go, Give that plan a huge estimatedCost. As a backup, in the exceedingly unlikely event that SQLite chooses your no-go plan in spite of the huge

Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

2017-10-04 Thread Simon Slavin
On 5 Oct 2017, at 3:17am, Hegde, Deepakakumar (D.) wrote: > 2) Both the link are added the busy handler and busy handler function is > retries for 1 times with 10ms second of delay. > sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle, >

Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

2017-10-04 Thread Igor Tandetnik
On 10/4/2017 10:17 PM, Hegde, Deepakakumar (D.) wrote: There is a problem we are facing with the multiple DB process write operation. Following the procedure: 1) Open the Database in the two process as: sqlite3_open_v2 (pcDbName, >sSqliteInfo.pSqlHandle, (SQLITE_OPEN_READWRITE |

Re: [sqlite] How to handle such situation

2017-10-04 Thread J. King
Perhaps I need to be more explicit: If the transaction fails because of a constraint violation, then using either the ROLLBACK, IGNORE, or REPLACE conflict resolution strategies would commit or roll back the transaction and automatically close it. If you add conflict clauses to table schemata,

Re: [sqlite] How to handle such situation

2017-10-04 Thread Igor Korot
Hi, Jens, On Wed, Oct 4, 2017 at 6:15 PM, Jens Alfke wrote: > > >> On Oct 4, 2017, at 2:20 PM, Igor Korot wrote: >> >> If I start transaction, all queries were successful, but issuing "COMMIT" >> fails. >> On such failure I am going to present an error,

[sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

2017-10-04 Thread Hegde, Deepakakumar (D.)
Hi All, There is a problem we are facing with the multiple DB process write operation. Following the procedure: 1) Open the Database in the two process as: sqlite3_open_v2 (pcDbName, >sSqliteInfo.pSqlHandle, (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX),

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Simon Slavin
On 5 Oct 2017, at 12:07am, Toby Dickenson wrote: > How does PRAGMA optimize or ANALYSE affect concurrency? Is there any > risk that it might leave the database locked for the full duration of > the scan? ANALYZE does this every time. It’s not possible to analyze a database

Re: [sqlite] How to handle such situation

2017-10-04 Thread Igor Korot
Hi, On Wed, Oct 4, 2017 at 6:19 PM, J. King wrote: > See also: > This is good to know but I don't think it is applicable in this case as I explicitly said that this is inside transaction which fails. Thank you. > > On October 4, 2017

Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.

2017-10-04 Thread Hegde, Deepakakumar (D.)
Hi, Thanks a lot for this suggestion. I have reduced the delay in the busy handler from 10ms to 1ms and added a delay of 2ms after "commit" and before "begin". With this it is working fine now. After reading you input we got to know the root cause is because of the CPU context switch

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Howard Kapustein
>On your development system This is impractical for our case >It’s intended for offline-maintenance. The documentation doesn't say that. In fact the only related comment is https://sqlite.org/pragma.html#pragma_optimize ...This pragma is usually a no-op or nearly so and is very fast. However if

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread Jean-Luc Hainaut
On 04/10/2017 02:16, Simon Slavin wrote: The differences between SQLite and (a.o.) MySQL versions of "group_concat" are a recurrent topic. Since I often need to specify "distinct", "order by", "order direction" and "separator", I have written a simple UDF class that simulates the MySQL full

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Howard Kapustein
>From my personal experience, performance instability of SQLite queries >(drastically jumping from milliseconds to seconds and back) I haven't seen that sort of instability. We found some queries were consistently poor until we ran ANALYZE, due to nature of the data, indexes and queries.

[sqlite] Segfault on query related to json_each in where clause

2017-10-04 Thread Wout Mertens
Crashes on 3.19, 3.20, but not on 3.15: $ sqlite3 SQLite version 3.20.0 2017-08-01 13:24:15 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(json JSON); sqlite> select * from t

Re: [sqlite] Segfault on query related to json_each in where clause

2017-10-04 Thread Richard Hipp
Thanks for the bug report. This problem was previously described by ticket https://sqlite.org/src/info/b899b6 and was fixed by check-in https://sqlite.org/src/info/c7f9f47b23 on 2017-09-04. The re-release snapshot on the https://sqlite.org/download.html download page contains this fix (among

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread Doug Nebeker
Is it just a matter of using sqlite3_create_function to register a function that guarantees it will concatenate in the order rows are received? Would that guarantee that your example works, or is order no longer guaranteed once they leave the inner select? SELECT group_concat(LineText, '\n')

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Simon Slavin
On 4 Oct 2017, at 5:06pm, Jens Alfke wrote: > That’s an interesting idea. I’ve been assuming that ANALYZE was dependent on > the exact contents of the database, but if its results can be canned and > applied to new databases, that’s a good optimization. It works and I’ve

Re: [sqlite] Shell tool allows creation of column name ""

2017-10-04 Thread David Raymond
But without the quotes you couldn't have the empty string as a field name :) It also allows for spaces etc. and avoids needing logic to find out whether it really needs the quotes or not. And remember that the quotes aren't part of the field name, they're just there in the SQL text. In general

[sqlite] Shell tool allows creation of column name ""

2017-10-04 Thread Simon Slavin
Given a .csv file which starts like this: ID,code,name,sortOrder,,AlternativeName 1,RAH,Robert A. Heinlein,"Heinlein, Robert A.",Real Name, 2,IA,Isaac Asimov,"Asimov, Isaac",Real Name, 3,HH,Harry Harrison,"Harrison, Harry",, Shell tool of this version SQLite version 3.19.3 2017-06-27 16:48:08

[sqlite] Unlikely data security problem in sqlite_stat tables

2017-10-04 Thread Simon Slavin
The problem occurs only with non-default compilation settings, and depends on an incorrect assumption by the programmer, so it is unlikely that it would cause a problem under normal circumstances. It is related to the following commands: DROP TABLE TableName — removes related rows from

Re: [sqlite] Error in docs for WHERE clause in CREATE INDEX

2017-10-04 Thread Richard Hipp
Fixed in the draft documentation. On 10/4/17, Jens Alfke wrote: > From https://www.sqlite.org/partialindex.html > : > >> The WHERE clause may not contain subqueries, references to other tables, >> non-deterministic functions, or

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Jens Alfke
Simon Slavin wrote: > > It is not expected that you’ll try to run ANALYZE while a database is in use. > It’s intended for offline-maintenance. “Offline maintenance” is for servers  I suspect it’s not relevant to the majority* of use cases for SQLite, like user-facing applications and

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread Simon Slavin
On 4 Oct 2017, at 4:06pm, Doug Nebeker wrote: > Is it just a matter of using sqlite3_create_function to register a function > that guarantees it will concatenate in the order rows are received? Would > that guarantee that your example works, or is order no longer

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread Richard Hipp
On 10/4/17, Doug Nebeker wrote: > Is it just a matter of using sqlite3_create_function to register a function > that guarantees it will concatenate in the order rows are received? Would > that guarantee that your example works, or is order no longer guaranteed > once they

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread David Raymond
http://www.sqlite.org/optoverview.html section 6.2 "Manual Control Of Query Plans Using SQLITE_STAT Tables" SQLite provides the ability for advanced programmers to exercise control over the query plan chosen by the optimizer. One method for doing this is to fudge the ANALYZE results in the

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Richard Hipp
That ability to manually pre-populate the sqlite_stat1 table has been around for ages. But not many developers use it. Probably because it is tedious to do and developers have more important problems to work on. A few weeks ago, we got a report of a performance problem from a support customer.

Re: [sqlite] Shell tool allows creation of column name ""

2017-10-04 Thread Darren Duncan
I believe that as long as quoted identifiers are allowed, every value allowed as a regular character string should also be allowed as an identifier, including the empty string. (Length limits notwithstanding.) Some best practices may be against empty string names, but the system should allow

[sqlite] Error in docs for WHERE clause in CREATE INDEX

2017-10-04 Thread Jens Alfke
From https://www.sqlite.org/partialindex.html : > The WHERE clause may not contain subqueries, references to other tables, > non-deterministic functions, or bound parameters. The LIKE, GLOB, MATCH, and > REGEXP operators in SQLite are implemented as

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Stephen Chrzanowski
The databases I make are pretty darned small and insignificant that even full table scans wouldn't show that much of a hit in performance. However, if there were an API or SQL code or something we can add to our code that would give you meaningful results (other than "Yes, it works") we could

Re: [sqlite] How to handle such situation

2017-10-04 Thread Jens Alfke
> On Oct 4, 2017, at 2:20 PM, Igor Korot wrote: > > If I start transaction, all queries were successful, but issuing "COMMIT" > fails. > On such failure I am going to present an error, but then what happens > if I go and try to close the connection? If COMMIT fails, you

Re: [sqlite] How to handle such situation

2017-10-04 Thread J. King
See also: On October 4, 2017 6:15:55 PM EDT, Jens Alfke wrote: > > >> On Oct 4, 2017, at 2:20 PM, Igor Korot wrote: >> >> If I start transaction, all queries were successful, but issuing >"COMMIT" fails. >> On such

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Jens Alfke
> On Oct 4, 2017, at 10:30 AM, Richard Hipp wrote: > > The PRAGMA optimize command is our effort to move further in the > direction of a "smart" SQLite that always automatically "does the > right thing" with respect to gathering and using database statistics. That’s a great

[sqlite] How to handle such situation

2017-10-04 Thread Igor Korot
Hi, list, Have a following question. I am writing an application in which I will be using transactions. At the end of the application I will close the connection. The application will verify every single call to SQLite for an error. If I start transaction, all queries were successful, but

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread sub sk79
On Wed, Oct 4, 2017 at 12:29 PM, Richard Hipp wrote: > > This restriction on the query flattener causes your example > query above to do what you want. > If subquery-flattening needs to be disabled explicitly, is using "LIMIT -1 OFFSET 0 " the recommended way? > SQLite

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread petern
You could also write it even more clearly as: WITH IndexedLines AS (SELECT LineText FROM DocLines WHERE DocID = 10 ORDER BY LineIndex) SELECT group_concat(LineText, char(10)) FROM IndexedLines; That code will actually work. As it is not C, SQLite will not recognize the '\n' C escaped line feed

[sqlite] Segfault on query related to json_each in where clause

2017-10-04 Thread Wout Mertens
Oh interesting, I am seeing it with 3.19.3 contrary to the bug report… Mind you this is High Sierra so Apple may have messed with the build… /usr/bin/sqlite3 --version 3.19.3 2017-06-27 16:48:08 2b0954060fe10d6de6d479287dd88890f1bef6cc1beca11bc6cdb79f72e2377b > Thanks for the bug report. This

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread Richard Hipp
On 10/4/17, sub sk79 wrote: > On Wed, Oct 4, 2017 at 12:29 PM, Richard Hipp wrote: > >> >> This restriction on the query flattener causes your example >> query above to do what you want. >> > > If subquery-flattening needs to be disabled explicitly, is using

Re: [sqlite] PRAGMA optimize; == no busy handler?

2017-10-04 Thread Toby Dickenson
How does PRAGMA optimize or ANALYSE affect concurrency? Is there any risk that it might leave the database locked for the full duration of the scan? On 4 October 2017 at 23:29, Jens Alfke wrote: > > >> On Oct 4, 2017, at 10:30 AM, Richard Hipp wrote: >> >>