Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Darren Duncan]

On 2019-10-29 6:20 a.m., Simon Slavin wrote:

• Every table must have at least one non-generated column.


I greatly admire this restriction.


From a relational purist perspective that restriction is counter-productive.

But then, disallowing a table or a key/unique constraint from having exactly 
zero columns is likewise counter-productive and SQL already does that.


Allowing zero-column primary key constraints is the most elegant way to restrict 
a table to having not more than 1 row, useful for storing singleton data like 
some application configuration settings for example.


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

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Simon Slavin]

On 29 Oct 2019, at 10:16pm, Warren Young  wrote:

> One question I had after reading the draft doc is whether an 
> application-defined SQLITE_DETERMINISTIC function can be used to compute a 
> generated column.

Hmm.  Well, I can see that any non-deterministic function would be a problem 
here.  How about applying the logic from DEFAULT values and CHECK constraints ? 
 Do they allow deterministic functions ?

I just tried to find documentation to answer that but failed.

>>   ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS … STORED 
>> columns
> 
> The doc should explain why this restriction exists, given that SQLite does 
> otherwise allow ALTER TABLE ADD COLUMN.

I believe that the developers don't want any ALTER TABLE command to have to 
rewrite all the data in the table.  I can see the reasoning.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[J Decker]

On Tue, Oct 29, 2019 at 5:08 PM Keith Medcalf  wrote:

>
> On Tuesday, 29 October, 2019 16:17, Warren Young 
> wrote:
>
> >On Oct 29, 2019, at 7:20 AM, Simon Slavin  wrote:
>
> >One question I had after reading the draft doc is whether an application-
> >defined SQLITE_DETERMINISTIC function can be used to compute a generated
> >column.  My immediate use case for this feature would require logic I’d
> >struggle to define in SQL, but which we already have logic for in C++.
> >Indeed, I’d be using this feature to cache the results of that C++ code
> >in the DB table, so it only needs to run when the source DB column
> >changes.
>
> But of course.
>
> sqlite> create table sintab(x real not null, sin as (sin(radians(x)))
> stored);
> sqlite> insert into sintab select value from generate_series where start=0
> and stop=90 and step=5;
> sqlite> select * from sintab;
> 0.0|0.0
> 5.0|0.0871557427476582
> 10.0|0.17364817766693
> 15.0|0.258819045102521
> 20.0|0.342020143325669
> 25.0|0.422618261740699
> 30.0|0.5
> 35.0|0.573576436351046
> 40.0|0.642787609686539
> 45.0|0.707106781186548
> 50.0|0.76603118978
> 55.0|0.819152044288992
> 60.0|0.866025403784439
> 65.0|0.90630778703665
> 70.0|0.939692620785908
> 75.0|0.965925826289068
> 80.0|0.984807753012208
> 85.0|0.996194698091746
> 90.0|1.0
>
> >> ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS …
> >> STORED columns
>
> > The doc should explain why this restriction exists, given that SQLite
> > does otherwise allow ALTER TABLE ADD COLUMN.
>
> For the same reason that you cannot add a column that does not have a
> default.  Adding a column merely adds the definition of the column, not the
> data for that column to each existing record of the database.  In order to
> be able to add a stored column, you must compute the value of that column
> for each record and update every record.
>
> You can add a virtual column, however, since it is the same thing as
> adding a regular column with a default -- the default is merely the
> computation expression and the result is never stored.
>
> However, I suppose it would theoretically be possible to add a generated
> stored column and have the value of the stored column computed on retrieval
> just like for a regular column that is added where the default is computed
> at retrieval time if the stored value does not exist -- and that the value
> would only become "stored" once the record were updated (just like how a
> regular added column is treated).
>
I like the sounds of that... but I would consider going one step further
and using that in an INDEX for the table with a deterministic function...
it would be nice if the data was only stored in the index.

The application of this I was thinking of is, I have user_id's.  Each
service gets a computed value of that user_id that makes it different
between every service, and being able to reverse lookup from the computed
user_id to the real user.row would be nice.

But, I suppose indexes are updated on every insert, and a full scan of the
table when created?  So it wouldn't really be sparse... like not all users
use all services, so the computed IDs would exist for lots of relations
that can't happen.



>
> > My first use of this feature will likely have me adding a STORED column
> > to an existing table, so unless this restriction is lifted before I get
> > around to using the new feature, I’ll be doing the old table migration
> > dance.
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Keith Medcalf]

On Tuesday, 29 October, 2019 16:17, Warren Young  wrote:

>On Oct 29, 2019, at 7:20 AM, Simon Slavin  wrote:

