Re: [sqlite] SQLite.NET Class Library Documentation Feedback: Limitations

> On Oct 5, 2016, at 5:49 AM, Santiago Bayeta wrote: > > But I also read from SQLite docs that now SQLite supports unlimited Reading > operations while writing. It does if the databases uses a WAL (Write-Ahead Log). This is a persistent per-database setting that can be

[sqlite] SQLite.NET Class Library Documentation Feedback: Limitations

Hi, I’m just starting with SQLite.NET and found this in the documentation (chm file downloaded from sqlite.org). “When a connection locks the database for writing, no other connection or process may read or write to the database until the write operation is complete.” But I also read from

Re: [sqlite] Two problems in latest version of lempar.c

Hi Richard thanks for the quick response! On 04.10.2016 14:21, Richard Hipp wrote: > On 10/4/16, Benjamin Franksen wrote: >> I am using the lemon parser generator in a project >> (http://www-csr.bessy.de/control/SoftDist/sequencer/). I updated the >> lemon

[sqlite] armv7 build of native sqlite jdbc driver

Hello All, I am working on a project where we are using sqlite as the store for data on a computer with armv7 processor running Mentorgraphics embedded linux. I am currently running xerial sqlite jdbc driver in pure java mode but it always seems to get into an illegal state so I was thinking

Re: [sqlite] Database malformed after 6000 Inserts?

Are you accessing the database across a network, or is local on the server? Are you using a transaction, or are these individual inserts? I'm using system.data.sqlite.dll for a file backup program and that happily rattles through 800,000 inserts (continuously) without issues. Its worth noting that

Re: [sqlite] Mac OS Sierra deprecation in bindings

Thanks for the info. Do you mind if I copy some of this information back to the go-sqlite3 issues? On Tue, Oct 4, 2016 at 1:27 PM Richard Hipp wrote: On 10/4/16, Judson Lester wrote: > > That said, it's harmless until the deprecation turns into a removal at

Re: [sqlite] How/Where to check if a table exists and read it ?

On 10/5/16, Domingo Alvarez Duarte wrote: > > I just found that the changes I made to sqlite3 to allow reference > objects on attached databases does not work properly, > > Where in the view execution path the table/view qualifiers could be > discarded ? > I'm not sure

Re: [sqlite] How/Where to check if a table exists and read it ?

Hello Richard ! I just found that the changes I made to sqlite3 to allow reference objects on attached databases does not work properly, it does work when the tables/views from more than one database are not equal but if they are equal even with qualification it seems to always try to find

Re: [sqlite] multiple processes working on one database file

On 5 Oct 2016, at 4:30pm, Jens Alfke wrote: > I did find that SQLite’s own locking was less effective/fair than using a > mutex, and in situations with a lot of write contention could lead to > starvation and timeouts. It’s possible I was doing things wrong, as the >

Re: [sqlite] Default ordering of SELECT query (Clemens Ladisch)

On 2016/10/05 5:27 PM, Andrii Motsok wrote: Date: Mon, 3 Oct 2016 16:25:09 +0200 Hi, 1) In case of ORDER BY if we choose ordering which does not follow "index ordering" we will get performance degradation. 2) If we use ORDER BY can we be sure that order of rows is the same for two

Re: [sqlite] Default ordering of SELECT query (Clemens Ladisch)

Date: Mon, 3 Oct 2016 16:25:09 +0200 From: Clemens Ladisch To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Default ordering of SELECT query Message-ID: Content-Type: text/plain; charset=utf-8 >>Andrii Motsok

Re: [sqlite] multiple processes working on one database file

> On Oct 5, 2016, at 3:25 AM, Simon Slavin wrote: > > Yes. SQLite incorporates multi-process/multi-thread locking, exponential > backoff, and such things. This all works fine unless you have intentionally > turned off those features. I did find that SQLite’s own

Re: [sqlite] How to build the sqlite3 documentation locally ?

On 10/5/16, Domingo Alvarez Duarte wrote: > Hello ! > > I just trying to build the sqlite3 documentation to run locally but it > seems to depend on "../bld/tclsqlite3.c" amalgamation but it doesn't > exists in the repository or in the sqlite3 repository. > > How to generate it

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

On 10/5/16, Richard Hipp wrote: > On 10/5/16, Clemens Ladisch wrote: >> stop >> >> This looks like a bug. >> > > I think it might be fixed on trunk. I was just trying to bisect... I think this may be a repeat of the problem described by ticket

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

