[sqlite] Infinity

[Dan Bishop]

I've noticed that I can use IEEE Infinity values in SQLite by writing 
any literal too big for a double.

sqlite> CREATE TABLE foo (x REAL);
sqlite> INSERT INTO foo VALUES (9e999); -- +Inf
sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf
sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted 
to NULL
sqlite> .null NULL
sqlite> select * FROM foo;
Inf
-Inf
NULL
sqlite> SELECT * FROM foo WHERE ABS(x) = 9e999;
Inf
-Inf

Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS 
TEXT) = 'Inf'?  What's the preferred SQL syntax for infinity?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sparse matrix in scientific computing with sqlite

[Dan Bishop]

Michael Chen wrote:
> --this is my first version for the purpose of storing sparse numerical
> matrix in sql
> --please let me know how to fix the bug at the end of the file, and how to
> tune the performance
> --or any better reference, thanks!
>
> .explain-- return result in more readable
> format
> .echo on-- echo the sql stmnt
>
> --I plan to store my sparse matrix (3 x 4) in table like this:
> -- [ 1 2 0 0 ]
> -- [ 0 3 9 0 ]
> -- [ 0 1 4 0 ]
>   
A sparse matrix can be stored in a table like

CREATE TABLE Matrix (
   Row INTEGER NOT NULL,
   Col INTEGER NOT NULL,
   Value REAL,
   PRIMARY KEY (Row, Col)
);

With this representation, arithmetic on matrices can be done as:

-- MatrixA + MatrixB
SELECT Row, Col, SUM(Value) AS Value FROM (SELECT * FROM MatrixA UNION 
ALL SELECT * FROM MatrixB) GROUP BY Row, Col;

-- MatrixA - MatrixB
SELECT Row, Col, SUM(Value) AS Value FROM (SELECT * FROM MatrixA UNION 
ALL SELECT Row, Col, -Value FROM MatrixB) GROUP BY Row, Col;

-- MatrixA * MatrixB
SELECT MatrixA.Row AS Row, MatrixB.Col AS Col, SUM(MatrixA.Value * 
MatrixB.Value) AS Value FROM MatrixA INNER JOIN MatrixB ON MatrixA.Col = 
MatrixB.Row GROUP BY Row, Col;

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

Re: [sqlite] DIVIDE (was Re: INTERSECT?)

[Jay A. Kreibich]

On Fri, Oct 16, 2009 at 05:34:02PM -0700, Darren Duncan scratched on the wall:
> I believe what you are looking for is the relational operator called DIVIDE, 

  Yes, this is a text-book example of a Relational "divide."  If you
  have a table with a key column and an attribute column, and you want
  a list of all the keys with at least a specific set of attributes,
  that's basically a divide.

  As many others have pointed out, it is easy enough to simulate a
  divide when there are only a few terms, but things get much more
  complex (and SLOW...) when you need to match against five or six
  (or more) attributes.  Another way to do it is a series of self-joins
  for each required attribute.  Again, works with a small number but
  gets complex and slow very very quickly.  For most approaches you
  also need custom and specific SQL statements for each number of
  terms.  A divide operator would allow an arbitrary match.

  In a sense, I'm still surprised this isn't in SQL.  Many-to-One
  key/attribute tables are extremely common, as is the need to find
  something with a specific set of attributes.  Codd figured that out
  easily enough.

> For example, you can do it with a nested pair of NOT EXISTS subqueries.

  This is the best solution I've found.  Read the Celko article.  The
  pilot/airplane example is also covered in SQL For Smarties and a
  number of his other books.  Just be warned that the SQL can get
  really interesting.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTERSECT?

[Dan Bishop]

P Kishor wrote:
> I don't even know how to title this post, and it just might be
> something very obvious. Either way, I apologize in advance. Consider
> the following --
>
> sqlite> SELECT * FROM foo;
> a   b
> --  --
> 1   6
> 2   6
> 2   3
> 3   3
> 3   4
> 3   5
> 4   7
> 4   8
> 5   3
> 5   4
> 6   9
> 6   10
> 7   11
> 7   12
> 8   13
> 8   14
> 9   7
> 9   15
> 9   16
> 10  17
>
> I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just
> an example. In reality, b could be any set of numbers, not just two
> numbers). To illustrate --
> sqlite> SELECT * FROM foo WHERE b = 3;
> a   b
> --  --
> 2   3
> 3   3
> 5   3
> sqlite> SELECT * FROM foo WHERE b = 4;
> a   b
> --  --
> 3   4
> 5   4
>
> So, I really want only the following rows
> 3   3
> 5   3
> 3   4
> 5   4
>
> I don’t want
>
> 2   3
>
> because there is no
>
> 2   4
>
> sqlite> SELECT * FROM foo WHERE b = 3 AND b = 4;
> sqlite> SELECT * FROM foo WHERE b = 3 OR b = 4;
> a   b
> --  --
> 2   3
> 3   3
> 3   4
> 5   3
> 5   4
> sqlite> SELECT * FROM foo WHERE b IN (3, 4);;
> a   b
> --  --
> 2   3
> 3   3
> 3   4
> 5   3
> 5   4
> sqlite> SELECT * FROM foo WHERE b = 3
>...> UNION
>...> SELECT * FROM foo WHERE b = 4;
> a   b
> --  --
> 2   3
> 3   3
> 3   4
> 5   3
> 5   4
>
> I guess I need something like
>
> sqlite> SELECT * FROM ideas_tags WHERE tag_id = 3
>...> INTERSECT
>...> SELECT * FROM ideas_tags WHERE tag_id = 4;
> sqlite>
>
>   
You can do it with a self-join:

SELECT foo.a, foo.b FROM foo INNER JOIN foo foo2 ON foo.a = foo2.a WHERE 
(foo.b = 3 AND foo2.b = 4) OR (foo.b = 4 AND foo2.b = 3)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DIVIDE (was Re: INTERSECT?)

[Darren Duncan]

I believe what you are looking for is the relational operator called DIVIDE, 
which is one of the original ones that E. F. Codd defined (along with (natural 
inner) JOIN, UNION, MINUS, PROJECT, RESTRICT, etc) but that very few if any SQL 
DBMSs actually implement directly, which is a crying shame.

Fortunately you can simulate DIVIDE using a combination of other relational 
operators, so it is still possible to do this in SQLite.  For example, you can 
do it with a nested pair of NOT EXISTS subqueries.

DIVIDE is basically the opposite of JOIN (or more specifically, cartesian 
product), where JOIN is analogous to a multiplication (and so is INTERSECT, and 
UNION is analogous to sum, and MINUS self-explanatory).  An example of where 
you 
might use DIVIDE is with the query "who are the pilots where for each pilot 
that 
pilot can fly every plane in this hangar", so only the pilots that match every 
plane are returned and those that match just some aren't.  (DIVIDE also has the 
analogy of divide by zero, which comes up if the total number of planes period 
is empty; in that case every pilot is returned.  Or some people might say none.)

See http://en.wikipedia.org/wiki/Relational_algebra#Division for an explanation 
of DIVIDE and what its useful for.

See also http://www.dbazine.com/ofinterest/oi-articles/celko1 for an 
explanation 
of DIVIDE that shows how to simulate it with standard SQL.

In your case, you would use a DIVIDE like this:

   SELECT * FROM bar;
   b
   
   3
   4

   SELECT * FROM (foo DIVIDE bar);
   a
   
   3
   5

You can probably see how that's the the opposite of JOIN.

If the result of the DIVIDE above were in tables quux, then you could get the 
original bar rows back by saying:

   SELECT * FROM quux JOIN bar

Now it would be really great if SQLite would help lead the pack and implement a 
DIVIDE operator natively, which is just shorthand syntax for SQL it already 
supports.

-- Darren Duncan

