Allow clicking cells with foreign key in order to jump to referenced cell

MKleusberg · MKleusberg · commit 39a49e33c2e4 · 2015-06-21T23:59:45.000+02:00
Add a tooltip to the database browser when you hover a cell with a foreign key set in order to show the referenced table and column. When clicking on such a cell while holding the Ctrl and Shift key (only one of them won't work because they are for multiselection and Alt doesn't do the trick on my system because it's just for grabbing and moving the window) try to jump to the table and row which is referenced in the clicked cell. See issue #192.
diff --git a/src/ExtendedTableWidget.cpp b/src/ExtendedTableWidget.cpp
@@ -1,6 +1,7 @@
 #include "ExtendedTableWidget.h"
 #include "sqlitetablemodel.h"
 #include "FilterTableHeader.h"
+#include "sqlitetypes.h"
 
 #include <QApplication>
 #include <QClipboard>
@@ -15,6 +16,7 @@ ExtendedTableWidget::ExtendedTableWidget(QWidget* parent) :
     setHorizontalScrollMode(ExtendedTableWidget::ScrollPerPixel);
 
     connect(verticalScrollBar(), SIGNAL(valueChanged(int)), this, SLOT(vscrollbarChanged(int)));
+    connect(this, SIGNAL(clicked(QModelIndex)), this, SLOT(cellClicked(QModelIndex)));
 
     // Set up filter row
     m_tableHeader = new FilterTableHeader(this);
@@ -125,3 +127,16 @@ QSet<int> ExtendedTableWidget::selectedCols()
         selectedCols.insert(idx.column());
     return selectedCols;
 }
+
+void ExtendedTableWidget::cellClicked(const QModelIndex& index)
+{
+    // If Alt key is pressed try to jump to the row referenced by the foreign key of the clicked cell
+    if(qApp->keyboardModifiers().testFlag(Qt::ControlModifier) && qApp->keyboardModifiers().testFlag(Qt::ShiftModifier) && model())
+    {
+        SqliteTableModel* m = qobject_cast<SqliteTableModel*>(model());
+        sqlb::ForeignKeyClause fk = m->getForeignKeyClause(index.column()-1);
+
+        if(fk.isSet())
+            emit foreignKeyClicked(fk.table(), fk.columns().size() ? fk.columns().at(0) : "", m->data(index, Qt::EditRole).toByteArray());
+    }
+}
diff --git a/src/ExtendedTableWidget.h b/src/ExtendedTableWidget.h
@@ -17,12 +17,16 @@ class ExtendedTableWidget : public QTableView
 public:
     QSet<int> selectedCols();
 
+signals:
+    void foreignKeyClicked(const QString& table, const QString& column, const QByteArray& value);
+
 private:
     void copy();
     int numVisibleRows();
 
 private slots:
     void vscrollbarChanged(int value);
+    void cellClicked(const QModelIndex& index);
 
 protected:
     virtual void keyPressEvent(QKeyEvent* event);
diff --git a/src/FilterTableHeader.cpp b/src/FilterTableHeader.cpp
@@ -129,3 +129,9 @@ void FilterTableHeader::clearFilters()
     foreach (FilterLineEdit* filterLineEdit, filterWidgets)
         filterLineEdit->clear();
 }
+
+void FilterTableHeader::setFilter(int column, const QString& value)
+{
+    if(column < filterWidgets.size())
+        filterWidgets.at(column)->setText(value);
+}
diff --git a/src/FilterTableHeader.h b/src/FilterTableHeader.h
@@ -20,6 +20,7 @@ public slots:
     void generateFilters(int number, bool bKeepValues = false);
     void adjustPositions();
     void clearFilters();
+    void setFilter(int column, const QString& value);
 
 signals:
     void filterChanged(int column, QString value);
diff --git a/src/MainWindow.cpp b/src/MainWindow.cpp
@@ -2151,3 +2151,27 @@ void MainWindow::on_comboPointShape_currentIndexChanged(int index)
     }
     ui->plotWidget->replot();
 }
+
+void MainWindow::jumpToRow(const QString& table, QString column, const QByteArray& value)
+{
+    // First check if table exists
+    DBBrowserObject obj = db.getObjectByName(table);
+    if(obj.getname().size() == 0)
+        return;
+
+    // If no column name is set, assume the primary key is meant
+    if(!column.size())
+        column = obj.table.fields().at(obj.table.findPk())->name();
+
+    // If column doesn't exist don't do anything
+    int column_index = obj.table.findField(column);
+    if(column_index == -1)
+        return;
+
+    // Jump to table
+    ui->comboBrowseTable->setCurrentIndex(ui->comboBrowseTable->findText(table));
+    populateTable(table);
+
+    // Set filter
+    ui->dataTable->filterHeader()->setFilter(column_index+1, value);
+}
diff --git a/src/MainWindow.h b/src/MainWindow.h
@@ -110,6 +110,7 @@ public slots:
     void logSql(const QString &sql, int msgtype);
     void dbState(bool dirty);
     void browseRefresh();
