Re: [sqlite] Test suite

2008-01-15 Thread Joe Wilson
Grab the source tree via tar.gz file or cvs and run:

  ./configure
  make test
or
  make fulltest

To run just a single test file:

  make testfixture# if not already built by make test

  ./testfixture test/select1.test

--- Ken <[EMAIL PROTECTED]> wrote:
> Sorry if this has been asked, but I'd like to know how to run the test suite.




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Aggregates in SELECT without GROUP BY

2008-01-15 Thread Joe Wilson
--- Ken <[EMAIL PROTECTED]> wrote:

> Doing this in oracle results in an error: 
> 
> SQL> select max(addr_id), emp_id from z_address;
> select max(addr_id), emp_id from z_address
>  *
> ERROR at line 1:
> ORA-00937: not a single-group group function

As expected.

> I think an error is more appropriate when there is no group by clause. But as 
> a developer I know
> better, and write aggregated sql with a group by.
> 
> select max(addr_id), emp_id from z_address group by null;
> 
> Does not return an error nor does it return data.

The GROUP BY NULL thing is not standard, which is why I qualified it
with for sqlite. It varies from database to database.

 -- mysql, sqlite
 select max(a) from t group by null;
 select max(a) from t group by '';
 
 -- postgres
 select max(a) from t group by +0;

Oracle may or may not have an equivalent.





  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Question on SQL arbitrary arrays

2008-01-15 Thread Joe Wilson
> On 1/14/08, mark pirogovsky <[EMAIL PROTECTED]> wrote:
> > Some relational (PostgreSQL for example) databases allow you to store
> > arbitrary array as a field in on row.

I was not aware of Postgres arrays or that it is part of the 
SQL:1999 standard:

 http://www.postgresql.org/docs/8.0/interactive/arrays.html

Or, for that matter, that postgres supports Set Returning Functions
that can be queried in the FROM clause:

 http://www.postgresql.org/docs/8.0/static/functions-srf.html

 select * from generate_series(2,4);
  generate_series
 -
2
3
4

I suppose that generate_series() could be implemented as an iterator.

Useful stuff. Thanks for the tip.



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Aggregates in SELECT without GROUP BY

2008-01-14 Thread Joe Wilson
--- Darren Duncan <[EMAIL PROTECTED]> wrote:
> At 10:17 PM -0500 1/14/08, Griggs, Donald wrote:
> >Regarding: " A DBMS accepting such queries isn't just a little
> >dangerous, its flat out wrong.  I would ask what rationale there is for
> >this query not failing. -- Darren Duncan"
> >
> >I'm not asserting that you have to agree with the rationale, but did you
> >see and read the discussion that Joe Wilson pointed out to you?
> >
> >=
> >This issue is debated from time to time on the list:
> >
> >  http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html
> 
> Sorry, I missed the url on my first reading.
> 
> Also, my first comment was based on the idea that SQL usually returns 
> exactly one row on a query that uses an aggregate but no group-by, 
> and where all result field values are scalar.

In sqlite, assuming there's at least one row, an aggregate SELECT 
with no GROUP BY clause is conceptually the same as an equivalent 
SELECT with GROUP BY NULL - i.e., the group of all rows. 
(I say 'conceptually' because GROUP BY NULL is much slower).

But I agree with your point.





  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Aggregates in SELECT without GROUP BY

2008-01-14 Thread Joe Wilson
This issue is debated from time to time on the list:

 http://www.mail-archive.com/sqlite-users@sqlite.org/msg17769.html

The only other database that I'm aware of that supports selecting 
non-aggregates that are not listed in GROUP BY is MySQL:

  -- valid in sqlite and mysql, invalid in postgres
  select b from t group by a;

But your particular example is not valid in MySQL:

  mysql> SELECT MAX(a), b FROM T;
  ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no 
GROUP columns is
illegal if there is no GROUP BY clause

--- Lauri Nurmi <[EMAIL PROTECTED]> wrote:
> SQLite seems to be accepting SELECT queries that use aggregate functions 
> without a GROUP BY.  This is a little dangerous, because queries that 
> should not work at all are returning sensible-looking results.
> 
> Example:
> 
> Let's have a simple table T with the following structure and content:
> 
> CREATE TABLE T(a INTEGER, b TEXT);
> INSERT INTO "T" VALUES(1,'Dog');
> INSERT INTO "T" VALUES(2,'Cat');
> INSERT INTO "T" VALUES(3,'Mouse');
> INSERT INTO "T" VALUES(6,'Cat');
> INSERT INTO "T" VALUES(7,'Mouse');
> 
> Now, let's say we want the maximum value of "a" and the animal name 
> related to it. Easy:
> 
> sqlite> SELECT MAX(a), b FROM T;
> 7|Mouse
> 
> The result was as expected, and everyone is happy?
> 
> Let's find the minimum of "a" and the related animal name:
> 
> sqlite> SELECT MIN(a), b FROM T;
> 1|Mouse
> 
> Wait -- this is not what we expected.  But in a database with hundreds 
> or thousands of lines we might not have noticed the result is wrong. 
> Also the result of the previous MAX(a) query was "correct" only by 
> coincidence.



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Next Version of SQLite

2008-01-13 Thread Joe Wilson
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> Sorry for the confusion. 

No problem.

For what it's worth, I am also curious as to the final form of the 
VM opcode transformation. The number of opcodes generated by the various
SQL statements seems to be roughly the same as the old scheme. At this 
point without sub-expresssion elimination are you seeing any speed 
improvement?



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Next Version of SQLite

2008-01-13 Thread Joe Wilson
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> There are people on this mailing list (ex: Joe Wilson) who appear 
> to read every line of every change that we make to SQLite, within 
> minutes of making them, and complain if we so much as misspell a 
> word in a comment.  And I haven't heard a peep from Joe 

Wow - what prompted that dig against me?

Fixing a spelling mistake in a comment adds no value to the software. 
None of those spelling mistake tickets are my doing. 

The majority of the tickets I've reported were legitimate problems 
in the code - and arguing the case for some of these bugs isn't 
exactly the easiest thing. But hey, it's cool. I won't file any 
more tickets.



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] GROUP BY optimizer cost problem with index

2007-12-23 Thread Joe Wilson
>   create table stuff(a,b,c,d);
>   insert into stuff values(1,2,3,4);
>   create temp view v1 as select random()%100,
> random()%100, random()%1000, random()%1
>  from stuff x, stuff y;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   create index stuff_b on stuff(b);
>   create index stuff_c on stuff(c);
>   create index stuff_d on stuff(d);
>   analyze;

A better example of this issue would be:

  -- 47 seconds
  select c from stuff where a=23 group by c;

vs.

  -- 2 seconds
  select c from stuff where a=23 group by +c;

since there is no index on a.




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] GROUP BY optimizer cost problem with index

2007-12-23 Thread Joe Wilson
There seems to be an issue with the sqlite cost heuristic with 
an INDEX present on GROUP BY with certain types of WHERE clauses.

Given the database formed by running these statements:

  create table stuff(a,b,c,d);
  insert into stuff values(1,2,3,4);
  create temp view v1 as select random()%100,
random()%100, random()%1000, random()%1
 from stuff x, stuff y;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  create index stuff_b on stuff(b);
  create index stuff_c on stuff(c);
  create index stuff_d on stuff(d);
  analyze;

Using sqlite.org's sqlite3-3.5.4.bin, this query takes 47 seconds:

  select c from stuff where +b=23 group by c;

while this query takes just 2 seconds:
  
  select c from stuff where +b=23 group by +c;

It is more efficient in this case to do a full table scan instead 
of using the INDEX on column c.




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Unresolved symbols when I try to import SQLite int oVxWroks

2007-12-23 Thread Joe Wilson
Did you link with libc and libdl (-lc -ldl)?

Perhaps these functions are found in other libraries on that platform.

--- Gal Guttman <[EMAIL PROTECTED]> wrote:
> I'm trying to port the SQLite to VxWorks, and I'm getting the following 
> messages:
> 
> __divdi3, _rtld_dlsym, __umoddi3, __udivdi3, _rtld_dlopen, _rtld_close, 
> _rtld_dlerror,
> gettimeofday, __fixunsdfdi,__moddi3



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: WHERE clause OR to UNION optimization

2007-12-22 Thread Joe Wilson
This new attached patch corrects a slight inefficiency in my 
previous patch. It now uses UNION ALL (TK_ALL) instead of UNION
(TK_UNION) for the subselects, as was my initial intention. 
OR queries on dis-similar columns with large intermediate result
sets are now even faster.

Given the same sample database below and running this query:

 select count(*) from stuff 
 where b=1 or b=5 or c=2 or d=4 or b=9;

will yield these timings:

 sqlite 3.5.4 unpatched: CPU Time: user 3.560223 sys 0.156010
 3.5.4 + 20071221 patch: CPU Time: user 1.252079 sys 0.072004
 3.5.4 + 20071222 patch: CPU Time: user 0.776048 sys 0.116007

I also changed the OR cost estimator to reduce the cost for each 
additional OR expression pair, as it better reflects the actual
cost.

Here's the meta-diff between yesterday's patch and the new one:

-+return (costL+costR);
++/* Reduce the cost for each OR expression pair by a few percent due
++** to the fact that if the transform was performed then there would
++** be fewer expressions to evaluate in each compound subselect's
++** WHERE clause.
++*/
++return (costL+costR)*31/32;
@@ -323 +328 @@
-+  p->op = TK_UNION;
++  p->op = TK_ALL;
@@ -329 +334 @@
-+p->op = TK_UNION;
++p->op = TK_ALL;
@@ -339 +344 @@
-+p->op = TK_UNION;
++p->op = TK_ALL;


--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> The attached patch implements the WHERE clause "OR to UNION" 
> optimization as described in this post:
> 
>  http://www.mail-archive.com/sqlite-users@sqlite.org/msg09004.html
> 
> If the computed cost of the rewritten WHERE clause is lower than 
> the original query when indexes are taken into account, then it 
> will perform the optimization. If the cost is estimated to be 
> higher then the query will not be rewritten.
> 
> Given the database formed by running these statements:
> 
>   create table stuff(a,b,c,d);
>   insert into stuff values(1,2,3,4);
>   create temp view v1 as select random()%100,
> random()%100, random()%1000, random()%1
>  from stuff x, stuff y;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   insert into stuff select * from v1;
>   create index stuff_b on stuff(b);
>   create index stuff_c on stuff(c);
>   create index stuff_d on stuff(d);
>   analyze;
> 
> The patched version of sqlite 3.5.4 will run the following query 
> many times faster than an unpatched sqlite 3.5.4:
> 
>   select b, count(*) from stuff 
>   where c=2 or b=23 or c=17 or c=493 or d=7 or c=111 and a=14 
>   group by 1 order by 2 DESC, 1 limit 10;
> 
> On my machine, the patched version produces these query timings:
> 
>   CPU Time: user 0.724045 sys 0.092005
> 
> with the EXPLAIN QUERY PLAN:
> 
>   0|0|TABLE stuff USING PRIMARY KEY
>   0|0|TABLE stuff WITH INDEX stuff_c
>   0|0|TABLE stuff WITH INDEX stuff_d
>   0|0|TABLE stuff WITH INDEX stuff_b
>   0|0|TABLE stuff WITH INDEX stuff_c
> 
> For the same query the unpatched sqlite 3.5.4 produces:
> 
>   CPU Time: user 20.869304 sys 8.912557
> 
>   0|0|TABLE stuff WITH INDEX stuff_b ORDER BY
> 
> Only single table queries are supported by this OR optimization. 
> For this optimization to be considered, the WHERE clause may only 
> consist of column equality comparisons to constants, ORs and ANDs.
> 
> The optimization only looks at the top-level WHERE clause ORs. It 
> will not work with "IN" expressions. Nor will it will not expand 
> expressions like "a=1 AND (b=2 or c=3)" into "a=1 AND b=2 OR a=1 
> AND c=3" - although if manually expanded, the latter form could 
> potentially be optimized.
> 
> It passes "make test" without regressions, but more testing is needed.




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hsIndex: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.372
diff -u -3 -p -r1.372 select.c
--- src/select.c14 Dec 2007 17:24:40 -  1.372
+++ src/select.c22 Dec 2007 17:37:13 -
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements in SQLite.
 **
-** $Id: select.c,v 1.372 2007/12/14 17:24:40 drh Exp $
+** $Id: select.c,v 1.370 2007/12/13 21:54:11 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -2961,6 +2961,445 @@ static void updateAccumulator(Parse *pPa
   pAggInfo->directMode = 0;
 }
 
+#ifndef SQLITE_OMIT_OR_UNION_TRANSFORM
+
+/* The function prefix "o2u"

[sqlite] PATCH: WHERE clause OR to UNION optimization

2007-12-21 Thread Joe Wilson
The attached patch implements the WHERE clause "OR to UNION" 
optimization as described in this post:

 http://www.mail-archive.com/sqlite-users@sqlite.org/msg09004.html

If the computed cost of the rewritten WHERE clause is lower than 
the original query when indexes are taken into account, then it 
will perform the optimization. If the cost is estimated to be 
higher then the query will not be rewritten.

Given the database formed by running these statements:

  create table stuff(a,b,c,d);
  insert into stuff values(1,2,3,4);
  create temp view v1 as select random()%100,
random()%100, random()%1000, random()%1
 from stuff x, stuff y;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  insert into stuff select * from v1;
  create index stuff_b on stuff(b);
  create index stuff_c on stuff(c);
  create index stuff_d on stuff(d);
  analyze;

The patched version of sqlite 3.5.4 will run the following query 
many times faster than an unpatched sqlite 3.5.4:

  select b, count(*) from stuff 
  where c=2 or b=23 or c=17 or c=493 or d=7 or c=111 and a=14 
  group by 1 order by 2 DESC, 1 limit 10;

On my machine, the patched version produces these query timings:

  CPU Time: user 0.724045 sys 0.092005

with the EXPLAIN QUERY PLAN:

  0|0|TABLE stuff USING PRIMARY KEY
  0|0|TABLE stuff WITH INDEX stuff_c
  0|0|TABLE stuff WITH INDEX stuff_d
  0|0|TABLE stuff WITH INDEX stuff_b
  0|0|TABLE stuff WITH INDEX stuff_c

For the same query the unpatched sqlite 3.5.4 produces:

  CPU Time: user 20.869304 sys 8.912557

  0|0|TABLE stuff WITH INDEX stuff_b ORDER BY

Only single table queries are supported by this OR optimization. 
For this optimization to be considered, the WHERE clause may only 
consist of column equality comparisons to constants, ORs and ANDs.

The optimization only looks at the top-level WHERE clause ORs. It 
will not work with "IN" expressions. Nor will it will not expand 
expressions like "a=1 AND (b=2 or c=3)" into "a=1 AND b=2 OR a=1 
AND c=3" - although if manually expanded, the latter form could 
potentially be optimized.

It passes "make test" without regressions, but more testing is needed.




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hsIndex: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.372
diff -u -3 -p -r1.372 select.c
--- src/select.c14 Dec 2007 17:24:40 -  1.372
+++ src/select.c22 Dec 2007 02:39:00 -
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements in SQLite.
 **
-** $Id: select.c,v 1.372 2007/12/14 17:24:40 drh Exp $
+** $Id: select.c,v 1.370 2007/12/13 21:54:11 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -2961,6 +2961,440 @@ static void updateAccumulator(Parse *pPa
   pAggInfo->directMode = 0;
 }
 
+#ifndef SQLITE_OMIT_OR_UNION_TRANSFORM
+
+/* The function prefix "o2u" stands for "OR to UNION TRANSFORM" */
+static double o2uAndCost(Expr *p, int iTable, Bitmask *bm);
+static double o2uEqCost(Expr *p, int iTable, Bitmask *bm);
+
+/*
+** Count the number of bits in Bitmask. Each bit represents the existance
+** of a column in an expression. The zeroth bit represents the use of the
+** rowid column in the WHERE clause, which is different from non-o2u
+** uses of Bitmask in the code.
+*/
+static int o2uBitCount(Bitmask x){
+  int n = 0;
+  while( x ){
+x &= x-1;
+++n;
+  }
+  return n;
+}
+
+/*
+** Full table scan cost is simply the average number rows for each index
+** for the specified table.
+*/
+static double o2uFullTableScanCost(Table* pTab){
+  if( pTab ){
+double sum = 0;
+int n = 0;
+Index *pIndex;
+for( pIndex = pTab->pIndex; pIndex; pIndex = pIndex->pNext, n++ ){
+  if( pIndex->nColumn>0 ){
+sum += pIndex->aiRowEst[0];
+  }
+}
+if( n && sum>n ){
+  return sum/n;
+}
+  }
+  return 100;
+}
+
+/*
+** Estimate a WHERE clause column's cost taking indexes into account.
+** Record any columns encountered in Bitmask.
+*/
+static double o2uColumnCost(Expr *p, int iTable, Bitmask *bm){
+  if( p && p->op==TK_COLUMN && p->pSelect==0 && p->iTable==iTable && p->pTab ){
+int iColumn = p->iColumn;
+Index* pIndex;
+if( iColumn>=-1 && iColumn<=(int)(sizeof(Bitmask)*8-2) ){
+  *bm |= 1<<(iColumn+1); /* rowid column -1 is the 0th bit */
+}else{
+  *bm |= 0x3; /* iTable beyond range: disqualify single column OR opt */
+}
+if( iColumn==-1 ){
+  return 10;  /* Match: rowid */
+}
+for( pIndex = p->pTab->pIndex; pIndex; pIndex = pIndex->pNext ){
+  if( pIndex->nColumn>0 && pIndex->aiColumn[0]==iColumn ){
+return pIndex->aiRowEst[1];   /* 

RE: [sqlite] a few lemon questions

2007-12-20 Thread Joe Wilson
--- "Wilson, Ron" <[EMAIL PROTECTED]> wrote:
> That being said, I've run into a huge roadblock.  I'm using flex which
> produces lex.yy.c which includes  and . 

Which errno/unistd functions are actually used by the generated flex code?
getc, ungetc?

Maybe you can supply work-alike functions and those 2 header files.

> another lexer?

Follow drh's advice and write your own.
You'll have fewer dependencies/problems and more control.


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] a few lemon questions

2007-12-20 Thread Joe Wilson
--- "Wilson, Ron" <[EMAIL PROTECTED]> wrote:
> 1. My non-terminal token destructors are not getting called.  The
> terminal destructors work fine, but none of my terminal tokens need
> destruction.  Two of my non-terminals definitely require destruction.  I
> have properly defined the %destructor and instrumented the destructor
> code with std::cout << "in destructor" << std::endl;.  They are simply
> not being called.  Ever. 

The destruction of well-formed parsed tree objects is your responsibility.

See the comment below in the generated code as to the conditions when 
the destructor is called. If you use the symbol in the C code action
of the rule, the destructor will not be invoked. (It wouldn't be a useful
feature if the destructor was called unconditionally by each action!)

Looking at lemon.c for the { C code }, I think it skips C/C++ code 
comments and quoted characters and strings when searching for rule 
symbols. Clever stuff.

parse.y:

 372 %type select {Select*}
 373 %destructor select {sqlite3SelectDelete($$);}

the generated parse.c file:

/* The following function deletes the value associated with a
** symbol.  The symbol can be either a terminal or nonterminal.
** "yymajor" is the symbol code, and "yypminor" is a pointer to
** the value.
*/
static void yy_destructor(YYCODETYPE yymajor, YYMINORTYPE *yypminor){
  switch( yymajor ){
/* Here is inserted the actions which take place when a
** terminal or non-terminal is destroyed.  This can happen
** when the symbol is popped from the stack during a
** reduce or during error processing or when a parser is
** being destroyed before it is finished parsing.
**
** Note: during a reduce, the only symbols destroyed are those
** which appear on the RHS of the rule, but which are not used
** inside the C code.
*/
case 155:
case 189:
case 206:
#line 373 "parse.y"
{sqlite3SelectDelete((yypminor->yy219));}





  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] a few lemon questions

2007-12-18 Thread Joe Wilson
--- "Wilson, Ron" <[EMAIL PROTECTED]> wrote:
> I think the source of my confusion is the fact that parse.h does not
> appear in any of the sqlite source tree.  I found it now that I've run
> lemon on parse.y.  However, I still can't find definitions for COMMA,
> SEMI, etc.  They appear in the lemon rules, but there has to be
> something somewhere that says COMMA = ',' or #define COMMA ',' or ',' {
> return COMMA; }

$ grep parse.h src/*.h
src/sqliteInt.h:#include "parse.h"

$ rm -f parse.h
$ ./lemon parse.y
$ egrep 'SEMI|COMMA' parse.h
#define TK_SEMI1
#define TK_COMMA  22

>  I can't find anything that looks like lexer source for
> sqlite.  What lexer does sqlite use?  Where is the sqlite.l file?

sqlite/src/tokenize.c



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] a few lemon questions

2007-12-18 Thread Joe Wilson
--- "Wilson, Ron" <[EMAIL PROTECTED]> wrote:
> %type multiselect_op {int}
> multiselect_op(A) ::= UNION(OP). {A = @OP;}
> multiselect_op(A) ::= UNION ALL. {A = TK_ALL;}
> multiselect_op(A) ::= EXCEPT|INTERSECT(OP).  {A = @OP;}
> 
> 1. What does the '@' symbol mean?  At first glance I thought it meant,
> 'give me the literal string,' but 'A' is an integer, so that doesn't
> work.  How is {A = @OP;} different from {A = OP;}?

This rule:

  multiselect_op(A) ::= EXCEPT|INTERSECT(OP).  {A = @OP;}

is shorthand for:

  multiselect_op(A) ::= EXCEPT(OP). {A = TK_EXCEPT;}
  multiselect_op(A) ::= INTERSECT(OP).  {A = TK_INTERSECT;}

You can get away with putting EXCEPT|INTERSECT in the same rule if they 
share the same precedence.

> 2. If TK_ALL is a token, what are the other all-caps literals? 

They are also tokens. The TK_ prefixed ones appear in { C code },
whereas the non TK_-prefixed all caps tokens appear in the lemon rules.

> I assume
> they are literal text, i.e. 'UNION' is a keyword in sql.  However, SQL
> is not case sensitive, so explain how case is handled with these
> literals.  I don't think sqlite upcases all text sent to the parser, so
> there must be some rule that controls the case sensitivity.  Or maybe
> these are also tokens?

They're tokens.

You code the lexer to return UNION or ONION or whatever you want to 
call the token. It need not match the real spelling. It's just an 
implementation detail of your lexer.

> 3. Lemon prefixes all tokens with TK_ (or whatever you define
> %token_prefix).  But there are some other literals that I can find no
> definition for, e.g. SEMI, COMMA, etc. 

SEMI and COMMA are required by the grammar, but not by the rule action's
C code. 

The command:

  lemon parse.y

generates, among other things, the file "parse.h" which contains defines
for TK_SEMI and TK_COMMA which you #include in your code.

> 4. I don't see any documention on using a lexer (e.g. flex) with lemon.
> There are some helps on the internet, but the obvous ommission leads me
> to believe I don't need a lexer.  Does lemon take on some of the
> functionality of a lexer?  If I need to define tokens as regular
> expressions can I do that in lemon or do I need a lexer?

You have to supply your own lexer function.
There's an example of flex being used with lemon.
Search the web for "lemon tutorial". 




  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SQLite and Columnar Databases

2007-12-15 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> The problem is when inserting into large database that is
> indexed, the values being indexed are randomly distributed.
> So with each insert, SQLite has to seek to a new random
> place in the file to insert the new index entry there.
> It does not matter that pages of the index are not in
> consecutive order.

For bulk INSERTs I see your point.

But for SELECT performance on the resulting database in a cold OS file 
cache situation (assuming you do not have the luxury of running VACUUM 
beforehand) or when the database file is many times the size of RAM, it 
does improve SELECT performance if the pages of each index are contiguous.
It would be nice if contiguous index pages could be a fortunate "by-product" 
of the bulk INSERT optimization algorithm for this reason.

>  What matters is that each insertion
> is into a different place and that the places are randomly
> distributed over a large file - larger than will fit in cache.
> In this situation, each new entry probably needs to go on 
> a page that is not in cache, and hence a real disk
> seek and read is required (as opposed to simply reading
> the value out of cache) when inserting each new entry.  Disk
> seeks and reads are much, much slower than disk cache hits,
> which really slows down insertion performance.
> 
> If you do many inserts such that the indexed values are
> in sorted order, then most inserts will go on the same page
> as the previous.  The previous page is already in cache
> so it doesn't need to be read from disk.  It has also
> already been journaled, so no excess writing is required.
> Disk I/O is greatly reduced. Things go much, much faster.
> The problem is that you really have the luxury of being
> able to insert entries in sorted order.  And if you are
> indexing multiple columns, it is impossible to sort
> the entries on both columns at once.
> 
> The usual way to work around this problem is to only do random
> inserts into indices which are small enough to fit into
> your disk cache.  Suppose you start inserting into index A.
> Once A gets too big to fit entirely in cache, stop inserting
> into it and start inserting into B.  Once B gets to be the
> cache size, merge A and B together into a new index C.
> The merge operation requires reading A and B straight through
> from beginning to end once.  This is a lot of disk I/O but
> it is still much faster than jumping around within the file
> reading bits here and there.  After creating C, reset A and
> B back to being empty (since all records have been transfered
> into C).  Start inserting into A again until it fills up.
> Then fill up B again.  Merge A and B into D, then merge C and D
> into E.  Reset A, B, C, and D.  Keep doing this, merging
> smaller indices into larger indices, until you insert all
> the records you need to insert.  Then make a single pass
> through all of the smaller indices and merge them all
> together into a single big index Z.  Z becomes the new
> index used for search operations on the database.
> 
> The algorithm above should probably go into SQLite 
> to construct an index as part of the CREATE INDEX
> statement.  When populating a database will a large
> amount of data, first put all of the data into an
> unindexed table.  This will be very fast because each
> new entry goes at the end of the table (and also at the
> end of the file.)  After all data is in place, issue
> the CREATE INDEX statements to index any fields you
> think need indexing.  The CREATE INDEX statement has
> to populate the index.  The current algorithm is to
> scan through the original table and create and insert
> index entries one by one as they are encountered.  I am
> proposing that you substitute the algorithm outlined in
> the previous paragraph in place of the current algorithm.

Are you assuming that you do not have any indexes already in place on 
the table being populated?

It would be nice if the optimization could work in the general case
where the table already has data and indexes (implicit or explicit) 
as in:

  create foo (
a UNIQUE,
b UNIQUE,
c,
d,
primary key(d, b, c)
  );

Or are you suggesting that you first determine whether you're running 
INSERTs within a larger transaction and perform the new table/index population 
algorithm for each INSERT and only combine the various indexes at the
end of the transaction or whenever a page threshold is exceeded?

Guessing the end of INSERTs into the same table could be tricky within a 
larger transaction. End might be defined as when you're running an SQL 
command other than an insert into the same table, as in:

  BEGIN;
  INSERT INTO foo VALUES ...;
-- detect that we've started inserting into table foo
-- and begin the bulk insert optimization
  INSERT INTO foo VALUES ...;
  ...a million other inserts into foo...
  INSERT INTO foo VALUES ...;
  INSERT INTO foo VALUES ...;
  INSERT INTO foo VALUES ...;
-- detect that we've stopped inserting into foo 

Re: [sqlite] SQLite and Columnar Databases

2007-12-15 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > The reason why I asked is that I haven't had much luck with sqlite3 
> > performance for databases larger than the size of RAM on my machine
> > regardless of PRAGMA settings.
> 
> This is probably do to the cache locality problem.  We know how
> to fix this, Joe.  Would you like to have a go at it?

I think setting aside contiguous pages in the file for exclusive use 
by each btree would help improve locality of reference on disk.

For example, let A, B and C represent in-use pages of 3 btrees and 
a, b and c represent free pages corresponding to the same btrees:

 AAaAAaAaaaBBbbBBBbbbCCcCCC

Is this what you had in mind in your post?

 http://www.mail-archive.com/sqlite-users@sqlite.org/msg27851.html

If not, could you elaborate?




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Joe Wilson
The reason why I asked is that I haven't had much luck with sqlite3 
performance for databases larger than the size of RAM on my machine
regardless of PRAGMA settings.

Maybe querying speed is fine for multi gigabyte database files, but 
INSERT speed into tables with multiple indexes is slow when you exceed 
the RAM threshold, so I did not pursue it.

I guess the answer is to get more RAM - it's cheap.
...and use a 64 bit OS.

High speed non-volatile RAM drives will be commonplace soon enough 
and alleviate this problem.

--- Tom Briggs <[EMAIL PROTECTED]> wrote:
>Heh, no, I've never tried, but I don't see much reason why I
> couldn't.  I was just trying to make the point that labeling SQLite as
> "good ... for smaller databases" was not a slight. 
> 
>-T
> 
> > -Original Message-
> > From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, December 13, 2007 11:51 AM
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] SQLite and Columnar Databases
> > 
> > --- Tom Briggs <[EMAIL PROTECTED]> wrote:
> > >For clarity, my definition of small is about 200GB, so I'm not
> > > selling SQLite short here... 
> > 
> > Are you able to get decent performance out of sqlite3 for a 
> > 200GB database?
> > 
> > How much RAM do you have on such a machine?




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] SQLite and Columnar Databases

2007-12-13 Thread Joe Wilson
--- Tom Briggs <[EMAIL PROTECTED]> wrote:
>For clarity, my definition of small is about 200GB, so I'm not
> selling SQLite short here... 

Are you able to get decent performance out of sqlite3 for a 200GB database?

How much RAM do you have on such a machine?


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] DeviceSQL

2007-12-12 Thread Joe Wilson
Be careful about speculative comments.

For all anyone knows, said product could use SQLite internally with 
a couple of proprietary optimizations here and there that may make it
faster in specific cases. 

The sqlite public domain license would allow that sort of thing.


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-12 Thread Joe Wilson
It appears that Postgres, DB2 and SQL Server CE have issues with 
certain types of alias expresssions in GROUP BY, while MySQL does 
not.

Postgres will allow column aliases in GROUP BY - even aliases overriding
table column names - as long as every table column component of that 
alias' expression is also explicitly individually specified in the 
GROUP BY clause (of course this would change the result of the query). 
DB2 and SQL Server CE had similar GROUP BY errors, so I'm speculating 
that they may the same GROUP BY resolution behavior as postgres.

I guess the superset behavior that would allow the running of queries
from any of the databases mentioned would be to allow aliases in 
GROUP BY, with aliases having precedence over table column names.

 create table t1(a INT, b INT, c INT);
 insert into t1 values(1, 2, 4);
 insert into t1 values(2, -1000, 5);

postgres=> select a, a+b AS c from t1 group by c, a order by c, a;
ERROR:  column "t1.b" must appear in the GROUP BY clause or be used in an
aggregate function

"b" added to SELECT list and GROUP BY (changing the meaning of the query):

postgres=> select a, a+b AS c, b from t1 group by c, a, b order by c, a, b;
 a |  c   |   b
---+--+---
 2 | -998 | -1000
 1 |3 | 2

Verify that an unambiguous alias "g" returns the same results:

postgres=> select a, a+b AS g, b from t1 group by g, a, b order by g, a, b;
 a |  g   |   b
---+--+---
 2 | -998 | -1000
 1 |3 | 2

Remove mention of column b from the SELECT list:

postgres=> select a, a+b AS c from t1 group by c, a, b order by c, a, b;
 a |  c
---+--
 2 | -998
 1 |3

Do not overload c, use unambiguous alias g:

postgres=> select a, a+b AS g from t1 group by g, a, b order by g, a, b;
 a |  g
---+--
 2 | -998
 1 |3

> mysql>   select a, a+b AS c from t1 group by c, a order by c, a;
> +--+--+
> | a| c|
> +--+--+
> |2 | -998 |
> |1 |3 |
> +--+--+

DB2:
> select a, a+b AS c from t1 group by c, a order by c, a
> SQL0119N  An expression starting with "B" specified in a SELECT clause, HAVING
> clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in
> a SELECT clause, HAVING clause, or ORDER BY clause with a column function and
> no GROUP BY clause is specified.  SQLSTATE=42803

SQL Server CE:
> select a, a+b AS c from t1 group by c, a order by c, a;
> SQL Server Compact Edition Version 3.5 returns
> Unhandled Exception: In aggregate and grouping expressions, the SELECT
> clause can contain only aggregates and grouping expressions. [ Select
> clause = ,b ]



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> I think those exceedingly rare programs that need a larger
> SQL statement length limit can include their own copy of
> sqlite3.c.  I does not take up that much space, after all.

It's easy enough to recompile with the new setting once you're 
aware of it. But shouldn't the default limits be unlimited as 
they were before 3.4.x?  

The new limit behavior broke backwards compatibility and could 
surprise a few people, as in the link I cited.



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Yes, this does create problems for .dump/.load in the shell.
> But, as has been pointed out, you can work around it using
> a compile-time switch:
> 
> gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o sqlite3
> 
> I should probably modify the makefile to do this automatically...

It would be useful if this value could be set at runtime via PRAGMA or 
a function like sqlite3_set_max_sql_length().

Why? An operating system (say, Mac OSX) may have only one shared 
libsqlite3.so and many programs using that shared library. It would be 
beneficial if they could all use the same shared library even though they 
require a different maximum SQL length.

Perhaps other defaults could be similarly set at runtime as well.


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> The current name resolution rules for SQLite are that it
> first tries to resolve names using just the tables in the
> join.  If that fails, then it looks at result column aliases.
> I think that approach continues to work on WHERE.  But I need
> to reverse the search order on ORDER BY - the column aliases
> need to take precedence over tables in the join.

Should GROUP BY follow the WHERE resolution rules or the proposed
new ORDER BY rules?

Given:

  create table t1(a INT, b INT, c INT);
  insert into t1 values(1, 2, 4);
  insert into t1 values(2, -1000, 5);

mysql>   select a, a+b AS c from t1 group by c, a order by c, a;
+--+--+
| a| c|
+--+--+
|2 | -998 |
|1 |3 |
+--+--+

postgres=> select a, a+b AS c from t1 group by c, a order by c, a;
ERROR:  column "t1.b" must appear in the GROUP BY clause or be used in an 
aggregate function





  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] .dump/.load not workin in 3.4.0 or later for "large" rows?

2007-12-11 Thread Joe Wilson

--- Jim Correia <[EMAIL PROTECTED]> wrote:
> I notice that SQLite 3.4.0 and later impose hard limits on some  
> sizes. I'm running into a problem where a .dump/.load cycle fails on  
> a database with columns that have blobs which are about 2MB in size.
> 
> Looking at the source for 3.5.3 (I can't find a tarball of 3.4 on the  
> web site, but I'm using 3.4 since that is what ships on Mac OS X 10.5)
> 
> I see:
> 
>   /*
>   ** The maximum length of a TEXT or BLOB in bytes.   This also
>   ** limits the size of a row in a table or index.
>   **
>   ** The hard limit is the ability of a 32-bit signed integer
>   ** to count the size: 2^31-1 or 2147483647.
>   */
>   #ifndef SQLITE_MAX_LENGTH
>   # define SQLITE_MAX_LENGTH 10
>   #endif
> 
> and more importantly:
> 
>   /*
>   ** The maximum length of a single SQL statement in bytes.
>   ** The hard limit here is the same as SQLITE_MAX_LENGTH.
>   */
>   #ifndef SQLITE_MAX_SQL_LENGTH
>   # define SQLITE_MAX_SQL_LENGTH 100
>   #endif
> 
> Is the comment wrong, or the source? The value is not the same as  
> SQLITE_MAX_LENGTH; it is in fact much smaller.
> 
> If this is intentional, what is the recommended replacement  
> for .dump/.load for large rows?

You have to recompile with a large value for SQLITE_MAX_SQL_LENGTH
via a compiler -D flag or other means.

Monotone encountered this issue as well for dumping/restoring databases 
with large BLOBs:

 http://lists.gnu.org/archive/html/monotone-devel/2007-09/msg00246.html

I think the default value is too small, but as long as you're able to
compile/use your own library, it's not too much trouble. 





  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-11 Thread Joe Wilson
--- Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote:
> 
> > IBM DB2 9.5
> >
> > select a AS "foo" from t1 union select b from t1 order by foo
> > SQL0206N  "FOO" is not valid in the context where it is used.  
> > SQLSTATE=42703
> 
> The problem here is with the inconsistent quoting. PostgreSQL uses the
> opposite case folding as everyone else, hence the behavior difference.

Could someone please post the results of these queries on Oracle, 
DB2 or SQL Server? If they don't work as is, could you show the syntax
(double quoted aliases or otherwise) that would make these queries work 
on these particular databases?

  create table t1(a INT, b INT, c INT);
  insert into t1 values(1, 2, 4);
  insert into t1 values(2, -1000, 5);

  -- See if select alias or table column has precedence in ORDER BY
  select a, a+b AS c from t1 order by c;

  -- See if aliases accepted in UNION/ORDER BY combination
  select a AS foo from t1 union select b from t1 order by foo;

  -- See if ambiguous column aliases, UNIONs and ORDER BY work together
  select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c;


MySQL 5.0.45:

mysql>   select a, a+b AS c from t1 order by c;
+--+--+
| a| c|
+--+--+
|2 | -998 |
|1 |3 |
+--+--+

mysql>   select a AS foo from t1 union select b from t1 order by foo;
+---+
| foo   |
+---+
| -1000 |
| 1 |
| 2 |
+---+

mysql>   select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 
order by c;
+--+--+
| a| c|
+--+--+
|2 | -998 |
|1 |3 |
|1 |4 |
|2 |5 |
+--+--+


PostgreSQL 8.1.5:

postgres=>   select a, a+b AS c from t1 order by c;
 a |  c
---+--
 2 | -998
 1 |3

postgres=>   select a AS foo from t1 union select b from t1 order by foo;
  foo
---
 -1000
 1
 2

postgres=>   select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 
order by c;
 a |  c
---+--
 2 | -998
 1 |3
 1 |4
 2 |5




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-10 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Dennis Cote <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > >
> > > Can you please tell me what
> > > other databases do with this:
> > >
> > >CREATE TABLE t1(a,b,c);
> > >INSERT INTO t1 VALUES(1,2,4);
> > >SELECT a+b AS c FROM t1 WHERE c==4;
> > >
> > > In the WHERE clause, should the "c" resolve to
> > > the column "c" or to the "a+b" expression?  I'm
> > > guessing the "a+b" expression.  But SQLite is
> > > currently resolving the name to the column "c"
> > > in table t1.  Thus SQLite currently answers
> > > "3" to the SELECT statement, when I think it
> > > should give an empty set.  Or maybe it should give
> > > an error?
> > >
> > > Opinions, anyone?
> > 
> > According to the where clause definition in the SQL:1999 standard the 
> > "c" in the where clause should refer to the column in table "t1" which 
> > is the result of the preceding from clause. To conform to the standard 
> > SQLite should return 3.
> 
> I never would have guessed things worked that way.  But then
> again, SQL is not noted for making a whole lot of sense.

I figure if you get agreement between many different databases, they 
probably follow the standard. Or is it vice versa?

It doesn't appear to be possible to use column aliases in the WHERE 
clause of postgres and MySQL. So they seem to have interpreted the 
standard in the same way. I thought sqlite's useful WHERE clause column 
alias extension was common. Perhaps not.

postgres=> select a AS foo from t1 where foo=1;
ERROR:  column "foo" does not exist
postgres=> select a AS foo from t1 where "foo"=1;
ERROR:  column "foo" does not exist
postgres=> select a AS "foo" from t1 where "foo"=1;
ERROR:  column "foo" does not exist

mysql> select a AS foo from t9 where foo=1;
ERROR 1054 (42S22): Unknown column 'foo' in 'where clause'
mysql> select a AS foo from t9 where "foo"=1;
Empty set, 1 warning (0.00 sec)

MySQL seems to interpret the WHERE clause "foo" as the string literal 'foo'.

But ORDER BY is a different story:

mysql> select a AS "foo" from t1 union select b from t1 order by foo;
+--+
| foo  |
+--+
|1 |
|2 |
+--+

postgres=> select a AS "foo" from t1 union select b from t1 order by foo;
 foo
-
   1
   2

postgres=> select a+b AS "c" from t1 union select b from t1 order by c;
 c
---
 2
 3

Let's add another row to table t1...

postgres=> insert into t1 values(2, -1000, 5);
INSERT 0 1
test=> select * from t1;
 a |   b   | c
---+---+---
 1 | 2 | 4
 2 | -1000 | 5

postgres=> select a, a+b AS "c" from t1 order by c;
 a |  c
---+--
 2 | -998
 1 |3


mysql> select * from t1;
+--+---+--+
| a| b | c|
+--+---+--+
|1 | 2 |4 |
|2 | -1000 |5 |
+--+---+--+

mysql> select a, a+b AS "c" from t1 order by c;
+--+--+
| a| c|
+--+--+
|2 | -998 |
|1 |3 |
+--+--+

which differs from:

SQLite version 3.5.1
sqlite> select * from t1;
a   b   c
--  --  --
1   2   4
2   -1000   5

sqlite> select a, a+b AS "c" from t1 order by c;
a   c
--  --
1   3
2   -998

Which database is correct?



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-08 Thread Joe Wilson
> >CREATE TABLE t1(a,b,c);
> >INSERT INTO t1 VALUES(1,2,4);
> >SELECT a+b AS c FROM t1 WHERE c=4;
> 
> SQL Server returns a 3.
> ORACLE 9 returns a 3.

PostgreSQL 8.1.5 also returns a 3



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-08 Thread Joe Wilson
--- Robert Wishlaw <[EMAIL PROTECTED]> wrote:
> > >CREATE TABLE t1(a,b,c);

> IBM DB2 9.5
> 
> INSERT INTO t1 VALUES(1,2,4);
> SELECT a+b AS c FROM t1 WHERE c=4;
> 
> returns
> 
> C
> 
>3
>3
>3
>3
>4 record(s) selected

How could more than one row be returned if t1 only has one row?




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-08 Thread Joe Wilson
>CREATE TABLE t1(a,b,c);
>INSERT INTO t1 VALUES(1,2,4);
>SELECT a+b AS c FROM t1 WHERE c==4;

MySQL 5.0.45:

create table t1(a INT, b INT, c INT);
INSERT INTO t1 VALUES(1,2,4);
SELECT a+b AS c FROM t1 WHERE c=4;
+--+
| c|
+--+
|3 |
+--+

> In the WHERE clause, should the "c" resolve to
> the column "c" or to the "a+b" expression?  I'm
> guessing the "a+b" expression.  But SQLite is
> currently resolving the name to the column "c"
> in table t1.  Thus SQLite currently answers
> "3" to the SELECT statement, when I think it
> should give an empty set.  Or maybe it should give
> an error?
> 
> Opinions, anyone?

That's a good puzzle.

One might argue that the explicit alias has priority over the 
column that was not mentioned in the SELECT expression list.
But who knows.



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-08 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > 
> > So few lines of code are changed by the patch that one could easily 
> > add a new PRAGMA to have the old compound SELECT behavior to be the 
> > default. If a database is explicitly created or altered with the new 
> > PRAGMA setting, it could have the new behavior, i.e.:
> 
> My goal is to keep SQLite simple.  It is already way more
> complex than it needs to be.  Adding new pragma settings is
> going in the wrong direction.

I agree that PRAGMAs may not be the best way to go, it was just a 
compromise proposal.

I believe that incompatible changes are warranted when the end 
result is more understandable and follows common practise.
There have been many changes of the course of development of SQLite
that have broken backwards compatibilty in order to accept common
database practise. 

For example, the name resolution and result-set heading of compound 
statements was changed from right-to-left to left-to-right when it 
was demonstrated that most databases used that methodology. No doubt 
some users' queries were affected by an upgrade.

The SQL92 standard is so ambiguous that a strictly compliant database 
could actually perform no useful work at all. The standard itself just
serves as a guideline.

> Joe, I appreciate your desire to help.  But adding complexity
> is not helping.  If you want to be helpful without adding
> complexity, consider writing up a paragraph or two about
> how the current ORDER BY matching works and how it ought
> to work.  Be precise, unambiguous, and consise. If you can 
> write formal requirements instead of prose, that would be 
> even better.

I can appreciate that, but it's just a hobby for me. My interest is 
to make sqlite more compatible with other databases or to improve 
its speed.

It's often easier and less ambiguous to express an idea in code than 
to describe it in words. A few of my patches and findings (DISTINCT, 
OP_Real) have formed the basis of new SQLite features and optimizations.  
If someone finds the patches useful, great.  If not, that's also fine.




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-08 Thread Joe Wilson
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> On Dec 7, 2007, at 11:47 PM, Joe Wilson wrote:
> >
> > I believe it makes compound query behavior more compatible with other
> > popular databases. It is mostly backwards compatible with the previous
> > syntax and only 2 tests performed by "make test" had to be altered.
> 
> Compatibility with other databases is good.  But backwards compatibility
> with the thousands and thousands of existing SQLite applications is more
> important.  The current behavior of SQLite regarding the column names
> in compound SELECTs may be boneheaded and broken.  But it does at
> least have the virtue of being backwards compatible with all prior  
> releases
> of SQLite.  So a project can update the version of SQLite they are using
> to the latest code out of CVS and be reasonable confident that it  
> will still
> work.
> 
> If I put in this patch, that confidence is diminished somewhat.
> 
> Hence I am moving very slowly and cautiously here.

So few lines of code are changed by the patch that one could easily 
add a new PRAGMA to have the old compound SELECT behavior to be the 
default. If a database is explicitly created or altered with the new 
PRAGMA setting, it could have the new behavior, i.e.:

  PRAGMA sqlite_dialect = 2;

But if it is decided that a PRAGMA is the way to go to maintain backwards
compatibility, I think further changes to the compound column name 
resolution should go in. Such as only resolving ORDER BY columns against 
the leftmost SELECT, which would pave the way for complete expression 
support in compound SELECT ORDER BY. Other changes to the SQL syntax that 
you might have been saving could be bundled up with such a PRAGMA as well.




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] PATCH: compound query column naming and resolving (Ticket #2822)

2007-12-07 Thread Joe Wilson
The attached sqlite 3.5.3 patch addresses several different compound 
query column naming and resolving issues in ORDER BY and the SELECT 
expression list mentioned in this ticket:

  http://www.sqlite.org/cvstrac/tktview?tn=2822

(The exception being it does not support expressions in the ORDER BY 
clause of compound SELECT statements. That functionality remains the
same as in version 3.5.3.)

I believe it makes compound query behavior more compatible with other 
popular databases. It is mostly backwards compatible with the previous 
syntax and only 2 tests performed by "make test" had to be altered.

It seems to work, although it's quite possible that I missed something.
At least this patch serves as a basis of syntax discussion.

If you want to test it, just put sqlite-3.5.3.tar.gz and the patch
file in the same directory and run these commands:

  tar xzvf sqlite-3.5.3.tar.gz
  cd sqlite-3.5.3
  patch -p0 < ../union-alias-20071207.patch.txt
  ./configure
  make
  ./sqlite3

Please report any issues to the mailing list.




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 
Index: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.363
diff -u -3 -p -r1.363 select.c
--- src/select.c23 Nov 2007 13:42:52 -  1.363
+++ src/select.c8 Dec 2007 03:49:30 -
@@ -1118,8 +1118,19 @@ Table *sqlite3ResultSetOfSelect(Parse *p
   /* For columns of the from A.B use B as the name */
   zName = sqlite3MPrintf(db, "%T", >token);
 }else if( p->span.z && p->span.z[0] ){
-  /* Use the original text of the column expression as its name */
-  zName = sqlite3MPrintf(db, "%T", >span);
+  Token t = p->span;
+  if( p->op==TK_COLUMN ){
+/* Get rid of all dotted prefixes, if any */
+int n;
+for(n = t.n-1; n>=0; n--){
+  if( t.z[n]=='.' ){
+t.n -= n+1;
+t.z += n+1;
+break;
+  }
+}
+  }
+  zName = sqlite3MPrintf(db, "%T", );
 }else{
   /* If all else fails, make up a name */
   zName = sqlite3MPrintf(db, "column%d", i+1);
@@ -1469,12 +1480,23 @@ static int matchOrderbyToColumn(
   if( !mustComplete ) continue;
   iCol--;
 }
-if( iCol<0 && (zLabel = sqlite3NameFromToken(db, >token))!=0 ){
+if( iCol<0 && (
+(zLabel = sqlite3NameFromToken(db, >token))!=0
+|| ((pE->op==TK_STRING || pE->op==TK_DOT || pE->op==TK_ID)
+   && (zLabel = sqlite3NameFromToken(db, >span))!=0) )){
   for(j=0, pItem=pEList->a; jnExpr; j++, pItem++){
 char *zName;
 int isMatch;
 if( pItem->zName ){
   zName = sqlite3DbStrDup(db, pItem->zName);
+}else if( pItem->pExpr->op==TK_DOT && pE->op==TK_ID ){
+  Expr *pRight = pItem->pExpr->pRight;
+  if( pRight->op==TK_DOT ){
+pRight = pRight->pRight;
+  }
+  zName = sqlite3NameFromToken(db, >token);
+}else if( pItem->pExpr->op==TK_DOT && pE->op==TK_DOT ){
+  zName = sqlite3NameFromToken(db, >pExpr->span);
 }else{
   zName = sqlite3NameFromToken(db, >pExpr->token);
 }
@@ -1829,7 +1851,7 @@ static int multiSelect(
 if( eDest==SRT_Callback ){
   Select *pFirst = p;
   while( pFirst->pPrior ) pFirst = pFirst->pPrior;
-  generateColumnNames(pParse, 0, pFirst->pEList);
+  generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList);
 }
 iBreak = sqlite3VdbeMakeLabel(v);
 iCont = sqlite3VdbeMakeLabel(v);
@@ -1907,7 +1929,7 @@ static int multiSelect(
   if( eDest==SRT_Callback ){
 Select *pFirst = p;
 while( pFirst->pPrior ) pFirst = pFirst->pPrior;
-generateColumnNames(pParse, 0, pFirst->pEList);
+generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList);
   }
   iBreak = sqlite3VdbeMakeLabel(v);
   iCont = sqlite3VdbeMakeLabel(v);
Index: test/select1.test
===
RCS file: /sqlite/sqlite/test/select1.test,v
retrieving revision 1.54
diff -u -3 -p -r1.54 select1.test
--- test/select1.test   23 Jul 2007 22:51:15 -  1.54
+++ test/select1.test   8 Dec 2007 03:49:31 -
@@ -559,6 +559,66 @@ do_test select1-6.23 {
}
 } {b d}
 
+# Ticket #2822
+do_test select1-6.30 {
+   execsql {
+ CREATE TABLE x1(a, b, c);
+ INSERT INTO x1 VALUES(6, 4, 2);
+ CREATE TABLE x2(a, b, c);
+ INSERT INTO x2 VALUES(7, 1, 3);
+ CREATE VIEW v1 AS
+   SELECT x1.b, x1.a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b, a;
+ CREATE VIEW v2 AS
+   SELECT b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b, a;
+ SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 

Re: [sqlite] unable to open a temporary database file for storing temporary tables

2007-12-05 Thread Joe Wilson
Regarding the DOS-like name, it may be the canonical name on Windows 
for FAT/VFAT file systems. Is that what you're using?
I wonder if there's a hash collision in trying to get the file name
down to DOS 8.3 file name format. 

Do you have many sqlite_* files in that directory?

Since the file prefix is sqlite_ instead of etilqs_, you must be using
an older sqlite release. A newer version may have solved the problem.

Some guesses:

Do you have sufficient space and/or privileges in that directory?

Is some anti-virus program running doing some crazy stuff like
locking directories or temporarily filling up Temp?

Is there a limit on the number of files in a directory on Windows?

--- Charlie Bursell <[EMAIL PROTECTED]> wrote:
> I have seen the error "malformed database schema - unable to open a
> temporary database file for storing temporary tables" discussed in
> another thread relative to PHP and BSD Unix.  However, I am having this
> problem on Windows 2003 using the Tcl API.
> 
> If I run the .databases command, I see something like:
> 
> C:\DOCUME~1\charlie\LOCALS~1\Temp\sqlite_PfaaLBX02TU6g9w
> 
> Is the DOS like path statement a problem?
> 
> The application runs fine for a few days but then gets the above error.
> Shutting everything down and restarting seems to clear it for a whle.
> 
> Would periodically clearing the cache help?




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

2007-12-05 Thread Joe Wilson
> Not in principle. But I think changes that break backwards
> compatibility would be more trouble than they're worth for
> something like this. In the absence of clearer guidance
> from sql-92, it's probably more important to be compatible
> with earlier sqlite versions than with mysql and friends.
> 
> Maybe it would be better to document the current behaviour
> and move on.
> 
> Dan.

It would be tricky to document the current behavior accurately.
Compound queries with ORDER BY without aliases have never really worked 
in a uniform way in sqlite. I don't think fixing the issue will trouble 
people, as most must use column aliases and subqueries as a workaround 
for these problems anyway.

I think at the very least, the inconsistency of the column names in
the result set should be resolved:

SQLite version 3.5.3
Enter ".help" for instructions
sqlite> create table foo(a);
sqlite> insert into foo values(1);
sqlite> .header on

sqlite> select foo.a from foo;
a
1

sqlite> select foo.a from foo union all select foo.a from foo;
a
1
1

sqlite> select foo.a from foo union all select foo.a from foo order by 1;
foo.a
1
1

sqlite> select foo.a from foo union all select foo.a from foo group by 1;
a
1
1

Notice the column headings.
Why does the column name change in the result set because of the 
addition of an ORDER BY but not with a GROUP BY clause or with a 
regular non-compound query?

And should regular queries support expressions in ORDER BY, while
compound statements not?

sqlite> select a from foo order by a*a-3*a;
1

sqlite> select a from foo union all select a+5 as a from foo order by a*a-3*a;
SQL error: ORDER BY term number 1 does not match any result column

The only way to get this query to work is to use this workaround:

sqlite> select * from (select a from foo union all select a+5 from foo) order 
by a*a-3*a;
1
6

Other databases allow expressions in compound SELECT/ORDER BY without the
subquery:

mysql> select a from foo union all select a+5 as a from foo order by a*a-3*a;
+--+
| a|
+--+
|1 |
|6 |
+--+

I only have MySQL to test with here. I'm fairly certain it works on 
most other open source and popular commercial databases.



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

2007-12-04 Thread Joe Wilson
--- Dan <[EMAIL PROTECTED]> wrote:

> The "b" in the ORDER BY does not match "x1.b" because it is
> not a simple identifier (according to matchOrderbyToColumn()).
> It does not match either "" or " as ".
> 
> After failing to find a match for "b" in the leftmost SELECT,
> SQLite searches the next leftmost and matches "b" to "b"
> (column 2).
> 
> That's how it is at the moment, anyhow.
> 
> >
> >   http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html
> >
> 
> Cheers. I'm starting to realise why this little corner of sqlite
> is the way it is...

I believe that there are 2 different issues with the current implementation:

1. The result set column names of a compound SELECT should drop all 
   table qualifiers, as they've lost all meaning once in a UNION.

   i.e., instead of:

 sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2;
 x1.b|a
 value|value

   you should see:

 b|a
 value|value

   as other databases do:

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY b;
 +--+--+
 | b| a|
 +--+--+
 |2 |1 |
 |9 |0 |
 +--+--+

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY x1.b;
 ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause'

2. The compound SELECT's ORDER BY statement elements should only be matched
   against the leftmost SELECT. If there is no match in the leftmost
   SELECT, then an error should result - even if a match could potentially
   be found in non-leftmost SELECTs.

Or do you disagree?




  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SQLite does not support multi-row inserts?

2007-12-04 Thread Joe Wilson
--- Sander Marechal <[EMAIL PROTECTED]> wrote:
> Joe Wilson wrote:
> > --- Sander Marechal <[EMAIL PROTECTED]> wrote:
> >> I ran into a problem when using SQLite from PHP. It appears that SQLite3 
> >> does not support multi-row inserts in the form:
> >>
> >> INSERT INTO (col1, col2) VALUES (1, 2), (3, 4)
> >> 
> >> Will if be implemented in the future?
> > 
> > I doubt it.
> 
> Too bad. I think it's in SQL92 but I'm not 100% sure (The SQL92 specs 
> are a bit hard to read).

I can't confirm whether it is, but Wikipedia claims it is:

http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

Multirow inserts

An SQL feature (since SQL-92) is the use of row value constructors to insert 
multiple rows at a
time in a single SQL statement:

 INSERT INTO table (column1, [column2, ... ]) VALUES (value1a, [value1b, ...]), 
(value2a,
[value2b, ...]), ...

This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and H2.



  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

2007-12-04 Thread Joe Wilson
--- Dan <[EMAIL PROTECTED]> wrote:
> i.e., if we have:
> 
>CREATE TABLE x1(a, b, c);
>CREATE TABLE x2(a, b, c);
> 
> then the following pairs of statements are equivalent:
...
> 
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;

Don't you mean ORDER BY 1?

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; <--

I thought *only* the leftmost SELECT in the compound chain governs the
selection of the column names used by the ORDER BY.  The names of the 
subsequent compound SELECTs should be ignored. At least that's how it 
works on MySQL and other databases I've used:

given:

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table x2(a INT, b INT, c INT);
  insert into x2 values(9, 0, 4);  

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
+--+--+
| b| a|
+--+--+
|2 |1 |
|9 |0 |
+--+--+

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;
+--+--+
| b| a|
+--+--+
|9 |0 |
|2 |1 |
+--+--+

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;
+--+--+
| b| a|
+--+--+
|2 |1 |
|9 |0 |
+--+--+

Oracle has the same behavior as MySQL, as I recall.

sqlite 3.5 produces a different result since it appears to be
getting the column name from the rightmost compound select:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  x1.b|a
  9|0
  2|1

Compare MySQL:

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table g2(x INT, y INT, z INT);
  insert into g2 values(9, 0, 4);

  mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  ERROR 1054 (42S22): Unknown column 'y' in 'order clause'

to sqlite:

  sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  9|0
  2|1

> To my mind, the logical change to make would be to allow this:
> 
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b";
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b];
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;

This query is (also) unambiguous given the logic outlined above:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;

I thought all of this was already hashed in this thread:

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

2007-12-03 Thread Joe Wilson
--- Marco Bambini <[EMAIL PROTECTED]> wrote:
> Starting from version 3.4.2 I receive errors with queries like:
> 
> SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field
> or even
> SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field
> 
> error is:
> ORDER BY term number 1 does not match any result column
> 
> Tables are created by:
> CREATE TABLE a (field);
> CREATE TABLE b (field);
> 
> Please note that the above queries worked fine with sqlite 3.2.x or  
> 3.3.x.
> Any idea?

You probably know the workarounds:

  SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1;

or

  SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER BY x;

but it's odd that this one doesn't work as well:

  create table t1(a);
  create table t2(b);

  select t1.a from t1 union all select t2.b from t2 order by a;

  SQL error: ORDER BY term number 1 does not match any result column




  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SQLite does not support multi-row inserts?

2007-12-03 Thread Joe Wilson
--- Sander Marechal <[EMAIL PROTECTED]> wrote:
> I ran into a problem when using SQLite from PHP. It appears that SQLite3 
> does not support multi-row inserts in the form:
> 
> INSERT INTO (col1, col2) VALUES (1, 2), (3, 4)
> 
> Is that correct? 

That's correct.

> Will if be implemented in the future?

I doubt it. However, you might try:

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg28337.html

Or you could do the equivalent:

  INSERT INTO t1(col1, col2) SELECT 1, 2 UNION ALL SELECT 3, 4;



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] compiling for mingw

2007-12-02 Thread Joe Wilson
--- "A.J.Millan" <[EMAIL PROTECTED]> wrote:
> Joe Wilso wrote:
> >I would not bother with make install on MinGW - too much hassle -
> >just copy out sqlite3.exe, libsqlite3.a and sqlite3.h manually
> >to wherever you want to put it.
> Joe:So, do is there a libsqlite3.a file?Where is it?Thanks--

Sorry, I meant sqlite3.dll. 

This ought to make a static library on MinGW:

  make sqlite3.c
  gcc -c -O2 sqlite3.c
  ar rv libsqlite3.a sqlite3.o



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Mac OS X name mangling

2007-12-01 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- [EMAIL PROTECTED] wrote:
> > > The comment probably should read:  "Needed to enable pthread
> > > recursive mutexes *on Linux*".  
> > 
> > Or more specifically, on old versions of Linux/GNU LIBC.
> > 
> > Have you seen any recent Linux distros that need this macro defined?
> > If so, maybe you could define it only for those specific distros, 
> > since defining it can be troublesome on other platforms.
> 
> I'm running SuSE 10.1.  It isn't that old.

I was wrong.  My recent linux distro also requires defining _XOPEN_SOURCE 
to 500 to pick up PTHREAD_MUTEX_RECURSIVE -- unless _GNU_SOURCE is defined.

This could be an alternative to avoid defining _XOPEN_SOURCE:

-#if !defined(_XOPEN_SOURCE) && !defined(__DARWIN__) && SQLITE_THREADSAFE
-#  define _XOPEN_SOURCE 500  /* Needed to enable pthread recursive mutexes */
+#if defined(linux) && !defined(_GNU_SOURCE) && \
+!defined(_XOPEN_SOURCE) && SQLITE_THREADSAFE
+# define _GNU_SOURCE
 #endif

Perhaps configure should decide if defining _XOPEN_SOURCE or _GNU_SOURCE 
is actually required on a platform.



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Mac OS X name mangling

2007-12-01 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> The comment probably should read:  "Needed to enable pthread
> recursive mutexes *on Linux*".  

Or more specifically, on old versions of Linux/GNU LIBC.

Have you seen any recent Linux distros that need this macro defined?
If so, maybe you could define it only for those specific distros, 
since defining it can be troublesome on other platforms.


  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: delete/update joins

2007-12-01 Thread Joe Wilson
--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Joe Wilson  wrote:
> > delete from table1 where rowid in (
> >  select table1.rowid
> >from table2
> >   where table1.id = table2.id
> > and table2.otherid = 1
> > );
> 
> Why not just
> 
>  delete from table1 where id in (
>   select id from table2
>where table2.otherid = 1
>  );

Yep, I missed that.

But in the (admittedly unlikely) event that table1 was small 
and table2 had billions of rows and otherid had low cardinality
or otherid was not indexed, while id was indexed in both tables,
then the first form could be more efficient.   ;-)




  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] delete/update joins

2007-11-30 Thread Joe Wilson
This may be better:

 delete from table1 where rowid in (
  select table1.rowid 
from table2 
   where table1.id = table2.id 
 and table2.otherid = 1
 );

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> How about:
> 
>  delete from table1 where id in (
>select table1.id 
>  from table1, table2 
> where table1.id = table2.id 
>   and table2.otherid = 1
>  );
> 
> or this:
> 
>  delete from table1 where rowid in (
>select table1.rowid 
>  from table1, table2 
> where table1.id = table2.id 
>   and table2.otherid = 1
>  );
> 
> Not sure how it could be done with a trigger.
> 
> --- Curtis Bruneau <[EMAIL PROTECTED]> wrote:
> > Are there any plans to support joins on delete? it can get quite long to 
> > do it the manual way when you have a lot of relational data.
> > 
> > DELETE FROM table
> > INNER JOIN table2
> > ON table.id = table2.id
> > WHERE table2.otherid = 1
> > 
> > This works in standard sql, I realise it may be difficult to implement 
> > just wondering if it was overlooked. The same goes for joins on UPDATE 
> > which don't appear to be supported. I should probably attempt to use 
> > triggers to solve this issue.



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] delete/update joins

2007-11-30 Thread Joe Wilson
How about:

 delete from table1 where id in (
   select table1.id 
 from table1, table2 
where table1.id = table2.id 
  and table2.otherid = 1
 );

or this:

 delete from table1 where rowid in (
   select table1.rowid 
 from table1, table2 
where table1.id = table2.id 
  and table2.otherid = 1
 );

Not sure how it could be done with a trigger.

--- Curtis Bruneau <[EMAIL PROTECTED]> wrote:
> Are there any plans to support joins on delete? it can get quite long to 
> do it the manual way when you have a lot of relational data.
> 
> DELETE FROM table
> INNER JOIN table2
> ON table.id = table2.id
> WHERE table2.otherid = 1
> 
> This works in standard sql, I realise it may be difficult to implement 
> just wondering if it was overlooked. The same goes for joins on UPDATE 
> which don't appear to be supported. I should probably attempt to use 
> triggers to solve this issue.



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] compiling for mingw

2007-11-30 Thread Joe Wilson
I would not bother with make install on MinGW - too much hassle -
just copy out sqlite3.exe, libsqlite3.a and sqlite3.h manually
to wherever you want to put it.

Or just use the sqlite3.[ch] amalgamation and don't use libsqlite3.a 
at all.

--- Bob Rossi <[EMAIL PROTECTED]> wrote:
> However, when I type
>   $ make install
> it fails because tclsh isn't installed when running this command,
>   tclsh ./tool/mksqlite3c.tcl
>   make: tclsh: Command not found



  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] ftruncate() for values greater than file size

2007-11-29 Thread Joe Wilson
> On Nov 29, 2007, at 9:19 PM, Mark Brown wrote:
> > I have finally tracked down a bug that has plagued by vxWorks  
> > port.  It
> > appears that our file system's implementation of ftruncate() does  
> > not like
> > to "truncate" a file larger than its current size, and returns  
> > EINVAL for
> > this operation.

This is interesting - ftruncate's behavior is not defined by POSIX
in this case.  SQLite shouldn't rely on the commonly implemented 
zero-fill extension.

 http://www.opengroup.org/onlinepubs/007908799/xsh/ftruncate.html

 "If the file previously was larger than length, the extra data is 
 discarded. If it was previously shorter than length, it is unspecified 
 whether the file is changed or its size increased."

One could easily write a wrapper function for ftruncate and use it
instead.




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Lemon: Conflicts with repeated TERMINALS

2007-11-29 Thread Joe Wilson
--- Ralf Junker <[EMAIL PROTECTED]> wrote:
> >  paragraph ::= PARA text.
> 
> I observed the new PARA terminal token (the clear separator!?). Unfortunately 
> the lexer does not
> generate such a token. Paragraph repeats are also removed.

It was just an HTML-like example. I just wanted to demonstrate one
possible way to remove the conflicts by adding a special tag. 
I'm not suggesting that you alter your grammar in this way.

> >Here's another:
> >
> >  article ::= blocks.
> >
> >  blocks ::= block.
> >  blocks ::= blocks block.
> >
> >  block ::= heading NEWLINE.
> >  block ::= paragraph NEWLINE.
> >
> >  heading ::= HEADING_START text HEADING_END.
> >  heading ::= HEADING_START text.
> >  heading ::= HEADING_START.
> >
> >  paragraph ::= text.
> >
> >  text ::= textpiece.
> >  text ::= text textpiece.
> >
> >  textpiece ::= TEXT.
> >  textpiece ::= LINK.
> 
> This one also removes paragraph repeats, doesn't it? Unfortunately paragraphs 
> need to repeat for
> my grammar. Is there a way to achieve this without conflicts?

In your original grammar, you could have random sequences of TEXT 
and LINK and NEWLINE tokens without any way of differentiating whether 
they were part of a "text" or "paragraph" or "heading", hence the conflict.
So I figured that a paragraph may as well be any combination of
TEXT and LINK tokens ending with NEWLINE. The headings in my 2nd grammar
also have to end with NEWLINE. "paragraph" will not repeat, per se, but you 
can repeat "block" (see the "blocks" rules), where you can have several 
consecutive "block"s that happen to be of type paragraph so you can achieve 
the same effect. 

The following grammar may be clearer to you:

  article ::= blocks.

  blocks ::= block.
  blocks ::= blocks block.

  block ::= heading.
  block ::= paragraph.

  heading ::= HEADING_START text HEADING_END.
  heading ::= HEADING_START text NEWLINE.
  heading ::= HEADING_START NEWLINE.

  paragraph ::= NEWLINE.
  paragraph ::= text NEWLINE.

  text ::= textpiece.
  text ::= text textpiece.

  textpiece ::= TEXT.
  textpiece ::= LINK.

It's much the same as the previous grammar, but puts the NEWLINEs as part 
of the paragraph and heading rules instead of in the block rule.
The difference being that heading no longer needs to end with NEWLINE 
in one case if HEADING_END is encountered, as it is not ambiguous:

  heading ::= HEADING_START text HEADING_END.

and a paragraph in this grammar may also be empty so you can parse 
consecutive NEWLINE tokens with this rule:

  paragraph ::= NEWLINE.

Again, this was just an example of how to disambiguate the grammar. 
You can find other ways.

> >Lemon generates an .out file for the .y file processed.
> >You can examine it for errors.
> 
> I have tried to make sense of the .out file before. It tells me where to look 
> for the problem,
> but not how to fix it ...

Try reading some papers on parsing or search for the book
"Compilers: Principles, Techniques, and Tools" (a.k.a. 
the dragon book).

Also try writing on paper random sequences of tokens and 
manually parse your grammar to see the conflicts firsthand.



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] db crash when creating rows

2007-11-28 Thread Joe Wilson
I compiled your program with MinGW gcc 3.4.2 on Windows with 
sqlite-amalgamation-3_5_3.zip.

  gcc -I. ged.c sqlite3.c -o ged.exe

It runs fine. If another sqlite3 process issues a query like:

  select * from test;

while ged.exe is running, then ged.exe will exit with:

  Creating 10 rows...
  died on row 2595
  SQL error 2: database is locked

So I'm wondering if another process accessed your database 
file while it was running. Or perhaps, are you running antivirus 
software on your PC?

What happens if you retry sqlite3_exec a few times after failure 
instead of exitting?  (throw in Sleep(50) after each failure for 
good measure)


--- Ged Murphy <[EMAIL PROTECTED]> wrote:

> I am doing some programmatic stress / benchmark testing on sqlite in the
> hope it'll be of use to me in my project.
> 
> I'm performing some simple stress tests to gauge read/write speeds, whereby
> I fill a table with a large amount rows
> 
> However,  I hit the following problem on writing:
> 
>   Creating 10 rows...
> 
>   died on row 1075
> 
>   SQL error 2: unable to open database fi
> 
>   Press any key to continue . . .
> 
> I'm using version 3.5.2 and my stress test code (written in C) is attached.
> 
> Does anyone have any ideas as to why it fails?



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Lemon: Conflicts with repeated TERMINALS

2007-11-28 Thread Joe Wilson
--- Ralf Junker <[EMAIL PROTECTED]> wrote:
> article ::= blocks.
> 
> blocks ::= block.
> blocks ::= blocks block.
> 
> block ::= heading.
> block ::= paragraph.
> 
> heading ::= HEADING_START text HEADING_END.
> heading ::= HEADING_START text.
> heading ::= HEADING_START.
> 
> paragraph ::= text NEWLINE.
> paragraph ::= paragraph text NEWLINE.
> paragraph ::= text.
> paragraph ::= paragraph text.
> 
> text ::= textpiece.
> text ::= text textpiece.
> 
> textpiece ::= TEXT.
> textpiece ::= LINK.

Your grammar is ambiguous. The text tokens run together for 
various rules because the grammar lacks clear separators between 
them. You can fix it a million ways by altering your grammar.

Here is one way:

  article ::= blocks.

  blocks ::= block.
  blocks ::= blocks block.

  block ::= heading.
  block ::= paragraph.

  heading ::= HEADING_START text HEADING_END.
  heading ::= HEADING_START text.
  heading ::= HEADING_START.

  paragraph ::= PARA text.

  text ::= textpiece.
  text ::= text textpiece.

  textpiece ::= TEXT.
  textpiece ::= LINK.

Here's another:

  article ::= blocks.

  blocks ::= block.
  blocks ::= blocks block.

  block ::= heading NEWLINE.
  block ::= paragraph NEWLINE.

  heading ::= HEADING_START text HEADING_END.
  heading ::= HEADING_START text.
  heading ::= HEADING_START.

  paragraph ::= text.

  text ::= textpiece.
  text ::= text textpiece.

  textpiece ::= TEXT.
  textpiece ::= LINK.

Lemon generates an .out file for the .y file processed.
You can examine it for errors.



  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] DESC indexes not available after a VACUUM?

2007-11-28 Thread Joe Wilson
Thanks for addressing this, Richard.

 Preset the legacy_file_format pragma to the value of the 
 primary database so that a VACUUM will not unknowingly 
 alter the setting. Ticket #2804.

 http://www.sqlite.org/cvstrac/chngview?cn=4574

Could you please update the PRAGMA documentation to reflect the 
new preset behavior of "PRAGMA legacy_file_format;"?

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> When I compare the database bytes before and after the 2nd 
> process VACUUM these bytes differ:
> 
> zero-basedvalue value
> byte offset   beforeafter my guess of what it is
>   2nd VAC   2nd VAC
> ---   ---   ---   --
> 272 5 File change counter?
> 432 5 Schema cookie?
> 474 1 meta[1], file format?
> 
> Doesn't byte offset 47 correspond to meta[1], the file format 
> of the schema layer?
> 
> --- Joe Wilson <[EMAIL PROTECTED]> wrote:
> > Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ...
> > 
> > I'm confused by the explanation.
> > Ignoring the PRAGMA for the moment, I was surprised that DESC 
> > indexes were not available after a reconnect and VACUUM, as shown
> > in the example:
> > 
> > $ rm -f foo.db
> > $ ./sqlite3-3.5.3.bin foo.db
> > SQLite version 3.5.3
> > Enter ".help" for instructions
> > sqlite> PRAGMA legacy_file_format=OFF;
> > sqlite> CREATE TABLE abc(a,b,c);
> > sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc);
> > sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> > 0|0|TABLE abc WITH INDEX abc_i ORDER BY
> > sqlite> vacuum;
> > sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> > 0|0|TABLE abc WITH INDEX abc_i ORDER BY
> > sqlite> .q
> > 
> > $ ./sqlite3-3.5.3.bin foo.db
> > SQLite version 3.5.3
> > Enter ".help" for instructions
> > sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> > 0|0|TABLE abc WITH INDEX abc_i ORDER BY
> > sqlite> vacuum;
> > sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> > 0|0|TABLE abc
> > 
> > Notice that the DESC index is no longer available after the second
> > process' VACUUM was run.
> > 
> > If this isn't the file format being changed, then why isn't the DESC
> > index being used?
> > 
> > Is this by design?




  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] DESC indexes not available after a VACUUM?

2007-11-27 Thread Joe Wilson
When I compare the database bytes before and after the 2nd 
process VACUUM these bytes differ:

zero-basedvalue value
byte offset   beforeafter my guess of what it is
  2nd VAC   2nd VAC
---   ---   ---   --
272 5 File change counter?
432 5 Schema cookie?
474 1 meta[1], file format?

Doesn't byte offset 47 correspond to meta[1], the file format 
of the schema layer?

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ...
> 
> I'm confused by the explanation.
> Ignoring the PRAGMA for the moment, I was surprised that DESC 
> indexes were not available after a reconnect and VACUUM, as shown
> in the example:
> 
> $ rm -f foo.db
> $ ./sqlite3-3.5.3.bin foo.db
> SQLite version 3.5.3
> Enter ".help" for instructions
> sqlite> PRAGMA legacy_file_format=OFF;
> sqlite> CREATE TABLE abc(a,b,c);
> sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc);
> sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> 0|0|TABLE abc WITH INDEX abc_i ORDER BY
> sqlite> vacuum;
> sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> 0|0|TABLE abc WITH INDEX abc_i ORDER BY
> sqlite> .q
> 
> $ ./sqlite3-3.5.3.bin foo.db
> SQLite version 3.5.3
> Enter ".help" for instructions
> sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> 0|0|TABLE abc WITH INDEX abc_i ORDER BY
> sqlite> vacuum;
> sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
> 0|0|TABLE abc
> 
> Notice that the DESC index is no longer available after the second
> process' VACUUM was run.
> 
> If this isn't the file format being changed, then why isn't the DESC
> index being used?
> 
> Is this by design?




  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] DESC indexes not available after a VACUUM?

