Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

A way to attach multiple databases? #88

Open
rharder opened this issue Feb 5, 2015 · 17 comments
Open

A way to attach multiple databases? #88

rharder opened this issue Feb 5, 2015 · 17 comments
Labels

Comments

@rharder
Copy link

@rharder rharder commented Feb 5, 2015

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 lovasoa added the enhancement label Feb 5, 2015
@lovasoa

This comment has been minimized.

Copy link
Collaborator

@lovasoa lovasoa commented Feb 5, 2015

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

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 6, 2015

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 #88 (comment).

@lovasoa

This comment has been minimized.

Copy link
Collaborator

@lovasoa lovasoa commented Feb 6, 2015

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

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 6, 2015

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

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 6, 2015

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
#88 (comment).

@lovasoa

This comment has been minimized.

Copy link
Collaborator

@lovasoa lovasoa commented Feb 6, 2015

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
#88 (comment).


Reply to this email directly or view it on GitHub:
#88 (comment)

@rharder

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 6, 2015

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
#88 (comment).


Reply to this email directly or view it on GitHub:
#88 (comment)


Reply to this email directly or view it on GitHub
#88 (comment).

@lovasoa

This comment has been minimized.

Copy link
Collaborator

@lovasoa lovasoa commented Feb 6, 2015

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

@tkns

This comment has been minimized.

Copy link

@tkns tkns commented Feb 7, 2015

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

@lovasoa

This comment has been minimized.

Copy link
Collaborator

@lovasoa lovasoa commented Feb 7, 2015

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

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 7, 2015

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

This comment has been minimized.

Copy link
Collaborator

@lovasoa lovasoa commented Feb 7, 2015

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

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 7, 2015

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

This comment has been minimized.

Copy link
Collaborator

@lovasoa lovasoa commented Feb 7, 2015

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.

@rharder

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 7, 2015

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

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 9, 2015

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

This comment has been minimized.

Copy link
Author

@rharder rharder commented Feb 9, 2015

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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.