wview data recovery | fishparts.net

This page shows how I edited my wview archive database to patch in data values that were NULL when my Vpro2 sensors battery went dead in the middle of the night.

Become root.
Stop wview!

root@wview:~# sudo /usr/bin/service wview stop Shutting down wview daemons...

Make a backup of the database

root@wview:~# cd /usr/local/var/wview/archive # /var/lib/wview/archive if installed using apt root@wview:~# sqlite3 wview-archive.sdb .dump | sqlite3 archive-backup.sdb root@wview:~# mv archive_backup.sdb /tmp root@wview:~# chown wuser:wuser /tmp/archive_backup.sdb

Now you can work with the backup in a less risky environment. Copy the archive backup to user space so you can work as a non-root user to find the error spots and determine how you want to fix them and so forth. You can restart wview while you are working on your data.
Now, for the research part. Start by searching for the NULL entries in the database:

wuser@wview:~$ sqlite3 /tmp/archive_test.db sqlite> select datetime(datetime,'unixepoch','localtime'), * from archive where outTemp is null; 2010-01-09 00:00:00|1263016800|1|5|30.487|29.832266|30.436926|71.099998||14.0||||0.0||0.0|0.0||||0.0|||47.0|||||||||48.0||||||||0.0|0.0|446.0|||||||||| 2010-01-09 00:05:00|1263017100|1|5|30.487|32.043041|32.6842|70.400002||14.0||||0.0||0.0|0.0||||0.0|||47.0|||||||||48.0||||||||0.0|0.0|383.0|||||||||| 2010-01-09 00:10:00|1263017400|1|5|30.487|30.842573|31.463978|70.199997||13.0||||0.0||0.0|0.0||||0.0|||47.0|||||||||47.0||||||||0.0|0.0|392.0|||||||||| 2010-01-09 00:15:00|1263017700|1|5|30.481001|30.681679|31.300421|70.099998||13.0||||0.0||0.0|0.0||||0.0|||47.0|||||||||47.0||||||||0.0|0.0|562.0|||||||||| 2010-01-09 00:20:00|1263018000|1|5|30.481001|30.620796|31.238535|70.099998||13.0||||0.0||0.0|0.0||||0.0|||47.0|||||||||47.0||||||||0.0|0.0|393.0|||||||||| 2010-01-09 00:25:00|1263018300|1|5|30.481001|30.588255|31.205452|70.099998||13.0||||0.0||0.0|0.0||||0.0|||47.0|||||||||47.0||||||||0.0|0.0|545.0||||||||||

You can see that the NULL values were from midnight to 12:25 AM. You could just delete the rows, but if you want to keep the indoors data, you need to do a little more "homework" before changing the database.
Find a row from which to copy the data (if you want to see headers, do the ".headers on" command). I selected the five minute interval immediate before the missing data.

sqlite> select * from archive where datetime(datetime,'unixepoch','localtime') = '2010-01-08 23:55:00'; 1263016500|1|5|30.499001|29.844067|30.448923|70.699997|20.799999|14.0|57.0|||0.0||0.0|0.0|7.999364||20.799999|0.0|||47.0|||||||||48.0||||||||75.0|0.0|535.0||||||||||

Now you can start editing the data. It is easier to have sqlite3 create the SQL statements for you, so do similar select statements again, that will insert data into the archive, but setting the output mode to "insert" and putting the data in a text file you can edit:

sqlite> .mode insert archive sqlite> .output data_patch.sql sqlite> select * from archive where datetime(datetime,'unixepoch','localtime') = '2010-01-08 23:55:00'; sqlite> select * from archive where outTemp is null; sqlite> .quit

Edit the SQL statements with the editor of your choice. The original data file looked like this:

