API : Accès à la base de données
1Présentation
Pour faire fonctionner notre API, nous allons avoir besoin de trois tables en base de données, une pour stocker les notes, une pour stocker les tags et une pour faire le lien entre les deux. Ces trois tables s'ajoutent à celles qui servent à stocker les utilisateurs et les clés d'authentification.
2Tables
Voici les requêtes de création des deux tables :
-- Table contenant les notes
CREATE TABLE Note (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date_creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
title TINYTEXT NOT NULL,
content TEXT NOT NULL,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX date_creation (date_creation),
INDEX date_update (date_update),
FULLTEXT title (title),
FOREIGN KEY (user_id) REFERENCES User (id) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- Table contenant les tags
CREATE TABLE Tag (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag TINYTEXT CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX tag (tag(255))
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- Table de liaison entre les notes et les tags
CREATE TABLE Note_Tag (
note_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
FOREIGN KEY (note_id) REFERENCES Note (id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES Tag (id) ON DELETE CASCADE,
UNIQUE INDEX note_id_tag_id (note_id, tag_id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3DAO
Les contrôleurs vont avoir besoin d'accéder aux données de la base. Cela se fera au travers d'un objet "DAO" (Data Access Object = objet d'accès aux données), qui sera un objet accédé via le copmosant d'injection de dépendances.
Voici le code enregistré dans le fichier lib/NoteDao.php :
<?php
/**
* DAO de gestion des notes et des tags.
* Objet chargeable avec le composant d'injection de dépendances.
*/
class NoteDao implements \Temma\Base\Loadable {
/** Accès à la base de données. */
private \Temma\Datasource\Sql $_db;
/**
* Constructeur.
* @param \Temma\Base\Loader $loader Composant d'injection de dépendances.
*/
public function __construct(\Temma\Base\Loader $loader) {
$this->_db = $loader->dataSources['db'];
}
/**
* Retourne la liste des tags d'un utilisateur.
* @param int $userId Identifiant de l'utilisateur.
* @return array Tableau associatif avec en clés les tags et en valeurs le nombre de notes associées.
*/
public function getTags(int $userId) : array {
$sql = "SELECT tag,
COUNT(note_id) AS nbrNotes
FROM Note
INNER JOIN Note_Tag ON (Note.id = Note_Tag.note_id)
INNER JOIN Tag ON (Note_Tag.tag_id = Tag.id)
WHERE Note.user_id = " . $this->_db->quote($userId) . "
GROUP BY Tag.id
ORDER BY tag";
$tags = $this->_db->queryAll($sql, 'tag', 'nbrNotes');
return ($tags);
}
/**
* Retourne une note à partir de son identifiant.
* @param int $noteId Identifiant de la note.
* @return array Tableau associatif.
*/
public function getNote(int $noteId) : array {
// récupération de la note
$sql = "SELECT id,
date_creation AS `creation`,
date_update AS `update`,
title,
content,
user_id AS userId
FROM Note
WHERE id = " . $this->_db->quote($noteId);
$note = $this->_db->queryOne($sql);
// récupération des tags
$sql = "SELECT tag
FROM Note_Tag
INNER JOIN Tag ON (Note_Tag.tag_id = Tag.id)
WHERE Note_Tag.note_id = " . $this->_db->quote($noteId);
$note['tags'] = $this->_db->queryAll($sql, null, 'tag');
return ($note);
}
/**
* Retourne la liste des notes d'un utilisateur, les plus récemment modifiées en premier.
* @param int $userId Identifiant de l'utilisateur.
* @return array Liste de tableaux associatifs.
*/
public function getNotes(int $userId) : array {
// récupération des notes
$sql = "SELECT id,
date_creation AS `creation`,
date_update AS `update`,
title
FROM Note
WHERE user_id = " . $this->_db->quote($userId) . "
ORDER BY date_update DESC";
$notes = $this->_db->queryAll($sql, 'id');
// récupération des tags
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Retourne une liste de notes à partir de critères de recherche.
* @param int $userId Identifiant de l'utilisateur.
* @param ?string $tag (optionnel) Tag à rechercher.
* @param ?string $title (optionnel) Chaîne de caractère à rechercher dans le titre.
* @return array Liste de tableaux associatifs.
*/
public function searchNotes(int $userId, ?string $tag=null, ?string $title) : array {
// recherche les notes
$sql = "SELECT id,
date_creation AS `creation`,
date_update AS `update`,
title
FROM ";
if ($tag)
$sql .= "Tag INNER JOIN Note ON (Tag.note_id = Note.id) ";
else
$sql .= "Note ";
$sql .= "WHERE user_id = " . $this->_db->quote($userId) . " ";
if ($tag)
$sql .= "AND tag = " . $this->_db->quote($tag);
if ($title)
$sql .= "AND title LIKE " . $this->_db->quote("%$title%");
$notes = $this->_db->queryAll($sql, 'id');
// récupération des tags
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Ajoute une nouvelle note.
* @param int $userId Identifiant de l'utilisateur.
* @param string $title Titre de la note.
* @param string $content Contenu HTML de la note.
* @param ?array $tags Liste des tags de la note.
* @return int Identifiant de la nouvelle note.
*/
public function create(int $userId, string $title, string $content, ?array $tags) : int {
// création de la note
$sql = "INSERT INTO Note
SET title = " . $this->_db->quote($title) . ",
content = " . $this->_db->quote($content) . ",
user_id = " . $this->_db->quote($userId);
$this->_db->exec($sql);
$noteId = $this->_db->lastInsertId();
if (!$tags)
return ($noteId);
// ajout des tags
$this->_addTagsToNote($noteId, $tags);
return ($noteId);
}
/**
* Modifie une note existante.
* @param int $noteId Identifiant de la note.
* @param ?string $title (optionnel) Nouveau titre de la note.
* @param ?string $content (optionnel) Nouveau contenu HTML de la note.
* @param ?array $tags (optionnel) Nouvelle liste de tags de la note.
*/
public function update(int $noteId, ?string $title=null, ?string $content=null, ?array $tags=null) : void {
if ($title || $content) {
$set = [];
if ($title)
$set[] = "title = " . $this->_db->quote($title);
if ($content)
$set[] = "content = " . $this->_db->quote($content);
$sql = "UPDATE Note
SET " . implode(', ', $set) . "
WHERE id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
}
if (!$tags)
return;
// effacement des anciens tags
$sql = "DELETE FROM Note_Tag
WHERE note_id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
// ajout des tags
$this->_addTagsToNote($noteId, $tags);
}
/**
* Efface une note.
* @param int $noteId Identifiant de la note.
*/
public function remove(int $noteId) : void {
$sql = "DELETE FROM Note
WHERE id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
}
/**
* Méthode privée qui enrichit des notes avec leurs tags.
* @param array $notes Liste de notes.
* @return array La liste enrichie.
*/
private function _fetchTags(array $notes) : array {
$sql = "SELECT tag,
note_id AS noteId
FROM Note_Tag
INNER JOIN Tag ON (Note_Tag.tag_id = Tag.id)
WHERE Note_Tag.note_id IN " . implode(', ', array_keys($notes));
$tags = $this->_db->queryAll($sql);
foreach ($tags as $tag) {
$notes[$tag['noteId']]['tags'] ??= [];
$notes[$tag['noteId']]['tags'][] = $tag['tag'];
}
return ($notes);
}
/**
* Méthode privée qui ajoute des tags à une note.
* @param int $noteId Identifiant de la note.
* @param array $tags Liste de tags.
*/
private function _addTagsToNote(int $noteId, array $tags) : void {
// échappement des caractères
$tags = array_map(function($t) {
return $this->_db->quote($t);
}, $tags);
// insertion des tags qui n'existent pas encore
$sql = "INSERT INTO Tag (tag)
VALUES (" . implode('), (', $tags) . ")
ON DUPLICATE KEY UPDATE id = id";
$this->_db->exec($sql);
// récupération des identifiants des tags
$sql = "SELECT id
FROM Tag
WHERE tag IN (" . implode(', ', $tags) . ")";
$tagIds = $this->_db->queryAll($sql);
// liens entre la note et les tags
$sql = "INSERT INTO Note_Tag (note_id, tag_id)
VALUES ('$noteId', " . implode("), ('$noteId', ", $tagIds) . ")
ON DUPLICATE KEY UPDATE note_id = note_id";
$this->_db->exec($sql);
}
}
- Ligne 7 : Création de l'objet NoteDao, qui implémente l'interface \Temma\Base\Loadable pour être utilisable à travers le composant d'injection de dépendances.
- Ligne 9 : Objet de connexion à la base de données.
- Lignes 15 à 17 : Constructeur, qui sert à récupérer l'objet de connexion à la base de données.
- Lignes 23 à 34 : Méthode qui retourne la liste des tags correspondant aux notes d'un utilisateur.
- Lignes 40 à 58 : Méthode qui retourne tous les données d'une note.
- Lignes 64 à 77 : Méthode qui retourne la liste des notes d'un utilisateur.
- Lignes 85 à 105 : Méthode qui recherche un liste de notes à partir de critères.
- Lignes 114 à 127 : Méthode qui sert à ajouter une nouvelle note en base de données.
- Lignes 135 à 155 : Méthode qui sert à mettre à jour une note existante.
- Lignes 160 à 164 : Méthode servant à effacer une note.
- Lignes 171 à 183 : Méthode privée utilisée pour enrichir une liste de notes avec les tags associés.
- Lignes 189 à 209 : Méthode privée utilisée pour créer des tags et les associer à une note.
Dans un contrôleur, cette DAO s'appelle via le composant d'injection de dépendance. Par exemple :
$note = $this->_loader->NoteDao->getNote($noteId);