Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Wout Mertens]

On Fri, Mar 13, 2020 at 1:15 AM Jens Alfke  wrote:
>
> > On Mar 12, 2020, at 1:17 PM, Richard Hipp  wrote:
> >
> > I have set up an on-line forum as a replacement for this mailing list:
>
> Oh crap.
>
> > The Forum is powered by Fossil.
>
> I appreciate that you like to 'eat your own dog food'. However, I strongly 
> disagree with your using a homemade forum rather than something like 
> Discourse.

Normally I would say the same, but it's insane how much faster the
Fossil Forum is than Discourse.

I'd say this is a great showcase of SQLite's prowess, and while the
interface is decidedly engineerish, it's very usable. I look forward
to seeing it in action!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Wout Mertens]

The nice thing about web browsers, you can apply your own styling.
There's extensions that help with that, like StyleBot.

Wout.

On Fri, Mar 13, 2020 at 1:05 AM Keith Medcalf  wrote:
>
>
> Uck.  That is the most horrible looking thing I have ever seen in my life.  
> Good luck with it.
>
>
> --
> 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  On
> >Behalf Of Richard Hipp
> >Sent: Thursday, 12 March, 2020 15:29
> >To: SQLite mailing list 
> >Subject: Re: [sqlite] New SQLite Forum established - this mailing list is
> >deprecated
> >
> >On 3/12/20, no...@null.net  wrote:
> >> I am wondering what (apparently invisible)
> >> anti-spam features are present.
> >
> >I will be happy to discuss that, and any other questions you have, on
> >the Forum.  :-)
> >
> >--
> >D. Richard Hipp
> >d...@sqlite.org
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum requires Javascript?

[Wout Mertens]

Hi,

I normally wouldn't do this on the internet, but this mailing list
goes deep. Not sure how that will go now that we have to use the
forum, but here goes.

JavaScript makes websites much nicer to work with, by a wide margin,
especially those with lots of interaction like forums. Furthermore, it
is quite hard and expensive to make sites that work well with and
without JS.

Nowadays even microcontrollers can run JS - there are no browsers that
can't run JS, even the text based ones.

So IMHO, you are asking "please use time/money to achieve this state
that I prefer but that doesn't make a difference for the vast majority
of people".

Or am I missing something?

Wout.

On Fri, Mar 13, 2020 at 4:19 AM J.B. Nicholson  wrote:
>
> Richard Hipp wrote:
> > The Forum is powered by Fossil.  It has been in active use in the
> > Fossil community for a couple of years, and has worked well.
>
> Is there a way to use this without running the Javascript?
> ___
> 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] New SQLite Forum requires Javascript?

[J.B. Nicholson]

Richard Hipp wrote:

The Forum is powered by Fossil.  It has been in active use in the
Fossil community for a couple of years, and has worked well.


Is there a way to use this without running the Javascript?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Stephen Chrzanowski]

I've read a bunch of the posts here, and quite honestly, I'm kind of
looking forward to having Fossil send me notes that messages have been
added, then, if I want to reply, I just log into the site and do the
reply.  I'm not 100% sure about the anon sending to Fossil, if that's a
thing I thought I read somewhere, but at least my email address doesn't end
up in a public forum and find myself signed up to a dating site.  .. well..
at least until the mailing list is shut down.

On Thu, Mar 12, 2020 at 4:18 PM Richard Hipp  wrote:

> I have set up an on-line forum as a replacement for this mailing list:
>
> https://sqlite.org/forum
> https://www.sqlite.org/forum/forumpost/a6a27d79ac
>
> Please consider subscribing to the new Forum.  The intent is that the
> forum will eventually replace this mailing list.
>
> The Forum is powered by Fossil.  It has been in active use in the
> Fossil community for a couple of years, and has worked well.  See the
> second link above for more information.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Warren Young]

On Mar 12, 2020, at 6:15 PM, Jens Alfke  wrote:
> 
> I strongly disagree with your using a homemade forum rather than something 
> like Discourse.

Unlike SQLite proper, the Fossil project accepts outside contributions without 
a whole lot of gatekeeping.  I myself have made a few improvements to Fossil, 
including to its forum feature.

> What's important is usability — following discussions

The stock CSS for Fossil forums color threads with new posts differently.  
There’s message threading, and there’s email alerts on top of that so you can 
continue to use your preferred MUA to follow threads.

What more do you need?

> finding new content

Fossil’s built-in message searching capabilities are likely as good as those in 
your mail reader or in a mailing list manager.

And if you identify a lack, the person responsible for adding features to FTS5 
is also the person you’re upset at for this change, so maybe he’ll be receptive 
to your wishes for improvement.

> reading it

…which you can continue to do in your mail reader.

> and composing messages.

What features do you need here?  Most email messages are short, so while a 
browser’s textarea control isn’t super powerful, it’s usually sufficient.

Between that and the power of Fossil flavored Markdown, I rarely find myself 
needing more power.  And I say that as one who’s been using Fossil forums since 
their inception.

> There's a reason many people cling to mailing lists as their preferred 
> messaging system: email clients have evolved for nearly 50 years to be good 
> messaging clients.

I’d say it’s more because email is a lingua franca for online communication, 
one of the few truly federated mechanisms, beholden to no single corporation 
for its existence.

And Fossil forums embraces email quite well on the outbound side.

If you think you can solve the inbound side as well, Fossil has the beginnings 
of an SMTP server in it already.  I think it’s a massive project, which 
explains why it’s unfinished, but it’s there for someone interested.

> In a nutshell: by building a forum you're moving way outside your core 
> competency. It would be wiser to outsource this to a product that's been 
> built for this purpose by people who are really good at it.

