Sqlite under FireDAC [9]-About sorting

SQLite internal is in binary order, can support ANSI; FRIEDAC supports Unicode sorting through tfdsqlitecollation, and can be customized by its Oncompare event.

The following example tests the differences between the two types of sorting.
You can quickly complete the form design by pasting the following code directly onto a blank form:
Object Dbgrid1:tdbgrid left = 0 Top = 0 Width = 297 Height = 199 Align = Alleft DataSource = DataSource1 TabOrder = 0 Titlefont.charset = Default_charset Titlefont.color = Clwindowtext Titlefont.height = -11 TitleFont.Name = ' Tahoma ' Titlefont.style = []endobject Button1:tbutton left = 303 Top = Width = Height = + Caption = ' SQLite ' #208 69#32622#25490#24207 TabOrder = 1 OnClick = Button1clickendobject Button2:tbutton left = 303 Top = Width = + H eight = Caption = ' FireDAC ' #40664 #35748#25490#24207 taborder = 2 OnClick = Button2clickendobject fdconnection1:tfd Connection left = over-top = 24endobject Fdphyssqlitedriverlink1:tfdphyssqlitedriverlink left = 143 top = 24endobject F Dguixwaitcursor1:tfdguixwaitcursor Provider = ' Forms ' left = 260 Top = 24endobject fdquery1:tfdquery Connection = FD Connection1 left = + top = 88endobject Datasource1:tdatasource DataSet = FDQuery1 left = top = 88endobject FDSQ Litecollation1:tfdsqlitEcollation Driverlink = FDPhysSQLiteDriverLink1 collationname = ' mycollation ' localename = ' zh-cn ' left = Top = 1 20end
Code:

Procedure TForm1.FormCreate(Sender: TObject);
Var
  i: Integer;
  LCode: Integer;
Begin
  {Set parameters for FDSQLiteCollation1}
  FDSQLiteCollation1.DriverLink := FDPhysSQLiteDriverLink1;
// FDSQLiteCollation1.CollationKind := scCompareString; //This is the default value (Unicode is not case sensitive, WinForm.CompareString is called under Win); use other options to customize the collation
  FDSQLiteCollation1.LocaleName := ‘zh-CN‘;
  FDSQLiteCollation1.Flags := [sfIgnoreCase];
  FDSQLiteCollation1.CollationName := ‘MyCollation‘; //All the following calls depend on this name.
  FDSQLiteCollation1.Active := True;

  FDConnection1.Params.Add(‘DriverID=SQLite‘);
// FDConnection1.Params.Add(‘OpenMode=CreateUTF8‘); //This is the default value, optional CreateUTF16(Unicode)

  {Create test table, three fields str (Chinese characters), code (Unicode values corresponding to Chinese characters), id (add order)}
  FDConnection1.ExecSQL('CREATE TABLE MyTable(str string(10), code integer, id integer) ‘);
// FDConnection1.ExecSQL(‘CREATE TABLE MyTable(str string(10) COLLATE MyCollation, code integer, id integer) ‘); //Used in table design

  {Add test data data}
  For i := 0 to 99 do
  Begin
    LCode := Random($9FA5-$4E00);
    FDConnection1.ExecSQL(‘INSERT INTO MyTable(str, code, id) VALUES(:1, :2, :3)‘, [WideChar($4E00 + LCode), LCode, i+1]);
  End;

  FDQuery1.Open(‘SELECT * FROM MyTable‘); //no sort
End;

Procedure TForm1.Button1Click(Sender: TObject);
Begin
  FDQuery1.Open(‘SELECT * FROM MyTable ORDER BY str‘); //SQLite built-in sorting
End;

Procedure TForm1.Button2Click(Sender: TObject);
Begin
  FDQuery1.Open(‘SELECT * FROM MyTable ORDER BY str COLLATE MyCollation‘); //FireDAC default sort
End;

Test: