Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases

2007-11-22 Thread Cory Nelson
On Nov 22, 2007 1:04 PM, Daniel Önnerby <[EMAIL PROTECTED]> wrote:
> In the future I am using UTF8 encoded databases since the conversion of
> strings is a small thing for the system. The advantages of using UTF8
> are many:
> 1. Faster in most cases
> 2. Smaller databases (30% smaller in benchmark test database)
> 3. Less memory usage OR more information will fit in memory.

Well of course it comes at no surprise that if your database is
primarily US-ASCII text, UTF-8 will be better.  Smaller sizes mean
smaller comparisons and more packed b-trees.  UTF-16 is only good if
you have a lot of text that would be encoded with >= 2 UTF-8 code
units.

-- 
Cory Nelson


Re: [sqlite] Sqlite version for libc 2.1.3

2007-11-22 Thread Trevor Talbot
On 11/22/07, Tara_Nair <[EMAIL PROTECTED]> wrote:

> I just recently started to use Sqlite3 version 3.5.2 on an ARM-LINUX
> based embedded platform.
> Sqlite3-v3.5.2. has a dependency on libc, ld and libpthread versions 2.2.3.

Er.. it should depend on whatever versions you link it against when
you cross-compile for your platform.

How/where did you get the version of SQLite you're trying to use now?

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



[sqlite] Sqlite version for libc 2.1.3

2007-11-22 Thread Tara_Nair
Hello all,

I just recently started to use Sqlite3 version 3.5.2 on an ARM-LINUX
based embedded platform.
Sqlite3-v3.5.2. has a dependency on libc, ld and libpthread versions 2.2.3.

My sorrow is that the rest of my application and the libraries on my
ramdisk are of version 2.1.3. It doesn't seem to be a simple task to
update the ramdisk and possibly other software to be compatible with the
latest versions of these libraries.

I was wondering if anybody knew if there exists an older version of
sqlite3 which uses 2.1.3 versions of libc,ld and libpthread.

Would be very grateful, if anyone could help with a version number and
where I could download it from.
Thanks in advance.
Tara.


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



[sqlite] wrong version number when compiled

2007-11-22 Thread Xabriel J Collazo Mojica
Hi all:

I've just downloaded, compiled and installed SQLite3 3.5.2 on my mac.
Everything is working fine but the version number. I know OS X 10.4 comes
with SQLite built-in on /usr/bin. I installed 3.5.2  on /usr/local and
updated my PATH so that is all right. Look at what I get:

xabita:/ xabriel$ which sqlite3
/usr/local/bin/sqlite3
xabita:/ xabriel$ sqlite3 -version
3.1.3


As mentioned previously my PATH is all right:

xabita:/ xabriel$ echo $PATH
/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin


Something weird is that 3.1.3 is the version that my Mac has built-in:

xabita:/ xabriel$ /usr/bin/sqlite3 -version
3.1.3


So, do somebody see what I am missing?

thanks, regards,
-- 
Xabriel J. Collazo-Mojica
B.S. in Computer Engineering Student
University of Puerto Rico at Mayagüez
http://xabrielc.googlepages.com


[sqlite] Reported Error with SQLITE_MEMORY_SIZE

2007-11-22 Thread Teg
3.5.2 from the ZIP file,
Compiled under Visual Studio 2005.
Static Library

SQLITE_OMIT_AUTHORIZATION
SQLITE_OMIT_AUTOVACUUM
SQLITE_OMIT_BLOB_LITERAL
SQLITE_OMIT_DATETIME_FUNCS
_CRT_SECURE_NO_DEPRECATE
SQLITE_MEMORY_SIZE=100
SQLITE_CORE

A couple weeks ago I reported a crash when I used the
SQLITE_MEMORY_SIZE=100 compile time setting. I've now gotten a
chance to look at it further. The reply on the list was it's working
fine.

I have several small DB's that I vacuum during startup. In most cases
the vacuum works perfectly OK with this setting and there are no
crashes. There is one DB though that, during the vacuum,

It gets to here

int sqlite3BtreeRollbackStmt(Btree *p){
  int rc = SQLITE_OK;
  BtShared *pBt = p->pBt;
  sqlite3BtreeEnter(p);
  if( pBt->inStmt && !pBt->readOnly ){
rc = sqlite3PagerStmtRollback(pBt->pPager);
assert( countWriteCursors(pBt)==0 );
pBt->inStmt = 0;
  }
  sqlite3BtreeLeave(p);
  return rc;
}

