API: Acceso a la base de datos
1Introducción
Para que nuestra API funcione, vamos a necesitar tres tablas en la base de datos: una para almacenar las notas, una para almacenar las etiquetas y otra para vincular ambas. Estas tres tablas se suman a las usadas para almacenar los usuarios y las claves de autenticación.
2Tablas
Aquí están las consultas para crear las dos tablas:
-- Tabla que contiene las notas
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;
-- Tabla que contiene las etiquetas
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;
-- Tabla de vínculo entre notas y etiquetas
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
Los controladores necesitarán acceder a los datos de la base de datos. Esto se hará mediante una "DAO" (Data Access Object), que será un objeto al que se accede a través del componente de inyección de dependencias.
Aquí está el código guardado en el archivo lib/NoteDao.php:
<?php
/**
* DAO para gestionar notas y etiquetas.
* Objeto cargable con el componente de inyección de dependencias.
*/
class NoteDao implements \Temma\Base\Loadable {
/** Acceso a la base de datos. */
private \Temma\Datasources\Sql $_db;
/**
* Constructor.
* @param \Temma\Base\Loader $loader Componente de inyección de dependencias.
*/
public function __construct(\Temma\Base\Loader $loader) {
$this->_db = $loader->dataSources->db;
}
/**
* Devuelve la lista de etiquetas de un usuario.
* @param int $userId Identificador del usuario.
* @return array Array asociativo con las etiquetas como claves y el número de notas asociadas como valores.
*/
public function getTags(int $userId) : array {
$sql = "SELECT tag,
COUNT(note_id) AS noteCount
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', 'noteCount');
return ($tags);
}
/**
* Devuelve una nota a partir de su identificador.
* @param int $noteId Identificador de la nota.
* @return array Array asociativo.
*/
public function getNote(int $noteId) : array {
// recuperación de la nota
$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);
// recuperación de las etiquetas
$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);
}
/**
* Devuelve una lista de notas de un usuario, con las modificadas más recientemente primero.
* @param int $userId Identificador del usuario.
* @return array Lista de arrays asociativos.
*/
public function getNotes(int $userId) : array {
// recuperación de las notas
$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');
// recuperación de las etiquetas
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Devuelve una lista de notas basada en criterios de búsqueda.
* @param int $userId Identificador del usuario.
* @param ?string $tag (opcional) Etiqueta a buscar.
* @param ?string $title (opcional) Cadena a buscar en el título.
* @return array Lista de arrays asociativos.
*/
public function searchNotes(int $userId, ?string $tag=null, ?string $title=null) : array {
// búsqueda de notas
$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');
// recuperación de las etiquetas
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Añade una nueva nota.
* @param int $userId Identificador del usuario.
* @param string $title Título de la nota.
* @param string $content Contenido HTML de la nota.
* @param ?array $tags Lista de etiquetas de la nota.
* @return int Identificador de la nueva nota.
*/
public function create(int $userId, string $title, string $content, ?array $tags) : int {
// creación de la nota
$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);
// añade las etiquetas
$this->_addTagsToNote($noteId, $tags);
return ($noteId);
}
/**
* Modifica una nota existente.
* @param int $noteId Identificador de la nota.
* @param ?string $title (opcional) Nuevo título de la nota.
* @param ?string $content (opcional) Nuevo contenido HTML de la nota.
* @param ?array $tags (opcional) Nueva lista de etiquetas de la nota.
*/
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;
// eliminación de las etiquetas antiguas
$sql = "DELETE FROM Note_Tag
WHERE note_id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
// añade las etiquetas
$this->_addTagsToNote($noteId, $tags);
}
/**
* Elimina una nota.
* @param int $noteId Identificador de la nota.
*/
public function remove(int $noteId) : void {
$sql = "DELETE FROM Note
WHERE id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
}
/**
* Método privado que enriquece las notas con sus etiquetas.
* @param array $notes Lista de notas.
* @return array La lista enriquecida.
*/
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étodo privado que añade etiquetas a una nota.
* @param int $noteId Identificador de la nota.
* @param array $tags Lista de etiquetas.
*/
private function _addTagsToNote(int $noteId, array $tags) : void {
// escapado de caracteres
$tags = array_map(function($t) {
return $this->_db->quote($t);
}, $tags);
// inserta las etiquetas que aún no existen
$sql = "INSERT INTO Tag (tag)
VALUES (" . implode('), (', $tags) . ")
ON DUPLICATE KEY UPDATE id = id";
$this->_db->exec($sql);
// recuperación de los identificadores de las etiquetas
$sql = "SELECT id
FROM Tag
WHERE tag IN (" . implode(', ', $tags) . ")";
$tagIds = $this->_db->queryAll($sql, null, 'id');
// vínculos entre la nota y las etiquetas
$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);
}
}
- Línea 7: Creación del objeto NoteDao, que implementa la interfaz \Temma\Base\Loadable para poder usarse a través del componente de inyección de dependencias.
- Línea 9: Objeto de conexión a la base de datos.
- Líneas 15 a 17: Constructor, usado para recuperar el objeto de conexión a la base de datos.
- Líneas 23 a 34: Método que devuelve la lista de etiquetas correspondientes a las notas de un usuario.
- Líneas 40 a 58: Método que devuelve todos los datos de una nota.
- Líneas 64 a 77: Método que devuelve la lista de notas de un usuario.
- Líneas 85 a 105: Método que busca una lista de notas basada en criterios.
- Líneas 114 a 127: Método usado para añadir una nueva nota a la base de datos.
- Líneas 135 a 155: Método usado para actualizar una nota existente.
- Líneas 160 a 164: Método usado para eliminar una nota.
- Líneas 171 a 183: Método privado usado para enriquecer una lista de notas con las etiquetas asociadas.
- Líneas 189 a 209: Método privado usado para crear etiquetas y asociarlas a una nota.
En un controlador, esta DAO se llama a través del componente de inyección de dependencias. Por ejemplo:
$note = $this->_loader->NoteDao->getNote($noteId);