Re: [sqlite] Asymmetric keys encryption
On 22 Feb 2011, at 22:03, H. Phil Duby wrote: On Tue, Feb 22, 2011 at 9:07 AM, Philip Graham Willoughby phil.willoug...@strawberrycat.com wrote: On 22 Feb 2011, at 15:41, Max Vlasov wrote: The obvious solution is public-key cryptography. The question is about different ways how it could be implemented with sqlite. The requirement for this system is that it should operate in two modes: - insert-only when no reading operation is used. This mode uses public key to store the data - full-mode when the private key is supplied and any operation is possible. It might work, but it wouldn't be quick. Public-key cryptography is very slow. There are benchmarks on this page (http://www.cryptopp.com/benchmarks.html) but most of what you need to know is in the choice of scale: AES and other shared key systems are in cycles-per-byte and RSA/friends are in megacycles-per-operation. The simple answer to 'public-key' cryptography is very slow', so to not encrypt the complete text. Instead you generate a random key for one of the good [and fast] symmetric encryption implementations, encrypt the complete text with that, and encrypt only the symmetric key using public-key encryption. Indeed; I had interpreted the OPs scenario as logging short snippets (e.g. single syslog entry size), in which case the data is probably short enough to be encrypted in a single RSA operation and nothing is gained (indeed time is lost and space wasted) by using a secondary symmetric key. I should have stated that assumption; apologies all. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT (string field) returns part of contents, then asterisks
Hello list, thanks for helping me out- I guess it was a limitation in the DLL. I resorted to ODBC now, using the SQLITE3ODBC.DLL from http://www.ch-werner.de/sqliteodbc/. Works like a charm. Have a fine day, /T Den 22.02.2011 16:07, Puneet Kishor skrev: On Tue, Feb 22, 2011 at 04:03:22PM +0100, Haldrup Office wrote: Hello list, I'm in the process of writing a little interface tool for notes and adress databases from an iPad. Using MS Word VBA and SQLite3_StdCall.dll. My query looks quite simply put: SELECT ROWID,creation_date,title FROM Note and it runs fine and returns w/o problems. When I iterate through it, though, and I try to read a long note (saved in field 'title') I happen to get some 2700 characters back plus a sequence of asterisks. Probably a limitation of your MS Word VBA and SQLite3_StdCall.dll Am I missing something here? Thank you for your efforts, /T -- Med venlig hilsen, Thomas Besand ___ 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 -- Med venlig hilsen, Thomas Besand J. Haldrup A/S Bredgade 129 DK-9670 Løgstør +45 98 67 10 33 +45 51 57 23 43 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS
It appears that Qt (or more accurately, WebKit) had defined SQLITE_OMIT_LOAD_EXTENSION and a couple of other symbols at compile time, and therefore when Qt opens a database, it uses a slightly different VFS than the default VFS contained in sqlite3.c (i.e., no xDl* members are set). I believe now that I should recompile Qt and make SQLite a plug-in ... or else build it separately and let Qt use the system's SQLite instead of their version. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oracle compatibility mode
Am 23.02.2011 03:28, schrieb Phil Oertel: Hi sqliters, After a recent failed attempt to use SQLite as an in-memory fake Oracle for some of my tests, I'm curious whether anyone has attempted an Oracle compatibility mode for SQLite. H2 and others have this tremendously useful feature, but there doesn't seem to be anything available for those not running on a JVM. Why is it 'tremendously useful'? If you want an Oracle for testing you can always simply setup an Oracle XE somewhere to have the real syntax and features available without any incomplete 'compatibility mode' that always fails to catch the important little nuances that Oracle does differently. And if it is too slow you can throw money at their Times-Ten product too, which is kinda in memory database. The complexity depends on how shallow the 'compatibility' shall be (e.g. do you want the braindead '' = NULL feature of Oracle, or all of their TO_DATE/TO_CHAR stuff, or Stored Procedures, UPDATE RETURNING, the NLS_LANG crazyness, CREATE SEQUENCE, etc. etc.). In general i would say its not worth the trouble. If you need that kind of portability you should probably use some kind of higher level mapper (for example some ORM like sqlalchemy). Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asymmetric keys encryption
On Wed, Feb 23, 2011 at 1:03 AM, H. Phil Duby phild...@phriendly.netwrote: On Tue, Feb 22, 2011 at 9:07 AM, Philip Graham Willoughby phil.willoug...@strawberrycat.com wrote: On 22 Feb 2011, at 15:41, Max Vlasov wrote: The obvious solution is public-key cryptography. The question is about different ways how it could be implemented with sqlite. The requirement for this system is that it should operate in two modes: - insert-only when no reading operation is used. This mode uses public key to store the data - full-mode when the private key is supplied and any operation is possible. It might work, but it wouldn't be quick. Public-key cryptography is very slow. There are benchmarks on this page ( http://www.cryptopp.com/benchmarks.html) but most of what you need to know is in the choice of scale: AES and other shared key systems are in cycles-per-byte and RSA/friends are in megacycles-per-operation. The simple answer to 'public-key' cryptography is very slow', so to not encrypt the complete text. Instead you generate a random key for one of the good [and fast] symmetric encryption implementations, encrypt the complete text with that, and encrypt only the symmetric key using public-key encryption. Good point, Phil. In case of virtual tables and insert-only mode there might be some intermediate caching (for example based on the boundaries of transaction) that uses the same random key. When the transaction ends the random key will be saved encrypted. In the worst case when a single record inserted wrapped in a transaction, one will get worst performance, but inserting a bunch of records will give a performance boost. Thanks for the idea, Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
I'm currently dealing with a similar issue. I've found that the page_size PRAGMA setting can have a dramatic effect on how long it takes to warm up the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes 8.5 seconds. This was done with a reboot between each test. This page recommends a page_size of 4096: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows If I see better performance with the larger page sizes (going to test 16384 and beyond after this) is there any reason not to use them? Greg On Mon, Feb 21, 2011 at 4:37 PM, Stephen Oberholtzer oliverkloz...@gmail.com wrote: On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson pri...@gmail.com wrote: On Mon, Feb 21, 2011 at 11:05 AM, Sven L larvpo...@hotmail.se wrote: Thank you for your detailed explanation! First, can you please tell me how to purge the cache in Windows 7? This could be very useful for my tests! Sorry, dunno for Windows. On Mac OSX it is the purge command, in the development tools. On Linux, you do: echo 3 /prog/sys/vm/drop_caches Just make sure you either (a) quote the 3 (echo '3' /proc/sys/vm/drop_caches) or (b) put a space between the 3 and the . If you don't quote it, and you don't put the space in (echo 3/proc/sys/vm/drop_caches) it doesn't do anything, for reasons I won't go into. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ 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.7.5 : same query written using NOT IN and EXCEPT does not give out same results
On Tue, Feb 22, 2011 at 14:17, Richard Hipp d...@sqlite.org wrote: The query is really more like this: SELECT DISTINCT COALESCE(a.xxx, b.value) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE value NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); The value on the left-hand side of the NOT IN operator is ambiguous: Does it refer to the first column of output or to the value column of the b table? SQLite chooses the latter. Thank you for your answer. Benoit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with VACUUM feature
Hi All, I am using sqlite-amalgamation-3_7_3.zip source in my project. I tested VACUUM command on a DB file which has lot of holes(fragmentation caused by deletion of random records ) but the source file size does not change. Instead sqlite applies the vaccum command and writes data into new temporary file prefixed by etilqs_. Input:MyDb.db with size 23KB (has lot of empty pages caused due to delete operation) *Expected OutPut: after applying Vacuum command, should be MyDb.db with reduced file size of 13KB.* *Actual output: MyDb.db remains size 23KB(size not changes from original) and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as MyDb.db but the size is reduced to 13KB* I applied the VACUUM command on MyDb.db using sqlite3.exe(shell based commands interpreter) and it applies to the MyDb.db whose size beccomes 13KB after the command completion. ? I have ported sqlite-amalgamation-3_7_3.zip on some X platform using WINCE configuration. Everything else seem to work fine except this strange behaviour of VACUUM feature. Is this a bug or i am doing something wrong? Why sqlite writing data of MyDb.db in to temporary file and applying VACUUM on temporary file instead of original file? Please let me know if any of you come across such scenario and have solution for this issue. Thanks a lot. ~Sudha ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
Interesting! I've been using PRAGMA page_size = 4096; in my software. Perhaps I should increase it and see if I can get a performance gain. Does it affect INSERTs too? Date: Tue, 22 Feb 2011 10:59:29 -0800 From: fle...@fletchowns.net To: sqlite-users@sqlite.org Subject: Re: [sqlite] COUNT() extremely slow first time! I'm currently dealing with a similar issue. I've found that the page_size PRAGMA setting can have a dramatic effect on how long it takes to warm up the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes 8.5 seconds. This was done with a reboot between each test. This page recommends a page_size of 4096: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows If I see better performance with the larger page sizes (going to test 16384 and beyond after this) is there any reason not to use them? Greg On Mon, Feb 21, 2011 at 4:37 PM, Stephen Oberholtzer oliverkloz...@gmail.com wrote: On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson pri...@gmail.com wrote: On Mon, Feb 21, 2011 at 11:05 AM, Sven L larvpo...@hotmail.se wrote: Thank you for your detailed explanation! First, can you please tell me how to purge the cache in Windows 7? This could be very useful for my tests! Sorry, dunno for Windows. On Mac OSX it is the purge command, in the development tools. On Linux, you do: echo 3 /prog/sys/vm/drop_caches Just make sure you either (a) quote the 3 (echo '3' /proc/sys/vm/drop_caches) or (b) put a space between the 3 and the . If you don't quote it, and you don't put the space in (echo 3/proc/sys/vm/drop_caches) it doesn't do anything, for reasons I won't go into. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ 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] Problem with VACUUM feature
On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote: *Actual output: MyDb.db remains size 23KB(size not changes from original) and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as MyDb.db but the size is reduced to 13KB* Your problem is probably related to http://www.sqlite.org/cvstrac/tktview?tn=2829 . It's quite legitimate for your symptoms to occur while the database handle is still open but you should not be seeing those files after you have closed the connection to the database. Either you are not closing the database connection properly, or some part of the API you're using is not closing the database connection properly. I'm not familiar with how this problem manifests because I don't use Windows, so I'll leave it up to an expert to tell you if it needs fixing somehow. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug
SQLite version 3.7.5 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE t1 (a INT); sqlite CREATE TABLE t2 (b INT); sqlite CREATE TABLE t3 (a INT); sqlite SELECT * FROM t1 JOIN t2 ON t1.a = t2.b AND a /* obviously t1.a */ != 1 JOIN t3 ON t1.a = t3.a; Error: ambiguous column name: a ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker fle...@fletchowns.net wrote: I'm currently dealing with a similar issue. I've found that the page_size PRAGMA setting can have a dramatic effect on how long it takes to warm up the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes 8.5 seconds. This was done with a reboot between each test. This page recommends a page_size of 4096: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows If I see better performance with the larger page sizes (going to test 16384 and beyond after this) is there any reason not to use them? Greg, you should also take the record size into account. My hypothesis is that if your record is comparatively small (several fits into 1024) the speed of select count will be the same for any page size (my quick tests confirm this). It's interesting to know what is an average size of your record to understand why the numbers are so different. Returning to the original topic, for performance reasons I sometimes recommend using an index created on the id/rowid. It's a strange construct that makes no sense, but actually it sometimes give a speed improvement. This is because any index contains only the data used in it and if the query doesn't require getting additional data from the table it was created for, sqlite only reads this index and nothing else. So to get the fastest count result one can create the following index (assuming id is the alias for rowid) CREATE INDEX [idx_MyTableId] ON [MyTable] ([ID] ) And use the following query SELECT COUNT(id) from (SELECT id FROM MyTable ORDER By Id) Order by here forces using this index and I used outer select since count(id) inside the main select for unknown reasons triggers the table scanning. For any query in my tests that usually takes 5-50 seconds, this one is always less than a second. But is costs a little in term of the size (the index takes space) and the speed of insert. If this is a small price to pay then this may be an answer. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with VACUUM feature
Hi,
I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
it is slightly related to it but the temporary files are created while
running VACUUM command.
---
Ticket 2829:
This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
if( flags (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
-| SQLITE_OPEN_SUBJOURNAL) ){
+| SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){
--
The temp files were created in the below call sequence:
-
62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
53 sqlite3Step() sqlite3.c:59380 0x3af87b34
52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
--
Basically there 2 problems associated when i run VACUUM command.
Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
which are not deleted when main DB is closed.
Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
closing the main DB connection, the size of the main DB file MyDb.db does
not change where as one of the temp file(etilqs_*) will actually contain the
reduced size of the same data as of main DB file.
I am not sure if this is the expected behaviour or there is some bug in the
flow.
Please let me know if there is a solution to resolve this issue.
Thanks,
Sudha
On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin slav...@bigfraud.org wrote:
On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
*Actual output: MyDb.db remains size 23KB(size not changes from original)
and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as
MyDb.db but the size is reduced to 13KB*
Your problem is probably related to
http://www.sqlite.org/cvstrac/tktview?tn=2829
. It's quite legitimate for your symptoms to occur while the database
handle is still open but you should not be seeing those files after you have
closed the connection to the database. Either you are not closing the
database connection properly, or some part of the API you're using is not
closing the database connection properly.
I'm not familiar with how this problem manifests because I don't use
Windows, so I'll leave it up to an expert to tell you if it needs fixing
somehow.
Simon.
___
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] COUNT() extremely slow first time!
Does this trick work on the primary key? If not, why? From: max.vla...@gmail.com Date: Wed, 23 Feb 2011 16:09:04 +0300 To: sqlite-users@sqlite.org Subject: Re: [sqlite] COUNT() extremely slow first time! On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker fle...@fletchowns.net wrote: I'm currently dealing with a similar issue. I've found that the page_size PRAGMA setting can have a dramatic effect on how long it takes to warm up the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes 8.5 seconds. This was done with a reboot between each test. This page recommends a page_size of 4096: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows If I see better performance with the larger page sizes (going to test 16384 and beyond after this) is there any reason not to use them? Greg, you should also take the record size into account. My hypothesis is that if your record is comparatively small (several fits into 1024) the speed of select count will be the same for any page size (my quick tests confirm this). It's interesting to know what is an average size of your record to understand why the numbers are so different. Returning to the original topic, for performance reasons I sometimes recommend using an index created on the id/rowid. It's a strange construct that makes no sense, but actually it sometimes give a speed improvement. This is because any index contains only the data used in it and if the query doesn't require getting additional data from the table it was created for, sqlite only reads this index and nothing else. So to get the fastest count result one can create the following index (assuming id is the alias for rowid) CREATE INDEX [idx_MyTableId] ON [MyTable] ([ID] ) And use the following query SELECT COUNT(id) from (SELECT id FROM MyTable ORDER By Id) Order by here forces using this index and I used outer select since count(id) inside the main select for unknown reasons triggers the table scanning. For any query in my tests that usually takes 5-50 seconds, this one is always less than a second. But is costs a little in term of the size (the index takes space) and the speed of insert. If this is a small price to pay then this may be an answer. Max ___ 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] Compiler warning for v3.7.5 -- signed overflow
Hi,
I've just upgraded from SQLite 3.6.19 to 3.7.5 and have come across the
following compile warning:
sqlite3.c: In function ‘fkLookupParent’:
sqlite3.c:55991: warning: assuming signed overflow does not occur when
assuming that (X - c) = X is always true
How to reproduce:
unzip sqlite-src-3070500.zip
cd sqlite-src-3070500/
./configure --enable-shared --disable-static --disable-tcl
--disable-readline
vi Makefile
Add -Wall to the TCC makefile variable, then:
make
gcc -v yields:
Target: i486-linux-gnu
Thread model: posix
gcc version 4.4.3 (Ubuntu 4.4.3-4ubuntu5)
In my copy of the amalgamated sqlite3.c, I see:
...
SQLITE_PRIVATE void sqlite3VdbeChangeP2(Vdbe *p, int addr, int val){
assert( p!=0 );
assert( addr=0 );
if( p-nOpaddr ){// this is line 55991
p-aOp[addr].p2 = val;
}
}
...
The functions above (sqlite3VdbeChangeP1) and below (sqlite3VdbeChangeP3)
look suspicious too.
I'm a little intrigued that line 55991 is in the sqlite3VdbeChangeP2
function and not, as reported by the compiler, the fkLookupParent function
!?
If I configure for the non-amalgamated build, edit the Makefile to add -Wall
and build, no such error appears !?
I would like to use the amalgamated build (since all the documentation
implores me too), but this warning makes me nervous ...
Graham.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
SQLite version 3.7.5 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE t1 (a INT); sqlite CREATE TABLE t2 (b INT); sqlite CREATE TABLE t3 (a INT); sqlite SELECT * FROM t1 JOIN t2 ON t1.a = t2.b AND a /* obviously t1.a */ != 1 JOIN t3 ON t1.a = t3.a; Error: ambiguous column name: a I think for SQlite it's not that obviously, that t1.a = a. If you join tables which have the same column names, you need to use the table names. (that's what i read in the documentation..) -Artur- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with VACUUM feature
Hi-
On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
temporary files automatically deleted after they are closed. WINCE
doesn't support this flag, so you will see special logic in os_win.c,
wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
files. You mentioned in an earlier post that you had ported to your
platform based on this code. Could you check that your ported code
includes this logic?
-Shane
On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy sudha@gmail.com wrote:
Hi,
I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
it is slightly related to it but the temporary files are created while
running VACUUM command.
---
Ticket 2829:
This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
if( flags (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
- | SQLITE_OPEN_SUBJOURNAL) ){
+ | SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){
--
The temp files were created in the below call sequence:
-
62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
53 sqlite3Step() sqlite3.c:59380 0x3af87b34
52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
--
Basically there 2 problems associated when i run VACUUM command.
Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
which are not deleted when main DB is closed.
Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
closing the main DB connection, the size of the main DB file MyDb.db does
not change where as one of the temp file(etilqs_*) will actually contain the
reduced size of the same data as of main DB file.
I am not sure if this is the expected behaviour or there is some bug in the
flow.
Please let me know if there is a solution to resolve this issue.
Thanks,
Sudha
On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin slav...@bigfraud.org wrote:
On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
*Actual output: MyDb.db remains size 23KB(size not changes from original)
and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as
MyDb.db but the size is reduced to 13KB*
Your problem is probably related to
http://www.sqlite.org/cvstrac/tktview?tn=2829
. It's quite legitimate for your symptoms to occur while the database
handle is still open but you should not be seeing those files after you have
closed the connection to the database. Either you are not closing the
database connection properly, or some part of the API you're using is not
closing the database connection properly.
I'm not familiar with how this problem manifests because I don't use
Windows, so I'll leave it up to an expert to tell you if it needs fixing
somehow.
Simon.
___
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] Compiler warning for v3.7.5 -- signed overflow
On Wed, Feb 23, 2011 at 9:18 AM, Graham Hudspith graham.hudsp...@gmail.comwrote: I would like to use the amalgamated build (since all the documentation implores me too), but this warning makes me nervous ... Please read http://www.sqlite.org/testing.html and especially section 10.0 http://www.sqlite.org/testing.html#staticanalysis -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oracle compatibility mode
Thanks Michael, that's a great response. I didn't know about XE - having that available, I'm much less interested in such a feature. On Feb 23, 2011 2:14 AM, Michael Schlenker m...@contact.de wrote: ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] apostrophes in strings...
Kevin, Thank you, that is what I needed. Now to statisfy my curiosity... What exactly is the KEY value? On Tue, Feb 22, 2011 at 10:39 PM, Kevin Benson kevin.m.ben...@gmail.comwrote: The suggestion apparently derives from comments in attach.c For example: http://gears.googlecode.com/svn/trunk/third_party/sqlite_google/src/attach.c /* ** An SQL user-function registered to do the work of an ATTACH statement. The ** three arguments to the function come directly from an attach statement: ** ** ATTACH DATABASE x AS y KEY z ** ** SELECT sqlite_attach(x, y, z) ** ** If the optional KEY z syntax is omitted, an SQL NULL is passed as the ** third argument. */ -- -- -- --ΞΞ-- ô¿ô¬ K e V i N /¯\ On Tue, Feb 22, 2011 at 9:35 PM, Sam Carleton scarle...@miltonstreet.com wrote: y KEY z ___ 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] Bug
On Wed, Feb 23, 2011 at 04:24:14AM -0800, Wiktor Adamski scratched on the wall: SQLite version 3.7.5 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE t1 (a INT); sqlite CREATE TABLE t2 (b INT); sqlite CREATE TABLE t3 (a INT); sqlite SELECT * FROM t1 JOIN t2 ON t1.a = t2.b AND a /* obviously t1.a */ != 1 JOIN t3 ON t1.a = t3.a; Error: ambiguous column name: a Not a bug, not obviously anything, and just the joy of SQL. In short, you cannot assume conditions are processed left-to-right, including JOIN conditions. You have two tables in your statement with an a column, so any reference, anywhere in the statement, must be qualified or it will be ambiguous. In this case the expression t1.a != 1 would be much more appropriate in a WHERE clause, since it is just a row filter and has nothing to do with the JOIN itself (it only involves one table, after all). If we rewrite the query with that condition in the WHERE clause, the column name is clearly ambiguous (even if the structure of the query tells us that all a columns must have the same value): SELECT * FROM t1 JOIN t2 ON t1.a = t2.b JOIN t3 ON t1.a = t3.a WHERE a != 1; -- which a? Internally, SQLite actually moves all JOIN conditions to the WHERE clause, effectively processing all statement conditions in one batch. That means the statement that SQLite is actually processing looks a lot more like this: SELECT * FROM t1 JOIN t2 JOIN t3 WHERE t1.a = t2.b AND t1.a = t3.a AND a != 1; -- which a? Again, viewed this way, the a reference is clearly ambiguous. Moving the conditions to the WHERE clause is allowed under the SQL spec, as is reordering those conditions, mostly because this is how all JOINs used to be written before the ANSI JOIN syntax came about (which I greatly prefer). You can see more evidence of this in a statement like this, which, at face value, is even more clear about which a you want but still throws an ambiguous column name error: SELECT * FROM t1 JOIN t2 ON a = b -- can't figure out which a this is. JOIN t3 ON t1.a = t3.a; Once again, things become more clear when you realize the statement being processed might look like this: SELECT * FROM t1 JOIN t2 JOIN t3 WHERE t1.a = t3.a AND a = b; -- which a? While these kinds of issues are the root of many headaches and a lot of cursing at the designers of SQL, there are very good reasons for this behavior. First, there are the historical issues in how the language has evolved. It would be very bad to have a query output change just because an alternate syntax was used, especially a syntax that is supposed to be equivalent. But more importantly, moving all the conditions into the WHERE clause allows the query optimizer to consider all the different JOINs and all the different filter conditions at once. This allows it to re-order conditions, filters and joins. For example, the query optimizer might reorder a series of JOINs based off table size and available indexes. Given the ability of a JOIN to generate a vast number of rows, this reordering can have an extremely significant impact on the processing time for a query. So the actual query might join t2 to t3, and then add t1 to the mix, especially if the optimizer could guess that the output of (t2 JOIN t3) was only a handful of rows, while the output of (t1 JOIN t2) might produce a vast number of rows. The take-away from all this is, if you ever mix tables with similar column names, make sure you always qualify your column references, because you really don't know exactly what the query optimizer might do with your statement, and it is better to be safe than sorry. This is not unlike using extra ()s in complex math statements, even if the language doesn't actually require them. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] apostrophes in strings...
I believe the key is used for attaching to encrypted databases. -scott On Wed, Feb 23, 2011 at 7:15 AM, Sam Carleton scarle...@miltonstreet.com wrote: Kevin, Thank you, that is what I needed. Now to statisfy my curiosity... What exactly is the KEY value? On Tue, Feb 22, 2011 at 10:39 PM, Kevin Benson kevin.m.ben...@gmail.comwrote: The suggestion apparently derives from comments in attach.c For example: http://gears.googlecode.com/svn/trunk/third_party/sqlite_google/src/attach.c /* ** An SQL user-function registered to do the work of an ATTACH statement. The ** three arguments to the function come directly from an attach statement: ** ** ATTACH DATABASE x AS y KEY z ** ** SELECT sqlite_attach(x, y, z) ** ** If the optional KEY z syntax is omitted, an SQL NULL is passed as the ** third argument. */ -- -- -- --ΞΞ-- ô¿ô¬ K e V i N /¯\ On Tue, Feb 22, 2011 at 9:35 PM, Sam Carleton scarle...@miltonstreet.com wrote: y KEY z ___ 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] apostrophes in strings...
On Wed, Feb 23, 2011 at 10:15 AM, Sam Carleton scarle...@miltonstreet.comwrote: Kevin, Thank you, that is what I needed. Now to statisfy my curiosity... What exactly is the KEY value? The KEY is used by the (proprietary) SQLite Encryption Extension. It allows you to ATTACH an encrypted database and specify the decryption key. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
If you join tables which have the same column names, you need to use the table names. You are right, but joined tables don't have the same column names. SELECT * FROM t1 JOIN t2 ON a = b -- there is only one 'a' and that select is correctly implemented SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON 1 -- adding another select does't change column names in first join Reported error would be correct in following query: SELECT * FROM t1 JOIN t2 ON t1.a = t2.b JOIN t3 ON t1.a = t3.a AND a ! = 1; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
Hello Greg, I found this to be the case too. The difference between 1K and 8K is staggering. I default all my windows DB's to 8K now. Tuesday, February 22, 2011, 1:59:29 PM, you wrote: GB I'm currently dealing with a similar issue. I've found that the page_size GB PRAGMA setting can have a dramatic effect on how long it takes to warm up GB the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) GB takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes GB 8.5 seconds. This was done with a reboot between each test. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
In short, you cannot assume conditions are processed left-to-right, including JOIN conditions. I admit that i haven't checked the snadart but I did check other engines and they evaluate from left to right. I think that long time ago I'v read in a standart that 3 table join is basicly equivalent to joining first 2 tables and than third (I'm not sure though) which implies left to right. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
On Wed, Feb 23, 2011 at 03:10:02PM +0100, Sven L scratched on the wall: Does this trick work on the primary key? If not, why? Yes, all the time. Defining a column as a PK automatically creates a UNIQUE index over that column. The only exception is when the column is an INTEGER PRIMARY KEY, in which case the column becomes the rowid and uses the table's native index. In that case, to achieve the same result, one would need to manually create an index, as Max outlines below. In addition to fast counts, this setup is also very good for equi-joins, which tend to be somewhat common in most database designs. (An equi-join is when you join table A to table B only for the purpose of filtering rows in A, and don't actually return any values from B as part of the result set.) Because of the way SQLite works internally, there are also tricks of creating indexes with extra columns. For example, if you have a table with 23 columns, but you mostly use the PK and two additional columns, you can create an index over just those tree columns. This will generally result in faster access to those two columns (for reasons that take several pages to explain). Of course, even better would be to break things up into a primary and detail table, but that's a design consideration. Just beware that all of these indexes come at a cost. If you database is strongly read dominated, you might consider some of these techniques. On the other hand, if you're more or less read/write mixed, or write dominated, these techniques will cause an overall performance drop. Indexes can be useful for reads, but they always come at a write (INSERT/UPDATE/DELETE) cost. -j Returning to the original topic, for performance reasons I sometimes recommend using an index created on the id/rowid. It's a strange construct that makes no sense, but actually it sometimes give a speed improvement. This is because any index contains only the data used in it and if the query doesn't require getting additional data from the table it was created for, sqlite only reads this index and nothing else. -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Wed, Feb 23, 2011 at 07:21:22AM -0800, Wiktor Adamski scratched on the wall: If you join tables which have the same column names, you need to use the table names. You are right, but joined tables don't have the same column names. When I say join tables I'm referring to the collective output of ALL join operations in a statement, not individual JOIN expressions. JOINs are associative, so the individual ordering and grouping doesn't really matter, only the final result. So, even in the given statements, the query optimizer may join t1 directly to t3, and then mix in t2. You're assuming ...FROM t1 JOIN t2 ... JOIN t3... is being processed as ... FROM ( ( t1 JOIN t2 ) ... JOIN t3 )... and that assumption is wrong. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
The SQLite cache size is in pages (2000 by default), so by increasing the page size 8x, you're also increasing the cache size and memory requirements by 8x. Not saying it's a bad thing, just something to be aware of. If you want to compare 1K and 8K page size and only compare the effect page size has, you should either increase the cache size to 16000 for 1K pages or decrease the cache to 250 for 8K pages. The other thing to be aware of is that SQLite will not allow a row to cross 2 pages. (It does allow a row to be larger than a page, using an overflow page.) So for example, if your page size is 1024 and row size is 512 bytes, you can fit 2 rows on a page. I'm simplifying this somewhat and ignoring internal SQLite data, but you get the idea. If your row size is 513 bytes, you will have 511 bytes of waste on each page, so 50% of your database will be air. As your row size heads toward 1024 there will be less waste. At 1025 bytes, SQLite will start splitting rows into overflow pages, putting 1024 bytes into the overflow page and 1 byte in the btree page. These numbers aren't right, but illustrate the point. So to find a good page size, experiment and measure. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Feb 23, 2011 at 10:20 AM, Teg t...@djii.com wrote: Hello Greg, I found this to be the case too. The difference between 1K and 8K is staggering. I default all my windows DB's to 8K now. Tuesday, February 22, 2011, 1:59:29 PM, you wrote: GB I'm currently dealing with a similar issue. I've found that the page_size GB PRAGMA setting can have a dramatic effect on how long it takes to warm up GB the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) GB takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes GB 8.5 seconds. This was done with a reboot between each test. -- Best regards, Teg mailto:t...@djii.com ___ 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] Ideas for Searching Names in Contacts
I am trying to build a simple contacts database in SQLite. I want people to be able to search starting with any word of the name. So, if I have: 1,John Smith 2,Simon James 3,Simon Kelly Smith 4,Jimmy Garcia 5,Smith White Jones 6,Simon Kelly Grant ... If a user types Smi, he should get the following results: John Smith Simon Kelly Smith Smith White I am confused as to how to go about structuring this database. So far, all I have is that I could split all the names into individual words and then create a join table that joins every single word with every contact that matches it. So, I would have something like: Smith,1 Smith,3 Smith,5 This handles single-word cases... but if he types in Simon Kelly, I'd like to return: Simon Kelly Smith Simon Kelly Grant Any suggestions on how I should handle and model this requirement? Thanks, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
Thanks for pointing this out! In my case I have spent much time on normalizing my tables, so the row size should be constant in most cases. I do wonder though, what if the row size is 32 bytes? Or is there a minimum? For instance, I have many lookup tables with ID+text (usually around 20 characters): MyID|MyText With a page size of 4096, will SQLite put ~200 rows in one page? Date: Wed, 23 Feb 2011 10:47:03 -0500 From: pri...@gmail.com To: t...@djii.com; sqlite-users@sqlite.org Subject: Re: [sqlite] COUNT() extremely slow first time! The SQLite cache size is in pages (2000 by default), so by increasing the page size 8x, you're also increasing the cache size and memory requirements by 8x. Not saying it's a bad thing, just something to be aware of. If you want to compare 1K and 8K page size and only compare the effect page size has, you should either increase the cache size to 16000 for 1K pages or decrease the cache to 250 for 8K pages. The other thing to be aware of is that SQLite will not allow a row to cross 2 pages. (It does allow a row to be larger than a page, using an overflow page.) So for example, if your page size is 1024 and row size is 512 bytes, you can fit 2 rows on a page. I'm simplifying this somewhat and ignoring internal SQLite data, but you get the idea. If your row size is 513 bytes, you will have 511 bytes of waste on each page, so 50% of your database will be air. As your row size heads toward 1024 there will be less waste. At 1025 bytes, SQLite will start splitting rows into overflow pages, putting 1024 bytes into the overflow page and 1 byte in the btree page. These numbers aren't right, but illustrate the point. So to find a good page size, experiment and measure. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Feb 23, 2011 at 10:20 AM, Teg t...@djii.com wrote: Hello Greg, I found this to be the case too. The difference between 1K and 8K is staggering. I default all my windows DB's to 8K now. Tuesday, February 22, 2011, 1:59:29 PM, you wrote: GB I'm currently dealing with a similar issue. I've found that the page_size GB PRAGMA setting can have a dramatic effect on how long it takes to warm up GB the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) GB takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes GB 8.5 seconds. This was done with a reboot between each test. -- Best regards, Tegmailto:t...@djii.com ___ 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] Problem with VACUUM feature
On Wed, Feb 23, 2011 at 11:50:05AM +0530, Sudha Venkatareddy scratched on the wall: Hi All, Input:MyDb.db with size 23KB (has lot of empty pages caused due to delete operation) *Expected OutPut: after applying Vacuum command, should be MyDb.db with reduced file size of 13KB.* *Actual output: MyDb.db remains size 23KB(size not changes from original) and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as MyDb.db but the size is reduced to 13KB* VACUUM is a two step process. First, the data is copied from the original file to a temp file. This is a high-level copy, where the data is compacted and reordered, and free pages are eliminated. The second step copies the data from the temp file back to the primary file. This is done as a low-level page-by-page copy. It is *not* an OS file copy. By using the page update system already built into SQLite, the copy-back will create a rollback journal and remain transaction-safe for the whole VACUUM process. From the sound of things, the first step is working, but the second step is failing for some reason. My first guess would be that there are permissions issues with creating the rollback file, so the second copy process fails. That's just a guess, however, as there could be a number of other issues. If you can figure out if a rollback file is ever being created, that would help determine if the copy-back is starting, but fails for some reason, or if the copy-back step is failing right from the start. Given the small database size, it might be somewhat hard to figure that out, however-- any rollback is going to be there and gone (or not there at all) very quickly. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ideas for Searching Names in Contacts
On 23 February 2011 15:48, Mohit Sindhwani m...@onghu.com wrote: I am trying to build a simple contacts database in SQLite. I want people to be able to search starting with any word of the name. So, if I have: 1,John Smith 2,Simon James 3,Simon Kelly Smith 4,Jimmy Garcia 5,Smith White Jones 6,Simon Kelly Grant ... If a user types Smi, he should get the following results: John Smith Simon Kelly Smith Smith White I am confused as to how to go about structuring this database. So far, all I have is that I could split all the names into individual words and then create a join table that joins every single word with every contact that matches it. So, I would have something like: Smith,1 Smith,3 Smith,5 This handles single-word cases... but if he types in Simon Kelly, I'd like to return: Simon Kelly Smith Simon Kelly Grant Any suggestions on how I should handle and model this requirement? Thanks, Mohit. I suspect that FTS will do what you want (http://www.sqlite.org/fts3.html) Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with VACUUM feature
Make sure your antivirus is turned off when you run your vacuum test. It's a long-shot, but I've seen some AVs lock files etc... Date: Wed, 23 Feb 2011 10:14:15 -0600 From: j...@kreibi.ch To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem with VACUUM feature On Wed, Feb 23, 2011 at 11:50:05AM +0530, Sudha Venkatareddy scratched on the wall: Hi All, Input:MyDb.db with size 23KB (has lot of empty pages caused due to delete operation) *Expected OutPut: after applying Vacuum command, should be MyDb.db with reduced file size of 13KB.* *Actual output: MyDb.db remains size 23KB(size not changes from original) and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as MyDb.db but the size is reduced to 13KB* VACUUM is a two step process. First, the data is copied from the original file to a temp file. This is a high-level copy, where the data is compacted and reordered, and free pages are eliminated. The second step copies the data from the temp file back to the primary file. This is done as a low-level page-by-page copy. It is *not* an OS file copy. By using the page update system already built into SQLite, the copy-back will create a rollback journal and remain transaction-safe for the whole VACUUM process. From the sound of things, the first step is working, but the second step is failing for some reason. My first guess would be that there are permissions issues with creating the rollback file, so the second copy process fails. That's just a guess, however, as there could be a number of other issues. If you can figure out if a rollback file is ever being created, that would help determine if the copy-back is starting, but fails for some reason, or if the copy-back step is failing right from the start. Given the small database size, it might be somewhat hard to figure that out, however-- any rollback is going to be there and gone (or not there at all) very quickly. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ 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] COUNT() extremely slow first time!
On Wed, Feb 23, 2011 at 11:12 AM, Sven L larvpo...@hotmail.se wrote: Thanks for pointing this out! In my case I have spent much time on normalizing my tables, so the row size should be constant in most cases. I do wonder though, what if the row size is 32 bytes? Or is there a minimum? For instance, I have many lookup tables with ID+text (usually around 20 characters): MyID|MyText With a page size of 4096, will SQLite put ~200 rows in one page? Yes, very roughly. There is other internal information: a header on each page, on each row, on each field, ints are variable length, etc., and SQLite reserves some free space on each page for later inserts. Use sqlite3_analyzer for lots of useful info when picking a page size. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com Date: Wed, 23 Feb 2011 10:47:03 -0500 From: pri...@gmail.com To: t...@djii.com; sqlite-users@sqlite.org Subject: Re: [sqlite] COUNT() extremely slow first time! The SQLite cache size is in pages (2000 by default), so by increasing the page size 8x, you're also increasing the cache size and memory requirements by 8x. Not saying it's a bad thing, just something to be aware of. If you want to compare 1K and 8K page size and only compare the effect page size has, you should either increase the cache size to 16000 for 1K pages or decrease the cache to 250 for 8K pages. The other thing to be aware of is that SQLite will not allow a row to cross 2 pages. (It does allow a row to be larger than a page, using an overflow page.) So for example, if your page size is 1024 and row size is 512 bytes, you can fit 2 rows on a page. I'm simplifying this somewhat and ignoring internal SQLite data, but you get the idea. If your row size is 513 bytes, you will have 511 bytes of waste on each page, so 50% of your database will be air. As your row size heads toward 1024 there will be less waste. At 1025 bytes, SQLite will start splitting rows into overflow pages, putting 1024 bytes into the overflow page and 1 byte in the btree page. These numbers aren't right, but illustrate the point. So to find a good page size, experiment and measure. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Feb 23, 2011 at 10:20 AM, Teg t...@djii.com wrote: Hello Greg, I found this to be the case too. The difference between 1K and 8K is staggering. I default all my windows DB's to 8K now. Tuesday, February 22, 2011, 1:59:29 PM, you wrote: GB I'm currently dealing with a similar issue. I've found that the page_size GB PRAGMA setting can have a dramatic effect on how long it takes to warm up GB the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) GB takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes GB 8.5 seconds. This was done with a reboot between each test. -- Best regards, Teg mailto:t...@djii.com ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] apostrophes in strings...
On 23 Feb 2011, at 3:15pm, Sam Carleton wrote: Thank you, that is what I needed. Now to statisfy my curiosity... What exactly is the KEY value? It took me a second read of this message to grin at it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ideas for Searching Names in Contacts
On 23 Feb 2011, at 3:48pm, Mohit Sindhwani wrote: I am trying to build a simple contacts database in SQLite. I want people to be able to search starting with any word of the name. So, if I have: 1,John Smith 2,Simon James 3,Simon Kelly Smith 4,Jimmy Garcia 5,Smith White Jones 6,Simon Kelly Grant I would not try to make each individual word of a name a row in a table. I think 'LIKE' is designed almost exactly for your problem: http://www.sqlite.org/lang_expr.html#like This means you can search on any component of the name. for instance, SELECT id,name FROM contacts WHERE name LIKE '%ell%' will find all the 'Kelly' entries and also all the 'Ella' entries. Similarly SELECT id,name FROM contacts WHERE name LIKE '%Simon Kelly%' Would return 'Simon Kelly Smith' and 'Simon Kelly Grant' and 'Eric Simon Kelly' and even 'Simon Kellyson' and 'Jossimon Kellysen'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] apostrophes in strings...
On 21/02/2011, at 8:11 AM, Scott Hess wrote: You can also convert: ATTACH DATABASE x AS y KEY z to: SELECT sqlite_attach(x, y, z) where the parameters can be turned into bind arguments. Then embedded quotes won't be an issue. SQLite won't allow an attach statement within a transaction. If I use the select sqlite_attach() function, I presume I can place that within a transaction, yes? If I rollback the transaction, will it detach? Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] apostrophes in strings...
On Wed, Feb 23, 2011 at 12:50 PM, BareFeetWare list@barefeetware.com wrote: On 21/02/2011, at 8:11 AM, Scott Hess wrote: You can also convert: ATTACH DATABASE x AS y KEY z to: SELECT sqlite_attach(x, y, z) where the parameters can be turned into bind arguments. Then embedded quotes won't be an issue. SQLite won't allow an attach statement within a transaction. If I use the select sqlite_attach() function, I presume I can place that within a transaction, yes? If I rollback the transaction, will it detach? Transactions are forbidden by the implementation in attachFunc(), not by the syntax of how you called it. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use sqlite and pthread together?
On Sun, Feb 20, 2011 at 6:36 PM, Samuel Adam a...@certifound.com wrote: On Sun, 20 Feb 2011 14:46:06 -0500, Nico Williams n...@cryptonector.com wrote: I appreciate your extensive (if wildly offtopic) analysis as quoted below. You thoroughly misunderstood what I said, though. Again, my fork()/exec() comment was directed to the same “cultural thing” as you spoke about in a different context; and my object thereby was to posit __why__ *nix kernel developers have more incentive to make sure processes run light. Winapi doesn’t offer a really equivalent pair of syscalls, nor an extensive existing fork-exec practice, so NT kernel developers needn’t optimize that use case; whereas *nix kernel folks must of practical necessity design their process models to support a typical *nix code pattern. If they do not so do, their users will complain bitterly about the overhead of all their daemons’ zillion workers *after* those workers are started with the classic fork()/exec(). Unix _application_ developers have an incentive to keep their processes light-weight, but _kernel_ developers can't do that very much to make fork() faster other than encourage _application_ developers to use posix_spawn(). The semantics of fork() + threads are such that COW is really expensive for processes with large writable resident set sizes -- it is what it is. This being off-topic as it is, I must decline to continue discussing OS process practice in front of 10,000 or so people (or so I heard) who tuned in for discussion about SQLite. You said some very interesting stuff, though, particularly as to the TLB. I’d like to leave the door open to engaging such discussions in an appropriate venue sometime (ENOTIME for the foreseeable future). I thought it was on topic: I'm giving advice to SQLite3 application developers: a) fork-safety is _really_ difficult for complex libraries to implement, so assume fork-unsafe libraries unless the documentation tells you otherwise, b) fork() is not cheap, so use vfork() or better, posix_spawn() if at all possible. You're free to disregard such advice, of course. Cheers, Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite WAL mode
Michael D. Black, Thank you for your suggestion use the sqlite 3.7 WAL mode. We
wrote a small test program to open 2 WAL connections to the main database and
insert 5.4 million rows into a table. The code is shown below. We wiil add
sqlite error handling handling code tomorrow.
The program appears to be running okay but we noticed it took 5 minutes for
the sqlite3_open corresponding to the DROP TABLE statement to complete. Is
there anything we can do to speed up the DROP TABLE? Thank you.
sprintf(Path,%s/mdMatchup.dat,ConfigPath);
if (sqlite3_open(Path,Database)!=SQLITE_OK) {
return mdMUBatchUpdate::ErrorConfigFile;
}
sprintf(Path,%s/mdMatchup.dat,ConfigPath);
if (sqlite3_open(Path,Database2)!=SQLITE_OK) {
return mdMUBatchUpdate::ErrorConfigFile;
}
ReturnValue = sqlite3_create_function(Database, msign, 4, SQLITE_UTF8, NULL,
cIntersectingGroupCache::msignFunc, NULL, NULL);
ReturnValue = sqlite3_create_function(Database2, CombineBlob, 3, SQLITE_UTF8,
NULL,
cIntersectingGroupCache::CombineBlobFunc, NULL, NULL);
strcpy(Command,PRAGMA journal_mode=wal);
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
ReturnValue=sqlite3_prepare(Database2,Command,-1,Statement2,0);
status = sqlite3_step(Statement2);
status = sqlite3_finalize(Statement2);
sprintf(Command,SELECT COUNT(*) FROM sqlite_master WHERE [Name]=\KeyFile\);
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
if (status==SQLITE_ROW){
Count = sqlite3_column_int(Statement,0);
}
status = sqlite3_finalize(Statement);
if (Count == 0){
strcpy(Command,CREATE TABLE [KeyFile] ([Key] CHAR (256), [UserInfo] CHAR
(256), [DupeGroup] INTEGER) );
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
}
else {
strcpy(Command,DROP TABLE [KeyFile]);
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
strcpy(Command,CREATE TABLE [KeyFile] ([Key] CHAR (256), [UserInfo] CHAR
(256), [DupeGroup] INTEGER) );
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with VACUUM feature
Hi,
The porting is done in little tricky way due to limited support from
underlying platform.
Ported code does not completely follow WINDOWS. it is WINDOWS + WINCE
configuration.
Porting is done as below.
*Step 1.* Main macros defnined in the source include:
#define SQLITE_DEBUG 0
#define SQLITE_OS_OTHER 1
#define SQLITE_MUTEX_OTHER_OS 1
#define SQLITE_CORE 1
#define SQLITE_AMALGAMATION 1
SQLITE_OS_OTHER is same as SQLITE_OS_WIN
*Step 2.* SQLITE_OS_WINCE is not defined but isNT() is defined as 1.
#define isNT() 1
Here is the code snippet of otherOsClose() API for your reference.
#define MX_CLOSE_ATTEMPT 3
static int otherOsClose(sqlite3_file *id){
int rc, cnt = 0;
otherOsFile *pFile = (otherOsFile*)id;
assert( id!=0 );
assert( pFile-pShm==0 );
OSTRACE2(CLOSE %d\n, pFile-h);
do{
rc = CloseHandle(pFile-h);
/* SimulateIOError( rc=0; cnt=MX_CLOSE_ATTEMPT; ); */
}while( rc==0 ++cnt MX_CLOSE_ATTEMPT (Sleep(100), 1) );
#if SQLITE_OS_WINCE
#define WINCE_DELETION_ATTEMPTS 3
winceDestroyLock(pFile);
if( pFile-zDeleteOnClose ){
int cnt = 0;
while(
DeleteFileW(pFile-zDeleteOnClose)==0
GetFileAttributesW(pFile-zDeleteOnClose)!=0x
cnt++ WINCE_DELETION_ATTEMPTS
){
Sleep(100); /* Wait a little before trying again */
}
free(pFile-zDeleteOnClose);
}
#endif
OSTRACE3(CLOSE %d %s\n, pFile-h, rc ? ok : failed);
OpenCounter(-1);
return rc ? SQLITE_OK : SQLITE_IOERR;
}
With configuration steps 1 and 2 as mentioned above, will there be any
problems?
Since SQLITE_OS_WINCE is not defined as 1, the file will not be deleted on
close. I can make some hack to enable pFile-zDeleteOnClose and modify the
ported code to delete the file if pFile-zDeleteOnClose is true without
actually defining macro SQLITE_OS_WINCE.
SQLITE_OS_WINCE can not be enabled since it requires winceLocks to be
implemented which can not be supported at the moment.
Thanks,
Sudha
On Wed, Feb 23, 2011 at 8:14 PM, Shane Harrelson sh...@sqlite.org wrote:
Hi-
On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
temporary files automatically deleted after they are closed. WINCE
doesn't support this flag, so you will see special logic in os_win.c,
wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
files. You mentioned in an earlier post that you had ported to your
platform based on this code. Could you check that your ported code
includes this logic?
-Shane
On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy sudha@gmail.com
wrote:
Hi,
I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
it is slightly related to it but the temporary files are created while
running VACUUM command.
---
Ticket 2829:
This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
if( flags (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
-| SQLITE_OPEN_SUBJOURNAL) ){
+| SQLITE_OPEN_SUBJOURNAL |
SQLITE_OPEN_DELETEONCLOSE) ){
--
The temp files were created in the below call sequence:
-
62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
53 sqlite3Step() sqlite3.c:59380 0x3af87b34
52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
--
Basically there 2 problems associated when i run VACUUM command.
Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
which are not deleted when main DB is closed.
Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
closing the main DB connection, the size of the main DB file MyDb.db does
not change where as one of the temp file(etilqs_*) will actually contain
the
reduced size of the same data as of main DB file.
I am not sure if this is the expected behaviour or there is some bug in
the
flow.
Please let me know if there is a solution to resolve this issue.
Thanks,
Sudha
On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin slav...@bigfraud.org
wrote:
On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
*Actual output: MyDb.db remains size 23KB(size not changes from
original)
