[sqlite] convert zip to sqlar file

[Peng Yu]

Hi,

I'd like to convert zip files to sqlar files. Is there a command line
tool that I can help with the conversion? Thanks.

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

[sqlite] Troubleshooting corrupt indexes

[Jens Alfke]

We've found a few database instances with index corruption, and I'm unsure how 
to troubleshoot it.

The symptom is SELECT queries failing with SQLITE_CORRUPT. I've looked at the 
database files, and `pragma integrity_check` spits out five "row missing from 
index" errors on one index, and "wrong # of entries" on that same index and 
three others. Running `REINDEX` seems to repair everything, at least 
integrity_check reports no more problems.

The indexes aren't fancy. The one with the row-missing errors is simply
CREATE UNIQUE INDEX seq ON table (sequence)
where the `sequence` column is simply defined as `sequence INTEGER`.

The other indexes are all of the form
CREATE INDEX … ON table (customfn(body, 'key1'), customfn(body, 
'key2'), …)
where `customfn` is a custom function that extracts key-value data from the 
structured blob in `body`, somewhat like the regular `json_extract` function. 

I'm sure this custom function could conceivably cause this sort of error if it 
weren't properly deterministic, but it's been in use for a few years and pretty 
well tested, and I've never seen a bug with index corruption like this.

And I'm baffled how something as simple as the `seq` index could go awry…

—Jens [doggedly denying the existence of the new forum]

PS: We're using SQLite 3.28, statically linked into the app, on Android.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Richard Hipp]

On 3/24/20, Luuk  wrote:
> "The mailing list is deprecated. You need to go to
> https://sqlite.org/forum/ for the sqlite forum." 
>
> Can anyone give the source of this?

https://www.sqlite.org/support.html
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg119468.html

-- 
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] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Hick Gunter]

Weren't we all...

When I requested working from home office on march 10th, the company went from 
"No, we can't do that until it is mandated by the law" to "Come to the office 
only if you can't work from home" in a matter of days. With "Please verify if 
you can access the company VPN from home" in between.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Dienstag, 24. März 2020 09:26
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re: [EXTERNAL] 
No such column error]

OK i must have must the posts from the 12th of March till the end of that week 
, being busy with other things.

On 24-3-2020 09:19, Hick Gunter wrote:
> See announcement on the mailing list dated march 12th
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Richard Hipp
> Gesendet: Donnerstag, 12. März 2020 21:18
> An: General Discussion of SQLite Database
> 
> Betreff: [EXTERNAL] [sqlite] New SQLite Forum established - this
> mailing list is deprecated
>
> 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
> ___
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Luuk
> Gesendet: Dienstag, 24. März 2020 09:13
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re:
> [EXTERNAL] No such column error]
>
>
> On 24-3-2020 09:04, Luuk wrote:
>> "The mailing list is deprecated. You need to go to
>> https://sqlite.org/forum/ for the sqlite forum." 
>>
>> Can anyone give the source of this?
> No, i do NOT mean the source of the forum, but the source for "The list is 
> deprecated"
>
>
> ___
> 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-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

Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Luuk]

OK i must have must the posts from the 12th of March till the end of 
that week , being busy with other things.


On 24-3-2020 09:19, Hick Gunter wrote:

See announcement on the mailing list dated march 12th

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Donnerstag, 12. März 2020 21:18
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] New SQLite Forum established - this mailing list 
is deprecated

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
___


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Dienstag, 24. März 2020 09:13
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re: [EXTERNAL] 
No such column error]


On 24-3-2020 09:04, Luuk wrote:

"The mailing list is deprecated. You need to go to
https://sqlite.org/forum/ for the sqlite forum." 

Can anyone give the source of this?

No, i do NOT mean the source of the forum, but the source for "The list is 
deprecated"


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

Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Hick Gunter]

See announcement on the mailing list dated march 12th

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Donnerstag, 12. März 2020 21:18
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] New SQLite Forum established - this mailing list 
is deprecated

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
___


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Dienstag, 24. März 2020 09:13
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re: [EXTERNAL] 
No such column error]


On 24-3-2020 09:04, Luuk wrote:
> "The mailing list is deprecated. You need to go to
> https://sqlite.org/forum/ for the sqlite forum." 
>
> Can anyone give the source of this?

No, i do NOT mean the source of the forum, but the source for "The list is 
deprecated"


___
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

Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Petite Abeille]

> On Mar 24, 2020, at 09:13, Luuk  wrote:
> 
> No, i do NOT mean the source of the forum, but the source for "The list is 
> deprecated"


http://sqlite.1065341.n5.nabble.com/New-SQLite-Forum-established-this-mailing-list-is-deprecated-td111358.html

But that's just DRH. The mailing list is fully functional.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Norman Dunbar]

It originally came from Dr Richard Hipp. It's official.

HTH

Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767

On 24/03/2020 08:13, Luuk wrote:


On 24-3-2020 09:04, Luuk wrote:
"The mailing list is deprecated. You need to go to 
https://sqlite.org/forum/ for the sqlite forum." 


Can anyone give the source of this?


No, i do NOT mean the source of the forum, but the source for "The list 
is deprecated"



___
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] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Luuk]

On 24-3-2020 09:04, Luuk wrote:
"The mailing list is deprecated. You need to go to 
https://sqlite.org/forum/ for the sqlite forum." 


Can anyone give the source of this?


No, i do NOT mean the source of the forum, but the source for "The list 
is deprecated"



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

[sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Luuk]

"The mailing list is deprecated. You need to go to 
https://sqlite.org/forum/ for the sqlite forum." 


Can anyone give the source of this?

On 23-3-2020 14:35, Hick Gunter wrote:

The mailing list is deprecated. You need to go tohttps://sqlite.org/forum/  for 
the sqlite forum.

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

Re: [sqlite] [EXTERNAL] No such column error

