Allow foreign key constraints to be disabled in batches

[psarna]

Our code currently implicitly wraps batches of statements into BEGIN + COMMIT or BEGIN + ROLLBACK. Problem is, in order to temporarily turn off foreign key contraints users might want to send a PRAGMA FOREIGN_KEYS=off statement as a first statement in the batch. Unfortunately, this pragma is not effective inside a transaction: https://www.sqlite.org/foreignkeys.html#fk_enable

A simple workaround would be to check if the first statement happens to be a pragma and execute it outside of the transaction scope. Another simple workaround would be to add a special interface that allows you to say "please run this batch without foreign keys", and then we'd send a PRAGMA FOREIGN_KEYS=off ourselves, right before the BEGIN+COMMIT block.

[psarna]

/cc @penberg

[psarna]

It also sounds like executeMultiple can be used to execute statements without the explicit BEGIN+COMMIT blocks: https://github.com/tursodatabase/libsql-client-ts/blob/9decdd54a6207e2b01c370af3dccf418ebbd4cc3/packages/libsql-core/src/api.ts#L259-L268

So a workaround for the above is to use executeMultiple with pragma foreign_keys=off; begin; your-stuff; commit rather than batch with your-stuff.

[Martoxdlol]

Same issue here!!!

Los too many hours because of this