Renato Golin | 1 Jul 16:32 2008
Picon
Picon

substring search


Hi,

I want to define a filter (descriptions, comments) and search within 
that field and not as a whole thing (as in LIKE '%$term%' or full text 
search). Is there a way of doing it in BioMart?

cheers,
--renato

--

-- 
Reclaim your digital rights, eliminate DRM, learn more at 
http://www.defectivebydesign.org/what_is_drm

Richard Holland | 1 Jul 16:36 2008
Picon

Re: substring search

You need to specify 'like' as the qualifier in MartEditor, instead of
'=', then it will happen automatically.

cheers,
Richard

2008/7/1 Renato Golin <renato <at> ebi.ac.uk>:
>
> Hi,
>
> I want to define a filter (descriptions, comments) and search within that
> field and not as a whole thing (as in LIKE '%$term%' or full text search).
> Is there a way of doing it in BioMart?
>
> cheers,
> --renato
>
> --
> Reclaim your digital rights, eliminate DRM, learn more at
> http://www.defectivebydesign.org/what_is_drm
>

Renato Golin | 1 Jul 16:50 2008
Picon
Picon

Re: substring search

Richard Holland wrote:
> You need to specify 'like' as the qualifier in MartEditor, instead of
> '=', then it will happen automatically.

Hi Richard,

I put "=,in,like" and "like" and "=" and ... on both qualifier and 
legal_qualifier and it's not working...

Maybe that's another Oracle issue?

cheers,
--renato

--

-- 
Reclaim your digital rights, eliminate DRM, learn more at 
http://www.defectivebydesign.org/what_is_drm

Richard Holland | 1 Jul 17:02 2008
Picon

Re: substring search

Can you check the generated SQL to see if it is actually getting through?

2008/7/1 Renato Golin <renato <at> ebi.ac.uk>:
> Richard Holland wrote:
>>
>> You need to specify 'like' as the qualifier in MartEditor, instead of
>> '=', then it will happen automatically.
>
> Hi Richard,
>
> I put "=,in,like" and "like" and "=" and ... on both qualifier and
> legal_qualifier and it's not working...
>
> Maybe that's another Oracle issue?
>
> cheers,
> --renato
>
> --
> Reclaim your digital rights, eliminate DRM, learn more at
> http://www.defectivebydesign.org/what_is_drm
>

Renato Golin | 1 Jul 17:10 2008
Picon
Picon

Re: substring search

Richard Holland wrote:
> Can you check the generated SQL to see if it is actually getting through?

Hi Richard,

I hacked it and made the names different (to print me the query again) 
and the like is wrong:

(UNIPROT__DESCRIPTION__dm.text_1054 like 'Cytochrome')

Where it should be "like '%Cytochrome%'"...

The full query:

SELECT renato1.UNIPROT__DESCRIPTION__DM.text_1054 FROM 
renato1.UNIPROT__DESCRIPTION__DM, renato1.UNIPROT__DESCRIPTION__dm, 
renato1.UNIPROT__ENTRY__MAIN main WHERE 
(UNIPROT__DESCRIPTION__dm.text_1054 like 'Cytochrome') AND 
main.dbentry_id_1039_key=renato1.UNIPROT__DESCRIPTION__DM.dbentry_id_1039_key 
AND 
main.dbentry_id_1039_key=<*>UNIPROT__DESCRIPTION__dm.dbentry_id_1039_key 
AND rowNum < 201

cheers,
--renato

Richard Holland | 1 Jul 17:13 2008
Picon

Re: substring search

Ok, thanks for that. It confirms my suspicions!

BioMart currently relies on the MySQL-specific quirk that when using
'like', MySQL implicitly adds % symbols to both ends of the
expression, guessing that is what the user wanted. It only skips doing
this if a % is explicitly included in the search term by the user.

MySQL is unique in this approach, and other systems (Postgres, Oracle,
etc.) do not make any such assumptions. For these systems, writing
'like' but not explicitly specifying any % symbols is exactly the same
as writing = instead of like.

So, it's a BioMart bug! Syed/Arek - do you know how to fix this? (I
guess some kind of extra step is needed during SQL construction that
spots the like qualifier and wraps the search expression in % symbols
unless it already contains one).

cheers,
Richard

2008/7/1 Renato Golin <renato <at> ebi.ac.uk>:
> Richard Holland wrote:
>>
>> Can you check the generated SQL to see if it is actually getting through?
>
> Hi Richard,
>
> I hacked it and made the names different (to print me the query again) and
> the like is wrong:
>
(Continue reading)

