Re: [sqlite] Why this LIMIT

2018-03-11 Thread Cecil Westerhof 
2018-03-11 9:49 GMT+01:00 Clemens Ladisch :

> Cecil Westerhof wrote:
> > I see that in certain older queries I use:
> > LIMIT  (SELECT COUNT(*) FROM TABLE)
> >
> > ​It looks like​ this has no use (limiting the selected records to the
> > number of records there are). Anyone an idea what could be a reason for
> > this?
>
> Trying to put some table data into the cache?
>
> Reading into a fixed-sized array, and protecting against concurrent
> inserts (from inside the same transaction)?
>
> Needing the LIMIT clause for some reason (disabling subquery flattening?)
> but not knowing about "LIMIT -1"?
>

​None ring a bell.

-- 
Cecil Westerhof
___
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?

2018-03-11 Thread csanyipal 
R Smith-2 wrote
> This seems like a whole assignment, and we are not in the habit to do 
> assignments for people,
> 
> BUT, we can get you started down the path.
> 
> You should know how to do all you are asking by simple RDBMS mechanics, 
> except maybe how to initialize a table with all dates and other things 
> pre-populated, so to assist with that
> 
> 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.

I did not even think that you would do the job for me.
However, thank you very much for the path you showed.
That's what I wanted to, to get me started down the path.
Thank you very much!



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

2018-03-11 Thread Simon Slavin 
On 11 Mar 2018, at 12:05pm, Csányi Pál  wrote:

> Columns are: id,date,D,lb,g,c,lp,ld,re
> 
> where D is a Day name in Week,
> lb is the number of the Lesson Block hour,
> g is the grade of a school class,
> c is the name of the school class,
> lp is LessonPlan,
> ld is LessonDiary,
> re is Reminder.

Don't do that.  If a column contains a lesson plan, call it "lessonPlan".  
Don't make up a short form you will forget.

Simon.
___
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?

2018-03-11 Thread R Smith 
This seems like a whole assignment, and we are not in the habit to do 
assignments for people,


BUT, we can get you started down the path.

You should know how to do all you are asking by simple RDBMS mechanics, 
except maybe how to initialize a table with all dates and other things 
pre-populated, so to assist with that


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


PS: There are efficiency improvements possible, but this is more to show 
the method than to be 100% processor friendly.



On 2018/03/11 2:05 PM, Csányi Pál wrote:

Hi,

is it possible to create a table from other tables with the following
conditions?

Note!
My goal is to create that table 'Hours' with a say 362 records and
after that to update those records with UPDATE sql commands. That is,
I don't want to INSERT in that table any more records, but just UPDATE
it's existing records only with new or modified informations.

The conditions are as follows.
The resulting table called Hours should look like this:

Columns are: id,date,D,lb,g,c,lp,ld,re

where D is a Day name in Week,
lb is the number of the Lesson Block hour,
g is the grade of a school class,
c is the name of the school class,
lp is LessonPlan,
ld is LessonDiary,
re is Reminder.

Example records would be like this:

1,2017-9-1,F,1-2,5,b,,,
2,2017-9-1,F,1-2,7,c,,,
3,2017-9-4,M,1-2,7,b,,,
4,2017-9-4,M,1-2,5,a,,,
5,2017-9-5,Tu,1-2,8,c,,,
6,2017-9-5,Tu,1-2,8,b,,,
7,2017-9-6,W,1-2,8,a,,,
8,2017-9-6,W,1-2,7,a,,,
9,2017-9-7,Th,1-2,6,a,,,
10,2017-9-7,Th,1-2,5,c,,,
11,2017-9-8,F,3-4,5,b,,,
12,2017-9-8,F,3-4,7,c,,,

where the last three columns have NULL values so far, because later I
want to fill those with informations by using UPDATE sql command.

So in Hours table one or more records could have the same date, Day
name in Week, Lesson Block number, but could have also same grade, and
same class name. Only id is UNIQUE in these records.

For start I will be happy to get the Hours table with proper date and
Day name in Week values out there.

Which date is proper in the Hours table should be determined by
another table in the database. Let this table be called the
'Semesters'.

However, a date value in Hours table should not be a Sunday date in the Year.

The Semesters table should have the following columns:
id,numberOfSemester, StartDay,EndDay

In a record of Hours table a date should have the Day name in the Week
which it has in the Calendar, but this behavior can be changed by
another two tables in the database; let these be called the
'TeachingSaturdays' and 'Timetable Exceptions'.

The TeachingSaturdays table should have the following columns:
id,date,D

where the value for date column  is definitely a Saturday day date,
and D could be one of these: M, Tu, W, Th, F.

The TimetableExceptions table should have the following columns:
id,date,D

where the value for D column could be one of these: M, Tu, W, Th, F.

Well, I hope I have clearly defined the problem.

Can this Hours table be created with these constraints?



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

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

2018-03-11 Thread Csányi Pál 
Hi,

is it possible to create a table from other tables with the following
conditions?

Note!
My goal is to create that table 'Hours' with a say 362 records and
after that to update those records with UPDATE sql commands. That is,
I don't want to INSERT in that table any more records, but just UPDATE
it's existing records only with new or modified informations.

The conditions are as follows.
The resulting table called Hours should look like this:

Columns are: id,date,D,lb,g,c,lp,ld,re

where D is a Day name in Week,
lb is the number of the Lesson Block hour,
g is the grade of a school class,
c is the name of the school class,
lp is LessonPlan,
ld is LessonDiary,
re is Reminder.

Example records would be like this:

1,2017-9-1,F,1-2,5,b,,,
2,2017-9-1,F,1-2,7,c,,,
3,2017-9-4,M,1-2,7,b,,,
4,2017-9-4,M,1-2,5,a,,,
5,2017-9-5,Tu,1-2,8,c,,,
6,2017-9-5,Tu,1-2,8,b,,,
7,2017-9-6,W,1-2,8,a,,,
8,2017-9-6,W,1-2,7,a,,,
9,2017-9-7,Th,1-2,6,a,,,
10,2017-9-7,Th,1-2,5,c,,,
11,2017-9-8,F,3-4,5,b,,,
12,2017-9-8,F,3-4,7,c,,,

where the last three columns have NULL values so far, because later I
want to fill those with informations by using UPDATE sql command.

So in Hours table one or more records could have the same date, Day
name in Week, Lesson Block number, but could have also same grade, and
same class name. Only id is UNIQUE in these records.

For start I will be happy to get the Hours table with proper date and
Day name in Week values out there.

Which date is proper in the Hours table should be determined by
another table in the database. Let this table be called the
'Semesters'.

However, a date value in Hours table should not be a Sunday date in the Year.

The Semesters table should have the following columns:
id,numberOfSemester, StartDay,EndDay

In a record of Hours table a date should have the Day name in the Week
which it has in the Calendar, but this behavior can be changed by
another two tables in the database; let these be called the
'TeachingSaturdays' and 'Timetable Exceptions'.

The TeachingSaturdays table should have the following columns:
id,date,D

where the value for date column  is definitely a Saturday day date,
and D could be one of these: M, Tu, W, Th, F.

The TimetableExceptions table should have the following columns:
id,date,D

where the value for D column could be one of these: M, Tu, W, Th, F.

Well, I hope I have clearly defined the problem.

Can this Hours table be created with these constraints?

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

Re: [sqlite] Need two ORDER two times when using RANDOM

2018-03-11 Thread R Smith 
There is nothing "stable" about the randomizer (at least not if it does 
its work well).


In your queries you are simply seeing 2 different orderings with the 
values accompanying the "randomized" order being linked to two different 
random sets. To put it another way, the "last" order-by is not so much 
redundant as it is the ONLY one that counts and the first order-by 
(inside the sub-1uery) causes the sub-query to be "made stable" while it 
can still have rows where the random value and the random order are 
calculating to two different values so the order doesn't seem to hold to 
the value (as it shouldn't). Once this sub-query has been "remembered" 
though, any next order-by will do the job just fine because now values 
do not get re-calculated anymore, they just get recalled from flat memory.


The only reason your two "randomiser" references in the outer query 
shows the same value is that they are using the same alias in a single 
row, which provides the Query Planner an easy reference to just 
duplicate while inside the same single row in the cursor, but the 
calculation for the ORDER BY clause happens at another time and place, 
and so there is no re-referencing of the value that used to be referred 
to by "randomiser" way back when. Alias != Variable.



Hope that is semi-clear...
Ryan

On 2018/03/11 9:52 AM, Cecil Westerhof wrote:

I have the following query:
SELECT *
,  randomiser
,  randomiser
FROM   (
 SELECT *
 ,  ABS(RANDOM()) / 5E17 AS randomiser
 FROM   proverbs
 WHERE  used <> 'notUsed'
 ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC

And this gives for example:
"Voor niets gaat de zon op.""2017-01-12""0.337325790117148"
"0.337325790117148""0.337325790117148"
"Met de wolven in het bos meehuilen.""2017-01-11"
"2.59601454335206""2.59601454335206""2.59601454335206"
"Als katten muizen,
mauwen ze niet.""2017-01-11""2.7932230420896"
"2.7932230420896""2.7932230420896"
"Uitstel is afstel.""2017-01-12""1.88933779146209"
"1.88933779146209""1.88933779146209"
"Het is rozengeur en maneschijn.""2017-01-13""1.16363975452034"
"1.16363975452034""1.16363975452034"

But when I remove the last ORDER (which seems redundant) I get:
"Het is rozengeur en maneschijn.""2017-01-13""3.0795495790489"
"2.7886449148631""6.78717082169993"
"Met de kippen op stok.""2017-01-16""6.56483737827297"
"1.32007069440753""5.18920985400017"
"Schoenmaker,
blijf bij je leest.""2017-01-12""8.17180081902947"
"5.91518750003302""4.78956808218011"
"Een goed begin is het halve werk.""2017-01-17"
"7.70627730482033""15.580638189131""2.0146022387495"
"Zoals het klokje thuis tikt,
tikt het nergens.""2017-01-18""8.53059705262686"
"15.7307229942""1.1516802288132"

So randomiser is not stable any-more.


For the moment I work with:
SELECT   *
,randomiser
,randomiser
FROM   (
 SELECT   *
 ,ABS(RANDOM()) / 5E17 AS randomiser
 FROM proverbs
 WHEREused <> 'notUsed'
 ORDER BY used ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC

​Then randomiser is stable and the time needed is only slightly more as
time for the second query. (It is about 43, 53 and 46 ms.)​



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

Re: [sqlite] Why this LIMIT

2018-03-11 Thread Clemens Ladisch 
Cecil Westerhof wrote:
> I see that in certain older queries I use:
> LIMIT  (SELECT COUNT(*) FROM TABLE)
>
> ​It looks like​ this has no use (limiting the selected records to the
> number of records there are). Anyone an idea what could be a reason for
> this?

Trying to put some table data into the cache?

Reading into a fixed-sized array, and protecting against concurrent
inserts (from inside the same transaction)?

Needing the LIMIT clause for some reason (disabling subquery flattening?)
but not knowing about "LIMIT -1"?


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