Discussion :

[Zacheus]

Bonjour,

SQLite n'est pas fait pour le français... enfin ses collations. En Python, il y a une manière élégante de contourner ce problème. On peut aussi sans "toucher à rien", cloner les index de tris dans des champs supplémentaires sans accents.
Toujours est-il que le tri par défaut (le classique Épées) serait

Epais
Epees
Epreuve
Epées ! Le é après les autres lettres donc ici, après le r
SQLite
Épais ! Le É après le S
Épées

La majuscule accentuée étant en fin de liste après les lettres "anglaises" non accentuées.

Donc, j'ai lu la documentation, vérifié sur l'exemple. Il y a un moyen simple en effet.
Sauf que je ne pose rien sur ma Form, j'utilse la création dynamique des composants FireDac : Create(nil). J'ai bien regardé les codes de unit1.pas et unit1.dfm de C:\Users\Public\Documents\Embarcadero\Studio\15.0\Samples\Object Pascal\Database\FireDAC\Samples\DBMS Specific\SQLite\UserCollation\SQLite_UserColl.dpr, je n'arrive pas à obtenir le résultat escompté

J'ai bien utilsé comme précisé sur la doc :

SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase
CREATE TABLE IF NOT EXISTS test_col (f1 VARCHAR(10) COLLATE UTF16NoCase)

Je suppose que le fait que l'exemple soit en VCL alors que j'utilise FMX n'a pas d'incidence. Je suis même allé faire un tour du côté de la Chine, sans plus de succès. Si quelqu'un sait comment régler ce problème, je suis intéressé.

Petite précision : j'utilise des NCHAR et NVARCHAR (et non des CHAR et VARCHAR)
Merci. Zac.

[Paul TOTH]

tu crées bien le TFDSQLiteCollation ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
FDSQLiteCollation1.DriverLink := FDPhysSQLiteDriverLink1;
FDSQLiteCollation1.CollationName := 'UTF16NoCase';
FDSQLiteCollation1.Flags := [sfIgnoreCase];
FDSQLiteCollation1.Active := True;

[Zacheus]

Oui

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
function TfBDD.SQLiteConnexion(): TErrors;
begin
  Result := eNoErr;
  try
 
    SQLitePhysDriverLink  := TFDPhysSQLiteDriverLink.Create(nil);
    SQLiteCollation       := TFDSQLiteCollation.Create(nil);
 
    SQLiteconn        := TFDConnection.Create(nil);
    SQLitetrans       := TFDTransaction.Create(nil);
    SQLitequery       := TFDQuery.Create(nil);
 
    with SQLiteCollation do begin
//    LocaleName    := 'fr_FR';  ??
      CollationName := 'UTF16NoCase';
      DriverLink    := SQLitePhysDriverLink;
      CollationKind := scCompareString;
      Flags         := [sfIgnoreCase];
      Active        := True;
    end;
 
    with SQLiteconn do begin
      if Connected then Connected := False;
      DriverName  := 'SQLite';
      Transaction := SQLitetrans;
      with Params do begin
        Add ('Database = ' + gDataFile);
        Add ('DriverID = SQLite');
        Add ('OpenMode = CreateUTF8');
      end;
    end;
 
    with SQLitetrans do
      Connection := SQLiteconn;
    with SQLitequery do begin
      Connection   := SQLiteconn;
      Transaction  := SQLitetrans;
     end;
 
    with SQLiteconn do
      Connected := True;
 
  except
    Result := eConnErr;
  end;
 
end;

La requête de création : j'ai besoin d'un tri composite sur NOM et Prénom (accentués y compris majuscules) et un tri sur usABREV (également accentué). Les autres tris sont sans accents.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
csCREATEusers: string = 'CREATE TABLE IF NOT EXISTS [users] (' +
  '[usID] NCHAR(20) NOT NULL,' +
  '[usNOM] NVARCHAR(30) COLLATE UTF16NoCase,' +
  '[usPRENOM] NVARCHAR(30) COLLATE UTF16NoCase,' +
  '[usABREV] NVARCHAR(5) COLLATE UTF16NoCase,' +
  '[usDROITS] NCHAR(3),' +
  '[usLOGIN] NVARCHAR(30),' +
  '[usPASSWORD] NVARCHAR(30),' +
  '[xxCREAT] NCHAR(17),' +
  '[xxUSER] NVARCHAR(20),' +
  '[xxPOSTE] NVARCHAR(20),' +
  '[xxSTAMP] NCHAR(17),' +
  'CONSTRAINT [] PRIMARY KEY ([usID]));' +
'CREATE INDEX IF NOT EXISTS [kPRENOM] ON [users] ([usPRENOM]);' +
'CREATE UNIQUE INDEX IF NOT EXISTS [uNOM] ON [users] ([usNOM], [usPRENOM]);' +
'CREATE UNIQUE INDEX IF NOT EXISTS [uLOGIN] ON [users] ([usLOGIN], [usPASSWORD]);';

