I am working with 2 databases say logdb.db and logdb2.db .
I have created a table named Original in logdb.db Sqlite database such as follows :
CREATE TABLE Original (s_id INT PRIMARY KEY NOT NULL UNIQUE, s_author TEXT, s_timestamp INT, s_editedby TEXT, s_edited_timestamp INT, s_body_xml TEXT);
Similarly, I have created a table named Edited and NonEdited in logdb2.db Sqlite database such as follows : CREATE TABLE Edited (s_id INT PRIMARY KEY NOT NULL UNIQUE, s_author TEXT, s_timestamp INT, s_editedby TEXT, s_edited_timestamp INT, s_body_xml TEXT);
CREATE TABLE NonEdited (s_id INT PRIMARY KEY NOT NULL UNIQUE, s_author TEXT, s_timestamp INT, s_editedby TEXT, s_edited_timestamp INT, s_body_xml TEXT);
Then I attached logdb2.db to logdb.db as follows from command line:
$ sqlite3 logdb.db
$ ATTACH DATABASE '/home/sid/Desktop/Logging/logdb2.db' as DB2;
Now, the table Original gets inserted or updated by some other user, so depending on what is being inserted, I want to insert those values into either Edited or NonEdited table. Basically I am targeting the last few rows of the Original table. So I created a shell script file called test.sh and The contents are as follows:
sqlite3 /home/sid/Desktop/Logging/logdb.db 'insert or replace into DB2.Edited select * from ( select * from Original order by s_id desc limit 10) where s_editedby is NOT NULL order by s_id;'
sqlite3 /home/sid/Desktop/Logging/logdb.db 'insert or ignore into DB2.NonEdited select * from ( select * from Original order by s_id desc limit 10) where s_editedby is NULL order by s_id;'
Then I am running the test.sh as follows :
watch -n 1 ./test.sh
But I am getting the following error when I run the above command saying,
Error: no such table: DB2.Edited
Error: no such table: DB2.NonEdited
Where and what am I doing wrong?
Please note, when all three tables are in same database, I wont get this problem and it works perfectly. I checked the attached database in logdb.db , and it is correct:
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/sid/Desktop/Logging/logdb.db
2 DB2 /home/sid/Desktop/Logging/logdb2.db
sqlite> .tables
DB2.Edited DB2.NonEdited Original
What could have gone wrong? Any help would be much appreciated.
PLEASE NOTE : This is the only way I can do, I cant create triggers. ( I am trying to mock some experiment set up).
Aucun commentaire:
Enregistrer un commentaire