A way to attach multiple databases?

[rharder]

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]

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]

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]

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]

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]

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]

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]

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]

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

[tkns]

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

[lovasoa]

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]

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]

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]

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]

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.