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.