This isn’t brand new functionality.  It’s been baking for most of two years.  
(The feature's approximate birthday is 2018-06-14.)

> Personally, I don't have SQLite questions all that often. I hang out in the 
> mailing list because it's easy to follow it in my email client and it's 
> convenient to post and reply. 

…which you can still do with the new SQLite forum.

> The forum, from my brief experience today, is really awkward.

From my ~2 years of experience, it isn’t all that awkward.  It’s not whizzy, 
but it is functional and useful.

It’s worth noting that Fossil's forum feature also builds on several much older 
pieces of tech.  At a low level, Fossil forums are just specialized 
applications of the pre-existing wiki code!  Some of the stuff that underpins 
the forum feature goes back to its very roots.

This isn’t something drh just hacked up last weekend.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[J. King]

On March 12, 2020 8:15:15 p.m. EDT, Jens Alfke  wrote:
>
>
>> On Mar 12, 2020, at 1:17 PM, Richard Hipp  wrote:
>> 
>> I have set up an on-line forum as a replacement for this mailing
>list:
>
>Oh crap.
>
>> The Forum is powered by Fossil.  
>
>I appreciate that you like to 'eat your own dog food'. However, I
>strongly disagree with your using a homemade forum rather than
>something like Discourse.

I will never be able to understand how anyone could hold up Discourse as 
superior to, well, anything. It's slow, it has poor UI feedback, and it 
surprises me continually. 

So far the SQLite forum seems fine enough to me (though I would have definitely 
preferred the mailing list continue), and definitely better than Discourse. 

To each their own, though!

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[J. King]

On March 12, 2020 8:06:47 p.m. EDT, Petite Abeille  
wrote:

>
>Also, would it be possible to actually mark such emails to include
>proper List Header Fields, e.g.:

I had the same thought, but would that really be appropriate? Unless you could 
post to it like a list, anyway. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Jens Alfke]

> On Mar 12, 2020, at 1:17 PM, Richard Hipp  wrote:
> 
> I have set up an on-line forum as a replacement for this mailing list:

Oh crap.

> The Forum is powered by Fossil.  

I appreciate that you like to 'eat your own dog food'. However, I strongly 
disagree with your using a homemade forum rather than something like Discourse.

In a messaging system, the user interface is critically important. I don't 
think it matters much whether the SQLite forum can render a page in "about 
0.003s" as it says in the footer. What's important is usability — following 
discussions, finding new content, reading it, and composing messages.

There's a reason many people cling to mailing lists as their preferred 
messaging system: email clients have evolved for nearly 50 years to be good 
messaging clients. If you like mail apps there are really good ones like Apple 
Mail and Outlook, if you like using a website then Gmail etc. are pretty good, 
and if you're a CLI guy there are great terminal-based ones.

It's very easy to slap together some HTML tables and textareas and have a 
functional forum GUI. It will suck, though. The kind of things that make 
web-based forums work well are difficult to do, and in my experience there are 
few implementations that really work well — the only ones that come to mind are 
Discourse, Google Groups, and groups.io .

In a nutshell: by building a forum you're moving way outside your core 
competency. It would be wiser to outsource this to a product that's been built 
for this purpose by people who are really good at it.

Personally, I don't have SQLite questions all that often. I hang out in the 
mailing list because it's easy to follow it in my email client and it's 
convenient to post and reply. The forum, from my brief experience today, is 
really awkward. I may not be showing up there very often.

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Petite Abeille]

> On Mar 13, 2020, at 01:04, Keith Medcalf  wrote:
> 
> Uck.  That is the most horrible looking thing I have ever seen in my life.  
> Good luck with it.

If only this was confine to the esthetics. But yes, horrible sums it up.

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Petite Abeille]

> On Mar 13, 2020, at 00:54, BohwaZ  wrote:
> 
> So that people wishing to use emails could still do it by subscribing
> to alerts and then replying to notifications.

Also, would it be possible to actually mark such emails to include proper List 
Header Fields, e.g.:

   List-Id: List Header Mailing List 
   List-Help: 
   List-Unsubscribe: 
   List-Subscribe: 
   List-Post: 
   List-Owner:  (Contact Person for Help)
   List-Archive: 


https://tools.ietf.org/html/rfc5983 

Anyway, tried the forum 'notification'. Very subpar, much like the entire 
'forum' experience. Unsubscribed. To each their own I guess. 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Keith Medcalf]

Uck.  That is the most horrible looking thing I have ever seen in my life.  
Good luck with it.


-- 
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  On
>Behalf Of Richard Hipp
>Sent: Thursday, 12 March, 2020 15:29
>To: SQLite mailing list 
>Subject: Re: [sqlite] New SQLite Forum established - this mailing list is
>deprecated
>
>On 3/12/20, no...@null.net  wrote:
>> I am wondering what (apparently invisible)
>> anti-spam features are present.
>
>I will be happy to discuss that, and any other questions you have, on
>the Forum.  :-)
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[BohwaZ]

Kia ora,

Like with the Fossil list I did subscribe and switch to the forum, but
wouldn't it be possible to add the ability in Fossil to handle incoming
email replies to forum posts?

So that people wishing to use emails could still do it by subscribing
to alerts and then replying to notifications.

It would be a perfect solution for my use of Fossil as well.

Would that feature would be accepted in Fossil if someone (probably not
me, I lack the time right now sorry) would submit a patch?

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Domingo Alvarez Duarte]

Hello Richard !

I like the concept of fossil including the forum functionality !

With that said we are in a database forum and most of us know that one 
common problem/task with databases is migration, there is any 
plan/attempt to migrate the actual mailing list to the new fossil forum ?


That would be a good way to see how the fossil forum would perform with 
some non trivial amount of data and would be an example that can attract 
more users/conversions from other forum platforms.