P Kishor wrote:
> I don't even know how to title this post, and it just might be
> something very obvious. Either way, I apologize in advance. Consider
> the following --
> 
> I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just
> an example. In reality, b could be any set of numbers, not just two
> numbers). To illustrate --
> sqlite> SELECT * FROM foo WHERE b = 3;
> a   b
> --  --
> 2   3
> 3   3
> 5   3
> sqlite> SELECT * FROM foo WHERE b = 4;
> a   b
> --  --
> 3   4
> 5   4
> 
> So, I really want only the following rows
> 3   3
> 5   3
> 3   4
> 5   4
> 
> I don’t want
> 
> 2   3
> 
> because there is no
> 
> 2   4
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTERSECT?

[Igor Tandetnik]

P Kishor  wrote:
> I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just
> an example. In reality, b could be any set of numbers, not just two
> numbers). To illustrate --
> sqlite> SELECT * FROM foo WHERE b = 3;
> a   b
> --  --
> 2   3
> 3   3
> 5   3
> sqlite> SELECT * FROM foo WHERE b = 4;
> a   b
> --  --
> 3   4
> 5   4
> 
> So, I really want only the following rows
> 3   3
> 5   3
> 3   4
> 5   4

select * from foo where a in 
(select a from foo where b=3
 intersect
 select a from foo where b=4)
and b in (3, 4);

Or something like this - it scales to longer lists:

select * from foo f1 where
(select count(*) from (select distinct b from foo f2 where f1.a = f2.a and f2.b 
in (...) )) =
length_of_b_list
and b in (...);

Igor Tandetnik


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

[sqlite] INTERSECT?

[P Kishor]

I don't even know how to title this post, and it just might be
something very obvious. Either way, I apologize in advance. Consider
the following --

sqlite> SELECT * FROM foo;
a   b
--  --
1   6
2   6
2   3
3   3
3   4
3   5
4   7
4   8
5   3
5   4
6   9
6   10
7   11
7   12
8   13
8   14
9   7
9   15
9   16
10  17

I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just
an example. In reality, b could be any set of numbers, not just two
numbers). To illustrate --
sqlite> SELECT * FROM foo WHERE b = 3;
a   b
--  --
2   3
3   3
5   3
sqlite> SELECT * FROM foo WHERE b = 4;
a   b
--  --
3   4
5   4

So, I really want only the following rows
3   3
5   3
3   4
5   4

I don’t want

2   3

because there is no

2   4

sqlite> SELECT * FROM foo WHERE b = 3 AND b = 4;
sqlite> SELECT * FROM foo WHERE b = 3 OR b = 4;
a   b
--  --
2   3
3   3
3   4
5   3
5   4
sqlite> SELECT * FROM foo WHERE b IN (3, 4);;
a   b
--  --
2   3
3   3
3   4
5   3
5   4
sqlite> SELECT * FROM foo WHERE b = 3
   ...> UNION
   ...> SELECT * FROM foo WHERE b = 4;
a   b
--  --
2   3
3   3
3   4
5   3
5   4

I guess I need something like

sqlite> SELECT * FROM ideas_tags WHERE tag_id = 3
   ...> INTERSECT
   ...> SELECT * FROM ideas_tags WHERE tag_id = 4;
sqlite>


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sparse matrix in scientific computing with sqlite

[Michael Chen]

yes, that's what I am thinking of too. This big table is in charge of store
all matrix, keep track of all index changes, and rollback when needed. I
will only extract a tiny part from this big table in format like a sparse
matrix, and put it in C array, then the available numerical routines, such
as matrix inversion, can be applied to the C array. So this big table for
all matrix is a information manager or a data warehouse in my thinking. I am
not sure how easy it will be to use this big table as a central warehouse
for now.


On Fri, Oct 16, 2009 at 3:50 PM, Stephan Wehner wrote:

> On Fri, Oct 16, 2009 at 1:41 PM, Michael Chen
>  wrote:
> > rdbms is indeed not a place for store a single sparse matrix like this.
> > However I have hundreds of them, and I need to break them and recombine
> them
> > frequently; furthermore, I need to drop a few rows or columns
> successively,
> > and need to be able to trace back what's a row's original index. I think
> > sqlite in my C/C++ code will save lots of boring reindexing coding, and
> it
> > is more robust.
>
> Would you not store all the matrices in one table, and use a
> "matrix_id" column to identify which records are for one matrix ?
>
> Stephan
>
> >
> > On Fri, Oct 16, 2009 at 1:14 PM, P Kishor  wrote:
> >
> >> This is not the answer you are looking for, and there are SQL geniuses
> >> on this list who will help you better, but really, is an rdbms really
> >> a good place to store a matrix the way you are trying to do? So
> >> convoluted.
> >>
> >> My approach, if I really was determined to store it in sqlite, would
> >> be to flatten the matrix in my code, and then stuff that into a table.
> >> Retrieve it and deserialize it as needed. For example, Perl's freeze,
> >> thaw are tailor made for this kind of stuff.
> >>
> >>
> >> On Fri, Oct 16, 2009 at 1:09 PM, Michael Chen
> >>  wrote:
> >> > --this is my first version for the purpose of storing sparse numerical
> >> > matrix in sql
> >> > --please let me know how to fix the bug at the end of the file, and
> how
> >> to
> >> > tune the performance
> >> > --or any better reference, thanks!
> >> >
> >> > .explain-- return result in more
> readable
> >> > format
> >> > .echo on-- echo the sql stmnt
> >> >
> >> > --I plan to store my sparse matrix (3 x 4) in table like this:
> >> > -- [ 1 2 0 0 ]
> >> > -- [ 0 3 9 0 ]
> >> > -- [ 0 1 4 0 ]
> >> > --
> >> > -- is a three-by-four matrix with six nonzero elements, so
> >> > --
> >> > -- A  = [ 1 2 3 9 1 4 ] // List of non-zero matrix element in order
> >> > -- IA = [ 1 3 5 7 ]  //  IA(i) = Index of the first nonzero
> element
> >> of
> >> > row i in A
> >> > -- JA = [ 1 2 2 3 2 3 ] // JA(i) = Column position of the non zero
> >> element
> >> > A(i)
> >> > --
> >> > -- Note: In this example, the row and line index begin by 1 and not 0.
> >> >
> >> >  create table rowids(
> >> >  rows integer primary key  -- there should be an easy way for a
> sequence
> >> > 1..M?
> >> >  );
> >> >
> >> > insert into rowids values (1);
> >> > insert into rowids values (2);
> >> > insert into rowids values (3);
> >> >
> >> > create table colids(
> >> > cols integer primary key   -- there should be an easy way for a
> sequence
> >> > 1..N?
> >> > );
> >> >
> >> > insert into colids values (1);
> >> > insert into colids values (2);
> >> > insert into colids values (3);
> >> > insert into colids values (4);
> >> >
> >> >
> >> > create table matrix(
> >> > rowid integer reference rowids,
> >> > colid integer reference colids,
> >> > value real non null
> >> > );
> >> >
> >> > insert into matrix values (1,1,1);
> >> > insert into matrix values (1,2,2);
> >> > insert into matrix values (2,2,3);
> >> > insert into matrix values (2,3,9);
> >> > insert into matrix values (3,2,1);
> >> > insert into matrix values (3,3,4);
> >> > insert into matrix values (2,4,0); --this entry is inserted on purpose
> >> >
> >> >
> >> > select * from matrix;
> >> >
> >> >
> >> > --A rowwise  sparse matrix is then:
> >> >
> >> > create temp view rowwiseA as
> >> > select  value as A, colid as JA from matrix
> >> > where value != 0
> >> > order by rowid, colid
> >> > ;
> >> >
> >> > create temp view rowwiseB as
> >> > select rowid, count(rowid) as ct
> >> > from matrix
> >> > where value != 0
> >> > group by rowid
> >> > order by rowid
> >> > ;
> >> >
> >> > create temp view rowwiseC as
> >> > select a1.rowid, sum(a2.ct) +1 as JA
> >> > from rowwiseB a1, rowwiseB a2
> >> > where a2.rowid < a1.rowid
> >> > group by a1.rowid
> >> > ;
> >> > --this is not quite right yet, the first entry and last entry of JA
> >> vector
> >> > is not here.
> >> >
> >> > select * from rowwiseA;
> >> > select * from rowwiseB;
> >> > select * from rowwiseC;
> >> >
> >> >
> >> > create temp view rowwiseB as
> >> > select count(rowid) as ia from matrix
> >> > where 

Re: [sqlite] sparse matrix in scientific computing with sqlite

[Stephan Wehner]

On Fri, Oct 16, 2009 at 1:41 PM, Michael Chen
 wrote:
> rdbms is indeed not a place for store a single sparse matrix like this.
> However I have hundreds of them, and I need to break them and recombine them
> frequently; furthermore, I need to drop a few rows or columns successively,
> and need to be able to trace back what's a row's original index. I think
> sqlite in my C/C++ code will save lots of boring reindexing coding, and it
> is more robust.

Would you not store all the matrices in one table, and use a
"matrix_id" column to identify which records are for one matrix ?

Stephan

>
> On Fri, Oct 16, 2009 at 1:14 PM, P Kishor  wrote:
>
>> This is not the answer you are looking for, and there are SQL geniuses
>> on this list who will help you better, but really, is an rdbms really
>> a good place to store a matrix the way you are trying to do? So
>> convoluted.
>>
>> My approach, if I really was determined to store it in sqlite, would
>> be to flatten the matrix in my code, and then stuff that into a table.
>> Retrieve it and deserialize it as needed. For example, Perl's freeze,
>> thaw are tailor made for this kind of stuff.
>>
>>
>> On Fri, Oct 16, 2009 at 1:09 PM, Michael Chen
>>  wrote:
>> > --this is my first version for the purpose of storing sparse numerical
>> > matrix in sql
>> > --please let me know how to fix the bug at the end of the file, and how
>> to
>> > tune the performance
>> > --or any better reference, thanks!
>> >
>> > .explain                                -- return result in more readable
>> > format
>> > .echo on                                -- echo the sql stmnt
>> >
>> > --I plan to store my sparse matrix (3 x 4) in table like this:
>> > -- [ 1 2 0 0 ]
>> > -- [ 0 3 9 0 ]
>> > -- [ 0 1 4 0 ]
>> > --
>> > -- is a three-by-four matrix with six nonzero elements, so
>> > --
>> > -- A  = [ 1 2 3 9 1 4 ] // List of non-zero matrix element in order
>> > -- IA = [ 1 3 5 7 ]      //  IA(i) = Index of the first nonzero element
>> of
>> > row i in A
>> > -- JA = [ 1 2 2 3 2 3 ] // JA(i) = Column position of the non zero
>> element
>> > A(i)
>> > --
>> > -- Note: In this example, the row and line index begin by 1 and not 0.
>> >
>> >  create table rowids(
>> >  rows integer primary key  -- there should be an easy way for a sequence
>> > 1..M?
>> >  );
>> >
>> > insert into rowids values (1);
>> > insert into rowids values (2);
>> > insert into rowids values (3);
>> >
>> > create table colids(
>> > cols integer primary key   -- there should be an easy way for a sequence
>> > 1..N?
>> > );
>> >
>> > insert into colids values (1);
>> > insert into colids values (2);
>> > insert into colids values (3);
>> > insert into colids values (4);
>> >
>> >
>> > create table matrix(
>> > rowid integer reference rowids,
>> > colid integer reference colids,
>> > value real non null
>> > );
>> >
>> > insert into matrix values (1,1,1);
>> > insert into matrix values (1,2,2);
>> > insert into matrix values (2,2,3);
>> > insert into matrix values (2,3,9);
>> > insert into matrix values (3,2,1);
>> > insert into matrix values (3,3,4);
>> > insert into matrix values (2,4,0); --this entry is inserted on purpose
>> >
>> >
>> > select * from matrix;
>> >
>> >
>> > --A rowwise  sparse matrix is then:
>> >
>> > create temp view rowwiseA as
>> > select  value as A, colid as JA from matrix
>> > where value != 0
>> > order by rowid, colid
>> > ;
>> >
>> > create temp view rowwiseB as
>> > select rowid, count(rowid) as ct
>> > from matrix
>> > where value != 0
>> > group by rowid
>> > order by rowid
>> > ;
>> >
>> > create temp view rowwiseC as
>> > select a1.rowid, sum(a2.ct) +1 as JA
>> > from rowwiseB a1, rowwiseB a2
>> > where a2.rowid < a1.rowid
>> > group by a1.rowid
>> > ;
>> > --this is not quite right yet, the first entry and last entry of JA
>> vector
>> > is not here.
>> >
>> > select * from rowwiseA;
>> > select * from rowwiseB;
>> > select * from rowwiseC;
>> >
>> >
>> > create temp view rowwiseB as
>> > select count(rowid) as ia from matrix
>> > where value != 0
>> > group by rowid
>> > order by rowid
>> > ;
>> >
>> >
>> >
>> > --Best regards,
>> > --Michael Chen
>> > --Google Voice Phone.: 847-448-0647
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, 

Re: [sqlite] sparse matrix in scientific computing with sqlite

[Michael Chen]

rdbms is indeed not a place for store a single sparse matrix like this.
However I have hundreds of them, and I need to break them and recombine them
frequently; furthermore, I need to drop a few rows or columns successively,
and need to be able to trace back what's a row's original index. I think
sqlite in my C/C++ code will save lots of boring reindexing coding, and it
is more robust.

On Fri, Oct 16, 2009 at 1:14 PM, P Kishor  wrote:

> This is not the answer you are looking for, and there are SQL geniuses
> on this list who will help you better, but really, is an rdbms really
> a good place to store a matrix the way you are trying to do? So
> convoluted.
>
> My approach, if I really was determined to store it in sqlite, would
> be to flatten the matrix in my code, and then stuff that into a table.
> Retrieve it and deserialize it as needed. For example, Perl's freeze,
> thaw are tailor made for this kind of stuff.
>
>
> On Fri, Oct 16, 2009 at 1:09 PM, Michael Chen
>  wrote:
> > --this is my first version for the purpose of storing sparse numerical
> > matrix in sql
> > --please let me know how to fix the bug at the end of the file, and how
> to
> > tune the performance
> > --or any better reference, thanks!
> >
> > .explain-- return result in more readable
> > format
> > .echo on-- echo the sql stmnt
> >
> > --I plan to store my sparse matrix (3 x 4) in table like this:
> > -- [ 1 2 0 0 ]
> > -- [ 0 3 9 0 ]
> > -- [ 0 1 4 0 ]
> > --
> > -- is a three-by-four matrix with six nonzero elements, so
> > --
> > -- A  = [ 1 2 3 9 1 4 ] // List of non-zero matrix element in order
> > -- IA = [ 1 3 5 7 ]  //  IA(i) = Index of the first nonzero element
> of
> > row i in A
> > -- JA = [ 1 2 2 3 2 3 ] // JA(i) = Column position of the non zero
> element
> > A(i)
> > --
> > -- Note: In this example, the row and line index begin by 1 and not 0.
> >
> >  create table rowids(
> >  rows integer primary key  -- there should be an easy way for a sequence
> > 1..M?
> >  );
> >
> > insert into rowids values (1);
> > insert into rowids values (2);
> > insert into rowids values (3);
> >
> > create table colids(
> > cols integer primary key   -- there should be an easy way for a sequence
> > 1..N?
> > );
> >
> > insert into colids values (1);
> > insert into colids values (2);
> > insert into colids values (3);
> > insert into colids values (4);
> >
> >
> > create table matrix(
> > rowid integer reference rowids,
> > colid integer reference colids,
> > value real non null
> > );
> >
> > insert into matrix values (1,1,1);
> > insert into matrix values (1,2,2);
> > insert into matrix values (2,2,3);
> > insert into matrix values (2,3,9);
> > insert into matrix values (3,2,1);
> > insert into matrix values (3,3,4);
> > insert into matrix values (2,4,0); --this entry is inserted on purpose
> >
> >
> > select * from matrix;
> >
> >
> > --A rowwise  sparse matrix is then:
> >
> > create temp view rowwiseA as
> > select  value as A, colid as JA from matrix
> > where value != 0
> > order by rowid, colid
> > ;
> >
> > create temp view rowwiseB as
> > select rowid, count(rowid) as ct
> > from matrix
> > where value != 0
> > group by rowid
> > order by rowid
> > ;
> >
> > create temp view rowwiseC as
> > select a1.rowid, sum(a2.ct) +1 as JA
> > from rowwiseB a1, rowwiseB a2
> > where a2.rowid < a1.rowid
> > group by a1.rowid
> > ;
> > --this is not quite right yet, the first entry and last entry of JA
> vector
> > is not here.
> >
> > select * from rowwiseA;
> > select * from rowwiseB;
> > select * from rowwiseC;
> >
> >
> > create temp view rowwiseB as
> > select count(rowid) as ia from matrix
> > where value != 0
> > group by rowid
> > order by rowid
> > ;
> >
> >
> >
> > --Best regards,
> > --Michael Chen
> > --Google Voice Phone.: 847-448-0647
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] sparse matrix in scientific computing with sqlite

