Re: [sqlite] Comparison of SQLite applications for Mac

[Dennis Cote]

BareFeet wrote:
> 2. Know of another application that should be included.
>
>   

You may want to include the free SQLite Manager add on for Firefox. See  
https://addons.mozilla.org/en-US/firefox/addon/5817 for additional 
information.

It provides a general database browser and editor that works on Mac OS 
as well.

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

Re: [sqlite] SQLITE_LOCKED behavior

[Shawn Wilsher]

>  Did you call sqlite3_reset() before each retry?
Doing this fixed the issue.  Thanks!

Cheers,

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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[Steve Topov]

I am on Windows XP using sqlite 3.5.6 dll created from the source. I
debugged the code - Sqlite3_open returns OK but any other function after
that returns 1.
For example sqlite3_prepare returns 1 when I do "Select * from
sqlite_master". Same for sqlite3_execute. Tried different SQL statements
- same error.
If I switch to 3.3.5 dll the problem goes away.

Regards.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Monday, April 14, 2008 4:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file


On Apr 14, 2008, at 4:30 PM, Steve Topov wrote:
> I already reported this bug but did not receive any responds so I am
> trying one more time.
> The problem:
> SQLite version 3.5.6 and up returns an error when opening some  
> database
> files. The error is “SQL logic error or missing database”.
> Version prior to version 3.5.6 works fine with these database files.
>
> I do not know the version of SQLite the database file in question was
> created with. And I am talking about SQLite 3 database file – not  
> SQLite
> 2, not Microsoft Access,
> not any other file format.
>
> To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
>
>

I'm working with the file named "dpdshop.sl3" that you sent me,
5444608 bytes in size.  MD5 checksum 9fa76c1610a5e7c826d2745191e7a401
I can open and read this file without problems on MacOSX using
SQLite version 3.4.0, 3.5.6, 3.5.7 and the latest in CVS.  I ran

PRAGMA integrity_check

using all four versions of SQLite and all return "ok".


D. Richard Hipp
[EMAIL PROTECTED]



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


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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[D. Richard Hipp]

On Apr 14, 2008, at 4:30 PM, Steve Topov wrote:
> I already reported this bug but did not receive any responds so I am
> trying one more time.
> The problem:
> SQLite version 3.5.6 and up returns an error when opening some  
> database
> files. The error is “SQL logic error or missing database”.
> Version prior to version 3.5.6 works fine with these database files.
>
> I do not know the version of SQLite the database file in question was
> created with. And I am talking about SQLite 3 database file – not  
> SQLite
> 2, not Microsoft Access,
> not any other file format.
>
> To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
>
>

I'm working with the file named "dpdshop.sl3" that you sent me,
5444608 bytes in size.  MD5 checksum 9fa76c1610a5e7c826d2745191e7a401
I can open and read this file without problems on MacOSX using
SQLite version 3.4.0, 3.5.6, 3.5.7 and the latest in CVS.  I ran

PRAGMA integrity_check

using all four versions of SQLite and all return "ok".


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[P Kishor]

On 4/14/08, Steve Topov <[EMAIL PROTECTED]> wrote:
> I do not have sqlite3 command line tool. I work with dlls. If you email
>  me sqlite3 I will create a dump.

http://www.sqlite.org/sqlite-3_5_7.zip


