mercredi 3 décembre 2014

Insert values from one database table to other database table through shell scripting


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