diff options
-rw-r--r-- | src/data.rs | 56 |
1 files changed, 45 insertions, 11 deletions
diff --git a/src/data.rs b/src/data.rs index 396e932..d559daf 100644 --- a/src/data.rs +++ b/src/data.rs @@ -14,25 +14,25 @@ pub struct Quote { pub fn init(c: &Connection) -> Result<()> { info!("Initializing db"); - try!(c.execute_batch( + c.execute_batch( r#" - CREATE TABLE quotes ( + CREATE TABLE IF NOT EXISTS quotes ( id INTEGER PRIMARY KEY AUTOINCREMENT, - approved BOOL DEFAULT false, + approved BOOL DEFAULT 0, timestamp DATETIME NOT NULL, author TEXT NOT NULL, content TEXT NOT NULL, CONSTRAINT unique_content UNIQUE (timestamp, content) ); - CREATE TABLE votes ( + 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(()) } @@ -56,13 +56,36 @@ pub fn populate_test_db(c: &Connection) -> Result<()> { 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, - sum(v.score), count(v.score) + COALESCE(sum(v.score), 0), count(v.score) FROM quotes q - JOIN votes v ON (q.id = v.quote_id) + 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; @@ -70,14 +93,21 @@ fn internal_get_quotes(c: &Connection, id: Option<i64>, ordering: &str) -> Resul )?; 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: format!("{:.2}", row.get::<i32, f64>(4) / row.get::<i32, f64>(5)), - points: row.get(4), - votes: row.get(5), + score: score, + points: points, + votes: votes, } })?; @@ -86,7 +116,11 @@ fn internal_get_quotes(c: &Connection, id: Option<i64>, ordering: &str) -> Resul // For some reason, the ordering from sqlite doesn't work. result.map(|mut queries| { if ordering == "score" { - queries.sort_by_key(|q| -(q.points / q.votes)); + queries.sort_by_key(|q| if q.votes > 0 { + -(q.points / q.votes) + } else { + 0 + }); } else { queries.sort_by_key(|q| -q.id); } |