How to merge two highlight/user formatting sqlite files

Share your favorite tips, workarounds and shortcuts for theWord
csterg
Site Admin
Posts: 8627
Joined: Tue Aug 29, 2006 3:09 pm
Location: Corfu, Greece
Contact:

How to merge two highlight/user formatting sqlite files

Post by csterg »

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.

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;
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.
JEM
Posts: 83
Joined: Tue Dec 16, 2008 6:58 am
Location: TN, In

Re: How to merge two highlight/user formatting sqlite files

Post by JEM »

Hello Costas,

I normally open desktop.bhls.twm and attach my laptop.bhls.twm and execute this code and it goes smoothly. But,
Every time when i execute this code, the db size becomes double .i.e the laptop.bhls.twm simply getting added to desktop.bhls.twm. My question is, will this code remove/check for duplicate OR will skip identical entries from attaching db?

A code to check for duplicate entries from the updated db and removing them is very much appreciated to have the control over its size.

Thanks.
In Christ with His love,

JEM

And now abideth faith, hope, love, these three; but the greatest of these [is] love. (I Cor 13:13 [AMP])
For our conversation is in heaven; from whence also we look for the Saviour, the Lord Jesus Christ: (Phil 3:20 [KJV])
csterg
Site Admin
Posts: 8627
Joined: Tue Aug 29, 2006 3:09 pm
Location: Corfu, Greece
Contact:

Re: How to merge two highlight/user formatting sqlite files

Post by csterg »

Hello JEM,
no, the above sql commands will not check for duplicates. They will just merge blindly the highlights from both files.
Run this sql command to see if you have duplicates:

Code: Select all

select volumeid, typ, vi, pos1, pos2, value, count(*)
from highlight
group by volumeid, typ, vi, pos1, pos2, value
having count(*) > 1
JEM
Posts: 83
Joined: Tue Dec 16, 2008 6:58 am
Location: TN, In

Re: How to merge two highlight/user formatting sqlite files

Post by JEM »

Costas,

I had run the query for finding duplicates. Please find attached snapshot for the result and could you please take me to steps further i.e. the commands to remove duplicates completely?

Thank you
Finding duplicates in bhls.twm
Finding duplicates in bhls.twm
bhls-find duplicates.jpg (184.38 KiB) Viewed 5226 times
In Christ with His love,

JEM

And now abideth faith, hope, love, these three; but the greatest of these [is] love. (I Cor 13:13 [AMP])
For our conversation is in heaven; from whence also we look for the Saviour, the Lord Jesus Christ: (Phil 3:20 [KJV])
csterg
Site Admin
Posts: 8627
Joined: Tue Aug 29, 2006 3:09 pm
Location: Corfu, Greece
Contact:

Re: How to merge two highlight/user formatting sqlite files

Post by csterg »

Please, post your bhls.twm file and I will fix it for you. I will then post the SQL commands I used to do this.
JEM
Posts: 83
Joined: Tue Dec 16, 2008 6:58 am
Location: TN, In

Re: How to merge two highlight/user formatting sqlite files

Post by JEM »

Thank you Costas. I thought of doing it yesterday itself, but was thinking about your time avilability and stopped posting it.

Here it is.

Sorry, the first attachment (MY.BHLS.TWM) is with minimal merge. The second attachment (mymerged.bhls.twm) is the full merging of two bhls.twm files from desktop and laptop.
Attachments
mymerged.bhls.twm.zip
(1.73 MiB) Downloaded 442 times
my.bhls.twm.zip
MY.BHLS.TWM
(943.25 KiB) Downloaded 464 times
In Christ with His love,

JEM

And now abideth faith, hope, love, these three; but the greatest of these [is] love. (I Cor 13:13 [AMP])
For our conversation is in heaven; from whence also we look for the Saviour, the Lord Jesus Christ: (Phil 3:20 [KJV])
csterg
Site Admin
Posts: 8627
Joined: Tue Aug 29, 2006 3:09 pm
Location: Corfu, Greece
Contact:

Re: How to merge two highlight/user formatting sqlite files

Post by csterg »

Here are the commands:

Code: Select all

create table highlight2 as select * from highlight limit 0;
insert into highlight2 (volumeid, typ, vi, pos1, pos2, value)
  select distinct volumeid, typ, vi, pos1, pos2, value from highlight;
delete from highlight;
insert into highlight(volumeid, typ, vi, pos1, pos2, value)
  select volumeid, typ, vi, pos1, pos2, value from highlight2;
drop table highlight2;
vacuum;
Attached the merged highlights of yours. Test it or just run the above commands yourself.
Costas
Attachments
mymerged.bhls.twm.zip
(160.88 KiB) Downloaded 464 times
JEM
Posts: 83
Joined: Tue Dec 16, 2008 6:58 am
Location: TN, In

Re: How to merge two highlight/user formatting sqlite files

Post by JEM »

Thank you very much Costas. I ll get back to you after seeing it.
In Christ with His love,

JEM

And now abideth faith, hope, love, these three; but the greatest of these [is] love. (I Cor 13:13 [AMP])
For our conversation is in heaven; from whence also we look for the Saviour, the Lord Jesus Christ: (Phil 3:20 [KJV])
JEM
Posts: 83
Joined: Tue Dec 16, 2008 6:58 am
Location: TN, In

Re: How to merge two highlight/user formatting sqlite files

Post by JEM »

Thank you Costas for this comprehensive fix and the codes. The bhls.twm size has now come to its normal size (almost from 5mb to 530kb). Works perfect.

Thank you again for your time.
In Christ with His love,

JEM

And now abideth faith, hope, love, these three; but the greatest of these [is] love. (I Cor 13:13 [AMP])
For our conversation is in heaven; from whence also we look for the Saviour, the Lord Jesus Christ: (Phil 3:20 [KJV])
Post Reply