[Michael Chen]

thanks Pavel !

On Fri, Oct 16, 2009 at 1:24 PM, Pavel Ivanov  wrote:

> > -- IA = [ 1 3 5 7 ]  //  IA(i) = Index of the first nonzero element
> of
> > row i in A
>
> Why 4th element if A has only 3 rows?
>
> > create temp view rowwiseC as
> > select a1.rowid, sum(a2.ct) +1 as JA
> > from rowwiseB a1, rowwiseB a2
> > where a2.rowid < a1.rowid
> > group by a1.rowid
> > ;
> > --this is not quite right yet, the first entry and last entry of JA
> vector
> > is not here.
>
> You want it like this:
>
> select a1.rowid, ifnull(sum(a2.ct), 0) + 1 as IA
> from rowwiseB a1 left outer join rowwiseB a2 on a2.rowid < a1.rowid
> group by a1.rowid;
>
>
> Pavel
>
> On Fri, Oct 16, 2009 at 2:09 PM, Michael Chen
>  wrote:
> > --this is my first version for the purpose of storing sparse numerical
> > matrix in sql
> > --please let me know how to fix the bug at the end of the file, and how
> to
> > tune the performance
> > --or any better reference, thanks!
> >
> > .explain-- return result in more readable
> > format
> > .echo on-- echo the sql stmnt
> >
> > --I plan to store my sparse matrix (3 x 4) in table like this:
> > -- [ 1 2 0 0 ]
> > -- [ 0 3 9 0 ]
> > -- [ 0 1 4 0 ]
> > --
> > -- is a three-by-four matrix with six nonzero elements, so
> > --
> > -- A  = [ 1 2 3 9 1 4 ] // List of non-zero matrix element in order
> > -- IA = [ 1 3 5 7 ]  //  IA(i) = Index of the first nonzero element
> of
> > row i in A
> > -- JA = [ 1 2 2 3 2 3 ] // JA(i) = Column position of the non zero
> element
> > A(i)
> > --
> > -- Note: In this example, the row and line index begin by 1 and not 0.
> >
> >  create table rowids(
> >  rows integer primary key  -- there should be an easy way for a sequence
> > 1..M?
> >  );
> >
> > insert into rowids values (1);
> > insert into rowids values (2);
> > insert into rowids values (3);
> >
> > create table colids(
> > cols integer primary key   -- there should be an easy way for a sequence
> > 1..N?
> > );
> >
> > insert into colids values (1);
> > insert into colids values (2);
> > insert into colids values (3);
> > insert into colids values (4);
> >
> >
> > create table matrix(
> > rowid integer reference rowids,
> > colid integer reference colids,
> > value real non null
> > );
> >
> > insert into matrix values (1,1,1);
> > insert into matrix values (1,2,2);
> > insert into matrix values (2,2,3);
> > insert into matrix values (2,3,9);
> > insert into matrix values (3,2,1);
> > insert into matrix values (3,3,4);
> > insert into matrix values (2,4,0); --this entry is inserted on purpose
> >
> >
> > select * from matrix;
> >
> >
> > --A rowwise  sparse matrix is then:
> >
> > create temp view rowwiseA as
> > select  value as A, colid as JA from matrix
> > where value != 0
> > order by rowid, colid
> > ;
> >
> > create temp view rowwiseB as
> > select rowid, count(rowid) as ct
> > from matrix
> > where value != 0
> > group by rowid
> > order by rowid
> > ;
> >
> > create temp view rowwiseC as
> > select a1.rowid, sum(a2.ct) +1 as JA
> > from rowwiseB a1, rowwiseB a2
> > where a2.rowid < a1.rowid
> > group by a1.rowid
> > ;
> > --this is not quite right yet, the first entry and last entry of JA
> vector
> > is not here.
> >
> > select * from rowwiseA;
> > select * from rowwiseB;
> > select * from rowwiseC;
> >
> >
> > create temp view rowwiseB as
> > select count(rowid) as ia from matrix
> > where value != 0
> > group by rowid
> > order by rowid
> > ;
> >
> >
> >
> > --Best regards,
> > --Michael Chen
> > --Google Voice Phone.: 847-448-0647
> > ___
> > 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
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why FTS3 has the limitations it does

[P Kishor]

On Fri, Oct 16, 2009 at 3:12 PM, Scott Hess  wrote:
> On Wed, Oct 14, 2009 at 11:35 PM, John Crenshaw
>  wrote:
>> The severe limitations on FTS3 seemed odd to me, but I figured I could
>> live with them. Then I starting finding that various queries were giving
>> strange "out of context" errors with the MATCH operator, even though I
>> was following all the documented rules. As a result I started looking
>> deeply into what is going on with FTS3 and I found something that
>> bothers me.
>>
>> These limitations are really completely arbitrary. They should be
>> removable.
>
> fts is mostly the way it is because that was the amount that got done
> before I lost the motivation to carry it further.  The set of possible
> improvements is vast, but they need a motivated party to carry them
> forward.  Some of the integration with SQLite is the way it is mostly
> because it was decided to keep fts outside of SQLite core.  Feel free
> to dive in and improve it.
>
>> You can only use a single index to query a table, after that everything
>> else has to be done with a scan of the results, fair enough. But with
>> FTS3, the match operator works ONLY when the match expression is
>> selected for the index. This means that if a query could allow a row to
>> be selected by either rowid, or a MATCH expression, you can have a
>> problem. If the rowid is selected for use as the index, the MATCH won't
>> be used as the index, and you get errors. Similarly, a query with two
>> MATCH expressions will only be able to use one as the index, so you get
>> errors from the second.
>
> The MATCH code probes term->doclist, there is no facility for probing
> by docid.  At minimum the document will need to be tokenized.
> Worst-case, you could tokenize it to an in-memory segment and probe
> that, which would make good re-use of existing code.  Most efficient
> would be to somehow match directly against the tokenizer output (you
> could look at the snippeting code for hints there).
>
>> My first question is, why was FTS designed like this in the first place?
>
> Because running MATCH against a subset of the table was not considered
> an important use case when designing it?
>
>> Surely this was clear during the design stage, when the design could
>> have been easily changed to accommodate the lookups required for a MATCH
>> function. Is there some compelling performance benefit? Something I
>> missed?
>
> "Easily" is all relative.  There were plenty of hard problems to be
> solved without looking around for a bunch of easy ones to tack on.
>
>> My second question is, can we expect this to change at some point?
>
> Probably not unless someone out there decides to.  I got kind of
> burned out on fts about a year back.


With immense gratitude expressed here to Scott, I feel a bit
disappointed that FTS has fallen out of the core, and out of
"continued development and improvement." It is really a brilliant
piece of work that makes sqlite eminently more usable for a number of
tasks. I was setting up a "tagging" system, and what a nightmare that
was until I realized that I don't have to develop one. I can just use
FTS! It sort of fits right in Google's philosophy that I summarized in
my write-up a while back

