You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I want to query about a behavior of ATTACHed db that I've just encountered and find a bit strange.
import Database from 'better-sqlite3'; // v 8.4.0
const db1 = new Database('./db1.sqlite');
const db2 = new Database('./db2.sqlite');
db1.pragma('foreign_keys = ON');
db2.pragma('foreign_keys = ON');
db2.prepare(ATTACH DATABASE './db1.sqlite' AS db1).run();
create tables in db1 // pseudo code here on for brevity
create tables in db2
create a TEMPORARY TRIGGER AFTER INSERT ON TABLE in db2 that references a table in attached db1
insert data in db1
insert data in db2
// 11 fails with the error
SqliteError: no such table: db1.\<table in db1\>
Basically, the TEMPORARY TRIGGER fails to fire because it can't find the ATTACHEd db1.
However, if I run any query before creating the TEMPORARY TRIGGER, it all works. So, imagine a step between #8 and 9 above like so
8a. select * from db1.<table from db1>
Even though there is no data in any table in db1 yet, now, everything works. It is almost as if a query was needed to actually make an ATTACHed db really get attached? I've asked this question on the SQLite forum as well, but could this be a quirk of better-sqlite3?
The text was updated successfully, but these errors were encountered:
I have confirmed that the above behavior happens only with better-sqlite3. If I run plain SQL, everything just works, and I don't need to run a useless query to kickstart the ATTACHed database. Unless this behavior is by design, it seems like a bug.
Here is a gist with the script that shows the bug in action. Run the script as is and it will fail. Uncomment lines 165-166 (see line 160 for notes) and the script will succeed.
Do you have some example code prepared that will run all of this within a native sqlite3 context (executable)? Have you tried narrowing down the issue by reducing the implementation to a critical minimum that is able to reproduce this issue?
If I run all the commands within your example gist the following code works without throwing an error:
constDatabase=require('better-sqlite3');constmat=newDatabase('/tmp/mat.sqlite');mat.exec(`attach database '/tmp/geo.sqlite' as geo;`);mat.exec(`INSERT INTO materialCitations (materialCitation, treatments_id, longitude, latitude) VALUES ('one materialCitation', '1', -5, 10);`);
I want to query about a behavior of ATTACHed db that I've just encountered and find a bit strange.
ATTACH DATABASE './db1.sqlite' AS db1
).run();// 11 fails with the error
SqliteError: no such table: db1.\<table in db1\>
Basically, the TEMPORARY TRIGGER fails to fire because it can't find the ATTACHEd db1.
However, if I run any query before creating the TEMPORARY TRIGGER, it all works. So, imagine a step between #8 and 9 above like so
8a. select * from db1.<table from db1>
Even though there is no data in any table in db1 yet, now, everything works. It is almost as if a query was needed to actually make an ATTACHed db really get attached? I've asked this question on the SQLite forum as well, but could this be a quirk of
better-sqlite3
?The text was updated successfully, but these errors were encountered: