From a922aa28908d83c8ecead51e6d63c7c549568b5c Mon Sep 17 00:00:00 2001 From: Federico Igne Date: Fri, 5 Aug 2022 13:44:36 +0100 Subject: feat(sqlite): move from file to SQLite backend --- README.md | 249 ++++++++++++++++++++++++++++++++++++++++++++++++-------------- 1 file 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; <> -<> +<> -<> +<> <> ``` ## 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, tags: Vec, body: String, } - -type Notes = Vec; ``` -A `Note` can be automatically created from a `NoteRequest`. - ```{#note_impl .rust} impl Note { - fn new(body: String, tags: Vec) -> Self { - Self { uuid: Uuid::new_v4(), timestamp: Utc::now(), tags, body } + pub fn new(id: i64, timestamp: DateTime, tags: Vec, body: String) -> Self { + Self { id, timestamp, tags, body } } } -impl From 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, 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; +```{#note_request_impl .rust} +pub type NoteParams = (DateTime, Value, String); + +impl From 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; - - <> - +pub async fn get_notes(pool: web::Data) -> HttpResponse { + let mut conn = web::block(move || pool.get()) + .await + .expect("Blocking error") + .expect("Error getting from connection pool"); + let notes: Vec = 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) -> impl Responder { - let mut new_notes: Notes = - new_notes - .into_inner() - .into_iter() - .map(|n| n.into()) - .collect(); - let count = new_notes.len(); - - <> - - format!("Successfully added {} note(s)", count) +pub async fn post_notes(pool: web::Data, req: web::Json>) -> 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) -> HttpResponse { let tags = tags.split('+').map(|t| t.to_string()).collect::>(); - let notes: Notes; + let notes: Vec; <> let tagged = notes.into_iter().filter(|n| tags.iter().all(|t| n.tags.contains(t))).collect::>(); @@ -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` is converted to/from `TEXT`, `Vec` 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; +``` + +```{#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/"} +<> + +<> + +<> + +<> +``` + +### Retrieving notes + +We first build the query (caching it for later) and then execute it to retrieve all requested notes. + +We return a `Vec` 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 { + 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) -> 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::(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; <> 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,¬es).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 -- cgit v1.2.3