Re: [sqlite] Getting datatypes of columns in a resultset from an SQLITE view

[Dulini Atapattu]

Thanks


On Fri, May 17, 2013 at 11:08 PM, Igor Tandetnik  wrote:

> Yes, as long as p_Stmt is positioned on a row (that is, the last call to
> sqlite3_step returned SQLITE_ROW).
>
> In SQLite, columns don't really have types; only individual values do.
>
> Igor Tandetnik
>
>
> On 5/17/2013 1:32 PM, Dulini Atapattu wrote:
>
>> Adding more to my question, is it possible to get the datatypes using
>> sqlite3_column_type(p_Stmt, iCol)?
>>
>>
>> On Fri, May 17, 2013 at 11:00 PM, Dulini Atapattu <
>> dulini.atapa...@gmail.com
>>
>>> wrote:
>>>
>>
>>  Hi,
>>>
>>> Is it possible to get the data types of columns in a result set that is
>>> obtained using a view in an sqlite database?
>>>
>>>
>
> __**_
> 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] Problem with index on multiple columns

[Petite Abeille]

On May 18, 2013, at 3:13 AM, Keith Medcalf  wrote:

> This applies *only* to the rowid (integer primary key).  Other indexes (as in 
> CREATE INDEX) are always unique since the key always contains the rowid as 
> the final (unspoken) component.  A "unique" index must be unique without 
> considering the (unspoken) rowid component of the key.  So a manual index 
> that is created with the rowid as the final component is always unique 
> (whether you specify it or not) and the rowid is placed in the key twice 
> (once spoken, once unspoken).

Well spoken, Sir.

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

Re: [sqlite] Problem with index on multiple columns

[Keith Medcalf]

>> Furthermore, the "id" is unique so if there is a constraint on the "id" the
>> query planner will always use that constraint to look up the rows in the
>> table directly, rather than going through an index, since doing so will be
>> about twice as fast as using an index.

>Could you explain that last bit? I always thought UNIQUE was implemented 
>under the hood with a regular index. How would simply knowing something 
>is unique make it so much easier to find the needle in the haystack 
>without an index?

Tables in SQLite are indexes.  That is, tables are implemented as b-tree 
structures where the rowid (id -- integer primary key) is the unique key to the 
b-tree and thus to the entire row.  Or you could look at it that a Table is a 
covering index of all the columns in a table that does not exist.  (Not exactly 
-- as a Table has the row data as the payload, whereas an index includes the 
rowid (id) as part of the key and is payloadless).

This applies *only* to the rowid (integer primary key).  Other indexes (as in 
CREATE INDEX) are always unique since the key always contains the rowid as the 
final (unspoken) component.  A "unique" index must be unique without 
considering the (unspoken) rowid component of the key.  So a manual index that 
is created with the rowid as the final component is always unique (whether you 
specify it or not) and the rowid is placed in the key twice (once spoken, once 
unspoken).

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] table with check

[Roman Fleysher]

Thank you, both 

typeof(handedness)='null'

and 

handedness is null

work. I see the problem and agree.

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter Aronson [pbaron...@att.net]
Sent: Friday, May 17, 2013 3:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] table with check

The "OR NULL" doesn't work the way you think -- it's going to make the whole
expression null, which apparently check constraints treat the same as not
false.  What you want there is "OR typeof(handedness)='null'".

Peter

- Original Message 
> From: Roman Fleysher 
> To: General Discussion of SQLite Database 
> Sent: Fri, May 17, 2013 12:19:21 PM
> Subject: [sqlite] table with check
>
> Dear SQLiters,
>
> I am using sqlite shell, I believe version 3.7.16.2. I created a table with
>CHECK condition as:
>
> CREATE TABLE subject(
>   subjectID  INTEGER PRIMARY KEY,
>   handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL)
> );
>
> in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail
>otherwise. But:
>
> INSERT INTO subject (subjectID,"qqq");
>
> actually inserts qqq. Am I doing something wrong? I read manual that newer
>versions of sqlite should enforce CHECKs.
>
> Thank you,
>
> Roman
> ___
> 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


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

