One comment more: the list of applied colours is open to improvements. I've selected some light colours from http://www.december.com/html/spec/colorsvg.html more or less randomly. For the dark colours (used when the application has a dark theme) the colours are the same as for the plot.

This looks really nifty @mgrojo. 😄

You ok to fix the file conflicts?

You ok to fix the file conflicts?

Yes, of course. But I have little time in the next days. Not sure when I'll get some for this.

I found some time and have solved the conflicts. I've supposed that conditional formats must be reapplied only when filters are also reapplied (in the new applyBrowseTableSettings function).

Thanks I'll look into this PR soon. It's kept postponing it because it's really large but I'll do my best to finish it this week 😉

Don't worry, there is no rush. 👍

Some of the changes are code refactoring, so the same code can be used in two contexts. I had some doubts about how to best structure the reused code.

At some moment, I'd like to implement the dialog for editing (at least) the automatically assigned colours.

Hello @mgrojo,

This feature would be too COOL to be true! :-)

Needless to say, having the option to choose manually your own color and background would be a huge extra bonus !
On LibreOffice Calc or Microsoft Excel, for instance, just to give an example, I usually choose these settings:

• red color for the font;
• bold for the font;
• yellow for the background of the cell (where the font is inserted).

With Calc, you can create your ows Style with your preferred colors and use it later on for all your formatting.
Saving your own style allows you to work faster later on when you want to adopt the same formatting on the filtered cells.

Thanks for the feedback, @SilvioGrosso. This feature will be extended with time. The dialog for choosing your own colour is definitely needed. And other kind of formats, maybe if it is often asked for 😃

Maybe it's just that I had a long day and am a bit tired right now but I can't find any issue with this code 😉 Either way I have just rebased it, solved any conflicts, and will merge it in a second. The only things change besides the custom colour and format selection that comes to mind would be a more prominent place for this feature in the UI to make it more obvious that it's there. Maybe that could be done in the same dialog but that's definitely something we can leave for a later time. Anyway, thanks again! This looks really well done and is a great addition 👍

Thanks, Martin. You're right that it should have a more visible access path. I'll think about it when I add the dialog.

As a data point, the nightly build server isn't set up at my new, new location. I should be getting that happening today though, after which I'll kick off the nightly build scripts manually.

I've seen a problem with this feature and the multithreading code. If you use it with a DB with more rows than the fetch block, set one format for a column and then scroll, the dialog for cancelling the current DB action is open. Answering Yes or No is apparently irrelevant; the application is frozen. I guess it's due to the SELECT we do for checking whether the current cell accomplishes the condition. This is the backtrace. I'll try to see whether there is an easy and safe solution for this. This SELECT query is not actually using the database, but only the SQLite engine. Should then actually wait for DB release?

