On Tue, Oct 29, 2019 at 5:08 PM Keith Medcalf <kmedc...@dessus.com> wrote:
> > 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). > 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