Re: [sqlite] table with check

[Peter Aronson]

The "OR NULL" doesn't work the way you think -- it's going to make the whole 
expression null, which apparently check constraints treat the same as not 
false.  What you want there is "OR typeof(handedness)='null'".

Peter

- Original Message 
> From: Roman Fleysher 
> To: General Discussion of SQLite Database 
> Sent: Fri, May 17, 2013 12:19:21 PM
> Subject: [sqlite] table with check
> 
> Dear SQLiters,
> 
> I am using sqlite shell, I believe version 3.7.16.2. I created a table with 
>CHECK condition as:
> 
> CREATE TABLE subject(
>   subjectID  INTEGER PRIMARY KEY,
>   handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL) 
> );
> 
> in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail 
>otherwise. But:
> 
> INSERT INTO subject (subjectID,"qqq");
> 
> actually inserts qqq. Am I doing something wrong? I read manual that newer 
>versions of sqlite should enforce CHECKs.
> 
> Thank you,
> 
> Roman
> ___
> 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] table with check

[Richard Hipp]

On Fri, May 17, 2013 at 3:16 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
> I am using sqlite shell, I believe version 3.7.16.2. I created a table
> with CHECK condition as:
>
> CREATE TABLE subject(
>   subjectID  INTEGER PRIMARY KEY,
>   handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL)
>

I think you want to say "... OR handedness IS NULL" not just "... OR NULL".

 );
>
> in hopes to be able to insert only "Right", "Left" or nothing "", i.e.
> fail otherwise. But:
>
> INSERT INTO subject (subjectID,"qqq");
>
> actually inserts qqq. Am I doing something wrong? I read manual that newer
> versions of sqlite should enforce CHECKs.
>
> Thank you,
>
> Roman
> ___
> 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] table with check

[Roman Fleysher]

Dear SQLiters,

I am using sqlite shell, I believe version 3.7.16.2. I created a table with 
CHECK condition as:

CREATE TABLE subject(
  subjectID  INTEGER PRIMARY KEY,
  handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL) 
);

in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail 
otherwise. But:

INSERT INTO subject (subjectID,"qqq");

actually inserts qqq. Am I doing something wrong? I read manual that newer 
versions of sqlite should enforce CHECKs.

Thank you,

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

Re: [sqlite] Getting datatypes of columns in a resultset from an SQLITE view

[Igor Tandetnik]

Yes, as long as p_Stmt is positioned on a row (that is, the last call to 
sqlite3_step returned SQLITE_ROW).


In SQLite, columns don't really have types; only individual values do.

Igor Tandetnik

On 5/17/2013 1:32 PM, Dulini Atapattu wrote:

Adding more to my question, is it possible to get the datatypes using
sqlite3_column_type(p_Stmt, iCol)?


On Fri, May 17, 2013 at 11:00 PM, Dulini Atapattu 

Re: [sqlite] Getting datatypes of columns in a resultset from an SQLITE view

[Dulini Atapattu]

Adding more to my question, is it possible to get the datatypes using
sqlite3_column_type(p_Stmt, iCol)?


On Fri, May 17, 2013 at 11:00 PM, Dulini Atapattu  wrote:

> Hi,
>
> Is it possible to get the data types of columns in a result set that is
> obtained using a view in an sqlite database?
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting datatypes of columns in a resultset from an SQLITE view

[Dulini Atapattu]

Hi,

Is it possible to get the data types of columns in a result set that is
obtained using a view in an sqlite database?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with index on multiple columns

[GB]

Konstantinos Alogariastos schrieb am 17.05.2013 17:41:

... class can have only 5 distinct values, while Id will
reach to the millions ...


Kostas,

in this case looking up by "class" first would mean to scan through 
2/5th of your data (assuming even distribution). So how long is that 
"long list of integers"? Does it reference 40% of your data? If not, 
you're better off with looking up by "id" anyway. Try for yourself.


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

Re: [sqlite] Problem with index on multiple columns

[Michael Black]