http://punkish.org/Why-File-When-You-Can-Full-Text-Search


I don't have the smarts to work on FTS, only the smarts to realize
that it is a great thing to use. I hope someone will pick it up and
that eventually we will have FTS4. Makes life so much easier instead
of dicking around with external search mechanisms such as Lucene or
swish-e or htdig, etc.




>
>> All that is needed is the ability to lookup by a combination
>> of docid and term. Isn't a hash already built while creating a list of
>> terms for storage? What if that hash were stored, indexed by docid?
>
> In database world, space==time.  Storing more data means the system gets 
> slower.
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

P Kishor wrote:
> So, what suggestion might you all have for getting around this?

Why not set a busy timeout?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrY1T8ACgkQmOOfHg372QTfogCdGrcPbCZKxKZ39adEwGjE4H9V
3H0AoNnAXRmgmF9Lv//9cpaFQnkM/fTE
=EcPJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[P Kishor]

On Fri, Oct 16, 2009 at 3:09 PM, Stephan Wehner  wrote:
> On Fri, Oct 16, 2009 at 12:58 PM, Simon Slavin
>  wrote:
>>
>> On 16 Oct 2009, at 8:53pm, P Kishor wrote:
>>
>>> If neither the username nor the email exist in the db, then the
>>> application creates a record and informs the user of success. Except,
>>> the previous ajax request (I am assuming it is the previous ajax
>>> request from onblur event from the email field) has locked the
>>> database, and the app returns an error that "the database is locked."
>>>
>>> So, what suggestion might you all have for getting around this?
>>
>> You're using AJAX ?  That means JavaScript.  Put a 3 second pause into
>> the routine that returns the query about the email field.  3 seconds
>> should be enough to get the database unlocked.
>
> What if two users sign up at the same time? Javascript surely wouldn't help.
>


Right. Which is why I am hoping for some sure-shot way of avoiding
such a race condition.





-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Best Query And Index Structure

[Pavel Ivanov]

Yes, pretty interesting results. I didn't expect that. :)
Query plan seems to suggest that SQLite executes query not in the way
you said but first takes tit table, joins epgdata to it and then joins
tit1 and tit2 to it. So it should be executed faster than you
thought...

I've played with your queries a bit and found the only way to force
SQLite to execute query the way I've intended - to change table
epgdata so that id is not "integer primary key" but has a non-unique
(!) index on it. :) But of course that will not mean that query
execution would be the fastest in this case.
I'm surprised and impressed with SQLite's optimizer. :)

BTW, to make your query fastest you need index on (lang, epgdata_id,
tittext) instead of (lang, tittext, epgdata_id). Even for this
particular query tittext shouldn't be in the index at all.


Pavel

On Fri, Oct 16, 2009 at 8:08 AM, Brad Phelan  wrote:
> On Fri, Oct 16, 2009 at 1:39 PM, Pavel Ivanov  wrote:
>>> So if
>>> x has a very large range and a small probability of a match then
>>> we still have to do a full scan of 10,000 rows of A.
>>>
>>> Is there a better way to construct the query and or indexes so
>>> the result is faster.
>>
>> If your x has a small selectivity in B disregarding of A, i.e. for
>> each x you have pretty small amount of rows in B, then I'd suggest
>> instead of your index create these two:
>>
>> CREATE INDEX index_B on B (x, A_id);
>> CREATE INDEX index_A on A (id);
>>
>> And write your select in this way:
>>
>> select distinct *
>> from A join
>> (select B0.A_id as A_id
>>  from B B0, B B1
>>  where B0.x = 10
>>  and B1.x = 20
>>  and B0.A_id = B1.A_id) B2 on B2.A_id = A.id
>>
>>
>
> I've tried your pattern on my production code with some interesting
> results. The original pattern is below followed by your suggestion.
> However in this case I have used three terms. There is a LIKE "%FOO%"
> term in there which I really should replace with FTS3.
>
> select count(*) from epgdata
>     JOIN tit AS tit0
>         ON tit0.epgdata_id = epgdata.id
>         AND ( (tit0.lang = "deu") AND ((tit0.tittext) LIKE ("%die%")) )
>     JOIN tit AS tit1
>         ON tit1.epgdata_id = tit0.epgdata_id
>         AND ( (tit1.lang = "deu") AND ((tit1.tittext) LIKE ("%der%")) )
>     JOIN tit AS tit2
>         ON tit2.epgdata_id = tit1.epgdata_id
>         AND ( (tit2.lang = "deu") AND ((tit2.tittext) LIKE ("%zu%")) )
>
> ---
>
> select count(*) from epgdata join
>    ( select tit0.epgdata_id as epgdata_id
>      from tit as tit0, tit as tit1, tit as tit2
>      where tit0.lang="deu" and tit0.tittext LIKE "%die%"
>        and tit1.lang="deu" and tit1.tittext LIKE "%der%"
>        and tit2.lang="deu" and tit2.tittext LIKE "%zu%"
>        and tit0.epgdata_id = tit1.epgdata_id
>        and tit0.epgdata_id = tit2.epgdata_id
>    ) as foo on foo.epgdata_id = epgdata.id
>
>
> generates almost identical sqlite bytecode using the EXPLAIN keyword. Some
> of the register numbers are different but the code structure is word for
> word the same. Unfortunately I can't make head or tail of the codes.
>
> The query plan for both of them is
>
>    0|1|TABLE tit AS tit0 WITH INDEX tit__lang__tittext__epgdata_id__
>    1|0|TABLE epgdata USING PRIMARY KEY
>    2|2|TABLE tit AS tit1 WITH INDEX tit__epgdata_id__
>    3|3|TABLE tit AS tit2 WITH INDEX tit__epgdata_id__
>
> I have indices
>
>    (epgdata_id) -> tit__epgdata_id__
>
> and
>
>    (lang, tittext, epgdata_id) -> tit__lang__tittext__epgdata_id__
>
> It seems that SQLite maps both queries to the same internal
> representation. Curious!!
>
> B
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Best Query And Index Structure

[Brad Phelan]

On Fri, Oct 16, 2009 at 2:08 PM, Brad Phelan  wrote:
> On Fri, Oct 16, 2009 at 1:39 PM, Pavel Ivanov  wrote:
>>> So if
>>> x has a very large range and a small probability of a match then
>>> we still have to do a full scan of 10,000 rows of A.
>>>
>>> Is there a better way to construct the query and or indexes so
>>> the result is faster.
>>
>> If your x has a small selectivity in B disregarding of A, i.e. for
>> each x you have pretty small amount of rows in B, then I'd suggest
>> instead of your index create these two:
>>
>> CREATE INDEX index_B on B (x, A_id);
>> CREATE INDEX index_A on A (id);
>>
>> And write your select in this way:
>>
>> select distinct *
>> from A join
>> (select B0.A_id as A_id
>>  from B B0, B B1
>>  where B0.x = 10
>>  and B1.x = 20
>>  and B0.A_id = B1.A_id) B2 on B2.A_id = A.id
>>
>>
>
> I've tried your pattern on my production code with some interesting
> results. The original pattern is below followed by your suggestion.
> However in this case I have used three terms. There is a LIKE "%FOO%"
> term in there which I really should replace with FTS3.
>
> select count(*) from epgdata
>     JOIN tit AS tit0
>         ON tit0.epgdata_id = epgdata.id
>         AND ( (tit0.lang = "deu") AND ((tit0.tittext) LIKE ("%die%")) )
>     JOIN tit AS tit1
>         ON tit1.epgdata_id = tit0.epgdata_id
>         AND ( (tit1.lang = "deu") AND ((tit1.tittext) LIKE ("%der%")) )
>     JOIN tit AS tit2
>         ON tit2.epgdata_id = tit1.epgdata_id
>         AND ( (tit2.lang = "deu") AND ((tit2.tittext) LIKE ("%zu%")) )
>
> ---
>
> select count(*) from epgdata join
>    ( select tit0.epgdata_id as epgdata_id
>      from tit as tit0, tit as tit1, tit as tit2
>      where tit0.lang="deu" and tit0.tittext LIKE "%die%"
>        and tit1.lang="deu" and tit1.tittext LIKE "%der%"
>        and tit2.lang="deu" and tit2.tittext LIKE "%zu%"
>        and tit0.epgdata_id = tit1.epgdata_id
>        and tit0.epgdata_id = tit2.epgdata_id
>    ) as foo on foo.epgdata_id = epgdata.id
>
>
> generates almost identical sqlite bytecode using the EXPLAIN keyword. Some
> of the register numbers are different but the code structure is word for
> word the same. Unfortunately I can't make head or tail of the codes.
>
> The query plan for both of them is
>
>    0|1|TABLE tit AS tit0 WITH INDEX tit__lang__tittext__epgdata_id__
>    1|0|TABLE epgdata USING PRIMARY KEY
>    2|2|TABLE tit AS tit1 WITH INDEX tit__epgdata_id__
>    3|3|TABLE tit AS tit2 WITH INDEX tit__epgdata_id__
>
> I have indices
>
>    (epgdata_id) -> tit__epgdata_id__
>
> and
>
>    (lang, tittext, epgdata_id) -> tit__lang__tittext__epgdata_id__
>
> It seems that SQLite maps both queries to the same internal
> representation. Curious!!
>
> B
>

