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
129
130
131
132
133
134
135
136
137
138
139
140
|
use rusqlite::Connection;
use error::{Result, LinoError};
#[derive(Serialize, Debug, Clone)]
pub struct Quote {
id: i64,
date: String,
author: String,
score: String,
points: i32,
votes: i32,
content: String,
}
pub fn init(c: &Connection) -> Result<()> {
info!("Initializing db");
c.execute_batch(
r#"
CREATE TABLE IF NOT EXISTS quotes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
approved BOOL DEFAULT 0,
timestamp DATETIME NOT NULL,
author TEXT NOT NULL,
content TEXT NOT NULL,
CONSTRAINT unique_content UNIQUE (timestamp, content)
);
CREATE TABLE IF NOT EXISTS 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, 1),
(2, 2);
"#,
));
Ok(())
}
pub fn new_quote(c: &Connection, date: &str, author: &str, content: &str) -> Result<()> {
c.execute(
r#"
INSERT INTO quotes (timestamp, author, content) VALUES
(?1, ?2, ?3);
"#,
&[&date, &author, &content],
)?;
Ok(())
}
pub fn approve_quote(c: &Connection, quote_id: i64) -> Result<()> {
c.execute(
r#"
UPDATE quotes
SET approved = 1
WHERE id = ?1
"#,
&["e_id],
)?;
Ok(())
}
fn internal_get_quotes(c: &Connection, id: Option<i64>, ordering: &str) -> Result<Vec<Quote>> {
let mut stmt = c.prepare(
r#"
SELECT q.id, q.timestamp, q.author, q.content,
COALESCE(sum(v.score), 0), count(v.score)
FROM quotes q
LEFT 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| {
let points = row.get(4);
let votes = row.get(5);
let score = if votes > 0 {
format!("{:.1}", points as f64 / votes as f64)
} else {
"—".to_string()
};
Quote {
id: row.get(0),
date: row.get(1),
author: row.get(2),
content: row.get(3),
score: score,
points: points,
votes: votes,
}
})?;
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| {
if ordering == "score" {
queries.sort_by_key(|q| if q.votes > 0 {
-(q.points / q.votes)
} else {
0
});
} else {
queries.sort_by_key(|q| -q.id);
}
queries
})
}
pub fn get_quotes(c: &Connection, ordering: &str) -> Result<Vec<Quote>> {
internal_get_quotes(c, None, ordering)
}
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),
))
}
|
