Re: [sqlite] SQLite and nested transactions

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:

> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- [EMAIL PROTECTED] wrote:
> > > It may be more difficult to implement this in a backwards-compatible
> > > way such that older versions of SQLite can rollback a journal created
> > > by a newer version if it encounters one.
> > 
> > I wonder if there are many projects that have different versions of 
> > SQLite updating and reading the same database file at the same time.
> > This can't be very common.
> > 
> 
> As we learned from the release of 3.3.0, this is more common
> that you might expect.  There are surprisingly many projects 
> that expect to be able to access SQLite databases from both 
> older and newer versions of the library.

It's not quite the same thing - the 3.x file format change was not 
backwards compatible _at all_ with previous versions of SQLite.
Having a large degree of backwards compatibilty makes all the difference.

Changing the journal file format to accommodate a hypothetical new 
feature would still produce a backwards compatible database _except_ 
in the rare case where a transaction in a new version of SQLite is 
abruptly aborted or if the power fails. If the transactions are finished 
you would still have backwards compatibility with previous versions.

But I still think that simultaneous read/write access to the same database 
file with different version of SQLite is not very common.


   

Don't get soaked.  Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather

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

Re: [sqlite] 3.3.15 test coverage improvements? (.dump again)

[Joe Wilson]

Unfortunately, the sqlite3 commandline shell is not part of the test suite
despite its widespread use as an administrative tool for sqlite databases.

 http://marc.info/?l=sqlite-users=117253099812346=2

But you know this already - you're the same guy as this previous post.  :-)

--- Travis Daygale <[EMAIL PROTECTED]> wrote:
> Change log for 3.3.15 says:
> Many improvements to the test suite.  Test coverage now exceeded 98%
> 
> What does this mean?  
> 
> Does it mean that (say) the sqlite3 command line tool (especially the .dump 
> command) is tested
> at each release now?
> 
>   ---
> 
> I'm asking this because previously on this list it was made clear that the 
> command line tool was
> not part of the test suite.  The .dump command in that tool is (very very 
> often!!!) described as
> a good backup.  But it isn't (IMHO).  It's not a tested tool, and indeed, 
> there were 3.x.x
> releases where that tool was not correctly dumping out (eg.) triggers.  
> 
> It just seems that to download sqlite source and know that it contains a 
> reliable backup tool
> with it would add to this first rate open source software, regardless of how 
> simple it might be
> to write our own code in our language of choice to dump databases.
> 
> Either that or consider removing the .dump command?  I say this because I 
> have seen so very much
> documentation that references the .dump feature, and yet that feature lies in 
> code that is
> (was?) tested very differently than the rest of sqlite.That just seems 
> uncomfortable... 
> 
> Backups are so important, etc..
> 
> I hope my point is made.  I'm not complaining here at all.   Trying to be 
> helpful.



   

Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

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

RE: [sqlite] File size issue?

[Joe Wilson]

> What is worse is that VACUUM didn't really help that much. It takes
> forever, and it doesn't really "fix" the fragmentation either.

That used to be the case, but VACUUM is vastly improved in the latest 
version of SQLite.


   

Sucker-punch spam with award-winning protection. 
Try the free Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/features_spam.html

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

Re: [sqlite] Select columns & performance

[Joe Wilson]

--- Mike Johnston <[EMAIL PROTECTED]> wrote:
> Are there any significant performance benefits by limiting the number of 
> columns in a single
> table select statement?   Does joining (<5 tables) make a significant 
> difference to that answer?

If you need the columns, you don't have much choice but to select them.

Limiting the number of columns in subqueries makes a big difference.
i.e., try to avoid SELECT * in subqueries when you just need a few values.

Do a lot of benchmarking.


   

Get your own web address.  
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

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

Re: [sqlite] Help with SQL syntax. Ticket #2296

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Consider this query:
> 
>SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b;
> 
> Is the query above equalent to:
> 
>   (1)  SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2;
> 
> Or is it the same as:
> 
>   (2)  SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1;

Most databases use form (1).

SQLite already leans in this direction anyway, using the first select 
in a union for the headings. May as well be consistant.

  SQLite version 3.3.15
  Enter ".help" for instructions
  sqlite> .header on
  sqlite> select 3 a union select 4 b;
  a
  3
  4



   

8:00? 8:25? 8:40? Find a flick in no time 
with the Yahoo! Search movie showtime shortcut.
http://tools.search.yahoo.com/shortcuts/#news

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

Re: [sqlite] Database performance

[Joe Wilson]

--- "Andrey A. Lapin" <[EMAIL PROTECTED]> wrote:
> I develop database in which information on the traffic (headings of packets
> MAC, TCP/IP) is stored.
> Processing of queries occurs slowly.
> For example, quantity of records in a database of 17 million lines and its
> size 3 Gb.
> How to increase performance?

No one will be able to help you unless you post the table schema and
provide some working SQL expressions that you wish to speed up.

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

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

Re: [sqlite] Journal file question

[Joe Wilson]

