Re: SQL(ite) question

[Richard Gaskin via use-livecode]

Bob Sneidar wrote:
> Yes, but the original question was about whether or not it could be
> done with a single statement. Also I am not a big fan of full text
> searches the way they are implemented typically. For instance, on our
> copier vendor's websites they of course have searches, but what I end
> up with are 10,000 hits and almost none of them relevant. This is
> because they are not ranked, and the ranking makes all the difference
> in the world.
>
> My method does not rank of course, but it is a quick one liner that
> gets the job done (unless you have to build the query on the fly in a
> repeat loop).

SQLite's FTS is described as providing ranking.  Maybe they were using 
an older version, or not using the full API?


--
 Richard Gaskin
 Fourth World Systems
 Software Design and Development for the Desktop, Mobile, and the Web
 
 ambassa...@fourthworld.comhttp://www.FourthWorld.com


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Bob Sneidar via use-livecode]

Yes, but the original question was about whether or not it could be done with a 
single statement. Also I am not a big fan of full text searches the way they 
are implemented typically. For instance, on our copier vendor's websites they 
of course have searches, but what I end up with are 10,000 hits and almost none 
of them relevant. This is because they are not ranked, and the ranking makes 
all the difference in the world. 

My method does not rank of course, but it is a quick one liner that gets the 
job done (unless you have to build the query on the fly in a repeat loop). 

Bob S


> On Jan 14, 2020, at 11:16 , Richard Gaskin via use-livecode 
>  wrote:
> 
> Bob Sneidar wrote:
>> Actually I do this all the time. SELECT * from MyTable where
>>   COLUMN1 like "%mysearchtermhere%" ORCOLUMN2 like "%mysearchtermhere%" 
>> ORCOLUMN3 like "%mysearchtermhere%" I loop through a list of columns I 
>> want to search to build the query. 
> 
> Wouldn't that be a brute-force search rather than the efficiency of SQLite's 
> FTS index?
> 
> -- 
> Richard Gaskin


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Richard Gaskin via use-livecode]

Bob Sneidar wrote:
Actually I do this all the time. 


SELECT * from MyTable where
   COLUMN1 like "%mysearchtermhere%" OR 
   COLUMN2 like "%mysearchtermhere%" OR 
   COLUMN3 like "%mysearchtermhere%" 

I loop through a list of columns I want to search to build the query. 


Wouldn't that be a brute-force search rather than the efficiency of 
SQLite's FTS index?


--
 Richard Gaskin
 Fourth World Systems
 Software Design and Development for the Desktop, Mobile, and the Web
 
 ambassa...@fourthworld.comhttp://www.FourthWorld.com

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Bob Sneidar via use-livecode]

Pretty sure + means concatenate, otherwise that SQL statement would not work in 
any I can imagine. 

Bob S


> On Jan 14, 2020, at 10:05 , Klaus major-k via use-livecode 
>  wrote:
> 
> Hi Bob,
> 
>> Am 14.01.2020 um 19:00 schrieb Bob Sneidar via use-livecode 
>> :
>> 
>> The only problem I see here is that it is possible to get a match with the 
>> last part of one column and the first part of the next, especially with 
>> numerical data, which would be a false positive. 
> 
> AHA, so SQL treats this:
> ... WHERE ((col1+col2+aCol1+aCol2) ...)
> 
> Like this in LC:
> ... WHERE ((col1 & col2 & aCol1 ) ...)
> ?
> 
>> Bob S


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Klaus major-k via use-livecode]

Hi Bob,

> Am 14.01.2020 um 19:00 schrieb Bob Sneidar via use-livecode 
> :
> 
> The only problem I see here is that it is possible to get a match with the 
> last part of one column and the first part of the next, especially with 
> numerical data, which would be a false positive. 

AHA, so SQL treats this:
... WHERE ((col1+col2+aCol1+aCol2) ...)

Like this in LC:
... WHERE ((col1 & col2 & aCol1 ) ...)
?

> Bob S
> ...
>>> Even easier:
>>> SELECT * FROM MyTable WHERE ((col1+col2+aCol1+aCol2) LIKE 
>>> ‘%mysearchtermhere%’)
>> aha, thank you very much, I had no idea this is possible! :-)
>>> Sean Cole
>>> Pi Digital Prod Ltd
> On 13 Jan 2020, at 23:03, Bob Sneidar via use-livecode 
>  wrote:
 Actually I do this all the time. 
 SELECT * from MyTable where
 COLUMN1 like "%mysearchtermhere%" OR 
 COLUMN2 like "%mysearchtermhere%" OR 
 COLUMN3 like "%mysearchtermhere%" 
 I loop through a list of columns I want to search to build the query. 

Best

Klaus

--
Klaus Major
https://www.major-k.de
kl...@major-k.de


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Bob Sneidar via use-livecode]

The only problem I see here is that it is possible to get a match with the last 
part of one column and the first part of the next, especially with numerical 
data, which would be a false positive. 

Bob S


> On Jan 14, 2020, at 01:15 , Klaus major-k via use-livecode 
>  wrote:
> 
> Hi Sean,
> 
>> Am 14.01.2020 um 02:04 schrieb Pi Digital via use-livecode 
>> :
>> 
>> Even easier:
>> 
>> SELECT * FROM MyTable WHERE ((col1+col2+aCol1+aCol2) LIKE 
>> ‘%mysearchtermhere%’)
> 
> aha, thank you very much, I had no idea this is possible! :-)
> 
>> Sean Cole
>> Pi Digital Prod Ltd
>> 
 On 13 Jan 2020, at 23:03, Bob Sneidar via use-livecode 
  wrote:
>>> Actually I do this all the time. 
>>> SELECT * from MyTable where
>>> COLUMN1 like "%mysearchtermhere%" OR 
>>> COLUMN2 like "%mysearchtermhere%" OR 
>>> COLUMN3 like "%mysearchtermhere%" 
>>> I loop through a list of columns I want to search to build the query. 
>>> 
>>> Bob S
>>> 
> 

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Klaus major-k via use-livecode]

Hi Sean,

> Am 14.01.2020 um 02:04 schrieb Pi Digital via use-livecode 
> :
> 
> Even easier:
> 
> SELECT * FROM MyTable WHERE ((col1+col2+aCol1+aCol2) LIKE 
> ‘%mysearchtermhere%’)

aha, thank you very much, I had no idea this is possible! :-)

> Sean Cole
> Pi Digital Prod Ltd
> 
>>> On 13 Jan 2020, at 23:03, Bob Sneidar via use-livecode 
>>>  wrote:
>> Actually I do this all the time. 
>> SELECT * from MyTable where
>>  COLUMN1 like "%mysearchtermhere%" OR 
>>  COLUMN2 like "%mysearchtermhere%" OR 
>>  COLUMN3 like "%mysearchtermhere%" 
>> I loop through a list of columns I want to search to build the query. 
>> 
>> Bob S
>> 
>>> On Jan 13, 2020, at 11:03 , Klaus major-k via use-livecode 
>>>  wrote:
>>> 
>>> Hi all,
>>> 
>>> I searched the docs of SQLite but could not find a hint.
>>> 
>>> Is it possible to create a "full text search" in SQL(ite)
>>> with ONE SQL command? Something like this (pseudocode):
>>> ...
>>> SELECT * from MyTable where ANY_COLUMN like "%mysearchtermhere%"
>>> ...
>>> ...

Best

Klaus

--
Klaus Major
https://www.major-k.de
kl...@major-k.de


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Mark Wieder via use-livecode]

On 1/13/20 3:03 PM, doc hawk via use-livecode wrote:


On Jan 13, 2020, at 11:39 AM, Mark Wieder via use-livecode 
 wrote:


Single command? No.


Also, note that you *can* submit compound commands from LiveCode to SQLite and 
PostgreSQL, unlike with mySQL.  (Or, maybe the interface to mySQL has changed 
in the years since I learned this the hard way).


Yes, but it would no doubt be overkill to generate a new virtual table 
for every search.


--
 Mark Wieder
 ahsoftw...@gmail.com

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Pi Digital via use-livecode]

Even easier:

SELECT * FROM MyTable WHERE ((col1+col2+aCol1+aCol2) LIKE ‘%mysearchtermhere%’)

Sean Cole
Pi Digital Prod Ltd

