From 74c3687fa7b25c24131cc7530e35095b22399d64 Mon Sep 17 00:00:00 2001 From: Eduardo Date: Thu, 28 Sep 2023 18:35:38 +0200 Subject: [PATCH] todo tasks functions --- singletons/database_dao.gd | 105 ++++++++++++++++++++++++++++++------- 1 file changed, 86 insertions(+), 19 deletions(-) diff --git a/singletons/database_dao.gd b/singletons/database_dao.gd index eb29f5a..978ccee 100644 --- a/singletons/database_dao.gd +++ b/singletons/database_dao.gd @@ -11,13 +11,13 @@ func _get_instance() -> SQLite: _db = SQLite.new() _db.path = DB_NAME _db.verbosity_level = VERBOSITY_LELVEL - + var success := _db.open_db() if not success: printerr("Failed to open database: ", DB_NAME) - + _migrate() - + return _db func _is_instanced() -> bool: @@ -36,14 +36,14 @@ func instanciate(): ## get todo list ## if done is null will return all tasks, else it will return the done ones or not done ones func get_todo_tasks(done = null) -> Array[Dictionary]: - var query := "SELECT task, done, position, priority FROM todo" + var query := "SELECT rowid, task, done, position, priority FROM todo" if done: query += " WHERE done = 1;" elif done == false: query += " WHERE done = 0;" else: query += ";" - + var success := _get_instance().query(query) if success: return _get_instance().query_result @@ -51,6 +51,68 @@ func get_todo_tasks(done = null) -> Array[Dictionary]: printerr("Query didnt work (DB.get_todo_tasks()): ", query) return [Dictionary()] +## add a task to the todo list +## if done correctly will return the inserted task, else a blank Dictionary +func add_todo_task(task: String, priority: int, position: int = 0) -> Dictionary: + const TABLE := "todo" + var data := { + "task": task, + "priority": priority, + "position": position + } + var success := _get_instance().insert_row(TABLE, data) + if success: + var query := "SELECT rowid, task, done, position, priority FROM todo WHERE rowid = " + String.num(_get_instance().last_insert_rowid) + ";" + success = _get_instance().query(query) + if success: + return _get_instance().query_result[0] + + # else (if no returned data because some success was not success) + printerr("Query didnt work (DB.add_todo_task()): ", data) + return Dictionary() + +## edit a task from the todo list +## if done correctly will return the edited task, else a blank Dictionary +func edit_todo_task(task_id: int = -1, done: int = -1, task: String = "", priority: int = -1, position: int = -1) -> Dictionary: + const TABLE := "todo" + var data: Dictionary = {} + + if task_id != -1: + data["rowid"] = task_id + if done != -1: + data["done"] = done + if task != "": + data["task"] = task + if priority != -1: + data["priority"] = priority + if position != -1: + data["position"] = position + + var success = _get_instance().update_rows(TABLE, "", Dictionary()) + if success: + var query := "SELECT rowid, task, done, position, priority FROM todo WHERE rowid = " + String.num(_get_instance().last_insert_rowid) + ";" + success = _get_instance().query(query) + if success: + return _get_instance().query_result[0] + + # else (if no returned data because some success was not success) + printerr("Query didnt work (DB.edit_todo_task()): ", data) + return Dictionary() + +## marks a task as completed based on its rowId +## if done correctly will return the edited task, else a blank Dictionary +func complete_todo_task(task_id: int) -> Dictionary: + return edit_todo_task(task_id, 1) + +## marks a task as un-completed based on its rowId +## if done correctly will return the edited task, else a blank Dictionary +func uncomplete_todo_task(task_id: int) -> Dictionary: + return edit_todo_task(task_id, 0) + +## marks a task as completed based on its rowId +## if done correctly will return true, else false +func delete_todo_task(task_id: int) -> bool: + return _get_instance().delete_rows("todo", "rowid = " + String.num(task_id)) ## MIGRATIONS @@ -60,21 +122,18 @@ func _migrate(): var success := _db.query("SELECT value FROM metadata WHERE key = 'version';") if success: current_version = int(_db.query_result[0]["value"]) - + if current_version < DB_VERSION: while current_version < DB_VERSION: match current_version: # 0 means there was no database, so we generate v1 - 0: - _migrate_database_from_0_to_1() - + 0: _migrate_database_from_0_to_1() # upgrade from v1 to v2 - 1: - pass + 1: pass # ... - + current_version = current_version + 1 - + # save to database the new version _db.update_rows("metadata", "key='version'", {"value": current_version}) @@ -86,19 +145,19 @@ func _migrate_database_from_0_to_1(): "key": { "data_type": "text", "primary_key": true - }, + }, "value": {"data_type": "text"} } var data := [ {"key": "version", "value": "1"}, {"key": "install_time", "value": Time.get_unix_time_from_system()} ] - + _db.create_table(table_name, columns) - + for row in data: _db.insert_row(table_name, row) - + # todo table table_name = "todo" columns = { @@ -109,9 +168,17 @@ func _migrate_database_from_0_to_1(): "created_at": {"data_type": "text"}, "updated_at": {"data_type": "text"} } - + data = [ + {"task": "Pick the laundry", "priority": 2, "created_at": Time.get_unix_time_from_system()}, + {"task": "Feed the bunnuy", "priority": 3, "created_at": Time.get_unix_time_from_system()}, + {"task": "Wash the dishes", "priority": 1, "created_at": Time.get_unix_time_from_system()}, + ] + _db.create_table(table_name, columns) - + + for row in data: + _db.insert_row(table_name, row) + # calendar table table_name = "calendar" columns = { -- 2.30.2