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 ();
}