[Hick Gunter]

The mailing list is deprecated. You need to go to https://sqlite.org/forum/ for 
the sqlite forum.

Can you replicate the problem while using the sqlite shell? Are you checking 
column names returned from the second statement? Note that a.BIRTH.YYY from 
your example looks a bit weird for a qualified column name.

My guess is that you are either not accessing the database file you want (rumor 
has it that windows has some strange goings on under the hood for certain file 
locations) or not handling transactions properly.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Montag, 23. März 2020 13:30
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] No such column error

I have been developing a c++ program in windows. In this program, in a loop, I 
open database, query and update the records then close it.

I am encountering randomly with "No such column error".

Query looks like that (but there are more columns than this STATUS table):
"select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS a;"

I randomly encounter with this error while executing sqlite3_prepare_v2 method. 
When encountered this error, I made a second execution of
sqlite3_prepare_v2  with "select * from STATUS a;" statement. Return value was 
SQLITE_OK.

Do you have any idea what is going on?

Thank you very much in advance,
___
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] No such column error

[Aydin Ozgur Yagmur]

I have been developing a c++ program in windows. In this program, in a
loop, I open database, query and update the records then close it.

I am encountering randomly with "No such column error".

Query looks like that (but there are more columns than this STATUS table):
"select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS
a;"

I randomly encounter with this error while executing sqlite3_prepare_v2
method. When encountered this error, I made a second execution of
sqlite3_prepare_v2  with "select * from STATUS a;" statement. Return value
was SQLITE_OK.

Do you have any idea what is going on?

Thank you very much in advance,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Use of same aliases in single query

[Rob Golsteijn]

Hi list,

Accidentally I discovered that I could use the same table aliases for 2 tables 
in a single sql statement.
As long as I do not use a column name that exists in both tables Sqlite accepts 
this.
It looks strange to me that I can use the same aliases multiple times, and that 
they co-exist.
As a programmer I'm not used to this, but the Sqlite docs do not seem to forbid 
this. Is this a bug or a feature? 
I guess because of backward compatibility this behavior will not be changed. 
Something to document in https://www.sqlite.org/quirks.html ?

Similar behavior for result row aliases, but in that case the ambiguous aliases 
can even be used.

Examples duplicate table aliases:



CREATE TABLE aaa (a INT, z INT);
CREATE TABLE bbb (b INT, z INT);

SELECT 1 FROM aaa x, bbb x ON x.a = x.b;  -- Same alias "x" for 2 tables, but 
no complaints (all column names in the query can be resolved)
SELECT 1 FROM aaa x, bbb x ON x.z = x.z;  -- "Error: ambiguous column name: x.z"
SELECT * FROM aaa x, bbb x ON x.a = x.b;  -- "Error: ambiguous column name: 
x.z" (during expansion of *)


Example duplicate result rows aliases:



SELECT

    x.a as y,
    x.z as y    -- Same alias, but no complaints
  FROM aaa x;

And the result row aliases can even be used in the query.

INSERT INTO "aaa" VALUES(1,2);
INSERT INTO "aaa" VALUES(1,3);

SELECT count(),
   x.a as y,
   x.z as y
  FROM aaa x
GROUP BY y;   -- No complaints, even though "y" is ambiguous here

count()|y|y
2|1|3 -- Looks like the first alias "y" is used.

Tested with versions 3.27.2 and 3.15.2.

Regards,
Rob Golsteijn

Met Vriendelijke Groet, Kind Regards, 谨致问候,


Rob


---


Rob Golsteijn     Software Engineer     Mapscape

Luchthavenweg 34  |  5657 EB  Eindhoven  |  The Netherlands 
Phone  +31 (0)40 7113583  |  Fax: +31 (0)40 711 3599  

www.mapscape.eu  


 

Mapscape B.V. is ISO9001:2008 certified.This e-mail and any attachment may 
contain corporate proprietary information and may only be read, copied and used 
by the intended recipient. If you have received it by mistake, please notify us 
immediately by reply e-mail and delete this e-mail and its attachments from 
your system. We believe but do not warrant that this message and any 
attachments are virus free. Mapscape B.V. is registered at the Kamer van 
Koophandel Oost-Brabant located in Eindhoven, The Netherlands number 17210210


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

Re: [sqlite] Fwd: [sqlite-forum] Convert datetime string to second since Epoch with millisecond precision

[Keith Medcalf]

