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

[Keith Medcalf]

Well, the documentation *says* that a with clause cannot be used in a trigger, 
but that is incorrect, at least for the current tip of trunk ... because the 
following script works ...


---//--- snip ---//---
pragma recursive_triggers = 1;

create table if not exists services
(
id  INTEGER PRIMARY KEY,
startDate   text not null,
endDate text null,
years   integer null,
months  integer null,
daysinteger null,
toggle  integer default (-1)
);

create trigger if not exists InsertServices after insert on services
begin
update services
   set toggle = -new.toggle
 where id = new.id;
end;

create trigger if not exists UpdateServices after update of startDate, endDate, 
toggle on services
begin
update services
   set (years, months, days) = (with recursive
dates (startDate, endDate) as
(
select coalesce(new.startDate, date('now', 'localtime')),
   coalesce(new.endDate, date('now','localtime'))
),
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, printf('%+d years', years + 1)),
   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, printf('%+d years', years),
   printf('%+d months', months + 1)),
   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, printf('%+d years', years),
   printf('%+d months', months),
   printf('%+d days', days + 1)),
   endDate
  from daysTable
 where resultDate < endDate
),
dateDifference (startDate, resultDate, years, months, days) as
(
select  startDate,
resultDate,
years,
months,
days
   from daysTable
  where resultDate = endDate
)
select years,
   months,
   days
  from dateDifference
)
where id = new.id;
end;

.mode col
.head on
insert into services (startDate) values ('1995-02-01');
insert into services (startDate, endDate) values ('1995-02-01', '2018-08-31');
insert into services (startDate) values ('2004-02-01');
insert into services (startDate, endDate) values ('2004-02-01', '2018-08-31');
select * from services;

-- when run this recalculates all rows where the endDate is null using the 
current date
update services set toggle=-toggle where endDate is null;

---//--- snip ---//---

---
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 Clemens Ladisch
>Sent: Sunday, 26 August, 2018 06:56
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Why trigger (UPDATE) does not work?
>
>Csányi Pál wrote:
>> CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON
>MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = (  with
>...
>
> says:
>| The WITH clause cannot be used within a CREATE TRIGGER.
>
>
>Regards,
>Clemens
>___
>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] Why trigger (UPDATE) does not work?

[R Smith]

On 2018/08/26 8:44 PM, Csányi Pál wrote:

On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote:

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.



Great, that's even easier:

WITH worktime(grp, totDays) AS (
  SELECT WorkPlaceName, SUM(CAST(strftime('%J',date(EndDate)) AS 
INT)-CAST(strftime('%J',date(StartDate)) AS INT))

    FROM MyLengthOfService
   GROUP BY WorkPlaceName
)
SELECT grp AS WorkPlace, (totDays / 365) AS Years, ((totDays % 365) / 
30) AS Months, ((totDays % 365) % 30) AS Days, totDays

  FROM worktime
;

And, can be made into a VIEW if you like.


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

Re: [sqlite] Time Precision

[dmp]

Keith Medcalf wrote:
> Are you running Windows or Unix?  I am sending this to you as I was just
looking
> into this again and although SQLite maintains time internally with a
millisecond
> precision, the API used on Windows to read the time is limited by the Clock
> Resolution (usually about 16.5 ms).  If you are using Windows 8 or
later, then you
> can edit the SQLite3 amalgamation code (and/or the winfvs source) and
use the
> GetSystemTimePreciseAsFileTime call rather than the
GetSystemTimeAsFileTime call
> (around line 40866 in the aamalgamation code)

The time precision issue revolved around the lack of date, time, etc.
types in SQLite
and my Java application GUI using a JDBC. The java.sql.Time class is
defined as:

Time(long time)
Constructs a Time object using a milliseconds time value.

A JDBC setTime() or getTime() expects this long integer. Most databases I
would
assume stores a time type as a long intger with precision of milliseconds
even
though the ISO-8601 standard defines precsion to seconds.

The conversions using the datetime functions with SQLite as I was using them
did not give me milliseconds precsion. With a greater understanding of those
functions I was able to create the precision needed.

Seems it was one of the of the recommended solutions provided by you that
solved
my issues.

danap.

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

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?

[Simon Slavin]

On 26 Aug 2018, at 6:17pm, R Smith  wrote:

> Ok, enough about what is wrong with it. Here's how it can be fixed:

And there you have it, ladies and gentlemen.  Around two thousand bucks of 
consultancy for free.  The difference between copy-and-paste and actually 
understanding the products in use.

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

[R Smith]

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')
;


Then, add this View:

CREATE VIEW MyLengthOfServiceTotal AS
WITH worktime(id, dStart, dEnd) AS (
  SELECT id, date(StartDate), date(EndDate) FROM MyLengthOfService
), timediff(id, years, months, days) AS (
  SELECT id,
  CAST(strftime('%Y',dEnd) AS INT)-CAST(strftime('%Y',dStart) AS INT),
  CAST(strftime('%m',dEnd) AS INT)-CAST(strftime('%m',dStart) AS INT),
  CAST(strftime('%d',dEnd) AS INT)-CAST(strftime('%d',dStart) AS INT)
    FROM worktime
), timefix(id, years, months, days) AS (
  SELECT id,
  CASE WHEN years  < 0 THEN 0 WHEN months < 0 THEN years-1 ELSE 
years END,

  CASE WHEN months < 0 THEN months+12 ELSE months END,
  CASE WHEN days   < 0 THEN days+30   ELSE days END
    FROM timediff
), post(id, years, months, days) AS (
  SELECT id,
  CASE WHEN days>29 AND months = 11 THEN years+1 ELSE years END,
  CASE WHEN days>29 THEN months+1 ELSE months END,
  CASE WHEN days>29 THEN 0 ELSE days+1 END
    FROM timefix
)
SELECT post.id, post.years AS TotalYears, post.months AS TotalMonths, 
post.days AS TotalDays

  FROM post

The workings is simple: it calculates the differences in years, months 
and days between the starting and ending dates in the timediff CTE. Then 
it corrects those for overflow in the timefix CTE, and then lastly the 
post CTE is a processing step for some rules that are specific to your 
case (like adding a day, calling 30+ days a month, etc.).


This will now allow you to query things like this:

SELECT A.*, B.TotalYears, B.TotalMonths, B.TotalDays
FROM MyLengthOfService AS A
  JOIN MyLengthOfServiceTotal AS B ON B.id = A.id
;
  -- |   | |    | WithWor- 
| | Total- | Total- | Total-
  --  id | WorkPlaceName | StartDate  | EndDate    | 
kingTime | Comment | Years  | Months |  Days
  -- --- | - | -- | -- | 
 | --- | -- | -- | --
  --  1  | Name of the 1. work place | 1983-07-11 | 1984-08-31 |    
1 | workman |    1   |    1   |   21
  --  2  | Name of the 2. work place | 1984-11-01 | 1986-01-15 |    
1 | workman |    1   |    2   |   15
  --  3  | Name of the 3. work place | 1986-01-16 | 1999-07-16 |    
1 | workman |   13   |    6   |    1
  --  4  | Name of the 4. work place | 2000-02-01 | 2000-08-31 |    
1 | teacher |    0   |    7   |    0
  --  5  | Name of the 4. work place | 2000-09-01 | 2001-01-31 |  
0.5 | teacher |    0   |    5   |    0
  --  6  | Name of the 4. work place | 2001-02-01 | 2018-08-26 |    
1 | teacher |   17   |    6   |   26
  --  7  | Johnny    | 2018-05-01 | 2019-04-30 |    
1 | workman |    0   |   11   |   30


