termux-wake-lock
output="/storage/emulated/0/output"
dirs=("/sdcard/msgstore.db" "/sdcard/msgstore2.db")
#order databases by last timestamp
declare -A dirArray
for dir in "${dirs[@]}"; do
dirArray["$dir"]=$(sqlite3 "$dir" "select timestamp from message order by _id desc limit 1;")
done
newdirs=()
while read -r value key; do
newdirs+=("$key")
done < <(for key in "${!dirArray[@]}"; do echo "${dirArray[$key]} $key"
done | sort -n -k1)
if [[ -n "${newdirs[@]}" ]]; then
if [[ ! -d "$output" ]]; then
mkdir "/storage/emulated/0/output"
fi
lastdir=${#newdirs[@]}-1
firstdb=${newdirs[0]}
echo "first (base) database is $firstdb"
lastdb=${newdirs[$lastdir]}
cp "$lastdb" "$output/msgstore.db"
#copy
cp "$lastdb" "$output/msgstorecopy.db"
outputcopy="$output/msgstorecopy.db"
output="$output/msgstore.db"
tables=($(sqlite3 "$lastdb" ".tables"))
for tb in "${tables[@]}"; do
if [[ "$tb" != *"_fts"* && "$tb" != *"_view"* ]]; then
echo "copying $tb table ..."
if [[ "$tb" != "chat" && "$tb" != "jid" ]]; then
sqlite3 "$outputcopy" "delete from $tb;" "delete from sqlite_sequence where name='$tb';"
fi
sqlite3 "$output" "delete from '$tb';" "delete from sqlite_sequence where name='$tb';"
checktb=$(sqlite3 "$firstdb" "SELECT name FROM sqlite_master WHERE type='table' AND name='$tb';")
if [[ -n "$checktb" ]]; then
newcols=$(sqlite3 "$output" "SELECT group_concat('''' || name || '''', ',') FROM pragma_table_info('$tb');")
cols=$(sqlite3 "$firstdb" "select group_concat(name) from pragma_table_info('$tb') where name in ($newcols);")
sqlite3 "$output" "attach '$firstdb' as db1" "insert into $tb ($cols) select $cols from db1.$tb;"
fi
fi
done
newdirs+=("$outputcopy")
for ((k=1; k<${#newdirs[@]}; k++)); do
if [[ $k == $((${#newdirs[@]} - 1)) ]]; then
dir="$outputcopy"
dir1="$output"
echo "sorting messages according to timestamp ..."
else
dir="$output"
dir1="${newdirs[k]}"
echo "joining database $(($k+1)) '$dir1'"
fi
lastmsg=$(sqlite3 "$dir" "select seq from sqlite_sequence where name='message';")
if [[ -z "$lastmsg" ]]; then
lastmsg=0
fi
lastgpid=$(sqlite3 "$dir" "select seq from sqlite_sequence where name='group_participant_user';")
#check common tables
tbls1=($(sqlite3 "$dir1" ".tables"))
tbls2=($(sqlite3 "$dir" ".tables"))
dbs=()
for tbl2 in "${tbls2[@]}"; do
for tbl1 in "${tbls1[@]}"; do
if [[ "$tbl2" == "$tbl1" && "$tbl2" != *"view"* && "$tbl2" != "jid" && "$tbl2" != "chat" && "$tbl2" != *"_map7"* && "$tbl2" != "call_log" && "$tbl2" != "message_add_on" && "$tbl2" != *"_fts"* && "$tbl2" != "message" && "$tbl2" != "message_vcard" && "$tbl2" != "quick_replies" && "$tbl2" != "group_participant_user " ]]; then
dbs+=("$tbl2")
fi
done
done
#jid
echo "joining jid table ..."
colsjid1=$(sqlite3 "$dir" "select group_concat('''' || name || '''', ',') from pragma_table_info('jid') where cid <>0;")
colsjid=$(sqlite3 "$dir1" "select group_concat(name) from pragma_table_info ('jid') where cid not in (0) and name in ($colsjid1);")
sqlite3 "$dir" "attach '$dir1' as db1" "insert or ignore into jid ($colsjid) select $colsjid from db1.jid;" "create table if not exists jid_map7 (old_id integer null, new_id integer null);" "delete from jid_map7;" "insert into jid_map7 (old_id,new_id) values (-1,-1),(0,0);" "insert into jid_map7 (old_id,new_id) values ('',0);" "insert into jid_map7 (old_id,new_id) select dj._id,j._id from db1.jid dj join jid j on dj.raw_string=j.raw_string;"
#chat
echo "joining chat table ..."
colschat1=$(sqlite3 "$dir" "select group_concat('''' || name || '''', ',') from pragma_table_info('chat');")
colschat=$(sqlite3 "$dir1" "select group_concat(name) from pragma_table_info('chat') where name in ($colschat1) and cid not in (0,1);")
ccolschat=$(sqlite3 "$dir1" "select group_concat('dc.' || name) from pragma_table_info('chat') where name in ($colschat1) and cid not in (0,1);")
sqlite3 "$dir" "attach '$dir1' as db1;" "insert or ignore into chat (jid_row_id,$colschat) select jm7.new_id,$ccolschat from db1.chat dc join jid_map7 jm7 on dc.jid_row_id=jm7.old_id;" "create table if not exists chat_map7 (old_id integer null, new_id integer null);" "delete from chat_map7;" "insert into chat_map7 (old_id,new_id) select dc._id,c._id from db1.chat dc join db1.jid dj on dj._id=dc.jid_row_id join jid j on dj.raw_string=j.raw_string join chat c on j._id=c.jid_row_id;"
if [[ $k == 1 ]]; then
lastgpid=0
else
lastgpid=$(sqlite3 "$dir" "select seq from sqlite_sequence where name='group_participant_user';")
if [[ -z "$lastgpid" ]]; then
lastgpid=0
fi
fi
#maintables
mtbls=(message message_add_on call_log quick_replies message_vcard group_participant_user)
uniques=(key_id key_id call_id title vcard empty)
short=(mm7 ad7 cl7 qr7 vc7 gp7)
for ((j=0; j<${#mtbls[@]}; j++)); do
echo "joining ${mtbls[j]} table ..."
mstr=()
cstr=()
jstr=()
order=""
xtr=""
if [[ "${mtbls[j]}" == "message" ]]; then
order=" order by m.timestamp"
fi
if [[ "${mtbls[j]}" == "group_participant_user" ]]; then
xtr=" JOIN db1.jid j1_group ON m1.group_jid_row_id = j1_group._id JOIN db1.jid j1_user ON m1.user_jid_row_id = j1_user._id JOIN jid j_group ON j1_group.raw_string = j_group.raw_string JOIN jid j_user ON j1_user.raw_string = j_user.raw_string JOIN group_participant_user m ON m.group_jid_row_id = j_group._id AND m.user_jid_row_id = j_user._id WHERE m._id > $lastgpid"
elif [[ "${mtbls[j]}" == "chat" ]]; then
xtr=" join db1.jid dj on dj._id=m1.jid_row_id join jid j on dj.raw_string=j.raw_string join chat m on j._id=m.jid_row_id;"
else
xtr=" join ${mtbls[j]} m on m.${uniques[j]}=m1.${uniques[j]}"
fi
cols1=$(sqlite3 "$output" "select group_concat('''' || name || '''', ',') from pragma_table_info('${mtbls[j]}');")
cols2=($(sqlite3 "$dir1" "select name from pragma_table_info('${mtbls[j]}') where cid<>0 and name in ($cols1);"))
ccols2=($(sqlite3 "$dir1" "select group_concat('dm.' || name) from pragma_table_info('${mtbls[j]}') where cid<>0 and name in ($cols1);"))
cols=("${cols2[@]}")
for ((i=0; i<${#cols2[@]}; i++)); do
if [[ "${cols2[i]}" == *"message_row_id"* || "${cols2[i]}" == *"message_table_id"* ]]; then
mstr+=( "join message_map7 mm7$i on mm7$i.old_id=m.${cols2[i]}")
cols2[i]="mm7$i.new_id"
elif [[ "${cols2[i]}" == *"jid_row_id"* || "${cols2[i]}" == *"lid_row_id"* ]]; then
jstr+=("join jid_map7 jm7$i on jm7$i.old_id=m.${cols2[i]}")
cols2[i]="jm7$i.new_id"
elif [[ "${cols2[i]}" == *"chat_row_id"* ]]; then
cstr+=("join chat_map7 cm7$i on cm7$i.old_id=m.${cols2[i]}")
cols2[i]="cm7$i.new_id"
else
cols2[i]="m.${cols2[i]}"
fi
done
colst=$(IFS=","; echo "${cols[*]}")
selstr=$(IFS=","; echo "${cols2[*]}")
sqlite3 "$dir" "attach '$dir1' as db1;" "insert or ignore into "${mtbls[j]}" ($colst) select $selstr from db1.${mtbls[j]} m ${mstr[*]} ${cstr[*]} ${jstr[*]}$order;" "create table if not exists ${mtbls[j]}_map7 (new_id integer null,old_id integer null);" "delete from ${mtbls[j]}_map7;" "insert into ${mtbls[j]}_map7 (new_id,old_id) values (-1,-1);" "insert into ${mtbls[j]}_map7 (new_id,old_id) select m._id,m1._id from db1.${mtbls[j]} m1$xtr;"
done
#joined tables
for db in "${dbs[@]}"; do
echo "joining $db table ..."
newcols=$(sqlite3 "$dir" "SELECT group_concat('''' || name || '''', ',') FROM pragma_table_info('$db');")
cols=($(sqlite3 "$dir1" "select name from pragma_table_info('$db') where name in ($newcols);"))
colstr=("${cols[@]}")
firstcol=$(sqlite3 "$dir" "SELECT name FROM pragma_table_info('$db') where cid=0;")
if [[ "$firstcol" == "_id" ]]; then
unset cols["_id"]
unset colstr["_id"]
cols=("${cols[@]}")
colstr=("${colstr[@]}")
fi
mstr=()
cstr=()
jstr=()
adstr=()
clstr=()
vcstr=()
qrstr=()
gpstr=()
for ((i=0; i<${#colstr[@]}; i++)); do
if [[ "${colstr[i]}" == *"message_row_id"* || "${colstr[i]}" == *"message_table_id"* ]]; then
mstr+=("join message_map7 mm7$i on mm7$i.old_id=m.${colstr[i]}")
colstr[i]="mm7$i.new_id"
elif [[ "${colstr[i]}" == *"jid_row_id"* || "${colstr[i]}" == *"lid_row_id"* ]]; then
jstr+=("join jid_map7 jm7$i on jm7$i.old_id=m.${colstr[i]}")
colstr[i]="jm7$i.new_id"
elif [[ "${colstr[i]}" == *"chat_row_id"* ]]; then
cstr+=("join chat_map7 cm7$i on cm7$i.old_id=m.${colstr[i]}")
colstr[i]="cm7$i.new_id"
elif [[ "${colstr[i]}" == *"call_log_row_id"* ]]; then
clstr+=("join call_log_map7 cl7$i on cl7$i.old_id=m.${colstr[i]}")
colstr[i]="cl7$i.new_id"
elif [[ "${colstr[i]}" == *"message_add_on_row_id"* ]]; then
adstr+=("join message_add_on_map7 ad7$i on ad7$i.old_id=m.${colstr[i]}")
colstr[i]="ad7$i.new_id"
elif [[ "${colstr[i]}" == *"vcard_row_id"* ]]; then
vcstr+=("join message_vcard_map7 vc7$i on vc7$i.old_id=m.${colstr[i]}")
colstr[i]="vc7$i.new_id"
elif [[ "${colstr[i]}" == *"quick_reply_id"* ]]; then
qrstr+=("join quick_replies_map7 qr7$i on qr7$i.old_id=m.${colstr[i]}")
colstr[i]="qr7$i.new_id"
elif [[ "${colstr[i]}" == *"group_participant_row_id"* ]]; then
gpstr+=("join group_participant_user_map7 gp7$i on gp7$i.old_id=m.${colstr[i]}")
colstr[i]="gp7$i.new_id"
else
colstr[i]="m.${colstr[i]}"
fi
done
colst=$(IFS=","; echo "${cols[*]}")
selstr=$(IFS=","; echo "${colstr[*]}")
echo "joining table $db"
sqlite3 "$dir" "attach '$dir1' as db1;" "insert or ignore into "$db" ($colst) select $selstr from db1.$db m ${mstr[*]} ${cstr[*]} ${jstr[*]} ${clstr[*]} ${adstr[*]} ${vcstr[*]} ${qrstr[*]} ${gpstr[*]};"
done
echo "updating chats ..."
updatechatstr=$(IFS="," read -ra arr <<< "$colschat"
str=()
for a in ${arr[@]}; do
if [[ "$a" != *"jid_row_id"* ]]; then
if [[ "$a" == *"message_row_id"* || "$a" == *"message_sort_id"* || "$a" == *"reaction_row_id"* ]]; then
str+=("$a = (SELECT _id FROM message WHERE chat_row_id = chat._id and message_type<>7 ORDER BY _id DESC LIMIT 1 )")
elif [[ "$a" == *"timestamp"* ]]; then
str+=("$a = (SELECT timestamp FROM message WHERE chat_row_id = chat._id and message_type<>7 ORDER BY _id DESC LIMIT 1 )")
fi
fi
done
IFS=","; echo "${str[*]}")
sqlite3 "$dir" "attach '$dir1' as db1; " "update chat set $updatechatstr"
done
#remove duplicates
echo "removing duplicates ..."
sqlite3 "$dir" "delete from message_vcard_jid where vcard_row_id=-1 and message_row_id in (select message_row_id from message_vcard_jid where vcard_row_id<>-1);" "DELETE From message_vcard_jid WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM message_vcard_jid GROUP BY message_row_id);" "DELETE from android_metadata WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM android_metadata GROUP BY locale);" "DELETE From frequent WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM frequent GROUP BY jid_row_id);" "DELETE From frequents WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM frequents GROUP BY jid);" "UPDATE frequent SET message_count = (SELECT fs.message_count FROM frequent AS f JOIN jid AS j ON f.jid_row_id = j._id JOIN frequents AS fs ON fs.jid = j.raw_string WHERE frequent.jid_row_id = f.jid_row_id);"
#update hidden in messages
echo "fixing chats ..."
sqlite3 "$dir" "update chat set hidden=1 where _id not in (select distinct chat_row_id from message where message_type<>7);" "update chat set hidden=0 where _id in (select distinct chat_row_id from message where message_type<>7);"
#with the numerous updates this table balloons the database size
sqlite3 "$dir" "delete from backup_changes;"
#remove messages where chats are hidden. to reduce size of merged database
echo "trimming empty messages ..."
sqlite3 "$dir" "delete from message where chat_row_id in (select _id from chat where hidden <> 0);"
#sort according to _id
echo "sorting messages ..."
sqlite3 $dir "update message set sort_id = _id;"
echo "deleting temp tables ..."
dbs=($(sqlite3 "$dir" ".tables"))
for db in "${dbs[@]}"; do
if [[ "$db" == *"_map7"* ]]; then
sqlite3 "$dir" "drop table $db;"
fi
done
rm "$output"
mv "$outputcopy" "$output"
echo "done. Output is in $output"
else
echo "no valid databases found"
fi
termux-wake-unlock