Re: [sqlite] COUNT() extremely slow first time!

[Stephen Oberholtzer]

On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson  wrote:
> On Mon, Feb 21, 2011 at 11:05 AM, Sven L  wrote:
>>
>> Thank you for your detailed explanation!
>> First, can you please tell me how to purge the cache in Windows 7? This 
>> could be very useful for my tests!
>
> Sorry, dunno for Windows.  On Mac OSX it is the purge command, in the
> development tools.  On Linux, you do: echo 3 >
> /prog/sys/vm/drop_caches

Just make sure you either (a) quote the 3 (echo '3' >
/proc/sys/vm/drop_caches) or (b) put a space between the 3 and the >.
If you don't quote it, and you don't put the space in (echo
3>/proc/sys/vm/drop_caches) it doesn't do anything, for reasons I
won't go into.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Patch to allow DISTINCT in multi-argument aggregatefunctions

[Stephen Oberholtzer]

On Fri, Jan 21, 2011 at 5:51 AM, smart sl  wrote:
> Good news. I've asked a few days earlier that how could I use DISTINCT in
> group_concat with seperator specified meanwhile. It's lovely.

I know -- reading that thread is what inspired the patch.


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Patch to allow DISTINCT in multi-argument aggregate functions

[Stephen Oberholtzer]

Good news, everyone! I've taught SQLite to feel love!

No, wait, that's not it. I spotted the thread 'how to use group_concat
uniquely' today, and felt inspired, so I wrote a quick patch.

The patch allows a multi-argument aggregate function to be passed
DISTINCT, under the restriction that every other argument must be
constant.

This allows for, among other things, the following syntax:

sqlite> select group_concat(distinct type, ';') from sqlite_master;
table;index

But you still can't do it if there is more than one column referenced:

sqlite> select group_concat(distinct type, tbl_name) from sqlite_master;
Error: DISTINCT aggregates must have at most one non-constant argument

I know how I would implement that, but that's way more effort than I'm
willing to expend right now.


Here's a quick diff.  If people (in particular, DRH) think it's worth
it, I'll write up the test cases and submit a more formal patch.

Index: src/select.c
===
--- src/select.c
+++ src/select.c
@@ -3487,10 +3487,74 @@
   if( pParse->nErr || db->mallocFailed ) return;
   sqlite3SelectAddTypeInfo(pParse, p);
 }

 /*
+** Set up and validate an aggregate being called with a DISTINCT argument.
+**
+** Verifies that one of the following is true:
+**
+** *  The aggregate takes exactly one argument.
+** *  The aggregate is being passed at most one non-constant argument.
+**
+** If neither of these is satisfied, the return value is zero.
+** Otherwise, the return value is nonzero, and the following are performed:
+**
+** * pFunc->iDistinctArg is set to the index of the argument that is
+**   DISTINCT.
+** * keyExprList is filled out to refer to only the distinct column.
+**   Note that all pointers are aliased to the original expression list;
+**   no freeing must be done.
+**
+*/
+
+static int setupDistinctAggregate(Parse *pParse, struct AggInfo_func
*pFunc, ExprList *keyExprList){
+  int i;
+  ExprList *pEL = pFunc->pExpr->x.pList;
+  struct ExprList_item *pELi;
+  pFunc->iDistinctArg = -1;
+  if( pEL==0 ){
+return 0;
+  }
+  if( pEL->nExpr==1 ){
+/* If there is only one argument to the aggregate, that argument
must be the
+** DISTINCT one.
+*/
+
+pFunc->iDistinctArg = 0;
+  }else{
+/* Okay, there is more than one argument to this aggregate.
+** Require that at most one is non-constant.
+*/
+for(i=0, pELi=pEL->a; inExpr; i++, pELi++){
+  if( !sqlite3ExprIsConstantOrFunction(pELi->pExpr) )
+  {
+if( pFunc->iDistinctArg>=0 ){
+  return 0;
+}
+pFunc->iDistinctArg = i;
+  }
+}
+
+if( pFunc->iDistinctArg<0 ){
+  /* All of the arguments passed to this function are constant.
+  ** Arbitrarily choose the first argument for the DISTINCTness.
+  ** TODO: somehow degrade this to a non-DISTINCT for this case.
+  */
+  pFunc->iDistinctArg = 0;
+}
+  }
+
+  keyExprList->nExpr = 1;
+  keyExprList->nAlloc = 0;
+  keyExprList->iECursor = pEL->iECursor;
+  keyExprList->a = >a[pFunc->iDistinctArg];
+
+  return 1;
+}
+
+/*
 ** Reset the aggregate accumulator.
 **
 ** The aggregate accumulator is a set of memory cells that hold
 ** intermediate results while calculating an aggregate.  This
 ** routine simply stores NULLs in all of those memory cells.
@@ -3507,17 +3571,18 @@
   }
   for(pFunc=pAggInfo->aFunc, i=0; inFunc; i++, pFunc++){
 sqlite3VdbeAddOp2(v, OP_Null, 0, pFunc->iMem);
 if( pFunc->iDistinct>=0 ){
   Expr *pE = pFunc->pExpr;
+  ExprList keyEL;
   assert( !ExprHasProperty(pE, EP_xIsSelect) );
-  if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
-sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
-   "argument");
+  if( !setupDistinctAggregate(pParse, pFunc, ) ){
+sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have at most one "
+   "non-constant argument");
 pFunc->iDistinct = -1;
   }else{
-KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList);
+KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, );
 sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0,
   (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
   }
 }
   }
@@ -3565,12 +3630,12 @@
   nArg = 0;
   regAgg = 0;
 }
 if( pF->iDistinct>=0 ){
   addrNext = sqlite3VdbeMakeLabel(v);
-  assert( nArg==1 );
-  codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg);
+  //assert( nArg==1 );
+  codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg +
pF->iDistinctArg);
 }
 if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){
   CollSeq *pColl = 0;
   struct ExprList_item *pItem;
   int j;

Index: src/sqliteInt.h
===
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -1530,10 +1530,11 @@
   struct AggInfo_func {   /* For each aggregate function */
 

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Stephen Oberholtzer]

On Sat, Sep 11, 2010 at 2:24 PM, Max Vlasov <max.vla...@gmail.com> wrote:
> On Sat, Sep 11, 2010 at 8:38 PM, Stephen Oberholtzer <
> oliverkloz...@gmail.com> wrote:
>
>> 2. They contain only a subset of the columns in the main table, so
>> they are smaller -- so reading through the entire index is faster than
>> reading through the entire table.
>>
>>
> Stephen, are you telling that is' smaller in any situation? When I mentioned
> the trigger in case of fast reading of rowid/id, I thought that in this case
> there can be a separated table with sing field id (rowid) that should change
> its contents synchronously to the main table that contains all data. I
> suppose in this case the two variants (index vs trigger) is on par in terms
> of the size or am I wrong?
>
> Max

Underneath the surface, an index is just a mini-table that contains
the indexed columns, plus the rowid, and is stored in sort order.
An index will always contain the indexed columns, plus the rowid.
Since their is no way to have MORE columns in an index than in the
table itself, there is no way for an index to be  bigger than its
table. The worst case is when the index has every single column in the
table, in which case the index is exactly the same size as the table
(because it contains the exact same data, just in a different order).
In fact, the table itself is basically an index with 'rowid' as the
first column.

 When you generate an insert/update/delete statement, SQLite
automatically generates code to maintain the index (updates are
handled by deleting + reinserting):

CREATE TABLE Foo (value integer primary key, insertdate text not null,
name text not null);
CREATE INDEX date_IX on Foo (insertdate);

sqlite> explain insert into Foo (insertdate, name) values ('20100911', 'Steve');
addr  opcode         p1    p2    p3    p4             p5  comment
  -        -  --  -
0     Trace          0     0     0                    00
1     Goto           0     19    0                    00
2     OpenWrite      0     2     0     3              00
3     OpenWrite      1     3     0     keyinfo(1,BINARY)  00
4     NewRowid       0     3     0                    00
5     Null           0     4     0                    00
6     String8        0     5     0     20100911       00
7     String8        0     6     0     Steve          00
8     HaltIfNull     19    2     5     Foo.insertdate may not be NULL
00
9     HaltIfNull     19    2     6     Foo.name may not be NULL  00
10    SCopy          5     7     0                    00
11    SCopy          3     8     0                    00
12    MakeRecord     7     2     1     ab             00
13    IdxInsert      1     1     0                    10
14    MakeRecord     4     3     9     daa            00
15    Insert         0     9     3     Foo            1b
16    Close          0     0     0                    00
17    Close          1     0     0                    00
18    Halt           0     0     0                    00
19    Transaction    0     1     0                    00
20    VerifyCookie   0     4     0                    00
21    TableLock      0     2     1     Foo            00
22    Goto           0     2     0                    00

Step #12 builds the index record and step #13 performs an insert into
the index. You may note that #12 builds a record with 2 columns, when
the index definition only has 1. That's because every index implicitly
includes the rowid.  If I were to add additional indexes to Foo, there
would be additional (SCopy + MakeRecord + IdxInsert) instructions for
each one.

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Stephen Oberholtzer]

On Sat, Sep 11, 2010 at 7:52 AM, Max Vlasov  wrote:

> Cory, thanks, just wondering, is the nature of the index internals is a part
> of SQL standard or just a common sense. For example, I can imagine real
> example when the space used is not a big problem so index could contain
> other non-indexed fields just for benefits of returning them without
> necessity to query the main table. Also (unlikely really used, but
> theoretically possible) minimalistic approach to space when only rowids (or
> equivalent) is saved and every operation including comparision operation
> requires querying the main table by rowid. It seems that in the first case
> the benefits of the index for faster loading will be completely lost.
>
> Max

First, I'd like to point out that if you fiddle around with EXPLAIN,
you will find that, interestingly enough, creating an index internally
behaves a lot like if you placed insert/update/delete triggers on your
table -- but the index requires a LOT less code (in fact, a single
instruction).



Second, on the subject of indexes:

Indexes have two properties that make them beneficial in two different ways:
1. They are sorted, so any WHERE clause that matches the first N
columns can be matched very effectively.
2. They contain only a subset of the columns in the main table, so
they are smaller -- so reading through the entire index is faster than
reading through the entire table.

Now, my tests indicate that SQLite's optimizer is *not* clever enough
to account for #2:

CREATE TABLE Foo (value integer primary key, insertdate text not null,
name text not null);
CREATE INDEX date_IX on Foo (insertdate);

sqlite> explain query plan select insertdate from foo;
orde  from   deta
  -  
0 0  TABLE foo

It would be an interesting attempt to try efficiently patching SQLite
to recognize this situation and read data directly out of the index.


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[Stephen Oberholtzer]

> If you make PRAGMA freelist_count writable as you suggest, I would
> expect the database to reserve space once, and not use a larger pice of
> the disk every time ist has to be expanded in the future.
>
> Martin


To throw in my $0.02, I would suggest a *different* name for the
pragma, something more like

PRAGMA reserve_space(N) where N is in pages or bytes or whatever.

That could call whatever functions are needed to force the database to
grow such that the minimum number of free pages/bytes in the database
is N.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partial index?

[Stephen Oberholtzer]

