[sqlite] [Fwd: FTS statistics and stemming]

[Stephen Woodbridge]

Hello Scott et al,

I posted this to the list a while back but never got a response from 
anyone. It would be great if you could take 5 mins to read and respond 
to this. I have been reading through the fts3 code, but there is a lot 
of it and I'm not sure how it works from a big picture let along in any 
of its details. My assumption is that there are no stats at the moment, 
but I'm not opposed to writing some code the cycles through the indexes 
and tables to compute some stats for my needs or even the general case 
if I had some pointers on:

1) where are the words indexed
2) how can I cycle through the words in C to build some stats

pointers to existing code would be fine and I can focus reading that code.

So from reading through the various fts documents and posts and the code 
I think I understand the there is a blob with a structure something like:

word [doc_id, offset, offset, ...], [doc_id, offset, ...], ...

is this correct? where is this stored?

Best regards,
   -Stephen Woodbridge

 Original Message 
Subject: [sqlite] FTS statistics and stemming
Date: Sat, 05 Jul 2008 23:30:55 -0500
From: Stephen Woodbridge <[EMAIL PROTECTED]>
Reply-To: General Discussion of SQLite Database 
To: General Discussion of SQLite Database 

Hi,

First let me say that FTS3 is really awesome. This is my first
experience playing with FTS and it works very nicely with the PORTER
stemming.

My particular use for FTS is not document text but addresses and it
would be very useful if there were a way to analyze the FTS index to get
statistics on the keys. I could then use this information to make a
custom parser/stemmer that could eliminate stop words.

For example, Rd, road, st, street, etc would be overly represented and
not very discriminating, so these should/could be removed. Ideally this
list should be generated based on loading the data, the analyzing the
index, then updating the stemmer to remove the new stop works and again
analyzing and adjusting if needed.

Is this possible? How?

If I had to code this where would I start, I would like to get a list of
the keys and a count of how many rows that a given key is represented
in. I assume a token that is represented multiple times in a document is
represented by a list of offsets, so I can also get a count of the
number of time it show in each document somehow. I think I have figured
this much out by reading all the posts on FTS in the archive.

Thanks,
-Steve
___
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] General help, a bit OT

[Robert Simpson]

Without causing a rehash of that long-debated topic, I'll say this:  As it
relates to SQLite, generally you will get better performance out of an
optimized C application talking to SQLite than you will out of .NET talking
to SQLite.

The difference can be insignificant to a user application however -- as
someone is probably not going to notice the difference between your C app
inserting 100,000 rows a second vs. .NET's 90,000 rows a second.

I did a simple insert/read/update test comparing the ADO.NET provider to raw
C++, results here:

http://sqlite.phxsoftware.com/forums/t/19.aspx


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of cstrader
Sent: Tuesday, July 15, 2008 5:36 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] General help, a bit OT

is it true that C is always (or even generally) faster than .net? 


- Original Message - 
From: "Szomraky, Stefan" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, July 15, 2008 8:40 AM
Subject: Re: [sqlite] General help, a bit OT


>> -Original Message-
>> From: [EMAIL PROTECTED] 
>> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
>> Sent: Tuesday, July 15, 2008 2:28 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] General help, a bit OT
>> 
>> Oh, sorry, the example was just an example.  The calculations 
>> I need to make on on ColC cannot be done within SQLite.
>>
>> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
>> >
> 
> AFAIK, the current .NET provider for SQLITE supports custom functions
> implemented in .NET.
> Maybe this will work?
> 
> SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1;
> 
> You might also want to implement the function in C for speed
> improvements.
> 
> Greetings,
> Stefan.
> ___
> 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] General help, a bit OT

[cstrader]

is it true that C is always (or even generally) faster than .net? 


- Original Message - 
From: "Szomraky, Stefan" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, July 15, 2008 8:40 AM
Subject: Re: [sqlite] General help, a bit OT


>> -Original Message-
>> From: [EMAIL PROTECTED] 
>> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
>> Sent: Tuesday, July 15, 2008 2:28 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] General help, a bit OT
>> 
>> Oh, sorry, the example was just an example.  The calculations 
>> I need to make on on ColC cannot be done within SQLite.
>>
>> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
>> >
> 
> AFAIK, the current .NET provider for SQLITE supports custom functions
> implemented in .NET.
> Maybe this will work?
> 
> SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1;
> 
> You might also want to implement the function in C for speed
> improvements.
> 
> Greetings,
> Stefan.
> ___
> 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] General help, a bit OT

