diff options
Diffstat (limited to 'db_sqlite.py')
| -rw-r--r-- | db_sqlite.py | 346 |
1 files changed, 0 insertions, 346 deletions
diff --git a/db_sqlite.py b/db_sqlite.py deleted file mode 100644 index af7f185..0000000 --- a/db_sqlite.py +++ /dev/null @@ -1,346 +0,0 @@ -import os -import sys -from time import strptime -from PyQt5.QtCore import QDate -from PyQt5.QtSql import QSqlDatabase, QSqlQuery -Globals = __import__("globals") -from group import Group -from entry import Entry - -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_(""" - 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 - ) - """) - - 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 - 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 - 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())) - - 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 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 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 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 hidden entries - query.exec_(""" - DELETE FROM entries WHERE hidden = 1 - """) - - # Remove hidden groups - query.exec_(""" - DELETE FROM groups WHERE hidden = 1 - """) - - database.close() |