SALT > Manuel > Tutoriel > Base de données >

Le framework SALT dispose de deux APIs pour exécuter des requêtes sur les objets mappés :

• L'API SQL qui consiste à écrire le texte des requêtes SQL et utiliser la méthode DBHelper::execSQL pour l'exécuter (conseillé et disponible depuis la version 2+)
  Avantages : Pas d'apprentissage, utilisation directe de SQL. Meilleure lisibilité.
  Inconvénients : Pas de mapping automatique, c'est au développeur de gérer les conversions de dates et de booléens.
  
• L'API Query (SELECT, INSERT, UPDATE, DELETE) et les méthodes DBHelper::execQuery/execInsert/execUpdate/execDelete
  Avantages : Mapping automatique des dates et des booléens. Contrôles renforcés lors de l'écriture de la requête.
  Inconvénients : Cette API demande un apprentissage, les requêtes devant être "construites". Les requêtes sont plus difficiles à maintenir car moins lisibles.
  Cette API est historique, mais elle va à l'encontre de la philosophie du framework SALT :
  • Elle nécessite l'apprentissage d'un mini langage
  • Elle produit des requêtes qui sont moins "simples" à comprendre que du texte SQL

Dans le cas où le framework ne permettrait pas de construire une requête complexe avec l'API Query, il est conseillé de ne pas essayer de faire une requête "hybride" avec les 2 API, mais d'utiliser uniquement l'API SQL.

Présentation

L'API SQL consiste a écrire directement le texte d'une requête SQL, avec juste quelques améliorations :

• Le remplacement des noms des tables
• Le remplacement des variables
• Le mapping vers un objet après l'exécution de la requête

Pour exécuter une requête de l'API SQL, on utilisera toujours la méthode DBHelper::execSQL()

Nom des tables

Il est possible d'indiquer le nom d'un objet entre crochet dans le texte de la requête. Il sera remplacé par le nom de la table de l'objet correspondant <?php
$q = 'SELECT * FROM [City] t WHERE t.id > 0';
$res = $db->execSQL($q); // will execute "SELECT * FROM city t WHERE t.id > 0" Si l'objet entre crochet n'est pas trouvé le texte ne sera remplacé.
On peut donc écrire des crochets dans le texte de la requête si son contenu n'est pas un nom de classe.

Si la classe appartient à un namespace, il faut également indiquer le namespace : SELECT * FROM [namespace\Class]

Variables

Les variables doivent être nommées et commencer par "deux points" (:) dans la requête.
Les valeurs seront passées dans un tableau lors de l'exécution de la requête. <?php
$q = 'SELECT * FROM [City] WHERE id > :id';
$res = $db->execSQL($q, array('id' => 42)); // will execute "SELECT * FROM city WHERE id > 42" Si une valeur apparait plusieurs fois, il n'est pas nécessaire de lui donner des noms différents : <?php
$q = 'SELECT * FROM [City] WHERE country = :text AND city_name = :text';
$res = $db->execSQL($q, array('text' => 'Mexico')); // will execute "SELECT * FROM city WHERE country = 'Mexico' AND city_name = 'Mexico' Le nom des variables doit rester simple et ne contenir que des caractères alphanumériques ou underscore : A-Z, a-z, 0-9, _ Par exemple : dans City, le champ date_mise_a_jour est au format DATETIME en base, mais dans le framework il est au format TIMESTAMP. Si on exécute cette requête : <?php
$q = 'SELECT * FROM [City] WHERE date_mise_a_jour > :date';
$res = $db->execSQL($q, array('date' => time()));
echo $res->rowCount(); // return all lines... not expected ! Elle "fonctionnera" mais elle sera fausse : on va comparer une date au format 'YYYY-MM-DD HH:mm:ss' avec un timestamp unix.
Il faudra donc écrire à la place : <?php
// convert timestamp => datetime in MySQL
$q = 'SELECT * FROM [City] WHERE date_mise_a_jour > FROM_UNIXTIME(:date)';
$res = $db->execSQL($q, array('date' => time()));
echo $res->rowCount(); // return 0 lines : ok.

// or convert timestamp => datetime in PHP
$q = 'SELECT * FROM [City] WHERE date_mise_a_jour > :date';
$res = $db->execSQL($q, array('date' => date('Y-m-d H:i:s', time())));
echo $res->rowCount(); // return 0 lines : ok. Si la donnée provient d'un objet mappé, il est possible de convertir le timestamp dans le format attendu directement avec la méthode de conversion SQL : <?php
$city = $db->execSQL('SELECT * FROM [City] LIMIT 1')->getAs(City::singleton());
// will execute ... date_mise_a_jour = 'YYYY-MM-DD HH:mm:ss'
$res = $db->execSQL('SELECT * FROM [City] WHERE date_mise_a_jour = :date', array('date' => $city->SQL->date_mise_a_jour));
echo $res->rowCount(); // return 1 line : ok.

Tableau

Si une variable est un tableau, elle sera automatiquement entourée de parenthèses et linéarisée, ce qui permet d'écrire facilement des clauses colonne IN (valeur1, valeur2, ...) <?php
$q = 'SELECT * FROM [City] WHERE id IN :ids';
$params = array('ids' => array(1, 2, 3));
$res = $db->execSQL($q, $params); // will execute "SELECT * FROM city WHERE id IN (1, 2, 3)" Les tuples sont également supportés, il suffit d'avoir un tableau à 2 dimensions : <?php
$q = 'SELECT * FROM [City] WHERE (id, country) IN :tuples';
$params = array('tuples' => array(
    array(1, 'USA'),
    array(2, 'France'),
));
$res = $db->execSQL($q, $params); // will execute "SELECT * FROM city WHERE (id, country) IN ((1, 'USA'), (2, 'France'))"

