API: Database access
1Introduction
To make our API work, we'll need three database tables, one to store notes, one to store tags and one to link the two. These three tables are in addition to those used to store users and authentication keys.
2Tables
Here are the queries for creating the two tables:
-- Table containing 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 containing 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;
-- Link table between notes and 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
The controllers will need to access the data in the database. This will be done via a "DAO" (Data Access Object), which will be an object accessed via the dependency injection copmosant.
Here's the code recorded in the lib/NoteDao.php file:
<?php
/**
* DAO for managing notes and tags.
* Loadable object with dependency injection component.
*/
class NoteDao implements \Temma\Base\Loadable {
/** Database access. */
private \Temma\Datasource\Sql $_db;
/**
* Constructor.
* @param \Temma\Base\Loader $loader Dependency injection components.
*/
public function __construct(\Temma\Base\Loader $loader) {
$this->_db = $loader->dataSources['db'];
}
/**
* Returns the list of tags for a user.
* @param int $userId User identifier.
* @return array Associative array with tags as keys and the number of associated notes as values.
*/
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);
}
/**
* Returns a note from its identifier.
* @param int $noteId Note identifier.
* @return array Associative array.
*/
public function getNote(int $noteId) : array {
// note retrieval
$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);
// tag retrieval
$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);
}
/**
* Returns a list of a user's notes, most recently modified first.
* @param int $userId User identifier.
* @return array List of associative arrays.
*/
public function getNotes(int $userId) : array {
// notes retrieval
$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');
// tag retrieval
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Returns a list of notes based on search criteria.
* @param int $userId User identifier.
* @param ?string $tag (optionnel) Tag to search for.
* @param ?string $title (optionnel) String to search for in title.
* @return array List of associative arrays.
*/
public function searchNotes(int $userId, ?string $tag=null, ?string $title) : array {
// search for 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');
// tag retrieval
$notes = $this->_fetchTags($notes);
return ($notes);
}
/**
* Add a new note.
* @param int $userId User identifier.
* @param string $title Note title.
* @param string $content HTML content of the note.
* @param ?array $tags List of tags in the note.
* @return int New note identifier.
*/
public function create(int $userId, string $title, string $content, ?array $tags) : int {
// creation of the 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);
// add tags
$this->_addTagsToNote($noteId, $tags);
return ($noteId);
}
/**
* Modifies an existing note.
* @param int $noteId Note identifier.
* @param ?string $title (optionnel) New note title.
* @param ?string $content (optionnel) New HTML content for the note.
* @param ?array $tags (optionnel) New list of tags for the 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;
// removal of old tags
$sql = "DELETE FROM Note_Tag
WHERE note_id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
// add tags
$this->_addTagsToNote($noteId, $tags);
}
/**
* Deletes a note.
* @param int $noteId Note identifier.
*/
public function remove(int $noteId) : void {
$sql = "DELETE FROM Note
WHERE id = " . $this->_db->quote($noteId);
$this->_db->exec($sql);
}
/**
* Private method that enriches notes with their tags.
* @param array $notes List of notes.
* @return array The enriched list.
*/
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);
}
/**
* Private method that adds tags to a note.
* @param int $noteId Note identifier.
* @param array $tags List of tags.
*/
private function _addTagsToNote(int $noteId, array $tags) : void {
// character escaping
$tags = array_map(function($t) {
return $this->_db->quote($t);
}, $tags);
// insert tags that don't yet exist
$sql = "INSERT INTO Tag (tag)
VALUES (" . implode('), (', $tags) . ")
ON DUPLICATE KEY UPDATE id = id";
$this->_db->exec($sql);
// retrieve tag identifiers
$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);
}
}
- Line 7: Creation of the NoteDao object, which implements the \Temma\Base\Loadable interface to be used through the dependency injection component.
- Line 9: Database connection object.
- Lines 15 to 17: Constructor, used to retrieve the database connection object.
- Lines 23 to 34: Method that returns the list of tags corresponding to a user's notes.
- Lines 40 to 58: Method that returns all note data.
- Lines 64 to 77: Method that returns the list of a user's notes.
- Lines 85 to 105: Method that searches a list of notes based on criteria.
- Lines 114 to 127: Method used to add a new note to the database.
- Lines 135 to 155: Method used to update an existing note.
- Lines 160 to 164: Method used to delete a note.
- Lines 171 to 183: Private method used to enrich a list of notes with associated tags.
- Lines 189 to 209: Private method used to create tags and associate them with a note.
In a controller, this DAO is called via the dependency injection component. For example:
$note = $this->_loader->NoteDao->getNote($noteId);