Re: [sqlite] Determine sort order of query

[Keith Medcalf]

Hint:  Index entries must be unique.  They are made unique by having the rowid 
in the index (how else would you find the row from the index)?

if you asked for the data in an order that can be produced by an index without 
a sort, then you will get the output without a sort.

select * from t1, t2 order by b, t1.rowid, d, t2.rowid;

Index b can be used to get the initial column sorted, but a separate sorter is 
required to get the order you asked for, for each subgroup (next bunch of 
columns).


---
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 x
>Sent: Saturday, 24 March, 2018 13:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Determine sort order of query
>
>sqlite> create table t1(a, b text);
>sqlite> create table t2(c, d text);
>sqlite> create index b on t1(b);
>sqlite> create index d on t2(d);
>sqlite> explain query plan select * from t1,t2 order by b, d,
>t1.RowID, t2.RowID;
>0|0|0|SCAN TABLE t1 USING INDEX b
>0|1|1|SCAN TABLE t2
>0|0|0|USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
>
>Is there a reason it uses TEMP B-TREE rather than index t2(d) ?
>
>___
>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] Determine sort order of query

[Keith Medcalf]

That index will not result in the ordering you asked for.


---
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 x
>Sent: Saturday, 24 March, 2018 13:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Determine sort order of query
>
>sqlite> create table t1(a, b text);
>sqlite> create table t2(c, d text);
>sqlite> create index b on t1(b);
>sqlite> create index d on t2(d);
>sqlite> explain query plan select * from t1,t2 order by b, d,
>t1.RowID, t2.RowID;
>0|0|0|SCAN TABLE t1 USING INDEX b
>0|1|1|SCAN TABLE t2
>0|0|0|USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
>
>Is there a reason it uses TEMP B-TREE rather than index t2(d) ?
>
>___
>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] Determine sort order of query

[x]

sqlite> create table t1(a, b text);
sqlite> create table t2(c, d text);
sqlite> create index b on t1(b);
sqlite> create index d on t2(d);
sqlite> explain query plan select * from t1,t2 order by b, d, t1.RowID, 
t2.RowID;
0|0|0|SCAN TABLE t1 USING INDEX b
0|1|1|SCAN TABLE t2
0|0|0|USE TEMP B-TREE FOR RIGHT PART OF ORDER BY

Is there a reason it uses TEMP B-TREE rather than index t2(d) ?

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

[sqlite] Android SQLite DB app with forms etc.

[Bart Smissaert]

Looking for an android DB phone app (free) based on SQLite with
configurable forms and buttons
to setup a DB app. Is there such a thing? Not found it yet.
I could develop such an app with something like B4A:
https://www.b4x.com/b4a.html
but that will take quite a bit of time.
Thanks for any suggestions.

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

Re: [sqlite] Determine sort order of query

[x]

Simon, assume the sql will be input by the user. I suppose I could give them an 
error message but it would be good to just add the ‘calculated’ order by to the 
sql and display that.




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Saturday, March 24, 2018 6:04:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] Determine sort order of query

On 24 Mar 2018, at 6:00pm, x  wrote:

> Thanks for the reply Richard. I don’t suppose it matters to me that it may 
> change from one day to the next or one connection to another as long as I can 
> determine the order it will be returned in milliseconds before the first 
> sqlite3_step.

Can't be done without putting enough analysis in your program to completely 
understand the output of EXPLAIN QUERY PLAN.  And the output of that statement 
does not have a fixed form: lines can go up or down, ANALYZE can change what 
happens, whether a table is empty or has data can change what happens.

If you want to be able to predict the order of results from a SELECT, set it 
using ORDER BY.

Simon.
___
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] Determine sort order of query

[Richard Hipp]

On 3/24/18, x  wrote:
> Thanks for the reply Richard. I don’t suppose it matters to me that it may
> change from one day to the next or one connection to another as long as I
> can determine the order it will be returned in milliseconds before the first
> sqlite3_step.

The only practical way to determine the order is to run it and see.
-- 
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] Determine sort order of query

[Simon Slavin]

On 24 Mar 2018, at 6:00pm, x  wrote:

> Thanks for the reply Richard. I don’t suppose it matters to me that it may 
> change from one day to the next or one connection to another as long as I can 
> determine the order it will be returned in milliseconds before the first 
> sqlite3_step.

Can't be done without putting enough analysis in your program to completely 
understand the output of EXPLAIN QUERY PLAN.  And the output of that statement 
does not have a fixed form: lines can go up or down, ANALYZE can change what 
happens, whether a table is empty or has data can change what happens.

If you want to be able to predict the order of results from a SELECT, set it 
using ORDER BY.

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

Re: [sqlite] Determine sort order of query

[x]

I should have pointed out that I intended to append the ‘calculated’ order by 
to the sql string in any case.




From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Saturday, March 24, 2018 5:38:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] Determine sort order of query

On 3/24/18, x  wrote:
> Suppose you’re given an sql select statement that doesn’t contain an order
> by clause. Is there any way of accurately determining the order it will be
> sorted in.

No.  The database engine is free to return the rows in whatever order
it thinks will be most efficient.  This can change from one run to the
next, such that you can run the same query twice and get the rows in a
different order each time.  SQLite will normally return rows in the
same order unless there is an intervening ANALYZE command, but on
other SQL database engines a row order shift can happen at any time.
You can expect to sometimes see rows  in different orders if you run
the same query on two different versions of SQLite.

--
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determine sort order of query

[x]

Thanks for the reply Richard. I don’t suppose it matters to me that it may 
change from one day to the next or one connection to another as long as I can 
determine the order it will be returned in milliseconds before the first 
sqlite3_step.






From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Saturday, March 24, 2018 5:38:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] Determine sort order of query

On 3/24/18, x  wrote:
> Suppose you’re given an sql select statement that doesn’t contain an order
> by clause. Is there any way of accurately determining the order it will be
> sorted in.

No.  The database engine is free to return the rows in whatever order
it thinks will be most efficient.  This can change from one run to the
next, such that you can run the same query twice and get the rows in a
different order each time.  SQLite will normally return rows in the
same order unless there is an intervening ANALYZE command, but on
other SQL database engines a row order shift can happen at any time.
You can expect to sometimes see rows  in different orders if you run
the same query on two different versions of SQLite.

--
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determine sort order of query

[Richard Hipp]

On 3/24/18, x  wrote:
> Suppose you’re given an sql select statement that doesn’t contain an order
> by clause. Is there any way of accurately determining the order it will be
> sorted in.

No.  The database engine is free to return the rows in whatever order
it thinks will be most efficient.  This can change from one run to the
next, such that you can run the same query twice and get the rows in a
different order each time.  SQLite will normally return rows in the
same order unless there is an intervening ANALYZE command, but on
other SQL database engines a row order shift can happen at any time.
You can expect to sometimes see rows  in different orders if you run
the same query on two different versions of SQLite.

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

[sqlite] Determine sort order of query

[x]

Suppose you’re given an sql select statement that doesn’t contain an order by 
clause. Is there any way of accurately determining the order it will be sorted 
in. Trivial example

‘select * from Tbl’ will be ordered by RowID or some covering index if one 
exists.

I know the EXPLAIN (maybe even EXPLAIN QUERY PLAN) give hints but I’ve not used 
these enough to be sure of them.

e.g. I think the EXPLAIN ‘order’ column corresponds to the order the ‘from’ 
tables are accessed in. Would that order along with the index mentioned (none 
mentioned => RowID for with rowID tables) give the order?

If the bottom row in the EXPLAIN is ‘USE TEMP B-TREE FOR ORDER BY’ does that 
definitely mean there’s no indexes used?

If the bottom row in the EXPLAIN is ‘USE TEMP B-TREE FOR RIGHT PART OF ORDER 
BY’ does that imply the sort isn’t unique?

I know the output of EXPLAIN isn’t guaranteed and shouldn’t really be used so 
is there an alternative?

Tom.

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

Re: [sqlite] non-returned column aliases for repeating expressions?

[Don V Nielsen]

I'm not the sharpest tool in the shed, here, but a couple of observations /
ideas. First, why in the ORDER clause use _3 and then "id" instead of _1?
Using one alias and not the other is inconsistent and could be confusing.

