Sources de données : SQL

1Pré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\Datasources\Sql. Par convention, nous partirons du principe que vous avez nommé cette connexion db dans le fichier etc/temma.php (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']

2Configuration

Dans le fichier etc/temma.php (voir la documentation de la configuration), vous déclarez le DSN (Data Source Name) qui permet de se connecter à la base de données.
La forme d'écriture du DSN dépend du type de base de données concerné. Pour les serveurs de bases de données classiques, il s'écrit : TYPE://LOGIN:MOT_DE_PASSE@SERVEUR[:PORT]/BASE

Cela concerne les types de bases suivants : mysql, mysqli, pgsql, cubrid, sybase, mssql, dblib, firebird, ibm, informix, sqlsrv, oci, odbc, 4D.

Pour les bases sqlite et sqlite2, le DSN est de la forme : sqlite:/chemin/vers/fichier.sq3

3Appels unifiés

Les bases de données SQL peuvent être utilisées comme les autres sources de données, pour stocker des paires clé-valeur, soit de manière brute (stockage de chaînes de caractères), soit en sérialisant des données complexes.

Pour cela, il faut que la connexion à la base de données permette d'accéder à une table nommée TemmaData dont voici la définition :

CREATE TABLE TemmaData (
    key    CHAR(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
    data   LONGTEXT,
    PRIMARY KEY (key)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3.1Accès de type tableau

// vérification de l'existence d'une donnée
if (isset($this->db['key1']))
    doSomething();

// lecture de données (désérialisées)
$data = $this->db['key1'];

// écriture de données (sérialisées)
$this->db['key1'] = $value;

// effacement de données
unset($this->db['key1']);

// nombre d'éléments
$nbr = count($this->db);

3.2Méthodes générales

// vérification de l'existence d'une donnée
if ($this->db->isSet('user:1'))
    doSomething();

// effacement de donnée
$this->db->remove('user:1');

// effacement de plusieurs données
$this->db->mRemove(['user:1', 'user:2', 'user:3']);

// effacement de données à patir d'un préfixe
$this->db->clear('user:');

// effacement de toutes les données
$this->db->flush();

3.3Gestion de données complexes sérialisées

// recherche de clés correspondant à un préfixe
$users = $this->db->search('user:');

// recherche de clés à partir d'un préfixe,
// avec récupération des données (désérialisées)
$users = $this->db->search('user:', true);

// lecture de données (désérialisées)
$user = $this->db->get('user:1');
// lecture de données avec valeur par défaut
$color = $this->db->get('color', 'blue');
// lecture de données avec création de données si nécessaire
$user = $this->db->get("user:$userId", function() use ($userId) {
    return $this->dao->get($userId);
});

// lecture de plusieurs données (désérialisées)
$users = $this->db->mGet(['user:1', 'user:2', 'user:3']);

// écriture de données (sérialisées)
$this->db->set('user:1', $userData);

// écriture de plusieurs données (sérialisées)
$this->db->mSet([
    'user:1' => $user1data,
    'user:2' => $user2data,
    'user:3' => $user3data,
]);

3.4Gestion de données brutes

// recherche de clés correspondant à un préfixe
$colors = $this->db->find('color:');

// recherche de clés à partir d'un préfixe,
// avec récupération des données (brutes)
$colors = $this->db->find('color:', true);

// lecture de données (brutes)
$html = $this->db->read('page:home');
// lecture de données avec valeur par défaut
$html = $this->db->read('page:home',
                        '<html><body><h1>Homepage</h1><body><html>');
// lecture de données avec création de données si nécessaire
$html = $this->db->read('page:home', function() {
    return file_get_contents('/path/to/homepage.html');
});

// lecture de plusieurs données (brutes)
$pages = $this->db->mRead(['page:home', 'page:admin', 'page:products']);

// copie d'une donnée dans un fichier local
$this->db->copyFrom('page:home', '/path/to/newpage.html');
// copie d'une donnée dans un fichier local, avec valeur par défaut
$this->db->copyFrom('page:home', '/path/to/newpage.html', $defaultHtml);
// copie d'une donnée dans un fichier local,
// avec création de données si nécessaire
$this->db->copyFrom('page:home', '/path/to/newpage.html', function() {
    return file_get_contents('/path/to/oldpage.html');
});

// écriture de donnée (brute)
$this->db->write('color:blue', '#0000ff');

// écriture de plusieurs données (brutes)
$this->db->mWrite({
    'color:blue'  => '#0000ff',
    'color:red'   => '#ff0000',
    'color:green' => '#00ff00',
]);

// écriture d'une donnée (brute) à partir d'un fichier local
$this->db->copyTo('page:home', '/path/to/homepage.html');

// écriture de plusieurs données (brutes) à partir de fichiers locaux
$this->db->mCopyTo([
    'page:home'     => '/path/to/homepage.html',
    'page:admin'    => '/path/to/admin.html',
    'page:products' => '/path/to/products.html',
]);

4Appels spécifiques

4.1exec()

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);

4.2queryOne()

queryOne(string $sql, [string $valueField]) : 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
)

Le second paramètre peut prendre le nom de l'un des champs. Dans ce cas, la méthode retournera directement la valeur de ce champ.

Exemple :

$sql = "SELECT COUNT(*) AS nbr FROM article";
$nbr = $this->db->queryOne($sql, 'nbr');

4.3queryAll()

queryAll(string $sql, [string $key], [string $valueField]) : 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.

Le premier paramètre contient la requête SQL à exécuter.

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

Le second paramètre ($key) contient le nom du champ à utiliser pour indexer les éléments dans la liste.

Par exemple, le code suivant :

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

Donnera un résultat de ce genre :

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

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

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

Le troisième paramètre ($valueField) peut prendre le nom de l'un des champs, qui sera utilisé pour fournir une seule valeur.

Par exemple, le code suivant :

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

Donnera un résultat de ce genre :

Array
(
    [11] => Titre de l'article
    [12] => Autre titre
    [16] => Troisième article
)

4.4lastInsertId()

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();

5É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\Datasources\Sql 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

6Requêtes préparées

L'objet \Temma\Datasources\Sql 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']);

7Gestion des transactions

L'objet \Temma\Datasources\Sql 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();
}