date:20070829

[sqlite] sqlite3 Database Pointer multithreading / concurrency

2007-08-29 Thread Ben Mann

I have a multithreaded application which is exhibiting some unexpected
behaviour in the form of SQLITE_BUSY api return codes. I've traced
this back to a fairly simple way to model the problem, but I can't
seem to find any documentation which explicitly describes this issue.
I'll summarise the problem below, and I also have provided a short
console program which can be used to demonstrate the issue.

To my understanding, the behaviour is:

I have two threads, A and B.
1. A opens the database, and gets an sqlite3*
2. A runs a SELECT, which puts the database in a SHARED lock state
3. The OS interrupts A and allows B to run
4. B opens the database, gets an sqlite3*
5. B attempts an INSERT, but is blocked by the SHARED lock state.
6. B puts the database in a PENDING lock state.
7. The OS interrupts B and allows A to continue
8. A calls some function foo()
9. foo() opens the database, and gets an sqlite3*
10. foo() runs a SELECT, which blocks because the database is in a
PENDING lock state.
11. Because foo() is part of thread A, the system is deadlocked.

In my example code, disabling foo() and replacing it with
bar(sqlite3*), which has a pointer to A's sqlite3* will allow the
application to run normally.

So my questions are:
Is this a known feature?
Should functions called by a thread, which has its own DB handle, be
forbidden from opening a new DB handle?

Any answers much appreciated! This is also my first post here so
scathing criticisms and allegations of being a newbie are also welcome
>.<

Example code follows (beware, it's quick and dirty!); this version
will compile as a windows console app (remember to set OS_WIN and
THREADSAFE in compile options):
__
#include 
#include 
#include 
#include 
#include 
#include "sqlite/sqlite3.h"

#define ALONGTIME 1000

void dotdotdot() { //simulate 'other' stuff
volatile int stupid = 0;
while(stupid < 1) stupid++;
}

//use an open connection and select
void bar(sqlite3 *pdb) {
char sql[] = "SELECT * FROM B";
sqlite3_stmt* pstmt;
const char* szTail=0;
int ret;

printf("B");
assert(SQLITE_OK == (ret = sqlite3_prepare(pdb, sql, -1, , )));
ret = sqlite3_step(pstmt);
assert(ret == SQLITE_DONE || ret == SQLITE_ROW);

sqlite3_finalize(pstmt);
}

//open a new connection and select
void foo() {
sqlite3 *pdb;
int ret;
assert(SQLITE_OK == (ret = sqlite3_open("testdb",)));
sqlite3_busy_timeout(pdb,ALONGTIME);

bar(pdb);

sqlite3_close(pdb);
}

//open a connection and select
DWORD WINAPI ThreadA(LPVOID) {
while(1) {
Sleep(7);

sqlite3 *pdb = 0;
char sql[] = "SELECT * FROM A";
sqlite3_stmt* pstmt;
const char* szTail=0;
int ret;

printf("A");
assert(SQLITE_OK == (ret = sqlite3_open("testdb",)));
sqlite3_busy_timeout(pdb,ALONGTIME);
assert(SQLITE_OK == (ret = sqlite3_prepare(pdb, sql, -1,
, )));
ret = sqlite3_step(pstmt);
assert(ret == SQLITE_DONE || ret == SQLITE_ROW);
dotdotdot(); //simulate 'other code' that occurs here and may
use the query

foo();  //call a function that may query the db
//bar(pdb);

dotdotdot(); //simulate 'other code' that occurs here and may
use the query
sqlite3_finalize(pstmt);
sqlite3_close(pdb);
}
}

void TouchDB() {
static int counter = 0;
sqlite3 *pdb = 0;
char *sql;
char *err = 0;
int ret;

assert(SQLITE_OK == (ret = sqlite3_open("testdb",)));
sqlite3_busy_timeout(pdb,ALONGTIME);
printf("C(%d)", counter);
sql = sqlite3_mprintf("INSERT INTO C VALUES(%d);", counter++);
assert(SQLITE_OK == (ret = sqlite3_exec(pdb,sql,0,0,)));
sqlite3_close(pdb);
}

DWORD ThreadB(LPVOID) {
while(!kbhit()) {
Sleep(3);
TouchDB(); //touch the db
}
return 0;
}

void MakeDB()
{
sqlite3 *pdb = 0;
char sql[] ="DROP TABLE IF EXISTS A;"
"DROP TABLE IF EXISTS B;"
"DROP TABLE IF EXISTS C;"
"CREATE TABLE A(ID INTEGER PRIMARY KEY);"
"CREATE TABLE B(ID INTEGER PRIMARY KEY);"
"CREATE TABLE C(ID INTEGER PRIMARY KEY);"
"INSERT INTO A VALUES(1);"
"INSERT INTO B VALUES(2);";
char *err = 0;
int ret;

if (SQLITE_OK == (ret = sqlite3_open("testdb",))) {
sqlite3_busy_timeout(pdb,ALONGTIME);
sqlite3_exec(pdb,sql,0,0,);
sqlite3_close(pdb);
}
}

int main(int argc, char* argv[]) {
MakeDB(); //make the database the first time
CreateThread(NULL,0,,0,0,NULL);

ThreadB(NULL);

return 0;
}

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

Re: [sqlite] version 3.5.0 - Segv

2007-08-29 Thread Ken

Also erros out here, sporadically.
int sqlite3OsWrite(sqlite3_file *id, const void *pBuf, int amt, i64 offset){
  return id->pMethods->xWrite(id, pBuf, amt, offset);
}

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1075841376 (LWP 15747)]
0x0040c413 in sqlite3OsWrite (id=0x55aaa0, pBuf=0x401ffc30, amt=24, 
offset=0) at os.c:38
(gdb) Quit
(gdb) 

Ken <[EMAIL PROTECTED]> wrote: 4 threads, shared_Cache enabled
LOOP 100 
BEGIN
 LOOP 50 times
  INSERT
 end LOOP
 COMMIT

  SELECT COUNT(*) ...
end LOOP


program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1080043872 (LWP 15448)]
moveToChild (pCur=0x569058, newPgno=) at btree.c:3304
(gdb) 


if( rc ) return rc;
  pNewPage->idxParent = pCur->idx;
  pOldPage = pCur->pPage;
  pOldPage->idxShift = 0; < Error Here
  releasePage(pOldPage);
  pCur->pPage = pNewPage;
  pCur->idx = 0;
  pCur->info.nSize = 0;


Ken




Ken <[EMAIL PROTECTED]> wrote: 4 threads, shared_Cache enabled
LOOP 100 
BEGIN
 LOOP 50 times
  INSERT
 end LOOP
 COMMIT

  SELECT COUNT(*) ...
end LOOP


program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1080043872 (LWP 15448)]
moveToChild (pCur=0x569058, newPgno=) at btree.c:3304
(gdb) 


if( rc ) return rc;
  pNewPage->idxParent = pCur->idx;
  pOldPage = pCur->pPage;
  pOldPage->idxShift = 0; < Error Here
  releasePage(pOldPage);
  pCur->pPage = pNewPage;
  pCur->idx = 0;
  pCur->info.nSize = 0;


Ken

[sqlite] version 3.5.0 - Segv

2007-08-29 Thread Ken

4 threads, shared_Cache enabled
LOOP 100 
BEGIN
 LOOP 50 times
  INSERT
 end LOOP
 COMMIT

  SELECT COUNT(*) ...
end LOOP


program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1080043872 (LWP 15448)]
moveToChild (pCur=0x569058, newPgno=) at btree.c:3304
(gdb) 


if( rc ) return rc;
  pNewPage->idxParent = pCur->idx;
  pOldPage = pCur->pPage;
  pOldPage->idxShift = 0; < Error Here
  releasePage(pOldPage);
  pCur->pPage = pNewPage;
  pCur->idx = 0;
  pCur->info.nSize = 0;


Ken

[sqlite] version 3.5.0 - Segv

2007-08-29 Thread Ken

program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1080043872 (LWP 15448)]
moveToChild (pCur=0x569058, newPgno=) at btree.c:3304
(gdb) 


if( rc ) return rc;
  pNewPage->idxParent = pCur->idx;
  pOldPage = pCur->pPage;
  pOldPage->idxShift = 0; < Error Here
  releasePage(pOldPage);
  pCur->pPage = pNewPage;
  pCur->idx = 0;
  pCur->info.nSize = 0;


Ken

Re: [sqlite] Towards SQLite version 3.5.0

2007-08-29 Thread Joe Wilson

--- [EMAIL PROTECTED] wrote:
> =?ISO-8859-1?Q?Daniel_=D6nnerby?= <[EMAIL PROTECTED]> wrote:
> > 
> > The new multithread-features will be great.
> > Do you think that it will be better to share one connection between all 
> > theads in an application or is better to have each thread open a new 
> > connection and use the sqlite3_enable_shared_cache?
> 
> I think you will be much much better off to put every
> thread it is own private address space.  In other words,
> turn each thread into a separate process.  Threads are
> an invention of the devil.  Stay as far away from these
> fiendish abominations as you can get.

:-)

>From a library maker's point of view, I can see your point.
But from a library user's perspective, it's often more convenient to 
use threads over a multi-process approach.

Event-based asynchronous coding tends to turn your code inside out.
Many users like the simpler linear style of coding within a thread 
that keeps business logic together - the Java way.

Multi-threading headaches typically stem from manual shared memory 
coordination. Here's one alternative to mutexes that's getting some 
attention, but probably needs built-in language support to be effective:

http://en.wikipedia.org/wiki/Software_transactional_memory

Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

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

Re: [sqlite] Towards SQLite version 3.5.0

2007-08-29 Thread Michael Hooker

D R H said: >>Threads are an invention of the devil.<<

I rather think human beings were to blame;  but they had probably read a lot 
about the Spanish Inquisition.

Stay as far away from these fiendish abominations as you can get.<<

That's the best bit of programming advice on threads I have ever seen. 
Thank you for not pretending that in fact it's all very straightforward, 
which would make the rest of us feel inadequate.

Michael Hooker

On 29/08/2007 19:52:11, [EMAIL PROTECTED] wrote:

=?ISO-8859-1?Q?Daniel_=D6nnerby?= <[EMAIL PROTECTED]> wrote:
>
> The new multithread-features will be great.
> Do you think that it will be better to share one connection between all

> theads in an application or is better to have each thread open a new
> connection and use the sqlite3_enable_shared_cache?
>

I think you will be much much better off to put every
thread it is own private address space.  In other words,
turn each thread into a separate process.  Threads are
an invention of the devil.  Stay as far away from these
fiendish abominations as you can get.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

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

RE: [sqlite] GUID/UUID in sqlite.

2007-08-29 Thread Prakash Reddy Bande

Thanks,

This actually solves another problem also where the uid generation
algorithm fails to generate a true uuid. Since table u has uuid column
unique, my database will never have a uuid repeated. But the number of
uuids I can have will now be dependent on what integer can accommodate
(I am sure it's a large number and I hope my database will not consume
all of it). Moreover, when the database no longer uses a uuid, it can be
removed from this table too, which might mean a considerable work for
application developer :-).

Regards,

Prakash Reddy Bande
Altair Engg. Inc,
Troy, MI

-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 29, 2007 1:47 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] GUID/UUID in sqlite.

In either case, if you use the UUID as a primary key in more than one
table, you should consider having one table to convert the UUID to a
64-bit id, and use that as the primary keys on the other tables.  If
you have UUID as a primary key, your table will have 2 b-trees, one
for the index of UUID to rowid, the other to map the rowid to row
data.  Once you have 2 such tables, it can be more efficient to break
out the mapping of the UUID to an internal id you use elsewhere.

Example, instead of:

CREATE TABLE t (
  uuid TEXT PRIMARY KEY,
  ...
);
INSERT INTO t (uuid, ...) VALUES (?, ...);

Do:

CREATE TABLE u (
  uuid TEXT UNIQUE,
  internalid INTEGER PRIMARY KEY
);
CREATE TABLE t (
  fk_internalid INTEGER PRIMARY KEY,
  ...
);
BEGIN;
INSERT INTO u (uuid, internalid) VALUES (?, NULL);
INSERT INTO t (fk_internalid, ...) VALUES (LAST_INSERT_ROWID(), ...);
COMMIT;

-scott

On 8/29/07, John Stanton <[EMAIL PROTECTED]> wrote:
> You could experiment with making your 128 bit entity a BLOB.  The
> alternative would be to represent it it in ASCII.  Changing its radix
> would probably be the significant overhead, not the Sqlite storage.
>
> Prakash Reddy Bande wrote:
> > Hi,
> >
> > I am designing a database where-in my column data is UUID.
> > I am trying to figure out which is the best way to handle UUID since
if
> > stored as text the length would be 32 characters (though UUIDs are
128
> > bit size) and select query based on UUIDs might not be really fast
(I
> > might be wrong here.)
> >
> > Regards,
> >
> > Prakash Reddy Bande
> > Altair Engg. Inc,
> > Troy, MI
> >
> >

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

-
> >
>
>
>

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

-
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]

-

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

Re: [sqlite] Towards SQLite version 3.5.0

2007-08-29 Thread drh

=?ISO-8859-1?Q?Daniel_=D6nnerby?= <[EMAIL PROTECTED]> wrote:
> 
> The new multithread-features will be great.
> Do you think that it will be better to share one connection between all 
> theads in an application or is better to have each thread open a new 
> connection and use the sqlite3_enable_shared_cache?
> 

I think you will be much much better off to put every
thread it is own private address space.  In other words,
turn each thread into a separate process.  Threads are
an invention of the devil.  Stay as far away from these
fiendish abominations as you can get.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Re: Shouldn't this query work?

2007-08-29 Thread Kervin L. Pierre

Hello Igor,

Thanks.  That was the problem.  I had been
doing 32 bit math on SQLite's 64 bit integers.

Best regards,
Kervin


--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Kervin L. Pierre
> 
> wrote:
> > I expected this script to work...
> >
> > create table testtable ( testcol int  );
> > insert into testtable ( testcol ) values (
> -2146369472
> > )
> > select * from testtable where ( testcol &
> 4294967295 )
> > = -2146369472
> >
> > Note that 4294967295 in binary is
> > all '1's.
> 
> No, it's 32 zeros followed by 32 ones. SQLite deals
> with 64-bit 
> integers.
> 
> Igor Tandetnik 
> 
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 


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

Re: [sqlite] FTS2 suggestion

2007-08-29 Thread brian kruse

On 8/29/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> What was fts3 will now be fts4.  fts3 will now be
> fts2-with-rowid-fixed.  fts3 is already in the tree, but with an
> #error at the top to force people to not use it without reading a
> comment.  I was planning to turn that off this week (what with the
> SQLite 3.5 stuff going on, might as well!).

Thanks very much for the prompt and detailed update!

-B

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

Re: [sqlite] GUID/UUID in sqlite.

2007-08-29 Thread Scott Hess

In either case, if you use the UUID as a primary key in more than one
table, you should consider having one table to convert the UUID to a
64-bit id, and use that as the primary keys on the other tables.  If
you have UUID as a primary key, your table will have 2 b-trees, one
for the index of UUID to rowid, the other to map the rowid to row
data.  Once you have 2 such tables, it can be more efficient to break
out the mapping of the UUID to an internal id you use elsewhere.

Example, instead of:

CREATE TABLE t (
  uuid TEXT PRIMARY KEY,
  ...
);
INSERT INTO t (uuid, ...) VALUES (?, ...);

Do:

CREATE TABLE u (
  uuid TEXT UNIQUE,
  internalid INTEGER PRIMARY KEY
);
CREATE TABLE t (
  fk_internalid INTEGER PRIMARY KEY,
  ...
);
BEGIN;
INSERT INTO u (uuid, internalid) VALUES (?, NULL);
INSERT INTO t (fk_internalid, ...) VALUES (LAST_INSERT_ROWID(), ...);
COMMIT;

-scott

On 8/29/07, John Stanton <[EMAIL PROTECTED]> wrote:
> You could experiment with making your 128 bit entity a BLOB.  The
> alternative would be to represent it it in ASCII.  Changing its radix
> would probably be the significant overhead, not the Sqlite storage.
>
> Prakash Reddy Bande wrote:
> > Hi,
> >
> > I am designing a database where-in my column data is UUID.
> > I am trying to figure out which is the best way to handle UUID since if
> > stored as text the length would be 32 characters (though UUIDs are 128
> > bit size) and select query based on UUIDs might not be really fast (I
> > might be wrong here.)
> >
> > Regards,
> >
> > Prakash Reddy Bande
> > Altair Engg. Inc,
> > Troy, MI
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

RE: [sqlite] GUID/UUID in sqlite.

2007-08-29 Thread Prakash Reddy Bande

Thanks,

I will try both (ASCII and BLOB) approaches and see speed vs. storage
trade-offs. Any more ideas are welcome.

Regards,

Prakash Reddy Bande
Altair Engg. Inc,
Troy, MI

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 29, 2007 11:26 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] GUID/UUID in sqlite.

You could experiment with making your 128 bit entity a BLOB.  The 
alternative would be to represent it it in ASCII.  Changing its radix 
would probably be the significant overhead, not the Sqlite storage.

Prakash Reddy Bande wrote:
> Hi,
> 
> I am designing a database where-in my column data is UUID.
> I am trying to figure out which is the best way to handle UUID since
if
> stored as text the length would be 32 characters (though UUIDs are 128
> bit size) and select query based on UUIDs might not be really fast (I
> might be wrong here.)
> 
> Regards,
> 
> Prakash Reddy Bande
> Altair Engg. Inc,
> Troy, MI
> 
>

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

-
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]

-

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

Re: [sqlite] FTS2 suggestion

2007-08-29 Thread Scott Hess

Hmm, and a clarification on the n-gram case ... there are no current
plans to implement any n-gram capabilities in fts.  This kind of thing
has been discussed, but since it still seems like a nice-to-have type
thing and not a must-have type thing, no time is being spent on it.  I
have somewhat of a suspicion that this kind of index requires a
materially different model than fts has been using, which might
encourage it to be a completely different virtual table.

-scott


