Speed up deck synchronization

Apply changes for what has been inserted, updated, removed.

Also use transactions to speed up multi-writing.

2 files changed, 68 insertions, 49 deletions

diff --git a/src/db/mod.rs b/src/db/mod.rs
index c2749dc..434d74a 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -1,81 +1,99 @@
-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::model::DbEntry;
 use crate::util::time;
+use crate::{
+    model::{Card, Question},
+    space_repetition,
+    util::serialization,
+};
 
 pub fn init(database: String) -> Result<Connection> {
     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")),
+        M::up(include_str!("sql/3-drop-deck-read.sql")),
     ]);
     migrations.to_latest(&mut conn)?;
     Ok(conn)
 }
 
-pub fn last_deck_read(conn: &Connection) -> Option<u64> {
-    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()?
+pub fn all(conn: &Connection) -> Result<Vec<DbEntry>> {
+    let mut stmt = conn.prepare("SELECT question, responses, deleted FROM cards")?;
+
+    let res: Result<Vec<DbEntry>, 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?)
 }
 
-/// 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<Entry>) -> Result<()> {
+pub fn insert(conn: &mut Connection, questions: &Vec<Question>) -> 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)?;
-        }
+    let tx = conn.transaction()?;
+    for Question {
+        question,
+        responses,
+    } in questions
+    {
+        let responses = serialization::words_to_line(responses);
+        tx.execute(
+            "
+            INSERT INTO cards (question, responses, state, created, ready)
+            VALUES (?, ?, ?, ?, ?)
+            ",
+            params![question, responses, state, now, now],
+        )?;
     }
-
-    delete_read_before(conn, now)?;
-
+    tx.commit()?;
     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],
-    )?;
+pub fn delete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> {
+    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(
+            "UPDATE cards SET deleted = ? WHERE question = ? AND responses = ?",
+            params![now, question, responses],
+        )?;
+    }
+    tx.commit()?;
     Ok(())
 }
 
-fn delete_read_before(conn: &Connection, t: u64) -> Result<()> {
-    conn.execute(
-        "UPDATE cards SET deleted = ? WHERE deck_read < ?",
-        params![t, t],
-    )?;
-
+pub fn undelete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> {
+    let tx = conn.transaction()?;
+    for Question {
+        question,
+        responses,
+    } in questions
+    {
+        let responses = serialization::words_to_line(responses);
+        tx.execute(
+            "UPDATE cards SET deleted = NULL WHERE question = ? AND responses = ?",
+            params![question, responses],
+        )?;
+    }
+    tx.commit()?;
     Ok(())
 }
 
diff --git a/src/db/sql/3-drop-deck-read.sql b/src/db/sql/3-drop-deck-read.sql
new file mode 100644
index 0000000..1ca23d1
--- /dev/null
+++ b/src/db/sql/3-drop-deck-read.sql
@@ -0,0 +1 @@
+ALTER TABLE cards DROP COLUMN deck_read;