>> On 13 Jan 2020, at 23:03, Bob Sneidar via use-livecode 
>>  wrote:
> Actually I do this all the time. 
> 
> SELECT * from MyTable where
>   COLUMN1 like "%mysearchtermhere%" OR 
>   COLUMN2 like "%mysearchtermhere%" OR 
>   COLUMN3 like "%mysearchtermhere%" 
> 
> I loop through a list of columns I want to search to build the query. 
> 
> Bob S
> 
> 
>> On Jan 13, 2020, at 11:03 , Klaus major-k via use-livecode 
>>  wrote:
>> 
>> Hi all,
>> 
>> I searched the docs of SQLite but could not find a hint.
>> 
>> Is it possible to create a "full text search" in SQL(ite)
>> with ONE SQL command? Something like this (pseudocode):
>> ...
>> SELECT * from MyTable where ANY_COLUMN like "%mysearchtermhere%"
>> ...
>> If not, how can this be done?
>> 
>> You get the picture, any hints appreciated, thank you.
>> 
>> 
>> Best
>> 
>> Klaus
>> --
>> Klaus Major
>> https://www.major-k.de
>> kl...@major-k.de
>> 
>> 
>> ___
>> use-livecode mailing list
>> use-livecode@lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription 
>> preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Bob Sneidar via use-livecode]

Actually I do this all the time. 

SELECT * from MyTable where
   COLUMN1 like "%mysearchtermhere%" OR 
   COLUMN2 like "%mysearchtermhere%" OR 
   COLUMN3 like "%mysearchtermhere%" 

I loop through a list of columns I want to search to build the query. 

Bob S


> On Jan 13, 2020, at 11:03 , Klaus major-k via use-livecode 
>  wrote:
> 
> Hi all,
> 
> I searched the docs of SQLite but could not find a hint.
> 
> Is it possible to create a "full text search" in SQL(ite)
> with ONE SQL command? Something like this (pseudocode):
> ...
> SELECT * from MyTable where ANY_COLUMN like "%mysearchtermhere%"
> ...
> If not, how can this be done?
> 
> You get the picture, any hints appreciated, thank you.
> 
> 
> Best
> 
> Klaus
> --
> Klaus Major
> https://www.major-k.de
> kl...@major-k.de
> 
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[doc hawk via use-livecode]

On Jan 13, 2020, at 11:39 AM, Mark Wieder via use-livecode 
 wrote:
> 
> Single command? No.

Also, note that you *can* submit compound commands from LiveCode to SQLite and 
PostgreSQL, unlike with mySQL.  (Or, maybe the interface to mySQL has changed 
in the years since I learned this the hard way).


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Klaus major-k via use-livecode]

Hi Mark,

> Am 13.01.2020 um 20:39 schrieb Mark Wieder via use-livecode 
> :
> 
> On 1/13/20 11:03 AM, Klaus major-k via use-livecode wrote:
>> Hi all,
>> I searched the docs of SQLite but could not find a hint.
>> Is it possible to create a "full text search" in SQL(ite)
>> with ONE SQL command? Something like this (pseudocode):
>> ...
>> SELECT * from MyTable where ANY_COLUMN like "%mysearchtermhere%"
>> ...
>> If not, how can this be done?
>> You get the picture, any hints appreciated, thank you.
> 
> Single command? No.

well, I guessed.

> But you can ahead of time create a virtual table to allow full-text searching 
> without regard to whereness. The current LC version is linked with sqlite 
> 3.28. 
> 

Great, thanks a lot for this link!

> -- 
> Mark Wieder

Best

Klaus

--
Klaus Major
https://www.major-k.de
kl...@major-k.de


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: SQL(ite) question

[Mark Wieder via use-livecode]

On 1/13/20 11:03 AM, Klaus major-k via use-livecode wrote:

Hi all,

I searched the docs of SQLite but could not find a hint.

Is it possible to create a "full text search" in SQL(ite)
with ONE SQL command? Something like this (pseudocode):
...
SELECT * from MyTable where ANY_COLUMN like "%mysearchtermhere%"
...
If not, how can this be done?

You get the picture, any hints appreciated, thank you.


Single command? No.
But you can ahead of time create a virtual table to allow full-text 
searching without regard to whereness. The current LC version is linked 
with sqlite 3.28.




--
 Mark Wieder
 ahsoftw...@gmail.com

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode