API: Acesso ao banco de dados
1Introdução
Para que nossa API funcione, vamos precisar de três tabelas no banco de dados: uma para armazenar notas, uma para armazenar tags e uma para ligar as duas. Essas três tabelas se somam às usadas para armazenar usuários e chaves de autenticação.
2Tabelas
Aqui estão as consultas para criar as duas tabelas:
-- Tabela que armazena as 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;
-- Tabela que armazena as 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;
-- Tabela de ligação entre notas e 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
Os controladores vão precisar acessar os dados no banco de dados. Isso será feito por meio de uma "DAO" (Data Access Object), que será um objeto acessado via o componente de injeção de dependências.
Aqui está o código gravado no arquivo lib/NoteDao.php:
<?php
/**
* DAO de gerenciamento de notas e tags.
* Objeto carregável com o componente de injeção de dependências.
*/
class NoteDao implements \Temma\Base\Loadable {
/** Acesso ao banco de dados. */
private \Temma\Datasource\Sql $_db;
/**
* Construtor.
* @param \Temma\Base\Loader $loader Componente de injeção de dependências.
*/
public function __construct(\Temma\Base\Loader $loader) {
$this->_db = $loader->dataSources->db;
}
/**
* Retorna a lista de tags de um usuário.
* @param int $userId Identificador do usuário.
* @return array Array associativo com as tags como chaves e o número de notas associadas 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);
}
/**
* Retorna uma nota a partir do seu identificador.
* @param int $noteId Identificador da nota.
* @return array Array associativo.
*/
public function getNote(int $noteId) : array {
// recuperação da 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);
// recuperação das 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);
}
/**
* Retorna a lista de notas de um usuário, as modificadas mais recentemente primeiro.
* @param int $userId Identificador do usuário.
* @return array Lista de arrays associativos.
*/
public function getNotes(int $userId) : array {
// recuperação das 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');
// recuperação das tags
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Retorna uma lista de notas com base em critérios de busca.
* @param int $userId Identificador do usuário.
* @param ?string $tag (opcional) Tag a ser buscada.
* @param ?string $title (opcional) String a ser buscada no título.
* @return array Lista de arrays associativos.
*/
public function searchNotes(int $userId, ?string $tag=null, ?string $title=null) : array {
// busca as 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');
// recuperação das tags
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Adiciona uma nova nota.
* @param int $userId Identificador do usuário.
* @param string $title Título da nota.
* @param string $content Conteúdo HTML da nota.
* @param ?array $tags Lista de tags da nota.
* @return int Identificador da nova nota.
*/
public function create(int $userId, string $title, string $content, ?array $tags) : int {
// criação da 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);
// adiciona as tags
$this->_addTagsToNote($noteId, $tags);
return ($noteId);
}
/**
* Modifica uma nota existente.
* @param int $noteId Identificador da nota.
* @param ?string $title (opcional) Novo título da nota.
* @param ?string $content (opcional) Novo conteúdo HTML da nota.
* @param ?array $tags (opcional) Nova lista de tags da 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;
// remoção das tags antigas
$sql = "DELETE FROM Note_Tag
WHERE note_id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
// adiciona as tags
$this->_addTagsToNote($noteId, $tags);
}
/**
* Apaga uma nota.
* @param int $noteId Identificador da 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 notas com suas tags.
* @param array $notes Lista de notas.
* @return array A 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 adiciona tags a uma nota.
* @param int $noteId Identificador da nota.
* @param array $tags Lista de tags.
*/
private function _addTagsToNote(int $noteId, array $tags) : void {
// escapamento de caracteres
$tags = array_map(function($t) {
return $this->_db->quote($t);
}, $tags);
// insere as tags que ainda não existem
$sql = "INSERT INTO Tag (tag)
VALUES (" . implode('), (', $tags) . ")
ON DUPLICATE KEY UPDATE id = id";
$this->_db->exec($sql);
// recuperação dos identificadores das tags
$sql = "SELECT id
FROM Tag
WHERE tag IN (" . implode(', ', $tags) . ")";
$tagIds = $this->_db->queryAll($sql, null, 'id');
// vínculos entre a nota e as 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);
}
}
- Linha 7: Criação do objeto NoteDao, que implementa a interface \Temma\Base\Loadable para ser usado por meio do componente de injeção de dependências.
- Linha 9: Objeto de conexão com o banco de dados.
- Linhas 15 a 17: Construtor, usado para recuperar o objeto de conexão com o banco de dados.
- Linhas 23 a 34: Método que retorna a lista de tags correspondentes às notas de um usuário.
- Linhas 40 a 58: Método que retorna todos os dados de uma nota.
- Linhas 64 a 77: Método que retorna a lista de notas de um usuário.
- Linhas 85 a 105: Método que busca uma lista de notas com base em critérios.
- Linhas 114 a 127: Método usado para adicionar uma nova nota ao banco de dados.
- Linhas 135 a 155: Método usado para atualizar uma nota existente.
- Linhas 160 a 164: Método usado para excluir uma nota.
- Linhas 171 a 183: Método privado usado para enriquecer uma lista de notas com as tags associadas.
- Linhas 189 a 209: Método privado usado para criar tags e associá-las a uma nota.
Em um controlador, essa DAO é chamada por meio do componente de injeção de dependências. Por exemplo:
$note = $this->_loader->NoteDao->getNote($noteId);