Broken SQL.open( data ) / SQL.exportData()

[sql-js]

Broken SQL.open( data ) / SQL.exportData()

[acajic]

Is it possible that this is still not fixed? I am having the same problem.

https://github.com/kripken/sql.js/issues/55

[lovasoa]

This issue was opened a while ago, before I arrive in the project.
I'm now the only active maintainer of this repo. I'll take a look at this next weekend.

If you want to look at this yourself, I think the first thing to try is to close the database before exporting its contents. The method to change is here: https://github.com/kripken/sql.js/blob/master/coffee/api.coffee#L374

[acajic]

Thank you for a quick response, I will look at the code right away. Will post if I have any progress.

[acajic]

https://github.com/kripken/sql.js/blob/master/coffee/api.coffee#L374

is a one-line method:

'export': -> new Uint8Array FS.root.contents[@filename].contents

I am not familiar with coffeescript but it seems to me that the file by the name of @filename that is supposed to be created inside constructor does not get updated when I perform INSERT/UPDATE/DELETE commands agains db.

[lovasoa]

I'm sorry, but I can't reproduce this issue, nor #55. I added a test to the test directory, and created a jsfiddle with the code reported here (http://jsfiddle.net/3tx8G/). Both behave as expected.

Can you please provide a js file that triggers the error?

[acajic]

I updated JSFiddle so that my problem is now apparent:

http://jsfiddle.net/3tx8G/3/

[lovasoa]

Ok, I'll look at it after my holidays. Thank you for the report, and for the test case.

[acajic]

Are there any news on this issue?
Feel free to use the server that is used in jsFiddle, I hope it will stay functional in the following days...

[lovasoa]

I've added the sqlite db from your example to the tests.

The tests break now, you can see it here: https://travis-ci.org/kripken/sql.js

The fix I first thought of (closing the db before exporting its contents) doesn't work.

So, I really don't know where the problem is. I'm going to debug this, but it will require much more work than I first imagined.

I will investigate during one of the next weekend. If anyone wants to help, he's welcome, and I'll work with them

[acajic]

OK, thanks for the update. I doubt that I can make a contribution but I will look into the code and try to make some sense out of it all.

[lovasoa]

The code really isn't very complex. It's just bindings to the original sqlite library, the documentation of which can be found here: http://www.sqlite.org/c3ref/funclist.html

For this particular bug, I think the best is to compile the whole thing in debug mode with emscripten (with -g) execute it in the browser, and add breakpoints in the emscripten virtual filesystem code to see what's going on.

[acajic]

Database = (function() {
  function Database(data) {
    this.filename = 'dbfile_' + (0xffffffff * Math.random() >>> 0);
    ...

When testing test_issue55.js, this constructor gets called 2 times.
First time instantiating the database based on the 'issue55.db' file.
Second time using the bytes array generated by the function db.export().

I compared the contents of the 'data' argument to this constructor both times it is called, they are identical.
But this we already knew.

Then I inspected the db.export() function and it is a one-liner so I believe nothing can go wrong there.
So the problem can be:
1) db.export() is referencing the wrong file by using 'this.filename'.
2) When db.run() executes, the changes are manifested inside the 'this.db' object but not in the underlying file referenced by 'this.filename'.
3) (out of ideas) FS.readFile somehow caches the value of the file and does not return correct result if the file was modified in the meantime.

Pursuing 2), I checked db.run() function.
It contains an if-else blocks where an 'if' block delegates all the work to 'sqlite3_exec' function and the 'else' block depends on two functions - 'sqlite3_prepare_v2' and 'sqlite3_step'.
I tried to look for potential problem inside 'sqlite3_exec', 'sqlite3_prepare_v2' or 'sqlite3_step' functions, but they are too complicated for me.

Number 1) seems more likely to be the actual problem. But I have not yet made any progress in this direction.