Re: [sqlite] Sqlite error code 14 when using 3.31.0+

2020-03-09 Thread Rowan Worth
On Mon, 9 Mar 2020 at 23:22, Daniel Polski wrote: > Updated to 3.31.1 but my application started spitting out an error when > opening the database, so I tested some earlier sqlite versions to figure > out when the problem starts. > I don't get the message in versions <= 3.30.1. > > (from the

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-09 Thread Keith Medcalf
On Monday, 9 March, 2020 18:18, Peng Yu wrote: >But I never experience the problem in my original email when I used >python3's default sqlite3 module (WAL was not used). What is the >difference between the default sqlite3 module and apsw? Thanks. THe relevant difference is that the sqlite3

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-09 Thread Peng Yu
On 3/8/20, Keith Medcalf wrote: > > On Sunday, 8 March, 2020 21:24, Peng Yu wrote: > >>When I open an sqlite3 db using the following python code, > >>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) > >>, I got the following error. > >>Traceback (most recent call last): >> File

Re: [sqlite] sqlite3: .width counts bytes, not characters

2020-03-09 Thread Richard Hipp
On 3/9/20, Keith Medcalf wrote: >>I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24 >>3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for > > Have you tried version more recent than 4 years and 1 month old? > I think some of these issues may have been fixed in the

Re: [sqlite] sqlite3: .width counts bytes, not characters

2020-03-09 Thread Keith Medcalf
>I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24 >3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for Have you tried version more recent than 4 years and 1 month old? I think some of these issues may have been fixed in the last couple of years. -- The fact that

Re: [sqlite] sqlite3: .width counts bytes, not characters

2020-03-09 Thread Jose Isaias Cabrera
softw...@quantentunnel.de, on Monday, March 9, 2020 04:02 PM, wrote... > > Hi > > I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24 > 3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for > formatted output. '.width' does not behave as I expected when non-ASCII >

[sqlite] sqlite3: .width counts bytes, not characters

2020-03-09 Thread Software
Hi I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for formatted output. '.width' does not behave as I expected when non-ASCII Unicode characters are printed. It seems that .width counts bytes and not characters.

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-09 Thread Keith Medcalf
On Monday, 9 March, 2020 08:33, Simon Slavin wrote: >If your .shm and .wal files still exist when no apps are accessing the >database, the most likely cause is that at least one of the apps is not >closing its connection correctly. or you are opening the database connection with

Re: [sqlite] Custom VFSes and PENDING_BYTE position

2020-03-09 Thread Alexey Alyaev
Hi D. Richard Hipp, Thank you for quick response. I did consider all options as it stands: 1) Skipping the lock bytes in our vfs. This becomes really messy very fast, as it is based on test_onefile vfs and both db and journal are part of the same file, and lock bytes may end up in any of

[sqlite] Sqlite error code 14 when using 3.31.0+

2020-03-09 Thread Daniel Polski
Updated to 3.31.1 but my application started spitting out an error when opening the database, so I tested some earlier sqlite versions to figure out when the problem starts. I don't get the message in versions <= 3.30.1. (from the applications log) SQLite Version: 3.31.0 INFO: Database opened:

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-09 Thread Simon Slavin
On 9 Mar 2020, at 2:16pm, David Raymond wrote: > In general I believe the last connection tries to do a complete checkpoint > when it closes, and if it succeeds then it'll delete the -wal and -shm files. > If you have automatic checkpointing turned off (maybe you're doing regular >

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-09 Thread David Raymond
I see this. What does it mean? Does it mean even when the sqlite3 session is closed there is still -wal and -shm left on the disk? """ There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as

Re: [sqlite] Custom VFSes and PENDING_BYTE position

2020-03-09 Thread Richard Hipp
On 3/9/20, Alexey Alyaev wrote: > > I have been working on a custom VFS project since 2017 > > would it not be reasonable to allow setting a different > lock byte offset just in windows/unix VFS, while keeping the lock-byte page > position untouched? > > This would allow VFSes such as ours to

Re: [sqlite] speeding up an FTS5 search with a JOIN

2020-03-09 Thread P Kishor
following up on my own response to Dan… I see what you mean Dan. I am not an expert at reading the query plans, but I do see that effectively my new query has the same query plan as the last two queries combined as well as the original query. The only difference is the order in which it

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

2020-03-09 Thread Peng Yu
I see this. What does it mean? Does it mean even when the sqlite3 session is closed there is still -wal and -shm left on the disk? """ There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as

Re: [sqlite] speeding up an FTS5 search with a JOIN

2020-03-09 Thread P Kishor
> On Mar 9, 2020, at 1:04 PM, Dan Kennedy wrote: > > > On 9/3/63 01:44, Puneet Kishor wrote: >> Update: so, after much hitting of my head against the wall of sql, I came up >> with the following – as noted above, I really have two distinct set of >> queries I can do separately like so >>

[sqlite] Custom VFSes and PENDING_BYTE position

2020-03-09 Thread Alexey Alyaev
Hi sqlite developers, As this is my first post to the mailing list, I wanted to say huge thanks and regards to D. Richard Hipp and his team for all the hard work put into this wonderful database software/library :-) I have been working on a custom VFS project since 2017. The project got

Re: [sqlite] speeding up an FTS5 search with a JOIN

2020-03-09 Thread Dan Kennedy
On 9/3/63 01:44, Puneet Kishor wrote: Update: so, after much hitting of my head against the wall of sql, I came up with the following – as noted above, I really have two distinct set of queries I can do separately like so Q1: (SELECT t1Id FROM t1 WHERE …) AS a Q2: (SELECT t1Id FROM vt1

[sqlite] speeding up an FTS5 search with a JOIN

2020-03-09 Thread P Kishor
Hi Dan, > On Sat Mar 7 13:32:54 UTC 2020,Dan Kennedy danielk1977 at gmail.com wrote: >> On 7/3/63 14:58, P Kishor wrote: [snipped] >> The actual query, in this case, takes ~47ms. So far so good. But the problem >> occurs when I join the two tables >> >> ``` >> sqlite> EXPLAIN QUERY PLAN