From 1093233102eb4ab5d7b9e5aa6253d50f46b2ae7f Mon Sep 17 00:00:00 2001 From: APTX Date: Sat, 14 Jul 2012 01:19:06 +0200 Subject: [PATCH] Move/Add various methods to get file data useful for opening a particular file to the Database class --- localmylist/database.cpp | 151 +++++++++++++++++++++++++++++++++- localmylist/database.h | 19 +++++ management-gui/mainwindow.cpp | 44 ++-------- play-next/main.cpp | 53 ++---------- 4 files changed, 184 insertions(+), 83 deletions(-) diff --git a/localmylist/database.cpp b/localmylist/database.cpp index c6daa68..5257633 100644 --- a/localmylist/database.cpp +++ b/localmylist/database.cpp @@ -1,5 +1,7 @@ #include "database.h" +#include "mylist.h" + #include #include #include @@ -109,6 +111,13 @@ HostInfo::HostInfo() isUdpHost = false; } +OpenFileData::OpenFileData() +{ + fid = 0; + epno = 0; +} + + DatabaseConnectionSettings::DatabaseConnectionSettings() { port = 0; @@ -218,7 +227,128 @@ bool Database::rollback() if (success) return true; qDebug() << "Commit Error:" << d->db.lastError(); - return false; +return false; +} + +OpenFileData Database::firstUnwatchedByTitle(const QString &title) +{ + QSqlQuery q(d->db); + q.prepare( + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path FROM file f " + " LEFT JOIN anime a ON f.aid = a.aid " + " LEFT JOIN anime_title at ON f.aid = at.aid " + " LEFT JOIN episode e ON f.eid = e.eid " + " LEFT JOIN file_location fl ON fl.fid = f.fid " + " WHERE f.my_watched IS NULL " + " AND lower(at.title) = :title " + " AND fl.path IS NOT NULL " + " AND fl.host_id = :hostId " + "UNION " + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path FROM file f " + " LEFT JOIN anime a ON f.aid = a.aid " + " LEFT JOIN anime_title at ON f.aid = at.aid " + " LEFT JOIN episode e ON f.eid = e.eid " + " LEFT JOIN file_location fl ON fl.fid = f.fid " + " WHERE f.my_watched IS NULL " + " AND at.title ILIKE :fuzzyTitle " + " AND fl.path IS NOT NULL " + " AND fl.host_id = :hostId " + "GROUP BY f.fid, a.title_romaji, e.title_english, e.epno, fl.path " + "ORDER BY epno ASC "); + q.bindValue(":title", title); + q.bindValue(":fuzzyTitle", "%" + title + "%"); + q.bindValue(":hostId", MyList::instance()->hostId()); + qDebug() << q.lastError().text(); + return readOpenFileData(q); +} + +OpenFileData Database::firstUnwatchedByAid(int aid) +{ + QSqlQuery q(d->db); + q.prepare( + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path FROM file f " + " LEFT JOIN anime a ON f.aid = a.aid " + " LEFT JOIN episode e ON f.eid = e.eid " + " LEFT JOIN file_location fl ON fl.fid = f.fid " + " WHERE f.my_watched IS NULL " + " AND f.aid = :aid " + " AND fl.path IS NOT NULL " + " AND fl.host_id = :hostId " + "ORDER BY e.epno ASC "); + q.bindValue(":aid", aid); + q.bindValue(":hostId", MyList::instance()->hostId()); + + return readOpenFileData(q); +} + +OpenFileData Database::openFileByTitle(const QString &title, int epno) +{ + if (epno == 0) + return firstUnwatchedByTitle(title); + + QSqlQuery q(d->db); + q.prepare( + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path FROM file f " + " LEFT JOIN anime a ON f.aid = a.aid " + " LEFT JOIN anime_title at ON f.aid = at.aid " + " LEFT JOIN episode e ON f.eid = e.eid " + " LEFT JOIN file_location fl ON fl.fid = f.fid " + " WHERE lower(at.title) = :title " + " AND e.epno = :epno " + " AND fl.path IS NOT NULL " + " AND fl.host_id = :hostId " + "UNION " + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path FROM file f " + " LEFT JOIN anime a ON f.aid = a.aid " + " LEFT JOIN anime_title at ON f.aid = at.aid " + " LEFT JOIN episode e ON f.eid = e.eid " + " LEFT JOIN file_location fl ON fl.fid = f.fid " + " WHERE at.title ILIKE :fuzzyTitle " + " AND e.epno = :epno " + " AND fl.path IS NOT NULL " + " AND fl.host_id = :hostId " + "GROUP BY f.fid, a.title_romaji, e.title_english, e.epno, fl.path "); + q.bindValue(":title", title); + q.bindValue(":fuzzyTitle", "%" + title + "%"); + q.bindValue(":epno", epno); + q.bindValue(":hostId", MyList::instance()->hostId()); + + return readOpenFileData(q); +} + +OpenFileData Database::openFileByEid(int eid) +{ + QSqlQuery q(d->db); + q.prepare( + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path FROM file f " + " LEFT JOIN anime a ON f.aid = a.aid " + " LEFT JOIN episode e ON f.eid = e.eid " + " LEFT JOIN file_location fl ON fl.fid = f.fid " + " WHERE f.eid = :eid " + " AND fl.path IS NOT NULL " + " AND fl.host_id = :hostId" + " ORDER BY f.version DESC "); + q.bindValue(":hostId", MyList::instance()->hostId()); + q.bindValue(":eid", eid); + + return readOpenFileData(q); +} + +OpenFileData Database::openFile(int fid) +{ + QSqlQuery q(d->db); + q.prepare( + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path FROM file f " + " LEFT JOIN anime a ON f.aid = a.aid " + " LEFT JOIN episode e ON f.eid = e.eid " + " 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"); + q.bindValue(":hostId", MyList::instance()->hostId()); + q.bindValue(":fid", fid); + + return readOpenFileData(q); } HostInfo Database::getHostInfo(const QString &hostName) @@ -1169,6 +1299,25 @@ void Database::prepareQueries() d->db.driver()->subscribeToNotification("config_changed"); } +OpenFileData Database::readOpenFileData(QSqlQuery &q) +{ + OpenFileData data; + + if (!exec(q)) + return data; + + if (!q.next()) + return data; + + data.fid = q.value(0).toInt(); + data.animeTitle = q.value(1).toString(); + data.episodeTitle = q.value(2).toString(); + data.epno = q.value(3).toInt(); + data.path = q.value(4).toString(); + + return data; +} + bool Database::exec(QSqlQuery &query) { Q_ASSERT_X(d->thread == QThread::currentThread(), "threads", "DB used from different thread"); diff --git a/localmylist/database.h b/localmylist/database.h index 416e88a..d56bca3 100644 --- a/localmylist/database.h +++ b/localmylist/database.h @@ -191,6 +191,17 @@ struct LOCALMYLISTSHARED_EXPORT HostInfo HostInfo(); }; +struct LOCALMYLISTSHARED_EXPORT OpenFileData +{ + int fid; + QString animeTitle; + QString episodeTitle; + int epno; + QString path; + + OpenFileData(); +}; + struct LOCALMYLISTSHARED_EXPORT DatabaseConnectionSettings { QString host; @@ -220,6 +231,12 @@ public: bool commit(); bool rollback(); + OpenFileData firstUnwatchedByTitle(const QString &title); + OpenFileData firstUnwatchedByAid(int aid); + OpenFileData openFileByTitle(const QString &title, int epno = 0); + OpenFileData openFileByEid(int eid); + OpenFileData openFile(int fid); + HostInfo getHostInfo(const QString &hostName); QVariantMap getConfig(); @@ -302,6 +319,8 @@ private slots: private: void prepareQueries(); + OpenFileData readOpenFileData(QSqlQuery &q); + DatabaseInternal *d; DatabaseConnectionSettings m_connectionSettings; diff --git a/management-gui/mainwindow.cpp b/management-gui/mainwindow.cpp index 05d4ddc..0670bbc 100644 --- a/management-gui/mainwindow.cpp +++ b/management-gui/mainwindow.cpp @@ -150,63 +150,33 @@ void MainWindow::on_myListView_openFileRequested(const QModelIndex &index) if (!id) return; - QString path; - QSqlQuery q(MyList::instance()->database()->connection()); + OpenFileData data; if (type == MyListModel::Anime) { - q.prepare( - "SELECT fl.path, e.epno, a.title_romaji, e.title_english FROM file f " - " LEFT JOIN anime a ON f.aid = a.aid " - " LEFT JOIN episode e ON f.eid = e.eid " - " LEFT JOIN file_location fl ON fl.fid = f.fid " - " WHERE f.my_watched IS NULL " - " AND f.aid = :aid " - " AND fl.path IS NOT NULL " - " GROUP BY fl.path, a.title_romaji, e.epno, e.title_english " - " ORDER BY epno ASC "); - q.bindValue(":aid", id); + data = MyList::instance()->database()->firstUnwatchedByAid(id); } else if (type == MyListModel::Episode) { - q.prepare( - "SELECT fl.path FROM file f " - " LEFT JOIN file_location fl ON fl.fid = f.fid " - " WHERE f.eid = :eid " - " AND fl.path IS NOT NULL " - " ORDER BY f.version DESC "); - q.bindValue(":eid", id); + data = MyList::instance()->database()->openFileByEid(id); } else if (type == MyListModel::File) { - q.prepare( - "SELECT fl.path FROM file f " - " LEFT JOIN file_location fl ON fl.fid = f.fid " - " WHERE f.fid = :fid " - " AND fl.path IS NOT NULL "); - q.bindValue(":fid", id); + data = MyList::instance()->database()->openFile(id); } else { return; } - if (!q.exec()) - { - qDebug() << q.lastError(); - return; - } - - if (!q.next()) + if (!data.fid) { ui->statusBar->showMessage(tr("No file found.")); return; } - path = q.value(0).toString(); - - QDesktopServices::openUrl(QUrl("file:///" + path, QUrl::TolerantMode)); - ui->statusBar->showMessage(tr("Openieng file: %1").arg(path)); + QDesktopServices::openUrl(QUrl("file:///" + data.path, QUrl::TolerantMode)); + ui->statusBar->showMessage(tr("Openieng file: %1").arg(data.path)); } diff --git a/play-next/main.cpp b/play-next/main.cpp index 2aec6ad..22a1930 100644 --- a/play-next/main.cpp +++ b/play-next/main.cpp @@ -7,7 +7,6 @@ #include "mylist.h" #include "database.h" -#include #include using namespace LocalMyList; @@ -34,54 +33,18 @@ int main(int argc, char *argv[]) QString title = args.join(QChar(' ')); - QString atitle, etitle; - QString path; - int epno; - { - QSqlQuery q(MyList::instance()->database()->connection()); - q.prepare( - "SELECT fl.path, e.epno, a.title_romaji, e.title_english FROM file f " - " LEFT JOIN anime a ON f.aid = a.aid " - " LEFT JOIN anime_title at ON f.aid = at.aid " - " LEFT JOIN episode e ON f.eid = e.eid " - " LEFT JOIN file_location fl ON fl.fid = f.fid " - " WHERE f.my_watched IS NULL " - " AND lower(at.title) = :title " - " AND fl.path IS NOT NULL " - "UNION " - "SELECT fl.path, e.epno, a.title_romaji, e.title_english FROM file f " - " LEFT JOIN anime a ON f.aid = a.aid " - " LEFT JOIN anime_title at ON f.aid = at.aid " - " LEFT JOIN episode e ON f.eid = e.eid " - " LEFT JOIN file_location fl ON fl.fid = f.fid " - " WHERE f.my_watched IS NULL " - " AND at.title ILIKE :title " - " AND fl.path IS NOT NULL " - "GROUP BY fl.path, a.title_romaji, e.epno, e.title_english " - "ORDER BY title_romaji ASC, epno ASC "); - q.bindValue(":title", title); - - if (!q.exec()) - { - qDebug() << q.lastError(); - return 1; - } + OpenFileData data = MyList::instance()->database()->firstUnwatchedByTitle(title); - if (!q.next()) - { - cout << "No file to watch."; - return 1; - } - path = q.value(0).toString(); - epno = q.value(1).toInt(); - atitle = q.value(2).toString(); - etitle = q.value(3).toString(); + if (!data.fid) + { + cout << "No file to watch."; + return 1; } - cout << "ANIME " << atitle << endl; - cout << "EPISODE " << epno << " - " << etitle << endl; + cout << "ANIME " << data.animeTitle << endl; + cout << "EPISODE " << data.epno << " - " << data.episodeTitle << endl; cout << "Starting player..." << endl; - QProcess::startDetached("D:/_C/aniplayer/build/aniplayer", QStringList() << path); + QProcess::startDetached("D:/_C/aniplayer/build/aniplayer", QStringList() << data.path); return 0; -- 2.52.0