How to merge two highlight/user formatting sqlite files
Posted: Tue Nov 01, 2016 11:57 am
Here is a list of SQL commands you may use to merge 2 sqlite files that contain Bible highglight information (e.g. my.bhls.twm). These are SQLite files and you may use any sqlite editor.
Before attempting to do so, make sure you keep copies.
To run the command, open the first file in your sqlite editor and move the second in the same folder. The second file is assumed to be named 'my2.bhls.twm': change the name to the proper one in the first line in the code above.
Costas
PS: Nov 1, 2016: the code has not been tested really, so please test and let me know if it works, especially when merging files where you have highlighted 2-3 different Bibles but you did so in 2 different installations in different order (check the highglight_master files for cases where the volumes has different volumeid-s in the two .twm files)
PS2: if you get some errors about transactions, just run the commands one at a time... some programs start transactions by themselves...
PS3: always make copies of your files: if something goes wrong, just start over.
Before attempting to do so, make sure you keep copies.
Code: Select all
attach database 'my2.bhls.twm' as db2;
create table highlight2 as select * from db2.highlight;
create table highlight_master2 as select * from db2.highlight_master;
detach database db2;
create table tmp as select max(volumeid) maxvolid from highlight_master;
update highlight_master2 set volumeid = volumeid + (select maxvolid from tmp);
update highlight2 set volumeid = volumeid + (select maxvolid from tmp);
insert into highlight(volumeid, typ, vi, pos1, pos2, value)
select ifnull(hm.volumeid, hm2.volumeid) volumeid, h2.typ, h2.vi, h2.pos1, h2.pos2, h2.value
from highlight2 h2
inner join highlight_master2 hm2 on h2.volumeid = hm2.volumeid
left outer join highlight_master hm on hm2.descr = hm.descr;
insert into highlight_master
select hm2.volumeid, hm2.descr from highlight_master2 hm2
where hm2.descr not in (select descr from highlight_master);
drop table tmp;
drop table highlight2;
drop table highlight_master2;
vacuum;
Costas
PS: Nov 1, 2016: the code has not been tested really, so please test and let me know if it works, especially when merging files where you have highlighted 2-3 different Bibles but you did so in 2 different installations in different order (check the highglight_master files for cases where the volumes has different volumeid-s in the two .twm files)
PS2: if you get some errors about transactions, just run the commands one at a time... some programs start transactions by themselves...
PS3: always make copies of your files: if something goes wrong, just start over.