Kexi/Junior Jobs/Add support for "NOT LIKE" SQL operator

Status: DONE to Radoslaw Wicik <[email protected]>, Review: 116889, Target: 2.8.2, Difficulty: MEDIUM

Proposed and mentored by Jstaniek (talk) 20:52, 14 March 2014 (UTC)

The Goal

Implement "NOT LIKE" SQL operator for queries.

Rationale

"NOT LIKE" operator is a part of SQL92 standard supported by Kexi's database backends. Without it users forced to use constructions such as NOT (a LIKE '%foo%' OR b LIKE '%bar%'), see Kexi forum.

Equivalent condition with NOT LIKE would be more human: a NOT LIKE '%foo%' AND b NOT LIKE '%bar%'.

Requirements

1. Between NOT and LIKE there can be any amount of non-empty white space
2. The token should be case-insensitive while parsed in SQL statements
3. All properties of LIKE defined by the database backend also apply to NOT LIKE. The difference is in inverting the sense of the test. (See for example 'The LIKE and GLOB operators' section for SQLite at [1])

Hints

1. LIKE operator uses the BinaryExpr class, NOT LIKE should too.
2. KexiDB's SQL lexer (sqlscanner.l) should introduce a new token: NOT_LIKE, what is analogous to NOT_SIMILAR_TO.
3. NOT_LIKE support should be implemented in sqlparser.y (see NOT_SIMILAR_TO for hints).
4. Do not forget to execute make parser command in $builddir/libs/db to update generated parser and lexer whenever the .y and .l files change.

Tests

Let's perform functional tests for LIKE and NOT LIKE in one go. Positive test cases:

• "Foo" LIKE "%F%"
• "Foo" LIKE "Foo"

Negative test cases:

• "Bar" LIKE "%F%"

NULL result

• NULL LIKE "x"

TODO: check case sensitivity

TODO...

TODO: Test db from kexi/newapi would be used. To create the testdb.kexi database:

cd $build/calligra/kexi/tests/newapi
./kexidbtest --test tables sqlite3 testdb.kexi

First, do not forget add -DKDE4_BUILD_TESTS=ON option to cmake so the kexidbtest gets built.