>  Compressed database file is 1.8 Meg.
>
>
>  -Original Message-
>  From: [EMAIL PROTECTED]
>
> [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
>  Sent: Monday, April 14, 2008 1:59 PM
>  To: General Discussion of SQLite Database
>
> Cc: [EMAIL PROTECTED]
>  Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file
>
>
>
> On Apr 14, 2008, at 4:53 PM, D. Richard Hipp wrote:
>  >
>  > On Apr 14, 2008, at 4:47 PM, Steve Topov wrote:
>  >> I can open the file with 3.3.5. I did not try to dump it and recreate
>  >> with 3.5.6.
>  >>
>  >
>  > With version 3.5.5, please do this:
>  >
>  >  sqlite3  yourdatabasefile.db .schema >schema.txt
>  >
>  > then email me schema.txt file.
>  >
>
>  If the database file is small enough, please send me the whole thing.
>
>  D. Richard Hipp
>  [EMAIL PROTECTED]
>
>
>
>
> ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite>=3.5.6 does not open database file

[Steve Topov]

I do not have sqlite3 command line tool. I work with dlls. If you email
me sqlite3 I will create a dump.
Compressed database file is 1.8 Meg.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Monday, April 14, 2008 1:59 PM
To: General Discussion of SQLite Database
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file


On Apr 14, 2008, at 4:53 PM, D. Richard Hipp wrote:
>
> On Apr 14, 2008, at 4:47 PM, Steve Topov wrote:
>> I can open the file with 3.3.5. I did not try to dump it and recreate
>> with 3.5.6.
>>
>
> With version 3.5.5, please do this:
>
>  sqlite3  yourdatabasefile.db .schema >schema.txt
>
> then email me schema.txt file.
>

If the database file is small enough, please send me the whole thing.

D. Richard Hipp
[EMAIL PROTECTED]



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


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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[D. Richard Hipp]

On Apr 14, 2008, at 6:58 PM, Steve Topov wrote:
> 1.8 Meg
>

email it to me, please.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[Steve Topov]

1.8 Meg

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Monday, April 14, 2008 1:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file

Steve Topov wrote:
> I already reported this bug but did not receive any responds so I am
> trying one more time.
> The problem:
> SQLite version 3.5.6 and up returns an error when opening some
database
> files. The error is “SQL logic error or missing database”.
> Version prior to version 3.5.6 works fine with these database files.
>  
> I do not know the version of SQLite the database file in question was
> created with. And I am talking about SQLite 3 database file – not
SQLite
> 2, not Microsoft Access,
> not any other file format.
>  
> To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
>  
> Thanks for your time and possible help.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

How large is a zipped (or better yet a 7z compressed) copy of the 
problematic database file?

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


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

[sqlite] delete rows but the database files still have the big size

[Joanne Pham]

Hi All,
I have used sqlite3 command to clean up ton of rows in the table by executing 
the following command
sqlite3> delete from compressTable where appId > 10;

The command above has deleted ~ 99% of the table and now this table has ~ 100 
rows but the database file size is the same as before the deletion.
Do I miss some commands here? I thought the database file size shoud get much 
smaller after the delete operation but it isn't.
Can you please help to let me know how to get the database file szie smaller.
I have tried "VACUUM" but the file's size didn't change.
Thanks,
JL


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Populating and scrolling the Listbox using query

[Clay Dowling]

Farzana wrote:
> Dear All,
> 
> We are working in eVC++ environment with SQLite database.We need to populate
> the listbox with the values obtained by executing the query.
> We were able to get the values of the query by using the API’s
> sqlite3_prepare and sqlite3_step.
> But we were able to populate and move the listbox in the downward direction
> only and we couldn’t get the correct result when we click the upward
> direction button of the listbox.Is there any API or functions available to
> move the data in upward direction in the listbox?
> 
> We happened to come across the functions of BTree such as
> sqlite3BtreePrevious, sqlite3BtreeNext where we are suppose to use cursors.
> Is it possible to execute the query using cursors and move the pointer in
> the upward or downward direction?

Farzana,

Moving the list control has nothing to do with SQLite, that's a function
of your interface code.  I have a couple of solutions that I use for
dealing with grids and list controls in general.

The first is to maintain a shadow store behind the scenes with the data
from my database records.  In particular, I define a class to hold a
record.  My screen has a vector of these classes.  So when the user
makes changes to row 3 of the list control, behind the scenes I make
those changes to item 3 of the vector.  In fact I find this easiest to
do by deriving a specialized list control that deals with this
particular view on the data.

Another approach is to record the primary key with each row of the list
control.  This works fine if you don't allow in-place editing, and the
users will only be adding or deleting records (or their edits occur on a
different screen).

Clay

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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[D. Richard Hipp]

On Apr 14, 2008, at 4:53 PM, D. Richard Hipp wrote:
>
> On Apr 14, 2008, at 4:47 PM, Steve Topov wrote:
>> I can open the file with 3.3.5. I did not try to dump it and recreate
>> with 3.5.6.
>>
>
> With version 3.5.5, please do this:
>
>  sqlite3  yourdatabasefile.db .schema >schema.txt
>
> then email me schema.txt file.
>

If the database file is small enough, please send me the whole thing.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[D. Richard Hipp]

On Apr 14, 2008, at 4:47 PM, Steve Topov wrote:
> I can open the file with 3.3.5. I did not try to dump it and recreate
> with 3.5.6.
>

With version 3.5.5, please do this:

  sqlite3  yourdatabasefile.db .schema >schema.txt

then email me schema.txt file.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[P Kishor]

On 4/14/08, Steve Topov <[EMAIL PROTECTED]> wrote:
> I can open the file with 3.3.5. I did not try to dump it and recreate
>  with 3.5.6.
>

well then. Do it and see if it works.


>
>  -Original Message-
>  From: [EMAIL PROTECTED]
>
> [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
>  Sent: Monday, April 14, 2008 1:38 PM
>  To: General Discussion of SQLite Database
>  Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file
>
>
> On 4/14/08, Steve Topov <[EMAIL PROTECTED]> wrote:
>  > I already reported this bug but did not receive any responds so I am
>  >  trying one more time.
>  >  The problem:
>  >  SQLite version 3.5.6 and up returns an error when opening some
>  database
>  >  files. The error is "SQL logic error or missing database".
>  >  Version prior to version 3.5.6 works fine with these database files.
>
>  Which highest version of SQLite can you open the db with? That said,
>  have you tried dumping the db and building a 3.5.6 db with it?
>
>  >
>  >  I do not know the version of SQLite the database file in question was
>  >  created with. And I am talking about SQLite 3 database file – not
>  SQLite
>  >  2, not Microsoft Access,
>  >  not any other file format.
>  >
>  >  To make story short: SQLite 3.5.6 can not open SQLite 3 database
>  file.
>  >
>  >  Thanks for your time and possible help.
>  >  ___
>  >  sqlite-users mailing list
>  >  sqlite-users@sqlite.org
>  >  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>
>
>  --
>  Puneet Kishor http://punkish.eidesis.org/
>  Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>  Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite>=3.5.6 does not open database file

[Dennis Cote]

Steve Topov wrote:
> I already reported this bug but did not receive any responds so I am
> trying one more time.
> The problem:
> SQLite version 3.5.6 and up returns an error when opening some database
> files. The error is “SQL logic error or missing database”.
> Version prior to version 3.5.6 works fine with these database files.
>  
> I do not know the version of SQLite the database file in question was
> created with. And I am talking about SQLite 3 database file – not SQLite
> 2, not Microsoft Access,
> not any other file format.
>  
> To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
>  
> Thanks for your time and possible help.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

How large is a zipped (or better yet a 7z compressed) copy of the 
problematic database file?

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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[Steve Topov]

I can open the file with 3.3.5. I did not try to dump it and recreate
with 3.5.6.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: Monday, April 14, 2008 1:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file

On 4/14/08, Steve Topov <[EMAIL PROTECTED]> wrote:
> I already reported this bug but did not receive any responds so I am
>  trying one more time.
>  The problem:
>  SQLite version 3.5.6 and up returns an error when opening some
database
>  files. The error is "SQL logic error or missing database".
>  Version prior to version 3.5.6 works fine with these database files.

Which highest version of SQLite can you open the db with? That said,
have you tried dumping the db and building a 3.5.6 db with it?

>
>  I do not know the version of SQLite the database file in question was
>  created with. And I am talking about SQLite 3 database file – not
SQLite
>  2, not Microsoft Access,
>  not any other file format.
>
>  To make story short: SQLite 3.5.6 can not open SQLite 3 database
file.
>
>  Thanks for your time and possible help.
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[Steve Topov]

I can open this file with version 3.3.5. I do not know the version  this
file was created with but I can guess it was some earliest versions of
SQLite 3. File created with version 3.3.5 works fine with version 3.5.6.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Michael Ruck
Sent: Monday, April 14, 2008 1:34 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file

Is there a previous version, which is able to open this database file?
Which
version of SQLite was the database file created with? 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Steve Topov
Gesendet: Montag, 14. April 2008 22:31
An: sqlite-users@sqlite.org
Betreff: [sqlite] SQLite>=3.5.6 does not open database file

I already reported this bug but did not receive any responds so I am
trying
one more time.
The problem:
SQLite version 3.5.6 and up returns an error when opening some database
files. The error is “SQL logic error or missing database”.
Version prior to version 3.5.6 works fine with these database files.
 
I do not know the version of SQLite the database file in question was
created with. And I am talking about SQLite 3 database file – not SQLite
2,
not Microsoft Access, not any other file format.
 
To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
 
Thanks for your time and possible help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


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

Re: [sqlite] SQLite>=3.5.6 does not open database file

[P Kishor]

On 4/14/08, Steve Topov <[EMAIL PROTECTED]> wrote:
> I already reported this bug but did not receive any responds so I am
>  trying one more time.
>  The problem:
>  SQLite version 3.5.6 and up returns an error when opening some database
>  files. The error is "SQL logic error or missing database".
>  Version prior to version 3.5.6 works fine with these database files.

Which highest version of SQLite can you open the db with? That said,
have you tried dumping the db and building a 3.5.6 db with it?

>
>  I do not know the version of SQLite the database file in question was
>  created with. And I am talking about SQLite 3 database file – not SQLite
>  2, not Microsoft Access,
>  not any other file format.
>
>  To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
>
>  Thanks for your time and possible help.
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite>=3.5.6 does not open database file

[Michael Ruck]

Is there a previous version, which is able to open this database file? Which
version of SQLite was the database file created with? 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Steve Topov
Gesendet: Montag, 14. April 2008 22:31
An: sqlite-users@sqlite.org
Betreff: [sqlite] SQLite>=3.5.6 does not open database file

I already reported this bug but did not receive any responds so I am trying
one more time.
The problem:
SQLite version 3.5.6 and up returns an error when opening some database
files. The error is “SQL logic error or missing database”.
Version prior to version 3.5.6 works fine with these database files.
 
I do not know the version of SQLite the database file in question was
created with. And I am talking about SQLite 3 database file – not SQLite 2,
not Microsoft Access, not any other file format.
 
To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
 
Thanks for your time and possible help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] SQLite>=3.5.6 does not open database file

[Steve Topov]

I already reported this bug but did not receive any responds so I am
trying one more time.
The problem:
SQLite version 3.5.6 and up returns an error when opening some database
files. The error is “SQL logic error or missing database”.
Version prior to version 3.5.6 works fine with these database files.
 
I do not know the version of SQLite the database file in question was
created with. And I am talking about SQLite 3 database file – not SQLite
2, not Microsoft Access,
not any other file format.
 
To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
 
Thanks for your time and possible help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[John Stanton]

You can make the timestamps unique by appending a sequence number.  A 32 
bit Unix timestamp can map into a 64 bit Sqlite integer.

P Kishor wrote:
> On 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote:
>> Err,
>>
>> Make that example table data:
>>
>>  <...>
>>
>> 1167611400|89|7|34
>>  1167611400|101|5|4
>>  1167611400|147|9|14
>>  1167611400|173|7|2
>>  1167611400|195|8|1
>>  1167611400|314|8|12
>>  1167611400|347|6|48
>>  1167611400|364|1|49
>>
>> 
>>
>> 1167615600|111|7|39
>>  1167615600|155|2|8
>>  1167615600|157|4|7
>>  1167615600|220|4|47
>>  1167615600|247|7|21
>>  1167615600|261|8|30
>>  1167615600|308|9|20
>>
>> <...>
>>
>> As I'd originally said, the timestamps are inserted in ascending
>>  order.  (I just screwed up pasting my example values in the wrong order)
>>
>>
> 
> 
> ok, but first, I don't really understand the following --
> 
>> SELECT * from bridge_table WHERE timestamp = ;
>>
>> and almost immediately get back all the results I want, since they're
>> right next to each other in the db.  However, the actual SELECT doesn't
>> return for almost 13 additional seconds, as the entire table is scanned
>> for other rows where timestamp might be 
> 
> Your first sentence above is that you "almost immediately get back all
> the results you want" then you go on to say that the "actual SELECT
> doesn't return for almost 13 additional seconds." So, what is it
> exactly that you "almost immediately get back"?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Implicit INDEX?

[RB Smissaert]

Have tested this now on a table of some 30 rows (no indexes at all)
and with 100 rows to find in the middle of the table, sorted asc on
time-stamp field.
It gave me a speed increase of about 25%.
If I looked for rows at the beginning of the table the speed increase was
more, some 50% faster.
If I looked for rows at the end (highest timestamp) then the simple select
was quite a lot faster than the one with limit etc.

Maybe not the kind of increase you were interested in, but still something
and with no extra overhead at all, just a different SQL. Probably only worth
it if looking for early times.

RBS


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: 14 April 2008 19:11
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Implicit INDEX?

How about this:

select
*
from
table1
where
rowid >=
(select
rowid
from
table1
where
time_stamp = xxx
limit 1)
and
rowid <
(select
rowid
from
table1
where
time_stamp > xxx
limit 1)

RBS


> Donald,
>
>> To test this, I think you'd want to create a select for some of the most
>> recent data (i.e. data at the tail of the database), perhaps after
>> clearing cache.  I suspect this will take the full table scan time to
>> return any values.
>
>  I'd actually just thought of that.  All my test SELECTs happened
> to be using data close to the "front" of the file.  As suspected, if I try
> it on "later" data, I have to wait for the table scan, then I get the
> data.  I'd be erroneously assuming that SQLite was somehow searching for
> the first value quickly, then scanning the table.
>
>> Two thoughts:
>>
>>   1).  Easy.
>> How bad is the extra 0.8 GByte cost of the index?  At today's prices,
>> it's only about 20 cents on ordinary 5" drives.  (but maybe you're
>> programming a cellphone)
>
>  No, it's nothing terribly constrained.  I'm just trying to
> understand the mechanisms and do what I can to keep the size down where
> possible.  I was somewhat surprised to find that adding an index on a
> single INTEGER column nearly doubled the size of the database and wanted
> to figure out if there was a way around it, given that the column will
> always be sorted.  (And given my, perhaps erroneous understanding that
> creating an INDEX just makes sure that the column stays sorted so SQLite
> can search through it more intelligently)
>
>>   2).  Fancy.
>> You could create your own sparse index table mapping a ROWID to say,
>> every thousandth timestamp.  Then you could create upper and lower ROWID
>> bounds on any query based on timestamps.  Maybe you import the records
>> in batches already and can create the new table at the same time.
>
>  This is closer to what I'm probably going to do.  The data gets
> pulled in every 5 minutes, but between runs, very little actually changes.
> So the idea is to store only the changes along with a full dump say once
> or twice every day.  Then I can just query the values from  time> to  and compute the state of everything from
> that data.
>
>  Thanks,
>
>   Chris
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>



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


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