which will show you what you had already asked for - i.e. the length of 
service of all the people. You could also make this into a view so you 
only need to query 1 single item. (If that's a requirement)


Now, once that is done, here are two more views that will give you more 

Re: [sqlite] Unsigned

[R Smith]

On 2018/08/26 4:47 PM, Thomas Kurz wrote:

But, as noted, you could just store those as blobs, bigendian if you want
sorting, and indexing will work just fine. No other conversion needed.

Yes, I can store *anything* as blobs. When arguing like this, we wouldn't need 
strings, dates, floats or any other type neither.


Since you bring up "arguing like this"  - let me point out that the 
reply offered, in argument terms, is a straw men at best.


For similar functionalities A and B where it is not shown that B is 
required/needed/useful in the general case, does not in any way obviate 
the need (by precedent or otherwise) for A which IS shown to be useful 
in the general case.


By example, if a person offers a good reason why cars should have 5 
wheels, with good motivation too, say an added wheel in the center under 
the car would assist greatly with navigating huge bumps in the road - 
which might be objectively true, but until it can be shown that "huge 
bumps in the road" is sufficiently "general" a case to warrant the 
change, that person may still request it, but not require it.


More to the point of argument, if the request for 5-wheeled cars does 
not find implementation, it in no way obviates the need for 4-wheeled 
cars, so saying "well then we might just as well also not have 4 wheeled 
cars" is simply an appeal to false precedent.



Btw, my point is only towards the argument offered, I have no thoughts 
on the UINT itself - it would probably be rather nice to have.


Cheers,
Ryan

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

Re: [sqlite] Unsigned

[Thomas Kurz]

> But, as noted, you could just store those as blobs, bigendian if you want
> sorting, and indexing will work just fine. No other conversion needed.

Yes, I can store *anything* as blobs. When arguing like this, we wouldn't need 
strings, dates, floats or any other type neither.

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

Re: [sqlite] Unsigned

[Wout Mertens]

On Sun, Aug 26, 2018, 2:21 AM D Burgess  wrote:


> 2. Mixed 64/32 bit system that has integers that use the full 64 bits.
> Numbers are sourced by realtime hardware.
> Absence of 64 bit unsigned means addition of few functions to handle
> inserts and display representation(s), numbers stored as text/blobs.
> Again this all works, just extra code and indexes are less than
> optimum (compared to previous used mysql).
>

But, as noted, you could just store those as blobs, bigendian if you want
sorting, and indexing will work just fine. No other conversion needed.

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

[Jean-Luc Hainaut]

Please check the syntax of the case-end function. As you have written 
them, they just return boolean values.


J-L Hainaut


On 26/08/2018 14:16, Csányi Pál wrote:

On Sun, Aug 26, 2018 at 07:45:33AM -0400, Brian Curley wrote:

You don't list your trigger definition if there's anything that might need
troubleshooting, but I will say that I've recently stopped using
SQLiteStudio for its somewhat erratic behavior.

I attached to my previous mail the whole database dump in which is
that trigger which does not work as I expected.

But here is then the database itself:
CREATE TABLE MyLengthOfService (id INT PRIMARY KEY, WorkPlaceName TEXT, 
StartDate DATE, EndDate DATE, WithWorkingTime INT, Comment TEXT, Years INT, 
RemainingMonths INT, RemainingDays INT);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (1, 
'Name of the 1. work place', '1983-07-11', '1984-08-31', 1, 'workman', 1, 1, 
21);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (2, 
'Name of the 2. work place', '1984-11-01', '1986-01-15', 1, 'workman', 1, 2, 
15);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (3, 
'Name of the 3. work place', '1986-01-16', '1999-07-16', 1, 'workman', 13, 6, 
1);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (4, 
'Name of the 4. work place', '2000-02-01', '2000-08-31', 1, 'teacher', 0, 7, 0);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (5, 
'Name of the 4. work place', '2000-09-01', '2001-01-31', 0.5,'teacher', 0, 5, 
0);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (6, 
'Name of the 4. work place', '2001-02-01', '2018-08-26', 1, 'teacher', 17, 6, 
26);

CREATE TABLE SummedYearsMonthsDays (
 idINT PRIMARY KEY,
 SummedYears   INT,
 RemainingSummedMonths INT,
 RemainingSummedDays   INT
);
INSERT INTO SummedYearsMonthsDays (id, SummedYears, RemainingSummedMonths, 
RemainingSummedDays) VALUES (1, 12, 0, 0);

CREATE TRIGGER AllYearsMonthsDays AFTER UPDATE OF Years, RemainingMonths, 
RemainingDays ON MyLengthOfService BEGIN 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;
UPDATE SummedYearsMonthsDays SET RemainingSummedMonths = CASE WHEN ( SELECT 
total(RemainingDays) FROM MyLengthOfService ) < 30 THEN RemainingSummedMonths = 
( SELECT total(RemainingMonths) FROM MyLengthOfService ) ELSE 
RemainingSummedMonths = ( SELECT CAST ( total(RemainingMonths) + 
total(RemainingMonths) / 30 AS INTEGER ) FROM MyLengthOfService ) END WHERE id = 
1; END;



CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON MyLengthOfService BEGIN UPDATE MyLengthOfService SET 
Years = (  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) unionall 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  years from daysTable where resultDate = endDateR ), RemainingMonths = (  
with recursive dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE 
EndDate = NEW.EndDate), yearsTable 

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