[cstrader]

Oh, well that's interesting.  I didn't know I could apply a function to 
create a new column as I read the data.  I have to figure out how to make 
that work in vb.net.

More generally, can I create a new blank column in my datatable as I read 
from an existing set of columns?  Or must I do a copy from an existing 
variable?

Thanks

- Original Message - 
From: "Szomraky, Stefan" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, July 15, 2008 8:40 AM
Subject: Re: [sqlite] General help, a bit OT


>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
>> Sent: Tuesday, July 15, 2008 2:28 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] General help, a bit OT
>>
>> Oh, sorry, the example was just an example.  The calculations
>> I need to make on on ColC cannot be done within SQLite.
>>
>> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
>> >
>
> AFAIK, the current .NET provider for SQLITE supports custom functions
> implemented in .NET.
> Maybe this will work?
>
> SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1;
>
> You might also want to implement the function in C for speed
> improvements.
>
> Greetings,
> Stefan.
> ___
> 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] Recent minor fts3 changes.

[Scott Hess]

Since fts3 development is sort of sporadic, I figure it's worth a
heads-up about a minor feature I've recently checked in.

-scott

***

http://www.sqlite.org/cvstrac/chngview?cn=5417

optimize() function.  This takes all segments in the fts3 index and
merges them into a single segment.  As a side effect, it also removes
deleted-token records from the index, which is actually the behavior
that convinced me to implement it (some fts users had asked
privacy-related questions).  Usage is like:

  SELECT optimize(t) FROM t LIMIT 1;

The use of LIMIT 1 is a hack.  Virtual tables allow local functions,
but the function applies per row of the table.  optimize() tries to be
smart about detecting whether it's being called on an
already-optimized table, so omitting the LIMIT 1 isn't deadly, but it
is wasted effort.

***

http://www.sqlite.org/cvstrac/chngview?cn=5413

While writing optimize(), I wanted to test that when I had deleted
everything from the table, optimize() would delete the entire index.
Due to how virtual table functions are implemented, this was
impossible - because there were no rows remaining, SQLite wouldn't
call optimize() in this case!  I realized that it would be better to
just detect empty and automatically drop the index data.

***

http://www.sqlite.org/cvstrac/chngview?cn=5340

Implements some fts3 testing functions when compiling with
SQLITE_TEST.  In writing the above code, I wanted to be able to write
tests which verified that things were working correctly internally.  I
imagine this code will mutate substantially over time to provide
better testing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] requested patch to rtree.c v1.6

[Steve Friedman]

I happily saw the changes to rtree.c v1.6 and have the following 
requested changes:

1) On a 64-bit machine, compilation generates two warnings:

sqlite3.c: In function ‘rtreeCreate’:
sqlite3.c:90889: warning: cast from pointer to integer of different size
sqlite3.c: In function ‘rtreeConnect’:
sqlite3.c:90902: warning: cast from pointer to integer of different size

(These lines refer to the casts on lines 614 and 623 of rtree.c)


2) In order to compile the rtree code into the amalgamated build (and 
thus not require a .load) with -DSQLITE_ENABLE_RTREE=1, please consider 
the following patch:

--- rtree.c 14 Jul 2008 15:37:01 -  1.6
+++ rtree.c 15 Jul 2008 19:01:44 -
@@ -59,6 +59,9 @@
  #ifndef SQLITE_CORE
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
+typedef sqlite3_int64 i64;
+typedef unsigned char u8;
+typedef unsigned int u32;
  #else
#include "sqlite3.h"
  #endif
@@ -66,9 +69,6 @@
  #include 
  #include 

-typedef sqlite3_int64 i64;
-typedef unsigned char u8;
-typedef unsigned int u32;

  typedef struct Rtree Rtree;
  typedef struct RtreeCursor RtreeCursor;

Steve Friedman

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

Re: [sqlite] IOERR_DIR_FSYNC on main db

[Ken]

This was on an AIX 5.3 system using the JFS filesystem. I wonder what would 
happen on a JFS2, VFS or UFS filesystems?

Ken


Ken <[EMAIL PROTECTED]> wrote: Sorry but after a second read, probably don't 
want the O_DSYNC except for maybe an initial opening of any persistent file ( 
open(o_dsync), close, re-open ?).

Ken  wrote: On AIX, you might want to open the files using  O_DSYNC flag: (see)
http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/basetrf1/open.htm#HDRA1509805