Re: [sqlite] SQLITE_LOCKED behavior

[Shawn Wilsher]

>  Did you call sqlite3_reset() before each retry?
Ah, I didn't realize I'd have to do that.  When I get SQLITE_BUSY
returned, I can just retry it, so I made the wrong assumption that
that would work in this case as well.

Cheers,

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

Re: [sqlite] Table modifications and analyze

[D. Richard Hipp]

On Apr 14, 2008, at 2:49 PM, Petite Abeille wrote:
> Hello,
>
> What heuristics do people use to determine the frequency for analyzing
> their indices?
>

I use the simple heuristic of never running ANALYZE.  The ANALYZE  
command
is only helpful to the optimizer in certain extreme cases.  See, the  
discussion at

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

If you have an application where SQLite chooses a bad query plan by  
default,
you can probably get by with running ANALYZE just once on a typical  
database,
then copying the resulting sqlite_stat1 table into every new database  
you create
with the same schema.

Some database engines benefit from running ANALYZE periodically.  SQLite
does not, in general.

D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] Table modifications and analyze

[Petite Abeille]

Hello,

What heuristics do people use to determine the frequency for analyzing  
their indices?

Is there something equivalent to user_tab_modifications that keep  
tracks of the number of inserts, updates and deletes for each table?

Thanks in advance.

Kind regards,

--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_LOCKED behavior

[D. Richard Hipp]

On Apr 14, 2008, at 2:44 PM, Shawn Wilsher wrote:
>> I am not aware of any reason why you cannot retry an SQLITE_LOCKED
>> error after a delay, however.  Have you actually tried doing that?
>> Is it giving you trouble?
> Attempting to retry after it being issues results in SQLITE_MISUSE
> being returned.
>

Did you call sqlite3_reset() before each retry?

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] SQLITE_LOCKED behavior

[Shawn Wilsher]

>  I am not aware of any reason why you cannot retry an SQLITE_LOCKED
>  error after a delay, however.  Have you actually tried doing that?
>  Is it giving you trouble?
Attempting to retry after it being issues results in SQLITE_MISUSE
being returned.

Cheers,

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

Re: [sqlite] Looking for a consultant to write a sqlite (not mysql-->typo) extended function

[Dewey Gaedcke]

Very sorry for the typo-we need a sqlite extentionNOT mysql

Subject: Looking for a consultant to write a mysql extended function

If you are interested, please email directly to [EMAIL PROTECTED]

Please don't cc the list

Regards,
Dewey

No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.519 / Virus Database: 269.22.13/1377 - Release Date: 4/14/2008
9:26 AM
 

No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.519 / Virus Database: 269.22.13/1377 - Release Date: 4/14/2008
9:26 AM
 

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

Re: [sqlite] Implicit INDEX?

[Ken]

Does sqlite have support for index based tables as a storage means?

This is an excelent case for  index based storage.

just my .02
Ken


Dennis Cote <[EMAIL PROTECTED]> wrote: Jeffrey Rennie wrote:
> On Mon, Apr 14, 2008 at 9:48 AM, Chris Tracy  wrote:
> 
  inserted into the database in ascending order, and where there may be
>> as
  many as 500 hID entries for each timestamp.  After a while, this table
>>> Have you considered making timestamp a PRIMARY KEY?
>>>
>>> So, declare it as INTEGER PRIMARY KEY NOT NULL
>>Unfortunately, the timestamps aren't unique, so I can't use
>> PRIMARY KEY to solve the problem.  (Each run generates as many as 500
>> entries, all with the same timestamp)
>>
> 
> Are there ever identical rows?  If not, just make the whole row a primary
> key:
> 
> CREATE TABLE bridge_table (
> timestamp INTEGER NOT NULL,
> hID INTEGER NOT NULL,
> sID INTEGER NOT NULL,
> pID INTEGER NOT NULL,
> *PRIMARY KEY (timestamp, hID, sID, pID)
> *);

This is a good suggestion. Even if there are identical rows, you can 
still use and index on all columns, without making it an primary key 
(i.e. unique index).

CREATE TABLE bridge_table (
 timestamp INTEGER NOT NULL,
 hID INTEGER NOT NULL,
 sID INTEGER NOT NULL,
 pID INTEGER NOT NULL
);
CREATE INDEX bt_all ON bridge_table(timestamp, hID, sID, pID);

This will double the size of the database file and double the row insert 
time, but SQLite has an optimization that allows it to use the columns 
stored in the index to supply the results of a query without looking at 
the main table. In effect this index becomes your main table because it 
stores all the data.

HTH
Dennis Cote



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

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

Re: [sqlite] Implicit INDEX?

[P Kishor]

On 4/14/08, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Jeffrey Rennie wrote:
>  > On Mon, Apr 14, 2008 at 9:48 AM, Chris Tracy <[EMAIL PROTECTED]> wrote:
>  >
>    inserted into the database in ascending order, and where there may be
>  >> as
>    many as 500 hID entries for each timestamp.  After a while, this table
>  >>> Have you considered making timestamp a PRIMARY KEY?
>  >>>
>  >>> So, declare it as INTEGER PRIMARY KEY NOT NULL
>  >>Unfortunately, the timestamps aren't unique, so I can't use
>  >> PRIMARY KEY to solve the problem.  (Each run generates as many as 500
>  >> entries, all with the same timestamp)
>  >>
>  >
>  > Are there ever identical rows?  If not, just make the whole row a primary
>  > key:
>  >
>  > CREATE TABLE bridge_table (
>  > timestamp INTEGER NOT NULL,
>  > hID INTEGER NOT NULL,
>  > sID INTEGER NOT NULL,
>  > pID INTEGER NOT NULL,
>  > *PRIMARY KEY (timestamp, hID, sID, pID)
>  > *);
>
>
> This is a good suggestion.

ummm no. The OP very clearly states that an INDEX is out of question
because of space constraints.


> Even if there are identical rows, you can
>  still use and index on all columns, without making it an primary key
>  (i.e. unique index).
>
>
>  CREATE TABLE bridge_table (
>  timestamp INTEGER NOT NULL,
>  hID INTEGER NOT NULL,
>  sID INTEGER NOT NULL,
>  pID INTEGER NOT NULL
>
> );
>  CREATE INDEX bt_all ON bridge_table(timestamp, hID, sID, pID);
>
>  This will double the size of the database file and double the row insert
>  time, but SQLite has an optimization that allows it to use the columns
>  stored in the index to supply the results of a query without looking at
>  the main table. In effect this index becomes your main table because it
>  stores all the data.
>
>  HTH
>
> Dennis Cote
>
>
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[Dennis Cote]

Jeffrey Rennie wrote:
> On Mon, Apr 14, 2008 at 9:48 AM, Chris Tracy <[EMAIL PROTECTED]> wrote:
> 
  inserted into the database in ascending order, and where there may be
>> as
  many as 500 hID entries for each timestamp.  After a while, this table
>>> Have you considered making timestamp a PRIMARY KEY?
>>>
>>> So, declare it as INTEGER PRIMARY KEY NOT NULL
>>Unfortunately, the timestamps aren't unique, so I can't use
>> PRIMARY KEY to solve the problem.  (Each run generates as many as 500
>> entries, all with the same timestamp)
>>
> 
> Are there ever identical rows?  If not, just make the whole row a primary
> key:
> 
> CREATE TABLE bridge_table (
> timestamp INTEGER NOT NULL,
> hID INTEGER NOT NULL,
> sID INTEGER NOT NULL,
> pID INTEGER NOT NULL,
> *PRIMARY KEY (timestamp, hID, sID, pID)
> *);

