I’m an OpenCellID contributor. I use Tower Collector application on Android phones for recording cell data. I have recorded data for more than 500,000 locations.

One day one of my phones has shut down on a trip after 80 kilometers which I will never do again. I have checked Locus where the tracklog stopped: a few kilometers behind. Started Tower Collector, it showed zero locations! Oh my god. I had another trip previous day which was not uploaded yet.

This device was rooted, started root browser, checked the application’s data directory:

	/data/data/info.zamojski.soft.towercollector

In directory “databases” I have seen an empty measurements.db (a few kB), but there was another file: measurements.db.back with 600 kB! Made a backup copy of the whole directory and resumed my route.

At home I have checked the db. It is an sqlite database, but has been corrupted.

	sqlite> pragma integrity_check;
	Error: database disk image is malformed

I have made a dump of the database:

	echo .dump | sqlite3 measurements.db.back > measurements.sql

There were 2059 measurements for 76 cells, valuable data. How to restore them? Load back to Tower Collector. I did not have time for that for weeks, but continued collecting new data. I was frustrated because this phone did not remember the cells collected before the crash, displayed a most of cells as new. I have also missed the data from that two trips. Some weeks later I have tried to load data.

At first I have created a new database using the recovered data but I have failed many times. I replaced measurements.db with the new one, app crashed (SQLiteCantOpenDatabaseException). Checked directory and file permissions via Root Browser, it showed root as owner of every file, but later I have checked the same thing via adb and it showed totally different owners: every app has a userid and owner should be set correctly.

After setting owner for the database, I had another kind of crash: Tower Collector said “upgrading database” and crashed again. Stack trace had an SQL statement: “CREATE TABLE cells” which failed because cells table already existed. Why does it upgrade? I did not see any difference in schema of old and current database.

Then I started a different way. Prepared only data from the dump: kept only INSERT statements for the following tables:

	INSERT INTO "measurements" ...
	INSERT INTO "cells_archive" ...
	INSERT INTO "cells" ...

Uploaded this file via adb:

	adb push inserts.sql /data/data/info.zamojski.soft.towercollector/databases/inserts.sql

Then restored the last working measurements.db and logged in via adb:

	$ adb shell
	~ # cd /data/data/info.zamojski.soft.towercollector/databases
	# sqlite3 measurements.db

Here I had an almost empty database, only cells_archive table had 1227 rows. I have truncated this to make history clean:

	# DELETE FROM cells_archive;

Then I imported the SQL file just uploaded:

	# .read inserts.sql

Logged out and tested: app displayed 2059 measurements for 76 cells. Quickly uploaded to OpenCellID and checked newly uploaded measurements there.

Then I merged the new cells captured since the failure. Dumped cells_archive from the previously backed up version of measurements.db, replaced table name “cells_archive” to “cells”, loaded into the database using the same way and then deleted them. This database has a trigger which archives rows deleted from table “cells” to “cells_archive” ignoring duplicates.

I had seen 6176 cells before the crash. Since then there were 1227 new, merging resulted 6793 cells, 617 new. The rest were duplicates.

The last step was setting total number of locations to the sum measurements before and after crash:

UPDATE stats SET total_locations = 156440+29641 WHERE row_id=1;

Now I have a database which knows all cells and number of locations since I have started collecting data. I am happy.