Computed column or create index on a view ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Computed column or create index on a view ?

Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Computed column or create index on a view ?

Jean-Luc Hainaut

- 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Computed column or create index on a view ?

Simon Slavin-3

On 15 May 2016, at 4:08pm, Jean-Luc Hainaut <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users