On Thu, Aug 19, 2010 at 5:53 PM, Kees Nuyt  wrote:
> On Thu, 19 Aug 2010 17:39:14 -0400, Eric Smith
>  wrote:
>
>>Am I missing something?
>
> You could add a "deleted" column with value range (0,1) and
> create an index on it if benchmarks show that makes it
> faster. As a bonus it is easier to code and maintain than a
> separate table with references and triggers.
>
> Alternatively, you can create an composite index with the
> "deleted" column as one of the components.
>
> From a theoretical view, if you care about the visibility of
> a row, you should express it as an attribute of the entity.
> The solutions above comply with that notion.
> --
>  (  Kees Nuyt

I think you've missed the point. I believe what he's getting at is this:

>> CREATE INDEX foo ON bar (to_be_deleted) <<

Imagine if he had 100 million rows in his table, and 100 of them were
marked "to_be_deleted".
His index will have 100 million rows, probably 500MB or 900MB (not
sure if rowid is 32- or 64-bit), consisting of 99,999,900 "0"s and 100
"1"s.

If he could create what MSSQL calls a "filtered index", using a syntax
like this:

>> CREATE INDEX foo_filtered ON bar (to_be_deleted) WHERE to_be_deleted = 1 <<

he could speed up the statement

>> DELETE FROM bar WHERE to_be_deleted = 1 <<

using that index, just like he could with the unfiltered "foo" index.
The only difference is that where foo has 100 million rows,
foo_filtered only contains 100 rows, taking up only 500-900 bytes
(thus actually having like 300% overhead due to page sizes!)


Now, in order to implement this, the following changes would have to be made:

1. Conditional logic would have to be generated inside the VDBE
programs for INSERT statements.  This is pretty straightforward.

2. Conditional logic would have to be generated inside the VDBE
programs for UPDATE statements. Care must be taken to make sure that
the index is updated properly when the column(s) referenced in the
WHERE clause are updated, but other than that, it's probably pretty
straightforward.

3. Depending on how the IdxDelete operator handles "key not found in
index" errors, the VDBE code generated for DELETE statements may also
need to be updated.

4. The statement parser needs to be modified to parse this syntax.

5. The schema parser needs to be modified to decode this syntax.

6. The optimizer needs to flatten and check that every possible branch
of the WHERE clause on a SELECT/DML statement is compatible with the
WHERE clause of the index, before it can use that index.

Now, I personally could do #1-3, because they're pretty easy.
I could probably even manage #4 and #5 if I spent a week familiarizing
myself with the code.
But #6, as far as I can tell, is a LOT harder.  Consider the following examples:

create index ix1 on Bar (baz) where quux between 30 and 95;

select * from baz where quux = 35; -- Index is viable
select * from baz where quux between 31 and 94; -- Index is viable
select * from baz where quux = 38 or quux between 80 and 90; -- Index is viable
select * from baz where quux in (40,50,60,70); -- again, index is viable
select * from baz where quux between 25 and 35; -- index is NOT viable
select * from baz where quux = 38 or baz = 5; -- index is NOT viable



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL asymetry between CREATE TABLE and ALTER TABLE, why?

[Stephen Oberholtzer]

The difference is that when you issue an ALTER TABLE, the table already exists.

When you create a new table, it is known that there are no rows in it.
 But you can issue an ALTER TABLE to a table with rows in it. If so,
what value should you put for the existing rows?

Granted, in this specific case, there *are* no rows, so it's
theoretically possible. But SQLite won't accept it. If you really want
to add the column, you can just drop and recreate the table.

On Tue, Aug 17, 2010 at 10:23 AM, Michael Schlenker  wrote:
> Hi all,
>
> Have a look at the following short sqlite shell session:
>
> SQLite version 3.6.4
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table foo (a text(5) NOT NULL);
> sqlite> alter table foo add column b text(5) NOT NULL;
> SQL error: Cannot add a NOT NULL column with default value NULL
>
> Is there a reason for this asymetric behaviour of ALTER TABLE and CREATE
> TABLE?
>
> Its a bit dated version, but current version has doc'ed the limitation
> that for ALTER TABLE still.
>
> Michael
>
> --
> Michael Schlenker
> Software Architect
>
> CONTACT Software GmbH           Tel.:   +49 (421) 20153-80
> Wiener Straße 1-3               Fax:    +49 (421) 20153-41
> 28359 Bremen
> http://www.contact.de/          E-Mail: m...@contact.de
>
> Sitz der Gesellschaft: Bremen
> Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
> Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY and NULL values

[Stephen Oberholtzer]

select * from MyTable
order by case when b is null then 0 else 1 end, b

On Sun, Apr 19, 2009 at 3:53 PM, January Weiner wrote:

> Hello,
>
> default sorting with ORDER BY puts the NULL values at the beginning.
> How can I change this behaviour? I.e., instead of
>
> a   b
> -   
> x  NULL
> y  0
> z  2
>
> I would like to have
>
> a  b
> 
> y  0
> z  2
> x  NULL
>
> Explanation: I have a table with e-values (expected number of
> something). A lower e-value means a more significant search hit. No
> evalue at all means that hit was not found, so that the missing
> e-values should be reported at the end (unfortunately, I want to have
> them reported, but just not at the beginning).
>
> Thank you in advance,
>
> j.
>
> --
> -Dr. January Weiner 3  -+---
> Inst. of Bioinformatics, UKM, Univ. of Muenster | Von-Esmarch-str. 54
> (+49) (251) 83 53002| D48149 Münster
> http://www.compgen.uni-muenster.de/ | Germany
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selecting top entry from each day

[Stephen Oberholtzer]

On Sun, Nov 9, 2008 at 1:40 PM, Tejas <[EMAIL PROTECTED]> wrote:

> Hi all,
>
> I have a table 'stats' with following schema
> date varchar (25), utilization int, os_name varchar(25) ...
>
> I am recording host utilization for windows and linux operating systems
> (os_names) at various times in a day in this table. The date column has a
> format of -mm-dd.
>
> I want to do the following:
> 1. select top utilization for each day for all days recorded so far.
> 2. display a table with columns: date, utilization_linux,
> utilization_windows from the recorded data.
>
> any idea how to structure a query?
> for item 1 I am using the following query but it gives me only one row of
> max utilization so far.
> "select date, max(utilization) from stats where os='win2k3' and date in
> (select distinct date from stats ) order by date;"


If I understand you correctly, what you want can be provided by GROUP BY:


select date, max(utilization) from stats where os='win2k3' group by date



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intermittent SQLITE_CANTOPEN on Windows

[Stephen Oberholtzer]

On Mon, Oct 13, 2008 at 12:52 PM, Doug <[EMAIL PROTECTED]> wrote:

> I'm using SQLite 3.5.6 on Windows and intermittently get SQLITE_CANTOPEN
> when doing an insert.  When that fails, I can use the debugger to go back
> up
> and step through the same lines again (using the same database handle -
> nothing opened or closed in between) and it will work.
>


We see this a lot when people have an antivirus that autoscans files after
they've been modified.  Is that possibly the case?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Between And statement too much slow

[Stephen Oberholtzer]

On Fri, Sep 19, 2008 at 12:36 PM, Giuseppe Costanzi <[EMAIL PROTECTED]>wrote:

>
> How could improve my code?


Go into the sqlite3 command line and issue

EXPLAIN QUERY PLAN 

You'll have to fill in some values for the ?s, but that'll give you some
hints.


 Also: How long is "too slow"? Several seconds?


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bigger table and query optimization

[Stephen Oberholtzer]

On Tue, Sep 9, 2008 at 10:18 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> "Stephen Oberholtzer"
> <[EMAIL PROTECTED]> wrote in
> message
> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> > Idea: Submit a patch that allows LIKE expressions that start with a
> > fixed
> > string (i.e. don't start with '%') to use the index to improve
> > performance. (SQL Server 2000 does this.)
>
> http://www.sqlite.org/optoverview.html
> 4.0 The LIKE optimization
>
> Igor Tandetnik
>
>
"For the LIKE operator, if case_sensitive_like mode is enabled then the
column must use the default BINARY collating sequence, or if
case_sensitive_like mode is disabled then the column must use the built-in
NOCASE collating sequence."

There's a flaw in this design -- which explains why, when I actually tested
it, the index wasn't used:

CREATE TABLE foo (id integer primary key, name text, name_back text);
INSERT INTO "foo" VALUES(1,'one','eno');
CREATE INDEX name_back_IX2 on foo(name_back collate nocase);

pragma case_sensitive_like=off;
explain query plan select name_back from foo where name_back like 'e%';
order   fromdetail
--  --  --
0   0   TABLE foo


The index "name_back_IX2" is collated NOCASE, which is the same collation
that LIKE uses -- but the index doesn't get used!

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bigger table and query optimization

[Stephen Oberholtzer]

On Tue, Sep 9, 2008 at 9:04 AM, Bruno Moreira Guedes <[EMAIL PROTECTED]>wrote:

> 2008/9/9 P Kishor <[EMAIL PROTECTED]>:
> > On 9/9/08, Bruno Moreira Guedes <[EMAIL PROTECTED]> wrote:
> >> Hi people!!
> >>
> >>  I'm getting problems with a bigger table. I'm doing a query like this:
> >>
> >>  SELECT field FROM tables WHERE '%.' || somefield LIKE 'somestring'
> >
> > interesting variation on the syntax. Usually one would
> >
> > WHERE somefield LIKE '%.somestring'
> >
> >
>
> Yes... I need to get the rows where 'something' ENDS wifh
> '.[field-value]'. But I really think this solution isn't very well.


Store the field *twice* -- once normally, and once *backwards*.
Put an index on the backwards column, and when searching, do:
somefield_backwards >= 'gnitset' and somefield_backwards < 'gnitseu'
(Note that I advanced the last character from 't' to 'u' and used < instead
of <=)

This will enable SQLite to use the index on the backwards column to
efficiently find everything.

Idea: Submit a patch that allows LIKE expressions that start with a fixed
string (i.e. don't start with '%') to use the index to improve performance.
(SQL Server 2000 does this.)

Idea 2: Submit a patch adding a built-in 'reverse' function to assist in
this.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting using random rowids

[Stephen Oberholtzer]

On Sun, Aug 24, 2008 at 6:18 PM, Susan Ottwell <[EMAIL PROTECTED]>wrote:

> How would one insert rows randomly within a range of 1000 rows? I can
> easily enough do this in the script that calls the insert function,
> but it would be more efficient if I could use an sqlite function or
> feature to do this.
>
> sottwell


There are a few ways to accomplish this, based on your particular definition
of "random":

1. Unpredictable.  This is where things get cryptographic.

2. Evenly distributed.

3. "Not obviously sequential".  i.e. you don't really need unpredictable or
even distribution, but you don't want your row IDs to go  (1,2,3,4...)

Anything that satisfies #2 will satisfy #3; anything that satisfies #1 will
satisfy #2 (and by induction, #3).

#1 is technically impossible, although it's possible to get fairly close.
If you're on a *nix box, read 2-4 bytes out of  /dev/random and treat them
as an integer.

#2 can be provided by a Mersenne Twister; most standard libraries' rand()
functions are implemented using one.

These two, while providing some semblance of randomness, have the
disadvantage that you need to pick a number, then check to see if that
number has already been used by another row.  Thus I present a third option:

#3 can be achieved through an LFSR (Linear Feedback Shift Register). A
maximal n-bit LFSR will go through every numbers from 1 to (2**n)-1 without
repeating, but do it in a seemingly random order.
It's great if you want to e.g. generate what *looks* like a list of account
numbers for a mock-up report/screenshot.


Now, with that said, random rowids means poor locality of reference for
newly inserted rows.  This means cache misses and reduced performance.  Why,
exactly, do you want to randomize the rowid?


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL statement to get min/max values

[Stephen Oberholtzer]

On Thu, Aug 14, 2008 at 9:45 PM, Dennis Volodomanov <
[EMAIL PROTECTED]> wrote:

> > Seems to work ok for me. What values were you expecting?
>
> Yes, that works. Bad example on my part, sorry.
>
> What doesn't work is this:
>
> 1|2|-7
> 2|2|-5
> 3|2|-20
> 4|2|-5
> 5|2|-2
>
> SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2);
>
> This returns a -5, while I'm expecting a -2.
>
> Thank you,


What version of SQLite are you using? I'm using the  3.5.7 version that came
with OS X 10.5, and I get -2 as expected.

Also, what's with the superfluous subquery?  Why not just say

SELECT max(Data) FROM test_table WHERE ExternalID=2;

You can even do min and max at the same time:

SELECT min(Data), max(Data) FROM test_table WHERE ExternalID=2;

Or get real fancy:

create view test_stats as select ExternalId, max(Data) as maxData, min(Data)
as minData, avg(Data) as avgData from test_table group by ExternalId



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System function with Sqlite

[Stephen Oberholtzer]

What does strace reveal?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: malformed database schema - near ")"

[Stephen Oberholtzer]

On Fri, Aug 8, 2008 at 5:21 PM, Mark Easton <[EMAIL PROTECTED]> wrote:

> Hi,
>
> We are using SQLite version 3.3.8 on Debian linux. Every now and again we
> are getting corrupted databases. We suspect it is because the computer is
> being turned off mid database operation.
>
> We have given full permissions to the folder the database is in and to the
> database itself.
>
> The most important question - is how can I recover this database? When I
> type in  "pragama integrity_check" it still returns the error "Error:
> malformed database schema - near ")"".


I seem to recall someone else having a similar problem, once.

Can you open the database and do "select * from sqlite_master;", then sanity
check the "sql" column for syntax errors?

Several months ago, someone had managed to create a bunch of tables with an
extra "," at the end due to a parser bug, and when they upgraded the new
parser rejected the old schema's SQL.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Stephen Oberholtzer]

On Thu, Aug 7, 2008 at 1:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> String literals in SQL are suppose to be enclosed in single-quotes -
> Pascal-style.  Double-quotes are used around table and/or column names
> in cases where the name would otherwise be a keyword or when the name
> contains non-standard characters.



>3.  Double-quoted names fall back to being string literals if
> there is no matching table or column name.
>
> In retrospect, (3) seems to be a bad idea.  It is accident-prone and



The first time I saw an issue related to this on the mailing list, I thought
that it should be fixed somehow.

The main reason it's been kept was for backward compatibility reasons. I
believe that one of the more recent proposed solutions was to make it a
PRAGMA or something, which was struck down because it would only make the
already-complex parser code *more* complicated.

Is there some way to have SQLite's parser take note of when condition #3
results in a literal, and output a warning or something?  Something like

fprintf(stderr, "Warning: The query ''%s' has an invalid table/column name
'%s' delimited by double quotes (\"). I am assuming it should have been a
string literal delimited by single quotes ('). This behavior will be removed
in the future; please update your SQL statements.");

Or perhaps, to be shorter,

fprintf(stderr, "Warning: The query ''%s' has an invalid table/column name
'%s' delimited by double quotes (\"). See
http://www.sqlite.org/blah/blah/blah.;);

Warnings could be allowed for "properly" by e.g. having a callback
registered, or adding a new void(*xWarning)(char*str) entry in the VFS.



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Loading a existing database 100% into memory

[Stephen Oberholtzer]

On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:

> Good afternoon list,
>
> I would like to load my current database file completely into memory,
> mostly as an experiment to check SQLite's maximum memory footprint,
> however searching through the documentation I can only find references
> about how to create new databases that are completely memory resident.
> Is there a way to do this?  I'm currently using the SQlite console
> application for my testing if that makes a difference.


What, exactly, is it you're after?  I can load a SQLite database 100% into
memory quite quickly:

int fd = open("sqlitedb.dat");
struct stat info;
fstat(fd, );
char *buf = malloc(info.st_size);
read(fd, buf, info.st_size);

I find it extremely unlikely that this is what you want (although it might
be an interesting academic exercise to make a VFS port of SQLite that uses
memory arrays for read/write ops.)

At the other end of the spectrum, you could just dump the entire database on
disk and then insert all the data into a :memory: database.  However, this
doesn't seem like it would be very useful, either.

This sounds like an XY problem.  What are you really trying to accomplish?
What constraints are preventing you from simply using an on-disk database?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Realtime Backup of Database

[Stephen Oberholtzer]

On Tue, Aug 5, 2008 at 4:36 PM, Till Steinbach
<[EMAIL PROTECTED]> wrote:
> Hi Ingo!
> Although I'm limited to C-Code your code would be really useful for
> me. The triggers will be the same for me. When I have the right
> triggers the most difficult part is done. The idea with the seperate
> table for logging is great. I have no idea yet how to log whole
> statements. I'm looking forward to see your code.
>
> greetings Till

Side note: I once looked into using rsync to reduce remote firmware
update times for an embedded device over a slow link, and I found just
what you were finding -- rsync's overhead is HUGE unless you're
dealing with tens or hundreds of megabytes.

That said, these devices can also be configured remotely, and they can
also request a refresh of all their configuration settings in the
event of a problem.
The way I accomplished this is by giving each setting a "dirty" flag.
When the setting is changed for any reason, the "dirty" flag is set.
When the device reports in (so I know that it is still working), it
checks for any "dirty" settings and includes them in the report.  When
the server they report to receives and stores those settings, it sends
back a response indicating such. Upon receiving that response, the
device clears the "dirty" flag for all settings.

This scenario works fine so long as it is impossible for a setting to
be changed while the device is reporting in.  This is possible for my
devices, but it may not be for yours.  If that is the case, then a
more sophisticated solution will do the job:

First, create a table called "generation":

create table generation (
   id int AUTOINCREMENT not null primary key, -- the autoincrement is
kind of important here
   date date not null default(current_timestamp),
   reported int not null
)

Then, when a configuration row (or other row that needs to be tracked)
is to be inserted/changed, do the following steps:

1. Get the max(id) from generation where reported=0.
2. If that's null, insert a new row into generation with reported=0
and get the new row ID
3. Insert/update the relevant row, including generationId=

When the device needs to report in:

1. If the 'generation' table is empty, there is nothing to do. Stop now.
2. Select the maximum generation ID from the 'generation' table.  We
will call this generation G.
3. Mark every generation with ID <= G.ID as reported.
4. Report in, including all rows with generation.Id <= G.ID
5. If the server confirms receipt of the data, delete all rows from
generation where generation.Id <= G.ID

That *should* make sure that no row gets missed, but I'd feel better
if somebody else could sanity check and confirm.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ignoring "The"

[Stephen Oberholtzer]

On Wed, Jul 23, 2008 at 9:11 AM, Andrew Gatt <[EMAIL PROTECTED]> wrote:

> I have a table of music artist names which i'd like to output in order.
> Normally i just use:
>
> select * from artists order by artist_name;
>
> What i'd really like to do is order the artists by name but ignore any
> "the" or "the," preceding it.
>

iTunes and iPods solve this problem by having two of each field: Title /
Sort Title, Artist / Sort Artist, Album / Sort Album.  The "Sort" versions
are populated by stripping things like "The" and whatnot.   You can keep
them in sync with triggers, or application logic, or whatever.



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bad UPDATE Problems in Mobile6 device

[Stephen Oberholtzer]

On Mon, Jul 7, 2008 at 7:26 AM, Bob Dennis <[EMAIL PROTECTED]> wrote:

> Hi
> I am getting odd results with a simple update query in a Mobile 6 device.
> It only works sometimes , but never reports an error.
> The same code works fine in PocketPC and Mobile5 devices.
>
>  UPDATE 'CommentList' SET xFlag = 3
>
> I wondered if anyone else has seen this, and why would a query not work
> without giving an error.
> This leaves me very worried.
>
> Any ideas greatfully received.


Well, you shouldn't be putting your table names in single-quotes.
Double-quotes are okay, although even they're not necessary in this case


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DDL statements in transactions

[Stephen Oberholtzer]

On Wed, Jun 11, 2008 at 3:55 PM, Robert Lehr <[EMAIL PROTECTED]> wrote:

> Oracle does not allow DDL statements to be executed in transactions,
> i.e., it does but the statements are COMMITted as they are executed,
> thus cannot be rolled back.
>
> PostgreSQL does allow DDL statements to be executed in transactions,
> i.e., if a DDL query fails then then entire transaction is rolled back
> and no tables, indices, etc., are created, modified, etc.
>
> Which behaviour is implemented in SQLite?
>
> -rlehr
> Robert Lehr


Let's find out!

C:\temp>sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table foo (id integer, name text);
sqlite> begin immediate;
sqlite> alter table foo add number text;
sqlite> .schema
CREATE TABLE foo (id integer, name text, number text);
sqlite> rollback;
sqlite> .schema
CREATE TABLE foo (id integer, name text);


sqlite> begin immediate;
sqlite> alter table foo add number text;
sqlite> insert into foo (name, number) values ('Jenny', '867-5309');
sqlite> alter table foo add number2 integer not null;
SQL error: Cannot add a NOT NULL column with default value NULL
sqlite> .schema
CREATE TABLE foo (id integer, name text, number text);
sqlite> select * from foo;
|Jenny|867-5309
sqlite> rollback;
sqlite> select * from foo;
sqlite> .schema
CREATE TABLE foo (id integer, name text);
sqlite>


So: first off, at least *some* DDL statements can be performed inside of a
transaction.
Second off, at least *some* failed DDL statements do *not* automatically
roll back the transaction.

Based on the simplistic locking mechanisms used by SQLite, I'm betting that
*any* DDL statement can be done inside a transaction.
Based on http://www.sqlite.org/c3ref/get_autocommit.html, your code should
call get_autocommit() to verify whether or not the transaction has been
rolled back after a DDL statement fails.




-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practices

[Stephen Oberholtzer]

On Tue, Jun 10, 2008 at 1:29 PM, A. H. Ongun <[EMAIL PROTECTED]> wrote:

> We have  an application requirement for the following:
> 1) Maintain an in memory database of real time data.  The main table mimics
> a Modbus register address scheme and once the table is created and records
> are inserted to create the table at startup, all writes are updates for the
> state machine. (app. 1000 records).  Reads are through multiple Modbus
> TCP/IP protocol based devices which result in selects of up to 100
> contiguous records at a time.
> 2) At periodic intervals based on events and time intervals a disk (SD
> card) database gets updated (inserts).  This contains system parameters, and
> various different tables.
> 3)  Occasionally the whole database on disk is written to an external media
> (USB) for backup purposes.
> 4)  Through a web interface user(s) can export portions of a table by
> specifying type of data (table) and start and end time in csv format. (cgi
> scripts).
> All of the above is implemented currently using a "custom" format, and is
> really a pain to expand and modify.
> Items 1 & 2 are fairly straight forward to implement, they would be in the
> same executable as well.
> My original thought was to use the sqlite3 shell interface to accomplish
> 4.  I am concerned a bit about items 3 and 4 though in regards to
> performance issues in that I can not afford not to service data requests
> that could come several times a second if database could be locked during
> operation of 3 and 4.  Size of the database can grow to 1-2.4GB.
> I am open to any suggestions.
> Thanks in advance.
> andy



Okay, my first question is: What OS is this device running? If you need
realtime semantics, you really need an RTOS.  If the answer contains the
string "Windows" you're in serious trouble, right from the get-go.

You mention two databases (#1 has a modbus database, then #2 has a config
database), then #3 mentions a "whole database".  Which one are you referring
to?

I know nothing about Modbus, but you mention both reading and writing, and
that the reading is being done in bulk by remote devices.  How often does
this happen? How often are new rows inserted into this Modbus database?


My experience with USB memory sticks is: They're SLOW.  A typical desktop
hard drive does about 30MB/sec, which would require 80 seconds to fully
write out a database.  A USB memory stick will be maybe a tenth of that.
That's over ten minutes to write the whole database out to the USB drive.


Based on the limited information I have, the best advice I can give is this:

If the rate of inserts is very low (less than one per second), it may be
best to maintain two sets of databases: the 'live' ones (modbus in memory
and config on SD) and 'backup' ones (on USB stick).

When you need to do an insert/update, take all of the data needed for the
modification, stuff it into a structure, and stick that structure on a
spooling queue.  Then, all you need to do is have a thread that pulls items
off the spooling queue and applies them to the USB stick's version of the
database.

* Inserts to the 'live' database aren't blocked by the slow USB stick
* People doing reporting can report from the USB stick version.   Inserts
that come in while the reporting thread has a shared lock will just pile up
in the spool, then they can all get processed when the report is done.
* Your external backup will probably actually get updated more often.


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



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is quicker?

[Stephen Oberholtzer]

One of the things that people fail to understand is that floating point
numbers are stored in *binary*.  In fact, I bet a number of people who
understand the exact binary formatting of integers don't understand that the
technique translates pretty much directly into floating point: a floating
point number is recorded like 1.0111001010101110101

So when they hear "floating point numbers are inaccurate because of rounding
errors", they often think "Oh, that doesn't apply to me -- I'm not doing
anything with enough decimal places to hit a rounding error."  This exact
sort of problem cropped up in a financial transaction processing system I
helped maintain.

Problem: Sometimes, members's transactions would be declined even though
they had enough to cover the transaction.

This was narrowed down to

Specific problem: Member X is unable to make a purchase for $1.40 cents
despite them having 40 cents in their account and a $1.00-off coupon.

I looked at the code in question and announced it was a rounding error due
to the use of a 'double' for storing currency values.  "How is that
possible? It's only two decimal places, I've seen these things work for half
a dozen decimal places!" I was asked.

So I demonstrated what happened:  The application's test was (total amount)
- (balance) <= (coupon).  Or something like that.  It was years ago; all I
remember is that the three numbers were $1.40, $1.00, and $0.40.  So I
translated everything into the internal binary form:

Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
1.1001100110011001100110011001100110011001100110011010 * (2**-2)

To add these together, they need to be adjusted to the same exponent:

Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
0.011001100110011001100110011001100110011001100110011010
* (2**0)

This is where things go wrong.  You see that extra '10' at the end of the
member's balance?  The floating point process doesn't have room for it, so
it rounds.  And much the same way as 0.5 rounds up to 1.0, so does binary
0.1:

Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
0.0110011001100110011001100110011001100110011001100111
* (2**0)

Now we subtract:


  1.0110011001100110011001100110011001100110011001100110
- 0.0110011001100110011001100110011001100110011001100111

  0.

This is *practically* 1, in much the same way as
0.99 is *practically* 1.  But it's still
technically less than 1.  So when the application compared it to the coupon
amount, or whichever it was, the rounding error caused a false failure and
the transaction was declined.

Things are easier to understand if you realize that for any fraction (P/Q),
if Q is not exactly a power of 2, then the answer cannot be exactly
represented in binary.  In contrast, for our decimal system, any fraction
(P/Q) cannot be represented exactly unless Q can be expressed as (some power
of 2)*(some power of 5).

For your edification, I wrote a Perl script to tell how many Qs offer exact
representations in bases 2, 10, and 60.  These are the results:

Bin: 25 of 16777216 (0.00015%)
Decimal: 143 of 16777216 (0.00085%)
Base60: 836 of 16777216 (0.00498%)

This roughly indicates that if you have a number that can be expressed
exactly in decimal, there's only about a 1-in-6 chance that it's *also*
expressible exactly in binary without running into rounding errors. I also
threw in base 60 for comparison -- an arbitrary number is nearly 6 times as
likely to be expressible exactly using base-60 than it is in base-10.  GPS
coordinates are expressed using base-60 (degrees, minutes, seconds).

== script  ==
#!/usr/bin/perl
my ($b,$d,$b60) = (0,0,0);
my $max = 16_777_216;
for (1..$max) {
  my $q = $_;
  while ( ($q % 2) == 0 ) { $q /= 2; }
  if ($q == 1) { $b++; }
  while ( ($q % 5) == 0) { $q /= 5; }
  if ($q == 1) { $d++; }
  while ( ($q % 3) == 0) { $q /= 3; }
  if ($q == 1) { $b60++; }
}
printf "%s: %d of $max (%.5f%%)\n", @$_, 100*$_->[1]/$max
  for [Bin=>$b],[Decimal=>$d],[Base60=>$b60];
#!/usr/bin/perl
my ($b,$d,$b60) = (0,0,0);
my $max = 16_777_216;
for (1..$max) {
  my $q = $_;
  while ( ($q % 2) == 0 ) { $q /= 2; }
  if ($q == 1) { $b++; }
  while ( ($q % 5) == 0) { $q /= 5; }
  if ($q == 1) { $d++; }
  while ( ($q % 3) == 0) { $q /= 3; }
  if ($q == 1) { $b60++; }
}
printf "%s: %d of $max (%.5f%%)\n", @$_, 100*$_->[1]/$max
  for [Bin=>$b],[Decimal=>$d],[Base60=>$b60];
== snip ==

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Stephen Oberholtzer]

On Wed, Jun 4, 2008 at 7:12 AM, Christophe Leske <[EMAIL PROTECTED]> wrote:

> Wilson, Ron P schrieb:
> > I'm not a guru yet, but I think you are not using the latlon index in
> > your query.  Perhaps if you index on lat and lon separately your query
> > will use those indices.  I think the lines below indicate using the
> > indices on class_dds and rowid.
> >
>
> Thanks also for the tiling hint, but my application is already written,
> and I have to stick to the databse given. I might reconsider though if
> the performance is still bad.
>

Does that mean you can't change the application at all?

Remember, the tiling (I call it "blockmap" since that's what Doom called it)
method doesn't require changing your existing table layout; it only requires
adding new tables.  Furthermore, the contents of these new tables can be
computed from the existing data in the Cities table -- all your application
would need to do is check for the existence of those tables, and if it
doesn't find them, create them and populate them.  It'd be an expensive
initial setup, but you only have to pay it once.

Also recall that SQLite supports multiple databases:

attach 'blockmap.db' as bm;
-- the following two tables are created in the blockmap.db file because they
have the 'bm.' qualifier
create table bm.blockmap (id integer primary key, lat real, long real);
create table bm.blockmapCity (blockmapId integer not null, cityId integer
not null);

-- but you don't need to use the 'bm.' qualifier, *unless* more than one of
the attached databases has a blockmapcity
-- by the way: tmp_blockfilter is a temporary table containing blockmap
IDs.  SQLite won't use an index for an IN clause, but it will use one if
you're joining against another table.
select c.* from Cities c join blockmapCity bc on bc.cityId=c.id join
tmp_blockfilter tb on tb.blockmapId = bc.blockmapId;

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bind arguments for insert and not null columns with default values

[Stephen Oberholtzer]

On Tue, Jun 3, 2008 at 5:09 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> It would be nice to be able to revert back to the default value for a
> column. I don't think SQLite support this right now.
> The closest thing I found is "pragma table_info(foo)". If you prepare this
> and then grab the dflt_value for your column.
> On Tue, May 20, 2008 at 2:33 PM, Jeff Hamilton <[EMAIL PROTECTED]> wrote:
>

I've been following this discussion and here's my $0.02: there are three
ways I can imagine going with this.

1.  Extend the API with a "sqlite_bind_default" function, and then modify
the VDBE to inject instructions that are the equivalent of "if
(value==usedefault) value=default" into INSERT statements

2.  Extend the API with a "sqlite_bind_default" function, which somehow does
this 'pragma table-Info(foo)'  and binds *that* value.

3.  Don't support it.  If people want to allow default values they have to
prepare multiple versions of INSERT statements.

#2 wouldn't actually work 100% without being insanely complicated; consider
columns with a default value derived from CURRENT_TIMESTAMP.  This leaves #1
and #3.

#1 is appealing because it enables certain scenarios a *lot* simpler to
maintain, but it also means that *every single application* has to pay the
penalty, in memory and extra CPU cycles, to allow them to specify defaults,
even if they have absolutely no intention of doing so.   There's something
about that on the SQLite site, but I can't find it... it boils down to "If
we implement this feature and document it, DRH has to support it *forever
after*."


I offer you an option #4 that came to me while I was writing this email:

CREATE TABLE Foo(fooName text, fooDate date default(current_timestamp),
fooLevel integer default(42));

INSERT INTO Foo (fooName, fooDate, fooLevel) values (?, ifnull(?,
current_timestamp), ifnull(?, 42));

If you want to get really fancy you can construct the above statement using
Alex's suggested "pragma table_info(Foo)" in order to find out what the
defaults are when you're preparing the query.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Stephen Oberholtzer]

On Tue, Jun 3, 2008 at 1:27 PM, Christophe Leske <[EMAIL PROTECTED]> wrote:

> Hi,
>
> i am a new member of this list and interested in speeding up my sqlite
> queries.
>
> I am using SQlite in a 3d environment which is close to Google Earth or
> Nasa WorldWind.
>
> We have a city database that is being queried regurlarly depending on
> the lat/long position of the viewport in order to show city names and
> labels.




>From what I gather,  you've got something very similar to the 2D-spatial
problem:

"I need to find cities that are within R miles of (X,Y)."

This translates as

"I need to find objects that are within the circle with origin (X, Y) and
radius=R".

This is fundamentally a collision-detection algorithm, and I have a
suggestion that might help, based on the way old DOS game Doom optimized its
collision detection code.

Here's the setup: You divide the world into equally-sized blocks of width W
and height H. Let's say, for the sake of argument, that W=1' and H=1' (this
is huge, but it helps illustrate the point)
Since the world is 180 degrees north-to-south and 360 degrees around the
equator, this gives 64,800 blocks.  So, for example:

CREATE TABLE blockmap (id integer not null primary key, lat real, long
real);

Then you need to build a correspondence table:

CREATE TABLE blockmapCity(blockmapId, cityId);

A naive implementation might only mark a city's center, while a more
advanced version might get fancy and have an approximate size of the city
and place it in multiple blockmaps, in case it was big enough to spill over
into adjacent blocks.

What you do then, in order to do a lookup, is to find all of the blocks that
intersect with your circle.  This can be done easily with the right math.
Then, once you've figured out which blocks to include, you just filter out
the relevant cities from blockmapCity.  Once you have *those* cities you can
filter them out as precisely as you were doing before.

Some notes:

-- Even if you only go down to 1'-by-1' granularity, you've divided the
world into 64,800 blocks.  Assuming that your 840K cities are all over the
globe, and that about 70% of Earth is covered by water, that means that only
about 20,000 blocks would actually have cities in them.  But with 840K
cities, that means you're only considering about 42 cities for a single
block.
-- The algorithm used to prune down the set of blocks to include doesn't
need to be perfect. Remember, this is all an optimization; even if you
return every blockmap in the same *hemisphere*, you'd still be searching
through only 420K cities instead of 840K!


If you need any more help implementing something like this, go ahead and
reply to the list.
If you can provide a concrete set of data (for example, all or most or at
least a significant number of the cities in the US) I can help put together
a more concrete example.



>
> Plus, there are additional databases for special features, like natural
> hazards and catastrophies.
>
> The city database has around 840.000 records,  the following schema and
> weights currently short under 40Mb:
>
> sqlite> .schema cities
> CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS
> NUMERIC, LONGI
> TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC);
> CREATE INDEX class ON Cities(CLASS_DDS ASC);
> CREATE INDEX latlon on Cities(latitude_dds,longitude_dds);
>
> My questions are:
>
> - how do I speed up the queries? For small lat/long windows, and high
> classes for the cities, i get long query times (e.g. about 600ms)
> Is this reasonable to ask for, or IS that already a top speed for this
> kind of query?
>
> - I have indexed latitude AND longitude,as you can see above. Is this ok?
>
> - I came across the EXLPAIN command, and have read an email by someone
> on this list on how to analyze my queries. I should probably do that,
> yet i am unfamiliar with reading the output of the Explain command.
>
> Thanks for your time and eventual help,
>
> --
> Christophe Leske
>
> www.multimedial.de - [EMAIL PROTECTED]
> http://www.linkedin.com/in/multimedial
> Lessingstr. 5 - 40227 Duesseldorf - Germany
> 0211 261 32 12 - 0177 249 70 31
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multitable query question

[Stephen Oberholtzer]

On Thu, May 29, 2008 at 1:09 AM, <[EMAIL PROTECTED]> wrote:

> I've implemented this behaviour in my program. I was just curious whether
> it was possible in sql when I learnt about the Left Join (as there are more
> values in one column than the other).
>
> I guess it isn't or is not really the proper use of the database. Thanks
> very much for the prompt replies though.
> I'll also have to sort out the date entry bit.


Ah, okay :) Cool.

Just for the  heck  of it, I decided to attempt what you were going for.

I do not, repeat, I do NOT recommend using this technique.  I'm just proving
what SQLite is capable of if you put a sufficiently twisted mind towards it
:)


CREATE TABLE Calls (id integer primary key, houseId integer, date date);
CREATE TABLE Letters (id integer primary key, houseId integer, date date);
CREATE TABLE callSeq (sequence integer primary key, date date);
CREATE TABLE letterSeq (sequence integer primary key, date date);
CREATE INDEX Calls_HouseId on Calls(houseId);
CREATE INDEX Letters_HouseId on Letters(houseId);
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE Calls (id integer primary key, houseId integer, date date);
INSERT INTO "Calls" VALUES(1,1,'2008-05-15');
INSERT INTO "Calls" VALUES(9,1,'2008-05-28');
INSERT INTO "Calls" VALUES(10,1,'2008-05-28');
INSERT INTO "Calls" VALUES(24,16,'2008-05-15');
INSERT INTO "Calls" VALUES(27,16,'2008-05-15');
INSERT INTO "Calls" VALUES(31,16,'2008-05-15');
CREATE TABLE Letters (id integer primary key, houseId integer, date date);
INSERT INTO "Letters" VALUES(1,16,'2008-05-26');
INSERT INTO "Letters" VALUES(3,16,'2008-05-27');
INSERT INTO "Letters" VALUES(4,16,'2008-05-28');
INSERT INTO "Letters" VALUES(7,16,'2008-05-16');
CREATE INDEX Calls_HouseId on Calls(houseId);
CREATE INDEX Letters_HouseId on Letters(houseId);
COMMIT;


