aboutsummaryrefslogtreecommitdiff
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/db.rs122
-rw-r--r--src/db/mod.rs1
-rw-r--r--src/db/sql/1-init.sql10
3 files changed, 133 insertions, 0 deletions
diff --git a/src/db/db.rs b/src/db/db.rs
new file mode 100644
index 0000000..93c9564
--- /dev/null
+++ b/src/db/db.rs
@@ -0,0 +1,122 @@
+use crate::{
+ model::{card::Card, deck::Entry},
+ space_repetition,
+ util::serialization,
+};
+use anyhow::Result;
+use rusqlite::{params, Connection};
+use rusqlite_migration::{Migrations, M};
+use std::time::SystemTime;
+
+pub fn init() -> Result<Connection> {
+ let mut conn = Connection::open("database.db")?;
+ let migrations = Migrations::new(vec![M::up(include_str!("sql/1-init.sql"))]);
+ migrations.to_latest(&mut conn)?;
+ Ok(conn)
+}
+
+pub fn add_missing_deck_entries(conn: &Connection, entries: Vec<Entry>) -> Result<()> {
+ let now = get_current_time()?;
+ let ready = now;
+ let day: u64 = 60 * 60 * 24;
+
+ 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 (i, w) in entry.part_1.iter().enumerate() {
+ let r = ready + (i as u64) * day;
+ insert(&conn, now, r, &w, &concat_2, &state)?;
+ }
+
+ for (i, w2) in entry.part_2.iter().enumerate() {
+ let r = ready + ((entry.part_1.len() + i) as u64) * day;
+ insert(&conn, now, r, &w2, &concat_1, &state)?;
+ }
+ }
+
+ delete_read_before(&conn, now)?;
+
+ Ok(())
+}
+
+fn insert(
+ conn: &Connection,
+ now: u64,
+ ready: u64,
+ question: &String,
+ responses: &String,
+ state: &String,
+) -> Result<()> {
+ conn.execute(
+ "
+ INSERT INTO cards (question, responses, state, created, deck_read, ready)
+ VALUES (?, ?, ?, ?, ?, ?)
+ ON CONFLICT (question) DO UPDATE SET deck_read = ?, deleted = null
+ ",
+ params![question, responses, state, now, now, ready, 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_ready(conn: &Connection) -> Option<Card> {
+ let now = get_current_time().ok()?;
+
+ let mut stmt = conn
+ .prepare(
+ "
+ SELECT question, responses, state
+ FROM cards
+ WHERE ready <= ? AND deleted IS NULL
+ ORDER BY ready
+ 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()?,
+ })
+}
+
+pub fn update(conn: &Connection, question: &String, state: &space_repetition::State) -> Result<()> {
+ let now = get_current_time()?;
+ 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(())
+}
+
+fn get_current_time() -> Result<u64> {
+ Ok(SystemTime::now()
+ .duration_since(SystemTime::UNIX_EPOCH)?
+ .as_secs())
+}
diff --git a/src/db/mod.rs b/src/db/mod.rs
new file mode 100644
index 0000000..dec1023
--- /dev/null
+++ b/src/db/mod.rs
@@ -0,0 +1 @@
+pub mod db;
diff --git a/src/db/sql/1-init.sql b/src/db/sql/1-init.sql
new file mode 100644
index 0000000..29d70ed
--- /dev/null
+++ b/src/db/sql/1-init.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS cards (
+ question VARCHAR PRIMARY KEY,
+ responses VARCHAR NOT NULL,
+ state VARCHAR NOT NULL,
+ created TIMESTAMP NOT NULL,
+ updated TIMESTAMP NULL,
+ deleted TIMESTAMP NULL,
+ deck_read TIMESTAMP NOT NULL,
+ ready TIMESTAMP NOT NULL
+)