How to change collation in an existing database?

edwinsn · 2023-02-24 08:24:15

@ab,

Is there a way to change all collations in a SQLite database? Collations like WIN32CASE created by mORMot is preventing me from using high productivity database GUI tools like Sqlite Studio, so I want to change all the mORMot-specific collations to the standard SQLite collations. Would you advise? Thanks.

Update 1: I assume TSQLRecordProperties.SetCustomCollation and TSQLRecordProperties.SetCustomCollationForAll are for newly created records only, but not existing records, is that correct?

Last edited by edwinsn (2023-02-24 08:47:17)

ab · 2023-02-24 08:47:30

It is a SQLite3 restriction: you can't change the collation after the table is created.

You need to create a new table, fill it with the data from the previous table, then delete the old table and rename the new one in place of the old.
You could try to do it at the columns level, but I guess it would be slower.

edwinsn · 2023-02-24 10:06:12

Thanks for the suggestion, it seems there is not an alternative easier way...

edwinsn · 2023-03-08 07:48:58

I found a workaround when using the external tools to query table rows with mORMot-specific collations - SQLite supports the following sql syntax:

select id, name collate nocase, datecreated from table 1

In other words, append 'collate nocase' to each of the field that contain non-standard collations. It might be a bit of a hassle, but this allowed me to take advantage of mORMot's great feature, while still using productive SQLite GUI like Sqlite Studio, what' a great world!

zed · 2023-03-14 06:27:11

In SQLiteStudio you can define your own collation (Tools - Open collations editor) and open DB as usual.

For the SYSTEMNOCASE you can try this snippet:

function system_nocase(a, b)
{   
    a = a.toUpperCase();
    b = b.toUpperCase();

    return (a < b ? -1 : (a > b ? 1 : 0));  
}

edwinsn · 2023-03-14 07:43:53

zed wrote:

In SQLiteStudio you can define your own collation (Tools - Open collations editor) and open DB as usual.
https://i.ibb.co/crSzwwb/1678775092.png

For the SYSTEMNOCASE you can try this snippet:
function system_nocase(a, b)
{   
    a = a.toUpperCase();
    b = b.toUpperCase();

    return (a < b ? -1 : (a > b ? 1 : 0));  
}

Wow, didn't know about this before! Thanks zed!

mORMot Open Source

#1 2023-02-24 08:24:15

How to change collation in an existing database?

#2 2023-02-24 08:47:30

Re: How to change collation in an existing database?

#3 2023-02-24 10:06:12

Re: How to change collation in an existing database?

#4 2023-03-08 07:48:58

Re: How to change collation in an existing database?

#5 2023-03-14 06:27:11

Re: How to change collation in an existing database?

#6 2023-03-14 07:43:53

Re: How to change collation in an existing database?

Board footer