+    void jumpToRow(const QString& table, QString column, const QByteArray& value);
 
 private slots:
     void createTreeContextMenu(const QPoint & qPoint);
diff --git a/src/MainWindow.ui b/src/MainWindow.ui
@@ -324,8 +324,8 @@
             <rect>
              <x>0</x>
              <y>0</y>
-             <width>311</width>
-             <height>531</height>
+             <width>509</width>
+             <height>458</height>
             </rect>
            </property>
            <layout class="QFormLayout" name="formLayout">
@@ -810,7 +810,7 @@
      <x>0</x>
      <y>0</y>
      <width>1037</width>
-     <height>25</height>
+     <height>19</height>
     </rect>
    </property>
    <widget class="QMenu" name="fileMenu">
@@ -1766,6 +1766,9 @@
    <class>ExtendedTableWidget</class>
    <extends>QTableWidget</extends>
    <header>ExtendedTableWidget.h</header>
+   <slots>
+    <signal>foreignKeyClicked(QString,QString,QByteArray)</signal>
+   </slots>
   </customwidget>
   <customwidget>
    <class>SqlTextEdit</class>
@@ -2664,6 +2667,22 @@
     </hint>
    </hints>
   </connection>
+  <connection>
+   <sender>dataTable</sender>
+   <signal>foreignKeyClicked(QString,QString,QByteArray)</signal>
+   <receiver>MainWindow</receiver>
+   <slot>jumpToRow(QString,QString,QByteArray)</slot>
+   <hints>
+    <hint type="sourcelabel">
+     <x>60</x>
+     <y>105</y>
+    </hint>
+    <hint type="destinationlabel">
+     <x>518</x>
+     <y>314</y>
+    </hint>
+   </hints>
+  </connection>
  </connections>
  <slots>
   <slot>fileOpen()</slot>
@@ -2719,5 +2738,6 @@
   <slot>saveSqlFileAs()</slot>
   <slot>switchToBrowseDataTab()</slot>
   <slot>copyCurrentCreateStatement()</slot>
+  <slot>jumpToRow(QString,QString,QByteArray)</slot>
  </slots>
 </ui>
diff --git a/src/sqlitetablemodel.cpp b/src/sqlitetablemodel.cpp
@@ -265,11 +265,26 @@ QVariant SqliteTableModel::data(const QModelIndex &index, int role) const
         if(m_data.at(index.row()).at(index.column()).isNull())
             return QColor(PreferencesDialog::getSettingsValue("databrowser", "null_bg_colour").toString());
         return QVariant();
+    } else if(role == Qt::ToolTipRole) {
+        sqlb::ForeignKeyClause fk = getForeignKeyClause(index.column()-1);
+        if(fk.isSet())
+            return tr("References %1(%2)\nHold Ctrl+Shift and click to jump there").arg(fk.table()).arg(fk.columns().join(','));
+        else
+            return QString();
     } else {
         return QVariant();
     }
 }
 
+sqlb::ForeignKeyClause SqliteTableModel::getForeignKeyClause(int column) const
+{
+    DBBrowserObject obj = m_db->getObjectByName(m_sTable);
+    if(obj.getname().size())
+        return obj.table.fields().at(column)->foreignKey();
+    else
+        return sqlb::ForeignKeyClause();
+}
+
 bool SqliteTableModel::setData(const QModelIndex& index, const QVariant& value, int role)
 {
     if(index.isValid() && role == Qt::EditRole)
diff --git a/src/sqlitetablemodel.h b/src/sqlitetablemodel.h
@@ -6,6 +6,7 @@
 #include <QVector>
 
 class DBBrowserDB;
+namespace sqlb { class ForeignKeyClause; }
 
 class SqliteTableModel : public QAbstractTableModel
 {
@@ -41,6 +42,8 @@ class SqliteTableModel : public QAbstractTableModel
 
     typedef QList<QByteArray> QByteArrayList;
 
+    sqlb::ForeignKeyClause getForeignKeyClause(int column) const;
+
 signals:
     
 public slots:
