feat(GET /tag/{tags}): reenable querying by tag(s)

This reintroduces the feature that allows to query the database by tag(s);
this time the feature is implemented using the `LIKE` operator of SQL.

1 files changed, 58 insertions, 11 deletions

diff --git a/README.md b/README.md
index 4366979..c2f62bc 100644
--- a/README.md
+++ b/README.md
@@ -183,21 +183,26 @@ pub async fn post_notes(pool: web::Data<Pool>, req: web::Json<Vec<NoteRequest>>)
 
 ## GET /tag/{tags}
 
-**Deprecated:** this is currently not implemented for the new SQLite backend.
+Notes can be retrieved by (sets of) tags.
+One or more tags separated by `+` can be passed to the request and `joyce` will retrieve those notes marked with **all** the provided tags.
 
-This handler allows to query the set of notes for specific tags.
-One or more tags separated by `+` can be passed to the request.
+The function takes 2 parameters (the connection pool to the underlying SQLite database and the sequence of tags) and returns a collection of notes as a JSON array.
+
+Passing an empty collection of tags results in an error.
 
 ```{#req_get_tags .rust}
 #[get("/tag/{tags}")]
-pub async fn get_tags(tags: web::Path<String>) -> HttpResponse {
+pub async fn get_tags(pool: web::Data<Pool>, tags: web::Path<String>) -> HttpResponse {
+  <<get_connection>>
   let tags = tags.split('+').map(|t| t.to_string()).collect::<Vec<_>>();
-
-  todo();
-
-  HttpResponse::Ok()
-    .content_type(ContentType::json())
-    .json(tagged)
+  if !tags.is_empty() {
+    let notes: Vec<Note> = db::get_tags(&mut conn, &tags);
+    HttpResponse::Ok()
+      .content_type(ContentType::json())
+      .json(notes)
+  } else {
+    todo!()
+  }
 }
 ```
 
@@ -310,6 +315,45 @@ pub fn post_notes(conn: &mut Connection, reqs: Vec<NoteRequest>) -> usize {
 
 ```
 
+### Retrieving tagged notes
+
+We can query for specific tags by using the `LIKE` SQL operator.
+Such an SQL query will look like this
+
+    SELECT * FROM notes
+    WHERE tags LIKE '%<tag>%'
+    [ AND tags LIKE '%<tag>%' ...]
+
+where the escaped expression `'%<tag>%'` matches anything containing the word `<tag>`, i.e., the `%` operator matches a (possibly empty) sequence of characters.
+
+A query needs to be dynamically generated for the specific number of provided tags.
+
+```{#build_query_tags .rust}
+let mut query = String::with_capacity(32 + 16 * tags.len());
+query.push_str("SELECT * FROM notes WHERE tags LIKE ? ");
+for _ in 0..(tags.len()-1) {
+    query.push_str("AND tags LIKE ? ");
+}
+```
+
+Once built, we execute the query with the sequence of tags as parameters.
+Note that we need to use [`rusqlite::params_from_iter`](https://docs.rs/rusqlite/latest/rusqlite/fn.params_from_iter.html) in order to handle a dynamic set of parameters.
+
+```{#db_operations .rust}
+pub fn get_tags(conn: &mut Connection, tags: &[String]) -> Vec<Note> {
+  <<build_query_tags>>
+  let mut query = conn.prepare_cached(&query)
+      .expect("Failed to prepare SELECT query");
+  let params = tags.iter().map(|tag| format!("%\"{}\"%", tag));
+  query
+      .query_map(rusqlite::params_from_iter(params), |row| Ok(Note::from(row)))
+      .and_then(Iterator::collect)
+      .expect("Failed to collect query results")
+}
+
+```
+
+
 # Main service
 
 The main service will instantiate a new `App` running within a `HttpServer` bound to *localhost* on port 8080.
@@ -328,6 +372,7 @@ async fn main() -> std::io::Result<()> {
         App::new()
             .app_data(web::Data::new(db_pool.clone()))
             .service(note::get_notes)
+            .service(note::get_tags)
             .service(note::post_notes)
     })
     .bind(("127.0.0.1", 8080))?
@@ -364,6 +409,8 @@ Notes, along with their REST API are defined in their own `note` module.
 
 <<req_get_notes>>
 
+<<req_get_tags>>
+
 <<req_post_notes>>
 ```
 
@@ -381,7 +428,7 @@ Communication with SQLite is grouped under the `db` module.
 
 # TODOs
 
-- Better error handling with `anyhow`
+- Better error handling
 - CLI with `clap`
 
 ## Open questions