On 8/29/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> A primary constraint of the porter algorithm in fts is that it's
> completely unencumbered open-source.  That may-or-may-not make it a
> great stemmer, of course :-).  One of the reasons it's in there in the
> first place is as an example of an alternative to the very basic
> "simple" fts tokenizer.  One of the near-term goals with Google Gears
> is to improve the tokenizer, and that will probably extend benefits
> out to fts (since Google Gears is also open-source).
>
> Thanks for the link, I'm always looking for reading material!
>
> As far as SQLite having inbuilt search, some projects (Google Gears,
> for example) wanted to use SQLite for reasons other than fulltext
> search.  Rather than try to integrate two distinct projects, we
> decided that it might be cleaner to just make one project a strict
> subsidiary of the other.  So you get fts basically for free once
> you've integrated SQLite into your project.  A side benefit is that
> you don't have to make decisions about where to store your index data,
> and there are no problems with making sure index data and database
> data conform to the same transaction model, these things just happen
> naturally.  This will hopefully make fulltext search more applicable
> in projects where searching is not the core functionality of the
> project.
>
> -scott
>
>
> On 8/29/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:
> > Hello Scott,
> >
> > I have several clarifications with respect to full text search. I'm a 
> > newbie in open source development, so please bear with me if some of the 
> > questions are irrelevant/obvious/nonsense.
> >
> > I was given to understand that the potter stemming algorithm implemented in 
> > fts2 is not robust enough (or rather snowball is more accurate). If fts2(or 
> > 3) has to be made more robust, then what should be the next step. The 
> > following url (I thought) gave the steps to follow rather succinctly:
> >
> > http://web.njit.edu/~wu/teaching/CIS634/GoodProjects/AccessLisa/documentation.php
> >
> > At what stage would n-gram kick in (I assume n-gram would be in conjunction 
> > to snowball/potter). Which would be a good n-gram algorithm to implement.
> >
> > Finally, what's the rationale in having sqlite's own search. Why not use 
> > something like luceneC?
> >
> > Thanks in advance
> >
> > Uma
> >
> > Scott Hess <[EMAIL PROTECTED]> wrote: Porter stemmer is already in there.  
> > The main issue with Porter is
> > that it's English only.
> >
> > There is no general game-plan for fuzzy search at this time, though if
> > someone wants to step into the breech, go for it!  Even a prototype
> > which demonstrates the concepts and problems but isn't
> > production-ready would be worth something.
> >
> > My current focus for the next generation is international support
> > (this is more of a Google Gears project, but with focus on SQLite so
> > there is likely to be stuff checked in on the SQLite side), and more
> > scalable/manageable indexing.  Not a lot of focus on things like
> > quality and recall, mostly because I'm not aware of any major users
> > with enough of an installed baseline to even generate decent metrics.
> > [Basically, solving concrete identified problems rather than looking
> > for ill-defined potential problems.]
> >
> > -scott
> >
> >
> > On 8/24/07, Uma Krishnan  wrote:
> > > Would it not be more useful to first implement potter stemmer algorithm, 
> > > and then to implement n-gram (as I understand n-gram is for cross column 
> > > fuzzy search?). What is the general game plan for FTS3 with regard to 
> > > fuzzy search?
> > >
> > >   Thanks in advance
> > >
> > > "Cesar D. Rodas"  wrote:
> > >   On 23/08/07, Scott Hess wrote:
> > > > On 8/20/07, Cesar D. Rodas wrote:
> > > > > As I know ( I can be wrong ) SQLite Full Text Search is only match 
> > > > > with hole
> > > > > words right? It could not be
> > > > > And also no FT extension to db ( as far I know) is miss spell 
> > > > > tolerant,
> > > >
> > > > Yes, fts is matching exactly. There is some primitive support for
> > > > English stemming using the Porter stemmer, but, honestly, it's not
> > > > well-exercised.
> > > >
> > > > > And
> > > > > I've found this Paper that talks about *Using Superimposed Coding Of 
> > > > > N-Gram
> > > > > Lists For Efficient Inexact Matching*
> > > >
> > > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf
> > >

Re: [sqlite] FTS2 suggestion

2007-08-29 Thread Scott Hess

What was fts3 will now be fts4.  fts3 will now be
fts2-with-rowid-fixed.  fts3 is already in the tree, but with an
#error at the top to force people to not use it without reading a
comment.  I was planning to turn that off this week (what with the
SQLite 3.5 stuff going on, might as well!).

The next generation of fts has been 6 weeks out for ... the entire
year.  Sigh.  At this time it's my highest priority, though, and I'm
not really supposed to be working on anything else, so I'm hopeful
that there will be substantial code checked in by the end of
September.  Going by the fts2 experience, it will probably need 2 or 3
weeks beyond that to really settle into a usable state.

-scott


On 8/29/07, brian kruse <[EMAIL PROTECTED]> wrote:
> On 8/24/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> > My current focus for the next generation is international support
> > (this is more of a Google Gears project, but with focus on SQLite so
> > there is likely to be stuff checked in on the SQLite side), and more
> > scalable/manageable indexing.
>
> Thanks for the update Scott. Given that FTS3 will also presumably fix
> the VACUUM FTS1/2 bug, do you have a timeline for the FTS3 release?
>
> Even a estimate with a +/- 30 day granularity would be nice.
>
> Kind regards,
> -B
>
> Not a lot of focus on things like
> > quality and recall, mostly because I'm not aware of any major users
> > with enough of an installed baseline to even generate decent metrics.
> > [Basically, solving concrete identified problems rather than looking
> > for ill-defined potential problems.]
> >
> > -scott
> >
> >
> > On 8/24/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:
> > > Would it not be more useful to first implement potter stemmer algorithm, 
> > > and then to implement n-gram (as I understand n-gram is for cross column 
> > > fuzzy search?). What is the general game plan for FTS3 with regard to 
> > > fuzzy search?
> > >
> > >   Thanks in advance
> > >
> > > "Cesar D. Rodas" <[EMAIL PROTECTED]> wrote:
> > >   On 23/08/07, Scott Hess wrote:
> > > > On 8/20/07, Cesar D. Rodas wrote:
> > > > > As I know ( I can be wrong ) SQLite Full Text Search is only match 
> > > > > with hole
> > > > > words right? It could not be
> > > > > And also no FT extension to db ( as far I know) is miss spell 
> > > > > tolerant,
> > > >
> > > > Yes, fts is matching exactly. There is some primitive support for
> > > > English stemming using the Porter stemmer, but, honestly, it's not
> > > > well-exercised.
> > > >
> > > > > And
> > > > > I've found this Paper that talks about *Using Superimposed Coding Of 
> > > > > N-Gram
> > > > > Lists For Efficient Inexact Matching*
> > > >
> > > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf
> > > > >
> > > > > I was reading and it is not so hard to implement, but it cost a extra
> > > > > storage space, but I think the benefits are more.
> > > > >
> > > > > Also following this paper could be done a way to match with fragments 
> > > > > of
> > > > > words... what do you think of it?
> > > >
> > > > It's an interesting paper, and I must say that anything which involves
> > > > Bloom Filters automatically draws my attention :-).
> > >
> > > Yeah. I am doing some investigations about that, I love that too. And
> > > I was watching that with n-grams you get a filter to stop common
> > > words, and could be used as a stemming-like algorithm but independent
> > > from the language.
> > >
> > > I was thinking to implement this
> > > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html
> > > when I finish up some things. What do you think of it?
> > >
> > > > While I think spelling-suggestion might be valuable for fts in the
> > > > longer term, I'm not very enthusiastic about this particular model.
> > > > It seems much more useful in the standard indexing model of building
> > > > the index, manually tweaking it, and then doing a ton of queries
> > > > against it. fts is really fairly constrained, because many use-cases
> > > > are more along the lines of update the index quite a bit, and query it
> > > > only a few times.
> > > >
> > > > Also, I think the concepts in the paper might have very significant
> > > > problems handling Unicode, because the bit vectors will get so very
> > > > large. I may be wrong, sometimes the overlapping-vector approach can
> > > > have surprising relevance depending on the frequency distribution of
> > > > the things in the vector. It would need some experimentation to
> > > > figure that out.
> > > >
> > > > Certainly something to bookmark, though.
> > > >
> > > > Thanks,
> > > > scott
> > > >
> > > > -
> > > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > > -
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Cesar D. Rodas
> > >

Re: [sqlite] FTS2 suggestion

2007-08-29 Thread Scott Hess

A primary constraint of the porter algorithm in fts is that it's
completely unencumbered open-source.  That may-or-may-not make it a
great stemmer, of course :-).  One of the reasons it's in there in the
first place is as an example of an alternative to the very basic
"simple" fts tokenizer.  One of the near-term goals with Google Gears
is to improve the tokenizer, and that will probably extend benefits
out to fts (since Google Gears is also open-source).

Thanks for the link, I'm always looking for reading material!

As far as SQLite having inbuilt search, some projects (Google Gears,
for example) wanted to use SQLite for reasons other than fulltext
search.  Rather than try to integrate two distinct projects, we
decided that it might be cleaner to just make one project a strict
subsidiary of the other.  So you get fts basically for free once
you've integrated SQLite into your project.  A side benefit is that
you don't have to make decisions about where to store your index data,
and there are no problems with making sure index data and database
data conform to the same transaction model, these things just happen
naturally.  This will hopefully make fulltext search more applicable
in projects where searching is not the core functionality of the
project.

-scott