2007-11-27 Thread Joe Wilson
Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ...

I'm confused by the explanation.
Ignoring the PRAGMA for the moment, I was surprised that DESC 
indexes were not available after a reconnect and VACUUM, as shown
in the example:

$ rm -f foo.db
$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format=OFF;
sqlite> CREATE TABLE abc(a,b,c);
sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc);
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> .q

$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc

Notice that the DESC index is no longer available after the second
process' VACUUM was run.

If this isn't the file format being changed, then why isn't the DESC
index being used?

Is this by design?


  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: Distinguishing empty-result SELECT from INSERT, UPDATE, DELETE, etc.

2007-11-27 Thread Joe Wilson
See also sqlite3_sql() if sqlite3_prepare_v2() or 
sqlite3_prepare16_v2() was used.

http://www.sqlite.org/cvstrac/chngview?cn=4543

--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Evans, Mark (Tandem) <[EMAIL PROTECTED]> wrote:
> > Would I be the first person to wish there were a way for, say, a JDBC
> > driver that uses SQLite to be able to tell what kind of SQL statement
> > it is executing?
> 
> sqlite3_column_count returns 0 for a non-SELECT statement, and a 
> non-zero value for SELECT.



  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Issue with index (maybe asc/desc problem)

2007-11-27 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> DESC indices requires a backwards-compatible file format
> changes.  Older versions of SQLite (prior to 3.3.0) cannot
> read or write databases that make use of DESC indicdes.
> To preserve compatibility, SQLite generates databases in
> the old format by default.   This means the the DESC keyword
> is ignored.  To generate a new-format database that honors
> the DESC keyword on indices, you have to do this:
> 
>PRAGMA legacy_file_format=OFF;
> 
> Prior to doing your very first CREATE TABLE statement. Or,
> you can compile SQLite to use the new file format by
> default using -DSQLITE_DEFAULT_FILE_FORMAT=4
> 
> Note that newer versions of SQLite understand both the old
> and the new format and can read and write both kinds of 
> database files.  The reason for continuing to use the older
> file format is that otherwise older versions of SQLite
> could not read or write database files created by newer
> versions of SQLite.

It seems that the PRAGMA legacy_file_format=OFF and the ability 
to use DESC indexes is lost after two VACUUMs and reconnects. 
Or am I doing something wrong?

$ rm -f foo.db

$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> PRAGMA legacy_file_format=OFF;
sqlite> PRAGMA legacy_file_format;
0
sqlite> CREATE TABLE abc(a,b,c);
sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc);
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> .q

In the next connection we see that the legacy file format reverted
back to 1, but the DESC index is still picked up...

$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> .q

But if connected to another time, the DESC index is not picked up...

$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Version 3.5.3

2007-11-27 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> SQLite version 3.5.3 is now up on the website:
> As always, please report any problems.

http://www.sqlite.org/releaselog/3_5_3.html

  * DISTINCT can now make use of an INDEX in some cases.



  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] sqlite3Explorer

2007-11-25 Thread Joe Wilson
--- Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
> I wish I could "make it for Unix", but it uses a lot of windows-specific 
> things, plus it is done
> in Delphi, and since
> Kylix is practically dead, wlll...

I didn't realize it was written in Delphi.
Yes, that would be a difficult port to UNIX - more like a complete rewite.

There's always the WINE Windows emulator, I guess.


  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sqlite3Explorer

2007-11-24 Thread Joe Wilson
> the address follows, obfuscated for the usual reasons. to e-mail me, remove 
> any
> numeric digits and punctuation from the address that follows, and do the 
> obvious substitutions.
> hopefully spammers will not...
> 
> m6_i_ke$car(ampersand)(dot)s#i#n#g#u#l#a#r(dot)gr

Nevermind the spammers - I can't break that encryption.

Nice software, that sqlite3Explorer, by the way.
Do you plan to make a version for UNIX?




  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Performance tuning using PRAGMA, other methods

2007-11-20 Thread Joe Wilson
Why not try benchmarking the pragmas yourself and posting your 
findings to the list?

  http://www.sqlite.org/pragma.html

--- Scott Krig <[EMAIL PROTECTED]> wrote:
> Q1)) PRAGMA: Does anyone have experience and good results optimizing
> sqlite performance using PRAGMA's? If so, which ones, how were they
> used, and what was the performance increase?
> 
> Q2)) Other techniques: Any success stories on sqlite optimization
> methods of any type would be appreciated.




  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Memory Usage

2007-11-20 Thread Joe Wilson
Once again you're missing the point.

Of course you can get a malloc/free implementation that performs 
garbage collection, such as Boehm's conservative GC. But C garbage 
collection and malloc/free memory fragmentation are quite different 
things. You can still get heavily fragmented memory with a C garbage 
collector - or even with traditional malloc/free with an ideal 
free()ing scheme.

Java and C garbage collectors are not directly comparable. Java is 
able to compact memory by copying it and adjusting the references 
transparently such that no unusable memory gaps are left. This is 
not possible in C due to its cavalier pointer casting and raw memory 
manipulation. Successful C garbage collectors instead opt for 
conservative GC leaving the memory at the same location.
(I am not aware of any precise C garbage collector that works with
multi-threaded code or runs in a comparable time to malloc/free.)

Nevermind that typical garbage collectors (Java or C) need 3 times
or more working memory as a typical malloc/free scheme in order 
to achieve decent timings. If memory is tight and performance is
paramount, then garbage collection is not the answer.

--- John Stanton <[EMAIL PROTECTED]> wrote:
> You confused my point which is that your usual malloc/free definitely 
> does no garbage collection.  That does not mean that a C language 
> program cannot perform garbage collection, just look at a Java run time 
> package for an example.
> 
> If you never execute a free your dynamic memory is essentially contiguous.
> 
> Joe Wilson wrote:
> > --- John Stanton <[EMAIL PROTECTED]> wrote:
> > 
> >>Malloc is a concept implemented in various ways, some more successful 
> >>than others but all of them hidden from the programmer.  Free tries to 
> >>give back memory but as you can appreciate unless you use some garbage 
> >>collection scheme with backwards pointers fragmentation and 
> >>checkerboarding is very difficult to avoid.
> > 
> > 
> > You seem to be confusing the topics of malloc/free memory fragmentation 
> > with C garbage collection - they are orthogonal concepts.
> > 
> > C's free() has no power to free up any extra memory as active memory 
> > must stay where it was allocated until freed or else you'll have 
> > dangling pointers. A single call to free() typically releases only memory 
> > allocated by a single malloc() call. If, as result of the free, there 
> > happens to be block(s) of memory immediately before or after that 
> > released memory then that entire memory region can be coalesced and 
> > be made available as a larger block to future mallocs.
> > 
> > If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no 
> > memory leaks, then garbage collection is irrelevant to the topic of
> > memory fragmentation. It's not like C can employ a copying garbage 
> > collector that moves memory blocks after free() without the knowledge 
> > or participation of the host program. The malloc() call is where 
> > fragmentation happens. Fragmentation in malloc depends on your allocation 
> > strategy: first-fit, best-fit, short-lived versus long-lived pools, 
> > per-allocation-size pools, statistical prediction, etc. Malloc must 
> > try to guess where an allocation must go to try to prevent future 
> > memory fragmentation. 



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] how do I edit the wiki? or do I?

2007-11-19 Thread Joe Wilson
Step 1: [Edit]

--- P Kishor <[EMAIL PROTECTED]> wrote:
> I would like to put up step-by-step instructions that would make sense
> to some one not so developer-like as I. How do I add stuff to the
> wiki? or, how do I get a login for the wiki to do so?



  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: building a custom DBD::SQLite

2007-11-19 Thread Joe Wilson
Grep for the unresolved external in all the *.[ch] files.
sqlite3Fts3InitHashTable lives in ext/fts3/fts3_tokenizer.c.
Make sure it is in your makefile.

Ditto for fts2, with a slightly different name.

If you're building fts2 make sure you compile with 

  -DSQLITE_ENABLE_BROKEN_FTS2
  -DSQLITE_ENABLE_FTS2
  -DSQLITE_CORE

When In doubt, grep and read the source files.
There's a lot of info there.

--- P Kishor <[EMAIL PROTECTED]> wrote:
> Here are my travails thus far. I have been singularly unable to build
> SQLite 3.5.2 with fts3 on Intel Mac OS X 10.4.11
> 
> I added the following to my Makefile.in
> 
> #TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1
> TCC += -DSQLITE_CORE=1
> TCC += -DSQLITE_ENABLE_FTS3=1
> 
> and then added all the fts3 .c and .h files in the appropriate places
> as documented by Joe Wilson in another email. Then I did the following
> 
> $ export MACOSX_DEPLOYMENT_TARGET=10.3
> $ ./configure
> $ make
> 
> make croaks with the following error
> 
> /usr/bin/ld: Undefined symbols:
> _sqlite3Fts3InitHashTable
> collect2: ld returned 1 exit status
> make: *** [sqlite3] Error 1



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Lemon problem

2007-11-18 Thread Joe Wilson
It's most likely your bug. Just add some debug prints in your grammar 
and tokenizer to see what's going on.
See also: ParseTrace() in http://www.hwaci.com/sw/lemon/lemon.html

--- Téragone <[EMAIL PROTECTED]> wrote:
> I have a problem with a rule of a small calculator which accept variables :
> 
> assignment(A) ::= VARIABLE(C) EQUAL expr(B).
> 
> I can set variable with simple value or expression like :
> a=2
> b=2+3
> 
> but when I try :
> c=a+b
> 
> The result is put in variable b instead of c.
> 
> Is it my bug or a Lemon bug ?



  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Bug on "!" unary prefix operator?

2007-11-17 Thread Joe Wilson
--- Ken <[EMAIL PROTECTED]> wrote:
> SQLite  documentation indicates that "!" is a unary prefix operator. Is this 
> a bug?

Yeah, the docs seem to indicate that SQLite supports '!'.

http://sqlite.org/lang_expr.html:

  Supported unary prefix operators are these:

-+!~NOT

If you care to have this operator, see attached patch.

This unary '!' operator works like the NOT operator except 
it has the same precedence level as the unary bitwise not 
operator '~'.

sqlite> select !!23;
1
sqlite> select !0 + !0;
2
sqlite> select not 0 + not 0;
0




  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/Index: src/parse.y
===
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.236
diff -u -3 -p -r1.236 parse.y
--- src/parse.y 17 Nov 2007 22:23:28 -  1.236
+++ src/parse.y 18 Nov 2007 05:33:03 -
@@ -205,7 +205,7 @@ id(A) ::= ID(X). {A = X;}
 %left STAR SLASH REM.
 %left CONCAT.
 %left COLLATE.
-%right UMINUS UPLUS BITNOT.
+%right UMINUS UPLUS BITNOT UNOT.
 
 // And "ids" is an identifer-or-string.
 //
@@ -746,6 +746,10 @@ expr(A) ::= BITNOT(B) expr(X). {
   A = sqlite3PExpr(pParse, @B, X, 0, 0);
   sqlite3ExprSpan(A,,>span);
 }