INSERT

Lors de l'insertion de plusieurs valeurs, la requête doit ressembler à INSERT INTO table(col1, col2, ...) VALUES (a1, a2, ...), (b1, b2, ...) Les valeurs peuvent provenir d'un tableau, comme pour des tuples, mais pour éviter que des parenthèses soient ajoutées autour de (a1, a2, ...), (b1, b2, ...), on préfixera la clé dans le tableau par arobase (@) <?php
$q = 'INSERT INTO [City](city_name, country) VALUES :values';
$params = array('@values' => array( // use @ for avoid extra parenthesis
    array('Washington', 'USA'),
    array('Genève', 'Suisse'),
));
$res = $db->execSQL($q, $params); // will execute "INSERT INTO city(city_name, country) VALUES ('Washington', 'USA'), ('Genève', 'Suisse')"
// If we don't use @, the query will be incorrect : "INSERT INTO city(city_name, country) VALUES (('Washington', 'USA'), ('Genève', 'Suisse'))"

Type des variables

Si la valeur est NULL le type PDO::PARAM_NULL sera utilisé.
Si la valeur est une valeur numérique le type PDO::PARAM_INT sera utilisé.
Dans tout les autres cas le type PDO::PARAM_STR sera utilisé.

Si l'on souhaite forcer l'utilisation d'un autre type, on peut utiliser l'une de ces méthodes :

• Définir une valeur comme un tableau array('value' => ..., 'type' => FieldType::...) <?php
$q = 'SELECT * FROM [City] WHERE id > :id';
$res = $db->execSQL($q, array('id' => array('value' => '42', 'type' => FieldType::NUMBER))); // will execute "SELECT * FROM city WHERE id > 42" and not "> '42'"
• Ajouter le type PDO dans la clé : key@type <?php
$q = 'SELECT * FROM [City] WHERE id > :id';
$res = $db->execSQL($q, array('id@'.PDO::PARAM_INT => '42')); // will execute "SELECT * FROM city WHERE id > 42" and not "> '42'"

Mapping objet

Une fois la requête exécutée, l'objet retourné est SqlStatement qui est une classe fille de PDOStatement. Il est donc possible d'utiliser toutes les méthodes PDOStatement::* pour récupérer les résultats, mais on peut aussi utiliser les méthodes suivantes :

• getAs(Base $object) Renvoi le prochain résultat en le mappant sur le type $object. Renvoi NULL s'il n'y a plus de résultats.
• getAllAs(Base $object, $indexedBy = NULL) Renvoi la liste des résultats en les mappant sur le type $object. Renvoi un tableau vide s'il n'y a pas de résultats.
  Si la colonne $indexedBy est fournie, la liste sera indexée par la valeur de la colonne (si plusieurs résultats ont la même valeur pour la colonne d'indexation, seule la dernière sera conservée)

<?php
$q = 'SELECT * FROM [City]';
$res = $db->execSQL($q);
$cities = $res->getAllAs(City::singleton(), 'id'); Exemple complet avec affichage : <?php
$res = $db->execSQL('SELECT * FROM [City]');
$cities = $res->getAllAs(City::singleton());
?>
<table>
    <tr>
        <?php foreach($res->getColumns() as $col) { ?>
        <th><?= City::COLUMN()->$col ?></th>
        <?php } ?>
    </tr>
    <?php foreach($cities as $row) { ?>
    <tr>
        <?php foreach($res->getColumns() as $col) { ?>
        <td>
            <?= $row->VIEW->$col ?>
        </td>
        <?php } ?>
    </tr>
    <?php } ?>
</table>

Résultat simple

Pour récupérer une valeur unique, on peut utiliser l'API de PDO : PDOStatement::fetchColumn() <?php
$res = $db->execSQL('SELECT count(*) FROM [City]');
var_dump($res->fetchColumn()); // display the number of lines

Vérifier le nombre de lignes modifiées

La méthode execSQL prend un 3ème paramètre qui est le nombre de lignes qui doivent être modifiées.
Si le paramètre est fourni et que le nombre de lignes modifiées n'est celui attendu, une exception sera levée : <?php
$q = 'UPDATE [City]
SET date_mise_a_jour = date_mise_a_jour
WHERE 1 != 2
';
$db->execSQL($q, array(), 1); // the query do not change any row, so an exception will be thrown Cela ne concerne que les requêtes de type UPDATE, INSERT, DELETE. Le 3ème paramètre n'a pas de sens pour un SELECT.

Pagination

Pour mettre en place une pagination il faut gérer manuellement le comptage des résultats.

En général on commence par construire la requête, avec ses conditions, mais sans la clause LIMIT.
Puis on compte le nombre de résultats en créant une 2ème requête de comptage à l'aide de la première.
Puis enfin on ajoute la clause LIMIT sur la 1ère requête. <?php
$offset = 10; // from user

$params = array();
$q = 'SELECT * FROM [City]';
// add WHERE clauses

// count results :
$nb = $db->execSQL('SELECT COUNT(*) FROM ('.$q.') c')->fetchColumn();

if ($offset > $nb) { // ajust offset if outbound
    $offset = 0;
}
// add LIMIT clause
$q.=' LIMIT :offset, :size';
$params['offset'] = $offset;
$params['size'] = 100;

// execute the limited query
$res = $db->execSQL($q, $params);