Re: [sqlite] Why trigger (UPDATE) does not work?

[Csányi Pál]

On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote:
> On 2018/08/26 2:16 PM, Csányi Pál wrote:
> >
> >>> When the trigger is fired, those numbers are not updated in the
> >>> corresponding fields of the corresponding table.
> >>> To be more precise, the AllYearsMonthsDays trigger does not work.
> >>> The YearsRemainingMonthsDays trigger works.
> >>>
> >>> What am I missing here?
> >>>
> >>> --
> >>> Best, Pali
> 
> Many things are wrong with those triggers.
> They can't use CTE's, the CASE statements evaluate equations, not 
> values, so they have Boolean results, and one desperate space is missing 
> after a union all statement, (to name the obvious ones) but then more 
> hurtful to my OCD is the repetitive adding of CTEs, each of which 
> evaluates 3 values of which, on every iteration, only 1 value is updated 
> in the table - not to mention that the table is pressed into service as 
> a VIEW, and the very expensive ON UPDATE trigger is filling in the table 
> values that would be much more salient and simple with a VIEW.
> 
> Ok, enough about what is wrong with it. Here's how it can be fixed:
> 
> First, get rid of the triggers, completely.
> Then get rid of the columns in the table named: Years, RemainingMonths, 
> and RemainingDays.
> 
> This will leave you with a Table scripted like this (I added Johnny to 
> test future end-of-service dates):
> 
> CREATE TABLE "MyLengthOfService" (
>    "id" INT PRIMARY KEY,
>    "WorkPlaceName" TEXT,
>    "StartDate" DATE,
>    "EndDate" DATE,
>    "WithWorkingTime" INT,
>    "Comment" TEXT
> );
> INSERT INTO "MyLengthOfService" 
> ("id","WorkPlaceName","StartDate","EndDate","WithWorkingTime","Comment") 
> VALUES
>   (1,'Name of the 1. work place','1983-07-11','1984-08-31',1,'workman')
> ,(2,'Name of the 2. work place','1984-11-01','1986-01-15',1,'workman')
> ,(3,'Name of the 3. work place','1986-01-16','1999-07-16',1,'workman')
> ,(4,'Name of the 4. work place','2000-02-01','2000-08-31',1,'teacher')
> ,(5,'Name of the 4. work place','2000-09-01','2001-01-31',0.5,'teacher')
> ,(6,'Name of the 4. work place','2001-02-01','2018-08-26',1,'teacher')
> ,(7,'Johnny','2018-05-01','2019-04-30',1,'workman')
> ;

You have misunderstand the purpose of the MyLengthOfService table.
The MyLengthOfService table contains WorkPlaces not for many persons,
but for one person only.

So my goal is to get summed years, months and days for that one
person. The person has been worked on many places.

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

Re: [sqlite] Why trigger (UPDATE) does not work?

[Csányi Pál]

ateR), max(startDateR,endDateR) from dates union all 
select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years 
+ 1 as text) || ' years'), endDateR from yearsTable where resultDate < endDateR 
), monthsTable (startDateR, years, months, days, resultDate, endDateR) as ( 
select * from ( select * from yearsTable where resultDate <= endDateR order by 
years desc, months desc, days desc limit 1) union all select startDateR, years, 
months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+' 
|| cast(months + 1 as text) || ' months'), endDateR from monthsTable where 
resultDate < endDateR ), daysTable (startDateR, years, months, days, 
resultDate, endDateR) as ( select * from( select * from monthsTable where 
resultDate <= endDateR order by years desc, months desc, days desc limit 1) 
union all select startDateR, years, months, days + 1, date(startDateR, '+' || 
cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' 
|| cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate 
< endDateR ) select  months from daysTable where resultDate = endDateR ), 
RemainingDays = (  with recursive dates (startDateR, endDateR) as (SELECT 
date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate = 
NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate, 
endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR, 
endDateR), max(startDateR, endDateR) from dates union all select startDateR, 
years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) ||' 
years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable 
(startDateR, years, months, days, resultDate, endDateR) as ( select * from ( 
select * from yearsTable where resultDate <= endDateR order by years desc, 
months desc, days desc limit 1) union all select startDateR, years, months + 1, 
days, date(startDateR, '+' || cast(years as text) || ' years', '+' || 
cast(months + 1 as text) || ' months'), endDateR from monthsTable where 
resultDate < endDateR ), daysTable (startDateR, years, months, days, 
resultDate, endDateR) as ( select * from( select * from monthsTable where 
resultDate <= endDateR order by years desc, months desc, days desc limit 1) 
union all select startDateR, years, months, days + 1, date(startDateR, '+' || 
cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' 
||cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate 
< endDateR ) select  days from daysTable where resultDate = endDateR ) WHERE 
EndDate = NEW.EndDate; END;

The question is: why does not work the AllYearsMonthsDays trigger as I 
expecting?