This is a good suggestion. Even if there are identical rows, you can 
still use and index on all columns, without making it an primary key 
(i.e. unique index).

CREATE TABLE bridge_table (
 timestamp INTEGER NOT NULL,
 hID INTEGER NOT NULL,
 sID INTEGER NOT NULL,
 pID INTEGER NOT NULL
);
CREATE INDEX bt_all ON bridge_table(timestamp, hID, sID, pID);

This will double the size of the database file and double the row insert 
time, but SQLite has an optimization that allows it to use the columns 
stored in the index to supply the results of a query without looking at 
the main table. In effect this index becomes your main table because it 
stores all the data.

HTH
Dennis Cote



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

Re: [sqlite] SQLITE_LOCKED behavior

[D . Richard Hipp]

On Apr 14, 2008, at 12:57 PM, Shawn Wilsher wrote:
>> Are you using a shared cache?  You can get also get SQLITE_LOCKED  
>> when
>> using a shared cache.  See section 2.2 of
>> .
>>
>> I've not used a shared cache myself.  One day I was wondering if I
>> needed to worry about handling SQLITE_LOCKED errors and I came across
>> that page.  Are these the only times you can get SQLITE_LOCKED  
>> errors?
> Ah-ha!  We are in fact using the shared cache, which probably explains
> this.  Any reason why SQLITE_LOCKED is returned instead of
> SQLITE_BUSY?  With SQLITE_BUSY you can keep retrying until you decide
> to give up, or until it works, but you can't do that with
> SQLITE_LOCKED.
>

You can disable much of the SQLITE_LOCKED behavior using

PRAGMA read_uncommitted=ON;

In that case one thread will be able to read uncommited
changes made by a second thread.  Turning on uncommitted
read will prevent writer and readers from blocking one another.
But you still won't be able to have two or more connections writing
at the same time.  Nor will you be able to DROP a table out from
under a reader.

I am not aware of any reason why you cannot retry an SQLITE_LOCKED
error after a delay, however.  Have you actually tried doing that?
Is it giving you trouble?


D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] Connection Sharing across threads

[Shailesh Birari]

In one of the wiki entries,
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
 , I read that
application cannot share the sqlite connection across threads. However
in another posting at http://www.sqlite.org/sharedcache.html
  I read that with 3.5.0 update,
the connection can be shared. 

>>>

In version 3.5.0, shared-cache mode was modified so that the same cache
can be shared across an entire process rather than just within a single
thread. Prior to this change, there were restrictions on passing
database connections between threads. Those restrictions were dropped in
3.5.0 update. This document describes shared-cache mode as of version
3.5.0.

>>>

Is the sharing of connection possible even when the shared cache mode is
disabled? or is it possible only when shared cache is enablesd?

 

Kindly let me know 

 

regards

Shailesh

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

Re: [sqlite] Implicit INDEX?

[Jeffrey Rennie]

On Mon, Apr 14, 2008 at 9:48 AM, Chris Tracy <[EMAIL PROTECTED]> wrote:

> >>  inserted into the database in ascending order, and where there may be
> as
> >>  many as 500 hID entries for each timestamp.  After a while, this table
>
> > Have you considered making timestamp a PRIMARY KEY?
> >
> > So, declare it as INTEGER PRIMARY KEY NOT NULL
>
>Unfortunately, the timestamps aren't unique, so I can't use
> PRIMARY KEY to solve the problem.  (Each run generates as many as 500
> entries, all with the same timestamp)
>

Are there ever identical rows?  If not, just make the whole row a primary
key:

CREATE TABLE bridge_table (
timestamp INTEGER NOT NULL,
hID INTEGER NOT NULL,
sID INTEGER NOT NULL,
pID INTEGER NOT NULL,
*PRIMARY KEY (timestamp, hID, sID, pID)
*);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Looking for a consultant to write a mysql extended function

[Dewey Gaedcke]

If you are interested, please email directly to [EMAIL PROTECTED]

Please don't cc the list

Regards,
Dewey

No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.519 / Virus Database: 269.22.13/1377 - Release Date: 4/14/2008
9:26 AM
 

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

Re: [sqlite] want to purchase

[Rich Shepard]

On Mon, 14 Apr 2008, Arvind M wrote:

>  i want to purchase of sqlite server kinly give me guidance

   It comes bundled with a bridge in New York City. But, if you don't want
the bridge, just download the software and use it. Read the license, ask
questions on what you don't understand.

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[P Kishor]

On 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote:
> >  If you need to do this from the shell, I'm not sure there is anything
>  >  you can do.  If you're doing this from the C API just look for timestamps
>  >  that are >= your target and terminate the query as soon as you find one
>  >  larger than the target timestamp (e.g. stop calling step() and call
>  >  reset() on the statement).  If the number of datapoints at a given
>  >  timestamp is known (because it is fixed, or because you stashed that
>  >  value in another table), you could also use a LIMIT clause.  That has
>  >  the added bonus of doing the right thing (even if it takes longer) if
>  >  the rows somehow get out-of-order.
>
>
> I'm actually using Perl's DBI interface.  Not sure that I can
>  easily do the exit when seeing a timestamp one bigger than the one I'm
>  after, but it's an intriguing idea I'd not considered before.  The LIMIT
>  I'd also considered, but since the data is variable, I'd abandoned it.
>  Hadn't considered storing the value in another table.  But since the whole
>  thing was based on an invalid premise (that SQLite was somehow magically
>  finding the initial value quickly, ignoring that it just happened to be
>  closer to the start of the db) I think I'll take another approach to
>  solving the problem.
>

heck, I didn't realize you were using Perl (I thought you were working
in some highly constrained environment with 2 bytes of memory, like
the Mars rover or something).

Just exit the loop.

my $timestamp = 1167615600;
while ($ary_ref = $sth->fetch) {
  last if $ary_ref->[0] > $timestamp;

  do other stuff...
}

what's the problem? It should be blindingly fast.


> Thanks for the input though,
>
>
> Chris
>
> ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[David Bicking]

Can you create a second table holding the minimum rowid and maximum rowid from 
your main data table. You query this table, get the range or rowid, then you 
can query the main table WHERE rowid >= MinRowid AND rowid <= MaxRowid. That 
would than use the built in primary index.

David


--- On Mon, 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote:

> From: Chris Tracy <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] Implicit INDEX?
> To: "General Discussion of SQLite Database" 
> Date: Monday, April 14, 2008, 1:35 PM
> >  If you need to do this from the shell, I'm not
> sure there is anything
> >  you can do.  If you're doing this from the C API
> just look for timestamps
> >  that are >= your target and terminate the query as
> soon as you find one
> >  larger than the target timestamp (e.g. stop calling
> step() and call
> >  reset() on the statement).  If the number of
> datapoints at a given
> >  timestamp is known (because it is fixed, or because
> you stashed that
> >  value in another table), you could also use a LIMIT
> clause.  That has
> >  the added bonus of doing the right thing (even if it
> takes longer) if
> >  the rows somehow get out-of-order.
> 
>   I'm actually using Perl's DBI interface.  Not
> sure that I can 
> easily do the exit when seeing a timestamp one bigger than
> the one I'm 
> after, but it's an intriguing idea I'd not
> considered before.  The LIMIT 
> I'd also considered, but since the data is variable,
> I'd abandoned it. 
> Hadn't considered storing the value in another table. 
> But since the whole 
> thing was based on an invalid premise (that SQLite was
> somehow magically 
> finding the initial value quickly, ignoring that it just
> happened to be 
> closer to the start of the db) I think I'll take
> another approach to 
> solving the problem.
> 
>   Thanks for the input though,
> 
>   Chris
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[Chris Tracy]

Donald,

> To test this, I think you'd want to create a select for some of the most
> recent data (i.e. data at the tail of the database), perhaps after
> clearing cache.  I suspect this will take the full table scan time to
> return any values.

 I'd actually just thought of that.  All my test SELECTs happened 
to be using data close to the "front" of the file.  As suspected, if I try 
it on "later" data, I have to wait for the table scan, then I get the 
data.  I'd be erroneously assuming that SQLite was somehow searching for 
the first value quickly, then scanning the table.

> Two thoughts:
>
>   1).  Easy.
> How bad is the extra 0.8 GByte cost of the index?  At today's prices,
> it's only about 20 cents on ordinary 5" drives.  (but maybe you're
> programming a cellphone)

 No, it's nothing terribly constrained.  I'm just trying to 
understand the mechanisms and do what I can to keep the size down where 
possible.  I was somewhat surprised to find that adding an index on a 
single INTEGER column nearly doubled the size of the database and wanted 
to figure out if there was a way around it, given that the column will 
always be sorted.  (And given my, perhaps erroneous understanding that 
creating an INDEX just makes sure that the column stays sorted so SQLite 
can search through it more intelligently)

