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

[Glynn Clements]

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 at gclements.plus.com>