- No index on a view in SQLite (so far). - A computed column can be maintained through appropriate triggers (here, "on insert" and "on update"). Efficient if you read data more than you modify them.
- Perhaps trying this: create table readings(...); create index trg_cx on readings(timestamp,(20+(a+b)/(c+c))); J-L Hainaut >I know that SQLite does not currently implement these things but I'm curious >if anyone else wants them and how hard they would be to implement. > >I have what you might consider to be a computed column. You might imagine > >CREATE TABLE readings > (timestamp TEXT PRIMARY KEY, > a REAL, b REAL, c REAL) > >and I constantly need to evaluate > >pressure = 20+(a+b)/(c+c) > >What I really want from SQLite is to support computed columns. I don't really >care which syntax is used but perhaps > >CREATE TABLE readings > (timestamp TEXT PRIMARY KEY, > a REAL, b REAL, c REAL, > (20+(a+b)/(c+c)) AS pressure) > >... or perhaps ... > >CREATE TABLE readings > (timestamp TEXT PRIMARY KEY, > a REAL, b REAL, c REAL, > pressure = (20+(a+b)/(c+c))) > >One can then, of course, do > >CREATE INDEX r_tp ON readings (timestamp,pressure DESC) > >That's my ideal. Second choice would be to be able to create an index on a >VIEW: > >CREATE TABLE readings > (timestamp TEXT PRIMARY KEY, > a REAL, b REAL, c REAL); >CREATE VIEW r_t_p (timestamp,pressure) > AS SELECT timestamp,(20+(a+b)/(c+c)) FROM readings; >CREATE INDEX r_tp ON VIEW r_t_p (timestamp, pressure DESC) > >At the moment I have to simulate the above abilities by creating both the VIEW >and an index with a calculated column independently, and even after that I >have to do two fetches to get the row of data I need. > >Surely I'm not alone in thinking that since SQLite now implements expressions >in indexes computed columns are a natural addition to SQLite at this time ? > >Simon. >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users