J'ai regardé plusieurs fois si je n'avais pas oublié de renseigner une propriété. Normalement si elle n'est pas "par défaut", elle devrait être signifiée dans le dfm de l'exemple. Je n'ai rien vu mais ce n'est pas significatif. On peut relire plusieurs fois la même erreur sans la voir.

Et évidemment j'ai effacé physiquement la base à chaque modification.

En précisant les COLLATE dans les requêtes, j'obtiens immanquablement une erreur lors de la création de la table. En ne le précisant pas, le tri n'est pas modifié. J'ai modifié également le CollationKind := scCompareString; en CollationKind := scCustomUTF16; et autres variantes mais sans aucun succès.
Ce dont j'aurais aimé disposer c'est des requêtes de création de la base. D'un autre côté la base ffdemo dispose des mêmes compile_options [ENABLE_FTS3], [ENABLE_FTS3_PARENTHESIS], [ENABLE_RTREE], [SOUNDEX] que la base que je crée avec FireDac. Avec certains langages, on recompile la librairie sqlite pour modifier notamment ses cultures (de tri). J'ai également un page_size 4096 contre une de 1024 mais sans conséquence, un encoding UTF-8 et collation_list [RTRIM], [NOCASE], [BINARY] identiques.

Bref, je suis dans le brouillard le plus complet faute de pouvoir orienter mon travail.

[Zacheus]

J'ai trouvé les requêtes de l'exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase');
 
//  FDQuery1.ExecSQL('CREATE TABLE IF NOT EXISTS test_col (f1 VARCHAR(10) COLLATE UTF16NoCase)');
//  FDQuery1.Open('SELECT * FROM test_col ORDER BY f1');
end;

Donc cela ne vient pas d'elles.
Je re-re-regarde le dfm.
Et pourtant,


Un uses manquant ? Cela m'est déjà arrivé avec FireDac... et à l'époque non signalé (sans message d'erreur à la compilation).
Bon je refais le projet complet en FMX en utilisant les mêmes composants que l'exemple VCL.

[Zacheus]

Suite du problème.

En reposant des composants sur la Form, en recréant ma propre base, tout fonctionne correctement :


Je reconstruis mes composants dynamiquement, impossible d'ajouter une nouvelle table contenant UTF16NoCase. C'est problématique car mes tables SQLite sont construites et modifiées dans un Thread à l'initialisation du projet

[Paul TOTH]

alors il y a une chose qui me surprend dans ton code, mais c'est peut-être valide puisque l'exemple le propose...

SQLite permet d'enregistrer des fonctions callback pour traiter les données, c'est à priori ce que fait TFDSQLiteCollation ...mais du coup je ne vois pas ce que le COLLATE a à faire dans la description de table, pour moi c'est quelque chose de dynamique puisque la fonction DOIT avoir été déclarée pour pouvoir être utilisé...or le CREATE TABLE va stocker une information permanente, ce que n'est pas le cas d'un SELECT.

https://www.sqlite.org/c3ref/create_collation.html

or donc, est-ce que spécifier le COLLATE uniquement dans le SELECT fonctionne ?

[Zacheus]

Bonjour Paul,

Voici la création :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
procedure TForm1.Button1Click(Sender: TObject);
begin
  FDQuery1.ExecSQL('CREATE TABLE IF NOT EXISTS test_col (f1 NVARCHAR(10) COLLATE UTF16NoCase, f2 NVARCHAR(10))'); 
  FDQuery1.ExecSQL('CREATE UNIQUE INDEX IF NOT EXISTS [uf1] ON [test_col] ([f1]);');
  FDQuery1.ExecSQL('CREATE UNIQUE INDEX IF NOT EXISTS [uf2] ON [test_col] ([f2]);');
 
  FDQuery1.ExecSQL('INSERT INTO test_col VALUES (''Epais'', ''Epais'')');
  FDQuery1.ExecSQL('INSERT INTO test_col VALUES (''Epees'', ''Epees'')');
  FDQuery1.ExecSQL('INSERT INTO test_col VALUES (''Epreuve'', ''Epreuve'')');
  FDQuery1.ExecSQL('INSERT INTO test_col VALUES (''Épées'', ''Épées'')');
  FDQuery1.ExecSQL('INSERT INTO test_col VALUES (''Epées'', ''Epées'')');
  FDQuery1.ExecSQL('INSERT INTO test_col VALUES (''SQLite'', ''SQLite'')');
  FDQuery1.ExecSQL('INSERT INTO test_col VALUES (''Épais'', ''Épais'')');
end;

RAS. La collation figure bien dans la configuration de la table (voir l'image ci-dessus). Il n'y a pas de problème avec les clefs uniques. C'était quand même à vérifier pour éviter toute mauvaise surprise.

Les tris :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
procedure TForm1.Button2Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT * FROM "test_col" ORDER BY f1 COLLATE UTF16NoCase');
end;

Résultat attendu. [Les accents sont "correctement" gérés]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
procedure TForm1.Button3Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT * FROM "test_col" ORDER BY f2');
end;

Résultat attendu. [Les accents sont repoussés après les lettres non accentuées]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
procedure TForm1.Button4Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT * FROM "test_col" ORDER BY f1');
end;

Comme button1 : [Les accents sont "correctement" gérés]. Donc le COLLATE dans le SELECT semble inutile (car déjà associé à ce champ dans le CREATE).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
procedure TForm1.Button5Click(Sender: TObject);
begin
  FDQuery1.Open('SELECT * FROM "test_col" ORDER BY f2 COLLATE UTF16NoCase');
end;

Comme button1 : [Les accents sont "correctement" gérés]. Donc le COLLATE dans le SELECT fonctionne même s'il n'a pas été précisé pour le champ dans le CREATE.

De mes essais, je dirais qu'il faut déclarer au moins un COLLATE UTF16NoCase dans le CREATE de la table.
Ensuite et sous réserve de cette condition,

• s'il est précisé lors du CREATE sur le champ, inutile de le préciser dans le ORDER BY du champ,
• s'il n'a pas été précisé dans le CREATE au niveau du champ, il faut le préciser dans le ORDER BY au niveau de ce même champ.

Ma position va être simple : si le comportement est identique sur OS X, je déclare le COLLATE dans le CREATE et je m'en passe dans le SELECT.

Ceci dit, cela ne résout pas mon problème ! Qu'est ce qu'il y a comme différence entre une création dynamique et une création sur la fiche ? Le propriétaire des composants, cela peut-il avoir un impact ? Et sinon ?

PS : si quelqu'un veut peaufiner le fonctionnement, mon projet de test sous FMX/FireDac est à dispo. Je n'irai pas plus loin à ce niveau (celui des requêtes) à moins que mon hypothèse d'écriture soit erronée. Je voyais le Défi Pascal/Delphi 2015-2016 : un outil pour le programmeur. A côté des outils, des études pourraient être primées (comme celle que j'ai récemment lue sur IntraWeb par exemple ). Je suppose que les programmeurs francophones utilisent des accents... et SQLite. Je suis le seul cancre de la classe ? Et comment cela marche sur les mobiles ? SQLite est souvent LE choix retenu.

[tourlourou]

Le COLLATE d'un SELECT prime sur celui du CREATE, en cas de besoin d'un comportement différent au SELECT. Remarque valable en général, sans référence à des composants d'accès particuliers.

[Zacheus]

Merci Toulourou pour la précision.

OK. C'était un problème d'activation du TFDSQLiteCollation (ligne 51).

Voici la bonne séquence dans le FormCreate():

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
uses
  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
  FMX.Types, FMX.Controls, FMX.Forms, FMX.Graphics, FMX.Dialogs, System.Rtti,
 
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
  FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async,
  FireDAC.Phys, FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteDef,
  FireDAC.Stan.ExprFuncs, FireDAC.FMXUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt.Intf, FireDAC.DApt,
 
  Data.Bind.EngExt, Fmx.Bind.DBEngExt,
  Fmx.Bind.Grid, System.Bindings.Outputs, Fmx.Bind.Editors,
  Data.Bind.Components, Data.Bind.Grid, Data.Bind.DBScope, Data.DB,
  FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.Comp.UI, FMX.Layouts,
  FMX.Grid, FMX.StdCtrls;
type
  TForm1 = class(TForm)
    Button1: TButton;
    Grid1: TGrid;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    BindSourceDB1: TBindSourceDB;
    FDConnection1: TFDConnection;
    FDQuery1: TFDQuery;
    BindSourceDB2: TBindSourceDB;
    BindingsList1: TBindingsList;
    LinkGridToDataSourceBindSourceDB2: TLinkGridToDataSource;
    Button2: TButton;
    Button3: TButton;
    procedure FormCreate(Sender: TObject);
  private
    { Déclarations privées }
  public
    { Déclarations publiques }
  end;
 
var
  Form1: TForm1;
  SQLitePhysDriverLink: TFDPhysSQLiteDriverLink;
  SQLiteCollation: TFDSQLiteCollation;
 
procedure TForm1.FormCreate(Sender: TObject);
begin
  with FDConnection1 do
    if Connected then Connected := False;
  {La connexion doit être fermée avant de définir la collation }
 
  SQLitePhysDriverLink:= TFDPhysSQLiteDriverLink.Create(nil);
  SQLiteCollation:= TFDSQLiteCollation.Create(nil);
 
  With SQLiteCollation do begin
    Active := False;    { Ouuverte par défaut !!! Voici le problème}
  {Directement récupérér du unit1.fmx :
    DriverLink = FDPhysSQLiteDriverLink1
    Active = True
    CollationName = 'UTF16NoCase'
    Flags = [sfIgnoreCase]}
  {Donc}
    DriverLink := SQLitePhysDriverLink;
    CollationName := 'UTF16NoCase';
    Flags := [sfIgnoreCase];
    Active := True;
  end;
 
{Ouverture de FDConnection après activation de SQLiteCollation }
  with FDConnection1 do begin
    Params.Add ('DriverID = SQLite');      {Inutile ici}
    Params.Add ('OpenMode = CreateUTF8');  {Idem}
    Connected := True;
  end;
end;