Re: [sqlite] crypt() as SQL core function

2010/8/2 Simon Slavin : > But frankly I'm not even sure that abs(), round() and soundex() should be in >SQLite.  I wonder how they ever made it in. Soundex function is build-in :-) Compile with -DSQLITE_SOUNDEX. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/

[sqlite] "database disk image is malformed" while using WAL

Hi, I am getting "database disk image is malformed" error while using WAL. To reproduce: 1. open sqlite connection. 2. PRAGMA journal_mode=WAL 3. PRAGMA synchronous=full 4. PRAGMA temp_store=memory 5. sqlite3_enable_shared_cache(1) 6. while(1) loop - insert records to a table. 7. after 15 seconds,

Re: [sqlite] "database disk image is malformed" while using WAL

Hi, Forgot to mention that the inserting should be inside a transactions, so complete repro steps are: 1. open sqlite connection. 2. PRAGMA journal_mode=WAL 3. PRAGMA synchronous=full 4. PRAGMA temp_store=memory 5. sqlite3_enable_shared_cache(1) 6. begin transaction 7. while(1) loop - insert

Re: [sqlite] "database disk image is malformed" while using WAL

On Aug 2, 2010, at 1:42 PM, Yoni Londner wrote: > Hi, > Forgot to mention that the inserting should be inside a > transactions, so > complete repro steps are: > 1. open sqlite connection. > 2. PRAGMA journal_mode=WAL > 3. PRAGMA synchronous=full > 4. PRAGMA temp_store=memory > 5.

Re: [sqlite] "database disk image is malformed" while using WAL

On Aug 2, 2010, at 6:13 PM, Dan Kennedy wrote: > > On Aug 2, 2010, at 1:42 PM, Yoni Londner wrote: > >> Hi, >> Forgot to mention that the inserting should be inside a >> transactions, so >> complete repro steps are: >> 1. open sqlite connection. >> 2. PRAGMA journal_mode=WAL >> 3. PRAGMA

Re: [sqlite] "database disk image is malformed" while using WAL

Hi Dan, Thanks for the response. I tried to new version, but the problem still exist. I continued to debug it, and found that the error detected in btreeInitPage (on decodeFlags). I also found that if i run without enabling shared cache, the problem does not happen. Hope that it will help. Yoni.

Re: [sqlite] "database disk image is malformed" while using WAL

Hi, Actually it DOES help. I just found out that I ran the wrong executable. Sorry if I wasted your time :-) When will you have an official release of this version? Yoni. On Mon, Aug 2, 2010 at 3:02 PM, Yoni Londner wrote: > Hi Dan, > Thanks for the response. > I tried to

[sqlite] UnSubscribe

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

Re: [sqlite] Statistics on integer primary key - Cardinality

Hi Peter, Regarding: "Selectivity is known -- since it's a primary key, which is unique -- it will be 1. Cardinality can vary." I wonder if you're referring to the definition of *cardinality* as used in mathematics http://en.wikipedia.org/wiki/Cardinality In mathematics,

Re: [sqlite] "database disk image is malformed" while using WAL

2010/8/2 Yoni Londner : > Hi, > Actually it DOES help. > I just found out that I ran the wrong executable. > Sorry if I wasted your time :-) > When will you have an official release of this version? See planned date here: http://sqlite.org/draft/releaselog/3_7_1.html "SQLite

Re: [sqlite] crypt() as SQL core function

Whoa, Nellie. :-) I use the abs() function on full-text proximity queries for situations when the order of the words is not relevant. Don't be taking that function out. Regards Tim Romano Swarthmore PA On Mon, Aug 2, 2010 at 2:34 AM, Alexey Pechnikov wrote: >

Re: [sqlite] crypt() as SQL core function

On Mon, Aug 02, 2010 at 03:58:19AM +0100, Simon Slavin scratched on the wall: > But frankly I'm not even sure that abs(), round() and soundex() should > be in SQLite. I wonder how they ever made it in. The SQL standard defines several functions. I'm fairly sure these three are not part of

Re: [sqlite] crypt() as SQL core function

Would it be possible to have SQLite tell you "not enabled" for any functions that are not compiled in? Rather than "no such function"? So for those of us who are not familiar with all the possibilities might accidentally stumble upon them? Or...for when you start disabling functions to

Re: [sqlite] crypt() as SQL core function

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/02/2010 08:24 AM, Black, Michael (IS) wrote: > Would it be possible to have SQLite tell you "not enabled" for any functions > that are not compiled in? No because to do that it would have to compile in extra code to produce the "not enabled"

[sqlite] list table structure

I read on the SQLite how to get the list of tables in the SQLite database. How do I get the table structure from within a program? For example "describe " would be how I would do it in Oracle. Thanks, Chris ___ sqlite-users mailing list

Re: [sqlite] list table structure

On Mon, Aug 02, 2010 at 11:11:25AM -0500, Chris Hare scratched on the wall: > I read on the SQLite how to get the list of tables in the SQLite database. > > How do I get the table structure from within a program? Foro > example "describe " would be how I would do it in Oracle.

Re: [sqlite] list table structure

You would use pragma table_info(); http://www.sqlite.org/pragma.html#pragma_table_info --- On Mon, 8/2/10, Chris Hare wrote: > From: Chris Hare > Subject: [sqlite] list table structure > To: sqlite-users@sqlite.org > Date: Monday, August 2, 2010, 12:11 PM > I

Re: [sqlite] list table structure