Do you have to have an autoincrement column?
You can implement a non-primary key column in a trigger that fills itself
from the rowid after insert giving you the same thing.
Mike


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

Re: [sqlite] Problem with index on multiple columns

[GB]

Richard Hipp schrieb am 17.05.2013 17:06:
Furthermore, the "id" is unique so if there is a constraint on the 
"id" the query planner will always use that constraint to look up the 
rows in the table directly, rather than going through an index, since 
doing so will be about twice as fast as using an index. 


It's not necessarily faster. If using the INTEGER PRIMARY KEY index 
means to scan through thousands of rows then an index lookup of only a 
handful of rows would be faster. That's why I'd like the implicit index 
to be ANALYZEd and weighted as well (at least with SQLITE_ENABLE_STAT3).


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

Re: [sqlite] Problem with index on multiple columns

[Konstantinos Alogariastos]

Thank you all for your answers. It seems indeed that the PRIMARY KEY column
is causing the problem.
The rationale behind using both "Id" and "class" in the index is that, in
my actual use case, class can have only 5 distinct values, while Id will
reach to the millions, and the table cannot be modified. Also, the
constraint for class is IN(0,1), while for id is IN ( "a large list of
integers inside here"). Therefore I thought it made more sense to first
filter on class and then on id. Now that I see that this is not possible I
should rethink my query.

Best regards,
Kostas


2013/5/17 Richard Hipp 

> On Fri, May 17, 2013 at 11:02 AM, GB  wrote:
>
> >
> > Richard Hipp schrieb am 17.05.2013 16:37:
> >
> >  Collating orders and affinities might be disqualifying the constraint on
> >> "id" from being used with the index.
> >>
> > It just came to my mind that "id" is an INTEGER PRIMARY KEY column and as
> > such is part of every index anyway. Could it be that the additional "id"
> > part of that index is silently ignored in this case?
> >
>
> Yes.  If "id" is the INTEGER PRIMARY KEY then that messes up everything.
> Don't do that.  Change the index to omit the "id" and you'll get better
> results.
>
> Furthermore, the "id" is unique so if there is a constraint on the "id" the
> query planner will always use that constraint to look up the rows in the
> table directly, rather than going through an index, since doing so will be
> about twice as fast as using an index.
>
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with index on multiple columns

[Ryan Johnson]

On 17/05/2013 11:06 AM, Richard Hipp wrote:

On Fri, May 17, 2013 at 11:02 AM, GB  wrote:


Richard Hipp schrieb am 17.05.2013 16:37:

  Collating orders and affinities might be disqualifying the constraint on

"id" from being used with the index.


It just came to my mind that "id" is an INTEGER PRIMARY KEY column and as
such is part of every index anyway. Could it be that the additional "id"
part of that index is silently ignored in this case?


Yes.  If "id" is the INTEGER PRIMARY KEY then that messes up everything.
Don't do that.  Change the index to omit the "id" and you'll get better
results.

Furthermore, the "id" is unique so if there is a constraint on the "id" the
query planner will always use that constraint to look up the rows in the
table directly, rather than going through an index, since doing so will be
about twice as fast as using an index.
Could you explain that last bit? I always thought UNIQUE was implemented 
under the hood with a regular index. How would simply knowing something 
is unique make it so much easier to find the needle in the haystack 
without an index?


Do normal indexes map index keys to primary keys, so you still have to 
drill the PK index afterward? (BerkeleyDB does that, but it does it for 
all indexes, not just user-created ones).


Thanks,
Ryan

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

Re: [sqlite] Problem with index on multiple columns

[Richard Hipp]

On Fri, May 17, 2013 at 11:02 AM, GB  wrote:

>
> Richard Hipp schrieb am 17.05.2013 16:37:
>
>  Collating orders and affinities might be disqualifying the constraint on
>> "id" from being used with the index.
>>
> It just came to my mind that "id" is an INTEGER PRIMARY KEY column and as
> such is part of every index anyway. Could it be that the additional "id"
> part of that index is silently ignored in this case?
>

Yes.  If "id" is the INTEGER PRIMARY KEY then that messes up everything.
Don't do that.  Change the index to omit the "id" and you'll get better
results.

Furthermore, the "id" is unique so if there is a constraint on the "id" the
query planner will always use that constraint to look up the rows in the
table directly, rather than going through an index, since doing so will be
about twice as fast as using an index.


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

Re: [sqlite] Problem with index on multiple columns

[Michael Black]

Indeed would seem so.  Remove the primary key and idx_test is used.
This is on 3.7.16.2

CREATE TABLE test(
  id INTEGER,
  class INTEGER NOT NULL);
CREATE INDEX idx_test ON TEST(class,id);
 EXPLAIN QUERY PLAN SELECT * FROM test WHERE id IN (0,1) AND class IN (3,4);
0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=? AND id=?)
(~36 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Friday, May 17, 2013 9:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with index on multiple columns

On Fri, May 17, 2013 at 10:26 AM, Konstantinos Alogariastos <
marau...@gmail.com> wrote:

> I am already aware of the contents of the query planner documentation.
>
> As you say, only one b-tree index will be used. What I want to achieve
> is what is described in the documentation " *The second column is used
> to break ties in the left-most column* ".
>
> In my case, my custom index "idx_test(class,id)" should first search
> in the "class" column and if there are any ties there (which happens a
> lot in my case), the "id" column should be used to break them.
> However, as evidenced by the query plan output, only the "class"
> column is used for indexing.
>

Collating orders and affinities might be disqualifying the constraint on
"id" from being used with the index.


>
>
> Best regards,
>
> Kostas
>
>
> On May 17, 2013, at 10:54 AM, Konstantinos Alogariastos  gmail.com >
> wrote:
>
> >* Does this mean that one cannot use a index on two columns when in the
> query*>* both columns are used with "IN"?*
> You might want to read up on the query planner:
> http://www.sqlite.org/queryplanner.html#searching
>
> The short of it: only one btree index will be used per source table.
>
> >* Is this a limitation of SQLite or a bug?*
> Neither. But read up on Multi-Column Indices.
>
> There exist other types of indexes (for example bitmap indexes [1]),
> which have different properties and can be combined to resolve a
> query, but such structures are not supported by SQLite.
>
> [1] http://en.wikipedia.org/wiki/Bitmap_index
>
>
>
> 2013/5/17 Konstantinos Alogariastos 
>
> > Hi all,
> >
> > I am using SQLite 3.7.13 and I am experiencing a problem with using an
> > index on multiple columns.
> >
> > Let's assume the following example:
> > I have a simple table constructed as such:
> >
> > CREATE TABLE test(
> >   id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   class INTEGER NOT NULL);
> >
> > and I insert some data to it.
> >
> > Next I create an index: CREATE INDEX idx_test ON TEST(class,id);
> >
> > If I try to use the index with a query similar to the following:
> > EXPLAIN QUERY PLAN
> >   SELECT *
> >  FROM test
> >WHERE id IN (0,1)
> >  AND class IN (3,4)
> >
> > I get the output:
> > 0|0|0|SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> >
> > (which means my index is not used)
> >
> > If I add "INDEXED BY idx_test" on the above query, I get:
> > 0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2
rows)
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> >
> > which shows that only one column of the index is used for indexing. This
> > has the side effect of the query taking longer than it should be.
> > Modifying the order of the columns on the index or in the query didn't
> > help either.
> >
> > Does this mean that one cannot use a index on two columns when in the
> > query both columns are used with "IN"?
> > Is this a limitation of SQLite or a bug?
> >
> > Thanks in advance.
> >
> > Best regards,
> > Kostas
> >
> >
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with index on multiple columns

[GB]

Richard Hipp schrieb am 17.05.2013 16:37:
Collating orders and affinities might be disqualifying the constraint 
on "id" from being used with the index.
It just came to my mind that "id" is an INTEGER PRIMARY KEY column and 
as such is part of every index anyway. Could it be that the additional 
"id" part of that index is silently ignored in this case?


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

Re: [sqlite] Problem with index on multiple columns

[GB]

Konstantinos Alogariastos schrieb am 17.05.2013 16:15:

it is clearly shown that only the  "class" column is used for indexing.

Kostas,

I'm not sure if the fact that only "class" column is mentioned also 
means that other parts of that index are not used. Maybe someone more 
knowledged about SQLite's inner workings can shed some light here.


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

Re: [sqlite] Problem with index on multiple columns

[Richard Hipp]

On Fri, May 17, 2013 at 10:26 AM, Konstantinos Alogariastos <
marau...@gmail.com> wrote:

> I am already aware of the contents of the query planner documentation.
>
> As you say, only one b-tree index will be used. What I want to achieve
> is what is described in the documentation " *The second column is used
> to break ties in the left-most column* ".
>
> In my case, my custom index "idx_test(class,id)" should first search
> in the "class" column and if there are any ties there (which happens a
> lot in my case), the "id" column should be used to break them.
> However, as evidenced by the query plan output, only the "class"
> column is used for indexing.
>

