void AccountStorage::CreateTables () { QMap<QString, QString> table2query; table2query ["entries"] = "CREATE TABLE IF NOT EXISTS entries (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "Entry TEXT, " "Date DATE, " "Subject TEXT " ");"; table2query ["entry_tags"] = "CREATE TABLE IF NOT EXISTS tags (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "Tag TEXT NOT NULL, " "EntryId INTEGER NOT NULL REFERENCES entries (Id) ON DELETE CASCADE " ");"; Util::DBLock lock (AccountDB_); lock.Init (); const auto& tables = AccountDB_.tables (); Q_FOREACH (const QString& key, table2query.keys ()) if (!tables.contains (key)) { QSqlQuery q (AccountDB_); if (!q.exec (table2query [key])) { Util::DBLock::DumpError (q); throw std::runtime_error ("cannot create required tables"); } } lock.Good (); }
void AccountStorage::RemoveEntry (qint64 entryId) { Util::DBLock lock (AccountDB_); lock.Init (); RemoveEntry_.bindValue (":entry_id", entryId); if (!RemoveEntry_.exec ()) { Util::DBLock::DumpError (RemoveEntry_); throw std::runtime_error ("unable to remove entry"); } lock.Good (); }
int Storage::AddRepo (const RepoInfo& ri) { Util::DBLock lock (DB_); try { lock.Init (); } catch (const std::runtime_error& e) { qWarning () << Q_FUNC_INFO << "could not acquire DB lock"; throw; } QueryAddRepo_.bindValue (":url", Slashize (ri.GetUrl ()).toEncoded ()); QueryAddRepo_.bindValue (":name", ri.GetName ()); QueryAddRepo_.bindValue (":description", ri.GetShortDescr ()); QueryAddRepo_.bindValue (":longdescr", ri.GetLongDescr ()); QueryAddRepo_.bindValue (":maint_name", ri.GetMaintainer ().Name_); QueryAddRepo_.bindValue (":maint_email", ri.GetMaintainer ().Email_); if (!QueryAddRepo_.exec ()) { Util::DBLock::DumpError (QueryAddRepo_); throw std::runtime_error ("Query execution failed."); } QueryAddRepo_.finish (); int repoId = FindRepo (Slashize (ri.GetUrl ())); if (repoId == -1) { qWarning () << Q_FUNC_INFO << "OH SHI~, just inserted repo cannot be found!"; throw std::runtime_error ("Just inserted repo cannot be found."); } Q_FOREACH (const QString& component, ri.GetComponents ()) AddComponent (repoId, component); lock.Good (); return repoId; }
void Storage::AddExpenseEntry (ExpenseEntry& entry) { Util::DBLock lock (Impl_->DB_); lock.Init (); try { Impl_->NakedExpenseEntryInfo_.DoInsert_ (entry); AddNewCategories (entry, entry.Categories_); } catch (const oral::QueryException& e) { qWarning () << Q_FUNC_INFO; Util::DBLock::DumpError (e.GetQuery ()); throw; } lock.Good (); }
void Storage::InitializeTables () { Impl_->AccountInfo_ = oral::Adapt<Account> (Impl_->DB_); Impl_->NakedExpenseEntryInfo_ = oral::Adapt<NakedExpenseEntry> (Impl_->DB_); Impl_->ReceiptEntryInfo_ = oral::Adapt<ReceiptEntry> (Impl_->DB_); Impl_->CategoryInfo_ = oral::Adapt<Category> (Impl_->DB_); Impl_->CategoryLinkInfo_ = oral::Adapt<CategoryLink> (Impl_->DB_); Impl_->RateInfo_ = oral::Adapt<Rate> (Impl_->DB_); const auto& tables = Impl_->DB_.tables (); QMap<QString, QString> queryCreates; queryCreates [Account::ClassName ()] = Impl_->AccountInfo_.CreateTable_; queryCreates [NakedExpenseEntry::ClassName ()] = Impl_->NakedExpenseEntryInfo_.CreateTable_; queryCreates [ReceiptEntry::ClassName ()] = Impl_->ReceiptEntryInfo_.CreateTable_; queryCreates [Category::ClassName ()] = Impl_->CategoryInfo_.CreateTable_; queryCreates [CategoryLink::ClassName ()] = Impl_->CategoryLinkInfo_.CreateTable_; queryCreates [Rate::ClassName ()] = Impl_->RateInfo_.CreateTable_; Util::DBLock lock (Impl_->DB_); lock.Init (); QSqlQuery query (Impl_->DB_); bool tablesCreated = false; for (const auto& key : queryCreates.keys ()) if (!tables.contains (key)) { tablesCreated = true; if (!query.exec (queryCreates [key])) { Util::DBLock::DumpError (query); throw std::runtime_error ("cannot create tables"); } } lock.Good (); // Otherwise queries created by oral::Adapt() don't work. if (tablesCreated) InitializeTables (); }
void Storage::UpdateExpenseEntry (const ExpenseEntry& entry) { Util::DBLock lock (Impl_->DB_); lock.Init (); Impl_->NakedExpenseEntryInfo_.DoUpdate_ (entry); auto nowCats = entry.Categories_; for (const auto& cat : boost::fusion::at_c<1> (Impl_->CategoryLinkInfo_.SingleFKeySelectors_) (entry)) { if (!nowCats.removeAll (Impl_->CatIDCache_.value (cat.Category_).Name_)) Impl_->CategoryLinkInfo_.DoDelete_ (cat); } if (!nowCats.isEmpty ()) AddNewCategories (entry, nowCats); lock.Good (); }
void Storage::RemoveComponent (int repoId, const QString& component) { Util::DBLock lock (DB_); try { lock.Init (); } catch (const std::exception& e) { qWarning () << Q_FUNC_INFO << "unable to start transaction"; throw std::runtime_error ("Unable to start transaction"); } int compId = FindComponent (repoId, component); if (compId == -1) { qWarning () << Q_FUNC_INFO << "component" << component << "not found."; throw std::runtime_error ("Requested component not found"); } QSqlQuery idsSelector (DB_); idsSelector.prepare ("SELECT DISTINCT package_id " "FROM locations WHERE component_id = :component_id"); idsSelector.bindValue (":component_id", compId); if (!idsSelector.exec ()) { Util::DBLock::DumpError (idsSelector); throw std::runtime_error ("Fetching of possibly affected packages failed."); } QList<int> possiblyAffected; while (idsSelector.next ()) possiblyAffected << idsSelector.value (0).toInt (); idsSelector.finish (); QSqlQuery remover (DB_); remover.prepare ("DELETE FROM locations WHERE component_id = :component_id;"); remover.bindValue (":component_id", compId); if (!remover.exec ()) { Util::DBLock::DumpError (remover); throw std::runtime_error ("Unable to remove component from locations."); } remover.prepare ("DELETE FROM components WHERE component_id = :component_id;"); remover.bindValue (":component_id", compId); if (!remover.exec ()) { Util::DBLock::DumpError (remover); throw std::runtime_error ("Unable to remove component from components."); } remover.finish (); QSqlQuery checker (DB_); checker.prepare ("SELECT COUNT (package_id) FROM locations WHERE package_id = :package_id;"); Q_FOREACH (int packageId, possiblyAffected) { checker.bindValue (":package_id", packageId); if (!checker.exec ()) { Util::DBLock::DumpError (checker); throw std::runtime_error ("Unable to remove check affected."); } if (!checker.next ()) { qWarning () << Q_FUNC_INFO << "zarroo rows"; throw std::runtime_error ("Unable to move to the next row"); } if (checker.value (0).toInt ()) continue; checker.finish (); emit packageRemoved (packageId); remover.prepare ("DELETE FROM packages WHERE package_id = :package_id;"); remover.bindValue (":package_id", packageId); if (!remover.exec ()) { Util::DBLock::DumpError (remover); throw std::runtime_error ("Unable to remove orphaned package."); } remover.finish (); }
void LocalCollectionStorage::CreateTables () { typedef QPair<QString, QString> QueryPair_t; QList<QueryPair_t> table2query; table2query << QueryPair_t ("artists", "CREATE TABLE artists (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "Name TEXT " ");"); table2query << QueryPair_t ("albums", "CREATE TABLE albums (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "Name TEXT, " "Year INTEGER, " "CoverPath TEXT " ");"); table2query << QueryPair_t ("artists2albums", "CREATE TABLE artists2albums (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "ArtistID INTEGER NOT NULL REFERENCES artists (Id) ON DELETE CASCADE, " "AlbumID INTEGER NOT NULL REFERENCES albums (Id) ON DELETE CASCADE " ");"); table2query << QueryPair_t ("tracks", "CREATE TABLE tracks (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "ArtistID INTEGER NOT NULL REFERENCES artists (Id) ON DELETE CASCADE, " "AlbumId NOT NULL REFERENCES albums (Id) ON DELETE CASCADE, " "Path TEXT NOT NULL, " "Name TEXT NOT NULL, " "TrackNumber INTEGER, " "Length INTEGER " ");"); table2query << QueryPair_t ("genres", "CREATE TABLE genres (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "TrackId NOT NULL REFERENCES tracks (Id) ON DELETE CASCADE, " "Name TEXT NOT NULL " ");"); table2query << QueryPair_t ("statistics", "CREATE TABLE statistics (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "TrackId NOT NULL UNIQUE REFERENCES tracks (Id) ON DELETE CASCADE, " "Playcount INTEGER, " "Added TIMESTAMP, " "LastPlay TIMESTAMP, " "Score INTEGER, " "Rating INTEGER " ");"); table2query << QueryPair_t ("lovedBanned", "CREATE TABLE lovedBanned (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "TrackId NOT NULL UNIQUE REFERENCES tracks (Id) ON DELETE CASCADE, " "State INTEGER" ");"); table2query << QueryPair_t ("fileTimes", "CREATE TABLE fileTimes (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "TrackID INTEGER UNIQUE NOT NULL REFERENCES tracks (Id) ON DELETE CASCADE, " "MTime TIMESTAMP NOT NULL" ");"); table2query << QueryPair_t ("rgdata", "CREATE TABLE rgdata (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "TrackId INTEGER UNIQUE NOT NULL REFERENCES tracks (Id) ON DELETE CASCADE, " "LastMTime TIMESTAMP NOT NULL, " "TrackGain DOUBLE NOT NULL, " "TrackPeak DOUBLE NOT NULL, " "AlbumGain DOUBLE NOT NULL, " "AlbumPeak DOUBLE NOT NULL " ");"); table2query << QueryPair_t ("playhistory", "CREATE TABLE playhistory (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "TrackId INTEGER NOT NULL REFERENCES tracks (Id) ON DELETE CASCADE, " "Date TIMESTAMP" ");"); QSqlQuery { DB_ }.exec ("PRAGMA defer_foreign_keys = ON;"); QSqlQuery { DB_ }.exec ("PRAGMA foreign_keys = OFF;"); Util::DBLock lock (DB_); lock.Init (); const auto& tables = DB_.tables (); Q_FOREACH (const auto& pair, table2query) if (!tables.contains (pair.first)) { QSqlQuery q (DB_); if (!q.exec (pair.second)) { Util::DBLock::DumpError (q); throw std::runtime_error ("cannot create required tables"); } } if (XmlSettingsManager::Instance ().Property ("TracksTableVersion", 1).toInt () < 2) { const QString query = "CREATE TABLE tracks2 (" "Id INTEGER PRIMARY KEY AUTOINCREMENT, " "ArtistID INTEGER NOT NULL REFERENCES artists (Id) ON DELETE CASCADE, " "AlbumId NOT NULL REFERENCES albums (Id) ON DELETE CASCADE, " "Path TEXT NOT NULL UNIQUE, " "Name TEXT NOT NULL, " "TrackNumber INTEGER, " "Length INTEGER " ");"; QSqlQuery q { DB_ }; if (!q.exec (query)) { Util::DBLock::DumpError (q); throw std::runtime_error ("cannot create tracks2"); } if (!q.exec ("INSERT OR IGNORE INTO tracks2 SELECT * FROM tracks;") || !q.exec ("DROP TABLE tracks;") || !q.exec ("ALTER TABLE tracks2 RENAME TO tracks;")) { Util::DBLock::DumpError (q); throw std::runtime_error ("cannot copy data from tracks2"); } XmlSettingsManager::Instance ().setProperty ("TracksTableVersion", 2); } QSqlQuery (DB_).exec ("CREATE UNIQUE INDEX IF NOT EXISTS index_tracksPaths ON tracks (Path);"); lock.Good (); QSqlQuery { DB_ }.exec ("PRAGMA foreign_keys = ON;"); }
void Storage::addMessage (const QVariantMap& data) { Util::DBLock lock (*DB_); try { lock.Init (); } catch (const std::exception& e) { qWarning () << Q_FUNC_INFO << "unable to start transaction:" << e.what (); return; } const QString& entryID = data ["EntryID"].toString (); if (!Users_.contains (entryID)) { try { AddUser (entryID); } catch (const std::exception& e) { qWarning () << Q_FUNC_INFO << entryID << "unable to add the user to the DB:" << e.what (); return; } } EntryCacheSetter_.bindValue (":id", Users_ [entryID]); EntryCacheSetter_.bindValue (":visible_name", data ["VisibleName"]); if (!EntryCacheSetter_.exec ()) Util::DBLock::DumpError (EntryCacheSetter_); const QString& accountID = data ["AccountID"].toString (); if (!Accounts_.contains (accountID)) { try { AddAccount (accountID); } catch (const std::exception& e) { qWarning () << Q_FUNC_INFO << accountID << "unable to add account ID to the DB:" << e.what (); return; } } MessageDumper_.bindValue (":id", Users_ [entryID]); MessageDumper_.bindValue (":account_id", Accounts_ [accountID]); MessageDumper_.bindValue (":date", data ["DateTime"]); MessageDumper_.bindValue (":direction", data ["Direction"]); MessageDumper_.bindValue (":message", data ["Body"]); MessageDumper_.bindValue (":variant", data ["OtherVariant"]); switch (data ["MessageType"].toInt ()) { case IMessage::MTChatMessage: MessageDumper_.bindValue (":type", "CHAT"); break; case IMessage::MTMUCMessage: MessageDumper_.bindValue (":type", "MUC"); break; case IMessage::MTStatusMessage: MessageDumper_.bindValue (":type", "STATUS"); break; case IMessage::MTEventMessage: MessageDumper_.bindValue (":type", "EVENT"); break; case IMessage::MTServiceMessage: MessageDumper_.bindValue (":type", "SERVICE"); break; } if (!MessageDumper_.exec ()) { Util::DBLock::DumpError (MessageDumper_); return; } lock.Good (); }