use anyhow::Result; use rusqlite::{named_params, Connection}; use crate::model::DbEntry; use crate::util::time; use crate::{ model::{Card, Question}, space_repetition, util::serialization, }; pub fn all(conn: &Connection) -> Result> { let query = r#"SELECT question, responses, deleted FROM cards"#; let mut stmt = conn.prepare(query)?; let res: Result, rusqlite::Error> = stmt .query_map([], |row| { let responses: String = row.get(1)?; Ok(DbEntry { question: row.get(0)?, responses: serialization::line_to_words(&responses), deleted: row.get(2)?, }) })? .collect(); Ok(res?) } pub fn insert(conn: &mut Connection, questions: &Vec) -> 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())?; let tx = conn.transaction()?; for Question { question, responses, } in questions { let responses = serialization::words_to_line(responses); tx.execute( query, named_params![ ":question": question, ":responses": responses, ":state": state, ":created": now, ":ready": now ], )?; } tx.commit()?; Ok(()) } pub fn delete(conn: &mut Connection, questions: &Vec) -> 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()?; for Question { question, responses, } in questions { let responses = serialization::words_to_line(responses); tx.execute( query, named_params![ ":deleted": now, ":question": question, ":responses": responses ], )?; } tx.commit()?; Ok(()) } pub fn undelete(conn: &mut Connection, questions: &Vec) -> Result<()> { let query = r#" UPDATE cards SET deleted = NULL WHERE question = :questions AND responses = :responses "#; let tx = conn.transaction()?; for Question { question, responses, } in questions { let responses = serialization::words_to_line(responses); tx.execute( query, named_params![ ":question": question, ":responses": responses ], )?; } tx.commit()?; Ok(()) } pub fn pick_random_ready(conn: &Connection) -> Option { let query = r#" SELECT question, responses, state, ready FROM cards WHERE deleted IS NULL AND ready <= :ready ORDER BY RANDOM() LIMIT 1 "#; 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()?; Some(Card { question: row.get(0).ok()?, responses: serialization::line_to_words(&responses_str), state: serde_json::from_str(&state_str).ok()?, }) } pub fn next_ready(conn: &Connection) -> Option { let query = r#" SELECT ready FROM cards WHERE deleted IS NULL ORDER BY ready LIMIT 1 "#; let mut stmt = conn.prepare(query).ok()?; let mut rows = stmt.query([]).ok()?; let row = rows.next().ok()??; row.get(0).ok()? } pub fn count_available(conn: &Connection) -> Option { let now = time::seconds_since_unix_epoch().ok()?; let mut stmt = conn .prepare("SELECT COUNT(*) FROM cards WHERE ready <= ? AND deleted IS NULL") .ok()?; let mut rows = stmt.query([now]).ok()?; let row = rows.next().ok()??; row.get(0).ok()? } 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( query, named_params![ ":state": state_str, ":updated": now, ":ready": ready, ":question": question ], )?; Ok(()) }