[sqlite] Computed column or create index on a view ?
- 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
[sqlite] Computed column or create index on a view ?
On 15 May 2016, at 4:08pm, Jean-Luc Hainaut wrote: > - 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. Hmm. Yes, you're right. Your way of simulating it is better than my way of simulating it. It just needs triggers on INSERT and UPDATE, and I can create an index on the table itself and I don't need a view. That's neat. Thanks. Simon.
[sqlite] Computed column or create index on a view ?
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.
