diff options
| -rw-r--r-- | src/data.rs | 81 |
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 + "#, + &["e_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), )) |