--- DragonK <[EMAIL PROTECTED]> wrote:
> I'm having the following problem:  a sqlite database file is on an NTFS
> filesystem, in a directory with no permissions to create new files, but only
> to modify the original database. By using filemon i've noticed some access
> denied errors when sqlite attempted to create the journal files.
> I've created a sepparate test case and (by using filemon again) i've noticed
> that indeed, sqlite uses the journal file, even outside transactions (an
> insert sql was executed).
> 
> My question is how can I stop this behaviour (creating/deleting the journal)
> so that sqlite will work properly under the scenario described above (when
> it can't create the journal)?

No problem - just create your own virtual file system in a file and
change sqlite's I/O functions:

/*
** An instance of the following structure contains pointers to all
** methods on an OsFile object.
*/
struct IoMethod {
  int (*xClose)(OsFile**);
  int (*xOpenDirectory)(OsFile*, const char*);
  int (*xRead)(OsFile*, void*, int amt);
  int (*xWrite)(OsFile*, const void*, int amt);
  int (*xSeek)(OsFile*, i64 offset);
  int (*xTruncate)(OsFile*, i64 size);
  int (*xSync)(OsFile*, int);
  void (*xSetFullSync)(OsFile *id, int setting);
  int (*xFileHandle)(OsFile *id);
  int (*xFileSize)(OsFile*, i64 *pSize);
  int (*xLock)(OsFile*, int);
  int (*xUnlock)(OsFile*, int);
  int (*xLockState)(OsFile *id);
  int (*xCheckReservedLock)(OsFile *id);
  int (*xSectorSize)(OsFile *id);
};

I don't know of any other way given your constraints.

See also: Single-file virtual file systems
http://en.wikipedia.org/wiki/Virtual_file_system


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

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

Re: [sqlite] How is the windows binary built?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> I'm using cygwin under windows XP.
> gcc version 3.4.4
> 
> I unzipped the sqlite-3.3.16.tar.gz to the directory sqlite-3.3.16.
> 
> Executed the following:
> 
> cd sqlite-3.3.16
> mkdir build
> cd build
> ./configure
> make
> 
> The resulting sqlite3.exe is 4 times bigger than the windows release in 
> sqlite-3.3.16.zip.
> 
> Can anyone explain the size difference?

The debug symbols.

  strip sqlite3.exe

The sqlite3.exe posted on the website is built with a MinGW gcc cross 
compiler hosted on Linux. It is not dependent on cygwin DLLs, unlike the
cygwin version:

# cygwin
$ cygcheck ./sqlite3.exe 
.\sqlite3.exe
  c:\cygwin\bin\cygwin1.dll
C:\WINNT\system32\ADVAPI32.DLL
  C:\WINNT\system32\NTDLL.DLL
  C:\WINNT\system32\KERNEL32.DLL
  C:\WINNT\system32\RPCRT4.DLL
  c:\cygwin\bin\cygreadline6.dll
c:\cygwin\bin\cygncurses-8.dll
C:\WINNT\system32\USER32.dll
  C:\WINNT\system32\GDI32.DLL

# MinGW
$ cygcheck ./sqlite3.exe 
.\sqlite3.exe
  C:\WINNT\system32\KERNEL32.dll
C:\WINNT\system32\NTDLL.DLL
  C:\WINNT\system32\msvcrt.dll

DLLs aside, the cygwin version has the advantage of playing nice with 
rxvt and xterm.

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

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

Re: Re: [sqlite] How is the windows binary built?

[Joe Wilson]

--- Miha Vrhovnik <[EMAIL PROTECTED]> wrote:
> >  strip sqlite3.exe
> is not ok at least for DLL, because it strips everything even reallocation 
> info.
> strip --strip-unneeded sqlite3.dll

The GNU toolchain's strip command corrupts the reallocation information for 
MinGW DLLs, so I would not recommend using it.

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

Use the configure generated Makefile target "make sqlite3.dll" instead for 
MinGW to have the linker not generate the extra stuff in the first place:

#
# Windows section
#
dll: sqlite3.dll

REAL_LIBOBJ = $(LIBOBJ:%.lo=.libs/%.o)

$(REAL_LIBOBJ): $(LIBOBJ)

sqlite3.def: $(REAL_LIBOBJ)
echo 'EXPORTS' >sqlite3.def
nm $(REAL_LIBOBJ) | grep ' T ' | grep ' _sqlite3_' \
| sed 's/^.* _//' >>sqlite3.def

sqlite3.dll: $(REAL_LIBOBJ) sqlite3.def
$(TCC) -shared -o sqlite3.dll sqlite3.def \
-Wl,"--strip-all" $(REAL_LIBOBJ)


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

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

[sqlite] Re: sqlite3 shell doesn't install correctly

[Joe Wilson]

> I've used SQLite-3.3.4 for quite a while, mostly the Tcl binding.
> A few days ago I installed 3.3.15 and the Tcl binding worked fine.
> Tonight I downloaded 3.3.16 and compiled it without any errors
> or warnings and then installed it. When I tried to execute sqlite3
> the following error appered:
> 
> sqlite3: symbol lookup error: sqlite3: undefined symbol: sqlite3_io_trace
> 
> I switched back to 3.3.15 with the same result. A switch back to
> 3.3.4 behaved normal.
> 
> What's wrong with 3.3.15 and 3.3.16?


Apply this patch to fix this problem.


Index: src/main.c
===
RCS file: /sqlite/sqlite/src/main.c,v
retrieving revision 1.370
diff -u -3 -p -r1.370 main.c
--- src/main.c  18 Apr 2007 14:24:33 -  1.370
+++ src/main.c  21 Apr 2007 13:35:18 -
@@ -27,6 +27,7 @@ const char sqlite3_version[] = SQLITE_VE
 const char *sqlite3_libversion(void){ return sqlite3_version; }
 int sqlite3_libversion_number(void){ return SQLITE_VERSION_NUMBER; }
 
+#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
 /*
 ** If the following function pointer is not NULL and if
 ** SQLITE_ENABLE_IOTRACE is enabled, then messages describing
@@ -34,6 +35,7 @@ int sqlite3_libversion_number(void){ ret
 ** are intended for debugging activity only.
 */
 void (*sqlite3_io_trace)(const char*, ...) = 0;
+#endif
 
 /*
 ** If the following global variable points to a string which is the
Index: src/shell.c
===
RCS file: /sqlite/sqlite/src/shell.c,v
retrieving revision 1.160
diff -u -3 -p -r1.160 shell.c
--- src/shell.c 28 Feb 2007 06:14:25 -  1.160
+++ src/shell.c 21 Apr 2007 13:35:19 -
@@ -1242,6 +1242,7 @@ static int do_meta_command(char *zLine, 
 }
   }else
 
+#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
   if( c=='i' && strncmp(azArg[0], "iotrace", n)==0 ){
 extern void (*sqlite3_io_trace)(const char*, ...);
 if( iotrace && iotrace!=stdout ) fclose(iotrace);
@@ -1261,6 +1262,7 @@ static int do_meta_command(char *zLine, 
   }
 }
   }else
+#endif
 
 #ifndef SQLITE_OMIT_LOAD_EXTENSION
   if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){


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

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

[sqlite] Re: sqlite3 shell doesn't install correctly

[Joe Wilson]

> I've used SQLite-3.3.4 for quite a while, mostly the Tcl binding.
> A few days ago I installed 3.3.15 and the Tcl binding worked fine.
> Tonight I downloaded 3.3.16 and compiled it without any errors
> or warnings and then installed it. When I tried to execute sqlite3
> the following error appered:
> 
> sqlite3: symbol lookup error: sqlite3: undefined symbol: sqlite3_io_trace
> 
> I switched back to 3.3.15 with the same result. A switch back to
> 3.3.4 behaved normal.
> 
> What's wrong with 3.3.15 and 3.3.16?

Okay, I get it now. You seem to be using a 3.3.15 sqlite3 binary requiring
the shared library sqlite3.so. This 3.3.15 sqlite3 binary's shell.c makes 
reference to sqlite3_io_trace in the 3.3.15 sqlite3.so's main.c.
You cannot use an older 3.3.4 sqlite3.so against the 3.3.15 sqlite3 binary
because the older shared library lacks sqlite3_io_trace.

In general, sqlite3.so is backwards compatible, but not necessarily 
forward compatible. So you can upgrade an older binary to use a more 
recent 3.x sqlite3.so release, but not the other way around.


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

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

Re: [sqlite] sqlite3 shell doesn't install correctly

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Ulrich =?iso-8859-1?q?Sch=F6bel?= <[EMAIL PROTECTED]> wrote:
> > On Saturday 21 April 2007 15:40, Joe Wilson wrote:
> > >
> > > Apply this patch to fix this problem.
> > >
> > 
> > Thanks for the patch, but there is still something wrong in the
> > linking stage.
> > 
> 
> I do not understand why you are having problems.  Nor can
> I figure out why Joe's patch might help.

I was just trying to avoid the unresolved references to sqlite3_io_trace
that he was reporting, without knowing what options he built the code with
in the first place.

But is there any reason why the reference sqlite3_io_trace in shell.c
and the extern sqlite3_io_trace in main.c should not be #ifdef'd like 
the others?


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

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

Re: [sqlite] Re: sqlite3 shell doesn't install correctly

[Joe Wilson]

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> In general, sqlite3.so is backwards compatible, but not necessarily 
> forward compatible. So you can upgrade an older binary to use a more 
> recent 3.x sqlite3.so release, but not the other way around.

I meant to to say:

 In general, sqlite3.so is forward compatible, but not necessarily 
 backwards compatible. So you can upgrade an older binary to use a more 
 recent 3.x sqlite3.so release, but not the other way around.


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

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

[sqlite] older http://sqlite.org/sqlite-3.x.y.tar.gz downloads no longer available?

[Joe Wilson]

I noticed that sqlite tar.gz downloads prior to 

  http://sqlite.org/sqlite-3.3.10.tar.gz 

are missing. So historical releases are not available except by CVS.
Is this intentional?

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

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

Re: [sqlite] Re: sqlite3 shell doesn't install correctly

[Joe Wilson]

--- Ulrich Schöbel <[EMAIL PROTECTED]> wrote:
> Thanks for the patch, but there is still something wrong in the
> linking stage.
> 
> I have 3 instances of SQLite on my system:
> The first is 3.2.1, installed with the system and not used, at least
> not by me, library in /usr/lib.
> The second is installed in a separate directory tree, version 3.3.4,
> which I don't want to change, at least not now.
> The third is in /usr/local/..., version 3.3.4 until now, to be substituted
> by 3.3.16.
> 
> When I run make it produces an sqlite3 binary linked against the
> 3.3.4 library. I made it temporarily unsearchable, now it links
> against the 3.2.1. Next step was to remove the 3.2.1 lib in /usr/lib.
> Now the linker doesn't find a library. It simply refuses to accept the
> right one. Scanning libtool showed all three lib directories in place.
> 
> The shell script sqlite3 runs fine as it did yesterday without the patch.

To avoid future linkage and path problems with the sqlite3 shell, 
I'd recommend building it statically. Fewer possible problems.

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

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

Re: [sqlite] older http://sqlite.org/sqlite-3.x.y.tar.gz downloads no longer available?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > I noticed that sqlite tar.gz downloads prior to 
> > 
> >   http://sqlite.org/sqlite-3.3.10.tar.gz 
> > 
> > are missing. So historical releases are not available except by CVS.
> > Is this intentional?
> 
> Yes.  Why would you want an historical release?  Everything
> is backwards compatible.

To support and debug software using old specific old sqlite releases
(both open source and proprietary). I was trying to see if 3.3.4 had
sqlite3_io_trace, for example. Also, some historical versions have (much)
smaller memory footprints in certain cases.

In my case, SQLite 3.2.2 in particular executes several classes
of data mining queries 10X faster using 100X less temp store involving 
GROUP BY clauses (that cannot be indexed).

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

I still recommend that version for exactly this case.

It's not a big problem no longer having the old releases on the 
site - one could grab a release via CVS - I just was wondering 
why the recent change?


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

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

Re: [sqlite] v3.6.16-threads fails to build via freebsd ports sys

[Joe Wilson]

This libtool --tag problem does not seem to be unique to sqlite.

 http://lists.freebsd.org/pipermail/freebsd-questions/2005-November/104076.html

I think it's a question for the freebsd port maintainers.

--- snowcrash <[EMAIL PROTECTED]> wrote:
> hi,
> 
> building v3.3.16 from src on osx works fine.
> 
> trying same, using freebsd 6.2's ports sys fails to build,
> 
> % portinstall sqlite3-threads
> [Updating the pkgdb  in /var/db/pkg ... - 97
> packages found (-1 +1) (...). done]
> --->  Installing 'sqlite3-threads-3.3.16' from a port
> (databases/sqlite3-threads)
> --->  Building '/usr/ports/databases/sqlite3-threads'
> ...
> /usr/local/bin/libtool --mode=compile --tag=CC
> /usr/local/libexec/ccache/world-cc -pipe -march=pentium-mmx
> -DSQLITE_ENABLE_FTS1 -g -I. -I./src -DSQLITE_DEBUG=1
> -I/usr/local/include/tcl8.4 -DTHREADSAFE=1
> -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c
> ./ext/fts1/fts1_tokenizer1.c
>  /usr/local/libexec/ccache/world-cc -pipe -march=pentium-mmx
> -DSQLITE_ENABLE_FTS1 -g -I. -I./src -DSQLITE_DEBUG=1
> -I/usr/local/include/tcl8.4 -DTHREADSAFE=1
> -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c
> ./ext/fts1/fts1_tokenizer1.c  -fPIC -DPIC -o .libs/fts1_tokenizer1.o
>  /usr/local/libexec/ccache/world-cc -pipe -march=pentium-mmx
> -DSQLITE_ENABLE_FTS1 -g -I. -I./src -DSQLITE_DEBUG=1
> -I/usr/local/include/tcl8.4 -DTHREADSAFE=1
> -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c
> ./ext/fts1/fts1_tokenizer1.c -o fts1_tokenizer1.o >/dev/null 2>&1
> /usr/local/bin/libtool --mode=link /usr/local/libexec/ccache/world-cc
> -pipe -march=pentium-mmx -DSQLITE_ENABLE_FTS1 -g -I. -I./src
> -DSQLITE_DEBUG=1  -I/usr/local/include/tcl8.4 -DTHREADSAFE=1
> -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -o
> libsqlite3.la alter.lo analyze.lo attach.lo auth.lo btree.lo build.lo
> callback.lo complete.lo date.lo delete.lo expr.lo func.lo hash.lo
> insert.lo loadext.lo main.lo opcodes.lo os.lo os_unix.lo os_win.lo
> os_os2.lo pager.lo parse.lo pragma.lo prepare.lo printf.lo random.lo
> select.lo table.lo tokenize.lo trigger.lo update.lo util.lo vacuum.lo
> vdbe.lo vdbeapi.lo vdbeaux.lo vdbefifo.lo vdbemem.lo where.lo utf.lo
> legacy.lo vtab.lo fts1.lo fts1_hash.lo fts1_porter.lo
> fts1_tokenizer1.lo -pthread  \
>  -rpath /usr/local/lib -version-info "8:6:8"
> libtool: link: unable to infer tagged configuration
> libtool: link: specify a tag with `--tag'
> gmake: *** [libsqlite3.la] Error 1
> *** Error code 2
> 
> Stop in /usr/ports/databases/sqlite3-threads.
> ** Command failed [exit code 1]: /usr/bin/script -qa
> /tmp/portinstall.47997.0 env make
> ** Fix the problem and try again.
> ** Listing the failed packages (*:skipped / !:failed)
> ! databases/sqlite3-threads (unknown build error)
> --->  Packages processed: 0 done, 0 ignored, 0 skipped and 1 failed
> %
> 
> is this a known issue?  short of a manual build, is there a fix/workaround?



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

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

Re: [sqlite] test fixture tcl errors on Windows

[Joe Wilson]

The test script itself has the test.db open, and as result Windows cannot
delete an open file. It worked on UNIX because you can delete anything at 
any time whether it is open, running, locked or whatever.

If you apply this patch, exclusive2.test will run to completion on cygwin
without error. It ought to work on MinGW as well.


Index: test/exclusive2.test
===
RCS file: /sqlite/sqlite/test/exclusive2.test,v
retrieving revision 1.4
diff -u -3 -p -r1.4 exclusive2.test
--- test/exclusive2.test16 Apr 2007 15:02:20 -  1.4
+++ test/exclusive2.test25 Apr 2007 02:42:56 -
@@ -161,6 +161,7 @@ do_test exclusive2-2.4 {
   seek $fd 1024
   puts -nonewline $fd [string repeat [binary format c 0] 1]
   flush $fd
+  close $fd
   t1sig
 } $::sig
 

--- Dennis Cote <[EMAIL PROTECTED]> wrote:

> I'm getting a strange failure of the test suite on Windows (XP all 
> updates). The exclusive2 test is getting a permission denied error when 
> deleting a file. I can manually delete the file and the tclsh can 
> execute the same file delete command if I enter it manually.
> 
> $ ./testfixture.exe ../sqlite/test/exclusive2.test
> exclusive2-1.0... Ok
> exclusive2-1.1... Ok
> exclusive2-1.2... Ok
> exclusive2-1.3... Ok
> exclusive2-1.4... Ok
> exclusive2-1.5... Ok
> exclusive2-1.6... Ok
> exclusive2-1.7... Ok
> exclusive2-1.9... Ok
> 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... Ok
> exclusive2-2.6... Ok
> exclusive2-2.7... Ok
> exclusive2-2.8... Ok
> c:\SQLite\SQLiteV3\build\testfixture.exe: error deleting "test.db": 
> permission denied
> while executing
> "file delete -force test.db"
> (file "../sqlite/test/exclusive2.test" line 192)
> 
> $ ls test.db
> test.db
> 
> $ tclsh
> file delete -force test.db
> exit
> 
> $ ls test.db
> ls: test.db: No such file or directory
> 
> I'm building SQLite and running these tests using MinGW/MSYS. I haven't 
> had any problems before, but I haven't built sqlite from source since 
> around version 3.3.12.


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

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

Re: [sqlite] test fixture tcl errors on Windows

[Joe Wilson]

a better patch...

Index: test/exclusive2.test
===
RCS file: /sqlite/sqlite/test/exclusive2.test,v
retrieving revision 1.4
diff -u -3 -p -r1.4 exclusive2.test
--- test/exclusive2.test16 Apr 2007 15:02:20 -  1.4
+++ test/exclusive2.test25 Apr 2007 03:05:04 -
@@ -161,6 +161,7 @@ do_test exclusive2-2.4 {
   seek $fd 1024
   puts -nonewline $fd [string repeat [binary format c 0] 1]
   flush $fd
+  close $fd
   t1sig
 } $::sig
 
@@ -242,4 +243,8 @@ do_test exclusive2-3.6 {
   pagerChangeCounter test.db
 } {5}
 
+db close
+file delete -force test.db
+file delete -force test.db-journal
+
 finish_test



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

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

Re: [sqlite] how to compile sqlite3 in ads1.2 environment?

[Joe Wilson]

What's an ads1.2?

--- [EMAIL PROTECTED] wrote:
> Dose anybody compile the sqlite3 in the ads1.2 environment?

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

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

Re: [sqlite] how to compile sqlite3 in ads1.2 environment?

[Joe Wilson]

Try compiling with sqlite3.c from http://sqlite.org/sqlite-source-3_3_17.zip

It is already pre-generated and does not require generating the parser from 
the .y file.

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

--- [EMAIL PROTECTED] wrote:
> arm developer suite v1.2
> support arm7,arm9


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

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

Re: [sqlite] sqlite and borland c++ builder

[Joe Wilson]

>   When I try to use the header I get errors
> 
>  [C++ Error] sqlite3.h(1778): E2232 Constant member
> 'sqlite3_index_info::nConstraint' in class without constructors

It appears it is trying to compile the sqlite header file as if it were C++.
Lemme guess - you're using the almalgomated sqlite3.c from 3.3.17, right?

from the generated sqlite3.c:

 /*
 ** Make sure we can call this stuff from C++.
 */
 #if 0
 extern "C" {
 #endif

See the #if 0? That's the problem. It should be:

 #if __cplusplus

SQLite 3.3.17 has a bug in sqlite3.c generation.
To work around this issue, do this:

 extern "C" {
 #include "sqlite3.h"
 }


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

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

Re: [sqlite] sqlite and borland c++ builder

[Joe Wilson]

I wrote too quickly - sqlite3.h correctly uses __cplusplus for extern "C".

But the almalgomated sqlite3.c cannot be compiled from a C++ compiler
for the reasons described below.

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> >   When I try to use the header I get errors
> > 
> >  [C++ Error] sqlite3.h(1778): E2232 Constant member
> > 'sqlite3_index_info::nConstraint' in class without constructors
> 
> It appears it is trying to compile the sqlite header file as if it were C++.
> Lemme guess - you're using the almalgomated sqlite3.c from 3.3.17, right?
> 
> from the generated sqlite3.c:
> 
>  /*
>  ** Make sure we can call this stuff from C++.
>  */
>  #if 0
>  extern "C" {
>  #endif
> 
> See the #if 0? That's the problem. It should be:
> 
>  #if __cplusplus
> 
> SQLite 3.3.17 has a bug in sqlite3.c generation.
> To work around this issue, do this:
> 
>  extern "C" {
>  #include "sqlite3.h"
>  }
> 
> 
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 


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

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

RE: [sqlite] sqlite and borland c++ builder

[Joe Wilson]

--- Jonathan Kahn <[EMAIL PROTECTED]> wrote:
> I really appreciate your response.  What do you suggest I do?  Is there
> something else I need to include aside from sqlite3.lib?  I am willing to
> try anything.

I only use GNU C++, so I can't help you with .lib files.

I'd suggest to compile sqlite3.c with a C compiler or change sqlite3.c to 
include this:

 #ifdef __cplusplus
 extern "C" {
 #endif

 ... contents of sqlite3.c ...

 #ifdef __cplusplus
 }
 #endif

sqlite3.h has the correct __cplusplus extern wrapper.

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

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

Re: [sqlite] Curious performance issue with large number of inserts

[Joe Wilson]

> create table filemap(id integer primary key,
>uid integer, gid integer, mtime integer,
>vol integer,
>path varchar(1024));
> 
> It has no indices built yet.
> 
> I'm adding quite a lot of records to it using a perl script which  
> generates SQL like this:
> 
> begin;
>   insert into filemap values(null, 1, 1, , 0, "/path/to/file");
>   ...  more like this ...
> commit;
> ... repeat above ...
> 
> The uid, gid and mtime fields vary obviously, but there are very many  
> paths for each uid/gid pair.  The idea is that I need to be able to  
> say `show me all the files owned by UID x on volume y?', and we have  
> enough data that awk can't hack it.
> 
> before doing this I've done a
> 
> pragma synchronous=off;
> 
> All this is just being piped into sqlite3 (I know I should use the  
> proper interface, but it's a very quick & dirty hack).
> 
> I have about 16,000,000 records.  When adding them it goes really  
> quickly for about the first 1,000,000 (using the big transaction  
> trick made it much faster, as did the synchronous=off thing).  But  
> then it slows down dramatically, perhaps by a factor of 100 or 1000  
> or something.  I've actually left it running, but I'm not convinced  
> it will have done all 16 million by Monday.
> 
> I have not looked at what the program is doing in the sense of system  
> calls or any more detailed profiling.  It is clear that disk activity  
> falls right off when it becomes slow.
> 
> Am I doing anything obviously stupid here?  I suspect I must be.

The batch insert you describe ought to be pretty fast since you're only
appending data.

This is a guess - instead of inserting NULL for the INTEGER PRIMARY KEY 
column try assigning an increasing number for each new row.
This would avoid an OP_NewRowid per insert, which I would not think to
be slow, but it's worth trying.

You might also experiment with the sqlite3 import facility which should be
slightly faster.

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

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

Re: [sqlite] Curious performance issue with large number of inserts

[Joe Wilson]

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> > create table filemap(id integer primary key,
> >uid integer, gid integer, mtime integer,
> >vol integer,
> >path varchar(1024));
> > 
> > It has no indices built yet.
> > 
> > I'm adding quite a lot of records to it using a perl script which  
> > generates SQL like this:
> > 
> > begin;
> >   insert into filemap values(null, 1, 1, , 0, "/path/to/file");
> >   ...  more like this ...
> > commit;
> > ... repeat above ...
> > 
> > The uid, gid and mtime fields vary obviously, but there are very many  
> > paths for each uid/gid pair.  The idea is that I need to be able to  
> > say `show me all the files owned by UID x on volume y?', and we have  
> > enough data that awk can't hack it.
> > 
> > before doing this I've done a
> > 
> > pragma synchronous=off;
> > 
> > All this is just being piped into sqlite3 (I know I should use the  
> > proper interface, but it's a very quick & dirty hack).
> > 
> > I have about 16,000,000 records.  When adding them it goes really  
> > quickly for about the first 1,000,000 (using the big transaction  
> > trick made it much faster, as did the synchronous=off thing).  But  
> > then it slows down dramatically, perhaps by a factor of 100 or 1000  
> > or something.  I've actually left it running, but I'm not convinced  
> > it will have done all 16 million by Monday.
> > 
> > I have not looked at what the program is doing in the sense of system  
> > calls or any more detailed profiling.  It is clear that disk activity  
> > falls right off when it becomes slow.
> > 
> > Am I doing anything obviously stupid here?  I suspect I must be.
> 
> The batch insert you describe ought to be pretty fast since you're only
> appending data.
> 
> This is a guess - instead of inserting NULL for the INTEGER PRIMARY KEY 
> column try assigning an increasing number for each new row.
> This would avoid an OP_NewRowid per insert, which I would not think to
> be slow, but it's worth trying.

Ignore the idea above - an insert of NULL or an incrementing integer for the
INTEGER PRIMARY KEY yields the same timings.

I can't reproduce your problem. I can insert 16M records into your table
schema in 25 minutes on a 5 year old Windows machine. The sqlite3 process
had peak RAM usage of less than 20M.

If you had additional indexes on the table prior to insert (other than the 
INTEGER PRIMARY KEY), that could explain the slow results you are seeing.


$ cat ins.pl

print "
pragma page_size=8192;
pragma temp_store=memory;
pragma synchronous=off;
create table filemap(id integer primary key, uid integer, gid integer,
 mtime integer, vol integer, path varchar(1024));
begin;
";
for (my $i = 1; $i <= 1600; ++$i) {
  if ($i % 1000 == 0) { print "commit;\nbegin;\n"; }
  print "insert into filemap values(null,1,1,,0,'/path/to/file');\n";
}
print "commit;\n";

$ rm -f ins.db ; perl ins.pl | time ./sqlite3 ins.db
1389.89user 61.98system 25:13.57elapsed 95%CPU (0avgtext+0avgdata 
218880maxresident)k
0inputs+0outputs (4938major+0minor)pagefaults 0swaps

$ ls -l ins.db
-rw-r--r-- 1 User Nobody 512417792 May  5 10:47 ins.db

$ ./sqlite3 ins.db "select count(*) from filemap"
1600


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

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

Re: [sqlite] Curious performance issue with large number of inserts

[Joe Wilson]

--- Tim Bradshaw <[EMAIL PROTECTED]> wrote:
> > I can't reproduce your problem. I can insert 16M records into your  
> > table
> > schema in 25 minutes on a 5 year old Windows machine. The sqlite3  
> > process
> > had peak RAM usage of less than 20M.
> 
> Rats, I suspect it must be some compiler/architecture specific thing.  

I run GCC-compiled sqlite3 on sparc/solaris without such performance issues.

> I can rebuild it for x86, since I don't really care where it runs. It  
> definitely isn't running out of memory though (at least, not unless  
> it's throttling itself somehow, the machine has loads  free when it  
> gets sick).

What timings do you get when you run the perl script in my last email?

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

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

Re: [sqlite] Limiting the size of a database?

[Joe Wilson]

--- Ron Stevens <[EMAIL PROTECTED]> wrote:
> Is it possible to tell SQLite to limit the size that a database may
> grow to? It would be useful for storage constrained applications.

This is a tricky problem.

What would you have the database do if an insert failed upon reaching 
the limit?

What about the space for the journal files?

You could change the I/O subsystem's seek and write calls to have them
fail upon exceeding a threshold, but I'm not certain if that's useful.

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

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

Re: [sqlite] Limiting the size of a database?

[Joe Wilson]

I wrote too soon:

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

+ /*
+ ** Maximum number of pages in one database file.
+ */
+ #ifndef SQLITE_MAX_PAGE_COUNT
+ # define SQLITE_MAX_PAGE_COUNT 1073741823
+ #endif

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- Ron Stevens <[EMAIL PROTECTED]> wrote:
> > Is it possible to tell SQLite to limit the size that a database may
> > grow to? It would be useful for storage constrained applications.
> 
> This is a tricky problem.
> 
> What would you have the database do if an insert failed upon reaching 
> the limit?
> 
> What about the space for the journal files?
> 
> You could change the I/O subsystem's seek and write calls to have them
> fail upon exceeding a threshold, but I'm not certain if that's useful.


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

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

Re: [sqlite] Limiting the size of a database?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > I wrote too soon:
> > 
> >  http://www.sqlite.org/cvstrac/chngview?cn=3941
> > 
> > + /*
> > + ** Maximum number of pages in one database file.
> > + */
> > + #ifndef SQLITE_MAX_PAGE_COUNT
> > + # define SQLITE_MAX_PAGE_COUNT 1073741823
> > + #endif
> > 
> 
> This #define doesn't do anything yet.  But check back
> in a few days and it might.

What effect would this have on transaction/journal file 
sizes, if any?

What currently happens if SQLite runs out of real disk space?
An error followed by a clean rollback?

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

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

[sqlite] Check-ins [3987,3988] : Fix an obscure b-tree bug that applied to transient trees used for IN(...) expressions.

[Joe Wilson]

Does anyone know if this bug was the result of a recent btree optimization, 
or was it a longstanding issue?


   
Ready
 for the edge of your seat? 
Check out tonight's top picks on Yahoo! TV. 
http://tv.yahoo.com/

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

Re: [sqlite] Different VDBE opcodes for the same query

[Joe Wilson]

> The DB schema is:
> CREATE TABLE xxx(a TEXT, b TEXT, c TEXT);
> CREATE INDEX idx on xxx(b);
> 
> The command
>  explain select rowid, a, b, c from xxx where b in('qwerty') order by rowid;
> shows 58 opcodes.
> 
> The command
>  explain select rowid, a, b bbb, c from xxx where bbb in('qwerty') order by 
> rowid;
> shows 35 opcodes.
> 
> Could somebody explain - is it a bug or a feature? :)

Looks like an optimization oversight. It seems that the index is not used 
(i.e., slower) if the
SELECT references an alias of the index column in an IN clause.

This also produces 35 opcodes:

  explain select rowid, a, b, c from xxx where +b in('qwerty') order by rowid;

sqlite> explain query plan select rowid, a, b, c from xxx where b in('qwerty') 
order by rowid;
0|0|TABLE xxx WITH INDEX idx

sqlite> explain query plan select rowid, a, b bbb, c from xxx where bbb 
in('qwerty') order by
rowid;
0|0|TABLE xxx USING PRIMARY KEY ORDER BY

sqlite> explain query plan select rowid, a, b, c from xxx where +b in('qwerty') 
order by rowid;
0|0|TABLE xxx USING PRIMARY KEY ORDER BY



 

Don't get soaked.  Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather

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

Re: [sqlite] Different VDBE opcodes for the same query

[Joe Wilson]

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> > The DB schema is:
> > CREATE TABLE xxx(a TEXT, b TEXT, c TEXT);
> > CREATE INDEX idx on xxx(b);
> > 
> > The command
> >  explain select rowid, a, b, c from xxx where b in('qwerty') order by rowid;
> > shows 58 opcodes.
> > 
> > The command
> >  explain select rowid, a, b bbb, c from xxx where bbb in('qwerty') order by 
> > rowid;
> > shows 35 opcodes.
> > 
> > Could somebody explain - is it a bug or a feature? :)
> 
> Looks like an optimization oversight. It seems that the index is not used 
> (i.e., slower) if the
> SELECT references an alias of the index column in an IN clause.
> 
> This also produces 35 opcodes:
> 
>   explain select rowid, a, b, c from xxx where +b in('qwerty') order by rowid;
> 
> sqlite> explain query plan select rowid, a, b, c from xxx where b 
> in('qwerty') order by rowid;
> 0|0|TABLE xxx WITH INDEX idx
> 
> sqlite> explain query plan select rowid, a, b bbb, c from xxx where bbb 
> in('qwerty') order by
> rowid;
> 0|0|TABLE xxx USING PRIMARY KEY ORDER BY
> 
> sqlite> explain query plan select rowid, a, b, c from xxx where +b 
> in('qwerty') order by rowid;
> 0|0|TABLE xxx USING PRIMARY KEY ORDER BY

If there's an alias, the INDEX is not used for the equals form either:

sqlite> explain query plan select rowid, a, b bbb, c from xxx where bbb = 
'qwerty' order by rowid;
0|0|TABLE xxx USING PRIMARY KEY ORDER BY

sqlite> explain query plan select rowid, a, b, c from xxx where b = 'qwerty' 
order by rowid;
0|0|TABLE xxx WITH INDEX idx ORDER BY



 

Bored stiff? Loosen up... 
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

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

[sqlite] NameContext.nRef question

[Joe Wilson]

Is the sole purpose of NameContext.nRef to see whether you're dealing 
with a correlated subquery?



 

We won't tell. Get more on shows you hate to love 
(and love to hate): Yahoo! TV's Guilty Pleasures list.
http://tv.yahoo.com/collections/265 

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

Re: [sqlite] WG: indexing large databases

[Joe Wilson]

--- Juri Wichanow <[EMAIL PROTECTED]> wrote:
> > In my database of 30 000 000 records. 
> > At " Pragma cache_size = 100 " indexation lasts > 12 hours, 
> > at  " Pragma cache_size = 2000 " - 45 minutes. 

It would be interesting if you ran a profiler such as gprof during 
each run to see where the time is spent and post the results.


   
Take
 the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

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

Re: [sqlite] Different VDBE opcodes for the same query (part 2)

[Joe Wilson]

--- "Sergey M. Brytsko" <[EMAIL PROTECTED]> wrote:

> Hi All!
> 
> The DB schema is:
> CREATE TABLE xxx(a TEXT, b INTEGER, c TEXT);
> CREATE INDEX idx on xxx(b);
> 
> explain query plan select rowid, a, b, c from xxx where b > 1 order by 
> rowid;
> 0|0|TABLE xxx USING PRIMARY KEY ORDER BY
> The index is not used?!

sqlite> explain query plan select rowid, a, b, c from xxx where b > 1;  
0|0|TABLE xxx WITH INDEX idx

sqlite> explain query plan select rowid, a, b, c from xxx where b > 1 order 
by +rowid;
0|0|TABLE xxx WITH INDEX idx



   
Get
 the Yahoo! toolbar and be alerted to new email wherever you're surfing.
http://new.toolbar.yahoo.com/toolbar/features/mail/index.php

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

Re: [sqlite] replace function?

[Joe Wilson]

No error when run with most recent SQLite.
replace() may not have existed in 3.3.7.

--- Jim Dodgen <[EMAIL PROTECTED]> wrote:
> I get an error in version 3.3.7 when using the replace function as defined 
> here:
> 
> http://sqlite.org/lang_expr.html
> 
> "replace(X,Y,Z)   Return a string formed by substituting string Z for 
> every occurrance of string Y in string X. The BINARY collating sequence is 
> used 
> for comparison"
> 
> --- test case ---
> 
> create temp table temp_x (a,b);
> insert into temp_x values ("foo/bar",1);
> select a,b from temp_x where replace(a,"/","-") <> "foo-bar";
> 
> --- error message ---
> 
> error: 1 - no such function: replace(1) at dbdimp.c line 271 select a,b from 
> temp_x where replace(a,"/","-") <> "foo-bar"




   
Luggage?
 GPS? Comic books? 
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail=graduation+gifts=bz

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

[sqlite] PATCH: updated compound query WHERE clause optimization

[Joe Wilson]

This updated patch greatly improves query times against compound 
SELECT statements (i.e., UNIONs) when the parent SELECT has a WHERE 
clause.

For example, this query:

 SELECT * FROM (
   SELECT a,b FROM t1 
UNION ALL 
   SELECT x,y FROM t2
 ) WHERE a>b;

will be transformed into the more speed/memory efficient form:

 SELECT * FROM (
   SELECT a,b FROM t1 WHERE a>b
UNION ALL
   SELECT x,y FROM t2 WHERE x>y
 );

It is a particularly useful optimization when a VIEW containing 
one or more UNIONs is queried. Queries of this type can run 5 
times faster and use a fraction of the memory to process it.

See also: http://www.sqlite.org/cvstrac/tktview?tn=1924

This patch excludes the integer constant folding stuff in the previous
patch in an attempt to simplify it and get it into the SQLite tree.

"make test" runs without regressions, but more testing is welcome.

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


   
Get
 the free Yahoo! toolbar and rest assured with the added security of spyware 
protection.
http://new.toolbar.yahoo.com/toolbar/features/norton/index.phpIndex: src/expr.c
===
RCS file: /sqlite/sqlite/src/expr.c,v
retrieving revision 1.294
diff -u -3 -p -r1.294 expr.c
--- src/expr.c  15 May 2007 07:00:34 -  1.294
+++ src/expr.c  16 May 2007 19:27:15 -
@@ -604,6 +604,7 @@ Select *sqlite3SelectDup(Select *p){
   pNew->isAgg = p->isAgg;
   pNew->usesEphm = 0;
   pNew->disallowOrderBy = 0;
+  pNew->fOptPass = 0;
   pNew->pRightmost = 0;
   pNew->addrOpenEphm[0] = -1;
   pNew->addrOpenEphm[1] = -1;
Index: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.348
diff -u -3 -p -r1.348 select.c
--- src/select.c14 May 2007 16:50:49 -  1.348
+++ src/select.c16 May 2007 19:27:15 -
@@ -73,6 +73,7 @@ Select *sqlite3SelectNew(
   pNew->pOffset = pOffset;
   pNew->iLimit = -1;
   pNew->iOffset = -1;
+  pNew->fOptPass = 0;
   pNew->addrOpenEphm[0] = -1;
   pNew->addrOpenEphm[1] = -1;
   pNew->addrOpenEphm[2] = -1;
@@ -1397,7 +1398,7 @@ static int matchOrderbyToColumn(
   Parse *pParse,  /* A place to leave error messages */
   Select *pSelect,/* Match to result columns of this SELECT */
   ExprList *pOrderBy, /* The ORDER BY values to match against columns */
-  int iTable, /* Insert this value in iTable */
+  int *iTable,/* Insert this value in iTable */
   int mustComplete/* If TRUE all ORDER BYs must match */
 ){
   int nErr = 0;
@@ -1423,6 +1424,10 @@ static int matchOrderbyToColumn(
 int iCol = -1;
 char *zLabel;
 
+if( pE->op==TK_COLUMN ) {
+  *iTable = pE->iTable;
+  continue;
+}
 if( pOrderBy->a[i].done ) continue;
 if( sqlite3ExprIsInteger(pE, ) ){
   if( iCol<=0 || iCol>pEList->nExpr ){
@@ -1456,7 +1461,7 @@ static int matchOrderbyToColumn(
 if( iCol>=0 ){
   pE->op = TK_COLUMN;
   pE->iColumn = iCol;
-  pE->iTable = iTable;
+  pE->iTable = *iTable;
   pE->iAgg = -1;
   pOrderBy->a[i].done = 1;
 }else if( mustComplete ){
@@ -1731,7 +1736,7 @@ static int multiSelect(
 ** intermediate results.
 */
 unionTab = pParse->nTab++;
-if( pOrderBy && matchOrderbyToColumn(pParse, p, pOrderBy, unionTab,1) 
){
+if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,,1) ){
   rc = 1;
   goto multi_select_end;
 }
@@ -1825,7 +1830,7 @@ static int multiSelect(
   */
   tab1 = pParse->nTab++;
   tab2 = pParse->nTab++;
-  if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,tab1,1) ){
+  if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,,1) ){
 rc = 1;
 goto multi_select_end;
   }
@@ -2643,11 +2648,11 @@ int sqlite3SelectResolve(
  sqlite3ExprResolveNames(, p->pHaving) ){
 return SQLITE_ERROR;
   }
-  if( p->pPrior==0 ){
-if( processOrderGroupBy(, p->pOrderBy, "ORDER") ||
-processOrderGroupBy(, pGroupBy, "GROUP") ){
-  return SQLITE_ERROR;
-}
+  if( p->pPrior==0 && processOrderGroupBy(, p->pOrderBy, "ORDER") ){
+return SQLITE_ERROR;
+  }
+  if( processOrderGroupBy(, pGroupBy, "GROUP") ){
+return SQLITE_ERROR;
   }
 
   /* Make sure the GROUP BY clause does not contain aggregate functions.
@@ -2774,6 +2779,148 @@ static void updateAccumulator(Parse *pPa
   pAggInfo->directMode = 0;
 }
 
+#ifdef SQLITE_OMIT_COMPOUND_SELECT
+#define SQLITE_OMIT_UNION_WHERE_OPTIMIZATION

Re: [sqlite] PATCH: updated compound query WHERE clause optimization

[Joe Wilson]

Improved patch against latest CVS with more comments and new test 
case attached. No regressions with make test.

> This updated patch greatly improves query times against compound 
> SELECT statements (i.e., UNIONs) when the parent SELECT has a WHERE 
> clause.


 

Expecting? Get great news right away with email Auto-Check. 
Try the Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html Index: src/expr.c
===
RCS file: /sqlite/sqlite/src/expr.c,v
retrieving revision 1.294
diff -u -3 -p -r1.294 expr.c
--- src/expr.c  15 May 2007 07:00:34 -  1.294
+++ src/expr.c  19 May 2007 15:19:50 -
@@ -604,6 +604,7 @@ Select *sqlite3SelectDup(Select *p){
   pNew->isAgg = p->isAgg;
   pNew->usesEphm = 0;
   pNew->disallowOrderBy = 0;
+  pNew->fOptPass = 0;
   pNew->pRightmost = 0;
   pNew->addrOpenEphm[0] = -1;
   pNew->addrOpenEphm[1] = -1;
Index: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.348
diff -u -3 -p -r1.348 select.c
--- src/select.c14 May 2007 16:50:49 -  1.348
+++ src/select.c19 May 2007 15:19:50 -
@@ -73,6 +73,7 @@ Select *sqlite3SelectNew(
   pNew->pOffset = pOffset;
   pNew->iLimit = -1;
   pNew->iOffset = -1;
+  pNew->fOptPass = 0;
   pNew->addrOpenEphm[0] = -1;
   pNew->addrOpenEphm[1] = -1;
   pNew->addrOpenEphm[2] = -1;
@@ -1397,7 +1398,7 @@ static int matchOrderbyToColumn(
   Parse *pParse,  /* A place to leave error messages */
   Select *pSelect,/* Match to result columns of this SELECT */
   ExprList *pOrderBy, /* The ORDER BY values to match against columns */
-  int iTable, /* Insert this value in iTable */
+  int *iTable,/* Insert this value in iTable */
   int mustComplete/* If TRUE all ORDER BYs must match */
 ){
   int nErr = 0;
@@ -1423,6 +1424,10 @@ static int matchOrderbyToColumn(
 int iCol = -1;
 char *zLabel;
 
+if( pE->op==TK_COLUMN ) {
+  *iTable = pE->iTable;
+  continue;
+}
 if( pOrderBy->a[i].done ) continue;
 if( sqlite3ExprIsInteger(pE, ) ){
   if( iCol<=0 || iCol>pEList->nExpr ){
@@ -1456,7 +1461,7 @@ static int matchOrderbyToColumn(
 if( iCol>=0 ){
   pE->op = TK_COLUMN;
   pE->iColumn = iCol;
-  pE->iTable = iTable;
+  pE->iTable = *iTable;
   pE->iAgg = -1;
   pOrderBy->a[i].done = 1;
 }else if( mustComplete ){
@@ -1731,7 +1736,7 @@ static int multiSelect(
 ** intermediate results.
 */
 unionTab = pParse->nTab++;
-if( pOrderBy && matchOrderbyToColumn(pParse, p, pOrderBy, unionTab,1) 
){
+if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,,1) ){
   rc = 1;
   goto multi_select_end;
 }
@@ -1825,7 +1830,7 @@ static int multiSelect(
   */
   tab1 = pParse->nTab++;
   tab2 = pParse->nTab++;
-  if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,tab1,1) ){
+  if( pOrderBy && matchOrderbyToColumn(pParse,p,pOrderBy,,1) ){
 rc = 1;
 goto multi_select_end;
   }
@@ -2643,11 +2648,11 @@ int sqlite3SelectResolve(
  sqlite3ExprResolveNames(, p->pHaving) ){
 return SQLITE_ERROR;
   }
-  if( p->pPrior==0 ){
-if( processOrderGroupBy(, p->pOrderBy, "ORDER") ||
-processOrderGroupBy(, pGroupBy, "GROUP") ){
-  return SQLITE_ERROR;
-}
+  if( p->pPrior==0 && processOrderGroupBy(, p->pOrderBy, "ORDER") ){
+return SQLITE_ERROR;
+  }
+  if( processOrderGroupBy(, pGroupBy, "GROUP") ){
+return SQLITE_ERROR;
   }
 
   /* Make sure the GROUP BY clause does not contain aggregate functions.
@@ -2774,6 +2779,167 @@ static void updateAccumulator(Parse *pPa
   pAggInfo->directMode = 0;
 }
 
+#ifdef SQLITE_OMIT_COMPOUND_SELECT
+#define SQLITE_OMIT_UNION_WHERE_OPTIMIZATION
+#endif
+
+#ifndef SQLITE_OMIT_UNION_WHERE_OPTIMIZATION
+
+static int optSelect(Parse*, Select*);
+static int optExprList(Parse*, ExprList*);
+
+static int optExpr(Parse* pParse, Expr* p) {
+  if (p) {
+if( optExpr(pParse, p->pLeft)
+|| optExpr(pParse, p->pRight)
+|| optExprList(pParse, p->pList)
+|| optSelect(pParse, p->pSelect) ){
+  return SQLITE_ERROR;
+}
+  }
+  return SQLITE_OK;
+}
+
+static int optSrcList(Parse* pParse, SrcList* p) {
+  if (p) {
+int i;
+for (i = 0; i < p->nSrc; ++i) {
+  if( optSelect(pParse, p->a[i].pSelect) ){
+return SQLITE_ERROR;
+  }
+}
+  }
+  return SQLITE_OK;
+}
+
+static int optExprList(Parse* pParse, ExprList* p) {
+  if (p) {
+int i;
+for (i = 0; i < p->nExpr; ++i) {
+  if( optExpr(pParse, p->a[i].pExpr) ){
+return SQLITE_ERROR;
+  }
+}
+  }
+  return SQLITE_OK;
+}
+
+/* This function will optimize the WHERE clauses of a single compound SELECT
+** statement by 

[sqlite] R-Trees and SQLite

[Joe Wilson]

In a previous post drh mentioned:

  You need an R-Tree index to do something like this.  The
  public-domain version of SQLite only supports B-Tree indices.

  (http://www.mail-archive.com/sqlite-users%40sqlite.org/msg24643.html)

Does this imply that there exists a commercial version of SQLite with
R-Tree indexing?

I did not see it mentioned here:

  http://www.hwaci.com/sw/sqlite/prosupport.html



 

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]
-

Re: [sqlite] SQL error: disk I/O error

[Joe Wilson]

If you're using Linux, try:

echo "create table t1(a);" |strace ./sqlite3 my.db 2>&1 |less

and examine the output. See where it differs from the successful
tmpfs run.

Newer versions of sqlite3 may have better IO error messages.

> I'm running the command line tool to create sqlite3 db.
> My directory permissions are  drwxrwxrwt.
> I'm getting the following error.
> 
> > ./sqlite3 newdb
> SQLite version 3.3.12
> Enter ".help" for instructions
> sqlite> create table tbl1(one varchar(10), two smallint);
> SQL error: disk I/O error
> 
> If I try the same thing in  a tmpfs, it works.



   
Looking
 for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/

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

Re: [sqlite] bizarre query problem

[Joe Wilson]

Nothing you've mentioned is out of the ordinary. I would expect the
same behavior on both platforms.

Can you post the complete schema, and the exact query that exhibits the 
problem? (And perhaps a couple of insert statements into the objects table).
Without this I don't think anyone can recreate the problem.

--- Brett Keating <[EMAIL PROTECTED]> wrote:
> I have a bizarre problem. Here is an example of something I tried in
> sqlite3 3.3.8:
> 
> sqlite> select genre,filename from objects where media_type=1;
> query abbreviated...
> Msica independiente|0056_People Get Ready1_test1.wma
> POP|0057_The Mighty Ship1_test1.wma
> POP|0058_The Mighty Quinn1_test1.wma
> query abbreviated...
> sqlite> select genre,filename from objects where genre='POP';
> sqlite>
> 
> So basically, no results are returned from the second query although
> clearly there are items in the list with genre='POP'. 
> 
> This problem only happens on Linux. On Windows, the query returns the
> results as expected... Which makes it yet more bizarre.



   
Got
 a little couch potato? 
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz
 

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

Re: [sqlite] bizarre query problem

[Joe Wilson]

Just for kicks, what happens on both platforms when you issue:

  select genre, length(genre), hex(genre), filename 
  from objects where media_type=1;

as well as:

  select count(*) from objects where genre LIKE '%POP%';

> I have a bizarre problem. Here is an example of something I tried in
> sqlite3 3.3.8:
> 
> sqlite> select genre,filename from objects where media_type=1;
> query abbreviated...
> Msica independiente|0056_People Get Ready1_test1.wma
> POP|0057_The Mighty Ship1_test1.wma
> POP|0058_The Mighty Quinn1_test1.wma
> query abbreviated...
> sqlite> select genre,filename from objects where genre='POP';
> sqlite>
> 
> So basically, no results are returned from the second query although
> clearly there are items in the list with genre='POP'. 
> 
> This problem only happens on Linux. On Windows, the query returns the
> results as expected... Which makes it yet more bizarre.



   
Get
 the Yahoo! toolbar and be alerted to new email wherever you're surfing.
http://new.toolbar.yahoo.com/toolbar/features/mail/index.php

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

Re: [sqlite] SQL error: disk I/O error

[Joe Wilson]

What is the OS you're using and what kind of media is it?
Hard drive or USB key or ???

--- Shilpa Sheoran <[EMAIL PROTECTED]> wrote:
> It seems that  rc = fsync(fd); is failing in function
>  static int full_fsync(int fd, int fullSync, int dataOnly) in file os_unix.c
> {
> #else /* if !defined(F_FULLSYNC) */
>   if( dataOnly ){
> rc = fdatasync(fd);
>   }else{
> 
> //*this call is failing
>rc = fsync(fd);
>   }
> 
> }
> 
> using -DSQLITE_NO_SYNC in the Makefile works
> What is the way out for this problem?
> What happens if we use this option -DSQLITE_NO_SYNC ?



   
Take
 the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

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

Re: [sqlite] About a Vista problem

[Joe Wilson]

--- "Tian-Jian \"Barabbas\" [EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> Hi Millan,
> 
> I encountered the same problem, the record is on
> 
> http://b6s.blogspot.com/2007/04/previous-version-ghost-on-vista-can-be.html
> 
> and the issue was sent to
> 
> http://www.sqlite.org/cvstrac/tktview?tn=2178
> 
> To my best knowledge, there's something funny in the
> dynamic library of file I/O, since my SQLite usage is
> actually a DLL, which may attach to ANY applications.
> Some cross-platform apps, such as Firefox/Thunderbird,
> met this problem; some other Win32 native apps not.
> 
> However, I have no solution now...
> 
> Sincerely,
> /Mike "b6s" Jiang/

This Windows Vista sounds like a joy to program for.

Can you programatically disable Shadow Copy in Windows Vista?
Or must the user do it manually for each folder/application?


   
Yahoo!
 oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

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

Re: [sqlite] Is .dump the definitive backup method for sqlite?

[Joe Wilson]

--- Nigel Metheringham <[EMAIL PROTECTED]> wrote:
> I recently experimented with putting binary data into SQLite table  
> rows - I declared the column holding the binary data as a BLOB (not  
> that it makes a ton of difference for SQLite).
> 
> This worked very well (using perl DBIx::Class/DBI/DBD::SQLite as the  
> interface into SQLite).
> 
> However a dump and rebuild of the database - ie
>sqlite mydatabase.db .dump > out.sql
>sqlite newdatabase.db  
> does not reconstruct the database correctly - there are the right  
> number of rows, no errors are thrown during the rebuild, but some  
> rows have differing data in them.
> 
> I presume that this should not happen, or is .dump & rebuild only  
> valid for some subset of data?
> 
>   Nigel.

What version of sqlite are you using (2.x, 3.x)? One 3.x version in 
particular had a bug related to not dumping indexes and triggers.
I've never used sqlite 2.x, so I can't comment on that.

Could you put together a few line schema and a couple of insert 
statements that when dumped exhibit the problem you mention?


   
Ready
 for the edge of your seat? 
Check out tonight's top picks on Yahoo! TV. 
http://tv.yahoo.com/

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

Re: [sqlite] Monitor Queries

[Joe Wilson]

> I've a "black-box" (Linux) program - so no source available - using a
> sqlite database. Is there any way I can monitor/log the queries made to
> the database?

If it's dynamically linked, just replace libsqlite3.so with your own.
If it is statically linked and has -g symbols, use gdb.
If it's stripped, x86 assembler is not that hard to learn.



 

No need to miss a message. Get email on-the-go 
with Yahoo! Mail for Mobile. Get started.
http://mobile.yahoo.com/mail 

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

Re: [sqlite] Monitor Queries

[Joe Wilson]

--- Lloyd <[EMAIL PROTECTED]> wrote:
> On Wed, 2007-05-23 at 07:21 -0700, Joe Wilson wrote:
> > > I've a "black-box" (Linux) program - so no source available - using
> > a
> > > sqlite database. Is there any way I can monitor/log the queries made
> > to
> > > the database?
> > 
> > If it's dynamically linked, just replace libsqlite3.so with your own.
> 
> How should be my own libsqlite3.so looking like. Can you explain the way
> I should make the library? I would like to learn this technique :)

You have all the sqlite source code and documentation.
Take the time to read and understand it.
Add printf's and change it any way you like.


 

Looking for earth-friendly autos? 
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/

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

Re: [sqlite] Is .dump the definitive backup method for sqlite?

[Joe Wilson]

--- Nigel Metheringham <[EMAIL PROTECTED]> wrote:
> I'll see if I can reproduce the result with standard INSERT statements

Try this:

  sqlite3 orig.db vacuum -- might be necessary to preserve row order
  sqlite3 orig.db ".dump BrokenTable" | tee orig.sql | sqlite3 new.db
  sqlite3 new.db  ".dump BrokenTable" | diff -u0 orig.sql -

If it does not work, just load orig.db into sqlite3, ATTACH new.db and
then write a SELECT that returns the new.db rows that do not exactly match 
the original table using ".mode insert".

  sqlite> .mode insert
  sqlite> select * from ...



  
Fussy?
 Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user panel and lay 
it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 


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

RE: [sqlite] bizarre query problem

[Joe Wilson]

--- Brett Keating <[EMAIL PROTECTED]> wrote:
> Msica independiente|text|19|0056_People Get Ready1_test1.wma
> POP|text|3|0057_The Mighty Ship1_test1.wma
> POP|text|3|0058_The Mighty Quinn1_test1.wma
> 
> Anyway, it turns out the problem was caused by creating an index on the
> genre field. If I don't create an index, it works normally for both
> OSes.

Clearly there's a problem. Can you try testing with the latest version 
of sqlite? Assuming it is not already fixed, consider making a small test 
case and filing a ticket: http://www.sqlite.org/cvstrac/tktnew



   
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]
-

RE: [sqlite] bizarre query problem

[Joe Wilson]

--- Brett Keating <[EMAIL PROTECTED]> wrote:
> Actually this wasn't the issue after all... Indices have nothing to do
> with it.
> 
> The genre was being inserted from two different sources. It is a UTF-16
> string, and in one case it was being inserted with a null terminator,
> and in another case it was not. Since I used "sqlite3_bind_text16" and
> specified a length that included the null terminator, it was stored in
> the database with that null terminator. 
> 
> Unfortunately when I do this, the string that I get back from the
> database is of length -1 compared to what I inserted. So for example if
> genre is "Rock" and I inserted "Rock0" where 0 is null terminator, I get
> "Rock" back and not "Rock0."
> 
> Note below that POP is reported as 3 characters long, but was inserted
> as 4 with a null terminator.  
> 
> Interestingly enough, sqlite3 will give me two copies of POP when I ask
> for unique genres, if I insert a value as "POP" and another value as
> "POP0."
> 
> So in a sense this was merely user error but also an interesting
> idiosyncracy of the sqlite3 database.

If you had inserted the text with the null terminator as a blob, then 
sqlite would have reported it as follows:

 SQLite version 3.3.17
 Enter ".help" for instructions
 sqlite> create table t1(a text);
 sqlite> insert into t1 values(x'504F5000');
 sqlite> select * from t1;
 POP
 sqlite> select a, typeof(a), hex(a), length(a) from t1 where a LIKE '%POP';
 POP|blob|504F5000|4

But I guess sqlite has to take your word for it for UTF strings when
you supply a length that is wrong. The other option is that sqlite could
convert UTF strings with embedded nulls to blobs. Not sure what is the
better option.


   
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]
-

Re: [sqlite] Problem using loadable extensions

[Joe Wilson]

--- Alexander Smondyrev <[EMAIL PROTECTED]> wrote:
> I am trying to use loadable extensions in Sqlite and I've run into the
> following 2 problems:
> 
> 1) I've downloaded src for 3.3.17 Sqlite and build it, but the '.load'
> option does not seem to appear when I run the shell. I've used the default
> build mechanism with one minor change. I set the flag to disable tcl
> extensions. It is likely that I am missing some ./configure option, but I
> can't figure out what I need to do. Any advise would be greatly appreciated.

Comment out the line in Makefile.in (or the generated Makefile) to enable
loadable extensions:

  # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1

Then "./configure && make" as normal.

If you want the FTS1 and FTS2 modules "pre-loaded" in sqlite3, just apply
the attached patch to the latest version of the sqlite source tree, and
"./configure && make". The fts1 and fts2 modules will be statically linked
into sqlite3. No need to load the FTS modules at runtime.


 

Looking for earth-friendly autos? 
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/Index: Makefile.in
===
RCS file: /sqlite/sqlite/Makefile.in,v
retrieving revision 1.172
diff -u -3 -p -r1.172 Makefile.in
--- Makefile.in 17 May 2007 16:38:30 -  1.172
+++ Makefile.in 24 May 2007 20:35:40 -
@@ -117,6 +117,8 @@ NAWK = @AWK@
 # You should not have to change anything below this line
 ###
 TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1
+TCC += -DSQLITE_ENABLE_FTS1=1
+TCC += -DSQLITE_ENABLE_FTS2=1
 
 # Object files for the SQLite library.
 #
@@ -130,6 +132,12 @@ LIBOBJ = alter.lo analyze.lo attach.lo a
  vdbe.lo vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbefifo.lo vdbemem.lo \
  where.lo utf.lo legacy.lo vtab.lo
 
+# FTS1 (optional)
+LIBOBJ += fts1.lo fts1_hash.lo fts1_porter.lo fts1_tokenizer1.lo
+
+# FTS2 (optional)
+LIBOBJ += fts2.lo fts2_hash.lo fts2_porter.lo fts2_tokenizer1.lo
+
 # All of the source code files.
 #
 SRC = \
@@ -196,7 +204,14 @@ SRC += \
   $(TOP)/ext/fts1/fts1_hash.h \
   $(TOP)/ext/fts1/fts1_porter.c \
   $(TOP)/ext/fts1/fts1_tokenizer.h \
-  $(TOP)/ext/fts1/fts1_tokenizer1.c
+  $(TOP)/ext/fts1/fts1_tokenizer1.c \
+  $(TOP)/ext/fts2/fts2.c \
+  $(TOP)/ext/fts2/fts2.h \
+  $(TOP)/ext/fts2/fts2_hash.c \
+  $(TOP)/ext/fts2/fts2_hash.h \
+  $(TOP)/ext/fts2/fts2_porter.c \
+  $(TOP)/ext/fts2/fts2_tokenizer.h \
+  $(TOP)/ext/fts2/fts2_tokenizer1.c
 
 
 # Source code to the test files.
@@ -259,7 +274,10 @@ HDR = \
 HDR += \
   $(TOP)/ext/fts1/fts1.h \
   $(TOP)/ext/fts1/fts1_hash.h \
-  $(TOP)/ext/fts1/fts1_tokenizer.h
+  $(TOP)/ext/fts1/fts1_tokenizer.h \
+  $(TOP)/ext/fts2/fts2.h \
+  $(TOP)/ext/fts2/fts2_hash.h \
+  $(TOP)/ext/fts2/fts2_tokenizer.h
 
 # Header files used by the VDBE submodule
 #
@@ -482,6 +500,30 @@ vtab.lo:   $(TOP)/src/vtab.c $(VDBEHDR)
 where.lo:  $(TOP)/src/where.c $(HDR)
$(LTCOMPILE) -c $(TOP)/src/where.c
 
+fts1.lo:   $(TOP)/ext/fts1/fts1.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1.c
+
+fts1_hash.lo:  $(TOP)/ext/fts1/fts1_hash.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_hash.c
+
+fts1_porter.lo:$(TOP)/ext/fts1/fts1_porter.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_porter.c
+
+fts1_tokenizer1.lo:$(TOP)/ext/fts1/fts1_tokenizer1.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_tokenizer1.c
+
+fts2.lo:   $(TOP)/ext/fts2/fts2.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2.c
+
+fts2_hash.lo:  $(TOP)/ext/fts2/fts2_hash.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_hash.c
+
+fts2_porter.lo:$(TOP)/ext/fts2/fts2_porter.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_porter.c
+
+fts2_tokenizer1.lo:$(TOP)/ext/fts2/fts2_tokenizer1.c $(HDR)
+   $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_tokenizer1.c
+
 tclsqlite-shell.lo:$(TOP)/src/tclsqlite.c $(HDR)
$(LTCOMPILE) -DTCLSH=1 -o $@ -c $(TOP)/src/tclsqlite.c
 

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

Re: [sqlite] How can I get my query to run as fast as SQLiteSpy?

[Joe Wilson]

On Windows, timing sending the results to a file:

  sqlite3 your.db "SELECT * FROM trend_data" > foo.txt

On a 2.0 GHZ P4 I get 50,000 rows/second, which is not bad considering
it's a 5 year old machine and sqlite3 was not compiled with much 
optimization.

Any new machine should be twice as fast.

>   time sqlite3 your.db "SELECT * FROM trend_data" | wc -l
> 
> Run the command more than once, as the first timing is always slower.




   
You
 snooze, you lose. Get messages ASAP with AutoCheck
in the all-new Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_html.html

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

Re: [sqlite] Embedded SQL in C

[Joe Wilson]

--- Leif Jensen <[EMAIL PROTECTED]> wrote:
>In a larger project we are using PostgreSQL database and Embedded SQL 
> in C (using ECPG) in a server daemon. We would like to be able to have this 
> to work with 
> SQLite for a stand-alone application. The Embedded SQL in C standard is 
> as used by Oracle (and former Informix) and of course PostgreSQL.
> 
>Does anyone know of an Embedded SQL in C (pre-compiler/interface) 
> that will work with SQLite ?

I am not aware of such a pre-compiler for SQLite.

If your project is quite large (100,000+ lines of code) then porting 
PostgreSQL's embedded C compiler ECPG to SQLite may be a viable alternative.

 http://doxygen.postgresql.org/dir_45ac211730e8147f7f879781af2b8b2a.html

The main porting effort would be in the ecpglib/ directory, mapping all
PQ-prefixed postgres functions and the PG-prefixed data structures to their 
SQLite equivalents.

 http://doxygen.postgresql.org/dir_9bd0bfe8893f57601e39e3093fd54938.html

A possibly simpler approach may be to construct a "fake" libpq library for 
use by the PostgreSQL ECPG program that does all the mappings to/from sqlite3.
You'd only have to support the subset of libpq functions actually used 
by ECPG.

But even if you did such a port using either approach, be aware that the 
SQL dialects of Postgres and SQLite are not the same - you'd likely have 
to change some of the embedded SQL statements in your code.



   
Sick
 sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

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

Re: [sqlite] Embedded SQL in C

[Joe Wilson]

--- Leif Jensen <[EMAIL PROTECTED]> wrote:
> I thought about the "fake" library myself 
> even though our project is more like 20 - 30k lines, but I'm not sure 
> how bad it would be. 

May not be worth the hassle given the size of your project.

Since you're using C, consider rewriting your code using TrollTech's Qt 
library so you could hook up any database for which they've written a driver.
There may be other multi-database libraries for C/C++.



   
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]
-

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Joe Wilson]

> I am working at porting sqlite ( ver 3.3.8 ) on an embedded device with
> extremely low main memory.
> 
> I tried running select queries on the tables( with about 2k records each
> having about 5 strings) and they do well within 20kB of runtime heap
> usage.
> 
> But, when I try new insertions, the heap usage grows tremendously (about
> 70 kB at peak).

Perhaps preparing the statements (sqlite3_prepare) might decrease RAM 
use somewhat.

Can you post an example of your schema and these insert statements?



   
Choose
 the right car based on your needs.  Check out Yahoo! Autos new Car Finder tool.
http://autos.yahoo.com/carfinder/

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

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Joe Wilson]

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> > I am working at porting sqlite ( ver 3.3.8 ) on an embedded device with
> > extremely low main memory.
> > 
> > I tried running select queries on the tables( with about 2k records each
> > having about 5 strings) and they do well within 20kB of runtime heap
> > usage.
> > 
> > But, when I try new insertions, the heap usage grows tremendously (about
> > 70 kB at peak).
> 
> Perhaps preparing the statements (sqlite3_prepare) might decrease RAM 
> use somewhat.
> 
> Can you post an example of your schema and these insert statements?

Is your temp_store in memory or flash ram or other?

Another thought... are you performing a COMMIT after each INSERT?
(or at least every X inserts, where X<100)?
It might help to reduce memory (at the cost of reduced speed).



   
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]
-

RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Joe Wilson]

The default is auto-commit, so unless you've opened an explicit transaction
with BEGIN and do a number of inserts, the COMMIT suggestion is not useful
in reducing memory footprint.

(apologies in advance if this is obvious...)
SDRAM is the normal volatile RAM, right?
You know that temp_store is competing with sqlite for your RAM.
If you point your temp_store to "disk" to use non-volatile storage 
(disk or flash) then you'd have more RAM available for sqlite. 
Or do you lack such non-volatile storage?

You can run the tests on Windows by installing either Cygwin or
MinGW/MSYS, installing Tcl 8.4, running configure, and then running "make test".
You can change the generated Makefile to define various sqlite compile-time
flags for memory debugging. Just scan the ifdef's in the source code for
ideas what to enable. It's much easier to do this under Linux than under 
Windows, in my opinion.

--- Kalyani Tummala <[EMAIL PROTECTED]> wrote:
> My temp_store is SDRAM. Thanks for your suggestion of using COMMIT. I
> have not used it. Any other pointers?
> 
> Best Regards
> Kalyani
> 
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 29, 2007 10:27 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to restrict the peak heap usage during
> multiple inserts and updates?
> 
> --- Joe Wilson <[EMAIL PROTECTED]> wrote:
> > > I am working at porting sqlite ( ver 3.3.8 ) on an embedded device
> with
> > > extremely low main memory.
> > > 
> > > I tried running select queries on the tables( with about 2k records
> each
> > > having about 5 strings) and they do well within 20kB of runtime heap
> > > usage.
> > > 
> > > But, when I try new insertions, the heap usage grows tremendously
> (about
> > > 70 kB at peak).
> > 
> > Perhaps preparing the statements (sqlite3_prepare) might decrease RAM 
> > use somewhat.
> > 
> > Can you post an example of your schema and these insert statements?
> 
> Is your temp_store in memory or flash ram or other?
> 
> Another thought... are you performing a COMMIT after each INSERT?
> (or at least every X inserts, where X<100)?
> It might help to reduce memory (at the cost of reduced speed).



   
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]
-

RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Joe Wilson]

I think I know what's going on. 

When you insert new rows in the presence of indexes then sqlite must
touch a lot of pages in each trascation to satisfy the rebuilding of
the index(es). These pages are built up in the transaction log which is 
stored in temp_store, which happens to be memory in your case.
SQLite's transaction log cannot be disabled.

If you drop the indexes, then your inserts will touch fewer pages resulting
in a smaller transaction log, and less temp_store use. But this is not
really a solution. To avoid this problem, you cannot use memory for 
temp_store.

--- Kalyani Tummala <[EMAIL PROTECTED]> wrote:
> I am planning to use sqlite as a database for storing and retrieving
> media data of about 5-10k records in a device whose main memory is
> extremely small. A sequence of insert statements increasing the heap
> usage to nearly 70K(almost saturating point) which is crashing my
> application. I want to restrict this to 30K. 
> 
> I tried closing database and reopen after some inserts but of no use. 
> 
> I have observed that, when I open the database with about 1K to 2K
> records in it, inserts and updates take more heap and also gradually
> increase than a a database with less than 1k records in it. 
> 
> My objective is to reduce the peak heap usage during inserts, updates
> and also deletes with little or no performance degradation.



   
Sick
 sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

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

Re: [sqlite] Problem using loadable extensions

[Joe Wilson]

> I'd like to figure out why the example
> with half function which was provided by sqlite team can't be loaded as a
> shared library.

Yeah, it's poorly documented. I'll just put this in the wiki:

http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions=1180475067=1

How To Build a Loadable Extension Shared Library on Linux

0. untar latest sqlite3 source code in a new directory

1. cd to the newly untarred sqlite directory

2. Comment out the line in Makefile.in to enable loadable extensions:

 # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1

3. ./configure LIBS=-ldl && make sqlite3

4. export LD_LIBRARY_PATH="`pwd`:$LD_LIBRARY_PATH"

5. gcc -I`pwd` -shared src/test_loadext.c -o half.so

6. ./sqlite3

SQLite version 3.3.17
Enter ".help" for instructions
sqlite> .load half.so testloadext_init
sqlite> select half(7);
3.5



   
Yahoo!
 oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

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

RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Joe Wilson]

It may not be possible to get peak heap usage down to 30K, but here's 
some random ideas:

I imagine you've already tried defining SQLITE_OMIT_* for the features 
that you don't need.

Verify that your embedded OS has a space-efficient malloc implementation.

Try to find a realtime graphical heap allocation analyzer for C programs. 
I'm just guessing it exists for C, as there are several for Java.

> 2500 records already stored in the database, temp_store set to 0(file
> always), sqlite is taking 48K heap to open the database, 55K for first 5
> inserts and increases for every other insert and goes to 68K for next 5
> inserts. 

Based on these findings, perhaps btree recursion is using that extra 
memory on inserts on larger tables?

btree.c:

/*
** This routine redistributes Cells on pPage and up to NN*2 siblings
** of pPage so that all pages have about the same amount of free space.
** Usually NN siblings on either side of pPage is used in the balancing,
** though more siblings might come from one side if pPage is the first
** or last child of its parent.  If pPage has fewer than 2*NN siblings
** (something which can only happen if pPage is the root page or a
** child of root) then all available siblings participate in the balancing.
**
** The number of siblings of pPage might be increased or decreased by one or
** two in an effort to keep pages nearly full but not over full. The root page
** is special and is allowed to be nearly empty. If pPage is
** the root page, then the depth of the tree might be increased
** or decreased by one, as necessary, to keep the root page from being
** overfull or completely empty.
**
** Note that when this routine is called, some of the Cells on pPage
** might not actually be stored in pPage->aData[].  This can happen
** if the page is overfull.  Part of the job of this routine is to
** make sure all Cells for pPage once again fit in pPage->aData[].
**
** In the course of balancing the siblings of pPage, the parent of pPage
** might become overfull or underfull.  If that happens, then this routine
** is called recursively on the parent.
**
** If this routine fails for any reason, it might leave the database
** in a corrupted state.  So if this routine fails, the database should
** be rolled back.
*/
static int balance_nonroot(MemPage *pPage){

...

#ifndef SQLITE_OMIT_INTEGRITY_CHECK
/*
** Do various sanity checks on a single page of a tree.  Return
** the tree depth.  Root pages return 0.  Parents of root pages
** return 1, and so forth.
**
** These checks are done:
**
**  1.  Make sure that cells and freeblocks do not overlap
**  but combine to completely cover the page.
**  NO  2.  Make sure cell keys are in order.
**  NO  3.  Make sure no key is less than or equal to zLowerBound.
**  NO  4.  Make sure no key is greater than or equal to zUpperBound.
**  5.  Check the integrity of overflow pages.
**  6.  Recursively call checkTreePage on all children.
**  7.  Verify that the depth of all children is the same.
**  8.  Make sure this page is at least 33% full or else it is
**  the root of the tree.
*/
static int checkTreePage(


> With temp_store set to 3(memory always), it is reaching 78K. 
> 
> Increasing PAGE size increases STACK consumption, which is again a
> problem with little main memory. 
> 
> So, I think, as the database grows, the initial heap required by an
> insert or update statement grows. Can we have control over max heap
> usage by sqlite? 



 

Get your own web address.  
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

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

[sqlite] sqlite internal structs don't make use of C bitfields?

[Joe Wilson]

You could save a few bytes in some sqlite internal structs if you'd
use C bitfields for boolean flags:

For example:

struct MemPage {
  u8 isInit;   /* True if previously initialized. MUST BE FIRST! */
  u8 idxShift; /* True if Cell indices have changed */
  u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
  u8 intKey;   /* True if intkey flag is set */
  u8 leaf; /* True if leaf flag is set */
  u8 zeroData; /* True if table stores keys only */
  u8 leafData; /* True if tables stores data on leaves only */
  u8 hasData;  /* True if this page stores data */
  u8 hdrOffset;/* 100 for page 1.  0 otherwise */
  u8 childPtrSize; /* 0 if leaf==1.  4 if leaf==0 */
  u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
  u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
  u16 cellOffset;  /* Index in aData of first cell pointer */
  u16 idxParent;   /* Index in parent of this node */
  u16 nFree;   /* Number of free bytes on the page */
  u16 nCell;   /* Number of cells on this page, local and ovfl */
  struct _OvflCell {   /* Cells that will not fit on aData[] */
u8 *pCell;  /* Pointers to the body of the overflow cell */
u16 idx;/* Insert this cell before idx-th non-overflow cell */
  } aOvfl[5];
  BtShared *pBt;   /* Pointer back to BTree structure */
  u8 *aData;   /* Pointer back to the start of the page */
  DbPage *pDbPage; /* Pager page handle */
  Pgno pgno;   /* Page number for this page */
  MemPage *pParent;/* The parent of this page.  NULL for root */
};

struct MemPage2 {
  u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
  u8 childPtrSize; /* 0 if leaf==1.  4 if leaf==0 */
  u8 hdrOffset;/* 100 for page 1.  0 otherwise */
  u8 isInit:1;   /* True if previously initialized. MUST BE FIRST! */
  u8 idxShift:1; /* True if Cell indices have changed */
  u8 intKey:1;   /* True if intkey flag is set */
  u8 leaf:1; /* True if leaf flag is set */
  u8 zeroData:1; /* True if table stores keys only */
  u8 leafData:1; /* True if tables stores data on leaves only */
  u8 hasData:1;  /* True if this page stores data */
  u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
  u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
  u16 cellOffset;  /* Index in aData of first cell pointer */
  u16 idxParent;   /* Index in parent of this node */
  u16 nFree;   /* Number of free bytes on the page */
  u16 nCell;   /* Number of cells on this page, local and ovfl */
  struct _OvflCell {   /* Cells that will not fit on aData[] */
u8 *pCell;  /* Pointers to the body of the overflow cell */
u16 idx;/* Insert this cell before idx-th non-overflow cell */
  } aOvfl[5];
  BtShared *pBt;   /* Pointer back to BTree structure */
  u8 *aData;   /* Pointer back to the start of the page */
  DbPage *pDbPage; /* Pager page handle */
  Pgno pgno;   /* Page number for this page */
  MemPage *pParent;/* The parent of this page.  NULL for root */
};

sizeof(struct MemPage) = 84
sizeof(struct MemPage2) = 76

Or is there a C portability issue?



  

Park yourself in front of a world of choices in alternative vehicles. Visit the 
Yahoo! Auto Green Center.
http://autos.yahoo.com/green_center/ 

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

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[Joe Wilson]

struct MemPage2 {
  u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
  u8 childPtrSize; /* 0 if leaf==1.  4 if leaf==0 */
  u8 hdrOffset;/* 100 for page 1.  0 otherwise */
  u8 isInit:1;   /* True if previously initialized. MUST BE FIRST! */

Okay, maybe not this MUST BE FIRST field...
:-)




   
Ready
 for the edge of your seat? 
Check out tonight's top picks on Yahoo! TV. 
http://tv.yahoo.com/

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

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[Joe Wilson]

MemPage bitfield patch below. 

sizeof(MemPage) on Linux: 

  original: 84
  patched:  76

Patched "make test" runs without regressions on Linux and Windows.
Timings for "make test" (elapsed):

  original: 1:20.74
  patched:  1:20.22

Size of sqlite3.o when compiled from almalogmation with all 
sqlite features enabled with gcc flags -O3 -fomit-frame-pointer:

  original: 586976 bytes
  patched:  587880 bytes

Patched sqlite3.o is 904 bytes larger. 

Break-even for memory is 904/8 = 113 MemPage structs allocated.


Index: src/btreeInt.h
===
RCS file: /sqlite/sqlite/src/btreeInt.h,v
retrieving revision 1.4
diff -u -3 -p -r1.4 btreeInt.h
--- src/btreeInt.h  16 May 2007 17:28:43 -  1.4
+++ src/btreeInt.h  30 May 2007 16:26:03 -
@@ -269,15 +269,15 @@ typedef struct BtLock BtLock;
 */
 struct MemPage {
   u8 isInit;   /* True if previously initialized. MUST BE FIRST! */
-  u8 idxShift; /* True if Cell indices have changed */
   u8 nOverflow;/* Number of overflow cell bodies in aCell[] */
-  u8 intKey;   /* True if intkey flag is set */
-  u8 leaf; /* True if leaf flag is set */
-  u8 zeroData; /* True if table stores keys only */
-  u8 leafData; /* True if tables stores data on leaves only */
-  u8 hasData;  /* True if this page stores data */
-  u8 hdrOffset;/* 100 for page 1.  0 otherwise */
-  u8 childPtrSize; /* 0 if leaf==1.  4 if leaf==0 */
+  u8 hdrOffset:7;/* 100 for page 1.  0 otherwise */
+  u8 zeroData:1; /* True if table stores keys only */
+  u8 childPtrSize:3; /* 0 if leaf==1.  4 if leaf==0 */
+  u8 leaf:1; /* True if leaf flag is set */
+  u8 idxShift:1; /* True if Cell indices have changed */
+  u8 intKey:1;   /* True if intkey flag is set */
+  u8 leafData:1; /* True if tables stores data on leaves only */
+  u8 hasData:1;  /* True if this page stores data */
   u16 maxLocal;/* Copy of Btree.maxLocal or Btree.maxLeaf */
   u16 minLocal;/* Copy of Btree.minLocal or Btree.minLeaf */
   u16 cellOffset;  /* Index in aData of first cell pointer */



 

Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
http://videogames.yahoo.com/platform?platform=120121

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

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[Joe Wilson]

--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> There are also some issues with regard to the ordering and layout 
> of bitifleds in cross platform applications. I suspect that is the 
> reason they aren't used.

If an external interface changed, sure. But these internal structs 
change constantly from (minor) release to release.

The struct in question is used solely by btree.c, so the ordering 
and layout for bit fields on different compilers or different platforms 
do not matter.


   
Got
 a little couch potato? 
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz
 

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

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[Joe Wilson]

--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> Joe Wilson wrote:
> >
> > If an external interface changed, sure. But these internal structs 
> > change constantly from (minor) release to release.
> >
> > The struct in question is used solely by btree.c, so the ordering 
> > and layout for bit fields on different compilers or different platforms 
> > do not matter.
> >
> >   
> Joe,
> 
> Yeah, for strictly internal memory based structures, I can't see a 
> problem. I wasn't sure if these were ever written to disk.

Writing structs' memory directory to disk is not portable, whether bit 
fields are used or not.

> One other issue that might have been a problem is that you can't get the 
> address of a bitfield. Since you have successfully  compiled the code 
> using bitfields, there must not be any code that tries to get a pointer 
> to any of these fields.
> 
> Your test seems to show these changes don't have any adverse performance 
> impact either.
> 
> You may want to look at how the isInited field is used. You may be able 
> to combine it with the others as long as it stays in the first byte and 
> the code only checks for zero vs nonzero values on that byte (then again 
> that may not be safe if other combined bitfield are set nonzero before 
> the isInited field is set). If its safe, you could save another byte per 
> structure.

Generally structs are aligned on 8-byte boundaries, so making isInited 
a bitfield wouldn't save any additional space in this particular case.


 

Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html 

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

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[Joe Wilson]

> > If the MemPage are malloced individually (instead of being put in arrays), 
> > then they are 16
> byte
> > aligned on most platforms, making the allocated block effectively the same 
> > size (well, that
> > depends on how many bytes are used by malloc before the user block in 
> > memory).
> 
> This patch does indeed save memory - on Linux at least.
> Linux has a malloc mimimum resolution of 8 bytes, not 16.

I mis-wrote: Linux's minimum malloc, malloc(1), will occupy 16 bytes
of heap as you stated. 

Malloc(n) on Linux will allocate the next multiple of 8 bytes
for (n+4), minimum 16 bytes total.

Observed malloc behavior on Linux -
Malloc'd bytes on the left, heap occupied by that malloc on right:

1   16
2   16
3   16
4   16
5   16
6   16
7   16
8   16
9   16
10  16
11  16
12  16
13  24
14  24
15  24
16  24
17  24
18  24
19  24
20  24
21  32
22  32
23  32
24  32
25  32
26  32
27  32
28  32
29  40
30  40
31  40
32  40
33  40
34  40
35  40
36  40
37  48
38  48
39  48
40  48
41  48
42  48
43  48
44  48
45  56
46  56
47  56
48  56
49  56
50  56
51  56
52  56
53  64
54  64
55  64
56  64
57  64
58  64
59  64
60  64
61  72
62  72
63  72
64  72
65  72

etc...


 

Get your own web address.  
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

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

Re: [sqlite] Changing Schema On The Fly

[Joe Wilson]

Be aware of a Windows OS bug that prevents correct conversion of epoch
integers to local date/time due to the recent US DST change:

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

Assuming you've applied the Windows OS DST patch, epoch-converted 
times can be off by an hour for pre-2007 dates in the time periods 
that used to not be be in daylight savings time, but are now as of 
2007.

SQLite relies on the underlying OS to handle these timezone/DST issues.
In Windows versions prior to Vista, Windows only keeps one DST record
per timezone - regardless of different past DST periods.

--- Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> The issue isn't with SQLite at all, actually, but with the ODBC
> driver. I guess the ODBC driver "trusts" SQLite's data type
> description because I have a field called "timestamp" that actually
> stores an epoch integer in it and ODBC-aware applications see the
> datatype as "DateTime" and refuse to pass through the data. So I
> really just need to change the type description inside SQLite so it
> will report integer to the ODBC driver. And since there are a *lot* of
> databases with this problem I'd love to be able to just issue some
> sort of update through SQLite to make that happen.



  

Shape Yahoo! in your own image.  Join our Network Research Panel today!   
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 



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

Re: [sqlite] Re: CAST

[Joe Wilson]

--- John Stanton <[EMAIL PROTECTED]> wrote:
> Sqlite lets you put in anything as the declared type.  "DEAD PARROT", 
> "MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared 
> types.  Sqlite makes the underlying type TEXT if it is not obviously 
> numeric.

The default affinity type is SQLITE_AFF_NUMERIC if SQLite cannot determine 
the type:

  SQLite version 3.3.17
  Enter ".help" for instructions
  sqlite> create table t1(a GODZILLA);
  sqlite> insert into t1 values(3);
  sqlite> insert into t1 values('duck');
  sqlite> insert into t1 values('007');
  sqlite> insert into t1 values('0004.56');
  sqlite> select a, typeof(a) from t1;
  3|integer
  duck|text
  7|integer
  4.56|real

Note, if a column has no type specified, then its affinity is none:

  SQLite version 3.3.17
  Enter ".help" for instructions
  sqlite> create table n1(a);
  sqlite> insert into n1 values('009');
  sqlite> select a, typeof(a) from n1;
  009|text

But it's up to your program or sqlite wrapper to decide how to read each 
column with the appropriate sqlite3_column_* function.

/*
** Scan the column type name zType (length nType) and return the
** associated affinity type.
**
** This routine does a case-independent search of zType for the
** substrings in the following table. If one of the substrings is
** found, the corresponding affinity is returned. If zType contains
** more than one of the substrings, entries toward the top of
** the table take priority. For example, if zType is 'BLOBINT',
** SQLITE_AFF_INTEGER is returned.
**
** Substring | Affinity
** 
** 'INT' | SQLITE_AFF_INTEGER
** 'CHAR'| SQLITE_AFF_TEXT
** 'CLOB'| SQLITE_AFF_TEXT
** 'TEXT'| SQLITE_AFF_TEXT
** 'BLOB'| SQLITE_AFF_NONE
** 'REAL'| SQLITE_AFF_REAL
** 'FLOA'| SQLITE_AFF_REAL
** 'DOUB'| SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const Token *pType){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const unsigned char *zIn = pType->z;
  const unsigned char *zEnd = >z[pType->n];

  while( zIn!=zEnd ){
h = (h<<8) + sqlite3UpperToLower[*zIn];
zIn++;
if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){ /* CHAR */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){   /* CLOB */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){   /* TEXT */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')  /* BLOB */
&& (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
  aff = SQLITE_AFF_NONE;
#ifndef SQLITE_OMIT_FLOATING_POINT
}else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')  /* REAL */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
}else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')  /* FLOA */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
}else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b')  /* DOUB */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
#endif
}else if( (h&0x00FF)==(('i'<<16)+('n'<<8)+'t') ){/* INT */
  aff = SQLITE_AFF_INTEGER;
  break;
}
  }

  return aff;
}



   

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

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

