Re: [sqlite] NULL always greater?

[Dan Kennedy]

The basic rule is that the result of any comparison involving a
NULL value (including comparing against another NULL) is NULL.

See the following for the details:

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

Dan.


On Tue, 2007-01-30 at 16:41 -0800, Clark Christensen wrote:
> I've read through numerous discussions here about comparing values with null, 
> and how SQLite functions work with null values, and I thought I understood.
> 
> Now it seems appropriate to use the max(col1, col2) function to find the 
> latest of two dates (integer Unix times), and some rows will contain null in 
> one column or the other.  But, max() always returns null when one of its args 
> is null.  That just seems backwards :-))
> 
> FWIW, I'm on 3.3.12 on both Windows and Linux.
> 
> Any help is appreciated.
> 
> Thanks!
> 
>  -Clark
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Obtaining randomness on win32

[Dan Kennedy]

On Wed, 2007-01-31 at 20:04 -0600, Jay Sprenkle wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > > Please pick a pseudo algorithm of your choice and give us the ability to
> > > seed it?
> > >
> >
> > You already have this capability.  Use the redefinably I/O feature
> > to insert your own random number seeder in place of the one that
> > comes built in.
> 
> 
> I don't follow that. You mean write a user defined function?

I think you'll need to define SQLITE_ENABLE_REDEF_IO when compiling
and then do something like:

  int myRandomSeedFunction(char *zOutputBuffer){ ... }

  sqlite3_os_switch()->xRandomSeed = myRandomSeedFunction;

The comments in os.h have the details.

Dan.


> 
> --
> The PixAddixImage Collector suite:
> http://groups-beta.google.com/group/pixaddix
> 
> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
> http://www.reddawn.net/~jsprenkl/Sqlite
> 
> Cthulhu Bucks!
> http://www.cthulhubucks.com


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

Re: [sqlite] joins, brackets and non-existing columns

[Dan Kennedy]

On Thu, 2007-02-01 at 21:18 +0100, Info wrote:
> If I use the following inner join of 3 tables:
> 
> SELECT  T1.A
> FROM(T1 INNER JOIN T2 ON T1.A=T2.A) INNER JOIN T3 ON T1.B=T3.B
> 
> SQLite returns an error message saying that the column T1.A does not exist.
> If I run this statement on MysQL, Oracle or DB2, they accept it. And in
> fact, this is correct SQL.
> If I remove the brackets, SQLite also accepts it. But sometimes you want to
> work with brackets, if you are combining inner and left outer joins.
> 
> Any idea why this is not accepted by SQLite?

SQLite considers the tables within the brackets a sub-query. The
following two are handled in the same way internally:

   SELECT * FROM (abc, def);
   SELECT * FROM (SELECT * FROM abc, def);

In both cases the sub-query is handled as an anonymous table, it's
not possible to refer to it explicitly. The workaround is to name
it with an AS clause:

   SELECT T1T2.A
   FROM (T1 INNER JOIN T2 ON T1.A=T2.A) AS T1T2 ...

Dan.



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

Re: [sqlite] not allowing an empty column

[Dan Kennedy]

On Sat, 2007-02-03 at 00:53 -0600, P Kishor wrote:
> how do I add a constraint to a column so neither null nor empty
> strings ("") are allowed?
> 

create table tbl(col CHECK (col IS NOT NULL AND col != ''));






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

Re: [sqlite] ATTACH and :memory: databases

[Dan Kennedy]

On Wed, 2007-02-07 at 09:37 -0800, Dave Gierok wrote:
> I would like to attach a :memory: database to another :memory: database.  How 
> is this possible since the ATTACH command requires the database's file name?
> 
> ATTACH [DATABASE] database-filename AS database-name
> 
> -Dave Gierok

I don't think there are any special issues. Use ':memory:' as the
filename.

[EMAIL PROTECTED]:~> sqlite3 :memory:
SQLite version 3.2.8
Enter ".help" for instructions
sqlite> attach ':memory:' as db2;
sqlite> attach ':memory:' as db3;
sqlite> create table db2.abc(a, b, c);
sqlite> select * from sqlite_master;
sqlite> select * from db3.sqlite_master;
sqlite> select * from db2.sqlite_master;
table|abc|abc|2|CREATE TABLE abc(a, b, c)
sqlite> 



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

RE: [sqlite] ATTACH and :memory: databases

[Dan Kennedy]

On Wed, 2007-02-07 at 11:12 -0800, Dave Gierok wrote:
> I have one :memory: database open and I want to make a copy of it into 
> another :memory: database.

Ah, I get it now. Misunderstood earlier.

A new in-memory database is created each time ':memory:' is 
attached (using ATTACH) or opened (via sqlite3_open). You
cannot currently open a :memory: database via sqlite3_open() and 
then attach it to another handle.



>   When I open the 2nd :memory: database and call attach on the first one 
> specifying ':memory:' as the file name, it allows me to do this.  However 
> when I loop through the tables copying them into the 2nd one, it is still 
> empty after this has been done.  I think the first :memory: database might be 
> attaching to, uh, itself.
> 
> Is there a way to specify the name of a memory database uniquely?  Something 
> like ':memory:2'?  Nothing that I've tried works.

> -Dave Gierok
> 
> -Original Message-
> From: Dan Kennedy [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 9:56 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ATTACH and :memory: databases
> 
> On Wed, 2007-02-07 at 09:37 -0800, Dave Gierok wrote:
> > I would like to attach a :memory: database to another :memory: database.  
> > How is this possible since the ATTACH command requires the database's file 
> > name?
> >
> > ATTACH [DATABASE] database-filename AS database-name
> >
> > -Dave Gierok
> 
> I don't think there are any special issues. Use ':memory:' as the
> filename.
> 
> [EMAIL PROTECTED]:~> sqlite3 :memory:
> SQLite version 3.2.8
> Enter ".help" for instructions
> sqlite> attach ':memory:' as db2;
> sqlite> attach ':memory:' as db3;
> sqlite> create table db2.abc(a, b, c);
> sqlite> select * from sqlite_master;
> sqlite> select * from db3.sqlite_master;
> sqlite> select * from db2.sqlite_master;
> table|abc|abc|2|CREATE TABLE abc(a, b, c)
> sqlite>
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Question on PRAGMA syntax when including database

[Dan Kennedy]

On Sun, 2007-02-11 at 22:38 -0800, tolson wrote:
> Hello,
> 
> I just finished reading through the help files and I am unclear as to when
> database should be used to prefix the pragma call (i.e. PRAGMA
> [database.]cache_size;).

For pager pragmas not specifying a database where it is optional is the
same as specifying "main". So if you want to change the cache-size of 
an attached database (internally, each open database file has it's own
cache with it's own cache-size parameter) then you need to specify a
database.

>   I was also wondering about schema pramas such as
> index_list(table-name).  If the table is in an attached database should it
> be called as index_list(database.table-name) or would
> database.index_list(table-name) be more appropriate?

The latter. I'm pretty sure the former won't even parse. For a
schema-query pragma, not specifying a database name causes SQLite to
search all attached databases for the named schema object. With an
explicit database name, only the named database is searched.

The online docs don't cover the "PRAGMA database.pragma(...)" version
of the PRAGMA command. Not sure if this means anything about how
official this syntax is or not.


> Thank you,
> 
> Tom


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

Re: [sqlite] SQLite and text encoding

[Dan Kennedy]

On Tue, 2007-02-13 at 11:58 +0200, DragonK wrote:
> Hi,
> 
> I have a table containing a text field, which is UTF-8 encoded data.  I want
> to know if I use sqlite3_column_text16() to retrieve the contents
> of the field, would the data be automatically converted to UTF-16, or would
> the characters be simply "extended" to 16 bit ? The documentation didn't
> help me much

converted to utf-16.

> Any solutions/hints are deeply appreciated!  :)
> 
> Thanks.
> 


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

Re: [sqlite] Re: New (ish) to C== and new to SQLite error with prepare.

[Dan Kennedy]

On Tue, 2007-02-13 at 16:18 +, Paul Simpson wrote:
> rc = (sqlStepAdd)(&pStmt);

rc = (sqlStepAdd)(pStmt);


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

Re: [sqlite] Can't get results from PRAGMA

[Dan Kennedy]

>   wxString getDBVersion = "PRAGMA user_version = 2;";
>   rc = (sqlPrepareAdd)(newdb,getDBVersion.c_str(),getDBVersion.length(),
> &pStmt, &pzTail);
> 
>   if(rc != SQLITE_OK){
>   wxString msg;
>   msg.Printf(wxT("Can't process database: 
> %s"),(sqlErrMsgAdd)(newdb));
>   wxMessageBox(msg, wxT("File Open Failure"),
>   wxOK | wxICON_ERROR, NULL);
>   return false;
>   }
>   rc = (sqlFinalizeAdd)(pStmt);
>   if(rc != SQLITE_OK){
>   wxString msg;
>   msg.Printf(wxT("Unable to release database statement:
> %s"),(sqlErrMsgAdd)(newdb));
>   wxMessageBox(msg, wxT("File Open Failure"),
>   wxOK | wxICON_ERROR, NULL);
>   return false;
>   }

I don't see a Step() up there. Maybe it's never executing the pragma
to set the user-cookie.



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

Re: [sqlite] Difference between sqlite and sqlite3

[Dan Kennedy]

On Thu, 2007-02-15 at 11:49 +0100, Pavan wrote:
> Hi,
> 
> Can anyone tell me what is the difference between sqlite and sqlite3.

By itself, "sqlite" probably means SQLite version 2. Same concept as
version 3 - a client library to access an SQL database stored in a
single file -, but a different file format and API.

Version 2 is no longer actively developed. Use version 3 for new code
if at all possible.

> Thanks,
> Pavan.


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

Re: [sqlite] OR in virtual tables

[Dan Kennedy]

I think with a virtual table all you can do is:

  SELECT * FROM vtable WHERE x = 'a' UNION
  SELECT * FROM vtable WHERE x = 'b'

Virtual tables cannot supply an index for WHERE clauses of the form 
"x IN ('a', 'b')" or "x = 'a' OR x = 'b'" the way normal tables
can.

Dan.


On Fri, 2007-02-16 at 00:31 +0100, Jos van den Oever wrote:
> Hi All,
> 
> I'm playing with virtual tables and found that when i do
>  SELECT * FROM vtable WHERE x = 'a' OR x = 'b'
> xBestIndex is called without constraints. Is there a way to circumvent
> this? I dont want sqlite to traverse all the rows just because of the
> OR statement.
> The version I'm using is 3.3.13.
> Using
>   info->estimatedCost = 10;
> does not trigger additional calls to xBestIndex.
> 
> Cheers,
> Jos
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Question regarding REPLACE INTO and last row ID

[Dan Kennedy]

On Sat, 2007-02-17 at 00:38 -0800, Tom Olson wrote:
> Hello,
> 
> If a REPLACE INTO statement is executed and the there is an existing row
> that is replaced will sqlite3_last_inserted_rowid return the rowid of the
> record that was changed?

REPLACE INTO doesn't change records. It replaces them. With new
records that have new rowids. So the answer is yes, but the returned
rowid will be that of the new record, not the old.


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

Re: [sqlite] deleting a single row

[Dan Kennedy]

On Tue, 2007-02-20 at 00:29 -0500, Jim Crafton wrote:
> If I have a simple table without an index column, and have multiple
> rows with the same data, is it possible to *only* delete one row? In
> other words, is there anything like the LIMIT syntax that's found in
> the SELECT command for DELETEs?

Not directly. But every row has an implicit primary key that you can
refer to using (amongst other names) "oid". You can use a SELECT to
locate a single oid value and then use the oid to delete a single
row. i.e. instead of:

  DELETE FROM xxx WHERE ;

do

  DELETE FROM xxx 
  WHERE oid = (SELECT oid FROM xxx WHERE  LIMIT 1)

or similar.

Dan.



> Thanks,
> 
> Jim
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Effect of blobs on performance

[Dan Kennedy]

> I'm quite interested in hearing people's reasoning for going the blob route, 
> when you have a perfectly good "database" format for "blobs" already (various 
> filesystems).



Three technical reasons for me personally:

* Can include blob operations as part of atomic transactions.

* In SQLite, my blobs won't be corrupted if the machine loses power
  the way they (probably) will be if I write my own code to access
  the file-system.

* Don't have to invent my own scheme for mapping from database entry
  to blob file, nor my own cross platform

And the claim that it's less work to program that way.

Better question IMO: What are peoples reasons for calling open() 
when you could just as easily call sqlite3_open()? :)



> > --
> > D. Richard Hipp  <[EMAIL PROTECTED]>
> >
> >
> > ---
> >-- To unsubscribe, send email to [EMAIL PROTECTED]
> > ---
> >--
> 
> 
> 


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

Re: [sqlite] developers mailing list

[Dan Kennedy]

On Sun, 2007-02-25 at 22:48 +0100, Jakub Ladman wrote:
> Dne neděle 25 únor 2007 19:25 Martin Jenkins napsal(a):
> > Jakub Ladman wrote:
> >
> > You want "make -f Makefile.linux-gcc"
> Oh, of course, sorry.
> 
> And how to fix this?
> 
> [EMAIL PROTECTED] ~/src/sqlite-3.3.13 $ make -f Makefile.linux-gcc
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DTHREADSAFE=1 
> -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src  -c 
> ../sqlite-3.3.13/src/tclsqlite.c
> ../sqlite-3.3.13/src/tclsqlite.c:20:17: tcl.h: není souborem ani adresářem
> ../sqlite-3.3.13/src/tclsqlite.c:54: error: syntax error before "Tcl_Interp"
> ../sqlite-3.3.13/src/tclsqlite.c:54: warning: no semicolon at end of struct 
> or 
> union
> 
> I do not need tcl.

It's seems a bit strange to me that Makefile.linux-gcc includes tcl in
the build by default, but it does. Maybe that should change...

If you add "-DNO_TCL" to the OPTS variable in Makefile.linux-gcc this
error should go away. i.e. add the following line somewhere after the 
"OPTS = -DNDEBUG" bit:

OPTS += -DNO_TCL
 


Dan.




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

Re: [sqlite] developers mailing list

[Dan Kennedy]

On Mon, 2007-02-26 at 11:37 +0100, Jakub Ladman wrote:
> > It's seems a bit strange to me that Makefile.linux-gcc includes tcl in
> > the build by default, but it does. Maybe that should change...
> >
> > If you add "-DNO_TCL" to the OPTS variable in Makefile.linux-gcc this
> > error should go away. i.e. add the following line somewhere after the
> > "OPTS = -DNDEBUG" bit:
> >
> > OPTS += -DNO_TCL
> >
> >
> >
> > Dan.
> 
> :-(
> 
> This i get when i use makefile which is attached to this mail.

I think you accidentally typed "OPTS += -DNOTCL" instead of "OPTS +=
-DNO_TCL". Have another shot ;)

Dan.



> Jakub Ladman
> 
> [EMAIL PROTECTED] ~/src/sqlite-3.3.13 $ make -f mymakefile
> sed -e s/--VERS--/`cat ../sqlite-3.3.13/VERSION`/ \
> -e s/--VERSION-NUMBER--/`cat ../sqlite-3.3.13/VERSION | 
> sed 's/[^0-9]/ /g' | awk '{printf "%d%03d%03d",$1,$2,$3}'`/ \
>  ../sqlite-3.3.13/src/sqlite.h.in >sqlite3.h
> gcc -g -O2 -o lemon ../sqlite-3.3.13/tool/lemon.c
> cp ../sqlite-3.3.13/tool/lempar.c .
> cp ../sqlite-3.3.13/src/parse.y .
> ./lemon -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL parse.y
> mv parse.h parse.h.temp
> awk -f ../sqlite-3.3.13/addopcodes.awk parse.h.temp >parse.h
> cat parse.h ../sqlite-3.3.13/src/vdbe.c | 
> awk -f ../sqlite-3.3.13/mkopcodeh.awk >opcodes.h
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/alter.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/analyze.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/attach.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/auth.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/btree.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/build.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/callback.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/complete.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/date.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/delete.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/expr.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/func.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/hash.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/insert.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/loadext.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/main.c
> sort -n -b -k 3 opcodes.h | awk -f ../sqlite-3.3.13/mkopcodec.awk >opcodes.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> opcodes.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/os.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/os_os2.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/os_unix.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/os_win.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL 
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c 
> ../sqlite-3.3.13/src/pager.c
> sh4-pc-linux-

Re: [sqlite] PRAGMA temp_store -- is there a default_temp_store?

[Dan Kennedy]

On Mon, 2007-02-26 at 09:15 -0500, Samuel R. Neff wrote:
> The SQLite optimization faq [1] mentions a PRAGMA default_temp_store which
> should set temp_store on a per-database level.  However this doesn't seem to
> be correct--the official docs [2] don't mention default_temp_store and
> calling "PRAGMA default_temp_store;" always returns nothing (although
> calling "PRAGMA default_temp_store = MEMORY;" returns without error).
> 
> If default_temp_store isn't available, is temp_store a per-connection
> setting or per-database?

per-connection.

Dan.



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

Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

[Dan Kennedy]

On Mon, 2007-02-26 at 17:05 -0800, Travis Daygale wrote:
> I'll do that.  I was troubled enough by that bug report and this new testing 
> info to be so motivated.  :-)

This would be handy for Tcl developers.

In case you haven't noticed it yet, the SQL quote() function
in func.c will help with this. It's marked as experimental,
but it will help you get around some fiddly issues with strings
that look like numbers and so on.

Dan.




> If I think about it, the copy method has "filename" as an argument, and a 
> dump method for the tcl driver would need that too.  All other methods I 
> believe do not need to specify the filesystem particulars (i.e. a filename 
> path).
> 
> Is that (partly?) why the copy is not tested and why there is no db1 dump 
> filename method?  
> 
> 
> -T
> 
> 
> [EMAIL PROTECTED] wrote: Travis Daygale  wrote:
> > That is useful to know (i.e. non-testing of the shell).  Thanks.
> > 
> > Does "the core" include the tcl driver (what I use)?  (It must- the driver 
> > is in there and the testing is done with tcl, all of this being partly why 
> > I chose tcl for my app- but I want to make sure I'm not somehow 
> > misunderstanding...)
> 
> Everything except the COPY command is tested.
> 
> > 
> > Then:
> > 
> > How might one do the equivalent of a .dump from a trivial tcl script (and 
> > therefore avoid the shell)?   Sort of a reverse "copy" method... and not 
> > the same as logging (trace).  Is there a way to dump from tcl?  Am I being 
> > stupid here- I haven't seen it...
> > 
> > Based on the testing info, if one could do this, presumably one would have 
> > a (more reliable) dump/backup in a simple script.  (And if it happens that 
> > one's sqlite code is tcl using the tcl driver, as mine is, so much the 
> > better in all kinds of ways including crossplatform considerations.)
> > 
> 
> It would probably not require more than a few lines of TCL code to
> implement a "dump" command as a TCL proc.  Why don't you work something
> up and post it on either the TCLers wiki or on the SQLite wiki or
> both?
> 
> --
> D. Richard Hipp  
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
>  
> -
> Expecting? Get great news right away with email Auto-Check.
> Try the Yahoo! Mail Beta.


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

Re: [sqlite] Table Info Pragmas as Virtual Table?

[Dan Kennedy]

On Tue, 2007-02-27 at 10:48 +0100, Michael Schlenker wrote:
> Hi all,
> 
> for some uses its nice to query the PRAGMA and the sqlite_master table 
> with a single query instead of multiple PRAGMA statements.
> 
> Could the Pragmas to query the database schema be converted to virtual 
> tables? (that would enable joins with sqlite_master and other nice uses 
> to get all schema info for a table with a single statement).
> 
> Or did some already do something like that?

There is some code for an sqlite extension in the src/test_schema.c of
the distribution that provides a read-only view of the database schema
as a virtual table.

Dan.



> Michael
> 
> 


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

Re: [sqlite] UTF16 Encoding

[Dan Kennedy]

On Wed, 2007-02-28 at 13:13 +0100, Pavan wrote:
> Hi Lucas,
> 
> >Shouldn't it be "sqlite3_open16(L"test.db",&db)" ?
> 
> I tried this call. It works. But, the db file is created only with the first
> character name
> and the extenstion is also missing. (its like 't')
> 
> Also, can you point to me at some documentation which explains what does
> the 'L' mean in the API ?

It means "string literal", and is used for building an array of wchar_t
elements (instead of regular 8-bit char). The thing is, C doesn't
say how big wchar_t is supposed to be. Some compilers make it 32-bit
(yours could be one of these), and others make it 16-bit. There
is, AFAIK, no portable way to declare UTF-16 string constants using C.

Please inform me if I'm wrong about that, it's something I'd like to 
know how to do :)

But I'm thinking you really want to do this anyway:

sqlite3_open("test.db", &db);
sqlite3_exec(db, "PRAGMA encoding = UTF-16", 0, 0, 0);

sqlite3_open16() just converts the argument back to UTF-8 and does
exactly the above anyway.

Dan.


> Thanks,
> Pavan.
> 
> 
> On 2/28/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> >
> > On 2/28/07, Pavan <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > >
> > > When i tried to create the db file using API sqlite3_open16("test.db
> > ",&db)
> > > it creats the file name with some junk
> > > characters.
> >
> > Shouldn't it be "sqlite3_open16(L"test.db",&db)" ?
> >
> > Regards,
> > ~Nuno Lucas
> >
> > > The file name is correct if i use sqlite3_open("test.db",&db).  Does it
> > mean
> > > sqlite3 does not support
> > > UTF16 encoding ?
> > > or
> > > Am i missing some package ?
> > >
> > > Thanks,
> > > Pavan.
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
> 
> 


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

Re: [sqlite] How to interleave calls to sqlite3_prepare_v2 and sqlite3_step?

[Dan Kennedy]

On Thu, 2007-03-01 at 05:35 +0100, daan wrote:
> I'm compiling a SQL statement (consisting of multiple statements separated
> by semicolons) using sqlite3_prepare_v2 into a series of sqlite3_stmt
> pointers, later to be executed with sqlite3_step().
> 
> A statement such as:
> 
>   DROP TABLE IF EXISTS test;
>   CREATE TABLE test (id INTEGER, name TEXT);
> 
> is compiled into two sqlite3_stmt pointers that are subsequently fed
> through sqlite3_step(): this works fine. That is, the first time I run the
> program (and the database didn't exist upon program start). When I run the
> program the 2nd time (and the database indeed exists upon program start)
> the prepare_v2 bails out with ERROR and error 'table test already exists'.
> 
> When the statement above is fed through sqlite3_exec() the error does not
> occur. When I had a look at the sqlite3_exec code it struck me that it
> interleaves the calls to prepare/step/reset in a different way:
> 
>   prepare/step/reset prepare/step/reset
> 
> whereas I am trying to
> 
>   prepare/prepare step/step reset/reset
> 
> When I change my code to a similar interleaving scheme as sqlite3_exec I
> dont have any problems.
> 
> My question is: is it indeed required to interleave the calls to
> sqlite3_prepare_v2 and sqlite3_step in a similar way as sqlite3_exec does,
> or should it be possible to fully precompile a series of statements which
> then later can be executed?

When you compile a statement, it is compiled against the current
database schema. In the second run it tried to compile 
"CREATE TABLE test" against a schema that already contained the table
"test". Hence the error message.

Precompiling a series of statements to be executed later will be fine,
provided you are not modifying the database schema.

Dan.



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

Re: [sqlite] Custom collate - on field or index or both?

[Dan Kennedy]

On Thu, 2007-03-01 at 15:13 -0800, jp wrote:
> Hi, I have a custom collation sequence (e.g.
> mycollate).  Are there any advantages in terms of
> performance of declaring this collation at the table
> level, instead of just at the index level?
> 
> For example, if I have:
> 
>  CREATE TABLE people AS (
>  country_id char(02),
>  lastname varchar(100), 
>  phone varchar(50) 
>  );
> 
>  CREATE UNIQUE INDEX people_mycollate on people (
>   country_id, 
>   lastname COLLATE mycollate
>   );
> - - - - - -
> ...will the following use the index (about 500k recs)?
> - - - - - -
>  SELECT lastname FROM people 
>  WHERE country_id='US' AND lastname>'A' 
>  ORDER BY country_id,lastname COLLATE mycollate
>  LIMIT 100;
> - - - - - -

SQLite will use the index to implement the country_id='US' clause,
and the ORDER BY, but not the lastname>'A' clause. This is because
the lastname>'A' doesn't use the "mycollate" collation. 

If the COLLATE clause was specified as part of the table 
definition, then lastname>'A' would be a "mycollate" comparison
and the index would be used for this too.

Dan.



> I want to separate the table definition from the
> search/query/sort order, to have the flexibility of
> creating/dropping indexes as needed for different
> collations depending on the user's locale.
> 
> jp
> 
> 
> 
>  
> 
> The fish are biting. 
> Get more visitors on your site using Yahoo! Search Marketing.
> http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Last ID from Primary Key through ODBC

[Dan Kennedy]

On Sat, 2007-03-03 at 19:18 -0500, Mitchell Vincent wrote:
> I found the nifty ODBC driver for SQLite and have been trying to use
> it to replace a 2.8 SQlite implementation in some desktop software.
> It's working very well except for a rather large problem of not being
> able to call the last_id API functions. I need to get the primary keys
> assigned to parent tables in order to preserve referential integrity.
> 
> SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous
> seeing as multipl people *could* be accessing this database file at
> the same time..

What if you wrap the INSERT and "SELECT MAX(id)" statements together
in a transaction?

> 
> Are there alternatives?
> 
> Thanks!
> 


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

Re: [sqlite] Question about shared cache and read_uncommitted

[Dan Kennedy]

On Sat, 2007-03-10 at 14:11 +0900, Brownie wrote:
> Hi,
> 
> I have two questions about shared cache mode of SQLite.
> 
> 1. When is shared cache of SQLite deallocated?
> A last call of sqlite3_close()? Or end of thread procedure?

The former. The shared cache (and shared schema) are reference 
counted. Resources are released as soon as the number of connections
to the shared cache drops to zero.

> 2. Can I use read_uncommitted isolation in the following case?
> 
> (Connection1 and Connection2 is established by the same thread
> and shared cache mode was enabled)
> 
> Connection1: Opened for :memory:
> ATTACH DATABASE 'mydb.db' as mydb;
> SELECT myname FROM mydb.mytable;
> 
> Connection2: Opened for mydb.db
> INSERT INTO mytable(myname) VALUES('myname');

I think so. But I'm not 100% sure I understand the question.

If Connection1 is in read_uncommitted mode, then the SELECT will
neither block nor be blocked by the INSERT statement executed
by Connection2.

That the first database file was ATTACHed instead of being opened
directly makes no difference.

Dan.



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

Re: [sqlite] Question about shared cache and read_uncommitted

[Dan Kennedy]

On Sat, 2007-03-10 at 22:22 +0900, Brownie wrote:
> Thanks Dan,
> 
> > That the first database file was ATTACHed instead of being opened
> > directly makes no difference.
> 
> A cache of :memory: database also be shared on shared cache mode ?
> Or :memory: database of each connections are irrelevant ?

Shared cache mode does not apply to :memory: databases. It is not
currently possible for more than one database handle to access a 
single in-memory database.

Dan.


> Regards,
> 
> 2007/3/10, Dan Kennedy <[EMAIL PROTECTED]>:
> > On Sat, 2007-03-10 at 14:11 +0900, Brownie wrote:
> > > Hi,
> > >
> > > I have two questions about shared cache mode of SQLite.
> > >
> > > 1. When is shared cache of SQLite deallocated?
> > > A last call of sqlite3_close()? Or end of thread procedure?
> >
> > The former. The shared cache (and shared schema) are reference
> > counted. Resources are released as soon as the number of connections
> > to the shared cache drops to zero.
> >
> > > 2. Can I use read_uncommitted isolation in the following case?
> > >
> > > (Connection1 and Connection2 is established by the same thread
> > > and shared cache mode was enabled)
> > >
> > > Connection1: Opened for :memory:
> > > ATTACH DATABASE 'mydb.db' as mydb;
> > > SELECT myname FROM mydb.mytable;
> > >
> > > Connection2: Opened for mydb.db
> > > INSERT INTO mytable(myname) VALUES('myname');
> >
> > I think so. But I'm not 100% sure I understand the question.
> >
> > If Connection1 is in read_uncommitted mode, then the SELECT will
> > neither block nor be blocked by the INSERT statement executed
> > by Connection2.
> >
> > That the first database file was ATTACHed instead of being opened
> > directly makes no difference.
> >
> > Dan.
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >


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

Re: [sqlite] sqlite3_update_hook and transactions

[Dan Kennedy]

On Mon, 2007-03-12 at 10:51 +0100, Jef Driesen wrote:
> I was planning to use the sqlite3_update_hook function to notify my GUI
> about changes. The idea was that every part of the GUI can update itself
> when a database change is detected. But during testing, I encountered
> some problems with this approach together with transactions.
> 
> When I group some SQL statements inside a transaction (for performance
> or because they have to succeed or fail all together), the callback
> function is executed for every statement:
> 
> BEGIN
> statement 1 -> callback function called
> statement 2 -> callback function called
> COMMIT
> 
> But if the COMMIT is replaced with a ROLLBACK (for instance when an
> error is detected), the callback functions are still called and the GUI
> is updated with data that is not actually written to the database.
> 
> Any ideas on how to prevent this from happening?


Accumulate updates in a custom data structure (list or something) each
time the update_hook() callback is invoked.

Also register callbacks with sqlite3_commit_hook() and
sqlite3_rollback_hook(). When the commit_hook() callback is invoked,
update the GUI. When either the commit or rollback hooks are invoked,
reset the data structure to empty.

Dan.



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

Re: [sqlite] SQLite v/s SQLite3 Performance Assay

[Dan Kennedy]

> My Intentions are towards exploring the reason behind these differences; and
> what can be
> done to counter these performance differences. I'm seeking some pointers
> from the Community.

Version 3 has a different default safety-level (default FULL) to 
version 3 (default NORMAL). So if you didn't explicitly set the
safety-level during the tests, then version 3 was syncing the
disk more often than version 2. I think this might be why version 3
appears slower in Test Case I (Inserts).

The results of cases II to IV seem odd. Can you post the test
code to the list?

Dan.

> Many Thanks In Advance.
> 
> Test Setups:
> +--+--+-+-+-+
> | CPU   |(x86) |  (x86_64)| |
> | |
> |---|   P4 2.4 GHz |  Core2Duo| |   Athlon|
> Davinci |
> | OS|  | 1.86GHz x 2  |BSP15|   X2 (64)
> | |
> +---+--+--+-+-+-+
> | WinXP(32) |  X   |  X   | |
> | |
> +---+--+--+-+-+-+
> | WinXP(64) |  |  X   | |  X
> | |
> +---+--+--+-+-+-+
> | Linux(32) |  X   |  X   |  X  |
> |X|
> +---+--+--+-+-+-+
> | Linux(64) |  |  X   | |  X
> | |
> +---+--+--+-+-+-+
> 
> DB Schema:
>  It Consists of 4 Identical Tables
> tbl01{ code integer primary key
>   ,code01
>   ,code02
>   ,code03
>   ,code04
>   ,orderField
>   ,field01 }
> 
> Implementation:
> Application were written in C using SQLite & SQLite3's C API sets.
> 
>   Case I:
> SQL Insert Queries where fired in Sequential Progression; making 10
> Entries
> in First Table; 100 Entries in 2nd Table; 1000 Entries in 3rd Table and
> finally 1 Entries in 4th Table; Data below is Collective Time Taken
> to make Inserts in all 4 tables, expressed in millisecs.
> 
>  Insert |   SQLite|   SQLite3-> 0 Entries
> +-+-
>  Win32 x86  |78896| 97800
>  Win32 x86_64   |82100| 85000
>  Win64 x86_64   |  -  |   -
>  Linux32 x86|76900|100016
>  Linux32 x86_64 |87728| 99004
>  Linux64 x86_64 |79200| 99102
>  Linux64 x64|79788| 98794
>  Linux BSP15|37888| 37566
>  Linux Davinci  |  -  |   -
> --+-
> 
>Case II:
>  SQL Select with simple query on a single table fetching all records.
> 
>  Select on Simple Qry|   SQLite|   SQLite3   -> 1 (x 8 Cols)
> Entries
> -+-+-
>  Win32 x86   | 125 |   578
>  Win32 x86_64| |
>  Win64 x86_64|  -  |   -
>  Linux32 x86 |   8 |   297
>  Linux32 x86_64  |   6 |   251
>  Linux64 x86_64  |   6 |   149
>  Linux64 x64 |   7 |   144
>  Linux BSP15 | 287 | 22069
>  Linux Davinci   |  -  |   -
> ---+-
> 
>Case III:
>  SQL Select with Join of 2 Tables fetching all records.
> 
>  Select on Moderate Qry|   SQLite|   SQLite3 -> 1 (x 15
> Cols) [2 Table Join]
> ---+-+-
>  Win32 x86 | 5532|  1172
>  Win32 x86_64  | |
>  Win64 x86_64  |  -  |   -
>  Linux32 x86   |  439|   669
>  Linux32 x86_64|  251|  1108
>  Linux64 x86_64|  272|  1120
>  Linux64 x64   |  259|  1090
>  Linux BSP15   | 9258| 49773
>  Linux Davinci |  -  |   -
> ---+-+-
> 
>Case IV:
>  SQL Select with Join of 3 Tables fetching redundant records.
> 
>  Select on Complex Qry|   SQLite|   SQLite3  -> 9 (x 22
> Cols) [3 Table Join with redundant entries]
> --+-+-
>  Win32 x86| 6593|110157
>  Win32 x86_64 | |
>  Win64 x86_64 |  -  |  

Re: [sqlite] a problem trying to invoke sqlite3 commands from a C application

[Dan Kennedy]

On Tue, 2007-03-20 at 02:44 +0200, Rafi Cohen wrote:
> Hi, I'm calling system from a C application in order to invoke sqlite3
> database, then continue with commands: .separator "," and .import file
> table, all in a single string as argument to system.
> sqlite3 is indeed invoked with the correct database, but the problem is
> that the .import command is reported as part of .separator argument.
> There is just a blank between the 2 commands. Should there be a
> different separation betrween the 2 commands?

You probably need a newline following each individual dot-command:

  ".import file tbl\n.dump"


Dan.



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

Re: [sqlite] strict affinity mode

[Dan Kennedy]

On Fri, 2007-03-23 at 18:53 +0100, stephan nies wrote:
> Hi I read about a "strict affinity mode"
> on http://www.sqlite.org/datatype3.html.
> 
> But from searching the net, i get the impression
> that this mode is not yet implemented.
> 
> I think it seems to be a FAQ since it is even listed
> in the  WIKIFAQ:
> http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq
> 
> but without an answer.
> 
> I think this should be clarified. Its no problem if the
> feature is not implemented, but i find it annoying
> to hunt for a feature that is officially  documented,
> but without any hint on how to activate it if possible.
> 
> IS THIS MODE EXISTING OR NOT ?

I think it was a plan that did not materialize.

Dan.


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

Re: [sqlite] CREATE TRIGGER IF NOT EXISTS throws error

[Dan Kennedy]

I think you need 3.3.8 or greater.


On Mon, 2007-03-26 at 07:39 +0200, stephan nies wrote:
> I am using sqlite 3.3.5 .
> 
> On 3/25/07, Eric Pankoke <[EMAIL PROTECTED]> wrote:
> >
> > What version of SQLite are you using?  I'm not sure the IF EXISTS option
> > was present until a certain version of 3.x (though I'm not sure which
> > version).
> >
> > Eric Pankoke
> > Founder
> > Point Of Light Software
> > http://www.polsoftware.com/
> >
> >
> > -Original Message-
> > From: stephan nies [mailto:[EMAIL PROTECTED]
> > Sent: Sunday, March 25, 2007 1:47 PM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] CREATE TRIGGER IF NOT EXISTS throws error
> >
> > Hello,
> > when i build a test table with:
> >
> > CREATE TABLE test(a text);
> >
> > and try to implement a trigger using:
> >
> > CREATE TRIGGER IF NOT EXISTS trig_name
> > BEFORE INSERT ON test
> > FOR EACH ROW BEGIN
> > SELECT RAISE(ROLLBACK, 'blabla')
> > WHERE NEW.a = 'pong';
> > END;
> >
> > I get this error message:
> > SQL error: near "NOT": syntax error
> >
> >
> > if i omit the "IF NOT EXISTS" everything woks fine.
> > According to the Documentation at
> > http://www.sqlite.org/lang_createtrigger.html
> > SQLite should understand this syntax.
> > So what am i doing wrong here?
> >
> > Thanks,
> > Stephan
> >
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >


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

Re: [sqlite] Error reporting problem

[Dan Kennedy]

On Mon, 2007-03-26 at 17:08 +0200, Vivien Malerba wrote:
> On 3/26/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:
> > Vivien Malerba wrote:
> > > Hi!
> > >
> > > I've got an error reporting problem when trying to insert a row which
> > > breaks a UNIQUE constraint in a table in a C program, I get the
> > > following error with sqlite3_errmsg():
> > > "SQL logic error or missing database"
> > >
> > > If I fire the sqlite3 program and run the same SQL query, I get the
> > > following error:
> > > "SQL error: column name is not unique"
> > > which is much more obvious to understand.
> > >
> > > Any idea how I could get that error message calling sqlite3_errmsg()?
> >
> > The pysqlite wrapper has a bug which reports the same error message if
> > you try to nest transactions. I fixed the wrapper (locally) by adding an
> > sqlite_reset(). I think there's a new API (sqlite_prepare_v2()?) which
> > has the same effect. If you can't search (say) gmane then post a tiny
> > code snippet and someone who uses the C API more than I do will spot the
> > problem straight away.
> 
> Here is a sample test case, just uncompress, run "make" and "./test".
> Here is what I get using SQLite 3.3.13 (On Linux):
> SQL error (step): SQL logic error or missing database
> SQL error (step): column name is not unique
> 
> It shows that calling sqlite3_reset() seems to be required (contrary
> to what the doc says), or maybe I got something wrong...

Even using the prepare_v2() interface, the database handle error-code
and error-message (the stuff returned by sqlite3_errcode() and 
sqlite3_errmsg() respectively) are not populated by sqlite3_step(). 
After sqlite3_step() reports an error you need to call either
sqlite3_reset() or sqlite3_finalize() to copy the error-code and
message from the statement to the database handle.

The change the prepare_v2() API makes is that in your example 
sqlite3_step() returns SQLITE_CONSTRAINT when it hits the constraint.
Using sqlite3_prepare() it would have returned the generic SQLITE_ERROR.

Dan.



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

Re: [sqlite] matching only part of a string

[Dan Kennedy]

On Wed, 2007-03-28 at 12:27 +0530, Lloyd K L wrote:
> Hi,
>   My table contains a text field called Name. Let the data be
> 
> Name
> ---
> Abc
> abcd
> AB
> cab
> def
> 
> I want to selcct all the rows which contains the term ab (not case
> sensitive). How can I do this?

SELECT name FROM "My table" WHERE name LIKE '%ab%'




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

Re: [sqlite] The IN keyword

[Dan Kennedy]

> I am trying to use this query:
> 
> SELECT * FROM Files WHERE extension IN ('mp3','avi','ogg');
> 
> I am not getting any row back though.
> 
> SELECT * FROM Files WHERE extension='mp3' works though.

If this is literally the case, it's probably a bug.

> I am using sqlite3_step to execute a prepared statement looking like this:
> 
> SELECT * FROM Files WHERE extension IN (:extension)
> 
> where I bind a wchar_t* to extension.

Using which function - sqlite3_bind_text() or sqlite3_bind_text16()? If
your compiler happens to define wchar_t as a 16-bit type you can get
away with sqlite3_bind_text16(). However many compilers define wchar_t
as 32-bit so such code is not portable. 

Dan.




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

Re: [sqlite] The IN keyword

[Dan Kennedy]

On Wed, 2007-03-28 at 11:11 +0200, Jonas Sandman wrote:
> Yes, it seems so.
> 
> Still, can this be done without knowing how many extensions there are on
> beforehand?
> what happens if I make a
> 
> SELECT * FROM Files WHERE extension IN (:ext1, :ext2, :ext3, :ext4) and only
> bind :ext1 but not the others? An error?

After a statement is prepared, SQL NULL is implicitly bound to all
variables. So if you bind to :ext1 and :ext2 only, it's like doing:

  SELECT * FROM files where extension IN ('avi', 'mpg', NULL, NULL);

Which would probably work for you. Note that sqlite3_reset() does not
reset the values of SQL variables.

> Is it slow building a statement with sqlite3_prepare16_v2 before executing
> it?

Probably not. If you're not doing it more than a few times per second
I wouldn't worry about it.

> Best regards,
> Jonas
> 
> On 3/28/07, Alan Barkway <[EMAIL PROTECTED]> wrote:
> >
> > On Wed, 28 Mar 2007 08:38:55 +0100, Jonas Sandman
> > <[EMAIL PROTECTED]> wrote:
> >
> > [...]
> > >
> > > SELECT * FROM Files WHERE extension IN (:extension)
> >
> > Just a guess here but might it be the case that you need to do this?
> >
> > SELECT * FROM Files WHERE extension IN (:ext1, :ext2, :ext3)
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >


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

Re: [sqlite] exclusive2.test failures

[Dan Kennedy]

> exclusive2-1.7...
> Expected: [1]
>  Got: [2]
> exclusive2-1.9...
> Expected: [1]
>  Got: [0]
> exclusive2-1.10... Ok
> exclusive2-1.11... Ok
> exclusive2-2.1... Ok
> exclusive2-2.2... Ok
> exclusive2-2.3... Ok
> exclusive2-2.4... Ok
> exclusive2-2.5...
> Expected: [5]
>  Got: [3]
> exclusive2-2.6... Ok
> exclusive2-2.7... Ok
> exclusive2-2.8...
> Expected: [1 {database disk image is malformed}]
>  Got: [0 {64 ca9201f79a7fa971fb81ac2b382d71d2}]
> exclusive2-3.0... Ok

Hi,

It could be that these are due to a defect in the test 
script. I made some minor changes (added [flush] commands
after file I/O) to the test script that is failing.

http://www.sqlite.org/cvstrac/getfile?f=sqlite/test/exclusive2.test&v=1.2

Could you help test this by downloading the new version
of exclusive2.test from the above link, copying it
over the old one (in the test/ directory of the source
distribution) and re-running the "make test"? You don't
need to do a make clean or rebuild or anything.

Thanks very much.

Regards,
Dan.




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

Re: [sqlite] Re: Default sqlite memory management policy

[Dan Kennedy]

On Wed, 2007-04-04 at 23:36 -0400, Rich Rattanni wrote:
> On 4/3/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> > All:
> >I was wondering exactly how SQLite handles freeing memory used
> > from the heap when compiled without SQLITE_ENABLE_MEMORY_MANAGEMENT?
> >
> >Will absolutely no memory be freed, or is there some default
> > memory management scheme used?
> >
> 
> Let me explain my question better...
> SQLITE on my system has been compiled without the
> SQLITE_ENABLE_MEMORY_MANAGEMENT define.  I have a thread that writes a
> hundred or so samples into a database every 30 seconds.  I do not open
> and close the database pointer each time, but I do prepare and
> finalize a statement for each transaction.  I have notices that the
> memory used by my program slowly but surely increases as the program
> runs (about 24 hrs sees a 40%+ increase in system memory).  I have
> checked, and rechecked the program for any memory leaks on my part,
> but I have no found any obvious ones.  I just wanted to know if
> sqlite, as compiled for my system, will use as much memory as it sees
> fit in a particular thread if compiled without
> SQLITE_ENABLE_MEMORY_MANAGEMENT?
> 
> Can anyone answer this for me?

Defining the SQLITE_ENABLE_MEMORY_MANAGEMENT includes the 
following APIs in the build:

  int sqlite3_release_memory(int N);
  int sqlite3_set_soft_heap_limit(int N);

See the comments above these two functions in sqlite3.h for
details.

SQLite should not leak memory whether this macro is defined
or not. How large are the leaks you are seeing?

Dan.



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

Re: [sqlite] sqlite3_prepare_v2 schema error Fatal, need help please.

[Dan Kennedy]

On Thu, 2007-04-05 at 04:04 -0700, pompomJuice wrote:
> Ok.
> 
> I went and re-prepared the statement anyway even though the documentation
> says it won't work. This trick only works if you finalize the failed
> statement after the step command. Otherwhise you keep on getting
> SQLITE_SCHEMA errors which might cause and endless loop if so implemented.
> 
> Interesting.

Is this with 3.3.14 code? Also, do you know if there are any other 
statements created with the same db-handle that have been 
sqlite3_step()'d but not sqlite3_finalized()'d or sqlite3_reset()'d?

Finally, instead of re-prepairing the busted statement, what happens
if you sqlite3_reset() it and then try to run it again?

Dan.


> 
> pompomJuice wrote:
> > 
> > Hello.
> > 
> > I recently rewrote most of my SQLite wrappers to now ignore SCHEMA errors,
> > as these are now automagically handled by the new sqlite3_prepare_v2 API.
> > The logic changes were simple so I did not bother to test it and continued
> > with development. Now that development is complete and testing started
> > again I am getting the same old schema errors I use to get with the
> > deprecated prepare API. Only now I can’t get rid of them as re-prepairing 
> > with v2 won't make the problem go away.
> > 
> > I am therefore inclined to think that I am trying to use SQLite in a way
> > that is not permitted. Basically what I have is one .db file that is being
> > accessed by multiple binaries. If one binary drops or adds tables/indexes
> > the other binary fails with the schema error. There is no way that I can
> > rather use one binary with multiple threads. 
> > 
> > So my question is. Can SQLite recover from schema changes caused by other
> > processes or do I need to revert to the deprecated prepare API?
> > 
> > Many thanks for any help in this regards.
> > 
> > Werner.
> > 
> > 
> > 
> > 
> 


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

Re: [sqlite] sqlite3_prepare_v2 schema error Fatal, need help please.

[Dan Kennedy]

On Thu, 2007-04-05 at 05:37 -0700, pompomJuice wrote:
> Yes this is with the 3.3.14 code. I initially got the problem with the 3.3.12
> code so I just upgraded to the 3.3.14 code but it behaves exactly the same.
> 
> Just to recap, my proposed fix was
> //---
> while( rc == SQLITE_SCHEMA )
>   rc = prepare_v2;
>   // Some generic rc error checking here for prepare_v2
>   rc = step;
>   if( rc == SQLITE_SCHEMA )
>  finalize;
>  continue;
> end;
> // Some generic error checking for the step!=SQLITE_SCHEMA
> 
> You proposed:
> 
> //---
>   rc = prepare_v2;
>   // Some generic rc error checking here for prepare_v2
> while( rc == SQLITE_SCHEMA )
>   rc = step;
>   if( rc == SQLITE_SCHEMA )
>  reset;
>  continue;
> end;
> // Some generic error checking for the step!=SQLITE_SCHEMA
> 
> The proposed method works and I will change mine to work like that. Still is
> this the way it is done or am I missing something. I thought step is
> supposed to handle SQLITE_SCHEMA errors automatically.

Right, with prepare_v2(), the step() *should* have automatically 
re-prepared the statement and continued. And this is what happened
after the reset() - the next time step() was called the statement
was re-prepared() under the hood.

You should not have to do the reset() - this seems like a bug. I
only meant to suggest it for testing.

What is the SQL statement that the step() is failing for? And are
there any other SQL statements from the same handle active at the 
time? Definition of active: have called step() but not finalize() 
or reset().

Thanks,

Dan.
 

> Regards,
> Werner
> 
> 
> 
> 
> Dan Kennedy-4 wrote:
> > 
> > On Thu, 2007-04-05 at 04:04 -0700, pompomJuice wrote:
> >> Ok.
> >> 
> >> I went and re-prepared the statement anyway even though the documentation
> >> says it won't work. This trick only works if you finalize the failed
> >> statement after the step command. Otherwhise you keep on getting
> >> SQLITE_SCHEMA errors which might cause and endless loop if so
> >> implemented.
> >> 
> >> Interesting.
> > 
> > Is this with 3.3.14 code? Also, do you know if there are any other 
> > statements created with the same db-handle that have been 
> > sqlite3_step()'d but not sqlite3_finalized()'d or sqlite3_reset()'d?
> > 
> > Finally, instead of re-prepairing the busted statement, what happens
> > if you sqlite3_reset() it and then try to run it again?
> > 
> > Dan.
> > 
> > 
> >> 
> >> pompomJuice wrote:
> >> > 
> >> > Hello.
> >> > 
> >> > I recently rewrote most of my SQLite wrappers to now ignore SCHEMA
> >> errors,
> >> > as these are now automagically handled by the new sqlite3_prepare_v2
> >> API.
> >> > The logic changes were simple so I did not bother to test it and
> >> continued
> >> > with development. Now that development is complete and testing started
> >> > again I am getting the same old schema errors I use to get with the
> >> > deprecated prepare API. Only now I can’t get rid of them as
> >> re-prepairing 
> >> > with v2 won't make the problem go away.
> >> > 
> >> > I am therefore inclined to think that I am trying to use SQLite in a
> >> way
> >> > that is not permitted. Basically what I have is one .db file that is
> >> being
> >> > accessed by multiple binaries. If one binary drops or adds
> >> tables/indexes
> >> > the other binary fails with the schema error. There is no way that I
> >> can
> >> > rather use one binary with multiple threads. 
> >> > 
> >> > So my question is. Can SQLite recover from schema changes caused by
> >> other
> >> > processes or do I need to revert to the deprecated prepare API?
> >> > 
> >> > Many thanks for any help in this regards.
> >> > 
> >> > Werner.
> >> > 
> >> > 
> >> > 
> >> > 
> >> 
> > 
> > 
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> > 
> > 
> > 
> 


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

Re: [sqlite] in memory databases

[Dan Kennedy]

On Fri, 2007-04-06 at 13:22 -0700, Mike Johnston wrote:
> Hi,
> Is it possible to have memory databases located at a specific memory 
> address? I have a battery backed memory I'd like to store specific 
> information apart from the main database.

Not really possible at the moment.

An in-memory database will be trashed when the database connection
is closed, even if the memory it was using was persistent. I'm
guessing that's probably not what you're after, right?

Maybe your operating system can mount the memory as a file-system
and then you can create a database in that file-system?

Dan.



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

Re: [sqlite] Re: FTS does not support REPLACE

[Dan Kennedy]

On Mon, 2007-04-09 at 13:02 -0700, Scott Hess wrote:
> Thanks for the concise report.  I'm going to take a look at this
> today, to see if it's an fts1/2 problem.  If it's _not_, I'll still
> look at it, but perhaps with less eventual success :-).

I checked the code and conflict handling mechanisms (OR ERROR, 
OR ABORT, OR REPLACE) do not apply to virtual tables.

I'm not sure if this is documented anywhere or not. At the moment
if the vtable implementation decides a constraint has failed (in
an xUpdate()) it just returns an error code and the statement
transaction (if there is one) on the database is rolled back.

If the xUpdate() implementation also knew the current conflict
handling mechanism, it could implement OR REPLACE (but not OR
ABORT). By rights we would have to add xStmtBegin() etc. to 
the virtual module interface (although this would not be required
for FTS, only for modules that used external storage).

Something to think about anyhow... Do we want conflict handling 
for FTS (and other virtual modules)?

Dan.

> -scott
> 
> 
> On 4/9/07, Paul Quinn <[EMAIL PROTECTED]> wrote:
> > Very simple to replicate:
> >
> >
> >
> > CREATE VIRTUAL TABLE fts_table USING fts2(text);
> >
> > INSERT OR REPLACE INTO fts_table (rowid, text) VALUES (1, 'text1');
> >
> > INSERT OR REAPLCE INTO fts_table (rowid, text) VALUES (1, 'text2');
> >
> >
> >
> > The first insert succeeds, the second fails.
> >
> > Is FTS not supposed to support REPLACE, or is this a bug?
> >
> >
> >
> > -PQ
> >
> >
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Re: FTS does not support REPLACE

[Dan Kennedy]

On Tue, 2007-04-10 at 09:26 -0700, Scott Hess wrote:
> On 4/10/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:
> > I checked the code and conflict handling mechanisms (OR ERROR,
> > OR ABORT, OR REPLACE) do not apply to virtual tables.
> 
> > Something to think about anyhow... Do we want conflict handling
> > for FTS (and other virtual modules)?
> 
> I think OR REPLACE would be really unfortunate to lose, it's a pretty
> common idiom (especially using REPLACE in place of INSERT).
> 
> Do we really need to have this expressed in the API?  If the virtual
> table returned SQLITE_CONSTRAINT from xUpdate(), and guaranteed that
> the table was unmodified, then the core should be able to implement
> all of the variants.  I can see how expressing it in the API might
> allow certain implementations to be more efficient.

SQLite could take care of it if the only constraint that can fail is
a duplicate rowid. Just by saying:

if( xUpdate(INSERT)==SQLITE_CONSTRAINT ){
  xUpdate(UPDATE);
}

and other variants for UPDATE etc.

But that would leave no way for the virtual table to implement 
constraints related to other columns of the virtual table. At
least, that's my reason for thinking it would be better done as
a flag passed to the implementation of xUpdate().

Dan.



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

Re: [sqlite] SQLite Performance

[Dan Kennedy]

On Tue, 2007-04-17 at 11:53 +0100, Alberto Simões wrote:
> Hi
> 
> I've found SQLite faster than MySQL and Postgres for small/medium
> databases. Now I have big ones and I really do not want to change, but
> I have some performance issues.
> 
> Consider the following database schema:
> CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER,
> word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3,
> word4));
> CREATE INDEX tet_b ON tetragrams (word2);
> CREATE INDEX tet_c ON tetragrams (word3);
> CREATE INDEX tet_d ON tetragrams (word4);
> 
> And the following database size:
> sqlite> SELECT COUNT(*) from tetragrams;
> 18397532
> 
> Now, a query like
> SELECT FROM tetragrams WHERE word1 = 6;
> returns 166579 rows;
> 
> This query takes some time, but starts as soon as I type the query.
> The problem is that I want to use:
> 
> SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10;
> 
> and it takes.. five minutes and did not give the result yet...
> 
> Is there anything I can do to make it speed up CONSIDERABLY? I mean,
> this is to be used in a CGI and each CGI query will make 11 queries
> like the one above to the database.

You might need an index like:

  CREATE INDEX tet_e ON tetragrams(word1, occs);

Otherwise you have to sort the 166000 items each time the query 
is made.

Dan.



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

Re: [sqlite] Still getting "Insertion failed because database is full." errors

[Dan Kennedy]

> At first I thought this had solved the problem, because all in house testing
> runs beautifully.  However, as soon as the device is sent to the field, the
> error starts again.  Unfortunately, it means that I have never been able to
> catch this in debug.  I did, however, change the error reporting a little
> and got some more information.  The SQLiteException I am not getting
> includes this information:
> 
> Insertion failed because the database is full

That message is from the wrapper.

> database or disk is full

And the above is from sqlite3. The corresponding return code is 
SQLITE_FULL. Search source file "os_win.c" for where SQLITE_FULL
errors can be generated - there's only a couple of places. Odds
on it's one of them. Looks like on windows, any error writing
or seeking a file is reported as SQLITE_FULL.

> at System.Data.SQLite.SQLite3.Reset()
> at System.Data.SQLite.SQLite3.Step()
> at System.Data.SQLite.SQLiteDataReader.NextResult()
> at System.Data.SQLite.SQLiteDataReader.ctor()
> at System.Data.SQLite.SQLiteCommand.ExecuteReader()
> at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader()
> at ... etc etc

So is this really a SELECT? Probably good to check that.

If it is a SELECT, why would it be filling up the database?
Is it rolling back a journal file and running out of space
like that? Or the user rolling back the journal file doesn't
have permission to write the database file and SQLite is 
reporting the error incorrectly.

Check for a journal file on the device after the error. Also
run the SQLite integrity-check on the database.

Dan.



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

Re: [sqlite] SQLite Performance

[Dan Kennedy]

On Wed, 2007-04-18 at 11:06 +0100, Alberto Simões wrote:
> On 4/17/07, Alberto Simões <[EMAIL PROTECTED]> wrote:
> > On 4/17/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > > "=?ISO-8859-1?Q?Alberto_Sim=F5es?=" <[EMAIL PROTECTED]> wrote:
> > > >
> > > > Consider the following database schema:
> > > > CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER,
> > > > word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3,
> > > > word4));
> > > > CREATE INDEX tet_b ON tetragrams (word2);
> > > > CREATE INDEX tet_c ON tetragrams (word3);
> > > > CREATE INDEX tet_d ON tetragrams (word4);
> > > >
> > > > The problem is that I want to use:
> > > >
> > > > SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10;
> > > >
> > > > and it takes.. five minutes and did not give the result yet...
> > > >
> > > > Is there anything I can do to make it speed up CONSIDERABLY?
> > >
> > > CREATE INDEX tet_e ON tetragrams(word1, occs);
> 
> I tried:
> CREATE INDEX tet_a ON tetragrams(word1, occs);
> CREATE INDEX tet_b ON tetragrams(word2, occs);
> CREATE INDEX tet_c ON tetragrams(word3, occs);
> CREATE INDEX tet_d ON tetragrams(word4, occs);
> and PRIMARY KEY(word1,word2,word3,word4)
> 
> This works fairly well if I need
> SELECT * from tetragrams WHERE word1 = 'x' ORDER BY occs;
> 
> The problem is that I need as well
> SELECT * from tetragrams WHERE word1 = 'x' AND word2 = 'y' ORDER BY occs;
> and this is QUITE slow.

You could see if this one is faster:

  SELECT * FROM tetragrams 
  WHERE word1 = 'x' AND word2||'' = 'y' 
  ORDER BY occs;

Should take about the same time as the one that works "fairly well".
But return less rows. See also the ANALYZE command.

Dan.


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

Re: [sqlite] Still getting "Insertion failed because database isfull." errors

[Dan Kennedy]

On Wed, 2007-04-18 at 10:06 -0400, Joel Cochran wrote:
> OK, then I won't be worrying about Transactions for SELECT statements, it
> doesn't really apply to our application.
> 
> And some additionaly confirmation that Christian seems to have been right on
> key: according to the problems reported at the System.Data.SQLite forums,
> the problem is most likely due to the retaining of an ope Connection against
> the database residing on removable media.  When the system returns, the
> "pointer" to the media is not guaranteed to work again.  In other words,
> every time the system shuts down, there is the potential for losing database
> connectivity.  The recommended solution is to move the database to internal
> memory and use the CF card as a backup device.

If the win32 SetFilePointer() function fails (used to position the 
"pointer" at a given file offset, which SQLite does as part of a
SELECT) SQLite assumes the reason is that the disk is full and
returns SQLITE_FULL. This is probably what's happening here -
SetFilePointer() is saying "the file-system is gone!" and SQLite
is misinterpreting it. Probably SQLite should return SQLITE_IOERR
instead.