Collating orders and affinities might be disqualifying the constraint on
"id" from being used with the index.


>
>
> Best regards,
>
> Kostas
>
>
> On May 17, 2013, at 10:54 AM, Konstantinos Alogariastos  gmail.com >
> wrote:
>
> >* Does this mean that one cannot use a index on two columns when in the
> query*>* both columns are used with "IN"?*
> You might want to read up on the query planner:
> http://www.sqlite.org/queryplanner.html#searching
>
> The short of it: only one btree index will be used per source table.
>
> >* Is this a limitation of SQLite or a bug?*
> Neither. But read up on Multi-Column Indices.
>
> There exist other types of indexes (for example bitmap indexes [1]),
> which have different properties and can be combined to resolve a
> query, but such structures are not supported by SQLite.
>
> [1] http://en.wikipedia.org/wiki/Bitmap_index
>
>
>
> 2013/5/17 Konstantinos Alogariastos 
>
> > Hi all,
> >
> > I am using SQLite 3.7.13 and I am experiencing a problem with using an
> > index on multiple columns.
> >
> > Let's assume the following example:
> > I have a simple table constructed as such:
> >
> > CREATE TABLE test(
> >   id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   class INTEGER NOT NULL);
> >
> > and I insert some data to it.
> >
> > Next I create an index: CREATE INDEX idx_test ON TEST(class,id);
> >
> > If I try to use the index with a query similar to the following:
> > EXPLAIN QUERY PLAN
> >   SELECT *
> >  FROM test
> >WHERE id IN (0,1)
> >  AND class IN (3,4)
> >
> > I get the output:
> > 0|0|0|SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> >
> > (which means my index is not used)
> >
> > If I add "INDEXED BY idx_test" on the above query, I get:
> > 0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2 rows)
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> > 0|0|0|EXECUTE LIST SUBQUERY 1
> >
> > which shows that only one column of the index is used for indexing. This
> > has the side effect of the query taking longer than it should be.
> > Modifying the order of the columns on the index or in the query didn't
> > help either.
> >
> > Does this mean that one cannot use a index on two columns when in the
> > query both columns are used with "IN"?
> > Is this a limitation of SQLite or a bug?
> >
> > Thanks in advance.
> >
> > Best regards,
> > Kostas
> >
> >
> ___
> 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

Re: [sqlite] Problem with index on multiple columns

[Konstantinos Alogariastos]

I am already aware of the contents of the query planner documentation.

As you say, only one b-tree index will be used. What I want to achieve
is what is described in the documentation " *The second column is used
to break ties in the left-most column* ".

In my case, my custom index "idx_test(class,id)" should first search
in the "class" column and if there are any ties there (which happens a
lot in my case), the "id" column should be used to break them.
However, as evidenced by the query plan output, only the "class"
column is used for indexing.


Best regards,

Kostas


On May 17, 2013, at 10:54 AM, Konstantinos Alogariastos http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>>
wrote:

>* Does this mean that one cannot use a index on two columns when in the 
>query*>* both columns are used with "IN"?*
You might want to read up on the query planner:
http://www.sqlite.org/queryplanner.html#searching

