On Tuesday, 29 October, 2019 16:17, Warren Young <war...@etr-usa.com> wrote:
>On Oct 29, 2019, at 7:20 AM, Simon Slavin <slav...@bigfraud.org> 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.766044443118978 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
