Re: [sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

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  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 
> 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 

Re: [sqlite] sqlite WAL mode

[Frank Chang]

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,)!=SQLITE_OK) {
   return mdMUBatchUpdate::ErrorConfigFile;
}
 
sprintf(Path,"%s/mdMatchup.dat",ConfigPath);
if (sqlite3_open(Path,)!=SQLITE_OK) {
return mdMUBatchUpdate::ErrorConfigFile;
}
 
ReturnValue = sqlite3_create_function(Database, "msign", 4, SQLITE_UTF8, NULL,
::msignFunc, NULL, NULL);
ReturnValue = sqlite3_create_function(Database2, "CombineBlob", 3, SQLITE_UTF8, 
NULL,
::CombineBlobFunc, NULL, NULL);
 
strcpy(Command,"PRAGMA journal_mode=wal");
ReturnValue=sqlite3_prepare(Database,Command,-1,,0);

status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
ReturnValue=sqlite3_prepare(Database2,Command,-1,,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,,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,,0);

 status = sqlite3_step(Statement);
 status = sqlite3_finalize(Statement);
}
else {
strcpy(Command,"DROP TABLE [KeyFile]");
ReturnValue=sqlite3_prepare(Database,Command,-1,,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,,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] How to use sqlite and pthread together?

[Nico Williams]

On Sun, Feb 20, 2011 at 6:36 PM, Samuel Adam  wrote:
> On Sun, 20 Feb 2011 14:46:06 -0500, Nico Williams 
> 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] apostrophes in strings...

[Scott Hess]

On Wed, Feb 23, 2011 at 12:50 PM, BareFeetWare
 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] apostrophes in strings...

[BareFeetWare]

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] Ideas for Searching Names in Contacts

[Simon Slavin]

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:



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...

[Simon Slavin]

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] COUNT() extremely slow first time!

[Jim Wilcoxson]

On Wed, Feb 23, 2011 at 11:12 AM, Sven L  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  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] Problem with VACUUM feature

[Sven L]

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] Ideas for Searching Names in Contacts

[Simon Davies]

On 23 February 2011 15:48, 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
> ...
>
> 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

[Jay A. Kreibich]

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] COUNT() extremely slow first time!

[Sven L]

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  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

[sqlite] Ideas for Searching Names in Contacts

[Mohit Sindhwani]

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!

[Jim Wilcoxson]

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  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

Re: [sqlite] Bug

[Jay A. Kreibich]

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!

[Jay A. Kreibich]

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

[Wiktor Adamski]

>   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!

[Teg]

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

[Wiktor Adamski]

> 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] apostrophes in strings...

[Richard Hipp]

On Wed, Feb 23, 2011 at 10:15 AM, Sam Carleton
wrote:

> 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] apostrophes in strings...

[Scott Hess]

I believe the key is used for attaching to encrypted databases.

-scott


On Wed, Feb 23, 2011 at 7:15 AM, Sam Carleton
 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 
> wrote:
>
>> 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 > >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] Bug

[Jay A. Kreibich]

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...

[Sam Carleton]

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 wrote:

> 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  >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] oracle compatibility mode

[Phil Oertel]

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"  wrote:
___
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

[Richard Hipp]

On Wed, Feb 23, 2011 at 9:18 AM, Graham Hudspith
wrote:

>
> 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] Problem with VACUUM feature

[Shane Harrelson]

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  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  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] Bug

[Artur Reilin]

> 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

[sqlite] Compiler warning for v3.7.5 -- signed overflow

[Graham Hudspith]

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->nOp>addr ){// 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] COUNT() extremely slow first time!

[Sven L]

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  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

Re: [sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

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  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!

[Max Vlasov]

On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker  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] Bug

[Wiktor Adamski]

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] Problem with VACUUM feature

[Simon Slavin]

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

Re: [sqlite] COUNT() extremely slow first time!

[Sven L]

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  wrote:
> > > On Mon, Feb 21, 2011 at 11:05 AM, Sven L  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

[sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

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] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

[Benoit Mortgat]

On Tue, Feb 22, 2011 at 14:17, Richard Hipp  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

Re: [sqlite] COUNT() extremely slow first time!

[Greg Barker]

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  wrote:
> > On Mon, Feb 21, 2011 at 11:05 AM, Sven L  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] Asymmetric keys encryption

[Max Vlasov]

On Wed, Feb 23, 2011 at 1:03 AM, H. Phil Duby wrote:

> On Tue, Feb 22, 2011 at 9:07 AM, Philip Graham Willoughby
>  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] oracle compatibility mode

[Michael Schlenker]

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] VFS

[Robert Hairgrove]

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] SELECT (string field) returns part of contents, then asterisks

[Haldrup Office]

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] Asymmetric keys encryption

[Philip Graham Willoughby]

On 22 Feb 2011, at 22:03, H. Phil Duby wrote:

> On Tue, Feb 22, 2011 at 9:07 AM, Philip Graham Willoughby
>  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