Why does the Wappler use whereLike but not whereILike?

Server Side Components General
5

The knex library is only used in for NodeJS and the other server languages have a different implementation. I checked the docs and they don’t mention on which databases it will work. They added the feature for ILike somewhere last year.

So, I checked the source code, and I actually don’t like how they implemented it. Here are my findings:

  • Postgres all should be working fine; they just generate the ILIKE query as expected.
  • SQLite3 will throw an error, no support there.
  • MySQL the whereILike method is actual the like statement and they changed the whereLike method by adding COLLATE utf8_bin to the statement to make it case sensitive.
  • MSSQL they seem to have another solution which I think is questionable. The whereLike uses collate SQL_Latin1_General_CP1_CS_AS and the whereILike uses collate SQL_Latin1_General_CP1_CI_AS. This will make the Like case sensitive and ILike case insensitive, but also will not support Unicode since they use the Latin1 charset.

It’s nice that they added ILike as a method for Postgres, but they also changed the default behavior of the other databases to mimic Postgres. Their implementation actually brakes the expected behavior for MySQL and MSSQL where you set the Case Sensitivity of the table with the collation. When you set the table collation in MySQL to utf8_bin it will be case-sensitive, even when using whereILike.

6 Likes