Actually as I look into it I am not surprised that both queries
translate to the same code. First the ON clause and the WHERE clause
in SQLite are equivalent for inner joins. As well

select count(*) from A, B
where A.id = B.A_id

is identical to

select count(*) from A
join B on A.id = B.A_id

The question seems to be what is the best order to run the join loop
in. From A to B or B to A. SQlite seems to have it's own idea on how
to do this. As can be seen from my production query the loop was
reordered. Perhaps I don't need to worry. All I need to make sure is
that I have the correct indices available to let SQLite run the loop
in whichever order it sees fit. It is all a bit black magic to me :)

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

Re: [sqlite] Fwd: Best Query And Index Structure

[Brad Phelan]

>> So if
>> x has a very large range and a small probability of a match then
>> we still have to do a full scan of 10,000 rows of A.
>>
>> Is there a better way to construct the query and or indexes so
>> the result is faster.
>
> If your x has a small selectivity in B disregarding of A, i.e. for
> each x you have pretty small amount of rows in B, then I'd suggest
> instead of your index create these two:
>
> CREATE INDEX index_B on B (x, A_id);
> CREATE INDEX index_A on A (id);
>
> And write your select in this way:
>
> select distinct *
> from A join
> (select B0.A_id as A_id
>  from B B0, B B1
>  where B0.x = 10
>  and B1.x = 20
>  and B0.A_id = B1.A_id) B2 on B2.A_id = A.id
>
>

I've tried your pattern on my production code with some interesting
results. The original pattern is below followed by your suggestion.
However in this case I have used three terms. There is a LIKE "%FOO%"
term in there which I really should replace with FTS3.

select count(*) from epgdata
    JOIN tit AS tit0
        ON tit0.epgdata_id = epgdata.id
        AND ( (tit0.lang = "deu") AND ((tit0.tittext) LIKE ("%die%")) )
    JOIN tit AS tit1
        ON tit1.epgdata_id = tit0.epgdata_id
        AND ( (tit1.lang = "deu") AND ((tit1.tittext) LIKE ("%der%")) )
    JOIN tit AS tit2
        ON tit2.epgdata_id = tit1.epgdata_id
        AND ( (tit2.lang = "deu") AND ((tit2.tittext) LIKE ("%zu%")) )

---

select count(*) from epgdata join
   ( select tit0.epgdata_id as epgdata_id
     from tit as tit0, tit as tit1, tit as tit2
     where tit0.lang="deu" and tit0.tittext LIKE "%die%"
       and tit1.lang="deu" and tit1.tittext LIKE "%der%"
       and tit2.lang="deu" and tit2.tittext LIKE "%zu%"
       and tit0.epgdata_id = tit1.epgdata_id
       and tit0.epgdata_id = tit2.epgdata_id
   ) as foo on foo.epgdata_id = epgdata.id


generates almost identical sqlite bytecode using the EXPLAIN keyword. Some
of the register numbers are different but the code structure is word for
word the same. Unfortunately I can't make head or tail of the codes.

The query plan for both of them is

   0|1|TABLE tit AS tit0 WITH INDEX tit__lang__tittext__epgdata_id__
   1|0|TABLE epgdata USING PRIMARY KEY
   2|2|TABLE tit AS tit1 WITH INDEX tit__epgdata_id__
   3|3|TABLE tit AS tit2 WITH INDEX tit__epgdata_id__

I have indices

   (epgdata_id) -> tit__epgdata_id__

and

   (lang, tittext, epgdata_id) -> tit__lang__tittext__epgdata_id__

It seems that SQLite maps both queries to the same internal
representation. Curious!!

B
___
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] Fwd: Best Query And Index Structure

[Brad Phelan]

On Fri, Oct 16, 2009 at 1:39 PM, Pavel Ivanov  wrote:
>> So if
>> x has a very large range and a small probability of a match then
>> we still have to do a full scan of 10,000 rows of A.
>>
>> Is there a better way to construct the query and or indexes so
>> the result is faster.
>
> If your x has a small selectivity in B disregarding of A, i.e. for
> each x you have pretty small amount of rows in B, then I'd suggest
> instead of your index create these two:
>
> CREATE INDEX index_B on B (x, A_id);
> CREATE INDEX index_A on A (id);
>
> And write your select in this way:
>
> select distinct *
> from A join
> (select B0.A_id as A_id
>  from B B0, B B1
>  where B0.x = 10
>  and B1.x = 20
>  and B0.A_id = B1.A_id) B2 on B2.A_id = A.id
>
>

I've tried your pattern on my production code with some interesting
results. The original pattern is below followed by your suggestion.
However in this case I have used three terms. There is a LIKE "%FOO%"
term in there which I really should replace with FTS3.

select count(*) from epgdata
 JOIN tit AS tit0
 ON tit0.epgdata_id = epgdata.id
 AND ( (tit0.lang = "deu") AND ((tit0.tittext) LIKE ("%die%")) )
 JOIN tit AS tit1
 ON tit1.epgdata_id = tit0.epgdata_id
 AND ( (tit1.lang = "deu") AND ((tit1.tittext) LIKE ("%der%")) )
 JOIN tit AS tit2
 ON tit2.epgdata_id = tit1.epgdata_id
 AND ( (tit2.lang = "deu") AND ((tit2.tittext) LIKE ("%zu%")) )

---

select count(*) from epgdata join
( select tit0.epgdata_id as epgdata_id
  from tit as tit0, tit as tit1, tit as tit2
  where tit0.lang="deu" and tit0.tittext LIKE "%die%"
and tit1.lang="deu" and tit1.tittext LIKE "%der%"
and tit2.lang="deu" and tit2.tittext LIKE "%zu%"
and tit0.epgdata_id = tit1.epgdata_id
and tit0.epgdata_id = tit2.epgdata_id
) as foo on foo.epgdata_id = epgdata.id


generates almost identical sqlite bytecode using the EXPLAIN keyword. Some
of the register numbers are different but the code structure is word for
word the same. Unfortunately I can't make head or tail of the codes.

The query plan for both of them is

0|1|TABLE tit AS tit0 WITH INDEX tit__lang__tittext__epgdata_id__
1|0|TABLE epgdata USING PRIMARY KEY
2|2|TABLE tit AS tit1 WITH INDEX tit__epgdata_id__
3|3|TABLE tit AS tit2 WITH INDEX tit__epgdata_id__

I have indices

(epgdata_id) -> tit__epgdata_id__

and

(lang, tittext, epgdata_id) -> tit__lang__tittext__epgdata_id__

It seems that SQLite maps both queries to the same internal
representation. Curious!!

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

