You are viewing a plain text version of this content. The canonical link for it is here.
Posted to embperl@perl.apache.org by Kee Hinckley <na...@somewhere.com> on 2002/05/23 19:43:26 UTC
DBIx::Recordset and MySQL FullText Search
Before I reinvent the wheel--has anyone done any work integrating
MySQL's fulltext search into DBIx::Recordset.
I started off with a wrapper, but that gets tricky if you are
searching on both fulltext and non-fulltext fields. I'm thinking now
that it might make more sense to trying building it directly into
RecordSet.
--
Kee Hinckley - Somewhere.Com, LLC
http://consulting.somewhere.com/
I'm not sure which upsets me more: that people are so unwilling to accept
responsibility for their own actions, or that they are so eager to regulate
everyone else's.
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: DBIx::Recordset and MySQL FullText Search
Posted by Gerald Richter <ri...@ecos.de>.
>
> I have no idea if the MySQL folks are making up this syntax or got it
> from some standard--but I'm not happy with it.
>
I think it's mysql specific
> Barring all this--I'm trying to figure out how to do it on top of
> Recordset, but that's not easy either. Basically I need to check all
> the arguments that might be handed to Search, figure out how they are
> going to impact the search, and then construct my own search. (In
> other words, I need to know whether they passed '$fields' or not,
> since I need to do *,match... if they didn't, otherwise I'd just add
> 'match...' to the $fields hash.
>
> But then I run into problem because Recordset certainly isn't going
> to recognize 'match(...)' as a field name, so it will discard it.
>
You can prefix any field name with a \, then Recordset will let it
unmodified through
> At this point I think I'm just going to have to bypass Recordset
> entirely for this case. I don't suppose there's any way of getting
> the SQL statement that Recordset is going to use before it uses it,
> and modifying it?
>
No, also this shouldn't be to hard to implement.
The problem arises for any sort of database functions. You can pass them
through Recordset, but Recordset doesn't handle them in any way at the
moment.
Gerald
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: DBIx::Recordset and MySQL FullText Search
Posted by Kee Hinckley <na...@somewhere.com>.
At 8:11 PM +0200 5/25/02, Gerald Richter wrote:
> > Before I reinvent the wheel--has anyone done any work integrating
>> MySQL's fulltext search into DBIx::Recordset.
>>
>
>I am not working on that, also I have a set of patches here (nothing related
>to fulltext search), that I want to integrate into DBIx::Recordset, but I
>guess this have to wait until Embperl 2 is released.
>
>BTW. What excatly do you want to do?
Well, the deeper I dive into it, the more unhappy I am. To do a full
text search you have to name all the fields that were indexed
together--unfortunately you can use the index name--which would
actually make sense. The index argument looks like:
fulltext content (title,firstname,lastname,organization,field1,field2,
field3,field4,field5,field6),
and when I do a describe table, none of that information seems to
appear. (This may have changed in 4.0, I haven't tried running that
yet). So I don't know if it's possible to find the index information
automatically, in which case I don't know how Recordset would handle
it.
What I had hoped to be able to do was something like:
$$set->Search({foo => 1,
content => "search string"
'$fields' => 'id',
'$order' => '_score',
})
and have Recordset generate the appropriate query, which would look
something like:
select
id,match(title,summary,content,source,relation,spacial,temporal,field1,field2,field3,field4,field5,field6)
against ('search string' ) as _score from article where foo = 1 and
match(title,summary,content,source,relation,spacial,temporal,field1,field2,field3,field4,field5,field6)
against ('search string' ) order by _score;
Note that I'm playing some magic games here having a special field
called "_score".
I have no idea if the MySQL folks are making up this syntax or got it
from some standard--but I'm not happy with it.
Barring all this--I'm trying to figure out how to do it on top of
Recordset, but that's not easy either. Basically I need to check all
the arguments that might be handed to Search, figure out how they are
going to impact the search, and then construct my own search. (In
other words, I need to know whether they passed '$fields' or not,
since I need to do *,match... if they didn't, otherwise I'd just add
'match...' to the $fields hash.
But then I run into problem because Recordset certainly isn't going
to recognize 'match(...)' as a field name, so it will discard it.
At this point I think I'm just going to have to bypass Recordset
entirely for this case. I don't suppose there's any way of getting
the SQL statement that Recordset is going to use before it uses it,
and modifying it?
P.S. Oh yes, just to complicate things further. At 4.0 MySQL
supports "boolean" searches ala Google's (-foo +bar), so you need a
way of specifying that modifier. Perhaps the best way would be to
add a new argument to search
$match = {
'$fields' => 'title,summary,...',
'$result' => '_score',
'$boolean' => 1,
}
--
Kee Hinckley - Somewhere.Com, LLC
http://consulting.somewhere.com/
I'm not sure which upsets me more: that people are so unwilling to accept
responsibility for their own actions, or that they are so eager to regulate
everyone else's.
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: DBIx::Recordset and MySQL FullText Search
Posted by Gerald Richter <ri...@ecos.de>.
> Before I reinvent the wheel--has anyone done any work integrating
> MySQL's fulltext search into DBIx::Recordset.
>
I am not working on that, also I have a set of patches here (nothing related
to fulltext search), that I want to integrate into DBIx::Recordset, but I
guess this have to wait until Embperl 2 is released.
BTW. What excatly do you want to do?
Gerald
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org