So why can't you just handle this in the application? Open and 
close the database connection when an SQLITE_FULL occurs. If
the device is really shutting down (not starting up) then the 
second attempt to open will fail. Or just try every couple
of seconds from that point on.

Dan.


> This also confirms why I can't replicate the problem in DEBUG: the device
> never sleeps and the connection is never lost.
> 
> I'll keep the list posted.
> 
> Joel
> 
> On 4/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > "Joel Cochran" <[EMAIL PROTECTED]> wrote:
> > > should I be using
> > > Transactions for SELECT statements?
> >
> > The only reason to use a transaction around SELECT statements
> > is if you want to make sure that the database is not changed
> > by another process in between consecutive SELECTs.
> >
> > It used to be the case that putting multiple SELECTs within
> > a transaction would be slightly faster since doing so avoided
> > a cache flush after each statement.  But as of version 3.3.16
> > that cache flush is avoided regardless of whether or not
> > transactions are used so performance should no longer be a
> > factor.
> >
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
> 
> 


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

Re: [sqlite] beginner: Is posible inside a result of a function call another function for delete or update the row??

[Dan Kennedy]

On Thu, 2007-04-19 at 13:29 -0500, David A O L wrote:
> I have a very basic sql statement, mainly Im printing it...
> 
> static int GuardaActividadEnArchivo(void *arg1, int argc, char **argv, char
> **azColName){
> int i;
> char *nombre, *ok, *ko, *actividad;
> nombre = ok = ko = actividad = 0;
> for (i = 0; i < argc; i++) {
> printf("%s = %s ", azColName[i], argv[i] ? argv[i] : "NULL");
> }
> printf("\n");
> // Here!
> return 0;
> }
> 
> 
> What I whant is that in // Here! I whant to update (increment, decrement,
> etc).
> 
> But it say that the DB is locked...

With sqlite v2, yes. If you were using sqlite v3 you could do the 
UPDATE in the SELECT callback.

> What I see is that I can use arg1 like a pointer to a linked list to hold
> data and then do the update...

True. That's the way to go with v2 I think.

> The question is: the anterior way is the only way???

I think anterior means "before"... I'm no wordsmith...

Dan.



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

Re: [sqlite] testing with single source file

[Dan Kennedy]

On Fri, 2007-04-20 at 10:13 +0200, Jens Miltner wrote:
> Am 20.04.2007 um 09:13 schrieb Jens Miltner:
> 
> >
> > Am 01.04.2007 um 20:05 schrieb Iulian Musat:
> >
> >>
> >> [EMAIL PROTECTED] wrote:
> >>> [...]
> >>> In past releases of SQLite, we have made available a ZIP archive
> >>> with preprocessed source files.  In the future, we may change this
> >>> so that instead of a ZIP archive full of individual files, we
> >>> ship a single "sqlite3.c" source file which contains all of the
> >>> source file in a single translation unit.  By this mode of
> >>> delivery, we hope to enable users to see the performance  
> >>> improvements
> >>> we are seeing in our red bars.
> >>
> >> I just want to thanks for adding the "sqlite3.c" target to the  
> >> makefile!
> >>
> >> All tests run without problems compiled with a sqlite3.c produced  
> >> from CVS head !
> >>
> >> Few notes:
> >>
> >> 1.  I had to change the declaration of these two functions in  
> >> test7.c:
> >> void sqlite3_server_start(void);
> >> void sqlite3_server_stop(void);
> >>
> >> 2. I compiled everything using "-Wall -O3"; fixed few compiler  
> >> warnings related to unused or not initialized variables (probably  
> >> irrelevant).
> >>
> >> 3. I didn't used -DSQLITE_MEMDEBUG, so all malloc and vtab_err  
> >> tests were skipped.
> >>
> >> 4. System used: Linux, i686, gcc 3.4.2, glibc 2.3.3,  
> >> Pentium4/3.00GHz/1MB L2.
> >
> >
> >
> > Hmmh - did the same with sqlite 3.3.16 on Mac OS X 10.4 (gcc 4.0.1,  
> > PowerPC G4) and the vtab_err tests apparently were not skipped  
> > (even though I didn't defined -DSQLITE_MEMDEBUG either), since they  
> > were reported to have failed:
> 
> Sorry, my bad - I did in fact define -DSQLITE_MEMDEBUG=1 when  
> building the tests, but still - the tests fail...

Might be this:
 
  http://www.sqlite.org/cvstrac/chngview?cn=3855

If so, then it is a bug in the test module only. Does cvs head
pass at the moment?

Dan.

> >
> > vtab_err-2.373...
> > Error message returned: vtable constructor did not declare schema: e
> >
> > Expected: [1 1]
> >  Got: [999 0]
> > vtab_err-2.374...
> > Error message returned: vtable constructor did not declare schema: e
> >
> > Expected: [1 1]
> >  Got: [999 0]
> > vtab_err-2.375...
> > Error message returned: vtable constructor did not declare schema: e
> >
> > [snip]
> >
> > Failures on these tests: vtab_err-2.373 vtab_err-2.374  
> > vtab_err-2.375 vtab_err-2.376 vtab_err-2.377 vtab_err-2.378  
> > vtab_err-2.379 vtab_err-2.380 vtab_err-2.381 vtab_err-2.382  
> > vtab_err-2.383 vtab_err-2.384 vtab_err-2.385 vtab_err-2.386  
> > vtab_err-2.387 vtab_err-2.388 vtab_err-2.389 vtab_err-2.390  
> > vtab_err-2.391 vtab_err-2.392 vtab_err-2.393 vtab_err-2.394  
> > vtab_err-2.395 vtab_err-2.396 vtab_err-2.397 vtab_err-2.398  
> > vtab_err-2.399 vtab_err-2.400 vtab_err-2.401 vtab_err-2.402  
> > vtab_err-2.403 vtab_err-2.404 vtab_err-2.405 vtab_err-2.406  
> > vtab_err-2.407 vtab_err-2.408 vtab_err-2.409 vtab_err-2.410  
> > vtab_err-2.411 vtab_err-2.412 vtab_err-2.413 vtab_err-2.414  
> > vtab_err-2.415 vtab_err-2.416 vtab_err-2.417 vtab_err-2.418  
> > vtab_err-2.419 vtab_err-2.420 vtab_err-2.421 vtab_err-2.422  
> > vtab_err-2.423
> >
> >
> > Not sure whether I should be concerned - we're not using virtual  
> > tables...
> >
> > 
> >
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] another test suite failure under Windows

[Dan Kennedy]

> It seems like the use_up_files command must work sometimes, since 
> several iterations of the test worked (i.e. 6.1.1 and 6.1.2). I'm not 
> sure what to make of the sequence of test failures and successes before 
> it finally hangs.
> 
> Since the comment indicates that this test is designed for a unix OS, 
> shouldn't it be testing for that OS before running? I think it should be 
> skipped much like the tests that depend upon the SQLITE_MEMDEBUG being 
> defined.
> 
> Does anyone with better TCL knowledge have any recommendations for 
> either making these tests work under Windows, or detecting the OS so the 
> tests can be skipped when not running under that OS?

It shouldn't be run on windows. I'll fix it. The fix is to wrap the
unix specific tests in a block like:

  if {$tcl_platform(platform)=="unix"} {
...
  }

Dan.




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

Re: [sqlite] Conditional table select

[Dan Kennedy]

On Fri, 2007-05-04 at 18:22 -0400, Vitali Lovich wrote:
> Hi,
> 
> I was wondering what would be the optimal way to select 1 matching row 
> from multiple tables.  Here is the scenario.
> 
> Multiple tables contain a primary key KEY.  If Table1 contains a 
> matching KEY, then I want that row from Table1.  Only if it isn't in 
> Table1, then look in Table2 if it is there.  If not in Table2 go on to 
> Table3, etc etc.
> 
> Is there a way to do this using SQL, or should I just break this up into 
> multiple queries and have the logic in C - this is for an embedded 
> system, so I want to use the least amount of memory & CPU (memory is 
> more important though).

How about this:

  SELECT * FROM tbl1 WHERE key = $key 
  UNION ALL 
  SELECT * FROM tbl2 WHERE key = $key
  LIMIT 1;

Although technically, using LIMIT without ORDER BY is a bad thing.





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


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

Re: [sqlite] unicode like UPPER and LOWER ?

[Dan Kennedy]

On Tue, 2007-05-08 at 10:45 +0700, Kirill wrote:
> Good day,
> 
> SQLite version 3.3.17
> Enter ".help" for instructions
> sqlite> create table tbl1(t1 varchar(10));
> sqlite> insert into tbl1 values('софт'); - lowChar
> sqlite> insert into tbl1 values('СОФТ'); - upChar
> sqlite> select * from tbl1;
> софт
> СОФТ
> sqlite> select * from tbl1 where t1 like '%оф%';  - lowChar
> софт - lowChar
> 
> :(
> 
> what do?:
> 
> sqlite> select * from tbl1 where t1 like '%оф%';  - lowChar
> софт - lowChar
> СОФТ - upChar
> 

By default, SQLite only knows about the upper and lower case
equivalents for ASCII characters. You can override the built-in
LIKE operator with an external (possibly unicode aware) version
by overriding the like(X,Y) and like(X,Y,E) scalar functions
as described here:

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

There is some ***UNTESTED*** code for an SQLite extension to 
do this using the ICU library at:

  http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/icu.c

You could use this directly, or as an example to develop your own
LIKE function. If you do use it and find bugs, please report them 
here or in cvstrac. 

Dan.



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

Re: [sqlite] My HPUX Notes

[Dan Kennedy]

On Mon, 2007-05-07 at 12:28 -0700, km4hr wrote:
> Notes on how I got sqlite running on hpux 11.0
> 
> To install sqlite on hpux:
> * download sqlite-3.3.17.tar.gz from web site.
>   (I unzipped the file on linux using gunzip I think. Then copied
>the tar file to /opt on the unix box. I guess gunzip works on hpux also.)
> * cd to /opt and untar the file. (tar xf sqlite-3.3.17.tar)
> * cd to sqlite... dir created by tar.
> * create a "bld" directory as recommended in sqlite README.
> * cd to bld.
> * Use GNU bash version of "make" (came with hpux 11.0 on my computer
> /opt/OpenSource/...) to
>   perform the build instructions given in sqlite README. The hpux version of
>   make doesn't work. It chokes on the "+=" operators in the make file.
> * after "making" sqlite, look in the "bld/.libs" directory for the sqlite
>   library files. "sqlite3" command line program is there as well.
> * no "sqlite.h" file is provided by the install (?) but "sqlite.h.in"
> provided works.

Mostly works.

It's easiest if you run the "make install" target too. i.e.:

$ mkdir /home/km4hr/sqlite_install/
$ ../sqlite/configure --prefix=/home/km4hr/sqlite_install/

$ make install

Then you wind up with sub-directories "bin", "include" and 
"lib" under /home/kv4hr/sqlite_install/. Everything is more
or less where you expect it to be.

You'll still need to use the gnu make of course.

Dan.




> * here's how I compiled the test program given on the sqlite web site:
>   cd to my home directory.
>   Copied contents of C test program on web site into file "myprog.c".
>   Changed "#include " to "#include "/sqlite.h.in".
>   Then: "cc -o myprog myprog.c /opt/sqlite/bld/.libs/libsqlite3.a"
> * Used /opt/sqlite/bld/.libs/sqlite3 to create database "test.db" (create
> table...)
>   and to add some records in a table (insert into table ...).
> * run myprog test program to dump the table.
>   Ex: myprog test.db "select * from tablename"
> 
>   Works! Success!
> 
> * Now I need to figure out where to install the sqlite library and header
> file permanently on hpux.
> 
> 


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

Re: [sqlite] ATTACH and sqlite3_open()

[Dan Kennedy]

On Wed, 2007-05-09 at 16:18 -0700, Jon Scully wrote:
> Simpler than that.  I merely want to attach to two databases (files).
> flash.db contains a set of tables that hold non-volatile data; ram.db
> contains a set of tables that is re-built (volatile) on re-boot -- but
> offers fast, read-only access.  No table-name overlaps, of course.
> 
> I want to access both sets of tables, seamlessly, as I should be able
> to do using ATTACH, at the command prompt, but do so using the C API.
> 
> Just wondering how others do this (Using ATTACH?  Using
> sqlite3_open()? Obviously I haven't looked very far into the
> sqlite3_open() code to see how it's put together, etc.).

Execute an ATTACH statement via sqlite3_exec(), or 
sqlite3_prepare/step/finalize.

> sqlite3 *db;
>
> if (sqlite3_open("flash.db", &db)) {
> fprintf(stderr, "Can't open the database in the Flash file
> system\n");
> exit(2);
> } else if (sqlite3_open("ram.db", &db)) {
> fprintf(stderr, "Can't open the database in the RAM-disk file
> system\n");
> sqlite3_close(db);
> exit(2);
> }

Don't do this. The second call to sqlite3_open opens a
new database connection to the file "ram.db" and overwrites
variable db with the new handle.

Dan.



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

Re: [sqlite] Column order in resultset

[Dan Kennedy]

On Sat, 2007-05-12 at 08:02 -0700, Mike Johnston wrote:
> When doing a "select * from foo" and using sqlite3_step() with 
> the associated sqlite3_column_xx funcs, is the order of the 
> columns in the resultset deterministic?
> 
> What is the rule that defines what the order?  
> it seems to be the order of the create table statement...

Right on both counts. It is deterministic, and they are in 
the same order as in the CREATE TABLE statement. (Zero is the
leftmost column in the CREATE TABLE).

A lot of people think it's bad practice to write "SELECT *"
in application programs. If a column is added to the table
later on, the extra column might surprise the application
code. On the other hand, it could be just what you want.

Dan.

>   
> but I can't seem to find where it is written a "select *"must follow that 
> order.
> 
> TIA
> 
>  
> -
> No need to miss a message. Get email on-the-go 
> with Yahoo! Mail for Mobile. Get started.


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

Re: [sqlite] SQLITE_ERROR in sqlite3_prepare

[Dan Kennedy]

On Tue, 2007-05-15 at 13:47 +0530, B V, Phanisekhar wrote:
> Hello all,
> 
> When I try to prepare the stmt for the query "PRAGMA
> cache_size = ?"  I am getting an SQLITE_ERROR.

You can only use '?' in place of an SQL expression. PRAGMA
commands take a literal string, identifier or number as
an argument, not an SQL expression.

Dan.


> Whereas it doesn't give error for queries like "select xxx from table
> where rowed = ?" where xxx is some combination of columns.
> 
>  
> 
> Regards,
> 
> Phanisekhar
> 


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

Re: [sqlite] Re: sqlite3_column_xxx question

[Dan Kennedy]

On Tue, 2007-05-15 at 17:24 -0700, Mike Johnston wrote:
> Absolutely but it seems like duplicate effort if sqlite already has the info. 
> Any guess if this is a big deal to put into the source?

Not a big deal at all. But there's no real advantage 
to putting this in the core. And it would add a few bytes 
to the footprint. A wrapper can do just as good a job.

In case you were wondering: As long as you never call
sqlite3_column_name16(), the pointers returned by 
sqlite3_column_name() are safe to use for the lifetime
of the statement.

Dan.




> Igor Tandetnik <[EMAIL PROTECTED]> wrote: Mike Johnston 
> wrote:
> > While using sqlite3_step(), is there a clean easy way to retrieve the
> > data by column name instead of by ordinal?  I know I can convert an
> > ordinal to a column name but not the other way around.
> 
> You can enumerate all columns, retrieve the name of each, and find the 
> one you want. You only need to do this once after the statement is 
> prepared: after that, you can use the ordinal you've found.
> 
> Igor Tandetnik 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
>
> -
> Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, 
> when. 


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

RE: [sqlite] Re: Re: Order of result of a query?

[Dan Kennedy]

On Wed, 2007-05-16 at 11:39 +0530, B V, Phanisekhar wrote:
> Igor,
> 
> Assume I have a database of the files/folders.
> 
> Let it be
> 
> Rowid puid
> 1 1
> 2 2
> 3 3
> 4 5
> 5 7
> 6 8
> 7 10
> 
> Assume I have a relation table showing which file is in which folder
> 
> AbsPuid Puid
> 710
> 72
> 78
> 75
> 
> The above relation table tells that the object 10, 2, 8, 5 is inside a
> folder object 7.
> 
> Now assume the entries are added to this relation ship table in the
> sequence in which the files are created inside the folder along with the
> sequence by which they were moved inside the folder. So the above table
> says file 10 was first added to the sequence then 2, then 8, and at last
> 5. Now assume I want to retrieve the rowid information in the order in
> which the objects have been added to the folder. I am using the
> following SQL query:
> 
> Select rowid from maintable where puid in (select puid from
> relationtable where AbsPuid =7)
> 
> The subquery will return the values {10, 2, 8, 5} but the result of the
> main query is {2, 4, 6, 7} rather than {7, 2, 6, 4}. So what should be
> the query so that my end result is {7, 2, 6, 4}

  SELECT maintable.rowid 
  FROM maintable, relationtable
  WHERE AbsPuid=7 AND maintable.puid=relationtable.puid
  ORDER BY relationtable.rowid

Or something like that anyhow.

Dan.



> I hope this will clear the question. 
> 
> Another doubt which I have is will the SQLite search the entire table
> with the first entry in the result set then followed by second entry in
> the result set, and so on or will it first check the first row against
> all the values in the result test, then second row with all the values
> in the result set and so on. If the first case the result will be {7, 2,
> 6, 4} and in the latter the result will be {2, 4, 6, 7}. Hence I feel
> that sqlite follows the second case. So is that true?
> 
> 
> Chris,
>  I don't have any specific precise display sequence to mention it using
> another column.
> 
> 
> Regards,
> Phanisekhar
> 
> 
> 
> 
> -Original Message-
> From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 15, 2007 8:13 PM
> To: SQLite
> Subject: [sqlite] Re: Re: Order of result of a query?
> 
> B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> > Assume the values in the OR clause, be replaced by some subquery. Then
> > in such scenarios how will I be able to maintain the order?
> > I want the order of the subquery to be preserved in the main query.
> 
> Show an example. I'd like to look at the ORDER BY clause of that 
> subquery (it does have one, right)? Then I'll essentially move the ORDER
> 
> BY out of the subquery and into the main query.
> 
> > For e.g.: for the sub query returned values in order (2, 8, 7, 3)
> 
> How does the subquery impose this particular order?
> 
> Igor Tandetnik 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Re: Problem with Unicode surrogates

[Dan Kennedy]

On Thu, 2007-05-17 at 16:54 +0200, Jiri Hajek wrote:
> > The Unicode standard is beside the point. There is lots of code
> > that does not handle charsets and encodings correctly, which can
> > open vulnerabilities to metacharacter injection. (Examples of
> > this class of problem are SQL injection, XSS and format string
> > exploits.)
> 
> I can't agree. SQLite itself wouldn't be vurnelable at all by
> accepting any UTF-16 string (including invalid ones). Certainly, it
> could cause problems to some applications using SQLite, but SQLite
> can't be responsible for poorly written applications using it, can it?
> 
> Anyway, it certainly can't be called a bug if SQLite returns error
> when I try to prepare an SQL statement with invalid characters.
> However, it should be clear what SQLite considers as an invalid
> character, is it only an unpaired surrogate, anything that Unicode
> standard defines as a 'noncharacter' or even any character that
> currently isn't defined by Unicode standard (which would be pretty bad
> in my opinion)?
> 
> Re. that 0xE000 character, should I submit a bugreport somewhere?

You already did. Thanks. 

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

In general, formal bug reports can be submitted by clicking
[Ticket] on this page:

http://www.sqlite.org/cvstrac/

Dan.

> Thanks,
> Jiri
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] One more SQLite threading question

[Dan Kennedy]

On Thu, 2007-05-17 at 18:26 -0400, Martin Gentry wrote:
> Can you be a bit more specific? :-)  I ask because this is immediately 
> relevant to some code I'm writing today, and have been operating on the 
> understanding that I should honour the restriction.  I'm fine with honouring 
> the restriction if required, but it might make my life easier if I don't 
> have to.

The current official position, as I understand it, is that you can
pass handles between threads. There are no known reasons that this
will not work. But it's been a source of bugs in the past, and I 
personally wouldn't risk it if I had the choice. Especially if
I thought the code could be deployed with a variety of different
OS's or kernel versions. But that's just an opinion.

Tricky things, threads.

Dan.

> - Original Message - 
> From: <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, May 17, 2007 5:01 PM
> Subject: Re: [sqlite] One more SQLite threading question
> 
> 
> "Martin Gentry" <[EMAIL PROTECTED]> wrote:
> > Just as an FYI on the threading ...
> > http://www.sqlite.org/capi3ref.html#sqlite3_open
> >
> > "The returned sqlite3* can only be used in the same thread in which it was
> > created. It is an error to call sqlite3_open() in one thread then pass the
> > resulting database handle off to another thread to use. This restriction 
> > is
> > due to goofy design decisions (bugs?) in the way some threading
> > implementations interact with file locks."
> >
> 
> That restriction is due to bugs in GLIBC or maybe the Linux Kernel
> (I'm not sure which) which have been resolved.  And for that matter,
> more recent versions of SQLite work around the bugs even if they
> are there.  So you can mostly ignore this now.  Mostly.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

RE: [sqlite] One more SQLite threading question

[Dan Kennedy]

> Which in that case whats the point of a shared cache? 
> What is it shared against, since all threads must send 
> data to the shared server anyways and none may access
> it concurrently.

The idea is to have a single cache shared accessed by
more than one logical connection (read: more than one
transaction context). It's meant to reduce IO and memory
usage in the case that a process opens more than one 
connection to the same database file.

It's quite a specialised feature. Only really useful
if you are implementing an embedded server.

Dan



>  One thing that Other database engines do is allow read and writes to occur 
> without blocking. That is a Reader never blocks a writer and a Writer never 
> blocks a reader. SQLITE does not do this, Only a single writer or Multiple 
> readers, but not both concurrently.
>  
>  I'm not trying to pick on sqlite, just pointing out that it really doesn't 
> perform multi threading or even conncurrent access very well in a read/write 
> environment. Read Only, its great. Single threaded Read/Write ... Very good 
> as well.
>  
>  Regards,
>  Ken
>  
> 
> 
> 
> 
> Doug Nebeker <[EMAIL PROTECTED]> wrote: > > Yes I did the same experiment 
> with a lock that made thread A wait  
> > > until B was finished. So actually only one thread can be active at
> the time.
> > > I don't see how the outcome of this experiment can be of  any 
> > > interest, as there is no time reduction any longer. But your  guess
> is 
> > > right that, it works.
> > 
> >How would multiple threads be faster than a single one when you are
> accessing a single resource?
> 
> Assumably the thread that is accessing the database either spends some
> time gathering data to write
> or processing data it read.  The single resource isn't in use during
> that time.
> 
> This email was sent to you by Reuters, the global news and information 
> company. 
> To find out more about Reuters visit www.about.reuters.com
> 
> Any views expressed in this message are those of the individual sender, 
> except where the sender specifically states them to be the views of Reuters 
> Limited.
> 
> Reuters Limited is part of the Reuters Group of companies, of which Reuters 
> Group PLC is the ultimate parent company.
> Reuters Group PLC - Registered office address: The Reuters Building, South 
> Colonnade, Canary Wharf, London E14 5EP, United Kingdom
> Registered No: 3296375
> Registered in England and Wales
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 


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

RE: [sqlite] One more SQLite threading question

[Dan Kennedy]

On Fri, 2007-05-18 at 11:46 -0700, Ken wrote:
> Dan,
> Can you explain to me how within the context of the test_server.c code
> that the 
> sqlite3_enable_shared_Cache will improve concurrency, for a single DB
> file access?

Others have pointed out in the past that in many cases
using a shared-cache actually decreases concurrency. A
smart embedded server (smarter than test_server.c) might
be able to manage query execution to work around this.

If the process does not require multiple logical connections,
or saving memory and extra file-system reads is not
an issue in your environment, shared-cache mode will likely
do nothing for you.

Dan.


> I just don't see where any concurrency is gained. Sure maybe some
> memory savings. But I must be brain dead, because I don't see how it
> could improve concurrency given that a single thread is used to
> perform db access. And all clients are queued and blocked upon the
> single threads message queue.
> 
> Thanks,
> Ken
> 
> 
> 
> 
> Dan Kennedy <[EMAIL PROTECTED]> wrote:
> > Which in that case whats the point of a shared cache? 
> > What is it shared against, since all threads must send 
> > data to the shared server anyways and none may access
> > it concurrently.
> 
> The idea is to have a single cache shared accessed by
> more than one logical connection (read: more than one
> transaction context). It's meant to reduce IO and memory
> usage in the case that a process opens more than one 
> connection to the same database file.
> 
> It's quite a specialised feature. Only really useful
> if you are implementing an embedded server.
> 
> Dan
> 
> 
> 
> > One thing that Other database engines do is allow read and
> writes to occur without blocking. That is a Reader never
> blocks a writer and a Writer never blocks a reader. SQLITE
> does not do this, Only a single writer or Multiple readers,
> but not both concurrently.
> > 
> > I'm not trying to pick on sqlite, just pointing out that it
> really doesn't perform multi threading or even conncurrent
> access very well in a read/write environment. Read Only, its
> great. Single threaded Read/Write ... Very good as well.
> > 
> > Regards,
> > Ken
> > 
> > 
> > 
> > 
> > 
> > Doug Nebeker  wrote: > > Yes I did the same experiment with
> a lock that made thread A wait 
> > > > until B was finished. So actually only one thread can be
> active at
> > the time.
> > > > I don't see how the outcome of this experiment can be of
> any 
> > > > interest, as there is no time reduction any longer. But
> your guess
> > is 
> > > > right that, it works.
> > > 
> > >How would multiple threads be faster than a single one when
> you are
> > accessing a single resource?
> > 
> > Assumably the thread that is accessing the database either
> spends some
> > time gathering data to write
> > or processing data it read. The single resource isn't in use
> during
> > that time.
> > 
> > This email was sent to you by Reuters, the global news and
> information company. 
> > To find out more about Reuters visit www.about.reuters.com
> > 
> > Any views expressed in this message are those of the
> individual sender, 
> > except where the sender specifically states them to be the
> views of Reuters Limited.
> > 
> > Reuters Limited is part of the Reuters Group of companies,
> of which Reuters Group PLC is the ultimate parent company.
> > Reuters Group PLC - Registered office address: The Reuters
> Building, South Colonnade, Canary Wharf, London E14 5EP,
> United Kingdom
> > Registered No: 3296375
> > Registered in England and Wales
> > 
> > 
> > 
> >
> 
> -
> > To unsubscribe, send email to
> [EMAIL PROTECTED]
> >
> 
> -
> > 
> > 
> 
> 
> 
> -
> To unsubscribe, send email to
> [EMAIL PROTECTED]
> 
> -
> 
> 


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

Re: [sqlite] --prefix

[Dan Kennedy]

On Tue, 2007-05-29 at 13:11 -0700, Smith1, Robert E wrote:
> Hi,
> 
>  I am trying to install sqlite3 on Sun Solaris 2.8. I am not root so I
> cannot install to /usr/local.  I start configure with
> --prefix=/ptmp/usr/localto try to get it to install to a different
> directory. But I get the same
> 
> error:
> 
> /ptmp/bld/> make install
> 
> tclsh ../sqlite-3.3.17/tclinstaller.tcl 3.3
> 
> can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only file
> system

Tcl extensions have to be installed in the Tcl library directory,
in your case, under /usr/local/lib/. So the --prefix option doesn't
apply to the Tcl interface, only to the shell, library and include
files (the stuff you need for C programming).

If you don't care about Tcl, add "--disable-tcl" to the configure
line. If you do need it, you'll have to compile Tcl and install
it somewhere you have write permission. Then use environment
variables (PATH etc.) to make sure configure picks up the right
Tcl installation.

Dan.
 


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

Re: [sqlite] Sqlite 3 Crash on OSX

[Dan Kennedy]

On Fri, 2007-06-01 at 10:51 +0100, Mark Gilbert wrote:
> Folks.
> 
> My app just crashed in the field randomly after some time running fine.
> 
> Thread 12 Crashed:
> 0   libsqlite3.0.dylib0x9406e587 sqlite3pager_get + 390
> 1   libsqlite3.0.dylib0x94054275 sqlite3BtreeCopyFile + 381
> 2   libsqlite3.0.dylib0x940542dd sqlite3BtreeCopyFile + 485
> 3   libsqlite3.0.dylib0x940545b6 sqlite3BtreeLast + 134
> 4   libsqlite3.0.dylib0x940830c1 sqlite3VdbeExec + 16021
> 5   libsqlite3.0.dylib0x94084c73 sqlite3_step + 270
> 6   libsqlite3.0.dylib0x9408b343 sqlite3_exec + 260
> 7   libsqlite3.0.dylib0x9407a53d sqlite3_get_table + 189
> 
> Anyone have anything specific to suggest ?

Only that that stack trace looks corrupted to me.
sqliteBtreeLast() does not call sqlite3BtreeCopyFile(), 
indirectly or otherwise.

Dan.



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

Re: [sqlite] Stack usage

[Dan Kennedy]

On Tue, 2007-06-05 at 13:35 +0200, [EMAIL PROTECTED] wrote:
> I'v read in change log that some stack allocted memory were moved to the 
> heap, but I think that there is still to much allocated memory on the stack.
> After creating a table with 2000 columns, jdbc driver created a query that 
> run out of stack. Default java's stack limit is low, but it wasn't hard to 
> create simillar query that crashed C application with default stack limit. 
> And the fact that it crashed instead repoting an error isn't really nice.
> The query created by the driver looks like that:
> 
> select null as TABLE_CAT, null as TABLE_SCHEM, 'test' as TABLE_NAME, cn as 
> COLUMN_NAME, dt as DATA_TYPE, tn as TYPE_NAME, 20 as COLUMN_SIZE, 
> 20 as BUFFER_LENGTH, 10   as DECIMAL_DIGITS, 10   as NUM_PREC_RADIX, 
> colnullable as NULLABLE, null as REMARKS, null as COLUMN_DEF, 0as 
> SQL_DATA_TYPE, 0as SQL_DATETIME_SUB, 20 as CHAR_OCTET_LENGTH, 
> ordpos as ORDINAL_POSITION, (case colnullable when 0 then 'N' when 1 then 'Y' 
> else '' end)as IS_NULLABLE, null as SCOPE_CATLOG, null as SCOPE_SCHEMA, 
> null as SCOPE_TABLE, null as SOURCE_DATA_TYPE from (select 0 as ordpos, 1 as 
> colnullable, 'col1' as cn, 'double' as tn, 8 as dt union all select 1 as 
> ordpos, 1 as colnullable, 'col2' as cn, 'double' as tn, 8 as dt union all 
> select 2 as ordpos, 1 as colnullable, 'col3' as cn, 'double' as tn, 8 as dt 
> union all select 3 as ordpos, 1 as colnullable, 'col4' as cn, 'double' as tn, 
> 8 as dt union all select 4 as ordpos, 1 as colnullable, 'lastcol' as cn, 
> 'double' as tn, 8 as dt);
> 
> but uses more columns.

There have been some recent changes to try to address this
by placing various limits on number of columns, length of
SQL expressions, length of SQL statements etc. See:

  http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/limits.h&v=1.6

So you could try with cvs head and the problem might have
already been fixed.

But so that we can check, can you post the database schema 
and the actual SQL statement that caused the crash in the 
sqlite shell? Or mail it to me off-list if it's too large or
something.

Thanks,
Dan.



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

Re: [sqlite] Stack usage

[Dan Kennedy]

On Wed, 2007-06-06 at 13:38 +0200, [EMAIL PROTECTED] wrote:
> > There have been some recent changes to try to address this
> > by placing various limits on number of columns, length of
> > SQL expressions, length of SQL statements etc. See:
> > 
> >   http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/limits.h&v=1.6
> 
> Nice. Limit is much better than a crash. But I hope that I will always remain 
> configurable and will be possible to specify really large values.
> 
> > But so that we can check, can you post the database schema 
> > and the actual SQL statement that caused the crash in the 
> > sqlite shell? Or mail it to me off-list if it's too large or
> > something.
> 
> Below is a simple C programme that causes a crash with default stack size 
> (tested with MSVC i cygwin's gcc):
> 
> #include 
> #include 
> #include 
> #include "sqlite3.h"
> 
> int main(int argc, char **argv){
>   sqlite3 *db;
>   char *str = (char *) malloc(100);
>   int rc, i;
> 
>   strcpy(str, "select * from (select 1");
>   for (i = 0; i < 5000; ++i) {
> strcat(str, " union select 1");
>   }
>   strcat(str, ")");
> 
>   rc = sqlite3_open("test.db3", &db);
>   printf("rc: %d\n", rc);
>   rc = sqlite3_exec(db, str, NULL, 0, NULL);
>   printf("rc: %d\n", rc);
>   rc = sqlite3_close(db);
>   printf("rc: %d\n", rc);
>   
>   return 0;
> }
> 
> If you still want the big query let me know, but it basically does the same - 
> it doesn't even need any table.

Change the 5000 to 5 and it crashes here on linux too. Maybe
we need to limit the number of SELECT statements that can be
combined in a compound. Another case (haven't tested it though):

  SELECT * FROM (SELECT * FROM (SELECT * FROM ...)))

probably overflows the stack too.

> I was thinking about sqlite based solution (other solutions are: don't make 
> such query or increase stack limit). I guess the best way is:
> -include multiSelect in sqlite3Select - that's the easy part
> -instead of recursive calls to sqlite3Select use gotos with self written 
> stack created on the heap - unfortunatelly it requires to keep track of all 
> created variables. They are mostly in front of the function but still it's 
> annoying to have to remember all of them.
> 
> Using MSVC it's easy prevent a crash: __try, __except and _resetstkoflw.
> I have no idea yet hot to do this with gcc. Signal can be caught, but I 
> wasn't able to do anything more with demaged stack.

Even if it worked it would leak resources and maybe leave SQLite's 
internals in an inconsistent state.

Dan.


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

Re: [sqlite] Truncate Issue

[Dan Kennedy]

On Fri, 2007-06-08 at 09:50 +0530, Jimmy Mathew Ambalathuruthel wrote:
> Hi all,
> 
>   I am working in a porting project of SQLite from windows.
> 
>   I Could not port the Truncate () function (For Eg: WinTruncate () in
> windows code in the file os_win.c ) as the same logic cannot be applied
> to my platform.
> 
>   Can I make it a dummy function by just returning SQLITE_OK?
> 
>   Please help.

I think it's important to implement. SQLite sometimes needs 
to truncate files during VACUUM, when committing transactions 
in either auto-vacuum or exclusive access mode, and during 
rollback or statement rollback. There might be more uses in
the future (based on the idea that truncating a file is 
often cheaper than deleting and recreating it). 

Why can't you truncate files? What platform are you working on?

Dan.



>  
> 
> Regards
> 
> Jimmy Mathew
> 
>  
> 


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

RE: [sqlite] Amalgamation questions

[Dan Kennedy]

On Sun, 2007-06-10 at 16:08 -0700, Brett Keating wrote:
> Well I basically did the following, but not sure it's optimal:
> 
> 1) Took 3.3.17 amalgamation
> 2) Took shell.c from 3.3.17 full distribution, and made a target that
> just uses sqlite3.c and shell.c to get the command line tool
> 3) Took whatever I had in my old makefile for compile flags and added
> them for compiling sqlite3.c in my project
> 
> An extra step was needed:
> 1) Apparently 3.3.17 needs libdl, where 3.3.8 seemingly didn't, so I had
> to make some adjustments.
> 
> Not that it's all that hard to figure the above out, but just wanted
> some perspective on the "recommended" approach, since I couldn't find
> any docs or list emails regarding building the shell using the
> amalgamation, and things might change so that what I'm doing now may no
> longer "work."

