"Reset DB" in SQLite Fiddle is not always working

(1) By vorimi on 2024-03-04 23:26:59 [source]

Steps to reproduce:

open sqlite.org/fiddle/index.html
paste pastebin.com/raw/SzwMKePi [1] in the input textarea
press "Run" button (SQLs should be executed /wo any error)
press "Reset DB" button
press "Run" button again
notice DB was not reset in step 4

It seems the "Reset DB" is not accounting for possibly unclosed transaction.

I would expect SQLite to kill/close any uncloded transaction and reset the DB.

I would expect the source database to be always recreated from scratch so developer can rely on "Reset DB" in the fiddle reliably.

And let me express here one related feature request - please allow to always rerun the input on a clean DB - "Reset DB before run" option would be very helpful.

[1] copy of content of that link:

-- ==================================================
-- Use ctrl-enter or shift-enter to execute sqlite3
-- shell commands and SQL.
-- If a subset of the text is currently selected,
-- only that part is executed.
-- ==================================================
.nullvalue NULL
.headers on
 
CREATE TABLE `user` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL COLLATE `NOCASE`
);
 
 
BEGIN;

(2) By Stephan Beal (stephan) on 2024-03-05 06:38:00 in reply to 1 [link] [source]

It seems the "Reset DB" is not accounting for possibly unclosed transaction.

Good catch, thank you for the report. That's now resolved but won't be on the live system until the next time the site is updated:

Resetting database.
Rolling back in-progress transaction.
Reset /fiddle.sqlite3

Fixing that also led to fixing an unrelated problem which could caused /fiddle initialization to fail prematurely.

please allow to always rerun the input on a clean DB - "Reset DB before run" option would be very helpful.

That was a consideration during the initial development and was rejected because it seems likely to cause more grief, via unintended resets, than any small grief a manual reset requires, especially if enabled when using OPFS-backed databases (where arbitrarily large amounts of data could be deleted). That's still the reigning opinion on that feature. If you find the reset button too onerous, another option is to simply tap the Ctrl-R to reload the page. That will clear out any transient dbs, but not any which have been manually opened on OPFS storage (where a manual reset is necessary).

Thank you for the bug report and feedback!