Skip to content

Commit 3b455af

Browse files
mgrojomgrojo
authored
Allow custom display formats (#1720)
Allow user to define their own display formats. The SQL code is editable and the combo box changes automatically to custom if the user edits one of the predefined formats. The display format (either custom or predefined) is validated with a case-insensitive regex so at least it contains a function applied to the column name. Added a new callback for executeSQL following the model of sqlite3_exec. Using this, the number of columns is got from a checking query execution. If it is not one, the custom display format is also rejected. Note that these validations might still be fooled in some unforeseen way, but the users should know what they're doing. See issue #573
1 parent 14c82ea commit 3b455af

File tree

6 files changed

+114
-21
lines changed

6 files changed

+114
-21
lines changed

‎src/ColumnDisplayFormatDialog.cpp‎

Lines changed: 52 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,16 @@
1+
#include <QMessageBox>
2+
13
#include "ColumnDisplayFormatDialog.h"
24
#include "ui_ColumnDisplayFormatDialog.h"
35
#include "sql/sqlitetypes.h"
6+
#include "sqlitedb.h"
47

5-
ColumnDisplayFormatDialog::ColumnDisplayFormatDialog(const QString& colname, QString current_format, QWidget* parent)
8+
ColumnDisplayFormatDialog::ColumnDisplayFormatDialog(DBBrowserDB& db, const sqlb::ObjectIdentifier& tableName, const QString& colname, QString current_format, QWidget* parent)
69
: QDialog(parent),
710
ui(new Ui::ColumnDisplayFormatDialog),
8-
column_name(colname)
11+
column_name(colname),
12+
pdb(db),
13+
curTable(tableName)
914
{
1015
// Create UI
1116
ui->setupUi(this);
@@ -28,6 +33,9 @@ ColumnDisplayFormatDialog::ColumnDisplayFormatDialog(const QString& colname, QSt
2833
ui->comboDisplayFormat->insertSeparator(ui->comboDisplayFormat->count());
2934
ui->comboDisplayFormat->addItem(tr("Lower case"), "lower");
3035
ui->comboDisplayFormat->addItem(tr("Upper case"), "upper");
36+
ui->comboDisplayFormat->insertSeparator(ui->comboDisplayFormat->count());
37+
ui->comboDisplayFormat->addItem(tr("Custom"), "custom");
38+
3139
ui->labelDisplayFormat->setText(ui->labelDisplayFormat->text().arg(column_name));
3240

3341
formatFunctions["decimal"] = "printf('%d', " + sqlb::escapeIdentifier(column_name) + ")";
@@ -53,19 +61,14 @@ ColumnDisplayFormatDialog::ColumnDisplayFormatDialog(const QString& colname, QSt
5361
ui->comboDisplayFormat->setCurrentIndex(0);
5462
updateSqlCode();
5563
} else {
56-
QString formatName;
64+
// When it doesn't match any predefined format, it is considered custom
65+
QString formatName = "custom";
5766
for(auto& formatKey : formatFunctions.keys()) {
5867
if(current_format == formatFunctions.value(formatKey)) {
5968
formatName = formatKey;
6069
break;
6170
}
6271
}
63-
64-
if(formatName.isEmpty()) {
65-
ui->comboDisplayFormat->insertSeparator(ui->comboDisplayFormat->count());
66-
ui->comboDisplayFormat->addItem(tr("Custom"), "custom");
67-
formatName = "custom";
68-
}
6972
ui->comboDisplayFormat->setCurrentIndex(ui->comboDisplayFormat->findData(formatName));
7073
ui->editDisplayFormat->setText(current_format);
7174
}
@@ -90,7 +93,46 @@ void ColumnDisplayFormatDialog::updateSqlCode()
9093

9194
if(format == "default")
9295
ui->editDisplayFormat->setText(sqlb::escapeIdentifier(column_name));
93-
else
96+
else if(format != "custom")
9497
ui->editDisplayFormat->setText(formatFunctions.value(format));
98+
}
99+
100+
void ColumnDisplayFormatDialog::accept()
101+
{
102+
QString errorMessage;
103+
104+
// Accept the SQL code if it's the column name (default), it contains a function invocation applied to the column name and it can be
105+
// executed without errors returning only one column.
106+
// Users could still devise a way to break this, but this is considered good enough for letting them know about simple incorrect
107+
// cases.
108+
if(!(ui->editDisplayFormat->text() == sqlb::escapeIdentifier(column_name) ||
109+
ui->editDisplayFormat->text().contains(QRegExp("[a-z]+[a-z_0-9]* *\\(.*" + QRegExp::escape(sqlb::escapeIdentifier(column_name)) + ".*\\)", Qt::CaseInsensitive))))
110+
errorMessage = tr("Custom display format must contain a function call applied to %1").arg(sqlb::escapeIdentifier(column_name));
111+
else {
112+
// Execute a query using the display format and check that it only returns one column.
113+
int customNumberColumns = 0;
114+
115+
DBBrowserDB::execCallback callback = [&customNumberColumns](int numberColumns, QStringList, QStringList) -> bool {
116+
customNumberColumns = numberColumns;
117+
// Return false so the query is not aborted and no error is reported.
118+
return false;
119+
};
120+
if(!pdb.executeSQL(QString("SELECT %1 FROM %2 LIMIT 1").arg(ui->editDisplayFormat->text(), curTable.toString()),
121+
false, true, callback))
122+
errorMessage = tr("Error in custom display format. Message from database engine:\n\n%1").arg(pdb.lastError());
123+
else if(customNumberColumns != 1)
124+
errorMessage = tr("Custom display format must return only one column but it returned %1.").arg(customNumberColumns);
125+
126+
}
127+
if(!errorMessage.isEmpty())
128+
QMessageBox::warning(this, QApplication::applicationName(), errorMessage);
129+
else
130+
QDialog::accept();
131+
}
95132

133+
void ColumnDisplayFormatDialog::setCustom(bool modified)
134+
{
135+
// If the SQL code is modified by user, select the custom value in the combo-box
136+
if(modified && ui->editDisplayFormat->hasFocus())
137+
ui->comboDisplayFormat->setCurrentIndex(ui->comboDisplayFormat->findData("custom"));
96138
}

‎src/ColumnDisplayFormatDialog.h‎

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,10 @@
55
#include <QString>
66
#include <QMap>
77

8+
#include "sql/sqlitetypes.h"
9+
10+
class DBBrowserDB;
11+
812
namespace Ui {
913
class ColumnDisplayFormatDialog;
1014
}
@@ -14,18 +18,22 @@ class ColumnDisplayFormatDialog : public QDialog
1418
Q_OBJECT
1519

1620
public:
17-
explicit ColumnDisplayFormatDialog(const QString& colname, QString current_format, QWidget* parent = nullptr);
21+
explicit ColumnDisplayFormatDialog(DBBrowserDB& db, const sqlb::ObjectIdentifier& tableName, const QString& colname, QString current_format, QWidget* parent = nullptr);
1822
~ColumnDisplayFormatDialog() override;
1923

2024
QString selectedDisplayFormat() const;
2125

2226
private slots:
2327
void updateSqlCode();
28+
void accept() override;
29+
void setCustom(bool modified);
2430

2531
private:
2632
Ui::ColumnDisplayFormatDialog* ui;
2733
QString column_name;
2834
QMap<QString, QString> formatFunctions;
35+
DBBrowserDB& pdb;
36+
sqlb::ObjectIdentifier curTable;
2937
};
3038

3139
#endif

‎src/ColumnDisplayFormatDialog.ui‎

Lines changed: 18 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -31,11 +31,7 @@
3131
<widget class="QComboBox" name="comboDisplayFormat"/>
3232
</item>
3333
<item>
34-
<widget class="SqlTextEdit" name="editDisplayFormat">
35-
<property name="readOnly">
36-
<bool>true</bool>
37-
</property>
38-
</widget>
34+
<widget class="SqlTextEdit" name="editDisplayFormat"/>
3935
</item>
4036
</layout>
4137
</widget>
@@ -114,8 +110,25 @@
114110
</hint>
115111
</hints>
116112
</connection>
113+
<connection>
114+
<sender>editDisplayFormat</sender>
115+
<signal>modificationChanged(bool)</signal>
116+
<receiver>ColumnDisplayFormatDialog</receiver>
117+
<slot>setCustom(bool)</slot>
118+
<hints>
119+
<hint type="sourcelabel">
120+
<x>125</x>
121+
<y>69</y>
122+
</hint>
123+
<hint type="destinationlabel">
124+
<x>244</x>
125+
<y>4</y>
126+
</hint>
127+
</hints>
128+
</connection>
117129
</connections>
118130
<slots>
119131
<slot>updateSqlCode()</slot>
132+
<slot>setCustom()</slot>
120133
</slots>
121134
</ui>

‎src/MainWindow.cpp‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3348,7 +3348,7 @@ void MainWindow::editDataColumnDisplayFormat()
33483348
QString current_displayformat = browseTableSettings[current_table].displayFormats[field_number];
33493349

33503350
// Open the dialog
3351-
ColumnDisplayFormatDialog dialog(field_name, current_displayformat, this);
3351+
ColumnDisplayFormatDialog dialog(db, current_table, field_name, current_displayformat, this);
33523352
if(dialog.exec())
33533353
{
33543354
// Set the newly selected display format

‎src/sqlitedb.cpp‎

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -890,7 +890,23 @@ bool DBBrowserDB::dump(const QString& filePath,
890890
return false;
891891
}
892892

893-
bool DBBrowserDB::executeSQL(QString statement, bool dirtyDB, bool logsql)
893+
// Callback for sqlite3_exec. It receives the user callback in the first parameter. Converts parameters
894+
// to C++ classes and calls user callback.
895+
int DBBrowserDB::callbackWrapper (void* callback, int numberColumns, char** values, char** columnNames)
896+
{
897+
QStringList valuesList;
898+
QStringList namesList;
899+
900+
for (int i=0; i<numberColumns; i++) {
901+
valuesList << QString(values[i]);
902+
namesList << QString(columnNames[i]);
903+
}
904+
905+
execCallback userCallback = *(static_cast<execCallback*>(callback));
906+
return userCallback(numberColumns, valuesList, namesList);
907+
}
908+
909+
bool DBBrowserDB::executeSQL(QString statement, bool dirtyDB, bool logsql, execCallback callback)
894910
{
895911
waitForDbRelease();
896912
if(!_db)
@@ -905,7 +921,7 @@ bool DBBrowserDB::executeSQL(QString statement, bool dirtyDB, bool logsql)
905921
if (dirtyDB) setSavepoint();
906922

907923
char* errmsg;
908-
if (SQLITE_OK == sqlite3_exec(_db, statement.toUtf8(), nullptr, nullptr, &errmsg))
924+
if (SQLITE_OK == sqlite3_exec(_db, statement.toUtf8(), callback ? callbackWrapper : nullptr, &callback, &errmsg))
909925
{
910926
// Update DB structure after executing an SQL statement. But try to avoid doing unnecessary updates.
911927
if(!dontCheckForStructureUpdates && (statement.startsWith("ALTER", Qt::CaseInsensitive) ||
@@ -919,6 +935,7 @@ bool DBBrowserDB::executeSQL(QString statement, bool dirtyDB, bool logsql)
919935
lastErrorMessage = QString("%1 (%2)").arg(QString::fromUtf8(errmsg)).arg(statement);
920936
qWarning() << "executeSQL: " << statement << "->" << errmsg;
921937
sqlite3_free(errmsg);
938+
922939
return false;
923940
}
924941
}

‎src/sqlitedb.h‎

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@
66
#include <condition_variable>
77
#include <memory>
88
#include <mutex>
9+
#include <functional>
910

1011
#include <QByteArray>
1112
#include <QMultiMap>
@@ -54,6 +55,7 @@ class DBBrowserDB : public QObject
5455
};
5556

5657
public:
58+
5759
explicit DBBrowserDB () : _db(nullptr), db_used(false), isEncrypted(false), isReadOnly(false), dontCheckForStructureUpdates(false) {}
5860
~DBBrowserDB () override {}
5961

@@ -104,8 +106,17 @@ class DBBrowserDB : public QObject
104106
Wait,
105107
CancelOther
106108
};
107-
108-
bool executeSQL(QString statement, bool dirtyDB = true, bool logsql = true);
109+
// Callback to get results from executeSQL(). It is invoked for
110+
// each result row coming out of the evaluated SQL statements. If
111+
// a callback returns true (abort), the executeSQL() method
112+
// returns false (error) without invoking the callback again and
113+
// without running any subsequent SQL statements. The 1st argument
114+
// is the number of columns in the result. The 2nd argument to the
115+
// callback is the text representation of the values, one for each
116+
// column. The 3rd argument is a list of strings where each entry
117+
// represents the name of corresponding result column.
118+
typedef std::function<bool(int, QStringList, QStringList)> execCallback;
119+
bool executeSQL(QString statement, bool dirtyDB = true, bool logsql = true, execCallback callback = nullptr);
109120
bool executeMultiSQL(QByteArray query, bool dirty = true, bool log = false);
110121
QByteArray querySingleValueFromDb(const QString& sql, bool log = true, ChoiceOnUse choice = Ask);
111122

@@ -136,6 +147,8 @@ class DBBrowserDB : public QObject
136147
*/
137148
QString max(const sqlb::ObjectIdentifier& tableName, const sqlb::Field& field) const;
138149

150+
static int callbackWrapper (void* callback, int numberColumns, char** values, char** columnNames);
151+
139152
public:
140153
void updateSchema();
141154

0 commit comments

Comments
 (0)