Re: [sqlite] Index on expressions

[Nico Williams]

On Wed, Oct 22, 2014 at 3:42 PM, Philippe Riand  wrote:
> Hello,
> If there any plan to get this in? (see:
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-September/055065.html
> )

You'll notice that the SQLite3 developers don't often discuss what
features they will be adding (nor when), unless they are ready to make
a public commitment.  That's how it looks to me anyways.  That said,
Richard Hipp did not reject your request, and qualified the "SQLite3
does not support that" reply with "(yet)".  Take that for what you
will, though it's clearly NOT a rejection.  We'll find out eventually.
(You can always watch the public parts of the Fossil repo for SQLite3,
but you shouldn't count on anything you see there being committed to.)

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index on expressions

[Philippe Riand]

Hello,
If there any plan to get this in? (see:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-September/055065.html
)
Note that this seems implemented by several databases now, and this is a
bit different from virtual columns, which is what the previous discussion
ended with. Virtual columns force a table alteration (adding the columns)
why indexes on expressions eventually speed up the search on conditions
like f(x)='value', if the index exists. You add the index only if it makes
sense from a performance standpoint, and it is not required.

Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Nico Williams]

On Thu, Sep 4, 2014 at 3:59 AM, Dominique Devienne  wrote:
> On Thu, Sep 4, 2014 at 2:04 AM, Nico Williams  wrote:
>
>> [...] but there's a gotcha: SELECT * on a table
>> source with computed columns might result in much more work being done
>> than the user might have expected.
>
> If that's a real concern, you can always use the existing HIDDEN keyword to
> remove that virtual/computed column from the select-star. --DD

Ah!  Good point.

Getting computed columns as part of the process of getting indexing on
expressions would be very, very good.  It would allow having computed
columns in PRIMARY KEY and UNIQUE table constraints, for example.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Klaas V]

Dominique Devienne wrote:

(double-click on new version) SQLite version 3.8.5 2014-06-04 14:06:34

(terminal) SQLite version 3.7.13 2012-07-17 17:46:21

 
This means you downloaded the new version in a directory not in your $PATH

Add it to your path:

set PATH=: $PATH

If you do this in your startupscript you always get the latest

Another option (not recommended!) is to overwrite the systemversion with 

sudo cp /sqlite3 /usr/bin/sqlite3

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Dominique Devienne]

On Thu, Sep 4, 2014 at 2:04 AM, Nico Williams  wrote:

> [...] but there's a gotcha: SELECT * on a table
> source with computed columns might result in much more work being done
> than the user might have expected.
>

If that's a real concern, you can always use the existing HIDDEN keyword to
remove that virtual/computed column from the select-star. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Nico Williams]

Also, of course, MERGE is very convenient, syntactically and semantically.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Nico Williams]

On Wed, Sep 3, 2014 at 6:16 PM, Simon Slavin  wrote:
> I'm used to calling them 'computed columns' but yes, they should be 
> relatively easy to implement, as long as users accept a bunch of restructions 
> on what they can refer to, roughly equivalent to the restrictions on what can 
> be used in CHECK constraints.  I had hoped for them in SQLite4.
>
> One alternative is to use VIEWs but, as has already been said in this thread, 
> one can't index a VIEW.  And another alternative is to use TRIGGERs and that 
> works fine, but it's bulky and annoying to implement.

Triggers are very expensive.

IMO an index on expressions would be extremely valuable.  Computed
columns go hand in hand with this, in my mind, as no extended CREATE
INDEX syntax is needed then, but there's a gotcha: SELECT * on a table
source with computed columns might result in much more work being done
than the user might have expected.

> The MERGE command would, I'm guessing, be far harder to implement because it 
> does such different things depending on what data exists in the tables.  Not 
> only would writing the code be difficult but also thinking up the numerous 
> tricky things that would need to go into the test suite.

A MERGE basically does something like three statements, an INSERT ...
WHERE ; and UPDATE ... WHERE , and a DELETE ... WHERE ;.  I do this sort of thing all the time.  Expanding a MERGE
into something that shouldn't be hard.

The real value of MERGE is that the underlying query that drives this
need only be evaluated once.  The SQLite3 VM, I think, can handle
this, so it's mostly a matter of compiler code (yeah, I know, famous
last words).

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Simon Slavin]

On 3 Sep 2014, at 8:21pm, John McKown  wrote:

> Those are _both_ nice features. I don't know for sure, but somehow it
> seems that virtual columns would be easier to implement.

I'm used to calling them 'computed columns' but yes, they should be relatively 
easy to implement, as long as users accept a bunch of restructions on what they 
can refer to, roughly equivalent to the restrictions on what can be used in 
CHECK constraints.  I had hoped for them in SQLite4.

One alternative is to use VIEWs but, as has already been said in this thread, 
one can't index a VIEW.  And another alternative is to use TRIGGERs and that 
works fine, but it's bulky and annoying to implement.

