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
|
use rusqlite::Connection;
use error::{Result, LinoError};
#[derive(Serialize, Debug, Clone)]
pub struct Quote {
id: i64,
date: String,
author: String,
score: String,
votes: u32,
content: String,
}
pub fn init(c: &Connection) -> Result<()> {
info!("Initializing db");
try!(c.execute_batch(
r#"
CREATE TABLE quotes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
approved BOOL DEFAULT false,
timestamp DATETIME NOT NULL,
author TEXT NOT NULL,
content TEXT NOT NULL,
CONSTRAINT unique_content UNIQUE (timestamp, content)
);
CREATE TABLE votes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
quote_id INTEGER NOT NULL,
score INTEGER NOT NULL,
FOREIGN KEY(quote_id) REFERENCES quotes(id)
);
"#,
));
Ok(())
}
pub fn populate_test_db(c: &Connection) -> Result<()> {
info!("Populating test db");
try!(c.execute_batch(
r#"
INSERT INTO quotes (id, approved, timestamp, author, content) VALUES
(1, 1, '2017-07-09', 'orbekk', 'test quote'),
(2, 1, '2017-07-09', 'orbekk', 'test quote2'),
(3, 0, '2017-07-09', 'orbekk', 'test quote3');
INSERT INTO votes (quote_id, score) VALUES
(1, 2),
(1, 3),
(1, 1),
(2, 3),
(2, 4);
"#,
));
Ok(())
}
fn internal_get_quotes(c: &Connection, id: Option<i64>) -> Result<Vec<Quote>> {
let mut stmt = c.prepare(
r#"
SELECT q.id, q.timestamp, q.author, q.content,
sum(v.score), count(v.score)
FROM quotes q
JOIN votes v ON (q.id = v.quote_id)
WHERE (?1 AND q.id = ?2) OR (NOT ?1 AND q.approved)
GROUP BY 1, 2, 3, 4;
ORDER BY q.id DESC;
"#,
)?;
let rows = stmt.query_map(&[&id.is_some(), &id.unwrap_or(-1)], |row| {
Quote {
id: row.get(0),
date: row.get(1),
author: row.get(2),
content: row.get(3),
score: format!("{:.2}", row.get::<i32, f64>(4) / row.get::<i32, f64>(5)),
votes: row.get(5),
}
})?;
let result = rows.map(|r| r.map_err(|e| From::from(e)))
.collect::<Result<Vec<Quote>>>();
// For some reason, the ordering from sqlite doesn't work.
result.map(|mut queries| {
queries.sort_by_key(|q| -q.id);
queries
})
}
pub fn get_quotes(c: &Connection) -> Result<Vec<Quote>> {
internal_get_quotes(c, None)
}
pub fn get_quote(c: &Connection, id: i64) -> Result<Quote> {
let quotes = internal_get_quotes(c, Some(id))?;
quotes.into_iter().next().ok_or(LinoError::NotFound(
format!("quote with id {}", id),
))
}
|