INSERT INTO archive VALUES(1263016500,1,5,30.499001,29.844067,30.448923,70.699997,20.799999,14.0,57.0,NULL,NULL,0.0,NULL,0.0,0.0,7.999364,NULL,20.799999,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,48.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,75.0,0.0,535.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263016800,1,5,30.487,29.832266,30.436926,71.099998,NULL,14.0,NULL,NULL,NULL,0.0,NULL,0.0,0.0,NULL,NULL,NULL,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,48.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,446.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263017100,1,5,30.487,32.043041,32.6842,70.400002,NULL,14.0,NULL,NULL,NULL,0.0,NULL,0.0,0.0,NULL,NULL,NULL,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,48.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,383.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263017400,1,5,30.487,30.842573,31.463978,70.199997,NULL,13.0,NULL,NULL,NULL,0.0,NULL,0.0,0.0,NULL,NULL,NULL,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,392.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263017700,1,5,30.481001,30.681679,31.300421,70.099998,NULL,13.0,NULL,NULL,NULL,0.0,NULL,0.0,0.0,NULL,NULL,NULL,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,562.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263018000,1,5,30.481001,30.620796,31.238535,70.099998,NULL,13.0,NULL,NULL,NULL,0.0,NULL,0.0,0.0,NULL,NULL,NULL,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,393.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263018300,1,5,30.481001,30.588255,31.205452,70.099998,NULL,13.0,NULL,NULL,NULL,0.0,NULL,0.0,0.0,NULL,NULL,NULL,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,545.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

And the edited file looked like this (remove the first line to avoid overwriting the data):

INSERT INTO archive VALUES(1263016800,1,5,30.487,29.832266,30.436926,71.099998,20.799999,14.0,57.0,NULL,NULL,0.0,NULL,0.0,0.0,7.999364,NULL,20.799999,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,48.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,75.0,0.0,446.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263017100,1,5,30.487,32.043041,32.6842,70.400002,20.799999,14.0,57.0,NULL,NULL,0.0,NULL,0.0,0.0,7.999364,NULL,20.799999,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,48.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,75.0,0.0,383.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263017400,1,5,30.487,30.842573,31.463978,70.199997,20.799999,13.0,57.0,NULL,NULL,0.0,NULL,0.0,0.0,7.999364,NULL,20.799999,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,75.0,0.0,392.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263017700,1,5,30.481001,30.681679,31.300421,70.099998,20.799999,13.0,57.0,NULL,NULL,0.0,NULL,0.0,0.0,7.999364,NULL,20.799999,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,75.0,0.0,562.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263018000,1,5,30.481001,30.620796,31.238535,70.099998,20.799999,13.0,57.0,NULL,NULL,0.0,NULL,0.0,0.0,7.999364,NULL,20.799999,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,75.0,0.0,393.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO archive VALUES(1263018300,1,5,30.481001,30.588255,31.205452,70.099998,20.799999,13.0,57.0,NULL,NULL,0.0,NULL,0.0,0.0,7.999364,NULL,20.799999,0.0,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,47.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,75.0,0.0,545.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

Now you can apply the changes to the test data.

wuser@wview:~$ sqlite3 /tmp/archive_test.db sqlite> DELETE FROM archive WHERE outTemp IS NULL; sqlite> .read data_patch.sql

After performing some tests, you can apply the change to your wview archive. Become root. Stop wview.

root@wview:~# sudo /usr/bin/service wview stop Shutting down wview daemons... root@wview:~# cd /usr/local/var/wview/archive/ root@wview:/usr/local/var/wview/archive# sqlite3 wview-archive.sdb SQLite version 3.6.10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> DELETE FROM archive WHERE outTemp IS NULL; sqlite> .read /tmp/data_patch.sql sqlite> .quit

Now, you need to (re)move the HILOW and NOAA databases to allow wview to recreate them (the manual says to remove the file, but renaming them should do the trick). Then start wview, and watch /var/log/messages for the progress.

root@wview:/usr/local/var/wview/archive# mv wview-hilow.sdb wview-hilow.sdb.backup root@wview:/usr/local/var/wview/archive# mv wview-noaa.sdb wview-noaa.sdb.backup root@wview:/var/log# service wview start && tail -50f messages

If all goes well, your data is fixed, and wview is running!

Private