Converting entries from MySQL to SQLite: Be aware of case-sensitivity

[deoren]

I'm not sure if it's because of the version of Postfix provided by Ubuntu 16.04, the SQLite library or something else, but attempting to run this query gives different results depending on whether the db backend is MySQL or SQLite:

    SELECT destination FROM local_aliases
    WHERE source ='%s'
    AND enabled = '1'

If there is an entry in the source column of MAILER-DAEMON and destination value of root, I get these results:

sudo postalias -q 'MAILER-DAEMON' sqlite:/etc/postfix/sqlite/sqlite-local_aliases.cf

Result: Nothing.

sudo postalias -q 'MAILER-DAEMON' mysql:/etc/mysql/mysql/mysql-local_aliases.cf

Result: root

[deoren]

From the access table documentation:

CASE FOLDING The search string is folded to lowercase before database lookup. As of Postfix 2.3, the search string is not case folded with database types such as regexp: or pcre: whose lookup fields can match both upper and lower case.

So presumably a lower-case search string is used?

ubuntu@ubuntu-1604-virtual-machine:~$ sudo sqlite3 /var/cache/mysql2sqlite/mailserver.db "SELECT destination FROM local_aliases WHERE source = 'mailer-daemon' AND enabled = '1';"

No result.

ubuntu@ubuntu-1604-virtual-machine:~$ sudo sqlite3 /var/cache/mysql2sqlite/mailserver.db "SELECT destination FROM local_aliases WHERE source = 'MAILER-DAEMON' AND enabled = '1';"

Result: root

Does the DB schema need to be setup to be case-insensitive? Presumably yes since the Postfix docs indicate that search strings are folder to lowercase ...

[deoren]

Found this:

Every column of every table has an associated collating function. If no collating function is explicitly defined, then the collating function defaults to BINARY. The COLLATE clause of the column definition is used to define alternative collating functions for a column.

Other sources give examples of setting the collation using the COLLATE clause, either at table definition time or via SELECT query.

Example of the latter:

select * from table where col = 'aa' COLLATE NOCASE

and the former:

CREATE TABLE IF NOT EXISTS Streams
(
    Name      char(40) NOT NULL COLLATE NOCASE,
    GlobalVer INTEGER  NOT NULL,
    PRIMARY KEY(Name, GlobalVer)
) WITHOUT ROWID;

Some of the examples mention specific concerns regarding query speed and indexes, particularly that if you define a column with NOCASE collation you should do the same with indexes that you define. Indexes are one of my many weak spots, so I may have to revisit that work as part of a follow-up PR later.

References:

• https://www.sqlite.org/datatype3.html#collation
• https://stackoverflow.com/questions/799840/what-does-the-collate-keyword-do-when-creating-a-sqlite-index
• https://www.sqlite.org/datatype3.html#collation
• https://stackoverflow.com/questions/44947414/sqlite-speed-up-select-with-collate-nocase

[deoren]

This should be fixed now. I've got it working in a local/private repo, will plan to close this once the local repo has been cleaned up and pushed here.

[deoren]

Resolved in a commit that was squashed and folded into 5acba3af54c3006d6938c231516a36460ac6f070, which was merged as part of WhyAskWhy/mysql2sqlite#8.

In short, the fix (as noted previously) was setting the SQLite text columns and the indexes to COLLATE NOCASE.