Skip to content

Instantly share code, notes, and snippets.

@empi89 empi89/import.php
Last active Feb 7, 2018

Embed
What would you like to do?
FHEM filelog to dblog mysql migration
<?php
/**
* Script for migrating existing FileLog logfiles to the history database table.
*
* Howto:
* 1. Setup DbLog database tables and setup logging to database.
* 2. Wait until your devices are listed in the current-table of the database.
* 3. edit mysql username/password/database in this script
* 4. php <scriptname> <path_to_logfile>
*
* Maybe the error "no type found for device" will occur.
* If this is the case the device name was not found in the current table.
* You may want to check of the device name is still in use.
* If not you could remove lines with that device name manually from your logfile or
* trigger an action from that device thus it will create an entry in the current-table.
*
* This script requires a working php-cli with mysqli extension.
*
* Use at your own risk!
*/
$mysql = mysqli_connect("localhost", "USERNAME", "PASSWORD", "DATABASE");
$filename = $argv[1];
$fh = fopen($filename, "r") or die("Could not open file ".$filename." for reading");
$types = array();
$insert = mysqli_prepare($mysql, "INSERT INTO `history` VALUES(?, ?, ?, ?, ?, ?, '');");
while (!feof($fh)) {
$matches = array();
$line = trim(fgets($fh));
if ($line == "") {
continue;
}
preg_match("/^([0-9]{4}\-[0-9]{2}\-[0-9]{2})_([0-9]{2}:[0-9]{2}:[0-9]{2}) (\S*) (.*)/", $line, $matches);
if (!isset($type[$matches[3]])) {
$typerow = mysqli_fetch_assoc(mysqli_query($mysql, "SELECT `TYPE` FROM current WHERE `DEVICE` = '".mysqli_escape_string($mysql, $matches[3])."' LIMIT 1"));
if (is_array($typerow) && strlen($typerow['TYPE']) > 0) {
$type[$matches[3]] = $typerow['TYPE'];
} else {
die("no type found for device ".$matches[3]."!");
}
}
$timestamp = $matches[1]." ".$matches[2];
$device = $matches[3];
$devtype = $type[$matches[3]];
$event = $matches[4];
$reading_value = array();
preg_match("/(\S+): (.*)/", $event, $reading_value);
if (count($reading_value) == 0) {
$reading = "state";
$value = $event;
} else {
$reading = $reading_value[1];
$value = $reading_value[2];
}
$insert->bind_param("ssssss", $timestamp, $device, $devtype, $event, $reading, $value);
$insert->execute();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.