#0  pthread_cond_wait@@GLIBC_2.3.2 () at ../sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S:185
#1  0x00007ffff64f891c in std::condition_variable::wait(std::unique_lock<std::mutex>&) () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6
#2  0x0000000000795c00 in std::condition_variable::wait<DBBrowserDB::waitForDbRelease()::<lambda()> >(std::unique_lock<std::mutex> &, DBBrowserDB::<lambda()>) (this=0x1185498, __lock=..., __p=...) at /usr/include/c++/5/condition_variable:98
#3  0x000000000078a793 in DBBrowserDB::waitForDbRelease (this=0x1185450) at /home/mgr/src/sqlitebrowser/src/sqlitedb.cpp:637
#4  0x000000000078da4d in DBBrowserDB::querySingleValueFromDb (this=0x1185450, sql=..., log=false) at /home/mgr/src/sqlitebrowser/src/sqlitedb.cpp:991
#5  0x000000000079d6be in SqliteTableModel::data (this=0x117e810, index=..., role=8) at /home/mgr/src/sqlitebrowser/src/sqlitetablemodel.cpp:317
#6  0x00007ffff762a3aa in QStyledItemDelegate::initStyleOption(QStyleOptionViewItem*, QModelIndex const&) const () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#7  0x00007ffff762909c in QStyledItemDelegate::paint(QPainter*, QStyleOptionViewItem const&, QModelIndex const&) const () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#8  0x00007ffff75c9033 in ?? () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#9  0x00007ffff75d3c44 in QTableView::paintEvent(QPaintEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#10 0x00007ffff7382fc8 in QWidget::event(QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#11 0x00007ffff7481b8e in QFrame::event(QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#12 0x00007ffff75a76db in QAbstractItemView::viewportEvent(QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#13 0x00007ffff6a4d172 in QCoreApplicationPrivate::sendThroughObjectEventFilters(QObject*, QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#14 0x00007ffff734003c in QApplicationPrivate::notify_helper(QObject*, QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#15 0x00007ffff7345516 in QApplication::notify(QObject*, QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#16 0x00007ffff6a4d38b in QCoreApplication::notifyInternal(QObject*, QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#17 0x00007ffff737bab9 in QWidgetPrivate::sendPaintEvent(QRegion const&) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#18 0x00007ffff737c101 in QWidgetPrivate::drawWidget(QPaintDevice*, QRegion const&, QPoint const&, int, QPainter*, QWidgetBackingStore*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#19 0x00007ffff734d856 in ?? () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#20 0x00007ffff734da8c in ?? () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#21 0x00007ffff736bc5f in QWidgetPrivate::syncBackingStore() () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#22 0x00007ffff7382dc8 in QWidget::event(QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#23 0x00007ffff7498dbb in QMainWindow::event(QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#24 0x00007ffff734005c in QApplicationPrivate::notify_helper(QObject*, QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#25 0x00007ffff7345516 in QApplication::notify(QObject*, QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Widgets.so.5
#26 0x00007ffff6a4d38b in QCoreApplication::notifyInternal(QObject*, QEvent*) () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#27 0x00007ffff6a4f786 in QCoreApplicationPrivate::sendPostedEvents(QObject*, int, QThreadData*) () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#28 0x00007ffff6aa33c3 in ?? () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#29 0x00007ffff5003197 in g_main_context_dispatch () from /lib/x86_64-linux-gnu/libglib-2.0.so.0
#30 0x00007ffff50033f0 in ?? () from /lib/x86_64-linux-gnu/libglib-2.0.so.0
#31 0x00007ffff500349c in g_main_context_iteration () from /lib/x86_64-linux-gnu/libglib-2.0.so.0
#32 0x00007ffff6aa37cf in QEventDispatcherGlib::processEvents(QFlags<QEventLoop::ProcessEventsFlag>) () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#33 0x00007ffff6a4ab4a in QEventLoop::exec(QFlags<QEventLoop::ProcessEventsFlag>) () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#34 0x00007ffff6a52bec in QCoreApplication::exec() () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#35 0x000000000080c4f0 in main (argc=1, argv=0x7fffffffdf68) at /home/mgr/src/sqlitebrowser/src/main.cpp:13

And the other relevant threads:

Thread 18 (Thread 0x7fffdc805700 (LWP 4633)):
#0  pthread_cond_wait@@GLIBC_2.3.2 () at ../sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S:185
#1  0x00007ffff64f891c in std::condition_variable::wait(std::unique_lock<std::mutex>&) () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6
#2  0x00000000007ac1f0 in std::condition_variable::wait<RowLoader::run()::<lambda()> >(std::unique_lock<std::mutex> &, RowLoader::<lambda()>) (this=0x17ef8e0, __lock=..., __p=...) at /usr/include/c++/5/condition_variable:98
#3  0x00000000007ab77c in RowLoader::run (this=0x17ef850) at /home/mgr/src/sqlitebrowser/src/RowLoader.cpp:190
#4  0x00007ffff686c7be in ?? () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#5  0x00007ffff7bc16ba in start_thread (arg=0x7fffdc805700) at pthread_create.c:333
#6  0x00007ffff5c6341d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Thread 7 (Thread 0x7fffcf7fe700 (LWP 4613)):
#0  syscall () at ../sysdeps/unix/sysv/linux/x86_64/syscall.S:38
#1  0x00007ffff68648b8 in QBasicMutex::lockInternal() () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#2  0x00000000007a27dd in QMutexLocker::QMutexLocker (this=0x7fffcf7fde20, m=0x117e8c8) at /usr/include/x86_64-linux-gnu/qt5/QtCore/qmutex.h:128
#3  0x00000000007abde5 in RowLoader::process (this=0x1179a60, t=...) at /home/mgr/src/sqlitebrowser/src/RowLoader.cpp:247
#4  0x00000000007ab7ec in RowLoader::run (this=0x1179a60) at /home/mgr/src/sqlitebrowser/src/RowLoader.cpp:198
#5  0x00007ffff686c7be in ?? () from /usr/lib/x86_64-linux-gnu/libQt5Core.so.5
#6  0x00007ffff7bc16ba in start_thread (arg=0x7fffcf7fe700) at pthread_create.c:333
#7  0x00007ffff5c6341d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Heh Heh Heh. Oops. 😉

@mgrojo You're right 😉 We need to add a lock.unlock(); right before the call to m_db.querySingleValueFromDb() in SqliteTableModel::data() to avoid a deadlock. The dialog will still pop up but it should at least be usable then. The next step would be to avoid that dialog altogether, maybe by enforcing to wait for the DB release in situations like this or by cancelling the current action. As far as I know, it's not advisable to run two queries at the same time, even if one of them doesn't access the database - but I'm not 100% sure about that. But in theory we could open a second database connection to an in-memory database and use that. I'm currently in the middle of something else. So I'm not too eager to try any of that right now 😄