Cheers !

On 12/3/20 21:17, Richard Hipp wrote:

I have set up an on-line forum as a replacement for this mailing list:

 https://sqlite.org/forum
 https://www.sqlite.org/forum/forumpost/a6a27d79ac

Please consider subscribing to the new Forum.  The intent is that the
forum will eventually replace this mailing list.

The Forum is powered by Fossil.  It has been in active use in the
Fossil community for a couple of years, and has worked well.  See the
second link above for more information.


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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Petite Abeille]

> On Mar 12, 2020, at 21:17, Richard Hipp  wrote:
> 
> a replacement for this mailing list:

Tragic. Oh well. Anyhow, thanks for SQLite itself :)

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Warren Young]

On Mar 12, 2020, at 2:59 PM, no...@null.net wrote:
> 
> I would like to add my resistance "vote."

If experience on the Fossil mailing list is any guide, this mailing list will 
be a ghost town soon.  There have been just a few threads on the mailing list 
in the years since we started the forum, despite all the complaints leading up 
to the move.

Meanwhile, the forum is just as busy as the old mailing list was, maybe more so.

This SQLite mailing list has a higher posting rate than the Fossil Forum, but 
it probably also a greater percentage of “just plain users” than for Fossil, so 
I see no reason for the pattern not to repeat.

> For experienced vim/emacs/$EDITOR users, Email composition via web
> browser is one of the most debilitating experiences that can be
> imposed.

There is probably an $EDITOR key binding for your browser.

Alternately, you can compose in your preferred editor and copy-paste the result 
into the web page.

Realize that Fossil forums allow email subscription of posted messages, so from 
a pure reading standpoint, it’s not much different than with Mailman.

Based on my archives, you post to this list about once every 3 weeks on 
average.  I dearly hope that you have no problems greater than a slight 
inconvenience at that frequency.

> I also fear the loss of reading quality
> in my email client as Markdown-isms or html content take hold.

Fossil forums use Markdown by default, and experience on the Fossil Forum shows 
that most people either post in plain text or with minimal Markdown syntax.

Markdown syntax in forum posts is generally not much different than what you’d 
find in regular plain text email messages, such as *emphasis* and some way of 
posting URLs.

Keep in mind that Markdown is basically a formalization of existing text 
communication practices going back decades.  It is not a wholly new syntax.

Fossil's forum feature does *not* allow arbitrary HTML.

Both Markdown and Fossil Wiki syntaxes allow some minimal HTML, but you usually 
only see that used when the main markup syntax doesn’t allow a thing to be 
done.  And that rarely.

> I am wondering what (apparently invisible)
> anti-spam features are present.

It’s in the docs:

  https://fossil-scm.org/home/doc/trunk/www/antibot.wiki

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Richard Hipp]

On 3/12/20, no...@null.net  wrote:
> I am wondering what (apparently invisible)
> anti-spam features are present.

I will be happy to discuss that, and any other questions you have, on
the Forum.  :-)

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[nomad]

On Thu Mar 12, 2020 at 04:17:59PM -0400, Richard Hipp wrote:
> I have set up an on-line forum as a replacement for this mailing list:
> 
> https://sqlite.org/forum
> https://www.sqlite.org/forum/forumpost/a6a27d79ac

I know this topic has already been discussed deeply on this list, but I
would like to add my resistance "vote."

For experienced vim/emacs/$EDITOR users, Email composition via web
browser is one of the most debilitating experiences that can be
imposed.  I have kept well away from Gmail-like platforms for this
reason. In the other direction I also fear the loss of reading quality
in my email client as Markdown-isms or html content take hold.

On a purely technical note, although I haven't looked at the web
interface in detail, I am wondering what (apparently invisible)
anti-spam features are present. It seems rather easy to Preview and
Submit as anonymous.

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

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Scott Robison]

They can subscribe to the forum too. :)

On Thu, Mar 12, 2020, 2:40 PM Simon Slavin  wrote:

> Well, that'll annoy the nabble people.  And I can live with that.
> ___
> 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] New SQLite Forum established - this mailing list is deprecated

[Simon Slavin]

Well, that'll annoy the nabble people.  And I can live with that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] New SQLite Forum established - this mailing list is deprecated

[Richard Hipp]

I have set up an on-line forum as a replacement for this mailing list:

https://sqlite.org/forum
https://www.sqlite.org/forum/forumpost/a6a27d79ac

Please consider subscribing to the new Forum.  The intent is that the
forum will eventually replace this mailing list.

The Forum is powered by Fossil.  It has been in active use in the
Fossil community for a couple of years, and has worked well.  See the
second link above for more information.

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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Keith Medcalf]

>So is "julianday('now')" non-deterministic while "julianday()" _is_
>deterministic? That seems a little weird considering they're the same
>thing... right?

Yes.  Same as "datetime(julianday(), '+1 day')" and datetime(datetime(), '+1 
day') are deterministic but "datetime('now', '+1 day')" is not even though they 
all have the same result.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Richard Hipp]

On 3/12/20, Doug  wrote:
>
> Something triggered me when I looked at the generated code: you use the
> contents of register 2 for the constant value each time through the loop.
> What if the select looks like this, with more than one function call in the
> coalesce? Do you handle it properly?
>
> SELECT coalesce(a, abs(0), abs(1), abs(2)) FROM t1;

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 23000  Start at 23
1 OpenRead   0 2 0 1  00  root=2 iDb=0; t1
2 Explain2 0 0 SCAN TABLE t1  00
3 Rewind 0 22000
4   Column 0 0 100  r[1]=t1.a
5   NotNull1 20000  if
r[1]!=NULL goto 20
6   Once   0 9 000
7   Integer0 3 000  r[3]=0
8   Function   1 3 2 abs(1) 00  r[2]=func(r[3])
9   SCopy  2 1 000  r[1]=r[2]
10  NotNull1 20000  if
r[1]!=NULL goto 20
11  Once   0 14000
12  Integer1 5 000  r[5]=1
13  Function   1 5 4 abs(1) 00  r[4]=func(r[5])
14  SCopy  4 1 000  r[1]=r[4]
15  NotNull1 20000  if
r[1]!=NULL goto 20
16  Once   0 19000
17  Integer2 7 000  r[7]=2
18  Function   1 7 6 abs(1) 00  r[6]=func(r[7])
19  SCopy  6 1 000  r[1]=r[6]
20  ResultRow  1 1 000  output=r[1]
21Next   0 4 001
22Halt   0 0 000
23Transaction0 0 1 0  01  usesStmtJournal=0
24Goto   0 1 000

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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Doug]

Thanks, Richard.

Something triggered me when I looked at the generated code: you use the 
contents of register 2 for the constant value each time through the loop. What 
if the select looks like this, with more than one function call in the 
coalesce? Do you handle it properly?

SELECT coalesce(a, abs(0), abs(1), abs(2)) FROM t1;

Just curious...
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Richard Hipp
> Sent: Thursday, March 12, 2020 7:41 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-
> circuit sometimes
> 
> On 3/12/20, Doug  wrote:
> > Richard, what does the explain look like with your code change,
> please.
> 
> Test case:
> 
> CREATE TABLE t1(a);
> explain SELECT coalesce(a, abs(-9223372036854775808)) FROM t1;
> 
> Before the change:
> 
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  
> -
> 0 Init   0 10000  Start at
> 10
> 1 OpenRead   0 2 0 1  00  root=2
> iDb=0; t1
> 2 Explain2 0 0 SCAN TABLE t1  00
> 3 Rewind 0 9 000
> 4   Column 0 0 100
> r[1]=t1.a
> 5   NotNull1 7 000  if
> r[1]!=NULL goto 7
> 6   SCopy  2 1 000
> r[1]=r[2]
> 7   ResultRow  1 1 000
> output=r[1]
> 8 Next   0 4 001
> 9 Halt   0 0 000
> 10Transaction0 0 1 0  01
> usesStmtJournal=0
> 11Int64  0 3 0 -9223372036854775808  00
> r[3]=-9223372036854775808
> 12Function   1 3 2 abs(1) 00
> r[2]=func(r[3])
> 13Goto   0 1 000
> 
> Notice that the abs() function is invoked in the "prologue" code.
> The
> prologue begins on instruction 10 and continues through the Goto
> at
> instruction 13.
> 
> After the change:
> 
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  
> -
> 0 Init   0 13000  Start at
> 13
> 1 OpenRead   0 2 0 1  00  root=2
> iDb=0; t1
> 2 Explain2 0 0 SCAN TABLE t1  00
> 3 Rewind 0 12000
> 4   Column 0 0 100
> r[1]=t1.a
> 5   NotNull1 10000  if
> r[1]!=NULL goto 10
> 6   Once   0 9 000
> 7   Int64  0 3 0 -9223372036854775808  00
> r[3]=-9223372036854775808
> 8   Function   1 3 2 abs(1) 00
> r[2]=func(r[3])
> 9   SCopy  2 1 000
> r[1]=r[2]
> 10  ResultRow  1 1 000
> output=r[1]
> 11Next   0 4 001
> 12Halt   0 0 000
> 13Transaction0 0 1 0  01
> usesStmtJournal=0
> 14Goto   0 1 000
> 
> Now the prologue is just instructions 13 and 14 and omits the
> abs()
> function.  The abs() function is now computed on instructions 7
> and 8,
> but those instructions only run one time due to the "Once" opcode
> on
> instruction 6.
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Raymond]

> Alternatively, you can use the new GENERATED ALWAYS AS (...) STORED to make 
> it an automatically updated stored field and you do not need triggers at all, 
> just a version of SQLite3 that does generated columns (version 3.31.0 from 
> 2020-01-22 or later).
> 
> create table MyData
> (
>   id   integer primary key,
>   data,
>   lastupdate real as (julianday()) stored
> );

I thought that generated columns could only use deterministic functions?
https://www.sqlite.org/gencol.html
"2.3. Limitations
...
3. The expression of a generated column may only reference constant literals 
and columns within the same row, and may only use scalar deterministic 
functions. The expression may not use subqueries, aggregate functions, window 
functions, or table-valued functions.
..."


https://www.sqlite.org/deterministic.html
"3. Special-case Processing For Date/Time Functions

The built-in date and time functions of SQLite are a special case. These 
functions are usually considered deterministic. However, if these functions use 
the string "now" as the date, or if they use the localtime modifier or the utc 
modifier, then they are considered non-deterministic. Because the function 
inputs are not necessarily known until run-time, the date/time functions will 
throw an exception if they encounter any of the non-deterministic features in a 
context where only deterministic functions are allowed."


So is "julianday('now')" non-deterministic while "julianday()" _is_ 
deterministic? That seems a little weird considering they're the same thing... 
right?

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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Keith Medcalf]

On Thursday, 12 March, 2020 09:37, David Blake  wrote:

>What stops the
>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
>from also triggering the AFTER UPDATE ON recursively?

>Perhaps a pragma or inSQLite are  triggers non-recursive by default?

>I am using (now I have by semi-colons right)
>CREATE TRIGGER tgrUpdate AFTER UPDATE ON myTable FOR EACH ROW
>WHEN NEW.LastUpdate <= OLD. LastUpdate
>BEGIN
>UPDATE myTable SET  LastUpdate = DATETIME('now') WHERE id = OLD.id;
>END

>My intention is for the when to avoid infinite calls, but maybe I am
>fooling myself.

If you do not want the trigger to fire recursively you have to write it in such 
a way that it will not fire recursively since anyone can turn recursion on or 
off at any time.  Just because you decided to write a trigger that requires 
that recursive_triggers be turned off does not mean that recursive_triggers are 
turned off, merely that your design is insufficient.

It also depends if you want the "lastupdate" field to be an auditable field 
(that is, it is only changed when a row is updated and cannot otherwise be 
changed) or not.  If you want to make it an auditable field that cannot be 
tampered with, then you need many triggers to make that work properly.  

Alternatively, you can use the new GENERATED ALWAYS AS (...) STORED to make it 
an automatically updated stored field and you do not need triggers at all, just 
a version of SQLite3 that does generated columns (version 3.31.0 from 
2020-01-22 or later).

create table MyData
(
  id   integer primary key,
  data,
  lastupdate real as (julianday()) stored
);

(of course, you can put other "stuff" such as storing a iso8601 text timestamp 
if you want to (a) use more space and (b) have less precision)
(if you want a unixepoch secondstamp the use ((julianday()-2440587.5)*86400.0). 
 Resolution is only to the millisecond as that is all that is maintained 
internally and even the julianday double precision floating point format has 
enough significant digits to accurately portray milliseconds until well after 
we have to solve the Year 10K problem.)

Generated columns makes auditable "lastupdate" type data as simple to implement 
as using triggers to implement "createdon" type auditable data fields.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Simon Slavin]

On 12 Mar 2020, at 3:36pm, David Blake  wrote:

> What stops the
> UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
> from also triggering the AFTER UPDATE ON recursively?
> 
> Perhaps a pragma or inSQLite are  triggers non-recursive by default?

Bingo.


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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Raymond]

> What stops the
> UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
> from also triggering the AFTER UPDATE ON recursively?
> 
> Perhaps a pragma or inSQLite are  triggers non-recursive by default?
> 
> I am using (now I have by semi-colons right)
> CREATE TRIGGER tgrUpdate AFTER UPDATE ON myTable FOR EACH ROW
> WHEN NEW.LastUpdate <= OLD. LastUpdate
> BEGIN
> UPDATE myTable SET  LastUpdate = DATETIME('now') WHERE id = OLD.id;
> END
> 
> My intention is for the when to avoid infinite calls, but maybe I am
> fooling myself.

Recursive triggers are off by default.
Otherwise you could always add checks into the WHEN clause for seeing if any of 
the other fields was actually updated.

WHEN NEW.LastUpdate <= OLD.LastUpdate
 AND
 (   --Something actually changed
 NEW.Field1 is not OLD.Field1
 OR
 NEW.Field2 is not OLD.Field2
 OR ...
 --Think you want to exclude LastUpdate from this OR'd list of changed 
fields to check
 )

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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Ling, Andy]

>Thanks Andy

>> In addition, the role of the "when" clause is unclear.  Is it necessary?
>>
>> I don't think it is. I have a very similar trigger which I've been 
>> using for several years And it doesn't have the where...
>>
>> CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports  FOR EACH ROW  
>> BEGIN
>>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;  
>> END
>>

>What stops the
>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ; from also 
>triggering the AFTER UPDATE ON recursively?

>Perhaps a pragma or inSQLite are  triggers non-recursive by default?

It looks like this answers your question...

https://stackoverflow.com/questions/21223434/do-sqlite-triggers-trigger-other-triggers

Andy Ling



**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Blake]

Thanks Andy

> In addition, the role of the "when" clause is unclear.  Is it necessary?
>
> I don't think it is. I have a very similar trigger which I've been using
> for several years
> And it doesn't have the where...
>
> CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports
>  FOR EACH ROW
>  BEGIN
>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
>  END
>

What stops the
UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
from also triggering the AFTER UPDATE ON recursively?

Perhaps a pragma or inSQLite are  triggers non-recursive by default?

I am using (now I have by semi-colons right)
CREATE TRIGGER tgrUpdate AFTER UPDATE ON myTable FOR EACH ROW
WHEN NEW.LastUpdate <= OLD. LastUpdate
BEGIN
UPDATE myTable SET  LastUpdate = DATETIME('now') WHERE id = OLD.id;
END

My intention is for the when to avoid infinite calls, but maybe I am
fooling myself.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Richard Hipp]

On 3/12/20, Doug  wrote:
> Richard, what does the explain look like with your code change, please.

Test case:

CREATE TABLE t1(a);
explain SELECT coalesce(a, abs(-9223372036854775808)) FROM t1;

Before the change:

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 10000  Start at 10
1 OpenRead   0 2 0 1  00  root=2 iDb=0; t1
2 Explain2 0 0 SCAN TABLE t1  00
3 Rewind 0 9 000
4   Column 0 0 100  r[1]=t1.a
5   NotNull1 7 000  if r[1]!=NULL goto 7
6   SCopy  2 1 000  r[1]=r[2]
7   ResultRow  1 1 000  output=r[1]
8 Next   0 4 001
9 Halt   0 0 000
10Transaction0 0 1 0  01  usesStmtJournal=0
11Int64  0 3 0 -9223372036854775808  00
r[3]=-9223372036854775808
12Function   1 3 2 abs(1) 00  r[2]=func(r[3])
13Goto   0 1 000

Notice that the abs() function is invoked in the "prologue" code.  The
prologue begins on instruction 10 and continues through the Goto at
instruction 13.

