Oh I forgot, this is related to issues #161, #409, #422, #1005, #1006, #1274, #1363 and #1447.

This is indeed highly wanted 😄

I'll take a look tomorrow.

If you have the time, can you make a one off build of this for macOS and Windows so we can give this to some more testers?

Yep. I'm just about to upgrade the Win & OSX Qt versions on the build box so our future nightlies use newer Qt pieces. I'll create a build using this PR first though, so people can test it without having to wonder if it's weirdness from the Qt upgrade. 😄

Doing the OSX build now (made a script for it, for easier future rebuilding). Noticed one warning in the output (repeated in several places) that looks important:

In file included from MainWindow.cpp:1:
In file included from ./MainWindow.h:6:
./RunSql.h:10:1: warning: class 'sqlite3' was previously declared as a struct [-Wmismatched-tags]
class sqlite3;
^
./sqlitedb.h:14:8: note: previous use is here
struct sqlite3;
       ^
In file included from MainWindow.cpp:14:
./sqlitetablemodel.h:13:1: warning: struct 'sqlite3' was previously declared as a class [-Wmismatched-tags]
struct sqlite3;
^
./RunSql.h:10:7: note: previous use is here
class sqlite3;

The build itself is still in progress atm... 😄

Hmmm, it looks like the build process on OSX is now taking much longer, as the Homebrew install of Node that we use now builds itself from source. That seems to increase the run time by ~1hr each, for both the SQLite and SQLCipher builds. 😦

Here are the once-off OSX builds with this PR included:

• https://nightlies.sqlitebrowser.org/osx-onceoffs/DB%20Browser%20for%20SQLite_issue-1575v1.dmg
• https://nightlies.sqlitebrowser.org/osx-onceoffs/DB%20Browser%20for%20SQLite-sqlcipher_issue-1575v1.dmg

The standard OSX nightly build is running now. Guessing it'll take ~2 hours (ugh), after which I can trigger a Win build for this PR. 😄

Tried to compile this branch on windows (a few times), but no binary ever gets generated. There are several warnings displayed, mostly regarding the same sqlite3 redeclaration as above, then this linker problem:

6>  Generating Code...
6>RunSql.obj : error LNK2019: unresolved external symbol "public: class std::unique_ptr<class sqlite3,struct DBBrowserDB::DatabaseReleaser> __thiscall DBBrowserDB::get(class QString,bool)" (?get@DBBrowserDB@@QAE?AV?$unique_ptr@Vsqlite3@@UDatabaseReleaser@DBBrowserDB@@@std@@VQString@@_N@Z) referenced in function "private: void __thiscall RunSql::acquireDbAccess(void)" (?acquireDbAccess@RunSql@@AAEXXZ)
6>C:\git_repos\sqlitebrowser\release-sqlite-win32\Release\DB Browser for SQLite.exe : fatal error LNK1120: 1 unresolved externals

My best guess so far 😉, is that the redeclaration is causing the linking problem. So we'll probably need to get that addressed before being able to generate Windows builds for this PR.

Thanks, Justin. That's indeed a legitimate warning which might cause the linker problem too. I have just amended the second commit in order to fix that. Can you try again? 😄

I've compiled the branch. The behaviour already seems nice. I've found a problem though. I created an empty database and executed one SQL script generated with the Export SQL option. It runs smoothly and the log shows the executed statements. It reaches the end without any visible error, but no table is shown in the DB Schema dock and the Write and Revert buttons are disabled. It's like it had been rolled back.

The same file is imported well through the menu option and it works as expected when executed in the editor with a version from master branch. I can send the file by e-mail if it isn't easy to reproduce with another. I've tested with two and the other was OK, so I don't know.

I've noticed also that the result message doesn't show any more the executed statement and the line. Maybe the users would missed that, because without the SQL Log open, the feedback for the execution is too laconic, and can be the same for several queries that the user runs one after the other.

Another suggestion is using the cancel button that you used for PR #1563. You are using the one that we already use for meaning "close". Maybe you only want to not conflict with the other PR, so I'd shut up then 😄 but I prefer to suggest it now.

