search

sql-js / sql.js

A javascript library to run SQLite on the web.
http://sql.js.org
Other
12.66k stars 1.06k forks source link

A way to attach multiple databases? #88

Open rharder opened 9 years ago

rharder commented 9 years ago

I can't figure out how to attach multiple databases to run queries across two sqlite files. Something like this:

ATTACH 'db1.sqlite3' AS db1; ATTACH 'db2.sqlite3' AS db2; SELECT * FROM db1.users NATURAL JOIN db2.meta

Works in sqlite at command line. Not sure how to achieve same effect here. Possible?

-Rob

lovasoa commented 9 years ago

Yes, it would be possible, and even not very difficult.

We would have to create a new attach method, that would do basically the same thing as the Database constructor, and then run an ATTACH query. Are you interested in doing this patch?

rharder commented 9 years ago

I’d be interested in taking a look. I’ll be a little slow on it while I get up to speed, but I’m only waiting on me I guess!

I’ll try to keep my questions to a minimum, but I’m sure I’ll need a bit of help poking around your code here and there.

If the underlying sqlite engine is intact, which is my understanding since it’s a direct c->js port, then hopefully I only need to have two DBs in memory and figure out how to glue the memory together, so to speak.

-Rob

On Feb 5, 2015, at 1:43 PM, Ophir LOJKINE notifications@github.com wrote:

Yes, it would be possible, and even not very difficult.

We would have to create a new attach method, that would do basically the same thing as the Database constructor https://github.com/kripken/sql.js/blob/master/coffee/api.coffee#L240, and then run an ATTACH query. Are you interested in doing this patch?

— Reply to this email directly or view it on GitHub https://github.com/kripken/sql.js/issues/88#issuecomment-73123215.

lovasoa commented 9 years ago

Exactly!

And the database file is loaded on a virtual filesystem provided by emscripten. So all you need to do is call this fs api to load the database to a file, then running attach.

rharder commented 9 years ago

Ok. I'm trying to get the build environment up and running now. Thx.

Rob

Sent from my iPhone

On Feb 5, 2015, at 11:17 PM, Ophir LOJKINE notifications@github.com wrote:

Exactly!

And the database file is loaded on a virtual filesystem provided by emscripten. So all you need to do is call this fs api to load the database to a file, then running attach.

— Reply to this email directly or view it on GitHub.

rharder commented 9 years ago

Hmm. I'm having trouble getting a Windows build environment working (I'm Windows by day, Mac by night).

I put Node.js and Emscripten in c:\ (after space-in-path errors in c:\Program Files), and I'm getting the following:

Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Robert.Harder\Documents\GitHub\sql.js>emmake make ERROR root: Exception thrown when invoking Popen in make with args: "make"! Traceback (most recent call last): File "C:\Emscripten\emscripten\1.29.0\emmake", line 26, in shared.Building.make(sys.argv[1:]) File "C:\Emscripten\emscripten\1.29.0\tools\shared.py", line 1113, in make process = Popen(args, stdout=stdout, stderr=stderr, env=env) File "C:\Python27\lib\subprocess.py", line 710, in init errread, errwrite) File "C:\Python27\lib\subprocess.py", line 958, in _execute_child startupinfo) WindowsError: [Error 2] The system cannot find the file specified

C:\Users\Robert.Harder\Documents\GitHub\sql.js>

Running under Cygwin gives me different errors, but it seems more related to the tools not expecting to run under that environment:

Robert.Harder@dfcs-harder05 /cygdrive/c/users/Robert.Harder/Documents/GitHub/sql.js $ emmake make

Generate llvm bitcode

/cygdrive/c/Emscripten/emscripten/1.29.0/emcc -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_INT64_TYPE="long long int" -DSQLITE_THREADSAFE=0 c/sqlite3.c -o c/sqlite3.bc INFO root: (Emscripten: Running sanity checks)

module.js:340 throw err; ^ Error: Cannot find module 'C:\cygdrive\c\Emscripten\emscripten\1.29.0\src\hello_world.js' at Function.Module._resolveFilename (module.js:338:15) at Function.Module._load (module.js:280:25) at Function.Module.runMain (module.js:497:10) at startup (node.js:119:16) at node.js:929:3 Checking JS engine ['/cygdrive/c/nodejs/node'] failed. Check ~/.emscripten. Details: Expected the command ['/cygdrive/c/nodejs/node', '/cygdrive/c/Emscripten/emscripten/1.29.0/src/hello_world.js'] to finish with return code 0, but it returned with code 8 instead! Output: CRITICAL root: The JavaScript shell used for compiling (['/cygdrive/c/nodejs/node']) does not seem to work, check the paths in ~/.emscripten Makefile:38: recipe for target 'c/sqlite3.bc' failed make: *\ [c/sqlite3.bc] Error 1

Robert.Harder@dfcs-harder05

/cygdrive/c/users/Robert.Harder/Documents/GitHub/sql.js

I know the way this normally works: there's something simple and obvious to get the environment working, but it's foreign to new contributors, and I'm new. Any pointers?

Thanks.

-Rob

On Fri, Feb 6, 2015 at 7:00 AM, Robert Harder robertharder@gmail.com wrote:

Ok. I'm trying to get the build environment up and running now. Thx.

Rob

Sent from my iPhone

On Feb 5, 2015, at 11:17 PM, Ophir LOJKINE notifications@github.com wrote:

Exactly!

And the database file is loaded on a virtual filesystem provided by emscripten. So all you need to do is call this fs api to load the database to a file, then running attach.

— Reply to this email directly or view it on GitHub https://github.com/kripken/sql.js/issues/88#issuecomment-73187908.

lovasoa commented 9 years ago

Did you install emscripten with the emscripten sdk for windows?

Le 6 février 2015 16:36:38 UTC+01:00, Robert Harder notifications@github.com a écrit :

Hmm. I'm having trouble getting a Windows build environment working (I'm Windows by day, Mac by night).

I put Node.js and Emscripten in c:\ (after space-in-path errors in c:\Program Files), and I'm getting the following:

Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Robert.Harder\Documents\GitHub\sql.js>emmake make ERROR root: Exception thrown when invoking Popen in make with args: "make"! Traceback (most recent call last): File "C:\Emscripten\emscripten\1.29.0\emmake", line 26, in shared.Building.make(sys.argv[1:]) File "C:\Emscripten\emscripten\1.29.0\tools\shared.py", line 1113, in make process = Popen(args, stdout=stdout, stderr=stderr, env=env) File "C:\Python27\lib\subprocess.py", line 710, in init errread, errwrite) File "C:\Python27\lib\subprocess.py", line 958, in _execute_child startupinfo) WindowsError: [Error 2] The system cannot find the file specified

C:\Users\Robert.Harder\Documents\GitHub\sql.js>

Running under Cygwin gives me different errors, but it seems more related to the tools not expecting to run under that environment:

Robert.Harder@dfcs-harder05 /cygdrive/c/users/Robert.Harder/Documents/GitHub/sql.js $ emmake make

Generate llvm bitcode

/cygdrive/c/Emscripten/emscripten/1.29.0/emcc -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_INT64_TYPE="long long int" -DSQLITE_THREADSAFE=0 c/sqlite3.c -o c/sqlite3.bc INFO root: (Emscripten: Running sanity checks)

