julp

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Obtenir les dépendances entre tables (clés étrangères) ======

===== SQLite =====

<code sql>PRAGMA foreign_key_list(nomTable);</code>
Le résultat se composera d'une ligne de la forme ci-dessous **par** clé étrangère :
<code><id>|<seq>|<table référencée>|<champ correspondant à la clé étrangère [dans la table référencée]>|<le nom de la colonne en clé étrangère dans nomTable>|<action de modification : cascade, restrict, ...>|<action de suppression : cascade, restrict, ...>|<match></code>

===== MySQL =====

<code sql>SELECT DISTINCT t.table_name, kcu.referenced_table_name, kcu.referenced_column_name
    FROM information_schema.tables t
    JOIN information_schema.table_constraints tc USING (table_schema, table_name)
    JOIN information_schema.referential_constraints rc ON tc.table_schema = rc.constraint_schema AND tc.table_name = rc.table_name
    JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = rc.constraint_name
        WHERE t.table_schema = 'nomBaseDeDonnees' AND t.table_name = 'nomTable'
            AND tc.constraint_type = 'FOREIGN KEY'</code>

===== PostgreSQL =====

<code sql>SELECT owner.relname
    FROM pg_constraint
        JOIN pg_class owner
            ON pg_constraint.confrelid = owner.oid
        JOIN pg_class referenced
            ON pg_constraint.conrelid = referenced.oid
    WHERE pg_constraint.CONTYPE = 'f' -- <=> pg_constraint.confrelid <> 0
        AND referenced.relname = 'nomTable'
;</code>
Pour quelque chose de plus "précis" :
<code sql>SELECT
    /*tc.constraint_name, tc.table_name, kcu.column_name, */
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='NomTable' AND tc.table_catalog = 'NomBaseDeDonnées';</code>
====== La table X existe-t-elle ? ======

===== MySQL =====

<code sql>SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' AND table_name = 'table_name'</code>

Renvoie : 0 = la table n'existe ; 1 = la table existe
Pour faire la rechercher sur la base courante utiliser la fonction DATABASE() (ou son alias SCHEMA) (ie changer ''%%WHERE table_schema = DATABASE()%%'')

====== La base de données X existe-t-elle ? ======

===== MySQL =====

<code php>$stmt = $bdd->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?');
$stmt->execute(array('foo'));
if ($stmt->fetchColumn() == 0) {
    // la bdd foo n'existe pas
} else {
    // la bdd foo existe
}</code>

====== "Synchroniser" deux tables ======

Par là, j'entends insérer dans une table B, les nouvelles lignes qui sont +/- en relation avec une table A. On ne s'occupe pas des mises à jour (UPDATE) ou suppression (DELETE) opérées sur la table de référence A. Cette "procédure" est utile quand on ne souhaite pas réaliser cette tâche de synchronisation en temps réel (via trigger notamment) car elle serait plus coûteuse à réaliser au fur et à mesure et non en un bloc (insertions multiples dans la table A comme lors d'un import massif).

Le but est d'écarter toutes les lignes qui existent déjà, reconnaissable à la valeur de la clé primaire de la table A : tout repose sur l'exclusion des lignes existantes via l'"opérateur" NOT EXISTS :
<code sql>INSERT INTO B(pk_A, col1, col2, col3)
    SELECT pk, col2, 0.75 * col1, UPPER(col3) /* exemple */ FROM A
        WHERE NOT EXISTS(SELECT NULL FROM B WHERE A.pk = B.pk_A)</code>
Il est aussi possible d'utiliser un LEFT JOIN/IS NULL ou NOT IN, la solution la plus efficace dépendant du SGBD.

====== Gestion de la casse ======

Déplacé sur [[http://www.julp.fr/blog/posts/4-sql-gestion-de-la-casse]]

====== Formater la date en Français ======

(remplacer ci-dessous NOW() par votre DATE/DATETIME)

===== MySQL =====

<code sql>SET  lc_time_names = 'fr_FR';
SELECT DATE_FORMAT(NOW(), '%W %M %Y');</code>

===== PostgreSQL =====

<code sql>SET lc_time='fr_FR';
SELECT TO_CHAR(NOW(), 'TMDay TMMonth YYYY');</code>

====== 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 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 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

Outils de la page
