SQL(ite) question

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL(ite) question

Stephen Barncard via use-livecode
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
[hidden email]


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard 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.

<https://hashnode.com/post/sqlite-fts5-full-text-searching-cjmklqx50000d6is2lkuspn09>

--
  Mark Wieder
  [hidden email]

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
Hi Mark,

> Am 13.01.2020 um 20:39 schrieb Mark Wieder via use-livecode <[hidden email]>:
>
> 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. <https://hashnode.com/post/sqlite-fts5-full-text-searching-cjmklqx50000d6is2lkuspn09>

Great, thanks a lot for this link!

> --
> Mark Wieder

Best

Klaus

--
Klaus Major
https://www.major-k.de
[hidden email]


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
In reply to this post by Stephen Barncard 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 <[hidden email]> 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
> [hidden email]
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
In reply to this post by Stephen Barncard via use-livecode

On Jan 13, 2020, at 11:39 AM, Mark Wieder via use-livecode <[hidden email]> 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
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
In reply to this post by Stephen Barncard 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 <[hidden email]> 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 <[hidden email]> 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
>> [hidden email]
>>
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
In reply to this post by Stephen Barncard 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 <[hidden email]> 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
  [hidden email]

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
In reply to this post by Stephen Barncard via use-livecode
Hi Sean,

> Am 14.01.2020 um 02:04 schrieb Pi Digital via use-livecode <[hidden email]>:
>
> 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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard 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 <[hidden email]> wrote:
>
> Hi Sean,
>
>> Am 14.01.2020 um 02:04 schrieb Pi Digital via use-livecode <[hidden email]>:
>>
>> 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 <[hidden email]> 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
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
Hi Bob,

> Am 14.01.2020 um 19:00 schrieb Bob Sneidar via use-livecode <[hidden email]>:
>
> 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 &aCol2) ...)
?

> 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 <[hidden email]> 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
[hidden email]


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard 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 <[hidden email]> wrote:
>
> Hi Bob,
>
>> Am 14.01.2020 um 19:00 schrieb Bob Sneidar via use-livecode <[hidden email]>:
>>
>> 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 &aCol2) ...)
> ?
>
>> Bob S


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard via use-livecode
In reply to this post by Stephen Barncard 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
  ____________________________________________________________________
  [hidden email]                http://www.FourthWorld.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard 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 <[hidden email]> wrote:
>
> 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


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL(ite) question

Stephen Barncard 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
  ____________________________________________________________________
  [hidden email]                http://www.FourthWorld.com


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode