Skip to content

Sort Execute SQL results by header click and show small BLOBs as hex #4241

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
deepsidhu1313 wants to merge 2 commits into
base: master
Choose a base branch
from
Open

Sort Execute SQL results by header click and show small BLOBs as hex #4241

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
830e22f
Sort Execute SQL results by clicking on a column header
claude Oct 30, 2026
702479f
Add an option to show small BLOBs as hex strings in the cells
claude Oct 30, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 4 additions & 0 deletions src/PreferencesDialog.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -123,6 +123,7 @@ void PreferencesDialog::loadSettings()
ui->spinCompleteThreshold->setValue(Settings::getValue("databrowser", "complete_threshold").toInt());
ui->checkShowImagesInline->setChecked(Settings::getValue("databrowser", "image_preview").toBool());
ui->checkCellWordWrap->setChecked(Settings::getValue("databrowser", "cell_word_wrap").toBool());
ui->spinBlobHexLimit->setValue(Settings::getValue("databrowser", "blob_hex_limit").toInt());
ui->txtNull->setText(Settings::getValue("databrowser", "null_text").toString());
ui->txtBlob->setText(Settings::getValue("databrowser", "blob_text").toString());
ui->editFilterEscape->setText(Settings::getValue("databrowser", "filter_escape").toString());
Expand Down Expand Up @@ -166,6 +167,7 @@ void PreferencesDialog::loadSettings()
ui->checkCompleteUpper->setChecked(Settings::getValue("editor", "upper_keywords").toBool());
ui->checkErrorIndicators->setChecked(Settings::getValue("editor", "error_indicators").toBool());
ui->checkHorizontalTiling->setChecked(Settings::getValue("editor", "horizontal_tiling").toBool());
ui->checkSortResults->setChecked(Settings::getValue("editor", "sort_results").toBool());
ui->checkCloseButtonOnTabs->setChecked(Settings::getValue("editor", "close_button_on_tabs").toBool());

ui->listExtensions->addItems(Settings::getValue("extensions", "list").toStringList());
Expand Down Expand Up @@ -209,6 +211,7 @@ void PreferencesDialog::saveSettings(bool accept)
Settings::setValue("databrowser", "fontsize", ui->spinDataBrowserFontSize->value());
Settings::setValue("databrowser", "image_preview", ui->checkShowImagesInline->isChecked());
Settings::setValue("databrowser", "cell_word_wrap", ui->checkCellWordWrap->isChecked());
Settings::setValue("databrowser", "blob_hex_limit", ui->spinBlobHexLimit->value());
saveColorSetting(ui->fr_null_fg, "null_fg");
saveColorSetting(ui->fr_null_bg, "null_bg");
saveColorSetting(ui->fr_reg_fg, "reg_fg");
Expand Down Expand Up @@ -243,6 +246,7 @@ void PreferencesDialog::saveSettings(bool accept)
Settings::setValue("editor", "upper_keywords", ui->checkCompleteUpper->isChecked());
Settings::setValue("editor", "error_indicators", ui->checkErrorIndicators->isChecked());
Settings::setValue("editor", "horizontal_tiling", ui->checkHorizontalTiling->isChecked());
Settings::setValue("editor", "sort_results", ui->checkSortResults->isChecked());
Settings::setValue("editor", "close_button_on_tabs", ui->checkCloseButtonOnTabs->isChecked());

QStringList extList;
Expand Down
56 changes: 55 additions & 1 deletion src/PreferencesDialog.ui
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1293,6 +1293,16 @@ in new project file</string>
</property>
</widget>
</item>
<item row="4" column="0">
<widget class="QLabel" name="labelBlobHexLimit">
<property name="text">
<string>Show BLOBs as hex up to</string>
</property>
<property name="buddy">
<cstring>spinBlobHexLimit</cstring>
</property>
</widget>
</item>
<item row="0" column="1">
<widget class="QSpinBox" name="spinSymbolLimit">
<property name="correctionMode">
Expand Down Expand Up @@ -1343,6 +1353,28 @@ Can be set to 0 for disabling completion.</string>
</property>
</widget>
</item>
<item row="4" column="1">
<widget class="QSpinBox" name="spinBlobHexLimit">
<property name="toolTip">
<string>BLOBs of at most this many bytes are shown as a hexadecimal string in the cells. Larger BLOBs are shown as the BLOB text above. The hexadecimal string is cut off at the symbol limit. Set to 0 to always show the BLOB text.</string>
</property>
<property name="specialValueText">
<string>Off</string>
</property>
<property name="suffix">
<string> bytes</string>
</property>
<property name="correctionMode">
<enum>QAbstractSpinBox::CorrectToNearestValue</enum>
</property>
<property name="minimum">
<number>0</number>
</property>
<property name="maximum">
<number>10000</number>
</property>
</widget>
</item>
</layout>
</widget>
</item>
Expand Down Expand Up @@ -1861,6 +1893,26 @@ Can be set to 0 for disabling completion.</string>
</widget>
</item>
<item row="10" column="0">
<widget class="QLabel" name="labelSortResults">
<property name="text">
<string>Sort results</string>
</property>
<property name="buddy">
<cstring>checkSortResults</cstring>
</property>
</widget>
</item>
<item row="10" column="1">
<widget class="QCheckBox" name="checkSortResults">
<property name="toolTip">
<string>If enabled, clicking a column header of the results of a SELECT statement sorts the results by this column. Clicking again reverses the order, and clicking a third time goes back to the original order.</string>
</property>
<property name="text">
<string>enabled</string>
</property>
</widget>
</item>
<item row="11" column="0">
<widget class="QLabel" name="labelCloseButtonOnTabs">
<property name="text">
<string>Close button on tabs</string>
Expand All @@ -1870,7 +1922,7 @@ Can be set to 0 for disabling completion.</string>
</property>
</widget>
</item>
<item row="10" column="1">
<item row="11" column="1">
<widget class="QCheckBox" name="checkCloseButtonOnTabs">
<property name="toolTip">
<string>If enabled, SQL editor tabs will have a close button. In any case, you can use the contextual menu or the keyboard shortcut to close them.</string>
Expand Down Expand Up @@ -2071,6 +2123,7 @@ Can be set to 0 for disabling completion.</string>
<tabstop>spinCompleteThreshold</tabstop>
<tabstop>checkShowImagesInline</tabstop>
<tabstop>checkCellWordWrap</tabstop>
<tabstop>spinBlobHexLimit</tabstop>
<tabstop>spinFilterDelay</tabstop>
<tabstop>editFilterEscape</tabstop>
<tabstop>treeSyntaxHighlighting</tabstop>
Expand All @@ -2085,6 +2138,7 @@ Can be set to 0 for disabling completion.</string>
<tabstop>checkCompleteUpper</tabstop>
<tabstop>checkErrorIndicators</tabstop>
<tabstop>checkHorizontalTiling</tabstop>
<tabstop>checkSortResults</tabstop>
<tabstop>checkCloseButtonOnTabs</tabstop>
<tabstop>listExtensions</tabstop>
<tabstop>buttonAddExtension</tabstop>
Expand Down
6 changes: 6 additions & 0 deletions src/Settings.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -333,6 +333,8 @@ QVariant Settings::getDefaultValue(const std::string& group, const std::string&
return "NULL";
if(name == "blob_text")
return "BLOB";
if(name == "blob_hex_limit")
return 0;
if(name == "filter_escape")
return "\\";
if(name == "filter_delay")
Expand Down Expand Up @@ -410,6 +412,10 @@ QVariant Settings::getDefaultValue(const std::string& group, const std::string&
if(group == "editor" && name == "horizontal_tiling")
return false;

// editor/sort_results?
if(group == "editor" && name == "sort_results")
return false;

// editor/splitter1_sizes?
if(group == "editor" && name == "splitter1_sizes")
return QVariant();
Expand Down
8 changes: 8 additions & 0 deletions src/SqlExecutionArea.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -28,6 +28,7 @@ SqlExecutionArea::SqlExecutionArea(DBBrowserDB& _db, QWidget* parent) :
ui->tableResult->setModel(model);
connect(model, &SqliteTableModel::finishedFetch, this, &SqlExecutionArea::fetchedData);
connect(ui->tableResult->filterHeader(), &FilterTableHeader::sectionPressed, ui->tableResult, &QTableView::selectColumn);
connect(ui->tableResult->filterHeader(), &FilterTableHeader::sectionClicked, this, &SqlExecutionArea::headerClicked);

ui->findFrame->hide();

Expand Down Expand Up @@ -87,6 +88,13 @@ void SqlExecutionArea::setSql(const QString& sql)
ui->editEditor->setText(sql);
}

void SqlExecutionArea::headerClicked(int column)
{
// Sorting the results is optional because clicking on a header used to be only for selecting the column
if(Settings::getValue("editor", "sort_results").toBool())
model->cycleResultSort(column);
}

void SqlExecutionArea::finishExecution(const QString& result, const bool ok)
{
error_state = !ok;
Expand Down
1 change: 1 addition & 0 deletions src/SqlExecutionArea.h
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -56,6 +56,7 @@ private slots:
void findNext();
void findLineEdit_textChanged(const QString& text);
void hideFindFrame();
void headerClicked(int column);

void fileChanged(const QString& filename);

Expand Down
128 changes: 119 additions & 9 deletions src/sqlitetablemodel.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -18,6 +18,7 @@
#include <QPushButton>

#include <cassert>
#include <cstring>

SqliteTableModel::SqliteTableModel(DBBrowserDB& db, QObject* parent, const QString& encoding, bool force_wait)
: QAbstractTableModel(parent)
Expand Down Expand Up @@ -122,6 +123,10 @@ void SqliteTableModel::reset()
clearCache();
m_lastError.clear();
m_sQuery.clear();
m_customQuery.clear();
m_customQuerySortable = false;
m_customSortColumn = -1;
m_customSortOrder = Qt::AscendingOrder;
m_query.clear();
m_table_of_query.reset();
m_headers.clear();
Expand Down Expand Up @@ -168,15 +173,33 @@ void SqliteTableModel::setQuery(const sqlb::Query& query)
updateAndRunQuery();
}

// Statements which return rows and can be used inside another statement as a subquery: SELECT and WITH ... SELECT. This doesn't cover PRAGMA and
// EXPLAIN statements. It also doesn't check whether the statement could modify data, see SqliteTableModel::getColumnNames().
static bool canBeUsedAsSubquery(const QString& query)
{
for(const char* keyword : {"SELECT", "WITH"})
{
const int length = static_cast<int>(strlen(keyword));
if(query.startsWith(QLatin1String(keyword), Qt::CaseInsensitive) &&
(query.size() == length || !(query.at(length).isLetterOrNumber() || query.at(length) == '_')))
return true;
}
return false;
}

void SqliteTableModel::setQuery(const QString& sQuery)
{
// Reset
reset();

m_sQuery = sQuery.trimmed();
removeCommentsFromQuery(m_sQuery);
m_customQuery = m_sQuery;

// The query can only be wrapped in another one for sorting if it is a statement for which this is possible. getColumnNames() checks whether
// it is read-only, so statements like WITH ... DELETE ... RETURNING are not mistaken for a SELECT statement.
getColumnNames(sQuery.toStdString());
m_customQuerySortable = m_customQuerySortable && canBeUsedAsSubquery(m_customQuery);

worker->setQuery(m_sQuery, QString());

Expand Down Expand Up @@ -206,6 +229,13 @@ size_t SqliteTableModel::filterCount() const
return m_query.where().size();
}

// The arrows are not written as literals because MSVC converts the UTF-8 source to the ANSI code page for narrow string
// literals, which turns characters it doesn't have, like these, into question marks.
static QString sortIndicatorText(bool ascending)
{
return QString(QChar(' ')) + QChar(ascending ? 0x25B4 : 0x25BE);
}

// Convert a number to string using the Unicode superscript characters
template<class T>
static QString toSuperScript(T number)
Expand Down Expand Up @@ -261,14 +291,20 @@ QVariant SqliteTableModel::headerData(int section, Qt::Orientation orientation,
}
default:
QString sortIndicator;
for(size_t i = 0; i < m_query.orderBy().size(); i++) {
const sqlb::OrderBy sortedColumn = m_query.orderBy()[i];
// Append sort indicator with direction and ordinal number in superscript style
if (sortedColumn.expr == plainHeader) {
sortIndicator = sortedColumn.direction == sqlb::OrderBy::Ascending ? " ▴" : " ▾";
if(m_query.orderBy().size() > 1)
sortIndicator.append(toSuperScript(i+1));
break;
if(!m_customQuery.isEmpty()) {
// The results of a custom query are sorted by column number because the column names don't need to be unique
if(section == m_customSortColumn)
sortIndicator = sortIndicatorText(m_customSortOrder == Qt::AscendingOrder);
} else {
for(size_t i = 0; i < m_query.orderBy().size(); i++) {
const sqlb::OrderBy sortedColumn = m_query.orderBy()[i];
// Append sort indicator with direction and ordinal number in superscript style
if (sortedColumn.expr == plainHeader) {
sortIndicator = sortIndicatorText(sortedColumn.direction == sqlb::OrderBy::Ascending);
if(m_query.orderBy().size() > 1)
sortIndicator.append(toSuperScript(i+1));
break;
}
}
}
return QString::fromStdString(plainHeader) + sortIndicator;
Expand Down Expand Up @@ -361,7 +397,7 @@ QVariant SqliteTableModel::data(const QModelIndex &index, int role) const
{
return m_nullText;
} else if(isBinary(data)) {
return m_blobText;
return binaryDisplayText(data);
} else {
if (data.length() > m_symbolLimit) {
// Add "..." to the end of truncated strings
Expand Down Expand Up @@ -622,6 +658,13 @@ Qt::ItemFlags SqliteTableModel::flags(const QModelIndex& index) const

void SqliteTableModel::sort(int column, Qt::SortOrder order)
{
// The results of a custom query are not sorted by column name
if(!m_customQuery.isEmpty())
{
sortResults(column, order);
return;
}

// Construct a sort order list from this item and forward it to the function to sort by lists
std::vector<sqlb::OrderBy> list;
list.emplace_back(m_headers.at(static_cast<size_t>(column)), order == Qt::AscendingOrder ? sqlb::OrderBy::Ascending : sqlb::OrderBy::Descending);
Expand All @@ -642,6 +685,55 @@ void SqliteTableModel::sort(const std::vector<sqlb::OrderBy>& columns)
updateAndRunQuery();
}

void SqliteTableModel::cycleResultSort(int column)
{
if(column != m_customSortColumn)
sortResults(column, Qt::AscendingOrder);
else if(m_customSortOrder == Qt::AscendingOrder)
sortResults(column, Qt::DescendingOrder);
else
sortResults(-1, Qt::AscendingOrder);
}

void SqliteTableModel::sortResults(int column, Qt::SortOrder order)
{
if(!m_customQuerySortable)
return;

// Any column which doesn't exist means no sorting
if(column < 0 || column >= columnCount())
column = -1;

// Don't do anything when the sort order hasn't changed
if(column == m_customSortColumn && (column == -1 || order == m_customSortOrder))
return;

m_customSortColumn = column;
m_customSortOrder = column == -1 ? Qt::AscendingOrder : order;

clearCache();

if(column == -1)
{
m_sQuery = m_customQuery;
} else {
// Sort by wrapping the original query. This way it doesn't matter what the original query does, e.g. whether it has an ORDER BY or a LIMIT clause
// itself, and sorting by column number works even if some columns have the same name. The semicolon at the end of the original query has to go
// because it would end the statement inside the parentheses.
QString query = m_customQuery.trimmed();
while(query.endsWith(';'))
query = query.left(query.size() - 1).trimmed();
m_sQuery = QString("SELECT * FROM (%1) ORDER BY %2 %3").arg(query).arg(column + 1).arg(order == Qt::AscendingOrder ? QStringLiteral("ASC") : QStringLiteral("DESC"));
}

worker->setQuery(m_sQuery, QString());

// now fetch the first entries
triggerCacheLoad(static_cast<int>(m_chunkSize / 2) - 1);

emit layoutChanged();
}

SqliteTableModel::Row SqliteTableModel::makeDefaultCacheEntry () const
{
Row blank_data;
Expand Down Expand Up @@ -782,9 +874,12 @@ void SqliteTableModel::getColumnNames(const std::string& sQuery)
{
auto pDb = m_db.get(tr("retrieving list of columns"));

m_customQuerySortable = false;

sqlite3_stmt* stmt;
if(sqlite3_prepare_v2(pDb.get(), sQuery.c_str(), static_cast<int>(sQuery.size()), &stmt, nullptr) == SQLITE_OK)
{
m_customQuerySortable = sqlite3_stmt_readonly(stmt) != 0;
int columns = sqlite3_column_count(stmt);
for(int i = 0; i < columns; ++i)
{
Expand Down Expand Up @@ -894,6 +989,20 @@ bool SqliteTableModel::isBinary(const QByteArray& data) const
return !isTextOnly(data, m_encoding, true);
}

QString SqliteTableModel::binaryDisplayText(const QByteArray& data) const
{
// Small BLOBs can be shown as hexadecimal string, like the one the hex() function returns. The result is cut off at the symbol limit like text is.
if(data.size() <= m_blobHexLimit)
{
QString hex = QString::fromLatin1(data.toHex().toUpper());
if(hex.length() > m_symbolLimit)
hex = hex.left(m_symbolLimit) + " ...";
return hex;
}

return m_blobText;
}

QByteArray SqliteTableModel::encode(const QByteArray& str) const
{
return encodeString(str, m_encoding);
Expand Down Expand Up @@ -1182,6 +1291,7 @@ void SqliteTableModel::reloadSettings()
m_font = QFont(Settings::getValue("databrowser", "font").toString());
m_font.setPointSize(Settings::getValue("databrowser", "fontsize").toInt());
m_symbolLimit = Settings::getValue("databrowser", "symbol_limit").toInt();
m_blobHexLimit = Settings::getValue("databrowser", "blob_hex_limit").toInt();
m_rowsLimit = Settings::getValue("databrowser", "rows_limit").toInt();
m_imagePreviewEnabled = Settings::getValue("databrowser", "image_preview").toBool();
m_chunkSize = static_cast<std::size_t>(Settings::getValue("db", "prefetchsize").toUInt());
Expand Down
Loading
Loading