Skip to content

Commit abb6f68

Browse files
mgrojoMKleusberg
mgrojo
authored and
MKleusberg
committed
Initial implementation of "conditional formatting" (#1503)
After setting a filter, the user can select from the context menu in the filter line a new option "Use for Conditional Format", that assigns automatically a colour to the background of cells fulfilling that condition. The formatting is preserved after the user has removed the filter. Several conditional formats can be successively added to a column using different filters. The conditional formats of a column can be cleared when the filter is empty selecting "Clear All Conditional Formats" from the filter line context menu. The conditional formats are saved and loaded in project files as other browse table settings. A new class Palette has been added for reusing the automatic colour assignment of the Plot Dock. It takes into account the theme kind of the application (dark, light) for the colour selection. A new class CondFormat for using the conditional formatting settings from several classes. The conversion of a filter string from our format to an SQL condition has been moved here for reuse in filters and conditional formatting. Whether the conditional format applies is resolved by SQLite, so filters and conditional formats give the same exact results. Code for getting a pragma value has been reused for getting the condition result, and consequently renamed to selectSingleCell. Possible future improvement: - New dialog for editing the conditional formatting (at least colour and application order of conditions, but maybe too: adding new conditions and editing the condition itself).
1 parent 4031a16 commit abb6f68

21 files changed

Lines changed: 466 additions & 165 deletions

‎CMakeLists.txt‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -144,6 +144,8 @@ set(SQLB_MOC_HDR
144144
src/FileExtensionManager.h
145145
src/CipherSettings.h
146146
src/DotenvFormat.h
147+
src/Palette.h
148+
src/CondFormat.h
147149
)
148150

149151
set(SQLB_SRC
@@ -191,6 +193,8 @@ set(SQLB_SRC
191193
src/Data.cpp
192194
src/CipherSettings.cpp
193195
src/DotenvFormat.cpp
196+
src/Palette.cpp
197+
src/CondFormat.cpp
194198
)
195199

196200
set(SQLB_FORMS

‎src/CondFormat.cpp‎

Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,111 @@
1+
#include "CondFormat.h"
2+
#include "Settings.h"
3+
#include "Data.h"
4+
5+
CondFormat::CondFormat(const QString& filter, const QColor& color, const QString& encoding)
6+
: m_filter(filter),
7+
m_color(color)
8+
{
9+
m_sqlCondition = filterToSqlCondition(filter, encoding);
10+
}
11+
12+
QString CondFormat::filterToSqlCondition(const QString& value, const QString& encoding)
13+
{
14+
// Check for any special comparison operators at the beginning of the value string. If there are none default to LIKE.
15+
QString op = "LIKE";
16+
QString val, val2;
17+
QString escape;
18+
bool numeric = false, ok = false;
19+
20+
// range/BETWEEN operator
21+
if (value.contains("~")) {
22+
int sepIdx = value.indexOf('~');
23+
val = value.mid(0, sepIdx);
24+
val2 = value.mid(sepIdx+1);
25+
val.toFloat(&ok);
26+
if (ok) {
27+
val2.toFloat(&ok);
28+
ok = ok && (val.toFloat() < val2.toFloat());
29+
}
30+
}
31+
if (ok) {
32+
op = "BETWEEN";
33+
numeric = true;
34+
} else {
35+
val.clear();
36+
val2.clear();
37+
if(value.left(2) == ">=" || value.left(2) == "<=" || value.left(2) == "<>")
38+
{
39+
// Check if we're filtering for '<> NULL'. In this case we need a special comparison operator.
40+
if(value.left(2) == "<>" && value.mid(2) == "NULL")
41+
{
42+
// We are filtering for '<>NULL'. Override the comparison operator to search for NULL values in this column. Also treat search value (NULL) as number,
43+
// in order to avoid putting quotes around it.
44+
op = "IS NOT";
45+
numeric = true;
46+
val = "NULL";
47+
} else if(value.left(2) == "<>" && value.mid(2) == "''") {
48+
// We are filtering for "<>''", i.e. for everything which is not an empty string
49+
op = "<>";
50+
numeric = true;
51+
val = "''";
52+
} else {
53+
value.mid(2).toFloat(&numeric);
54+
op = value.left(2);
55+
val = value.mid(2);
56+
}
57+
} else if(value.left(1) == ">" || value.left(1) == "<") {
58+
value.mid(1).toFloat(&numeric);
59+
op = value.left(1);
60+
val = value.mid(1);
61+
} else if(value.left(1) == "=") {
62+
val = value.mid(1);
63+
64+
// Check if value to compare with is 'NULL'
65+
if(val != "NULL")
66+
{
67+
// It's not, so just compare normally to the value, whatever it is.
68+
op = "=";
69+
} else {
70+
// It is NULL. Override the comparison operator to search for NULL values in this column. Also treat search value (NULL) as number,
71+
// in order to avoid putting quotes around it.
72+
op = "IS";
73+
numeric = true;
74+
}
75+
} else {
76+
// Keep the default LIKE operator
77+
78+
// Set the escape character if one has been specified in the settings dialog
79+
QString escape_character = Settings::getValue("databrowser", "filter_escape").toString();
80+
if(escape_character == "'") escape_character = "''";
81+
if(escape_character.length())
82+
escape = QString("ESCAPE '%1'").arg(escape_character);
83+
84+
// Add % wildcards at the start and at the beginning of the filter query, but only if there weren't set any
85+
// wildcards manually. The idea is to assume that a user who's just typing characters expects the wildcards to
86+
// be added but a user who adds them herself knows what she's doing and doesn't want us to mess up her query.
87+
if(!value.contains("%"))
88+
{
89+
val = value;
90+
val.prepend('%');
91+
val.append('%');
92+
}
93+
}
94+
}
95+
if(val.isEmpty())
96+
val = value;
97+
98+
if(val == "" || val == "%" || val == "%%")
99+
return QString();
100+
else {
101+
// Quote and escape value, but only if it's not numeric and not the empty string sequence
102+
if(!numeric && val != "''")
103+
val = QString("'%1'").arg(val.replace("'", "''"));
104+
105+
QString whereClause(op + " " + QString(encodeString(val.toUtf8(), encoding)));
106+
if (!val2.isEmpty())
107+
whereClause += " AND " + QString(encodeString(val2.toUtf8(), encoding));
108+
whereClause += " " + escape;
109+
return whereClause;
110+
}
111+
}

‎src/CondFormat.h‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
#ifndef CONDFORMAT_H
2+
#define CONDFORMAT_H
3+
4+
#include <QString>
5+
#include <QColor>
6+
7+
// Conditional formatting for given format to table cells based on a specified condition.
8+
class CondFormat
9+
{
10+
public:
11+
CondFormat() {};
12+
explicit CondFormat(const QString& filter, const QColor& color, const QString& encoding = QString());
13+
14+
static QString filterToSqlCondition(const QString& value, const QString& encoding = QString());
15+
16+
private:
17+
QString m_sqlCondition;
18+
QString m_filter;
19+
QColor m_color;
20+
21+
public:
22+
QString sqlCondition() const { return m_sqlCondition; };
23+
QString filter() const { return m_filter; };
24+
QColor color() const { return m_color; };
25+
26+
};
27+
28+
#endif // CONDFORMAT_H

‎src/Data.cpp‎

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -21,8 +21,7 @@ bool isTextOnly(QByteArray data, const QString& encoding, bool quickTest)
2121
data = data.left(512);
2222

2323
// Convert to Unicode if necessary
24-
if(!encoding.isEmpty())
25-
data = QTextCodec::codecForName(encoding.toUtf8())->toUnicode(data).toUtf8();
24+
data = decodeString(data, encoding);
2625

2726
// Perform check
2827
return QString(data).toUtf8() == data;
@@ -65,3 +64,19 @@ QStringList toStringList(const QList<QByteArray> list) {
6564
}
6665
return strings;
6766
}
67+
68+
QByteArray encodeString(const QByteArray& str, const QString& encoding)
69+
{
70+
if(encoding.isEmpty())
71+
return str;
72+
else
73+
return QTextCodec::codecForName(encoding.toUtf8())->fromUnicode(str);
74+
}
75+
76+
QByteArray decodeString(const QByteArray& str, const QString& encoding)
77+
{
78+
if(encoding.isEmpty())
79+
return str;
80+
else
81+
return QTextCodec::codecForName(encoding.toUtf8())->toUnicode(str).toUtf8();
82+
}

‎src/Data.h‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
#define DATA_H
33

44
#include <QString>
5+
#include <QByteArray>
56

67
// This returns false if the data in the data parameter contains binary data. If it is text only, the function returns
78
// true. If the second parameter is specified, it will be used to convert the data from the given encoding to Unicode
@@ -19,4 +20,8 @@ QByteArray removeBom(QByteArray& data);
1920

2021
QStringList toStringList(const QList<QByteArray> list);
2122

23+
QByteArray encodeString(const QByteArray& str, const QString& encoding);
24+
25+
QByteArray decodeString(const QByteArray& str, const QString& encoding);
26+
2227
#endif

‎src/FilterLineEdit.cpp‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -107,6 +107,15 @@ void FilterLineEdit::showContextMenu(const QPoint &pos)
107107
// This has to be created here, otherwise the set of enabled options would not update accordingly.
108108
QMenu* editContextMenu = createStandardContextMenu();
109109
editContextMenu->addSeparator();
110+
QString conditionalFormatLabel = text().isEmpty() ? tr("Clear All Conditional Formats") : tr("Use for Conditional Format");
111+
QAction* conditionalFormatAction = new QAction(conditionalFormatLabel, editContextMenu);
112+
connect(conditionalFormatAction, &QAction::triggered, [&]() {
113+
if (text().isEmpty())
114+
emit clearAllCondFormats();
115+
else
116+
emit addFilterAsCondFormat(text());
117+
});
118+
editContextMenu->addSeparator();
110119

111120
QMenu* filterMenu = editContextMenu->addMenu(tr("Set Filter Expression"));
112121

@@ -164,6 +173,8 @@ void FilterLineEdit::showContextMenu(const QPoint &pos)
164173
setFilterHelper(QString ("?~"));
165174
});
166175

176+
editContextMenu->addAction(conditionalFormatAction);
177+
167178
filterMenu->addAction(whatsThisAction);
168179
filterMenu->addSeparator();
169180
filterMenu->addAction(isNullAction);

‎src/FilterLineEdit.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,8 @@ private slots:
2323

2424
signals:
2525
void delayedTextChanged(QString text);
26+
void addFilterAsCondFormat(QString text);
27+
void clearAllCondFormats();
2628

2729
protected:
2830
void keyReleaseEvent(QKeyEvent* event) override;

‎src/FilterTableHeader.cpp‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,8 @@ void FilterTableHeader::generateFilters(int number, bool showFirst)
3636
else
3737
l->setVisible(true);
3838
connect(l, SIGNAL(delayedTextChanged(QString)), this, SLOT(inputChanged(QString)));
39+
connect(l, SIGNAL(addFilterAsCondFormat(QString)), this, SLOT(addFilterAsCondFormat(QString)));
40+
connect(l, SIGNAL(clearAllCondFormats()), this, SLOT(clearAllCondFormats()));
3941
filterWidgets.push_back(l);
4042
}
4143

@@ -86,6 +88,18 @@ void FilterTableHeader::inputChanged(const QString& new_value)
8688
emit filterChanged(sender()->property("column").toInt(), new_value);
8789
}
8890