Re: [sqlite] Re: Changing Schema On The Fly

[Joe Wilson]

--- Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> I guess this isn't possible after all?

Get the SQLite ODBC driver source code and alter it to do whatever 
you like when the type "timestamp" column comes up.

> On 5/31/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> > I have a set of databases that contain a date type called "timestamp".
> > I need to make those "integer" so they come through the ODBC driver
> > the right way. Is there any way to change all of that through queries
> > on-the-fly? I'd like to avoid re-creating all the databases if
> > possible..



  
___
You snooze, you lose. Get messages ASAP with AutoCheck
in the all-new Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_html.html

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

Re: [sqlite] Sqlite 3 Crash on OSX

[Joe Wilson]

--- Dan Kennedy <[EMAIL PROTECTED]> wrote:
> 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.dylib  0x9406e587 sqlite3pager_get + 390
> > 1   libsqlite3.0.dylib  0x94054275 sqlite3BtreeCopyFile + 381
> > 2   libsqlite3.0.dylib  0x940542dd sqlite3BtreeCopyFile + 485
> > 3   libsqlite3.0.dylib  0x940545b6 sqlite3BtreeLast + 134
> > 4   libsqlite3.0.dylib  0x940830c1 sqlite3VdbeExec + 16021
> > 5   libsqlite3.0.dylib  0x94084c73 sqlite3_step + 270
> > 6   libsqlite3.0.dylib  0x9408b343 sqlite3_exec + 260
> > 7   libsqlite3.0.dylib  0x9407a53d 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.

It might not necessarily be a corrupted stack trace.
I've seen similarly odd traces for other programs on other platforms 
when the code was optimized and not compiled with -g.
The symbols cited might just be the closest ones available since some 
functions were inlined.



   
Ready
 for the edge of your seat? 
Check out tonight's top picks on Yahoo! TV. 
http://tv.yahoo.com/

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

Re: [sqlite] baffled by dates

[Joe Wilson]

--- John Stanton <[EMAIL PROTECTED]> wrote:
> Sqlite does have a date format, it is physically a 64 bit floating point 
> number.  There are functions to transform in and out of that format to 
> present dates as required by the user.  The Sqlite date format uses a 
> magib epoch which matches all of the major internaional date systems.

SQLite does not have a date type. Period.

What you're describing is not a true DATE type, but your own 
programming convention when dealing with dates. Your programming 
convention relies on functions, date strings and epoch-based floating 
point numbers.

If you were correct and SQLite currently supported a DATE type,
then why was this Proposed Incompatible Changes entry added to 
the SQLite wiki by DRH?

 "Support A DATE Type" 
 http://www.sqlite.org/cvstrac/wiki?p=ProposedIncompatibleChanges

Because rehashing the old arguments is pointless, here's the old thread:

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

> 
> P Kishor wrote:
> > There is no "DATE" format in SQLite. Dates are stored as strings. The
> > only formats SQLite knows and understands are TEXT, REAL, INTEGER,
> > BLOB, and NULL (see the link on datatypes). On the other hand, there
> > are built-in functions that can act on your date strings and convert
> > them back and forth, manipulate them, etc. Once again, see the link on
> > working with dates on the SQLite wiki.



 

We won't tell. Get more on shows you hate to love 
(and love to hate): Yahoo! TV's Guilty Pleasures list.
http://tv.yahoo.com/collections/265 

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

Re: [sqlite] Stack usage

[Joe Wilson]

--- [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.

SQLite uses recursion to generate code for SELECT UNION chains.

** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
**
** This statement is parsed up as follows:
** 
** SELECT c FROM t3
**  |
**  `->  SELECT b FROM t2
**|
**`-->  SELECT a FROM t1

So for your example you will have a stack of 2000 nested calls
of sqlite3Select's (via multiSelect).

Using gcc -O2 on x86 I see that each level's stack is 480 bytes.
So for 2000 unions in a select, SQLite will consume at least 
480 bytes/frame x 2000 frames = 960,000 bytes. I'm guessing that Java
limits the stack to 1M per thread in your case, hence your problem.

It is not easy to adapt SQLite's code to not use recursion for 
code generation, although with enough time and effort anything is
possible. Conceivably, the processing of compound queries could 
be turned into a for loop.

The SQLite authors have recently added a number of maximum limits 
via #defines. Perhaps the SQLITE_MAX_EXPR_DEPTH limit could also 
be applied here:

  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
if( p->pRightmost==0 ){
  Select *pLoop;
  for(pLoop=p; pLoop; pLoop=pLoop->pPrior){
pLoop->pRightmost = p;
  }
}
return multiSelect(pParse, p, eDest, iParm, aff);
  }




 