module.js:340 throw err; ^ Error: Cannot find module 'C:\cygdrive\c\Emscripten\emscripten\1.29.0\src\hello_world.js' at Function.Module._resolveFilename (module.js:338:15) at Function.Module._load (module.js:280:25) at Function.Module.runMain (module.js:497:10) at startup (node.js:119:16) at node.js:929:3 Checking JS engine ['/cygdrive/c/nodejs/node'] failed. Check ~/.emscripten. Details: Expected the command ['/cygdrive/c/nodejs/node', '/cygdrive/c/Emscripten/emscripten/1.29.0/src/hello_world.js'] to finish with return code 0, but it returned with code 8 instead! Output: CRITICAL root: The JavaScript shell used for compiling (['/cygdrive/c/nodejs/node']) does not seem to work, check the paths in ~/.emscripten Makefile:38: recipe for target 'c/sqlite3.bc' failed make: *\ [c/sqlite3.bc] Error 1

Robert.Harder@dfcs-harder05

/cygdrive/c/users/Robert.Harder/Documents/GitHub/sql.js

I know the way this normally works: there's something simple and obvious to get the environment working, but it's foreign to new contributors, and I'm new. Any pointers?

Thanks.

-Rob

On Fri, Feb 6, 2015 at 7:00 AM, Robert Harder robertharder@gmail.com wrote:

Ok. I'm trying to get the build environment up and running now. Thx.

Rob

Sent from my iPhone

On Feb 5, 2015, at 11:17 PM, Ophir LOJKINE notifications@github.com wrote:

Exactly!

And the database file is loaded on a virtual filesystem provided by emscripten. So all you need to do is call this fs api to load the database to a file, then running attach.

— Reply to this email directly or view it on GitHub https://github.com/kripken/sql.js/issues/88#issuecomment-73187908.

Reply to this email directly or view it on GitHub: https://github.com/kripken/sql.js/issues/88#issuecomment-73255051

rharder commented 9 years ago

Yes. I first put it in the default c:\program files and then uninstalled/reinstalled to c:.

My %PATH% includes C:\nodejs\; C:\Emscripten\clang\e1.29.0_64bit; C:\Emscripten\node\0.10.17_64bit; C:\Emscripten\python\2.7.5.3_64bit; C:\Emscripten\java\7.45_64bit\bin; C:\Emscripten; C:\Emscripten\emscripten\1.29.0; C:\Emscripten\crunch\1.03; C:\Emscripten\mingw\4.6.2_32bit

-Rob

On Fri, Feb 6, 2015 at 9:58 AM, Ophir LOJKINE notifications@github.com wrote:

Did you install emscripten with the emscripten sdk for windows?

Le 6 février 2015 16:36:38 UTC+01:00, Robert Harder < notifications@github.com> a écrit :

Hmm. I'm having trouble getting a Windows build environment working (I'm Windows by day, Mac by night).

I put Node.js and Emscripten in c:\ (after space-in-path errors in c:\Program Files), and I'm getting the following:

Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Robert.Harder\Documents\GitHub\sql.js>emmake make ERROR root: Exception thrown when invoking Popen in make with args: "make"! Traceback (most recent call last): File "C:\Emscripten\emscripten\1.29.0\emmake", line 26, in shared.Building.make(sys.argv[1:]) File "C:\Emscripten\emscripten\1.29.0\tools\shared.py", line 1113, in make process = Popen(args, stdout=stdout, stderr=stderr, env=env) File "C:\Python27\lib\subprocess.py", line 710, in init errread, errwrite) File "C:\Python27\lib\subprocess.py", line 958, in _execute_child startupinfo) WindowsError: [Error 2] The system cannot find the file specified

C:\Users\Robert.Harder\Documents\GitHub\sql.js>

Running under Cygwin gives me different errors, but it seems more related to the tools not expecting to run under that environment:

Robert.Harder@dfcs-harder05 /cygdrive/c/users/Robert.Harder/Documents/GitHub/sql.js $ emmake make

Generate llvm bitcode

/cygdrive/c/Emscripten/emscripten/1.29.0/emcc -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_INT64_TYPE="long long int" -DSQLITE_THREADSAFE=0 c/sqlite3.c -o c/sqlite3.bc INFO root: (Emscripten: Running sanity checks)