>   2).  Fancy.
> You could create your own sparse index table mapping a ROWID to say,
> every thousandth timestamp.  Then you could create upper and lower ROWID
> bounds on any query based on timestamps.  Maybe you import the records
> in batches already and can create the new table at the same time.

 This is closer to what I'm probably going to do.  The data gets 
pulled in every 5 minutes, but between runs, very little actually changes. 
So the idea is to store only the changes along with a full dump say once 
or twice every day.  Then I can just query the values from  to  and compute the state of everything from 
that data.

 Thanks,

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

Re: [sqlite] Implicit INDEX?

[Chris Tracy]

>  If you need to do this from the shell, I'm not sure there is anything
>  you can do.  If you're doing this from the C API just look for timestamps
>  that are >= your target and terminate the query as soon as you find one
>  larger than the target timestamp (e.g. stop calling step() and call
>  reset() on the statement).  If the number of datapoints at a given
>  timestamp is known (because it is fixed, or because you stashed that
>  value in another table), you could also use a LIMIT clause.  That has
>  the added bonus of doing the right thing (even if it takes longer) if
>  the rows somehow get out-of-order.

I'm actually using Perl's DBI interface.  Not sure that I can 
easily do the exit when seeing a timestamp one bigger than the one I'm 
after, but it's an intriguing idea I'd not considered before.  The LIMIT 
I'd also considered, but since the data is variable, I'd abandoned it. 
Hadn't considered storing the value in another table.  But since the whole 
thing was based on an invalid premise (that SQLite was somehow magically 
finding the initial value quickly, ignoring that it just happened to be 
closer to the start of the db) I think I'll take another approach to 
solving the problem.

Thanks for the input though,

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

Re: [sqlite] Implicit INDEX?

[Jay A. Kreibich]

On Mon, Apr 14, 2008 at 10:06:27AM -0700, Chris Tracy scratched on the wall:
> > Your first sentence above is that you "almost immediately get back all
> > the results you want" then you go on to say that the "actual SELECT
> > doesn't return for almost 13 additional seconds." So, what is it
> > exactly that you "almost immediately get back"?
> 
>   When I run the select inside the sqlite3 command line, I get back 
> all the results I'm after within a split second.  However, the actual 
> command itself doesn't return for 13 additional seconds, while the entire 
> table is scanned for other instances where timestamp equals the value I'm 
> after.  (No additional entries exist, so no additional data is ever 
> returned, but it always does this scan unless I've created an INDEX)

  I assume that only works if you are looking for data that was
  inserted near the "beginning" of the database (e.g. low timestamp
  values).  If you're looking for data that was more recently inserted
  (e.g at the "end" of the database), it is going to sit there for 13
  seconds and then display the values you're looking for... right?

  So some kind of short-cut solution will help save some time on
  average, but not for all cases.

>   So my question is, can I avoid the need to scan the entire table 
> for additional values where time =  without having to CREATE 
> INDEX (and use almost 100% more disk space) if I guarantee that all rows 
> are inserted with timestamp in ascending order?

  Technically no, as SQL doesn't consider rows to be ordered within a
  table.  In theory, the same query run two times can return the rows
  in a different order.

  On a more practical side, SQLite will generally return rows in the
  order of their ROWID, assuming no other ordering is in place.  Just
  be sure you understand how ROWIDs are assigned.



  If you need to do this from the shell, I'm not sure there is anything
  you can do.  If you're doing this from the C API just look for timestamps
  that are >= your target and terminate the query as soon as you find one
  larger than the target timestamp (e.g. stop calling step() and call
  reset() on the statement).  If the number of datapoints at a given
  timestamp is known (because it is fixed, or because you stashed that
  value in another table), you could also use a LIMIT clause.  That has
  the added bonus of doing the right thing (even if it takes longer) if
  the rows somehow get out-of-order.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[Mike Owens]

On Mon, Apr 14, 2008 at 8:45 AM, Martin Jenkins <[EMAIL PROTECTED]> wrote:
> Mike Owens wrote:
>  > I've been lobbying Apress to release the book in electronic form for
>  > free. It's currently under consideration, but I've not heard anything
>  > back yet.
>  >
>  That seems a bit extreme - how about a user generated/funded index on
>  the web somewhere?
>  Download and print a PDF, stick it in the back of the book and...

I wasn't referring to releasing the book as an exclusive solution to
the index problem, but rather for the community and SQLite in general.
Fixing the index issue would just be a nice dividend. I am indebted to
Apress for picking up the book and putting substantial resources into
making it a good book (they truly made it a much better book than it
ever would have been otherwise), and I would like to see them recoup
their costs and benefit in any way they so desire. But I couldn't care
less for any gain on my part. I wrote the book for SQLite. Don't get
me wrong, I jumped at the chance to write a book when offered to me,
but my main concern is that the book helps people and furthers the
project. As long as I can avoid getting vilified on Amazon, I'm happy.
Ultimately, I would love to see this book turn out like the "Dive Into
Python" book, which is available online, or the
Subversion/Samba/Asterisk books. I think it is in keeping with the
open source philosophy. But the decision in this case is not mine
alone to make. Regardless of their decision, I applaud Apress for
their continued efforts in supporting books on open source software,
and the people who reward their efforts by purchasing them.

-- Mike

On Mon, Apr 14, 2008 at 8:45 AM, Martin Jenkins <[EMAIL PROTECTED]> wrote:
> Mike Owens wrote:
>  > I've been lobbying Apress to release the book in electronic form for
>  > free. It's currently under consideration, but I've not heard anything
>  > back yet.
>  >
>  That seems a bit extreme - how about a user generated/funded index on
>  the web somewhere?
>  Download and print a PDF, stick it in the back of the book and...
>
>  Martin
>
>
> ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[Griggs, Donald]

Hi, Chris,

Regarding:  "It seems to me (and I might be wrong) that since the values
are already in the database in ascending order by timestamp that SQLite
is able to find all the results quite quickly. ..."


To test this, I think you'd want to create a select for some of the most
recent data (i.e. data at the tail of the database), perhaps after
clearing cache.  I suspect this will take the full table scan time to
return any values.

Two thoughts:

   1).  Easy. 
How bad is the extra 0.8 GByte cost of the index?  At today's prices,
it's only about 20 cents on ordinary 5" drives.  (but maybe you're
programming a cellphone)

   2).  Fancy.
You could create your own sparse index table mapping a ROWID to say,
every thousandth timestamp.  Then you could create upper and lower ROWID
bounds on any query based on timestamps.  Maybe you import the records
in batches already and can create the new table at the same time.
  


This email and any attachments have been scanned for known viruses using 
multiple scanners. We believe that this email and any attachments are virus 
free, however the recipient must take full responsibility for virus checking. 
This email message is intended for the named recipient only. It may be 
privileged and/or confidential. If you are not the named recipient of this 
email please notify us immediately and do not copy it or use it for any 
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[Chris Tracy]

> a shot in the dark. Try
>
> SELECT * from bridge_table WHERE timestamp < 1167615601;
>
> although, it would probably not help because SQLite would still have
> to scan the table to ascertain there were no more rows that met the
> criterion. My hunch is that without giving the program some hint (aka,
> INDEX) you can't get what you want. But, others on this list likely
> have much better knowledge of the internals.

Unfortunately, that doesn't solve the issue of needing to scan all 
the records in the database (without an explicit INDEX), since SQLite 
doesn't realize and thus doesn't act on the fact that the data in the 
timestamp column is already sorted.

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

Re: [sqlite] Implicit INDEX?

[P Kishor]

