1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
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<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")),
]);
migrations.to_latest(&mut conn)?;
Ok(conn)
}
/// 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<()> {
let now = time::now()?;
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: &String,
responses: &String,
state: &String,
) -> 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<Card> {
let mut stmt = conn
.prepare(
"
SELECT question, responses, state, ready
FROM cards
WHERE deleted IS NULL
ORDER BY RANDOM()
LIMIT 1
",
)
.ok()?;
let mut rows = stmt.query([]).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 count_available(conn: &Connection) -> Option<i32> {
let now = time::now().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: &String, state: &space_repetition::State) -> Result<()> {
let now = time::now()?;
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(())
}
|
