Re: [sqlite] Covering Index?

[Richard Hipp]

On Tue, Jun 4, 2013 at 10:41 PM, Simon Slavin  wrote:

>
> On 5 Jun 2013, at 3:16am, David de Regt  wrote:
>
> > CREATE TABLE test (col1 text, col2 text, col3 text);
> > CREATE INDEX tindex ON test (col1, col2, col3);
> >
> > explain query plan
> > SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';
> >
> > The above returns:
> > SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)
>
> Other people have answered, but I don't think anyone has come out and made
> this plain: the order of the columns in your index is significant.  For
> this specific SELECT, a more useful index would be
>
> CREATE INDEX tindex132 ON test (col1, col3, col2)
>
> and indeed this would not help any more than
>
> CREATE INDEX tindex13 ON test (col1, col3)
>
>
Actually, tindex132 would be a little faster than tindex13, since with
tindex132 it can look up the value of col2 directly from the index and
never has to seek into the original table.


-- 
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] Covering Index?

[Simon Slavin]

On 5 Jun 2013, at 3:16am, David de Regt  wrote:

> CREATE TABLE test (col1 text, col2 text, col3 text);
> CREATE INDEX tindex ON test (col1, col2, col3);
> 
> explain query plan
> SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';
> 
> The above returns:
> SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)

Other people have answered, but I don't think anyone has come out and made this 
plain: the order of the columns in your index is significant.  For this 
specific SELECT, a more useful index would be

CREATE INDEX tindex132 ON test (col1, col3, col2)

and indeed this would not help any more than

CREATE INDEX tindex13 ON test (col1, col3)

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

Re: [sqlite] Covering Index?

[Keith Medcalf]

> Quick question, SQLites,
 
> CREATE TABLE test (col1 text, col2 text, col3 text);
> CREATE INDEX tindex ON test (col1, col2, col3);
 
> explain query plan
> SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';
 
> The above returns:
> SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)
 
> Which of the following is happening:
> 1. It's actually only using it as an "index" for the col1 check, and then
> just using the fact that, somewhere, it contains the rest of the data for
> the query inside the index, which is, in theory, faster than table
> scanning the actual table for the results, but you're getting no search
> performance gain out of anything other than the col1 part of the index.
> 2. It's actually somehow using it as an optimized index over both col1 and
> col3 conditions, but only saying col1 in the explain.
> 3. Other..?

#1.  The index is used to lookup the first row where col1 = 'a'.  The index 
contains all the rest of the data required to satisfy the query, so a scan of 
the index is all that is used.  Values of col1, col2 and col3 are retrieved 
from the index and returned while col1 = 'a' and where col3 = 'c'.  The query 
is satisfied entirely from the index scan -- there is no need to access the 
underlying table test at all.  

> Thanks!
> -David

---
()  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] Covering Index?

[Richard Hipp]

On Tue, Jun 4, 2013 at 10:30 PM, David de Regt  wrote:

> Okay, but, it's essentially doing the equivalent of a "table scan" over
> the portion of the index where col1='a', so if col1='a' doesn't actually
> end up narrowing down the resultset hugely, you're still better off with a
> properly ordered index, correct?  (with YMMV disclaimers)
>

All indices in SQLite are ordered.  So I'm not sure what you mean by a
"properly ordered index"...

Yes, you are doing a "table scan" over some slice of the index.  But,
hopefully the col1='a' constraint limits the size of that slice by some
amount, say 1/10th the size of the complete index.  So it is helping
performance some.



>
> -David
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, June 4, 2013 7:27 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Covering Index?
>
> On Tue, Jun 4, 2013 at 10:16 PM, David de Regt  wrote:
>
> > Quick question, SQLites,
> >
> > CREATE TABLE test (col1 text, col2 text, col3 text); CREATE INDEX
> > tindex ON test (col1, col2, col3);
> >
> > explain query plan
> > SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';
> >
> > The above returns:
> > SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)
> >
> >
> > Which of the following is happening:
> > 1. It's actually only using it as an "index" for the col1 check, and
> > then just using the fact that, somewhere, it contains the rest of the
> > data for the query inside the index, which is, in theory, faster than
> > table scanning the actual table for the results, but you're getting no
> > search performance gain out of anything other than the col1 part of the
> index.
> > 2. It's actually somehow using it as an optimized index over both col1
> > and
> > col3 conditions, but only saying col1 in the explain.
> > 3. Other..?
> >
>
> It seeks to the first entry of the index where col1='a', then starts
> reading entries sequentially as long as col1 continues to equal 'a'.  Thus,
> only a small part of the index is examined, and the table itself is never
> even opened.
>
>
> >
> > Thanks!
> > -David
> >
> > ___
> > 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
>



-- 
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] Covering Index?

[David de Regt]

Okay, but, it's essentially doing the equivalent of a "table scan" over the 
portion of the index where col1='a', so if col1='a' doesn't actually end up 
narrowing down the resultset hugely, you're still better off with a properly 
ordered index, correct?  (with YMMV disclaimers)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, June 4, 2013 7:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Covering Index?

On Tue, Jun 4, 2013 at 10:16 PM, David de Regt  wrote:

> Quick question, SQLites,
>
> CREATE TABLE test (col1 text, col2 text, col3 text); CREATE INDEX 
> tindex ON test (col1, col2, col3);
>
> explain query plan
> SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';
>
> The above returns:
> SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)
>
>
> Which of the following is happening:
> 1. It's actually only using it as an "index" for the col1 check, and 
> then just using the fact that, somewhere, it contains the rest of the 
> data for the query inside the index, which is, in theory, faster than 
> table scanning the actual table for the results, but you're getting no 
> search performance gain out of anything other than the col1 part of the index.
> 2. It's actually somehow using it as an optimized index over both col1 
> and
> col3 conditions, but only saying col1 in the explain.
> 3. Other..?
>

It seeks to the first entry of the index where col1='a', then starts reading 
entries sequentially as long as col1 continues to equal 'a'.  Thus, only a 
small part of the index is examined, and the table itself is never even opened.


>
> Thanks!
> -David
>
> ___
> 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] Covering Index?

[Richard Hipp]

On Tue, Jun 4, 2013 at 10:16 PM, David de Regt  wrote:

> Quick question, SQLites,
>
> CREATE TABLE test (col1 text, col2 text, col3 text);
> CREATE INDEX tindex ON test (col1, col2, col3);
>
> explain query plan
> SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';
>
> The above returns:
> SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)
>
>
> Which of the following is happening:
> 1. It's actually only using it as an "index" for the col1 check, and then
> just using the fact that, somewhere, it contains the rest of the data for
> the query inside the index, which is, in theory, faster than table scanning
> the actual table for the results, but you're getting no search performance
> gain out of anything other than the col1 part of the index.
> 2. It's actually somehow using it as an optimized index over both col1 and
> col3 conditions, but only saying col1 in the explain.
> 3. Other..?
>

It seeks to the first entry of the index where col1='a', then starts
reading entries sequentially as long as col1 continues to equal 'a'.  Thus,
only a small part of the index is examined, and the table itself is never
even opened.


>
> Thanks!
> -David
>
> ___
> 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] Please send in SQLite database statistics

[Richard Hipp]

The "sqlite3_analyzer" program reads an SQLite database file and prints out
a text summary of the sizes of the various tables and indices in that
database file.  The source code to sqlite3_analyzer is in the public SQLite
source tree; you can type "make sqlite3_analyzer" or "make
sqlite3_analyzer.exe" to build it yourself.  Or you can download
precompiled binaries for popular platforms from the SQLite download page:
http://www.sqlite.org/download.html

In order to better understand how SQLite is being used, and to better tune
future versions of SQLite for its more common use cases, I'm asking you to
run the sqlite3_analyzer program on some of your database files and to send
me the output via private email.

Any sqlite3_analyzer output files you send will remain in this office.
Nothing will posted or revealed publicly.  Note also that the output of
sqlite3_analyzer does not contain any database content so sending such
output to me does not reveal any confidential information.  However, the
output of sqlite3_analyzer does reveal some information about the database
schema.  Hopefully, the schemas of your databases are not so sensitive that
you cannot share them with us in confidence.

All sqlite3_analyzer output is of interest, but an analysis of larger
database files is of particular interest.  Note that we already have
abundant information on common SQLite database files such as Fossil
repositories, Firefox "places" files, Android contact lists, and so forth,
so do not send more of them.  We are looking for other uses of SQLite.  We
want to verify that the workload patterns we see in these well-known SQLite
databases is typical.  So if you are using SQLite in an unusual way, we
especially would like to peek at your sqlite3_analyzer output.

If you want to also include a few words about how each analyzed database is
used, that might also be helpful to us, but is not essential.

Thanks for your help.

-- 
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] Covering Index?

[David de Regt]

Quick question, SQLites,

CREATE TABLE test (col1 text, col2 text, col3 text);
CREATE INDEX tindex ON test (col1, col2, col3);

explain query plan
SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';

The above returns:
SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)


Which of the following is happening:
1. It's actually only using it as an "index" for the col1 check, and then just 
using the fact that, somewhere, it contains the rest of the data for the query 
inside the index, which is, in theory, faster than table scanning the actual 
table for the results, but you're getting no search performance gain out of 
anything other than the col1 part of the index.
2. It's actually somehow using it as an optimized index over both col1 and col3 
conditions, but only saying col1 in the explain.
3. Other..?

Thanks!
-David

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

Re: [sqlite] Limit of attached databases

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/06/13 08:14, Eleytherios Stamatogiannakis wrote:
> We have tried with both views and VTs but SQLite does not create
> automatic indexes on them at all. So right now, to be able to have
> automatic indexes from SQLite's side we materialize all Virtual Tables
> into plain tables:

There is no reason that the virtual table implementation can't create
automatic indexes.  In response to the xBestIndex calls, the indices don't
actually have to exist.  You just need to ensure the estimated cost is
relatively useful.  If SQLite then decides to use the index you can then
create it on the fly.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGuQQMACgkQmOOfHg372QRxxwCfV1QvfO6Fsky0x3krTLe08+Nm
EN4AniM6kkOHgcTm/mtREY3iD4QAF9o+
=KOXn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Richard Hipp]

On Tue, Jun 4, 2013 at 11:59 AM, Philip Goetz  wrote:

> On Tue, Jun 4, 2013 at 11:27 AM, Richard Hipp  wrote:
> > Try adding the command:
> >
> >  .log stdout
> >
> > before running the CREATE INDEX and see what errors you get back.
>
> sqlite> .schema
> CREATE TABLE taxon (gi INTEGER, taxon INTEGER);
> sqlite> .log stdout
> sqlite> create index taxon_gi_index on taxon(gi);
> (14) os_win.c:34063: (3)
> winOpen(/var/tmp/etilqs_PIREaghry4bPES8\etilqs_02LTi9u3HO3cx0g) - The
> system cannot find the path specified.
> (14) cannot open file at line 34071 of [cbea02d938]
> (14) statement aborts at 21: [create index taxon_gi_index on taxon(gi);]
> Error: unable to open database file
>
> Not finding paths often has to do with windows vs. unix vs. cygwin
> file specifications. The above was running sqlite from the cygwin
> command prompt.
> But running it from the MS DOS command prompt with
> E:\data\db\SQLite>C:/bin/os/cygwin/bin/sqlite3 gi2taxonNt
> gives almost the same error (just a different /var/tmp filename).
> I have write permission on /var/tmp and can make files in it.
>

But apparently it is trying to create a file in a subdirectory.  I don't
quite understand that.

-- 
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] An "unable to open database file" error that has nothing to do with opening database file

[Philip Goetz]

On Tue, Jun 4, 2013 at 11:27 AM, Richard Hipp  wrote:
> Try adding the command:
>
>  .log stdout
>
> before running the CREATE INDEX and see what errors you get back.

sqlite> .schema
CREATE TABLE taxon (gi INTEGER, taxon INTEGER);
sqlite> .log stdout
sqlite> create index taxon_gi_index on taxon(gi);
(14) os_win.c:34063: (3)
winOpen(/var/tmp/etilqs_PIREaghry4bPES8\etilqs_02LTi9u3HO3cx0g) - The
system cannot find the path specified.
(14) cannot open file at line 34071 of [cbea02d938]
(14) statement aborts at 21: [create index taxon_gi_index on taxon(gi);]
Error: unable to open database file

Not finding paths often has to do with windows vs. unix vs. cygwin
file specifications. The above was running sqlite from the cygwin
command prompt.
But running it from the MS DOS command prompt with
E:\data\db\SQLite>C:/bin/os/cygwin/bin/sqlite3 gi2taxonNt
gives almost the same error (just a different /var/tmp filename).
I have write permission on /var/tmp and can make files in it.

$ ls -l /var/tmp
total 4.0K
drwxrwxrwt+ 1 phil None 0 Jun  4 11:56 ./
drwxr-xr-x+ 1 phil None 0 May 28 22:10 ../
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Richard Hipp]

On Tue, Jun 4, 2013 at 11:22 AM, Philip Goetz  wrote:

> "unable to open database file" errors are usually caused by incorrect
> file protection settings or directory protection settings, but I don't
> think this one is.
>
> This is a 5.0G database with one table that I created in Windows 7
> 64-bit with sqlite3 3.7.16.2 from Perl. I can connect to the DB, read
> from the db, write to the db, and retrieve back what I wrote to it,
> but I can't create an index on it.  I was able to create an index on
> it by copying it to a Linux system and creating the index there. I
> have write permission on the directory. (You can never be sure, with
> the madness of the Windows 7 file security and read-only settings, but
> I am able to create and index other databases in the same directory in
> the same way when they are not so large.)
>
> $ ls -l gi2taxonNt
> -rwxrwxrwx+ 1 phil None 5.0G Jun  4 11:01 gi2taxonNt
> $ sqlite3.exe gi2taxonNt
> SQLite version 3.7.16.2 2013-04-12 11:52:43
> ...
> sqlite> .schema
> CREATE TABLE taxon (gi INTEGER, taxon INTEGER);
> sqlite> insert into taxon (gi, taxon) VALUES (1, 1);
> sqlite> select * from taxon where gi=1;
> [very long wait...]
> 1|1
>

Try adding the command:

 .log stdout

before running the CREATE INDEX and see what errors you get back.


> sqlite> create index taxon_gi_index on taxon(gi);
> Error: unable to open database file
>
> Same error if I make the CREATE INDEX call thru the Perl DBI.
> Any idea what causes this error?
> Is it caused by using a 32-bit sqlite3?  How does a 32-bit app access
> a 5G file?  Is there a 64-bit Windows or Linux SQLite available?
> ___
> 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] Wiki out of date?

[Richard Hipp]

On Tue, Jun 4, 2013 at 11:07 AM, Philip Bennefall wrote:

> Hello,
>
> I was looking at the following page on the wiki:
>
> http://www.sqlite.org/cvstrac/**wiki?p=**LibraryRoutineCalledOutOfSeque**
> nce
>
>
> And it states that one of the causes for SQLITE_MISUSE being returned is:
>
> "Trying to use the same database connection at the same instant in time
> from two or more threads."
>
> Isn't this outdated? Doesn't this depend on the SqLite threading mode
> nowadays?
>

Very out-of-date.  That hold CVSTrac wiki is obsolete and has been
read-only for years.  It is retained for historical reference only.

-- 
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] An "unable to open database file" error that has nothing to do with opening database file

[Philip Goetz]

"unable to open database file" errors are usually caused by incorrect
file protection settings or directory protection settings, but I don't
think this one is.

This is a 5.0G database with one table that I created in Windows 7
64-bit with sqlite3 3.7.16.2 from Perl. I can connect to the DB, read
from the db, write to the db, and retrieve back what I wrote to it,
but I can't create an index on it.  I was able to create an index on
it by copying it to a Linux system and creating the index there. I
have write permission on the directory. (You can never be sure, with
the madness of the Windows 7 file security and read-only settings, but
I am able to create and index other databases in the same directory in
the same way when they are not so large.)

$ ls -l gi2taxonNt
-rwxrwxrwx+ 1 phil None 5.0G Jun  4 11:01 gi2taxonNt
$ sqlite3.exe gi2taxonNt
SQLite version 3.7.16.2 2013-04-12 11:52:43
...
sqlite> .schema
CREATE TABLE taxon (gi INTEGER, taxon INTEGER);
sqlite> insert into taxon (gi, taxon) VALUES (1, 1);
sqlite> select * from taxon where gi=1;
[very long wait...]
1|1
sqlite> create index taxon_gi_index on taxon(gi);
Error: unable to open database file

Same error if I make the CREATE INDEX call thru the Perl DBI.
Any idea what causes this error?
Is it caused by using a 32-bit sqlite3?  How does a 32-bit app access
a 5G file?  Is there a 64-bit Windows or Linux SQLite available?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit of attached databases

[Eleytherios Stamatogiannakis]

On 04/06/13 17:37, Simon Slavin wrote:


On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis  wrote:


Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* 
attached DBs?


See section 11 of



It's a 64-bit value, and two bits are already taken up.


Yes i have seen it in SQLite's code. I considered changing it to a 
bitfield, but the problem is that this long int is used in various other 
places in SQLite's internals for transaction' metadata.




You can attach databases, copy data from them to the main database, then detach 
those and attach some others.  Or you can create a hierarchy of shards (each of 
62 shards can point to up to 62 others).  Or you can rewrite your code so it 
never uses more than 62 shards no matter how many nodes are available.


Attaching and detaching is only useful when materializing the shards 
into a single table:


create table T
attach T1
insert into T select * from T1.T
detach T1
attach T2
insert into T select * from T2.T
...

How would a hierarchy of shards work? You cannot attach a DB onto 
another attached DB for the hierarchy idea to work.


Also, only using 62 shards at a time can be very constrained/slow in our 
use case (see below).



None of them good solutions, I'm afraid.


