From aeef1c671de0de54b327c436bcdff92a082b1612 Mon Sep 17 00:00:00 2001 From: Shane Jaroch Date: Wed, 21 Jan 2026 17:42:00 -0500 Subject: [PATCH] update db stuff --- include/db/databasemanager.h | 11 +- lib/ntsqlite | 2 +- lib/usdasqlite | 2 +- src/db/databasemanager.cpp | 212 +++++++++++++++-------------------- src/main.cpp | 12 ++ src/mainwindow.cpp | 98 +++++++++++++--- 6 files changed, 202 insertions(+), 135 deletions(-) diff --git a/include/db/databasemanager.h b/include/db/databasemanager.h index 764eeb4..3386a42 100644 --- a/include/db/databasemanager.h +++ b/include/db/databasemanager.h @@ -8,10 +8,20 @@ class DatabaseManager { public: static DatabaseManager& instance(); + static constexpr int CURRENT_SCHEMA_VERSION = 9; bool connect(const QString& path); [[nodiscard]] bool isOpen() const; [[nodiscard]] QSqlDatabase database() const; [[nodiscard]] QSqlDatabase userDatabase() const; + bool isValidNutraDatabase(const QSqlDatabase& db); + + struct DatabaseInfo { + bool isValid; + QString type; // "USDA" or "User" + int version; + }; + + DatabaseInfo getDatabaseInfo(const QString& path); DatabaseManager(const DatabaseManager&) = delete; DatabaseManager& operator=(const DatabaseManager&) = delete; @@ -21,7 +31,6 @@ private: ~DatabaseManager(); void initUserDatabase(); - bool isValidNutraDatabase(const QSqlDatabase& db); QSqlDatabase m_db; QSqlDatabase m_userDb; diff --git a/lib/ntsqlite b/lib/ntsqlite index 4f9eec2..a9d5c46 160000 --- a/lib/ntsqlite +++ b/lib/ntsqlite @@ -1 +1 @@ -Subproject commit 4f9eec211c1073f093411f15e29cb2347534e7cd +Subproject commit a9d5c4650928d27b43a9a99562192fbcb90d5bbf diff --git a/lib/usdasqlite b/lib/usdasqlite index 4644288..8324bf3 160000 --- a/lib/usdasqlite +++ b/lib/usdasqlite @@ -1 +1 @@ -Subproject commit 4644288e1cfc0bac44dd3a0bfdaafa5bd72cf819 +Subproject commit 8324bf302bc2417dbee8f5c7280acaaef620fd65 diff --git a/src/db/databasemanager.cpp b/src/db/databasemanager.cpp index 0ffa993..e4d417f 100644 --- a/src/db/databasemanager.cpp +++ b/src/db/databasemanager.cpp @@ -75,6 +75,41 @@ QSqlDatabase DatabaseManager::userDatabase() const { return m_userDb; } +DatabaseManager::DatabaseInfo DatabaseManager::getDatabaseInfo(const QString& path) { + DatabaseInfo info{false, "Unknown", 0}; + + if (!QFileInfo::exists(path)) return info; + + { + QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "info_connection"); + db.setDatabaseName(path); + if (db.open()) { + QSqlQuery query(db); + + // Get Version + if (query.exec("PRAGMA user_version") && query.next()) { + info.version = query.value(0).toInt(); + } + + // Determine Type + bool hasFoodDes = query.exec("SELECT 1 FROM food_des LIMIT 1"); + bool hasLogFood = query.exec("SELECT 1 FROM log_food LIMIT 1"); + + if (hasFoodDes) { + info.type = "USDA"; + info.isValid = true; + } else if (hasLogFood) { + info.type = "User"; + info.isValid = true; + } + + db.close(); + } + } + QSqlDatabase::removeDatabase("info_connection"); + return info; +} + void DatabaseManager::initUserDatabase() { QString dirPath = QDir::homePath() + "/.nutra"; QDir().mkpath(dirPath); @@ -88,126 +123,65 @@ void DatabaseManager::initUserDatabase() { QSqlQuery query(m_userDb); - // Helper to execute schema creation - auto createTable = [&](const QString& sql) { - if (!query.exec(sql)) { - qCritical() << "Failed to create table:" << query.lastError().text() << "\nSQL:" << sql; + // Check version + int schemaVersionOnDisk = 0; + if (query.exec("PRAGMA user_version") && query.next()) { + schemaVersionOnDisk = query.value(0).toInt(); + } + + qDebug() << "User database version:" << schemaVersionOnDisk; + + if (schemaVersionOnDisk == 0) { + // Initialize from tables.sql + // In a real deployed app, this file should be in a resource (.qrc) or installed path + // For now, we look in the submodule path if running from source, or a known fallback + QString schemaPath = QDir::currentPath() + "/lib/ntsqlite/sql/tables.sql"; + if (!QFileInfo::exists(schemaPath)) { + // Fallback for installed location (adjust as needed for packaging) + schemaPath = "/usr/share/nutra/sql/tables.sql"; } - }; - - createTable( - "CREATE TABLE IF NOT EXISTS version (" - "id integer PRIMARY KEY AUTOINCREMENT, " - "version text NOT NULL UNIQUE, " - "created date NOT NULL, " - "notes text)"); - - createTable( - "CREATE TABLE IF NOT EXISTS bmr_eq (" - "id integer PRIMARY KEY, " - "name text NOT NULL UNIQUE)"); - - createTable( - "CREATE TABLE IF NOT EXISTS bf_eq (" - "id integer PRIMARY KEY, " - "name text NOT NULL UNIQUE)"); - - createTable( - "CREATE TABLE IF NOT EXISTS profile (" - "id integer PRIMARY KEY AUTOINCREMENT, " - "uuid int NOT NULL DEFAULT (RANDOM()), " - "name text NOT NULL UNIQUE, " - "gender text, " - "dob date, " - "act_lvl int DEFAULT 2, " - "goal_wt real, " - "goal_bf real DEFAULT 18, " - "bmr_eq_id int DEFAULT 1, " - "bf_eq_id int DEFAULT 1, " - "created int DEFAULT (strftime ('%s', 'now')), " - "FOREIGN KEY (bmr_eq_id) REFERENCES bmr_eq (id) ON UPDATE " - "CASCADE ON DELETE CASCADE, " - "FOREIGN KEY (bf_eq_id) REFERENCES bf_eq (id) ON UPDATE CASCADE " - "ON DELETE CASCADE)"); - - createTable( - "CREATE TABLE IF NOT EXISTS rda (" - "profile_id int NOT NULL, " - "nutr_id int NOT NULL, " - "rda real NOT NULL, " - "PRIMARY KEY (profile_id, nutr_id), " - "FOREIGN KEY (profile_id) REFERENCES profile (id) ON UPDATE " - "CASCADE ON DELETE CASCADE)"); - - createTable( - "CREATE TABLE IF NOT EXISTS custom_food (" - "id integer PRIMARY KEY AUTOINCREMENT, " - "tagname text NOT NULL UNIQUE, " - "name text NOT NULL UNIQUE, " - "created int DEFAULT (strftime ('%s', 'now')))"); - - createTable( - "CREATE TABLE IF NOT EXISTS cf_dat (" - "cf_id int NOT NULL, " - "nutr_id int NOT NULL, " - "nutr_val real NOT NULL, " - "notes text, " - "created int DEFAULT (strftime ('%s', 'now')), " - "PRIMARY KEY (cf_id, nutr_id), " - "FOREIGN KEY (cf_id) REFERENCES custom_food (id) ON UPDATE " - "CASCADE ON DELETE CASCADE)"); - - createTable( - "CREATE TABLE IF NOT EXISTS meal_name (" - "id integer PRIMARY KEY AUTOINCREMENT, " - "name text NOT NULL)"); - - createTable( - "CREATE TABLE IF NOT EXISTS log_food (" - "id integer PRIMARY KEY AUTOINCREMENT, " - "profile_id int NOT NULL, " - "date int DEFAULT (strftime ('%s', 'now')), " - "meal_id int NOT NULL, " - "food_id int NOT NULL, " - "msre_id int NOT NULL, " - "amt real NOT NULL, " - "created int DEFAULT (strftime ('%s', 'now')), " - "FOREIGN KEY (profile_id) REFERENCES profile (id) ON UPDATE " - "CASCADE ON DELETE CASCADE, " - "FOREIGN KEY (meal_id) REFERENCES meal_name (id) ON UPDATE " - "CASCADE ON DELETE CASCADE)"); - - createTable( - "CREATE TABLE IF NOT EXISTS log_cf (" - "id integer PRIMARY KEY AUTOINCREMENT, " - "profile_id int NOT NULL, " - "date int DEFAULT (strftime ('%s', 'now')), " - "meal_id int NOT NULL, " - "food_id int NOT NULL, " - "custom_food_id int, " - "msre_id int NOT NULL, " - "amt real NOT NULL, " - "created int DEFAULT (strftime ('%s', 'now')), " - "FOREIGN KEY (profile_id) REFERENCES profile (id) ON UPDATE " - "CASCADE ON DELETE CASCADE, " - "FOREIGN KEY (meal_id) REFERENCES meal_name (id) ON UPDATE " - "CASCADE ON DELETE CASCADE, " - "FOREIGN KEY (custom_food_id) REFERENCES custom_food (id) ON " - "UPDATE CASCADE ON DELETE CASCADE)"); - - // Default Data Seeding - - // Ensure default profile exists - query.exec("INSERT OR IGNORE INTO profile (id, name) VALUES (1, 'default')"); - - // Seed standard meal names if table is empty - query.exec("SELECT count(*) FROM meal_name"); - if (query.next() && query.value(0).toInt() == 0) { - QStringList meals = {"Breakfast", "Lunch", "Dinner", "Snack", "Brunch"}; - for (const auto& meal : meals) { - query.prepare("INSERT INTO meal_name (name) VALUES (?)"); - query.addBindValue(meal); - query.exec(); + + QFile schemaFile(schemaPath); + if (schemaFile.open(QIODevice::ReadOnly)) { + QTextStream in(&schemaFile); + QString sql = in.readAll(); + + // Allow for simple splitting for now as tables.sql is simple + QStringList statements = sql.split(';', Qt::SkipEmptyParts); + for (const QString& stmt : statements) { + QString trimmed = stmt.trimmed(); + if (!trimmed.isEmpty() && !trimmed.startsWith("--")) { + if (!query.exec(trimmed)) { + qWarning() << "Schema init warning:" << query.lastError().text() + << "\nStmt:" << trimmed; + } + } + } + // Ensure version is set (tables.sql has it, but good to ensure) + query.exec(QString("PRAGMA user_version = %1").arg(CURRENT_SCHEMA_VERSION)); + qDebug() << "Upgraded user database version from" << schemaVersionOnDisk << "to" + << CURRENT_SCHEMA_VERSION << "."; + + // --- Seeding Data (moved from previous implementation) --- + + // Ensure default profile exists + query.exec("INSERT OR IGNORE INTO profile (id, name) VALUES (1, 'default')"); + + // Seed standard meal names if table is empty + query.exec("SELECT count(*) FROM meal_name"); + if (query.next() && query.value(0).toInt() == 0) { + QStringList meals = {"Breakfast", "Lunch", "Dinner", "Snack", "Brunch"}; + for (const auto& meal : meals) { + query.prepare("INSERT INTO meal_name (name) VALUES (?)"); + query.addBindValue(meal); + query.exec(); + } + } + } else { + qCritical() << "Could not find or open schema file:" << schemaPath; } + } else { + // Migration logic would go here + // if (currentVersion < 2) { ... } } } diff --git a/src/main.cpp b/src/main.cpp index 126ffc8..11f471c 100644 --- a/src/main.cpp +++ b/src/main.cpp @@ -4,6 +4,7 @@ #include #include #include +#include #include #include @@ -16,6 +17,17 @@ int main(int argc, char* argv[]) { QApplication::setOrganizationName("NutraTech"); QApplication::setWindowIcon(QIcon(":/resources/nutrition_icon-no_bg.png")); + // Prevent multiple instances + QString lockPath = + QStandardPaths::writableLocation(QStandardPaths::TempLocation) + "/nutra.lock"; + QLockFile lockFile(lockPath); + if (!lockFile.tryLock(100)) { + QMessageBox::warning(nullptr, "Nutra is already running", + "Another instance of Nutra is already running.\n" + "Please close it before starting a new one."); + return 1; + } + // Connect to database // Search order: // 1. Environment variable NUTRA_DB_PATH diff --git a/src/mainwindow.cpp b/src/mainwindow.cpp index ae41026..4a80b85 100644 --- a/src/mainwindow.cpp +++ b/src/mainwindow.cpp @@ -205,31 +205,103 @@ void MainWindow::onRecentFileClick() { void MainWindow::updateRecentFileActions() { QSettings settings("NutraTech", "Nutra"); - QStringList files = settings.value("recentFiles").toStringList(); - int numRecentFiles = static_cast( - qMin(static_cast(files.size()), static_cast(MaxRecentFiles))); + // Check for legacy setting if new one is empty + if (!settings.contains("recentFilesList") && settings.contains("recentFiles")) { + QStringList legacyFiles = settings.value("recentFiles").toStringList(); + QList newFiles; + for (const auto& path : legacyFiles) { + auto info = DatabaseManager::instance().getDatabaseInfo(path); + if (info.isValid) { // Only migrate valid ones + QVariantMap entry; + entry["path"] = path; + entry["type"] = info.type; + entry["version"] = info.version; + newFiles.append(entry); + } + } + settings.setValue("recentFilesList", newFiles); + settings.remove("recentFiles"); // Clean up legacy + } + + QList files = settings.value("recentFilesList").toList(); + + // Sort: User first, then USDA. Within type, preserve order (recency) or sort by name? + // Usually "Recent" implies recency. But user asked for "User on top". + // So we split into two lists (preserving recency within them) and concat. + + QList userDBs; + QList usdaDBs; + + for (const auto& v : files) { + QVariantMap m = v.toMap(); + if (m["type"].toString() == "User") { + userDBs.append(m); + } else { + usdaDBs.append(m); + } + } + + QList sortedFiles = userDBs; + sortedFiles.append(usdaDBs); + + int numToShow = static_cast(qMin(static_cast(sortedFiles.size()), + static_cast(MaxRecentFiles))); + + for (int i = 0; i < numToShow; ++i) { + QVariantMap m = sortedFiles[i]; + QString path = m["path"].toString(); + QString type = m["type"].toString(); + int version = m["version"].toInt(); + QString name = QFileInfo(path).fileName(); + + // Format: "nt.sqlite3 (User v1)" + // Or per user request: "Display pragma version... for full transparency" + QString text = QString("&%1 %2 (%3 v%4)").arg(i + 1).arg(name).arg(type).arg(version); - for (int i = 0; i < numRecentFiles; ++i) { - QString text = QString("&%1 %2").arg(i + 1).arg(QFileInfo(files[i]).fileName()); recentFileActions[static_cast(i)]->setText(text); - recentFileActions[static_cast(i)]->setData(files[i]); + recentFileActions[static_cast(i)]->setData(path); recentFileActions[static_cast(i)]->setVisible(true); } - for (int i = numRecentFiles; i < MaxRecentFiles; ++i) + for (int i = numToShow; i < MaxRecentFiles; ++i) recentFileActions[static_cast(i)]->setVisible(false); - recentFilesMenu->setEnabled(numRecentFiles > 0); + recentFilesMenu->setEnabled(numToShow > 0); } void MainWindow::addToRecentFiles(const QString& path) { + if (path.isEmpty()) return; + + auto info = DatabaseManager::instance().getDatabaseInfo(path); + if (!info.isValid) return; + QSettings settings("NutraTech", "Nutra"); - QStringList files = settings.value("recentFiles").toStringList(); - files.removeAll(path); - files.prepend(path); - while (files.size() > MaxRecentFiles) files.removeLast(); + // Read list of QVariantMaps + QList files = settings.value("recentFilesList").toList(); + + // Remove existing entry for this path + for (int i = 0; i < files.size(); ++i) { + if (files[i].toMap()["path"].toString() == path) { + files.removeAt(i); + break; + } + } + + // Prepare new entry + QVariantMap entry; + entry["path"] = path; + entry["type"] = info.type; + entry["version"] = info.version; + + // Prepend new entry + files.prepend(entry); + + // Limit list size + while (files.size() > MaxRecentFiles) { + files.removeLast(); + } - settings.setValue("recentFiles", files); + settings.setValue("recentFilesList", files); updateRecentFileActions(); } -- 2.52.0