On 10/5/16, Clemens Ladisch wrote: > stop > > This looks like a bug. > I think it might be fixed on trunk. I was just trying to bisect... -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

> Paul wrote: > > I've traced this issue down to the simplest test case: > > > > CREATE TABLE IF NOT EXISTS foo > > ( > > id INTEGER, > > baz INTEGER, > > PRIMARY KEY(id) > > ); > > > > CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id); > > > > CREATE TABLE IF NOT EXISTS bar > > ( > >

[sqlite] How to build the sqlite3 documentation locally ?

Hello ! I just trying to build the sqlite3 documentation to run locally but it seems to depend on "../bld/tclsqlite3.c" amalgamation but it doesn't exists in the repository or in the sqlite3 repository. How to generate it or where to get it ? Cheers !

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

Paul wrote: > I've traced this issue down to the simplest test case: > > CREATE TABLE IF NOT EXISTS foo > ( > id INTEGER, > baz INTEGER, > PRIMARY KEY(id) > ); > > CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id); > > CREATE TABLE IF NOT EXISTS bar > ( > foo INTEGER, > PRIMARY

Re: [sqlite] Inspect WAL file?

Den 2016-10-05 kl. 14:51, skrev Michael Schlenker: Hi, have a look at the 'showwal.c' file in the sources. https://www.sqlite.org/src/artifact/ec79959834f7b21f It allows you to look at the WAL.., but not sure if it fits your problem. Thanks!

Re: [sqlite] Inspect WAL file?

That should be safe too. I hope someone else can help debug your problem. Many bugs like the one you mentioned are a result of a) having other parts of your program misuse SQLite file handles b) having other parts of your program overwrite memory SQLite is using c) using file storage

Re: [sqlite] Inspect WAL file?

Hi, have a look at the 'showwal.c' file in the sources. https://www.sqlite.org/src/artifact/ec79959834f7b21f It allows you to look at the WAL.., but not sure if it fits your problem. Am 05.10.2016 um 10:44 schrieb Daniel Polski: > Do you have any suggestion about how to inspect the contents

Re: [sqlite] Inspect WAL file?

On 5 Oct 2016, at 11:37am, Daniel Polski wrote: >> Do your applications use any PRAGMAs ? > PRAGMA foreign_keys=ON; > PRAGMA journal_mode=WAL; Both of those should be safe in this context. It's not normally necessary to set the journal_mode each time you open the

Re: [sqlite] Fastest way to backup a "live" database

On 5 Oct 2016, at 1:07pm, Eric Grange wrote: > I have added SQLITE_OPEN_EXCLUSIVE to the flags of the backup db. That's a very good idea. Might add that to some tips I use elsewhere. Thanks. Simon. ___ sqlite-users mailing list

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

To add to that, EXPLAIN QUERY PLAN shows that covering index will be used: sqlite> EXPLAIN QUERY PLAN SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 1; selectidorder fromdetail

Re: [sqlite] Fastest way to backup a "live" database

> But yes, if you have an application which uses only the backup API it should be safe. I have added SQLITE_OPEN_EXCLUSIVE to the flags of the backup db. That should cover accidental cases where the backup db is opened before or during the backup by "something else" ? It should never happen in my

[sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

I've traced this issue down to the simplest test case: CREATE TABLE IF NOT EXISTS foo (  id  INTEGER,  baz INTEGER,  PRIMARY KEY(id) ); CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id); CREATE TABLE IF NOT EXISTS bar (  foo INTEGER,  PRIMARY KEY(foo),  FOREIGN KEY(foo) REFERENCES foo(id)

[sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

I've traced this issue down to the simplest test case: CREATE TABLE IF NOT EXISTS foo ( id INTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id); CREATE TABLE IF NOT EXISTS bar ( foo INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON

Re: [sqlite] Fastest way to backup a "live" database

Eric Grange wrote: > Ran some tests with variations of Clemens's backup exemple, and adding > > sqlite3_exec(src, "PRAGMA cache_size=1", NULL, NULL, NULL); > sqlite3_exec(dst, "PRAGMA cache_size=1", NULL, NULL, NULL); > > seems to provide the best performance [...] > > While the

Re: [sqlite] Inspect WAL file?

Do your applications use any PRAGMAs ? PRAGMA foreign_keys=ON; PRAGMA journal_mode=WAL; Are they running on the same computer both accessing storage held on that same computer ? It's different threads within the same application. Threads have "own" sqlite connections.

Re: [sqlite] Inspect WAL file?

There is no helper software, except maybe SQLite itself. Ok, thanks for the information. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multiple processes working on one database file

On 5 Oct 2016, at 11:10am, Christoph P.U. Kukulies wrote: > Would it be possible to have multiple processes (webapplication) working on a > singe Sqlite database file? WRT locking, deadlocks etc. Yes. SQLite incorporates multi-process/multi-thread locking, exponential

Re: [sqlite] Inspect WAL file?

On 5 Oct 2016, at 9:44am, Daniel Polski wrote: > The reason for the examination is that I suspect my application might have a > bug which makes one thread very occationally not see data in the WAL file > another thread has added. Do your applications use any PRAGMAs ?

Re: [sqlite] Fastest way to backup a "live" database

On 5 Oct 2016, at 8:46am, Eric Grange wrote: >sqlite3_exec(dst, "PRAGMA synchronous=OFF", NULL, NULL, NULL); Don't do that for any operation outside taking the backup. It's a major cause of corruption in any situation where two different connections (in the same

Re: [sqlite] Fastest way to backup a "live" database

Eric Grange wrote: >> No, this is what makes the backup restart. With a step size of -1, it >> would never restart. > > Hmm, given WAL mode, this brings another question: why the sqlite CLI does > not do that but uses 100 pages? Because it's much older than WAL mode. > Is using a backup_step N

Re: [sqlite] Inspect WAL file?

Daniel Polski wrote: > Do you have any suggestion about how to inspect the contents of a wal file? http://www.sqlite.org/fileformat2.html#the_write_ahead_log There is no helper software, except maybe SQLite itself. Regards, Clemens ___ sqlite-users

[sqlite] multiple processes working on one database file

Would it be possible to have multiple processes (webapplication) working on a singe Sqlite database file? WRT locking, deadlocks etc. -- Christoph ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Searching for a percent symbol

Thank you also Petite Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 5

Re: [sqlite] Searching for a percent symbol

Brilliant thansks Dominique - I had completely misunderstood it :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address

Re: [sqlite] Searching for a percent symbol

> On Oct 5, 2016, at 11:50 AM, Paul Sanderson > wrote: > > How do I search for a % symbol within a string when % is a wild card > and I am escaping that very wildcard? For example: with DataSet as ( select 'I got 20 quid' as value union all select 'i got 20%

Re: [sqlite] Searching for a percent symbol

On Wed, Oct 5, 2016 at 11:50 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > Thanks Petite - I have already looked at that - but how? > sqlite> create table t (v); sqlite> insert into t values ...> ('I got 20 quid'), ...> ('i got 20% of it'), ...> ('i got just 20%'), ...>

Re: [sqlite] Searching for a percent symbol

Thanks Petite - I have already looked at that - but how? How do I search for a % symbol within a string when % is a wild card and I am escaping that very wildcard? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Searching for a percent symbol

> On Oct 5, 2016, at 11:38 AM, Paul Sanderson > wrote: > > How casn I find just the rows containing the percent symbol? is it possible? Use the ESCAPE clause: "If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must

[sqlite] Searching for a percent symbol

Hi all I have a column of string values some of which may contain % characters Is it possible to search for just those rows that contain a % eg how could I search for 20%. on a test table containing: I got 20 quid i got 20% of it i got just 20% some money this is an underscore _ ok I tried

[sqlite] Inspect WAL file?

Do you have any suggestion about how to inspect the contents of a wal file? The reason for the examination is that I suspect my application might have a bug which makes one thread very occationally not see data in the WAL file another thread has added.

Re: [sqlite] Fastest way to backup a "live" database

Ran some tests with variations of Clemens's backup exemple, and adding sqlite3_exec(src, "PRAGMA cache_size=1", NULL, NULL, NULL); sqlite3_exec(dst, "PRAGMA synchronous=OFF", NULL, NULL, NULL); sqlite3_exec(dst, "PRAGMA cache_size=1", NULL, NULL, NULL); seems to provide

Re: [sqlite] Fastest way to backup a "live" database

Thanks for the tool Clemens! > No, this is what makes the backup restart. With a step size of -1, it would never restart. Hmm, given WAL mode, this brings another question: why the sqlite CLI does not do that but uses 100 pages? Is using a backup_step N parameter only useful if you want to