taskforge 0.2.0

// Copyright 2018 Mathew Robinson <chasinglogic@gmail.com>. All rights reserved. Use of this source code is
// governed by the Apache-2.0 license that can be found in the LICENSE file.

//! Implement a local SQLite based List implementation

use chrono::{Local, Utc};
use list::{Error, List};
use query::ast::{Expression, AST};
use query::token::Operator;
use rusqlite::types::ToSql;
use rusqlite::{Connection, Error as SQLError, OpenFlags, Row, NO_PARAMS};
use std::path::Path;
use task::{Note, Task};

impl From<SQLError> for Error {
    fn from(e: SQLError) -> Error {
        Error::BadRequest(e.to_string())
    }
}

const CREATE_TASK_TABLE: &str = "CREATE TABLE IF NOT EXISTS tasks(
    id text PRIMARY KEY,
    title text,
    body text,
    context text,
    priority integer,
    created_date integer,
    completed_date integer
)";

const CREATE_NOTE_TABLE: &str = "CREATE TABLE IF NOT EXISTS notes(
    task_id text,
    id text,
    body text,
    created_date integer,
    FOREIGN KEY(task_id) REFERENCES tasks(id)
)";

/// SQLiteList maintains a SQLite connection and exposes a List trait
/// implementation using it.
pub struct SQLiteList {
    conn: Connection,
}

impl SQLiteList {
    /// Create a new SQLiteList using the PathBuf file_name to store
    /// the sqlite database.
    ///
    /// This method panics if for some reason file_name is
    /// cannot be accessed or created.
    pub fn new(file_name: &Path) -> SQLiteList {
        SQLiteList::open(file_name).unwrap()
    }

    /// Creates the tables necessary for this list to function. It is
    /// indempotent and so can be called multiple times on a database
    /// with no detrimental effect. For performance however one should
    /// not call this if not necessary.
    pub fn create_tables(&mut self) -> Result<(), rusqlite::Error> {
        self.conn.execute(CREATE_TASK_TABLE, NO_PARAMS)?;
        self.conn.execute(CREATE_NOTE_TABLE, NO_PARAMS).map(|_| ())
    }

    /// Open's file_name as a SQLite database and returns the
    /// SQLiteList using it. In the case of an error returns the
    /// rusqlite::Error directly.
    pub fn open(file_name: &Path) -> Result<SQLiteList, rusqlite::Error> {
        Connection::open_with_flags(
            file_name,
            OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_CREATE,
        ).map(|conn| SQLiteList { conn })
    }

    // evaluate the expression returning a SQL where clause
    fn eval(exp: Expression) -> String {
        match exp {
            // evaluate the infix
            Expression::Infix(left, op, right) => SQLiteList::eval_infix(&left, op, &right),
            Expression::String(s) => format!(
                "(title LIKE \"%{}%\" OR body LIKE \"%{}%\")",
                s.clone(),
                s.clone()
            ),
            _ => "".to_string(),
        }
    }

    fn eval_literal(exp: Expression) -> String {
        match exp {
            // evaluate literal values
            // date is a special case since fmt::Display does not
            // necessarily return a to_rfc3339 utc string. This is
            // basically just replicating what the ToSql
            // implementation does in rusqlite.
            Expression::Date(d) => format!("\"{}\"", d.with_timezone(&Utc).to_rfc3339()),
            // need to quote strings
            Expression::String(s) => format!("\"{}\"", s),
            // everything else can just use the fmt::Display implementation
            x => format!("{}", x),
        }
    }