I've found also a problem with a create statement at the beginning of the buffer, but it is in master too. If the current line is set to one containing a field, the result is Result: near ""livestock"": syntax error:

CREATE TABLE IF NOT EXISTS "livestock_gallery" (
	"_id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"livestock"	INTEGER,
	"type"	INTEGER,
	"year"	INTEGER,
	"month"	INTEGER,
	"date"	INTEGER,
	"filename"	TEXT,
	"link"	INTEGER,
	"text1"	TEXT,
	"text2"	TEXT,
	"text3"	TEXT,
	"int1"	INTEGER,
	"int2"	INTEGER,
	"int3"	INTEGER
);

The same statement works when preceded by a line with ";" so I guess we only need to add a condition for the beginning of file.

Thanks @MKleusberg, the windows binaries are generating ok now.

If people on Windows have time to try things out and provide feed back, it would really help:

• https://nightlies.sqlitebrowser.org/win32-onceoffs/DB%20Browser%20for%20SQLite-sqlcipher-issue1575v2-win32.msi
• https://nightlies.sqlitebrowser.org/win64-onceoffs/DB%20Browser%20for%20SQLite-sqlcipher-issue1575v2-win64.msi

The v2 OSX are building now, and should automatically upload themselves in about 2 hours.

Thanks, Manuel. These are some very good points 😄

I've compiled the branch. The behaviour already seems nice. I've found a problem though. I created an empty database and executed one SQL script generated with the Export SQL option. It runs smoothly and the log shows the executed statements. It reaches the end without any visible error, but no table is shown in the DB Schema dock and the Write and Revert buttons are disabled. It's like it had been rolled back.

The same file is imported well through the menu option and it works as expected when executed in the editor with a version from master branch. I can send the file by e-mail if it isn't easy to reproduce with another. I've tested with two and the other was OK, so I don't know.

You might need to email me that file. I just tried with some test databases here and couldn't reproduce it. Does it happen every time for you with the problematic file?

I've noticed also that the result message doesn't show any more the executed statement and the line. Maybe the users would missed that, because without the SQL Log open, the feedback for the execution is too laconic, and can be the same for several queries that the user runs one after the other.

Good point! 👍 I didn't even see that I have broken this. Should be fixed in the amended commits now 😄

Another suggestion is using the cancel button that you used for PR #1563. You are using the one that we already use for meaning "close". Maybe you only want to not conflict with the other PR, so I'd shut up then smile but I prefer to suggest it now.

You're almost reading my mind there. I wanted to use the Cancel icon from #1563, then thought about the conflict and finally decided to avoid the problem for now. But I guess it's better to have it in this PR too so both can be merged independently. Also the conflict should be really easy to resolve. It's in the amended commits now 😄

I've found also a problem with a create statement at the beginning of the buffer, but it is in master too. If the current line is set to one containing a field, the result is Result: near ""livestock"": syntax error:

CREATE TABLE IF NOT EXISTS "livestock_gallery" (
	"_id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"livestock"	INTEGER,
	"type"	INTEGER,
	"year"	INTEGER,
	"month"	INTEGER,
	"date"	INTEGER,
	"filename"	TEXT,
	"link"	INTEGER,
	"text1"	TEXT,
	"text2"	TEXT,
	"text3"	TEXT,
	"int1"	INTEGER,
	"int2"	INTEGER,
	"int3"	INTEGER
);

The same statement works when preceded by a line with ";" so I guess we only need to add a condition for the beginning of file.

You're right. I have a vague feeling that this might have been on purpose though. Let's take this case and let's imagine the cursor is in the last line:

DROP TABLE important

-- Probably more stuff here

SELECT * FROM table

So for users who don't uses semicolons the current behaviour (most of the time) falls back to the start of the current line which at least produces an understandable error message when it's not the first line of a multi-line statement. The other suggested behaviour would execute everything which might be really unexpected. What do you think? I'm not really leaning towards any side.

Awesome! Thanks, Justin 😄 Also nice catch with the declaration problem there, finding an issue before even starting the build 😉