On 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote:
> > Your first sentence above is that you "almost immediately get back all
>  > the results you want" then you go on to say that the "actual SELECT
>  > doesn't return for almost 13 additional seconds." So, what is it
>  > exactly that you "almost immediately get back"?
>
>
> When I run the select inside the sqlite3 command line, I get back
>  all the results I'm after within a split second.  However, the actual
>  command itself doesn't return for 13 additional seconds, while the entire
>  table is scanned for other instances where timestamp equals the value I'm
>  after.  (No additional entries exist, so no additional data is ever
>  returned, but it always does this scan unless I've created an INDEX)
>
> To wit:
>
>  sqlite3 test.db
>  sqlite> SELECT * from bridge_table WHERE timestamp = 1167615600;
>  [within a split second I get several hundred values ending with...]
>  1167615600|494|8|2
>  1167615600|495|9|7
>  1167615600|496|5|21
>  1167615600|497|8|24
>  1167615600|498|6|46
>  1167615600|499|1|14
>  1167615600|500|9|31
>  [and here it sits for 13 more seconds, looking through the rest of the
>  table for any other values where time = 1167615600, but none exist, so
>  finally...]
>  sqlite>  [I get the prompt back once it finishes, having received no
> additional output, because there isn't any]
>
> It seems to me (and I might be wrong) that since the values are
>  already in the database in ascending order by timestamp that SQLite is
>  able to find all the results quite quickly.  However, it must then scan
>  through the rest of the database on the off chance that some other rows
>  exist where timestamp = 1167615600, because it doesn't know that they're
>  all stored in ascending order.
>
> So my question is, can I avoid the need to scan the entire table
>  for additional values where time =  without having to CREATE
>  INDEX (and use almost 100% more disk space) if I guarantee that all rows
>  are inserted with timestamp in ascending order?
>


a shot in the dark. Try

SELECT * from bridge_table WHERE timestamp < 1167615601;

although, it would probably not help because SQLite would still have
to scan the table to ascertain there were no more rows that met the
criterion. My hunch is that without giving the program some hint (aka,
INDEX) you can't get what you want. But, others on this list likely
have much better knowledge of the internals.

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

Re: [sqlite] Implicit INDEX?

[Chris Tracy]

> Your first sentence above is that you "almost immediately get back all
> the results you want" then you go on to say that the "actual SELECT
> doesn't return for almost 13 additional seconds." So, what is it
> exactly that you "almost immediately get back"?

When I run the select inside the sqlite3 command line, I get back 
all the results I'm after within a split second.  However, the actual 
command itself doesn't return for 13 additional seconds, while the entire 
table is scanned for other instances where timestamp equals the value I'm 
after.  (No additional entries exist, so no additional data is ever 
returned, but it always does this scan unless I've created an INDEX)

To wit:

sqlite3 test.db
sqlite> SELECT * from bridge_table WHERE timestamp = 1167615600;
[within a split second I get several hundred values ending with...]
1167615600|494|8|2
1167615600|495|9|7
1167615600|496|5|21
1167615600|497|8|24
1167615600|498|6|46
1167615600|499|1|14
1167615600|500|9|31
[and here it sits for 13 more seconds, looking through the rest of the 
table for any other values where time = 1167615600, but none exist, so 
finally...]
sqlite>  [I get the prompt back once it finishes, having received no
additional output, because there isn't any]

It seems to me (and I might be wrong) that since the values are 
already in the database in ascending order by timestamp that SQLite is 
able to find all the results quite quickly.  However, it must then scan 
through the rest of the database on the off chance that some other rows 
exist where timestamp = 1167615600, because it doesn't know that they're 
all stored in ascending order.

So my question is, can I avoid the need to scan the entire table 
for additional values where time =  without having to CREATE 
INDEX (and use almost 100% more disk space) if I guarantee that all rows 
are inserted with timestamp in ascending order?

Chris


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

Re: [sqlite] SQLITE_LOCKED behavior

[Shawn Wilsher]

>  Are you using a shared cache?  You can get also get SQLITE_LOCKED when
>  using a shared cache.  See section 2.2 of
>  .
>
>  I've not used a shared cache myself.  One day I was wondering if I
>  needed to worry about handling SQLITE_LOCKED errors and I came across
>  that page.  Are these the only times you can get SQLITE_LOCKED errors?
Ah-ha!  We are in fact using the shared cache, which probably explains
this.  Any reason why SQLITE_LOCKED is returned instead of
SQLITE_BUSY?  With SQLITE_BUSY you can keep retrying until you decide
to give up, or until it works, but you can't do that with
SQLITE_LOCKED.

Cheers,

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

Re: [sqlite] Implicit INDEX?

[P Kishor]

On 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote:
> Err,
>
> Make that example table data:
>
>  <...>
>
> 1167611400|89|7|34
>  1167611400|101|5|4
>  1167611400|147|9|14
>  1167611400|173|7|2
>  1167611400|195|8|1
>  1167611400|314|8|12
>  1167611400|347|6|48
>  1167611400|364|1|49
>
> 
>
> 1167615600|111|7|39
>  1167615600|155|2|8
>  1167615600|157|4|7
>  1167615600|220|4|47
>  1167615600|247|7|21
>  1167615600|261|8|30
>  1167615600|308|9|20
>
> <...>
>
> As I'd originally said, the timestamps are inserted in ascending
>  order.  (I just screwed up pasting my example values in the wrong order)
>
>


ok, but first, I don't really understand the following --

>SELECT * from bridge_table WHERE timestamp = ;
>
>and almost immediately get back all the results I want, since they're
>right next to each other in the db.  However, the actual SELECT doesn't
>return for almost 13 additional seconds, as the entire table is scanned
>for other rows where timestamp might be 

Your first sentence above is that you "almost immediately get back all
the results you want" then you go on to say that the "actual SELECT
doesn't return for almost 13 additional seconds." So, what is it
exactly that you "almost immediately get back"?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implicit INDEX?

[Chris Tracy]

Err,

Make that example table data:

<...>
1167611400|89|7|34
1167611400|101|5|4
1167611400|147|9|14
1167611400|173|7|2
1167611400|195|8|1
1167611400|314|8|12
1167611400|347|6|48
1167611400|364|1|49

1167615600|111|7|39
1167615600|155|2|8
1167615600|157|4|7
1167615600|220|4|47
1167615600|247|7|21
1167615600|261|8|30
1167615600|308|9|20
<...>

As I'd originally said, the timestamps are inserted in ascending 
order.  (I just screwed up pasting my example values in the wrong order)

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

Re: [sqlite] Implicit INDEX?

[Chris Tracy]

>>  inserted into the database in ascending order, and where there may be as
>>  many as 500 hID entries for each timestamp.  After a while, this table

> Have you considered making timestamp a PRIMARY KEY?
>
> So, declare it as INTEGER PRIMARY KEY NOT NULL

Unfortunately, the timestamps aren't unique, so I can't use 
PRIMARY KEY to solve the problem.  (Each run generates as many as 500 
entries, all with the same timestamp)

A simple example of the data in the table follows:

<...>
1167615600|111|7|39
1167615600|155|2|8
1167615600|157|4|7
1167615600|220|4|47
1167615600|247|7|21
1167615600|261|8|30
1167615600|308|9|20

1167611400|89|7|34
1167611400|101|5|4
1167611400|147|9|14
1167611400|173|7|2
1167611400|195|8|1
1167611400|314|8|12
1167611400|347|6|48
1167611400|364|1|49
<...>

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

Re: [sqlite] Implicit INDEX?

[P Kishor]

On 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I'm a relative newcommer to SQL and SQLite and have was is likely
>  a silly question.  However, I've not found an answer to it or even a
>  reference to anything similar in searching the list archives or the web.
>
> If I have a table laid out thusly:
>
>  CREATE TABLE bridge_table (
>  timestamp INTEGER NOT NULL,
>  hID INTEGER NOT NULL,
>  sID INTEGER NOT NULL,
>  pID INTEGER NOT NULL
>  );
>
>  where timestamp values (unix 32-bit integer timestamps) would always be
>  inserted into the database in ascending order, and where there may be as
>  many as 500 hID entries for each timestamp.  After a while, this table
>  will grow to contain between 30 and 50 million records.
>
> In doing some tests, I find that with the fully populated table, I
>  can run:
>
>  SELECT * from bridge_table WHERE timestamp = ;
>
>  and almost immediately get back all the results I want, since they're
>  right next to each other in the db.  However, the actual SELECT doesn't
>  return for almost 13 additional seconds, as the entire table is scanned
>  for other rows where timestamp might be .
>
> Of course, the simple answer is to add an index on timestamp,
>  which does what I want.  However, doing so increases the size of this
>  50 million row table from 1GB to 1.8GB.  It seems to me that if I could
>  convince SQLite that the rows would always be inserted in ascending order
>  by timestamp, that I could have a sort of implicit index without consuming
>  the additional 0.8GB.
>
> Is this even theoretically possible or am I missing something
>  obvious that would prevent an "implicit index" from working like I'm
>  suggesting?

Have you considered making timestamp a PRIMARY KEY?

So, declare it as INTEGER PRIMARY KEY NOT NULL

(the NOT NULL may be unnecessary at that point).

That should help.


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


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Transaction across threads

[Shailesh Birari]

Hello, 
I had a doubt of sqlite transactions across threads. 
I have a multithreaded application which shares the same sqlite
connection across threads. I had a few doubts
1) If I do a begin transaction and insert on one thread. Then do a
insert on the second thread and finally a commit on the first thread
will the insert from the second thread succeed or will it fail saying
SQLITE_BUSY? if it succeeds, will the insert on the second thread be a
part of the transaction on the first thread?
2) When I do a Begin transaction will I get a transactionId? 
3) If one thread is in the middle of a transaction and another thread
does a begin transaction, will it be able to start its own transaction
or will it fail with SQLITE_BUSY.?
 
Kindly let me know, 
 
Regards,
Shailesh.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] want to purchase

[Arvind M]

dear sir 
   
  i want to purchase of sqlite server kinly give me guidance 
   
  arvind 
  systime


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

Re: [sqlite] How to set memory usage as high as possible -- but not too high?