Personally, I would have did the following. Acquire the data using the CTE.
The select what is needed and order it from the CTE. Being script
generated, I find it really easy to create the primary select as a template
and insert the select within the CTE.

WITH data AS (
SELECT
  "id"AS _1,
  "json"  AS _2,
  json_extract(json, '$.foo') AS _3
FROM "testing"
)
SELECT *
FROM data
where _3 < 50
ORDER BY _3 DESC, _1


I read a lot about efficiency with sqlite here. I guess I've become
complacent about it. I find sqlite so fast that efficiency is distracting.
Just me.

dvn

On Sat, Mar 24, 2018 at 11:31 AM, Wout Mertens 
wrote:

> Hi list,
>
> I often have (autogenerated) queries like
>
> SELECT "id" AS _1,"json" AS _2 FROM "testing"
> WHERE json_extract(json, '$.foo') < 50
> ORDER BY json_extract(json, '$.foo') DESC,"id"
>
> where the json_extract(json, '$.foo') is indexed
>
> I wonder if it would be more efficient to write instead
>
> SELECT _1, _2 FROM (
> SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
> "testing"
> WHERE _3 < 50
> ORDER BY _3 DESC,"id")
>
> , so aliasing the common expression? Or is that just extra work for SQLite?
>
> If I did this, I would want to do it always, also when I'm using the
> expression only once (so only ordering or only filtering)
>
> Wout.
> ___
> 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] Beta-testing success story! Was: The upcoming 3.23.0 release

[Olivier Mascia]

> Le 24 mars 2018 à 14:35, Richard Hipp  a écrit :
> 
> A new pre-release snapshot with this bug fixed has now been uploaded
> to the https://sqlite.org/download.html page.  Please, everybody,
> continue testing!

If I'm not mistaken, a benign warning just appeared on line 185432 of sqlite3.c 
(amalgamation from sqlite-snapshot-201803241324.tar.gz, using Visual Studio 
2017 compiler: "warning C4267: 'function': conversion from 'size_t' to 'int', 
possible loss of data". This is the usual one related to strlen returning 
size_t not int.

Both previous warnings about chmod/_chmod and unlink/_unlink have been taken 
care of: thanks!

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

[sqlite] non-returned column aliases for repeating expressions?

[Wout Mertens]

Hi list,

I often have (autogenerated) queries like

SELECT "id" AS _1,"json" AS _2 FROM "testing"
WHERE json_extract(json, '$.foo') < 50
ORDER BY json_extract(json, '$.foo') DESC,"id"

where the json_extract(json, '$.foo') is indexed

I wonder if it would be more efficient to write instead

SELECT _1, _2 FROM (
SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
"testing"
WHERE _3 < 50
ORDER BY _3 DESC,"id")

, so aliasing the common expression? Or is that just extra work for SQLite?

If I did this, I would want to do it always, also when I'm using the
expression only once (so only ordering or only filtering)

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

[sqlite] Beta-testing success story! Was: The upcoming 3.23.0 release

[Richard Hipp]

Thanks to E.Pasma for finding a logic error in the new LEFT JOIN
strength reduction optimization!

A new pre-release snapshot with this bug fixed has now been uploaded
to the https://sqlite.org/download.html page.  Please, everybody,
continue testing!

The LEFT JOIN strength reduction optimization changes a LEFT JOIN into
an ordinary JOIN if any column of the right table of the LEFT JOIN is
used in a way that requires the value to be non-NULL.  The prover that
checks this condition was mistakenly assuming that a CASE expression
that contained a NULL value would always have a NULL answer.  This is
obviously wrong when you think about it for half a second, but I
missed this case when coding up the prover.  E.Pasma found a query
that exercises that case, however.

A simplified example:

CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,2),(3,4);
CREATE TABLE t2(x);
SELECT *
  FROM t1 LEFT JOIN t2
 WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;

Needless to say, new test cases have been added to SQLite's test
suites to make sure this particular mistake never happens again.  But
there are plenty of other potential mistakes out there, so do not
slack up on your testing, please!

