· 7 years ago · Jan 07, 2019, 07:36 PM
1#!/bin/sh
2
3# Tools:
4# sqlite3
5# sh, rm, grep, sed
6# Links:
7# https://superuser.com/questions/111998/how-do-i-repair-a-corrupted-firefox-places-sqlite-database
8# https://developer.mozilla.org/en-US/docs/Mozilla/Tech/Places/Database
9# http://www.forensicswiki.org/wiki/Mozilla_Firefox_3_History_File_Format
10
11OLD=profile1/places.sqlite
12NEW=profile2/places.sqlite
13# https://sqlite.org/download.html e.g. https://sqlite.org/2017/sqlite-tools-linux-x86-3210000.zip
14SQL=./sqlite3
15
16set -e # exit on error
17set -x # print commands
18
19#$SQL "$OLD" "PRAGMA integrity_check;"
20#$SQL "$NEW" "PRAGMA integrity_check;"
21
22rm -f result.sqlite result.sqlite-wal result.sqlite-shm
23
24# recover OLD database:
25$SQL "$OLD" .dump | grep -v ^DELETE | sed 's/^ROLLBACK/COMMIT/' | $SQL result.sqlite
26$SQL result.sqlite "PRAGMA page_size=4096;PRAGMA user_version=$($SQL "$OLD" "PRAGMA user_version;");"
27
28# append NEW database to it, renaming moz_* tables to mozadd_* tables:
29$SQL "$NEW" .dump | grep -v ^DELETE | sed 's/moz_anno_attributes/mozadd_anno_attributes/g;s/moz_annos/mozadd_annos/g;s/moz_bookmarks/mozadd_bookmarks/g;s/moz_favicons/mozadd_favicons/g;s/moz_historyvisits/mozadd_historyvisits/g;s/moz_hosts/mozadd_hosts/g;s/moz_inputhistory/mozadd_inputhistory/g;s/moz_items_annos/mozadd_items_annos/g;s/moz_keywords/mozadd_keywords/g;s/moz_places/mozadd_places/g;s/^ROLLBACK/COMMIT/' | $SQL result.sqlite
30#$SQL "$NEW" .dump | grep -v ^DELETE | sed ':a s/^\([^'\'']*\)moz_/\1mozadd_/;ta;:b s/^\(INSERT INTO[ "]*sqlite_.*\)moz_/\1mozadd_/;tb;s/^ROLLBACK/COMMIT/' | $SQL result.sqlite
31
32# helper funcs
33sql_map_duplicates(){
34 TSFX="$1"
35 COND="A.$2 IS B.$2"
36 shift
37 shift
38 for i in "$@"; do
39 COND="$COND AND A.$i IS B.$i"
40 done
41 FROM="moz_$TSFX A, mozadd_$TSFX B"
42 if [ -n "$CHECKPARENT" ]; then
43 SRCF="${CHECKPARENT%:*}"
44 DSTF="${CHECKPARENT#*:}"
45 FROM="$FROM LEFT JOIN moz_$TSFX PA ON PA.id=A.$SRCF LEFT JOIN mozadd_$TSFX PB ON PB.id=B.$SRCF"
46 COND="$COND AND PA.$DSTF IS PB.$DSTF"
47 fi
48 if [ -n "$MATCHANY" ]; then
49 $SQL result.sqlite "CREATE TABLE IF NOT EXISTS idmap(fromid INTEGER PRIMARY KEY, toid INTEGER);"
50 $SQL result.sqlite "CREATE INDEX idmap_toid_index ON idmap(toid);"
51 $SQL result.sqlite "INSERT INTO idmap SELECT B.id, MIN(A.id) FROM $FROM LEFT JOIN idmap ON toid=A.id WHERE $COND AND idmap.toid IS NULL GROUP BY B.id;"
52 else
53 $SQL result.sqlite "CREATE TABLE IF NOT EXISTS idmap(fromid INTEGER PRIMARY KEY, toid INTEGER UNIQUE);"
54 $SQL result.sqlite "INSERT INTO idmap SELECT B.id, A.id FROM $FROM LEFT JOIN idmap ON toid=A.id WHERE $COND AND idmap.toid IS NULL;"
55 fi
56}
57sql_delete_duplicates(){
58 TSFX="$1"
59 COND="A.$2 IS mozadd_$TSFX.$2"
60 shift
61 shift
62 for i in "$@"; do
63 COND="$COND AND A.$i IS mozadd_$TSFX.$i"
64 done
65 FROM="moz_$TSFX A"
66 if [ -n "$CHECKPARENT" ]; then
67 SRCF="${CHECKPARENT%:*}"
68 DSTF="${CHECKPARENT#*:}"
69 FROM="$FROM LEFT JOIN moz_$TSFX PA ON PA.id=A.$SRCF LEFT JOIN mozadd_$TSFX PB ON PB.id=mozadd_$TSFX.$SRCF"
70 COND="$COND AND PA.$DSTF IS PB.$DSTF"
71 fi
72 $SQL result.sqlite "DELETE FROM mozadd_$TSFX WHERE EXISTS (SELECT 1 FROM $FROM WHERE $COND);"
73}
74sql_shift_id(){
75 # make mozadd_$1.id > moz_$1.id
76 MAXOLD=$($SQL result.sqlite "SELECT MAX(id) FROM moz_$1;")
77 MINNEW=$($SQL result.sqlite "SELECT MIN(id) FROM mozadd_$1;")
78 MAXNEW=$($SQL result.sqlite "SELECT MAX(id) FROM mozadd_$1;")
79 SHIFT=$((MAXOLD-MINNEW+1))
80 WIDTH=$((MAXNEW-MINNEW+1))
81 $SQL result.sqlite "INSERT INTO idmap SELECT id,id+$SHIFT FROM mozadd_$1;"
82 if [ $SHIFT -ge $WIDTH ]; then
83 $SQL result.sqlite "UPDATE mozadd_$1 SET id=id+$SHIFT;"
84 else
85 $SQL result.sqlite "UPDATE mozadd_$1 SET id=id+$WIDTH;"
86 $SQL result.sqlite "UPDATE mozadd_$1 SET id=id-$WIDTH+$SHIFT;"
87 fi
88}
89sql_deduplicate(){
90 sql_map_duplicates "$@"
91 sql_delete_duplicates "$@"
92 sql_shift_id "$1"
93}
94sql_update_ids(){
95 MAXTOID=$($SQL result.sqlite "SELECT MAX(toid) FROM idmap;")
96 [ -n "$MAXTOID" ] && for TF in "$@"; do
97 TSFX=${TF%%.*}
98 TSFX=${TSFX#moz_}
99 TSFX=${TSFX#mozadd_}
100 F=${TF#*.}
101 # to avoid conflicts increase all the values above MAX
102 MAXID=$($SQL result.sqlite "SELECT MAX($MAXTOID,MAX($F)) FROM mozadd_$TSFX;")
103 if [ -n "$MAXID" ]; then
104 $SQL result.sqlite "UPDATE mozadd_$TSFX SET $F=$F+$MAXID WHERE EXISTS(SELECT * FROM idmap WHERE fromid=mozadd_$TSFX.$F);"
105 $SQL result.sqlite "UPDATE mozadd_$TSFX SET $F=IFNULL( (SELECT toid FROM idmap WHERE fromid=mozadd_$TSFX.$F-$MAXID), $F);"
106 fi
107 done
108 $SQL result.sqlite "DROP TABLE idmap"
109}
110
111$SQL result.sqlite "CREATE INDEX moz_places_url_mytmpindex ON moz_places (url);"
112
113# a hack: remove duplicates from mozadd_historyvisits if they get there somehow
114for PFX in mozadd; do
115 while [ "$($SQL result.sqlite "SELECT EXISTS(SELECT COUNT(*) cnt FROM ${PFX}_historyvisits GROUP BY from_visit,place_id,visit_date,visit_type,session HAVING cnt>1)")" = "1" ]; do
116 $SQL result.sqlite "CREATE TABLE idmap(fromid INTEGER PRIMARY KEY, toid INTEGER);"
117 $SQL result.sqlite "INSERT INTO idmap SELECT A.id, MIN(B.id) FROM ${PFX}_historyvisits A, ${PFX}_historyvisits B WHERE A.from_visit IS B.from_visit AND A.place_id IS B.place_id AND A.visit_date IS B.visit_date AND A.visit_type IS B.visit_type AND A.session IS B.session GROUP BY A.id;"
118 $SQL result.sqlite "DELETE FROM ${PFX}_historyvisits WHERE id NOT IN (SELECT MIN(id) FROM ${PFX}_historyvisits A GROUP BY from_visit,place_id,visit_date,visit_type,session);"
119 $SQL result.sqlite "UPDATE ${PFX}_historyvisits SET from_visit=(SELECT toid FROM idmap WHERE fromid=from_visit);"
120 $SQL result.sqlite "DROP TABLE idmap;"
121 done
122done
123
124# a hack: don't add doubled bookmarks twice
125for PFX in mozadd; do
126 while [ "$($SQL result.sqlite "SELECT EXISTS(SELECT COUNT(*) cnt FROM ${PFX}_bookmarks GROUP BY type,fk,parent,title HAVING cnt>1)")" = "1" ]; do
127 $SQL result.sqlite "CREATE TABLE idmap(fromid INTEGER PRIMARY KEY, toid INTEGER);"
128 $SQL result.sqlite "INSERT INTO idmap SELECT A.id, MIN(B.id) FROM ${PFX}_bookmarks A, ${PFX}_bookmarks B WHERE A.type IS B.type AND A.fk IS B.fk AND A.parent IS B.parent AND A.title IS B.title GROUP BY A.id;"
129 $SQL result.sqlite "DELETE FROM ${PFX}_bookmarks WHERE id NOT IN (SELECT MIN(id) FROM ${PFX}_bookmarks A GROUP BY type,fk,parent,title);"
130 $SQL result.sqlite "UPDATE ${PFX}_bookmarks SET parent=(SELECT toid FROM idmap WHERE fromid=parent);"
131 $SQL result.sqlite "DELETE FROM ${PFX}_items_annos WHERE item_id NOT IN (SELECT toid FROM idmap);"
132 $SQL result.sqlite "DROP TABLE idmap;"
133 done
134done
135
136# update mozadd_* tables
137sql_deduplicate anno_attributes name
138sql_update_ids items_annos.anno_attribute_id annos.anno_attribute_id
139
140sql_deduplicate favicons url
141sql_update_ids places.favicon_id
142
143sql_deduplicate hosts host
144sql_update_ids
145
146sql_deduplicate keywords keyword # it's unique, pointless to check for other fields
147sql_update_ids bookmarks.keyword_id
148
149sql_deduplicate places url # looks unique in my dababases
150sql_update_ids bookmarks.fk historyvisits.place_id annos.place_id inputhistory.place_id keywords.place_id
151
152sql_deduplicate annos place_id anno_attribute_id # NOTE: must be done AFTER `moz_places` AND `moz_anno_attributes`
153sql_update_ids
154
155$SQL result.sqlite "DELETE FROM mozadd_bookmarks WHERE type=3;" # type=3 are separators, don't know how to merge them
156( MATCHANY=1 CHECKPARENT=parent:title sql_map_duplicates bookmarks type fk title ) # NOTE: must be done AFTER `moz_places` as fk<->moz_places.id
157( CHECKPARENT=parent:title sql_delete_duplicates bookmarks type fk title )
158sql_map_duplicates bookmarks guid # it's unique
159sql_delete_duplicates bookmarks guid
160sql_shift_id bookmarks
161sql_update_ids bookmarks.parent items_annos.item_id
162
163( MATCHANY=1 CHECKPARENT=from_visit:place_id sql_deduplicate historyvisits place_id visit_date visit_type ) # NOTE: must be done AFTER `moz_places`
164sql_update_ids historyvisits.from_visit
165
166# moz_inputhistory has no id, nothing to update, just delete duplicates
167sql_delete_duplicates inputhistory place_id input # NOTE: must be done AFTER `moz_places`
168
169sql_deduplicate items_annos item_id anno_attribute_id # NOTE: AFTER `moz_bookmarks` AND `moz_anno_attributes`
170sql_update_ids
171
172$SQL result.sqlite "DROP INDEX moz_places_url_mytmpindex"
173
174# merge tables
175for TSFX in anno_attributes annos bookmarks favicons historyvisits hosts inputhistory items_annos keywords places; do
176 $SQL result.sqlite ".dump mozadd_$TSFX" | grep '^INSERT INTO' || true # show what's inserted
177 $SQL result.sqlite "INSERT INTO moz_$TSFX SELECT * FROM mozadd_$TSFX;"
178 $SQL result.sqlite "DROP TABLE mozadd_$TSFX;"
179done
180
181$SQL result.sqlite "VACUUM;"