export() resets foreign key pragma

[karlb]

When calling export() on a database, foreign keys are turned off. I would expect PRAGMA foreign_keys to return the same value before and after an export. Generally, I would expect export not to change the state of the db at all.

Example from developer console:

> db.run('PRAGMA foreign_keys=ON');
Database {filename: "dbfile_2179398655", db: 5292304, statements: {…}, changeRows: ƒ, changeRow: ƒ, …}

> db.exec('PRAGMA foreign_keys')[0].values[0];
[1]

> db.export()
Uint8Array(32768) [83, 81, 76, 105, 116, 101, 32, 102, 111, 114, 109, 97, 116, 32, 51, 0, 16, 0, 1, 1, 0, 64, 32, 32, 0, 0, 1, 43, 0, 0, 0, 8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 223, 0, 0, 0, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 43, 0, 46, 28, 176, …]

> db.exec('PRAGMA foreign_keys')[0].values[0];
[0]

[YagoLopez]

I have commented a similar question in other thread. Hope it helps.
https://github.com/kripken/sql.js/issues/221#issuecomment-390499489

[karlb]

Your comment seems to be about the fact that the export does not contain the pragma information, right? That matches the sqlite behavior and is understandable to me. What I'm surprised about is that doing an export changes the pragma for my existing connection.

[YagoLopez]

@karlb yes you are right, your case is very strange. I would try to reproduce that behaviour using the online sql gui to assure my code has not errors.

[seidtgeist]

[time machine noises]

This is happening because the export function closes the database file before it’s read into an Uint8Array and then opened again:

sql.js/src/api.js

Lines 866 to 868 in 5c40349

	this.handleError(sqlite3_close_v2(this.db));
	binaryDb = FS.readFile(this.filename, { encoding: "binary" });
	this.handleError(sqlite3_open(this.filename, apiTemp));

A newly-opened database doesn’t come with prior pragmas.

Could this be documented in the export function, or would it be possible to export the database without closing and re-opening it?

On a related note, I’ve been wondering which pragmas (especially locking_mode and journal_mode) make sense for sql.js since everything is happening in memory buffers anyway?

[lovasoa]

Yes, we've discussed that in #397
Closing the database and reopening it has many disadvantages. We should be able to just just flush it before exporting, without closing it.

[lovasoa]

I am going to close this issue as a duplicate of #159
But we should still fix it.

[seidtgeist]

@lovasoa Makes sense! I’ll read up on those two issues.