The short of it: only one btree index will be used per source table.

>* Is this a limitation of SQLite or a bug?*
Neither. But read up on Multi-Column Indices.

There exist other types of indexes (for example bitmap indexes [1]),
which have different properties and can be combined to resolve a
query, but such structures are not supported by SQLite.

[1] http://en.wikipedia.org/wiki/Bitmap_index



2013/5/17 Konstantinos Alogariastos 

> Hi all,
>
> I am using SQLite 3.7.13 and I am experiencing a problem with using an
> index on multiple columns.
>
> Let's assume the following example:
> I have a simple table constructed as such:
>
> CREATE TABLE test(
>   id INTEGER PRIMARY KEY AUTOINCREMENT,
>   class INTEGER NOT NULL);
>
> and I insert some data to it.
>
> Next I create an index: CREATE INDEX idx_test ON TEST(class,id);
>
> If I try to use the index with a query similar to the following:
> EXPLAIN QUERY PLAN
>   SELECT *
>  FROM test
>WHERE id IN (0,1)
>  AND class IN (3,4)
>
> I get the output:
> 0|0|0|SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> (which means my index is not used)
>
> If I add "INDEXED BY idx_test" on the above query, I get:
> 0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> which shows that only one column of the index is used for indexing. This
> has the side effect of the query taking longer than it should be.
> Modifying the order of the columns on the index or in the query didn't
> help either.
>
> Does this mean that one cannot use a index on two columns when in the
> query both columns are used with "IN"?
> Is this a limitation of SQLite or a bug?
>
> Thanks in advance.
>
> Best regards,
> Kostas
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with index on multiple columns

[Konstantinos Alogariastos]

Dear Gerd,

It is a valid observation that the query planner picks PRIMARY KEY indexes
very often.
However, my question is why, even when I force the query to use another
index ( e.g. idx_test ON TEST(class,id) ), the query plan only uses one
column out of it.
For example in the output below
( 0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2 rows)
)
it is clearly shown that only the  "class" column is used for indexing.

My actual use case involves a large table with about 20 columns, with some
of them selected, but the filtering and indexing happens similarly to my
simple example. Therefore, in my case performance counts a lot.

Kostas,

you have id defined as INTEGER PRIMARY KEY so id becomes the rowid and
thus the index leaf nodes are the table rows themselves. So I/O-wise
there should not be much of a difference in your case.

But I already noticed in the past that the query planner puts a heavy
weight on INTEGER PRIMARY KEY indexes so it always picks those if they
are involved in the WHERE-clause, no matter if other indexes would suit
better. Running ANALYZE doesn't help since it does not create
statistical data for the implicit PRIMARY KEY index.

regards
Gerd


2013/5/17 Konstantinos Alogariastos 

> Hi all,
>
> I am using SQLite 3.7.13 and I am experiencing a problem with using an
> index on multiple columns.
>
> Let's assume the following example:
> I have a simple table constructed as such:
>
> CREATE TABLE test(
>   id INTEGER PRIMARY KEY AUTOINCREMENT,
>   class INTEGER NOT NULL);
>
> and I insert some data to it.
>
> Next I create an index: CREATE INDEX idx_test ON TEST(class,id);
>
> If I try to use the index with a query similar to the following:
> EXPLAIN QUERY PLAN
>   SELECT *
>  FROM test
>WHERE id IN (0,1)
>  AND class IN (3,4)
>
> I get the output:
> 0|0|0|SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> (which means my index is not used)
>
> If I add "INDEXED BY idx_test" on the above query, I get:
> 0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> which shows that only one column of the index is used for indexing. This
> has the side effect of the query taking longer than it should be.
> Modifying the order of the columns on the index or in the query didn't
> help either.
>
> Does this mean that one cannot use a index on two columns when in the
> query both columns are used with "IN"?
> Is this a limitation of SQLite or a bug?
>
> Thanks in advance.
>
> Best regards,
> Kostas
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Savepoint and Begin Transaction, performance-wise

[Richard Hipp]

