Re: [sqlite] FTS4 Problem

2014-12-24 Thread Dan Kennedy 

On 12/25/2014 08:04 AM, Peter Truskier wrote:

As I mentioned, I seem to have solved the problem by doing a "rebuild" command 
on the FTS4 table. But, as I thought about it further, I'm still confused as to why 
dropping, and then re-creating the virtual table didn't solve the problem as well.

What am I missing?


Creating an FTS4 table that uses the "content=" option does not 
automatically populate the FTS index. It just creates an empty FTS index 
that SQLite assumes the user will somehow take care of populating.


Dan.









Thanks,

Peter



On Dec 24, 2014, at 12:03 PM, Peter Truskier  wrote:

Thanks so much for the quick AND HELPFUL response!

I had run the pragma, but was unaware of the command. When I ran the 'integrity-check' 
command on the virtual table, I got a "database disk image is malformed Error Code 
11"

Running the 'rebuild' command seems to have fixed the problem. I guess one of 
the triggers for keeping the virtual table up to date must have failed for some 
reason.

Thank you again!

--
Peter Truskier
Berkeley, CA USA
1-510-495-6442




On Dec 24, 2014, at 11:47 AM, Richard Hipp  wrote:

Have you run integrity checks on the database file (
https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
correct?

On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:


I have an sqlite database in which I've created a virtual table using
FTS4. For nearly a year, we've been using it to do full text searching with
no problem.

The database contains a table of products (tblProducts) with columns id,
SKU, itemDesc, etc.

The virtual table is created like this:

  CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
SKU, itemDesc)

A couple of days ago, full text searches (using "MATCH") suddenly stopped
working - always returning an empty recordset with no error. The data in
the virtual table appears to be correct.

If I do a query on the virtual table like this:

  SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',

I get a valid recordset containing the expected records. But, if I do this:

  SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',

I get an empty recordset.

I've checked the integrity of the database, and it is reported to be good.
I've tried dropping and re-creating the virtual table, and still get the
same behavior.

Does anyone have any suggestion for what might suddenly cause this
behavior after working for moths and months?

Thanks, and happy holidays!



___
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-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] FTS4 Problem

2014-12-24 Thread Peter Truskier 
As I mentioned, I seem to have solved the problem by doing a "rebuild" command 
on the FTS4 table. But, as I thought about it further, I'm still confused as to 
why dropping, and then re-creating the virtual table didn't solve the problem 
as well.

What am I missing?

Thanks,

Peter


> On Dec 24, 2014, at 12:03 PM, Peter Truskier  wrote:
> 
> Thanks so much for the quick AND HELPFUL response!
> 
> I had run the pragma, but was unaware of the command. When I ran the 
> 'integrity-check' command on the virtual table, I got a "database disk image 
> is malformed Error Code 11"
> 
> Running the 'rebuild' command seems to have fixed the problem. I guess one of 
> the triggers for keeping the virtual table up to date must have failed for 
> some reason.
> 
> Thank you again! 
> 
> --
> Peter Truskier
> Berkeley, CA USA
> 1-510-495-6442
> 
> 
> 
>> On Dec 24, 2014, at 11:47 AM, Richard Hipp  wrote:
>> 
>> Have you run integrity checks on the database file (
>> https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
>> tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
>> correct?
>> 
>> On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:
>> 
>>> I have an sqlite database in which I've created a virtual table using
>>> FTS4. For nearly a year, we've been using it to do full text searching with
>>> no problem.
>>> 
>>> The database contains a table of products (tblProducts) with columns id,
>>> SKU, itemDesc, etc.
>>> 
>>> The virtual table is created like this:
>>> 
>>>  CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
>>> SKU, itemDesc)
>>> 
>>> A couple of days ago, full text searches (using "MATCH") suddenly stopped
>>> working - always returning an empty recordset with no error. The data in
>>> the virtual table appears to be correct.
>>> 
>>> If I do a query on the virtual table like this:
>>> 
>>>  SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',
>>> 
>>> I get a valid recordset containing the expected records. But, if I do this:
>>> 
>>>  SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',
>>> 
>>> I get an empty recordset.
>>> 
>>> I've checked the integrity of the database, and it is reported to be good.
>>> I've tried dropping and re-creating the virtual table, and still get the
>>> same behavior.
>>> 
>>> Does anyone have any suggestion for what might suddenly cause this
>>> behavior after working for moths and months?
>>> 
>>> Thanks, and happy holidays!
>>> 
>>> 
>>> 
>>> ___
>>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4 Problem

2014-12-24 Thread Peter Truskier 
Thanks so much for the quick AND HELPFUL response!

I had run the pragma, but was unaware of the command. When I ran the 
'integrity-check' command on the virtual table, I got a "database disk image is 
malformed Error Code 11"

Running the 'rebuild' command seems to have fixed the problem. I guess one of 
the triggers for keeping the virtual table up to date must have failed for some 
reason.

Thank you again! 

--
Peter Truskier
Berkeley, CA USA
1-510-495-6442



> On Dec 24, 2014, at 11:47 AM, Richard Hipp  wrote:
> 
> Have you run integrity checks on the database file (
> https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
> tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
> correct?
> 
> On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:
> 
>> I have an sqlite database in which I've created a virtual table using
>> FTS4. For nearly a year, we've been using it to do full text searching with
>> no problem.
>> 
>> The database contains a table of products (tblProducts) with columns id,
>> SKU, itemDesc, etc.
>> 
>> The virtual table is created like this:
>> 
>>   CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
>> SKU, itemDesc)
>> 
>> A couple of days ago, full text searches (using "MATCH") suddenly stopped
>> working - always returning an empty recordset with no error. The data in
>> the virtual table appears to be correct.
>> 
>> If I do a query on the virtual table like this:
>> 
>>   SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',
>> 
>> I get a valid recordset containing the expected records. But, if I do this:
>> 
>>   SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',
>> 
>> I get an empty recordset.
>> 
>> I've checked the integrity of the database, and it is reported to be good.
>> I've tried dropping and re-creating the virtual table, and still get the
>> same behavior.
>> 
>> Does anyone have any suggestion for what might suddenly cause this
>> behavior after working for moths and months?
>> 
>> Thanks, and happy holidays!
>> 
>> 
>> 
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4 Problem

2014-12-24 Thread Richard Hipp 
Have you run integrity checks on the database file (
https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
correct?

On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:

> I have an sqlite database in which I've created a virtual table using
> FTS4. For nearly a year, we've been using it to do full text searching with
> no problem.
>
> The database contains a table of products (tblProducts) with columns id,
> SKU, itemDesc, etc.
>
> The virtual table is created like this:
>
>CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
> SKU, itemDesc)
>
> A couple of days ago, full text searches (using "MATCH") suddenly stopped
> working - always returning an empty recordset with no error. The data in
> the virtual table appears to be correct.
>
> If I do a query on the virtual table like this:
>
>SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',
>
> I get a valid recordset containing the expected records. But, if I do this:
>
>SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',
>
> I get an empty recordset.
>
> I've checked the integrity of the database, and it is reported to be good.
> I've tried dropping and re-creating the virtual table, and still get the
> same behavior.
>
> Does anyone have any suggestion for what might suddenly cause this
> behavior after working for moths and months?
>
> Thanks, and happy holidays!
>
>
>
> ___
> 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] FTS4 Problem

2014-12-24 Thread Peter Truskier 
I have an sqlite database in which I've created a virtual table using FTS4. For 
nearly a year, we've been using it to do full text searching with no problem.

The database contains a table of products (tblProducts) with columns id, SKU, 
itemDesc, etc. 

The virtual table is created like this:

   CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts", SKU, 
itemDesc)

A couple of days ago, full text searches (using "MATCH") suddenly stopped 
working - always returning an empty recordset with no error. The data in the 
virtual table appears to be correct. 

If I do a query on the virtual table like this:

   SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',

I get a valid recordset containing the expected records. But, if I do this:

   SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',

I get an empty recordset.

I've checked the integrity of the database, and it is reported to be good. I've 
tried dropping and re-creating the virtual table, and still get the same 
behavior.

Does anyone have any suggestion for what might suddenly cause this behavior 
after working for moths and months?

Thanks, and happy holidays!



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