If I look at the pPager, I see fd.methods is NULL but, jfd.methods and
stfd.methods aren't NULL.

  ** Ticket #1171:  The statement journal might contain page content that is
  ** different from the page content at the start of the transaction.
  ** This occurs when a page is changed prior to the start of a statement
  ** then changed again within the statement.  When rolling back such a
  ** statement we must not write to the original database unless we know
  ** for certain that original page contents are synced into the main rollback
  ** journal.  Otherwise, a power loss might leave modified data in the
  ** database file without an entry in the rollback journal that can
  ** restore the database to its original form.  Two conditions must be
  ** met before writing to the database files. (1) the database must be
  ** locked.  (2) we know that the original page content is fully synced
  ** in the main journal either because the page is not in cache or else
  ** the page is marked as needSync==0.
  */
  pPg = pager_lookup(pPager, pgno);
  PAGERTRACE4("PLAYBACK %d page %d hash(%08x)\n",
   PAGERID(pPager), pgno, pager_datahash(pPager->pageSize, aData));
  if( pPager->state>=PAGER_EXCLUSIVE && (pPg==0 || pPg->needSync==0) ){
i64 offset = (pgno-1)*(i64)pPager->pageSize;
rc = sqlite3OsWrite(pPager->fd, aData, pPager->pageSize, offset);
if( pPg ){
  makeClean(pPg);
}
  }

  Here it attempts to write to the OS using pPager->fd and since
  fd.methods is null, there's a crash.


  I don't know the connection between the memory setting and the
  crash. The other DB's don't rollback during the vacuum which
  probably explains why they don't crash. I'm going to make a backup
  copy of the DB and then try to manually vacuum the file and see if
  the crashing goes away.

  I'm sitting in a hotel so, my connectivity is spotty. Sorry if any
  replies to the list are delayed till Saturday evening.
  
  C


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



[sqlite] benchmarking UTF8 vs UTF16 encoded databases

2007-11-22 Thread Daniel Önnerby
When I started using SQLite I found it natural to use the sqlite3_open16 
and use UTF16 encoding on strings since my applications always use 
wchar_t when handeling strings. I never questioned this until now when I 
decided to do some benchmark, and I found it interesting enough to share 
with you.


In my benchmark I used a database with several tables and indexes and 
the table I decided to benchmark contains 10 columns and 14000 rows with 
different types. It's a well normalized database that is used in a real 
life application.


The benchmark is made on 2 different databases that are identical except 
for the fact that one is UTF8 encoded and the other is UTF16 encoded. I 
always get the 2 columns using sqlite3_column_text16 - so when getting 
the string from the UTF8 database - a conversion is made, but the output 
strings from both databases are always the same. The benchmark is looped 
10 times for better average results.


Benchmark 1:
Selecting 2 columns from the table without any WHERE or ORDER BY
UTF8.db0.38s
UTF16.db  0.33s
As expected the UTF16 encoded database is a little bit faster since no 
conversion is made. The difference is:

15% slower using UTF8 encoding.

Benchmark 2:
Selecting 2 columns from the table without and WHERE, but with ORDER BY 
on a text-column without any index (slow)

UTF8.db   4.34s
UTF16.db11.19s
Well, this is a slow query. Sorting a UTF8 encoded string is obviously a 
lot faster than sorting  a UTF16 encoded string. The conversion done by 
sqlite3_column_text16 is not noticeable in this benchmark. Difference:

66% faster using UTF8 encoding.

Benchmark 3:
Selecting 2 columns from the table without any WHERE, but with ORDER BY 
on text-column WITH index.

UTF8.db 0.58s
UTF16.db   0.63s
Interesting. I guess  the conversion done by sqlite3_column_text16 is 
not noticeable compared to the extra disk/mem IO for the extra data 
using UTF16. Difference:

8% faster using UTF8 encoding.



In the future I am using UTF8 encoded databases since the conversion of 
strings is a small thing for the system. The advantages of using UTF8 
are many:

1. Faster in most cases
2. Smaller databases (30% smaller in benchmark test database)
3. Less memory usage OR more information will fit in memory.

I forgot to tell you that the benchmark is made on windows XP. The 
conversion done in sqlite3_column_text16 may be a lot slower/faster on 
any other platform.



Best regards
Daniel

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



Re: [sqlite] SQLite Announcements

2007-11-22 Thread Darren Duncan

At 2:47 PM + 11/22/07, Alberto Simões wrote:

Is it just me, or SQLite announcements for new releases are not being
sent to this list?
At least I didn't see 3.5.0, 3.5.1 and 3.5.2 announcements. I am aware
of them just because complain about them :)


I saw the 3.5.0 announcement on this list, but no 
announcements for 3.5.1 nor 3.5.2, which was part 
of the reason I previously stated I hadn't 
realized that 3.5.x had come out of alpha. -- 
Darren Duncan


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



RE: [sqlite] SQL error: database disk image is malformed - SQLITE3.5.1

2007-11-22 Thread Salles, Joaquim Campos


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: quinta-feira, 22 de novembro de 2007 12:58
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL error: database disk image is malformed -
SQLITE3.5.1

>
>   
> There is a good description of the things that can cause database 
> corruption at http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption 
> which may give you some ideas for things to check.

> Your program can check the database on startup by executing a "pragma 
> integrity_check;" command just like you did from the command line.

> HTH


Dennis Cote

Thanks for the help.

Joaquim

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



Re: [sqlite] integrity_check needs too much RAM on WinCE

2007-11-22 Thread Thomas Damme
Dennis Cote schrieb:
> Thomas Damme wrote:
>> We set "page_size" and "cache_size" both at 8KB because it had the best
>> performance on the device.
>>   
> Do you have 64 MB of ram to dedicate to sqlite for its cache? That's
> what you are telling it by setting the cache to 8K pages of 8K each.

No we don't. But selects, updates and inserts work as expected. Also for
a long time. That's why we choosed this configuration. Though there are
not many during regular usage.

Anyway, thanks for the reminder. I read that during the development but
it seemed to have no effect(beside the increased performance). We are
experimenting with these values again right now.

Regards,
-- 

Thomas Damme

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



Re: [sqlite] integrity_check needs too much RAM on WinCE

2007-11-22 Thread Dennis Cote

Thomas Damme wrote:

We set "page_size" and "cache_size" both at 8KB because it had the best
performance on the device.
  
Do you have 64 MB of ram to dedicate to sqlite for its cache? That's 
what you are telling it by setting the cache to 8K pages of 8K each.


Dennis Cote


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



Re: [sqlite] integrity_check needs too much RAM on WinCE

2007-11-22 Thread Thomas Damme
Oh, maybe I should also tell you this:

We set "page_size" and "cache_size" both at 8KB because it had the best
performance on the device.

Thomas Damme schrieb:
> Hello Folks,
> 
> we are running a SQLite-Database(version 3.3.5) on a
> WinCE-device(version 4.2). The database is about 90 MB and resides on a
> USB-Stick. The database itself contains of about 500 records with BLOBs
> from 1KB to 100 KB.
> 
> When we are running an "PRAGMA integrity_check" the device stops and
> says "Not enough Program-Memory" in a system-dialogue. So I assume,
> SQLite ate up all dynamic RAM for the check and cannot complete.
> 
> Approx. how much RAM is needed for such a check? Can I prevent this? Is
> this influenced by the BLOB-size?
> 
> Thanks a lot.

-- 

Thomas Damme


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



[sqlite] integrity_check needs too much RAM on WinCE

2007-11-22 Thread Thomas Damme
Hello Folks,

we are running a SQLite-Database(version 3.3.5) on a
WinCE-device(version 4.2). The database is about 90 MB and resides on a
USB-Stick. The database itself contains of about 500 records with BLOBs
from 1KB to 100 KB.

When we are running an "PRAGMA integrity_check" the device stops and
says "Not enough Program-Memory" in a system-dialogue. So I assume,
SQLite ate up all dynamic RAM for the check and cannot complete.

Approx. how much RAM is needed for such a check? Can I prevent this? Is
this influenced by the BLOB-size?

Thanks a lot.
-- 

Thomas Damme


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



Re: [sqlite] SQL error: database disk image is malformed - SQLITE 3.5.1

2007-11-22 Thread Dennis Cote

Salles, Joaquim Campos wrote:


Now running the command "PRAGMA integrity_check":

sqlite> PRAGMA integrity_check;
*** in database main ***
Main freelist: 138691677 of 4 pages missing from overflow list starting
at 2972
On tree page 322 cell 0: 3 of 4 pages missing from overflow list
starting at 297
1
Page 2936 is never used
Page 2941 is never used
Page 2963 is never used
Page 2966 is never used
Page 2968 is never used
Page 2970 is never used
sqlite>


Running the VACUUM command the sqlite terminate abnormally (in windows).


Any one has some idea what I have to check in my program to avoid
corruption in database?

What I can do, in my program, in startup, to check if the database is
ok?

  
There is a good description of the things that can cause database 
corruption at http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption 
which may give you some ideas for things to check.


Your program can check the database on startup by executing a "pragma 
integrity_check;" command just like you did from the command line.


HTH
Dennis Cote



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



[sqlite] SQLite Announcements

2007-11-22 Thread Alberto Simões
Hi

Is it just me, or SQLite announcements for new releases are not being
sent to this list?
At least I didn't see 3.5.0, 3.5.1 and 3.5.2 announcements. I am aware
of them just because complain about them :)

Cheers
ambs
-- 
Alberto Simões

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



[sqlite] SQLITE_THREAD_OVERRIDE_LOCK

2007-11-22 Thread Kiran Kumar.M.R
Hi,

   I am not clear on when this flag must be enabled.

SQLITE_THREAD_OVERRIDE_LOCK

What are the implications of enabling this.

 

Thanks,

Kiran

 

 


***

This e-mail and attachments contain confidential information
from HUAWEI, which is intended only for the person or entity whose address
is listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient's) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!


***

 



[sqlite] SQL error: database disk image is malformed - SQLITE 3.5.1

2007-11-22 Thread Salles, Joaquim Campos
I'm using SQLITE 3.5.1 - in Linux, Had Red 7.1, with a single thread
application - and 2 different programs accessing the same database. I am
getting the following message:
SQL error: database disk image is malformed.

I copy the database to windows and run with sqlite and also get the same
message. Running the following select command I get the same message:

Select * from IDMsg;



SQL error: database disk image is malformed.



Now running the command "PRAGMA integrity_check":

sqlite> PRAGMA integrity_check;
*** in database main ***
Main freelist: 138691677 of 4 pages missing from overflow list starting
at 2972
On tree page 322 cell 0: 3 of 4 pages missing from overflow list
starting at 297
1
Page 2936 is never used
Page 2941 is never used
Page 2963 is never used
Page 2966 is never used
Page 2968 is never used
Page 2970 is never used
sqlite>


Running the VACUUM command the sqlite terminate abnormally (in windows).


Any one has some idea what I have to check in my program to avoid
corruption in database?

What I can do, in my program, in startup, to check if the database is
ok?

Thanks for the help,

Joaquim

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



Re: [sqlite] error

2007-11-22 Thread nishit sharma
Thanks

On Nov 22, 2007 6:35 PM, Dennis Povshedny <[EMAIL PROTECTED]> wrote:
> Hi!
>
> Place the limit clause at the end.
>
> select * from databaseentry where Sub="BarsandPubs" order by Button_Name
> limit 5;
>
> Regards,
> Dennis
>
>
> Xeepe Phone Solution Team
> http://en.xeepe.com
> mailto:[EMAIL PROTECTED]
> sip:[EMAIL PROTECTED]
>
>
> -Original Message-
> From: nishit sharma [mailto:[EMAIL PROTECTED]
> Sent: Thursday, November 22, 2007 3:51 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] error
>
>
> After making command i m getting this error
>
> select * from databaseentry where Sub="BarsandPubs" limit 5 order by
> Button_Name; SQL error: near "order": syntax error  can anybody help
>
> regards
> Nishit
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.503 / Virus Database: 269.16.3/1144 - Release Date:
> 21.11.2007 16:28
>
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.503 / Virus Database: 269.16.3/1144 - Release Date:
> 21.11.2007 16:28
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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



Re: [sqlite] Resetting a Primary Key

2007-11-22 Thread Asif Lodhi
Hi Vincent,

On 11/21/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> I have a primary key that auto increments and has apparently
> overlapped back on to itself.
>
> INSERT into mytable(id,name) values(NULL,'test');
>
> .. is giving me "primary key must be unique" errors.
>
> How can I reset the sequence for a primary key? The table only has
> about 15000 records in it and I've never seen this happen before..

Though I haven't used sqlite but apparently you must have stored NULL
once into the table and were storing it a second time which led to
this error because this violated the primary key constraint. You could
have COUNTed NULLs instead on ItemID using different values for other
relevant columns to use GROUP BY to check the data yourself and see
what was wrong.

--
Asif

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