After the change:

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 13000  Start at 13
1 OpenRead   0 2 0 1  00  root=2 iDb=0; t1
2 Explain2 0 0 SCAN TABLE t1  00
3 Rewind 0 12000
4   Column 0 0 100  r[1]=t1.a
5   NotNull1 10000  if
r[1]!=NULL goto 10
6   Once   0 9 000
7   Int64  0 3 0 -9223372036854775808  00
r[3]=-9223372036854775808
8   Function   1 3 2 abs(1) 00  r[2]=func(r[3])
9   SCopy  2 1 000  r[1]=r[2]
10  ResultRow  1 1 000  output=r[1]
11Next   0 4 001
12Halt   0 0 000
13Transaction0 0 1 0  01  usesStmtJournal=0
14Goto   0 1 000

Now the prologue is just instructions 13 and 14 and omits the abs()
function.  The abs() function is now computed on instructions 7 and 8,
but those instructions only run one time due to the "Once" opcode on
instruction 6.

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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Doug]

Richard, what does the explain look like with your code change, please.
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Richard Hipp
> Sent: Thursday, March 12, 2020 3:09 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-
> circuit sometimes
> 
> On 3/12/20, Hick Gunter  wrote:
> > Exactly what I gained from the EXPLAIN output.
> >
> > The SQL "compiler" is extracting the constant expression
> ABS(...) and
> > evaluating it in the program prolog (where schema is checked and
> locks
> > taken). See instructions 11 and 12
> 
> Exactly.  SQLite tries to factor out constant expressions into the
> prologue so that they are only evaluated once, rather than once
> for
> each row.  This is a performance optimization.
> 
> A traditional compiler like gcc or clang would do an enormous
> amount
> of code movement, similar to this, as well as other
> simplifications,
> to make the code run faster, especially with options like -O2.
> But
> the compiler for SQLite does not have that luxury.  Depending on
> the
> query, the time spent compiling the query into byte code can be a
> significant fraction of the total running time.  Hence, the
> compiler
> needs to be very fast.  This is an ongoing design struggle with
> SQLite:  how many CPU cycles do we burn trying to optimize the
> bytecode with the hopes of making up those lost CPU cycles with a
> shorter run-time?  Optimization is also constrained by the desire
> to
> keep the SQLite code small.  Hence, the optimizations applied by
> the
> SQLite byte-code compiler are relatively simple, so that they can
> be
> implemented with few CPU cycles and with few bytes of machine
> code.
> 
> Returning to the original discussion:  The underlying problem is
> that
> the constant expressions that get moved into the prologue, if they
> involve function calls, might throw an exception.  That is what is
> happening with abs(-9223372036854775808).  And that exception
> prevents
> the main body of the code from running, even if the offending
> expression was never actually going to be used.  The solution is
> to
> not factor out expressions that use functions, but instead use the
> OP_Once opcode (https://www.sqlite.org/opcode.html#Once) to
> prevent
> those expressions from being evaluated more than once.  This seems
> to
> make Coalesce (and CASE...END) short-circuit again.  And it also
> fixes
> ticket https://www.sqlite.org/src/info/3c9eadd2a6ba0aa5
> 
> That change is implemented by check-in
> https://www.sqlite.org/src/info/c5f96a085db9688a
> 
> 
> >
> > asql> explain select coalesce((SELECT 'hello'),ABS(-
> 9223372036854775808));
> > addr  opcode p1p2p3p4 p5
> comment
> >   -        -  --  --
> ---
> > 0 Init   0 11000  Start
> at 11
> > 1 Once   0 6 000
> > 2 Null   0 2 200
> r[2..2]=NULL; Init
> > subquery result
> > 3 Integer1 3 000
> r[3]=1; LIMIT
> > counter
> > 4 String80 2 0 hello  00
> r[2]='hello'
> > 5 DecrJumpZero   3 6 000  if (--
> r[3])==0
> > goto 6
> > 6 SCopy  2 1 000
> r[1]=r[2]
> > 7 NotNull1 9 000  if
> r[1]!=NULL goto
> > 9
> > 8 SCopy  4 1 000
> r[1]=r[4]
> > 9 ResultRow  1 1 000
> output=r[1]
> > 10Halt   0 0 000
> > 11Int64  0 5 0 -9223372036854775808  00
> > r[5]=-9223372036854775808
> > 12Function0  1 5 4 abs(1) 01
> r[4]=func(r[5])
> > 13Goto   0 1 000
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] Im
> > Auftrag von Jay Kreibich
> > Gesendet: Mittwoch, 11. März 2020 20:53
> > An: SQLite mailing list 
> > Cc: 38866424f8f6fc429174ff1ab2355...@mail.dessus.com
> > Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-
> circuit
> > sometimes
> >
> >
> >> On Mar 11, 2020, at 2:16 PM, Justin Ng
> 
> >> wrote:
> >>
> >> They generally do short-circuit but there are edge cases where
> they don't.
> >> It isn't entirely intuitive to me what the conditions are,
> though.
> >>
> >
> >
> > "ABS(-9223372036854775808)" is a constant expression, and as
> such, it makes
> > sense that it is evaluate during the parse/prepare phase of the
> processing,
> > not the execution.  There are similar problems in more
> traditional languages
> > (especially scripting languages) that attempt to optimize out or
> pre-compute
> > constant expressions.
> >
> > If that’s the case, then the issue is not so much that the
> COALESCE() is
> > failing to 

Re: [sqlite] Corrupted FTS5 index? disk image is malformed - Part II

[OrenKishon]

According to your pragmas you have vacuum done once in a while. Vacuum alters
the rowid values of a table, unless the rowid is declared explicitly as
"INTEGER PRIMARY KEY" (probably not the case here). See  from here
  :