I've reading a bit about multi-threading and SQLite The threading mode is selectable at compile-time and start-time: https://www.sqlite.org/threadsafe.html

In my case I'm getting that SQLite is in serialize mode, so there isn't actually a problem if we make a concurrent access in the current connection. These simple changes fix the problem totally for me:

diff --git a/src/sqlitedb.cpp b/src/sqlitedb.cpp
index 274e8b1..5b1b20b 100644
--- a/src/sqlitedb.cpp
+++ b/src/sqlitedb.cpp
@@ -988,7 +988,11 @@ bool DBBrowserDB::executeMultiSQL(const QString& statement, bool dirty, bool log
 
 QByteArray DBBrowserDB::querySingleValueFromDb(const QString& sql, bool log)
 {
-    waitForDbRelease();
+    // If the threading mode is Single-thread or Multi-thread then wait for
+    // the DB release.
+    if (sqlite3_db_mutex(_db) == nullptr)
+        waitForDbRelease();
+
     if(!_db)
         return QByteArray();
 
diff --git a/src/sqlitetablemodel.cpp b/src/sqlitetablemodel.cpp
index 1210211..d20fe60 100644
--- a/src/sqlitetablemodel.cpp
+++ b/src/sqlitetablemodel.cpp
@@ -314,6 +314,7 @@ QVariant SqliteTableModel::data(const QModelIndex &index, int role) const
                 else
                     sql = QString("SELECT '%1' %2").arg(value, eachCondFormat.sqlCondition());
 
+                lock.unlock();
                 if (m_db.querySingleValueFromDb(sql, false) == "1")
                     return eachCondFormat.color();
             }

@MKleusberg Do you think this would be enough? On the other hand, should we check for the threading mode in other places? Would other users have different compile time options? I'm using the SQLite library provided by Ubuntu 16.08.

The idea of a connection for an in-memory database is also good. It could be useful for other uses, but would require more effort.

Would other users have different compile time options?

It sounds like the "best" option for end users would be to do some kind of run time check how it was compiled, and be able to handle each approach. No idea how much effort it would be though.

Maybe, if there's a way to check the compiled in options then we could at least detect threading types we don't support + warn the user? That might be a good way to start out, if the "support everything" approach isn't realistic. 😄

It sounds like the "best" option for end users would be to do some kind of run time check how it was compiled, and be able to handle each approach. No idea how much effort it would be though.

This code is in fact getting whether we're in the Serialize mode. In that case we can ask for queries in parallel and no need to wait.

    if (sqlite3_db_mutex(_db) == nullptr)
        waitForDbRelease();

But my concern was about the other two modes. In those cases, the user will still get the dummy dialog asking to cancel the other query, when all that the user has done is to scroll the table. If we have to support the three modes, the options are always cancelling, always waiting or performing the query in an in-memory DB.

• Always cancelling: I think we shouldn't do that since the loading would be stopped.
• Always waiting: seems the best options for this particular case, but should be tested to see how behaves.
• Query an in-memory DB: would work for the serialize and multi-thread modes, but not for the mono-thread mode.

I'm going to commit the changes that I showed before, because otherwise the master branch is broken, but this is still open to discussion and refinement.

k. I'll generate new nightly builds sometime today for people to try with. And yeah... the automatic nightly build script isn't being "automatic" for now. There's something weird with the wireless here, and the build server refuses to connect to it other than manually. (ugh)

@mgrojo Sorry for the delay! Your change looks ok for now. The only problem is that it's starting to mix different assumptions about what threading modes we support. I guess we could open a separate discussion for that to see if we even want to support them all.

The solution I had in mind (at some point) was equivalent to your second suggestion (Always waiting) for exactly the reasons you stated. There's a fourth option too: Instead of waiting, just stop and return early when we'd have to wait. So when we can't get the DB lock, we neither cancel the current action nor wait but just return. In this particular case it would mean the conditional formatting is missing for some rows which isn't very nice. But I was thinking about this for e.g. the Import and Export CSV dialog which (with the changes from #1575) can be opened when the DB is still in use.

I'm implementing the wait case. I'm going to add a choice to waitForDbRelease: wait, cancel the other operation or ask user; default, ask. The choice for this case will be wait. I'll open then a PR.

Hello @mgrojo

Just tested since I could not resist it 👍

On Windows 10, with DB.Browser.for.SQLite-win64.zip, this new feature looks awesome:

THANKS a lot indeed!!!