>One question I had after reading the draft doc is whether an application-
>defined SQLITE_DETERMINISTIC function can be used to compute a generated
>column.  My immediate use case for this feature would require logic I’d
>struggle to define in SQL, but which we already have logic for in C++.
>Indeed, I’d be using this feature to cache the results of that C++ code
>in the DB table, so it only needs to run when the source DB column
>changes.

But of course.

sqlite> create table sintab(x real not null, sin as (sin(radians(x))) stored);
sqlite> insert into sintab select value from generate_series where start=0 and 
stop=90 and step=5;
sqlite> select * from sintab;
0.0|0.0
5.0|0.0871557427476582
10.0|0.17364817766693
15.0|0.258819045102521
20.0|0.342020143325669
25.0|0.422618261740699
30.0|0.5
35.0|0.573576436351046
40.0|0.642787609686539
45.0|0.707106781186548
50.0|0.76603118978
55.0|0.819152044288992
60.0|0.866025403784439
65.0|0.90630778703665
70.0|0.939692620785908
75.0|0.965925826289068
80.0|0.984807753012208
85.0|0.996194698091746
90.0|1.0

>> ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS …
>> STORED columns

> The doc should explain why this restriction exists, given that SQLite
> does otherwise allow ALTER TABLE ADD COLUMN.

For the same reason that you cannot add a column that does not have a default.  
Adding a column merely adds the definition of the column, not the data for that 
column to each existing record of the database.  In order to be able to add a 
stored column, you must compute the value of that column for each record and 
update every record.

You can add a virtual column, however, since it is the same thing as adding a 
regular column with a default -- the default is merely the computation 
expression and the result is never stored.

However, I suppose it would theoretically be possible to add a generated stored 
column and have the value of the stored column computed on retrieval just like 
for a regular column that is added where the default is computed at retrieval 
time if the stored value does not exist -- and that the value would only become 
"stored" once the record were updated (just like how a regular added column is 
treated).

> My first use of this feature will likely have me adding a STORED column
> to an existing table, so unless this restriction is lifted before I get
> around to using the new feature, I’ll be doing the old table migration
> dance.

-- 
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] https://www.sqlite.org/draft/gencol.html Typo

[Warren Young]

On Oct 29, 2019, at 7:20 AM, Simon Slavin  wrote:
> 
> 
> 
> OMG.  Much welcomed feature.

Yes, I can see immediate use for this.

One question I had after reading the draft doc is whether an 
application-defined SQLITE_DETERMINISTIC function can be used to compute a 
generated column.  My immediate use case for this feature would require logic 
I’d struggle to define in SQL, but which we already have logic for in C++.  
Indeed, I’d be using this feature to cache the results of that C++ code in the 
DB table, so it only needs to run when the source DB column changes.

>ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS … STORED 
> columns

The doc should explain why this restriction exists, given that SQLite does 
otherwise allow ALTER TABLE ADD COLUMN.

My first use of this feature will likely have me adding a STORED column to an 
existing table, so unless this restriction is lifted before I get around to 
using the new feature, I’ll be doing the old table migration dance.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Doug]

Thanks for the clarification.
I suggest you use your words of explanation in the document to make it clearer 
instead of a simple sentence.

> -Original Message-
> From: sqlite-users 
> On Behalf Of Keith Medcalf
> Sent: Tuesday, October 29, 2019 2:29 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
> 
> 
> On Tuesday, 29 October, 2019 12:25, Doug 
> wondered:
> 
> >The draft says "Nor may a generated column depend on the ROWID."
> 
> >If my table uses ROWID by default:
> 
> >CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (id+a));
> 
> >where id is ROWID by default, is the generated column disallowed
> because
> >it has an implied dependency on ROWID?
> 
> sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (id+a));
> sqlite> insert into foo values (1,1),(2,1),(3,1);
> sqlite> select * from foo;
> 1|1|2
> 2|1|3
> 3|1|4
> sqlite> create table bar (a INTEGER, b AS (_rowid_ + a));
> Error: no such column: _rowid_
> sqlite> create table bar (a INTEGER, b AS (rowid + a));
> Error: no such column: rowid
> sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (_rowid_+a));
> Error: no such column: _rowid_
> sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (rowid + a));
> Error: no such column: rowid
> sqlite> create table bar (_rowid_ INTEGER PRIMARY KEY, a INTEGER,
> b as (_rowid_ + a));
> sqlite> insert into bar (a) values (1),(1),(1);
> sqlite> select * from bar;
> 1|1|2
> 2|1|3
> 3|1|4
> 
> So a generated column may not depend on the IMPLICITLY named rowid
> (by whatever magic you want to use), but it may depend on an
> EXPLICIT rowid (or alias).  In other words, only explicitly named
> columns are permitted.  If you EXPLICITY named the rowid alias to
> be _rowid_ it works without problem.  Same rule applies to FOREIGN
> KEY contraints which may only depend on EXPLICITLY named columns,
> not the IMPLICIT rowid.
> 
> --
> 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

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

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Keith Medcalf]

On Tuesday, 29 October, 2019 12:25, Doug  wondered:

>The draft says "Nor may a generated column depend on the ROWID."

>If my table uses ROWID by default:

>CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a));

>where id is ROWID by default, is the generated column disallowed because
>it has an implied dependency on ROWID?

sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a));
sqlite> insert into foo values (1,1),(2,1),(3,1);
sqlite> select * from foo;
1|1|2
2|1|3
3|1|4
sqlite> create table bar (a INTEGER, b AS (_rowid_ + a));
Error: no such column: _rowid_
sqlite> create table bar (a INTEGER, b AS (rowid + a));
Error: no such column: rowid
sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS (_rowid_+a));
Error: no such column: _rowid_
sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS (rowid + a));
Error: no such column: rowid
sqlite> create table bar (_rowid_ INTEGER PRIMARY KEY, a INTEGER, b as (_rowid_ 
+ a));
sqlite> insert into bar (a) values (1),(1),(1);
sqlite> select * from bar;
1|1|2
2|1|3
3|1|4

So a generated column may not depend on the IMPLICITLY named rowid (by whatever 
magic you want to use), but it may depend on an EXPLICIT rowid (or alias).  In 
other words, only explicitly named columns are permitted.  If you EXPLICITY 
named the rowid alias to be _rowid_ it works without problem.  Same rule 
applies to FOREIGN KEY contraints which may only depend on EXPLICITLY named 
columns, not the IMPLICIT rowid.

-- 
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] https://www.sqlite.org/draft/gencol.html Typo

[Dominique Pellé]

Keith Medcalf wrote:

> At the end of the second paragraph of section 2.1:
>
> Only VIRTUAL tables can be added using ALTER TABLE.
>
> should be
>
> Only VIRTUAL columns can be added using ALTER TABLE.

Above typos is already corrected, but here are 2 other typos
in the same page: https://sqlite.org/draft/gencol.html

section 2.1:  The value of a VIRTUAL *columns* (-> column) is computed
section 2.3: The value of a generated *columns* (-> column) is always

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

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Doug]

The draft says "Nor may a generated column depend on the ROWID."

If my table uses ROWID by default:

CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a));

where id is ROWID by default, is the generated column disallowed because it has 
an implied dependency on ROWID?

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 29, 2019 6:21 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
> 
> <https://www.sqlite.org/draft/gencol.html>
> 
> OMG.  Much welcomed feature.
> 
> > • Every table must have at least one non-generated column.
> 
> I greatly admire this restriction.
> 
> Can I suggest an addition to gencol.html ?  An explicit statement
> saying whether VIRTUAL and/or STORED columns can be used in CREATE
> INDEX.  In fact it's such an obvious question it might be useful
> to have the same note in lang_createindex.html .  Not to mention
> expridx.html .
> 
> On 29 Oct 2019, at 8:44am, Keith Medcalf 
> wrote:
> 
> > Only VIRTUAL columns can be added using ALTER TABLE.
> 
> I agree with your criticism but not your suggested alternative,
> because the command can be used to add other types of columns.  I
> suggest something like
> 
> ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS
> … STORED columns
> 
> be used on /both/ pages.
> ___
> 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] https://www.sqlite.org/draft/gencol.html Typo

[Simon Slavin]

OMG.  Much welcomed feature.

> • Every table must have at least one non-generated column.

I greatly admire this restriction.

Can I suggest an addition to gencol.html ?  An explicit statement saying 
whether VIRTUAL and/or STORED columns can be used in CREATE INDEX.  In fact 
it's such an obvious question it might be useful to have the same note in 
lang_createindex.html .  Not to mention expridx.html .

On 29 Oct 2019, at 8:44am, Keith Medcalf  wrote:

> Only VIRTUAL columns can be added using ALTER TABLE.

I agree with your criticism but not your suggested alternative, because the 
command can be used to add other types of columns.  I suggest something like

ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS … STORED 
columns

be used on /both/ pages.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Keith Medcalf]

At the end of the second paragraph of section 2.1:

Only VIRTUAL tables can be added using ALTER TABLE.

should be

Only VIRTUAL columns can be added using ALTER TABLE.

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