add: Functions to add quotes in db.

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
+        "#,
+        &[&quote_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);
         }