On 3/24/18, E.Pasma  wrote:
> Dear Richard, the rather complicated script below used to print a tree:
>
> node1|node2|node3|node4|node5
> 1
> 1|11|||
> 1|12|||
> 1|12|121||
> 1|13|||
>
> and with yesterday's pre-release snapshot it does not yield any result.
> A clue is this depends on the size of the query, not so much on the
> logic.
> If you reduce the number of anticipated levels, by leaving out the
> last four lines, the output is alright.
> Hope this causes no headache, E. Pasma
>
> .version
> SQLite 3.23.0 2018-03-22 12:00:43
> dd568c27b1d7656388ea5b4132cc0265aedd7348d265d8e8c7412b00b28a31aa
> zlib version 1.2.3
> gcc-4.0.1 (Apple Inc. build 5465)
>
> create table node (node integer primary key, parent integer)
> ;
> insert into node values (1,0),(11,1),(12,1),(13,1),(121,12)
> ;
> create index node_parent on node (parent)
> ;
> create table bit (bit integer primary key)
> ;
> insert into bit values(0),(1)
> ;
> SELECT  node1, node2, node3, node4, node5
> /* level 1 is root */
> FROM(SELECT node AS node1 FROM node WHERE parent=0)
> /* level 2 */
> JOIN(SELECT bit AS bit1 FROM bit)
> LEFT JOIN (SELECT node AS node2, parent AS parent2 FROM node)
> ON  bit1 AND parent2=node1
> /* level 3 */
> JOIN(SELECT bit AS bit2 FROM bit)
> ON  bit2<=CASE WHEN bit1 THEN CASE WHEN node2 THEN 1 ELSE -1 END
> ELSE 0 END
> LEFT JOIN (SELECT node AS node3, parent AS parent3 FROM node)
> ON  bit2 AND parent3=node2
> /* level 4 */
> JOIN(SELECT bit AS bit3 FROM bit)
> ON  bit3<=CASE WHEN bit2 THEN CASE WHEN node3 THEN 1 ELSE -1 END
> ELSE 0 END
> LEFT JOIN (SELECT node AS node4, parent AS parent4 FROM node)
> ON  bit3 AND parent4=node3
> /* level 5 */
> JOIN(SELECT bit AS bit4 FROM bit)
> ON  bit4<=CASE WHEN bit3 THEN CASE WHEN node4 THEN 1 ELSE -1 END
> ELSE 0 END
> LEFT JOIN (SELECT node AS node5, parent AS parent5 FROM node)
> ON  bit4 AND parent5=node4
> ;
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[petern]

Peter, is "INSTEAD OF" trigger not available on your version of SQLite?

https://sqlite.org/lang_createtrigger.html#instead_of_trigger

CREATE VIEW mytable_UPSERT AS SELECT * FROM mytable;
CREATE TRIGGER mytable_UPSERT INSTEAD OF INSERT ON mytable_UPSERT BEGIN
-->INSERT OR IGNORE ... ;
-->UPDATE  ;
END;

INSERT INTO mytable_UPSERT 

Peter

On Thu, Mar 22, 2018 at 12:18 PM, Peter Michaux 
wrote:

> I think there are a couple main offenders with
>
> > BEGIN;
> > INSERT OR IGNORE ... ;
> > UPDATE  ;
> > COMMIT;
>
> The first is that it is bulky. If this is in the application code then it
> has to be repeated for each desired UPSERT and it has to be repeated in the
> code of each application that uses the database.
>
> The second is that it seems so inefficient in the case of a new row being
> inserted. The row is inserted and then immediately updated. Why do both
> operations when only one is needed?
>
> Is it possible to write a stored procedure that checks a result of the
> INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
> That would at least move the bulky code out of the application and into the
> database. Also it seems it would be more efficient.
>
> Thanks.
>
> Peter
>
>
> Peter
>
>
>
> On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt  wrote:
>
> > 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
> >
> ___
> 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] Is it possible to CREATE TABLE from other tables in a complex way?

[Csányi Pál]

2018-03-23 21:52 GMT+01:00 David Raymond :
> This gets a little ugly. Was stuck for a while wondering what the heck was 
> going on until I found out that the modulo operator can return negatives. 
> Which makes absolutely no sense coming from someone who was a math major, but 
> hey, now I know. It's also fun that %w "day of week" goes Sunday to Sunday, 
> but %W "week of year" goes Monday to Monday (And then your school weeks go 
> Friday to Friday)
>
> Tables and fields have been renamed slightly from the previous emails. The 4 
> tables I'm putting here are all static, so you can put them at the front of 
> your CTE with values clauses if you really, really want to make it one big 
> select statement from no tables.

