Différences

Ci-dessous, les différences entre deux révisions de la page.

--- basededonnees:general [08/12/2014 16:28]
127.0.0.1 modification externe
+++ basededonnees:general [04/01/2016 15:16] (Version actuelle)
julp [Peut-on rendre une partie d'une requête conditionnelle ?]
@@ Ligne 71: / Ligne 71: @@
 ====== Gestion de la casse ======
-Voilà un point qui est souvent négligé et qui devrait pourtant être pris en compte à la création de sa table. En effet, cette tâche incombe au SGBD et permet de manière transparente d'établir si deux données sont égales même si la casse diffère. Autant ça n'a aucun sens pour un mot de passe mais pour une adresse email c'est tout de suite plus critique (un utilisateur en mettrait à son inscription mais ne les reprendrait pas lors de son identification ou pour récupérer son mot de passe = vous ne le reverrez plus).
+Déplacé sur [[http://www.julp.fr/blog/posts/4-sql-gestion-de-la-casse]]
-On ne va pas s'amuser à mettre systématiquement des appels à la fonction SQL ''%%LOWER%%'' à tour de bras (''%%SELECT * FROM utilisateurs WHERE LOWER(email) = LOWER('monAdresseEmail')%%'') :
-  * à moins d'un index d'expression (type : ''%%CREATE INDEX ON utilisateurs (LOWER(email));%%''), ce sera moins performant (réappliquer LOWER à chaque ligne concernée à chaque requête)
-  * le moindre LOWER omis peut être catastrophique (les correspondances attendues n'étant plus établies quand la casse diffère : ''%%SELECT * FROM utilisateurs WHERE LOWER(email) = 'monAdresseEmail' /* au lieu de monadressemail */;%%''
-===== MySQL =====
-MySQL est certainement le plus commode sur la question : l'interclassement accolé à toute colonne de type texte %%((var)char + *text)%% détermine comment (par défaut) deux textes sont comparés. Tous les interclassements suffixés par *_ci* sont insensibles à la casse (ci, vous l'aurez compris, signifiant : **c**ase **i**nsensitive) contrairement à ceux en *_bin* (pour **bin**ary, comparaisons telles quelles).
-Exemple :
-<code>mysql> SELECT 'a' COLLATE utf8_bin = 'A';
-+----------------------------+
-| 'a' COLLATE utf8_bin = 'A' |
-+----------------------------+
-|                          0 |
-+----------------------------+
-mysql> SELECT 'a' COLLATE utf8_general_ci = 'A';
-+-----------------------------------+
-| 'a' COLLATE utf8_general_ci = 'A' |
-+-----------------------------------+
-|                                 1 |
-+-----------------------------------+</code>
-Vous n'attribuez bien qu'un interclassement par défaut à votre colonne, comme montré ci-dessus, il peut être redéfini temporairement à tout moment lorsque, pour un endroit d'une requête, vous avez des besoins contraires.
-<code sql>CREATE TABLE utilisateurs(
-    id INT NOT NULL AUTO_INCREMENT,
-    login VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
-    PRIMARY KEY(id),
-    UNIQUE KEY(login)
-);
-INSERT INTO utilisateurs(login) VALUES('j.chirac');
-INSERT INTO utilisateurs(login) VALUES('s.sarkozy');
-INSERT INTO utilisateurs(login) VALUES('f.hollande');
-SELECT * FROM utilisateurs WHERE login LIKE '%O%'; -- rien
-SELECT * FROM utilisateurs WHERE login COLLATE utf8_general_ci LIKE '%O%'; -- 2 lignes : sarkozy et hollande
--- on change l'interclassement de login de binaire vers insensible à la casse
-ALTER TABLE utilisateurs MODIFY login VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
-SELECT * FROM utilisateurs WHERE login LIKE '%O%'; -- 2 lignes : sarkozy et hollande</code>
-===== PostgreSQL =====
-Pour PostgreSQL, c'est plus compliqué : disons que rien n'existe réellement à la base.
-D'origine, vous avez quelques opérateurs comme ILIKE qui sont insensibles à la casse. Sinon il est nécessaire, comme évoqué initialement, de tout transformer en une casse prédéterminée lors de vos requêtes (LOWER).
-Toutefois, si vous avez la possibilité d'installer des modules complémentaires, envisagez citext.
-Installer tout d'abord l'extension : ''%%CREATE EXTENSION citext;%%''
-<code sql>CREATE TABLE utilisateurs(
-    id SERIAL PRIMARY KEY NOT NULL,
-    login VARCHAR(80) NOT NULL
-);
-CREATE UNIQUE INDEX ON utilisateurs(LOWER(login));
-INSERT INTO utilisateurs(login) VALUES('j.chirac');
-INSERT INTO utilisateurs(login) VALUES('s.sarkozy');
-INSERT INTO utilisateurs(login) VALUES('f.hollande');
-SELECT * FROM utilisateurs WHERE login LIKE '%O%'; -- rien
-SELECT * FROM utilisateurs WHERE login ILIKE '%O%'; -- 2 lignes : sarkozy et hollande
--- on change le type de login de varchar en citext
-ALTER TABLE utilisateurs ALTER COLUMN login TYPE CITEXT;
-DROP INDEX utilisateurs_lower_idx;
-CREATE UNIQUE INDEX ON utilisateurs(login);
-SELECT * FROM utilisateurs WHERE login LIKE '%O%'; -- 2 lignes : sarkozy et hollande</code>
-Et si vous avez de nouveau besoin de comparer de manière sensible à la casse avec du citext, procédez à une cast en text :
-<code sql>SELECT * FROM utilisateurs WHERE login::text LIKE '%O%'; -- rien</code>
-NOTE : pour mes exemples, j'ai utilisé LIKE par commodité mais tout opérateur fonctionnera de la même façon
 ====== Formater la date en Français ======
@@ Ligne 170: / Ligne 89: @@
 ====== Peut-on rendre une partie d'une requête conditionnelle ? ======
-Voilà une question souvent posée par rapport aux requêtes préparées, bien que le fait qu'elles soient ou non préparées n'ait aucune importance au final. Bref, la réponse est oui et non : il est possible de "bypasser" (ignorer) une partie de la clause WHERE en attribuant une valeur particulière, comme NULL notamment, au "paramètre" à ignorer mais il faut réécrire la partie de la clause WHERE qui cherche à utiliser ce "paramètre" par une double condition unie par un OU logique. Je m'explique avec un exemple : imaginons au départ que j'ai une requête qui a pour but de me retourner les lignes d'une table où ses colonne A et B ont (chacune) une certaine valeur. Ce qui donne, classiquement : ''%%WHERE A = :a AND B = :b%%'' (j'utilise la notation d'une requête préparée PDO).
+Voilà une question souvent posée par rapport aux requêtes préparées, bien que le fait qu'elles soient ou non préparées n'ait aucune importance au final. Bref, la réponse est oui et non : il est possible de "bypasser" (ignorer) une partie de la clause WHERE en attribuant une valeur particulière, comme NULL notamment, au "paramètre" à ignorer mais il faut réécrire la partie de la clause WHERE qui cherche à utiliser ce "paramètre" par une double condition unie par un OU logique. Je m'explique avec un exemple : imaginons au départ que j'ai une requête qui a pour but de me retourner les lignes d'une table où ses colonne A et B ont (chacune) une certaine valeur. Ce qui donne, classiquement : ''%%WHERE A = :a AND B = :b%%'' (j'utilise la notation d'une requête préparée PDO avec des paramètres nommés).
-Comment rendre, à présent, b (le paramètre) facultatif ? On va admettre que B (la colonne) ne peut être NULL, ce qui nous permet d'utiliser cette valeur particulière quand nous voudrons ignorer ce "paramètre" (avec PDO, penser à un bindValue/bindParam avec PDO::PARAM_NULL en 3e argument). La requête devient dès lors : ''%%WHERE A = :a AND ((:b IS NULL) OR (B = :b))%%'' et le tour est joué.
+Comment rendre, à présent, b (le paramètre) facultatif ? On va admettre que B (la colonne) ne peut être NULL, ce qui nous permet d'utiliser cette valeur particulière quand nous voudrons ignorer ce "paramètre" (avec PDO, penser à un bindValue/bindParam avec PDO::PARAM_NULL en 3e argument ou simplement assigner NULL en valeur du paramètre avec execute). La requête devient dès lors : ''%%WHERE A = :a AND ((:b IS NULL) OR (B = :b))%%'' et le tour est joué.
+<code php>
+$bdd = new PDO(/* ... */);
+$stmt = $bdd->prepare('SELECT * FROM table WHERE A = :a AND ((:b IS NULL) OR (B = :b))');
+$stmt->bindValue('a', 3, PDO::PARAM_INT);
+if (!empty($_POST['query'])) {
+    $stmt->bindValue('b', $_POST['query'], PDO::PARAM_STR);
+} else {
+    $stmt->bindValue('b', NULL, PDO::PARAM_NULL);
+}
+$stmt->execute();
+foreach ($stmt as $row) {
+    // afficher $row
+}
+</code>
 Notez que je n'ai pas testé cette "technique" sur chaque SGBD mais étant donné qu'ils cherchent tous à être le plus efficace possible, les opérateurs logiques ET/OU sont, selon toute vraisemblance, dits court-circuit partout (au sens où OU n'évalue pas son second opérande si le premier est vrai et, idem pour ET, quand le premier est faux).
 Cependant, n'utilisez pas cette approche pour créer un moteur de recherche. Cela deviendrait vite inutilement une usine à gaz. A mon sens, elle n'a d'intérêt que pour de vraies requête préparées (au sens préparée une fois pour être exécutée **plus** d'une fois) ou ce qui s'en rapproche, notamment côté SGBD pour les langages de procédures/triggers & co où ça me paraît simplifier les choses (curseurs).
+Concernant PDO : une requête préparée ne peut comporter plusieurs un même paramètre nommé que si les requêtes préparées sont émulées par PDO. Dans le cas contraire, il sera nécessaire de binder la même valeur sous deux noms différents (par exemple, notre clause WHERE précédente devra être réécrite en ''%%WHERE A = :a AND ((:b1 IS NULL) OR (B = :b2))%%'' et en affectant la valeur de notre marqueur précédemment :b à :b1 **et** :b2)
+<code php>
+$binds = array('a' => 3);
+$binds['b1'] = $binds['b2'] = NULL;
+$bdd = new PDO(/* ... */);
+$stmt = $bdd->prepare('SELECT * FROM table WHERE A = :a AND ((:b1 IS NULL) OR (B = :b2))');
+if (!empty($_POST['query'])) {
+    $binds['b1'] = $binds['b2'] = $_POST['query'];
+}
+$stmt->execute($binds);
+foreach ($stmt as $row) {
+    // afficher $row
+}
+</code>
+Bien qu'illustré par l'intermédiaire de PDO, le principe reste valable avec toute autre API (mysqli, sqlite3, etc).

julp

Outils pour utilisateurs

Outils du site

Différences

Outils de la page