From 4b99428a0e73c9e573624b2784464ce8ae632e33 Mon Sep 17 00:00:00 2001 From: Louie S Date: Thu, 29 Feb 2024 18:01:05 -0500 Subject: Rewrite db_sqlite as class --- src/backend/db_sqlite.cpp | 177 ++++++++++++++++++++++++---------------------- src/backend/db_sqlite.h | 94 ++++++++++++------------ 2 files changed, 142 insertions(+), 129 deletions(-) (limited to 'src/backend') diff --git a/src/backend/db_sqlite.cpp b/src/backend/db_sqlite.cpp index 4479b09..7176a95 100644 --- a/src/backend/db_sqlite.cpp +++ b/src/backend/db_sqlite.cpp @@ -13,31 +13,7 @@ #include "db_sqlite.h" -QString getDBPath(); -QSqlDatabase openDB(); - -QString getDBPath() { - QSettings settings; - settings.beginGroup("paths"); - return settings.value("db_path").toString(); -} - - -QSqlDatabase openDB() { - QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE"); - database.setDatabaseName(getDBPath()); - - database.open(); - if(database.isOpenError()) { - // FIXME end-user friendly error message - qDebug() << database.lastError(); - std::exit(1); - } - - return database; -} - -void BackendDB::init() { +BackendDB::BackendDB() { QString db_path(getDBPath()); int i; @@ -54,101 +30,134 @@ void BackendDB::init() { } // Create database - QSqlDatabase database = openDB(); - QSqlQuery query; - - // 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"); + { + QSqlDatabase database(this->openDB()); + QSqlQuery query; - for(i = 0; i < BackendDB::create_table_queries.length(); ++i) - query.exec(BackendDB::create_table_queries[i]); + // 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"); - qDebug() << database.lastError(); + for(i = 0; i < BackendDB::create_table_queries.length(); ++i) + query.exec(BackendDB::create_table_queries[i]); + } - database.close(); + QSqlDatabase::removeDatabase("qt_sql_default_connection"); } // load groups QList BackendDB::loadGroups() { - QSqlDatabase database = openDB(); - QSqlQuery query; QList output; - query.exec("SELECT * FROM groups"); - while(query.next()) { - output.append(new Group( - query.record().field("id").value().toInt(), - query.record().field("name").value().toString(), - query.record().field("column").value().toString(), - query.record().field("link").value().toString(), - query.record().field("hidden").value().toBool())); + { + QSqlDatabase database(this->openDB()); + QSqlQuery query; + + query.exec("SELECT * FROM groups"); + while(query.next()) { + output.append(new Group( + query.record().field("id").value().toInt(), + query.record().field("name").value().toString(), + query.record().field("column").value().toString(), + query.record().field("link").value().toString(), + query.record().field("hidden").value().toBool())); + } } - database.close(); + QSqlDatabase::removeDatabase("qt_sql_default_connection"); return output; } // load entries QList BackendDB::loadEntries() { - QSqlDatabase database = openDB(); - QSqlQuery query; QList output; - query.exec("SELECT * FROM groups"); - while(query.next()) { - output.append(new Entry( - query.record().field("id").value().toInt(), - query.record().field("parent_id").value().toInt(), - query.record().field("description").value().toString(), - query.record().field("due_date").value().toDateTime(), - query.record().field("alt_due_date").value().toString(), - query.record().field("link").value().toUrl(), - query.record().field("color").value().toString(), - query.record().field("highlight").value().toString(), - query.record().field("done").value().toBool(), - query.record().field("hidden").value().toBool())); + { + QSqlDatabase database(this->openDB()); + QSqlQuery query; + + query.exec("SELECT * FROM groups"); + while(query.next()) { + output.append(new Entry( + query.record().field("id").value().toInt(), + query.record().field("parent_id").value().toInt(), + query.record().field("description").value().toString(), + query.record().field("due_date").value().toDateTime(), + query.record().field("alt_due_date").value().toString(), + query.record().field("link").value().toUrl(), + query.record().field("color").value().toString(), + query.record().field("highlight").value().toString(), + query.record().field("done").value().toBool(), + query.record().field("hidden").value().toBool())); + } } - database.close(); + QSqlDatabase::removeDatabase("qt_sql_default_connection"); return output; } // load entries QList BackendDB::loadRules() { - QSqlDatabase database = openDB(); - QSqlQuery query; QList output; - query.exec("SELECT * FROM groups"); - while(query.next()) { - output.append(new Rule( - query.record().field("id").value().toInt(), - query.record().field("entry_id").value().toInt(), - (Rule::When) query.record().field("before_after").value().toInt(), - query.record().field("date").value().toDateTime(), - query.record().field("color").value().toString(), - query.record().field("highlight").value().toString())); + { + QSqlDatabase database(this->openDB()); + QSqlQuery query; + + query.exec("SELECT * FROM groups"); + while(query.next()) { + output.append(new Rule( + query.record().field("id").value().toInt(), + query.record().field("entry_id").value().toInt(), + (Rule::When) query.record().field("before_after").value().toInt(), + query.record().field("date").value().toDateTime(), + query.record().field("color").value().toString(), + query.record().field("highlight").value().toString())); + } } - database.close(); + QSqlDatabase::removeDatabase("qt_sql_default_connection"); return output; } // insert group to the database (returns 0 if failed) int BackendDB::insertGroup(const Group &new_group) { - QSqlDatabase database = openDB(); - QSqlQuery query; int output; - query.prepare("INSERT INTO groups (name, column, link) VALUES (?, ?, ?)"); - query.bindValue(0, new_group.name); - query.bindValue(1, new_group.column); - query.bindValue(2, new_group.link); - query.exec(); + { + QSqlDatabase database(this->openDB()); + QSqlQuery query; + + query.prepare("INSERT INTO groups (name, column, link) VALUES (?, ?, ?)"); + query.bindValue(0, new_group.name); + query.bindValue(1, new_group.column); + query.bindValue(2, new_group.link); + query.exec(); - output = query.lastInsertId().toInt(); - database.close(); + output = query.lastInsertId().toInt(); + } + + QSqlDatabase::removeDatabase("qt_sql_default_connection"); return output; } + +QString BackendDB::getDBPath() { + QSettings settings; + settings.beginGroup("paths"); + return settings.value("db_path").toString(); +} + +QSqlDatabase BackendDB::openDB() { + QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE"); + database.setDatabaseName(getDBPath()); + + database.open(); + if(database.isOpenError()) { + // FIXME end-user friendly error message + qDebug() << database.lastError(); + std::exit(1); + } + + return database; +} diff --git a/src/backend/db_sqlite.h b/src/backend/db_sqlite.h index fb04c00..bec534b 100644 --- a/src/backend/db_sqlite.h +++ b/src/backend/db_sqlite.h @@ -3,58 +3,62 @@ #include #include +#include #include "../entry.h" #include "../group.h" #include "../rule.h" -// TODO rewrite to be a class (see difference between ::addDatabase and ::database) +class BackendDB : QSqlDatabase { + public: + BackendDB(); + QList loadGroups(); + QList loadEntries(); + QList loadRules(); + int insertGroup(const Group &new_group); + int insertEntry(int new_entry); // param datatype TBD + int insertRule(int new_rule); // param datatype TBD + void updateGroup(int group); // param datatype TBD + void updateEntry(int entry); // param datatype TBD + void updateRule(int rule); // param datatype TBD + void removeGroup(int group); // param datatype TBD + void removeEntry(int entry); // param datatype TBD + void removeRule(int rule); // param datatype TBD + void cleanHidden(); -namespace BackendDB { - const QStringList create_table_queries = { - "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" - ")", - "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" - ")", - "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" - ")" - }; + private: + const QStringList create_table_queries = { + "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" + ")", + "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" + ")", + "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" + ")" + }; - void init(); - QList loadGroups(); - QList loadEntries(); - QList loadRules(); - int insertGroup(const Group &new_group); // param datatype TBD - int insertEntry(int new_entry); // param datatype TBD - int insertRule(int new_rule); // param datatype TBD - void updateGroup(int group); // param datatype TBD - void updateEntry(int entry); // param datatype TBD - void updateRule(int rule); // param datatype TBD - void removeGroup(int group); // param datatype TBD - void removeEntry(int entry); // param datatype TBD - void removeRule(int rule); // param datatype TBD - void cleanHidden(); + QString getDBPath(); + QSqlDatabase openDB(); }; #endif -- cgit