From 24889746b4b0342f5e794ce42ba67c076ef67afb Mon Sep 17 00:00:00 2001 From: APTX Date: Mon, 16 Jul 2012 17:06:09 +0200 Subject: [PATCH] Move all queries to the new prepare() --- localmylist/database.cpp | 945 ++++++++++++++++++++------------------- localmylist/database.h | 3 +- 2 files changed, 487 insertions(+), 461 deletions(-) diff --git a/localmylist/database.cpp b/localmylist/database.cpp index 96dbae6..1441100 100644 --- a/localmylist/database.cpp +++ b/localmylist/database.cpp @@ -340,7 +340,7 @@ OpenFileData Database::openFile(int fid) " LEFT JOIN file_location fl ON fl.fid = f.fid " " WHERE f.fid = :fid " " AND fl.path IS NOT NULL " - " AND fl.host_id = :hostId"); + " AND fl.host_id = :hostId "); q.bindValue(":hostId", MyList::instance()->hostId()); q.bindValue(":fid", fid); @@ -349,57 +349,67 @@ OpenFileData Database::openFile(int fid) HostInfo Database::getHostInfo(const QString &hostName) { - d->getHostInfoQuery.bindValue(":name", hostName); + QSqlQuery &q = prepare( + "SELECT host_id, name, is_udp_host FROM host " + " WHERE name = :name "); + + q.bindValue(":name", hostName); - if (!exec(d->getHostInfoQuery)) + if (!exec(q)) return HostInfo(); HostInfo hostInfo; - if (d->getHostInfoQuery.next()) + if (q.next()) { - hostInfo.id = d->getHostInfoQuery.value(0).toInt(); - hostInfo.name = d->getHostInfoQuery.value(1).toString(); - hostInfo.isUdpHost = d->getHostInfoQuery.value(2).toBool(); + hostInfo.id = q.value(0).toInt(); + hostInfo.name = q.value(1).toString(); + hostInfo.isUdpHost = q.value(2).toBool(); } - d->getHostInfoQuery.finish(); + q.finish(); return hostInfo; } QVariantMap Database::getConfig() { - if (!exec(d->getSettingsQuery)) + QSqlQuery &q = prepare("SELECT key, value FROM config"); + + if (!exec(q)) return QVariantMap(); QVariantMap settings; - while (d->getSettingsQuery.next()) + while (q.next()) { - settings.insert(d->getSettingsQuery.value(0).toString(), d->getSettingsQuery.value(1)); + settings.insert(q.value(0).toString(), q.value(1)); } - d->getSettingsQuery.finish(); + q.finish(); return settings; } bool Database::setConfig(const QString &key, const QVariant &value) { - d->updateSettingQuery.bindValue(":key", key); - d->updateSettingQuery.bindValue(":value", value); + QSqlQuery &q = prepare("UPDATE config SET value = :value WHERE key = :key"); + + q.bindValue(":key", key); + q.bindValue(":value", value); - return exec(d->updateSettingQuery); + return exec(q); } int Database::isKnownFile(const QByteArray &ed2k, qint64 size) { - d->isKnownFileQuery.bindValue(":ed2k", ed2k); - d->isKnownFileQuery.bindValue(":size", size); + QSqlQuery &q = prepare("SELECT fid FROM file WHERE ed2k = :ed2k AND size = :size"); + + q.bindValue(":ed2k", ed2k); + q.bindValue(":size", size); - if (!exec(d->isKnownFileQuery)) + if (!exec(q)) return 0; int fid = 0; - if (d->isKnownFileQuery.next()) - fid = d->isKnownFileQuery.value(0).toInt(); - d->isKnownFileQuery.finish(); + if (q.next()) + fid = q.value(0).toInt(); + q.finish(); return fid; } @@ -416,7 +426,11 @@ bool Database::addFileLocation(const FileLocation &fileLocation) bool Database::setFileLocation(const FileLocation &fileLocation) { - QSqlQuery &q = prepare("UPDATE file_location SET host_id = :hostId, path = :path, renamed = :renamed, failed_rename = :failedRename WHERE fid = :fid"); + QSqlQuery &q = prepare( + "UPDATE file_location SET host_id = :hostId, path = :path, renamed = :renamed, " + " failed_rename = :failedRename " + " WHERE fid = :fid"); + q.bindValue(":fid", fileLocation.fid); q.bindValue(":hostId", fileLocation.hostId); q.bindValue(":path", fileLocation.path); @@ -430,39 +444,47 @@ Anime Database::getAnime(int aid) { Anime a; - d->getAnimeQuery.bindValue(":aid", aid); + QSqlQuery &q = prepare( + "SELECT aid, anidb_update, entry_update, my_update, title_english, " + " title_romaji, title_kanji, description, year, start_date, end_date, " + " type, rating, votes, temp_rating, temp_votes, my_vote, my_vote_date, " + " my_temp_vote, my_temp_vote_date " + " FROM anime " + " WHERE aid = :aid "); + + q.bindValue(":aid", aid); - if (!exec(d->getAnimeQuery)) + if (!exec(q)) return a; - if (!d->getAnimeQuery.next()) + if (!q.next()) { - d->getAnimeQuery.finish(); + q.finish(); return a; } - a.aid = d->getAnimeQuery.value(0).toInt(); - a.anidbUpdate = d->getAnimeQuery.value(1).toDateTime(); - a.entryUpdate = d->getAnimeQuery.value(2).toDateTime(); - a.myUpdate = d->getAnimeQuery.value(3).toDateTime(); - a.titleEnglish = d->getAnimeQuery.value(4).toString(); - a.titleRomaji = d->getAnimeQuery.value(5).toString(); - a.titleKanji = d->getAnimeQuery.value(6).toString(); - a.description = d->getAnimeQuery.value(7).toString(); - a.year = d->getAnimeQuery.value(8).toString(); - a.startDate = d->getAnimeQuery.value(9).toDateTime(); - a.endDate = d->getAnimeQuery.value(10).toDateTime(); - a.type = d->getAnimeQuery.value(11).toString(); - a.rating = d->getAnimeQuery.value(12).toDouble(); - a.votes = d->getAnimeQuery.value(13).toInt(); - a.tempRating = d->getAnimeQuery.value(14).toDouble(); - a.tempVotes = d->getAnimeQuery.value(15).toInt(); - a.myVote = d->getAnimeQuery.value(16).toDouble(); - a.myVoteDate = d->getAnimeQuery.value(17).toDateTime(); - a.myTempVote = d->getAnimeQuery.value(18).toDouble(); - a.myTempVoteDate = d->getAnimeQuery.value(19).toDateTime(); - - d->getAnimeQuery.finish(); + a.aid = q.value(0).toInt(); + a.anidbUpdate = q.value(1).toDateTime(); + a.entryUpdate = q.value(2).toDateTime(); + a.myUpdate = q.value(3).toDateTime(); + a.titleEnglish = q.value(4).toString(); + a.titleRomaji = q.value(5).toString(); + a.titleKanji = q.value(6).toString(); + a.description = q.value(7).toString(); + a.year = q.value(8).toString(); + a.startDate = q.value(9).toDateTime(); + a.endDate = q.value(10).toDateTime(); + a.type = q.value(11).toString(); + a.rating = q.value(12).toDouble(); + a.votes = q.value(13).toInt(); + a.tempRating = q.value(14).toDouble(); + a.tempVotes = q.value(15).toInt(); + a.myVote = q.value(16).toDouble(); + a.myVoteDate = q.value(17).toDateTime(); + a.myTempVote = q.value(18).toDouble(); + a.myTempVoteDate = q.value(19).toDateTime(); + + q.finish(); return a; } @@ -477,7 +499,7 @@ QList Database::getEpisodes(int aid) " e.special, e.recap, e.opening, e.ending, e.rating, e.votes, e.my_vote, e.my_vote_date " " FROM episode e " " JOIN anime a ON (a.aid = e.aid)" - " WHERE a.aid = :aid"); + " WHERE a.aid = :aid "); q.bindValue(":aid", aid); @@ -503,7 +525,7 @@ Episode Database::getEpisode(int eid) " title_english, title_romaji, title_kanji, length, airdate, state, " " special, recap, opening, ending, rating, votes, my_vote, my_vote_date " " FROM episode " - " WHERE eid = :eid"); + " WHERE eid = :eid "); q.bindValue(":eid", eid); @@ -535,6 +557,7 @@ File Database::getFile(int fid) " my_watched, my_state, my_file_state, my_storage, my_source, my_other " " FROM file " " WHERE fid = :fid "); + q.bindValue(":fid", fid); if (!exec(q)) @@ -634,408 +657,531 @@ File Database::getFileByTitle(const QString &title, int epno) bool Database::setAnime(const Anime &anime) { - d->setAnimeQuery.bindValue(":aid", anime.aid); - d->setAnimeQuery.bindValue(":anidbUpdate", anime.anidbUpdate); - d->setAnimeQuery.bindValue(":entryUpdate", anime.entryUpdate); - d->setAnimeQuery.bindValue(":myUpdate", anime.myUpdate); - d->setAnimeQuery.bindValue(":titleEnglish", anime.titleEnglish); - d->setAnimeQuery.bindValue(":titleRomaji", anime.titleRomaji); - d->setAnimeQuery.bindValue(":titleKanji", anime.titleKanji); - d->setAnimeQuery.bindValue(":description", anime.description); - d->setAnimeQuery.bindValue(":year", anime.year); - d->setAnimeQuery.bindValue(":startDate", anime.startDate); - d->setAnimeQuery.bindValue(":endDate", anime.endDate); - d->setAnimeQuery.bindValue(":type", anime.type); - d->setAnimeQuery.bindValue(":rating", anime.rating); - d->setAnimeQuery.bindValue(":votes", anime.votes); - d->setAnimeQuery.bindValue(":tempRating", anime.tempRating); - d->setAnimeQuery.bindValue(":tempVotes", anime.tempVotes); - d->setAnimeQuery.bindValue(":myVote", anime.myVote); - d->setAnimeQuery.bindValue(":myVoteDate", anime.myVoteDate); - d->setAnimeQuery.bindValue(":myTempVote", anime.myTempVote); - d->setAnimeQuery.bindValue(":myTempVoteDate", anime.myTempVoteDate); - - return exec(d->setAnimeQuery); + QSqlQuery &q = prepare( + "UPDATE anime SET " + " anidb_update = :anidbUpdate, entry_update = :entryUpdate, " + " my_update = :myUpdate, title_english = :titleEnglish, " + " title_romaji = :titleRomaji, title_kanji = :titleKanji, " + " description = :description, year = :year, start_date = :startDate, " + " end_date = :endDate, type = :type, rating = :rating, votes = :votes, " + " temp_rating = :tempRating, temp_votes = :tempVotes, my_vote = :myVote, " + " my_vote_date = :myVoteDate, my_temp_vote = :myTempVote, " + " my_temp_vote_date = :myTempVoteDate " + " WHERE aid = :aid "); + + q.bindValue(":aid", anime.aid); + q.bindValue(":anidbUpdate", anime.anidbUpdate); + q.bindValue(":entryUpdate", anime.entryUpdate); + q.bindValue(":myUpdate", anime.myUpdate); + q.bindValue(":titleEnglish", anime.titleEnglish); + q.bindValue(":titleRomaji", anime.titleRomaji); + q.bindValue(":titleKanji", anime.titleKanji); + q.bindValue(":description", anime.description); + q.bindValue(":year", anime.year); + q.bindValue(":startDate", anime.startDate); + q.bindValue(":endDate", anime.endDate); + q.bindValue(":type", anime.type); + q.bindValue(":rating", anime.rating); + q.bindValue(":votes", anime.votes); + q.bindValue(":tempRating", anime.tempRating); + q.bindValue(":tempVotes", anime.tempVotes); + q.bindValue(":myVote", anime.myVote); + q.bindValue(":myVoteDate", anime.myVoteDate); + q.bindValue(":myTempVote", anime.myTempVote); + q.bindValue(":myTempVoteDate", anime.myTempVoteDate); + + return exec(q); } bool Database::setEpisode(const Episode &episode) { - d->setEpisodeQuery.bindValue(":eid", episode.eid); - d->setEpisodeQuery.bindValue(":aid", episode.aid); - d->setEpisodeQuery.bindValue(":anidbUpdate", episode.anidbUpdate); - d->setEpisodeQuery.bindValue(":entryUpdate", episode.entryUpdate); - d->setEpisodeQuery.bindValue(":myUpdate", episode.myUpdate); - d->setEpisodeQuery.bindValue(":epno", episode.epno); - d->setEpisodeQuery.bindValue(":titleEnglish", episode.titleEnglish); - d->setEpisodeQuery.bindValue(":titleRomaji", episode.titleRomaji); - d->setEpisodeQuery.bindValue(":titleKanji", episode.titleKanji); - d->setEpisodeQuery.bindValue(":length", episode.length); - d->setEpisodeQuery.bindValue(":airdate", episode.airdate); - d->setEpisodeQuery.bindValue(":state", episode.state); - d->setEpisodeQuery.bindValue(":special", episode.special); - d->setEpisodeQuery.bindValue(":recap", episode.recap); - d->setEpisodeQuery.bindValue(":opening", episode.opening); - d->setEpisodeQuery.bindValue(":ending", episode.ending); - d->setEpisodeQuery.bindValue(":rating", episode.rating); - d->setEpisodeQuery.bindValue(":votes", episode.votes); - d->setEpisodeQuery.bindValue(":myVote", episode.myVote); - d->setEpisodeQuery.bindValue(":myVoteDate", episode.myVoteDate); - - return exec(d->setEpisodeQuery); + QSqlQuery &q = prepare( + "UPDATE episode SET " + " aid = :aid, anidb_update = :anidbUpdate, entry_update = :entryUpdate, " + " my_update = :myUpdate, epno = :epno, title_english = :titleEnglish, " + " title_romaji = :titleRomaji, title_kanji = :titleKanji, length = :length, " + " airdate = :airdate, state = :state, special = :special, recap = :recap, " + " opening = :opening, ending = :ending, rating = :rating, votes = :votes, " + " my_vote = :myVote, my_vote_date = :myVoteDate " + " WHERE eid = :eid "); + + q.bindValue(":eid", episode.eid); + q.bindValue(":aid", episode.aid); + q.bindValue(":anidbUpdate", episode.anidbUpdate); + q.bindValue(":entryUpdate", episode.entryUpdate); + q.bindValue(":myUpdate", episode.myUpdate); + q.bindValue(":epno", episode.epno); + q.bindValue(":titleEnglish", episode.titleEnglish); + q.bindValue(":titleRomaji", episode.titleRomaji); + q.bindValue(":titleKanji", episode.titleKanji); + q.bindValue(":length", episode.length); + q.bindValue(":airdate", episode.airdate); + q.bindValue(":state", episode.state); + q.bindValue(":special", episode.special); + q.bindValue(":recap", episode.recap); + q.bindValue(":opening", episode.opening); + q.bindValue(":ending", episode.ending); + q.bindValue(":rating", episode.rating); + q.bindValue(":votes", episode.votes); + q.bindValue(":myVote", episode.myVote); + q.bindValue(":myVoteDate", episode.myVoteDate); + + return exec(q); } bool Database::setFile(const File &file) { - d->setFileQuery.bindValue(":fid", file.fid); - d->setFileQuery.bindValue(":eid", file.eid); - d->setFileQuery.bindValue(":aid", file.aid); - d->setFileQuery.bindValue(":gid", file.gid); - d->setFileQuery.bindValue(":anidbUpdate", file.anidbUpdate); - d->setFileQuery.bindValue(":entryUpdate", file.entryUpdate); - d->setFileQuery.bindValue(":myUpdate", file.myUpdate); - d->setFileQuery.bindValue(":ed2k", file.ed2k); - d->setFileQuery.bindValue(":size", file.size); - d->setFileQuery.bindValue(":length", file.length); - d->setFileQuery.bindValue(":extension", file.extension); - d->setFileQuery.bindValue(":groupName", file.groupName); - d->setFileQuery.bindValue(":groupNameShort", file.groupNameShort); - d->setFileQuery.bindValue(":crc", file.crc); - d->setFileQuery.bindValue(":releaseDate", file.releaseDate); - d->setFileQuery.bindValue(":version", file.version); - d->setFileQuery.bindValue(":censored", file.censored); - d->setFileQuery.bindValue(":type", file.type); - d->setFileQuery.bindValue(":qualityId", file.qualityId); - d->setFileQuery.bindValue(":quality", file.quality); - d->setFileQuery.bindValue(":resolution", file.resolution); - d->setFileQuery.bindValue(":videoCodec", file.videoCodec); - d->setFileQuery.bindValue(":audioCodec", file.audioCodec); - d->setFileQuery.bindValue(":audioLanguage", file.audioLanguage); - d->setFileQuery.bindValue(":subtitleLanguage", file.subtitleLanguage); - d->setFileQuery.bindValue(":aspectRatio", file.aspectRatio); - d->setFileQuery.bindValue(":myWatched", file.myWatched); - d->setFileQuery.bindValue(":myState", file.myState); - d->setFileQuery.bindValue(":myFileState", file.myFileState); - d->setFileQuery.bindValue(":myStorage", file.myStorage); - d->setFileQuery.bindValue(":mySource", file.mySource); - d->setFileQuery.bindValue(":myOther", file.myOther); - - return exec(d->setFileQuery); + QSqlQuery &q = prepare( + "UPDATE file SET " + " eid = :eid, aid = :aid, gid = :gid, anidb_update = :anidbUpdate, " + " entry_update = :entryUpdate, my_update = :myUpdate, " + " ed2k = :ed2k, size = :size, length = :length, extension = :extension, " + " group_name = :groupName, group_name_short = :groupNameShort, crc = :crc, " + " release_date = :releaseDate, version = :version, censored = :censored, " + " type = :type, quality_id = :qualityId, quality = :quality, " + " resolution = :resolution, video_codec = :videoCodec, " + " audio_codec = :audioCodec, audio_language = :audioLanguage, " + " subtitle_language = :subtitleLanguage, aspect_ratio = :aspectRatio, " + " my_watched = :myWatched, my_state = :myState, my_file_state = :myFileState, " + " my_storage = :myStorage, my_source = :mySource, my_other = :myOther " + " WHERE fid = :fid "); + + q.bindValue(":fid", file.fid); + q.bindValue(":eid", file.eid); + q.bindValue(":aid", file.aid); + q.bindValue(":gid", file.gid); + q.bindValue(":anidbUpdate", file.anidbUpdate); + q.bindValue(":entryUpdate", file.entryUpdate); + q.bindValue(":myUpdate", file.myUpdate); + q.bindValue(":ed2k", file.ed2k); + q.bindValue(":size", file.size); + q.bindValue(":length", file.length); + q.bindValue(":extension", file.extension); + q.bindValue(":groupName", file.groupName); + q.bindValue(":groupNameShort", file.groupNameShort); + q.bindValue(":crc", file.crc); + q.bindValue(":releaseDate", file.releaseDate); + q.bindValue(":version", file.version); + q.bindValue(":censored", file.censored); + q.bindValue(":type", file.type); + q.bindValue(":qualityId", file.qualityId); + q.bindValue(":quality", file.quality); + q.bindValue(":resolution", file.resolution); + q.bindValue(":videoCodec", file.videoCodec); + q.bindValue(":audioCodec", file.audioCodec); + q.bindValue(":audioLanguage", file.audioLanguage); + q.bindValue(":subtitleLanguage", file.subtitleLanguage); + q.bindValue(":aspectRatio", file.aspectRatio); + q.bindValue(":myWatched", file.myWatched); + q.bindValue(":myState", file.myState); + q.bindValue(":myFileState", file.myFileState); + q.bindValue(":myStorage", file.myStorage); + q.bindValue(":mySource", file.mySource); + q.bindValue(":myOther", file.myOther); + + return exec(q); } bool Database::addTitle(const AnimeTitle &title) { - d->addTitleQuery.bindValue(":aid", title.aid); - d->addTitleQuery.bindValue(":type", int(title.type)); - d->addTitleQuery.bindValue(":language", title.language); - d->addTitleQuery.bindValue(":title", title.title); + QSqlQuery &q = prepare("INSERT INTO anime_title VALUES(:aid, :type, :language, :title)"); + + q.bindValue(":aid", title.aid); + q.bindValue(":type", int(title.type)); + q.bindValue(":language", title.language); + q.bindValue(":title", title.title); - return exec(d->addTitleQuery); + return exec(q); } bool Database::addAnime(const Anime &anime) { - d->addAnimeQuery.bindValue(":aid", anime.aid); - d->addAnimeQuery.bindValue(":anidbUpdate", anime.anidbUpdate); - d->addAnimeQuery.bindValue(":entryUpdate", anime.entryUpdate); - d->addAnimeQuery.bindValue(":myUpdate", anime.myUpdate); - d->addAnimeQuery.bindValue(":titleEnglish", anime.titleEnglish); - d->addAnimeQuery.bindValue(":titleRomaji", anime.titleRomaji); - d->addAnimeQuery.bindValue(":titleKanji", anime.titleKanji); - d->addAnimeQuery.bindValue(":description", anime.description); - d->addAnimeQuery.bindValue(":year", anime.year); - d->addAnimeQuery.bindValue(":startDate", anime.startDate); - d->addAnimeQuery.bindValue(":endDate", anime.endDate); - d->addAnimeQuery.bindValue(":type", anime.type); - d->addAnimeQuery.bindValue(":rating", anime.rating); - d->addAnimeQuery.bindValue(":votes", anime.votes); - d->addAnimeQuery.bindValue(":tempRating", anime.tempRating); - d->addAnimeQuery.bindValue(":tempVotes", anime.tempVotes); - d->addAnimeQuery.bindValue(":myVote", anime.myVote); - d->addAnimeQuery.bindValue(":myVoteDate", anime.myVoteDate); - d->addAnimeQuery.bindValue(":myTempVote", anime.myTempVote); - d->addAnimeQuery.bindValue(":myTempVoteDate", anime.myTempVoteDate); - - return exec(d->addAnimeQuery); + QSqlQuery &q = prepare( + "INSERT INTO anime VALUES(:aid, :anidbUpdate, :entryUpdate, :myUpdate, :titleEnglish, " + " :titleRomaji, :titleKanji, :description, :year, :startDate, :endDate, :type, " + " :rating, :votes, :tempRating, :tempVotes, :myVote, :myVoteDate, " + " :myTempVote, :myTempVoteDate) "); + + q.bindValue(":aid", anime.aid); + q.bindValue(":anidbUpdate", anime.anidbUpdate); + q.bindValue(":entryUpdate", anime.entryUpdate); + q.bindValue(":myUpdate", anime.myUpdate); + q.bindValue(":titleEnglish", anime.titleEnglish); + q.bindValue(":titleRomaji", anime.titleRomaji); + q.bindValue(":titleKanji", anime.titleKanji); + q.bindValue(":description", anime.description); + q.bindValue(":year", anime.year); + q.bindValue(":startDate", anime.startDate); + q.bindValue(":endDate", anime.endDate); + q.bindValue(":type", anime.type); + q.bindValue(":rating", anime.rating); + q.bindValue(":votes", anime.votes); + q.bindValue(":tempRating", anime.tempRating); + q.bindValue(":tempVotes", anime.tempVotes); + q.bindValue(":myVote", anime.myVote); + q.bindValue(":myVoteDate", anime.myVoteDate); + q.bindValue(":myTempVote", anime.myTempVote); + q.bindValue(":myTempVoteDate", anime.myTempVoteDate); + + return exec(q); } bool Database::addEpisode(const Episode &episode) { - d->addEpisodeQuery.bindValue(":eid", episode.eid); - d->addEpisodeQuery.bindValue(":aid", episode.aid); - d->addEpisodeQuery.bindValue(":anidbUpdate", episode.anidbUpdate); - d->addEpisodeQuery.bindValue(":entryUpdate", episode.entryUpdate); - d->addEpisodeQuery.bindValue(":myUpdate", episode.myUpdate); - d->addEpisodeQuery.bindValue(":epno", episode.epno); - d->addEpisodeQuery.bindValue(":titleEnglish", episode.titleEnglish); - d->addEpisodeQuery.bindValue(":titleRomaji", episode.titleRomaji); - d->addEpisodeQuery.bindValue(":titleKanji", episode.titleKanji); - d->addEpisodeQuery.bindValue(":length", episode.length); - d->addEpisodeQuery.bindValue(":airdate", episode.airdate); - d->addEpisodeQuery.bindValue(":state", episode.state); - d->addEpisodeQuery.bindValue(":special", episode.special); - d->addEpisodeQuery.bindValue(":recap", episode.recap); - d->addEpisodeQuery.bindValue(":opening", episode.opening); - d->addEpisodeQuery.bindValue(":ending", episode.ending); - d->addEpisodeQuery.bindValue(":rating", episode.rating); - d->addEpisodeQuery.bindValue(":votes", episode.votes); - d->addEpisodeQuery.bindValue(":myVote", episode.myVote); - d->addEpisodeQuery.bindValue(":myVoteDate", episode.myVoteDate); - - return exec(d->addEpisodeQuery); + QSqlQuery &q = prepare( + "INSERT INTO episode VALUES(:eid, :aid, :anidbUpdate, :entryUpdate, :myUpdate, :epno, " + " :titleEnglish, :titleRomaji, :titleKanji, :length, :airdate, " + " :state, :special, :recap, :openineg, :ending, :rating, " + " :votes, :myVote, :myVoteDate) "); + + q.bindValue(":eid", episode.eid); + q.bindValue(":aid", episode.aid); + q.bindValue(":anidbUpdate", episode.anidbUpdate); + q.bindValue(":entryUpdate", episode.entryUpdate); + q.bindValue(":myUpdate", episode.myUpdate); + q.bindValue(":epno", episode.epno); + q.bindValue(":titleEnglish", episode.titleEnglish); + q.bindValue(":titleRomaji", episode.titleRomaji); + q.bindValue(":titleKanji", episode.titleKanji); + q.bindValue(":length", episode.length); + q.bindValue(":airdate", episode.airdate); + q.bindValue(":state", episode.state); + q.bindValue(":special", episode.special); + q.bindValue(":recap", episode.recap); + q.bindValue(":opening", episode.opening); + q.bindValue(":ending", episode.ending); + q.bindValue(":rating", episode.rating); + q.bindValue(":votes", episode.votes); + q.bindValue(":myVote", episode.myVote); + q.bindValue(":myVoteDate", episode.myVoteDate); + + return exec(q); } bool Database::addFile(const File &file) { - d->addFileQuery.bindValue(":fid", file.fid); - d->addFileQuery.bindValue(":eid", file.eid); - d->addFileQuery.bindValue(":aid", file.aid); - d->addFileQuery.bindValue(":gid", file.gid); - d->addFileQuery.bindValue(":anidbUpdate", file.anidbUpdate); - d->addFileQuery.bindValue(":entryUpdate", file.entryUpdate); - d->addFileQuery.bindValue(":myUpdate", file.myUpdate); - d->addFileQuery.bindValue(":ed2k", file.ed2k); - d->addFileQuery.bindValue(":size", file.size); - d->addFileQuery.bindValue(":length", file.length); - d->addFileQuery.bindValue(":extension", file.extension); - d->addFileQuery.bindValue(":groupName", file.groupName); - d->addFileQuery.bindValue(":groupNameShort", file.groupNameShort); - d->addFileQuery.bindValue(":crc", file.crc); - d->addFileQuery.bindValue(":releaseDate", file.releaseDate); - d->addFileQuery.bindValue(":version", file.version); - d->addFileQuery.bindValue(":censored", file.censored); - d->addFileQuery.bindValue(":type", file.type); - d->addFileQuery.bindValue(":qualityId", file.qualityId); - d->addFileQuery.bindValue(":quality", file.quality); - d->addFileQuery.bindValue(":resolution", file.resolution); - d->addFileQuery.bindValue(":videoCodec", file.videoCodec); - d->addFileQuery.bindValue(":audioCodec", file.audioCodec); - d->addFileQuery.bindValue(":audioLanguage", file.audioLanguage); - d->addFileQuery.bindValue(":subtitleLanguage", file.subtitleLanguage); - d->addFileQuery.bindValue(":aspectRatio", file.aspectRatio); - d->addFileQuery.bindValue(":myWatched", file.myWatched); - d->addFileQuery.bindValue(":myState", file.myState); - d->addFileQuery.bindValue(":myFileState", file.myFileState); - d->addFileQuery.bindValue(":myStorage", file.myStorage); - d->addFileQuery.bindValue(":mySource", file.mySource); - d->addFileQuery.bindValue(":myOther", file.myOther); - - return exec(d->addFileQuery); + QSqlQuery &q = prepare( + "INSERT INTO file VALUES(:fid, :eid, :aid, :gid, :anidbUpdate, :entryUpdate, :myUpdate, " + " :ed2k, :size, :length, :extension, :groupName, :groupNameShort, " + " :crc, :releaseDate, :version, :censored, :type, :qualityId, " + " :quality, :resolution, :vidoeCodec, :audioCodec, :audioLanguage, " + " :subtitleLanguage, :aspectRatio, :myWatched, :myState, " + " :myFileState, :myStorage, :mySource, :myOther) "); + + q.bindValue(":fid", file.fid); + q.bindValue(":eid", file.eid); + q.bindValue(":aid", file.aid); + q.bindValue(":gid", file.gid); + q.bindValue(":anidbUpdate", file.anidbUpdate); + q.bindValue(":entryUpdate", file.entryUpdate); + q.bindValue(":myUpdate", file.myUpdate); + q.bindValue(":ed2k", file.ed2k); + q.bindValue(":size", file.size); + q.bindValue(":length", file.length); + q.bindValue(":extension", file.extension); + q.bindValue(":groupName", file.groupName); + q.bindValue(":groupNameShort", file.groupNameShort); + q.bindValue(":crc", file.crc); + q.bindValue(":releaseDate", file.releaseDate); + q.bindValue(":version", file.version); + q.bindValue(":censored", file.censored); + q.bindValue(":type", file.type); + q.bindValue(":qualityId", file.qualityId); + q.bindValue(":quality", file.quality); + q.bindValue(":resolution", file.resolution); + q.bindValue(":videoCodec", file.videoCodec); + q.bindValue(":audioCodec", file.audioCodec); + q.bindValue(":audioLanguage", file.audioLanguage); + q.bindValue(":subtitleLanguage", file.subtitleLanguage); + q.bindValue(":aspectRatio", file.aspectRatio); + q.bindValue(":myWatched", file.myWatched); + q.bindValue(":myState", file.myState); + q.bindValue(":myFileState", file.myFileState); + q.bindValue(":myStorage", file.myStorage); + q.bindValue(":mySource", file.mySource); + q.bindValue(":myOther", file.myOther); + + return exec(q); } bool Database::addFileEpisodeRel(const FileEpisodeRel &fileEpisodeRel) { - d->addFileEpisodeRelQuery.bindValue(":fid", fileEpisodeRel.fid); - d->addFileEpisodeRelQuery.bindValue(":eid", fileEpisodeRel.eid); - d->addFileEpisodeRelQuery.bindValue(":startPercent", fileEpisodeRel.startPercent); - d->addFileEpisodeRelQuery.bindValue(":endPercent", fileEpisodeRel.endPercent); + QSqlQuery &q = prepare( + "INSERT INTO file_episode_rel VALUES(:fid, :eid, " + " :startPercentage, :endPercentage) "); + + q.bindValue(":fid", fileEpisodeRel.fid); + q.bindValue(":eid", fileEpisodeRel.eid); + q.bindValue(":startPercent", fileEpisodeRel.startPercent); + q.bindValue(":endPercent", fileEpisodeRel.endPercent); - return exec(d->addFileEpisodeRelQuery); + return exec(q); } bool Database::addUnknownFile(const UnknownFile &file) { - d->addUnknownFileQuery.bindValue(":ed2k", file.ed2k); - d->addUnknownFileQuery.bindValue(":size", file.size); - d->addUnknownFileQuery.bindValue(":hostId", file.hostId); - d->addUnknownFileQuery.bindValue(":path", file.path); + QSqlQuery &q = prepare("INSERT INTO unknown_file VALUES(:ed2k, :size, :hostId, :path)"); + + q.bindValue(":ed2k", file.ed2k); + q.bindValue(":size", file.size); + q.bindValue(":hostId", file.hostId); + q.bindValue(":path", file.path); - return exec(d->addUnknownFileQuery); + return exec(q); } UnknownFile Database::getUnknownFile(const QByteArray &ed2k, qint64 size) { - d->getUnknownFileQuery.bindValue(":ed2k", ed2k); - d->getUnknownFileQuery.bindValue(":size", size); + QSqlQuery &q = prepare( + "SELECT ed2k, size, host_id, path " + " FROM unknown_file " + " WHERE ed2k = :ed2k " + " AND size = :size "); + + q.bindValue(":ed2k", ed2k); + q.bindValue(":size", size); - if (!exec(d->getUnknownFileQuery)) + if (!exec(q)) return UnknownFile(); UnknownFile f; - if (d->getUnknownFileQuery.next()) + if (q.next()) { - f.ed2k = d->getUnknownFileQuery.value(0).toByteArray(); - f.size = d->getUnknownFileQuery.value(1).toLongLong(); - f.hostId = d->getUnknownFileQuery.value(2).toInt(); - f.path = d->getUnknownFileQuery.value(3).toString(); + f = readUnknownFile(q); } - d->getUnknownFileQuery.finish(); + q.finish(); return f; } UnknownFile Database::getUnknownFileByPath(const QString &path) { - d->getUnknownFileByPathQuery.bindValue(":path", path); + QSqlQuery &q = prepare( + "SELECT ed2k, size, host_id, path " + " FROM unknown_file " + " WHERE path = :path "); + + q.bindValue(":path", path); - if (!exec(d->getUnknownFileByPathQuery)) + if (!exec(q)) return UnknownFile(); UnknownFile f; - if (d->getUnknownFileByPathQuery.next()) + if (q.next()) { - f.ed2k = d->getUnknownFileByPathQuery.value(0).toByteArray(); - f.size = d->getUnknownFileByPathQuery.value(1).toLongLong(); - f.hostId = d->getUnknownFileByPathQuery.value(2).toInt(); - f.path = d->getUnknownFileByPathQuery.value(3).toString(); + f = readUnknownFile(q); } - d->getUnknownFileByPathQuery.finish(); + q.finish(); return f; } bool Database::removeUnknownFile(const QByteArray &ed2k, qint64 size) { - d->removeUnknownFileQuery.bindValue(":ed2k", ed2k); - d->removeUnknownFileQuery.bindValue(":size", size); + QSqlQuery &q = prepare( + "DELETE FROM unknown_file " + " WHERE ed2k = :ed2k " + " AND size = :size "); + + q.bindValue(":ed2k", ed2k); + q.bindValue(":size", size); - return exec(d->removeUnknownFileQuery); + return exec(q); } bool Database::addRequest(const PendingRequest &request) { - d->addPendingRequestQuery.bindValue(":aid", request.aid); - d->addPendingRequestQuery.bindValue(":eid", request.eid); - d->addPendingRequestQuery.bindValue(":fid", request.fid); - d->addPendingRequestQuery.bindValue(":ed2k", request.ed2k.isNull() ? QByteArray("") : request.ed2k); - d->addPendingRequestQuery.bindValue(":size", request.size); + QSqlQuery &q = prepare( + "INSERT INTO pending_request VALUES(:aid, :eid, :fid, " + " :ed2k, :size, DEFAULT, DEFAULT, DEFAULT, DEFAULT) "); + + q.bindValue(":aid", request.aid); + q.bindValue(":eid", request.eid); + q.bindValue(":fid", request.fid); + q.bindValue(":ed2k", request.ed2k.isNull() ? QByteArray("") : request.ed2k); + q.bindValue(":size", request.size); - return exec(d->addPendingRequestQuery); + return exec(q); } QList Database::getRequestBatch(int limit) { - d->getRequestBatchQuery.bindValue(":limit", limit); + QSqlQuery &q = prepare( + "UPDATE pending_request SET start = NOW() " + " WHERE (aid, eid, fid, ed2k, size) IN " + " (SELECT aid, eid, fid, ed2k, size FROM pending_request " + " WHERE start IS NULL " + " ORDER BY priority DESC, added ASC " + " LIMIT :limit) " + " RETURNING aid, eid, fid, ed2k, size "); + + q.bindValue(":limit", limit); QList ret; - if (!exec(d->getRequestBatchQuery)) + if (!exec(q)) return ret; - while (d->getRequestBatchQuery.next()) + while (q.next()) { PendingRequest request; - request.aid = d->getRequestBatchQuery.value(0).toInt(); - request.eid = d->getRequestBatchQuery.value(1).toInt(); - request.fid = d->getRequestBatchQuery.value(2).toInt(); - request.ed2k = d->getRequestBatchQuery.value(3).toByteArray(); - request.size = d->getRequestBatchQuery.value(4).toLongLong(); + request.aid = q.value(0).toInt(); + request.eid = q.value(1).toInt(); + request.fid = q.value(2).toInt(); + request.ed2k = q.value(3).toByteArray(); + request.size = q.value(4).toLongLong(); ret << request; } - d->getRequestBatchQuery.finish(); + q.finish(); return ret; } bool Database::clearRequest(const PendingRequest &request) { - d->clearRequestQuery.bindValue(":aid", request.aid); - d->clearRequestQuery.bindValue(":eid", request.eid); - d->clearRequestQuery.bindValue(":fid", request.fid); - d->clearRequestQuery.bindValue(":ed2k", request.ed2k.isNull() ? QByteArray("") : request.ed2k); - d->clearRequestQuery.bindValue(":size", request.size); - - bool ret = exec(d->clearRequestQuery); - qDebug() << "AFFECTED" << d->clearRequestQuery.numRowsAffected(); + QSqlQuery &q = prepare( + "DELETE FROM pending_request " + " WHERE aid = :aid " + " AND eid = :eid " + " AND fid = :fid " + " AND ed2k = :ed2k " + " AND size = :size "); + + q.bindValue(":aid", request.aid); + q.bindValue(":eid", request.eid); + q.bindValue(":fid", request.fid); + q.bindValue(":ed2k", request.ed2k.isNull() ? QByteArray("") : request.ed2k); + q.bindValue(":size", request.size); + + bool ret = exec(q); + qDebug() << "AFFECTED" << q.numRowsAffected(); return ret; } bool Database::addPendingMyListUpdate(const PendingMyListUpdate &request) { - d->addPendingMyListUpdateQuery.bindValue(":fid", request.fid); - d->addPendingMyListUpdateQuery.bindValue(":setMyWatched", request.setMyWatched); - d->addPendingMyListUpdateQuery.bindValue(":myWatched", request.myWatched); - d->addPendingMyListUpdateQuery.bindValue(":setMyState", request.setMyState); - d->addPendingMyListUpdateQuery.bindValue(":myState", request.myState); - d->addPendingMyListUpdateQuery.bindValue(":setMyFileState", request.setMyFileState); - d->addPendingMyListUpdateQuery.bindValue(":myFileState", request.myFileState); - d->addPendingMyListUpdateQuery.bindValue(":setMyStorage", request.setMyStorage); - d->addPendingMyListUpdateQuery.bindValue(":myStorage", request.myStorage); - d->addPendingMyListUpdateQuery.bindValue(":setMySource", request.setMySource); - d->addPendingMyListUpdateQuery.bindValue(":mySource", request.mySource); - d->addPendingMyListUpdateQuery.bindValue(":setMyOther", request.setMyOther); - d->addPendingMyListUpdateQuery.bindValue(":myOther", request.myOther); + QSqlQuery &q = prepare( + "INSERT INTO pending_mylist_update VALUES(DEFAULT, :fid, :setMyWatched, :myWatched, " + " :setMyState, :myState, :setMyFileState, :myFileState, :setMyStorage, :myStorage, " + " :setMySource, :mySource, :setMyOther, :myOther, DEFAULT, DEFAULT, DEFAULT, DEFAULT) "); + + q.bindValue(":fid", request.fid); + q.bindValue(":setMyWatched", request.setMyWatched); + q.bindValue(":myWatched", request.myWatched); + q.bindValue(":setMyState", request.setMyState); + q.bindValue(":myState", request.myState); + q.bindValue(":setMyFileState", request.setMyFileState); + q.bindValue(":myFileState", request.myFileState); + q.bindValue(":setMyStorage", request.setMyStorage); + q.bindValue(":myStorage", request.myStorage); + q.bindValue(":setMySource", request.setMySource); + q.bindValue(":mySource", request.mySource); + q.bindValue(":setMyOther", request.setMyOther); + q.bindValue(":myOther", request.myOther); - return exec(d->addPendingMyListUpdateQuery); + return exec(q); } PendingMyListUpdate Database::getPendingMyListUpdate(qint64 updateId) { - d->getPendingMyListUpdateQuery.bindValue(":updateId", updateId); + QSqlQuery &q = prepare( + "SELECT update_id, fid, set_my_watched, my_watched, set_my_state, my_state, " + " set_my_file_state, my_file_state, set_my_storage, my_storage, set_my_source, " + " my_source, set_my_other, my_other, added, started, finished " + " FROM pending_mylist_update " + " WHERE update_id = :updateId "); + + q.bindValue(":updateId", updateId); PendingMyListUpdate request; - if (!exec(d->getPendingMyListUpdateQuery)) + if (!exec(q)) return request; - if (d->getPendingMyListUpdateQuery.next()) + if (q.next()) { - request.updateId = d->getPendingMyListUpdateQuery.value(0).toLongLong(); - request.fid = d->getPendingMyListUpdateQuery.value(1).toInt(); - request.setMyWatched = d->getPendingMyListUpdateQuery.value(2).toBool(); - request.myWatched = d->getPendingMyListUpdateQuery.value(3).toDateTime(); - request.setMyState = d->getPendingMyListUpdateQuery.value(4).toBool(); - request.myState = d->getPendingMyListUpdateQuery.value(5).toInt(); - request.setMyFileState = d->getPendingMyListUpdateQuery.value(6).toBool(); - request.myFileState = d->getPendingMyListUpdateQuery.value(7).toInt(); - request.setMyStorage = d->getPendingMyListUpdateQuery.value(8).toBool(); - request.myStorage = d->getPendingMyListUpdateQuery.value(9).toString(); - request.setMySource = d->getPendingMyListUpdateQuery.value(10).toBool(); - request.mySource = d->getPendingMyListUpdateQuery.value(11).toString(); - request.setMyOther = d->getPendingMyListUpdateQuery.value(12).toBool(); - request.myOther = d->getPendingMyListUpdateQuery.value(13).toString(); - request.added = d->getPendingMyListUpdateQuery.value(14).toDateTime(); - request.started = d->getPendingMyListUpdateQuery.value(15).toDateTime(); + request.updateId = q.value(0).toLongLong(); + request.fid = q.value(1).toInt(); + request.setMyWatched = q.value(2).toBool(); + request.myWatched = q.value(3).toDateTime(); + request.setMyState = q.value(4).toBool(); + request.myState = q.value(5).toInt(); + request.setMyFileState = q.value(6).toBool(); + request.myFileState = q.value(7).toInt(); + request.setMyStorage = q.value(8).toBool(); + request.myStorage = q.value(9).toString(); + request.setMySource = q.value(10).toBool(); + request.mySource = q.value(11).toString(); + request.setMyOther = q.value(12).toBool(); + request.myOther = q.value(13).toString(); + request.added = q.value(14).toDateTime(); + request.started = q.value(15).toDateTime(); } - d->getPendingMyListUpdateQuery.finish(); + q.finish(); return request; } QList Database::getPendingMyListUpdateBatch(int limit) { - d->getPendingMyListUpdateBatchQuery.bindValue(":limit", limit); + QSqlQuery &q = prepare( + "UPDATE pending_mylist_update SET started = NOW() " + " WHERE update_id IN " + " (SELECT update_id FROM pending_mylist_update " + " WHERE started IS NULL " + " ORDER BY added ASC " + " LIMIT :limit) " + " RETURNING update_id, fid, set_my_watched, my_watched, set_my_state, " + " my_state, set_my_file_state, my_file_state, set_my_storage, my_storage, " + " set_my_source, my_source, set_my_other, my_other, added, started "); + + q.bindValue(":limit", limit); QList ret; - if (!exec(d->getPendingMyListUpdateBatchQuery)) + if (!exec(q)) return ret; - while (d->getPendingMyListUpdateBatchQuery.next()) + while (q.next()) { PendingMyListUpdate request; - request.updateId = d->getPendingMyListUpdateBatchQuery.value(0).toLongLong(); - request.fid = d->getPendingMyListUpdateBatchQuery.value(1).toInt(); - request.setMyWatched = d->getPendingMyListUpdateBatchQuery.value(2).toBool(); - request.myWatched = d->getPendingMyListUpdateBatchQuery.value(3).toDateTime(); - request.setMyState = d->getPendingMyListUpdateBatchQuery.value(4).toBool(); - request.myState = d->getPendingMyListUpdateBatchQuery.value(5).toInt(); - request.setMyFileState = d->getPendingMyListUpdateBatchQuery.value(6).toBool(); - request.myFileState = d->getPendingMyListUpdateBatchQuery.value(7).toInt(); - request.setMyStorage = d->getPendingMyListUpdateBatchQuery.value(8).toBool(); - request.myStorage = d->getPendingMyListUpdateBatchQuery.value(9).toString(); - request.setMySource = d->getPendingMyListUpdateBatchQuery.value(10).toBool(); - request.mySource = d->getPendingMyListUpdateBatchQuery.value(11).toString(); - request.setMyOther = d->getPendingMyListUpdateBatchQuery.value(12).toBool(); - request.myOther = d->getPendingMyListUpdateBatchQuery.value(13).toString(); - request.added = d->getPendingMyListUpdateBatchQuery.value(14).toDateTime(); - request.started = d->getPendingMyListUpdateBatchQuery.value(15).toDateTime(); + request.updateId = q.value(0).toLongLong(); + request.fid = q.value(1).toInt(); + request.setMyWatched = q.value(2).toBool(); + request.myWatched = q.value(3).toDateTime(); + request.setMyState = q.value(4).toBool(); + request.myState = q.value(5).toInt(); + request.setMyFileState = q.value(6).toBool(); + request.myFileState = q.value(7).toInt(); + request.setMyStorage = q.value(8).toBool(); + request.myStorage = q.value(9).toString(); + request.setMySource = q.value(10).toBool(); + request.mySource = q.value(11).toString(); + request.setMyOther = q.value(12).toBool(); + request.myOther = q.value(13).toString(); + request.added = q.value(14).toDateTime(); + request.started = q.value(15).toDateTime(); ret << request; } - d->getPendingMyListUpdateBatchQuery.finish(); + q.finish(); return ret; } bool Database::clearPendingMyListUpdate(const PendingMyListUpdate &request) { - d->clearPendingMyListUpdateQuery.bindValue(":update_id", request.updateId); - return exec(d->clearPendingMyListUpdateQuery); + QSqlQuery &q = prepare( + "UPDATE pending_mylist_update SET finished = NOW() " + " WHERE update_id = :updateId "); + + q.bindValue(":update_id", request.updateId); + return exec(q); } QStringList Database::getWatchedDirectories(int hostId) @@ -1130,12 +1276,13 @@ bool Database::removeKnownUnknownFile(const File &file) bool Database::log(const QString &message, int type) { - d->addLogQuery.bindValue(":type", type); - d->addLogQuery.bindValue(":log", message); + QSqlQuery &q = prepare("INSERT INTO log (type, log) VALUES (:type, :log)"); + q.bindValue(":type", type); + q.bindValue(":log", message); qDebug() << "LOG:" << message << "; TYPE:" << type; - return exec(d->addLogQuery); + return exec(q); } QSqlDatabase Database::connection() const @@ -1172,7 +1319,7 @@ bool Database::connect() } QObject::connect(d->db.driver(), SIGNAL(notification(QString)), this, SLOT(handleNotification(QString))); - prepareQueries(); + subscribeToNotifications(); emit connected(); return success; @@ -1192,142 +1339,8 @@ void Database::disconnect() emit disconnected(); } -void Database::prepareQueries() -{ - d->getHostInfoQuery = QSqlQuery(d->db); - d->getHostInfoQuery.prepare("SELECT host_id, name, is_udp_host FROM host " - "WHERE name = :name"); - d->isKnownFileQuery = QSqlQuery(d->db); - d->isKnownFileQuery.prepare("SELECT fid FROM file WHERE ed2k = :ed2k AND size = :size"); - - d->getSettingsQuery = QSqlQuery(d->db); - d->getSettingsQuery.prepare("SELECT key, value FROM config"); - - d->updateSettingQuery = QSqlQuery(d->db); - d->updateSettingQuery.prepare("UPDATE config SET value = :value WHERE key = :key"); - - d->getAnimeQuery = QSqlQuery(d->db); - d->getAnimeQuery.prepare("SELECT aid, anidb_update, entry_update, my_update, title_english, " - "title_romaji, title_kanji, description, year, start_date, end_date, " - "type, rating, votes, temp_rating, temp_votes, my_vote, my_vote_date, " - "my_temp_vote, my_temp_vote_date " - "FROM anime " - "WHERE aid = :aid"); - - d->setAnimeQuery = QSqlQuery(d->db); - d->setAnimeQuery.prepare("UPDATE anime SET " - "anidb_update = :anidbUpdate, entry_update = :entryUpdate, " - "my_update = :myUpdate, title_english = :titleEnglish, " - "title_romaji = :titleRomaji, title_kanji = :titleKanji, " - "description = :description, year = :year, start_date = :startDate, " - "end_date = :endDate, type = :type, rating = :rating, votes = :votes, " - "temp_rating = :tempRating, temp_votes = :tempVotes, my_vote = :myVote, " - "my_vote_date = :myVoteDate, my_temp_vote = :myTempVote, " - "my_temp_vote_date = :myTempVoteDate " - "WHERE aid = :aid"); - - d->setEpisodeQuery = QSqlQuery(d->db); - d->setEpisodeQuery.prepare("UPDATE episode SET " - "aid = :aid, anidb_update = :anidbUpdate, entry_update = :entryUpdate, " - "my_update = :myUpdate, epno = :epno, title_english = :titleEnglish, " - "title_romaji = :titleRomaji, title_kanji = :titleKanji, length = :length, " - "airdate = :airdate, state = :state, special = :special, recap = :recap, " - "opening = :opening, ending = :ending, rating = :rating, votes = :votes, " - "my_vote = :myVote, my_vote_date = :myVoteDate " - "WHERE eid = :eid"); - - d->setFileQuery = QSqlQuery(d->db); - d->setFileQuery.prepare("UPDATE file SET " - "eid = :eid, aid = :aid, gid = :gid, anidb_update = :anidbUpdate, " - "entry_update = :entryUpdate, my_update = :myUpdate, " - "ed2k = :ed2k, size = :size, length = :length, extension = :extension, " - "group_name = :groupName, group_name_short = :groupNameShort, crc = :crc, " - "release_date = :releaseDate, version = :version, censored = :censored, " - "type = :type, quality_id = :qualityId, quality = :quality, " - "resolution = :resolution, video_codec = :videoCodec, " - "audio_codec = :audioCodec, audio_language = :audioLanguage, " - "subtitle_language = :subtitleLanguage, aspect_ratio = :aspectRatio, " - "my_watched = :myWatched, my_state = :myState, my_file_state = :myFileState, " - "my_storage = :myStorage, my_source = :mySource, my_other = :myOther " - "WHERE fid = :fid"); - - d->addLogQuery = QSqlQuery(d->db); - d->addLogQuery.prepare("INSERT INTO log (type, log) VALUES (:type, :log)"); - - d->addTitleQuery = QSqlQuery(d->db); - d->addTitleQuery.prepare("INSERT INTO anime_title VALUES(:aid, :type, :language, :title)"); - - d->addAnimeQuery = QSqlQuery(d->db); - d->addAnimeQuery.prepare("INSERT INTO anime VALUES(:aid, :anidbUpdate, :entryUpdate, :myUpdate, :titleEnglish, " - ":titleRomaji, :titleKanji, :description, :year, :startDate, :endDate, :type, " - ":rating, :votes, :tempRating, :tempVotes, :myVote, :myVoteDate, " - ":myTempVote, :myTempVoteDate)"); - d->addEpisodeQuery = QSqlQuery(d->db); - d->addEpisodeQuery.prepare("INSERT INTO episode VALUES(:eid, :aid, :anidbUpdate, :entryUpdate, :myUpdate, :epno, " - ":titleEnglish, :titleRomaji, :titleKanji, :length, :airdate, " - ":state, :special, :recap, :openineg, :ending, :rating, " - ":votes, :myVote, :myVoteDate)"); - d->addFileQuery = QSqlQuery(d->db); - d->addFileQuery.prepare("INSERT INTO file VALUES(:fid, :eid, :aid, :gid, :anidbUpdate, :entryUpdate, :myUpdate, " - ":ed2k, :size, :length, :extension, :groupName, :groupNameShort, " - ":crc, :releaseDate, :version, :censored, :type, :qualityId, " - ":quality, :resolution, :vidoeCodec, :audioCodec, :audioLanguage, " - ":subtitleLanguage, :aspectRatio, :myWatched, :myState, " - ":myFileState, :myStorage, :mySource, :myOther)"); - d->addFileEpisodeRelQuery = QSqlQuery(d->db); - d->addFileEpisodeRelQuery.prepare("INSERT INTO file_episode_rel VALUES(:fid, :eid, " - ":startPercentage, :endPercentage)"); - - d->addUnknownFileQuery = QSqlQuery(d->db); - d->addUnknownFileQuery.prepare("INSERT INTO unknown_file VALUES(:ed2k, :size, :hostId, :path)"); - - d->getUnknownFileQuery = QSqlQuery(d->db); - d->getUnknownFileQuery.prepare("SELECT ed2k, size, host_id, path FROM unknown_file WHERE ed2k = :ed2k AND size = :size"); - - d->getUnknownFileByPathQuery = QSqlQuery(d->db); - d->getUnknownFileByPathQuery.prepare("SELECT ed2k, size, host_id, path FROM unknown_file WHERE path = :path"); - - d->removeUnknownFileQuery = QSqlQuery(d->db); - d->removeUnknownFileQuery.prepare("DELETE FROM unknown_file WHERE ed2k = :ed2k AND size = :size"); - - d->addPendingRequestQuery = QSqlQuery(d->db); - d->addPendingRequestQuery.prepare("INSERT INTO pending_request VALUES(:aid, :eid, :fid, :ed2k, :size, DEFAULT, DEFAULT, DEFAULT, DEFAULT)"); - - d->getRequestBatchQuery = QSqlQuery(d->db); - d->getRequestBatchQuery.prepare("UPDATE pending_request SET start = NOW() " - "WHERE (aid, eid, fid, ed2k, size) IN (SELECT aid, eid, fid, ed2k, size FROM pending_request " - "WHERE start IS NULL " - "ORDER BY priority DESC, added ASC " - "LIMIT :limit) " - "RETURNING aid, eid, fid, ed2k, size"); - - d->clearRequestQuery = QSqlQuery(d->db); - d->clearRequestQuery.prepare("DELETE FROM pending_request WHERE aid = :aid AND eid = :eid AND fid = :fid AND ed2k = :ed2k AND size = :size"); - - d->addPendingMyListUpdateQuery = QSqlQuery(d->db); - d->addPendingMyListUpdateQuery.prepare("INSERT INTO pending_mylist_update VALUES(DEFAULT, :fid, :setMyWatched, :myWatched, " - ":setMyState, :myState, :setMyFileState, :myFileState, :setMyStorage, :myStorage, " - ":setMySource, :mySource, :setMyOther, :myOther, DEFAULT, DEFAULT, DEFAULT, DEFAULT)"); - - d->getPendingMyListUpdateBatchQuery = QSqlQuery(d->db); - d->getPendingMyListUpdateBatchQuery.prepare("UPDATE pending_mylist_update SET started = NOW() " - "WHERE update_id IN (SELECT update_id FROM pending_mylist_update " - " WHERE started IS NULL " - " ORDER BY added ASC " - " LIMIT :limit) " - "RETURNING update_id, fid, set_my_watched, my_watched, set_my_state, " - "my_state, set_my_file_state, my_file_state, set_my_storage, my_storage, " - "set_my_source, my_source, set_my_other, my_other, added, started"); - - d->getPendingMyListUpdateQuery = QSqlQuery(d->db); - d->getPendingMyListUpdateQuery.prepare("SELECT update_id, fid, set_my_watched, my_watched, set_my_state, my_state, " - "set_my_file_state, my_file_state, set_my_storage, my_storage, set_my_source, " - "my_source, set_my_other, my_other, added, started, finished " - "FROM pending_mylist_update WHERE update_id = :updateId"); - - d->clearPendingMyListUpdateQuery = QSqlQuery(d->db); - d->clearPendingMyListUpdateQuery.prepare("UPDATE pending_mylist_update SET finished = NOW() WHERE update_id = :updateId"); - +void Database::subscribeToNotifications() +{ d->db.driver()->subscribeToNotification("new_pending_request"); d->db.driver()->subscribeToNotification("new_pending_mylist_update"); d->db.driver()->subscribeToNotification("rename_data_changed"); @@ -1424,6 +1437,18 @@ File Database::readFile(QSqlQuery &q) return f; } +UnknownFile Database::readUnknownFile(QSqlQuery &q) +{ + UnknownFile f; + + f.ed2k = q.value(0).toByteArray(); + f.size = q.value(1).toLongLong(); + f.hostId = q.value(2).toInt(); + f.path = q.value(3).toString(); + + return f; +} + QSqlQuery &Database::prepare(const char *const sql) { auto it = d->preparedQueries.find(sql); diff --git a/localmylist/database.h b/localmylist/database.h index 7ce916a..d038ea1 100644 --- a/localmylist/database.h +++ b/localmylist/database.h @@ -323,12 +323,13 @@ private slots: void handleNotification(const QString ¬ification); private: - void prepareQueries(); + void subscribeToNotifications(); bool checkError(QSqlQuery &query, bool prepared = false); OpenFileData readOpenFileData(QSqlQuery &q); Episode readEpisode(QSqlQuery &q); File readFile(QSqlQuery &q); + UnknownFile readUnknownFile(QSqlQuery &q); DatabaseInternal *d; DatabaseConnectionSettings m_connectionSettings; -- 2.52.0