sqlitex.ExecScript fails to run scripts that include statements that cannot be executed within a transaction

[DrGo]

@crawshaw @AdamSLevy

Currently, ExecScript executes passed script within a SAVEPOINT transaction. This prevents certain commands from running, eg, vacuum, begin. These are commonly used commands in SQL scripts. Should we have a different version that does not automatically SAVEPOINT?

Thanks,

[AdamSLevy]

@DrGo ExecScript runs a SAVEPOINT by design so that scripts can be run atomically. I understand there are certain operations you can't do inside of a transaction such as vacuuming, but why would you want to use BEGIN when you could just use SAVEPOINT or break up your script using the go APIs for nested transactions?

For the one off vacuum, which I generally do after a database migration, I just use sqlitex.Exec instead. Or rather ExecTransient because I don't think its a Stmt worth caching.

Can you expand on the usecase of having an ExecScript-like function that did not use a SAVEPOINT, or an option to not use it?

[boutros]

I think perhaps this is the explanation for the issue I opened: https://github.com/crawshaw/sqlite/issues/131

So the use case for me is to run a PRAGMA statement on all connections - for example to enforce FOREIGN_KEYS.

I see @DrGo have a solution here which seems reasonable: https://github.com/DrGo/sqlite/commit/018b2434e9e4ab2ed9712caf59df8ce7e902dcbf