Does the out-of-the-box version support case-insensitive search by Cyrillic characters?

[krutoo]

Hi, i use better-sqlite3 in Strapi (Headless CMS) and in my case search by Cyrillic characters is case sensitive (even when i use special $containsi filter where last i means case-insensitive comparison)

It’s hard for me to understand where exactly the problem is, on the Strapi side or on the better-sqlite3 side, so could you help?

Does the out-of-the-box compilation of sqlite support case-insensitive Cyrillic search?

[Prinzhorn]

Does the out-of-the-box compilation of sqlite support case-insensitive Cyrillic search?

I've put this verbatim into Google, first result:

https://stackoverflow.com/questions/3465118/nhibernate-sqlite-and-cyrillic-characters-case-sensitivity-and-fallback-queri

leads to

https://www.sqlite.org/faq.html#q18

which says

The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.

Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.

searching this repo for "icu extension"

https://github.com/search?q=repo%3AWiseLibs%2Fbetter-sqlite3+ICU+extension&type=issues

#465

So the answer to your question is no, but you can load an extension or create a custom build

Edit: you can also easily test this yourself

import Database from 'better-sqlite3'

const db = new Database(':memory:');

console.log(db.prepare(`SELECT 'a' == 'A'`).get());
console.log(db.prepare(`SELECT 'a' == 'A' COLLATE NOCASE`).get());

console.log(db.prepare(`SELECT 'э' == 'Э'`).get());
console.log(db.prepare(`SELECT 'э' == 'Э' COLLATE NOCASE`).get());

$ node index.mjs 
{ "'a' == 'A'": 0 }
{ "'a' == 'A' COLLATE NOCASE": 1 }
{ "'э' == 'Э'": 0 }
{ "'э' == 'Э' COLLATE NOCASE": 0 }

[krutoo]

@Prinzhorn Thanks a lot but I cannot compile sqlite with SQLITE_ENABLE_ICU, I do everything according to the guide

But have error:

npm install
# ...
unicode/utypes.h: No such file or directory
# ...

Can you suggest how to solve this? Looks like I don't have some of ICU files or something

[krutoo]

@Prinzhorn Oh, looks like installing libicu-dev helps

sudo apt update
sudo apt install libicu-dev

But it is not working as expected:

$ node test-sqlite.mjs
{ "'f' == 'F'": 0 }
{ "'f' == 'F' COLLATE NOCASE": 1 }
{ "'э' == 'Э'": 0 }
{ "'э' == 'Э' COLLATE NOCASE": 0 }

[Prinzhorn]

I've never done anything like this myself, so your Google skills will get you just as far as mine. ChatGPT says to run "PRAGMA collation_list;" to make sure the compilation actually worked. Other than that I don't know how to help you any further.

[hodoknow]

Круто, я думаю, вот это решит проблему:
https://antonz.ru/install-sqlite-extension

[samal-rasmussen]

Edit: I now notice that this discussion is about searching and not sorting, which was what I was the issue I was facing. I'll leave this here, as it is still somewhat relevant.

I solved this issue of sorting unicode text using a user-defined function:

sqlite_db.function('js_normalize', ((str: string) => {
	const normalized = str
		// Ignore case.
		.toLowerCase()
		/*
		 * 1. Decompose the string into its base characters and diacritics.
		 * 2. Remove all diacritics.
		 *
		 * This prevents accented characters from sorting after 'z' in Unicode order.
		 *
		 * Without normalization:
		 *   lang
		 *   laser
		 *   lång     ← å (U+00E5) sorts after all basic Latin letters
		 *
		 * With normalization:
		 *   lang
		 *   lång     ← treated as "lang" for sorting
		 *   laser
		 *
		 * Unfortunatly this is not perfect as it is blind to diacritics and will not move them around.
		 * E.g. it won't move the lång to the end of this list.
		 *   lang
		 *   lång
		 *   lang
		 *
		 * This is better than sorting all diacritics as last, and it beats having to build and load a custom collation.
		 */
		.normalize('NFD')
		.replace(/[\u0300-\u036f]/g, '');
	return normalized;
}) as any);

And you use it like this:

SELECT
	*,
	js_normalize(name) as normalized_name
FROM products
ORDER BY normalized_name ASC