diff options
Diffstat (limited to 'assignment_list_pyqt/db_sqlite.py')
| -rw-r--r-- | assignment_list_pyqt/db_sqlite.py | 493 |
1 files changed, 493 insertions, 0 deletions
diff --git a/assignment_list_pyqt/db_sqlite.py b/assignment_list_pyqt/db_sqlite.py new file mode 100644 index 0000000..b7bed41 --- /dev/null +++ b/assignment_list_pyqt/db_sqlite.py @@ -0,0 +1,493 @@ +import os +import sys +from time import strptime +from PyQt5.QtCore import QDate +from PyQt5.QtSql import QSqlDatabase, QSqlQuery +import assignment_list_pyqt.globals as Globals +from assignment_list_pyqt.group import Group +from assignment_list_pyqt.entry import Entry +from assignment_list_pyqt.rule import Rule + +def initDB(): + """ + Check for existing database. If it doesn't exist, build it + """ + if not os.path.exists(Globals.db_path) or not os.stat(Globals.db_path).st_size: + createTables() + + loadFromTables() + +def createTables(): + """ + Create database at a specified Globals.db_path + """ + print(Globals.db_path) + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + # Create database parent directory if necessary + if not os.path.exists(os.path.dirname(Globals.db_path)): + try: + os.mkdir(os.path.dirname(Globals.db_path)) + except: + print("Unable to open data source file.") + sys.exit(1) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + # Erase database contents so that we don't have duplicates + query.exec_("DROP TABLE groups") + query.exec_("DROP TABLE entries") + query.exec_("DROP TABLE rules") + + query.exec_(""" + CREATE TABLE groups ( + id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, + name VARCHAR(255) NOT NULL, + column TINYINT(1) DEFAULT FALSE, + link VARCHAR(255) NOT NULL, + hidden TINYINT(1) DEFAULT FALSE + ) + """) + + query.exec_(""" + CREATE TABLE entries ( + id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, + parent_id REFERENCES groups (id), + description VARCHAR(255) NOT NULL, + due_date TEXT DEFAULT NULL, + alt_due_date VARCHAR(255) DEFAULT NULL, + link VARCHAR(255) DEFAULT NULL, + color VARCHAR(255) DEFAULT NULL, + highlight VARCHAR(255) DEFAULT NULL, + done TINYINT(1) DEFAULT FALSE, + hidden TINYINT(1) DEFAULT FALSE + ) + """) + + query.exec_(""" + CREATE TABLE rules ( + id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, + entry_id REFERENCES entries (id), + before_after TINYINT(1) DEFAULT TRUE, + date TEXT NOT NULL, + color VARCHAR(255) DEFAULT NULL, + highlight VARCHAR(255) DEFAULT NULL + ) + """) + + print(database.lastError().text()) + + database.close() + +def loadFromTables(): + """ + Load groups and entries into global variables + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + # Load groups + Globals.groups = [] # Reset local groups array + query.exec_("SELECT * FROM groups") + while query.next(): + record = query.record() + Globals.groups.append( + Group( + record.field("id").value(), + record.field("name").value(), + record.field("column").value(), + record.field("link").value(), + record.field("hidden").value())) + + # Load entries + Globals.entries = [] # Reset local entries array + query.exec_("SELECT * FROM entries") + while query.next(): + record = query.record() + # create a QDate if the due date is set + if record.field("due_date").value(): + due_date_struct = strptime(record.field("due_date").value(), "%Y-%m-%d") + due_date = QDate(due_date_struct.tm_year, due_date_struct.tm_mon, due_date_struct.tm_mday) + else: + due_date = "" + Globals.entries.append( + Entry( + record.field("id").value(), + record.field("parent_id").value(), + record.field("description").value(), + due_date, + record.field("alt_due_date").value(), + record.field("link").value(), + record.field("color").value(), + record.field("highlight").value(), + record.field("done").value(), + record.field("hidden").value())) + + # Load rules + Globals.rules = [] # Reset local rules array + query.exec_("SELECT * FROM rules") + while query.next(): + record = query.record() + date_struct = strptime(record.field("date").value(), "%Y-%m-%d") + date = QDate(date_struct.tm_year, date_struct.tm_mon, date_struct.tm_mday) + Globals.rules.append( + Rule( + record.field("id").value(), + record.field("entry_id").value(), + "before" if record.field("before_after").value() == 0 else "after", + date, + record.field("color").value(), + record.field("highlight").value())) + + database.close() + +def insertGroup(new_group): + """ + Insert group to the database at Globals.db_path + """ + output = -1 + + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + INSERT INTO groups (name, column, link) VALUES (?, ?, ?) + """) + query.addBindValue(new_group.name) + query.addBindValue(new_group.column) + query.addBindValue(new_group.link) + query.exec_() + + output = query.lastInsertId() + + database.close() + + return output + +def insertEntry(new_entry): + """ + Insert entry to the database at Globals.db_path + """ + output = -1 + + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + INSERT INTO entries (parent_id, description, due_date, alt_due_date, link, color, highlight) VALUES (:p_id, :desc, :due, :alt_due, :link, :color, :highlight) + """) + query.bindValue(":p_id", new_entry.parent_id) + query.bindValue(":desc", new_entry.desc) + if new_entry.due: + query.bindValue(":due", "{0}-{1}-{2}".format( + new_entry.due.year(), + new_entry.due.month(), + new_entry.due.day())) + else: + query.bindValue(":due", "") + query.bindValue(":alt_due", new_entry.due_alt) + query.bindValue(":link", new_entry.link) + query.bindValue(":color", new_entry.color) + query.bindValue(":highlight", new_entry.highlight) + success = query.exec_() + # DEBUG + #print(query.lastError().text()) + #print(query.boundValues()) + #if success: + # print("Query succeeded") + #else: + # print("Query failed") + + output = query.lastInsertId() + + database.close() + + return output + +def insertRule(new_rule): + """ + Insert rule to the database at Globals.db_path + """ + output = -1 + + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + INSERT INTO rules (entry_id, before_after, date, color, highlight) VALUES (:e_id, :when, :date, :color, :highlight) + """) + query.bindValue(":e_id", new_rule.entry_id) + query.bindValue(":when", 0 if new_rule.when.lower() == "before" else 1) + query.bindValue(":date", "{0}-{1}-{2}".format( + new_rule.date.year(), + new_rule.date.month(), + new_rule.date.day())) + query.bindValue(":color", new_rule.color) + query.bindValue(":highlight", new_rule.highlight) + success = query.exec_() + # DEBUG + #print(query.lastError().text()) + #print(query.boundValues()) + #if success: + # print("Query succeeded") + #else: + # print("Query failed") + + output = query.lastInsertId() + + database.close() + + return output + +def updateGroup(group): + """ + Update group by its id + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + UPDATE groups SET name = ?, column = ?, link = ?, hidden = ? WHERE id = ? + """) + query.addBindValue(group.name) + query.addBindValue(group.column) + query.addBindValue(group.link) + query.addBindValue(group.hidden) + query.addBindValue(group.id) + query.exec_() + + database.close() + +def updateEntry(entry): + """ + Update entry by its id + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + UPDATE entries SET + description = :desc, + due_date = :due, + alt_due_date = :alt_due, + link = :link, + color = :color, + highlight = :highlight, + done = :done, + hidden = :hidden + WHERE id = :id + """) + query.bindValue(":desc", entry.desc) + if entry.due: + query.bindValue(":due", "{0}-{1}-{2}".format( + entry.due.year(), + entry.due.month(), + entry.due.day())) + else: + query.bindValue(":due", "") + query.bindValue(":alt_due", entry.due_alt) + query.bindValue(":link", entry.link) + query.bindValue(":color", entry.color) + query.bindValue(":highlight", entry.highlight) + query.bindValue(":done", entry.done) + query.bindValue(":hidden", entry.hidden) + query.bindValue(":id", entry.id) + query.exec_() + + database.close() + +def updateRule(rule): + """ + Update rule by its id + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + query.prepare(""" + UPDATE rules SET + before_after = :when, + date = :date, + color = :color, + highlight = :highlight + WHERE id = :id + """) + query.bindValue(":when", 0 if rule.when.lower() == "before" else 1) + query.bindValue(":date", "{0}-{1}-{2}".format( + rule.date.year(), + rule.date.month(), + rule.date.day())) + query.bindValue(":color", rule.color) + query.bindValue(":highlight", rule.highlight) + query.bindValue(":id", rule.id) + success = query.exec_() + # DEBUG + #print(query.lastError().text()) + #print(query.boundValues()) + #if success: + # print("Query succeeded") + #else: + # print("Query failed") + + database.close() + +def removeGroup(group_id): + """ + Remove a group by id from the database + (actually set hidden to true, don't permanently delete it) + """ + + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + # First, set entries to hidden + query.prepare(""" + UPDATE entries SET hidden = 1 WHERE parent_id = ? + """) + query.addBindValue(group_id) + query.exec_() + + # Now, set the group to hidden + query.prepare(""" + UPDATE groups SET hidden = 1 WHERE id = ? + """) + query.addBindValue(group_id) + query.exec_() + + output = query.numRowsAffected() + database.close() + return output + +def removeEntry(entry_id): + """ + Remove a group by id from the database + (actually set hidden to true, don't permanently delete it) + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + # Set entry to hidden + query.prepare(""" + UPDATE entries SET hidden = 1 WHERE id = ? + """) + query.addBindValue(entry_id) + query.exec_() + + output = query.numRowsAffected() + database.close() + return output + +def removeRule(rule_id): + """ + Remove a rule by id from the database + (we do not preserve rules, unlike groups and entries) + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + # Set entry to hidden + query.prepare(""" + DELETE FROM rules WHERE id = ? + """) + query.addBindValue(rule_id) + query.exec_() + + output = query.numRowsAffected() + database.close() + return output + +def cleanHidden(): + """ + Permanently delete removed/hidden groups and entries + """ + database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3 + database.setDatabaseName(Globals.db_path) + + if not database.open(): + print("Unable to open data source file.") + sys.exit(1) # Error out. TODO consider throwing a dialog instead + + query = QSqlQuery() + + # Remove rules associated with hidden entries + query.exec_(""" + DELETE FROM rules WHERE id IN ( + SELECT rules.id FROM rules + INNER JOIN entries ON rules.entry_id = entries.id + WHERE entries.hidden = 1 + )""") + + # Remove hidden entries + query.exec_(""" + DELETE FROM entries WHERE hidden = 1 + """) + + # Remove hidden groups + query.exec_(""" + DELETE FROM groups WHERE hidden = 1 + """) + + database.close() |