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

[Kees Nuyt]

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
> leads to a lot more application code using the UPDATE/INSERT combination.
> UPSERT doesn't exist in SQLite so I was trying to work around that by using
> INSERT OR REPLACE which is not the same thing. I can see from another
> recent thread that some others also think that UPSERT would be a valuable
> addition to SQLite.

I fail to see the problem in
BEGIN;
INSERT OR IGNORE ... ;
UPDATE  ;
COMMIT;
Simple code, no need to test number of affected rows, and pretty
fast because the relevant pages will be in cache.

Or use an updatable view with an INSTEAD OF INSERT trigger.

I did notice that attempts to define a proper UPSERT syntax
opened a can of worms by itself because it (also) has to provide
two colum lists, one for a full INSERT if the row with that PK
doesn't exist, and another one for the columns to be updated
when the row already exists. So, I don't see a big advantage in
UPSERT.

My humble two cents,

-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 the 
statement you're interested in.  You don't have to do the whole task in the CLI.

The advantage of using the CLI is that it doesn't involve any conversions to or 
from the SQLite API.  The SQLite development team wrote the whole thing and can 
completely understand the behaviour of everything it does.

You should work with a copy of the database, not the original, of course.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jonathan Moules]

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 
find some sort of sqlite front-end (I know SQLite has a CLI tool, but 
I'm happier with front-ends), the VM crashed (for the second time)!


So I'm afraid self-helping by trying that branch isn't happening. If you 
have the means to readily build one and send it to me off-list, feel 
free to and I'll happily test it. If you can't trust the creator of 
SQLite who can you trust? :-)


---

The good news is that Keith's suggestion of removing the "LEFT" from the 
JOIN fixed the ORDER BY DESC issue. The database and query are the same 
as the one's I provided you, just with a bunch of semi-random data in 
the "urls" table.


---

However, - I've now inserted 100,000 semi-random entries into the 
"lookups" table. If I run the same query again (which is unchanged 
except removing the LEFT's from in front of the JOINs), it's going slow 
again taking about 0.5s.
If I change the ORDER BY to ASC, it's also about 0.5s - so they're 
consistent.


That's with this index added which seems to be its preference from the 
numerous variants I created:


CREATE INDEX url_id_datetime_idx ON lookups (

url_id DESC,

retrieval_datetime

);


The things you're likely interested in though, and they may or may not 
be addressed by your branch:

a) If I remove the ORDER BY and LIMIT, the query takes 15 (fifteen) seconds!

b) And if I add the LEFT back in front of the JOIN's, the ORDER BY ASC 
query is back to being modestly speedy - 0.07s - and with no ORDER BY 
it's the same as well.


c) But with the LEFT JOIN's the query takes about 1.1s for ORDER BY DESC

I can provide another copy of the database with the new data in if you 
wish. Or test the fix if you have a dll you want to send me off list.

Thanks,
Jonathan





On 2018-03-21 17:58, Richard Hipp wrote:

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 per request.

Do you have the ability to compile SQLite from canonical sources?  If
so, please try again with the tip of the join-strength-reduction
branch (https://www.sqlite.org/src/timeline?r=join-strength-reduction).

To compile on Windows:

(1) Download a tarball or ZIP archive (or SQLite Archive) and unpack it.
(2) Run "nmake /f makefile.msc sqlite3.c"

On unix:

(1) Download and unpack as before
(2) ./configure; make sqlite3.c

The only dependence for the above is having a "tclsh" somewhere on your $PATH.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Michaux]

​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 exist in SQLite so I was trying to work around that by using
INSERT OR REPLACE which is not the same thing. I can see from another
recent thread that some others also think that UPSERT would be a valuable
addition to SQLite.​

Peter


On Fri, Mar 16, 2018 at 2:07 PM, Keith Medcalf  wrote:

