1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

// 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
        }
    }
}