Test results:
Opening for rdonly
kwrite(1, 0xF0373B18, 19)   = 19
open("/home/ixion/T", O_RDONLY|O_LARGEFILE) = 3
fsync(3)Err#9  EBADF
close(3)= 0

Opening for  rdw
kwrite(1, 0xF0373B18, 16)   = 16
open("/home/ixion/T", O_RDWR|O_LARGEFILE)   Err#21 EISDIR
close(-1)   Err#9  EBADF
kfcntl(1, F_GETFL, 0x2BC8)  = 2
kfcntl(2, F_GETFL, 0xF0373B18)  = 2

Dan,
If you want any other quick tests cases run, let me know and I'll put them 
together.

Thanks,
Ken



Ken   wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
>  http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
> basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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



Ken  wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
> http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
>  basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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] IOERR_DIR_FSYNC on main db

[Ken]

Just as a side note this was on an AIX 5.3 system using JFS. I wonder what 
would happen if some other filesystem was in in use, such as VFS,UFS etc?

Ken


Ken <[EMAIL PROTECTED]> wrote: Sorry but after a second read, probably don't 
want the O_DSYNC except for maybe an initial opening of any persistent file ( 
open(o_dsync), close, re-open ?).

Ken  wrote: On AIX, you might want to open the files using  O_DSYNC flag: (see)
http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/basetrf1/open.htm#HDRA1509805

Test results:
Opening for rdonly
kwrite(1, 0xF0373B18, 19)   = 19
open("/home/ixion/T", O_RDONLY|O_LARGEFILE) = 3
fsync(3)Err#9  EBADF
close(3)= 0

Opening for  rdw
kwrite(1, 0xF0373B18, 16)   = 16
open("/home/ixion/T", O_RDWR|O_LARGEFILE)   Err#21 EISDIR
close(-1)   Err#9  EBADF
kfcntl(1, F_GETFL, 0x2BC8)  = 2
kfcntl(2, F_GETFL, 0xF0373B18)  = 2

Dan,
If you want any other quick tests cases run, let me know and I'll put them 
together.

Thanks,
Ken



Ken   wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
>  http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
> basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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



Ken  wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
> http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
>  basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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] IOERR_DIR_FSYNC on main db

[Ken]

Sorry but after a second read, probably don't want the O_DSYNC except for maybe 
an initial opening of any persistent file ( open(o_dsync), close, re-open ?).

Ken <[EMAIL PROTECTED]> wrote: On AIX, you might want to open the files using  
O_DSYNC flag: (see)
http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/basetrf1/open.htm#HDRA1509805

Test results:
Opening for rdonly
kwrite(1, 0xF0373B18, 19)   = 19
open("/home/ixion/T", O_RDONLY|O_LARGEFILE) = 3
fsync(3)Err#9  EBADF
close(3)= 0

Opening for  rdw
kwrite(1, 0xF0373B18, 16)   = 16
open("/home/ixion/T", O_RDWR|O_LARGEFILE)   Err#21 EISDIR
close(-1)   Err#9  EBADF
kfcntl(1, F_GETFL, 0x2BC8)  = 2
kfcntl(2, F_GETFL, 0xF0373B18)  = 2

Dan,
If you want any other quick tests cases run, let me know and I'll put them 
together.

Thanks,
Ken



Ken  <[EMAIL PROTECTED]> wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
>  http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
> basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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



Ken <[EMAIL PROTECTED]> wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
> http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
>  basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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] Windows CE performance

[Filipe Madureira]

Hi,

Thanks for your help.

Just one question, you asked me in your previous e-mail if it where 
INSERT operations.

Do you think it may be related to this? Do you know of some issue about 
INSERTs that can slow performance in the recent versions?
Because the other operations seem better. Only the INSERT seems to have 
performance problems.

Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
- 



[EMAIL PROTECTED] wrote:
> Hello
>
> As usual, there is no general rule. You have to define the best settings for 
> your embedded environement yourself.
>
> Start here:
> "[sqlite] Performance tuning using PRAGMA, other methods"
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg29343.html
>
> Try different settings to get the feeling which PRAGMAs are critical for you.
>
> Daniel
>
> ___
> 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] Windows CE performance

[[EMAIL PROTECTED]]

Hello

As usual, there is no general rule. You have to define the best settings for 
your embedded environement yourself.

Start here:
"[sqlite] Performance tuning using PRAGMA, other methods"
http://www.mail-archive.com/sqlite-users@sqlite.org/msg29343.html

Try different settings to get the feeling which PRAGMAs are critical for you.

