feat(sqlite): move from file to SQLite backend

1 files changed, 192 insertions, 57 deletions

diff --git a/README.md b/README.md
index 64de347..2d6b444 100644
--- a/README.md
+++ b/README.md
@@ -34,48 +34,60 @@ mod note;
 
 <<note_request_struct>>
 
-<<req_get_notes>>
+<<note_request_impl>>
 
-<<req_get_tags>>
+<<req_get_notes>>
 
 <<req_post_notes>>
 ```
 
 ## Anatomy of a note
 
-A *note* is a simple structure recording a unique UUID and a timestamp, both assigned at creation, a list of tags, and the body of the note.
-A set of notes is just a `Vec` of `Note`s.
+A *note* is a simple structure recording a unique ID and a timestamp, both assigned at creation, a list of tags, and the body of the note.
 
 ```{#note_struct .rust}
 #[derive(Debug, Deserialize, Serialize)]
 pub struct Note {
-  uuid: Uuid,
+  id: i64,
   timestamp: DateTime<Utc>,
   tags: Vec<String>,
   body: String,
 }
-
-type Notes = Vec<Note>;
 ```
 
-A `Note` can be automatically created from a `NoteRequest`.
-
 ```{#note_impl .rust}
 impl Note {
-  fn new(body: String, tags: Vec<String>) -> Self {
-    Self { uuid: Uuid::new_v4(), timestamp: Utc::now(), tags, body }
+  pub fn new(id: i64, timestamp: DateTime<Utc>, tags: Vec<String>, body: String) -> Self {
+    Self { id, timestamp, tags, body }
   }
 }
 
-impl From<NoteRequest> for Note {
-    fn from(req: NoteRequest) -> Self {
-        Self::new(req.body, req.tags)
-    }
+```
+
+A `Note` can also be create from a `Row`, result of a query over the database.
+
+```{#note_uses .rust}
+use rusqlite::Row;
+```
+
+```{#note_impl .rust}
+impl From<&Row<'_>> for Note {
+  fn from(row: &Row<'_>) -> Note {
+    Note::new(
+      row.get(0).expect("Failed to read column 1"),        // id
+      row.get(1).expect("Failed to read column 2"),        // timestamp
+      serde_json::from_value(row.get::<_,serde_json::Value>(2).expect("Failed to read column 3")).expect("Failed to parse JSON"), // tags
+      row.get(3).expect("Failed to read column 4")) // body
+  }
 }
 ```
 
 Similarly, a `NoteRequest` is what a client would request at note creation.
-It contains the same information as a `Note` without the information assigned at creation by the server.
+It contains the same information as a `Note` without the information assigned at creation by the server and the database.
+
+```{#note_uses .rust}
+use serde_json::Value;
+```
 
 ```{#note_request_struct .rust}
 #[derive(Debug, Deserialize, Serialize)]
@@ -83,8 +95,18 @@ pub struct NoteRequest {
   tags: Vec<String>,
   body: String,
 }
+```
+
+The stucture can also be converted into a tuple of parameters to pass a SQL query (see [`Params`](https://docs.rs/rusqlite/latest/rusqlite/trait.Params.html) from `rusqlite`).
 
-type NoteRequests = Vec<NoteRequest>;
+```{#note_request_impl .rust}
+pub type NoteParams = (DateTime<Utc>, Value, String);
+
+impl From<NoteRequest> for NoteParams {
+  fn from(req: NoteRequest) -> NoteParams {
+    (Utc::now(), Value::from(req.tags), req.body)
+  }
+}
 ```
 
 ### (De)serialization
@@ -99,18 +121,9 @@ serde = { version = "1.0", features = ["derive"] }
 use serde::{Serialize, Deserialize};
 ```
 
-### UUIDs
+### IDs
 
-UUIDs are unique 128-bit identifiers, stored as 16 octets, and formatted as a hex string in five groups, e.g., `67e55044-10b1-426f-9247-bb680e5fe0c8`.
-Have a look at the [Wikipedia entry](http://en.wikipedia.org/wiki/Universally_unique_identifier) for more information.
-
-```{#dependencies .toml}
-uuid = { version = "1.1", features = ["v4","fast-rng","serde"] }
-```
-
-```{#note_uses .rust}
-use uuid::Uuid;
-```
+We are using a `i64` as unique identifier because the field is automatically handled by SQLite, and `i64` is the most convenient type to convert from/to SQLite's `INTEGER` (according to [`rusqlite`](https://docs.rs/rusqlite/latest/rusqlite/types/index.html#)).
 
 ### Timestamps
 
@@ -135,10 +148,21 @@ actix-web = "4.1"
 ```{#note_uses .rust}
 use actix_web::{HttpResponse,Responder,web,get,post};
 use actix_web::http::header::ContentType;
+use super::db::Pool;
 ```
 
 Each request handlers is an *async* function that accepts zero or more parameters, extracted from a request (see [`FromRequest`](https://docs.rs/actix-web/latest/actix_web/trait.FromRequest.html) trait), and returns an [`HttpResponse`](https://docs.rs/actix-web/latest/actix_web/struct.HttpResponse.html).
 
+Internally requests will be carried out by querying the underlying SQLite database.
+
+```{#main_mods .rust}
+mod db;
+```
+
+```{#note_uses .rust}
+use super::db;
+```
+
 ## Resources
 
 - [Tutorial](https://web.archive.org/web/20220710213947/https://hub.qovery.com/guides/tutorial/create-a-blazingly-fast-api-in-rust-part-1/)
@@ -151,11 +175,12 @@ The function takes 0 parameters and returns a JSON object.
 
 ```{#req_get_notes .rust}
 #[get("/notes")]
-pub async fn list() -> HttpResponse {
-  let notes: Notes;
-
-  <<notes_retrieve>>
-
+pub async fn get_notes(pool: web::Data<Pool>) -> HttpResponse {
+  let mut conn = web::block(move || pool.get())
+    .await
+    .expect("Blocking error")
+    .expect("Error getting from connection pool");
+  let notes: Vec<Note> = db::get_notes(&mut conn);
   HttpResponse::Ok()
     .content_type(ContentType::json())
     .json(notes)
@@ -175,18 +200,13 @@ New notes can be added by POSTing a JSON array of `NoteRequest`s of the form
 
 ```{#req_post_notes .rust}
 #[post("/notes")]
-pub async fn add(new_notes: web::Json<NoteRequests>) -> impl Responder {
-  let mut new_notes: Notes =
-    new_notes
-      .into_inner()
-      .into_iter()
-      .map(|n| n.into())
-      .collect();
-  let count = new_notes.len();
-
-  <<notes_add>>
-
-  format!("Successfully added {} note(s)", count)
+pub async fn post_notes(pool: web::Data<Pool>, req: web::Json<Vec<NoteRequest>>) -> impl Responder {
+  let mut conn = web::block(move || pool.get())
+    .await
+    .expect("Blocking error")
+    .expect("Error getting from connection pool");
+  let res = db::post_notes(&mut conn, req.into_inner());
+  format!("Successfully added {res} note(s)")
 }
 ```
 
@@ -200,7 +220,7 @@ One or more tags separated by `+` can be passed to the request.
 pub async fn get_tags(tags: web::Path<String>) -> HttpResponse {
   let tags = tags.split('+').map(|t| t.to_string()).collect::<Vec<_>>();
 
-  let notes: Notes;
+  let notes: Vec<Note>;
   <<notes_retrieve>>
   let tagged = notes.into_iter().filter(|n| tags.iter().all(|t| n.tags.contains(t))).collect::<Vec<_>>();
 
@@ -227,7 +247,7 @@ The main program is structured as follows
 The main service will instantiate a new `App` running within a `HttpServer` bound to *localhost* on port 8080.
 
 ```{#main_uses .rust}
-use actix_web::{App, HttpServer};
+use actix_web::{App, HttpServer, web};
 ```
 
 The `App` will register all request handlers defined above.
@@ -235,11 +255,12 @@ The `App` will register all request handlers defined above.
 ```{#main_service .rust}
 #[actix_web::main]
 async fn main() -> std::io::Result<()> {
-    HttpServer::new(|| {
+    let db_pool = db::get_connection_pool("notes.db");
+    HttpServer::new(move || {
         App::new()
-            .service(note::list)
-            .service(note::add)
-            .service(note::get_tags)
+            .app_data(web::Data::new(db_pool.clone()))
+            .service(note::get_notes)
+            .service(note::post_notes)
     })
     .bind(("127.0.0.1", 8080))?
     .run()
@@ -247,6 +268,119 @@ async fn main() -> std::io::Result<()> {
 }
 ```
 
+# SQLite backend
+
+`Note`s are saved into a [SQLite](https://sqlite.org/) database.
+The `notes` database contains a single table mirroring the `Note`'s structure.
+
+```{#notes.sql .sql}
+CREATE TABLE notes (
+    id        INTEGER PRIMARY KEY AUTOINCREMENT,
+    timestamp TEXT    NOT NULL,
+    tags      TEXT    NOT NULL,
+    body      TEXT    NOT NULL
+);
+```
+
+Note that, apart from [standard SQLite types](https://docs.rs/rusqlite/latest/rusqlite/types/index.html#), `DateTime<Utc>` is converted to/from `TEXT`, `Vec<String>` is first wrapped in a `Value` (from the `serde_json` crate) and then converted from/to `TEXT`.
+
+`id`s are handled automatically by SQLite and are not set on the Rust side.
+
+A new database can be bootstraped in the current directory as follows:
+
+```sh
+sqlite3 ./notes.db < notes.sql
+```
+
+## Interfacing with SQLite from Rust
+
+To interface with the underlying database we use [`rusqlite`](https://github.com/rusqlite/rusqlite) along with [`r2d2`](https://github.com/sfackler/r2d2) to create a connection pool.
+
+```{#dependencies .toml}
+r2d2 = "0.8"
+r2d2_sqlite = "0.21"
+rusqlite = { version = "0.28", features = ["chrono","serde_json"] }
+```
+
+The following code sets up a connection pool to the SQLite database.
+
+```{#db_uses .rust}
+use r2d2_sqlite::SqliteConnectionManager;
+```
+
+```{#db_types .rust}
+pub type Pool = r2d2::Pool<SqliteConnectionManager>;
+```
+
+```{#db_connection_pool .rust}
+pub fn get_connection_pool(db: &str) -> Pool {
+    let manager = SqliteConnectionManager::file(db);
+    r2d2::Pool::new(manager).expect("Unable to connect to 'notes.db'")
+}
+```
+
+For the sake of convenience, all operations on the database are stored on a separate file.
+
+```{#db.rs .rust path="src/"}
+<<db_uses>>
+
+<<db_types>>
+
+<<db_connection_pool>>
+
+<<db_operations>>
+```
+
+### Retrieving notes
+
+We first build the query (caching it for later) and then execute it to retrieve all requested notes.
+
+We return a `Vec<Note>` built from the query result.
+
+```{#db_uses .rust}
+use rusqlite::Connection;
+use super::note::Note;
+```
+
+```{#db_operations .rust}
+pub fn get_notes(conn: &mut Connection) -> Vec<Note> {
+    let mut query = conn.prepare_cached("SELECT * FROM notes")
+        .expect("Failed to prepare SELECT query");
+    query
+        .query_map([], |row| Ok(Note::from(row)))
+        .and_then(Iterator::collect)
+        .expect("Failed to collect query results")
+}
+
+```
+
+### Creating notes
+
+When inserting new `Note`s in the database, we loop over the requested notes, attaching a timestamp and executing an `INSERT` SQL query.
+
+SQLite will take care of attaching an ID to the new entry.
+
+Operations are executed into a single transaction [to achieve better performances](https://github.com/rusqlite/rusqlite/issues/262#issuecomment-294895051).
+
+```{#db_uses .rust}
+use super::note::{NoteParams,NoteRequest};
+```
+
+```{#db_operations .rust}
+pub fn post_notes(conn: &mut Connection, reqs: Vec<NoteRequest>) -> usize {
+    let tx = conn.transaction().expect("Failed to start transaction");
+    {
+        let mut stmt = tx.prepare_cached(
+            "INSERT INTO notes (timestamp, tags, body) VALUES (?, ?, ?)"
+        ).expect("Failed to prepare INSERT query");
+        reqs.into_iter().for_each(|req| { stmt.execute::<NoteParams>(req.into()).expect("Failed to execute INSERT query"); });
+    }
+    tx.commit().expect("Commit failed");
+    0
+}
+
+```
+
 # Testing
 
 ## Using a file as a backend
@@ -257,10 +391,6 @@ This is a temporary solution until an interface to a database (most likely SQLit
 serde_json = "1.0"
 ```
 
-```{#note_uses .rust}
-use std::fs::File;
-```
-
 ### Retrieving notes
 
 ```{#notes_retrieve .rust}
@@ -273,7 +403,7 @@ notes = {
 ### Adding notes
 
 ```{#notes_add .rust}
-let mut notes: Notes;
+let mut notes: Vec<Note>;
 <<notes_retrieve>>
 notes.append(&mut new_notes);
 
@@ -281,11 +411,16 @@ let db = File::create("notes.db").expect("Unable to create/open 'notes.db'");
 serde_json::to_writer(&db,&notes).expect("Unable to write to 'notes.db'");
 ```
 
-# Open questions
+# TODOs
+
+- Better error handling
+
+## Open questions
 
 - Should one be able to delete notes? Or mark them as read/processed?
 - Authentication method?
 - Custom filters on retrieval.
+- `rusqlite` ships with SQLite bundled if necessary (useful to bootstrap the db?)
 
 # Credits