{"id":1564,"date":"2017-08-31T22:17:20","date_gmt":"2017-09-01T03:17:20","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=1564"},"modified":"2017-08-31T22:45:37","modified_gmt":"2017-09-01T03:45:37","slug":"cleaning-up-old-openhab-persistence-tables","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=1564","title":{"rendered":"Cleaning Up Old OpenHAB Persistence Tables"},"content":{"rendered":"<p>So my husband asked for a program that would go out to the OpenHAB persistence database and identify all of the item tables that are no longer associated with active items. If you rename or delete an item from OpenHAB, the associated data is retained in the persistence database. Might be a good thing &#8211; maybe you\u00a0<em>wanted<\/em> that data. But if it&#8217;s useless fluff &#8230; well, no need to keep the state changes from a door sensor that&#8217;s no longer around.<\/p>\n<p>Wrote the code, and asked him how many days old he wanted the last update to be before the item table got dropped &#8230; and he told me this was a useless way to do it and maybe something really hadn&#8217;t updated in six months or three years and age of last update is no way to be identifying tables to be removed. Which, yeah, then why\u00a0<em>ask<\/em> for it!? Then I needed to write something that takes a list of items from OpenHAB and identifies everything\u00a0in the items table that does not appear in the OpenHAB list so\u00a0<em>those<\/em> tables can be deleted. But I figured I&#8217;d post the original code too in case anyone else could use it. Both in perl, and neither in particularly well written perl. I trust the data and don&#8217;t want to protect against insertion attacks.<\/p>\n<p>Drop tables for items that no longer appear in OpenHAB:<\/p>\n<pre>use strict;\r\nuse DBI;\r\n\r\nmy %strItemsFromOpenHAB = ();\r\nopen(INPUT,\".\/openhabItemList.txt\");\r\nwhile(&lt;INPUT&gt;){\r\n        chomp();\r\n        my $strCurrentItem = $_;\r\n        $strItemsFromOpenHAB{$strCurrentItem}++;\r\n}\r\nclose INPUT;\r\n\r\nmy $dbh = DBI-&gt;connect('DBI:mysql:openhabdb;host=DBHOST', 'DBUID', 'DBPassword', { RaiseError =&gt; 1 } );\r\n\r\nmy $sth = $dbh-&gt;prepare(\"SELECT * FROM items\");\r\n$sth-&gt;execute();\r\nwhile (my @row = $sth-&gt;fetchrow_array) {\r\n        my $strItemID = $row[0];\r\n        my $strItemName = $row[1];\r\n        if(! $strItemsFromOpenHAB{$strItemName} ){              # If the current item name is not in the list of items from OpenHAB\r\n#               print \"DELETE FROM items where ItemID = $strItemID\\n\";\r\n                print \"DROP TABLE Item$strItemID;  # $strItemName \\n\";\r\n        }\r\n}\r\n$sth-&gt;finish();\r\n\r\n$dbh-&gt;disconnect();\r\nclose OUTPUT;\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Identify tables that have not been updated in iTooOldInDays days:<\/p>\n<pre>use strict;\r\nuse DBI;\r\nuse Date::Parse;\r\nuse Time::Local;\r\n\r\nmy $iTooOldInDays = 365;\r\n\r\nmy $iCurrentEpochTime = time();\r\n\r\nmy @strItems = ();\r\nmy $iItems = 0;\r\n\r\nmy $dbh = DBI-&gt;connect('DBI:mysql:openhabdb;host=DBHOST', 'DBUID', 'DBPassword', { RaiseError =&gt; 1 } );\r\n\r\nmy $sth = $dbh-&gt;prepare(\"SELECT * FROM Items\");\r\n$sth-&gt;execute();\r\nwhile (my @row = $sth-&gt;fetchrow_array) {\r\n        $strItems[$iItems++] = $row[0];\r\n}\r\n$sth-&gt;finish();\r\n\r\nfor(my $i = 0; $i &lt; $iItems; $i++){ my $strTableName = 'Item' . $strItems[$i]; my $sth = $dbh-&gt;prepare(\"SELECT * FROM $strTableName ORDER BY Time DESC LIMIT 1\");\r\n        $sth-&gt;execute();\r\n        while (my @row = $sth-&gt;fetchrow_array) {\r\n                my $strUpdateTime = $row[0];\r\n                my @strDateTimeBreakout = split(\/ \/,$strUpdateTime);\r\n                my $strDate = $strDateTimeBreakout[0];\r\n                my $strTime = $strDateTimeBreakout[1];\r\n\r\n                my @strDateBreakout = split(\/-\/,$strDate);\r\n                my @strTimeBreakout = split(\/:\/,$strTime);\r\n\r\n                my $iUpdateEpochTime = timelocal($strTimeBreakout[2],$strTimeBreakout[1],$strTimeBreakout[0], $strDateBreakout[2],$strDateBreakout[1]-1,$strDateBreakout[0]);\r\n                my $iTableAge = $iCurrentEpochTime - $iUpdateEpochTime;\r\n\r\n                if($iTableAge &gt; ($iTooOldInDays * 86400) ){\r\n                        print \"$strTableName last updated $strUpdateTime - $iUpdateEpochTime\\n\";\r\n                }\r\n        }\r\n        $sth-&gt;finish();\r\n}\r\n\r\n$dbh-&gt;disconnect();\r\nclose OUTPUT;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>So my husband asked for a program that would go out to the OpenHAB persistence database and identify all of the item tables that are no longer associated with active items. If you rename or delete an item from OpenHAB, the associated data is retained in the persistence database. Might be a good thing &#8211; &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[63,30],"tags":[56,44,317],"class_list":["post-1564","post","type-post","status-publish","format-standard","hentry","category-home-automation","category-system-administration","tag-mysql","tag-openhab","tag-persistence"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/1564","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1564"}],"version-history":[{"count":4,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/1564\/revisions"}],"predecessor-version":[{"id":1568,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/1564\/revisions\/1568"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1564"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}