Syed Haider | 1 Jul 17:34 2008
Picon
Picon

Re: substring search

Hi Renato,

why dont you try to specify '%' in your input to filter. e.g %cytochrome
%

syed

On Tue, 2008-07-01 at 16:10 +0100, Renato Golin wrote:
> Richard Holland wrote:
> > Can you check the generated SQL to see if it is actually getting through?
> 
> Hi Richard,
> 
> I hacked it and made the names different (to print me the query again) 
> and the like is wrong:
> 
> (UNIPROT__DESCRIPTION__dm.text_1054 like 'Cytochrome')
> 
> Where it should be "like '%Cytochrome%'"...
> 
> 
> The full query:
> 
> SELECT renato1.UNIPROT__DESCRIPTION__DM.text_1054 FROM 
> renato1.UNIPROT__DESCRIPTION__DM, renato1.UNIPROT__DESCRIPTION__dm, 
> renato1.UNIPROT__ENTRY__MAIN main WHERE 
> (UNIPROT__DESCRIPTION__dm.text_1054 like 'Cytochrome') AND 
> main.dbentry_id_1039_key=renato1.UNIPROT__DESCRIPTION__DM.dbentry_id_1039_key 
> AND 
> main.dbentry_id_1039_key=<*>UNIPROT__DESCRIPTION__dm.dbentry_id_1039_key 
(Continue reading)

Renato Golin | 1 Jul 17:37 2008
Picon
Picon

Re: substring search

Syed Haider wrote:
> Hi Renato,
> 
> why dont you try to specify '%' in your input to filter. e.g %cytochrome
> %

Hi Syed,

Yes, that sure work.

Personally I think it'd be good to give the user that flexibility, 
especially because you can say "Citocrome%" instead of wrapping with 
both when you just want it in the beginning, and that's much faster.

I just don't know what current biomart users would think of that and if 
that should have some comment just before/after the field explaining that.

cheers,
--renato

--

-- 
Reclaim your digital rights, eliminate DRM, learn more at 
http://www.defectivebydesign.org/what_is_drm

Henrikki Almusa | 3 Jul 13:53 2008
Picon
Picon

Martview and marteditor

Hi all,

I've been looking on how to set up a mart on kubuntu linux 8.04. After
some problems (to which answers mostly was found with google like path
of axsp2 and so forth) I have it now up and running on a test setup.
There is though few questions that I have with regards to martview and
using marteditor to set it up.

1. Numbers in filters. I have some columns in database that uses
numbers. I would like to allow users to add filters like '>170'. How
should I do this? And is it possible to allow a range of values like
'150-170' or something like this?

2. The documentation suggests changing qualifier from '=' to '>', but it
neglects to tell me why or what alternatives there are. Any
documentation on that?

3. I would like to restrict some filters to different values. There was
column regexp, but no documentation on it. After browsing web I decided
to try it. And it seems that it does it. While this allows me to define
a text field into integers (\d+) or float for that matter ('[\d.]+'
should work), I'm wondering if there is a better way to restrict only to
range of numbers (like 0 - 100).

Note that with regex '\d+' in column, the martview website allows me to
write '>150' to that column, but result is not valid to more than 150 on
the column which I specified.

One problem that I encountered that I wasn't able to find answer from
web directly was problem that my apache killed itself immediately after
(Continue reading)

Renato Golin | 3 Jul 14:30 2008
Picon
Picon

Re: Martview and marteditor

Henrikki Almusa wrote:
> 2. The documentation suggests changing qualifier from '=' to '>', but it
> neglects to tell me why or what alternatives there are. Any
> documentation on that?

Hi Henrikki,

I'm not the expert, but as far as I got it any SQL qualifier can be 
used. So: =, >, <, !=, in, like are the most common ones.

> I tried to run it with sudo or as root. Eventually I figured that it
> tried to resolve unix user above from '/etc/passwd', which obviously
> will fail. So adding following row to httpd.conf fixed this
> 
> User www-data
> 
> www-data is user defined in system (at least on kubuntu and most likely
> on ubuntu).

Yes, if you're using your system-wide apache that's the way to go.

The configurator generates an httpd.conf for when you're using with your 
own user. Probably, as your user didn't have write permission on 
/var/www it failed.

If you had started your apache manually like:

$ apache2 -d ~/biomart -f conf/httpd.conf

it should work with your user (and with the generated config file).
(Continue reading)


Gmane