select (strftime('%J', '2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0;
1465810594.123

-- 
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 Dominique Devienne
>Sent: Tuesday, 17 March, 2020 10:37
>To: General Discussion of SQLite Database us...@mailinglists.sqlite.org>
>Subject: [sqlite] Fwd: [sqlite-forum] Convert datetime string to second
>since Epoch with millisecond precision
>
>Reposting to the ML, maybe I'll have more luck there, than in the forum?
>--DD
>
>-- Forwarded message -
>From: ddevienne 
>Date: Tue, Mar 17, 2020 at 5:09 PM
>Subject: [sqlite-forum] Convert datetime string to second since Epoch
>with
>millisecond precision
>To: 
>
>
>Forum post by ddevienne on 2020-03-17 16:09:17
>https://sqlite.org/forum/forumpost/0d9c338ff1
>
>Hi. Below does what I want, but surely there's a better way than parsing
>the datetime 3 times? Because the below is ugly as hell. Thanks, --DD
>
>```
>C:\Users\ddevienne>sqlite3
>SQLite version 3.28.0 2019-04-16 19:49:53
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> select
>   ...>cast(strftime('%s', '2016-06-13T09:36:34.123Z') as real) +
>   ...> strftime('%f', '2016-06-13T09:36:34.123Z') -
>   ...>cast(strftime('%S', '2016-06-13T09:36:34.123Z') as real)
>   ...> ;
>1465810594.123
>sqlite>
>```
>--
>Subscription info:
>https://sqlite.org/forum/alerts/54F6DD420B31FA7F9F69F5498F1631F5E6D4B48CF
>97539FDEEF90F71733E90A9
>___
>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] Fwd: [sqlite-forum] Convert datetime string to second since Epoch with millisecond precision

[Dominique Devienne]

Reposting to the ML, maybe I'll have more luck there, than in the forum?
--DD

-- Forwarded message -
From: ddevienne 
Date: Tue, Mar 17, 2020 at 5:09 PM
Subject: [sqlite-forum] Convert datetime string to second since Epoch with
millisecond precision
To: 


Forum post by ddevienne on 2020-03-17 16:09:17
https://sqlite.org/forum/forumpost/0d9c338ff1

Hi. Below does what I want, but surely there's a better way than parsing
the datetime 3 times? Because the below is ugly as hell. Thanks, --DD

```
C:\Users\ddevienne>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select
   ...>cast(strftime('%s', '2016-06-13T09:36:34.123Z') as real) +
   ...> strftime('%f', '2016-06-13T09:36:34.123Z') -
   ...>cast(strftime('%S', '2016-06-13T09:36:34.123Z') as real)
   ...> ;
1465810594.123
sqlite>
```
-- 
Subscription info:
https://sqlite.org/forum/alerts/54F6DD420B31FA7F9F69F5498F1631F5E6D4B48CF97539FDEEF90F71733E90A9
___
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 James !

Definitely you've made a very good point !

Thumbs up !

Cheers !

On 16/3/20 17:37, James K. Lowden wrote:

On Fri, 13 Mar 2020 11:22:46 -0400
Richard Hipp  wrote:


4.  Your email address is never displayed, even to subscribers.

While I have no illusions of altering the online trajectory of this ML,
I'd like to point out that email addresses foster community.

Studies and experience both show that the incidental availability of
email addresses invites the formation of new relationships through
off-list communication. Speaking for myself, that opportunity has
manifested several new connections and even professional
opportunities.

I value those relationships.  I'm sorry this forum will cease to be a
place where new ones will develop.

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

[James K. Lowden]

On Fri, 13 Mar 2020 11:22:46 -0400
Richard Hipp  wrote:

> 4.  Your email address is never displayed, even to subscribers.

While I have no illusions of altering the online trajectory of this ML,
I'd like to point out that email addresses foster community.

Studies and experience both show that the incidental availability of
email addresses invites the formation of new relationships through
off-list communication. Speaking for myself, that opportunity has
manifested several new connections and even professional
opportunities.  

I value those relationships.  I'm sorry this forum will cease to be a
place where new ones will develop.  

--jkl
___
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 15, 2020, at 4:54 AM, no...@null.net wrote:
> 
> By chance or by design most emails to this list, like yours above, came
> wrapped at a length suitable for text-based readers. Yet every message
> I've received from the forum is a shocking cut-words-in-half affair in
> my terminal.

There’s a long-established email line-break-encoding convention called 
“format-flowed” you can thank for that. It allows the encoded text to be broken 
into 72-column lines while still noting where the actual line breaks are.

This way those newfangled mail clients with the astonishing ability to reflow 
text to the user’s desired width — some even use futuristic “proportional 
fonts” that were recently invented in the 1400s — can work their crazy magic, 
while the typical user reading on a VT100 or Teletype is not inconvenienced.

Seriously, RFC822 email is chock full of little affordances and edge cases like 
this. It’s not the sort of thing one blithely wades into. That’s part of the 
reason I gave for leaving forum software implementation to the experts, or at 
least to those who’ve had years to fix these kinds of mistakes.

—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 requires Javascript?

[J.B. Nicholson]

Richard Hipp wrote:

Please try again.


Thanks, this change allows me to read the articles.
___
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 Fri Mar 13, 2020 at 11:22:46AM -0400, Richard Hipp wrote:
> On 3/13/20, Huỳnh Trần Khanh  wrote:
> > [On a mailing nlist] I can
> > filter the posts, sort them, search through them, archive them,
> > forward them to a friend, 
> 
> You can do all of that with the SQLite Forum.  Remember, all content
> is still delivered directly to your in-box, just like with a mailing
> list, so anything you can do with content received from a mailing list
> can also be done with content from the forum.  But there are many

By chance or by design most emails to this list, like yours above, came
wrapped at a length suitable for text-based readers. Yet every message
I've received from the forum is a shocking cut-words-in-half affair in
my terminal.  There have been several other topics raised already on
the forum about the email format. Your statements above and below imply
that the only thing list users will miss is composing, but that is a
clearly premature position to take.

> things that the forum provides that a mailing list does now.  For
> example, if you want an archive of the forum activity, you can clone
> the entire history with one command:
> ..
> Then periodically "sync" to keep your private archive up-to-date.  Now
> you have all historical content, neatly packaged in an SQL database.
> You can extract and search and manage the content in this archive in
> any way you want.

Not something I've ever needed or would want to dedicate disk space
towards. Web-indexable contents enables search engines do a better job
of categorizing and linking than I could do, and don't want to spend
the time doing. What is the typical use case here?

> use the web interface in order to post a message.  In my experience,
> this forces people to take a little extra time to think about what
> they are saying, and to format and arrange their thoughts for clarity,
> and hence results in a better experience for the readers.

Posting too early is not something I can say I've noticed many people
doing.

> There are other important features that the forum provides that
> mailinglists typically do not:
> 
> 1.  You can format your postings using Markdown

Useful to those who prefer to read messages in a browser, I guess. Not
much of an advantage to those who prefer to read text. My client
doesn't seem to mangle EXPLAIN output like yours does.

> 2.  You can add hyperlinks to your postings that are consistently
> displayed and are not dependent on the idiosyncrasies of various
> email clients.

I don't quite understand this one.

> 3.  You can edit prior posts to fix typos or mistakes.

Fair enough.

> 4.  Your email address is never displayed, even to subscribers.

That is a mailing list configuration item, not a core difference
between a list and a forum.

> 5.  It is much easier to contribute anonymously to a web-based forum
> than it is to contribute on a mailing list.  There is no verification
> process to go through.  You just type in what you want to say and
> press "Submit".

Fair enough, if having an open mailing list comes with too many other
costs.

> 6.  Moderators have much better control over spam and other malicious
> content.

Ok.

> The first point (use of Markdown) is the killer feature for me.  There
> was a recent thread on this mailing list that involved people posting
> EXPLAIN output.  That text gets hopelessly jumbled on most email
> readers.  If those messages had been formatted with Markdown, they
> would have been much easier to read and understand.
> 
> I've been using both this mailing list and the Forum on Fossil
> regularly for two years now.  The forum is so much nicer that I have
> come to dread having to work with the legacy mailing list, at least
> for complex subjects.  It is time for a switch to better technology.

It is your project and your time spent managing the list (for which I
and many others are grateful) so your choice to make. I do however
think your approach was a little too fast and somewhat dismissive of
the concerns of subscribers and the cost to the community.

I would estimate that more around half of the value of this mailing
list come from discussion of topics outside of SQLite, and from several
key individuals willing to share their advanced knowledge and ideas.
That we will likely lose some of them, and/or the spontaneous
discussions that the mix of beginner and advanced, is a shame, and
could have been avoided.

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

Re: [sqlite] REMOVE ME

[Jose Isaias Cabrera]

Click at the link at the bottom of the email...


From: sqlite-users  on behalf of 
Edmondo Borasio 
Sent: Saturday, March 14, 2020 03:34 AM
To: SQLite mailing list 
Subject: [sqlite] REMOVE ME


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

[Edmondo Borasio]

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

Re: [sqlite] Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

[Keith Medcalf]

On Friday, 13 March, 2020 20:14, Justin Ng  wrote:

>I just encountered something weird with "temp" and "sqlite_master".
>I was wondering if it was another bug, or intentional.

The sqlite_master table in "temp" is called "sqlite_temp_master"  
(temp.sqlite_temp_master) even though it responds to the spelling 
temp.sqlite_master as well.  In either case the alias name is 
"sqlite_temp_master", not "sqlite_master".  You can specify your own alias name 
which will be respected.

Three level qualifiers are not recognized.  That is, you cannot use 
.. to identify a column -- it won't work.

I cannot answer why this is as it is, merely that this is indeed how it is.

-- 
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] Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

