Re: [sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Simon Slavin]

On 21 Nov 2011, at 3:13am, Mohit Sindhwani wrote:

> I think my examples muddied the waters.  I have looked at Section 3 of the 
> FTS documents and that lets me bring back the "full result" that matches - 
> so, if I search for 'ling j', it can tell me that the result that matches is 
> 'james ling, alibaba'.  What it does not let me do is figure out that the 
> partly entered term "j" completes to the word 'james' - that is the part I'm 
> trying to figure out.

You have to look at the rows it returns and see how many of them there are.  If 
there's only one, that's your hit.  If there are more than one, see how many 
characters you can move along the row before they start to be different.  For 
instance suppose I have

Jones, Jenny
Smith, John
Smith, James
Zbignew, Andrew

Immediately they type 's', by looking at the two rows returned you can move all 
the way along to the 'j' before the strings are different.  The only efficient 
way to do it is programming.

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

Re: [sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Mohit Sindhwani]

Hi Abhinav,

On 21/11/2011 2:52 AM, Abhinav Upadhyay wrote:

On Mon, Nov 21, 2011 at 12:17 AM, Mohit Sindhwani  wrote:

What I'd like to be able to do is something like this:
- let's say that the FTS4 table has values such as:
* mohit sindhwani, onghu
* john doe, gmail
* james ling, alibaba
* john barn, yahoo
...and so on

If the user enters "j", I'd like to suggest that this would complete to the
words:
john and james in the current set

If the user enters 'ling j', I'd like to restrict it and say:

james is the only word that can be matched now
james ling, alibaba is the result


I think you might want to look at Token Prefix queries:
http://sqlite.org/fts3.html#section_3


I think my examples muddied the waters.  I have looked at Section 3 of 
the FTS documents and that lets me bring back the "full result" that 
matches - so, if I search for 'ling j', it can tell me that the result 
that matches is 'james ling, alibaba'.  What it does not let me do is 
figure out that the partly entered term "j" completes to the word 
'james' - that is the part I'm trying to figure out.


Best Regards,
Mohit.

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

Re: [sqlite] can you select series with SQL?

[Bart Smissaert]

I am not not working that close to the SQLite source to talk about
sqlite3_step etc.
as I am using a VB wrapper. Still, I suppose what you say still applies.
As it turns out and can now beforehand (without checking for
non-consecutive id numbers)
how many records should be fetched, so with that things are in fact simple.

RBS


On Sun, Nov 20, 2011 at 2:12 PM, Igor Tandetnik  wrote:
> Bart Smissaert  wrote:
>> In fact when doing something as you suggest:
>> select * from MyTable where ID <= 14 order by ID desc
>> I can make it a lot more efficient by adding a limit as not many
>> records will be needed.
>
> There's no difference between adding a LIMIT N clause to the query, and 
> simply calling sqlite3_step N times and then resetting or finalizing.
> --
> Igor Tandetnik
>
> ___
> 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] FTS3/FTS4 - Finding the term(s) that completes the input

[Abhinav Upadhyay]

On Mon, Nov 21, 2011 at 12:17 AM, Mohit Sindhwani  wrote:
> Hi, I'm finding my way through FTS3/FTS4 to replace some of the old code
> that we have for searching terms within titles.  I now know that FTS3/4
> should be the way to proceed.
>
> So far, I have this:
> - an FTS4 table that has two columns: title (main column), ext (certain
> conditions to match)
> - an FTS4aux table
>
> What I'd like to be able to do is something like this:
> - let's say that the FTS4 table has values such as:
> * mohit sindhwani, onghu
> * john doe, gmail
> * james ling, alibaba
> * john barn, yahoo
> ...and so on
>
> If the user enters "j", I'd like to suggest that this would complete to the
> words:
> john and james in the current set
>
> If the user enters 'ling j', I'd like to restrict it and say:
>> james is the only word that can be matched now
>> james ling, alibaba is the result
>
> Similarly, if the user enters 'yahoo j', I should be able to zoom in to
> 'john barn, yahoo'.
>
> I think this should be within the reach of FTS3/FTS4, but I'm having trouble
> framing the queries, etc.  Are you able to nudge me in the correct
> direction?
>
> Thanks,
> Mohit.

I think you might want to look at Token Prefix queries:
http://sqlite.org/fts3.html#section_3

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

[sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Mohit Sindhwani]

Hi, I'm finding my way through FTS3/FTS4 to replace some of the old code 
that we have for searching terms within titles.  I now know that FTS3/4 
should be the way to proceed.


So far, I have this:
- an FTS4 table that has two columns: title (main column), ext (certain 
conditions to match)

- an FTS4aux table

What I'd like to be able to do is something like this:
- let's say that the FTS4 table has values such as:
* mohit sindhwani, onghu
* john doe, gmail
* james ling, alibaba
* john barn, yahoo
...and so on

If the user enters "j", I'd like to suggest that this would complete to 
the words:

john and james in the current set