> Regards.
> 
> Brian P Curley
> 
> 
> On Sun, Aug 26, 2018, 3:41 AM Csányi Pál  wrote:
> 
> > Hi,
> >
> > I have a small database with schema attached in this mail.
> > I have two triggers to update fields after an update occures.
> >
> > When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
> > Editor, it output is that I expect. It gives the numbers of months and
> > days so far:
> > SELECT total(RemainingDays) FROM MyLengthOfService;
> > total(RemainingDays)
> > 63
> >
> > SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
> > total(RemainingDays) % 30
> > 3
> >
> > But even if I run the trigger in SqliteStudio's SQL Editor alone, it
> > does not give the expected output:
> > UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
> > SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
> > RemainingSummedDays = ( SELECT total(RemainingDays) FROM
> > MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
> > total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
> > The output is empty.
> >
> > When the trigger is fired, those numbers are not updated in the
> > corresponding fields of the corresponding table.
> > To be more precise, the AllYearsMonthsDays trigger does not work.
> > The YearsRemainingMonthsDays trigger works.
> >
> > What am I missing here?
> >
> > --
> > Best, Pali

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

[sqlite] Why trigger (UPDATE) does not work?

[Csányi Pál]

Hi,

I have a small database with schema attached in this mail.
I have two triggers to update fields after an update occures.

When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
Editor, it output is that I expect. It gives the numbers of months and
days so far:
SELECT total(RemainingDays) FROM MyLengthOfService;
total(RemainingDays)
63

SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
total(RemainingDays) % 30
3

But even if I run the trigger in SqliteStudio's SQL Editor alone, it
does not give the expected output:
UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
RemainingSummedDays = ( SELECT total(RemainingDays) FROM
MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
The output is empty.

When the trigger is fired, those numbers are not updated in the
corresponding fields of the corresponding table.
To be more precise, the AllYearsMonthsDays trigger does not work.
The YearsRemainingMonthsDays trigger works.

What am I missing here?

--
Best, Pali
Üdvözlettel, Csányi Pál tanár
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can date('now') fire a trigger automatically after a day passed?

[Csányi Pál]

Hi,

I have a column in a table where a value in a row is 'now'.
The 'now' value is in 'EndDate' column of that row.
I have also a trigger which fire up when in the 'EndDate' column a
Field was updated.

Because the 'now' value does changing every day in sense that every
one know that that yesterday's date is not equal with today's date I
am thinking about that that this could be used to fire up that trigger
automatically every day. But this does not work.

Then is there a mechanism which would update automatically such date
value - which is now the 'now' value in that 'EndDate' column Field
after a day passed?

This is needed in my application which calculates service time between
two dates, namely between StartDate and EndDate where EndDate has the
'now' value. 

I hope I was clear what I mean.

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

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

On Mon, Aug 06, 2018 at 05:02:00PM +, David Raymond wrote:
> The whole fencepost thing is probably doing weird things. Here's my take on 
> it.
> This is "time to get from A to B". If you want "Total timespan from A to B 
> inclusive" then just add 1 day.
> 
> Trying to do the +1 day or -1 day in the middle for the fencepost thing is 
> probably what's causing the error you mentioned. With one of the days being 
> the 1st, the -1 day might leave it on the 31st or the 30th or the 28th or the 
> 29th, depending on the start month. Best to just wait to the very end and say 
> "oh yeah, plus the start day"
> 
> 
> with recursive
> dates (startDate, endDate) as (values (:startDate, :endDate)),
> yearsTable (startDate, years, months, days, resultDate, endDate)
> as (
> select min(startDate, endDate),
> 0, 0, 0,
> min(startDate, endDate), max(startDate, endDate)
> from dates
> union all
> select startDate, years + 1, months, days,
> date(startDate,
> '+' || cast(years + 1 as text) || ' years'),
> endDate
> from yearsTable where resultDate < endDate
> ),
> monthsTable (startDate, years, months, days, resultDate, endDate)
> as (
> select * from (
> select * from yearsTable where resultDate <= endDate
> order by years desc, months desc, days desc limit 1)
> union all
> select startDate, years, months + 1, days,
> date(startDate,
> '+' || cast(years as text) || ' years',
> '+' || cast(months + 1 as text) || ' months'),
> endDate
> from monthsTable where resultDate < endDate
> ),
> daysTable (startDate, years, months, days, resultDate, endDate)
> as (
> select * from(
> select * from monthsTable where resultDate <= endDate
> order by years desc, months desc, days desc limit 1)
> union all
> select startDate, years, months, days + 1,
> date(startDate,
> '+' || cast(years as text) || ' years',
> '+' || cast(months as text) || ' months',
> '+' || cast(days + 1 as text) || ' days'),
> endDate
> from daysTable where resultDate < endDate
> )
> select  startDate, years, months, days, resultDate
> from daysTable where resultDate = endDate;
> 
> 
> startDate   years   months  daysresultDate
> --  --  --  --  --
> 2004-02-02  0   0   0   2004-02-02
> 2004-02-02  14  6   1   2018-08-03
> 1983-07-11  1   1   20  1984-08-31
> 1984-11-01  1   2   14  1986-01-15
> 1986-01-16  13  6   0   1999-07-16
> 1970-01-01  48  7   5   2018-08-06
> 
> 
> Leap year
> 
> startDate   years   months  daysresultDate
> --  --  --  --  --
> 2016-02-28  1   0   1   2017-03-01
> 2015-02-28  1   0   2   2016-03-01
> 
> 
> Around the end of a month
> 
> startDate   years   months  daysresultDate
> --  --  --  --  --
> 2018-01-15  0   0   30  2018-02-14
> 2018-02-15  0   0   27  2018-03-14
> 2016-02-15  0   0   28  2016-03-14
> 2018-04-15  0   0   29  2018-05-14

With this WITH statement I get outputs which are such as I expected to be.

Thank you very much!!

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

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

On Sun, Aug 05, 2018 at 01:12:17PM -0600, Keith Medcalf wrote:
> 
> >Some where in the WITH clause above I want to put '+1 day' in the
> >command out there.
>   
> That is because the query does not count the StartDate but does count the 
> EndDate, so if your EndDate is the next day from the StartDate you get 1 day, 
> not two.  You need to move the fencepost created by the StartDate to 
> counteract this (so that, effectively, the starting point becomes the day 
> before the first day, or "day 0") ... This makes the dateY / dateM reflect 
> the date on which, at the completion of that day, the year or month 
> respectively was completed:
> 
> WITH RECURSIVE
>  dates (StartDate, EndDate) as
>(
> select '2004-02-02', '2004-02-02'
>),
>  yearC (dateY) AS
>(
> SELECT date(StartDate, '+1 year', '-1 day')
>   FROM dates
>  WHERE date(StartDate, '+1 year', '-1 day') <= EndDate
>  UNION ALL
> SELECT date(dateY, '+1 year')
>   FROM yearC, dates
>  WHERE date(dateY, '+1 year') <= EndDate
>),
>  years (dateY, years) as
>(
> SELECT coalesce((SELECT max(dateY)
>FROM yearC), date(StartDate, '-1 day')),
>coalesce((SELECT count(*)
>FROM yearC), 0)
>   FROM dates
>),
>  monthC (dateM) as
>(
> SELECT date(dateY, '+1 month')
>   FROM years, dates
>  WHERE date(dateY, '+1 month') <= EndDate
>  UNION ALL
> SELECT date(dateM, '+1 month')
>   FROM monthC, dates
>  WHERE date(dateM, '+1 month') <= EndDate
>),
>  months (dateM, months) as
>(
> SELECT coalesce((SELECT max(dateM)
>FROM monthC), dateY),
>coalesce((SELECT count(*)
>FROM monthC), 0)
>   FROM years
>),
>  dayC (dateD) as
>(
> SELECT date(dateM, '+1 day')
>   FROM months, dates
>  WHERE date(dateM, '+1 day') <= EndDate
>  UNION ALL
> SELECT date(dateD, '+1 day')
>   FROM dayC, dates
>  WHERE date(dateD, '+1 day') <= EndDate
>),
>  days (dateD, days) as
>(
> SELECT coalesce((SELECT max(dateD)
>FROM dayC), DateM),
>coalesce((SELECT count(*)
>FROM dayC), 0)
>   FROM months
>)
> SELECT StartDate,
>DateY,
>DateM,
>DateD,
>EndDate,
>years,
>months,
>days
>   FROM dates, years, months, days;
> 
> StartDate   dateY   dateM   dateD   EndDate years   
> months  days
> --  --  --  --  --  --  
> --  --
> 2004-02-02  2018-02-01  2018-08-01  2018-08-03  2018-08-03  14  6 
>   2

The output of code above is:
WITH RECURSIVE dates (StartDate, EndDate) as ( select '2004-02-02', 
'2004-02-02' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year', '-1 day') 
FROM dates WHERE date(StartDate, '+1 year', '-1 day') <= EndDate UNION ALL 
SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= 
EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT max(dateY) FROM 
yearC), date(StartDate, '-1 day')), coalesce((SELECT count(*) FROM yearC), 0) 
FROM dates ), monthC (dateM) as  ( SELECT date(dateY, '+1 month') FROM years, 
dates WHERE date(dateY, '+1 month') <= EndDate UNION ALL SELECT date(dateM, '+1 
month') FROM monthC, dates WHERE date(dateM, '+1 month') <= EndDate ), months 
(dateM, months) as ( SELECT coalesce((SELECT max(dateM) FROM monthC), dateY), 
coalesce((SELECT count(*) FROM monthC), 0) FROM years ), dayC (dateD) as ( 
SELECT date(dateM, '+1 day') FROM months, dates WHERE date(dateM, '+1 day') <= 
EndDate UNION ALL SELECT date(dateD, '+1 day') FROM dayC, dates WHERE 
date(dateD, '+1 day') <= EndDate ), days (dateD, days) as ( SELECT 
coalesce((SELECT max(dateD) FROM dayC), DateM), coalesce((SELECT count(*) FROM 
dayC), 0) FROM months ) SELECT StartDate, DateY, DateM, DateD, EndDate, years, 
months, days FROM dates, years, months, days;

StartDate   dateY   dateM   dateD   EndDate years   months  
days  
--  --  --  --  --  --  
--  --
2004-02-02  2004-02-01  2004-02-01  2004-02-02  2004-02-02  0   0   
1

which is wrong because the StarDate and EndDate are the same:
'2004-02-02'. The output should be zero '0' in this case.

I tried this SQL command for three cases. The output of two cases are
the expected, but one is not. See bellow.

1983-07-11' - '1984-08-31'
^^
sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1983-07-11', 
'1984-08-31' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year', '-1 day') 
FROM dates WHERE date(StartDate, '+1 year', '-1 day') <= EndDate UNION ALL 
SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= 
EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT 

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-05 0:18 GMT+02:00 Keith Medcalf :
>
> WITH RECURSIVE
>  dates(dateD) AS (VALUES(:StartDate)
> UNION ALL
>   SELECT date(dateD, '+1 year')
> FROM dates
>WHERE date(dateD, '+1 year') <= :EndDate
>  )
> SELECT max(dateD), count(*) FROM dates;

How do I interpret the ':StartDate' and ':EndDate'?

Should I replace for example the ':StartDate' with '1983-07-11' like this:

sqlite> WITH RECURSIVE dates(dateD) AS (VALUES('1983-07-11') UNION ALL
SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year')
<= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates;
1984-07-11|2

But this is ot what I want, because between 1983-07-11 and 1984-08-31
there is exactly one whole year out there, namely: from 1983-07-11 to
1984-07-11. So the result value '2' above is wrong in the sense that
there is 1 year and not 2 years out there.

The following SQL commands gives to me the right output, what I desired:
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1983-07-11','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*)
FROM dates;
1984-07-11|1
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1984-11-01','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1986-01-15' ) SELECT max(dateD), count(*)
FROM dates;
1985-11-01|1
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1986-01-16','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1999-07-16' ) SELECT max(dateD), count(*)
FROM dates;
1999-01-16|13

but not in the following case:
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('2000-02-01','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '2000-08-31' ) SELECT max(dateD), count(*)
FROM dates;
2001-02-01|1

because here in the last example there should be '0' and not '1' in
the output of that command.

I am still trying to find the proper way to do this, what I desired,
if it is possible at all.

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

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-04 14:07 GMT+02:00 Csányi Pál :
> 2018-08-03 13:09 GMT+02:00 R Smith :
>
>> Do you want a recursive query that will add all years between 3 October and
>> some other date, but NOT the first year and NOT the last year?
>
> I want a recursive query that gives years to the start date until it
> reaches the end date or if it exceeds the end date, the last added
> date is valid. Meanwhile, it should report how many times it has added
> a year. Its output is the date of the last added year and the number
> of additions. Is this possible?

I think I found the solution.
Here is two SQL statement with different dates, and it seems to me
that that it do it right, no?

sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1983-07-11','+1
year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD) <= date('1984-08-31') ) SELECT date(dateD), count(*) FROM
dates WHERE dateD <= '1984-08-31';
1984-07-11|1

sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1984-11-01','+1
year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD) <= date('1986-01-15') ) SELECT date(dateD), count(*) FROM
dates WHERE dateD <= '1986-01-15';
1985-11-01|1

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

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-03 13:09 GMT+02:00 R Smith :