And here's the magic:

create temporary table letterSeq (sequence integer primary key, date date);
create temporary table callSeq (sequence integer primary key, date date);

insert into letterSeq (date) select date from Letters where houseId=16 order
by date desc;
insert into callSeq (date) select date from Calls where houseId=16 order by
date desc;
insert into callSeq (date) select NULL from Letters where (select count(*)
from callSeq) < (select count(*) from letterSeq);

select l.date as LetterDate, c.date as CallDate
from
callSeq c
join letterSeq l on c.sequence = l.sequence
order by c.sequence;


Let the IOSQC (International Obfuscated SQLite Querying Contest) begin!

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multitable query question

[Stephen Oberholtzer]

On Wed, May 28, 2008 at 11:15 PM, beejayzed <[EMAIL PROTECTED]> wrote:

>
> I have two tables, one which has each visit(call) on a house logged,
> another
> which has each letter sent to a house logged.
> I would like to create a query which has two columns, Letters.Date and
> Calls.Date, for a specific HouseID.
>
> So how do I get the desired result?
> This would be:
> 26/5/08 15/5/08
> 27/5/08 15/5/08
> 28/5/08 15/5/08
> 16/5/08
>
>
Before anything else: Use ISO date formats, e.g. 2008-05-26 instead of
26/5/08. Not only is it reasonably unambiguous (e.g. to me, 12/5/08 is
December 5th), but when you sort them as strings they also sort in date/time
order.

Secondly, glancing over your request, it seems that you have confused a
database table with the generic tabular data layout you might achieve using
e.g. HTML.
Each row of a table AKA 'relation' contains related information.  What you
seem to want, however, is completely different: You seem to actually want
two *different* sets of data, that have nothing to do with each other, and
display them in columns side-by-side.

The proper way to do this is to have your application pull back all of the
letter dates in one query, then all of the visit dates in a second query,
and then piece them together for display purposes outside of SQL.


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to add a column with CURRENT_DATETIME?

[Stephen Oberholtzer]

On Wed, May 28, 2008 at 1:31 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> [EMAIL PROTECTED] wrote:
> Another possibility would be to use the TIMESTAMP name for an integer
> unix epoch timestamp, and JULIANDAY for the floating point julian day
> number, giving five default value codes.
>
>   Name Inserts
>   ===
>   CURRENT_DATE string date
>   CURRENT_TIME string time
>   CURRENT_DATETIME string date and time
>   CURRENT_TIMESTAMPinteger unix timestamp
>   CURRENT_JULIANDAYreal julianday number



How about CURRENT_EPOCH for unix timestamp?


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query help?

[Stephen Oberholtzer]

What if you took a slightly different tack?

CREATE TABLE FinishedWork (
EventTime INTEGER NOT NULL,
FileName TEXT NOT NULL,
ProcessID INTEGER NOT NULL,
BytesProcessed INTEGER NOT NULL,
isDuplicate integer-- tri-state: 0=not duplicate 1=duplicate
null=unknown
);


And then periodically run this:

update FinishedWork
set isDuplicate = case when exists(select 1 from FinishedWork fw2 where
fw2.ProcessId=FinishedWork.ProcessId and fw2.FileName=FinishedWork.Filename
and fw2.rowid < FinsishedWork.rowid) then 1 else 0 end where isDuplicate is
null;

Then your report would be this:

SELECT ProcessID, sum(BytesProcessed)

FROM FinishedWork

WHERE EventTime > {20 minutes ago}
AND isDuplicate=0;



By the way, what's magic about 20 minutes ago?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Step Function

[Stephen Oberholtzer]

On Wed, May 28, 2008 at 12:32 AM, sqlite <[EMAIL PROTECTED]>
wrote:

>
> Dear Stephen,
>
> Thanks for the reply. As you said we checked the EXPLAIN QUERY PLAN with
> our
> query and it has shown that all the four tables we use in the query are
> using their indexes and there is no ORDER BY class in our query. So
> sqlite3_prepare compiles the query and sqlite3_step executes the query does
> it mean the execution time for our query is 40 secs because we are
> retrieving the records soon once gets executed.



Weird.  Would it be possible to post the schema (use the '.schema' command
in the sqlite3 command-line program) and the SELECT statement you're using?

Also, which version of SQLite are you using?




-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cross compiling sqlite3.c, anamolies.

[Stephen Oberholtzer]

On Tue, May 27, 2008 at 3:59 PM, Richard Klein <[EMAIL PROTECTED]>
wrote:

> > On May 26, 2008, at 3:24 PM, A. H. Ongun wrote:
> >> Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I
> >> get hundreds of error messages.
> >>
> >> I am puzzled to see why this is so.
> >
> > My guess would be because SQLite is written in C, not C++.
> >
> > D. Richard Hipp
> > [EMAIL PROTECTED]
>
> My company often needs to compile SQLite under C++, so we ran into the
> same problem.  It's easy to get rid of the error messages:  Mostly it's
> a matter of adding explicit typecasts, and of separating nested structs.


An "extern C" wrapper doesn't work?


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



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can't create table from a trigger

[Stephen Oberholtzer]

On Tue, May 27, 2008 at 4:15 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]>
wrote:

>
> >De: [EMAIL PROTECTED]
> >Fecha: 27/05/2008 19:56
> >
> >It's not supposed to, according to
> >http://sqlite.org/lang_createtrigger.html . The syntax
> >only allows select, insert, update and delete statements.
> >
> >What are you trying to achieve?
>
> I need to handle tables with several million records, on realtime, from
> RAM.
> One of the fields takes few values (say, company website id), but new ids
> are
> added from time to time.  I would prefer to dynamically create a set of
> tables
> when a new id shows up, for the improved locality of reference plus reduced
> overhead from the website id and its non-unique index.
>

Okay, this system seems to be screaming "doing something wrong" to me.

First: "Handle tables with several million records in realtime" is
incredibly vague and ambiguous.  First off, I have no idea what constitutes
"handling". Updating? Inserting? Selecting?  Secondly, I have no idea what
constitutes "realtime".  How fast is real time? Once per second? One billion
per second?

CREATE TABLE ttt ( t INTEGER PRIMARY KEY );
>
> CREATE TRIGGER ttt_new_trigger AFTER INSERT ON ttt FOR EACH ROW
> BEGIN
>CREATE TABLE uuu ( u INTEGER PRIMARY KEY );
> END;
>

This is really nonsensical. You can only have one table named 'uuu' at any
given moment.  Even if SQLite supported the syntax you're using, that table
would be recreated on the first insert to 'ttt' and then subsequent attempts
would fail with a 'table already exists' error.

One of the fields takes few values (say, company website id), but new ids
> are
> added from time to time.  I would prefer to dynamically create a set of
> tables
> when a new id shows up, for the improved locality of reference plus reduced
> overhead from the website id and its non-unique index.


This statement is really confusing me.  "New IDs are added from time to
time" sounds like "New IDs are added, but not very often" which conflicts
with your "realtime" assertion.
You say want to dynamically create a set of tables when a new ID shows up,
yet your example only tries to add one table.

My next quesiton would have been, "Why don't you just pregenerate your
tables" except for the "locality of reference" explanation.  Unfortunately,
this indicates to me that you don't really understand how SQLite works.
SQLite groups data in pages -- once data is split across two pages, you
can't really assume those two pages are anywhere close to each other in the
database file. Throw in filesystem fragmentation and even fewer assumptions
can be made.  So creating a table at the last minute doesn't mean its data
will be in a different area of the file -- that all depends on when the data
was added to the database.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'insert or ignore' vs self join?

[Stephen Oberholtzer]

On Tue, May 27, 2008 at 2:41 PM, Petite Abeille <[EMAIL PROTECTED]>
wrote:

> Hello,
>
> % sqlite3 -version
> 3.5.9
>
> I'm trying to figure out a frugal way to handle a unique key
> constrain...
>
> I tried using both 'insert or ignore' and a self join. The self join
> seems to be noticeably faster even though 'insert or ignore' would
> empirically appear to be the better deal (shorter query plan, less VM
> instructions).
>
> Specifically, given the following DML:
>
> insert  or ignore
> intotoken( name )
> select  stage.token as name
> fromstage
> order bystage.token;
>
> One gets a query plan like such:
>
> 0|0|TABLE stage
>
> And 'explain' reports 58 VM instructions.
>
>
> On the other hand, the following self join...
>
> insert
> intotoken( name )
> select  stage.token as name
> fromstage
> left join   token on token.name = stage.token
> where   token.id is null
> order bystage.token;
>
> ... uses a query plan like such:
>
> 0|0|TABLE stage
> 1|1|TABLE token WITH INDEX token_name
>
> ... and 82 VM instructions.
>
> Nonetheless, the self join would appear to be around 10% faster than
> the 'insert or ignore' flavor.
>
> Not sure why this is the case though... considering the apparent
> overhead incurred by the join.
>
> Thoughts?
>