>
> It is kind of hard to write a specific trigger since your "purpose" is
> confusing.  You have defined some referential integrity and are then
> ignoring it.  If your goal is to update the parent, then why not use the
> statement designed to do that (UPDATE) ... ?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux
> >Sent: Friday, 16 March, 2018 12:42
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
> >followed by INSERT
> >
> >Thank you for the information, Keith.
> >
> >It comes as a surprise to me that the conflict resolution clause of
> >the
> >statement that causes a trigger to fire can override an explicit
> >conflict
> >resolution clause in the body of a trigger. But, as you pointed out,
> >it is
> >documented.
> >
> >How can I write the trigger to not use a conflict resolution clause?
> >I'm
> >trying a bunch of different things but with no luck. Something like
> >the
> >following is possible?
> >
> >CREATE TRIGGER users_after_insert AFTER INSERT ON users
> >BEGIN
> >CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id =
> >new.id)) =
> >0 THEN
> >INSERT INTO user_extras (user_id) VALUES (new.id)
> >END;
> >END;
> >
> >Thanks.
> >
> >Peter
> >
> >
> >
> >On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf 
> >wrote:
> >
> >>
> >> On this page, 7th paragrph:
> >>
> >> https://www.sqlite.org/lang_createtrigger.html
> >>
> >> See that:
> >>
> >> An ON CONFLICT clause may be specified as part of an UPDATE or
> >INSERT
> >> action within the body of the trigger. However if an ON CONFLICT
> >clause is
> >> specified as part of the statement causing the trigger to fire,
> >then
> >> conflict handling policy of the outer statement is used instead.
> >>
> >> ---
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-Original Message-
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux
> >> >Sent: Monday, 12 March, 2018 21:09
> >> >To: sqlite-users@mailinglists.sqlite.org
> >> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
> >> >followed by INSERT
> >> >
> >> >Hi,
> >> >
> >> >I have read that INSERT OR REPLACE is equivalent to a DELETE
> >followed
> >> >by an
> >> >INSERT. I came across a case where that is not true.
> >> >
> >> >Set up a test case
> >> >
> >> >$ rm -f asdf.sqlite && sqlite3 asdf.sqlite
> >> >
> >> >sqlite> .mode columns
> >> >sqlite> .headers on
> >> >sqlite> PRAGMA foreign_keys=OFF;
> >> >
> >> >sqlite> CREATE TABLE users (
> >> >   ...> id INT UNSIGNED NOT NULL PRIMARY KEY,
> >> >   ...> username TEXT NOT NULL
> >> >   ...> );
> >> >
> >> >sqlite> CREATE TABLE user_extras (
> >> >   ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY,
> >> >   ...> other INT NULL DEFAULT NULL,
> >> >   ...> FOREIGN KEY (user_id) REFERENCES users(id) ON
> >UPDATE
> >> >CASCADE ON DELETE CASCADE
> >> >   ...> );
> >> >
> >> >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >> >
> >> >sqlite> INSERT INTO user_extras (user_id, other) VALUES (1,
> >33);
> >> >
> >> >sqlite> SELECT * FROM users;
> >> >id  username
> >> >--  --
> >> >1   asdf
> >> >
> >> >sqlite> SELECT * FROM user_extras;
> >> >user_id other
> >> >--  --
> >> >1   33
> >> >
> >> >
> >> >Let's try a delete followed by an insert.
> >> >
> >> >sqlite> DELETE FROM users WHERE id = 1;
> >> >
> >> >sqlite> SELECT * FROM users;
> >> >
> >> >sqlite> SELECT * FROM user_extras;
> >> >user_id other
> >> >--  --
> >> >1   33
> >> >
> >> >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >> >
> >> >sqlite> SELECT * FROM users;
> >> >id  username
> >> >--  --
> >> >1   asdf
> >> >
> >> >sqlite> SELECT * FROM user_extras;
> >> >user_id other
> >> >--  --
> >> > 

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

[csanyipal]

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 '2017-09-01', '2017-09-21'),
>  DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
> (4,'Th'), (5,'F'),(6,'Sa')),
>  LBs(lessonBlock) AS (VALUES ('1-2'), ('3-4'), ('5-6')/*, ('7-8'),
> ('9-10'), ('11-12'), ('13-14'), ('15-16'), ('17-18'), ('19-20')
>   , ('21-22'), ('23-24'), ('25-26'), ('27-28'), ('29-30'), ('31-32'),
> ('33-34'), ('35-36'), ('37-38'), ('39-40'), ('41-42')
>   , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
> ('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
>   , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
>  /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
>  Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
>  TimeTable(DoWeek,Grade,Class_) AS (VALUES
> ('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
>  
> ('W'),(7),('a'),('Th'),(6),('a'),('Th'),(5),('c'),('F'),(5),('b'),('F'),(7),('c')),*/
>  CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
>  date(calStartDate), -1
>  FROM PAR
>  UNION ALL
>   SELECT nextDay, date(nextDay,'+1 day'),
> CAST(STRFTIME('%w',nextDay) AS INT)
> FROM CAL,PAR
> WHERE nextDay <= calEndDate),
>  RES(dayDate, dayName, lessonBlock) AS
>(SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>   FROM CAL
>CROSS JOIN LBs
>JOIN DoW ON DoW.dayID = CAL.dayId
>WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
> Saturdays */
> SELECT *
> FROM RES;
> 

Indeed. I corrected that part and add the
JOIN TimeTable
like this:
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN TimeTable
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */

but get wrong outputs:
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2

etc.
What am I doing wrong?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[csanyipal]

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, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) AS (VALUES ('1-2'), ('3-4'), ('5-6')/*, ('7-8'),
('9-10'), ('11-12'), ('13-14'), ('15-16'), ('17-18'), ('19-20')
  , ('21-22'), ('23-24'), ('25-26'), ('27-28'), ('29-30'), ('31-32'),
('33-34'), ('35-36'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (VALUES
('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
 
('W'),(7),('a'),('Th'),(6),('a'),('Th'),(5),('c'),('F'),(5),('b'),('F'),(7),('c')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */
SELECT *
FROM RES;

As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4

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

[David Raymond]

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 [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of csanyipal
Sent: Wednesday, March 21, 2018 3:58 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) AS (VALUES ('1-2'), ('3-4'), ('5-6')/*, ('7-8'),
('9-10'), ('11-12'), ('13-14'), ('15-16'), ('17-18'), ('19-20')
  , ('21-22'), ('23-24'), ('25-26'), ('27-28'), ('29-30'), ('31-32'),
('33-34'), ('35-36'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (VALUES
('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
 
('W'),(7),('a'),('Th'),(6),('a'),('Th'),(5),('c'),('F'),(5),('b'),('F'),(7),('c')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL

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

[csanyipal]

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, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) AS (VALUES ('1-2'), ('3-4'), ('5-6')/*, ('7-8'),
('9-10'), ('11-12'), ('13-14'), ('15-16'), ('17-18'), ('19-20')
  , ('21-22'), ('23-24'), ('25-26'), ('27-28'), ('29-30'), ('31-32'),
('33-34'), ('35-36'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (VALUES
('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
 
('W'),(7),('a'),('Th'),(6),('a'),('Th'),(5),('c'),('F'),(5),('b'),('F'),(7),('c')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */
SELECT *
FROM RES;

As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4

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

[Keith Medcalf]

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 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Wednesday, 21 March, 2018 12:31
>To: SQLite mailing list
>Subject: 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 Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules
>>Sent: Wednesday, 21 March, 2018 12:17
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: 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 see if I can find a copy
>>(Microsoft
>>(I'm on Windows) never make it easy to find stuff).
>>
>>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
>(and
>>why I'm using 3.15) - probably why Dr H is suggesting I try his
>>branch.
>>I'm executing the query using SQLiteStudio (Or Python).
>>Thanks,
>>Jonathan
>>
>>
>>On 2018-03-21 17:58, Richard Hipp wrote:
>>> 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 per request.
>>> Do you have the ability to compile SQLite from canonical sources?
>>If
>>> so, please try again with the tip of the join-strength-reduction
>>> branch (https://www.sqlite.org/src/timeline?r=join-strength-
>>reduction).
>>>
>>> To compile on Windows:
>>>
>>> (1) Download a tarball or ZIP archive (or SQLite Archive) and
>>unpack it.
>>> (2) Run "nmake /f makefile.msc sqlite3.c"
>>>
>>> On unix:
>>>
>>> (1) Download and unpack as before
>>> (2) ./configure; make sqlite3.c
>>>
>>> The only dependence for the above is having a "tclsh" somewhere on
>>your $PATH.
>>>
>>
>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

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 Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules
>Sent: Wednesday, 21 March, 2018 12:17
>To: sqlite-users@mailinglists.sqlite.org
>Subject: 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 see if I can find a copy
>(Microsoft
>(I'm on Windows) never make it easy to find stuff).
>
>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 (and
>why I'm using 3.15) - probably why Dr H is suggesting I try his
>branch.
>I'm executing the query using SQLiteStudio (Or Python).
>Thanks,
>Jonathan
>
>
>On 2018-03-21 17:58, Richard Hipp wrote:
>> 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 per request.
>> Do you have the ability to compile SQLite from canonical sources?
>If
>> so, please try again with the tip of the join-strength-reduction
>> branch (https://www.sqlite.org/src/timeline?r=join-strength-
>reduction).
>>
>> To compile on Windows:
>>
>> (1) Download a tarball or ZIP archive (or SQLite Archive) and
>unpack it.
>> (2) Run "nmake /f makefile.msc sqlite3.c"
>>
>> On unix:
>>
>> (1) Download and unpack as before
>> (2) ./configure; make sqlite3.c
>>
>> The only dependence for the above is having a "tclsh" somewhere on
>your $PATH.
>>
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 (and why I'm using 
> 3.15) - probably why Dr H is suggesting I try his branch.
> I'm executing the query using SQLiteStudio (Or Python).

It is not supposed to be possible to speed up a query by adding an ORDER BY 
clause.  The very worst that SQLite is meant to do is pick a fast order for the 
results.  If the only difference between the two queries really is an ORDER BY 
clause, it looks like you've found something unexpected in SQLite.

I don't know what operating system you're using, but I hope Dr H will pilot you 
through a way to provide the developers with a way to reproduce this problem.

You can provide some useful information by using the precompiled binary of the 
sqlite shell tool to give the output from putting "EXPLAIN QUERY PLAN" before 
your SELECT commands:

EXPLAIN QUERY PLAN SELECT
   u.url, l.error_code 

The difference between the two might provide some clues about what's going on.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[David Raymond]

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 your 
...\PythonXX\DLLs (replacing the sqlite3.dll which is in there at the moment)

Then give it a whirl with either the CLI or the new dll in Python.

In addition you can run "explain query plan blah" or the more detailed "explain 
blah" to get a view of what it's doing when it executes the statement "blah"


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jonathan Moules
Sent: Wednesday, March 21, 2018 2:17 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 see if I can find a copy (Microsoft 
(I'm on Windows) never make it easy to find stuff).

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 (and 
why I'm using 3.15) - probably why Dr H is suggesting I try his branch.
I'm executing the query using SQLiteStudio (Or Python).
Thanks,
Jonathan


On 2018-03-21 17:58, Richard Hipp wrote:
> 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 per request.
> Do you have the ability to compile SQLite from canonical sources?  If
> so, please try again with the tip of the join-strength-reduction
> branch (https://www.sqlite.org/src/timeline?r=join-strength-reduction).
>
> To compile on Windows:
>
> (1) Download a tarball or ZIP archive (or SQLite Archive) and unpack it.
> (2) Run "nmake /f makefile.msc sqlite3.c"
>
> On unix:
>
> (1) Download and unpack as before
> (2) ./configure; make sqlite3.c
>
> The only dependence for the above is having a "tclsh" somewhere on your $PATH.
>


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jonathan Moules]

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 see if I can find a copy (Microsoft 
(I'm on Windows) never make it easy to find stuff).


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 (and 
why I'm using 3.15) - probably why Dr H is suggesting I try his branch.

I'm executing the query using SQLiteStudio (Or Python).
Thanks,
Jonathan


On 2018-03-21 17:58, Richard Hipp wrote:

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 per request.

Do you have the ability to compile SQLite from canonical sources?  If
so, please try again with the tip of the join-strength-reduction
branch (https://www.sqlite.org/src/timeline?r=join-strength-reduction).

To compile on Windows:

(1) Download a tarball or ZIP archive (or SQLite Archive) and unpack it.
(2) Run "nmake /f makefile.msc sqlite3.c"

On unix:

(1) Download and unpack as before
(2) ./configure; make sqlite3.c

The only dependence for the above is having a "tclsh" somewhere on your $PATH.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Eduardo]

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 sqlite 3.15) at about 0.6s per request.

After the huge insert, did you run ANALYZE?
 
> 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.
> 
> I've tried every possible combination of indexes I can think up, 
> including of course with url_id DESC. I've also removed the ORDER BY in 
> the view (and set it to DESC as well), but that made absolutely no 
> difference.

Have your indexes in the last row the primary key or rowid?
 
> I'm a little confused as to why I'm seeing this behaviour - my limited 
> understanding of the query-planner and reading 
> https://sqlite.org/queryplanner.html - suggests that at least when using 
> indexes, it'll simply scan an index backwards if that looks like it'll 
> help. I appreciate this is a result-set not an index, but in this case 
> could not the query planner realise that reading through the results 
> backwards would be faster than whatever it's doing?
> 
> And for that matter, shouldn't no ORDER BY be at least the same speed as 
> ORDER BY u.url_id ASC?

What does an integrity check returns?

Try a Reindex and Analyze, all cases should be similar fast (or slow).

> Thoughts welcome; Thanks!
> Jonathan
> 
> 
> On 2018-03-19 00:24, Jonathan Moules wrote:
> > Thanks Simon and Quan.
> > I'm not sure it's the view itself per-se - It takes 0.000s (time too 
> > small to measure) for just the full View to be run on this dataset.
> >
> > It turns out the problem is simpler than that and no data changes are 
> > needed. I did consider Quan Yong Zhai's option and gave it a try, but 
> > couldn't fathom out the necessary SQL to get what I wanted (it's 
> > getting late).
> >
> > Instead prompted by the replies here, I've changed the query very 
> > slightly to the below which solves the problem:
> >
> > SELECT
> > u.url, l.error_code
> > FROM
> > urls u
> > LEFT JOIN
> > lookups l
> > USING(url_id)
> > LEFT JOIN
> > (select * from v_most_recent_lookup_per_url where url_id in (
> > select url_id from urls where url = 'example.com'
> > )) recent
> > -- By definition url's can) recent
> > -- This is the important bit
> > -- Here we use the most recent lookup url_id to link to the 
> > source_seed_id, so we only find its children
> > -- Or alternatively itself
> > ON u.source_seed_id = recent.url_id
> > OR u.url_id = recent.url_id
> > WHERE
> > -- For JSON-spider at least, Generic's are guaranteed to be 
> > generic pages.
> > l.is_generic_flag = 1
> > AND
> > -- Must be "or equal to" so we can get the lookup of the very 
> > base url.
> > l.retrieval_datetime >= recent.retrieval_datetime
> > AND
> > DATETIME(recent.retrieval_datetime) > DATETIME('now', '-5 days')
> > ORDER BY
> > u.url_id DESC
> > LIMIT 1;
> >
> >
> > To save readers having to compare manually, the difference is this: I 
> > turned the "recent" alias item from the View into a subquery (still 
> > using the view), and then moved the "where url = example.com" part in 
> > to there.
> > The query is now literally two orders of magnitude faster, from 0.2s 
> > to 0.004s. No new indexes or anything, just that change.
> >
> > Hopefully this will scale to full datasets; if it doesn't I may have 
> > to consider the other suggestions, but for now this is a 
> > minimum-effort solution.
> >
> > I'm not actually sure what SQLite was doing in the previous query to 
> > make it take so long. , so I imagine there was some hideous recursing 
> > going on or something.
> >
> > Scope for optimisation?
> >
> > Thanks again,
> > Jonathan
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
Eduardo 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Toby Dickenson]

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, f2,
> f3,... ) and seeing the id increment automatically when needed, I lost sight
> of the fact that this HAS to be the rowid alias to work (i.e. INTEGER and
> not INT).
>
> This little fact probably lends slightly more weight to Peter's request for
> a way to tell INTEGER PRIMARY KEY apart from INT PRIMARY KEY in a way that
> doesn't require a schema parse.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 which gets faster when you add an ORDER BY clause ?  That's 
not meant to happen.

Are you using an up-to-date version of SQLite ?

Are you executing the query using the sqlite shell tool ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 per request.

Do you have the ability to compile SQLite from canonical sources?  If
so, please try again with the tip of the join-strength-reduction
branch (https://www.sqlite.org/src/timeline?r=join-strength-reduction).

To compile on Windows:

(1) Download a tarball or ZIP archive (or SQLite Archive) and unpack it.
(2) Run "nmake /f makefile.msc sqlite3.c"

On unix:

(1) Download and unpack as before
(2) ./configure; make sqlite3.c

The only dependence for the above is having a "tclsh" somewhere on your $PATH.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jonathan Moules]

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


I've tried every possible combination of indexes I can think up, 
including of course with url_id DESC. I've also removed the ORDER BY in 
the view (and set it to DESC as well), but that made absolutely no 
difference.


I'm a little confused as to why I'm seeing this behaviour - my limited 
understanding of the query-planner and reading 
https://sqlite.org/queryplanner.html - suggests that at least when using 
indexes, it'll simply scan an index backwards if that looks like it'll 
help. I appreciate this is a result-set not an index, but in this case 
could not the query planner realise that reading through the results 
backwards would be faster than whatever it's doing?


And for that matter, shouldn't no ORDER BY be at least the same speed as 
ORDER BY u.url_id ASC?


Thoughts welcome; Thanks!
Jonathan


On 2018-03-19 00:24, Jonathan Moules wrote:

Thanks Simon and Quan.
I'm not sure it's the view itself per-se - It takes 0.000s (time too 
small to measure) for just the full View to be run on this dataset.


It turns out the problem is simpler than that and no data changes are 
needed. I did consider Quan Yong Zhai's option and gave it a try, but 
couldn't fathom out the necessary SQL to get what I wanted (it's 
getting late).


Instead prompted by the replies here, I've changed the query very 
slightly to the below which solves the problem:


SELECT
u.url, l.error_code
FROM
urls u
LEFT JOIN
lookups l
USING(url_id)
LEFT JOIN
(select * from v_most_recent_lookup_per_url where url_id in (
select url_id from urls where url = 'example.com'
)) recent
-- By definition url's can) recent
-- This is the important bit
-- Here we use the most recent lookup url_id to link to the 
source_seed_id, so we only find its children

-- Or alternatively itself
ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
-- For JSON-spider at least, Generic's are guaranteed to be 
generic pages.

l.is_generic_flag = 1
AND
-- Must be "or equal to" so we can get the lookup of the very 
base url.

l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-5 days')
ORDER BY
u.url_id DESC
LIMIT 1;


To save readers having to compare manually, the difference is this: I 
turned the "recent" alias item from the View into a subquery (still 
using the view), and then moved the "where url = example.com" part in 
to there.
The query is now literally two orders of magnitude faster, from 0.2s 
to 0.004s. No new indexes or anything, just that change.


Hopefully this will scale to full datasets; if it doesn't I may have 
to consider the other suggestions, but for now this is a 
minimum-effort solution.


I'm not actually sure what SQLite was doing in the previous query to 
make it take so long. , so I imagine there was some hideous recursing 
going on or something.


Scope for optimisation?

Thanks again,
Jonathan



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 that this HAS to be the rowid alias to work (i.e. 
INTEGER and not INT).


This little fact probably lends slightly more weight to Peter's request 
for a way to tell INTEGER PRIMARY KEY apart from INT PRIMARY KEY in a 
way that doesn't require a schema parse.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[zheng xiaojin]

(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 zqianduan zqiand zqduan zqd",
"murren m mu l lu lun mulun mul mlun ml",
"l lu m ma man luman lum lman lm",
"d   di   din   ding   d   di   din   ding   f   fu   w   wu   dingding   dingd 
  dding   dd   dingfu   dingf   dfu   df   fuwu   fuw   fwu   fw   dingdingfu   
dingdingf   dingdfu   dingdf   ddingfu   ddingf   ddfu   ddf   dingfuwu   
dingfuw   dingfwu   dingfw   dfuwu   dfuw   dfwu   dfw   dingdingfuwu   
dingdingfuw   dingdingfwu   dingdingfw   dingdfuwu   dingdfuw   dingdfwu   
dingdfw   ddingfuwu   ddingfuw   ddingfwu   ddingfw   ddfuwu   ddfuw   ddfwu   
ddfw   l   li   x   xi   xia   lixia   lix   lxia   lx",
"lucy y ya yan x xi yanxi yanx yxi yx",
"p pe pen peng l le lei penglei pengl plei pl lucy"
};
char *nickName[] = {
"ab",
"bc",
"cd",
"de",
"ef",
"fg"
};
int id[] = { 21078,21218,21125,53234,40824,164873 };

void prepareSchema_prefix(sqlite3 *db) {
char *schemas[] = {
"PRAGMA journal_mode=WAL;",
"drop table if exists tbl;",
"drop table if exists tbl_fts;",
"create table if not exists tbl(name text, uid int primary key, nick text);",
"create virtual table if not exists tbl_fts USING fts5(name, uid);",
"create trigger if not exists trigger_insert_tbl after insert on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);
 \
end;",
"create trigger if not exists trigger_update_tbl after update on tbl \
begin \
replace into tbl_fts(rowid, name, uid) 
values(new.rowid,new.name,new.uid);\
end;"
};
int i, j, rc, cnt;
cnt = sizeof(schemas) / sizeof(char*);
char *errMsg;
for (i = 0; i < cnt; i++) {
rc = sqlite3_exec(db, schemas[i], NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("prepareSchema error: %s\n", errMsg);
return;
}
}
printf("prepareSchema over\n");
}

void fts5_prefix_match_test() {
sqlite3 *db;
int i, j, k, rc;
char *errMsg;
char *append = "abcdefghijklmnopqrstuvwxyz";
sqlite3_open("a.db", );
prepareSchema_prefix(db);
int test_cnt = 300, rc_copy = 0;
char zSql[2048];
char *tmp;
const int len_zSql = 2047;
int len_left;
int nRow = 0, nCol = 0;
char **pazResult;
const int trx_cnt = 1;
int trx_idx;
for (trx_idx = 0; trx_idx < 1; trx_idx++) {
for (i = 0; i < test_cnt; i++) {
j = random(6);
len_left = len_zSql;
tmp = zSql;
if (j < 4) {
int uid = id[j] + random(300);
rc_copy = snprintf(tmp, len_left, "insert into tbl 
values(\"%s%c\",%d,\"%s%c\");",
orgName[j], append[random(26)], uid, nickName[j], append[random(26)]);
//printf("%s\n", zSql);
rc = sqlite3_exec(db, zSql, NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
if (rc != SQLITE_CONSTRAINT) {
printf("error %s: %s\n", zSql, errMsg);
continue;
}
else {
rc_copy = snprintf(tmp, len_left, "update tbl set name=\"%s%c\" where uid=%d\n",
orgName[j], append[random(26)], uid);
rc = sqlite3_exec(db, zSql, NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("error %s: %s\n", zSql, errMsg);
continue;
}
}
}
}
else {
rc_copy = snprintf(zSql, len_zSql, "select * from tbl where uid=%d;", id[j]);
rc = sqlite3_get_table(db, zSql, , , , );
if (nRow == 0) {
rc_copy = snprintf(tmp, len_left, "insert into tbl(name, uid, nick) 
values(\"%s\",%d,\"%s\");",
orgName[j], id[j], nickName[j]);
}
else {
rc_copy = snprintf(zSql, len_zSql, "update tbl set name=\"%s\" where uid=%d;",
orgName[j], id[j]);
}
rc = sqlite3_exec(db, zSql, NULL, NULL, );
if (rc != SQLITE_OK& != SQLITE_DONE) {
printf("error %s: %s\n", zSql, errMsg);
continue;
}
}
}
printf("insert 300 records\n");
sqlite3_sleep(200);
}
}


unsigned __stdcall writeFunc(void *pIn) {
fts5_prefix_match_test();
printf("write over\n");
return 0;
}
unsigned __stdcall readFunc(void *pIn) {
sqlite3 *db;
int i, j, rc;
char *errMsg;
char **pazResult;
int nRow, nCol;
sqlite3_sleep(2000);
rc = sqlite3_open("a.db", );
if (rc != SQLITE_OK) {
printf("open db error\n");
return 0;
}
char *zSql = "select rowid, * from tbl_fts where tbl_fts match \'lucy*\';";
while (1) {
rc = sqlite3_get_table(db, zSql, , , , );
if (nRow > 2) {
printf("mis-match\n");
for (i = 0; i <= nRow; i++) {
for (j = 0; j < nCol; j++) {
printf("%s\t", pazResult[i*nCol + j]);
}
printf("\n");
}
return 0;
}
else {
printf("match cnt = %d\n", nRow);
}
sqlite3_free_table(pazResult);
sqlite3_sleep(3000);
}
return 0;
}
#define THREAD_NUM 2

int thread(ThreadFuncType writeFunc, ThreadFuncType readFunc) {
HANDLE handle[THREAD_NUM];
if (writeFunc) {
handle[0] = (HANDLE)_beginthreadex(NULL, 0, writeFunc, NULL, 0, NULL);
//WaitForMultipleObjects(1, handle, TRUE, INFINITE);
}
if (readFunc) {
for (int i = 1; i < THREAD_NUM; i++) {
handle[i] = (HANDLE)_beginthreadex(NULL, 0, readFunc, NULL, 

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

[Richard Hipp]

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 noticed.  Thanks for the
report.  The process has now been restarted.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
essentially the same way. i.e. if you insert a row and do not specifically
assign a value to the pk (i.e. you assign NULL) the value assigned will
usually be one more than last pk used.

if you have an INT primary key and add a new row with no value assigned to
the PK then null will be stored (all null values are treated as unique in
SQLite and so as far as the PK is concerned all rows are different).

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (id int primary key, data text);
sqlite> insert into test (data) values('row 1');
sqlite> insert into test (data) values('row 2');
sqlite> select id, data from test;
  |row 1
  |row 2

of course the rowid is still there hidden behind the scenes and you can
access it with

sqlite> select rowid, id, data from test;
1|  |row 1
2|  |row 2

but if you want to use the rowid as the PK then you should probably use an
INTEGER pk so it becomes an alias for the rowid in the first place.

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 20 March 2018 at 16:44, Chris Locke  wrote:

> >  some people seem to think that an int primary key can be auto
> incrementing, it can't
>
> But it works in the same way  sort of.  Its auto incrementing, with the
> caveat that if the last row is deleted, the previous number will be used
> again.  Depending on the database schema, this may or may not cause issues.
>
>
> Thanks,
> Chris
>
>
> On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
> >  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.
> >
> >
> > SQLite version 3.18.0 2017-03-28 18:48:43
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table test (id integer primary key autoincrement);
> > sqlite> create table test2 (id int primary key autoincrement);
> > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > sqlite>
> >
> > 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 20 March 2018 at 08:48, R Smith  wrote:
> >
> > >
> > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > >
> > >> Autoincrement can ONLY be used with an integer primary key
> > >>
> > >
> > > I think Peter's shouting is more about the inability to distinguish via
> > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> both
> > > of which are of course integer and can be auto-incrementing, but only
> one
> > > of which is an alias for rowid.
> > >
> > >
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Domingo Alvarez Duarte]

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 !

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

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 2018 22:45
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

Thank you for the quick reply Hick.  I've implemented your script file in C 
since I'm running this application in the embedded world with no OS.  I don't 
see a .describe in the SQLite documentation.  I've tried to use .schema but 
that returns an error.  Do you have another suggestion to obtain the table 
information you had in mind?





  From: Hick Gunter 
 To: 'SQLite mailing list' 
 Sent: Tuesday, March 20, 2018 9:04 AM
 Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?

SQLite does not have "query caching". It does have a "page cache" that will 
keep heavily used pages iin memory. There is also the possibility of a 
file-system/os-level cache. To break down the 1.6 seconds required for the 
first query, try executing an sql script. In linux this would be along the 
lines of:

> date; sqlite3 file.db < script.sql;date

With script.sql containing:

Select strftime('%Y-%m-%d %H:%M:%S.%f'); Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); .describe adas_link_geometry Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); .describe idx_adas_link_geometry Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d 
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d %H:%M:%S.%f');

This should give you an idea of where the time is being spent.


-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 2018 16:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] R*Trees query data cached?

Hello -
I have a question on SQLite query data buffering.
I'm successfully using SQLite v3.22.0 on an embedded ARM processor from ST with 
SD card.  The database file size is about 750MB.  The file system is Segger 
emFile FAT32.  I've configured SQLite to use 6MB RAM for heap.  I've done some 
query time benchmarking and found that the very first R*Trees query takes about 
1.6 seconds to complete.  Each successive R*Trees query (same query string with 
slightly different search parameters) takes about 11ms to complete.  Being new 
to SQLite and spatial queries, I'm trying to understand the substantial query 
time differences... does SQLite cache data from each query for future queries? 
The initial query:SELECT LINK_ID, FROM_REF_ELEVATION, TO_REF_ELEVATION FROM 
adas_link_geometry, idx_adas_link_geometry WHERE adas_link_geometry.ROWID = 
idx_adas_link_geometry.id AND minLat > 454760320 AND maxLat < 454800320 AND 
minLong > -1226807072 AND maxLong < -1226767072;

Thanks in advance,Dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dominique Devienne]

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
line utility [1], not the SQLite library itself.
And that CLI executable, unlike SQLite the library itself, directly uses C
I/O and NOT a VFS [4], and in general is not
meant for "outside" reuse (a pity IMHO), so you cannot use it as-is w/o an
OS and shell. But of course internally, that
CLI is also an SQLite "client", so you can always look into the code [5]
and lift the parts you need. --DD

[1] https://sqlite.org/cli.html
[2] https://sqlite.org/cli.html#querying_the_database_schema
[3] not yet documented apparently.
[4] http://www.sqlite.org/vfs.html
[5] https://sqlite.org/src/file/src/shell.c.in
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users