module.js:340 throw err; ^ Error: Cannot find module 'C:\cygdrive\c\Emscripten\emscripten\1.29.0\src\hello_world.js' at Function.Module._resolveFilename (module.js:338:15) at Function.Module._load (module.js:280:25) at Function.Module.runMain (module.js:497:10) at startup (node.js:119:16) at node.js:929:3 Checking JS engine ['/cygdrive/c/nodejs/node'] failed. Check ~/.emscripten. Details: Expected the command ['/cygdrive/c/nodejs/node', '/cygdrive/c/Emscripten/emscripten/1.29.0/src/hello_world.js'] to finish with return code 0, but it returned with code 8 instead! Output: CRITICAL root: The JavaScript shell used for compiling (['/cygdrive/c/nodejs/node']) does not seem to work, check the paths in ~/.emscripten Makefile:38: recipe for target 'c/sqlite3.bc' failed make: *\ [c/sqlite3.bc] Error 1

Robert.Harder@dfcs-harder05

/cygdrive/c/users/Robert.Harder/Documents/GitHub/sql.js

I know the way this normally works: there's something simple and obvious to get the environment working, but it's foreign to new contributors, and I'm new. Any pointers?

Thanks.

-Rob

On Fri, Feb 6, 2015 at 7:00 AM, Robert Harder robertharder@gmail.com wrote:

Ok. I'm trying to get the build environment up and running now. Thx.

Rob

Sent from my iPhone

On Feb 5, 2015, at 11:17 PM, Ophir LOJKINE notifications@github.com wrote:

Exactly!

And the database file is loaded on a virtual filesystem provided by emscripten. So all you need to do is call this fs api to load the database to a file, then running attach.

— Reply to this email directly or view it on GitHub https://github.com/kripken/sql.js/issues/88#issuecomment-73187908.

Reply to this email directly or view it on GitHub: https://github.com/kripken/sql.js/issues/88#issuecomment-73255051

— Reply to this email directly or view it on GitHub https://github.com/kripken/sql.js/issues/88#issuecomment-73271105.

lovasoa commented 9 years ago

If it's a fresh install through the emscripten SDK, then it's a bug in emscripten, and you should report it.

tkns commented 9 years ago

A 'filename' property of db instance can use to attach a database. try "attach /'" + db.filename + "' as db1;".

lovasoa commented 9 years ago

Yes, but that forces you to create a second database object. What we are talking about here is to create a db.attach() method.

However, I hadn't think about that possibility, which is more simple.

rharder commented 9 years ago

If I'm catching your meaning, since DBs can be loaded in a variety of ways, it might be safest to join two existing DBs. One might be local. One might be loaded from server.

Rob

Sent from my iPhone

On Feb 7, 2015, at 8:08 AM, Ophir LOJKINE notifications@github.com wrote:

Yes, but that forces you to create a second database object. What we are talking about here is to create a db.attach() method.

However, I hadn't think about that possibility, which is more simple.

— Reply to this email directly or view it on GitHub.

lovasoa commented 9 years ago

What @tkns is saying is that you can do

    var db = new SQL.Database(data1);
    var db2 = new SQL.Database(data2);
    db.run("ATTACH "+db2.filename+" as db2");
rharder commented 9 years ago

You mean the functionality is already built in, more or less? Well that'll be awesome. I'll want to make sure it can work from a worker too, which is how I'm using it and is driving my need.

Rob

Sent from my iPhone

On Feb 7, 2015, at 8:20 AM, Ophir LOJKINE notifications@github.com wrote:

What @tkns is saying is that you can do

var db = new SQL.Database(data1);
var db2 = new SQL.Database(data2);
db.run("ATTACH "+db2.filename+" as db2");

— Reply to this email directly or view it on GitHub.

lovasoa commented 9 years ago

There is no "functionality"to build in, because it's just using a feature of sqlite. However, you still have to compile sql.js without the SQLITE_OMIT_ATTACH flag which has been added in ca7b460e39e97ea185caa3e04ac37b953896ce3e.

