[GRASS-dev] lib/db/sqlp: how to activate "LIKE" for dbf?

GRASS GIS GRASS GIS developer
1

Hi,

I would like the SQL 'LIKE' to be functional for the
dbf driver. While '~' is working, 'LIKE' is rejected.
I assume (also tried a bit) that 'LIKE' needs to be
mapped to '~' (COMPARISON_OPERATOR, SQL_MTCH) to be passed
on to the dbf driver.

I didn't manage to implement it right in lib/db/sqlp.
Ideas?

Markus

2

Markus Neteler wrote:

Hi,

I would like the SQL 'LIKE' to be functional for the
dbf driver. While '~' is working, 'LIKE' is rejected.
I assume (also tried a bit) that 'LIKE' needs to be
mapped to '~' (COMPARISON_OPERATOR, SQL_MTCH) to be passed
on to the dbf driver.

I didn't manage to implement it right in lib/db/sqlp.
Ideas?

I would think that adding:

[Ll][Ii][Kk][Ee] {
        yylval.strval = (char*)strdup("~");
        return COMPARISON_OPERATOR;
      }

to lib/db/sqlp/lex.l should suffice.

--
Glynn Clements <glynn@gclements.plus.com>

3

On Thu, Jul 20, 2006 at 06:58:29AM +0100, Glynn Clements wrote:

Markus Neteler wrote:

> Hi,
>
> I would like the SQL 'LIKE' to be functional for the
> dbf driver. While '~' is working, 'LIKE' is rejected.
> I assume (also tried a bit) that 'LIKE' needs to be
> mapped to '~' (COMPARISON_OPERATOR, SQL_MTCH) to be passed
> on to the dbf driver.
>
> I didn't manage to implement it right in lib/db/sqlp.
> Ideas?

I would think that adding:

[Ll][Ii][Kk][Ee] {
        yylval.strval = (char*)strdup("~");
        return COMPARISON_OPERATOR;
      }

to lib/db/sqlp/lex.l should suffice.

Works, thanks.
The DBF driver now understands (sort of) 'LIKE'.
Unfortunately the behaviour isn't yet PostgreSQL compliant:

PostgreSQL says:
http://www.postgresql.org/docs/8.1/interactive/functions-matching.html#FUNCTIONS-LIKE
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false

GRASS DBF driver now does:

GRASS 6.1.cvs (pat):~ > v.db.select meteo_stations where="station LIKE 'P0'"
cat|station|east|north
93|P01|1653952|5061546
94|P02|1649511|5063172
95|P03|1640450|5098211

GRASS 6.1.cvs (pat):~ > v.db.select meteo_stations where="station LIKE 'P__'"
cat|station|east|north

GRASS 6.1.cvs (pat):~ > v.db.select meteo_stations where="station LIKE 'P%'"
cat|station|east|north

GRASS 6.1.cvs (pat):~ > v.db.select meteo_stations where="station LIKE 'P'"
cat|station|east|north
93|P01|1653952|5061546
94|P02|1649511|5063172
95|P03|1640450|5098211

GRASS 6.1.cvs (pat):~ >

I don't know where the pattern matching is done for the DBF
driver (db/drivers/dbf/?).

Markus

4

Markus Neteler wrote:

> > I would like the SQL 'LIKE' to be functional for the
> > dbf driver. While '~' is working, 'LIKE' is rejected.
> > I assume (also tried a bit) that 'LIKE' needs to be
> > mapped to '~' (COMPARISON_OPERATOR, SQL_MTCH) to be passed
> > on to the dbf driver.
> >
> > I didn't manage to implement it right in lib/db/sqlp.
> > Ideas?
>
> I would think that adding:
>
> [Ll][Ii][Kk][Ee] {
> yylval.strval = (char*)strdup("~");
> return COMPARISON_OPERATOR;
> }
>
> to lib/db/sqlp/lex.l should suffice.

Works, thanks.
The DBF driver now understands (sort of) 'LIKE'.
Unfortunately the behaviour isn't yet PostgreSQL compliant:

I don't know where the pattern matching is done for the DBF
driver (db/drivers/dbf/?).

The actual DBF implementation is in db/drivers/dbf/dbfexe.c.

The LIKE operator (SQLP_MTCH) is simply the strstr() function, i.e. it
indicates whether the RHS is a substring of the LHS.

Efficiently implementing correct semantics for patterns which contain
more than one % is non-trivial.

For a single %, the easiest approach is to match from both ends, i.e.
check that the beginning of the string matches the part before the %,
the end of the string matches the part after the %, and the two parts
don't overlap (the last part is necessary so that 'abc' LIKE 'ab%bc'
is false).

--
Glynn Clements <glynn@gclements.plus.com>