From 6ecd0f1ead9262a7de83f6e58c4ba2e9fcaef0eb Mon Sep 17 00:00:00 2001
From: Arno <arno@disconnect.de>
Date: Sat, 31 Mar 2018 09:06:08 +0200
Subject: 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.
---
 fswidget.cpp | 62 +++++++++++++++++++++++++++++++++++++++++++++++++++++++-----
 1 file changed, 57 insertions(+), 5 deletions(-)

(limited to 'fswidget.cpp')

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;
+}
-- 
cgit v1.2.3-70-g09d2