rharder commented 9 years ago

Well that was easy. ;-)

Rob

Sent from my iPhone

On Feb 7, 2015, at 8:36 AM, Ophir LOJKINE notifications@github.com wrote:

There is no "functionality"to build in, because it's just using a feature of sqlite. However, you still have to compile sql.js without the SQLITE_OMIT_ATTACH flag which has been added in ca7b460.

— Reply to this email directly or view it on GitHub.

rharder commented 9 years ago

Well then here's a test_attach.js file, though it doesn't verify that DBs loaded from different sources work.

exports.test = function(sql, assert){
    // Create two databases
    var db1 = new sql.Database();
    var db2 = new sql.Database();
    var db3 = new sql.Database();

    // Populate db1
    db1.exec("CREATE TABLE users (username,city);");
    db1.exec("INSERT INTO users VALUES ('alice','San Francisco');");
    db1.exec("INSERT INTO users VALUES ('bob',  'New York');");
    db1.exec("INSERT INTO users VALUES ('eve',  'Denver');");
    var result = db1.exec("SELECT name FROM sqlite_master WHERE type='table'");
    assert.deepEqual(result, [{columns:['name'], values:[['users']]}],
        "Table properly created in db1");

    // Populate db2
    db2.exec("CREATE TABLE cities (city,state);");
    db2.exec("INSERT INTO cities VALUES ('San Francisco','CA');");
    db2.exec("INSERT INTO cities VALUES ('New York','NY');");
    db2.exec("INSERT INTO cities VALUES ('Denver','CO');");
    result = db2.exec("SELECT name FROM sqlite_master WHERE type='table'");
    assert.deepEqual(result, [{columns:['name'], values:[['cities']]}],
        "Table properly created in db2");

    // Attach dbs
    db3.run("ATTACH "+db1.filename+" as db1");
    db3.run("ATTACH "+db2.filename+" as db2");

    // Verify with a join
    result = db3.exec("SELECT username, city, state FROM db1.users NATURAL JOIN db2.cities;");
    assert.deepEqual(result,
        [{columns:['username', 'city', 'state'], 
        values:[['alice','San Francisco','CA'],
        ['bob','New York','NY'],['eve','Denver','CO']]}],
        "Proper SELECT returned after attaching two databases");

    // Close the database and all associated statements
    db1.close();
    db2.close();
    db3.close();
}

if (module == require.main) {
    var sql = require('../js/sql.js');
    var assert = require("assert");
    exports.test(sql, assert);
}

-Rob

rharder commented 9 years ago

When I try to do this same thing in a worker, it doesn't work. I can't figure out why. I've distilled the problem down into as small an HTML example as possible. I'm at a loss. Incidentally, this might resolve itself if a worker could be loaded with an existing database and not just an array buffer. If I join a DB first and then export it, it loses its "attach" knowledge.

<!DOCTYPE html>
<html>
<head>
<title>Attach fails when in a worker</title>
<meta charset="UTF-8">
<script src="sql.js"></script>
<script id="test" type="text/javascript">

var sqlWorker = new Worker("worker.sql.js"); 

var db1 = new SQL.Database();
db1.run( "CREATE TABLE users (username,city);" );
db1.run( "INSERT INTO `users` VALUES ('alice','San Francisco');" );
db1.run( "INSERT INTO `users` VALUES ('bob','New York');" );
db1.run( "INSERT INTO `users` VALUES ('eve','Denver');" );

var db2 = new SQL.Database();
db2.run( "CREATE TABLE cities (city,state);" );
db2.run( "INSERT INTO `cities` VALUES ('San Francisco','CA');" );
db2.run( "INSERT INTO `cities` VALUES ('New York','NY');" );
db2.run( "INSERT INTO `cities` VALUES ('Denver','CO');" );