On 8/29/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:
> Hello Scott,
>
> I have several clarifications with respect to full text search. I'm a newbie 
> in open source development, so please bear with me if some of the questions 
> are irrelevant/obvious/nonsense.
>
> I was given to understand that the potter stemming algorithm implemented in 
> fts2 is not robust enough (or rather snowball is more accurate). If fts2(or 
> 3) has to be made more robust, then what should be the next step. The 
> following url (I thought) gave the steps to follow rather succinctly:
>
> http://web.njit.edu/~wu/teaching/CIS634/GoodProjects/AccessLisa/documentation.php
>
> At what stage would n-gram kick in (I assume n-gram would be in conjunction 
> to snowball/potter). Which would be a good n-gram algorithm to implement.
>
> Finally, what's the rationale in having sqlite's own search. Why not use 
> something like luceneC?
>
> Thanks in advance
>
> Uma
>
> Scott Hess <[EMAIL PROTECTED]> wrote: Porter stemmer is already in there.  
> The main issue with Porter is
> that it's English only.
>
> There is no general game-plan for fuzzy search at this time, though if
> someone wants to step into the breech, go for it!  Even a prototype
> which demonstrates the concepts and problems but isn't
> production-ready would be worth something.
>
> My current focus for the next generation is international support
> (this is more of a Google Gears project, but with focus on SQLite so
> there is likely to be stuff checked in on the SQLite side), and more
> scalable/manageable indexing.  Not a lot of focus on things like
> quality and recall, mostly because I'm not aware of any major users
> with enough of an installed baseline to even generate decent metrics.
> [Basically, solving concrete identified problems rather than looking
> for ill-defined potential problems.]
>
> -scott
>
>
> On 8/24/07, Uma Krishnan  wrote:
> > Would it not be more useful to first implement potter stemmer algorithm, 
> > and then to implement n-gram (as I understand n-gram is for cross column 
> > fuzzy search?). What is the general game plan for FTS3 with regard to fuzzy 
> > search?
> >
> >   Thanks in advance
> >
> > "Cesar D. Rodas"  wrote:
> >   On 23/08/07, Scott Hess wrote:
> > > On 8/20/07, Cesar D. Rodas wrote:
> > > > As I know ( I can be wrong ) SQLite Full Text Search is only match with 
> > > > hole
> > > > words right? It could not be
> > > > And also no FT extension to db ( as far I know) is miss spell tolerant,
> > >
> > > Yes, fts is matching exactly. There is some primitive support for
> > > English stemming using the Porter stemmer, but, honestly, it's not
> > > well-exercised.
> > >
> > > > And
> > > > I've found this Paper that talks about *Using Superimposed Coding Of 
> > > > N-Gram
> > > > Lists For Efficient Inexact Matching*
> > >
> > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf
> > > >
> > > > I was reading and it is not so hard to implement, but it cost a extra
> > > > storage space, but I think the benefits are more.
> > > >
> > > > Also following this paper could be done a way to match with fragments of
> > > > words... what do you think of it?
> > >
> > > It's an interesting paper, and I must say that anything which involves
> > > Bloom Filters automatically draws my attention :-).
> >
> > Yeah. I am doing some investigations about that, I love that too. And
> > I was watching that with n-grams you get a filter to stop common
> > words, and could be used as a stemming-like algorithm but independent
> > from the language.
> >
> > I was thinking to implement this
> >

Re: [sqlite] FTS2 suggestion

2007-08-29 Thread brian kruse

On 8/24/07, Scott Hess <[EMAIL PROTECTED]> wrote:
>
> My current focus for the next generation is international support
> (this is more of a Google Gears project, but with focus on SQLite so
> there is likely to be stuff checked in on the SQLite side), and more
> scalable/manageable indexing.

Thanks for the update Scott. Given that FTS3 will also presumably fix
the VACUUM FTS1/2 bug, do you have a timeline for the FTS3 release?

Even a estimate with a +/- 30 day granularity would be nice.

Kind regards,
-B

Not a lot of focus on things like
> quality and recall, mostly because I'm not aware of any major users
> with enough of an installed baseline to even generate decent metrics.
> [Basically, solving concrete identified problems rather than looking
> for ill-defined potential problems.]
>
> -scott
>
>
> On 8/24/07, Uma Krishnan <[EMAIL PROTECTED]> wrote:
> > Would it not be more useful to first implement potter stemmer algorithm, 
> > and then to implement n-gram (as I understand n-gram is for cross column 
> > fuzzy search?). What is the general game plan for FTS3 with regard to fuzzy 
> > search?
> >
> >   Thanks in advance
> >
> > "Cesar D. Rodas" <[EMAIL PROTECTED]> wrote:
> >   On 23/08/07, Scott Hess wrote:
> > > On 8/20/07, Cesar D. Rodas wrote:
> > > > As I know ( I can be wrong ) SQLite Full Text Search is only match with 
> > > > hole
> > > > words right? It could not be
> > > > And also no FT extension to db ( as far I know) is miss spell tolerant,
> > >
> > > Yes, fts is matching exactly. There is some primitive support for
> > > English stemming using the Porter stemmer, but, honestly, it's not
> > > well-exercised.
> > >
> > > > And
> > > > I've found this Paper that talks about *Using Superimposed Coding Of 
> > > > N-Gram
> > > > Lists For Efficient Inexact Matching*
> > >
> > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf
> > > >
> > > > I was reading and it is not so hard to implement, but it cost a extra
> > > > storage space, but I think the benefits are more.
> > > >
> > > > Also following this paper could be done a way to match with fragments of
> > > > words... what do you think of it?
> > >
> > > It's an interesting paper, and I must say that anything which involves
> > > Bloom Filters automatically draws my attention :-).
> >
> > Yeah. I am doing some investigations about that, I love that too. And
> > I was watching that with n-grams you get a filter to stop common
> > words, and could be used as a stemming-like algorithm but independent
> > from the language.
> >
> > I was thinking to implement this
> > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html
> > when I finish up some things. What do you think of it?
> >
> > > While I think spelling-suggestion might be valuable for fts in the
> > > longer term, I'm not very enthusiastic about this particular model.
> > > It seems much more useful in the standard indexing model of building
> > > the index, manually tweaking it, and then doing a ton of queries
> > > against it. fts is really fairly constrained, because many use-cases
> > > are more along the lines of update the index quite a bit, and query it
> > > only a few times.
> > >
> > > Also, I think the concepts in the paper might have very significant
> > > problems handling Unicode, because the bit vectors will get so very
> > > large. I may be wrong, sometimes the overlapping-vector approach can
> > > have surprising relevance depending on the frequency distribution of
> > > the things in the vector. It would need some experimentation to
> > > figure that out.
> > >
> > > Certainly something to bookmark, though.
> > >
> > > Thanks,
> > > scott
> > >
> > > -
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > -
> > >
> > >
> >
> >
> >
> > --
> > Cesar D. Rodas
> > http://www.cesarodas.com/
> > Mobile Phone: 595 961 974165
> > Phone: 595 21 645590
> > [EMAIL PROTECTED]
> > [EMAIL PROTECTED]
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

Re: [sqlite] FTS2 suggestion

2007-08-29 Thread Cesar D. Rodas

N-gram is a sequense of N Letters of a word or set of words...

http://en.wikipedia.org/wiki/N-gram



On 29/08/2007, Uma Krishnan <[EMAIL PROTECTED]> wrote:
>
> Hello Scott,
>
> I have several clarifications with respect to full text search. I'm a
> newbie in open source development, so please bear with me if some of the
> questions are irrelevant/obvious/nonsense.
>
> I was given to understand that the potter stemming algorithm implemented
> in fts2 is not robust enough (or rather snowball is more accurate). If
> fts2(or 3) has to be made more robust, then what should be the next step.
> The following url (I thought) gave the steps to follow rather succinctly:
>
>
> http://web.njit.edu/~wu/teaching/CIS634/GoodProjects/AccessLisa/documentation.php
>
> At what stage would n-gram kick in (I assume n-gram would be in
> conjunction to snowball/potter). Which would be a good n-gram algorithm to
> implement.
>
> Finally, what's the rationale in having sqlite's own search. Why not use
> something like luceneC?
>
> Thanks in advance
>
> Uma
>
> Scott Hess <[EMAIL PROTECTED]> wrote: Porter stemmer is already in
> there.  The main issue with Porter is
> that it's English only.
>
> There is no general game-plan for fuzzy search at this time, though if
> someone wants to step into the breech, go for it!  Even a prototype
> which demonstrates the concepts and problems but isn't
> production-ready would be worth something.
>
> My current focus for the next generation is international support
> (this is more of a Google Gears project, but with focus on SQLite so
> there is likely to be stuff checked in on the SQLite side), and more
> scalable/manageable indexing.  Not a lot of focus on things like
> quality and recall, mostly because I'm not aware of any major users
> with enough of an installed baseline to even generate decent metrics.
> [Basically, solving concrete identified problems rather than looking
> for ill-defined potential problems.]
>
> -scott
>
>
> On 8/24/07, Uma Krishnan  wrote:
> > Would it not be more useful to first implement potter stemmer algorithm,
> and then to implement n-gram (as I understand n-gram is for cross column
> fuzzy search?). What is the general game plan for FTS3 with regard to fuzzy
> search?
> >
> >   Thanks in advance
> >
> > "Cesar D. Rodas"  wrote:
> >   On 23/08/07, Scott Hess wrote:
> > > On 8/20/07, Cesar D. Rodas wrote:
> > > > As I know ( I can be wrong ) SQLite Full Text Search is only match
> with hole
> > > > words right? It could not be
> > > > And also no FT extension to db ( as far I know) is miss spell
> tolerant,
> > >
> > > Yes, fts is matching exactly. There is some primitive support for
> > > English stemming using the Porter stemmer, but, honestly, it's not
> > > well-exercised.
> > >
> > > > And
> > > > I've found this Paper that talks about *Using Superimposed Coding Of
> N-Gram
> > > > Lists For Efficient Inexact Matching*
> > >
> > >
> http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf
> > > >
> > > > I was reading and it is not so hard to implement, but it cost a
> extra
> > > > storage space, but I think the benefits are more.
> > > >
> > > > Also following this paper could be done a way to match with
> fragments of
> > > > words... what do you think of it?
> > >
> > > It's an interesting paper, and I must say that anything which involves
> > > Bloom Filters automatically draws my attention :-).
> >
> > Yeah. I am doing some investigations about that, I love that too. And
> > I was watching that with n-grams you get a filter to stop common
> > words, and could be used as a stemming-like algorithm but independent
> > from the language.
> >
> > I was thinking to implement this
> > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html
> > when I finish up some things. What do you think of it?
> >
> > > While I think spelling-suggestion might be valuable for fts in the
> > > longer term, I'm not very enthusiastic about this particular model.
> > > It seems much more useful in the standard indexing model of building
> > > the index, manually tweaking it, and then doing a ton of queries
> > > against it. fts is really fairly constrained, because many use-cases
> > > are more along the lines of update the index quite a bit, and query it
> > > only a few times.
> > >
> > > Also, I think the concepts in the paper might have very significant
> > > problems handling Unicode, because the bit vectors will get so very
> > > large. I may be wrong, sometimes the overlapping-vector approach can
> > > have surprising relevance depending on the frequency distribution of
> > > the things in the vector. It would need some experimentation to
> > > figure that out.
> > >
> > > Certainly something to bookmark, though.
> > >
> > > Thanks,
> > > scott
> > >
> > >
> -
> > > To unsubscribe, send email to [EMAIL

[sqlite] Re: BestMatch and SqliteStatment Clash

2007-08-29 Thread Igor Tandetnik


RaghavendraK 70574
<[EMAIL PROTECTED]> wrote:

One another thing is how does reversing of value and column work,
i mean the internals of it. Does it create any temp table ...


No, it just scans all records one by one, and runs the test for each 
record.



And i see the sqlite documentation of like, which say if the first
char is not a wild char then index
will be helpful.


Only if you have (columnName LIKE 'pattern') test, and a few other 
conditions are met (in particular, the index must use COLLATE NOCASE 
clause since LIKE is case-insensitive by default). An index cannot be 
used for ('string' LIKE columnName) test.


Igor Tandetnik 



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

Re: [sqlite] Re: BestMatch and SqliteStatment Clash

2007-08-29 Thread John Stanton

What is a "best match" anyway?  A fuzzy search?  The LIKE operator gives 
an exact match to a substring.


Simon Davies wrote:

ragha,

you want something to give you a 'best match'. The 'like' operator in
the way you are using it does not do that, but it IS working as it
should.

I am not sure how to make it any clearer.

Rgds,
Simon

On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:


Hi,

Pls see my last post, hope it is clear.

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

- Original Message -
From: Igor Tandetnik <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 7:57 pm
Subject: [sqlite] Re: BestMatch and SqliteStatment Clash



RaghavendraK 70574
<[EMAIL PROTECTED]> wrote:


Best match is "9854002656" among the 2 records.

Pls try this simple one.It will make it clear,

create table test(t text);

insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');

select * from test where '982' like t || '%' order by t desc


limit 1;


above sql tries to model a DST(digit search tree).
Expected output: 98

This works but will not work for earlier data(9854002656).So


seems to


be bug.


The expression ('982' like '98%') evaluates to true. The expression
('982' like '9854002656%') evaluates to false. LIKE operator
behaves
correctly in both cases. There is no bug, just a case of
unrealistic
expectations and/or wishful thinking on your part.

It's not clear what your definition of a "best match" is, but it's
obvious that the test you put into the SELECT statement is not it.
Computers have this nasty annoying habit of doing what you tell
them to
do, not what you want them to do. If you describe the metric you
want to
use to determine the "best" match, perhaps someone would help you
design
a statement that would implement it.

Igor Tandetnik



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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





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




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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread RaghavendraK 70574


Yes,i understand it is no problem,my mistake.

One another thing is how does reversing of value and column work,
i mean the internals of it. Does it create any temp table ...

And i see the sqlite documentation of like, which say if the first char is not 
a wild char then index
will be helpful.

create table test(t text);

insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');

select * from test where '982' like t || '%' order by t desc limit 1;

output: 98 [correct]


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

- Original Message -
From: Dennis Cote <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 8:45 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash

> RaghavendraK 70574 wrote:
> > Hi,
> >
> > There are 2 testcases one works and other fails
> > Hope am clear.
> >
> > SqliteVersion: 3.4.0
> >
> > TestCase 1: works
> >
> > create table test(t text);
> >
> > insert into test values ('9');
> > insert into test values('98');
> > insert into test values('983');
> > insert into test values('9854');
> >
> > select * from test where '982' like t || '%' order by t desc 
> limit 1;
> >
> > output: 98 [correct]
> >
> > TestCase 2: does not work
> > create table 'tbl.7'(ver integer,
> >   column1 text not NULL,
> >   column2 text not NULL,
> >   column3 text not NULL,
> >   column4 text not NULL,
> >   column5 text not NULL,
> >column6 text not NULL,
> >   column7 text not NULL,
> >   column8 text not NULL,
> >   column9 text not NULL,
> >   column10 text not NULL,
> >   primary 
> key(ver,column1,column2,column3,column4,column5));>
> >  insert into 'tbl.7'
> >  values
> >  (7, '9845002655', '1', '1', '1', '1','x','x','x',
> >   'x','x');
> >
> >  insert into 'tbl.7'
> >  values
> >  (7, '9855002655', '1', '1', '1', '1','x','x','x',
> >   'x','x');
> >
> > --Best match for 985 shd be 9855002655
> > select * from 'tbl.7' where '985' like column1 || '%' order by 
> column1 desc limit 1;
> >
> > output: none //this is a bug.
> >
> >
> >> '9854002656%' is not a match for '982', so seems not to be a bug
> >>
> >>
> >
> As has been pointed out several times already your expectations are 
> wrong.
> You need additional code to implement your best match criterion. 
> What 
> you might want to match is the substring of the column up to the 
> length 
> of the target string, not the entire column.
> 
>   select * from tab 
>   where :target like substr(column1,1,length(:target)) || '%' 
>   order by column1 desc limit 1;
> 
> I'm not sure why you think the first such match is the best match, 
> but 
> that is another issue for you to look at.
> 
> Another point, you should be using double quotes around your table 
> name, 
> not single quotes.
> 
> HTH
> Dennis Cote
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 

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

Re: [sqlite] FTS2 suggestion

2007-08-29 Thread Uma Krishnan

Hello Scott,

I have several clarifications with respect to full text search. I'm a newbie in 
open source development, so please bear with me if some of the questions are 
irrelevant/obvious/nonsense.

I was given to understand that the potter stemming algorithm implemented in 
fts2 is not robust enough (or rather snowball is more accurate). If fts2(or 3) 
has to be made more robust, then what should be the next step. The following 
url (I thought) gave the steps to follow rather succinctly:

http://web.njit.edu/~wu/teaching/CIS634/GoodProjects/AccessLisa/documentation.php

At what stage would n-gram kick in (I assume n-gram would be in conjunction to 
snowball/potter). Which would be a good n-gram algorithm to implement.

Finally, what's the rationale in having sqlite's own search. Why not use 
something like luceneC?

Thanks in advance

Uma

Scott Hess <[EMAIL PROTECTED]> wrote: Porter stemmer is already in there.  The 
main issue with Porter is
that it's English only.

There is no general game-plan for fuzzy search at this time, though if
someone wants to step into the breech, go for it!  Even a prototype
which demonstrates the concepts and problems but isn't
production-ready would be worth something.

My current focus for the next generation is international support
(this is more of a Google Gears project, but with focus on SQLite so
there is likely to be stuff checked in on the SQLite side), and more
scalable/manageable indexing.  Not a lot of focus on things like
quality and recall, mostly because I'm not aware of any major users
with enough of an installed baseline to even generate decent metrics.
[Basically, solving concrete identified problems rather than looking
for ill-defined potential problems.]

-scott

On 8/24/07, Uma Krishnan  wrote:
> Would it not be more useful to first implement potter stemmer algorithm, and 
> then to implement n-gram (as I understand n-gram is for cross column fuzzy 
> search?). What is the general game plan for FTS3 with regard to fuzzy search?
>
>   Thanks in advance
>
> "Cesar D. Rodas"  wrote:
>   On 23/08/07, Scott Hess wrote:
> > On 8/20/07, Cesar D. Rodas wrote:
> > > As I know ( I can be wrong ) SQLite Full Text Search is only match with 
> > > hole
> > > words right? It could not be
> > > And also no FT extension to db ( as far I know) is miss spell tolerant,
> >
> > Yes, fts is matching exactly. There is some primitive support for
> > English stemming using the Porter stemmer, but, honestly, it's not
> > well-exercised.
> >
> > > And
> > > I've found this Paper that talks about *Using Superimposed Coding Of 
> > > N-Gram
> > > Lists For Efficient Inexact Matching*
> >
> > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf
> > >
> > > I was reading and it is not so hard to implement, but it cost a extra
> > > storage space, but I think the benefits are more.
> > >
> > > Also following this paper could be done a way to match with fragments of
> > > words... what do you think of it?
> >
> > It's an interesting paper, and I must say that anything which involves
> > Bloom Filters automatically draws my attention :-).
>
> Yeah. I am doing some investigations about that, I love that too. And
> I was watching that with n-grams you get a filter to stop common
> words, and could be used as a stemming-like algorithm but independent
> from the language.
>
> I was thinking to implement this
> http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html
> when I finish up some things. What do you think of it?
>
> > While I think spelling-suggestion might be valuable for fts in the
> > longer term, I'm not very enthusiastic about this particular model.
> > It seems much more useful in the standard indexing model of building
> > the index, manually tweaking it, and then doing a ton of queries
> > against it. fts is really fairly constrained, because many use-cases
> > are more along the lines of update the index quite a bit, and query it
> > only a few times.
> >
> > Also, I think the concepts in the paper might have very significant
> > problems handling Unicode, because the bit vectors will get so very
> > large. I may be wrong, sometimes the overlapping-vector approach can
> > have surprising relevance depending on the frequency distribution of
> > the things in the vector. It would need some experimentation to
> > figure that out.
> >
> > Certainly something to bookmark, though.
> >
> > Thanks,
> > scott
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
>
>
>
> --
> Cesar D. Rodas
> http://www.cesarodas.com/
> Mobile Phone: 595 961 974165
> Phone: 595 21 645590
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
>
> -
> To unsubscribe, send

Re: [sqlite] GUID/UUID in sqlite.

2007-08-29 Thread John Stanton

You could experiment with making your 128 bit entity a BLOB.  The 
alternative would be to represent it it in ASCII.  Changing its radix 
would probably be the significant overhead, not the Sqlite storage.


Prakash Reddy Bande wrote:

Hi,

I am designing a database where-in my column data is UUID.
I am trying to figure out which is the best way to handle UUID since if
stored as text the length would be 32 characters (though UUIDs are 128
bit size) and select query based on UUIDs might not be really fast (I
might be wrong here.)

Regards,

Prakash Reddy Bande
Altair Engg. Inc,
Troy, MI

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




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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread Dennis Cote


RaghavendraK 70574 wrote:

Hi,

There are 2 testcases one works and other fails
Hope am clear.

SqliteVersion: 3.4.0

TestCase 1: works

create table test(t text);

insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');

select * from test where '982' like t || '%' order by t desc limit 1;

output: 98 [correct]

TestCase 2: does not work
create table 'tbl.7'(ver integer,
  column1 text not NULL,
  column2 text not NULL,
  column3 text not NULL,
  column4 text not NULL,
  column5 text not NULL,
   column6 text not NULL,
  column7 text not NULL,
  column8 text not NULL,
  column9 text not NULL,
  column10 text not NULL,
  primary key(ver,column1,column2,column3,column4,column5));

 insert into 'tbl.7'
 values
 (7, '9845002655', '1', '1', '1', '1','x','x','x',
  'x','x');

 insert into 'tbl.7'
 values
 (7, '9855002655', '1', '1', '1', '1','x','x','x',
  'x','x');

--Best match for 985 shd be 9855002655
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc 
limit 1;

output: none //this is a bug.



'9854002656%' is not a match for '982', so seems not to be a bug





As has been pointed out several times already your expectations are wrong.

You need additional code to implement your best match criterion. What 
you might want to match is the substring of the column up to the length 
of the target string, not the entire column.


	select * from tab 
	where :target like substr(column1,1,length(:target)) || '%' 
	order by column1 desc limit 1;


I'm not sure why you think the first such match is the best match, but 
that is another issue for you to look at.


Another point, you should be using double quotes around your table name, 
not single quotes.


HTH
Dennis Cote

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

Re: [sqlite] Re: BestMatch and SqliteStatment Clash

2007-08-29 Thread RaghavendraK 70574


Hi,

What u and Igor say is correct. Thx. 
Actually i was trying to model this.
"http://algo.inria.fr/flajolet/Publications/Flajolet06.pdf;

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

- Original Message -
From: Simon Davies <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 8:18 pm
Subject: Re: [sqlite] Re: BestMatch and SqliteStatment Clash

> ragha,
> 
> you want something to give you a 'best match'. The 'like' operator in
> the way you are using it does not do that, but it IS working as it
> should.
> 
> I am not sure how to make it any clearer.
> 
> Rgds,
> Simon
> 
> On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> >
> > Pls see my last post, hope it is clear.
> >
> > regards
> > ragha
> > 
> **>
>   This email and its attachments contain confidential information from 
> HUAWEI, which is intended only for the person or entity whose address is 
> listed above. Any use of the information contained herein in any way 
> (including, but not limited to, total or partial disclosure, reproduction, or 
> dissemination) by persons other than the intended recipient(s) is prohibited. 
> If you receive this e-mail in error, please notify the sender by phone or 
> email immediately and delete it!
> >  
> *>
> > - Original Message -
> > From: Igor Tandetnik <[EMAIL PROTECTED]>
> > Date: Wednesday, August 29, 2007 7:57 pm
> > Subject: [sqlite] Re: BestMatch and SqliteStatment Clash
> >
> > > RaghavendraK 70574
> > > <[EMAIL PROTECTED]> wrote:
> > > > Best match is "9854002656" among the 2 records.
> > > >
> > > > Pls try this simple one.It will make it clear,
> > > >
> > > > create table test(t text);
> > > >
> > > > insert into test values ('9');
> > > > insert into test values('98');
> > > > insert into test values('983');
> > > > insert into test values('9854');
> > > >
> > > > select * from test where '982' like t || '%' order by t desc
> > > limit 1;
> > > >
> > > > above sql tries to model a DST(digit search tree).
> > > > Expected output: 98
> > > >
> > > > This works but will not work for earlier data(9854002656).So
> > > seems to
> > > > be bug.
> > >
> > > The expression ('982' like '98%') evaluates to true. The 
> expression> > ('982' like '9854002656%') evaluates to false. LIKE 
> operator> > behaves
> > > correctly in both cases. There is no bug, just a case of
> > > unrealistic
> > > expectations and/or wishful thinking on your part.
> > >
> > > It's not clear what your definition of a "best match" is, but it's
> > > obvious that the test you put into the SELECT statement is not it.
> > > Computers have this nasty annoying habit of doing what you tell
> > > them to
> > > do, not what you want them to do. If you describe the metric you
> > > want to
> > > use to determine the "best" match, perhaps someone would help you
> > > design
> > > a statement that would implement it.
> > >
> > > Igor Tandetnik
> > >
> > >
> > > 
> 
> > > -
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > 
> 
> > > -
> > >
> > >
> >
> > --
> ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> ---
> >
> >
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 

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

Re: [sqlite] compiling 3.4.2 on solaris

2007-08-29 Thread Brian Munroe

On 8/29/07, rahed <[EMAIL PROTECTED]> wrote:

>
> Thank you much.
>

Not a problem.  I know how it is; when something compiles cleanly in
Linux, you can almost be  guaranteed you'll need to do some kind of
song and dance to get it to work in Solaris!

cheers

-- brian

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

Re: [sqlite] Re: BestMatch and SqliteStatment Clash

2007-08-29 Thread Simon Davies

ragha,

you want something to give you a 'best match'. The 'like' operator in
the way you are using it does not do that, but it IS working as it
should.

I am not sure how to make it any clearer.

Rgds,
Simon

On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> Pls see my last post, hope it is clear.
>
> regards
> ragha
> **
>  This email and its attachments contain confidential information from HUAWEI, 
> which is intended only for the person or entity whose address is listed 
> above. Any use of the information contained herein in any way (including, but 
> not limited to, total or partial disclosure, reproduction, or dissemination) 
> by persons other than the intended recipient(s) is prohibited. If you receive 
> this e-mail in error, please notify the sender by phone or email immediately 
> and delete it!
>  
> *
>
> - Original Message -
> From: Igor Tandetnik <[EMAIL PROTECTED]>
> Date: Wednesday, August 29, 2007 7:57 pm
> Subject: [sqlite] Re: BestMatch and SqliteStatment Clash
>
> > RaghavendraK 70574
> > <[EMAIL PROTECTED]> wrote:
> > > Best match is "9854002656" among the 2 records.
> > >
> > > Pls try this simple one.It will make it clear,
> > >
> > > create table test(t text);
> > >
> > > insert into test values ('9');
> > > insert into test values('98');
> > > insert into test values('983');
> > > insert into test values('9854');
> > >
> > > select * from test where '982' like t || '%' order by t desc
> > limit 1;
> > >
> > > above sql tries to model a DST(digit search tree).
> > > Expected output: 98
> > >
> > > This works but will not work for earlier data(9854002656).So
> > seems to
> > > be bug.
> >
> > The expression ('982' like '98%') evaluates to true. The expression
> > ('982' like '9854002656%') evaluates to false. LIKE operator
> > behaves
> > correctly in both cases. There is no bug, just a case of
> > unrealistic
> > expectations and/or wishful thinking on your part.
> >
> > It's not clear what your definition of a "best match" is, but it's
> > obvious that the test you put into the SELECT statement is not it.
> > Computers have this nasty annoying habit of doing what you tell
> > them to
> > do, not what you want them to do. If you describe the metric you
> > want to
> > use to determine the "best" match, perhaps someone would help you
> > design
> > a statement that would implement it.
> >
> > Igor Tandetnik
> >
> >
> > 
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > 
> > -
> >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

[sqlite] GUID/UUID in sqlite.

2007-08-29 Thread Prakash Reddy Bande

Hi,

I am designing a database where-in my column data is UUID.
I am trying to figure out which is the best way to handle UUID since if
stored as text the length would be 32 characters (though UUIDs are 128
bit size) and select query based on UUIDs might not be really fast (I
might be wrong here.)

Regards,

Prakash Reddy Bande
Altair Engg. Inc,
Troy, MI

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

Re: [sqlite] Re: BestMatch and SqliteStatment Clash

2007-08-29 Thread RaghavendraK 70574


Hi,

Pls see my last post, hope it is clear.

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

- Original Message -
From: Igor Tandetnik <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 7:57 pm
Subject: [sqlite] Re: BestMatch and SqliteStatment Clash

> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > Best match is "9854002656" among the 2 records.
> >
> > Pls try this simple one.It will make it clear,
> >
> > create table test(t text);
> >
> > insert into test values ('9');
> > insert into test values('98');
> > insert into test values('983');
> > insert into test values('9854');
> >
> > select * from test where '982' like t || '%' order by t desc 
> limit 1;
> >
> > above sql tries to model a DST(digit search tree).
> > Expected output: 98
> >
> > This works but will not work for earlier data(9854002656).So 
> seems to
> > be bug.
> 
> The expression ('982' like '98%') evaluates to true. The expression 
> ('982' like '9854002656%') evaluates to false. LIKE operator 
> behaves 
> correctly in both cases. There is no bug, just a case of 
> unrealistic 
> expectations and/or wishful thinking on your part.
> 
> It's not clear what your definition of a "best match" is, but it's 
> obvious that the test you put into the SELECT statement is not it. 
> Computers have this nasty annoying habit of doing what you tell 
> them to 
> do, not what you want them to do. If you describe the metric you 
> want to 
> use to determine the "best" match, perhaps someone would help you 
> design 
> a statement that would implement it.
> 
> Igor Tandetnik 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 

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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread Simon Davies

hi ragha,

On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> Hi,
>
> There are 2 testcases one works and other fails
> Hope am clear.
>

It is only clear that you are misunderstanding the operation of the
'like' operator.

See Igor's post for explanation...

Rgds,
Simon

On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> Hi,
>
> There are 2 testcases one works and other fails
> Hope am clear.
>
> SqliteVersion: 3.4.0
>
> TestCase 1: works
>
> create table test(t text);
>
> insert into test values ('9');
> insert into test values('98');
> insert into test values('983');
> insert into test values('9854');
>
> select * from test where '982' like t || '%' order by t desc limit 1;
>
> output: 98 [correct]
>
> TestCase 2: does not work
> create table 'tbl.7'(ver integer,
>  column1 text not NULL,
>  column2 text not NULL,
>  column3 text not NULL,
>  column4 text not NULL,
>  column5 text not NULL,
>   column6 text not NULL,
>  column7 text not NULL,
>  column8 text not NULL,
>  column9 text not NULL,
>  column10 text not NULL,
>  primary 
> key(ver,column1,column2,column3,column4,column5));
>
>  insert into 'tbl.7'
>  values
>  (7, '9845002655', '1', '1', '1', '1','x','x','x',
>  'x','x');
>
>  insert into 'tbl.7'
>  values
>  (7, '9855002655', '1', '1', '1', '1','x','x','x',
>  'x','x');
>
> --Best match for 985 shd be 9855002655
> select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc 
> limit 1;
>
> output: none //this is a bug.
>
> **
>  This email and its attachments contain confidential information from HUAWEI, 
> which is intended only for the person or entity whose address is listed 
> above. Any use of the information contained herein in any way (including, but 
> not limited to, total or partial disclosure, reproduction, or dissemination) 
> by persons other than the intended recipient(s) is prohibited. If you receive 
> this e-mail in error, please notify the sender by phone or email immediately 
> and delete it!
>  
> *
>
> - Original Message -
> From: Simon Davies <[EMAIL PROTECTED]>
> Date: Wednesday, August 29, 2007 7:39 pm
> Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
>
> > On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > >
> > > select * from test where '982' like t || '%' order by t desc
> > limit 1;
> > >
> > > This works but will not work for earlier data(9854002656).So
> > seems to be bug.
> > >
> > > regards
> > > ragha
> >
> > '9854002656%' is not a match for '982', so seems not to be a bug
> >
> > Rgds,
> > Simon
> >
> > 
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > 
> > -
> >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread RaghavendraK 70574

Hi,

There are 2 testcases one works and other fails
Hope am clear.

SqliteVersion: 3.4.0

TestCase 1: works

create table test(t text);

insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');

select * from test where '982' like t || '%' order by t desc limit 1;

output: 98 [correct]

TestCase 2: does not work
create table 'tbl.7'(ver integer,
  column1 text not NULL,
  column2 text not NULL,
  column3 text not NULL,
  column4 text not NULL,
  column5 text not NULL,
   column6 text not NULL,
  column7 text not NULL,
  column8 text not NULL,
  column9 text not NULL,
  column10 text not NULL,
  primary key(ver,column1,column2,column3,column4,column5));

 insert into 'tbl.7'
 values
 (7, '9845002655', '1', '1', '1', '1','x','x','x',
  'x','x');

 insert into 'tbl.7'
 values
 (7, '9855002655', '1', '1', '1', '1','x','x','x',
  'x','x');

--Best match for 985 shd be 9855002655
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc 
limit 1;

output: none //this is a bug.

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

- Original Message -
From: Simon Davies <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 7:39 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash

> On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> >
> > select * from test where '982' like t || '%' order by t desc 
> limit 1;
> >
> > This works but will not work for earlier data(9854002656).So 
> seems to be bug.
> >
> > regards
> > ragha
> 
> '9854002656%' is not a match for '982', so seems not to be a bug
> 
> Rgds,
> Simon
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 

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

[sqlite] Re: BestMatch and SqliteStatment Clash

2007-08-29 Thread Igor Tandetnik


RaghavendraK 70574
<[EMAIL PROTECTED]> wrote:

Best match is "9854002656" among the 2 records.

Pls try this simple one.It will make it clear,

create table test(t text);

insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');

select * from test where '982' like t || '%' order by t desc limit 1;

above sql tries to model a DST(digit search tree).
Expected output: 98

This works but will not work for earlier data(9854002656).So seems to
be bug.


The expression ('982' like '98%') evaluates to true. The expression 
('982' like '9854002656%') evaluates to false. LIKE operator behaves 
correctly in both cases. There is no bug, just a case of unrealistic 
expectations and/or wishful thinking on your part.


It's not clear what your definition of a "best match" is, but it's 
obvious that the test you put into the SELECT statement is not it. 
Computers have this nasty annoying habit of doing what you tell them to 
do, not what you want them to do. If you describe the metric you want to 
use to determine the "best" match, perhaps someone would help you design 
a statement that would implement it.


Igor Tandetnik 



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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread Simon Davies

On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>
> select * from test where '982' like t || '%' order by t desc limit 1;
>
> This works but will not work for earlier data(9854002656).So seems to be bug.
>
> regards
> ragha

'9854002656%' is not a match for '982', so seems not to be a bug

Rgds,
Simon

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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread RaghavendraK 70574

Hi,

Best match is "9854002656" among the 2 records.

Pls try this simple one.It will make it clear,

create table test(t text);

insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');

select * from test where '982' like t || '%' order by t desc limit 1;

above sql tries to model a DST(digit search tree).
Expected output: 98

This works but will not work for earlier data(9854002656).So seems to be bug.

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

- Original Message -
From: John Machin <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 6:22 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash

> On 29/08/2007 10:37 PM, RaghavendraK 70574 wrote:
> > Thx. I have modifed it to ?, but 
> > Sqlite fails to get records for the below query. When debug it 
> retuns 
> > SQLITE_DONE. Pls help.
> > 
> > select * from 'tbl.7' where ? like column1 || '%' order by 
> column1 desc limit 1;
> > 
> > Data is as below:
> > Version: 3.4.0
> > Re-confirm the problem in sqlite and not in my code,
> > I tried using sqlite3 commandLine tool and found the same problem.
> > 
> > create table 'tbl.7'(ver integer,
> >  column1 text not NULL,
> >  column2 text not NULL,
> >  column3 text not NULL,
> >  column4 text not NULL,
> >  column5 text not NULL,
> >   column6 text not NULL,
> >  column7 text not NULL,
> >  column8 text not NULL,
> >  column9 text not NULL,
> >  column10 text not NULL,
> >  primary 
> key(ver,column1,column2,column3,column4,column5));> 
> > 
> > insert into 'tbl.7'
> > values
> > (7,
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > 
> 'CO',>
>  
> 'CO',
> > 
> 'CO',>
>  
> 'CO',
> > 
> 'CO');>
>  
> > insert into 'tbl.7'
> > values
> > (7,
> > '9854002656',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > 
> 'CO',>
>  
> 'CO',
> > 
> 'CO',>
>  
> 'CO',
> > 
> 'CO');>
>  
> > --Best match for 985
> > select * from 'tbl.7' where '985' like column1 || '%' order by 
> column1 desc limit 1;
> 
> Can you leave out the incredibly annoying 'COL...' stuff? It's 
> nothing to do with your problem!
> 
> I don't see how *ANY* of your rows will match. Which rows do you 
> expect 
> to match? Why?
> 
> If column1 was '98' (for example), then you would have
> '985' like '98' || '%'
> which is
> '985' like '98%'
> which is true.
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 

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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread John Stanton


Show us your program.

RaghavendraK 70574 wrote:
Thx. I have modifed it to ?, but 
Sqlite fails to get records for the below query. When debug it retuns 
SQLITE_DONE. Pls help.


select * from 'tbl.7' where ? like column1 || '%' order by column1 desc limit 1;

Data is as below:
Version: 3.4.0
Re-confirm the problem in sqlite and not in my code,
I tried using sqlite3 commandLine tool and found the same problem.

create table 'tbl.7'(ver integer,
 column1 text not NULL,
 column2 text not NULL,
 column3 text not NULL,
 column4 text not NULL,
 column5 text not NULL,
 column6 text not NULL,
 column7 text not NULL,
 column8 text not NULL,
 column9 text not NULL,
 column10 text not NULL,
 primary key(ver,column1,column2,column3,column4,column5));


