Search database during FSWidget::gatherData

First, look for md5 in files and files_origin. Then look for the complete
filename in files, and finally for the filename without suffix in files_origin.
If we have a match, note it in the new column "Presence". Matches from
files are displayed green, matches from files_orgin blue.

This implementation tries to execute as few QSqlQueries as possible by
using goto for performance. We only want to know if the file is somewhere
present, so skip the remaining queries once we have a match.

1 files changed, 57 insertions, 5 deletions

diff --git a/fswidget.cpp b/fswidget.cpp
index 658b7ed..c9da947 100644
--- a/fswidget.cpp
+++ b/fswidget.cpp
@@ -11,6 +11,7 @@
 #include <QMimeDatabase>
 #include <QDirIterator>
 #include <QJsonObject>
+#include <QSqlDatabase>
 #include <QSettings>
 #include <QApplication>
 
@@ -154,16 +155,31 @@ void FSWidget::removeItem(QComboBox *cb){
 }
 
 void FSWidget::gatherData(const QString &curDir){
+    //setup view
     mFileView->setSortingEnabled(false);
     mFileView->setRootIsDecorated(false);
     mModel->clear();
     QStandardItem *root = mModel->invisibleRootItem();
     QMimeDatabase mimedb;
-    mModel->setHorizontalHeaderLabels(QStringList() << tr("Name") << tr("MIME") << tr("Size") << tr("Duration") << tr("MD5"));
+    mModel->setHorizontalHeaderLabels(QStringList() << tr("Name") << tr("MIME") << tr("Size") << tr("Duration") << tr("MD5") << tr("Presence"));
     QIcon videoIcon = Helper::icon(QColor(255,85,255), 'M');
     QIcon imageIcon = Helper::icon(QColor(255,85,255), 'P');
     QIcon otherIcon = Helper::icon(QColor(255,85,255), 'O');
     QBrush blackBrush(Qt::black);
+    QBrush greenBrush(Qt::darkGreen);
+    QBrush blueBrush(Qt::darkBlue);
+    QBrush currentBrush = blackBrush;
+
+    //setup database
+    QSqlDatabase db = QSqlDatabase::database("treedb");
+    QSqlQuery filesMd5Q(db);
+    filesMd5Q.prepare("SELECT COUNT(*) FROM files WHERE cmd5sum = :arg");
+    QSqlQuery originMd5Q(db);
+    originMd5Q.prepare("SELECT COUNT(*) FROM files_origin WHERE cmd5sum = :arg");
+    QSqlQuery filesNameQ(db);
+    filesNameQ.prepare("SELECT COUNT(*) FROM files WHERE tfilename = :arg");
+    QSqlQuery originNameQ(db);
+    originNameQ.prepare("SELECT COUNT(*) FROM files WHERE tfilename LIKE :arg");
 
     QDirIterator it(curDir, QDir::Files);
     mFileView->setUpdatesEnabled(false);
@@ -187,11 +203,36 @@ void FSWidget::gatherData(const QString &curDir){
         }
         QList<QStandardItem*> items;
         QFont f("courier new");
-        for(int i = 0; i < 5; ++i){
+        QString presenceStr = tr("None");
+        QString likeArg = QString("%1%%").arg(fi.completeBaseName());
+
+        //do this goto dance to execute as few queries as possible
+        if(queryCount(filesMd5Q, md5) > 0){
+            currentBrush = greenBrush;
+            presenceStr = tr("Files: MD5");
+            goto ci;
+        }
+        if(queryCount(originMd5Q, md5)){
+            currentBrush = blueBrush;
+            presenceStr = tr("Origin: MD5");
+            goto ci;
+        }
+        if(queryCount(filesNameQ, fi.fileName())){
+            currentBrush = greenBrush;
+            presenceStr = tr("Files: Name");
+            goto ci;
+        }
+        if(queryCount(originNameQ, likeArg)){
+            currentBrush = blueBrush;
+            presenceStr = tr("Origin: Name");
+        }
+
+        ci:
+        for(int i = 0; i < 6; ++i){
             QStandardItem *item = new QStandardItem;
             item->setFont(f);
             item->setEditable(false);
-            item->setForeground(blackBrush);
+            item->setForeground(currentBrush);
             items << item;
         }
         items[0]->setText(fi.fileName());
@@ -204,14 +245,25 @@ void FSWidget::gatherData(const QString &curDir){
         }
         items[1]->setText(mimeType.name());
         items[2]->setText(size);
-        items[2]->setTextAlignment(Qt::AlignRight);
+        items[2]->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
         items[3]->setText(durStr);
         items[4]->setText(md5);
+        items[5]->setText(presenceStr);
         root->appendRow(items);
-        for(int i = 0; i < 5; ++i){
+        for(int i = 0; i < 6; ++i){
             mFileView->resizeColumnToContents(i);
         }
     }
     mFileView->setUpdatesEnabled(true);
     mFileView->setSortingEnabled(true);
 }
+
+int FSWidget::queryCount(QSqlQuery &q, const QString &arg) const{
+    int retval = -1;
+    q.bindValue(":arg", arg);
+    q.exec();
+    while(q.next()){
+        retval = q.value(0).toInt();
+    }
+    return retval;
+}