[Petite Abeille]

> On Mar 14, 2020, at 03:14, Justin Ng  wrote:
> 
> This works fine,
> 
> SELECT
>x.sql
> FROM
>temp.sqlite_master AS x;
> 

As is this:

SELECT
   sql
FROM
   temp.sqlite_master;

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

[sqlite] Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

[Justin Ng]

I just encountered something weird with "temp" and "sqlite_master".
I was wondering if it was another bug, or intentional.

-

This throws a "column not found" error,

SELECT
sqlite_master.sql
FROM
temp.sqlite_master;

-

This throws a "column not found" error,

SELECT
temp.sqlite_master.sql
FROM
temp.sqlite_master;

-

This works fine,

SELECT
x.sql
FROM
temp.sqlite_master AS x;

-

Referring to temp.sqlite_master columns directly in the WHERE clause also 
causes the error.
I assume it's the same for every other part of a query.
___
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 23:09, Michael Falconer  
> wrote:
> 
> So I'll take the opportunity now to thank (too many to single out) the many 
> contributors to this list who have made it unique, and well...just a nice 
> place to be.

So long, and thanks for all the fish :)

Will miss this place. Good bye and good luck to all. Take care.
___
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

[Michael Falconer]

Ahthe inevitable becomes reality. I knew when this stuff surfaced a few
years back that it was not going to go away, and this outcome was just a
matter of time. Not a great a great shock as a life in programming has seen
so much change. I don't need to Fossil, because I am one. Also a bit of a
lurker who has enjoyed lurking here for years because of the excellent
standard of technical replies and the interesting banter that evolves from
differing technical opinions. So I'll take the opportunity now to thank
(too many to single out) the many contributors to this list who have made
it unique, and well...just a nice place to be.

So while you are all off playing with your interface, javascript forum
thingy's I guess I'll have to find somewhere else to go. You'll notice I
did not mention any specific technical objections and that's because
sometimes (even in I.T.) it's just not about the tech! Can't help feeling
that someone just shot my dog!



On Sat, 14 Mar 2020 at 07:01, Jim Dodgen  wrote:

> Another lurker here ...
> I have always preferred mailing lists and found forums to be too fat.  That
> said, I like the fossil forum.  It was referred to bring a "enginerish"
> style which is fine given the audience
> Kudos for its speed and light weight,  No fluff just data.
>
> Jim "Jed" Dodgen
> j...@dodgen.us
>
>
> On Fri, Mar 13, 2020 at 12:24 PM Simon Slavin 
> wrote:
>
> > On 13 Mar 2020, at 6:50pm, Thomas Kurz  wrote:
> >
> > > Why can't the forum just forward all new postings to this mailing list
> > and vice versa? Then everyone could chose what to use ;)
> >
> > I think one of the objectives of moving away from email is to prevent
> > email spam.
> > ___
> > 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
>


-- 
Regards,
 Michael.j.Falconer.
___
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

[Jim Dodgen]

Another lurker here ...
I have always preferred mailing lists and found forums to be too fat.  That
said, I like the fossil forum.  It was referred to bring a "enginerish"
style which is fine given the audience
Kudos for its speed and light weight,  No fluff just data.

Jim "Jed" Dodgen
j...@dodgen.us


