add: Display quotes from sqlite.

1 files changed, 63 insertions, 23 deletions

diff --git a/src/data.rs b/src/data.rs
index 83f5360..2149a06 100644
--- a/src/data.rs
+++ b/src/data.rs
@@ -1,37 +1,40 @@
 use serde_json::{Value, Map};
 use handlebars_iron::handlebars::to_json;
 use rusqlite::Connection;
-use std;
+use error::Result;
 
-#[derive(Serialize, Debug)]
+#[derive(Serialize, Debug, Clone)]
 pub struct Quote {
-    id: u64,
-    author: String,
+    id: i64,
     date: String,
+    author: String,
     score: String,
     votes: u32,
-    content: String
+    content: String,
 }
 
 pub fn make_data() -> Map<String, Value> {
     let mut data = Map::new();
-    data.insert("quotes".to_string(), to_json(&vec!(
-        &Quote {
-            id: 1,
-            author: "panda_man".to_owned(),
-            date: "2017-07-01".to_owned(),
-            score: format!("{:.2}", 450.0 / 96.0),
-            votes: 99,
-            content: "<orbekk> hvor er jantho?".to_owned(),
-        }
-    )));
+    data.insert(
+        "quotes".to_string(),
+        to_json(&vec![
+            &Quote {
+                id: 1,
+                author: "panda_man".to_owned(),
+                date: "2017-07-01".to_owned(),
+                score: format!("{:.2}", 450.0 / 96.0),
+                votes: 99,
+                content: "<orbekk> hvor er jantho?".to_owned(),
+            },
+        ]),
+    );
     data
 }
 
-type Result<T> = std::result::Result<T, Box<std::error::Error>>;
 pub fn init(c: &Connection) -> Result<()> {
     info!("Initializing db");
-    try!(c.execute_batch(r#"
+    try!(c.execute_batch(
+        r#"
         CREATE TABLE quotes (
           id INTEGER PRIMARY KEY AUTOINCREMENT,
           approved BOOL DEFAULT false,
@@ -47,16 +50,53 @@ pub fn init(c: &Connection) -> Result<()> {
           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 (timestamp, author, content) VALUES
-        ('2017-07-09', 'orbekk', 'test quote'),
-        ('2017-07-09', 'orbekk', 'test quote2');
-    "#));
+    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),
+        (1, 4);
+    "#,
+    ));
     Ok(())
 }
+
+pub fn get_quotes(c: &Connection) -> 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
+    WHERE q.approved
+    GROUP BY 1, 2, 3, 4;
+  "#,
+    )?;
+
+    let mut rows = stmt.query_map(&[], |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
+}