[Jay A. Kreibich]

On Mon, Apr 14, 2008 at 09:51:40AM +0200, Ralf Junker scratched on the wall:
> >> * OPTION 1: PRAGMA cache_size = 1000;
> >>
> >> Advantage: SQLite will use ample memory, but no more than that.
> >>
> >> Disadvantage: Difficulty to establish exact memory requirements in  
> >> advance. The help states that "Each page uses about 1.5K of  
> >> memory.", but I found this to be wrong. Memory usage obviously  
> >> depends on the page size, and my measurement shows that there is an  
> >> additional small overhead of undocumented size. Is there a formula  
> >> to calculate the required memory for a cache_size of x?
> 
> I'd be curious if you know an answer to this, too?

  Given that the default page size is 1K, it seems like the statement
  "Each page uses about 1.5K of memory" would account for most of the
  overhead.

> >> * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes);
> >>
> >> Disadvantage: My tests indicate that SQLite slows down drastically  
> >> when it hits the memory limit. Inserts drop from a few hundred per  
> >> second to just one or two per sec.
> >
> >That is an odd result. How did you test it? 
> 
> I set up a high cache_size and a lower sqlite3_soft_heap_limit() and 
> started inserting blobs.

  Be aware that the default setting for this is "unlimited", so any
  value set is only going to reduce the amount of available memory.
  I understand you might try to do this to keep the whole memory image
  in RAM, but as long as the page cache is something realistic, I'm not
  sure any additional limits are advisable.

  Also, I'm not sure I would consider this test valid.  If the system
  runs up against the soft heap limit, it is going recover memory from
  the page cache.  Setting up a large page cache and a low soft heap
  limit is going to cause the memory allocator and page cache to fight
  with each other.
  
  Things may really go south in this specific case, as the first pages
  the recovery system is going to go after are those that don't require
  a sync, i.e. those pages that have been read but not written to.
  That would include most of the internal B-Tree index pages that are
  providing your performance gain for the indexed inserts.


 ===


On Mon, Apr 14, 2008 at 09:38:50AM +0200, Ralf Junker scratched on the wall:
> Roger Binns wrote:
> 
> >Are you using a 32 bit or 64 bit process.
> 
> 32, but it does not matter to the problem.

  When you give examples like "PRAGMA cache_size = 1000;", or 10M
  pages which would take something on the order of 15 GB of memory to
  service with the default 1K page size, a few of us start to wonder.

  Not only is that impossible with a 32-bit system, if your database is
  only 6GB, that cache setting is much larger than the database itself,
  which only has about six million pages.

> >Also is there a requirement to create the database in the filesystem?
> 
> Yes.
> 
> > If not you could ensure your swap is sufficiently large (I use a
> > mininmum of 16GB on my machines :-) and create in a tmpfs filesystem,
> > and then copy the database to persistent storage when you are done.
> 
> The aim is to avoid slow swap memory but use fast RAM only.

  Yes, but that's impossible with a 6GB database (and a 32bit system).
  Some of it is going to sit on disk.  The big advantage of using tmpfs
  is that it is linked directly to the operating system, so it will use
  as much RAM as possible (but never more) and require very little tuning.

> >I am curious why you think memory is the bottleneck anyway!
> 
> It has often been pointed out on this list that inserts into
> indexed tables (regular or FTS) run faster with a high page cache.
> My own tests 2nd this. A few 100 MB more or less can make an
> difference of more than 100%.

  Given that the default page cache is 2000 pages, or on the order of
  3MB, it seems that you're hitting some serious limits.  If hundreds
  of megabytes (!) is giving you a return on the order of 2x, then there
  is no magic bullet-- you aren't going to find a setting that suddenly
  gives you a 10x speedup.  You're hitting diminishing returns in a
  serious kind of way.

  Personally, I'd pick a number, like half your RAM size or ~1.5GB*
  (whichever is smaller), set the page cache, and be done with it.
  It sounds like you've already found most of the other PRAGMAs that
  are going to get you something.  You might be able to tune the size
  of your INSERT transactions, but if you're around 1000 or so, going
  higher isn't likely to buy you too much.

  * This upper limit is OS-dependent.  I'd use 1.5GB on Windows and older
Linux systems, 2.5GB for some of the more modern Linux systems, 3.5GB
for Mac OS X.

  Like all things performance tuning, unless you have a really good
  idea of how the system is working, you're just shooting in the dark.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' 

[sqlite] Implicit INDEX?

[Chris Tracy]

Hello,

I'm a relative newcommer to SQL and SQLite and have was is likely 
a silly question.  However, I've not found an answer to it or even a 
reference to anything similar in searching the list archives or the web.

If I have a table laid out thusly:

CREATE TABLE bridge_table (
 timestamp INTEGER NOT NULL,
 hID INTEGER NOT NULL,
 sID INTEGER NOT NULL,
 pID INTEGER NOT NULL
);

where timestamp values (unix 32-bit integer timestamps) would always be 
inserted into the database in ascending order, and where there may be as 
many as 500 hID entries for each timestamp.  After a while, this table 
will grow to contain between 30 and 50 million records.

In doing some tests, I find that with the fully populated table, I 
can run:

SELECT * from bridge_table WHERE timestamp = ;

and almost immediately get back all the results I want, since they're 
right next to each other in the db.  However, the actual SELECT doesn't 
return for almost 13 additional seconds, as the entire table is scanned 
for other rows where timestamp might be .

Of course, the simple answer is to add an index on timestamp, 
which does what I want.  However, doing so increases the size of this 
50 million row table from 1GB to 1.8GB.  It seems to me that if I could 
convince SQLite that the rows would always be inserted in ascending order 
by timestamp, that I could have a sort of implicit index without consuming 
the additional 0.8GB.

Is this even theoretically possible or am I missing something 
obvious that would prevent an "implicit index" from working like I'm 
suggesting?

Thanks,

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

Re: [sqlite] segmentation fault

[Glenn McAllister]

dark0s dark0s wrote:
> I did not used never gdb.
> Can I post me commands that I must to use

OK, you are seriously becoming a help vampire 
(http://www.slash7.com/pages/vampires) at this point.  Here are some 
pointers:

- Programming in C: A Modern Approach
http://knking.com/books/c2/index.html

I use the first edition to train new developers in C, and I'll be 
picking up a copy of the second edition shortly.

- GCC
http://en.wikipedia.org/wiki/GNU_Compiler_Collection
http://gcc.gnu.org/
http://gcc.gnu.org/onlinedocs/
http://gcc.gnu.org/faq.html

- GDB
http://en.wikipedia.org/wiki/GNU_Debugger
http://www.gnu.org/software/gdb/
http://sourceware.org/gdb/download/onlinedocs/gdb.html

Please start reading.

-- 
Glenn McAllister <[EMAIL PROTECTED]>  +1 416 348 1594
SOMA Networks, Inc.  http://www.somanetworks.com/  +1 416 977 1414
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Populating and scrolling the Listbox using query

[Fred Williams]

Might want to download the free source for the Delphi VCL set for Sqlite DB
access componenets provided at Aducom.com and review the releven source
code. Sorry, it is written that highly "inelegant" Object Pascal.  But I'm
an old "C" coder from way back and do read and write Object Pascal more so
than "C".  Just think of it as a wordy "C" without a real need for those
pesky pointers.  I have never looked that deep in that particular area of
the source so have no direct suggestions.  I just let Delphi VCL's do the
scrolling in either direction.

Fred

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Igor Tandetnik
Sent: Monday, April 14, 2008 6:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Populating and scrolling the Listbox using query


"Farzana" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> We are working in eVC++ environment with SQLite database.We need to
> populate the listbox with the values obtained by executing the query.
> We were able to get the values of the query by using the API's
> sqlite3_prepare and sqlite3_step.
> But we were able to populate and move the listbox in the downward
> direction only

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

Igor Tandetnik



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

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

Re: [sqlite] SQL Quick Review/Reference

[Martin Jenkins]

Mike Owens wrote:
> I've been lobbying Apress to release the book in electronic form for
> free. It's currently under consideration, but I've not heard anything
> back yet.
>   
That seems a bit extreme - how about a user generated/funded index on 
the web somewhere?
Download and print a PDF, stick it in the back of the book and...

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

Re: [sqlite] SQL Quick Review/Reference

[Rich Shepard]

On Mon, 14 Apr 2008, Mike Owens wrote:

> I know I've said this before, but I just want to say it again because it
> pains me that the index is so bad: I had absolutely nothing to do with the
> index. While I was presented with the index to look over, I only had about
> a day to provide any feedback and there were so many other deadlines
> looming that I really only had about 30 minutes to "read" the index.

Mike,

   I'm certainly not blaming you!

> I've been lobbying Apress to release the book in electronic form for free.
> It's currently under consideration, but I've not heard anything back yet.

   Well, since I've purchased both the dead tree and digital versions, that
won't help me.

   I wrote my book in LaTeX (using the LyX GUI front end which is highly
productive), and used the indexing feature to create the index. Before doing
so, I researched how to write an index, bought a book on the subject, and
read that before starting. It did take me several iterations before I was
satisfied, and I'm sure there are still deficiencies I did not catch.

   Putting out a supplemental index would be a very useful effort.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[ajm]

Mike:

As a purchaser of your Book, I think that perhaps you can have a copy of the 
book in plain text form.
Then, with a little effort can be made a index of words, and (also) perhaps it 
would be easier that Apress let you publish it.

In respect to the index creation I would help in that.

Adolfo.-Original Message-
From: Mike Owens [mailto:[EMAIL PROTECTED]
Sent: Monday, April 14, 2008 08:23 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] SQL Quick Review/Reference

I've been lobbying Apress to release the book in electronic form forfree.
It's currently under consideration, but I've not heard anythingback yet.
 Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Quick Review/Reference

[Mike Owens]

On Sat, Apr 12, 2008 at 9:36 AM, Rich Shepard <[EMAIL PROTECTED]> wrote:
> On Fri, 11 Apr 2008, Amit Uttamchandani wrote:

>I second the recommendation of Mike Owens' "The Definitive Guide to
>  SQLite" for detailed insight on this system. And it does have the worst
>  index of any technical book. (I wrote to the publisher about it but never
>  received any reply.)

I know I've said this before, but I just want to say it again because
it pains me that the index is so bad: I had absolutely nothing to do
with the index. While I was presented with the index to look over, I
only had about a day to provide any feedback and there were so many
other deadlines looming that I really only had about 30 minutes to
"read" the index.

I've been lobbying Apress to release the book in electronic form for
free. It's currently under consideration, but I've not heard anything
back yet.

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

Re: [sqlite] Populating and scrolling the Listbox using query

[Igor Tandetnik]

"Farzana" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> We are working in eVC++ environment with SQLite database.We need to
> populate the listbox with the values obtained by executing the query.
> We were able to get the values of the query by using the API's
> sqlite3_prepare and sqlite3_step.
> But we were able to populate and move the listbox in the downward
> direction only

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

Igor Tandetnik 



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

[sqlite] Populating and scrolling the Listbox using query

[Farzana]

Dear All,

We are working in eVC++ environment with SQLite database.We need to populate
the listbox with the values obtained by executing the query.
We were able to get the values of the query by using the API’s
sqlite3_prepare and sqlite3_step.
But we were able to populate and move the listbox in the downward direction
only and we couldn’t get the correct result when we click the upward
direction button of the listbox.Is there any API or functions available to
move the data in upward direction in the listbox?

We happened to come across the functions of BTree such as
sqlite3BtreePrevious, sqlite3BtreeNext where we are suppose to use cursors.
Is it possible to execute the query using cursors and move the pointer in
the upward or downward direction?

Kindly help in this regard.
Thanks in advance.

Regards,
Farzana.

-- 
View this message in context: 
http://www.nabble.com/Populating-and-scrolling-the-Listbox-using-query-tp16676178p16676178.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] How to manage separate lists of ordered items?

[Paul Smith]

[EMAIL PROTECTED] wrote:
> If I have something like a real estate database where each customer
> can have an ordered list of houses they want to visit, is there a
> recommended way to design tables that just link to other tables to
> create the ordered list? I have a table of houses and a table of
> customers. My (probably incorrect) intuition is to create a separate
> table for each ordered list the customer wants. But looking at other
> databases, I see that people just create one big table for all
> customers like this:
>
> create table customer_list_map (
>   customer_id references customers( id ),
>   house_id references houses( id ),
>   visit_order integer );
>
> Then they do the following to find an ordered, customer-specific house list:
>
> select * from customer_list_map where customer_id=
> order by visit_order
>
> I don't know anything about databases, but that seems inefficient and
> more work to maintain (e.g. if multiple lists per customer are later
> supported). Is there a better way to do stuff like this with SQLite?
>
>   
One general rule about SQL work is that you don't create tables 
dynamically. So the 'customer_list_map' idea is the 'proper' way to do it.

Sometimes there are good reasons to create tables dynamically 
(especially temporary ones), but this requirement certainly isn't one of 
them, since the 'customer_list_map' is a good solution to the problem. 
There's a reason everyone else is doing it that way...

As long as you have an index on the customer_list_map on the 
'customer_id' column, and you have an index on the house list table on 
the 'house_id' column, then it should be quick.

If you are thinking of having multiple lists per customer, then just add 
a 'list_id' column to the customer_list_map table in anticipation. Then 
your select can select on that as well as the customer id.

You'll find that the 'customer_list_map' way works well, and is actually 
a lot simpler to handle in the long run than doing it your proposed way.


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

[sqlite] How to manage separate lists of ordered items?

[skillzero]

If I have something like a real estate database where each customer
can have an ordered list of houses they want to visit, is there a
recommended way to design tables that just link to other tables to
create the ordered list? I have a table of houses and a table of
customers. My (probably incorrect) intuition is to create a separate
table for each ordered list the customer wants. But looking at other
databases, I see that people just create one big table for all
customers like this:

create table customer_list_map (
customer_id references customers( id ),
house_id references houses( id ),
visit_order integer );

Then they do the following to find an ordered, customer-specific house list:

select * from customer_list_map where customer_id=
order by visit_order

I don't know anything about databases, but that seems inefficient and
more work to maintain (e.g. if multiple lists per customer are later
supported). Is there a better way to do stuff like this with SQLite?

Each of these customer lists also has a Unicode name and it seems like
table names are supposed to be ASCII so I suspect I'll need another
table for all the lists that the customer has (with the Unicode name
and any other attributes) and then somehow link each table of
house_id's and visit_order's to that table. That's where I get
confused. How would I link an entire table (as opposed to a row in a
table) so I can say "select * from "?
Do I just name the table with the unique id from the table of list
names?

Am I just going about this all wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to set memory usage as high as possible -- but not too high?

[Ralf Junker]

Dan,

many thanks for the quick response and detailed answers. However, a question or 
two still puzzle me.

>> * OPTION 1: PRAGMA cache_size = 1000;
>>
>> Advantage: SQLite will use ample memory, but no more than that.
>>
>> Disadvantage: Difficulty to establish exact memory requirements in  
>> advance. The help states that "Each page uses about 1.5K of  
>> memory.", but I found this to be wrong. Memory usage obviously  
>> depends on the page size, and my measurement shows that there is an  
>> additional small overhead of undocumented size. Is there a formula  
>> to calculate the required memory for a cache_size of x?

I'd be curious if you know an answer to this, too?

>> * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes);
>>
>> Disadvantage: My tests indicate that SQLite slows down drastically  
>> when it hits the memory limit. Inserts drop from a few hundred per  
>> second to just one or two per sec.
>
>That is an odd result. How did you test it? 

I set up a high cache_size and a lower sqlite3_soft_heap_limit() and started 
inserting blobs.

>What was the memory limit? Any chance the machine started using swap space?

I will test again and let you know.

>> * Does "PRAGMA cache_size=some_smaller_value;" cause SQLite to free  
>> used pages and release their memory straight away?
>
>No. If the cache_size parameter is set to a value that
>is less than the number of pages currently allocated for the
>cache, no more pages will be allocated. But no existing
>pages will be freed.

Good to know. So I would reduce the cache_size and then use 
sqlite3_release_memory() to free memory, right?

Maybe this is worth documenting?

>Does SQLite really run faster with 1GB available than it would with 100MB?

Yes. If there are indexes (regular or FTS) on the table, SQLite needs quick 
access to lots of pages for searching and rearranging b-tree entries. My 
timings show that 100MB or 500MB can sometimes make a difference of more than 
100%.

Richard recently talked about upcoming indexing performance improvements. I 
wonder if they are part of the performance refactoring due with the next 
release? :-)

Ralf 

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

Re: [sqlite] How to set memory usage as high as possible -- but not too high?

[Ralf Junker]

Roger Binns wrote:

>Are you using a 32 bit or 64 bit process.

32, but it does not matter to the problem.

>Also is there a requirement to create the database in the filesystem?

Yes.

>If not you could ensure your swap is sufficiently large (I use a mininmum of 
>16GB on my machines :-) and create in a tmpfs filesystem, and then copy the 
>database to
>persistent storage when you are done.

The aim is to avoid slow swap memory but use fast RAM only.

>You also didn't list turning off synchronous etc while creating the database 
>and turning it back on when done.

Performance settings are:

* PRAGMA locking_mode=exclusive;
* PRAGMA synchronous=off;
* Disable journal file :-)

>I am curious why you think memory is the bottleneck anyway!

It has often been pointed out on this list that inserts into indexed tables 
(regular or FTS) run faster with a high page cache. My own tests 2nd this. A 
few 100 MB more or less can make an difference of more than 100%.

Ralf 

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