Re: [sqlite] Fwd: Best Query And Index Structure

[Pavel Ivanov]

> So if
> x has a very large range and a small probability of a match then
> we still have to do a full scan of 10,000 rows of A.
>
> Is there a better way to construct the query and or indexes so
> the result is faster.

If your x has a small selectivity in B disregarding of A, i.e. for
each x you have pretty small amount of rows in B, then I'd suggest
instead of your index create these two:

CREATE INDEX index_B on B (x, A_id);
CREATE INDEX index_A on A (id);

And write your select in this way:

select distinct *
from A join
(select B0.A_id as A_id
 from B B0, B B1
 where B0.x = 10
 and B1.x = 20
 and B0.A_id = B1.A_id) B2 on B2.A_id = A.id


Pavel

On Fri, Oct 16, 2009 at 6:09 AM, Brad Phelan  wrote:
> Hi all,
>
> I am curious on how to design a schema and indexes to best fit the
> following pattern. My
> data is tree like and stored normalized in the database.
>
> CREATE TABLE A
>    ( id INTEGER PRIMARY
>    )
>
> CREATE TABLE B
>    ( id INTEGER PRIMARY
>    , A_ID  INTEGER       # Foreign key to A
>    , x     INTEGER
>    )
>
>
> Now I wish to make queries such as.
>
>    All A where
>    any A/B.x = 10
>    and
>    any A/B.x = 20
>
> This can be coded trivially in SQL as
>
>    select distinct * from A
>    join B as B0 on A.id = B0.A_id and B0.x = 10
>    join B as B1 on A.id = B1.A_id and B0.x = 20
>
> My guess is that the suitable index to create is
>
>    CREATE INDEX index on B
>        ( A_id
>        , x
>        )
>
> However my limited understanding of how SQLite works suggests
> that this will be implemented as
>
> for a in A:
>    for b1 in B where b1.A_id = a.id and b1.x = 10:
>        for b2 in B where b2.A_id = a.id and b2.x = 20:
>            yield a
>
>
> Here the branching factor is quite small. There will be no more than
> 20 or so B's for every A but there may be about 10,000 A's. So if
> x has a very large range and a small probability of a match then
> we still have to do a full scan of 10,000 rows of A.
>
> In this case the index helps the joining but the search is still
> O(N)
>
> Is there a better way to construct the query and or indexes so
> the result is faster.
>
> Regards
>
> Brad Phelan
> ___
> 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] keyword BEGIN in trigger statement

[Pavel Ivanov]

> Does the BEGIN  start a transaction?

No. Triggers work inside transactions that called them.

Pavel

On Fri, Oct 16, 2009 at 5:22 AM, Wenton Thomas  wrote:
> For example,
>
>
> CREATE TRIGGER update_customer_address UPDATE OF address ON customers
>  BEGIN
>    UPDATE orders SET address = new.address WHERE customer_name = old.name;
>  END;
>
> when  updating address ON customers,
> then follow  a  BEGIN ...END  statement.
> Does the BEGIN  start a transaction?
>
>
>
> ___
> 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] Exception writing to database from multiple processes

[Pavel Ivanov]

> Therefore, SQLite
> returns SQLITE_BUSY for the first process, hoping that this will
> induce the first process to release its read lock and allow the
> second process to proceed.
[snip]
>  In short, you can poke at a SQLITE_BUSY state for a bit, but fairly
>  soon you should give up and back all the way out.  If you don't, a
>  deadlock is possible.  But that would be considered an application
>  bug, not an SQLite bug.

That's exactly what I meant - that SQLite doesn't dead locks inside
its own code but returns SQLITE_BUSY instead and only application's
code can cause a dead lock.

Pavel

On Fri, Oct 16, 2009 at 12:37 AM, Jay A. Kreibich  wrote:
> On Thu, Oct 15, 2009 at 10:57:11PM -0400, Pavel Ivanov scratched on the wall:
>> You're definitely talking about some bug in your application or some
>> misunderstanding about how SQLite should work. SQLite by itself never
>> causes any deadlocks.
>
>  As I understand it, that's not exactly true.  SQLite can and does
>  deadlock.  It also tries to detect when this is happening and
>  encourage the application to break the deadlock.  This depends on the
>  applications' cooperation, however.
>
>  From  :
>
>     Consider a scenario where one process is holding a read lock that
>     it is trying to promote to a reserved lock and a second process is
>     holding a reserved lock that it is trying to promote to an
>     exclusive lock. The first process cannot proceed because it is
>     blocked by the second and the second process cannot proceed
>     because it is blocked by the first. If both processes invoke the
>     busy handlers, neither will make any progress. Therefore, SQLite
>     returns SQLITE_BUSY for the first process, hoping that this will
>     induce the first process to release its read lock and allow the
>     second process to proceed.
>
>  This implies that SQLite can figure out what is going on, but will
>  not automatically rollback a transaction and break the deadlock by
>  itself.  That also implies that if an application goes into an infinite
>  "try again" loop whenever it gets an SQLITE_BUSY return code, the
>  deadlock may persist.
>
>  The page  is clear that
>  this is the expected behavior, on behalf of the application:  "It is
>  recommended that applications respond to the errors listed above
>  [including SQLITE_BUSY] by explicitly issuing a ROLLBACK command."
>
>  In short, you can poke at a SQLITE_BUSY state for a bit, but fairly
>  soon you should give up and back all the way out.  If you don't, a
>  deadlock is possible.  But that would be considered an application
>  bug, not an SQLite bug.
>
>    -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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] sqlite-3.6.18: Segmentation fault on SQL INSERT.

[Dan Kennedy]

On Oct 16, 2009, at 2:53 PM, Vladimir Looze wrote:

> Sqlite causes Segmentation fault on SQL insert.
>
> Steps to reproduce:
> 1. create database with following schema (table/column names dosen't  
> matter)
>
> - BEGIN OF SQL LISTING
> create table tname ( id integer not null primary key asc  
> autoincrement,
>   cname string );
> create table surname ( id integer not null primary key asc  
> autoincrement,
>   surname string );
> create table people ( cname integer not null references cname (id),
>   surname integer not null references surnname (id));
> create view peopleview as
>   select tname.cname, surname.surname from people
>   left outer join tname on (tname.id = people.cname)
>   left outer join surname on (surname.id = people.surname);
>
> create trigger create_people instead of insert on peopleview
>   begin
>   insert into tname (cname) values (new.cname);
>   insert into surname (surname) values (new.surname);
>   end;
> - END OF SQL LISTING
>
> 2. execute sql insert statement: insert into peopleview  
> values('John', 'Smith');

Thanks for the report. Looks like the same bug as this one:

   http://www.sqlite.org/src/info/a696379c1f088

Was fixed for 3.6.19.

Dan.

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

Re: [sqlite] Help-using Case in query

[dave lilley]

how about this

select * from TestName where History > 399 and History < 501

the above SQL is saying (in laymans terms)

>From the table TestName gather all the column data and display ONLY those
rows where data in History column is greater than 399 and is below 501.

_OR_

Select ONLY those rows that have data in column History with values greater
than 399 and below 501.

Note this could be changed to History >= 400 and History <= 500 (note i am
not sure in >= placement so it maybe => and =<).

This I am assuming you want - how to take your layman speak and create an
SQL statement.

HTH.

Dave.

2009/10/13 yvette roberson 

> I am definitely an novice in writing but trying to practice and get up to
> speed and would appreciate any help:
>
> In laymen terms I have a quey that I am trying to write with the following
> columns
> TestName (where testname '1' = History, TestName '2'=Math, TestName '3'=
> Western Civilization, TestName '4'=Comp Apps, TestName '5'=Science.
> TestSource (Clep, DSST, Exelisor)
> Test Value  (400,500,600,700,800,900)
> TestUnit (101,102,103,104)
> TestDte  (datetime)
>
> When I pull these in an SSRS report it only returns one line of data I am
> trying to get a list, with the following results
> TestName   TestSource  Test Value   TestUnitTestDte
> HistoryClep   500 10110/1/2009
> Math   DSST 400 10210/3/2009
>
> I did try to use a case , but query only return one row, not sure how to
> use
> a loop in this instance.
>
> thanks in advance
> -Yvette
> ___
> 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] Fwd: Best Query And Index Structure

