use crate::{ model::{card::Card, entry::Entry}, space_repetition, util::serialization, }; use anyhow::Result; use rusqlite::{params, Connection}; use rusqlite_migration::{Migrations, M}; use crate::util::time; pub fn init(database: String) -> Result { let mut conn = Connection::open(database)?; let migrations = Migrations::new(vec![ M::up(include_str!("sql/1-init.sql")), M::up(include_str!("sql/2-primary-key-question-responses.sql")), ]); migrations.to_latest(&mut conn)?; Ok(conn) } pub fn last_deck_read(conn: &Connection) -> Option { let mut stmt = conn .prepare("SELECT deck_read FROM cards ORDER BY deck_read DESC LIMIT 1") .ok()?; let mut rows = stmt.query([]).ok()?; let row = rows.next().ok()??; row.get(0).ok()? } /// Synchronize the DB with the deck: /// /// - insert new cards, /// - keep existing cards, /// - hide unused cards (keep state in case the card is added back afterward). pub fn synchronize(conn: &Connection, entries: Vec) -> Result<()> { let now = time::seconds_since_unix_epoch()?; let state = serde_json::to_string(&space_repetition::init())?; for entry in entries { let concat_1 = serialization::words_to_line(&entry.part_1); let concat_2 = serialization::words_to_line(&entry.part_2); for w in entry.part_1.iter() { insert(conn, now, w, &concat_2, &state)?; } for w in entry.part_2.iter() { insert(conn, now, w, &concat_1, &state)?; } } delete_read_before(conn, now)?; Ok(()) } fn insert( conn: &Connection, now: u64, question: &str, responses: &str, state: &str, ) -> Result<()> { conn.execute( " INSERT INTO cards (question, responses, state, created, deck_read, ready) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT (question, responses) DO UPDATE SET deck_read = ?, deleted = null ", params![question, responses, state, now, now, now, now], )?; Ok(()) } fn delete_read_before(conn: &Connection, t: u64) -> Result<()> { conn.execute( "UPDATE cards SET deleted = ? WHERE deck_read < ?", params![t, t], )?; Ok(()) } pub fn pick_random_ready(conn: &Connection) -> Option { let now = time::seconds_since_unix_epoch().ok()?; let mut stmt = conn .prepare( " SELECT question, responses, state, ready FROM cards WHERE deleted IS NULL AND ready <= ? ORDER BY RANDOM() LIMIT 1 ", ) .ok()?; let mut rows = stmt.query([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()?, ready: row.get(3).ok()?, }) } pub fn next_ready(conn: &Connection) -> Option { let mut stmt = conn .prepare( " SELECT ready FROM cards WHERE deleted IS NULL ORDER BY ready LIMIT 1 ", ) .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 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], )?; Ok(()) }