The MERGE command would, I'm guessing, be far harder to implement because it 
does such different things depending on what data exists in the tables.  Not 
only would writing the code be difficult but also thinking up the numerous 
tricky things that would need to go into the test suite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[John McKown]

On Wed, Sep 3, 2014 at 1:35 PM, Petite Abeille  wrote:
>
> On Sep 3, 2014, at 3:01 PM, Dominique Devienne  wrote:
>
>> Asked differently, if adding this support, could this be done by adding
>> virtual / computed columns to tables, and indexing those columns?
>
> Ohohohoho… virtual columns [1][2]…. yes… shinny! :)
>
> Now that would be rather cool.
>
> On the other hand, if one had to choose, I would rather see a MERGE 
> statement, than some funky virtual columns.
>
> While virtual columns are handy at time, they are a bit exotic, all things 
> being equal.
>
> On the other hand, MERGE is a must have. No amount of creative select + 
> insert + update concoctions can begin to compensate for its absence in 
> SQLite. A huge gap altogether.
>
>
> [1] http://en.wikipedia.org/wiki/Virtual_column
> [2] http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php
>

Those are _both_ nice features. I don't know for sure, but somehow it
seems that virtual columns would be easier to implement. OTOH, I
really like what I've just read about the MERGE statement. I only use
SQLite and PostgreSQL. So I had never heard of MERGE before, but the
Oracle documentation on them, and the example, look interesting.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Petite Abeille]

On Sep 3, 2014, at 3:01 PM, Dominique Devienne  wrote:

> Asked differently, if adding this support, could this be done by adding
> virtual / computed columns to tables, and indexing those columns?

Ohohohoho… virtual columns [1][2]…. yes… shinny! :)

Now that would be rather cool. 

On the other hand, if one had to choose, I would rather see a MERGE statement, 
than some funky virtual columns.

While virtual columns are handy at time, they are a bit exotic, all things 
being equal. 

On the other hand, MERGE is a must have. No amount of creative select + insert 
+ update concoctions can begin to compensate for its absence in SQLite. A huge 
gap altogether.


[1] http://en.wikipedia.org/wiki/Virtual_column
[2] http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Shane Harrelson]

Use a trigger to populate your index column.
http://www.sqlite.org/lang_createtrigger.html


On Wed, Sep 3, 2014 at 9:01 AM, Dominique Devienne 
wrote:

> On Wed, Sep 3, 2014 at 2:52 PM, Richard Hipp  wrote:
>
> > SQLite does not (yet) support indexes on expressions.
>
>
> This begs the question: Are there plans, possibly ongoing, to add this
> support? Any timeframe?
>
> Asked differently, if adding this support, could this be done by adding
> virtual / computed columns to tables, and indexing those columns?
>
> You can emulate virtual columns with views of course, but then you have a
> table and view, necessarily named differently, and of course you cannot
> index views, while some DBMS allow indexing virtual  / computed columns.
> --DD
>
> Fantasy SQL:
> create table t (id number primary key, c1, c2, ... );
> alter table t add column c99 as (c1 + c2);
> create index idx_t_c99 on t.c99;
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Dominique Devienne]

On Wed, Sep 3, 2014 at 2:52 PM, Richard Hipp  wrote:

> SQLite does not (yet) support indexes on expressions.


This begs the question: Are there plans, possibly ongoing, to add this
support? Any timeframe?

Asked differently, if adding this support, could this be done by adding
virtual / computed columns to tables, and indexing those columns?

You can emulate virtual columns with views of course, but then you have a
table and view, necessarily named differently, and of course you cannot
index views, while some DBMS allow indexing virtual  / computed columns.
--DD

Fantasy SQL:
create table t (id number primary key, c1, c2, ... );
alter table t add column c99 as (c1 + c2);
create index idx_t_c99 on t.c99;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Richard Hipp]

SQLite does not (yet) support indexes on expressions.


On Tue, Sep 2, 2014 at 9:06 PM, Philippe Riand  wrote:

> Is there a way to create indexes based on expressions instead of simple
> columns (see:
> http://www.postgresql.org/docs/8.1/static/indexes-expressional.html)? The
> idea is to have some custom functions that extract data from a JSON column
> and allow a fast query based on an index. This is the strategy used by
> POSTGRES and DB2, for example.
> I know that we can create a table and do a manual field extraction, but
> this is not transparent from a query standpoint, and forces a manual re
> computation of all rows when a new index is desired.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index on expressions

[Philippe Riand]

Is there a way to create indexes based on expressions instead of simple
columns (see:
http://www.postgresql.org/docs/8.1/static/indexes-expressional.html)? The
idea is to have some custom functions that extract data from a JSON column
and allow a fast query based on an index. This is the strategy used by
POSTGRES and DB2, for example.
I know that we can create a table and do a manual field extraction, but
this is not transparent from a query standpoint, and forces a manual re
computation of all rows when a new index is desired.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users