On Mon, Aug 02, 2010 at 11:21:12AM -0500, Jay A. Kreibich scratched on the wall: > On Mon, Aug 02, 2010 at 11:11:25AM -0500, Chris Hare scratched on the wall: > > I read on the SQLite how to get the list of tables in the SQLite database. > > > > How do I get the table structure from within a

[sqlite] indexing speed

I have a table with just a few columns, one is a SHA1 hash and the second an MD5, there are about 17 Million rows in the table if I create an index on the SHA1 column using "create index if not exists sha1index on hashtable(sha1)" the process takes about 3 minutes, if I follow this immediately by

[sqlite] wal database reverts to 'delete' journal mode following vacuum

Hi, It seems that following a vacuum on a wal-enabled database, the journal mode reverts to delete? Is this meant to happen? I couldn't find this stated as such in the documentation at www.sqlite.org/wal.html. $ sqlite3 test.db SQLite version 3.7.0 Enter ".help" for instructions Enter SQL

Re: [sqlite] indexing speed

Paul Sanderson wrote: > I have a table with just a few columns, one is a SHA1 hash and the > second an MD5, there are about 17 Million rows in the table > > if I create an index on the SHA1 column using "create index if not > exists sha1index on hashtable(sha1)" the

Re: [sqlite] indexing speed

On 2 Aug 2010, at 5:31pm, Paul Sanderson wrote: > I have a table with just a few columns, one is a SHA1 hash and the > second an MD5, there are about 17 Million rows in the table > > if I create an index on the SHA1 column using "create index if not > exists sha1index on hashtable(sha1)" the

[sqlite] Database unreadable after WAL conversion on system w/o shared mem support

I recently tried out SQLite 3.7.0 on an ARM project board running linux 2.6. I did not realize that jffs2 lacked shared memory support, so when I opened my database file using the sqlite3 utility and typed 'pragma journal_mode=wal' it appeared as though the conversion worked (response: wal) but

Re: [sqlite] indexing speed

I think you may use FTS3 extension. This is very fast for big tables too. I did test about to 500 millions rows with UUIDs in FTS3 table and results are fine. See as example test here: http://book.mobigroup.ru/dir?name=web_project_DBMS/distributed_schema Some result logs you can find in files

Re: [sqlite] Optimizing Songbird

Hmm - My original pastebin is gone now (oops), so I reposted with the output from the sqlite3_ tracing functions (instead of our own performance logger) here: http://mozilla.pastebin.org/441814. Let me know if you see any obvious indexes to add, or other obvious problems with how we're

Re: [sqlite] Optimizing Songbird

On Mon, Aug 2, 2010 at 1:51 PM, Mike Hays wrote: > Hmm - My original pastebin is gone now (oops), so I reposted with the > output from the sqlite3_ tracing functions (instead of our own performance > logger) here: http://mozilla.pastebin.org/441814. Let me know if you

Re: [sqlite] Optimizing Songbird

On 2 Aug 2010, at 7:06pm, Richard Hipp wrote: > Most of the slow queries seem to be "SELECT count(*) FROM ". Such > queries have to visit every row in the table (in order to count the rows) > and thus get progressively slower as the number of entries in the database > increases. Might

Re: [sqlite] Optimizing Songbird

On Mon, Aug 2, 2010 at 2:11 PM, Simon Slavin wrote: > > On 2 Aug 2010, at 7:06pm, Richard Hipp wrote: > > > Most of the slow queries seem to be "SELECT count(*) FROM ". Such > > queries have to visit every row in the table (in order to count the rows) > > and thus get

Re: [sqlite] Optimizing Songbird

On Mon, Aug 2, 2010 at 11:11 AM, Simon Slavin wrote: > On 2 Aug 2010, at 7:06pm, Richard Hipp wrote: >> Most of the slow queries seem to be "SELECT count(*) FROM ".  Such >> queries have to visit every row in the table (in order to count the rows) >> and thus get

Re: [sqlite] Optimizing Songbird

Count() might still have to visit each row because there might also be a where clause. select count(toys) in toybox where wheels=4; (pardon rusty SQL plz) On Mon, Aug 2, 2010 at 1:37 PM, Scott Hess wrote: > On Mon, Aug 2, 2010 at 11:11 AM, Simon Slavin

Re: [sqlite] Optimizing Songbird

[Sorry for the mis-fire.] On Mon, Aug 2, 2010 at 11:11 AM, Simon Slavin wrote: > On 2 Aug 2010, at 7:06pm, Richard Hipp wrote: >> Most of the slow queries seem to be "SELECT count(*) FROM ".  Such >> queries have to visit every row in the table (in order to count the

Re: [sqlite] Optimizing Songbird

Richard and Scott - big thanks for the suggestions. -Mike - Original Message - From: "Scott Hess" To: "General Discussion of SQLite Database" Sent: Monday, August 2, 2010 1:45:26 PM Subject: Re: [sqlite] Optimizing Songbird [Sorry for the

[sqlite] Kevin Smekens - out of the office.

I will be out of the office starting 2010/08/03 and will not return until 2010/08/05. Please contact Mr. Joachim Vandersleyen or Ms. Nozomi fuji for urgent matters: joachim.vandersle...@awtce.be nozomi.f...@awtce.be ___ sqlite-users mailing list

[sqlite] How to write the trigger?

I created a table like this： CREATE TABLE record_trip (trip_id CHAR(1), gp_no CHAR(1), rec_date INTEGER, trun CHAR(1), ctrl_id CHAR(1), moment_value INTEGER, minutes_value INTEGER, set_value INTEGER ); CREATE UNIQUE INDEX i_record_trip ON record_trip (trip_id, gp_no, rec_date); And the trigger