Documentation

SQL

Présentation

Si vous avez correctement configuré les paramètres de connexion à la base de données, Temma crée automatiquement un objet de type \Temma\Base\Database. Par convention, nous partirons du principe que vous avez nommé cette connexion db dans le fichier temma.json (voir la documentation de la configuration).

La connexion est alors disponible dans le contrôleur en écrivant :

$this->db

Dans les autres objets gérés par le composant d'injection de dépendances, la connexion à la base de données est accessible en écrivant :

$loader->dataSources['db']

Méthodes de base

exec
exec(string $sql) : void

La méthode exec() sert à exécuter des requêtes pour lesquelles on n'attend pas de données en retour.
Exemples :

// effacement d'un article à partir de son identifiant
$this->db->exec("DELETE FROM article WHERE id = '12'");

// mise-à-jour de la date de dernière identification
// de tous les utilisateurs dont cette date n'est pas définie
$sql = "UPDATE user
        SET lastLogin = NOW()
        WHERE lastLogin IS NULL";
$this->db->exec($sql);

queryOne
queryOne(string $sql) : array

La méthode queryOne() sert à exécuter des requêtes pour lesquelles on veut récupérer une ligne de données. Les informations sont retournées sous la forme d'un tableau associatif, dont les clés correspondent aux noms des champs dans la table.

Par exemple, le code suivant :

$sql = "SELECT * FROM article WHERE id = '11'";
$data = $this->db->queryOne($sql);
print_r($data);

Donnera un résultat de ce genre :

Array
(
    [id] => 11
    [status] => valid
    [title] => Titre de l'article
    [creationDate] => 2021-04-04 23:17:39
    [content] => Texte de l'article ...
    [authorId] => 2
)

queryAll
queryAll(string $sql) : array

La méthode queryAll() sert à exécuter des requêtes qui retournent plusieurs lignes de données. On récupère les informations sous la forme d'une liste dont chaque élément est un tableau associatif représentant une ligne de données, dont les clés correspondent aux noms des champs dans la table.

Par exemple, le code suivant :

$sql = "SELECT * FROM article WHERE authorId = '2'";
$data = $this->db->queryAll($sql);
print_r($data);

Donnera un résultat de ce genre :

Array
(
    [0] => Array
        (
            [id] => 11
            [status] => valid
            [title] => Titre de l'article
            [creationDate] => 2011-04-04 23:17:39
            [content] => Texte de l'article ...
            [authorId] => 2
        )

    [1] => Array
        (
            [id] => 12
            [status] => valid
            [title] => Autre titre
            [creationDate] => 2011-04-05 20:34:20
            [content] => Texte différent ...
            [authorId] => 2
        )

    [2] => Array
        (
            [id] => 16
            [status] => waiting
            [title] => Troisième article
            [creationDate] => 2011-04-10 11:03:44
            [content] => Autre texte ...
            [authorId] => 2
        )
)

lastInsertId
lastInsertId(void) : int|string

La méthode lastInsertId() retourne la clé primaire du dernier enregistrement créé sur la connexion courante à la base de données.

Exemple d'utilisation :

// ajout d'un article en base de données
$sql = "INSERT INTO article
        SET title = 'Titre', content = 'Texte', creationDate = NOW()";
$this->db->exec($sql);

// récupération de l'identifiant du nouvel article
$id = $db->lastInsertId();

Échappement des caractères spéciaux

Quand on écrit des requêtes SQL, il faut faire attention aux risques d'injection SQL. Pour y faire face, il faut échapper les caractères spéciaux.

Pour cela, l'objet \Temma\Base\Database propose les méthodes quote() et quoteNull().

Ces deux méthodes prennent un texte en paramètre, et le retournent après avoir échappé chacun de ses caractères spéciaux. Elles ajoutent des apostrophes au début et à la fin du texte échappé.

La méthode quoteNull() retourne la chaîne NULL (sans apostrophes) si on lui a fourni une chaîne vide, un nombre égale à zéro ou un tableau vide.

Exemples :

$str = "Titre de l'article";
$sql = "INSERT INTO article SET text = " . $db->quote($str);
// INSERT INTO ARTICLE SET text = 'Titre de l\'article'

$str = '';
$sql = "INSERT INTO article SET text = " . $db->quote($str);
// INSERT INTO article SET text = ''

$str = '';
$sql = "INSERT INTO article SET text = " . $db->quoteNull($str);
// INSERT INTO article SET text = NULL

Requêtes préparées

L'objet \Temma\Base\Database permet de jouer des requêtes préparées à l'avance, en ne modifiant que les paramètres utilisés à chaque exécution.

Les paramètres peuvent être fournis sous forme de liste, dont les éléments viennent remplacer les points d'interrogation placés dans la requête :

// préparation de la requête
$sql = "DELETE FROM article WHERE status = ? AND title != ?";
$statement = $db->prepare($sql);
// première exécution
$statement->exec(['invalid', 'Celui-là est bon']);
// deuxième exécution
$statement->exec(['waiting', 'Celui-là aussi']);

Les paramètres peuvent être nommés dans la requête, et fournis sous forme de tableau associatif :

// préparation de la requête
$sql = "DELETE FROM article WHERE status = :status AND title != :title";
$statement = $db->prepare($sql);
// première exécution
$statement->exec([
    ':status' => 'invalid',
    ':title' => 'Celui-là est bon',
]);
// deuxième exécution
$statement->exec([
    ':status' => 'waiting',
    ':title' => 'Celui-là aussi',
]);

Dans les deux cas, les paramètres sont automatiquement échappés (cf. méthodes quote() et quoteNull()).

Il est aussi possible d'utiliser les requêtes préparées pour récupérer des données. Les méthodes queryOne() et queryAll() permettent de récupérer respectivement une ligne de données et toutes les lignes de données. La première retourne un tableau associatif dont les clés sont les noms de colonnes ; la seconde retourne une liste de tableaux associatifs.
Comme pour la méthode exec(), ces méthodes peuvent prendre une liste de paramètre, ou un tableau associatif de paramètres.

// préparation de la requête
$sql = "SELECT * FROM article WHERE id = ?";
$statement = $db->prepare($sql);
// première lecture
$article1 = $statement->queryOne([12]);
// deuxième lecture
$article2 = $statement->queryOne([23]);

// préparation de la requête
$sql = "SELECT * FROM article WHERE status = :status";
$statement = $db->prepare($sql);
// lecture
$articles = $statement->queryAll([':status' => 'invalid']);

Gestion des transactions

L'objet \Temma\Base\Database propose deux manières différentes pour gérer les transactions.

Soit en passant par une fonction anonyme, passée en paramètre à la méthode transaction(). Si une exception est levée dans la fonction anonyme, un rollback automatique est effectué ; sinon un commit est effectué automatiquement à la fin de l'exécution de la fonction anonyme.

// on crée la transaction
// la fonction anonyme reçoit la connexion
// à la base de données en paramètre
$this->db->transaction(function($db) use ($userId) {
    // on effectue diverses requêtes
    $db->exec(...);
    $db->exec(...);
    $db->exec(...);
});

Soit de manière explicite, en utilisant les méthodes startTransaction(), commit() et rollback() :

// on ouvre une transaction
$this->db->startTransaction();

// on effectue diverses requêtes
$this->db->exec(...);
$this->db->exec(...);
$this->db->exec(...);

// traitement conditionnel
if ($something) {
    // la transaction est acceptée
    $db->commit();
} else {
    // la transaction est annulée
    $db->rollback();
}
Précédent : Modèle
Suivant : Redis

Table des matières