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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

How to change collation in an existing database?

@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)


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,609
Website

Re: How to change collation in an existing database?

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.

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

Re: How to change collation in an existing database?

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


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

Re: How to change collation in an existing database?

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!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 105

Re: How to change collation in an existing database?

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

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));  
}

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

Re: How to change collation in an existing database?

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!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

Board footer

Powered by FluxBB