If the user enters 'ling j', I'd like to restrict it and say:
> james is the only word that can be matched now
> james ling, alibaba is the result

Similarly, if the user enters 'yahoo j', I should be able to zoom in to 
'john barn, yahoo'.


I think this should be within the reach of FTS3/FTS4, but I'm having 
trouble framing the queries, etc.  Are you able to nudge me in the 
correct direction?


Thanks,
Mohit.

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

Re: [sqlite] begin with or without semicolon?

[Igor Tandetnik]

Matt Young  wrote:
> In the documentation on trigger, begin has no semicolon, and it works.
> From the command line, I can begin and end transaction, but I need the
> semicolon after begin. Is there a difference?

BEGIN keyword is used in two different contexts, and has different meanings in 
these two contexts. It could start a BEGIN TRANSACTION statement (the 
TRANSACTION keyword can be omitted, so the statement becomes simply BEGIN). Or 
it could indicate the start of a trigger body in CREATE TRIGGER statement.

In sqlite command line application, any statement has to be terminated with a 
semicolon (that's how it knows the statement is complete and is ready to parse 
and execute). That's why you write BEGIN; to start a transaction. In CREATE 
TRIGGER statement, the statement ends with the END keyword, so you put the 
semicolon there. The syntax of the statement itself doesn't call for a 
semicolon after BEGIN keyword.
-- 
Igor Tandetnik

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

Re: [sqlite] can you select series with SQL?

[Igor Tandetnik]

Bart Smissaert  wrote:
> In fact when doing something as you suggest:
> select * from MyTable where ID <= 14 order by ID desc
> I can make it a lot more efficient by adding a limit as not many
> records will be needed.

There's no difference between adding a LIMIT N clause to the query, and simply 
calling sqlite3_step N times and then resetting or finalizing.
-- 
Igor Tandetnik

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

Re: [sqlite] Header inconsistency

[Igor Tandetnik]

Bernd  wrote:
> I found that table aliases are repeated in the headers of compound
> queries. Is this intentional and/or documented somewhere?

http://www.sqlite.org/c3ref/column_name.html
The name of a result column is the value of the "AS" clause for that column, if 
there is an AS clause. If there is no AS clause then the name of the column is 
unspecified and may change from one release of SQLite to the next.

-- 
Igor Tandetnik

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

Re: [sqlite] Simple one

[Igor Tandetnik]

Matt Young  wrote:
> Well, it was listed as an initial option o the trigger

What do you mean, listed? Listed where and by whom?
-- 
Igor Tandetnik

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

Re: [sqlite] Simple one

[Igor Tandetnik]

Simon Slavin  wrote:
> SELECT in a TRIGGER doesn't report its results to anything.  The only reason 
> I can think of for doing one is it uses a function
> that has some weird side-effect.   

Like, say, RAISE.
-- 
Igor Tandetnik

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

Re: [sqlite] disk I/O error

[Filip Navara]

On Sun, Nov 20, 2011 at 1:21 PM, Richard Hipp  wrote:
> On Sun, Nov 20, 2011 at 6:53 AM, Tal Tabakman wrote:
>
>> in a continuation to the below, it seems I get the following extended err
>> code
>> SQLITE_IOERR_WRITE
>> what are the reasons for that one ? is it a symptom of mem blow-up ?
>> cheers
>> Tal
>>
>>
> What operating system did you say you were using?
>

Just a note: Most recent versions of SQLite on Windows are broken
whenever sqlite3_config(SQLITE_CONFIG_LOG, ...) is used. It is fixed
in the repository, but not in any officially released version.

For debugging this kind of problems on Windows it is very helpful to
make a log with Process Monitor
(http://technet.microsoft.com/en-us/sysinternals/bb896645) and filter
the results on the directory containing the database file. I'd be
willing to look at it if such an error is reproduced and the log is
available.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk I/O error

[Richard Hipp]

On Sun, Nov 20, 2011 at 6:53 AM, Tal Tabakman wrote:

> in a continuation to the below, it seems I get the following extended err
> code
> SQLITE_IOERR_WRITE
> what are the reasons for that one ? is it a symptom of mem blow-up ?
> cheers
> Tal
>
>
What operating system did you say you were using?


> >* Hi Guys,*>* in which cases can I get sqlite3 error "disk I/O error", is
> it only when*>* disk is full ?*>* any other reasons for getting this one
> ?*>**
> Lots of reasons.  Use sqlite3_extended_errcode() to get a more detailed
> error code that can help isolate the problem.  And/or use
> sqlite3_config(SQLITE_CONFIG_LOG,...) to register a callback function that
> will give more detailed error messages.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] disik I/O reasopn

[Tal Tabakman]

in a continuation to the below, it seems I get the following extended err
code
SQLITE_IOERR_WRITE
what are the reasons for that one ? is it a symptom of mem blow-up ?
cheers
Tal