On Fri, Mar 13, 2020 at 12:24 PM Simon Slavin  wrote:

> On 13 Mar 2020, at 6:50pm, Thomas Kurz  wrote:
>
> > Why can't the forum just forward all new postings to this mailing list
> and vice versa? Then everyone could chose what to use ;)
>
> I think one of the objectives of moving away from email is to prevent
> email spam.
> ___
> 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]

On 13 Mar 2020, at 6:50pm, Thomas Kurz  wrote:

> Why can't the forum just forward all new postings to this mailing list and 
> vice versa? Then everyone could chose what to use ;)

I think one of the objectives of moving away from email is to prevent email 
spam.
___
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

[P Kishor]

In a way it already does… not to the mailing list but to the email address of 
everyone registered on the forum. The key thing you can’t do is post via email. 

My personal view is there never be a solution that will please everyone. But it 
is Richard’s software and Richard’s game, and we have to take his word that the 
web-based forum is better and give it a chance. That way we can focus on 
solving SQLite-related problems instead of web/email/forum related problems.

Fwiw, I like the new forum because of the few key differences already mentioned 
by Richard – editing, formatting, threading (and don’t like a few things – 
readability, styling – but these can be tweaked). I know many don’t like it, 
but that is how it is. I hope we can give the new forum a chance.

> On Mar 13, 2020, at 7:50 PM, Thomas Kurz  wrote:
> 
> Why can't the forum just forward all new postings to this mailing list and 
> vice versa? Then everyone could chose what to use ;)

___
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

[Thomas Kurz]

Why can't the forum just forward all new postings to this mailing list and vice 
versa? Then everyone could chose what to use ;)


- Original Message - 
From: Richard Hipp 
To: General Discussion of SQLite Database 
Sent: Thursday, March 12, 2020, 21:17:59
Subject: [sqlite] New SQLite Forum established - this mailing list is deprecated

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] sqlite-users Digest, Vol 147, Issue 13

[Balaji Ramanathan]

Thank you, thank you!

Balaji Ramanathan
-- Forwarded message --
From: Richard Hipp 
To: General Discussion of SQLite Database <
sqlite-users@mailinglists.sqlite.org>
Cc:
Bcc:
Date: Thu, 12 Mar 2020 16:17:59 -0400
Subject: [sqlite] New SQLite Forum established - this mailing list is
deprecated
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] New SQLite Forum requires Javascript?

[Richard Hipp]

On 3/13/20, J.B. Nicholson  wrote:
>
> But every link in that table which would (again I assume) point to a page
> with that
> thread's text instead points to https://sqlite.org/forum/honeypot .

Ah.  That's the anti-robot defense mechanism.  See
https://fossil-scm.org/fossil/doc/trunk/www/antibot.wiki for details.

Since the Forum does not (yet) have diffs and annotations and tarballs
and all the other features of Fossil that cause mindless robots to
burn CPU cycles on the server, I suppose I can disable that mechanism
for the Forum, which I have now done.

Please try again.
-- 
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

[Huỳnh Trần Khanh]

> I’m not sure I’d agree with that.  People tend to quote when replying to 
> mail, even if just one line, to provide context.  That’s a lot less true of 
> the forums, especially if you’re responding to a one line question or comment 
> on the message right above yours.   Since the forum emails only quote the new 
> message, there is much less context in any given message.  Part of why I like 
> email lists is that they may be old school, but people that use them well 
> make sure there is very very little state in the conversation.  That’s the 
> exact opposite with forum posts.

Couldn't agree more. I am a teenager who has to go to school and
attend classes regularly, and some of the days I am very, very busy.
The natural quoting style ubiquitous in emails means that I can
quickly scan through the message without missing much context. At the
end of the day I need to be productive.


On Fri, Mar 13, 2020 at 11:15 PM Jay Kreibich  wrote:
>
>
> > On Mar 13, 2020, at 10:22 AM, Richard Hipp  wrote:
> >
> > On 3/13/20, Huỳnh Trần Khanh  wrote:
> >> [On a mailing nlist] I can
> >> filter the posts, sort them, search through them, archive them,
> >> forward them to a friend, 
> >
> > You can do all of that with the SQLite Forum.  Remember, all content
> > is still delivered directly to your in-box, just like with a mailing
> > list,
>
> I’m not sure I’d agree with that.  People tend to quote when replying to 
> mail, even if just one line, to provide context.  That’s a lot less true of 
> the forums, especially if you’re responding to a one line question or comment 
> on the message right above yours.   Since the forum emails only quote the new 
> message, there is much less context in any given message.  Part of why I like 
> email lists is that they may be old school, but people that use them well 
> make sure there is very very little state in the conversation.  That’s the 
> exact opposite with forum posts.
>
> I’m not saying the forum isn’t better for the team, simply that they’re not 
> equal.  And at the end of the day, forums must be engaged in actively (I need 
> to go visit it), while email is passive (it comes to me in a media I scan 
> regularly).  For someone that is largely a lurker these days, it was easy to 
> just watch messages go by and jump in if needed, having most of the context 
> of the conversation.  With a forum, the email notifications are much less 
> useful (and less likely to trigger the “Oh, I know that..” response), and I’m 
> never going to go visit the forum just to see what’s up.
>
> This might be better for the community, but it largely means I’m out of the 
> community.  I’m not sure that’s a particular loss for me, or the community, 
> as I’ve not been very active for a decade or so.
>
>   -j
>
>
> ___
> 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?

[J.B. Nicholson]

Richard Hipp wrote:

As far as I know, the forum only uses javascript to scroll to the most
recent posting when you load a new thread.  So if you don't mind
scrolling manually, I think everything else will just work.  Did you
try it?


I did try it and your description does not match my experience.

Here's what I tried: I visited https://sqlite.org/forum which redirected me to 
https://sqlite.org/forum/forummain and there I see a list of "Most recent threads" 
with a table of (I assume) recent threads and text at the bottom telling me how long 
it took to generate that page.


But every link in that table which would (again I assume) point to a page with that 
thread's text instead points to https://sqlite.org/forum/honeypot . Visiting 
https://sqlite.org/forum/honeypot returns a 1-paragraph page which says "Please 
enable javascript or log in to see this content".


Hence I don't get to read the threads from the thread table.
___
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/13/20, Philip Bennefall  wrote:
> Should I write to you directly, or to the Fossil forum?

Either one (or both) will be fine.
-- 
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

[Philip Bennefall]

I am happy to do that, but I can't promise any kind of timeline as it 
will have to be in my free time only. Can't really sign well, though, as 
I am totally blind. But I guess we'll cross that bridge if I come up 
with a working audio captcha solution.


I'll have a look at the Fossil captcha code this evening and write back 
if I have questions. Should I write to you directly, or to the Fossil forum?


Thanks!

Philip


On 3/13/2020 5:16 PM, Richard Hipp wrote:

On 3/13/20, Philip Bennefall  wrote:

I submitted
a thread a while back offering to work on an audio captcha for Fossil,

I don't recall that thread.  But if you want to submit code that
generates an audio file of some kind that speaks the text of a Fossil
Captcha, that would be great.  I will build it into the system,
assuming it works, does not have onerous external dependencies, and
you can get me a signed CLA.



___
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/13/20, Philip Bennefall  wrote:
> I submitted
> a thread a while back offering to work on an audio captcha for Fossil,

I don't recall that thread.  But if you want to submit code that
generates an audio file of some kind that speaks the text of a Fossil
Captcha, that would be great.  I will build it into the system,
assuming it works, does not have onerous external dependencies, and
you can get me a signed CLA.

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

[Jay Kreibich]

> On Mar 13, 2020, at 10:22 AM, Richard Hipp  wrote:
> 
> On 3/13/20, Huỳnh Trần Khanh  wrote:
>> [On a mailing nlist] I can
>> filter the posts, sort them, search through them, archive them,
>> forward them to a friend, 
> 
> You can do all of that with the SQLite Forum.  Remember, all content
> is still delivered directly to your in-box, just like with a mailing
> list,

I’m not sure I’d agree with that.  People tend to quote when replying to mail, 
even if just one line, to provide context.  That’s a lot less true of the 
forums, especially if you’re responding to a one line question or comment on 
the message right above yours.   Since the forum emails only quote the new 
message, there is much less context in any given message.  Part of why I like 
email lists is that they may be old school, but people that use them well make 
sure there is very very little state in the conversation.  That’s the exact 
opposite with forum posts.

I’m not saying the forum isn’t better for the team, simply that they’re not 
equal.  And at the end of the day, forums must be engaged in actively (I need 
to go visit it), while email is passive (it comes to me in a media I scan 
regularly).  For someone that is largely a lurker these days, it was easy to 
just watch messages go by and jump in if needed, having most of the context of 
the conversation.  With a forum, the email notifications are much less useful 
(and less likely to trigger the “Oh, I know that..” response), and I’m never 
going to go visit the forum just to see what’s up.

This might be better for the community, but it largely means I’m out of the 
community.  I’m not sure that’s a particular loss for me, or the community, as 
I’ve not been very active for a decade or so.

  -j


___
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

[Philip Bennefall]

If you are comfortable doing that for any blind users who stop by, then 
of course that would be a quick fix. But it feels like taking a step 
backwards, from being able to subscribe without assistance to having to 
contact the author of whatever Fossil forum this applies to. I submitted 
a thread a while back offering to work on an audio captcha for Fossil, 
but cannot locate the exact post. I did not receive much response at the 
time, but am wondering if this would be of interest?


Thanks!

Kind regards,

Philip Bennefall
On 3/13/2020 4:53 PM, Richard Hipp wrote:

On 3/13/20, Philip Bennefall  wrote:

Is there a solution in the pipeline for the inaccessible captcha in the
forum for visually impaired users?

The solution is for you to send me a private email asking for me to
create your account for you, as doing that is way, way easier than
trying to engineer an audible captcha.



___
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/13/20, Philip Bennefall  wrote:
> Is there a solution in the pipeline for the inaccessible captcha in the
> forum for visually impaired users?

The solution is for you to send me a private email asking for me to
create your account for you, as doing that is way, way easier than
trying to engineer an audible captcha.

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

[Philip Bennefall]

Hi Richard,


Is there a solution in the pipeline for the inaccessible captcha in the 
forum for visually impaired users? I brought this up when the forum was 
first released and you kindly created an account manually for me, but it 
doesn't seem as though the problem was actually solved in the system 
itself. I just tried to create an account on the SqLite forum, but could 
not. The absolute majority of captcha systems offer some kind of audio 
version now, which is very important for me and other blind users.



Kind regards,


Philip Bennefall


On 3/13/2020 4:22 PM, Richard Hipp wrote:

On 3/13/20, Huỳnh Trần Khanh  wrote:

[On a mailing nlist] I can
filter the posts, sort them, search through them, archive them,
forward them to a friend, 

You can do all of that with the SQLite Forum.  Remember, all content
is still delivered directly to your in-box, just like with a mailing
list, so anything you can do with content received from a mailing list
can also be done with content from the forum.  But there are many
things that the forum provides that a mailing list does now.  For
example, if you want an archive of the forum activity, you can clone
the entire history with one command:

 fossil clone https://sqlite.org/forum sqlite-forum.fossil

Then periodically "sync" to keep your private archive up-to-date.  Now
you have all historical content, neatly packaged in an SQL database.
You can extract and search and manage the content in this archive in
any way you want.

