From 42f1e3089f9d1498093bc4ddf1ff3137a1f7c94b Mon Sep 17 00:00:00 2001 From: APTX Date: Sat, 13 Apr 2013 14:45:56 +0200 Subject: [PATCH] Make opening files by title a bit more predictable --- localmylist/database.cpp | 68 +++++++++++++++++++++++++++------------- 1 file changed, 46 insertions(+), 22 deletions(-) diff --git a/localmylist/database.cpp b/localmylist/database.cpp index 0491c62..337678b 100644 --- a/localmylist/database.cpp +++ b/localmylist/database.cpp @@ -121,6 +121,25 @@ return false; OpenFileData Database::firstUnwatchedByTitle(const QString &title) { + OpenFileData ofd; + + { + QSqlQuery &q = prepare( + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id 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) = lower(:title) " + " AND fl.path IS NOT NULL "); + q.bindValue(":title", title); + + ofd = readOpenFileData(q); + if (ofd.fid) + return ofd; + } + QSqlQuery &q = prepare( "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id FROM file f " " LEFT JOIN anime a ON f.aid = a.aid " @@ -128,23 +147,15 @@ OpenFileData Database::firstUnwatchedByTitle(const QString &title) " 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 f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id 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 " "GROUP BY f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id " "ORDER BY epno ASC "); - q.bindValue(":title", title); q.bindValue(":fuzzyTitle", "%" + title + "%"); - return readOpenFileData(q); + ofd = readOpenFileData(q); + + return ofd; } OpenFileData Database::firstUnwatchedByAid(int aid) @@ -168,30 +179,43 @@ OpenFileData Database::openFileByTitle(const QString &title, int epno) if (epno == 0) return firstUnwatchedByTitle(title); + OpenFileData ofd; + + { + QSqlQuery &q = prepare( + "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id 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) = lower(:title) " + " AND e.epno = :epno " + " AND fl.path IS NOT NULL "); + q.bindValue(":title", title); + q.bindValue(":epno", epno); + + ofd = readOpenFileData(q); + } + + if (ofd.fid) + return ofd; + QSqlQuery &q = prepare( "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id 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 " - "UNION " - "SELECT f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id 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 " "GROUP BY f.fid, a.title_romaji, e.title_english, e.epno, fl.path, fl.host_id "); - q.bindValue(":title", title); q.bindValue(":fuzzyTitle", "%" + title + "%"); q.bindValue(":epno", epno); - return readOpenFileData(q); + ofd = readOpenFileData(q); + + return ofd; } OpenFileData Database::openFileByEid(int eid) -- 2.52.0