insert into 'tbl.7'
values
(7,
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');

insert into 'tbl.7'
values
(7,
'9854002656',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');

--Best match for 985
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc 
limit 1;


regards
ragha

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

- Original Message -
From: Dan Kennedy <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 7:07 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash



On Wed, 2007-08-29 at 18:37 +0800, RaghavendraK 70574 wrote:


Hi,

Am using sqlite 3.4.0

stmt= sqlite_prepareV2("select * from test where '?'  like t || 


'%' order by t desc);

You need to remove the ' quotes around the question mark.
At the moment the expression is a literal string value, 
not an sql variable. 





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





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




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

[sqlite] Re: BestMatch and SqliteStatment Clash

2007-08-29 Thread Igor Tandetnik


RaghavendraK 70574
<[EMAIL PROTECTED]> wrote:

Thx. I have modifed it to ?, but
Sqlite fails to get records for the below query. When debug it retuns
SQLITE_DONE. Pls help.

select * from 'tbl.7' where ? like column1 || '%' order by column1
desc limit 1;

Data is as below:
insert into 'tbl.7'
values ('9845002655');

insert into 'tbl.7'
values ('9854002656')

--Best match for 985
select * from 'tbl.7' where '985' like column1 || '%' order by
column1 desc limit 1;


Why are you surprised? I don't see any record that would satisfy your 
query. '985' doesn't match either '9845002655%' or '9854002656%' 
patterns. You probably mean


select * from "tbl.7" where column1 like '985%';

Igor Tandetnik 



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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread John Machin


On 29/08/2007 10:37 PM, RaghavendraK 70574 wrote:
Thx. I have modifed it to ?, but 
Sqlite fails to get records for the below query. When debug it retuns 
SQLITE_DONE. Pls help.


select * from 'tbl.7' where ? like column1 || '%' order by column1 desc limit 1;

Data is as below:
Version: 3.4.0
Re-confirm the problem in sqlite and not in my code,
I tried using sqlite3 commandLine tool and found the same problem.

create table 'tbl.7'(ver integer,
 column1 text not NULL,
 column2 text not NULL,
 column3 text not NULL,
 column4 text not NULL,
 column5 text not NULL,
 column6 text not NULL,
 column7 text not NULL,
 column8 text not NULL,
 column9 text not NULL,
 column10 text not NULL,
 primary key(ver,column1,column2,column3,column4,column5));


insert into 'tbl.7'
values
(7,
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');

insert into 'tbl.7'
values
(7,
'9854002656',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');

--Best match for 985
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc 
limit 1;


Can you leave out the incredibly annoying 'COL...' stuff? It's 
nothing to do with your problem!


I don't see how *ANY* of your rows will match. Which rows do you expect 
to match? Why?


If column1 was '98' (for example), then you would have
'985' like '98' || '%'
which is
'985' like '98%'
which is true.


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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread RaghavendraK 70574

Thx. I have modifed it to ?, but 
Sqlite fails to get records for the below query. When debug it retuns 
SQLITE_DONE. Pls help.

select * from 'tbl.7' where ? like column1 || '%' order by column1 desc limit 1;

Data is as below:
Version: 3.4.0
Re-confirm the problem in sqlite and not in my code,
I tried using sqlite3 commandLine tool and found the same problem.

create table 'tbl.7'(ver integer,
 column1 text not NULL,
 column2 text not NULL,
 column3 text not NULL,
 column4 text not NULL,
 column5 text not NULL,
 column6 text not NULL,
 column7 text not NULL,
 column8 text not NULL,
 column9 text not NULL,
 column10 text not NULL,
 primary key(ver,column1,column2,column3,column4,column5));

insert into 'tbl.7'
values
(7,
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');

insert into 'tbl.7'
values
(7,
'9854002656',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');

--Best match for 985
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc 
limit 1;

regards
ragha

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

*

- Original Message -
From: Dan Kennedy <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 7:07 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash

> On Wed, 2007-08-29 at 18:37 +0800, RaghavendraK 70574 wrote:
> > Hi,
> > 
> > Am using sqlite 3.4.0
> > 
> > stmt= sqlite_prepareV2("select * from test where '?'  like t || 
> '%' order by t desc);
> 
> You need to remove the ' quotes around the question mark.
> At the moment the expression is a literal string value, 
> not an sql variable. 
> 
> 
> 
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread John Machin


On 29/08/2007 8:37 PM, RaghavendraK 70574 wrote:

Hi,

Am using sqlite 3.4.0

stmt= sqlite_prepareV2("select * from test where '?'  like t || '%' order by t desc); 
? is the sql variable. 


No it isn't; it's the contents of a string constant.
Try this:
select * from test where ? like t || '%' order by t desc

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

Re: [sqlite] compiling 3.4.2 on solaris

2007-08-29 Thread rahed

> Funny enough, I just ran into this problem yesterday!  I used the
> information in this ticket [1] to fix the problem. All you have to do
> is find/replace B_FALSE to BOOL_FALSE and B_TRUE to BOOL_TRUE in the
> lemon.c file.
>

Thank you much.

-- 
Radek

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

Re: [sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread Dan Kennedy

On Wed, 2007-08-29 at 16:43 +0800, RaghavendraK 70574 wrote:
> Hi,
> 
> create table test (t text);
> 
> insert into test values ('9');
> insert into test values ('98');
> insert into test values ('986');
> insert into test values ('9867');
> 
> select * from test where '98555'  like t || '%' order by t desc limit 1;

There are no SQL variables to bind to in that statement. Syntax
for SQL variables is here:

  http://www.sqlite.org/lang_expr.html

Dan.



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

[sqlite] BestMatch and SqliteStatment Clash

2007-08-29 Thread RaghavendraK 70574

Hi,

create table test (t text);

insert into test values ('9');
insert into test values ('98');
insert into test values ('986');
insert into test values ('9867');

select * from test where '98555'  like t || '%' order by t desc limit 1;

When we try to compile the above sql as a statement,we get Success but
when we bind it gives a error "SQLITE_RANGE".
After inspection we find
"sParse.nVar" = 0 [which represent nr of  "?"]

Can u pls help to correct this error.


regards
ragha


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

- Original Message -
From: Daniel Önnerby <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 4:20 pm
Subject: Re: [sqlite] Towards SQLite version 3.5.0

> Hi!
> 
> The new multithread-features will be great.
> Do you think that it will be better to share one connection 
> between all 
> theads in an application or is better to have each thread open a 
> new 
> connection and use the sqlite3_enable_shared_cache?
> 
> Best regards
> Daniel
> 
> [EMAIL PROTECTED] wrote:
> > The transition from 3.4.2 to 3.5.0 will perhaps be the
> > largest single change to SQLite since 2.8->3.0.  There 
> > will not be that many visible changes, but a lot is 
> > changing behind the scenes.  Some less frequently used
> > interfaces will be changing in slightly incompatible
> > ways.  Users who have build customized OS intereface layers
> > or backends for SQLite will find that they are going to
> > need to do some rework.
> >
> > SQLite version 3.5.0 is not close to being ready yet.
> > But it is to the point where the source code will
> > compile and pass many tests.  And so I would like to
> > take this opportunity to encourage people in the 
> > community to download the CVS HEAD and give it
> > a whirl in their applications.  Please let me know
> > about any serious issues you run across.
> >
> > I have *started* to prepare documentation describing
> > the changes in 3.5.0.  This is draft documentation.
> > But for those who are interested, please visit
> >
> >http://www.sqlite.org/34to35.html
> >http://www.sqlite.org/capi350ref.html
> >
> > In particular, if your application uses a customized
> > OS interface for SQLite, you should read the 34to35.html
> > document to see exactly what will be involved in porting
> > your application to run with version 3.5.0.
> >
> > The SQLite code currently in CVS HEAD is not ready for
> > production use.  We know that.  We know what many of the
> > problems are and Dan and I are working long hours to fix
> > them.  It's the problems that we *do not* know about that
> > are scary.  So that is why I am inviting the larger
> > community to have an early look and perhaps bring our
> > attention to issues sooner rather than later.
> >
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> > -
> 
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> 
> >
> >   
> 
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
>

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

Re: [sqlite] Towards SQLite version 3.5.0

2007-08-29 Thread Daniel Önnerby


Hi!

The new multithread-features will be great.
Do you think that it will be better to share one connection between all 
theads in an application or is better to have each thread open a new 
connection and use the sqlite3_enable_shared_cache?


Best regards
Daniel

[EMAIL PROTECTED] wrote:

The transition from 3.4.2 to 3.5.0 will perhaps be the
largest single change to SQLite since 2.8->3.0.  There 
will not be that many visible changes, but a lot is 
changing behind the scenes.  Some less frequently used

interfaces will be changing in slightly incompatible
ways.  Users who have build customized OS intereface layers
or backends for SQLite will find that they are going to
need to do some rework.

SQLite version 3.5.0 is not close to being ready yet.
But it is to the point where the source code will
compile and pass many tests.  And so I would like to
take this opportunity to encourage people in the 
community to download the CVS HEAD and give it

a whirl in their applications.  Please let me know
about any serious issues you run across.

I have *started* to prepare documentation describing
the changes in 3.5.0.  This is draft documentation.
But for those who are interested, please visit

   http://www.sqlite.org/34to35.html
   http://www.sqlite.org/capi350ref.html

In particular, if your application uses a customized
OS interface for SQLite, you should read the 34to35.html
document to see exactly what will be involved in porting
your application to run with version 3.5.0.

The SQLite code currently in CVS HEAD is not ready for
production use.  We know that.  We know what many of the
problems are and Dan and I are working long hours to fix
them.  It's the problems that we *do not* know about that
are scary.  So that is why I am inviting the larger
community to have an early look and perhaps bring our
attention to issues sooner rather than later.

--
D. Richard Hipp <[EMAIL PROTECTED]>


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

  



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

[sqlite] ANN: SQLite Data Wizard 7.8 released

2007-08-29 Thread SQL Maestro Group


Hi!

SQL Maestro Group announces the release of SQLite Data Wizard 7.8, a 
powerful Windows GUI solution for data management.


SQLite Data Wizard provides you with a number of easy-to-use wizards for 
performing the required data manipulation easily and quickly.  It allows you 
to generate PHP and ASP.NET scripts for tables, views and queries, convert 
any ADO-compatible database to the SQLite database, export data from SQLite 
tables, views and queries to most popular formats, and import data into the 
tables.


http://www.sqlmaestro.com/products/sqlite/datawizard/

New version features:

- ASP.NET Generator Wizard is implemented.
- New Shell Application with Ribbon toolbars (like Office 2007) and more.
- Unicode/UTF-8 support
- PHP Generator: now it is possible to create a lookup menu for a column 
using a custom value list.

- Data Pump: now optimized for Microsoft.Jet engine.
- Data Import: the speed of loading of Excel files was increased.
- All the wizards: a lot of minor improvements and corrections were made.

Full press-release is available at:
http://www.sqlmaestro.com/news/company/4457/

Background information:

SQL Maestro Group is engaged in developing complete database admin and
management tools for MySQL, Oracle, MS SQL Server, PostgreSQL, SQLite,
Firebird and MaxDB providing the highest performance, scalability and
reliability to meet the requirements of today's database applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com 



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

43 matches

Mail list logo