    fn eval_infix(left: &Expression, op: Operator, right: &Expression) -> String {
        // in the case that op is a logical operator we need to evalue
        // left and right using eval as they will be further infix
        // and/or string literal expressions.
        match op {
            Operator::AND => {
                return format!(
                    "({} AND {})",
                    SQLiteList::eval(left.clone()),
                    SQLiteList::eval(right.clone()),
                )
            }
            Operator::OR => {
                return format!(
                    "({} OR {})",
                    SQLiteList::eval(left.clone()),
                    SQLiteList::eval(right.clone()),
                )
            }
            _ => (),
        };

        // get the field name for this infix
        let ident = match left {
            Expression::String(s) => match s.as_ref() {
                "completed" => "completed_date",
                s => s,
            },
            _ => "",
        };

        // get the value for this infix. In the case that this is a
        // completed = BOOL expression change it to 'is (not) null' as
        // necessary
        let val = match right {
            Expression::Bool(true) => return format!("({} is not null)", ident),
            Expression::Bool(false) => return format!("({} is null)", ident),
            x => SQLiteList::eval_literal(x.to_owned()),
        };

        // format the string literal into a SQL query. the
        // fmt::Display impelementations for Expressions evaluate to
        // valid SQL as is so we only need to handle the two special
        // cases for LIKE and NLIKE since they fmt::Display to ~ and
        // !~ respectively which is not valid SQL
        match op {
            Operator::LIKE => format!("({} LIKE \"%{}%\")", ident, val),
            Operator::NLIKE => format!("({} NOT LIKE \"%{}%\")", ident, val),
            x => format!("({} {} {})", ident, x, val),
        }
    }

    // given a task query the notes for the task out of the notes
    // table and populate the task's notes field with the results
    fn populate_notes(&mut self, task: &mut Task) -> Result<(), Error> {
        let mut stmt = self
            .conn
            .prepare("SELECT id, body, created_date FROM notes WHERE task_id = ?")?;
        let iter = stmt.query_map(&[&task.id], SQLiteList::row_to_note)?;

        // TODO: can we optimize this by resizing task.notes to
        // iter.size() ahead of time?
        for note in iter {
            match note {
                Ok(n) => task.notes.push(n),
                Err(e) => return Err(Error::from(e)),
            }
        }

        Ok(())
    }

    // convert a SQL row to tasks this assumes the column order
    // matches the field order below which for all queries currently
    // in use is true
    fn row_to_task(row: &Row) -> Task {
        Task {
            id: row.get(0),
            title: row.get(1),
            context: row.get(2),
            created_date: row.get(3),
            priority: row.get(4),
            completed_date: row.get(5),
            body: row.get(6),
            notes: vec![],
        }
    }

    // convert a SQL row to a note again assuming order matches the
    // below
    fn row_to_note(row: &Row) -> Note {
        Note {
            id: row.get(0),
            body: row.get(1),
            created_date: row.get(2),
        }
    }
}

impl List for SQLiteList {
    /// Add a task to the List
    fn add(&mut self, task: Task) -> Result<(), Error> {
        self.conn
            .execute(
                "
            INSERT INTO tasks (
                id,
                title,
                context,
                created_date,
                priority,
                completed_date,
                body
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
                &[
                    &task.id as &ToSql,
                    &task.title as &ToSql,
                    &task.context as &ToSql,
                    &task.created_date,
                    &task.priority as &ToSql,
                    &task.completed_date,
                    &task.body as &ToSql,
                ],
            ).map(|_| ())
            .map_err(Error::from)
    }

    /// Add multiple tasks to the List, should be more efficient resource
    /// utilization.
    fn add_multiple(&mut self, task: Vec<Task>) -> Result<(), Error> {
        for task in task.iter() {
            self.add(task.clone())?;
        }

        Ok(())
    }

    /// Find a task by ID
    fn find_by_id(&mut self, id: String) -> Result<Task, Error> {
        let mut task = self
            .conn
            .query_row(
                "SELECT id, title, context, created_date, priority,
                        completed_date, body
                 FROM tasks
                 WHERE id = ?1",
                &[&id],
                SQLiteList::row_to_task,
            ).map_err(Error::from)?;

        self.populate_notes(&mut task)?;
        Ok(task)
    }

    /// Return the current task, meaning the oldest uncompleted task in the List
    fn current(&mut self) -> Result<Task, Error> {
        self.conn
            .query_row(
                "SELECT id, title, context, created_date, priority,
                        completed_date, body
                FROM tasks
                WHERE completed_date is null
                ORDER BY priority DESC, created_date ASC
                LIMIT 1",
                NO_PARAMS,
                SQLiteList::row_to_task,
            ).map_err(Error::from)
    }

    /// Return the contexts used in this list
    fn contexts(&mut self) -> Result<Vec<String>, Error> {
        let mut stmt = self.conn.prepare("SELECT context FROM tasks")?;
        let iter = stmt.query_map(NO_PARAMS, |row| row.get(0))?;

        let mut contexts = Vec::new();
        for context in iter {
            match context {
                Ok(c) => contexts.push(c),
                Err(e) => return Err(Error::from(e)),
            }
        }

        Ok(contexts)
    }

    /// Complete a task by id
    fn complete(&mut self, id: String) -> Result<(), Error> {
        self.conn
            .execute(
                "UPDATE tasks SET completed_date = ?2 WHERE id = ?1",
                &[&id as &ToSql, &Local::now()],
            ).map(|_| ())
            .map_err(Error::from)
    }

    /// Update a task in the list, finding the original by the ID of the given task
    fn update(&mut self, task: Task) -> Result<(), Error> {
        self.conn
            .execute(
                "
UPDATE tasks
SET title = ?2,
    context = ?3,
    priority = ?4,
    body = ?5
WHERE id = ?1",
                &[
                    &task.id as &ToSql,
                    &task.title as &ToSql,
                    &task.context as &ToSql,
                    &task.priority as &ToSql,
                    &task.body as &ToSql,
                ],
            ).map(|_| ())
            .map_err(Error::from)
    }

    /// Add note to a task by ID
    fn add_note(&mut self, id: String, note: Note) -> Result<(), Error> {
        self.conn
            .execute(
                "INSERT INTO notes (task_id, id, body, created_date) VALUES (?1, ?2, ?3, ?4)",
                &[
                    &id as &ToSql,
                    &note.id as &ToSql,
                    &note.body as &ToSql,
                    &note.created_date,
                ],
            ).map(|_| ())
            .map_err(Error::from)
    }

    /// Interpret the ast and search for tasks which match
    fn search(&mut self, ast: AST) -> Result<Vec<Task>, Error> {
        let where_clause = match ast.expression {
            infix => SQLiteList::eval(infix),
        };

        let sql_query = if where_clause != "" {
            format!(
                "SELECT id, title, context, created_date,
                            priority, completed_date, body
                     FROM tasks
                     WHERE {}",
                where_clause
            )
        } else {
            "SELECT id, title, context, created_date, priority,
                    completed_date, body
             FROM tasks"
                .to_string()
        };

        // TODO: pre-allocate based on query return size?
        let mut tasks: Vec<Task> = Vec::new();

        {
            let mut stmt = self.conn.prepare(&sql_query)?;
            let iter = stmt
                .query_map(NO_PARAMS, SQLiteList::row_to_task)
                .map_err(Error::from)?;

            for task in iter {
                match task {
                    Ok(t) => tasks.push(t),
                    Err(e) => return Err(Error::from(e)),
                }
            }
        }

        for task in tasks.iter_mut() {
            self.populate_notes(task)?;
        }

        Ok(tasks)
    }
}

#[cfg(test)]
pub mod test {
    use super::SQLiteList;
    use list::List;
    use rand::distributions::Alphanumeric;
    use rand::Rng;
    use std::fs;
    use std::path::PathBuf;

    const TEST_DIR: &str = ".sqlite_test_tmp";

    fn setup_sqlite() -> SQLiteList {
        // ignore errors since we don't care if it already exists
        // and if we can't create it the tests will fail anyway.
        match fs::create_dir(TEST_DIR) {
            Ok(_) => (),
            Err(_) => (),
        };

        let s = rand::thread_rng()
            .sample_iter(&Alphanumeric)
            .take(10)
            .collect::<String>();

        let path = PathBuf::from(format!("{}/{}.sqlite", TEST_DIR, s));
        let mut list = SQLiteList::new(&path);
        list.create_tables().unwrap();
        list
    }

    test_list!(setup_sqlite);
}