>* Hi Guys,*>* in which cases can I get sqlite3 error "disk I/O error", is it 
>only when*>* disk is full ?*>* any other reasons for getting this one ?*>**
Lots of reasons.  Use sqlite3_extended_errcode() to get a more detailed
error code that can help isolate the problem.  And/or use
sqlite3_config(SQLITE_CONFIG_LOG,...) to register a callback function that
will give more detailed error messages.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can you select series with SQL?

[Bart Smissaert]

Thanks; that to me looks a truly amazing SQL!
As you say doing this in code might be more efficient and definitely
less confusing.
In fact when doing something as you suggest:
select * from MyTable where ID <= 14 order by ID desc
I can make it a lot more efficient by adding a limit as not many
records will be needed.

RBS


On Sun, Nov 20, 2011 at 12:15 AM, Igor Tandetnik  wrote:
> Bart Smissaert  wrote:
>> If we have the 14 (we know to start at 14) can we select the records
>> 14, 13, 12 and 11,
>> so the consecutive numbers, going down from 14?
>
> select * from MyTable t1 where
> (select count(*) from MyTable t2 where t2.ID between t1.ID and 14) == 14 - 
> t1.ID + 1;
>
> Personally, I'd just run a query like
>
> select * from MyTable where ID <= 14 order by ID desc;
>
> and step through it until the next ID is non-consecutive.
> --
> Igor Tandetnik
>
> ___
> 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] Header inconsistency

[Bernd]

Hi list,
I found that table aliases are repeated in the headers of compound 
queries. Is this intentional and/or documented somewhere?
I'm asking, because it forces one to either already alias the columns in 
the query or use the aliased column name in the DataReader (I'm using 
the .NET provider). Not a big deal but worth asking a question ;-)



C:\Program Files (x86)\SQLite>sqlite3.exe :memory:
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .headers ON
sqlite>
sqlite> CREATE TABLE table1 (c1 INTEGER, c2 TEXT);
sqlite> CREATE TABLE table2 (c3 INTEGER, c4 TEXT);
sqlite>
sqlite> INSERT INTO table1 VALUES (1, 'text1');
sqlite> INSERT INTO table2 VALUES (3, 'text3');
sqlite>
sqlite> SELECT
   ...>   t1.c1,
   ...>   t1.c2
   ...> FROM
   ...>   table1 t1;
c1|c2
1|text1
sqlite>
sqlite> SELECT
   ...>   t1.c1,
   ...>   t1.c2
   ...> FROM
   ...>   table1 t1
   ...>
   ...> UNION
   ...>
   ...> SELECT
   ...>   t2.c3,
   ...>   t2.c4
   ...> FROM
   ...>   table2 t2;
t1.c1|t1.c2
1|text1
3|text3
sqlite>
sqlite> SELECT
   ...>   t1.c1 AS c1,
   ...>   t1.c2 AS c2
   ...> FROM
   ...>   table1 t1
   ...>
   ...> UNION
   ...>
   ...> SELECT
   ...>   t2.c3,
   ...>   t2.c4
   ...> FROM
   ...>   table2 t2;
c1|c2
1|text1
3|text3
sqlite> .e

C:\Program Files (x86)\SQLite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proper way to change temp directory

[Yang Zhang]

Cool beans, perhaps this should be added to the docs!

On Sun, Nov 20, 2011 at 1:36 AM, Dan Kennedy  wrote:
> On 11/20/2011 04:00 PM, Yang Zhang wrote:
>>
>> Out of curiosity, what's the proper way to change the temp directory
>> (say, to avoid "Error: database or disk full" errors on vacuum, which
>> I ran into)?  temp_store_directory has been working for me but it's
>> deprecated and may be elided from builds.  Is the only option to
>> recompile sqlite?  Thanks.
>
> On unix setting the TMPDIR environment variable works.
>
> On windows SQLite uses GetTempPath(). So perhaps setting
> TMP or TEMP works there.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Yang Zhang
http://yz.mit.edu/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proper way to change temp directory

[Dan Kennedy]

On 11/20/2011 04:00 PM, Yang Zhang wrote:

Out of curiosity, what's the proper way to change the temp directory
(say, to avoid "Error: database or disk full" errors on vacuum, which
I ran into)?  temp_store_directory has been working for me but it's
deprecated and may be elided from builds.  Is the only option to
recompile sqlite?  Thanks.


On unix setting the TMPDIR environment variable works.

On windows SQLite uses GetTempPath(). So perhaps setting
TMP or TEMP works there.





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

[sqlite] Proper way to change temp directory

[Yang Zhang]

Out of curiosity, what's the proper way to change the temp directory
(say, to avoid "Error: database or disk full" errors on vacuum, which
I ran into)?  temp_store_directory has been working for me but it's
deprecated and may be elided from builds.  Is the only option to
recompile sqlite?  Thanks.

-- 
Yang Zhang
http://yz.mit.edu/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] begin with or without semicolon?

[Matt Young]

In the documentation on trigger, begin has no semicolon, and it works.
 From the command line, I can begin and end transaction, but I need the
semicolon after begin. Is there a difference?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users