// Verify that direct SQL.Database manipulation works
var db3 = new SQL.Database();
db3.run( "ATTACH '" + db1.filename + "' AS db1" );
db3.run( "ATTACH '" + db2.filename + "' AS db2" );
console.log( "Direct manipulation of SQL.Database objects works: " );
console.log( db3.exec( "SELECT * FROM db1.users NATURAL JOIN db2.cities" ) );

// Prepare to receive open event
sqlWorker.onmessage = function(e){
    console.log("Response from open result: ");
    console.log(e.data);

    // Prepare to receive ATTACH result
    sqlWorker.onmessage = function(e){
        console.log("Response from attach command: ");
        console.log(e.data);

        // Prepare to receive JOIN query
        sqlWorker.onmessage = function(e){
            console.log("Response from JOIN query: ");
            console.log(e.data);

        };  // end join result
        sqlWorker.postMessage({
            id:2,
            action:'exec',
            sql: "SELECT * FROM users NATURAL JOIN meta.cities"
        });

    };  // end attach result
    sqlWorker.postMessage({
        id:1,
        action:'exec',
        sql: "ATTACH '" + db2.filename + "' AS meta"
    });
};
sqlWorker.postMessage({
    id:1,
    action:'open',
    buffer: db1.export().buffer // Would be nice to pass DB directly
});

</script>
</head>
<body>
    <h1>Attach fails when in a worker</h1>
    <p>See console: Uncaught Error: no such table: meta.cities</p>
    <pre><script>document.write(test.innerHTML);</script></pre>
</body>
</html>
hubbly commented 4 years ago

I also need this "attach database" feature，some times I need select some datarows from db1 and insert into db2, this is very usefull

lovasoa commented 4 years ago

A pull request is still welcome ! Look at these lines for how to create a file that will be visible to SQLite:

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

Once you've created the file, you just need to run the attach sql statement.

hubbly commented 4 years ago

A pull request is still welcome ! Look at these lines for how to create a file that will be visible to SQLite:

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

Once you've created the file, you just need to run the attach sql statement.

I do not know what'a your mean. You mean now I can exec "attach database filename as db2" statement?

lovasoa commented 4 years ago

I was speaking in the context of the pull request. You need to do two things to implement Datatabase.attach inside sql.js:

When you are done, add a test that covers the functionality, and open a new pull request. I'll review it and merge it.

hubbly commented 4 years ago

I'll try it, thanks

schickling commented 2 years ago

Also interested in this functionality. Is there any update in regards to adding support for ATTACH in sql.js?

rhashimoto commented 2 years ago

I think ATTACH already works in sql.js, e.g. this works in the sql.js online demo:

ATTACH DATABASE "foo.db" AS foo;
CREATE TABLE IF NOT EXISTS foo.bar (x, y, z);
SELECT * FROM foo.sqlite_master;

This will open the file "foo.db" from the Emscripten file system, creating it if it doesn't exist. If you want to use a pre-existing database file, you'll need to add it to the file system beforehand using the File System API. The tricky part might be getting access to the FS object from the module.

An alternative workaround (aka a hack) to getting FS would be to create and load an additional sql.js Database instance, so something like this (which I haven't actually tried):

// Create the main database file and connection.
const db = new Database(null);

// Create and load a second database file. We won't use the connection
// but don't close it because that will delete the file.
const attached = new Database(data);

// Attach the second database file on the main connection.
db.exec(`ATTACH DATABASE "${attached.filename}" AS foo;`);

// Access the attached database from the main connection.
const result = db.exec('SELECT * FROM foo.whatever;');

Edit: And which was all covered earlier in the thread.

schickling commented 2 years ago

Thanks a lot for this nice write-up with ready-to-use examples. It worked for me! 🙏

kaizhu256 commented 2 years ago

fyi here's a web-demo specifically targeting multiple attached-databases and moving data between them. feel free to peruse the source-code to get it to work and meet your needs.

https://sqlmath.github.io/sqlmath/index.html (web demo) https://github.com/sqlmath/sqlmath

image]