+expr(A) ::= UNOT(B) expr(X). {
+  A = sqlite3PExpr(pParse, TK_NOT, X, 0, 0);
+  sqlite3ExprSpan(A,,>span);
+}
 expr(A) ::= MINUS(B) expr(X). [UMINUS] {
   A = sqlite3PExpr(pParse, TK_UMINUS, X, 0, 0);
   sqlite3ExprSpan(A,,>span);
Index: src/tokenize.c
===
RCS file: /sqlite/sqlite/src/tokenize.c,v
retrieving revision 1.136
diff -u -3 -p -r1.136 tokenize.c
--- src/tokenize.c  27 Aug 2007 23:26:59 -  1.136
+++ src/tokenize.c  18 Nov 2007 05:33:03 -
@@ -203,12 +203,12 @@ static int getToken(const unsigned char 
   }
 }
 case '!': {
-  if( z[1]!='=' ){
-*tokenType = TK_ILLEGAL;
-return 2;
-  }else{
+  if( z[1]=='=' ){
 *tokenType = TK_NE;
 return 2;
+  }else{
+*tokenType = TK_UNOT;
+return 1;
   }
 }
 case '|': {
Index: test/main.test
===
RCS file: /sqlite/sqlite/test/main.test,v
retrieving revision 1.27
diff -u -3 -p -r1.27 main.test
--- test/main.test  3 Sep 2007 15:42:48 -   1.27
+++ test/main.test  18 Nov 2007 05:33:03 -
@@ -267,7 +267,7 @@ do_test main-3.1 {
   sqlite3 db testdb
   set v [catch {execsql {SELECT * from T1 where x!!5}} msg]
   lappend v $msg
-} {1 {unrecognized token: "!!"}}
+} {1 {near "!": syntax error}}
 do_test main-3.2 {
   catch {db close}
   foreach f [glob -nocomplain testdb/*] {file delete -force $f}

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQL operator precedence

2007-11-17 Thread Joe Wilson
> I believe SQLite uses the same operator precedence as the SQL standard
> requires.  If I am wrong about that, please correct me and I will  
> change it.

As I interpret the SQL92 standard (which I believe SQLite tries to 
follow), I think all comparison operators should have the same level 
of precedence:

  ::=

  | 
  | 
  | 
  | 
  | 

Nothing in the wording of the standard that I can find suggests 
that NE and EQ have a different precedence level from the other
4 comparison ops:

  %left IS MATCH LIKE_KW BETWEEN IN ISNULL NOTNULL NE EQ.
  %left GT LE LT GE.

The following binary bitwise operators are outside of the SQL92 
standard:

  <<, >>
  &
  |

but many SQL implementations tend to adopt the C precedence rules 
in this case. Principle of least astonishment, I imagine.



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sqlite and lemon operator precedence problem/question

2007-11-17 Thread Joe Wilson
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> I was wrong.  Turns out the bug was in the SQLite grammar
> file parse.y.  It was assigning the same precedence to the
> ones-complement ~ operator and the NOT operator.  But
> ~ should have higher precedence, it seems.  Fixed by
> check-in [4548].

Just to confirm, if you mix multiple operators in the same Lemon 
rule must you take manual precautions that they do not have different
precedences?

It would be pretty cool if that expansion could happen automatically
since the action specified in the grammar is the same:

original rule:

 expr(A) ::= NOT|BITNOT(B) expr(X). {
   A = sqlite3PExpr(pParse, @B, X, 0, 0);
   sqlite3ExprSpan(A,,>span);
 }

your grammar correction:

 expr(A) ::= NOT(B) expr(X). {
   A = sqlite3PExpr(pParse, @B, X, 0, 0);
   sqlite3ExprSpan(A,,>span);
 }
 expr(A) ::= BITNOT(B) expr(X). {
   A = sqlite3PExpr(pParse, @B, X, 0, 0);
   sqlite3ExprSpan(A,,>span);
 }

...or perhaps have Lemon issue a warning of an operator precedence 
problem?



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sqlite and lemon operator precedence problem/question

2007-11-17 Thread Joe Wilson
> >   sqlite> select ~1 - ~5;
> >   -8
> >   sqlite> select (~1) - (~5);
> >   4
> >
> 
> That would be a bug in lemon...

I guess adopting the same operator precedence as MySQL or MS SQL Server
is out of the question?

http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
:=

http://msdn2.microsoft.com/en-us/library/ms190276.aspx



  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] sqlite and lemon operator precedence problem/question

2007-11-17 Thread Joe Wilson
I'm having difficulty with Lemon's operator precedence.

Given SQLite's operator precedence table where it's presumably
interpreted with lowest precedence tokens at the top to the
highest precedence tokens at the bottom:

  %left OR.
  %left AND.
  %right NOT.
  %left IS MATCH LIKE_KW BETWEEN IN ISNULL NOTNULL NE EQ.
  %left GT LE LT GE.
  %right ESCAPE.
  %left BITAND BITOR LSHIFT RSHIFT.
  %left PLUS MINUS.
  %left STAR SLASH REM.
  %left CONCAT.
  %left COLLATE.
  %right UMINUS UPLUS BITNOT.

Why doesn't the BITNOT operator '~' have the highest precedence?

  SQLite version 3.5.2
  Enter ".help" for instructions
  sqlite> select ~1 - ~5;
  -8
  sqlite> select (~1) - (~5);
  4

Is precedence not determined by the order of the %left/%right 
lines in parse.y?  If not, how might one assign BITNOT the highest 
precedence?

MySQL, by comparison:

  mysql> select ~1 - ~5;
  +-+
  | ~1 - ~5 |
  +-+
  |   4 |
  +-+

  mysql> select (~1) - (~5);
  +-+
  | (~1) - (~5) |
  +-+
  |   4 |
  +-+

MySQL operator precedence:
http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

MS SQL Server precedence:
http://msdn2.microsoft.com/en-us/library/ms190276.aspx



  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: Threads

2007-11-14 Thread Joe Wilson
--- John Stanton <[EMAIL PROTECTED]> wrote:
> One of the ignored points about thread usage is just how expensive are 
> the synchronization mechanisms.  It is a good idea to apply Occam's 
> Razor to your design and eliminate unnecessary features and have a 
> result which provides a better level of functionality and a structure 
> which is much simpler to prove correct.
>
> I see situations where there is a complex web of worker threads etc 
> applied to what would otherwise be a simple problem.  The result runs 
> slowly and has hidden race conditions and other defects.  DRH's 
> reservations about threads come to mind.

Sometimes it takes effort to get optimal performance.
Take recent video game hardware, for instance:
http://www.ddj.com/hpc-high-performance-computing/197801624

In general terms if your program runs more slowly after introducing 
threads then you're doing something wrong and you should revisit
your design. Synchronization should be minimal and threads should 
operate, for the most part, independently of each other. But if various 
threads happen to use a common global resource such as a database, 
some degree of serialization is expected depending on your SQL and
the implementation of the database.

> Applications run best when they can be reduced to a single stream 
> without any synchronization requirements.

The trick is to run many of those independent serialized processing
streams in parallel to achieve good performance.
The CPU makers are only putting more cores on their chips.
Might as well take advantage of them to improve performance.



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Request for help with the SQLite Website

2007-11-14 Thread Joe Wilson
Threads are very much in the C tradition - minimalistic.
If you code in C you must know what you're doing anyway. 
C is by no means a high level or safe language.

But until an automatically parallelizing safe language with 
good performance becomes popular - this is what we got.
You just have to rely on simple conventions to minimize the 
risks.

--- Michael Scharf <[EMAIL PROTECTED]> wrote:
> Why Threads Are A Bad Idea:
> http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf
> 
>  From the article:
>"Threads are a seemingly straightforward adaptation of the
>dominant sequential model of computation to concurrent
>systems. Languages require little or no syntactic changes to
>support threads, and operating systems and architectures
>have evolved to efficiently support them. Many technologists
>are pushing for increased use of multithreading in software
>in order to take advantage of the predicted increases in
>parallelism in computer architectures. In this paper, I
>argue that this is not a good idea. Although threads seem to
>be a small step from sequential computation, in fact, they
>represent a huge step. They discard the most essential and
>appealing properties of sequential computation:
>understandability, predictability, and determinism. Threads,
>as a model of computation, are wildly nondeterministic, and
>the job of the programmer becomes one of pruning that
>nondeterminism. Although many research techniques improve
>the model by offering more effective pruning, I argue that
>this is approaching the problem backwards. Rather than
>pruning nondeterminism, we should build from essentially
>deterministic, composable components. Nondeterminism should
>be explicitly and judiciously introduced where needed,
>rather than removed where not needed. The consequences of
>this principle are profound. I argue for the development of
>concurrent coordination languages based on sound, composable
>formalisms. I believe that such languages will yield much
>more reliable, and more concurrent programs."



  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Re: Threads

2007-11-14 Thread Joe Wilson
--- Ken <[EMAIL PROTECTED]> wrote:
> In general I'v found that Thread cancellation is very painful,
> a simpler paradigm to utilize is the lock timeout with a Global 
> variable status check.

Rather than check a global variable you could simply pass a null
event to the queue which instructs the thread to simply to finish
(a.k.a. return) gracefully. That way you can avoid the lock timeout
and polling.

On the GUI thread, however a timeout and poll may be necessary 
depending on the framework.


  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Request for help with the SQLite Website

2007-11-13 Thread Joe Wilson
--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> 
> On Nov 13, 2007, at 10:55 PM, Joe Wilson wrote:
> 
> > http://home.pacbell.net/ouster/threads.pdf
> 
> JO and I reach a similar conclusion but by different
> reasoning, I think.

I like this line:

  Should You Abandon Threads?
  * No: important for high-end servers (e.g. databases).

If you use the idiom whereby each thread solely takes its tasks 
from a thread-safe work queue, you can have a clear separation of 
responsibilities and minimal or preferably no shared-state between 
threads. You get concurrency as a side effect of this simple hybrid 
event-passing/thread scheme.

Thread madness lies in complex multi-layer mutexes and shared data.


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Request for help with the SQLite Website

2007-11-13 Thread Joe Wilson
http://home.pacbell.net/ouster/threads.pdf

--- Richard Klein <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > What?  And encourage people to write multitheaded programs?
> > Not likely...
> 
> I've been meaning to ask ... When you say that multiple threads
> are evil, do you mean "as opposed to multiple processes"?  Or
> do you feel that multiprogramming in general is evil?
> 
> - Richard Klein



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Request for help with the SQLite Website

2007-11-13 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > You might mention the library is multi-thread safe in the Features 
> > section of http://www.sqlite.org/about.html
> 
> What?  And encourage people to write multitheaded programs?
> Not likely...

Good luck with that quest.

People are going to want to know it whether you want to encourage it
or not. 

How about mentioning it on the http://www.sqlite.org/compile.html page,
at least?

> > Do you have a page that describes all the SQLITE_OMIT_* ifdefs 
> > and compile options?
> > 
> 
> http://www.sqlite.org/compile.html  
> Likely it needs to be updated.

Is there a link to it from the Features section of the About page?
Maybe with a comment like "Highly customizable" or something.



  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Request for help with the SQLite Website

2007-11-13 Thread Joe Wilson
You might mention the library is multi-thread safe in the Features 
section of http://www.sqlite.org/about.html

Do you have a page that describes all the SQLITE_OMIT_* ifdefs 
and compile options?



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] PATCH: allow DISTINCT queries to use an INDEX, if present

2007-11-11 Thread Joe Wilson
Attached patch allows DISTINCT queries to take advantage of an appropriate
INDEX, if present.

For example, given:

  CREATE TABLE foo(a, b);
  CREATE INDEX foo_b on foo(b);
  explain query plan select distinct b from foo;

sqlite 3.5.2 returns:

  0|0|TABLE foo

and sqlite 3.5.2 + patch returns:

  0|0|TABLE foo WITH INDEX foo_b ORDER BY

The query "select distinct b from foo" is transformed into
"select b from foo group by b" by the patch.

No regressions in "make test", although a few tests had to be
altered in order to take into account the new ordering of DISTINCT
rows. This is to be expected, as the order in which DISTINCT rows 
are returned were never guaranteed without an explicit ORDER BY 
clause.

Please send any bug reports or comments to the list.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com Index: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.359
diff -u -3 -p -r1.359 select.c
--- src/select.c31 Aug 2007 17:42:48 -  1.359
+++ src/select.c11 Nov 2007 23:49:01 -
@@ -3067,6 +3067,14 @@ int sqlite3Select(
   }
 #endif
 
+  /* Optimizion: convert DISTINCT to GROUP BY to potentially
+  ** take advantage of an INDEX
+  */
+  if (p->isDistinct && !p->isAgg && !p->pGroupBy) {
+pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, p->pEList);
+isDistinct = p->isDistinct = 0;
+  }
+
   /* If there is an ORDER BY clause, then this sorting
   ** index might end up being unused if the data can be 
   ** extracted in pre-sorted order.  If that is the case, then the
Index: test/collate5.test
===
RCS file: /sqlite/sqlite/test/collate5.test,v
retrieving revision 1.5
diff -u -3 -p -r1.5 collate5.test
--- test/collate5.test  7 Sep 2005 22:48:16 -   1.5
+++ test/collate5.test  11 Nov 2007 23:49:01 -
@@ -57,17 +57,17 @@ do_test collate5-1.1 {
   execsql {
 SELECT DISTINCT a FROM collate5t1;
   }
-} {a b n}
+} {A B N}
 do_test collate5-1.2 {
   execsql {
 SELECT DISTINCT b FROM collate5t1;
   }
-} {apple Apple banana {}}
+} {{} Apple apple banana}
 do_test collate5-1.3 {
   execsql {
 SELECT DISTINCT a, b FROM collate5t1;
   }
-} {a apple A Apple b banana n {}}
+} {A Apple a apple B banana N {}}
 
 # The remainder of this file tests compound SELECT statements.
 # Omit it if the library is compiled such that they are omitted.
Index: test/insert4.test
===
RCS file: /sqlite/sqlite/test/insert4.test,v
retrieving revision 1.8
diff -u -3 -p -r1.8 insert4.test
--- test/insert4.test   9 Oct 2007 08:29:32 -   1.8
+++ test/insert4.test   11 Nov 2007 23:49:01 -
@@ -112,7 +112,7 @@ do_test insert4-2.4.1 {
 INSERT INTO t3 SELECT DISTINCT * FROM t2;
 SELECT * FROM t3;
   }
-} {9 1 1 9}
+} {1 9 9 1}
 xferopt_test insert4-2.4.2 0
 do_test insert4-2.4.3 {
   catchsql {
Index: test/misc5.test
===
RCS file: /sqlite/sqlite/test/misc5.test,v
retrieving revision 1.17
diff -u -3 -p -r1.17 misc5.test
--- test/misc5.test 12 Sep 2007 17:01:45 -  1.17
+++ test/misc5.test 11 Nov 2007 23:49:01 -
@@ -490,6 +490,7 @@ ifcapable subquery {
 LIMIT 10
   )
   WHERE artist <> '' 
+  ORDER BY 1 DESC
 )  
)   
   )  

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- P Kishor <[EMAIL PROTECTED]> wrote:
> On 11/9/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- James Dennett <[EMAIL PROTECTED]> wrote:
> >
> > > Joe Wilson wrote:
> > > > Also, non-technical people would be a better judge of which website
> > > > design is appealing.
> > >
> > > Appealing *to* non-technical people?  Why would a website on an embedded
> > > database wish to appeal primarily to such an audience?  I'd think it
> > > would be best to present information in a way that appeals to its likely
> > > viewers.
> >
> > My wording was poor. I think a lot of programmers don't care what a software
> > website looks like, as long as the code works.
> 
> Wrong. Many, many programmers do care a lot about the looks of a
> software, the code behind the software, stuff written with and for
> that software, and the website of that software. While not always
> co-relating, a well laid out website is also attractive, and an
> attractive website is also well laid out. Software makers and
> designers, particularly in the world of Macs, are very proud of how
> their products look and behave, and they spend a considerable amount
> of effort making them look good.

Of course we all like nice looking software and websites. 
No one is disputing this. The question is how to achieve it.
Programmers are not necessarily the best people to make aesthetic 
decisions. Apple has their Human Interface Group.
Apple software tends to look better largely because someone has 
already made most of these design decisions in their famous 
Apple Human Interface Guidelines. There are people out there 
who do this sort of thing for a living, and frankly, are much 
better at it than most programmers.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- James Dennett <[EMAIL PROTECTED]> wrote:

> Joe Wilson wrote:
> > Also, non-technical people would be a better judge of which website
> > design is appealing.
> 
> Appealing *to* non-technical people?  Why would a website on an embedded
> database wish to appeal primarily to such an audience?  I'd think it
> would be best to present information in a way that appeals to its likely
> viewers.

My wording was poor. I think a lot of programmers don't care what a software 
website looks like, as long as the code works. The old website was sufficient 
in that regard. 

Maybe I'm completely wrong, but I thought the point of the website redesign 
was to reach a new market. People who don't code, yet make development
decisions for their companies. Everyone on this mailing list already is a 
user a SQLite. Sometimes it takes an outsider to give you an objective opinion. 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- "Samuel R. Neff" <[EMAIL PROTECTED]> wrote:
> 
> I hope this doesn't offend, but perhaps the best solution is to outsource
> the website to someone or a company that specializes in websites and design
> (with your stated simplicity goals in mind of course).  We certainly
> wouldn't want a graphic designer hacking away at the SQLite engine, so isn't
> the reverse also true? 
> 
> Sam

+1 

Also, non-technical people would be a better judge of which website 
design is appealing.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
Yep. The 'T' in SUPPORT disappears for me.
The fonts in Linux may differ slightly from Windows.
Maybe my machine does not have Verdana and is using the sans-serif
backup font choice.

(2) has the same issue.

--- "Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote:
> I like 3 too.  There's a minor glitch on Firefox - increasing font size
> causes the right side of menu bar to be whited out but display when
> cursor hovers. Is this the bug Joe refers to below?
> 
> Mark
> 
> > -Original Message-
> > From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> > Sent: Friday, November 09, 2007 1:30 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Suggests for improving the SQLite website
> > 
> > --- [EMAIL PROTECTED] wrote:
> > > I put up 4 variations.  Please, everyone, offer your opinions:
> > > 
> > >(1) http://sqlite.hwaci.com/v1/ No CSS of any kind.
> > >(2) http://sqlite.hwaci.com/v2/ CSS menus with 
> > rounded corners
> > >(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners
> > >(4) http://sqlite.hwaci.com/v4/ CSS font specification only
> > > 
> > > (2) and (3) do not work on IE6.  (1) has ugly fonts, I am told.
> > > That leaves me with (4).  
> > > 
> > > I suppose we could go with (4) now and change it later
> > 
> > I prefer (3). (SUPPORT 'T' render bug in Firefox aside).
> > 
> > (4) would also be good if you just centered the contents as 
> > in (3) to look better on wider resolutions.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- John Stanton <[EMAIL PROTECTED]> wrote:
> Joe Wilson wrote:
> >>(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners
> > 
> > In Firefox 2.0.0.8, press "CTRL +" a couple of times to see the render 
> > problem.  If I press "CTRL -" it renders properly.
> > 
> > On larger screen resoltions, sometimes the default fonts are a bit bigger 
> > than usual.
> > 
> Unfortunately fonts cannot be relied upon to scale smoothly so you get 
> truncation and similar situations occurring at certain resolutions or 
> font resizing.

Most popular websites will accommodate slight font size variation in 
their layouts.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> I put up 4 variations.  Please, everyone, offer your opinions:
> 
>(1) http://sqlite.hwaci.com/v1/ No CSS of any kind.
>(2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners
>(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners
>(4) http://sqlite.hwaci.com/v4/ CSS font specification only
> 
> (2) and (3) do not work on IE6.  (1) has ugly fonts, I am told.
> That leaves me with (4).  
> 
> I suppose we could go with (4) now and change it later

I prefer (3). (SUPPORT 'T' render bug in Firefox aside).

(4) would also be good if you just centered the contents as in (3)
to look better on wider resolutions.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> I don't think the extra bandwidth is an issue.  Dan points out that
> if you put the CSS in a separate file, then sometimes a browser
> will render the page without CSS, then when the CSS arrives a
> fraction of a second later, everything shifts.

That's not the case with external CSS file(s).
The browser blocks until the external css file is loaded. 
Nothing is rendered until that time.

Might you be refering to when images are loaded and cause layout 
to shift?

If the CSS does not load due to HTTP error, then I guess it's possible 
that the page will be rendered without it, but I don't know for
sure what happens in that case.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- James Dennett <[EMAIL PROTECTED]> wrote: 

> Joe Wilson wrote:
> > No need to say it's stable or recommended - it's assumed. Otherwise it
> > wouldn't appear on the home page.
> 
> I disagree.  3.5.0 appeared, even though discussion was that it was
> relatively experimental.  It's *good* to be explicit about this.

  Latest Stable Release: 2007-11-05 version 3.5.2
  Latest Dev Release:2038-01-01 version 3.9.7

Is more succinct and concise.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> There is a new look up on the demo site at
> 
>http://sqlite.hwaci.com/
> 
> It looks good on Firefox and Safari, but IE6 renders

The 'T' in 'SUPPORT' in the horizontal toolbar is cut off in my Linux 
Firefox 2.0.0.8 browser. I have a screen resolution of 1600x1200.

It takes time to get all popular browsers working, but it leaves a
good first impression with potential users of your software.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > The font change to
> > 
> >   font-family: "Verdana" "sans-serif";
> > 
> > makes a huge difference - much more professional looking.
> > 
> 
> This is indeed a sad commentary on the state of the
> world wide web that it is now necessary to specify
> a font on every web page   Oh well.

I think sqlite.org was the last site in the world that did not 
specify a font. But now I can't render the new home page on my 
original Tim Berners-Lee web browser on my NeXT cube! (just kidding)

You chose to embed the CSS settings on each page to avoid the round
trip to the web server. You can always put the css info in a separate 
file, and define it only once for the entire site. It should reduce
the number of bytes sent over the wire.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- bash <[EMAIL PROTECTED]> wrote:
> I am owner of web site with ~ 15k uniq visitors/day and I can say this
> is not really true.
> For example this is statistics from google analytics:
> 1.1024x76842.51%  
> 2.1280x1024   27.73%
> 3.1280x80010.43%
> 4.1152x8645.33%
> 5.1440x9003.10%

For some reason many PCs ship Windows with much lower resolutions 
than their graphics cards and monitors can support - even laptops.
It's a 2 second change for someone technical, but a lot of users
aren't even aware of the issue and leave the default setting.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
The font change to

  font-family: "Verdana" "sans-serif";

makes a huge difference - much more professional looking.

--- [EMAIL PROTECTED] wrote:
> A rough prototype of what a revised website might look like
> can be seen at
> 
>   http://sqlite.hwaci.com/


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Re: Disk caching impacts performance.

2007-11-09 Thread Joe Wilson
> > One more thing, did raising the limit on the number of pages SQLITE
> > can cache internally have any effect?
> 
> I just tried (hadn't noticed that option before) to go from 2000 to 4000
> and 8000, without noticing any difference. I might try next week to
> raise the page size to 50k and see if it makes a difference?

On an OS with file caching, this sqlite page cache setting will benefit 
write transactions more than reads.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> A rough prototype of what a revised website might look like
> can be seen at
> 
>   http://sqlite.hwaci.com/

Instead of:

  Current Status

  As of 2007-11-05 20:49:21 UTC, version 3.5.2 of SQLite is stable. 
  There are no known issues effecting database integrity or correctness. 
  Version 3.5.2 is recommended for all users.

I'd recommend:

  Latest Release: 2007-11-05 version 3.5.2

No need to say it's stable or recommended - it's assumed. Otherwise it 
wouldn't appear on the home page.

Or perhaps you could write:

  Latest Stable Release: 2007-11-05 version 3.5.2
  Latest Dev Release:2038-01-01 version 3.9.7

with an appropriate link to download the specific versions.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SQLite and Large Databases

2007-11-08 Thread Joe Wilson
--- PokerAce <[EMAIL PROTECTED]> wrote:
> "Are you certain it's sqlite RAM, and not your application?"
> 
> Yes, we are also testing PostgreSQL and MySQL and the application memory
> with those stays < 20 mb.

You're not using the sqlite3 API directly, are you?
If that's the case, I think your database driver is leaking memory.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Suggests for improving the SQLite website

2007-11-08 Thread Joe Wilson
--- [EMAIL PROTECTED] wrote:
> We are looking at renovating the design of the SQLite
> website and would love to have suggestions from the
> community.  If you have any ideas on how to improve
> the SQLite website, please constribute either to the
> mailing list or directly to me.

Prize giveaway to millionth page visitor.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] SQLite and Large Databases

2007-11-08 Thread Joe Wilson
--- PokerAce <[EMAIL PROTECTED]> wrote:
> I'm trying to see if SQLite is suitable for large databases ( > 1 gb,
> millions of rows in each of several tables).  Initially, the memory usage
> was outrageous (~ 500 mb for a 1.3 gb db), but I got that down to < 30 mb by
> setting the cache size to 0 and setting a low soft heap limit.  That works
> when I'm reading from the database, but when I am inserting these rows, the
> memory usage grows back into the ~500 mb range.  My goal is to never have
> the application use more than 100 mb of memory, preferably much less than
> that.  Does anyone have any suggestions?

If your cache_size is 0 I'm not sure what's eating up 500M of RAM for inserts.

Some questions that might give you some ideas:

Are you certain it's sqlite RAM, and not your application?
Is your temp_store set to memory or file?
How are you performing your inserts (prepared statements)?
How many rows are you inserting per batch?
What's your database page_size?
Can you build your table indexes after you populate the data?

Is this for a poker showdown database by any chance?
http://games.cs.ualberta.ca/poker/IRC/



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Disk caching impacts performance.

2007-11-08 Thread Joe Wilson
This disk cache effect is pretty well known on this list.

Mozilla/Firefox also uses a similar technique for its sqlite database:

 http://developer.mozilla.org/en/docs/Storage:Performance#Priming_the_cache

They go to the extra step of populating the sqlite page cache with
some custom code:

 http://mxr.mozilla.org/mozilla/source/db/sqlite3/preload-cache.patch

This patch probably won't work with the sqlite 3.5.x series without 
modification. But I don't think it's worth the hassle when a plain 
'file read and discard' without modifying sqlite code delivers 
comparable results for most (all?) desktop OSes with file caches,
as you've discovered.

--- Julien Renggli <[EMAIL PROTECTED]> wrote:
> I then had the following idea: SQLite is probably reading the file
> randomly, depending on where the data lies. If I can force the DB to be
> cached, everything should be fine. So before connecting the database, I
> first read it sequentially (using a C++ ifstream) until the end of file.
> 
> It perfectly solves the database problem, even though I still notice a
> difference (3s to read the file on 1st run, 0.2s later). But 3s is OK
> where 30s was worrying me.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] sqlite for 16 bits microcontroler x186

2007-11-08 Thread Joe Wilson
--- Felix <[EMAIL PROTECTED]> wrote:
> I would use sqlite in an embedded DOS system ( intel 16 bits processor ) .
> 
> Do you think that is possible or sqlite work only on 32/64 bits systems?

I could be wrong, but I think sqlite expects that int and pointers are
at least 32 bit.

See if you can find a C compiler that emulates 32 bit ints and pointers 
on 80186 - although it would be very slow. But even if such a compiler
exists your chip may not have enough RAM to effectively run sqlite anyway.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] why select distinct does not use index?

2007-11-08 Thread Joe Wilson
--- "Maxim V. Shiyanovsky" <[EMAIL PROTECTED]> wrote:
> Suppose I have simple table:
> 
> CREATE TABLE [profile_data] (
>   [profile_id] INTEGER,
>   [version] INTEGER);
> 
> CREATE INDEX [by_id] ON [profile_data] ([id]);
> 
> Why 
> 
> sqlite> explain query plan
>...> select distinct(profile_id) from profile_data;
> 
> 0|0|TABLE profile_data
> 
> Does not use index?

It should, but sqlite doesn't appear to make that optimization.

Try this instead:

CREATE TABLE profile_data(profile_id INTEGER, version INTEGER);
CREATE INDEX by_id ON profile_data(profile_id);
explain query plan select profile_id from profile_data group by 1;
0|0|TABLE profile_data WITH INDEX by_id ORDER BY


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] UTF-8 BLOB

2007-11-06 Thread Joe Wilson
It works in a Linux xterm.
There's probably some UTF or codepage issue with the Windows console.
Try using another command-line shell.

--- Robert Wishlaw <[EMAIL PROTECTED]> wrote:

> Using sqlite 3.5.2 on Windows XP, I have a textblob.csv file
> 
> 192,C0,À0,À0
> 193,C1,Á0,Á0
> 254,FE,þ0,þ0
> 255,FF,ÿ0,ÿ0
> 
> which I have imported into a new database
> 
> sqlite3 textblob.db < textblob.sql
> 
> via a file named textblob.sql
> 
> .separator ,
> create Table textblob(dec INTEGER, hex BLOB, txt TEXT, blb BLOB);
> .import 'textblob.csv' textblob
> 
> When I run the query
> 
> SELECT dec, hex FROM textblob WHERE blb = 'À0';
> 
> there is no result. Likewise
> 
> SELECT dec, hex FROM textblob WHERE blb = "À0";
> 
> does not work.
> 
> SELECT dec, hex FROM textblob WHERE hex = "C0";
> 
> returns
> 
> 192|C0
> 
> Any idea how to get the BLOB data? Or is the problem that the BLOB
> data is not there because .import is filtering out invalid UTF-8?



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



Re: [sqlite] Which built-in functions are mandatory?

2007-11-05 Thread Joe Wilson
--- Aladdin Lampé <[EMAIL PROTECTED]> wrote:
> In other terms, which built-in functions can I safely remove (or rename) 
> without getting
> internal errors?
> I hope I can remove or rename all of them without compilation or execution 
> errors... What do you
> think?

It depends on your use.
Some functions are used internally and cannot be renamed:

  substr()
  quote()
  LIKE operator (likeFunc)

You may need these as well:

  sqlite_rename_table()
  sqlite_rename_trigger()
  sqlite_detach()
  sqlite_attach()

If you run your program with this print statement, you should see 
all functions called by your program:

Index: src/expr.c
===
RCS file: /sqlite/sqlite/src/expr.c,v
retrieving revision 1.315
diff -u -3 -p -r1.315 expr.c
--- src/expr.c  23 Oct 2007 18:55:49 -  1.315
+++ src/expr.c  6 Nov 2007 05:09:47 -
@@ -1371,6 +1371,7 @@ static int nameResolverStep(void *pArg,

   zId = (char*)pExpr->token.z;
   nId = pExpr->token.n;
+  if (nId>0 && zId) printf("TK_FUNCTION: %.*s\n", nId, zId);
   pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
   if( pDef==0 ){
 pDef = sqlite3FindFunction(pParse->db, zId, nId, -1, enc, 0);


For example:

SQLite version 3.5.1
Enter ".help" for instructions
sqlite> create table foo(a);
sqlite> alter table "foo" rename to "goo";
TK_FUNCTION: sqlite_rename_table
TK_FUNCTION: sqlite_rename_trigger
TK_FUNCTION: LIKE
TK_FUNCTION: substr
sqlite> vacuum;
TK_FUNCTION: substr
TK_FUNCTION: substr
TK_FUNCTION: LIKE
TK_FUNCTION: substr
TK_FUNCTION: LIKE
TK_FUNCTION: quote
TK_FUNCTION: quote
TK_FUNCTION: quote
TK_FUNCTION: quote
TK_FUNCTION: quote


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  1   2   3   4   5   6   7   8   >