fix: SQL model.

Add date constraint to prevent invalid dates.

1 files changed, 54 insertions, 27 deletions

diff --git a/src/data.rs b/src/data.rs
index 7f1fce8..ba821cc 100644
--- a/src/data.rs
+++ b/src/data.rs
@@ -19,10 +19,10 @@ pub fn init(c: &Connection) -> Result<()> {
         CREATE TABLE IF NOT EXISTS quotes (
           id INTEGER PRIMARY KEY AUTOINCREMENT,
           approved BOOL DEFAULT 0,
-          timestamp DATETIME NOT NULL,
+          quote_date DATE CHECK (DATE(quote_date, '+0 days') IS quote_date),
           author TEXT NOT NULL,
           content TEXT NOT NULL,
-          CONSTRAINT unique_content UNIQUE (timestamp, content)
+          CONSTRAINT unique_content UNIQUE (quote_date, content)
         );
 
         CREATE TABLE IF NOT EXISTS votes (
@@ -40,7 +40,7 @@ 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
+      INSERT INTO quotes (id, approved, quote_date, 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');
@@ -59,7 +59,7 @@ pub fn populate_test_db(c: &Connection) -> Result<()> {
 pub fn new_quote(c: &Connection, date: &str, author: &str, content: &str) -> Result<()> {
     c.execute(
         r#"
-            INSERT INTO quotes (timestamp, author, content) VALUES
+            INSERT INTO quotes (quote_date, author, content) VALUES
             (?1, ?2, ?3);
         "#,
         &[&date, &author, &content],
@@ -80,37 +80,60 @@ pub fn approve_quote(c: &Connection, quote_id: i64) -> Result<()> {
     Ok(())
 }
 
-fn internal_get_quotes(c: &Connection, id: Option<i64>, ordering: &str) -> Result<Vec<Quote>> {
+pub fn delete_quote(c: &Connection, quote_id: i64) -> Result<()> {
+    c.execute(
+        r#"
+            DELETE FROM quotes
+            WHERE id = ?1
+        "#,
+        &[&quote_id],
+    )?;
+    Ok(())
+}
+
+fn internal_get_quotes(
+    c: &Connection,
+    id: Option<i64>,
+    ordering: &str,
+    approved: bool,
+) -> Result<Vec<Quote>> {
+    // Arguments:
+    // ?1: bool, whether to query for a specific quote
+    // ?2: i64, quote id to use if ?1 is true
+    // ?3: bool, whether to fetch approved or not approved quotes
     let mut stmt = c.prepare(
         r#"
-    SELECT q.id, q.timestamp, q.author, q.content,
+    SELECT q.id, q.quote_date, 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)
+    WHERE (?1 AND q.id = ?2) OR (NOT ?1 AND q.approved = ?3)
     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 rows = stmt.query_map(
+        &[&id.is_some(), &id.unwrap_or(-1), &approved],
+        |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>>>();
@@ -130,11 +153,15 @@ fn internal_get_quotes(c: &Connection, id: Option<i64>, ordering: &str) -> Resul
 }
 
 pub fn get_quotes(c: &Connection, ordering: &str) -> Result<Vec<Quote>> {
-    internal_get_quotes(c, None, ordering)
+    internal_get_quotes(c, None, ordering, true)
+}
+
+pub fn get_pending_quotes(c: &Connection) -> Result<Vec<Quote>> {
+    internal_get_quotes(c, None, "", false)
 }
 
 pub fn get_quote(c: &Connection, id: i64) -> Result<Quote> {
-    let quotes = internal_get_quotes(c, Some(id), "")?;
+    let quotes = internal_get_quotes(c, Some(id), "", true)?;
     quotes.into_iter().next().ok_or(LinoError::NotFound(
         format!("quote with id {}", id),
     ))