[Brad Phelan]

Hi all,

I am curious on how to design a schema and indexes to best fit the
following pattern. My
data is tree like and stored normalized in the database.

CREATE TABLE A
   ( id INTEGER PRIMARY
   )

CREATE TABLE B
   ( id INTEGER PRIMARY
   , A_ID  INTEGER       # Foreign key to A
   , x     INTEGER
   )


Now I wish to make queries such as.

   All A where
   any A/B.x = 10
   and
   any A/B.x = 20

This can be coded trivially in SQL as

   select distinct * from A
   join B as B0 on A.id = B0.A_id and B0.x = 10
   join B as B1 on A.id = B1.A_id and B0.x = 20

My guess is that the suitable index to create is

   CREATE INDEX index on B
       ( A_id
       , x
       )

However my limited understanding of how SQLite works suggests
that this will be implemented as

for a in A:
   for b1 in B where b1.A_id = a.id and b1.x = 10:
       for b2 in B where b2.A_id = a.id and b2.x = 20:
           yield a


Here the branching factor is quite small. There will be no more than
20 or so B's for every A but there may be about 10,000 A's. So if
x has a very large range and a small probability of a match then
we still have to do a full scan of 10,000 rows of A.

In this case the index helps the joining but the search is still
O(N)

Is there a better way to construct the query and or indexes so
the result is faster.

Regards

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

[sqlite] keyword BEGIN in trigger statement

[Wenton Thomas]

For example,


CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

when  updating address ON customers,
then follow  a  BEGIN ...END  statement.
Does the BEGIN  start a transaction?


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

[sqlite] sqlite-3.6.18: Segmentation fault on SQL INSERT.

[Vladimir Looze]

Sqlite causes Segmentation fault on SQL insert.

Steps to reproduce:
1. create database with following schema (table/column names dosen't matter)

- BEGIN OF SQL LISTING
create table tname ( id integer not null primary key asc autoincrement,
   cname string );
create table surname ( id integer not null primary key asc autoincrement,
   surname string );
create table people ( cname integer not null references cname (id),
   surname integer not null references surnname (id));
create view peopleview as
   select tname.cname, surname.surname from people
   left outer join tname on (tname.id = people.cname)
   left outer join surname on (surname.id = people.surname);

create trigger create_people instead of insert on peopleview
   begin
insert into tname (cname) values (new.cname);
insert into surname (surname) values (new.surname);
   end;
- END OF SQL LISTING

2. execute sql insert statement: insert into peopleview values('John', 'Smith');

Reproductible: always.

Affected versions: sqlite-3.6.18

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

Re: [sqlite] Exception writing to database from multiple processes

[David Carter]

Thanks everyone for your suggestions.  The locking was caused by not
resetting / finalizing the statements in my transactions.  

Thanks again,

David

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw
Sent: Friday, 16 October 2009 2:00 PM
To: j...@kreibi.ch; General Discussion of SQLite Database
Subject: Re: [sqlite] Exception writing to database from multiple
processes

Yes, sqlite CAN deadlock. It is a weakness of the locking model combined
with a weakness in the transaction model. The big problem is that all
write locks are elevatable. The possibility for deadlock can be
eliminated if only one write lock at a time is elevatable (all others
must be known to not elevate). SQLite could do this I suppose by
treating an explicit transaction as an "elevatable" lock. Additional
write locks can be allowed when an elevatable lock is held, but
additional elevatable locks, and write locks, must be denied. Actually,
that might not be a bad idea, since most of the time explicit
transactions will try to elevate.

Fortunately, rather than REMAIN deadlocked, SQLite will tell the
application to back up and try again. Unfortunately, you can't really
write code like that. It also seems to not always detect the
"deadlocked" scenarios, so you can end up waiting for a full timeout,
then being told that the database is "busy". THEORETICALLY, you could
put a loop around your transaction, and abort/retry any time you get a
BUSY error, but that really just isn't much of an option in the real
world. You can also try IMMEDIATE transactions, but again, not practical
when concurrency is the objective in the first place. Table locking in
the shared cache mode is really the way to go, although this only helps
when all threads are in the same process, and still requires an absurd
amount of extra code to get tolerable concurrency, especially if you
have a long running process, like a sync.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Friday, October 16, 2009 12:38 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Exception writing to database from multiple
processes

On Thu, Oct 15, 2009 at 10:57:11PM -0400, Pavel Ivanov scratched on the
wall:
> You're definitely talking about some bug in your application or some
> misunderstanding about how SQLite should work. SQLite by itself never
> causes any deadlocks.

  As I understand it, that's not exactly true.  SQLite can and does
  deadlock.  It also tries to detect when this is happening and
  encourage the application to break the deadlock.  This depends on the
  applications' cooperation, however.

  From  :

 Consider a scenario where one process is holding a read lock that
 it is trying to promote to a reserved lock and a second process is
 holding a reserved lock that it is trying to promote to an
 exclusive lock. The first process cannot proceed because it is
 blocked by the second and the second process cannot proceed
 because it is blocked by the first. If both processes invoke the
 busy handlers, neither will make any progress. Therefore, SQLite
 returns SQLITE_BUSY for the first process, hoping that this will
 induce the first process to release its read lock and allow the
 second process to proceed.

  This implies that SQLite can figure out what is going on, but will
  not automatically rollback a transaction and break the deadlock by
  itself.  That also implies that if an application goes into an
infinite
  "try again" loop whenever it gets an SQLITE_BUSY return code, the
  deadlock may persist.

  The page  is clear that
  this is the expected behavior, on behalf of the application:  "It is
  recommended that applications respond to the errors listed above
  [including SQLITE_BUSY] by explicitly issuing a ROLLBACK command."

  In short, you can poke at a SQLITE_BUSY state for a bit, but fairly
  soon you should give up and back all the way out.  If you don't, a
  deadlock is possible.  But that would be considered an application
  bug, not an SQLite bug.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
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

Re: [sqlite] length() behaviors on Mac osx

[Michael Chen]

yes, you are right. I'd better to go to sleep now -:)


On Fri, Oct 16, 2009 at 2:23 AM, P Kishor  wrote:

> On Fri, Oct 16, 2009 at 1:42 AM, Michael Chen
>  wrote:
> > select *, (length(path) - length( replace(path, '/','' )) +1 )
>
> sqlite> SELECT length('/1/2/5/');
> 7
> sqlite> SELECT replace('/1/2/5/', '/', '');
> 125
> sqlite> SELECT length('125');
> 3
>
> 7 - 3 = 4
>
> 4 + 1 = 5
>
> sqlite> SELECT (length('/1/2/5/') - length( replace('/1/2/5/', '/','' )) +1
> );
> 5
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] length() behaviors on Mac osx

[P Kishor]

On Fri, Oct 16, 2009 at 1:42 AM, Michael Chen
 wrote:
> select *, (length(path) - length( replace(path, '/','' )) +1 )

sqlite> SELECT length('/1/2/5/');
7
sqlite> SELECT replace('/1/2/5/', '/', '');
125
sqlite> SELECT length('125');
3

7 - 3 = 4

4 + 1 = 5

sqlite> SELECT (length('/1/2/5/') - length( replace('/1/2/5/', '/','' )) +1 );
5


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] length() behaviors on Mac osx

[Michael Chen]

I just downloaded the source file and build it on my Mac osx 10.6, and it
seems problematic:
  create table tree (
 idinteger primary key,
 parent_id integer references tree,
 data  text,
 path  text-- materialized path
   );

  -- compute depth of a node
  select *, (length(path) - length( replace(path, '/','' )) +1 ) as depth
from tree;

which returns 5 for a path field "/1/2/5/", shouldn't it be 3?



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users