Well, the first thing you should bring away from this experience is that the
number of VM instructions isn't really an indicator of how efficient the
query is :)

Now, I'm not sure exactly why one is faster than the other, especially since
you didn't post your exact schema and indices, and I have no idea how many
rows there are in either table.
But if I had to guess, it's because of the ORDER BY clause.  In general, an
ORDER BY means that SQLite needs to generate a temporary table with all the
rows to be selected/inserted,
then sort that temporary table.  The INSERT OR IGNORE version has to
unconditionally sort the entire 'stage' table; your second query only has to
sort those rows in 'stage' that don't already exist in 'table'.  If each
table fits comfortably in your computer's disk cache, the extra pass won't
matter so much.

In any case, I invite you to try the following:

1. Add an index: [[ create index stage_token_ix on stage(token);  ]] SQLite
will use that index to improve the ORDER BY.

2. Try the following variation:

insert intotoken( name )
select  stage.token as name
fromstage
where not exists(select 1 from token where token.name = stage.token)
order bystage.token;

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema optimization question

[Stephen Oberholtzer]

On Fri, May 23, 2008 at 3:01 PM, Jeff Gibson <[EMAIL PROTECTED]> wrote:

> I'm sorry if this is an obvious question - I'm new to databases.  I have
> an application where the database is used to log a large number of
> simulation events.  The database is written once and read many times
> (i.e., there are never any inserts or updates after database creation).
> The three most interesting tables I have are:
>
> CREATE TABLE events (eid INTEGER PRIMARY KEY, time INTEGER, aid INTEGER,
> subtype INTEGER);
>
> CREATE TABLE actions (aid INTEGER PRIMARY KEY, type INTEGER, seqnum
> INTEGER, tid INTEGER, instid INTEGER);
>
> CREATE TABLE subtypes (type INTEGER, subtype INTEGER, name TEXT, verbose
> INTEGER, PRIMARY KEY(type,subtype) );
>
> The column names are such that columns in different tables with the same
> name act as foreign keys.  The largest (and most often queried) table is
> events, and it can have many millions of entries.  The actions table is
> also large (about a fifth as big as events) and subtypes is very small
> (dozens of entries).  My application involves querying events many
> times, but very common queries include events that match a particular
> verbose value and/or a particular type value.  This leads to queries
> that have one or two joins, and such queries are substantially slower
> than just a query on just the events table.
>The question is, what can I do to speed up those queries?  The
> obvious answer would be to put type and verbose as columns in the events
> table, but they would be redundant.  Is that par for the course, or is
> there some best practice I'm overlooking?
>Thanks,
>Jeff
>

Well, I'd like to note that in general, reporting databases are denormalized
in order to improve performance.
However, you shouldn't need to do that for a case this simple.

Let's start by creating your database and having fun with EXPLAIN QUERY
PLAN.  I created the tables as you provided and did this:

create view eventdetail as select e.eid as eid, e.time as time, a.aid as
aid, a.seqnum as seqnum, a.tid as tid, a.instid as instid, s.type as type,
s.subtype as subtype, s.name as name, s.verbose as verbose from events e
join actions a on a.aid=e.aid join subtypes s on s.subtype = e.subtype;


This makes life a lot easier.  Besides, SQLite is extremely well-written and
will handle this stuff beautifully.
( I noticed that you have a 'type' on both the 'actions' table and the
'subtypes' table. I assume that they are the same thing and used the version
from subtypes. )

sqlite> explain query plan select * from eventdetail where type=123;
orde  from   deta
  -  
0 0  TABLE events AS e
1 1  TABLE actions AS a USING PRIMARY KEY
2 2  TABLE subtypes AS s WITH INDEX
sqlite_autoindex_subtypes_1

You can see here that we are table-scanning 'events'.   This is bad.  The
solution here is to add an index so events can be searched by subtype:

[[ create index events_subtype_ix on events(subtype); ]]

sqlite> explain query plan select * from eventdetail where type=123;
orde  from   deta
  -  
0 2  TABLE subtypes AS s WITH INDEX
sqlite_autoindex_subtypes_1
1 0  TABLE events AS e WITH INDEX events_subtype_ix
2 1  TABLE actions AS a USING PRIMARY KEY

As you can see here, SQLite is actually figuring out which subtypes have
type=123, then looking up that subset of the 'events' table using the newly
created index, then joining to 'actions' based on the 'aid' column.  All in
all, if there are very many different top-level types, the first one will
only find a few subtypes (the term is 'high selectivity').

In general, EXPLAIN QUERY PLAN  will give you a good idea on what
SQLite is doing to perform the requested actions.  Index tweaking and
ANALYZE (http://www.sqlite.org/lang_analyze.html) will enable you to filter
out the amount of data SQLite has to consider when returning a resultset.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "IN" keyword for multi-column index

[Stephen Oberholtzer]

On Thu, May 22, 2008 at 2:02 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


> IN only works on a single column. The closest you can get to this is
> something like
>
> SELECT map.*
> FROM map join (
> select 1 x, 1 y
> union all
> select 1 x, 2 y
> union all
> select 1 x, 3 y) t
> ON map.x = t.x AND map.y=t.y;
>
> I checked - it does use map_xy index. The subselect in parentheses
> essentially creates a temporary table, which is then joined with your
> main table.
>
> Igor Tandetnik


Hah! I was going to test something similar out, but decided not to.  I
figured that that would never actually work.  That'll teach me to make
assumptions :)

This is what you'd proposed:

sqlite> explain query plan select * from map m join (select 1 as x, 1 as y
union all select 1, 2 union all select 1, 3) z on z.x=m.x and z.y=m.y;
orde  from   deta
  -  
0 1  TABLE  AS z
1 0  TABLE map AS m WITH INDEX map_xy

And this is what I considered:

sqlite> explain query plan select * from map where x=1 and y=1 union all
select * from map where x=1 and y=2 union all select * from map where x=1
and y=3;
orde  from   deta
  -  
0 0  TABLE map WITH INDEX map_xy
0 0  TABLE map WITH INDEX map_xy
0 0  TABLE map WITH INDEX map_xy

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "IN" keyword for multi-column index

[Stephen Oberholtzer]

On Thu, May 22, 2008 at 1:41 PM, Alexander Batyrshin <[EMAIL PROTECTED]>
wrote:

>  Hello All,
> For example we have table like this:
>
> CREATE TABLE map (
>  name text,
>  x integer,
>  y integer
> );
> CREATE INDEX map_xy ON map(x,y);
>
> How to query this table with "IN" keyword?
> Query like this, doesn't work:
>
> SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3));


select * from map where x=1 and y in (1,2,3);

1. Some people might suggest some crazy things like [ SELECT * FROM map
WHERE x||'.'||y in ('1.1','1.2','1.3') ]. While this would technically work,
it wouldn't be able to use your index.
2. I tried this first: [ select * from map where (x=1 and y=1) or (x=1 and
y=2) or (x=1 and y=3) ] but that didn't use the index -- not on 3.5.6 anyway
3. If you're going to have a bunch of choices with different values for X
and Y, you *might* want to creating a precomputed statement of the form
'select * from map where x=? and y=?', then binding and re-executing the
statement for each (x,y) pair you're interested in, and piece them together
in your application.  You wouldn't be able to take advantage of ORDER BY,
GROUP BY, or DISTINCT that way, but it would work.




-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] deleting 100,000 entries

[Stephen Oberholtzer]

Actually, I seem to recall a discussion that revealed that "DELETE FROM Foo"
*does* truncate the table.  The discussion came up because someone
complained that "ON DELETE" triggers were not being fired; the explanation
was that 'DELETE FROM Foo" simply drops and re-creates Foo.  The solution
was to rewrite the code as "DELETE FROM Foo WHERE 1=1" or something similar.

This was pretty easy to verify:

sqlite> explain delete from foo;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 explain delete from foo;  00
1 Goto   0 4 000
2 Clear  2 0 0 foo00
3 Halt   0 0 000
4 Transaction0 1 000
5 VerifyCookie   0 1 000
6 Goto   0 2 000

sqlite> explain delete from foo where 1=1;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 explain delete from foo where 1=1;
00
1 Goto   0 23000
2 Integer1 2 000
3 Integer1 3 000
4 Ne 3 1426a
5 OpenRead   0 2 000
6 SetNumColumns  0 0 000
7 Rewind 0 14000
8 Integer1 3 000
9 Integer1 2 000
10Ne 2 1336a
11Rowid  0 1 000
12FifoWrite  1 0 000
13Next   0 8 000
14Close  0 0 000
15OpenWrite  0 2 000
16SetNumColumns  0 2 000
17FifoRead   1 21000
18NotExists  0 20100
19Delete 0 1 0 foo00
20Goto   0 17000
21Close  0 0 000
22Halt   0 0 000
23Transaction0 1 000
24VerifyCookie   0 1 000
25TableLock  -12 0 foo00
26Goto   0 2 000



Heading over to
http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/vdbe.c=1.741 and
looking at OP_Clear reveals that "DELETE FROM Foo" is closest in
implementation to "TRUNCATE TABLE Foo".



On Wed, May 21, 2008 at 2:46 AM, Harold Wood & Meyuni Gani <
[EMAIL PROTECTED]> wrote:

> Doesn't sqlite support the truncate table command
>
> Woody
> from his pda
>
> -Original Message-
> From: Carlo S. Marcelo <[EMAIL PROTECTED]>
> Sent: Tuesday, May 20, 2008 8:49 PM
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] deleting 100,000 entries
>
> @Barefoot and Keith,
>
> Awesome! It took forever for the web interface to delete, and I had to
> restart httpd for the database to unlock.. the command you provided took
> less than a second to clear everything out, thanks!
>
> Carlo
>
> - Original Message 
> From: BareFeet <[EMAIL PROTECTED]>
> To: General Discussion of SQLite Database 
> Sent: Wednesday, May 21, 2008 11:45:06 AM
> Subject: Re: [sqlite] deleting 100,000 entries
>
> Hi Carlo,
>
> > I want to clear the whole thing out(list)!
>
> Do you mean that you want to delete all rows from the "list" table? If
> so, do this:
>
> delete from list;
>
> See the syntax pages at:
> http://www.sqlite.org/lang.html
> http://www.sqlite.org/lang_delete.html
>
> Tom
> BareFeet
> http://www.tandb.com.au/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

[Stephen Oberholtzer]

On Fri, Mar 21, 2008 at 10:12 AM, Noah Hart <[EMAIL PROTECTED]> wrote:
> Suggestion to SQLite Developers ... Have PRAGMA integrity_check reparse
>  the SQL in sqlite_master, looking for errors.
>
>  Regards,
>
>  Noah

I don't think that would actually help.  It seems that this problem
was caused by older versions of SQLite accepting certain invalid SQL
syntax.  Since the bad syntax was accepted by the older parser, it's
not going to just start rejecting the database.

What I would recommend is a twofold change:

1. Improve the error message -- perhaps display the table/view name
and/or full SQL that it couldn't parse

2. When PRAGMA writable_schema=ON, treat schema errors as warnings and
simply disallow access to the affected tables/views.  This shouldn't
cause any compatibility problems because nobody should be using
writable_schema anyway.


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why did 3.5.7 pass the following test in alter2

[Stephen Oberholtzer]

>  sqlite> CREATE TABLE abc2(a, b, c);
>
>  sqlite> CREATE VIEW abc2_v AS SELECT * FROM abc2;
>  sqlite> SELECT * FROM abc2_v;
>
>  sqlite> ALTER TABLE abc2 ADD COLUMN d;
>
>  
>  I would expect the view to give 4 columns after the alter, not 3.
>  as the following direct select shows.

I started out by explaining why SQLite was not doing what you
expected, and then I found out that there does in fact seem to be a
bug afoot.

There are two reasonable behaviors here:

1. Preserving the "*"ness, so that adding a column to the relevant
source table will add the column to the view
2. Expand the '*' at view-creation-time, so that adding columns to the
source table(s) does not affect the view (I'm pretty sure PostgreSQL
works like this)

It looks like SQLite mostly does the first one.  However, if a table
is altered, the schema cache is not flushed.  Watch!

D:\>sqlite3 tmp.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .headers on
sqlite> create table abc2 (a,b,c);
sqlite> insert into abc2 values(1,2,3);
sqlite> create view abc2_v as select * from abc2;
sqlite> select * from abc2_v;
a|b|c
1|2|3
sqlite> alter table abc2 add column d;
sqlite> select * from abc2_v;
a|b|c
1|2|3


As you can see, SQLite hasn't realized that abc2_v needs to be
updated. However, if I open another command prompt and run:

D:\>sqlite3 tmp.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> create table xyz(a);

This increments the schema version, which will invalidate the schema
cache (and any prepared statements, but that doesn't really apply to
sqlite3.)
So, back in the original terminal:

sqlite> select * from abc2_v;
a|b|c|d
1|2|3|

Looking at the 'alter2.test' source at
http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/alter2.test=1.13,
I can see that the "alter_table" function appears to open a dedicated
connection to the database, which means when the "execsql" function is
then called, it is not on the same connection as the "alter_table" one
and cannot take advantage of the database cache.

I think we might need an alter2b.test, and maybe even an
alternot2b.test (ba-dum-pshh!)

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can't get concat operator to work

[Stephen Oberholtzer]

On Mon, Mar 10, 2008 at 7:36 PM,  <[EMAIL PROTECTED]> wrote:
>
>
>   I'm trying to get the concat operator to work with my user-defined
>  function.  This works fine:
>
>   SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM  Employees
>
>   But this doesn't work:
>
>   SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time)
>  FROM  Sessions
>
>   I get only the formatted date - missing the formatted time.
>  FORMAT_DATE is my own user-defined function that returns text data
>  type.
>
>   Can someone *please* check into this.  I must get this working.
>
>   Thank you
>  -brett

What about this?

SELECT FORMAT_DATE(login_time), ' ', FORMAT_TIME(login_time) FROM Sessions


That will make sure that FORMAT_DATE(login_time) is working properly

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Alternative to * to SELECT most columns?

[Stephen Oberholtzer]

On Sat, Mar 1, 2008 at 6:13 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote:
> On Sat, 1 Mar 2008 18:04:12 -0500, "Stephen Oberholtzer"
>  <[EMAIL PROTECTED]> wrote:
>  >>  INSERT INTO Table2 VALUES (NULL,"Some text in Table2");
>  >>  INSERT INTO Table2 VALUES (NULL,"Some other text in Table2");
>  >>  =
>  >>  INSERT INTO Table1 VALUES (NULL,"John Doe",1);
>  >>  INSERT INTO Table1 VALUES (NULL,"JaneDoe",2);
>  >>  =
>  >>  SELECT * FROM Table1,Table2 WHERE Table1.table2id=1;
>  >>  =
>  >>  1|John Doe|1|1|Some text in Table2
>  >>  1|John Doe|1|2|Some other text in Table2
>  >>  =
>  >
>
> >I'm confused. Which one of those rows does not have table1.table2id=1?
>
>  Sorry for the imprecision: It's the same record, but why do I get two
>  rows instead of one? I expected only the first one, since "Some text
>  in Table2" has its ID = 1.

Then you need to specify that:

SELECT * FROM Table1,Table2 WHERE Table1.table2id=1 AND Table2.ID=1;

I have to ask: Why is it that you expected a condition applying to one
column on one table, to also apply to a differently named column in a
differently named table?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling sources for Coldfire embedded platform

[Stephen Oberholtzer]

On Fri, Feb 29, 2008 at 11:01 PM,  <[EMAIL PROTECTED]> wrote:
>
>
>  I've downloaded and built the sources for the standard Intel Linux
>  platform but I want to run this on Linux on a Coldfire (MCF5484) platform.
>  How do I modify the compiler the build uses (short of just modifying the
>  Makefile)? I've looked at the Makefile.in but there's nothing obvious
>  about how to change it.
>
>  Didn't see any docs on this. Any help appreciated.
>

I don't know that specifically, but the usual method for that stuff is

CC=compilername make

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Alternative to * to SELECT most columns?

[Stephen Oberholtzer]

>  =
>  CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT,
>  table2id INTEGER);
>  CREATE TABLE Table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
>  =
>  INSERT INTO Table2 VALUES (NULL,"Some text in Table2");
>  INSERT INTO Table2 VALUES (NULL,"Some other text in Table2");
>  =
>  INSERT INTO Table1 VALUES (NULL,"John Doe",1);
>  INSERT INTO Table1 VALUES (NULL,"JaneDoe",2);
>  =
>  SELECT * FROM Table1,Table2 WHERE Table1.table2id=1;
>  =
>  1|John Doe|1|1|Some text in Table2
>  1|John Doe|1|2|Some other text in Table2
>  =
>
>  I expected only the first row, but I got two :-/

I'm confused. Which one of those rows does not have table1.table2id=1?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How To concatenate two fields in one

[Stephen Oberholtzer]

On Fri, Feb 29, 2008 at 11:52 AM, Alessio Forconi <[EMAIL PROTECTED]> wrote:
> Hello everyone,
>
>  SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students

+ is addition. You want ||.  Also, you're using double-quotes (") when
you should be using single-quotes (').

SELECT IDStudent, Name || '.' || Surname



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update fail without ERRORS

[Stephen Oberholtzer]

On Fri, Feb 29, 2008 at 11:01 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I'm working on a program using sqlite library, but I've got an issue
>  that I can't solve.
>  Suddenly, my program don't update the tables
>  I
>  don't understand whats matter because, if I write SQL instructions
>  using Sqlite3 client, UPDATE works fine, and I haven't any ERROR CODE.
>  sqlite3_exec function return SQLITE_OK, but tables aren't updated.
>
>  How
>  can I debug this issu ?
>
>  Pierlugi

Could you provide some examples of the UPDATE statements you're using?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[Stephen Oberholtzer]

On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m
<[EMAIL PROTECTED]> wrote:
>
>
>  Hi,
>  My table looks like:
>  IdName
>  1 1aaa
>  2 01345
>  3 1asdf
>
>  I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
>  I want to bind it as 'a'.
>
>  My Prepare statement is as follows:
>
>  Unsigned char u8_ClassificationCode=1;
>
>  Unsigned short u16_Input=0x0061;
>
>  if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
>  Name >= '%d%c'  LIMIT 1;",-1,_SearchPrepareStmt,0)!= SQLITE_OK)
>
>  {
>
> return SQLITE_DB_ERROR;
>
>  }
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
>  *)u16_Input,-1,SQLITE_STATIC);
>
>  }
>

Since nobody else mentioned it: there's something seriously wrong with
your database design.

But first: Your usage of sqlite3_bind_text16 is incorrect.   The
fourth argument, -1, means "My string is NUL-terminated. Use strlen()
to figure out how long my string is and use that.".

However, for that to always work correctly, u16_input needs to be an
array with a NUL terminator:

>> unsigned short u16_input[] = { 'a', '\0' }; <<