On Fri, May 17, 2013 at 9:06 AM, Paolo Bolzoni <
paolo.bolzoni.br...@gmail.com> wrote:

> In my programs I often use savepoints so in case
> of errors I can rollback leaving the db untouched.
>
> I use savepoint instead of begin transaction because
> it fits more naturally in the nested structure of a C
> program.
> When I write a function I do not need to recall if the
> caller already  opened the transaction.
> When the last savepoint is released the data is safely
> written in the db.
>
> First of all, did I understand correctly?
>

yes.


> Secondly, is there a significant difference of performance
> to use nested savepoints instead of a single transaction?
>

Yes.  SQLite must open a new rollback journal for each nested savepoint.
These secondary rollback journals do not need to be synced like the primary
journal (or WAL file) so they are not nearly as expensive.  But neither are
they free.

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

Re: [sqlite] System.Data.SQLite: Failing APTCA Check

[DevTeam]

Hi Joe,

Thanks for the reply. I've been reading about this APTCA issue and it 
appears that this is a fairly recent change Microsoft has made regarding 
security, starting with the .Net Framework 4. In the March 2010 issue of 
MSDN Magazine there was an article by Mike Rousos titled "Migrating an 
APTCA Assembly to the .NET Framework 4" which looks like it does a real 
nice job of explaining the history of the APTCA attribute, what the 
change means in .Net 4, and how to modify an assembly.


Here's a link to the article:

http://msdn.microsoft.com/en-us/magazine/ee336023.aspx

The MSDN article does mention a tool called SecAnnotate.exe as follows:

To help with the correct application of transparency attributes, there 
is a new .NET Framework SDK tool, the Security Annotator 
(SecAnnotate.exe). This tool consumes a user’s binary (or collection of 
binaries) and provides guidance on where transparency attributes should 
be applied. It can be very helpful when migrating an APTCA library to v4.


My initial impression ( and I'm still trying to understand this, so take 
these comments with this in mind ) is that the assembly attribute should 
be updated to reflect this security change. Whether the attribute is 
removed or is changed to one of the attributes in the article looks like 
it depends on what the underlying code in the assembly does - security 
wise. For example, the attribute may be able to be changed to 
SecurityTransparent - however there are certain code operations that 
cannot be marked as SecurityTransparent - the article lists these. I'm 
not familiar with the code at this point to know one way or the other ...


Mike


On 5/16/2013 5:01 PM, Joe Mistachkin wrote:

DevTeam wrote:

Next I downloaded and ran the binscope.exe utility against just the
System.Data.Sqlite.dll file to isolate the test to just this file and
received the same result - Failed checks - the assembly has
AllowPartiallyTrustedCallersAttribute (APTCA).


The use of this attribute by System.Data.SQLite appears to date back to
at least version 1.0.32.0, released in 2006.

I'm not aware of the original intent when this attribute was added.

Furthermore, this is the first time I've even heard it mentioned.


I would like to be able to better understand and eliminate this
warning/failure if possible, but not sure how best to proceed.  Any
assistance or advice would be greatly appreciated.  Thanks in advance.


The warning deals with assemblies that are not "fully trusted" being able
to use System.Data.SQLite.

In theory, this could pose a problem:  If you actually intend to use
partially trusted assemblies -OR- if you intend to install the
System.Data.SQLite assemblies into a shared location on the machine,
such as the GAC.

Perhaps the attribute should be removed from System.Data.SQLite?  I'm not
sure.

--
Joe Mistachkin

___
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

[sqlite] Savepoint and Begin Transaction, performance-wise

[Paolo Bolzoni]

In my programs I often use savepoints so in case
of errors I can rollback leaving the db untouched.

I use savepoint instead of begin transaction because
it fits more naturally in the nested structure of a C
program.
When I write a function I do not need to recall if the
caller already  opened the transaction.
When the last savepoint is released the data is safely
written in the db.

First of all, did I understand correctly?
Secondly, is there a significant difference of performance
to use nested savepoints instead of a single transaction?

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

Re: [sqlite] Problem with index on multiple columns

[GB]

Kostas,

you have id defined as INTEGER PRIMARY KEY so id becomes the rowid and 
thus the index leaf nodes are the table rows themselves. So I/O-wise 
there should not be much of a difference in your case.


But I already noticed in the past that the query planner puts a heavy 
weight on INTEGER PRIMARY KEY indexes so it always picks those if they 
are involved in the WHERE-clause, no matter if other indexes would suit 
better. Running ANALYZE doesn't help since it does not create 
statistical data for the implicit PRIMARY KEY index.


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

Re: [sqlite] Release history on sqlite.org

[Richard Hipp]

On Fri, May 17, 2013 at 5:26 AM, Georg Hofmann wrote:

> Hi All,
>
> I have just noticed that on the sqlite.org/changes.html web page, the
> latest release is dated to 2012-04-12. Shouldn't it be 2013-04-12?
>

Thanks.  That typo was fixed about a month ago with checkin
http://www.sqlite.org/docsrc/info/224d684b8b and will appear on the next
website rebuild which should happen in sometime next week.

-- 
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] Release history on sqlite.org

[Georg Hofmann]

Hi All,

I have just noticed that on the sqlite.org/changes.html web page, the latest 
release is dated to 2012-04-12. Shouldn't it be 2013-04-12? 

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

Re: [sqlite] Problem with index on multiple columns

[Petite Abeille]

On May 17, 2013, at 10:54 AM, Konstantinos Alogariastos  
wrote:

> Does this mean that one cannot use a index on two columns when in the query
> both columns are used with "IN"?

You might want to read up on the query planner:

http://www.sqlite.org/queryplanner.html#searching

The short of it: only one btree index will be used per source table. 

> Is this a limitation of SQLite or a bug?

Neither. But read up on Multi-Column Indices.

There exist other types of indexes (for example bitmap indexes [1]), which have 
different properties and can be combined to resolve a query, but such 
structures are not supported by SQLite.

[1] http://en.wikipedia.org/wiki/Bitmap_index

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

[sqlite] Problem with index on multiple columns

[Konstantinos Alogariastos]

Hi all,

I am using SQLite 3.7.13 and I am experiencing a problem with using an
index on multiple columns.

Let's assume the following example:
I have a simple table constructed as such:

CREATE TABLE test(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  class INTEGER NOT NULL);

and I insert some data to it.

Next I create an index: CREATE INDEX idx_test ON TEST(class,id);

If I try to use the index with a query similar to the following:
EXPLAIN QUERY PLAN
  SELECT *
 FROM test
   WHERE id IN (0,1)
 AND class IN (3,4)

I get the output:
0|0|0|SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1

(which means my index is not used)

If I add "INDEXED BY idx_test" on the above query, I get:
0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1

which shows that only one column of the index is used for indexing. This
has the side effect of the query taking longer than it should be.
Modifying the order of the columns on the index or in the query didn't help
either.

Does this mean that one cannot use a index on two columns when in the query
both columns are used with "IN"?
Is this a limitation of SQLite or a bug?

Thanks in advance.

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

Re: [sqlite] sqlite shell: arrow keys

[Paolo Bolzoni]

maybe rlwrap can help?

On Fri, May 17, 2013 at 4:27 AM, Fehmi Noyan ISI  wrote:
> Depends on your system I think. Here is what is contained in shell.c
> readline.h is the thing making all those history and similar things you use 
> in your linux command line shell available.
>
> #ifdef HAVE_EDITLINE
> # include 
> #endif
> #if defined(HAVE_READLINE) && HAVE_READLINE==1
> # include 
> # include 
> #endif
>
>
>
>
> 
>  From: Roman Fleysher 
> To: General Discussion of SQLite Database 
> Sent: Friday, May 17, 2013 11:49 AM
> Subject: [sqlite] sqlite shell: arrow keys
>
>
> Dear SQLiters,
>
> I am new to SQLite and learning it (and SQL) using shell. It would make life 
> easier if arrow keys on keyboard could be used to scroll through command 
> history and along command for editing. Is there a way to enable this?
>
> Thank you,
>
> Roman
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users