Yes :-(.


Also is there anyway for SQLite to create an automatic index on a view (or 
Virtual Table), without having to first materialize the view (or VT)?


I believe that SQLite needs the data to be in one place (i.e. at least a 
virtual table) for the indexing routine to work.


We have tried with both views and VTs but SQLite does not create 
automatic indexes on them at all. So right now, to be able to have 
automatic indexes from SQLite's side we materialize all Virtual Tables 
into plain tables:


create temp table T as select * from UnionAllVT1;
create temp table G as select * from UnionAllVT2;
...

which doubles our I/O to process a single sharded table.

 - 1 full read + 1 full write of all data to materialize the UnionAllVT 
into a plain table.
 - 1 full read + 1 full write of the data in the materialized table to 
create the automatic index.


It would be very nice if the automatic index could be created directly 
from the UnionAllVT, but we haven't found a way to do it.



If you're willing to put a bit of SQLite-only effort in, you could implement 
your own virtual table implementation that consulted data on each of your 
nodes.  This would be quite highly customised for your own application's 
requirements but it would mean you didn't have to do any attaching or detaching 
at all.  Your SQLite API calls could address your data as if it was all in one 
database file but SQLite would understand how data is partitioned between nodes 
and automatically gather it from all the necessary nodes.


We already have done this (creating VTs is very easy in madIS [*]). We 
have the UnionALL virtual table that scans over all the DB shards.


Above UnionALL VT only supports scans, and it is only used to 
materialize the shards into a regular table. It would be very costly 
having per shard indexes, because each Filter on the UnionALL VT would 
need to be passed to all of the shards. A single automatic index, works 
best.



Another way to do it would be to implement your own VFS which would distribute 
over the nodes not at the row level but as if they were all one huge storage 
medium (i.e. like a RAID).


Each shard that we use is already a self contained SQLite DB. We would 
need to change our whole approach to convert to a disk page based 
sharding approach.


Thanks for your ideas.

l.

[*] 
https://code.google.com/p/madis/source/browse/src/functions/vtable/unionalldb.py


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

[sqlite] Wiki out of date?

[Philip Bennefall]

Hello,

I was looking at the following page on the wiki:

http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence


And it states that one of the causes for SQLITE_MISUSE being returned is:

"Trying to use the same database connection at the same instant in time from 
two or more threads."


Isn't this outdated? Doesn't this depend on the SqLite threading mode 
nowadays?


Kind regards,

Philip Bennefall 


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

Re: [sqlite] Limit of attached databases

[Michael Black]

Oops...make that an unsigned int.

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __uint128_t yDbMask;

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Black
Sent: Tuesday, June 04, 2013 9:51 AM
To: est...@gmail.com; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Limit of attached databases

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and
have a current enough gcc (I'm using 4.4.4 and this works on Linux and
Windows)
Looks like a fairly easy change in the code.
Unless somebody already knows that this won't work?

main()
{
__uint128_t i128;
printf("i128=%d\n",sizeof(i128));
}
i128=16

Change this to 126
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 126
#endif

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __int128_t yDbMask;

Change this to 126 instead of 62
#if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>62
# error SQLITE_MAX_ATTACHED must be between 0 and 62
#endif

And see if it works OK for you.  I have no way to test this but it does
compile for me.

I don't see why it wouldn't work.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eleytherios
Stamatogiannakis
Sent: Tuesday, June 04, 2013 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Limit of attached databases

Hi,

During our work on a distributed processing system (which uses SQLite 
shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs.

The way we use SQLite for distributed processing [*], is the following:
  - Each table is sharded into multiple SQLite DBs on different nodes of 
the cluster.
  - To process a query, we run on each shard a query which produces 
multiple sharded SQLite result DBs.
  - We redistribute in the cluster the result DBs, and the next set of 
cluster nodes, attaches all the input shard SQLite DBs, and it creates a 
temp view that unions all the input DB shards into a single view.
  - It then executes a query on the views that produces new result DB shards
  - and so on

We recently got access to a cluster of 64 nodes and it is very easy now 
to hit the SQLITE_MAX_ATTACHED limit (1 DB shard gets produced per node).

So the question that i have is:

Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read 
only* attached DBs?

Also is there anyway for SQLite to create an automatic index on a view 
(or Virtual Table), without having to first materialize the view (or VT)?

Thanks in advance.

Lefteris Stamatogiannakis.

[*] The same processing ideas are used in hadapt:

http://hadapt.com/

which uses Postgres for the DB shards.
___
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] Limit of attached databases

[Michael Black]

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and
have a current enough gcc (I'm using 4.4.4 and this works on Linux and
Windows)
Looks like a fairly easy change in the code.
Unless somebody already knows that this won't work?

main()
{
__uint128_t i128;
printf("i128=%d\n",sizeof(i128));
}
i128=16

Change this to 126
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 126
#endif

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __int128_t yDbMask;

Change this to 126 instead of 62
#if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>62
# error SQLITE_MAX_ATTACHED must be between 0 and 62
#endif

And see if it works OK for you.  I have no way to test this but it does
compile for me.

I don't see why it wouldn't work.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eleytherios
Stamatogiannakis
Sent: Tuesday, June 04, 2013 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Limit of attached databases

Hi,

During our work on a distributed processing system (which uses SQLite 
shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs.

The way we use SQLite for distributed processing [*], is the following:
  - Each table is sharded into multiple SQLite DBs on different nodes of 
the cluster.
  - To process a query, we run on each shard a query which produces 
multiple sharded SQLite result DBs.
  - We redistribute in the cluster the result DBs, and the next set of 
cluster nodes, attaches all the input shard SQLite DBs, and it creates a 
temp view that unions all the input DB shards into a single view.
  - It then executes a query on the views that produces new result DB shards
  - and so on

We recently got access to a cluster of 64 nodes and it is very easy now 
to hit the SQLITE_MAX_ATTACHED limit (1 DB shard gets produced per node).

So the question that i have is:

Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read 
only* attached DBs?

Also is there anyway for SQLite to create an automatic index on a view 
(or Virtual Table), without having to first materialize the view (or VT)?

Thanks in advance.

Lefteris Stamatogiannakis.

[*] The same processing ideas are used in hadapt:

http://hadapt.com/

which uses Postgres for the DB shards.
___
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] Limit of attached databases

[Simon Slavin]

On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis  wrote:

> Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* 
> attached DBs?

See section 11 of



It's a 64-bit value, and two bits are already taken up.

You can attach databases, copy data from them to the main database, then detach 
those and attach some others.  Or you can create a hierarchy of shards (each of 
62 shards can point to up to 62 others).  Or you can rewrite your code so it 
never uses more than 62 shards no matter how many nodes are available.

None of them good solutions, I'm afraid.

> Also is there anyway for SQLite to create an automatic index on a view (or 
> Virtual Table), without having to first materialize the view (or VT)?

I believe that SQLite needs the data to be in one place (i.e. at least a 
virtual table) for the indexing routine to work.

If you're willing to put a bit of SQLite-only effort in, you could implement 
your own virtual table implementation that consulted data on each of your 
nodes.  This would be quite highly customised for your own application's 
requirements but it would mean you didn't have to do any attaching or detaching 
at all.  Your SQLite API calls could address your data as if it was all in one 
database file but SQLite would understand how data is partitioned between nodes 
and automatically gather it from all the necessary nodes.



Another way to do it would be to implement your own VFS which would distribute 
over the nodes not at the row level but as if they were all one huge storage 
medium (i.e. like a RAID).



I don't know which, if either, to recommend.  This kind of programming is 
beyond me, but someone into C and with a good understanding of your farm should 
be able to do it.

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

[sqlite] Limit of attached databases

[Eleytherios Stamatogiannakis]

Hi,

During our work on a distributed processing system (which uses SQLite 
shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs.


The way we use SQLite for distributed processing [*], is the following:
 - Each table is sharded into multiple SQLite DBs on different nodes of 
the cluster.
 - To process a query, we run on each shard a query which produces 
multiple sharded SQLite result DBs.
 - We redistribute in the cluster the result DBs, and the next set of 
cluster nodes, attaches all the input shard SQLite DBs, and it creates a 
temp view that unions all the input DB shards into a single view.

 - It then executes a query on the views that produces new result DB shards
 - and so on

We recently got access to a cluster of 64 nodes and it is very easy now 
to hit the SQLITE_MAX_ATTACHED limit (1 DB shard gets produced per node).


So the question that i have is:

Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read 
only* attached DBs?


Also is there anyway for SQLite to create an automatic index on a view 
(or Virtual Table), without having to first materialize the view (or VT)?


Thanks in advance.

Lefteris Stamatogiannakis.

[*] The same processing ideas are used in hadapt:

http://hadapt.com/

which uses Postgres for the DB shards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row_number?

[Igor Tandetnik]

On 6/4/2013 8:49 AM, Paxdo wrote:

In fact, it is for purposes of pagination.

For example, I have a web application that displays a list of customers.
Each page of the list is 20 lines. It is sorted by city name.


http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

--
Igor Tandetnik

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

Re: [sqlite] Row_number?

[Paxdo]

Thank you Clemens and Michael!

In fact, it is for purposes of pagination.

For example, I have a web application that displays a list of customers. 
Each page of the list is 20 lines. It is sorted by city name.

If I need to display the customer N°45, I need to know its position in the list 
(sorted on the name of the city) to display the right page.

olivier

Le 4 juin 2013 à 14:41, Michael Black  a écrit :

> Or perhaps this is better since it is your example:
> 
> 
> sqlite> create table people(id,name);
> sqlite> insert into people values(5,'Chris');
> sqlite> insert into people values(12,'Arthur');
> sqlite> insert into people values(23,'Bill');
> sqlite> insert into people values(34,'Ron');
> sqlite> insert into people values(43,'William');
> sqlite> select rowid,* from people;
> 1|5|Chris
> 2|12|Arthur
> 3|23|Bill
> 4|34|Ron
> 5|43|William
> sqlite> create table mylist as select id,name from people order by name;
> sqlite> select rowid,* from mylist;
> 1|12|Arthur
> 2|23|Bill
> 3|5|Chris
> 4|34|Ron
> 5|43|William
> 
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paxdo
> Sent: Tuesday, June 04, 2013 4:50 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Row_number?
> 
> 
> Hello,
> 
> I have a problem and I do not find the solution with Sqlite. I need an
> equivalent to ROW_NUMBER.
> 
> Here's the problem:
> 
> SELECT id, name FROM people ORDER BY name
> 
> On this, I would like to know the line number of the ID 34, how?
> 
> example:
> 
> SELECT id, name FROM people ORDER BY name
> Result:
> 
> 12 Arthur
> 23 Bill
> 5 Chris
> 34 Ron
> 43 William
> 
> The line number of the ID34 is 4. 
> But how to know with sqlite?
> 
> Thank you very much!
> 
> Olivier
> ___
> 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] Row_number?

[Michael Black]

Or perhaps this is better since it is your example:


sqlite> create table people(id,name);
sqlite> insert into people values(5,'Chris');
sqlite> insert into people values(12,'Arthur');
sqlite> insert into people values(23,'Bill');
sqlite> insert into people values(34,'Ron');
sqlite> insert into people values(43,'William');
sqlite> select rowid,* from people;
1|5|Chris
2|12|Arthur
3|23|Bill
4|34|Ron
5|43|William
sqlite> create table mylist as select id,name from people order by name;
sqlite> select rowid,* from mylist;
1|12|Arthur
2|23|Bill
3|5|Chris
4|34|Ron
5|43|William


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paxdo
Sent: Tuesday, June 04, 2013 4:50 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Row_number?


Hello,

I have a problem and I do not find the solution with Sqlite. I need an
equivalent to ROW_NUMBER.

Here's the problem:

SELECT id, name FROM people ORDER BY name

On this, I would like to know the line number of the ID 34, how?

example:

SELECT id, name FROM people ORDER BY name
Result:

12 Arthur
23 Bill
5 Chris
34 Ron
43 William

The line number of the ID34 is 4. 
But how to know with sqlite?

Thank you very much!

Olivier
___
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] Row_number?

[Michael Black]

Do you want rowid perhaps for a guaranteed one-to-one mapping to the row
regardless of the query?  Or are you looking for a repeatable one-up counter
for the query results?

select rowid,id,name from people ORDER BY name;

If you want a one-up counter automagically you can create another table from
the query like this example too:


sqlite> create table a(b);
sqlite> insert into a values(1);
sqlite> insert into a values(2);
sqlite> insert into a values(3);
sqlite> insert into a values(4);
sqlite> insert into a values(5);
sqlite> create table c as select * from a where (b % 2)==0;
sqlite> select rowid,* from c;
1|2
2|4

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paxdo
Sent: Tuesday, June 04, 2013 4:50 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Row_number?


Hello,

I have a problem and I do not find the solution with Sqlite. I need an
equivalent to ROW_NUMBER.

Here's the problem:

SELECT id, name FROM people ORDER BY name

On this, I would like to know the line number of the ID 34, how?

example:

SELECT id, name FROM people ORDER BY name
Result:

12 Arthur
23 Bill
5 Chris
34 Ron
43 William

The line number of the ID34 is 4. 
But how to know with sqlite?

Thank you very much!

Olivier
___
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] Row_number?

[Clemens Ladisch]

Paxdo wrote:
> I need an equivalent to ROW_NUMBER.
>
> SELECT id, name FROM people ORDER BY name
>
> 12 Arthur
> 23 Bill
> 5 Chris
> 34 Ron
> 43 William
>
> The line number of the ID34 is 4.
> But how to know with sqlite?

Count how often you have called sqlite3_step() on the statement.

What do you need this number for?


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

[sqlite] Row_number?

[Paxdo]

Hello,

I have a problem and I do not find the solution with Sqlite. I need an 
equivalent to ROW_NUMBER.

Here's the problem:

SELECT id, name FROM people ORDER BY name

On this, I would like to know the line number of the ID 34, how?

example:

SELECT id, name FROM people ORDER BY name
Result:

12 Arthur
23 Bill
5 Chris
34 Ron
43 William

The line number of the ID34 is 4. 
But how to know with sqlite?

Thank you very much!

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