diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/db/cards.rs | 99 | ||||
| -rw-r--r-- | src/db/mod.rs | 4 |
2 files changed, 69 insertions, 34 deletions
diff --git a/src/db/cards.rs b/src/db/cards.rs index 709e4eb..7778354 100644 --- a/src/db/cards.rs +++ b/src/db/cards.rs @@ -1,5 +1,5 @@ use anyhow::Result; -use rusqlite::{params, Connection}; +use rusqlite::{named_params, Connection}; use crate::model::DbEntry; use crate::util::time; @@ -10,7 +10,8 @@ use crate::{ }; pub fn all(conn: &Connection) -> Result<Vec<DbEntry>> { - let mut stmt = conn.prepare("SELECT question, responses, deleted FROM cards")?; + let query = r#"SELECT question, responses, deleted FROM cards"#; + let mut stmt = conn.prepare(query)?; let res: Result<Vec<DbEntry>, rusqlite::Error> = stmt .query_map([], |row| { @@ -27,6 +28,11 @@ pub fn all(conn: &Connection) -> Result<Vec<DbEntry>> { } pub fn insert(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { + let query = r#" + INSERT INTO cards (question, responses, state, created, ready) + VALUES (:question, :responses, :state, :created, :ready) + "#; + let now = time::seconds_since_unix_epoch()?; let state = serde_json::to_string(&space_repetition::init())?; @@ -38,11 +44,14 @@ pub fn insert(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { { let responses = serialization::words_to_line(responses); tx.execute( - " - INSERT INTO cards (question, responses, state, created, ready) - VALUES (?, ?, ?, ?, ?) - ", - params![question, responses, state, now, now], + query, + named_params![ + ":question": question, + ":responses": responses, + ":state": state, + ":created": now, + ":ready": now + ], )?; } tx.commit()?; @@ -50,6 +59,14 @@ pub fn insert(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { } pub fn delete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { + let query = r#" + UPDATE cards + SET deleted = :deleted + WHERE + question = :question + AND responses = :responses + "#; + let now = time::seconds_since_unix_epoch()?; let tx = conn.transaction()?; @@ -60,8 +77,12 @@ pub fn delete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { { let responses = serialization::words_to_line(responses); tx.execute( - "UPDATE cards SET deleted = ? WHERE question = ? AND responses = ?", - params![now, question, responses], + query, + named_params![ + ":deleted": now, + ":question": question, + ":responses": responses + ], )?; } tx.commit()?; @@ -69,6 +90,14 @@ pub fn delete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { } pub fn undelete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { + let query = r#" + UPDATE cards + SET deleted = NULL + WHERE + question = :questions + AND responses = :responses + "#; + let tx = conn.transaction()?; for Question { question, @@ -77,8 +106,11 @@ pub fn undelete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> { let responses = serialization::words_to_line(responses); tx.execute( - "UPDATE cards SET deleted = NULL WHERE question = ? AND responses = ?", - params![question, responses], + query, + named_params![ + ":question": question, + ":responses": responses + ], )?; } tx.commit()?; @@ -86,21 +118,18 @@ pub fn undelete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> } pub fn pick_random_ready(conn: &Connection) -> Option<Card> { - let now = time::seconds_since_unix_epoch().ok()?; - - let mut stmt = conn - .prepare( - " + let query = r#" SELECT question, responses, state, ready FROM cards - WHERE deleted IS NULL AND ready <= ? + WHERE deleted IS NULL AND ready <= :ready ORDER BY RANDOM() LIMIT 1 - ", - ) - .ok()?; + "#; - let mut rows = stmt.query([now]).ok()?; + let now = time::seconds_since_unix_epoch().ok()?; + + let mut stmt = conn.prepare(query).ok()?; + let mut rows = stmt.query(named_params![":ready": now]).ok()?; let row = rows.next().ok()??; let state_str: String = row.get(2).ok()?; let responses_str: String = row.get(1).ok()?; @@ -113,18 +142,15 @@ pub fn pick_random_ready(conn: &Connection) -> Option<Card> { } pub fn next_ready(conn: &Connection) -> Option<u64> { - let mut stmt = conn - .prepare( - " + let query = r#" SELECT ready FROM cards WHERE deleted IS NULL ORDER BY ready LIMIT 1 - ", - ) - .ok()?; + "#; + let mut stmt = conn.prepare(query).ok()?; let mut rows = stmt.query([]).ok()?; let row = rows.next().ok()??; row.get(0).ok()? @@ -142,17 +168,24 @@ pub fn count_available(conn: &Connection) -> Option<i32> { } pub fn update(conn: &Connection, question: &str, state: &space_repetition::State) -> Result<()> { + let query = r#" + UPDATE cards + SET state = :state, updated = :updated, ready = :ready + WHERE question = :question + "#; + let now = time::seconds_since_unix_epoch()?; let ready = now + state.get_interval_seconds(); let state_str = serde_json::to_string(state)?; conn.execute( - " - UPDATE cards - SET state = ?, updated = ?, ready = ? - WHERE question = ? - ", - params![state_str, now, ready, question], + query, + named_params![ + ":state": state_str, + ":updated": now, + ":ready": ready, + ":question": question + ], )?; Ok(()) diff --git a/src/db/mod.rs b/src/db/mod.rs index 67d9a93..210f5e9 100644 --- a/src/db/mod.rs +++ b/src/db/mod.rs @@ -15,7 +15,9 @@ pub fn init(database: &str) -> Result<Connection> { fn apply_migrations(conn: &mut Connection) -> Result<()> { let migrations = Migrations::new(vec![ M::up(include_str!("migrations/01-init.sql")), - M::up(include_str!("migrations/02-primary-key-question-responses.sql")), + M::up(include_str!( + "migrations/02-primary-key-question-responses.sql" + )), M::up(include_str!("migrations/03-drop-deck-read.sql")), M::up(include_str!("migrations/04-strict-tables.sql")), ]); |