[kexi] [Bug 348017] Add support for 'case insensitive' operator ILIKE for SQL queries

[Jarosław Staniek]

https://bugs.kde.org/show_bug.cgi?id=348017

--- Comment #4 from Jarosław Staniek <staniek at kde.org> ---
@Ian
Two things
#1. Consistency between backends is by design, and is important for tools like
Kexi, which isn't a frontend to a specific backend but rather a complete
integrated stack. We had to pick something and if I read
[http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt] correctly,
collation is based on what column has defined, and on default collation, and so
on (there are many levels), so there's no one particular behaviour of LIKE
worldwide. If you copy a query with LIKEs to another database (the same
backend!) and there are the same tables, but some different colations, the
LIKEs can behave differently. It's more explicitly stated for example in  MSSQL
FAQs. I've been reading this often about MySQL too. No idea about Oracle but I
can guess...

#2. I agree that so called native SQL support could be useful for two reason:
give the power user power they deserve (and right to shot in the feet to
others), have a solution until KEXISQL is relatively feature-complete. 

#2.1. I also completely agree that there's a big value in having the KEXISQL
mode; so don't give up :) Having that in Kexi costs tons of development,
without this we wouldn't need the whole parser in the first place. LO Base does
not go that far and then the users have to study the behaviour of the backend
before they enter some SQL for forms. (it's good if they know they have to
study that...) In contrast, MS Access has one db engine plus ADO/ODBC linking
just for the same consistency. 

So exactly thanks to KEXISQL we can and want to add case sensitive LIKE, see
https://mail.kde.org/pipermail/kexi-devel/2015-May/000343.html -- #1 does not
mean we reject the need for case-sensitive LIKEs.

We can do that in 2.9!

Now a question or an exercise: what grammar for that would be OK? 
My proposal: one that could fit the SQL known so far.

<column> COLLATE CASE LIKE <pattern>

'CASE' would be the magical case-sensitive collation. We can go with CS or
SQL_CS or anything like at
https://msdn.microsoft.com/en-us/library/ms144250%28v=sql.105%29.aspx for
example. But we fight for readability.

-- 
You are receiving this mail because:
You are the assignee for the bug.