Re: [sqlite] completion of sql words

2011-04-09 Thread Edzard Pasma
Here is the unbroken link: http://apidoc.apsw.googlecode.com/hg/shell.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug Report: Prepared statement doesn't drop previously not defined table

2011-04-09 Thread Robert Lehmkühler
Version: SQLite 3.7.5 OS: Windows 7 Professional / Ubuntu 10.10 Compiler: gcc 4.4.5 (Ubuntu/Linaro 4.4.4-14ubuntu5) / Visual Studio 2010 Version 10.0.30319.1 [cl.exe(Version 16.00.30319.01)] SQLite doesn't recognize the creation of a table for updating a prepared statement that could drop this ta

[sqlite] SQLite works strange

2011-04-09 Thread Vadim Smirnov
Hello! We have performance trouble with SQLite. We have created custom function MY_FUNC(a TEXT, b TEXT) and bind it with sqlite3_create_function. So, we have 2 tables: Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) 2 records Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records And SQL-query: SELE

[sqlite] Automatic index yields bad performance for a simple join

2011-04-09 Thread Martin Gadbois
Hi there! I noticed a heavy slow-down due to automatic indexes. Look at the following output, where table "events" has 100k entries, and "tags" has ~10 entries: SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .stats on sqlite> .timer on sqlit

Re: [sqlite] Automatic index yields bad performance for a simple join

2011-04-09 Thread Richard Hipp
On Fri, Apr 8, 2011 at 10:55 AM, Martin Gadbois wrote: > Hi there! > > I noticed a heavy slow-down due to automatic indexes. Look at the following > output, where table "events" has 100k entries, and "tags" has ~10 entries: > Thanks for the details in your trouble report. However, you left out

Re: [sqlite] How is the page-cache filled?

2011-04-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/08/2011 12:11 PM, Clemens Eisserer wrote: > * Which version of the source should I use? The amalgamized source > isn't really useful, however for all other source-archives its stated > its not recommended using. You should check out the code and

Re: [sqlite] Automatic index yields bad performance for a simple join

2011-04-09 Thread Martin Gadbois
On Sat, Apr 9, 2011 at 7:06 AM, Richard Hipp wrote: > > > On Fri, Apr 8, 2011 at 10:55 AM, Martin Gadbois wrote: > >> Hi there! >> >> I noticed a heavy slow-down due to automatic indexes. Look at the >> following >> output, where table "events" has 100k entries, and "tags" has ~10 entries: >> > >

[sqlite] Sqlite database corrupted

2011-04-09 Thread giorgi giorgi
Hi, I have developed an application which is using sqlite database for querying data from the database which ships with it. Several days ago the database somehow got corrupted. The application is only issuing select statements (no insert/update/delete or vacuum) so I cannot understand what could

Re: [sqlite] Sqlite database corrupted

2011-04-09 Thread Simon Slavin
On 9 Apr 2011, at 02:47 PM, giorgi giorgi wrote: > The application is only issuing select statements (no insert/update/delete > or vacuum) so I cannot understand what could have caused database > corruption. Almost definitely a problem with your hardware, or possibly an operating-system glitch.

Re: [sqlite] sqlite-users Digest, Vol 40, Issue 9

2011-04-09 Thread Pete
Yes, I already figured out what the problem was. I was asking for a solution. The SELECT scalar query gets me what I wanted. Pete Molly's Revenge > Message: 1 > Date: Fri, 8 Apr 2011 08:17:22 -0400 > From: "Igor Tandetnik" > Subject: Re: [sqlite] GROUP BY Prob

Re: [sqlite] SQLite works strange

2011-04-09 Thread Simon Slavin
On 8 Apr 2011, at 2:34pm, Vadim Smirnov wrote: > Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) 2 records > Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records > And SQL-query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN > Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1

Re: [sqlite] Automatic index yields bad performance for a simple join

2011-04-09 Thread Richard Hipp
On Sat, Apr 9, 2011 at 8:36 AM, Martin Gadbois wrote: > > If ANALYZE is ran, the speed is fast: there are no auto-index. > There you go. Without ANALYZE, the query planner has no idea what your database contains, and so it guesses that both the tags and events table hold 1,000,000 rows each. I

Re: [sqlite] SQLite works strange

2011-04-09 Thread Igor Tandetnik
Simon Slavin wrote: > On 8 Apr 2011, at 2:34pm, Vadim Smirnov wrote: > >> Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) 2 records >> Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records >> And SQL-query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN >> Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id

Re: [sqlite] SQLite works strange

2011-04-09 Thread Simon Slavin
On 9 Apr 2011, at 8:18pm, Igor Tandetnik wrote: > Simon Slavin wrote: >> On 8 Apr 2011, at 2:34pm, Vadim Smirnov wrote: >> >>> Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) 2 records >>> Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records >>> And SQL-query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, T

Re: [sqlite] completion of sql words

2011-04-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/08/2011 02:40 PM, Edzard Pasma wrote: > Just found that the APSW shell does tabcomplete (and even for > tablenames). It is described here: > > http://apidoc.apsw.googlecode.com/hg/shell.html (I'm the APSW author). It also does output colour

Re: [sqlite] completion of sql words

2011-04-09 Thread Edzard Pasma
Op 9-apr-2011, om 21:29 heeft Roger Binns het volgende geschreven: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 04/08/2011 02:40 PM, Edzard Pasma wrote: >> Just found that the APSW shell does tabcomplete (and even for >> tablenames). It is described here: >> >> http://apidoc.apsw.go

Re: [sqlite] completion of sql words

2011-04-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/09/2011 02:03 PM, Edzard Pasma wrote: > I just made an alias in the shell (Unix-) for this tool: > > alias apsw='python -c "import apsw;apsw.main()"' I keep trying to make it so you can do "python -m apsw " and the code is structured to all

Re: [sqlite] Automatic index yields bad performance for a simple join

2011-04-09 Thread Martin Gadbois
On Sat, Apr 9, 2011 at 2:49 PM, Richard Hipp wrote: > > > On Sat, Apr 9, 2011 at 8:36 AM, Martin Gadbois wrote: > >> >> If ANALYZE is ran, the speed is fast: there are no auto-index. >> > > There you go. > > Without ANALYZE, the query planner has no idea what your database contains, > and so it

[sqlite] Sqlite3_step

2011-04-09 Thread Guilherme Batista
Hi, since sqlite does a nested loop to evaluate queries, is it possible to know how many interactions have each loop, i.e. how many rows in the table that loop will interact, and which table is that? And how can I use the WHERETRACE function that prints the optimization steps? Thanks! -- Att.

[sqlite] Can't load sqlite 3.75 under tcl 8.4.4.0

2011-04-09 Thread thoselaings
% package require sqlite couldn't load library "C:/Tcl/lib/sqlite3/sqlite3/.dll": this library or a dependent library could not be found in library path What is sqlite3/.dll ? Where did that "/" come from? The pkgindex file contains - package ifneeded sqlite 3.7.5 [list load [file join $dir \