todo_app_server 0.1.2

// db.rs
// database related functions are implemented here 
use std::fs::create_dir;
use std::path::Path;
use std::sync::Arc;
use tokio::sync::Mutex;

use rusqlite::{Connection, named_params, Result, Row};
use rusqlite::NO_PARAMS;
use log::*;

use crate::models::{ListOptions, Todo};

pub type DbConn = Arc<Mutex<Connection>>;

// database connection
pub fn conn() -> DbConn {
    debug!("db::conn()..............<< called up");
    let folder = super::config("db_folder");
    let filename = super::config("db_filename");
    let db_init_needed = dir_created(&folder) || file_missing(&folder, &filename);
    let conn = Connection::open( file_path_string(&folder, &filename) ).unwrap(); // creates database if it is missing; then, opens a connection. 
    if db_init_needed {
        debug!("starting db init........................");
        init_db(&conn); // database does not exist, then initialize it
    }
    Arc::new(Mutex::new(conn))
}

// database setup
fn init_db(conn: &Connection) {
    match create_todo_table(conn) {
        Ok(count) => debug!("{:?} todo table is created", count),
        Err(message) => error!("{:?} occurred while creating todo table", message),
    }
}

// database folder and file 
fn dir_created(folder: &str) -> bool {
    if Path::new(&folder).is_dir() { // checks whether folder already exists, if so, return false
        return false
    }
    debug!("{} folder is CREATED successfully", &folder);
    create_dir(&folder).unwrap();
    true
}
fn file_missing(folder: &str, filename: &str) -> bool {
    let path = file_path_string(folder, filename);
    !Path::new(&path).is_file()
}
fn file_path_string(folder: &str, filename: &str) -> String {
    folder.to_owned() + &"/".to_string() + &filename
}

// Table(s) creation 
fn create_todo_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "CREATE TABLE IF NOT EXISTS todo(
            id INTEGER PRIMARY KEY,
            text TEXT,
            completed BOOLEAN
        )",
        NO_PARAMS,
    )?;
    Ok(count)
}

// Actions on Todo table
pub async fn get_all_todos(opts: ListOptions, db_conn: DbConn) -> Vec<Todo> {
    debug!("get_all_todos().....................<<");
    let conn = db_conn.lock().await;
    let mut stmt = conn.prepare(
        "SELECT id, text, completed 
         FROM todo 
         ORDER BY id DESC
         LIMIT :limit 
         OFFSET :offset").unwrap();
    let rows = stmt.query_map_named(
        &[
            (":offset", &opts.offset.unwrap_or(0)),
            (":limit", &opts.limit.unwrap_or(std::u32::MAX)),
        ],
        |row| extract_todo_data(row)
    ).unwrap();
    let mut todos = Vec::new();
    for each_row in rows {
        todos.push(each_row.unwrap());
    }
    debug!("todos are: {:?}", todos);
    todos
}

fn extract_todo_data(row: &Row) -> Result<Todo> {
    Ok( Todo {
        id: row.get(0)?,
        text: row.get(1)?,
        completed: row.get(2)?,
    })
}

pub async fn add_todo(todo: &Todo, db_conn: DbConn) -> usize {
    let conn = db_conn.lock().await;
    let row_count = conn.execute_named(
        "INSERT INTO todo (
            text,
            completed
        ) VALUES (
            :text,
            :completed
        )",
        named_params!{
            ":text": todo.text,
            ":completed": todo.completed
        }
    ).unwrap();
    row_count
}

pub async fn get_todo(id: u32, db_conn: DbConn) -> Todo {
    debug!("get_todo({}).....................<<", id);
    let conn = db_conn.lock().await;
    let todo = conn.query_row_named(
        "SELECT id, text, completed 
         FROM todo 
         WHERE id = :id",
        &[ (":id", &id) ],
        |row| extract_todo_data(row)
    ).unwrap();
    debug!("todo is: {:?}", todo);
    todo
}

pub async fn update_todo(todo: &Todo, db_conn: DbConn) -> usize {
    debug!("update_todo(..)...............<<");
    let sql_str = "UPDATE todo
        SET text = :text, completed = :completed
        WHERE id = :id";
    let conn = db_conn.lock().await;
    let row_count = conn.execute_named(
        &sql_str,
        &[
            (":id", &todo.id),
            (":text", &todo.text),
            (":completed", &todo.completed),
        ]
    ).unwrap();
    row_count
}

pub async fn delete_todo(id: u32, db_conn: DbConn) -> usize {
    debug!("delete todo(..)................<<");
    let conn = db_conn.lock().await;
    let result = conn.execute_named(
        "DELETE FROM todo where id = :id",
        named_params!{ ":id": id }
    );
    match result {
        Ok(row_count) => row_count,
        Err(message) => {
            error!("{:?} occurred in delete_todo({:?}", message, id);
            0
        }
    }
}