The only thing that you can do with a mailing list that the SQLite
Forum does not allow is to submit new postings via email.  You must
use the web interface in order to post a message.  In my experience,
this forces people to take a little extra time to think about what
they are saying, and to format and arrange their thoughts for clarity,
and hence results in a better experience for the readers.

There are other important features that the forum provides that
mailinglists typically do not:

1.  You can format your postings using Markdown

2.  You can add hyperlinks to your postings that are consistently
displayed and are not dependent on the idiosyncrasies of various email
clients.

3.  You can edit prior posts to fix typos or mistakes.

4.  Your email address is never displayed, even to subscribers.

5.  It is much easier to contribute anonymously to a web-based forum
than it is to contribute on a mailing list.  There is no verification
process to go through.  You just type in what you want to say and
press "Submit".

6.  Moderators have much better control over spam and other malicious content.

The first point (use of Markdown) is the killer feature for me.  There
was a recent thread on this mailing list that involved people posting
EXPLAIN output.  That text gets hopelessly jumbled on most email
readers.  If those messages had been formatted with Markdown, they
would have been much easier to read and understand.

I've been using both this mailing list and the Forum on Fossil
regularly for two years now.  The forum is so much nicer that I have
come to dread having to work with the legacy mailing list, at least
for complex subjects.  It is time for a switch to better technology.


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

[Richard Hipp]

On 3/13/20, Warren Young  wrote:
>>
>> Is there a way to use this without running the Javascript?
>
> It should be, but I lack the time right now to test it.  What I can do is
> point you to our documentation on how Fossil uses Javascript now:
>
> https://fossil-scm.org/fossil/doc/js-use-doc/www/javascript.md
>
> If there’s some problem that prevents you from using the forum without
> JavaScript, we’ll certainly consider it a significant issue to be addressed.

As far as I know, the forum only uses javascript to scroll to the most
recent posting when you load a new thread.  So if you don't mind
scrolling manually, I think everything else will just work.  Did you
try it?

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

[Richard Hipp]

On 3/13/20, Huỳnh Trần Khanh  wrote:
> [On a mailing nlist] I can
> filter the posts, sort them, search through them, archive them,
> forward them to a friend, 

You can do all of that with the SQLite Forum.  Remember, all content
is still delivered directly to your in-box, just like with a mailing
list, so anything you can do with content received from a mailing list
can also be done with content from the forum.  But there are many
things that the forum provides that a mailing list does now.  For
example, if you want an archive of the forum activity, you can clone
the entire history with one command:

fossil clone https://sqlite.org/forum sqlite-forum.fossil

Then periodically "sync" to keep your private archive up-to-date.  Now
you have all historical content, neatly packaged in an SQL database.
You can extract and search and manage the content in this archive in
any way you want.

The only thing that you can do with a mailing list that the SQLite
Forum does not allow is to submit new postings via email.  You must
use the web interface in order to post a message.  In my experience,
this forces people to take a little extra time to think about what
they are saying, and to format and arrange their thoughts for clarity,
and hence results in a better experience for the readers.

There are other important features that the forum provides that
mailinglists typically do not:

1.  You can format your postings using Markdown

2.  You can add hyperlinks to your postings that are consistently
displayed and are not dependent on the idiosyncrasies of various email
clients.

3.  You can edit prior posts to fix typos or mistakes.

4.  Your email address is never displayed, even to subscribers.

5.  It is much easier to contribute anonymously to a web-based forum
than it is to contribute on a mailing list.  There is no verification
process to go through.  You just type in what you want to say and
press "Submit".

6.  Moderators have much better control over spam and other malicious content.

The first point (use of Markdown) is the killer feature for me.  There
was a recent thread on this mailing list that involved people posting
EXPLAIN output.  That text gets hopelessly jumbled on most email
readers.  If those messages had been formatted with Markdown, they
would have been much easier to read and understand.

I've been using both this mailing list and the Forum on Fossil
regularly for two years now.  The forum is so much nicer that I have
come to dread having to work with the legacy mailing list, at least
for complex subjects.  It is time for a switch to better technology.
-- 
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 requires Javascript?

[Warren Young]

On Mar 12, 2020, at 9:19 PM, 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?

It should be, but I lack the time right now to test it.  What I can do is point 
you to our documentation on how Fossil uses Javascript now:

https://fossil-scm.org/fossil/doc/js-use-doc/www/javascript.md

If there’s some problem that prevents you from using the forum without 
JavaScript, we’ll certainly consider it a significant issue to be addressed.

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

Re: [sqlite] SQLite forum posts are about the forum, not SQLite

[Huỳnh Trần Khanh]

That should be. Also as stated in a previous email, I want to interact
with the forum via email also. I mean, I have been using web forums
for a long time and I find the UX _really,_ really lacking. GitHub
allows me to comment on issues by email, which is more convenient for
me.

On Fri, Mar 13, 2020 at 8:47 PM Dominique Devienne  wrote:
>
> I hope that's only a temporary situation... --DD
> ___
> 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] SQLite forum posts are about the forum, not SQLite

[Dominique Devienne]

I hope that's only a temporary situation... --DD
___
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

[Huỳnh Trần Khanh]

No. Web-based forums are terrible. I prefer mailing lists. I can
filter the posts, sort them, search through them, archive them,
forward them to a friend,  The same experience **can't be
replicated** on a web-based forum. Also the styling is terrible.

At the very least I'd love a feature where I could subscribe to the
forum and interact with the forum in the same way I would with mailing
lists.

On Fri, Mar 13, 2020 at 8:14 PM Jose Isaias Cabrera  wrote:
>
>
> x, on Friday, March 13, 2020 09:01 AM, wrote...
> >
> > * I truly hope Keith you'll continue making your tremendous contributions
> > to
> > the SQLite community.
> >
> I would say to the SQL world.
>
> josé
> ___
> 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

[Jose Isaias Cabrera]

x, on Friday, March 13, 2020 09:01 AM, wrote...
>
> * I truly hope Keith you'll continue making your tremendous contributions
> to
> the SQLite community.
>
I would say to the SQL world.

josé
___
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

[x]

  *   I truly hope Keith you'll continue making your tremendous contributions to
the SQLite community.



Ditto.



___
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

[dirdi]

Since Fossil was founded and is developed by drh as well, this step was
not that unpredictable.

If you compare the forum to the mailing list the only drawback I can see
is that one is no longer able to contribute via mail:

   | Mailing list | Forum
---+--+-
Web Interface  | Read [0] | Read & Write
Mail Interface | Read & Write | Read

Like BohwaZ already suggested, this could be mitigated by making it
> 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.

Of course only mails by registered users should be accepted, to keep
spam low.

Maybe someone wants to open a feature request over at the Fossil SCM
forum for this [1].

my 2 cent

[0] https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/
[1] https://fossil-scm.org/forum/forum
___
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

[Mike King]

As an occasional poster I like the idea of a forum as for me it's easier to
dip in and follow threads (after lots of years of using System.Data.Sqlite
I'm reluctantly moving over to using the Microsoft drivers because it
supports EF Core).

For the look and feel, it's plain but it's functional. I'm reminded of the
quote from Deng Xiaoping  "No matter if it is a white cat or a black cat;
as long as it can catch mice, it is a good cat."

Cheers,

Mike

On Thu, 12 Mar 2020 at 20:18, 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

[Ling, Andy]

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

Having just had a go at subscribing, I screwed up and mistyped my email address.
Is there any way for me to change it?

Regards

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] New SQLite Forum established - this mailing list is deprecated

[Kees Nuyt]

On Fri, 13 Mar 2020 10:09:38 +, you wrote:

>On 13 Mar 2020, at 09:35, Peter da Silva  wrote:
>
>> What do you mean there have been "just a few threads" in the mailing list?
>> I can barely keep up with it.
>
> As a lurker I can say that this mailing list suits just
> fine. Another forum means another username/password I have
> to manage. 

I'm mostly a lurker myself. I lurk many mailing lists, all
require a username/password. One more doesn't matter much.
My experience with the fossil-forum is positive, it has not
changed my reading habits. I just subscribed to its email
notifications, and I get everything threaded in the same way
as a mailing list.

> On a list, the contributions come in date order
> which is easier to manage than, rather than as with a forum,
> having them grouped by thread. In fact, with this list,
> there's no management at all required. An advantage of
> writing one's own email client is extra added features which
> are just right for me: I can limit the size of a mailbox to
> a certain number of mails. For the sqlite list, it's 10k
> mails. More than that and the oldest get moved to the Trash
> automatically.
> It's all I need.

With a subscription to email notifications of the sqlite-forum
you can lurk in just the same way as the sqlite-users mailing list.
Threaded or in date order, whatever your email client supports.
Just try it, your worries will vanish soon.

-- 
Regards,
Kees Nuyt
___
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

[Dominique Devienne]

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

I truly hope Keith you'll continue making your tremendous contributions to
the SQLite community.

Things will inevitably move over to the Forum I'm afraid, but I'd
rather you registered to the forum
(one time setup, with email notifications) and replied on the (deprecated)
ML, rather than having
none your usual insightful answers because you decided to stay exclusively
on the ML (or worse,
if you completely gave up on both). FWIW. --DD

PS: I kinda recall the prospect of switching to a forum wasn't exactly well
received last time it
  was discussed on this ML, and especially so from "heavyweights" of this
ML, it's thus disappointing
  Richard forced that switch on us.
___
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

[Tim Streater]

On 13 Mar 2020, at 09:35, Peter da Silva  wrote:

> What do you mean there have been "just a few threads" in the mailing list?
> I can barely keep up with it.

As a lurker I can say that this mailing list suits just fine. Another forum 
means another username/password I have to manage. On a list, the contributions 
come in date order which is easier to manage than, rather than as with a forum, 
having them grouped by thread. In fact, with this list, there's no management 
at all required. An advantage of writing one's own email client is extra added 
features which are just right for me: I can limit the size of a mailbox to a 
certain number of mails. For the sqlite list, it's 10k mails. More than that 
and the oldest get moved to the Trash automatically.

It's all I need.


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

[J Decker]

On Thu, Mar 12, 2020 at 10:32 PM Wout Mertens 
wrote:

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

Lynx doesn't support JS...  (it's the only one I know)

 https://stackoverflow.com/questions/9353519/lynx-with-javascript

http://elinks.or.cz/  is another text based browser   (from that)
a little more searching there's https://www.brow.sh/


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

[Peter da Silva]

What do you mean there have been "just a few threads" in the mailing list?
I can barely keep up with it.
___
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

[Rowan Worth]

On Fri, 13 Mar 2020 at 08:15, Jens Alfke  wrote:

> In a messaging system, the user interface is critically important.


Agree absolutely!

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.


But I take issue with this -- I find latency is a critical part of a user
interface, and the vast majority of websites and mobile apps I encounter
just get worse and worse in this area. Granted most of that is probably not
rendering time, but I find it incredibly frustrating having to wait for
billions of cycles for the UI to get ready to accept my input.

What's important is usability — following discussions, finding new content,
> reading it, and composing messages.



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

I can't figure out what you're actually objecting to, because the
information currently on the forum seems be arranged sensibly and fit the
criteria of being easy to read and follow.

Finding content is impossible to assess at present, although the search
functionality seems broken -- which I posted about and the composition
process was painless.

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

[ingo]

Can we clone (push, pull, sync) the forum fossil?

Ingo

On 12-3-2020 21:17, Richard Hipp wrote:

The Forum is powered by Fossil.

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

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