Daniel

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

Re: [sqlite] IOERR_DIR_FSYNC on main db

[Ken]

On AIX, you might want to open the files using  O_DSYNC flag: (see)
http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/basetrf1/open.htm#HDRA1509805

Test results:
Opening for rdonly
kwrite(1, 0xF0373B18, 19)   = 19
open("/home/ixion/T", O_RDONLY|O_LARGEFILE) = 3
fsync(3)Err#9  EBADF
close(3)= 0

Opening for rdw
kwrite(1, 0xF0373B18, 16)   = 16
open("/home/ixion/T", O_RDWR|O_LARGEFILE)   Err#21 EISDIR
close(-1)   Err#9  EBADF
kfcntl(1, F_GETFL, 0x2BC8)  = 2
kfcntl(2, F_GETFL, 0xF0373B18)  = 2

Dan,
If you want any other quick tests cases run, let me know and I'll put them 
together.

Thanks,
Ken



Ken <[EMAIL PROTECTED]> wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
> http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
> basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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



Ken <[EMAIL PROTECTED]> wrote: Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan  wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
> http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
> basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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] patch to allow integer rtree keys

[Steve Friedman]

 wrote:
  On Fri, Jul 11, 2008 at 9:23 PM, Steve Friedman
   wrote:
 > I've just started using the rtree extension, and have found that
 > the 32-bit
 > float for the range keys is not appropriate for me.  Please find
 > attached a
 > patch for rtree.c (based on v1.5) that allows for int -OR-
 > unsigned int -OR-
 > float operation.
 >>
 >> What kind of advantages does using int over float have here?
 >>
 >> With a little work it might be possible to select int or float at
 >> runtime. Do other people who know about such things think that this
 >> would be a good option to have?
 >
 > Dan,
 >
 > I think the need for integer support is to avoid floating point rounding
 > errors that might cause you to miss a key otherwise.
 >
 > I think this would be a nice feature to have. I think it should be
 > implemented at runtime because if I ever have an application that need
 > both say time (int) and spatial rtrees (floats) then it puts me into a
 > problem of not being able to support both in a single build.
 >
 > -Steve

I agree with your desire; however, let's not let the perfect become the 
enemy of the good.  My patch satisfies my initial need.  Can I have that 
included in 3.6.0?  A subsequent patch, with the more complete 
implementation to eliminate compile-time restrictions, could be included 
in a later release.

Steve Friedman

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

Re: [sqlite] IOERR_DIR_FSYNC on main db

[Ken]

Dan,
I'll test this with a quick bit of C code and get back to you with the truss 
output.

Good catch Roger!  ( and thanks)

Thanks,
Ken


Dan <[EMAIL PROTECTED]> wrote: 
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY| 
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10)   Err#9  EBADF
>
> My best guess is that the call is rejected is because the directory  
> was
> not opened for writing.
> http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/ 
> basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF  The FileDescriptor parameter is not a valid file descriptor  
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening  
> directories for
> write though).

Ah... Quite possibly. In os_unix.c directories are opened with:

   fd = open(zDirname, O_RDONLY|O_BINARY, 0);


___
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] Datetime issue with the time part

[Sebastien Robillard]

Eric Minbiole wrote:
> From what you describe, it seems that the compiler is performing 
> single-precision, rather than double-precision, math.  After a quick 
> Google search, I found a few posts indicating that Direct3D silently 
> switches the FPU from double to single precision math, presumably in 
> order to improve performance.
>
> While it seems nearly unconscionable that a graphics library would mess 
> with the FPU, the good news is that it appears you can override this 
> default behavior when creating a 3D device.  See "FpuPreserve" flag:
>
> http://msdn.microsoft.com/en-us/library/bb153282(VS.85).aspx
>
> Hope this helps,
>   Eric
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

That was exactly the problem, using the FpuPreserve flag I have no 
problems at all. Thanks for your help.

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

Re: [sqlite] General help, a bit OT

[Szomraky, Stefan]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
> Sent: Tuesday, July 15, 2008 2:28 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] General help, a bit OT
> 
> Oh, sorry, the example was just an example.  The calculations 
> I need to make on on ColC cannot be done within SQLite.
>
> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
> >

AFAIK, the current .NET provider for SQLITE supports custom functions
implemented in .NET.
Maybe this will work?

SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1;

You might also want to implement the function in C for speed
improvements.

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

Re: [sqlite] General help, a bit OT

[cstrader]