That seems fine to me. libdl is required by loadable extensions. If
you define the pre-processor symbol SQLITE_OMIT_LOAD_EXTENSION, 
it's not required.

BTW you're right - there is no 3.3.18 yet.

Dan.



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

Re: [sqlite] Re: sqlite_omit_xx build failure

[Dan Kennedy]

On Tue, 2007-06-12 at 09:56 +0200, weiyang wang wrote:
> Hi Dr.H,
> Yes, I did a clean build in a clean directory( (top)/bld ). and i had run
> 'make clean' before i run the 'make'.
> 
> i am wondering whether the lemon tool configuration in my environment is
> well done or not.
> 
> thanks in advance.
> 
> wang
> 
> On 6/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > "weiyang wang" <[EMAIL PROTECTED]> wrote:
> > > hi,
> > >
> > > i am trying to get a smaller sqlite lib by adding sqlite_omit_xx
> > defines.
> > > but it failed at the compiler time.
> > >
> > > steps:
> > > 1, in 'Makefile.in ', omit macro is added:
> > >
> > >   TCC += -DSQLITE_OMIT_ALTERTABLE=1

Do this as well:

   OPTS += -DSQLITE_OMIT_ALTERTABLE=1

The key is that the same set of -DSQLITE_OMIT_ options have
to be passed to "lemon" as the C compiler.

Dan.

> > >
> > > 2, run '(top)/configure' and 'make' in cygwin,
> > >
> > > 3, i got the fowllowing error:
> > >
> >
> > Did you do this from a clean directory?  Did you
> > do "make clean" before doing "make"?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> > -
> >
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >


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

Re: [sqlite] PRAGMA cache_size = 0

[Dan Kennedy]

> What exactly happens when I change the cache_size (both increase and
> decrease size)?

A variable is set.

> What happens to the data that's there in the result cache at the time
> when the instruction PRAGMA cache_size = 0 is executed? 

Nothing. The aforementioned variable is set to 10 instead of the
specified value.

> Will there be any memory that will be freed up when I reduce the size of
> result cache?

No.



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

Re: [sqlite] SQLite 3.X Database File Format ?

[Dan Kennedy]

On Thu, 2007-06-14 at 15:08 -0700, Joe Wilson wrote:
> Is there an SQLite 3.x equivalent document for this?
> 
>   SQLite 2.X Database File Format
>   http://sqlite.org/fileformat.html
> 
> If not, is this 2.x document worth reading as a background to
> the general structure of the sqlite 3.x file and page format?
> Or has it changed so much that it's not useful?

I just took a quick look, and it all still looks pretty 
relevant to me. 

The exact way the bytes are arranged to form row-records
has changed (to accommodate manifest types), the database 
header is different and some of the "b trees" are now "b+ trees".
And the root-page of the sqlite_master table is now on page 1
(with the header) instead of 2. But these are all really 
just details, the basic approach is the same.  

If you need the precise details, it might be easiest to read
that document and then proceed to the comments in btreeInt.h.

Dan.

> 
> 
>
> 
> Be a better Globetrotter. Get better travel answers from someone who knows. 
> Yahoo! Answers - Check it out.
> http://answers.yahoo.com/dir/?link=list&sid=396545469
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Journal File Optimization

[Dan Kennedy]

On Mon, 2007-06-18 at 06:04 -0500, John Stanton wrote:
> Andre du Plessis wrote:
> > How can one optimize the creation of the journal file. The problem is
> > this, for our system which is an event based one each message needs to
> > be insterted and committed to the database (guaranteed), this results in
> > a commit per insert, this was obviously unacceptably slow and according
> > to the lists by design you need to do more bulk transactions, and with
> > some efford I modified it with a combination of bulk and temporary
> > tables to do quite a few at a time, but the problem remains that
> > committing is terribly slow. 
> > 
> >  
> > 
> > I'm thinking the journal file, the fact that it is created written and
> > deleted each time which is slowing it down, Is there not a way to create
> > and set up a permanent journal file pre-allocated to a certain size, so
> > sqlite does not have to go through the OS each time to create and delete
> > this file?
> > 
> >  
> > 
> > Along the same lines of this question, is there a way to fix the initial
> > size of the DB, ie set it to pre-allocate 700mb for instance so that no
> > growing of the db file is needed until the space is exceeded, may also
> > speed things up.
> > 
> >  
> > 
> >  
> > 
> > Thank you very much in advance.
> > 
> I suspest the you will find that the sync process is the logjam.
> 

This is almost certainly right.

But for trivias sake: In exclusive mode, sqlite truncates the journal
file at the end of each transaction instead of deleting it. Because
file creation/deletion is slow on some systems.

You can turn on exclusive mode using:

   PRAGMA locking_mode = "exclusive;


Dan. 



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

Re: [sqlite] Proper way to transfer a live sqlite database

[Dan Kennedy]

On Tue, 2007-06-19 at 00:46 +0100, Michael Hooker wrote:
> Christian wrote:
> 
> >>Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the
> database file, then executing a 'ROLLBACK' to end the transaction.<<
> 
> >>and can be safely copied at the OS level<<
> 
> I also have a need to copy a live database which is constantly being updated 
> 24/7 by software which decodes radio signals.  I need to safely interrogate 
> and process a static version of the data without risk of messing up the 
> original. Once midnight has passed, the copy can be taken and the previous 
> day's data extracted from the copy.But as a raw beginner I don't clearly 
> understand what is being said here.
> 
> When you say >>and can be safely copied at the OS level<<, I guess you must 
> mean something more than right-click the file icon and select "Copy" ;)   In 
> any case I would much prefer the copy to be taken programmatically, which 
> would be neater and faster, and could be done automatically in the middle of 
> the night when the data flow is much less intense.   I use, as best I can, 
> Delphi 7 and Ralf Junker's DisqLite3, so can I safely:-
> 
> (1) send a BEGIN IMMEDIATE command,
> 
> (2) issue Delphi Windows API command "CopyFile(PChar(SourceFileName), 
> PChar(DestFileName), FALSE);
> 
> (3) send a ROLLBACK command.
> 
> The destination would be the same folder as the source, so no network delay. 
> The file is about 55 Megabytes.
> 
> My data is of no commercial value, but I have a few hundred people looking 
> forward to my reports every day and don't want to mess it up;  I have no 
> other SQLite3 databases to experiment with, so please forgive me for asking 
> you experts what is probably a very basic question.  It would also be very 
> helpful if someone could explain in jargon-free terms what ROLLBACK means in 
> this context and why it apparently serves the purpose of finishing the 
> transaction which has not attempted to change anything(why not END?)

"ROLLBACK" means abandon the current transaction, and put the database
back the way it was before the transaction started. To "roll back" all
changes so far.

In this specific context, the important part is that the "BEGIN 
IMMEDIATE" locks the database file and the "ROLLBACK" releases the
lock. A "COMMIT" or "END" would be logically identical - it releases
the lock, and since there were no database changes made in this
transaction, it doesn't matter if they are rolled back or not.

Under the hood, there is a minor difference - a COMMIT will update
the database change-counter, meaning that all other connections
will need to discard their caches. A ROLLBACK does not update the
change-counter, so caches held by other connections will remain
valid.

Dan.


>  - I 
> keep coming across the word and I'm sure it means something fairly simple, 
> but I have not encountered it until I started looking at SQLite.  I've let 
> Delphi and VisualDB handle all my database work through the BDE until now 
> and never had any need to worry about locking or contentions.
> 
> Thanks
> 
> Michael Hooker
> 
> - Original Message - 
> From: "Christian Smith" <[EMAIL PROTECTED]>
> To: 
> Sent: Monday, June 18, 2007 6:39 PM
> Subject: Re: [sqlite] Proper way to transfer a live sqlite database
> 
> 
> > Rich Rattanni uttered:
> >
> >> The databases will be in flux, and I didnt necessairly want to suspend
> >> the application that is performs reads and writes into the database.
> >> A simple copy worries me because it seems like messing with SQLITE on
> >> the file level is dangerous since you circumvent all the protection
> >> mechanisms that provide fault tolerance.  I didnt want to have to
> >> worry about if the database has a journal file that needs copied, or
> >> any other situation like that.  I figured using the SQLITE API to do
> >> the copy would award me some protection against corruption.
> >
> >
> > You're right to be cautious. Never copy an in use database if that 
> > database could possibly be updated.
> >
> > If you open the database, and obtain a SQLite read lock on it, you can be 
> > sure it is not going to be modified, and can be safely copied at the OS 
> > level.
> >
> > Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the 
> > database file, then executing a 'ROLLBACK' to end the transaction.
> >
> > To limit the time the database is locked, I suggest copying the file to a 
> > local filesystem first, then transferring across the network after the 
> > lock is released.
> >
> > Christian
> >
> >
> >
> > --
> > /"\
> > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
> >  X   - AGAINST MS ATTACHMENTS
> > / \
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> > 
> 
> 
> ---

Re: [sqlite] Step Query

[Dan Kennedy]

On Tue, 2007-06-19 at 10:58 +0530, anand chugh wrote:
> Hi
> 
> I am having code like this:
> 
>rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
>if( rc!=SQLITE_OK ){
>  return rc;
>}
>sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
>sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);
> 
>while( sqlite3_step(pStmt)==SQLITE_ROW )
>  {
>  *pnBlob = sqlite3_column_bytes(pStmt, 0);
>  *pzBlob = (unsigned char *)malloc(*pnBlob);
>  memcpy(*pzBlob, sqlite3_column_blob(pStmt, 0), *pnBlob);
>}
> 
>   sqlite3_finalize(pStmt);
> 
> My question here is do I need to do sqlite3_finalize(pStmt); after
> every sqlite3_step() to free all memory allocated by
> sqlite3_step().

No. Exactly one sqlite3_finalize() for each sqlite3_prepare(). In
this respect the code above is fine.

It's not SQLite related, but if the SQL statement returns more 
than one row, the malloc() in the while loop will cause a memory 
leak.

Dan.

> Does calling finalize at end will free all memory
> allocated by all steps statements?
> 
>  Example shown http://www.sqlite.org/cvstrac/wiki?p=BlobExample does
> same , it calls finalize after  every step.
> 
> My Program shows some Memory Leaks(Virtual Bytes).
> 
> Please clarify.
> 
> Anand
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Cache invalidation after insert statements.

[Dan Kennedy]

On Tue, 2007-06-19 at 01:06 -0700, pompomJuice wrote:
> Hello there.
> 
> I need some insight into how SQLite's caching works. I have a database that
> is quite large (5Gb) sitting on a production server that's IO is severely
> taxed. This causes my SQLite db to perform very poorly. Most of the time my
> application just sits there and uses about 10% of a CPU where it would use a
> 100% on test systems with idle IO. Effectively what the application does is
> constantly doing lookups as fast as it can.
> 
> To counteract this I increased the page size to 8192 (Unix server with advfs
> having 8K block sizes) and increased the MAX_PAGES value in sqliteInt.h to
> 512000. This worked. My application starts at low memory usage and as it
> gradually gains more memory. As it gains more memory it uses more CPU and
> reaches a point where it finally uses 100% CPU and 5Gb of ram.
> 
> Every now and then the lookup table is udpated. As soon as the application
> does this however the performance goes back to a crawl and slowly builds up
> again as described in the previous paragraph. The memory usage stays at 5Gb.
> All that I can think of is that the update invalidates the cache. 

Probably right.

> The update
> is not very big, say 20 rows in a table that has about 45 million rows.
> 
> What exactly is happening here?

Are you using 3.3.17? And is it an external process (or at least a
different connection doing) doing the update?

If so, the update is modifying the pager change-counter, invalidating
the pager cache held by the lookup application. The lookup app has
to start loading pages from the disk again, instead of just reading
it's cache.

The only way around this performance hit is to do the UPDATE through
the lookup app, using the same database connection.

Dan.

> Regards.


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

Re: [sqlite] problems with .dump procedure

[Dan Kennedy]

On Tue, 2007-06-19 at 09:42 +0200, Roberto Davico wrote:
> Hi all,
> 
>I am Roberto and I find a problem using sqlite3 (version: SQLite 
> version 3.3.8).
> 
>I make a sample db to explain the situation:
> 
>1) Create the sample database using command line tool:
> 
> sqlite> .schema
> CREATE TABLE LOG (ID INTEGER PRIMARY KEY AUTOINCREMENT, MSG TEXT );
> CREATE TABLE USERS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, 
> SURNAME TEXT);
> CREATE VIEW USR AS SELECT NAME FROM USERS;
> CREATE TRIGGER USR_DELETE AFTER DELETE ON USERS BEGIN INSERT INTO LOG 
> (ID, MSG) VALUES( NULL, 'Cancellato utente'); END;
> 
> 
>2) Export with .dump command procedure
> 
> sqlite> .output dumpo.txt
> sqlite> .dump
> sqlite> .output stdout
> sqlite> .quit
> 
> 
>3) Analyze the dump file:
> 
> ~$ cat dumpo.txt
> BEGIN TRANSACTION;
> CREATE TABLE USERS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, 
> SURNAME TEXT);
> DELETE FROM sqlite_sequence;
> CREATE TABLE LOG (ID INTEGER PRIMARY KEY AUTOINCREMENT, MSG TEXT );
> COMMIT;
> 
> where are my view and trigger?
> 
> Is it a problem on my handwork or it isn't possible to dump views or 
> triggers? are there many settings to do before dump?

It's an old bug:

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

You'll have to upgrade the sqlite version I think.

Dan.


> thanks all for any advice...
> 
> ciao
> Roberto
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Cache invalidation after insert statements.

[Dan Kennedy]

> My question is then, if any one connection makes any change to the database
> ( not neccesarily to the huge lookup table ) will all the other connections
> invalidate their entire cache?

Yes. The entire cache, regardless of what table was modified etc.


Dan.



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

Re: [sqlite] Re: How to use pragmas from code?

[Dan Kennedy]

On Tue, 2007-06-19 at 15:39 -0400, Igor Tandetnik wrote:
> Shane Harrelson
> <[EMAIL PROTECTED]> wrote:
> > To use pragmas from code, do I simply prepare them as a regular SQL
> > statement and then execute them?
> 
> Yes.

Another thing to note: Some pragmas take effect during 
sqlite3_prepare(), not sqlite3_step() (examples: cache_size, 
temp_store). For this reason calling sqlite3_reset() and then
reusing a PRAGMA statement has the potential to produce 
confusing results (or at least SQLITE_SCHEMA errors).

Personally, I would use sqlite3_exec() to execute pragma statements
from C code.

Dan.

> > And when can they/should they be done?   As the first statement after
> > an open?
> 
> Some pragmas have to be set early, others may be changed at any time.
> 
> > Are the pragma values stored with the database?
> 
> Some pragmas affect the format of the database file - these are stored 
> in the database. Others only affect current connection - these are not 
> stored.
> 
> 
> Is there are particular pragma you are worrying about?
> 
> Igor Tandetnik 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Proper way to transfer a live sqlite database

[Dan Kennedy]

On Tue, 2007-06-19 at 11:51 -0700, Gerry Snyder wrote:
> Michael Hooker wrote:
> > Many thanks for the explanation Dan.  
> Ditto the thanks.
> > I suspected the purpose of ROLLBACK was as you say, but couldn't see 
> > why it was used here.  You point out the "under the hood" difference 
> > between ROLLBACK and COMMIT, but what about END? My main (third-party, 
> > commercial) application may well have some data stored waiting to be 
> > fed into the database after the file has been copied, and if it is 
> > forced to discard its cache that presumably means these are lost, 
> > which wouldn't be a good idea. 

END is the same as COMMIT in sqlite.

> It shouldn't have to. The cache Dan was referring to was an internal 
> copy of (part of) what is already in the data base. If the data base 
> file has been updated, that copy has to be discarded, since it may not 
> be valid--of course, it may be valid, but figuring out whether it is 
> would be a lot more work than just rereading it. Anyhow, this is all 
> happening at a much lower level than the application data you are 
> referring to, which is still valid and should be entered into the file.

Right. If another app has some "writes" (dirty pages) in it's cache,
then it will already have at least a RESERVED lock on the database
file. If this is the case the "BEGIN IMMEDIATE" statement executed
by the copy-file process will fail to obtain it's EXCLUSIVE database
lock.

So the only logic the file-copy process needs is "Do not do the file
copy until after the BEGIN IMMEDIATE succeeds".

Dan.


> HTH,
> 
> Gerry
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

RE: [sqlite] More SQLite Misuse, sorted i think

[Dan Kennedy]

On Wed, 2007-06-20 at 11:05 +0200, Andre du Plessis wrote:
> Sorry if I created any confusion there were some code that seemed to
> have called
> Sqlite_reset simultaneously from more than one thread, even though the
> statements were unique for each thread the call to the library was not
> locked. I know assumptions are bad but I thought that reset on a unique
> statement should not have to be locked and serialized, but now I think
> it might, so now every single call to the library gets locked in a
> critical section and it seems to work.
> 
> However finalize worked because it seems that finalize can be called
> without synchronizing.

I'm not sure I completely understand, but anyway... :)

sqlite3_finalize() sets the database handle error code (and does
some other stuff to the db handle internally too). So if you're 
calling sqlite3_finalize(), you have to make sure no other thread 
is messing with the database handle or any of it's statements.

Dan.



> -Original Message-
> From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
> Sent: 19 June 2007 07:21 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] More SQLite Misuse
> 
> DLL version
> 
>   Sqlite3.3.17
> 
>  
> 
> The os is windows
> 
>  
> 
> After the last query of sqlite3_step
> 
>  
> 
> I decided to so some more tests, with threads, if synchronized properly,
> it seems that you can use more than one thread without any problem as
> long as 
> 
> Sqlite3_finalize is called is this correct?
> 
>  
> 
> Please note that this is a very simple query being executed :  "select *
> from threads where id = 1"
> 
>  
> 
> Imagine in the following scenarios both threads are executing
> simultaneously and will lock on the global critical section (so they are
> synchronized)
> 
> Using the same DB handle.
> 
>  
> 
> Scenario 1
> 
>  
> 
> THREAD1   THREAD2
> 
>  
> 
> LockGlobalCriticalSection
> LockGlobalCriticalSection
> 
> Sqlite3_prepare
> Sqlite3_prepare
> 
> Sqlite3_step
> Sqlite3_step<   SQLITE_MISUSE: library routine
> called out of sequence here
> 
> Sqlite3_reset
> Sqlite3_reset 
> 
> UnLockGlobalCriticalSection
> UnLockGlobalCriticalSection
> 
>  
> 
> // The following code works fine though
> 
>  
> 
> THREAD1   THREAD2
> 
>  
> 
> LockGlobalCriticalSection
> LockGlobalCriticalSection
> 
> Sqlite3_prepare
> Sqlite3_prepare
> 
> Sqlite3_step
> Sqlite3_step
> 
> Sqlite3_finalize
> Sqlite3_finalize 
> 
> UnLockGlobalCriticalSection
> UnLockGlobalCriticalSection
> 
>  
> 
> 
> 
> If my tests are correct it is not possible to retain a prepared
> statement across threads. And has to be reprepared each time ??
> 
>  
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

RE: [sqlite] More SQLite Misuse, sorted i think

[Dan Kennedy]

> Hope that is more clear.

Perfectly. I get it now. As you say in the other post, every
sqlite call needs to be inside the critical section, including
sqlite3_finalize().

Dan.




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

Re: [sqlite] The problem with index

[Dan Kennedy]

> Another question: Is it correct that virtual tables can be created using 
> Perl but not Tcl? I don't have a current need (with the possible 
> exception of FTS1/2, which are already accessible from Tcl), but the 
> situation seemed curious. Wondering whether there was an undocumented 
> capability (or one I just missed in the docs).

You're correct.

Using the standard Tcl interface, there is no way to create
a new virtual table type. But you can instantiate and access
virtual tables for which the implementation is written in C
(i.e. fts1/2).

Dan.



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

Re: [sqlite] Trigger on Attached Database

[Dan Kennedy]

On Thu, 2007-06-21 at 11:57 +0200, Andre du Plessis wrote:
> Is it possible to do this:
> 
>  
> 
>Open DB1
> 
>Attatch DB2
> 
>  
> 
> 
> 
> In DB1 have a trigger that does
> 
>   Insert into DB2. ?
> 
>  
> 
> 
> 
> Theoretically it seems possible but we couldn't get it to work. Before I
> investigate further just want to know if it is possible

No. A trigger may only reference objects in it's own database.
You should be getting an error message along the lines of
"cannot reference object in database XXX" when you create
the trigger.

Dan.





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

Re: [sqlite] Unicode collation

[Dan Kennedy]

On Fri, 2007-06-22 at 18:57 +0200, Jiri Hajek wrote:
> Hello,
> 
> I wonder whether there are any plans to include internally proper
> Unicode comparisons? Don't get me wrong, I think that it's great that
> SQLite supports custom collations, there's absolutely no problem to
> handle it in internally for my database, but problem is that if I
> define UNICODE collation, no other application knows about it and so
> users can't open it in any SQLite DB editor.
> 
> Nowadays applications without Unicode support slowly become rare, as I
> see, I'm not the first one asking for this kind of support in SQLite.
> Is there any technical reason why not to include UNICODE and e.g.
> IUNICODE (for case-insensitive comparisons) collations in SQLite? Is
> it because of some systems that don't have (full) Unicode support? In
> such a case, I guess that it could be a compile-time option.

The reason is as you've surmised. Not all systems have full unicode
support (I'm not sure, but if I had to guess, I would say very few
systems do). Including an implementation with SQLite would bloat
the library to at least several times it's current size.

Another reason is that it's a pretty complex topic. Supporting
most European languages would probably be possible without too
much trouble, but once you get into Asian and Middle-eastern
languages I think it's much harder.

There is an extension packaged with SQLite sources that uses the
ICU library to provide locale dependent collation sequences and
case folding. See here for details:

  http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt

Dan.



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

Re: [sqlite] Unicode collation

[Dan Kennedy]

On Sat, 2007-06-23 at 10:56 +0200, Jiri Hajek wrote:
> > The reason is as you've surmised. Not all systems have full unicode
> > support (I'm not sure, but if I had to guess, I would say very few
> > systems do). Including an implementation with SQLite would bloat
> > the library to at least several times it's current size.
> 
> I know really well only Windows where it's only about using
> CompareString() function, i.e. almost no code in SQLite.
> 
> > Another reason is that it's a pretty complex topic. Supporting
> > most European languages would probably be possible without too
> > much trouble, but once you get into Asian and Middle-eastern
> > languages I think it's much harder.
> 
> As far as I know, there wouldn't be any complexity on SQLite's side -
> it's only about calling proper methods (be it CompareString() on
> Windows or ICU methods elsewhere), i.e. again pretty much no code
> needed in SQLite.
> 
> > There is an extension packaged with SQLite sources that uses the
> > ICU library to provide locale dependent collation sequences and
> > case folding. See here for details:
> 
> ICU is nice, but pretty large. Since I develop for Windows, I'd rather
> not distribute it with my application considering that this is alredy
> provided in Windows in reasonable quality.
> 
> Anyway, I guess that the question isn't mainly about how to implement
> this in SQLite, but about the problem that SQLite doesn't define any
> standard how to handle Unicode. Currently, any application that needs
> to work with Unicode data has to define its own collation and name it
> 'tr_TR', 'turkish', 'MyTurkish', or any other way. The result is a big
> mess and no chance of opening SQLite database in other application
> than it was designed for.
>
> So, why don't we (or you - SQLite developers) define how to name
> collations (e.g. that 'tr_TR', 'en_AU', ... standard?) and then every
> database complying this would be perfectly portable.

That's not too bad an idea. One thing to watch out for is that if
the definition of a collation sequence that you have used to create
an index varies even slightly from machine to machine (say from 
win98 to windows vista), you are headed for database corruption.

For that reason I'd be a bit reluctant to encourage people to use
more than one implementation of a named collation sequence. 

Dan.




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

Re: [sqlite] pragma page_count

[Dan Kennedy]

On Mon, 2007-06-25 at 17:08 -0500, Andrew Finkenstadt wrote:
> How easy would it be for me to implement a "pragma page_count;" statement
> which returns the CURRENT page count of the database, and is much more
> cross-platform than my attempt to just "check the file size".

Not difficult I would think. You will need to add a case
to the big "if ... else" statement in pragma.c. Search for
the string "page_size" in pragma.c, your new pragma 
implementation will be similar to this.

Use sqlite3BtreePager() to get the pager pointer from the
btree. Then sqlite3PagerPagecount() to get the actual number
of pages. returnSingleInt() to return the data.

Compile, test, debug

Dan.



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

Re: [sqlite] colname=22 vs colname='22'

[Dan Kennedy]

On Tue, 2007-06-26 at 17:50 -0400, jose isaias cabrera wrote:
> Greetings.
> 
> I have the following db declarations:
> 
> SQLite version 3.3.8
> Enter ".help" for instructions
> sqlite> .schema
> CREATE TABLE LSOpenJobs
> (
>  id integer primary key, ProjID integer, subProjID, parent, 
> children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, 
> pmuk, lang, vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, 
> TMDir, DeliveryDir, paid, paidDate, notes, status
> );
> CREATE TABLE LSOpenProjects
> (
>id integer primary key, ProjID integer, subProjID, parent, children, 
> login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, 
> vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir, 
> DeliveryDir, paid, paidDate, notes, status
> );
> CREATE TABLE LSOpenSubProjects
> (
>id integer primary key, ProjID integer, subProjID, parent, children, 
> login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, 
> vendor, vEmail, invoice, ProjFund, PMTime, A_No, wDir, BiliDir, TMDir, 
> DeliveryDir, paid, paidDate, notes, status
> );
> CREATE TABLE PMTime (id integer primary key, rec integer, date, secs 
> integer);
> CREATE TABLE PMUserData
> (
> login primary key,
> Name,
> Password,
> email,
> phone,
> homephone,
> Lang,
> ProjOwned
> );
> 
> If I do this call,
> 
> sqlite> select * from LSOpenJobs where SubProjID='22';
> 
> I get nothing.  If I do this call,
> sqlite> select * from LSOpenJobs where SubProjID=22;
> 106|22|22|22||...|c
> 107|22|22|22||...|c
> 108|22|22|22||...|c
> 109|22|22|22||...|c
> sqlite>
> 
> I get stuff.  Anybody would like to tell me why?  I have done some command 
> prompt manual record deletion and edition, but it should not matter, 
> correct?

It's about the differences between a string and a number, how
they compare and when a string is converted to a number. See
section 3 of this page:

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

Dan.

> thanks,
> 
> josé 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] SQL query assistance...

[Dan Kennedy]

On Sun, 2007-07-01 at 16:31 -0500, Jeff Godfrey wrote:
> Hi All,
> 
> Given the following sample data...
> 
> ID  Name  Version
> --- - ---
> 1   name1  0.9
> 2   name1  1.0
> 3   name2  1.2
> 4   name3  1.0
> 5   name3  1.7
> 6   name3  1.5
> 
> I need to create a query that will group the data together by Name, but for 
> each group will return the record with the highest version number and a count 
> of the items in the group.  So, with the above data, I'd expect this as 
> output:
> 
> ID  Name  Version Count
> --- - --- -
> 2   name1 1.0 2
> 3   name2 1.2 1
> 5   name3 1.7 3
> 
> Thanks for any assistance.

How about:

SELECT tbl.id, grp.name, grp.c, grp.v FROM
  (SELECT name, count(*) AS c, max(version) AS v 
   FROM tbl GROUP BY name
  ) AS grp, tbl
WHERE grp.name = tbl.name AND grp.version = tbl.version;

I think the question only makes sense if the combination of 
name and version are unique in the table.

Dan.




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

Re: [sqlite] Seg Fault when using sqlite3_bind_blob

[Dan Kennedy]

On Mon, 2007-07-02 at 08:51 -0400, Rich Rattanni wrote:
> I think I am incorrectly using the API, which may be leading to my
> segmentation fault...
> 
> I have a variable amount (22k -> 1MB) of data, split across several
> blobs to insert into a database.  I transactify the process to save
> some time (alot by my tests).
> 
> sqlite3_exec(db, "BEGIN IMMEDIATE", NULL, NULL, NULL);
> -For each blob...
> sqlite3_prepare(db, "INSERT INTO table1 (blobData) VALUES (?1)", -1,
> &stmt, NULL);
> sqlite3_bind_blob(stmt, 1, blobData, blobSize, SQLITE_TRANSIENT);
> sqlite3_step(stmt);  // with appropriate error checking
> sqlite3_finalize(stmt);
> -end for each.
> sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
> 
> Is this incorrect?  What I feel may be incorrect is the fact that I am
> finalizing the stmt before the commit.  I programmed this way because
> I did not want to have a separate stmt for each INSERT.  But I was
> wondering if the finalize is destroying copy of the blob data sqlite
> made during the call to sqlite3_bind_blob().

That looks fine. After the sqlite3_step() executes, SQLite has 
modified the pager cache to store the inserted blob. The 
statement handle may be finalized or reset at this point - the
insert has already happened.

Have you tried running valgrind or similar? Heap corruption can
result in mysterious behaviour, and shifting lines of code around
(like interchanging the free() and COMMIT ops) can sometimes make
the symptoms go away.

Dan.
 

> --
> Rich Rattanni
> 
> On 7/1/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> > I stand corrected, thank you Andrew.  I seriuosly doubt it is a bug in
> > SQlite, but I have had a hell of a time with sqlite and binding
> > dynamically allocated text and binary data.
> >
> > On 7/1/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> > > On 7/1/07, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> > > >
> > > > I was trying to look through the SQLITE source code to see how the
> > > > sqlite3_bind_blob routine worked.
> > > >
> > > > sqlite3_bind_blob passes the data pointer to bindText
> > > > bindText passes the data pointer to sqlite3VdbeMemSetStr
> > > > sqlite3VdbeMemSetStr then does...
> > > > ...
> > > > pMem->z = (char *)z;
> > > >   if( xDel==SQLITE_STATIC ){
> > > > pMem->flags = MEM_Static;
> > > >   }else if( xDel==SQLITE_TRANSIENT ){
> > > > pMem->flags = MEM_Ephem;
> > > >   }else{
> > > > pMem->flags = MEM_Dyn;
> > > > pMem->xDel = xDel;
> > > >   }
> > > > ...
> > > >
> > > > I dont see anywhere where sqlite3 copies data to a private buffer, I
> > > > just see where sqlite3 saves a copy of the user pointer.
> > > >
> > >
> > >
> > > Further down in that function, after setting MEM_Ephem, there are these
> > > lines of code:
> > >
> > >  if( pMem->flags&MEM_Ephem ){
> > >return sqlite3VdbeMemMakeWriteable(pMem);
> > >  }
> > >
> > > which does the memory copy when SQLITE_TRANSIENT is used as a side-effect 
> > > of
> > > making it "writable".
> > >
> > > In your original outline you issued sqlite3_step before freeing the 
> > > memory.
> > > If you leave it that way, you can get away with SQLITE_STATIC when binding
> > > the blob... which might indicate something by whether/where the crash 
> > > still
> > > occurs.
> > >
> > > --andy
> > >  just a sqlite user, not really a knower-of-code
> > >
> >
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] Possible memory leaks in shell.c

[Dan Kennedy]

On Mon, 2007-07-02 at 18:46 -0500, Ben Combee wrote:
> This one's in shell.c.  In process_input(), zLine is assigned using
> 
>   zLine = one_input_line(zSql, in);
> 
> Usually, you hit the free(zLine) call in one of the code paths before
> repeating the while loop.  However, on line 1614
> 
> if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue;
> 
> you can continue to the top of the while loop without freeing zLine,
> resulting in a leak when the next line of input is read.
> 
> It also looks like you can lose the memory attached to zLine if you
> hit line 1609's if statement
> 
>   if( in!=0 ) break;
> 
> and break out of the while loop.

I think you're right. I made some minor changes:

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

See what you think now:

  http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/shell.c&v=1.164

Dan.

  

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


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

RE: [sqlite] Problem: SQLite C++ Examples in the SQLite Documentation No Longer Work; Can't See Tables in Database

[Dan Kennedy]

How are you launching the second program (the one you wrote, not
sqlite3.exe)? It really sounds like you are opening the wrong file.

Try passing the full path to the database file to sqlite3_open(). 
Failing that, run windows find and search for files of the same
name. There's a good chance you will find a file of size 0 bytes with
the same name as your database in a different directory.

Dan.



On Tue, 2007-07-03 at 10:34 -0600, Lee Crain wrote:
> Ritesh Kapoor,
> 
> Thank you for your response. I attempted to send you a response complete
> with screenshots to eliminate any ambiguity but it was returned unsent
> because it was too large for your mail daemon (30,000 bytes). 
> 
> I took your advice and ran the query you suggested which produced this
> result:
> 
> --
> 
> sqlite> .tables
> t
> sqlite> .schema
> CREATE TABLE t( one varchar( 10 ), two smallint );
> sqlite> select * from sqlite_master;
> table|t|t|2|CREATE TABLE t( one varchar( 10 ), two smallint )
> sqlite>
> 
> --
> 
> 
> Also, I have taken care to make certain that the "sqlite3.exe" executable,
> all databases (highlighted in red), and my import files (*.txt) are all in
> the same directory to avoid path issues. Please see the screenshot below:
> 
> DELETED
> 
> 
> --
> 
> I've created a very simple database named "DBm" with one table "t" and 2
> fields, a varchar(10) and a smallint. Please see the screenshot below:
>  
> 
> DELETED - the screenshot showed the following:
> 
> 
> D:\DATA\SQLite>sqlite3 DBm
> SQLite version 3.3.17
> Enter ".help" for instructions
> sqlite> .tables
> t
> sqlite> .schema
> CREATE TABLE t( one varchar( 10 ), two smallint );
> sqlite>
>  
> 
> --
> 
> Then, using the examples in the SQLite documentation, I ran the example
> code: 
> 
> int main( )
> {
>   sqlite3 *db;
>   char *zErrMsg = 0;
>   int rc;
> 
>   // Test Open
>   rc = sqlite3_open( "DBm", & db );
>   if( rc )
>   {
> assert( false );
> fprintf( stderr, "Can't open database: %s\n", sqlite3_errmsg(
> db );
> sqlite3_close( db );
> Sleep( 5000 );
> return( 1 );
>   }
> 
> 
>   // Test SELECT
>   rc = sqlite3_exec( db, "SELECT * from t", callback, 0, &zErrMsg );
>   if( rc!=SQLITE_OK )
>   {
> fprintf( stderr, "SQL error: %s\n", zErrMsg );
> Sleep( 15000 );
> sqlite3_free( zErrMsg );
> return( 1 );
>   }
> 
> The failure occurs on the sqlite3_exec( ) call. 
> 
> --
> 
> And I receive this message (see screenshot):
> 
> 
> DELETED 
> 
> 
> "SQL error: no such table: t"
> 
> --
> 
>  
> What I'm attempting to do is about as vanilla an application as I can
> think of. 
> 
> I am open to any other suggestions as to what the problem might be. I hope
> you are correct that the problem will turn out to be something very small.
> 
> Sincerely,
> 
> Lee Crain
> Senior Software Engineer
> DAZ 3D Productions
> 801-495-1777, x759 
> [EMAIL PROTECTED]
> 
> __
> 
> 
> 
> -Original Message-
> From: Ritesh Kapoor [mailto:[EMAIL PROTECTED] 
> Sent: Monday, July 02, 2007 10:20 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem: SQLite C++ Examples in the SQLite
> Documentation No Longer Work; Can't See Tables in Database
> Importance: High
> 
> Try connecting to this database using the SQLite command line browser and
> then check if the table 'tb1' exists. Most probably it dosen't.
> 
> You can check which all tables have been created using the query -
> 
> "select * from sqlite_master"
> 
> Regards,
> Ritesh
> 
> > I've had to do a complete backtrack today. I went back to where I was 3
> > weeks ago.
> >
> >
> >
> > I used the SQLite command line documentation to create the "ex1"
> > database and enter 2 data records, exactly as the example shows.
> >
> >
> >
> > Then, I copied and pasted the SQLite C++ example into my program and
> > attempted to execute it. It failed with a "table does not exist" error
> > for the "tbl1" table in the "ex1" database.
> >
> >
> >
> > I've re-extracted the sqlite3 executable onto my hard drive and
> > overwritten the old one. I've completely cleaned and rebuilt my project.
> > Nothing has prevented this failure from recurring except for one thing:
> >
> > I have the original SQLite test database I created 3 weeks ago and I can
> > still read it with the SQLite C++ example code and my C++ code. Why is
> > this so?
> >
> >
> >
> > Is there some database metadata hidden somewhere on my hard drive that
> > might be corrupted? If so, how do I resolve the problem with it?
> >
> >
> >
> > Thanks,
> >
> >
> >
> > Lee Crain
> >
> > Senior Software Engineer

Re: [sqlite] Re: sqlite_finalize() releases resources?

[Dan Kennedy]

On Wed, 2007-07-04 at 09:58 +0200, Jef Driesen wrote:
> Igor Tandetnik wrote:
> > Mario Figueiredo wrote:
> >> I'm a tad bit confused with sqlite3_finalize() usage when the query
> >> fails. As it is, I'm assuming it releases resources and I use it in
> >> this context:
> >>
> >> 
> >> rc = sqlite3_prepare_v2(/* ... */);
> >> if (rc != SQLITE_OK)
> >> {
> >>   sqlite3_finalize(stmt);
> >>   /* ... */
> >> }
> > 
> > This doesn't make any sense. If prepare fails, you do not have a valid 
> > statement handle to call finalize on.
> > 
> > Igor Tandetnik
> 
> Are you sure about that? The documentation for sqlite3_prepare_v2 says:

Igor is, as usual, correct.

The situation in 3.4.0 is that if sqlite3_prepare() returns other 
than SQLITE_OK, you are guaranteed that *ppStmt is set to NULL.
You may call sqlite3_finalize() on this if you wish - it's a no-op.

Historically, it may have been that *ppStmt was sometimes left
uninitialized if an error occured (hence the "may" in the docs).

This is in contrast to sqlite3_open(). You must call sqlite3_close(),
even if sqlite3_open() returned an error code.

Dan.

> *ppStmt is left pointing to a compiled SQL statement structure that can 
> be executed using sqlite3_step(). Or if there is an error, *ppStmt may 
> be set to NULL. If the input text contained no SQL (if the input is and 
> empty string or a comment) then *ppStmt is set to NULL. The calling 
> procedure is responsible for deleting the compiled SQL statement using 
> sqlite3_finalize() after it has finished with it.
> 
> I also thought this means the statement has to be deleted with 
> sqlite3_finalize, even when sqlite3_prepare_v2 failed (except for the 
> case where NULL is returned). Notice the "may be set to NULL" in the 
> documentation. Doesn't a non-NULL value indicate some memory was 
> allocated and thus need to be freed?




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

Re: [sqlite] Re: sqlite_finalize() releases resources?

[Dan Kennedy]

On Wed, 2007-07-04 at 12:37 +0200, Jef Driesen wrote:
> Dan Kennedy wrote:
> > On Wed, 2007-07-04 at 09:58 +0200, Jef Driesen wrote:
> >> Igor Tandetnik wrote:
> >>> Mario Figueiredo wrote:
> >>>> I'm a tad bit confused with sqlite3_finalize() usage when the query
> >>>> fails. As it is, I'm assuming it releases resources and I use it in
> >>>> this context:
> >>>>
> >>>> 
> >>>> rc = sqlite3_prepare_v2(/* ... */);
> >>>> if (rc != SQLITE_OK)
> >>>> {
> >>>>   sqlite3_finalize(stmt);
> >>>>   /* ... */
> >>>> }
> >>> This doesn't make any sense. If prepare fails, you do not have a valid 
> >>> statement handle to call finalize on.
> >>>
> >> Are you sure about that? The documentation for sqlite3_prepare_v2 says:
> > 
> > Igor is, as usual, correct.
> > 
> > The situation in 3.4.0 is that if sqlite3_prepare() returns other 
> > than SQLITE_OK, you are guaranteed that *ppStmt is set to NULL.
> > You may call sqlite3_finalize() on this if you wish - it's a no-op.
> > 
> > Historically, it may have been that *ppStmt was sometimes left
> > uninitialized if an error occured (hence the "may" in the docs).
> 
> What do you mean with uninitialized? Leaving the pointer unchanged, or 
> pointing to some memory that is already freed or still needs to be 
> freed? This is important if you need to support older versions.
>
> I suppose you mean the first one, but I'm asking anyway just to be sure. 
> In my code, I always initialize pointers to NULL, so this case would not 
> cause any problems at all. The second case is a completely different 
> story of course.

Right, leaving the pointer unchanged.

Dan.



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

Re: [sqlite] How do I get sources for previous versions?

[Dan Kennedy]

On Wed, 2007-07-04 at 13:25 +0200, Makavy, Erez (Erez) wrote:
> Hi,
>  
> How do I get sources for previous versions? such as 3.3.4.
>  
> Thanks,
> Erez.

Do a cvs checkout of the latest sources, as described here:

  http://www.sqlite.org/download.html#cvs

Then use the [cvs update] command with the -D option to
specify the exact date/time you want sources from. You
can figure this out by looking at the timeline:

  http://www.sqlite.org/cvstrac/timeline

All times on the timeline are UTC. For 3.3.4, try 
"2006/02/11 03:00 UTC" as the timestamp.

Dan.





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

Re: [sqlite] FTS and upgrades

[Dan Kennedy]

On Thu, 2007-07-05 at 21:08 -0700, Dave Brown wrote:
> I am considering using FTS for a new feature. My question is:
> 
> - If I use fts1 (or fts2) now, and then a new version of fts comes out which
> is not backwards compatible, will I at least be able to upgrade users by
> simply deleting the old FTS table and re-building it using the new FTS
> system?
> 
> In other words, my users will have a database file built with fts1, then my
> new upgraded code (which contains sqlite with fts3, for example) will try to
> upgrade them by deleting the fts1 table, and creating a new table.
> 
> Will the delete of the old table at least be guaranteed to work?

fts1 and fts2 are separate extensions that happen to do similar things.
You can load them both at the same time and have a single database
connection access both fts1 and fts2 tables. The same should be
true with fts3.

Dan. 


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

Re: [sqlite] sqlite3_step

[Dan Kennedy]

On Thu, 2007-07-05 at 18:26 -0300, Bruno S. Oliveira wrote:
> Hi all,
> 
> I'm having problems with the sqlite3_step function.
> I have a table with only one record and then I execute the
> sqlite3_step twice (with some processing in between). In the first run
> of the function everything goes fine, but, in the second one, it
> returns SQLITE_ROW even with only one record in the table and with no
> modifications in the SQL statement.
> Is this the correct behaviour of that function? Does anyone knows why
> this happens?
> 
> I'm using the sqlite3_prepare before executing the sqlite3_step.

Could be a few things. If you post the problematic code somebody 
will be able to help you.

Dan.



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

Re: [sqlite] Views

[Dan Kennedy]

On Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote:
> I have to join three tables to retrieve bits of data from each. I'm wondering 
> if I use a view are there any performance issues vs. issuing the complete 3 
> table join query in code.
> 
> Also, is it quicker to have sqlite do joins on primary keys between tables 
> instead of doing three separate single table queries on the primary key in 
> code?

The short answers are probably not and no.

But it depends on the exact view you're defining. If it's just a join, 
no GROUP BY or ORDER BY or aggregate functions, it should be fine.

Dan.



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

Re: [sqlite] Views

[Dan Kennedy]

On Fri, 2007-07-06 at 08:42 -0700, Mike Johnston wrote:
> n Fri, 2007-07-06 at 07:18 -0700, Mike Johnston wrote:
> > I have to join three tables to retrieve bits of data from each. I'm
>  wondering if I use a view are there any performance issues vs. issuing
>  the complete 3 table join query in code.
> > 
> > Also, is it quicker to have sqlite do joins on primary keys between
>  tables instead of doing three separate single table queries on the
>  primary key in code?
> 
> >The short answers are probably not and no.

> >But it depends on the exact view you're defining. If it's just a join, 
> >no GROUP BY or ORDER BY or aggregate functions, it should be fine.
> 
> >Dan.
> 
> They are very simple select statements retrieving exactly one row.
> 
> On the second point, are you saying that doing a select with a three table 
> join is no quicker than having three groups of sqlite_prepare, sqlite_step 
> sequences for single table, single row lookups?  That just seems a little 
> counter-intuitive (at least to me).

Sorry, I think I read the question the wrong way around. It
probably is a bit faster to use a join query because you 
call the parser less. The point I meant to make was that the
I/O operations on the file-system/database-cache are the same 
either way.

Dan.


> Mike  
>
> -
> Choose the right car based on your needs.  Check out Yahoo! Autos new Car 
> Finder tool.


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

Re: [sqlite] LIKE operator case-senisitive for international characters

[Dan Kennedy]

On Tue, 2007-07-10 at 22:23 -0700, Sweden wrote:
> I have searched many forums without success for the following question:
> 
> Is there any possibility to use LIKE operator  in a SELECT statment -
> without being case sensitive AND with support for international characters?
> I am using "PRAGMA case_sensitive_like=OFF;". The LIKE statement is in the
> format '%what%' (*.*). This works fine for English characters but not for
> Swedish.
> 
> E.g. LIKE "%åker%'  
> 
> returns "fred åkerholm" 
> 
> but NOT "Fred Åkerholm"
> 
> Doc says "International character sets are case sensitive in SQLite unless a
> user-supplied collating sequence is used. But if you employ a user-supplied
> collating sequence, the LIKE optimization describe here will never be
> taken". 
> 
> Regards, 
> 
> Michael from Sweden

There is an extension bundled with SQLite sources that uses the ICU
library to do this. See here:

http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt&v=1.2

Dan.



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

Re: [sqlite] FTS and upgrades

[Dan Kennedy]

On Tue, 2007-07-10 at 16:02 -0700, Joe Wilson wrote:
> --- [EMAIL PROTECTED] wrote:
> > "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > > If you have an fts1 table f, you could drop f_term and f_content, but
> > > you won't be able to drop f itself.  So you would have to name the
> > > fts2 version of f something else, like f2.
> > > 
> > 
> > I probably shouldn't tell you this, but
> > 
> > There is a pragma:
> > 
> >PRAGMA writable_schema=ON;
> > 
> > Which when enabled allows you to UPDATE or DELETE against the
> > sqlite_master table.  So you could turn on writable_schema
> > then do:
> > 
> >DELETE sqlite_master WHERE type='f';
> 
> Will all the btree pages orphaned at rootpage for these rows 
> not be recyclable until you run a full VACUUM?

Like Scott says, it doesn't matter with a virtual table, as it
has no physical representation in the database file except for
the entry in sqlite_master.

But if you delete the record for a regular table or index from
the sqlite_master table using the "writable_schema" backdoor,
you have just corrupted the database. In some cases a VACUUM
will fix this corruption, in some cases not.

The "writable_schema" pragma has another side effect too. When
it is set, if an error occurs while loading the schema then the
error is returned to the user as normal. But internally, the
subset of the schema that did load stays loaded (normally it
would be discarded and reloaded for the next query). So you
can end up with a database connection that can only "see" a
subset of the tables and indices. If you then update a table
that has an index for which the schema failed to load - database
corruption again.

In short - be really careful with this pragma :)

Dan.

   
> 
> Get the free Yahoo! toolbar and rest assured with the added security of 
> spyware protection.
> http://new.toolbar.yahoo.com/toolbar/features/norton/index.php
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] RE: Question regarding INTEGER PRIMARY KEY (zero value in column) ?

[Dan Kennedy]

On Thu, 2007-07-12 at 23:38 -0700, RohitPatel wrote:
> Hi
> 
> I have a Question regarding INTEGER PRIMARY KEY (zero value in column) ?
> 
> Example table => create table {id INTEGER PRIMARY KEY, name TEXT};
> 
> Is it ever possible that value 0 (zero) will be inserted in a column
> declared as INTEGER PRIMARY KEY (and not as AUTOINCREMENT) ? Inserts are
> always with NULL value for that column. (i.e zero is never inserted
> implicitly in that column)

It's possible. But it's very unlikely. The fifth paragraph of
the following document describes how values are allocated for
integer primary key columns not declared as AUTOINCREMENT
columns:
 
  http://www.sqlite.org/autoinc.html

Dan.



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