@chrisjlocke Are you interested in giving these builds a try? Usually you're really good at finding hidden problems 😁

New OSX builds (using the updated 2nd commit):

• https://nightlies.sqlitebrowser.org/osx-onceoffs/DB%20Browser%20for%20SQLite_issue-1575v2.dmg
• https://nightlies.sqlitebrowser.org/osx-onceoffs/DB%20Browser%20for%20SQLite-sqlcipher_issue-1575v2.dmg

Just tried, and tried to import 625,000 records via the 'Execute SQL' tab. Wish I hadn't. 😉

The status is just showing one individual update at a time - probably every 300 ms or so? So as a user, I'd probably (not spotting the changing 'Took 108ms' status line) think either the update had completed, or maybe hadn't even started. I'm curious as to why its taking this long anyway - I thought the whole bunch of commands was wrapped up in a transaction?

If DB4S is working its way down, does it know how many statements its done, compared to how many its got to go? Could it display 'Executing statement 1003 of 45,983' ?
If it doesn't, a 'trick' I once used was to count the number of characters I had to process (eg, 2000). Parse the statement, process it, count how many characters in that statement - 20. So we've processed 20/2000 ... 1%. Repeat. Its not terribly accurate - the progress would shudder its way, rather than be a smooth progress, but even so, at the moment, after 10 minutes, I have no idea how its going on - if I flip to another tab to display all records in the table to count/guess its progress, I'm either told that its busy and bugger off, or 'do I want to cancel' (I got two different messages - is this depending on where in the processing loop it is?)

Side note: If I do cancel, it crashes.

Also. There is no 'completed' message, so the status bar displays "1 row affected" for each row, but no final "and I'm done" message.

@chrisjlocke Are you interested in giving these builds a try? Usually you're really good at finding hidden problems 😁

Do you want further issues in this topic, or shall I open a new issue for each issue found?
I'll add 'em here but can shift them to new issues if required (makes it easier to comment on, debug and close), but I didn't want to panic you if you suddenly saw the issue count shoot up to 432...

Edit: Aah, just spotted the tab icon has an hourglass while its busy!

However, I still feel a 'I'm done!' message would be beneficial...

It reaches the end without any visible error, but no table is shown in the DB Schema dock and the Write and Revert buttons are disabled. It's like it had been rolled back.

Now experiencing this. Didn't check before! 😆

So my script imports 3,000 rows.... except it doesn't. It says it does though.

Result: query executed successfully. Took 0ms, 1 rows affected
-- At line 908:
insert into comboboxHistory (ch_userId,ch_comboboxId,ch_date,ch_item) values (8,29,'2000-01-01','Item');
-- Result: query executed successfully. Took 0ms, 1 rows affected
-- At line 909:
insert into comboboxHistory (ch_userId,ch_comboboxId,ch_date,ch_item) values (8,29,'2000-01-01','Item');
-- Result: query executed successfully. Took 0ms, 1 rows affected
-- At line 910:
insert into comboboxHistory (ch_userId,ch_comboboxId,ch_date,ch_item) values (8,29,'2000-01-01','Item');
-- Result: query executed successfully. Took 0ms, 1 rows affected

But no new records appear.

While importing the records, if you switch to another tab and get the 'stop it - I'm busy. Do you want me to stop what I'm doing?' but press 'No', DB4S locks up when the import is complete.

Should I put the Qt upgrade of the build server on hold for a few days, while we're getting builds for this PR done?

Actually... no. Qt is supposed to handle parallel installs of different versions ok, I just need to make sure the paths we're using for things still match up ok. Meh... something to look at tomorrow. 😄

Imported records. While importing, pressed Stop. DB4S stopped. Imported again. While importing, pressed Stop. DB4S died.

HOWEVER. If you press Stop and open a new tab, that tab will run a new query fine (and be stoppable) ... but only once ... that too will kill DB4S if you press Stop a second time.

@chrisjlocke

The status is just showing one individual update at a time - probably every 300 ms or so? So as a user, I'd probably (not spotting the changing 'Took 108ms' status line) think either the update had completed, or maybe hadn't even started. I'm curious as to why its taking this long anyway - I thought the whole bunch of commands was wrapped up in a transaction?

Maybe it's the UI updates slowing it down a bit. We have to wait between each two statements for the UI thread to catch up. So if the UI thread is super busy your import is essentially stalled. Might be a good reason to stop the UI updates.

If DB4S is working its way down, does it know how many statements its done, compared to how many its got to go? Could it display 'Executing statement 1003 of 45,983' ?
If it doesn't, a 'trick' I once used was to count the number of characters I had to process (eg, 2000). Parse the statement, process it, count how many characters in that statement - 20. So we've processed 20/2000 ... 1%. Repeat. Its not terribly accurate - the progress would shudder its way, rather than be a smooth progress, but even so, at the moment, after 10 minutes, I have no idea how its going on - if I flip to another tab to display all records in the table to count/guess its progress, I'm either told that its busy and bugger off, or 'do I want to cancel'

We can't count statements. So we would need to do the number of characters approximation. I was honestly thinking about exactly that but then decided to leave it out. The reasoning was that we obviously can only update the character counts after each fully executed statement and that might be terribly inaccurate and even misleading. One of my test files is an import with basically just two statements: one creating a table and one inserting ~740k rows. For this file it would stay at 0% all the time suggesting that no progress is happening. But I can see how it helps for your file. Should we add that progress bar?

I got two different messages - is this depending on where in the processing loop it is?

It depends on the way you cancel the query. You can click the stop button, close the running tab, start a new query or do something else which requires DB access. They all got different messages 😄

Also. There is no 'completed' message, so the status bar displays "1 row affected" for each row, but no final "and I'm done" message.

Agreed. Should we just add add an extra line to the result box at the bottom? Or something more prominent like adding some colour somewhere?

Do you want further issues in this topic, or shall I open a new issue for each issue found?

Let's just keep them here for now 😉 Since they don't affect master (yet) that's probably more correct.

It reaches the end without any visible error, but no table is shown in the DB Schema dock and the Write and Revert buttons are disabled. It's like it had been rolled back.

Now experiencing this. Didn't check before! laughing
So my script imports 3,000 rows.... except it doesn't. It says it does though.

Yeah, these were indeed rolled back 🙄 I have just pushed an update which fixes this issue 😄

While importing the records, if you switch to another tab and get the 'stop it - I'm busy. Do you want me to stop what I'm doing?' but press 'No', DB4S locks up when the import is complete.

Good point. I will keep that in mind when addressing the tab switching issue. Unfortunately I believe the only way to not have problems when switching tabs is to disallow switching tabs during execution 😦

Imported records. While importing, pressed Stop. DB4S stopped. Imported again. While importing, pressed Stop. DB4S died.

I couldn't reproduce that. Can you add some more details? Like are you reverting in between or not? Or maybe it's about the timing? If nothing obvious comes to mind the output of WinDbg (for all threads) would be interesting 😄

Yeah, I'll pick and choose the Qt version that makes the most sense at the time. 😉

Thanks I received your file and managed to reproduce the issue. It should be fixed now in the updated commits here ...

Sounds like a v3 is needed then. The build server is doing macOS building for the next ~2 hours, but I can run the "once off" generator (another recently made script 😉) after it, to make new Windows builds for people to test.

Might be a good reason to stop the UI updates.

What I do in my applications is check/record the current second of the time, and when that changes, update the status, so it does 1 status update a second. Quick 'n cheap to implement.

For this file it would stay at 0% all the time suggesting that no progress is happening.

Aah, have another background task that counts the lines in the query - you know how many lines you've processed (as that's written to the log)... that's more of a tongue-in-cheek answer though - that's way too complex and possibly overkill for what is a not-happens-often task. 90% of queries thrown in the Execute SQL pane will be single statements. Well, I know mine are...

Should we just add add an extra line to the result box at the bottom?

That would get my vote. Its where the other information is, so belongs there.

the only way to not have problems when switching tabs is to disallow switching tabs during execution

That kinda negates the benefits of a background running task. The point is to let the UI continue, but we now have to stop them using the UI while the task is running!

I couldn't reproduce that.

I was using v1 of the builds - it might be something you addressed in your development version. The poor build server is chomping away on other builds, so when another Windows one plops out the other end, I'll try again. You might have to remind me how to get the info for all threads out of WInDbg.,,

A new build (v3) for Windows has just started. Binaries should be up in roughly an hour or so. Upload speed from here is very, very slow.

Thanks I received your file and managed to reproduce the issue. It should be fixed now in the updated commits here 😄 I was indeed rolling back all the changes 🙄

I've tested again with the new commits and I can confirm that it's working now.

So for users who don't uses semicolons the current behaviour (most of the time) falls back to the start of the current line which at least produces an understandable error message when it's not the first line of a multi-line statement. The other suggested behaviour would execute everything which might be really unexpected. What do you think? I'm not really leaning towards any side.

I see. Well, it's difficult to support both styles without rough cases. I don't know. Better not to change the current behaviour if we don't know how to do it well.

New windows builds (v3) are online:

• https://nightlies.sqlitebrowser.org/win32-onceoffs/DB%20Browser%20for%20SQLite-sqlcipher-issue1575v3-win32.msi
• https://nightlies.sqlitebrowser.org/win64-onceoffs/DB%20Browser%20for%20SQLite-sqlcipher-issue1575v3-win64.msi

Pressing stop while inserting records crashes* straight away now, rather than on the second attempt.
(using v3 of the build)

*DB4S went into 'not responding' then simply vanished. Windows didn't even get a chance to display a 'DB4S is not responding' or 'its died' dialog

Same occurs if importing and switch tabs. A dialog appears ("do you want to stop the other process?") and if I click No, Db4S goes into 'not responding' mode.

I'll leave it for a bit in case its just the UI thread waiting, but there doesn't appear to be any disk activity.

I see. Well, it's difficult to support both styles without rough cases. I don't know. Better not to change the current behaviour if we don't know how to do it well.

@mgrojo We could just open an issue for it now, so the idea isn't lost.

Same occurs if importing and switch tabs. A dialog appears ("do you want to stop the other process?") and if I click No, Db4S goes into 'not responding' mode.

I'll leave it for a bit in case its just the UI thread waiting, but there doesn't appear to be any disk activity.

@chrisjlocke Seems like a deadlock situation. And by sheer coincidence (after thinking about your report) I have just encountered one of those in the code which was easy to fix too 😉 I start to feel sorry for @justinclift but I guess we need another build 😄 Good news is that the next build will also show a "Execution completed" message after the last executed statement and reduce the chance you get a Yes/No message box when changing tabs. And if you get one, you should be safe to click No.

That all said, it shouldn't crash because of the deadlock - just lock up. So I guess the crashes you saw are yet another problem. But we can check that with WinDbg as soon as you have the next build.

Cool. Just kicked off a new build (v4). Should be online in about an hour. 😄

@chrisjlocke Did you get any more crashes with this? If so, I now have the means to prepare some screenshots of WinDbg to walk you through the multiple thread thing here.

This isn't linked to the 'execute sql' tab, so unsure if it lives here. Yell if not, and I can create a new issue.

If I click on the 'Browse Data' tab on a table with 2.3 million rows, there is about a 1 to 1.5 second delay while the 'determining row count' thingy does its job. If I click on F5 to run that query again while its doing that query, the dialog box appears, "The database is currently busy..." etc. However, the query completes in the background. The grid is populated. However, whether you click on 'Yes' or 'No', the grid is cleared, and the query is re-run.

This is with nightly from 19 Nov.

@chrisjlocke That's indeed not related to this PR. A new issue for this probably makes sense.

I think I will rebase this PR and then merge it. It's apparently more or less working - excluding some UI polishing - and by merging it it'll get even more testing 😉

@chrisjlocke If you have the time, can you check again if it's working with tomorrow's nightly build? For remaining issues with this, I'd suggest opening another new issue 😄 If/when everything is working fine, we can update issues #161, #409, #422, #1005, #1006, #1274, #1363 and #1447 again 😉