Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux wrote: > You are right that my purpose does seem kind of confusing. > > What I really want is UPSERT to avoid doing an UPDATE, checking if the > number of rows affected is zero, then doing an INSERT. The lack of UPSERT >

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 10:58pm, Jonathan Moules wrote: > I know SQLite has a CLI tool, but I'm happier with front-ends You can use the CLI tool (which does still work under Windows 7) to open the database you prepared in your preferred environment and execute just

Re: [sqlite] How to optimise a somewhat-recursive query?

Hi Richard, I've spent the last ~90 minutes trying to build this but to no avail I'm afraid. I'm unable to find a version of nmake for Windows 7 (It seems to be a VS thing and that in turn is Windows 8/10 only). Then I tried inside a VM of Mint, managed to build it, and whilst I was trying to

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

​You are right that my purpose does seem kind of confusing. What I really want is UPSERT to avoid doing an UPDATE, checking if the number of rows affected is zero, then doing an INSERT. The lack of UPSERT leads to a lot more application code using the UPDATE/INSERT combination. UPSERT doesn't

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

David Raymond wrote > In the commented out section: > > TimeTable(DoWeek,Grade,Class_) AS > (VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')... > > Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5, > 'c')...? > > WITH PAR(calStartDate, calEndDate) AS (SELECT

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

R Smith-2 wrote > On 2018/03/17 12:40 PM, csanyipal wrote: >> R Smith-2 wrote >>> Here is a query that will produce all days of the year (without Sundays) >>> plus their week days (and I've expanded for lesson blocks too, but you >>> will probably need to add/edit as I don't know the exact values,

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

In the commented out section: TimeTable(DoWeek,Grade,Class_) AS (VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')... Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5, 'c')...? -Original Message- From: sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

R Smith-2 wrote > On 2018/03/17 12:40 PM, csanyipal wrote: >> R Smith-2 wrote >>> Here is a query that will produce all days of the year (without Sundays) >>> plus their week days (and I've expanded for lesson blocks too, but you >>> will probably need to add/edit as I don't know the exact values,

Re: [sqlite] How to optimise a somewhat-recursive query?

and Richards patch merely attempts to detect such errors of query phrasing and convert the join type for you ... so that there is no need to generate the possibly millions of unnecessary intermediate results ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a

Re: [sqlite] How to optimise a somewhat-recursive query?

Or just try it with the superfluous outer join keyword (LEFT) removed since you are really just doing an inner (equi) join and the outer join data is just discarded (by your WHERE clause constraints) after it is generated anyway ... --- The fact that there's a Highway to Hell but only a

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 6:17pm, Jonathan Moules wrote: > Simon - I suspected the ORDER BY thing was wrong but wanted to check first > rather than simply come out with "SQLite is broken!". This may be related to > the 3.22 regression I brought up a couple of days ago

Re: [sqlite] How to optimise a somewhat-recursive query?

To at least try increasing your version from 3.15 to 3.22 you can get pre-compiled binaries from the http://www.sqlite.org/download.html page. Go to the Precompiled Binaries for Windows section and there's a sqlite-tools which includes the Command Line Interface, and .dll's which you can put in

Re: [sqlite] How to optimise a somewhat-recursive query?

Hi Richard, Simon Re: Compiling - I'm afraid I wouldn't really know where to begin. A quick google finds https://superuser.com/questions/146577/where-do-i-find-nmake-for-windows-7-x64 - but the answers/links there don't seem to work. I've got to go-out now but can take another look later and

Re: [sqlite] How to optimise a somewhat-recursive query?

On Wed, 21 Mar 2018 17:39:45 + Jonathan Moules escribió: > So, I'm back to being stuck on this. > I have inserted 500,000 random urls (no extra lookups - still just > 1000), and now the query (as per the below reply) is back to being > somewhat slow (I'm using

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 in all schemas On 21 March 2018 at 09:22, R Smith wrote: > > On 2018/03/21 11:13 AM, Paul Sanderson wrote: >> >> Actually it is totally different Chris > > > Indeed, and thank you for highlighting this. > > I'm so used to putting down CREATE TABLE t (id INTEGER PRIMARY KEY,

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 5:39pm, Jonathan Moules wrote: > After a couple of hours of investigation, it's only slow when there is either > no ORDER BY, or if I use DESC (which is what I need). If I use ORDER BY u.url > ASC - it's near instantaneous. You have a query

Re: [sqlite] How to optimise a somewhat-recursive query?

On 3/21/18, Jonathan Moules wrote: > So, I'm back to being stuck on this. > I have inserted 500,000 random urls (no extra lookups - still just > 1000), and now the query (as per the below reply) is back to being > somewhat slow (I'm using sqlite 3.15) at about 0.6s

Re: [sqlite] How to optimise a somewhat-recursive query?

So, I'm back to being stuck on this. I have inserted 500,000 random urls (no extra lookups - still just 1000), and now the query (as per the below reply) is back to being somewhat slow (I'm using sqlite 3.15) at about 0.6s per request. After a couple of hours of investigation, it's only slow

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

On 2018/03/21 11:13 AM, Paul Sanderson wrote: Actually it is totally different Chris Indeed, and thank you for highlighting this. I'm so used to putting down CREATE TABLE t (id INTEGER PRIMARY KEY, f2, f3,... ) and seeing the id increment automatically when needed, I lost sight of the fact

[sqlite] here is my test case//Re: SQLITE3 FTS5 prefix query get mismatch result

(Please add.the head file and sqlite lib yourself, Thank you very much) char *orgName[] = { "yun j ji yunji yunj yji yj z zh zha zhao q qi qia qian d du dua duan zhaoqian zhaoq zhqian zhq zqian zq qianduan qiand qduan qd zhaoqianduan zhaoqiand zhaoqduan zhaoqd zhqianduan zhqiand zhqduan zhqd

Re: [sqlite] cannot connect to host www3.sqlite.org:443 (Connection refused)

On 3/21/18, Domingo Alvarez Duarte wrote: > Hello ! > > I'm getting this error when trying to update my sqlite3 repository, this > message began some days ago and I was thinking that it was a temporary > problem, but it seems it's not. The stunnel4 process crashed, and nobody

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

Actually it is totally different Chris > I read that - but my point was more that some people seem to think that an > int primary key can be auto incrementing, it can't. an INT primary key cannot be autoincrementing An INTEGER primary key and an INTEGER primary key autoincrement work in

[sqlite] cannot connect to host www3.sqlite.org:443 (Connection refused)

Hello ! I'm getting this error when trying to update my sqlite3 repository, this message began some days ago and I was thinking that it was a temporary problem, but it seems it's not. Maybe some configuration changed on the sqlite servers ? Cheers !

Re: [sqlite] [EXTERNAL] R*Trees query data cached?

The .describe is just to make sure the SQLite has loaded the table definition. Try pragma table_info(); -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von David Ashman - Zone 7 Engineering, LLC Gesendet: Dienstag, 20. März

Re: [sqlite] [EXTERNAL] R*Trees query data cached?

On Tue, Mar 20, 2018 at 10:45 PM, David Ashman - Zone 7 Engineering, LLC < da...@zone7engineering.com> wrote: > I don't see a .describe in the SQLite documentation. I've tried to use > .schema but that returns an error. > .describe [3] and .schema [2] are "dot-commands" of the sqlite3 command