> Do you want a recursive query that will add all years between 3 October and
> some other date, but NOT the first year and NOT the last year?

I want a recursive query that gives years to the start date until it
reaches the end date or if it exceeds the end date, the last added
date is valid. Meanwhile, it should report how many times it has added
a year. Its output is the date of the last added year and the number
of additions. Is this possible?

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

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-03 13:09 GMT+02:00 R Smith :
>
>
> On 2018/08/03 12:35 PM, Csányi Pál wrote:
>>
>> 2018-08-02 23:12 GMT+02:00 R Smith :
>>>
>>> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>>>
>>>> Hi,
>>>>
>>>> I just want to know why the following SQLite query does not work as I
>>>> expected?
>>>>
>>>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>>>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>>>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>>>> 2017-10-03|3
>>>> which is not what I am expecting.
>>>>
>>>> I am expecting the followings:
>>>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>>>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>>>> 3. because 2016-10-03 = 2016-10-03 it count 1
>>>> 4. then add to the result date 2016-10-03 once again 1 year which is
>>>> 2017-10-03
>>>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>>>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>>>> 7. it should gives the following result:
>>>> 2016-10-03|1
>>>>
>>>> What am I doing wrong here?
>>>>
>>> When the recursive Query starts up, the first value that it outputs is
>>> given
>>> by the very first part of the query, namely: VALUES('2015-10-03')
>>> So on the first iteration, it will produce one row like this:
>>> '2015-10-03'
>>> regardless of the rest of the Query. This row is pushed into the
>>> recursion
>>> buffer.
>>>
>>> After that it then reads a row from the recursion buffer and checks
>>> (within
>>> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
>>> '2016-11-01', and finds that it definitely IS less, so continues to
>>> produce
>>> the another line of output.
>>>
>>> The output created is that date from the buffer (2015-10-03) which is put
>>> through the given calculation: date(dateD, '+1 year') to give:
>>> '2016-10-03'
>>>
>>> It then continues to push that next row into the recursion buffer and
>>> next
>>> reads again from it and again checks if it (2016-10-03) is <= than
>>> 2016-11-01, which again it is... so it continues to produce the next
>>> output
>>> row, which after calculation becomes:
>>> '2017-10-03'
>>>
>>> It then continues to push that again into the buffer and again read it
>>> and
>>> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME,
>>> it
>>> isn't... so it stops right there.
>>>
>>> So in the end, it has produced 3 output rows namely:
>>> '2015-10-03'
>>> '2016-10-03'
>>> '2017-10-03'
>>>
>>> Which is exactly what you've asked for.
>>>
>>> Note: The first part of the query will ALWAYS reach the output buffer,
>>> even
>>> if it isn't a recursive query, and the UNION is NOT specified, you will
>>> get
>>> at least the '2015-10-03' value.
>>> Note: When comparing in the WHERE clause, you do not compare the newly
>>> calculated value (date(xxx, +1 year)), but indeed you compare the
>>> before-calculated value, i.e. the previous value in the buffer (the same
>>> as
>>> how your calculation is done on the PREVIOUS value in the buffer to yield
>>> the new date with.
>>>
>>> I hope that helps to make sense.
>>>
>>> Cheers,
>>> Ryan
>>
>> I think then that that the following SQL query gives to me the desired
>> result:
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;
>>
>> count(dateD)-2 does the math. Right?
>>
>
> That depends on what the desired result is. Do you want to count how many
> years elapsed (in full) since 3 October 2015?  Count()-2 is the worst hack
> for this (unless the question is specifically "what is 2 less than the
> number of years between x and y").
>
> Do you want a recursive query that will add all years between 3 October and
> some other date, but NOT the first year and NOT the last year?
>
> Maybe if you explain what is the question you are asking, and perhaps
> provide 2 examples, the one you alre

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-02 23:12 GMT+02:00 R Smith :
> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I just want to know why the following SQLite query does not work as I
>> expected?
>>
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>> 2017-10-03|3
>> which is not what I am expecting.
>>
>> I am expecting the followings:
>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>> 3. because 2016-10-03 = 2016-10-03 it count 1
>> 4. then add to the result date 2016-10-03 once again 1 year which is
>> 2017-10-03
>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>> 7. it should gives the following result:
>> 2016-10-03|1
>>
>> What am I doing wrong here?
>>
>
> When the recursive Query starts up, the first value that it outputs is given
> by the very first part of the query, namely: VALUES('2015-10-03')
> So on the first iteration, it will produce one row like this:
> '2015-10-03'
> regardless of the rest of the Query. This row is pushed into the recursion
> buffer.
>
> After that it then reads a row from the recursion buffer and checks (within
> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
> '2016-11-01', and finds that it definitely IS less, so continues to produce
> the another line of output.
>
> The output created is that date from the buffer (2015-10-03) which is put
> through the given calculation: date(dateD, '+1 year') to give:
> '2016-10-03'
>
> It then continues to push that next row into the recursion buffer and next
> reads again from it and again checks if it (2016-10-03) is <= than
> 2016-11-01, which again it is... so it continues to produce the next output
> row, which after calculation becomes:
> '2017-10-03'
>
> It then continues to push that again into the buffer and again read it and
> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
> isn't... so it stops right there.
>
> So in the end, it has produced 3 output rows namely:
> '2015-10-03'
> '2016-10-03'
> '2017-10-03'
>
> Which is exactly what you've asked for.
>
> Note: The first part of the query will ALWAYS reach the output buffer, even
> if it isn't a recursive query, and the UNION is NOT specified, you will get
> at least the '2015-10-03' value.
> Note: When comparing in the WHERE clause, you do not compare the newly
> calculated value (date(xxx, +1 year)), but indeed you compare the
> before-calculated value, i.e. the previous value in the buffer (the same as
> how your calculation is done on the PREVIOUS value in the buffer to yield
> the new date with.
>
> I hope that helps to make sense.
>
> Cheers,
> Ryan

I think then that that the following SQL query gives to me the desired result:
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;

count(dateD)-2 does the math. Right?

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

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

2018-08-02 23:12 GMT+02:00 R Smith :
> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I just want to know why the following SQLite query does not work as I
>> expected?
>>
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>> 2017-10-03|3
>> which is not what I am expecting.
>>
>> I am expecting the followings:
>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>> 3. because 2016-10-03 = 2016-10-03 it count 1
>> 4. then add to the result date 2016-10-03 once again 1 year which is
>> 2017-10-03
>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>> 7. it should gives the following result:
>> 2016-10-03|1
>>
>> What am I doing wrong here?
>>
>
> When the recursive Query starts up, the first value that it outputs is given
> by the very first part of the query, namely: VALUES('2015-10-03')
> So on the first iteration, it will produce one row like this:
> '2015-10-03'
> regardless of the rest of the Query. This row is pushed into the recursion
> buffer.
>
> After that it then reads a row from the recursion buffer and checks (within
> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
> '2016-11-01', and finds that it definitely IS less, so continues to produce
> the another line of output.
>
> The output created is that date from the buffer (2015-10-03) which is put
> through the given calculation: date(dateD, '+1 year') to give:
> '2016-10-03'
>
> It then continues to push that next row into the recursion buffer and next
> reads again from it and again checks if it (2016-10-03) is <= than
> 2016-11-01, which again it is... so it continues to produce the next output
> row, which after calculation becomes:
> '2017-10-03'
>
> It then continues to push that again into the buffer and again read it and
> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
> isn't... so it stops right there.
>
> So in the end, it has produced 3 output rows namely:
> '2015-10-03'
> '2016-10-03'
> '2017-10-03'
>
> Which is exactly what you've asked for.
>
> Note: The first part of the query will ALWAYS reach the output buffer, even
> if it isn't a recursive query, and the UNION is NOT specified, you will get
> at least the '2015-10-03' value.
> Note: When comparing in the WHERE clause, you do not compare the newly
> calculated value (date(xxx, +1 year)), but indeed you compare the
> before-calculated value, i.e. the previous value in the buffer (the same as
> how your calculation is done on the PREVIOUS value in the buffer to yield
> the new date with.
>
> I hope that helps to make sense.
>
> Cheers,
> Ryan

Ryan, thank you very much for the explanation!

Then, how can I implement steps from 1 to 7 in SQLite language?

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

[sqlite] Using CTE with date comparison

[Csányi Pál]

Hi,

I just want to know why the following SQLite query does not work as I expected?

WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
2017-10-03|3
which is not what I am expecting.

I am expecting the followings:
1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
2. then it compares the two dates: 2016-10-03 with 2016-10-03
3. because 2016-10-03 = 2016-10-03 it count 1
4. then add to the result date 2016-10-03 once again 1 year which is 2017-10-03
5. then it compares the two dates: 2017-10-03 with 2016-10-03
6. because 2017-10-03 > 2016-10-03 it does not count 2
7. it should gives the following result:
2016-10-03|1

What am I doing wrong here?

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

Re: [sqlite] Understanding SELECT statement

[Csányi Pál]

Once again, thank you all very much for your help.
I understand now this query.

R Smith  ezt írta (időpont: 2018. jún. 26., K, 17:28):
>
>
> On 2018/06/26 4:42 PM, Csányi Pál wrote:
> >
> >>> Then I get help and this code:
> >>> INSERT INTO SchoolYearTeachingDays
> >>>SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT
> >>> IN (SELECT S.aDate FROM SchoolYearTeachingDays S)
> >> This says "insert all dates that aren't already present", which of course 
> >> avoids UNIQUE constraint.
> > Just do not understand what are the 'T' and 'S' means out there, after
> > FROM clause.
> > Are they CTEs? Are they virtual tables?
> >
>
> The T and S are substitute identifiers that represent the tables
> "SchoolYearTeachingDays" and "TeachingSaturdaysInSchoolYear".  These
> substitute names are called "Aliases" which is something like a
> short-name or nick-name for something with a long name which we do not
> want to type every time.  Much like in school when someone was named
> "Josephine" but everyone just called her "Jo" for short, then "Jo" is an
> Alias for "Josephine", like in the above query where "S" is an alias for
> "SchoolYearTeachingDays".
>
> The main reason we use aliases, which other posts have highlighted
> already, is simply to spare ourselves the effort of having to type it
> out many times, but it's also handy for avoiding confusion when we have
> two references to the same table, or two tables with column names that
> are the same.
>
> In the query above, the reason we need to name the table is so that we
> make sure the "aDate" in the SELECT refers to the aDate from the
> "SchoolYearTeachingDays" table in the correlated sub-query (the bit
> inside parentheses) and not the outside query, which refers to table
> "TeachingSaturdaysInSchoolYear", which may also have an "aDate" column.
> So to avoid ambiguity or confusion about which aDate we are talking
> about, we must specify the table it is in. But we don't want to write
> the full table name every time, so in stead we use the aliases.
>
> One way to tell SQL we intend to use an alias is to use an "AS" clause,
> so we could say:
>
> SELECT A.id FROM MyLongNamedTable AS A WHERE A.id > 5
> Here we say we will get data from "MyLongNamedTable" but we will use it
> AS if it was named "A" so that everywhere else we will be able to refer
> to A.this or A.that in stead of having to type MyLongNamedTable.this and
> MyLongNamedTable.that.
>
> We can also do this for column names in the return header, so if I want
> to get the id from MyTable but we want it to be more descriptive, we
> could do:
> SELECT id AS MyTableID FROM MyTable...
> This will output a list of IDs under the aliased heading: "MyTableID".
>
> Lastly, as Simon mentioned, the SQL standard allows us to drop the word
> "AS" as long as we specify the known identifier directly followed by its
> Alias, so the previous query can also be written like:
> SELECT id MyTableID FROM MyTable...
>
> There is a school of thought that prefers this "id MyTableID" aliasing
> due to its brevity, and another that prefers the full "id AS MyTableID"
> due to its clarity.
> I don't think there is a "Right" way. Personally I use the first in my
> actual queries, but the second when using a query in an explanation.
>
>
> Hope that makes it clear.
>
> PS: Apologies for the overly "wordy" response, but it seems to me the OP
> is not natively English, or might read this through a translator, and I
> find for those to make sense in a technical translation, it is best if
> they have a lot of data to work with, saying the same thing in different
> ways, etc.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Understanding SELECT statement

[Csányi Pál]

Igor Tandetnik  ezt írta (időpont: 2018. jún. 26.,
K, 16:10):
>
> On 6/26/2018 9:15 AM, Csányi Pál wrote:

> > Then I get help and this code:
> > INSERT INTO SchoolYearTeachingDays
> >   SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT
> > IN (SELECT S.aDate FROM SchoolYearTeachingDays S)
>
> This says "insert all dates that aren't already present", which of course 
> avoids UNIQUE constraint.

Just do not understand what are the 'T' and 'S' means out there, after
FROM clause.
Are they CTEs? Are they virtual tables?

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

Re: [sqlite] [EXTERNAL] Understanding SELECT statement

[Csányi Pál]

I understand now a little better, how SELECT statement works.

Thank you all for the explanations.

Hick Gunter  ezt írta (időpont: 2018. jún. 26., K, 16:23):
>
> There is no DATE datatype in SQLite. Your declaration assigns NUMERIC 
> affinity for the date column.
>
> It seems that you are storing TEXT values, which is allowed, but in conflict 
> with your declaration.
>
> Since you have not declared INTEGER PRIMARY KEY, you only achieve what UNIQUE 
> alone would have done.
>
> As SQLite is asserting a UNIQUE constraint violation, you have probably 
> already inserted at least one saturday into your SchoolYearTeachingDays 
> table. You can check this with:
>
> SELECT rowid, aDate FROM SchoolYearTeachingDays WHERE aDate IN (SELECT aDate 
> FROM TeachingSaturdaysInSchoolYear);
>
> Or you may have duplicate dates (you did not declare aDate to be UNIQUE in 
> that table) in your TeachingSaturdaysInSchoolYear table, which you can check 
> with:
>
> SELECT aDate,count() from TeachingSaturdaysInSchoolYear group by 1 order by 1;
>
> To answer your question: INSERT INTO ... SELECT will attempt to insert each 
> result row of the SELECT exactly once. If you want to insert duplicated rows 
> of the SELECT only once, you need to SELECT DISTINCT.
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Csányi Pál
> Gesendet: Dienstag, 26. Juni 2018 15:15
> An: SQlite User 
> Betreff: [EXTERNAL] [sqlite] Understanding SELECT statement
>
> Hi,
>
> I have the 'SchoolYearTeachingDays' table with just one column, in which are 
> dates:
>
> CREATE TABLE SchoolYearTeachingDays (
> aDate DATE PRIMARY KEY
> UNIQUE
> );
> I filled it with many dates which are unique. These dates excludes dates for 
> Sundays and for Saturdays. I have another, the 
> 'TeachingSaturdaysInSchoolYear' table:
>
> CREATE TABLE TeachingSaturdaysInSchoolYear (
> idINT  PRIMARY KEY
>UNIQUE,
> aDate  DATE,
> TimetableForTheDay TEXT
> );
> This table holds just two dates. These two dates are for two Saturdays. On 
> these two Saturdays we have to teach students. When I do the following query 
> on this table, I get these two records:
>
> 2018-04-14
> 2018-05-05
>
> I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' 
> table into 'SchoolYearTeachingDays'
> table.
>
> I am trying with this query:
>
> INSERT INTO SchoolYearTeachingDays
>  SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: 
> Error: UNIQUE constraint failed:
> SchoolYearTeachingDays.aDate
>
> Then I get help and this code:
> INSERT INTO SchoolYearTeachingDays
>  SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN 
> (SELECT S.aDate FROM SchoolYearTeachingDays S)
>
> It works. But I am not understanding it at all.
> I wish to know followings.
> How many times want to inserts the SELECT query the one of the date from the 
> TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table?
>
> That is: the how many times wants select statement to insert one record from 
> first table into second table?
>
> --
> Best, Pali
> ___
> 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

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

[sqlite] Understanding SELECT statement

[Csányi Pál]

Hi,

I have the 'SchoolYearTeachingDays' table with just one column, in
which are dates:

CREATE TABLE SchoolYearTeachingDays (
aDate DATE PRIMARY KEY
UNIQUE
);
I filled it with many dates which are unique. These dates excludes
dates for Sundays and for Saturdays. I have another, the
'TeachingSaturdaysInSchoolYear' table:

CREATE TABLE TeachingSaturdaysInSchoolYear (
idINT  PRIMARY KEY
   UNIQUE,
aDate  DATE,
TimetableForTheDay TEXT
);
This table holds just two dates. These two dates are for two
Saturdays. On these two Saturdays we have to teach students. When I do
the following query on this table, I get these two records:

2018-04-14
2018-05-05

I want to INSERT these two dates from the
'TeachingSaturdaysInSchoolYear' table into 'SchoolYearTeachingDays'
table.

I am trying with this query:

INSERT INTO SchoolYearTeachingDays
 SELECT aDate FROM TeachingSaturdaysInSchoolYear
;
but I get this error: Error: UNIQUE constraint failed:
SchoolYearTeachingDays.aDate

Then I get help and this code:
INSERT INTO SchoolYearTeachingDays
 SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT
IN (SELECT S.aDate FROM SchoolYearTeachingDays S)

It works. But I am not understanding it at all.
I wish to know followings.
How many times want to inserts the SELECT query the one of the date
from the TeachingSaturdaysInSchoolYear table into
SchoolYearTeachingDays table?

That is: the how many times wants select statement to insert one
record from first table into second table?

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

Re: [sqlite] Free tool to visualize a schema?

[Csányi Pál]

2018-04-15 15:49 GMT+02:00 Lifepillar <lifepil...@lifepillar.me>:
> On 15/04/2018 14:14, Csányi Pál wrote:
>>
>> 2018-04-15 14:08 GMT+02:00 R Smith <ryansmit...@gmail.com>:
>>>
>>> On 2018/04/15 11:52 AM, Csányi Pál wrote:
>>>>
>>>>
>>>> Hi,
>>>>
>>>> I think the visualization of a schema helps to develop a sqlite
>>>> database.
>>>>
>>>> I am searching for a free software, like SchemaCrawler.
>>>> It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
>>>> diagram.
>>>>
>>>> I just tried out the Sqleton but it can't be start on my system so far.
>
>
> DbVisualizer (https://www.dbvis.com) is cross-platform, has free/pro
> versions, and has a good visualization tool, which displays foreign key
> names and other constraints (but not, AFAICS, arbitrary constraint́…
> check… clauses). It supports SQLite, too.
>
> Life.

Thanks!

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

Re: [sqlite] Free tool to visualize a schema?

[Csányi Pál]

2018-04-15 14:08 GMT+02:00 R Smith <ryansmit...@gmail.com>:
> On 2018/04/15 11:52 AM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I think the visualization of a schema helps to develop a sqlite database.
>>
>> I am searching for a free software, like SchemaCrawler.
>> It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
>> diagram.
>>
>> I just tried out the Sqleton but it can't be start on my system so far.
>>
>> Do you uses such a free tool?
>
>
> I think most SQLite (or indeed other DB) admin systems have some function
> for producing schema visualization - at least, in the standard sense of the
> word.
>
> Does this example do what you would like?
> http://sqlc.rifin.co.za/SchemaDocExample1.html
> or (in other colour scheme):
> http://sqlc.rifin.co.za/SchemaDocExample2.html
>
> If so you can find those in the schema documentation HTML export of
> sqlitespeed (http://sqlc.rifin.co.za/) but it's unfortunately on Windows
> only. The sqlitebrowser project has some nice features and work in the *nix
> systems too. (http://sqlitebrowser.org/)
>
> There might even be a command-line tool that does a good job, I think I've
> seen someone here talking about it, but am not sure now.
>
> Good luck!
> Ryan

No, I would like a tool such as sqleton
https://github.com/inukshuk/sqleton

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

[sqlite] Free tool to visualize a schema?

[Csányi Pál]

Hi,

I think the visualization of a schema helps to develop a sqlite database.

I am searching for a free software, like SchemaCrawler.
It is good, but can't show CONSTRAINT and FOREIGN KEY names in the diagram.

I just tried out the Sqleton but it can't be start on my system so far.

Do you uses such a free tool?

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

Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 21:09 GMT+02:00 Csányi Pál <csanyi...@gmail.com>:
> Thank you very much for the help and for the explanations.
>
> Waw! It is so complicated at first! I hope I shall understand these soon.
>
> Finally I decide to use this query:
> SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
> ORDER BY Keltezes LIMIT 1;
>
> At last this works on my Gentoo linux system here, on my laptop.
> I used datefudge to give a fake date to the sqlite3.
> When I run the above mentioned query without 'localtime', it gives the
> right date!
> But, when I run the same query with 'localtime', then I get the localtime.
>
> Because I think an Android operating system also uses localtime, I am
> going to try out this query now.

Well, it not works on my phone, altough I apply the 'localtime' in the query.
When I start the app it shows the TOMORROW date out there.
Can I ask you here what could be the solution?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Thank you very much for the help and for the explanations.

Waw! It is so complicated at first! I hope I shall understand these soon.

Finally I decide to use this query:
SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
ORDER BY Keltezes LIMIT 1;

At last this works on my Gentoo linux system here, on my laptop.
I used datefudge to give a fake date to the sqlite3.
When I run the above mentioned query without 'localtime', it gives the
right date!
But, when I run the same query with 'localtime', then I get the localtime.

Because I think an Android operating system also uses localtime, I am
going to try out this query now.

2018-04-12 18:48 GMT+02:00 Keith Medcalf :
>
> The Query Planner should decide in the case of the MIN containing query that 
> the best solution is to traverse TheDate in order and return the first hit.  
> This may entail the creation of the necessary index if it does not exist and 
> so the two plans should be more or less identical.
>
> However, if used in a subquery, the inclusion of the LIMIT may preclude 
> flattening whereas the MIN function version will not preclude flattening.  
> Since the most likely alternative to flattening is a co-routine it probably 
> would not make much of a difference.
>
> ---
> 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 Simon Slavin
>>Sent: Thursday, 12 April, 2018 10:24
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>On 12 Apr 2018, at 5:16pm, R Smith  wrote:
>>
>>> SELECT MIN(TheDate) -- get the smallest date
>>>   FROM Orak -- from the table with School-days
>>>  WHERE TheDate >= date('now')   -- where the school-day is later or
>>equal to today.
>>> ;
>>
>>This reflects exactly the right structure for the data.  The
>>following may be a little faster:
>>
>>  SELECT TheDate
>>FROM Orak
>>   WHERE TheDate >= date('now')
>>ORDER BY TheDate
>>   LIMIT 1
>>
>>Both the above queries will perform far faster if there is an index
>>on the "TheDate" column in Orak.  I'm not quite sure how your data is
>>organised but this may perform another job too if it is a UNIQUE
>>index.
>>
>>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] SELECT with CASE

[Csányi Pál]

Hi Ryan,

2018-04-12 17:36 GMT+02:00 R Smith <ryansmit...@gmail.com>:
> On 2018/04/12 5:20 PM, Csányi Pál wrote:
>>
>> Yes, this is what I am asking.
>>
>> 2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>> "I have a table with a bunch-o-dates in it.  I want a query which will
>>> return, at the time the query is run, based on the comuter on which the
>>> query is run concept of today's date, today's date, if that date is in the
>>> table otherwise the tomorrow's date (based on the current concept of
>>> 'tomorrow' on the computer on which the query is run."
>>>
>>> Is this what you are asking?
>
>
> Ok, now that we know what you are asking, there are some ways of doing it
> easily, of which Richard's way will work perfectly.
>
> But, some more information will be useful:
> - Can there be dates later than today in the table or not?
> - Can there be multiple dates for today in the table, or just the one?
> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
>
> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.
>
> Cheers!
> Ryan

> - Can there be dates later than today in the table or not?
Yes, it can.

> - Can there be multiple dates for today in the table, or just the one?
Yes, it can.

> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
It is used simply to determine the next date available for another query.

> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.

You're right.
I am developing an Android app on App Inventor2.
The app is in Hungarian language so the SQLite database contains
tables and columns with Hungarian names.

The whole schema is like this:
CREATE TABLE Beiratkozottak(
  az INTEGER PRIMARY KEY UNIQUE,
  TanuloNeve TEXT NOT NULL,
  EvFolyam INTEGER NOT NULL,
  Tagozat TEXT NOT NULL
);
CREATE TABLE Hianyzok(
  az INTEGER PRIMARY KEY,
  HianyzoTanulo TEXT NOT NULL,
  Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE JelenLevok(
  az INTEGER PRIMARY KEY UNIQUE,
  JelenLevoTanulo TEXT NOT NULL,
  Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE Orak(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  OrarendNapja TEXT DEFAULT NULL,
  BlokkoraSzama TEXT DEFAULT NULL,
  EvFolyam INTEGER DEFAULT NULL,
  Tagozat TEXT DEFAULT NULL,
  OraVazlat TEXT DEFAULT NULL,
  OraNaplo TEXT DEFAULT NULL,
  Emlekezteto TEXT DEFAULT NULL,
  OraRend INTEGER REFERENCES OraRend(az) DEFAULT NULL,
  Beiratkozottak INTEGER REFERENCES Beiratkozottak(az) DEFAULT NULL,
  TanSzombatokOraRendjei INTEGER REFERENCES TanSzombatokOraRendjei(az)
DEFAULT NULL,
  KivetelesNapokOraRendjei INTEGER REFERENCES
KivetelesNapokOraRendjei(az) DEFAULT NULL
);
CREATE TABLE OraRend(
  az INTEGER PRIMARY KEY UNIQUE,
  aHetNapja TEXT,
  HanyadikIskolaOra INTEGER,
  EvFolyam INTEGER,
  Tagozat TEXT
);
CREATE TABLE TanSzombatokOraRendjei(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  aHetNapja TEXT
);
CREATE TABLE KivetelesNapokOraRendjei(
  az INTEGER PRIMARY KEY UNIQUE,
  Keltezes DATE DEFAULT NULL,
  aHetNapja TEXT
);

There are datas already in this database.
The Orak table contains dates on which we have school, that is teaching.
That mean not every day in the year is a school day.
The OraRend table contains the timetable.

So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?

2018-04-12 17:26 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> Then Richard is correct (of course) ... which is a perfect translation of the 
> problem statement into SQL.
>
> SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), 
> date('now','+1 day')) as TheDate;
>
>
> ---
> 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 Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> 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 Csányi Pál
>>>>Sent: Thursday, 12 April, 2018 09:10
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] SELECT with CASE
>>>>
>>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>>>
>>>>> select TheDate from Dates where TheDate == date('now');
>>>>
>>>>Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day').
>>>>___
>>>>sqlite-users mailing list
>>>>sqlite-users@mailinglists.sqlite.org
>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Yes, this is what I am asking.

2018-04-12 17:17 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> The question you asked was:
>
> "Then how can I get only that date from the Dates table - which is
> equal to the current date?"
>
> and you are now posing a second question:
>
> ">Yes, but I want the CASE because if there is no such date in the
>>Dates
>>table which is equal to the date('now') then it should return the
>>date('now','+1 day')."
>
> Which seems like a rather long winded way of stating the problem:
>
> "I have a table with a bunch-o-dates in it.  I want a query which will 
> return, at the time the query is run, based on the comuter on which the query 
> is run concept of today's date, today's date, if that date is in the table 
> otherwise the tomorrow's date (based on the current concept of 'tomorrow' on 
> the computer on which the query is run."
>
> Is this what you are asking?
>
> ---
> 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 Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:10
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>>>
>>> select TheDate from Dates where TheDate == date('now');
>>
>>Yes, but I want the CASE because if there is no such date in the
>>Dates
>>table which is equal to the date('now') then it should return the
>>date('now','+1 day').
>>___
>>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] SELECT with CASE

[Csányi Pál]

2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>
> select TheDate from Dates where TheDate == date('now');

Yes, but I want the CASE because if there is no such date in the Dates
table which is equal to the date('now') then it should return the
date('now','+1 day').
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 17:00 GMT+02:00 Peter Da Silva :
> One of the lines of the output does indeed have '2018-04-12' as expected.

Indeed, I did not notice.
Then how can I get only that date from the Dates table - which is
equal to the current date?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is 
> what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT with CASE

[Csányi Pál]

Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:
> On 12 Apr 2018, at 1:25pm, Csányi Pál <csanyi...@gmail.com> wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the 
> format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

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

[sqlite] SELECT with CASE

[Csányi Pál]

Hi,

I have a small database:
DatesOfYear.db

with only one table:

CREATE TABLE Dates(id integer PRIMARY KEY UNIQUE, TheDate date NOT NULL);

I insert into the table some datas with:

INSERT INTO Dates VALUES(1,'2018-04-01');
INSERT INTO Dates VALUES(2,'2018-04-02');
INSERT INTO Dates VALUES(3,'2018-04-03');
INSERT INTO Dates VALUES(4,'2018-04-04');
INSERT INTO Dates VALUES(5,'2018-04-05');
INSERT INTO Dates VALUES(6,'2018-04-06');
INSERT INTO Dates VALUES(7,'2018-04-07');
INSERT INTO Dates VALUES(8,'2018-04-08');
INSERT INTO Dates VALUES(9,'2018-04-09');
INSERT INTO Dates VALUES(10,'2018-04-10');
INSERT INTO Dates VALUES(11,'2018-04-11');
INSERT INTO Dates VALUES(12,'2018-04-12');
INSERT INTO Dates VALUES(13,'2018-04-13');
INSERT INTO Dates VALUES(14,'2018-04-14');
INSERT INTO Dates VALUES(15,'2018-04-15');

I want the following by using SELECT and CASE:

to select the date:
 if TheDate is = with the current date then select that TheDate
 else to select only once the next date  from Dates table.

I tried with this query:
SELECT CASE TheDate = date('now') WHEN TheDate THEN TheDate ELSE
date('now','+1 day') END TheDate FROM Dates;

and the output is this:
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13

but I expect - because to day is 2018-04-12 this:
2018-04-12

What am I doing wrong?
NOTE! The dates are in Hungarian.

-- 
Best, Pali
___
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 

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

[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

[sqlite] Foreign keys - one to one, one to many relationship

[Csányi Pál]

Hi,

on Gentoo linux I have installed sqlite3 version 3.19.3.

With which constraints can one create a foreign key to
first: get one to one relationship, and
second: one to many relationship?

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

[sqlite] Support for named foreign keys?

[Csányi Pál]

Hi,

when will SQLite support the named foreign keys?

I am using SchemaCrawler to visualize my SQLite databases and because
SQLite does not support it yet, foreign keys get automatically
generated foreign key names in the diagram.

So it would be nice if SQLite would support it one day. Is'n it?

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

Re: [sqlite] Converting an sqlite database into format that an Android app can uses?

[Csányi Pál]

Hello Simon,

2017-08-28 15:28 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:
>
> On 28 Aug 2017, at 8:13am, Csányi Pál <csanyi...@gmail.com> wrote:
>
>> I attach in this mail the Probe.db file to ask you to see it, whether
>> is it really the database, or just a text file?
>
> This mailing list ignores attachments.  To see if it’s a database just try to 
> open or type the file.  If it’s all text, it’s not a database.  If it has 
> weird characters in it it’s probably a database.
>
> Simon.

I understand.
If I open the Probe.db to edit in Midnight Comander, then I find there
one line only, which begin like:
SQLite format 3^@^P^@^A^A^@@  ^@^@^@^B^@^@^@^C^@^@^@^ ... etc...

So it is definitely a database file and not a text file.

For newbies like me it should be mentioned this at
https://www.sqlite.org/quickstart.html
bellow the 'Create A New Database' title,
and below the line:
Enter SQL commands at the prompt to create and populate the new database.

like:

When one finished entering SQL commands and used the ".quit" command
to exit the shell tool, then there will be not a text, but a database
file which can then be used on various devices, like smart phones,
etc.

No?

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

Re: [sqlite] Converting an sqlite database into format that an Android app can uses?

[Csányi Pál]

2017-08-28 8:40 GMT+02:00 Simon Slavin :
>
>
> On 28 Aug 2017, at 7:37am, J Decker  wrote:
>
>> is redirection so hard?
>>
>> sqlite3 database.db < database.commands
>
> OP is learning and testing.  If redirection doesn’t work, they don’t know 
> what’s wrong.  The error messages in interactive mode are far clearer.
>
> Once they have the basic thing working, they can use interactive mode.

Agree.
But I did the redirection and get a Probe.db file, which is - I hope -
the database and not a text file with commands.

I attach in this mail the Probe.db file to ask you to see it, whether
is it really the database, or just a text file?

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

Re: [sqlite] Converting an sqlite database into format that an Android app can uses?

[Csányi Pál]

Hello Simon,

2017-08-28 7:14 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:
>
>
> On 28 Aug 2017, at 5:50am, Csányi Pál <csanyi...@gmail.com> wrote:
>
>> When I am at sqlite> prompt, I enter SQL commands to create tables for
>> the database Probe.db which is a file on my disk.
>>
>> In the Probe.db file are those SQL commands which I just entered and
>> naturally these line at the beginning of the file:
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>>
>> and at the end of file:
>> COMMIT;
>
> There is something wrong with this description.  You are talking about a file 
> "Probe.db" on your disk.  But you describe two different contents for this 
> file as if they are the same thing.
>
> Is "Probe.db" a list of SQLite commands, stored as a text file, or a SQLite 
> database ?  If it’s a SQLite database, can you given an example INSERT line 
> which was used to put data into this database ?

Probe.db is a list of SQL commands, stored as a text file, and not a
SQLite database.

How can I get it in SQLite database, instead of just a text file with
SQL commands?

>> How can I convert this Probe.db file into format that an Android app can 
>> uses?
>
> The SQlite database format is identical on all platforms.  You do not need to 
> do any conversion just because you move the database from a computer to a 
> phone.  However, your description above makes me think you have just a list 
> of commands, not the database that these commands would make.

Understand. Just want to get the SQLite database file, but not as a
text file with SQL commands.
How can I get it?

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

[sqlite] Converting an sqlite database into format that an Android app can uses?

[Csányi Pál]

Hi,

I am using sqlite3 on a command line, in an xterm window on Gentoo linux system.

When I am at sqlite> prompt, I enter SQL commands to create tables for
the database Probe.db which is a file on my disk.

In the Probe.db file are those SQL commands which I just entered and
naturally these line at the beginning of the file:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

and at the end of file:
COMMIT;


Say I want to use this database in my Android application on my phone.

How can I convert this Probe.db file into format that an Android app can uses?

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