[Clemens Ladisch]

Csányi Pál wrote:
> CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON 
> MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = (  with ...

 says:
| The WITH clause cannot be used within a CREATE TRIGGER.


Regards,
Clemens
___
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]

On Sun, Aug 26, 2018 at 07:45:33AM -0400, Brian Curley wrote:
> You don't list your trigger definition if there's anything that might need
> troubleshooting, but I will say that I've recently stopped using
> SQLiteStudio for its somewhat erratic behavior.

I attached to my previous mail the whole database dump in which is
that trigger which does not work as I expected.

But here is then the database itself:
CREATE TABLE MyLengthOfService (id INT PRIMARY KEY, WorkPlaceName TEXT, 
StartDate DATE, EndDate DATE, WithWorkingTime INT, Comment TEXT, Years INT, 
RemainingMonths INT, RemainingDays INT);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (1, 
'Name of the 1. work place', '1983-07-11', '1984-08-31', 1, 'workman', 1, 1, 
21);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (2, 
'Name of the 2. work place', '1984-11-01', '1986-01-15', 1, 'workman', 1, 2, 
15);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (3, 
'Name of the 3. work place', '1986-01-16', '1999-07-16', 1, 'workman', 13, 6, 
1);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (4, 
'Name of the 4. work place', '2000-02-01', '2000-08-31', 1, 'teacher', 0, 7, 0);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (5, 
'Name of the 4. work place', '2000-09-01', '2001-01-31', 0.5,'teacher', 0, 5, 
0);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, 
WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (6, 
'Name of the 4. work place', '2001-02-01', '2018-08-26', 1, 'teacher', 17, 6, 
26);

CREATE TABLE SummedYearsMonthsDays (
idINT PRIMARY KEY,
SummedYears   INT,
RemainingSummedMonths INT,
RemainingSummedDays   INT
);
INSERT INTO SummedYearsMonthsDays (id, SummedYears, RemainingSummedMonths, 
RemainingSummedDays) VALUES (1, 12, 0, 0);

CREATE TRIGGER AllYearsMonthsDays AFTER UPDATE OF Years, RemainingMonths, 
RemainingDays ON MyLengthOfService BEGIN 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;
UPDATE SummedYearsMonthsDays SET RemainingSummedMonths = CASE WHEN ( SELECT 
total(RemainingDays) FROM MyLengthOfService ) < 30 THEN RemainingSummedMonths = 
( SELECT total(RemainingMonths) FROM MyLengthOfService ) ELSE 
RemainingSummedMonths = ( SELECT CAST ( total(RemainingMonths) + 
total(RemainingMonths) / 30 AS INTEGER ) FROM MyLengthOfService ) END WHERE id 
= 1; END;



CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON 
MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = (  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) 
unionall 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  years from daysTable where 
resultDate = endDateR ), RemainingMonths = (  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) 

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

[Brian Curley]

You don't list your trigger definition if there's anything that might need
troubleshooting, but I will say that I've recently stopped using
SQLiteStudio for its somewhat erratic behavior.

While it's got a great regex implementation (which doesn't port to running
in pure CLI-based scripts) and a no-nonsense GUI, it also tends to flake
out with memory issues on relatively small databases. I also cannot find if
it's actively supported; no recent versions or activity on the forum.

You might need to crosscheck your results within alternates like
SQLiteAdmin or dbVis.

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

Re: [sqlite] Time Precision

[Keith Medcalf]