It's here! Your new message!  
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/

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

Re: [sqlite] Stack usage

[Joe Wilson]

Such a statement would never be issued on a low memory device.

This is an exceptional case involving a select with 2000 
unions - I would not worry about it.

--- [EMAIL PROTECTED] wrote:
> This is very worrying since it means that the statement cannot be compiled on 
> a
> low memory device.
> I am new to Sqlite, but I would guess that a precompiled query could be used,
> where memory is low
> and I also suppose that variable values could be bound to that precompiled
> query.


 

TV dinner still cooling? 
Check out "Tonight's Picks" on Yahoo! TV.
http://tv.yahoo.com/

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

Re: [sqlite] Stack usage

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson napisa³(a):
> > Please respond to the mailing list in the future.
> 
> Sorry. Different client. I didn't notice the adress.
> 
> > At least there's a known workaround, so no problem.
> 
> Workaround is not a solution.

Increasing the stack will fix your problem.

> > > > hence your problem.
> > > 
> > > Sure it is. Just like any bug or missing feature in any application. But
> > I'v always hoped that
> > > SQLite won't constraint me. It's sad the it does, and horrible that it
> > crashes instead returning
> > > an error. 
> > 
> > You should ask for your money back.
> 
> Yes it's free. But I think that no user (or author) of software like when 
> software crashes.
> Unfortunatelly SQLite does.

And you're also free to patch it.

> Last time wasn't sent to the mailing list so once more:
> Does anyone know other places apart from unions where deep recursion may be 
> used?

Because you've asked so nicely, it seems that SQLite already handles this:

#if SQLITE_MAX_EXPR_DEPTH>0
/* The following three functions, heightOfExpr(), heightOfExprList()
** and heightOfSelect(), are used to determine the maximum height
** of any expression tree referenced by the structure passed as the
** first argument.
**
** If this maximum height is greater than the current value pointed
** to by pnHeight, the second parameter, then set *pnHeight to that
** value.
*/
static void heightOfExpr(Expr *p, int *pnHeight){
  if( p ){
if( p->nHeight>*pnHeight ){
  *pnHeight = p->nHeight;
}
  }
}
static void heightOfExprList(ExprList *p, int *pnHeight){
  if( p ){
int i;
for(i=0; inExpr; i++){
  heightOfExpr(p->a[i].pExpr, pnHeight);
}
  }
}
static void heightOfSelect(Select *p, int *pnHeight){
  if( p ){
heightOfExpr(p->pWhere, pnHeight);
heightOfExpr(p->pHaving, pnHeight);
heightOfExpr(p->pLimit, pnHeight);
heightOfExpr(p->pOffset, pnHeight);
heightOfExprList(p->pEList, pnHeight);
heightOfExprList(p->pGroupBy, pnHeight);
heightOfExprList(p->pOrderBy, pnHeight);
heightOfSelect(p->pPrior, pnHeight);
  }
}


/*
** Set the Expr.nHeight variable in the structure passed as an
** argument. An expression with no children, Expr.pList or
** Expr.pSelect member has a height of 1. Any other expression
** has a height equal to the maximum height of any other
** referenced Expr plus one.
*/
void sqlite3ExprSetHeight(Expr *p){
  int nHeight = 0;
  heightOfExpr(p->pLeft, );
  heightOfExpr(p->pRight, );
  heightOfExprList(p->pList, );
  heightOfSelect(p->pSelect, );
  p->nHeight = nHeight + 1;
}

/*
** Return the maximum height of any expression tree referenced
** by the select statement passed as an argument.
*/
int sqlite3SelectExprHeight(Select *p){
  int nHeight = 0;
  heightOfSelect(p, );
  return nHeight;
}
#endif



 

Food fight? Enjoy some healthy debate 
in the Yahoo! Answers Food & Drink Q
http://answers.yahoo.com/dir/?link=list=396545367

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

Re: [sqlite] Stack usage

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> My application's doesn't create any databases itself. It allows users to 
> store any data. And users need to be able to store any number of columns in 
> 1 table (the most I'v heard about is about 1, but I wouldn't be 
> surprised if they had more). Trust me, they need it and they can't do 
> anything about it.. 
> But the problem isnt't actually caused by many columns in the table. The 
> query i posted before doesn't need any table in the database - it was 
> created based on the a table but any similar query will crash SQLite. The 
> query was created by JDBC driver - I will change driver's code and the 
> problem will be solved for now, but it doesn't mean that everything is ok. 
> Similar automatically created queries may be created based on anything, for 
> example rows of some table - 1 rows isn't too many, right? And that 
> query may cause any application to crash. And possibly there are other 
> innocent looking queries that are implemented using recursion that will do 
> the same damage (maybe nested selects ?).

Since your program uniquely requires such a high level of fault tolerance
and you have very demanding customers, you should consider buying some 
commercial support:

 http://www.hwaci.com/sw/sqlite/prosupport.html



   

Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

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

Re: [sqlite] disk I/O error

[Joe Wilson]

> A Linux 2.6/x86_64 system reports a "disk I/O error" (SQLITE_IOERR)
> while generating a specific report from a SQLite database (SQLite
> 3.3.6).  The database and temporary files are accessed through an NFS
> mount.  After running the program again with SQLite tracing enabled
> (plus a bit more I added), I see that SQLITE_IOERR is returned by
> unixRead() because read() unexpectedly returned 0!
> 
> Here's some relevant strace output:
> 
> open("/nfs/tmp/sqlite_dBjTG5bZdsqFVPb", O_RDWR|O_CREAT|O_EXCL, 0644) = 8
> 
> [...]
> 
> lseek(8, 193536, SEEK_SET)= 193536
> write(8, "\n\0\0\0\30\0\222\0\0\266\0\332\0\376\1\"\1F\1l\1\222\1"...,
> 1024) = 1024
> 
> [...]
> 
> lseek(8, 226304, SEEK_SET)= 226304
> write(8, "\n\0\0\0\30\0\240\0\0\240\0\304\0\350\1\f\0010\1T\1x\1"...,
> 1024) = 1024
> lseek(8, 193536, SEEK_SET)= 193536
> read(8, "", 1024) = 0
> fstat(8, {st_mode=S_IFREG|0644, st_size=227328, ...}) = 0
> 
> The read() call shouldn't fail -- the same page was written to at the
> beginning of the transaction!  At least by the time fstat() is called,
> the file is 227328 bytes long, so a read at an offset of 193536 should
> not fail.
> 
> I'm suspecting that the NFS server in question is buggy or
> misconfigured.  Unfortunately I don't have access to either the NFS
> server or the host running the program, so mainly all I can access is
> some strace and SQLite tracing output.

I'd guess that your NFS server's writes are not synchronous. 
If this is true, then it may be that the NFS server performing 
the read does not yet have the data in a readable form and 
returns 0. See "safe asynchronous writes" in 
http://nfs.sourceforge.net/

Yikes - you're using /nfs/tmp for your temp_store_directory - 
not good. 

Use either:

 PRAGMA temp_store_directory = '/some/local/disk/directory';

or

 pragma temp_store=memory;

to avoid the brutal NFS round trip delays for your temp files.

But if you *must* perform writes to the NFS database file, 
and for some strange reason must use NFS temp store, you might 
try hacking the unix reads to loop with a usleep delay if it 
encounters a zero return, giving up and failing after 
a number of retries. 

If you could post your findings to the list, that would be great.


   

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]
-

Re: [sqlite] Blob handling in command line tool sqlite3

[Joe Wilson]

.dump

> when using a blob column in the command line tool 'sqlite3' im getting 
> garbled output when selecting from a table that contains a blob column.
> 
> Is there a way to get the blob column output in escaped format, like its 
> used in the insert statement?



   

Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

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

Re: [sqlite] Blob handling in command line tool sqlite3

[Joe Wilson]

CREATE TABLE t(b blob);
INSERT INTO "t" VALUES(X'ABCD');
select quote(b) from t;
X'ABCD'

> Joe Wilson wrote:
> > .dump
> 
> yes, but dump gives me all rows for the table and not those that are the 
> result of a query (which might have a WHERE clause).
> 
> Is there no way for format the output of the select?



   

Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

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

Re: [sqlite] sqlite 3.2.8 segmentation fault

[Joe Wilson]

You're crashing in free(), which means your heap is corrupted.
The cause of the corruption could be from anywhere - and not 
necessarily sqlite. It might be the victim of a previously corrupted
heap. Try running your program through a memory checker like valgrind 
to see what it turns up. 
Also test against the latest version of sqlite, just in case.

If you don't have the time to find the source of the memory corruption,
perhaps this alternative malloc library may allow your program to run:

  DieHard, by Emery Berger (of Hoard fame).
  http://prisms.cs.umass.edu/emery/index.php?page=diehard

--- "Rachmel, Nir (Nir)" <[EMAIL PROTECTED]> wrote:
> I am running sqlite version 3.2.8, on a windriver linux, on a ppc
> platform.
> It is linked to PHP 5.1.4, where I have scripts that access the database
> (both sets and gets). Recently I have been experimenting with
> simultanious accesses to the sqlite database (meaning mutliple clients
> requesting information from the database while another client is
> commiting data to the database).
>  
> After a while, php crashes with the following error printed to the
> syslog:
>  *** glibc detected *** double free or corruption (fasttop): 0x10796ca8
> ***
>  
> Running the test again, with debug symbols produces the following
> backtrace:
>  
> 0x0fdde324 in raise () from /lib/libc.so.6
> (gdb) where
> #0  0x0fdde324 in raise () from /lib/libc.so.6
> #1  0x0fddfd8c in abort () from /lib/libc.so.6
> #2  0x0fe14bac in __fsetlocking () from /lib/libc.so.6
> #3  0x0fe14bac in __fsetlocking () from /lib/libc.so.6
> #4  0x0fe14bac in __fsetlocking () from /lib/libc.so.6
> #5  0x0fe14bac in __fsetlocking () from /lib/libc.so.6
> .
> .
> .
> Previous frame inner to this frame (corrupt stack?)
>  
> We have a special script that re-creates the backtrace, and it looks
> like this:
> 
> (gdb) bt_script
> frame #: stack_frame_ptrbackchain_ptr   LR_save_word
> frame 0: 0x:0x  $1 = 0xfdde324
> 
> frame 1: 0x337f5cf0:0x337f5d10  $2 = 0xfee2f7c
> 
> frame 2: 0x337f5d10:0x337f5e40  $3 = 0xfddfdf8
> 
> frame 3: 0x337f5e40:0x337f5fe0  $4 = 0xfe14bac
> <__libc_fatal>
> frame 4: 0x337f5fe0:0x337f6040  $5 = 0xfe1c6b4
> 
> frame 5: 0x337f6040:0x337f6060  $6 = 0xfe1caac
> 
> frame 6: 0x337f6060:0x337f6070  $7 = 0xf7f62dc
> 
> frame 7: 0x337f6070:0x337f60c0  $8 = 0xf7de980
> 
> frame 8: 0x337f60c0:0x337f60e0  $9 = 0xf7e2478



   

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

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

Re: [sqlite] Blob handling in command line tool sqlite3

[Joe Wilson]

--- Guido Ostkamp <[EMAIL PROTECTED]> wrote:
> On Sat, 9 Jun 2007, Joe Wilson wrote:
> > CREATE TABLE t(b blob);
> > INSERT INTO "t" VALUES(X'ABCD');
> > select quote(b) from t;
> > X'ABCD'
> 
> Thanks, Joe. I am just wondering why this 'quote' is not the default 
> behavior on binary columns when the 'sqlite3' tool is used.

Yeah it probably should be - for ".mode insert" anyway.


 

Sucker-punch spam with award-winning protection. 
Try the free Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/features_spam.html

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

Re: [sqlite] sqlite3 through PDO/PHP issues.

[Joe Wilson]

> $sql = "SELECT count(*) FROM feedback";
> $result = $handle->query($sql);
> echo "rows = " . $result->rowCount() . "\n";

I've never used PHP, but just for kicks, to eliminate 
the database from the equation, try:

 $sql = "SELECT 123 AS abc;";

and see what happens.


   

Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for 
today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow  

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

Re: [sqlite] error in round-function?

[Joe Wilson]

--- Olaf Schmidt <[EMAIL PROTECTED]> wrote:
> On what OS have you tested?
> If on windows, was it a GCC-compile or a MS-VC-compile?

Running the GCC cross-compiled sqlite3.exe from 
http://www.sqlite.org/sqlite-3_3_17.zip on Windows 
(well, wine on Linux):

  SQLite version 3.3.17
  Enter ".help" for instructions
  sqlite> select round(0.95, 1);
  0.9

What result does the sqlite3.exe from the above link give 
you on your machine?


  

Shape Yahoo! in your own image.  Join our Network Research Panel today!   
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 



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

Re: [sqlite] error in round-function?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Going into this statement on both Linux and Windows,
> the exact same values are in realvalue and in rounder:
> 
>realvalue:  0xf000   0.94995559107901499373838
>rounder:0x3faa   0.0500030
> 
> Both machines execute the exact same 4 ix86 opcodes:
> 
>fldl  
>fldt  
>faddp %st,%st(1)
>fstpt 
> 
> But they end up with different answers.  Linux gets
> what I believe is the correct answer:
> 
>realvalue:  0xfd00   0.5836663657655662973
> 
> Windows gets the wrong answer:
> 
>realvalue:  0x8000   1.000
> 
> Can somebody please suggest to me what the difference is?
> Does windows somehow initialize the floating point hardware
> differently so that it does some kind of rounding on its own?
> Why should the same machine-language instructions generate a
> different answer on windows than it does on linux?

http://support.microsoft.com/kb/102555

 Microsoft Visual C++ runtime library provides default floating-point 
 exception handling and includes functions such as _controlfp for 
 determining and adjusting the floating-point hardware's rounding, 
 precision control, and exception handling behavior.

More info on fp:precise and _controlfp:

 http://msdn2.microsoft.com/en-us/library/aa289157(vs.71).aspx

double a, b, cLower, cUpper;
. . .
_controlfp( _RC_DOWN, _MCW_RC );// round to -
cLower = a*b;
_controlfp( _RC_UP, _MCW_RC );// round to +
cUpper = a*b;
_controlfp( _RC_NEAR, _MCW_RC );// restore rounding mode



 

Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

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

RE: [sqlite] sqlite 3.2.8 segmentation fault

[Joe Wilson]

This is a question for your OS vendor.

--- "Rachmel, Nir (Nir)" <[EMAIL PROTECTED]> wrote:
> I have run into a problem - using the same environment variables as I
> did in the old version ( I am upgrading from 3.2.8 to the newest version
> of 3.3.17), I am unable to compile succesfully.
> Here is the error I get:
> 
>   attempt to open
> /home/lira/toolchain/gnu/3.4.4-wrlinux-1.2/x86-linux2/bin/../../../../wr
> linux-1.2/target-libs/gpp/powerpc-wrs-linux-powerpc/lib/crtn.o succeeded
>   
> /home/lira/toolchain/gnu/3.4.4-wrlinux-1.2/x86-linux2/bin/../../../../wr
> linux-1.2/target-libs/gpp/powerpc-wrs-linux-powerpc/lib/crtn.o
>   ld.so.1 needed by
> /home/lira/toolchain/wrlinux-1.2/target-libs/gpp/powerpc-wrs-linux-power
> pc/lib/libpthread.so.0
>   found ld.so.1 at
> /home/lira/toolchain/gnu/3.4.4-wrlinux-1.2/x86-linux2/bin/../../../../wr
> linux-1.2/target-libs/gpp/powerpc-wrs-linux-powerpc/lib//ld.so.1
>   collect2: ld returned 1 exit status
>   make[1]: *** [sqlite3] Error 1
>   make[1]: Leaving directory
> `/home/rachmel/work/sqlite_new/avaya_build_target'
>   make: *** [build] Error 2 
> 
> Any ideas as to what is the problem?



 

Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

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

RE: [sqlite] Slow View Performance

[Joe Wilson]

> TabA.ID1
> TabA.ID2
> TabA.field1
>
> TabB.ID1
> TabB.ID2
> TabB.field2
> 
> TabC.ID1
> TabC.field3
> 
> ViewBC:
> SELECT * FROM TabB INNER JOIN TabC On TabB.ID1 = TabC.ID1
> 
> This is slow:
> SELECT field1, field2, field3 from TabA LEFT OUTER JOIN ViewBC ON TabA.ID1 =
> ViewBC.ID1 AND TabA.ID2 = ViewBC.ID2 
> 
> This is fast:
> SELECT field1, field2, field3 from TabA LEFT OUTER JOIN TabB ON TabA.ID1 =
> TabB.ID1 AND TabA.ID2 = TabB.ID2 INNER JOIN TabC ON TabB.ID1 = TabC.ID1

The second query may be faster, but the two queries are not equivalent.
Outer joins are not associative.

CREATE TABLE t1(a);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(b);
INSERT INTO t2 VALUES(2);
CREATE TABLE t3(c);
INSERT INTO t3 VALUES(2);
create view v1 as select b,c from t2 join t3 on b=c;

sqlite> select a,b,c from t1 left join v1 on a=b;
a   b   c 
--  --  --
1   NULLNULL  

sqlite> select a,b,c from t1 left join (t2 join t3 on b=c) on a=b;
a   b   c 
--  --  --
1   NULLNULL  

sqlite> select a,b,c from  t1 left join t2 on a=b  join t3 on b=c;

sqlite> select a,b,c from (t1 left join t2 on a=b) join t3 on b=c;




 

Need Mail bonding?
Go to the Yahoo! Mail Q for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list=396546091

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

Re: [sqlite] Database replication question

[Joe Wilson]

Large bulk inserts with more than one index (implicit or explicit) 
is not SQLite's strong suit.

If you search the mailing list archives you'll find a few suggestions:

- "BEGIN EXCLUSIVE" (or is it "BEGIN IMMEDIATE"?) on the 
  database file and then copy the file over - fastest way

or

- increasing cache sizes 
- pre-sorting the data in index order prior to bulk insert
- creating the other indexes after all the data is inserted

If you do not require a live backup you could use the copy trick
and augment that with a daily archive via 

 sqlite3 file.db .dump | gzip etc...

in case the database file becomes corrupted.

--- [EMAIL PROTECTED] wrote:
> I am trying to put in place a simple replication process to copy a database 
> from one machine to
> an other.
...
> My question is:
> 
> is there a way to do a select or a .dump so that when inserting the data on 
> the other end,
> things will be faster? Or maybe there are some pragmas I can use that would 
> improve performance?



 

Expecting? Get great news right away with email Auto-Check. 
Try the Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html 

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

RE: [sqlite] PRAGMA cache_size = 0

[Joe Wilson]

> I tried to set the cache size to 0 (after sqlite3_open0, and then query
> for pragma cache_size which returns 2000 (default cache size). Why its
> not returning 10 (according to Weiyang Wang)?

It does report 0, even though internally it is using a value of 10.

 SQLite version 3.3.17
 Enter ".help" for instructions
 sqlite> pragma cache_size;
 2000
 sqlite> pragma cache_size=0;
 sqlite> pragma cache_size;
 0

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


   

Moody friends. Drama queens. Your life? Nope! - their life, your story. Play 
Sims Stories at Yahoo! Games.
http://sims.yahoo.com/  

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

Re: [sqlite] Database replication question

[Joe Wilson]

> If the performance problem is with the seconday index, is there a way to 
> "pause" indexing before
> a large bulk insert and then "resume" it later without rebuilding the entire 
> index (to avoid
> doing: drop index + inserts + create index)?

No


 

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]
-

Re: [sqlite] sqlite3_create_function function name when called?

[Joe Wilson]

--- Omar Eljumaily <[EMAIL PROTECTED]> wrote:
> Is it possible to get the name of the function that the callback was 
> called because of? 

If you specified a userData argument in sqlite3_create_function()
you can retrieve it in the functon itself with

  void *sqlite3_user_data(sqlite3_context*);

But you'd still have to call sqlite3_create_function() to register 
every new function anyway. Otherwise SQLite's parser wouldn't know 
about these functions' names, whether they are an aggregate function 
or not, or how many parameters they take.

I am not aware of a way to remove a previously created function using
the public API.


   

Sick sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

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

Re: [sqlite] Is it a bug?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> I have a user-defined function named DECRYPT, which decrypts column data
> encrypted by my other UDF named ENCRYPT.
> 
> The UDF callback function (which does the decrypting) calls 
> sqlite3_result_blob
> after decrypting the data.  Sqlite does return the data OK...BUT!  It doesn't
> provide the data type for the column when I call sqlite3_column_decltype for
> the column.  The return value is NULL.  I must have the column data type, else
> my code can't properly interpret the value.   This must be a bug, right?

As far as I know, SQLite has to way of knowing what the column type 
is in the case of a function:

  "If the Nth column of the result set is not a table column, 
  then a NULL pointer is returned."

  http://www.sqlite.org/capi3ref.html#sqlite3_column_decltype

You can interrogate the first row's column values with 
sqlite3_column_type as a workaround (assuming the column in subsequent
rows are of the same type):

  http://www.sqlite.org/capi3ref.html#sqlite3_column_type



 

Sucker-punch spam with award-winning protection. 
Try the free Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/features_spam.html

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

Re: [sqlite] Is it a bug?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Thank you Joe.  At least sqlite3_column_type gives me a column type I 
> can use as
> a hint.
> 
> The issue is that I have a well-defined column type naming convention for my
> wrapper classes to look for (INT, TEXT, FLOAT, BLOB, etc.).  For binary data
> fields, its BLOB.  For other field types, when encrypted they should be
> prefixed with BLOB (eg. BLOB_INT, BLOB_TEXT, BLOB_FLOAT), so that my low-level
> execute function can provide hints to the downstream result-set parsing to use
> for converting types.
> 
> Wouldn't it be entirely possible to provide the column's type if the function
> used only a single column in its parameter list?  For example, consider the

You can't infer a function's return type from its arguments.
Take the hypothetical function FOO(x). If I pass it a number, it will
return the number spelled out as TEXT, but if I pass it a BLOB it will
return its length*PI as a FLOAT.

As a possible extension one could see sqlite3_create_function taking an 
optional argument with a hint as to its return type that sqlite may use 
for sqlite3_column_decltype. But SQLite does not currently return any 
column types for any ad-hoc expression. So this would not be a trivial
change.

> UPPER function.  In cases where its just used to convert a single column to
> upper-case, couldn't Sqlite just use that column's data type?  E.g.,
> 
>  UPPER(FirstName)
> 
> It should be an easy thing to provide this info.  Even if there were multiple
> fields involved, if they were all the same data type, Sqlite could know this
> and provide the common type.  E.g.,
> 
>  UPPER(FirstName + LastName)

Even a simple operator like '+' you cannot easily determine the return 
type of its expression without evaluating it - and even then it may 
return a FLOAT in one invocation and TEXT in a different invocation.
With some static expression analysis you could infer the return type
for a number of input combinations, but this would require a fair bit
of new code.

> If both field types were BLOB_TEXT, then that should be provided.  Only 
> in cases
> where there were multiple columns and the data types were different 
> would a NULL
> be returned.
> 
> This would certainly be an improvement for Sqlite, would it not?
> 
> cheers
> -brett
> 
> Quoting Joe Wilson <[EMAIL PROTECTED]>:
> 
> > --- [EMAIL PROTECTED] wrote:
> >> I have a user-defined function named DECRYPT, which decrypts column data
> >> encrypted by my other UDF named ENCRYPT.
> >>
> >> The UDF callback function (which does the decrypting) calls 
> >> sqlite3_result_blob
> >> after decrypting the data.  Sqlite does return the data OK...BUT!  
> >> It doesn't
> >> provide the data type for the column when I call sqlite3_column_decltype 
> >> for
> >> the column.  The return value is NULL.  I must have the column data 
> >> type, else
> >> my code can't properly interpret the value.   This must be a bug, right?
> >
> > As far as I know, SQLite has to way of knowing what the column type
> > is in the case of a function:
> >
> >   "If the Nth column of the result set is not a table column,
> >   then a NULL pointer is returned."
> >
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_decltype
> >
> > You can interrogate the first row's column values with
> > sqlite3_column_type as a workaround (assuming the column in subsequent
> > rows are of the same type):
> >
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_type



   

Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for 
today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow  

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

Re: [sqlite] Is it a bug?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > 
> > As a possible extension one could see sqlite3_create_function taking an 
> > optional argument with a hint as to its return type that sqlite may use 
> > for sqlite3_column_decltype. But SQLite does not currently return any 
> > column types for any ad-hoc expression. So this would not be a trivial
> > change.
> > 
> 
> The code changes are not that complex.  The hard part is getting
> me to agree to such a change.  Surely by now you have come to
> better understand my views toward static typing

No doubt.

This issue comes up frequently when making database drivers for 
JDBC, ODBC, R, (you name the language) as they often expect static 
column types in result sets.  Yes, I've hacked my way around it, 
as has every other driver writer, but it would be nice if everyone 
did not have to reinvent the wheel each time. 

In a hypothetical implementation, one could preserve backwards 
API compatiblity by overloading the zFunctionName argument by
tacking on a TYPE suffix (i.e., "myfunction:TYPE") to convey the 
type being returned by the function.

 int sqlite3_create_function(
   sqlite3 *,
   const char *zFunctionName,
   int nArg,
   int eTextRep,
   void *pUserData,
   void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
   void (*xStep)(sqlite3_context*,int,sqlite3_value**),
   void (*xFinal)(sqlite3_context*)
 );

But it would probably be easier to convince all the other popular 
relational databases that they should change to exclusively use 
dynamic typing.
;-)


   

Moody friends. Drama queens. Your life? Nope! - their life, your story. Play 
Sims Stories at Yahoo! Games.
http://sims.yahoo.com/  

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

Re: [sqlite] Is it a bug?

[Joe Wilson]

--- Scott Hess <[EMAIL PROTECTED]> wrote:
> On 6/14/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > You can't infer a function's return type from its arguments.
> > Take the hypothetical function FOO(x). If I pass it a number, it will
> > return the number spelled out as TEXT, but if I pass it a BLOB it will
> > return its length*PI as a FLOAT.
> 
> Would it be reasonable to have:
> 
>   SELECT myfunc(x, y, z);
> 
> have an undefined return type, while:
> 
>   SELECT CAST(myfunc(x, y, z) AS INTEGER);
> 
> would return a well-defined return type?
> 
> After all, in SQLite you can't infer a column's type, either, you can
> only infer how the column is declared.  What it actually contains
> could be anything at all.

True enough, but in spite of my contrived FOO function, I'd think 
that 99% of functions would return one specific type or a NULL.
In the situations where this would not be the case, the CAST
operator, as you point out, would be a good way to override the
returned declared type.

I don't see this proposed type mechanism as being at odds with 
SQLite's typeless nature - it does not detract from it. It's not 
mandating policy - it's just suggesting a standard type reporting 
convention which you may elect to use or ignore.
It also would provide a facility to return column types for 
ad-hoc SELECT expressions that return no rows (i.e., you can't 
infer the column types from the first row because there are no
rows returned from the query).


   

Get the Yahoo! toolbar and be alerted to new email wherever you're surfing.
http://new.toolbar.yahoo.com/toolbar/features/mail/index.php

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

[sqlite] SQLite 3.X Database File Format ?

[Joe Wilson]

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?


   

Be a better Globetrotter. Get better travel answers from someone who knows. 
Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list=396545469

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

Re: [sqlite] Optimization of equality comparison when NULL involved

[Joe Wilson]

> select tableA.path, tableA.value from tableA,tableB where
> tableA.path=tableB.path and (tableA.value=tableB.value or
> (tableA.value IS NULL AND tableB.value IS NULL));
>
> It's possible that won't use an index, either, due to the OR, in which
> case you could try a union between a select with is-null only, and
> another with equality, something like:
> 
> select tableA.path, tableA.value from tableA,tableB where
> tableA.path=tableB.path and tableA.value=tableB.value union
> select tableA.path, tableA.value from tableA,tableB where
> tableA.path=tableB.path and tableA.value IS NULL AND tableB.value IS
> NULL;

Even though this query has no OR operation:

  select tableA.path, tableA.value from tableA,tableB where
  tableA.path=tableB.path and tableA.value=tableB.value 

it has to do a complete pass over one of the tables 
anyway since it does not have a constrained value (or at 
least a full pass over an index). So I think in this case 
the first query with the OR listed above is more efficient
since it would do just a single pass over one of the tables
instead of 2 passes (one for each SELECT in the UNION).

I've been thinking of heuristics to rewrite SELECTs with ORs
using UNIONs, which is pretty straightforward, but knowing 
when not to perform this optimization is the tricky part.


   

Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

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

Re: [sqlite] Version 3.4.0

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Gerry Snyder <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > > SQLite version 3.4.0 is now available for download
> > >   
> > 
> > The tcl bindings for windows appear to be missing. Is this deliberate?
> > 
> 
> Build-script bug.  Now fixed.  Try again, please.

Check-in [4086] http://www.sqlite.org/cvstrac/chngview?cn=4086 
does not make sense to me.

It now overwrites the previously created tclsqlite3.dll file in
the same script and produces no sqlite3.dll file.
It seemed to be correct before in cvs version 1.9.


   
Ready
 for the edge of your seat? 
Check out tonight's top picks on Yahoo! TV. 
http://tv.yahoo.com/

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

Re: [sqlite] Optimization of equality comparison when NULL involved

[Joe Wilson]

--- Sean Cunningham <[EMAIL PROTECTED]> wrote:
> I have very large datasets and have found that the built in union, 
> intersect, and except operations do not seem to use indices 
> (would be happy to be proven wrong here).   As  such, they
> are not very speedy with large large data sets.

A patch to speed up queries on a view (or a subquery) with 
compound selects:

  http://www.sqlite.org/cvstrac/tktview?tn=1924
  http://marc.info/?l=sqlite-users=117958960408282



   

Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

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

Re: [sqlite] Cache invalidation after insert statements.

[Joe Wilson]

Does every single process (however insignificant) that reads or writes 
to that sqlite database file run on the same 16 processor machine?

> 16 Processor machine
> ~40Gb ram
> EMC storage
> Running a huge Oracle 10G database
> Running a 3rd party application that generates HUGE IO. 
> Part of this 3rd party application is my application that does lookups.


  
___
You snooze, you lose. Get messages ASAP with AutoCheck
in the all-new Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_html.html

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

Re: [sqlite] The problem with index

[Joe Wilson]

--- "Sergey M. Brytsko" wrote:
> The problem is the index is NOT used for query:
> SELECT BBB FROM XXX WHERE BBB <> 100;
> 
> but in case of query
> SELECT BBB FROM XXX WHERE BBB > 100; 
> all is ok
...
> The indices are very important for me, how should I build these queries?

Say you have the following values for BBB:

1 2 3 10 20 30 50 70 80 80 90 100 101 110 110 120 120 150 190 200

How is an index going to help you with BBB <> 100 ?
You have to do a full table scan whether or not the column is indexed.



   

Be a better Globetrotter. Get better travel answers from someone who knows. 
Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list=396545469

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