Description
As of commit 1ee5a18, which fixes issues #9 and #55, the export() function closes the SQLite database (sqlite3_close_v2()) and reopens it (sqlite3_open()). This silently causes any pragmas that have been set since the database was last opened to be reset to their default values. This can have some nasty side-effects - the most obvious (and perhaps most damaging) example of this is with the foreign_keys pragma, which tells SQLite to enforce foreign key constraints on insertions and updates and is OFF by default.
Here's a MWE demonstrating the problem:
var sql = require("./sql.js");
var util = require("util");
var db = new sql.Database();
db.run("pragma foreign_keys = on");
db.run("create table x (xa int, xb char, primary key (xa)) without rowid");
db.run("create table y (ya int, yb int not null references x(xa), primary key (ya)) without rowid");
//db.export();
db.run("insert into x values (1, 'a'), (2, 'b'), (3, 'c')");
db.run("insert into y values (1, 1)");
db.run("insert into y values (999, 999)");
console.log(util.inspect(db.exec("select * from x order by xa"), false, null));
console.log(util.inspect(db.exec("select * from y order by ya"), false, null));
Output:
Error: FOREIGN KEY constraint failed
at Error (<anonymous>)
...
With line 9 uncommented:
[ { columns: [ 'xa', 'xb' ],
values: [ [ 1, 'a' ], [ 2, 'b' ], [ 3, 'c' ] ] } ]
[ { columns: [ 'ya', 'yb' ], values: [ [ 1, 1 ], [ 999, 999 ] ] } ]
I understand the need for the database to be closed and reopened, but is there any way the non-default pragma values that were set since the database was opened can be restored after it is reopened in the call to export()?