Are you running Windows or Unix?  I am sending this to you as I was just 
looking into this again and although SQLite maintains time internally with a 
millisecond precision, the API used on Windows to read the time is limited by 
the Clock Resolution (usually about 16.5 ms).  If you are using Windows 8 or 
later, then you can edit the SQLite3 amalgamation code (and/or the winfvs 
source) and use the GetSystemTimePreciseAsFileTime call rather than the 
GetSystemTimeAsFileTime call (around line 40866 in the aamalgamation code)


#if !SQLITE_OS_WINCE
  { "GetSystemTimeAsFileTime", (SYSCALL)GetSystemTimePreciseAsFileTime, 0 },
#else
  { "GetSystemTimeAsFileTime", (SYSCALL)0,   0 },
#endif


The attached extension (which *must* be included inline with the amalgamation 
code) adds functions which return datetimes with localization and/or 
milliseconds, and a separate unixtime function that returns the unixtime as a 
floating point number while still using the VDBE time stability guarantees (it 
uses the internal SQLite3 APIs to do most of the work).

Using the sqltime.c functions also requires some minor changes to the 
amalgamation code so that the localtime offset is kept when the localtime 
modifier is applied:

/*
** A structure for holding a single date and time.
*/
typedef struct DateTime DateTime;
struct DateTime {
  sqlite3_int64 iJD;  /* The julian day number times 8640 */
  int Y, M, D;/* Year, month, and day */
  int h, m;   /* Hour and minutes */
  int tz; /* Timezone offset in minutes */
  double s;   /* Seconds */
  char validJD;   /* True (1) if iJD is valid */
  char rawS;  /* Raw numeric value stored in s */
  char validYMD;  /* True (1) if Y,M,D are valid */
  char validHMS;  /* True (1) if h,m,s are valid */
  char validTZ;   /* True (1) if tz is valid */
  char tzSet; /* Timezone was set explicitly */
  char isError;   /* An overflow has occurred */
  sqlite3_int64 offs; /* Localtime Offset *//* ** */
};

The field offs has been added to the end of the structure and in function 
localtimeOffset just before the return ...

static sqlite3_int64 localtimeOffset(
  DateTime *p,/* Date at which to calculate offset */
  sqlite3_context *pCtx,  /* Write error here if one occurs */
  int *pRc/* OUT: Error code. SQLITE_OK or ERROR */
){

...

  computeJD();
  *pRc = SQLITE_OK;
  p->offs = y.iJD - x.iJD; /* ** */
  return y.iJD - x.iJD;
}

The offset is saved.

---
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 danap
>Sent: Sunday, 1 July, 2018 12:38
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Time Precision
>
>The time precision treated with and defined, ISO-8601, seems to be
>with regard to seconds. Storage of an Integer for time as an example
>in SQLite:
>
>sqlite> SELECT STRFTIME('%s', 'now', 'localtime');
>1530446557
>
>A 10 digit value. The issue I'm having is with regard to storage
>of time, in milliseconds, a 13 digit value. I would assume a more
>appropriate precision for a scientific community.
>
>Looking briefly at the c library for strftime() it does not seem
>to provide the possibility for a millisecond precision?
>
>STRFTIME('%ms'), Integer
>or
>TIME(D), 13 digits, Text.
>
>danap.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

/*
** NB:  On Windows, the SQLite3 Time & Date functions, even though the time
**  is maintained in milliseconds since the Julian Epoch, is limited by
**  the timer resolution, and the DateTime values are limited to this
**  precision.  Increassing the timer resolution will increase the
**  overhead used by the Operating System in maintaining the system
**  clock since it is updated much more frequently.
**
**  Modifying the SQLite3 winvfs to utilize the 
GetSystemTimePreciseAsFileTime
**  API instead of the GetSystemTimeAsFileTime API (search this in the code)
**  will ensure that all time & date functions use the maximum precision of
**  system clock that can be obtained (100 ns in theory) and the 
iCurrentTime
**  as maintained by the VDBE will always be accurate to the millisecond.
**
**  The granularity of the timer and the clock resolution will not be 
affected
**  by using the GetSystemTimePreciseAsFileTime API nor will the overhead
**  of changing the interrupt rate for maintaining the system clock.  The
**  (small) overhead will instead occur only when the system clock is read.
**
**  This file must be compiled as part of the Amalgamation by appending
**  the file to the end of 

[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