RFC: SQL::KeywordSearch 1.1

Hello,

I thought some people here would be interested in reviewing this:

I plan to publish a small module to generate SQL for simple keyword
searches.

The docs are here for easy HTML browsing:
http://mark.stosberg.com/perl/SQL-KeywordSearch.html

The preview distribution is here:
http://mark.stosberg.com/perl/SQL-KeywordSearch-1.1.tar.gz

If anyone has suggestions or feedback, I'm interested in discussing them before
the official upload.

I've been using variations of this for small projects over the years and works
great for that.

Mark

--
http://mark.stosberg.com/

Re: RFC: SQL::KeywordSearch 1.1

Hi Mark,

is a nice idea, but why don't You do it in standard SQL using LIKE?

Robert

Mark Stosberg wrote:
> Hello,
>
> I thought some people here would be interested in reviewing this:
>
> I plan to publish a small module to generate SQL for simple keyword
> searches.
>
> The docs are here for easy HTML browsing:
> http://mark.stosberg.com/perl/SQL-KeywordSearch.html
>
> The preview distribution is here:
> http://mark.stosberg.com/perl/SQL-KeywordSearch-1.1.tar.gz
>
> If anyone has suggestions or feedback, I'm interested in discussing them before
> the official upload.
>
> I've been using variations of this for small projects over the years and works
> great for that.
>
> Mark
>

Re: RFC: SQL::KeywordSearch 1.1

Hi Mark,

not all can be done by LIKE and must be formulated in a lenguish
OR-chain. But some cases can be treated be the LIKE-like SIMILAR. With
it it should be possible to say

SELECT pets, colors FROM Table WHERE (
(lower(pets) SIMILAR TO lower('%?%|%?%') )
OR
(lower(colors) SIMILAR TO lower('%?%|%?%') )
)
@bind = ('cat','brown','cat','brown');

instead of

SELECT pets, colors FROM Table WHERE (
(lower(pets) ~ lower(?)
OR lower(colors) ~ lower(?)
)
OR
(lower(pets) ~ lower(?)
OR lower(colors) ~ lower(?)
))
@bind = ('cat','cat','brown','brown');

I did not tested the syntax, but regarding the SQL-rules it should be
possible. This will shorten the statements and may be improve the
performance.

Best regards

Robert

--------

Mark Stosberg wrote:
> Robert Roggenbuck wrote:
>
>> Hi Mark,
>>
>> is a nice idea, but why don't You do it in standard SQL using LIKE?
>
> I looked at LIKE first.
>
> However, I want to "whole word" matching as an option, which requires a
> regular expression
> for word boundary matching. I don't think LIKE supports that.
>
> Mark
>
>
>
>
>>
>> Mark Stosberg wrote:
>>
>>> Hello,
>>>
>>> I thought some people here would be interested in reviewing this:
>>>
>>> I plan to publish a small module to generate SQL for simple keyword
>>> searches.
>>>
>>> The docs are here for easy HTML browsing:
>>> http://mark.stosberg.com/perl/SQL-KeywordSearch.html
>>>
>>> The preview distribution is here:
>>> http://mark.stosberg.com/perl/SQL-KeywordSearch-1.1.tar.gz
>>>
>>> If anyone has suggestions or feedback, I'm interested in discussing
>>> them before
>>> the official upload.
>>> I've been using variations of this for small projects over the years
>>> and works
>>> great for that.
>>> Mark
>>>
>
>
>
>

--

===================================================
Robert Roggenbuck, M.A.
Konrad Zuse Zentrum fuer Informationstechnik Berlin
Takustr. 7 D-14195 Berlin
roggenbuck@zib.de
http://www.mathematik-21.de/
http://www.zib.de/

Buero:
Universitaet Osnabrueck
Fachbereich Mathematik / Informatik
Albrechtstr. 28a Fon: ++49 (0)541/969-2735
D-49069 Osnabrueck Fax: ++49 (0)541/969-2770
http://www.mathematik.uni-osnabrueck.de/
===================================================

Re: RFC: SQL::KeywordSearch 1.1

Robert Roggenbuck wrote:
> Hi Mark,
>
> not all can be done by LIKE and must be formulated in a long-ish
> OR-chain. But some cases can be treated be the LIKE-like SIMILAR. With
> it it should be possible to say
>
> SELECT pets, colors FROM Table WHERE (
> (lower(pets) SIMILAR TO lower('%?%|%?%') )
> OR
> (lower(colors) SIMILAR TO lower('%?%|%?%') )
> )
> @bind = ('cat','brown','cat','brown');
>
> instead of
>
> SELECT pets, colors FROM Table WHERE (
> (lower(pets) ~ lower(?)
> OR lower(colors) ~ lower(?)
> )
> OR
> (lower(pets) ~ lower(?)
> OR lower(colors) ~ lower(?)
> ))
> @bind = ('cat','cat','brown','brown');
>
> I did not tested the syntax, but regarding the SQL-rules it should be
> possible. This will shorten the statements and may be improve the
> performance.

Robert,

This optimization looks reasonable. Would you be interested in
submitting a patch? It would also be interesting to see benchmarks to
see if there's really much difference.

Mark

--
http://mark.stosberg.com/