Exporting database resets pragmas to their default values

[chrisnovakovic]

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()?

[ttsirkia]

Since this is still open, could the documentation be updated to mention this side-effect? Caused a strange bug in my app before noticing this issue.

[lovasoa]

pr welcome !

[ttsirkia]

Documentation fix #572