Anyway, back to what I was saying: your database design needs
rethinking.  1NF (http://en.wikipedia.org/wiki/First_normal_form)
states that a column should only have one value.   However, you seem
to be combining *two* values (Classification Code and Input) into one
column (Name).  Therefore, you should be doing this:

>> create table Music ( id integer not null primary key,
classificationCode integer, input text) <<

Table:
id  classificationCode  input
--  --  -
1   1   aaa
2   0   1345
3   1   asdf

At this point, you would do this:

>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE classificationCode = ? AND
input >= ? LIMIT 1;", -1,_SearchPrepareStmt, 0); <<
Note that, if you you want the original form, you can do
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode
|| input as Name FROM MUSIC WHERE classificationCode = ? AND input >=
? LIMIT 1;", -1,_SearchPrepareStmt, 0); <<
This will convert classificationCode to a string and join it against
the 'input' column to return your original Name.

>> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
sqlite3_bind_text(pst_SearchPrepareStmt, 2, "a", -1, SQLITE_STATIC);
<<

This also means you can index the string portion of your Name column
separately, and quickly search for something with a specific name
without knowing its classification.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IF...THEN constructs

[Stephen Oberholtzer]

On Wed, Feb 27, 2008 at 6:00 PM, Jason Salas <[EMAIL PROTECTED]> wrote:
> Hi Igor,
>
>  Thanks for the insight.  I'm used to doing stored procedures for web
>  apps, which conditionally execute statements based on state and/or the
>  presence of variables.  Consider this construct, which I built recently
>  to populate a table with URL for a web spider bot I built:
>
>  CREATE PROCEDURE AddLinkInfo
>  (
> @ProviderName VARCHAR(200),
> @LinkPath VARCHAR(200),
> @LinkText VARCHAR(200)
>  )
>  AS
> DECLARE @ProviderIDINT
>
> -- only store a link if it isn't already listed in the database
> IF NOT EXISTS(SELECT LinkPath FROM SpiderBot WHERE LinkPath = @LinkPath)
> BEGIN
> -- is this a known provider?  if not, add it into the DB and
>  then assign it's new ID
> IF EXISTS(SELECT ContentProviderID FROM
>  SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
> BEGIN
> SET @ProviderID= (SELECT ContentProviderID FROM
>  SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
> END
> ELSE
> BEGIN
> INSERT INTO SpiderBot_ContentProviders VALUES
>  (@ProviderName)
> SET @ProviderID = @@IDENTITY
> END
>
>   -- do the main content insertion
> INSERT INTO SpiderBot (ContentProviderID,LinkPath,LinkText)
>  VALUES (@ProviderID,@LinkPath,@LinkText)
> END
>  GO
>
>  How would I got about re-writing something like this in SQLite?  Thanks
>  again for your help.
>
>

I would create several functions:

function RegisteProvider(providername)
check with a SELECT statement to see if the provider exists
   if it does, return the provider ID

   insert a new provider
   return new provider's ID

function RecordLinkInfo(providername, linkname, linkurl)
   check with a SELECT statement to see if linkurl is inuse
   if it is, bail
   providerId = RegisterProvider(providername)
   insert new row with provider ID, link name, link url



Remember to use transactions to significantly boost your insert
performance  Maybe one transaction per page?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improved sorting algorithm (was: indexing)

[Stephen Oberholtzer]

On Wed, Feb 13, 2008 at 2:12 PM,  <[EMAIL PROTECTED]> wrote:
>
>  A project on our to-do list is to implement a new sorter
>  that uses O(1) seeks.  We know how to do this.  It is just
>  finding time to do the implementation.

Do you have a link to a page or pdf describing the algorithm involved?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View update performance (was: Updatable views)

[Stephen Oberholtzer]

On Thu, Feb 14, 2008 at 10:17 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Stephen Oberholtzer wrote:
>
>  Stephen,
>
>  FYI, your ticket was fixed on Tuesday by checkin 4782 in case you hadn't
>  noticed.
>
>  Dennis Cote
>

Wow, awesome! My claim to fame -- I submitted a patch to an
open-source project and it got accepted!  (Actually, I've done it once
before, in Subversion, but this is for a feature that actually has a
chance at being used!)

It even looks like my patch was still mostly valid.  I'd love to know
what frame of mind I was in when I wrote it, because I'm pretty sure I
wouldn't have come up with the name 'MaterializeView' if I had tried
to write the patch today.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing Query

[Stephen Oberholtzer]

On Thu, Feb 14, 2008 at 2:26 PM, Nathan Biggs <[EMAIL PROTECTED]> wrote:
> I was hoping that someone could help me with optimizing this query.
>  Basically I need to return totals from an large database (> 1million
>  records in the table).  There needs to be two different totals one by
>  "a" and one by "b" where "a" and "b" could have up to 100 rows each.  So
>  totals for up to 200 distinct total values.



I'm trying to understand your problem, but unfortunately you're being
extremely abstract and vague. What are "a" and "b"? Separate columns
in your main table?


>  Query
>  --
>  replace into totals
>  select 0, 0, a, b, c
>  from table1

So, 'totals' gets one row for each row in 'table1'?  That doesn't make
any sense to me.  A total is an aggregate sum, so there should only be
one row.

>  -
>  create temp table totals(id integer primary key, cnt integer, a float, b
>  integer, c integer);
>  Begin Transaction;
>  insert into totals values (0, 0, 0.00, 0, 0,);
>  insert into totals values (1, 0, 0.00, 0, 0,);
>  insert into totals values (2, 0, 0.00, 0, 0);
>  etc... total of 500 rows
>  Commit;

Okay, so now I've got:
-> 9 different where clauses
-> 4 different inserts in your trigger
-> 1.3 million rows in the table
-> 500 rows in the table

You're really going to have to explain your problem in greater detail.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View update performance (was: Updatable views)

[Stephen Oberholtzer]

On Feb 11, 2008 1:53 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> Stephen Oberholtzer wrote:
> > I should note that there's a gross inefficiency when using triggers to
> > handle updates or deletes against views; SQLite does the equivalent of
> > this:
> >
> > For UPDATE  ...  WHERE ,  SQLite copies the entire
> > source view into the temp table.
> >
> > SELECT * INTO  FROM 
> >
> > Then, it iterates over , looking for rows that match
> > , and *then* runs the trigger on them.  This means that if
> > your source view is large, this will run slowly.
> >
> > I submitted a patch a long while ago to optimize this by turning the
> > initial temp-table population into "SELECT * INTO  FROM
> >  WHERE ", which worked much faster, but I don't think
> > anything came of it.
> >
> > (my original msg to this list:
> > http://readlist.com/lists/sqlite.org/sqlite-users/2/11029.html )
> >
> >
> >
>
> Stephen,
>
> This does seem like a good idea.
>
> The SQLite mailing list doesn't pass files attached to submissions, so
> no one saw your patch. I would suggest creating at ticket at
> http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew and posting
> your message, or a link to it, along with your patch.
>
> I suspect the patch itself will probably have to be modified, since
> SQLite recently underwent significant changes to its code generation
> routines.
>
> As with all patches, it will be reviewed and accepted much faster if it
> passes the test suite.
>
>
> HTH
> Dennis Cote

If only you'd been around when I'd posted my message! Nobody said
*anything*, so I figured nobody else cared about it.

I have reposted my mailing list message, with attachments, here:
http://www.sqlite.org/cvstrac/tktview?tn=2938

I have not updated the patch, however.  It *should* be pretty
straightforward -- looking at it again, it doesn't actually seem to do
any VDBE code itself, so who knows?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Stephen Oberholtzer]

I should note that there's a gross inefficiency when using triggers to
handle updates or deletes against views; SQLite does the equivalent of
this:

For UPDATE  ...  WHERE ,  SQLite copies the entire
source view into the temp table.

SELECT * INTO  FROM 

Then, it iterates over , looking for rows that match
, and *then* runs the trigger on them.  This means that if
your source view is large, this will run slowly.

I submitted a patch a long while ago to optimize this by turning the
initial temp-table population into "SELECT * INTO  FROM
 WHERE ", which worked much faster, but I don't think
anything came of it.

(my original msg to this list:
http://readlist.com/lists/sqlite.org/sqlite-users/2/11029.html )



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update value from either database table?

[Stephen Oberholtzer]

On Feb 4, 2008 1:56 PM, Gussimulator <[EMAIL PROTECTED]> wrote:
> I have a field I need to update given a condition, but I don't know wether 
> the condition occurs in table A or table B, how can I perform this query?
>
> I have 2 identical tables in design, but one contains system data and the 
> other one contains user data... On my update routine (in C) I have to 
> increase an INTEGER field from either table given a condition... The thing 
> is, the condition could be present on both tables... what should I do??
>
> Thanks!

I'm no database expert, but why not merge them with a single table, like this?

[[ create table MyTable (foo, bar, baz, user_data INTEGER) ]]

with user_data=0 for the "system" data, and user_data=1 for the "user" data.

Unique/Primary-key constraints can be kept unique by appending the
user_data column to the index (how well does SQLite perform with
regard to multi-column indices, btw?)


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite character comparisons

[Stephen Oberholtzer]

I feel compelled to throw in my $0.02 here.

To everyone who thinks that SQLite should allow  'foo '  == 'foo':

SQL was originally conceived as a query *language* -- a way for a
human being to request a set of data from a database.  It was
specifically designed for ad-hoc queries.

This little 'magic space trimming' feature exists to match the
'char(N)' data type.  A char(10) field is always exactly 10 characters
long; longer strings are truncated and shorter strings are
space-padded.
Most database engines are more efficient at these, because when all
rows are the same width, the task of finding a particular row reduces
to a simple array lookup; therefore, if performance is
a critical issue (and when SQL was first formed, CPUs weren't quite as
powerful as they are now.)

But this presents a problem: the 'usual' definition of equality would
mean that any comparisons to a char(N) field would need to be N
characters long, or they would always fail.  Since it's stupid to make
people count spaces, somebody came up with the solution 'if they enter
something shorter, pad it with spaces and then compare.'

(If anyone wishes to quote the spec regarding space-extension and
varchar(N) fields, first recall that the SQL specifications have been
created by committees.)

Since SQLite does not have any concept of a fixed-width field of
character data, the whole concept of ignoring/appending trailing
spaces doesn't even apply.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE

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

[sqlite] [patch] Improve performance of UPDATE/DELETE against views

[Stephen Oberholtzer]

(I sent an e-mail to the list, and got a nice response, although it
didn't really say where I should send enhancement patches. So I'm
sending this one here, in the hopes that at least one person will
comment on it.)

Currently, the implementation of 'update' and 'delete' for views is such that

UPDATE/DELETE FROM MyView WHERE condition

is effectively coded as the following steps:

1. INSERT INTO _temptable SELECT * FROM MyView;
2. for each row in _temptable, if "condition" is true, make a note
somewhere that we need to process this row
3. for each row we made note of, populate the OLD and NEW
pseudotables, then run all of the triggers.

This works fine in theory, but it has a slight problem: it makes a
complete replication of the view's data, then iterates over that data,
in order to work.

For a contrived example, I have attached a script that demonstrates
this quite well: it builds up a table "numbers" with 4096 rows in it,
and then creates a view "numview" which is a simple Cartesian join of
"numbers" against itself. This means that a "select * from numview"
yields 16777216 rows.

By extension, it means that the first step of an UDPATE against
numview creates a temporary(ephemeral) table with 16 million rows,
then iterates over those 16 million rows. This is a bit slow.

To see the problem in action, run the attached SQL script and then try
the following statement:

update numview set n1=-1 where n1=1 and n2=5;
(Be prepared to wait a bit.)


My patch restructures an update like "UPDATE view WHERE condition" to behave
like this:

1. INSERT INTO _temptable SELECT * FROM (SELECT * FROM MyView) WHERE condition;
2. for each row in _temptable, make a note somewhere that we need to
process this row
3. for each row we made note of, populate the OLD and NEW
pseudotables, then run all of the triggers.

This has the following advantages:
1. Only the rows that are matched by the delete/update's WHERE clause
are copied.  You can have a 100-million-row view, and it will only
copy one row if if your WHERE clause is specific enough. (It will copy
no rows if it's a bit too specific!)
2. Indices associated with columns referenced in the WHERE clause have
a chance to be used.

Oh, and for completeness:

I, the author of this patch, dedicate any and all copyright interest
in this code to the public domain. I make this dedication for the
benefit of the public at large, and to the detriment of myself, my
heirs, and my successors. I intend this dedication to be an overt act
of relinquishment in perpetuity of all present and future rights of
this code under copyright law.

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
create table numbers (num int PRIMARY KEY); 
create table numbers2 (c int);  
insert into numbers values (0); insert into numbers2 values (1);
-- 1
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 2
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 4
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 8
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 16
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 32
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 64
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 128
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 256
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 512
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 1024
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 2048
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 4096


create view numview as 
select a.num as n1, b.num as n2
from numbers a, numbers b
where a.num is not NULL and b.num is not NULL;

create trigger numviewupd instead of update on numview 
begin 
update numbers set num=new.n1 where num=old.n1;
end;
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Submitting patches?

[Stephen Oberholtzer]

What's the best way to submit patches for SQLite?  I looked around on
the website and didn't find anything relevant; Google wasn't much help
because all I got were pages for *other* projects that used SQLite to
maintain their patch databases.

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE

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

Re: [sqlite] INSTEAD OF Trigger Question

[Stephen Oberholtzer]

On 4/23/07, Sylko Zschiedrich <[EMAIL PROTECTED]> wrote:

Hi all,

i have a question to "instead of triggers" on views.

Following schema:



h>

That's my current implementation. But with this I can't update the View
to 'null' values because the coalesce statement will return the
old.values.

How can I handle that?
Can I "detect" the columns to update?



Actually, you don't need to. the OLD and NEW pseudotables don't
contain just the data affected by the update; it includes the
*complete* row before the update (in OLD) and after the update (in
NEW).

I have attached a sqlite3 script demonstrating this; just run it with

sqlite3 < sqlite-view-update.sql

to see the results.

A final warning: there is a gross inefficiency in the way updates on
views are handled. If your view is big -- i.e. "select count(*) from
myview" reports more than about 100K rows -- your update is going to
take inexplicably long.  I'm pondering a patch for that.

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
.headers on
.mode columns
-- create a table
create table footbl(key INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
num INTEGER NOT NULL,
str TEXT NOT NULL,
float REAL NOT NULL
);
create table footbl_history(
oldkey int, oldnum int, oldstr text, oldfloat real,
newkey int, newnum int, newstr text, newfloat real
);
-- create a seemingly useless view
create view foo as select key, num, str, float from footbl;

create trigger foo_update instead of update on foo
begin
insert into footbl_history values (
old.key, old.num, old.str, old.float,
new.key, new.num, new.str, new.float);
update footbl set key=new.key, num=new.num, str=new.str, float=new.float
where key=old.key;
end;

insert into footbl (num,str,float) values (1, 'one', 1.0);
insert into footbl (num,str,float) values (2, 'three', 2.0);
insert into footbl (num,str,float) values (3, 'two', 3.0);

-- oops! Oh wait!
update foo set str='two' where num=2;
update foo set str='three' where num=3;


.headers off
select 'View:';
.headers on
select * from foo;
.headers off
select '';
select 'History:';
.headers on
select * from footbl_history;
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL query, finding out which row a result is in

[Stephen Oberholtzer]

On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote:

Given a arbitrary statement, I need to find out which row a specific
result is in, as efficiently as possible. The arbitrary statement can
order the results any way it wants.


Let's say your resultset consists of 3 columns: memberid, lastname,
firstname.  Then you can do this:

create temp table _results (rownumber int AUTOINCREMENT, memberid int,
lastname text, firstname text);

insert into _results (memberid, lastname, firstname)
select memberid, lastname, firstname
from MainTable where whatever;

select * from _results where memberid=12345;

While this isn't a whole lot more efficient than just pulling
everything into the application and doing it that way, it will work
out a bit better if you need to do this for multiple member IDs.


--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE

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