SQLite Forum


6 forum posts by user bohwaz

23:22 Reply: How to avoid 'row X missing from index' when using a custom collation? (artifact: ac26a357d9 user: bohwaz)

Yes of course my example was bad, the index can only be used in the order in that query, but that's the main use anyhow.

The idea of using a function in the index is nice, but I'm wondering what's the "least bad" thing between using the custom NOCASE collation and the function index:

  1. NOCASE custom collation: integrity_check is broken, but database can be read in all instances, the index ordering actually works. BUT if you insert data to tables, you cannot match indexed columns using the custom collation (eg. INSERT INTO names (name) VALUES ('Élodie TEST'); SELECT * FROM names WHERE name = 'Élodie TEST'; will return nothing but SELECT * FROM names WHERE name = 'Élodie TEST' COLLATE BINARY; will work).

  2. function index: integrity_check works, but the database may not be read in some instances, and data cannot be inserted unless indexes are dropped.

Thanks for your help :)

23:02 Reply: How to avoid 'row X missing from index' when using a custom collation? (artifact: 3d2056cc62 user: bohwaz)

Thanks for the details, that explains a lot and makes a lot of sense :)

19:22 Post: How to avoid 'row X missing from index' when using a custom collation? (artifact: cde320493e user: bohwaz)

My app is used in France, and so we are using accents in French. My users want to be able to order a list of names without using the accents or the case, so that this list of users:

Mederic B
Méderic A
MÉdÉric C

is sorted like that:

Méderic A
Mederic B
MÉdÉric C

and when you are searching for mederic it will return all of the 3 entries.

My first solution was to use a custom function, eg. transliterate_to_ascii:

SELECT * FROM names WHERE transliterate_to_ascii(name) LIKE transliterate_to_ascii('%médéric%') ORDER BY transliterate_to_ascii(name);

But obviously this doesn't scale very well as that function would be called a lot, as I can't use indexes.

So my second solution was to use a custom collation.

But because my users must be able to download the database and explore it with any third-party SQLite tool, I couldn't use a custom-named collation, eg. french_unicode, and I used the "trick" of overriding the default NOCASE collation:

$db->createCollation('NOCASE', 'unicode_collation');

So now I can create indexes:

CREATE INDEX names_name ON names (names COLLATE NOCASE);

All went fine: I can give users of my app the ability to search regardless of accents and case, and I can have indexes so performance is good, and they can also use the database with third-party tools.

But when I'm doing a PRAGMA integrity_check; from any third-party app, I get error messages:

row 4 missing from index names_name
row 6 missing from index names_name
row 7 missing from index names_name

and so on. This does not happen when using my own app with the correct collation.

Not sure why this is happening, but most importantly is there a better way to do a "portable" database using custom collations that also handles indexes?


13:00 Reply: how best to completely replicate a db file? (artifact: 1ab12e5bbc user: bohwaz)

You can also use VACUUM INTO 'file.db'; to create a backup since 3.27. That's what I use. If not available, I use the Online Backup API. Before that I had to resort to making a file copy after starting an exclusive transaction.

00:41 Reply: Re-use and execute SQL code stored in tables (eval?) (artifact: 469088bbbe user: bohwaz)

Thank you, I didn't know about generated columns, sounds nice, but unfortunately this is a widely distributed web-app and the currently required version of SQLite is only 3.16, and I can't use any extension either. I knew about the eval() extension but can't use it here.

I will just do an extra loop in the app code itself then.

Thanks everyone for your help :)

20:54 Post: Re-use and execute SQL code stored in tables (eval?) (artifact: 9cd4308b37 user: bohwaz)

Hey, I know this has already probably been answered, but is it possible to re-use the content of a table cell in a query?

Not just executing a query stored in a table, but using one of its columns as SQL code for another query.

For example:

CREATE TABLE user_pricings (condition TEXT);
CREATE TABLE users (name, age);
INSERT INTO users ('bohwaz', 42);
INSERT INTO user_pricings ('CASE WHEN age < 18 THEN 42.00 ELSE 55.00 END');

SELECT *, `(SELECT condition FROM user_pricings)` FROM users;

Which would be interpreted as:

SELECT *, CASE WHEN age < 18 THEN 42.00 ELSE 55.00 END FROM users;

Thanks :)