On Wed, Sep 3, 2014 at 6:16 PM, Simon Slavin <slav...@bigfraud.org> wrote: > I'm used to calling them 'computed columns' but yes, they should be > relatively easy to implement, as long as users accept a bunch of restructions > on what they can refer to, roughly equivalent to the restrictions on what can > be used in CHECK constraints. I had hoped for them in SQLite4. > > One alternative is to use VIEWs but, as has already been said in this thread, > one can't index a VIEW. And another alternative is to use TRIGGERs and that > works fine, but it's bulky and annoying to implement.
Triggers are very expensive. IMO an index on expressions would be extremely valuable. Computed columns go hand in hand with this, in my mind, as no extended CREATE INDEX syntax is needed then, but there's a gotcha: SELECT * on a table source with computed columns might result in much more work being done than the user might have expected. > The MERGE command would, I'm guessing, be far harder to implement because it > does such different things depending on what data exists in the tables. Not > only would writing the code be difficult but also thinking up the numerous > tricky things that would need to go into the test suite. A MERGE basically does something like three statements, an INSERT ... WHERE <doesn't exist and is to be added>; and UPDATE ... WHERE <exists and is to be updated>, and a DELETE ... WHERE <exists and must be deleted>;. I do this sort of thing all the time. Expanding a MERGE into something that shouldn't be hard. The real value of MERGE is that the underlying query that drives this need only be evaluated once. The SQLite3 VM, I think, can handle this, so it's mostly a matter of compiler code (yeah, I know, famous last words). Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