91+
void FilterTableHeader::addFilterAsCondFormat(const QString& filter)
92+
{
93+
// Just get the column number and the new value and send them to anybody interested in new conditional formatting
94+
emit addCondFormat(sender()->property("column").toInt(), filter);
95+
}
96+
97+
void FilterTableHeader::clearAllCondFormats()
98+
{
99+
// Just get the column number and send it to anybody responsible or interested in clearing conditional formatting
100+
emit clearAllCondFormats(sender()->property("column").toInt());
101+
}
102+
89103
void FilterTableHeader::clearFilters()
90104
{
91105
for(FilterLineEdit* filterLineEdit : filterWidgets)

‎src/FilterTableHeader.h‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,12 +25,16 @@ public slots:
2525

2626
signals:
2727
void filterChanged(int column, QString value);
28+
void addCondFormat(int column, QString filter);
29+
void clearAllCondFormats(int column);
2830

2931
protected:
3032
void updateGeometries() override;
3133

3234
private slots:
3335
void inputChanged(const QString& new_value);
36+
void addFilterAsCondFormat(const QString& filter);
37+
void clearAllCondFormats();
3438

3539
private:
3640
QList<FilterLineEdit*> filterWidgets;

‎src/MainWindow.cpp‎

Lines changed: 51 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@
2727
#include "RemoteDatabase.h"
2828
#include "FindReplaceDialog.h"
2929
#include "Data.h"
30+
#include "CondFormat.h"
3031

3132
#include <QFile>
3233
#include <QApplication>
@@ -127,6 +128,8 @@ void MainWindow::init()
127128

128129
// Set up filters
129130
connect(ui->dataTable->filterHeader(), SIGNAL(filterChanged(int,QString)), this, SLOT(updateFilter(int,QString)));
131+
connect(ui->dataTable->filterHeader(), SIGNAL(addCondFormat(int,QString)), this, SLOT(addCondFormat(int,QString)));
132+
connect(ui->dataTable->filterHeader(), SIGNAL(clearAllCondFormats(int)), this, SLOT(clearAllCondFormats(int)));
130133
connect(m_browseTableModel, SIGNAL(dataChanged(QModelIndex,QModelIndex)), this, SLOT(dataTableSelectionChanged(QModelIndex)));
131134

132135
// Select in table the rows correspoding to the selected points in plot
@@ -716,7 +719,12 @@ void MainWindow::applyBrowseTableSettings(BrowseDataTableSettings storedData, bo
716719
bool oldState = filterHeader->blockSignals(true);
717720
for(auto filterIt=storedData.filterValues.constBegin();filterIt!=storedData.filterValues.constEnd();++filterIt)
718721
filterHeader->setFilter(filterIt.key(), filterIt.value());
719-
filterHeader->blockSignals(oldState);
722+
723+
// Conditional formats
724+
for(auto formatIt=storedData.condFormats.constBegin(); formatIt!=storedData.condFormats.constEnd(); ++formatIt)
725+
m_browseTableModel->setCondFormats(formatIt.key(), formatIt.value());
726+
727+
filterHeader->blockSignals(oldState);
720728
}
721729

722730
// Encoding
@@ -2438,6 +2446,21 @@ static void loadBrowseDataTableSettings(BrowseDataTableSettings& settings, QXmlS
24382446
xml.skipCurrentElement();
24392447
}
24402448
}
2449+
} else if(xml.name() == "conditional_formats") {
2450+
while(xml.readNext() != QXmlStreamReader::EndElement && xml.name() != "conditional_formats") {
2451+
if (xml.name() == "column") {
2452+
int index = xml.attributes().value("index").toInt();
2453+
while(xml.readNext() != QXmlStreamReader::EndElement && xml.name() != "column") {
2454+
if(xml.name() == "format") {
2455+
CondFormat newCondFormat(xml.attributes().value("condition").toString(),
2456+
QColor(xml.attributes().value("color").toString()),
2457+
settings.encoding);
2458+
settings.condFormats[index].append(newCondFormat);
2459+
xml.skipCurrentElement();
2460+
}
2461+
}
2462+
}
2463+
}
24412464
} else if(xml.name() == "display_formats") {
24422465
while(xml.readNext() != QXmlStreamReader::EndElement && xml.name() != "display_formats") {
24432466
if (xml.name() == "column") {
@@ -2700,6 +2723,19 @@ static void saveBrowseDataTableSettings(const BrowseDataTableSettings& object, Q
27002723
xml.writeEndElement();
27012724
}
27022725
xml.writeEndElement();
2726+
xml.writeStartElement("conditional_formats");
2727+
for(auto iter=object.condFormats.constBegin(); iter!=object.condFormats.constEnd(); ++iter) {
2728+
xml.writeStartElement("column");
2729+
xml.writeAttribute("index", QString::number(iter.key()));
2730+
for(auto format : iter.value()) {
2731+
xml.writeStartElement("format");
2732+
xml.writeAttribute("condition", format.filter());
2733+
xml.writeAttribute("color", format.color().name());
2734+
xml.writeEndElement();
2735+
}
2736+
xml.writeEndElement();
2737+
}
2738+
xml.writeEndElement();
27032739
xml.writeStartElement("display_formats");
27042740
for(auto iter=object.displayFormats.constBegin(); iter!=object.displayFormats.constEnd(); ++iter) {
27052741
xml.writeStartElement("column");
@@ -2872,6 +2908,20 @@ void MainWindow::updateFilter(int column, const QString& value)
28722908
applyBrowseTableSettings(settings, true);
28732909
}
28742910

2911+
void MainWindow::addCondFormat(int column, const QString& value)
2912+
{
2913+
CondFormat newCondFormat(value, m_condFormatPalette.nextSerialColor(Palette::appHasDarkTheme()), m_browseTableModel->encoding());
2914+
m_browseTableModel->addCondFormat(column, newCondFormat);
2915+
browseTableSettings[currentlyBrowsedTableName()].condFormats[column].append(newCondFormat);
2916+
}
2917+
2918+
void MainWindow::clearAllCondFormats(int column)
2919+
{
2920+
QVector<CondFormat> emptyCondFormatVector = QVector<CondFormat>();
2921+
m_browseTableModel->setCondFormats(column, emptyCondFormatVector);
2922+
browseTableSettings[currentlyBrowsedTableName()].condFormats[column].clear();
2923+
}
2924+
28752925
void MainWindow::editEncryption()
28762926
{
28772927
#ifdef ENABLE_SQLCIPHER

0 commit comments

Comments
 (0)