[Kexi-devel] iLIKE researches

Thu May 21 20:13:10 UTC 2015

On 21 May 2015 at 17:30, Shtemberko Roman <shtemberko at gmail.com> wrote:
> Some researches on iLIKE support for Kexi supported DBs (regarding this
> wish):
>
> Looks like only PostgreSQL supports(needs) iLike operator, as others(MySQL,
> SQLite (are these all supported?)) are using case-insensitive matching with
> default LIKE operator.
>
> So my suggestions are:
> 1) Use case-insensitive matching for LIKE. In this case we will only need to
> replace a call to PostgreSQL to iLIKE. And we can treat iLIKE as regular
> like in Kexi's Queries.
> 2) Use case-sensitive matching. This way we will need to make some hacks for
> calls to MySQL and SQLite (looks possible after some searching, but still a
> bit hackish tho).
>
> I will stick with first one, if you ask me. :)
>

Thanks so much, Roman, this is what we needed to know as a first step!

Re 1) It's a big in our PostgreSQL support. @Roman would you like to
report a bug and fix it?

Re 2) So we have case-insensitive LIKE by default and this is what we
want for all backend types. Consistency is our motto at this level.
We'd like to offer case-sensitive like in the future but this is
a tricky task indeed. All we can afford now is some precise notes from
this thread,
later to be moved to the wiki.

2.1 For PostgreSQL we're done: just use "LIKE" in the raw SQL.

2.2 For MySQL use can "BINARY", as in "'abc' LIKE BINARY 'ABC'"  in the raw SQL.

2.2 For SQLite this question shows it best:
http://stackoverflow.com/questions/10590700/sqlite-execute-case-sensitive-like-query-on-a-specific-query

"PRAGMA case_sensitive_like = boolean" changes behaviour forall LIKE queries.
So if we have "SELECT * FROM foo where a LIKE 'x%' AND b LIKE 'y%'"
and want only the second LIKE to be case-sensitive, we CAN'T do that.

I tried adding COLLATE BINARY: either this way: "a COLLATE BINARY LIKE
'foo%'" or "a LIKE 'foo%' COLLATE BINARY".
It is parsed but neither seems to work, LIKE is still case-insensitive.
Operator == is case-sensitive but then the user has to use complex
comparisons and make certain assumptions.

PROPOSAL: Maybe for SQLite the solution is to use REGEXP when
sending raw query to the SQLite engine. See
https://www.sqlite.org/lang_expr.html for REGEXP:
"The REGEXP operator is a special syntax for the regexp() user function."

It takes 2 args; so e.g. we can use first character of the 2nd arg as an
indicator of what behaviour of the regexp we want:

- 'c' == case sensitive LIKE
- 'r' == regular regexp (yes, it's a TODO, we don't have now this but
can have one or another implementation; see the 1st answer to
[http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query]
to see the usage of sqlite3-pcre extension.

The result is: hacky raw SQL statmements, but that's the cost. They
are hidden anyway :)

2.3 Regarding the original wish, it was about supporting the ILIKE
keyword by the Kexi's SQL processor.
LIKE is  == ILIKE in Kexi, what is inherited from SQLite's and MySQL's
behaviour  and PostgreSQL driver will do the same, see 1).
So we add parsing of the ILIKE keyword and react in the same way as for LIKE.

@Roman: a one line patch for the bison file would be enough. BTW, the
docs for Kexi SQL shall say "ILIKE works exactly as LIKE and is
provided for compatibility and completeness".

- -
PS: More input:
1. Interesting content from [1]
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt:

4.2.2.2  Other operators involving character strings,  <like predicate>

2. COLLATE controls case-sensitivity of LIKE.

3. From "8.5  <like predicate>" [1], the grammar is
   <like predicate> ::=
              <match value> [ NOT ] LIKE <pattern>
                [ ESCAPE <escape character> ]
I propose to file a separate wish "Add ESCAPE keyword for the [NOT]
LIKE operator".
Implementation is not necessarily of high priority though...

4. A TODO (mostly to me?): we forgot to add a unit test for the single
character "_" wildcard (in Predicate, now in KDb --
autotests/parser/data/statements.txt), we only seem to
have one for "%".

5. A TODO (for me, a detail but nicely shows the mechanics): We have
a proper support for query parameters with LIKE, e.g.

SELECT * FROM foo WHERE foo.id LIKE [ask] ORDER BY id;

Kexi asks for the [ask] parameter: http://i.imgur.com/9jM6Auv.png
One thing to fix: because we're comparing against foo.id (INT)
here, Kexi forces the numeric type to be entered while text should be allowed.
E.g. 1% should be possible to enter in this dialog.

5.1. Why the LIKE for integers works, by the way?

- In MySQL, LIKE is permitted on numeric expressions. (This is an
extension to the standard SQL LIKE.) -- an info from the docs.

- In SQLite there's type affinity so "12 LIKE '1%'" works too -- 12
is casted to "12" before comparing.

- TODO: check for PostgreSQL (@Roman?)

Thanks.

> Some relative links:
> SQLite: https://www.sqlite.org/lang_expr.html
> MySQL:
> https://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
> PostgreSQL:
> http://www.postgresql.org/docs/8.3/static/functions-matching.html

-- 
regards, Jaroslaw Staniek

KDE:
: A world-wide network of software engineers, artists, writers, translators
: and facilitators committed to Free Software development - http://kde.org
Calligra Suite:
: A graphic art and office suite - http://calligra.org
Kexi:
: A visual database apps builder - http://calligra.org/kexi
Qt Certified Specialist:
: http://www.linkedin.com/in/jstaniek