Oh, sorry, the example was just an example.  The calculations I need to make 
on on ColC cannot be done within SQLite.


- Original Message - 
From: "Szomraky, Stefan" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, July 15, 2008 8:19 AM
Subject: Re: [sqlite] General help, a bit OT


>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
>> Sent: Tuesday, July 15, 2008 2:03 PM
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] General help, a bit OT
>>
>> A bit off topic, but perhaps someone could direct me.
>>
>> I'm in vb.net
>>
>> I have to read thousands of tables and operate on each one in
>> sequence.  I need to do this fast.
>>
>> I want to read the data from 6 stored columns and many rows
>> from each table.
>>
>> Once the table is read, I need to create derived columns
>> (e.g. (new) Column C = column A + ColumnB).
>
>
> Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?
>
> Greetings,
> Stefan Szomraky.
> ___
> 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] General help, a bit OT

[Szomraky, Stefan]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader
> Sent: Tuesday, July 15, 2008 2:03 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] General help, a bit OT
> 
> A bit off topic, but perhaps someone could direct me.
> 
> I'm in vb.net
> 
> I have to read thousands of tables and operate on each one in 
> sequence.  I need to do this fast.
> 
> I want to read the data from 6 stored columns and many rows 
> from each table.
> 
> Once the table is read, I need to create derived columns 
> (e.g. (new) Column C = column A + ColumnB).


Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work?

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

[sqlite] General help, a bit OT

[cstrader]

A bit off topic, but perhaps someone could direct me.

I'm in vb.net

I have to read thousands of tables and operate on each one in sequence.  I 
need to do this fast.

I want to read the data from 6 stored columns and many rows from each table.

Once the table is read, I need to create derived columns (e.g. (new) Column 
C = column A + ColumnB).

I'm finding it pretty slow to add the new columns to each table before 
working on it.

I'm finding it faster to store the columns to be derived (empty) in the 
database -- but this makes the db larger.

Is there an established, best practice, method for this problem?  More 
generally, is there a place I could look for advice on how to optimize these 
procedures?

Thanks 

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

Re: [sqlite] Windows CE performance

[Filipe Madureira]

Hi,

What PRAGMA settings should I check?
The locking_mode?

Yes, the problem is with INSERT.
I am running about 3500 Inserts inside a transaction (BEGGIN TRANSACTION 
-> COMMIT TRANSACTION)


Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
- 



[EMAIL PROTECTED] wrote:
> Hello Filipe 
>
> Have you already checked your PRAGMA settings four your embedded version? 
> (see http://www.sqlite.org/pragma.html)
>
> On which operation do you have you performance decrease? Is it INSERT 
> performance?
>
> Daniel
> ___
> 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] Windows CE performance

[[EMAIL PROTECTED]]

Hello Filipe 

Have you already checked your PRAGMA settings four your embedded version? (see 
http://www.sqlite.org/pragma.html)

On which operation do you have you performance decrease? Is it INSERT 
performance?

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

[sqlite] Windows CE performance

[Filipe Madureira]

Hi,

I use Windows CE.
I was using a version of SQLite 3.2.2 I got from sourceforge.net
I decided to download the latest 3.5.9, I compiled and everything seems 
to work OK on Windows CE.

But I noticed a very big performance decrease.
In my application I had a process that took about 30 seconds.
Then, I just recompiled with version 3.5.9 an the exact same thing takes 
about 1 minute.

My question is, do I need to modify something for WindowsCE to increase 
performance?


Note: On the Win32, the performance improvement is impressive, but I need 
Windows CE

Thanks

-- 
Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
- 




-- 
Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
- 

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

Re: [sqlite] Problem writing to database using PHP under OS X

[Tim Streater]

At 15:39 14/07/2008, John LeSueur wrote:
>On Mon, Jul 14, 2008 at 4:28 AM, Tim Streater <[EMAIL PROTECTED]>
>wrote:
>
> > I have a small script which shows this problem: if I run it from the
> > command line I can read from and write to the database. If I run it in a
> > browser it fails at the write with "General error: 14 unable to open
> > database file" (having done the read OK
> > first).
> >
> > The database has permissions ugo+rwx which ought to mean anyone can read or
> > write it. This is under OS X 10.5.4.
> >
> > Suggestions welcome - thanks,
> >
> > -- Tim
> >
>
>what about the directory the database is in? sqlite needs to write journal
>files

Yes. Eventually I googled for the entire error message and found some useful 
message threads.

Thanks,

-- Tim

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