diff options
Diffstat (limited to 'src/db_sqlite.py')
| -rw-r--r-- | src/db_sqlite.py | 145 |
1 files changed, 145 insertions, 0 deletions
diff --git a/src/db_sqlite.py b/src/db_sqlite.py index fd06b5a..00f4edb 100644 --- a/src/db_sqlite.py +++ b/src/db_sqlite.py @@ -6,6 +6,7 @@ from PyQt5.QtSql import QSqlDatabase, QSqlQuery import src.globals as Globals from src.group import Group from src.entry import Entry +from src.rule import Rule def initDB(): """ @@ -40,6 +41,7 @@ def createTables(): # 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 ( @@ -65,6 +67,19 @@ def createTables(): 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() @@ -118,6 +133,22 @@ def loadFromTables(): 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): @@ -195,6 +226,47 @@ def insertEntry(new_entry): 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 @@ -264,6 +336,46 @@ def updateEntry(entry): 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 @@ -322,6 +434,31 @@ def removeEntry(entry_id): 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 @@ -335,6 +472,14 @@ def cleanHidden(): 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 |