Thank you very much.
I was already thinking of using static tables.

I modify your code a little: we call school years like this:
'2016/2017', '2017/2018' and so on.
Then I run it and then I realized there were breaks and holidays in a
school year.
Those days of holidays and breaks should be excluded from the result records.
Moreover, there are teaching Saturdays too, when on a Saturday we must
to take Lessons. Eg. on 2018-04-14 we must go to school and take
Lessons according to the Friday timetable. These teaching Saturdays
should be added to the result with UNION clause.

So I created more tables and now I have the Lessons.db with these clauses:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE SchoolYearDates
(
SchoolYear text NOT NULL PRIMARY KEY COLLATE nocase,
StartDate text NOT NULL COLLATE nocase
CHECK (date(StartDate) IS NOT NULL ),
EndDate text NOT NULL COLLATE nocase
CHECK (date(EndDate) IS NOT NULL )
);
INSERT INTO SchoolYearDates VALUES('2017/2018','2017-09-01','2018-06-14');
CREATE TABLE TeachingSaturdays
(
SaturdayDate text NOT NULL COLLATE nocase
CHECK (date(SaturdayDate) IS NOT NULL ),
TimeTableDay text NOT NULL COLLATE nocase
);
INSERT INTO TeachingSaturdays VALUES ('2018-04-14','F');
INSERT INTO TeachingSaturdays VALUES ('2018-05-05','M');
CREATE TABLE SchoolVacations
(
VacationName text NOT NULL COLLATE nocase,
StartDate text NOT NULL COLLATE nocase
CHECK (date(StartDate) IS NOT NULL ),
EndDate text NOT NULL COLLATE nocase
CHECK (date(EndDate) IS NOT NULL )
);
INSERT INTO SchoolVacations VALUES ('Winter Break','2017-12-25','2018-01-09');
INSERT INTO SchoolVacations VALUES ('Spring Break','2018-03-30','2018-04-09');
INSERT INTO SchoolVacations VALUES ('Summer Break','2018-06-15','2018-08-31');
CREATE TABLE HoliDays
(
HoliDayName text NOT NULL COLLATE nocase,
StartDate text NOT NULL COLLATE nocase
CHECK (date(StartDate) IS NOT NULL ),
EndDate text NOT NULL COLLATE nocase
CHECK (date(EndDate) IS NOT NULL )
);
INSERT INTO HoliDays VALUES ('Name 1','2017-10-21','2017-10-21');
INSERT INTO HoliDays VALUES ('Name 2','2017-11-11','2017-11-11');
INSERT INTO HoliDays VALUES ('Name 4','2018-02-15','2018-02-16');
INSERT INTO HoliDays VALUES ('Name 3','2018-02-27','2018-02-27');
INSERT INTO HoliDays VALUES ('Name 5','2018-04-22','2018-04-22');
INSERT INTO HoliDays VALUES ('Name 6','2018-05-01','2018-05-02');
CREATE TABLE DaysOfWeek

(
DayID integer NOT NULL PRIMARY KEY ,
DayAbbrev text NOT NULL COLLATE nocase,
DayName text NOT NULL COLLATE nocase
);
INSERT INTO DaysOfWeek VALUES(0,'Su','Sunday');
INSERT INTO DaysOfWeek VALUES(1,'M','Monday');
INSERT INTO DaysOfWeek VALUES(2,'Tu','Tuesday');
INSERT INTO DaysOfWeek VALUES(3,'W','Wednesday');
INSERT INTO DaysOfWeek VALUES(4,'Th','Thursday');
INSERT INTO DaysOfWeek VALUES(5,'F','Friday');
INSERT INTO DaysOfWeek VALUES(6,'Sa','Saturday');
CREATE TABLE LessonBlocks
(
WeekNumber integer NOT NULL PRIMARY KEY ,
LessonBlock text NOT NULL COLLATE nocase
);
INSERT INTO LessonBlocks VALUES(1,'1-2');
INSERT INTO LessonBlocks VALUES(2,'3-4');
INSERT INTO LessonBlocks VALUES(3,'5-6');
INSERT INTO LessonBlocks VALUES(4,'7-8');
INSERT INTO LessonBlocks VALUES(5,'9-10');
INSERT INTO LessonBlocks VALUES(6,'11-12');
INSERT INTO LessonBlocks VALUES(7,'13-14');
INSERT INTO LessonBlocks VALUES(8,'15-16');
INSERT INTO LessonBlocks VALUES(9,'17-18');
INSERT INTO LessonBlocks VALUES(10,'19-20');
INSERT INTO LessonBlocks VALUES(11,'21-22');
INSERT INTO LessonBlocks VALUES(12,'23-24');
INSERT INTO LessonBlocks VALUES(13,'25-26');
INSERT INTO LessonBlocks VALUES(14,'27-28');
INSERT INTO LessonBlocks VALUES(15,'29-30');
INSERT INTO LessonBlocks VALUES(16,'31-32');
INSERT INTO LessonBlocks VALUES(17,'33-34');
INSERT INTO LessonBlocks VALUES(18,'35-36');
INSERT INTO LessonBlocks VALUES(19,'37-38');
INSERT INTO LessonBlocks VALUES(20,'39-40');
INSERT INTO LessonBlocks VALUES(21,'41-42');
INSERT INTO LessonBlocks VALUES(22,'43-44');
INSERT INTO LessonBlocks VALUES(23,'45-46');
INSERT INTO LessonBlocks VALUES(24,'47-48');
INSERT INTO LessonBlocks VALUES(25,'49-50');
INSERT INTO LessonBlocks VALUES(26,'51-52');
INSERT 

Re: [sqlite] The upcoming 3.23.0 release

[E . Pasma]

Dear Richard, the rather complicated script below used to print a tree:

node1|node2|node3|node4|node5
1
1|11|||
1|12|||
1|12|121||
1|13|||

and with yesterday's pre-release snapshot it does not yield any result.
A clue is this depends on the size of the query, not so much on the  
logic.
If you reduce the number of anticipated levels, by leaving out the  
last four lines, the output is alright.

Hope this causes no headache, E. Pasma

.version
SQLite 3.23.0 2018-03-22 12:00:43  
dd568c27b1d7656388ea5b4132cc0265aedd7348d265d8e8c7412b00b28a31aa

zlib version 1.2.3
gcc-4.0.1 (Apple Inc. build 5465)

create table node (node integer primary key, parent integer)
;
insert into node values (1,0),(11,1),(12,1),(13,1),(121,12)
;
create index node_parent on node (parent)
;
create table bit (bit integer primary key)
;
insert into bit values(0),(1)
;
SELECT  node1, node2, node3, node4, node5
/* level 1 is root */
FROM(SELECT node AS node1 FROM node WHERE parent=0)
/* level 2 */
JOIN(SELECT bit AS bit1 FROM bit)
LEFT JOIN (SELECT node AS node2, parent AS parent2 FROM node)
ON  bit1 AND parent2=node1
/* level 3 */
JOIN(SELECT bit AS bit2 FROM bit)
ON  bit2<=CASE WHEN bit1 THEN CASE WHEN node2 THEN 1 ELSE -1 END  
ELSE 0 END

LEFT JOIN (SELECT node AS node3, parent AS parent3 FROM node)
ON  bit2 AND parent3=node2
/* level 4 */
JOIN(SELECT bit AS bit3 FROM bit)
ON  bit3<=CASE WHEN bit2 THEN CASE WHEN node3 THEN 1 ELSE -1 END  
ELSE 0 END

LEFT JOIN (SELECT node AS node4, parent AS parent4 FROM node)
ON  bit3 AND parent4=node3
/* level 5 */
JOIN(SELECT bit AS bit4 FROM bit)
ON  bit4<=CASE WHEN bit3 THEN CASE WHEN node4 THEN 1 ELSE -1 END  
ELSE 0 END

LEFT JOIN (SELECT node AS node5, parent AS parent5 FROM node)
ON  bit4 AND parent5=node4
;

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