A way to attach multiple databases? #88
Comments
|
Yes, it would be possible, and even not very difficult. We would have to create a new |
|
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
|
|
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. |
|
Ok. I'm trying to get the build environment up and running now. Thx. Rob Sent from my iPhone
|
|
Hmm. I'm having trouble getting a Windows build environment working (I'm I put Node.js and Emscripten in c:\ (after space-in-path errors in Microsoft Windows [Version 6.1.7601] C:\Users\Robert.Harder\Documents\GitHub\sql.js>emmake make C:\Users\Robert.Harder\Documents\GitHub\sql.js>Running under Cygwin gives me different errors, but it seems more related Robert.Harder@dfcs-harder05 Generate llvm bitcode/cygdrive/c/Emscripten/emscripten/1.29.0/emcc -DSQLITE_OMIT_LOAD_EXTENSION module.js:340 Robert.Harder@dfcs-harder05 /cygdrive/c/users/Robert.Harder/Documents/GitHub/sql.jsI know the way this normally works: there's something simple and obvious to Thanks. -Rob On Fri, Feb 6, 2015 at 7:00 AM, Robert Harder [email protected]
|
|
Did you install emscripten with the emscripten sdk for windows? Le 6 février 2015 16:36:38 UTC+01:00, Robert Harder [email protected] a écrit :
|
|
Yes. I first put it in the default c:\program files and then My %PATH% includes -Rob On Fri, Feb 6, 2015 at 9:58 AM, Ophir LOJKINE [email protected]
|
|
If it's a fresh install through the emscripten SDK, then it's a bug in emscripten, and you should report it. |
|
A 'filename' property of db instance can use to attach a database. |
|
Yes, but that forces you to create a second database object. What we are talking about here is to create a However, I hadn't think about that possibility, which is more simple. |
|
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
|
|
What @tkns is saying is that you can do |
|
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
|
|
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 |
|
Well that was easy. ;-) Rob Sent from my iPhone
|
|
Well then here's a test_attach.js file, though it doesn't verify that DBs loaded from different sources work. -Rob |
|
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. |
|
I also need this "attach database" feature，some times I need select some datarows from db1 and insert into db2, this is very usefull |
|
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 |
I do not know what'a your mean. |
|
I was speaking in the context of the pull request. You need to do two things to implement
When you are done, add a test that covers the functionality, and open a new pull request. I'll review it and merge it. |
|
I'll try it, thanks |
|
Also interested in this functionality. Is there any update in regards to adding support for |
|
I think 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 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. |
|
Thanks a lot for this nice write-up with ready-to-use examples. It worked for me! 🙏 |
|
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)
|
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
The text was updated successfully, but these errors were encountered: