- 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