/If the rowid is not aliased by INTEGER PRIMARY KEY then it is not
persistent and might change. In particular the VACUUM command will change
rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore,
applications should not normally access the rowid directly, but instead use
an INTEGER PRIMARY KEY.
/

Once rowids change, the references between the fts table to the content
table are broken. 




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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Richard Hipp]

On 3/12/20, Hick Gunter  wrote:
> Exactly what I gained from the EXPLAIN output.
>
> The SQL "compiler" is extracting the constant expression ABS(...) and
> evaluating it in the program prolog (where schema is checked and locks
> taken). See instructions 11 and 12

Exactly.  SQLite tries to factor out constant expressions into the
prologue so that they are only evaluated once, rather than once for
each row.  This is a performance optimization.

A traditional compiler like gcc or clang would do an enormous amount
of code movement, similar to this, as well as other simplifications,
to make the code run faster, especially with options like -O2.  But
the compiler for SQLite does not have that luxury.  Depending on the
query, the time spent compiling the query into byte code can be a
significant fraction of the total running time.  Hence, the compiler
needs to be very fast.  This is an ongoing design struggle with
SQLite:  how many CPU cycles do we burn trying to optimize the
bytecode with the hopes of making up those lost CPU cycles with a
shorter run-time?  Optimization is also constrained by the desire to
keep the SQLite code small.  Hence, the optimizations applied by the
SQLite byte-code compiler are relatively simple, so that they can be
implemented with few CPU cycles and with few bytes of machine code.

Returning to the original discussion:  The underlying problem is that
the constant expressions that get moved into the prologue, if they
involve function calls, might throw an exception.  That is what is
happening with abs(-9223372036854775808).  And that exception prevents
the main body of the code from running, even if the offending
expression was never actually going to be used.  The solution is to
not factor out expressions that use functions, but instead use the
OP_Once opcode (https://www.sqlite.org/opcode.html#Once) to prevent
those expressions from being evaluated more than once.  This seems to
make Coalesce (and CASE...END) short-circuit again.  And it also fixes
ticket https://www.sqlite.org/src/info/3c9eadd2a6ba0aa5

That change is implemented by check-in
https://www.sqlite.org/src/info/c5f96a085db9688a


>
> asql> explain select coalesce((SELECT 'hello'),ABS(-9223372036854775808));
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 11000  Start at 11
> 1 Once   0 6 000
> 2 Null   0 2 200  r[2..2]=NULL; Init
> subquery result
> 3 Integer1 3 000  r[3]=1; LIMIT
> counter
> 4 String80 2 0 hello  00  r[2]='hello'
> 5 DecrJumpZero   3 6 000  if (--r[3])==0
> goto 6
> 6 SCopy  2 1 000  r[1]=r[2]
> 7 NotNull1 9 000  if r[1]!=NULL goto
> 9
> 8 SCopy  4 1 000  r[1]=r[4]
> 9 ResultRow  1 1 000  output=r[1]
> 10Halt   0 0 000
> 11Int64  0 5 0 -9223372036854775808  00
> r[5]=-9223372036854775808
> 12Function0  1 5 4 abs(1) 01  r[4]=func(r[5])
> 13Goto   0 1 000
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
> Auftrag von Jay Kreibich
> Gesendet: Mittwoch, 11. März 2020 20:53
> An: SQLite mailing list 
> Cc: 38866424f8f6fc429174ff1ab2355...@mail.dessus.com
> Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit
> sometimes
>
>
>> On Mar 11, 2020, at 2:16 PM, Justin Ng 
>> wrote:
>>
>> They generally do short-circuit but there are edge cases where they don't.
>> It isn't entirely intuitive to me what the conditions are, though.
>>
>
>
> "ABS(-9223372036854775808)" is a constant expression, and as such, it makes
> sense that it is evaluate during the parse/prepare phase of the processing,
> not the execution.  There are similar problems in more traditional languages
> (especially scripting languages) that attempt to optimize out or pre-compute
> constant expressions.
>
> If that’s the case, then the issue is not so much that the COALESCE() is
> failing to short-circuit, but rather than the SQL statement failing to
> “compiling” an invalid statement.
>
> If you’re doing this in code as separate prepare/step/finalize, it would be
> interesting to see where it fails.  My guess is prepare, not step.
>
>   -j
>
> ___
> 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 |
> 

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Ling, Andy]

>On 12/03/2020 08:47, David Blake wrote:
>> I'm looking for an easy way to maintain a last updated column for each 
>> record in several tables and considering if using a triggers is viable.
>>
>> I thought that defining a trigger like this on each table would work
>>
>> CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
>>FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
>>BEGIN
>>UPDATE song SET  lastupdated = DATETIME('now') WHERE id = NEW.id
>>END

...

> In addition, the role of the "when" clause is unclear.  Is it necessary?

I don't think it is. I have a very similar trigger which I've been using for 
several years
And it doesn't have the where...

CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports
 FOR EACH ROW
 BEGIN
   UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
 END

Andy


**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Jean-Luc Hainaut]

On 12/03/2020 08:47, David Blake wrote:

I'm looking for an easy way to maintain a last updated column for each
record in several tables and considering if using a triggers is viable.

I thought that defining a trigger like this on each table would work

CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
   BEGIN
   UPDATE song SET  lastupdated = DATETIME('now') WHERE id = NEW.id
   END


As far as I know, updating the current row in a "before" trigger is a 
"nop" in SQLite since this row does not exist yet (for my information, 
could you check?). However, this works fine in an "after" trigger.


In addition, the role of the "when" clause is unclear.  Is it necessary?

JLH


--
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus

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

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Blake]

Ha, my error - missing ";" in the UPDATE clause but I was blind.

On Thu, 12 Mar 2020 at 08:18, David Blake  wrote:

> Thanks for such a swift reply, good to know that it should work (without
> typos)
>
> >Thank you very much for keeping the error message secret.
>
> near "END": syntax error:
>
>
> I'm testing out ideas using DB Browser, but will try in my app and see if
> this is just a DB Browser issue
>
> On Thu, 12 Mar 2020 at 08:04, Clemens Ladisch  wrote:
>
>> David Blake wrote:
>> > CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
>> >   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
>> >   BEGIN
>> >   UPDATE my_tableSET  lastupdated = DATETIME('now') WHERE id =
>> NEW.id
>> >   END
>> >
>> > The WHEN clause is an attempt to avoid infinite recursion that UPDATE
>> > within an UPDATE would cause.
>>
>> Typical UPDATE statements will leave this field with its old value, so
>> it might be a better ideas to use <= instead of <.
>>
>> > However I get SQL errors when I try defining a trigger this way this
>> > in my favorite db dbrowser.
>>
>> Thank you very much for keeping the error message secret.
>>
>> When I fix the wrong table table name and add the missing semicolon after
>> the UPDATE statement, this trigger works fine.
>>
>>
>> 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] Maintaing a Last updated timestamp column using triggers

[David Blake]

Thanks for such a swift reply, good to know that it should work (without
typos)

>Thank you very much for keeping the error message secret.

near "END": syntax error:


I'm testing out ideas using DB Browser, but will try in my app and see if
this is just a DB Browser issue

On Thu, 12 Mar 2020 at 08:04, Clemens Ladisch  wrote:

> David Blake wrote:
> > CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
> >   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
> >   BEGIN
> >   UPDATE my_tableSET  lastupdated = DATETIME('now') WHERE id = NEW.id
> >   END
> >
> > The WHEN clause is an attempt to avoid infinite recursion that UPDATE
> > within an UPDATE would cause.
>
> Typical UPDATE statements will leave this field with its old value, so
> it might be a better ideas to use <= instead of <.
>
> > However I get SQL errors when I try defining a trigger this way this
> > in my favorite db dbrowser.
>
> Thank you very much for keeping the error message secret.
>
> When I fix the wrong table table name and add the missing semicolon after
> the UPDATE statement, this trigger works fine.
>
>
> 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] Maintaing a Last updated timestamp column using triggers

[Clemens Ladisch]

David Blake wrote:
> CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
>   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
>   BEGIN
>   UPDATE song SET  lastupdated = DATETIME('now') WHERE id = NEW.id
>   END
>
> The WHEN clause is an attempt to avoid infinite recursion that UPDATE
> within an UPDATE would cause.

Typical UPDATE statements will leave this field with its old value, so
it might be a better ideas to use <= instead of <.

> However I get SQL errors when I try defining a trigger this way this
> in my favorite db dbrowser.

Thank you very much for keeping the error message secret.

When I fix the wrong table table name and add the missing semicolon after
the UPDATE statement, this trigger works fine.


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

Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes

[Hick Gunter]

Exactly what I gained from the EXPLAIN output.

The SQL "compiler" is extracting the constant expression ABS(...) and 
evaluating it in the program prolog (where schema is checked and locks taken). 
See instructions 11 and 12

asql> explain select coalesce((SELECT 'hello'),ABS(-9223372036854775808));
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 11000  Start at 11
1 Once   0 6 000
2 Null   0 2 200  r[2..2]=NULL; Init 
subquery result
3 Integer1 3 000  r[3]=1; LIMIT counter
4 String80 2 0 hello  00  r[2]='hello'
5 DecrJumpZero   3 6 000  if (--r[3])==0 goto 6
6 SCopy  2 1 000  r[1]=r[2]
7 NotNull1 9 000  if r[1]!=NULL goto 9
8 SCopy  4 1 000  r[1]=r[4]
9 ResultRow  1 1 000  output=r[1]
10Halt   0 0 000
11Int64  0 5 0 -9223372036854775808  00  
r[5]=-9223372036854775808
12Function0  1 5 4 abs(1) 01  r[4]=func(r[5])
13Goto   0 1 000

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jay Kreibich
Gesendet: Mittwoch, 11. März 2020 20:53
An: SQLite mailing list 
Cc: 38866424f8f6fc429174ff1ab2355...@mail.dessus.com
Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes


> On Mar 11, 2020, at 2:16 PM, Justin Ng  wrote:
>
> They generally do short-circuit but there are edge cases where they don't. It 
> isn't entirely intuitive to me what the conditions are, though.
>


"ABS(-9223372036854775808)" is a constant expression, and as such, it makes 
sense that it is evaluate during the parse/prepare phase of the processing, not 
the execution.  There are similar problems in more traditional languages 
(especially scripting languages) that attempt to optimize out or pre-compute 
constant expressions.

If that’s the case, then the issue is not so much that the COALESCE() is 
failing to short-circuit, but rather than the SQL statement failing to 
“compiling” an invalid statement.

If you’re doing this in code as separate prepare/step/finalize, it would be 
interesting to see where it fails.  My guess is prepare, not step.

  -j

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


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

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

[sqlite] Maintaing a Last updated timestamp column using triggers

[David Blake]

I'm looking for an easy way to maintain a last updated column for each
record in several tables and considering if using a triggers is viable.

I thought that defining a trigger like this on each table would work

CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
  FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
  BEGIN
  UPDATE song SET  lastupdated = DATETIME('now') WHERE id = NEW.id
  END

The WHEN clause is an attempt to avoid infinite recursion that UPDATE
within an UPDATE would cause. However I get SQL errors when I try defining
a trigger this way this in my favorite db dbrowser.

Maybe using triggers for this is flawed, and I should just ensure all
update statements set the column, or am I missing a trick?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users