summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/data.rs81
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),
))