Introduction

The front end I'm developing for FHEM has a requirement to draw charts and give access to historic data. The existing front ends seem to (from my very limited research) handle this based on text log files. However, I'd like to be able to handle a range of ultimate interface designs without changing my server, so I'd like the websites to have access to the raw data directly, and a flat text file is not a performant way to do this.

One option is to store log data in a database format, where it can be quickly and efficiently queried, it can be flexibly cached at the web end and different views of the data can easily be constructed for different scenarios. Fortunately, there's already an FHEM contribution to do just this called "dblog", which in the distribution seems to live in a folder called "contrib/dblog". A cursory glance over the "93_DbLog.pm" file suggests that the extension supports the FHT devices, so off we go.

Install a Suitable Database

For my purposes, I have the website and the FHEM server on the same box, so a great option looks like Sqlite (http://www.sqlite.org/). It's not a database I've played with before, but I was up and running with it within minutes.

1. Download Sqlite for Windows from http://www.sqlite.org/sqlite-3_7_3.zip.
2. Extract the application to a suitable location, in my case I've extracted it to "c:\strawberry\sqlite3"

Setup your Database

To configure your database, you can simply execute the SQL in the "contrib/dblog/fhemdb_create.sql" script. I must confess here I did have some problems, but it turns out to be because this script configures user accounts as well as database tables, and Sqlite doesn's support user accounts.

To setup the database:

1. Run a command prompt and navigate to your FHEM application folder
2. Enter "c:\path\to\your\sqlite3.exe fhem.db" (in my case I entered "c:\strawberry\sqlite3\sqlite3.exe fhem.db") to start editing your database
3. To run commands in the setup script, you need to enter ".read contrib/dblog/fhemdb_create.sql".
4. At this point you'll get some error messages about unrecognised commands, but if you now enter the command ".tables", you should see the "current" and "history" tables have been created for you.

Copy the 93_DbLog File to your FHEM Subfolder

This step was missing from this blog post until today (9th November), so apologies to anyone who has scratched their heads over this!

1. Check your modpath location in your config file - in my case it's just "." which means the folder where I've installed FHEM
2. Copy the file "93_DbLog" to the "FHEM" subfolder in your modpath location
3. Note - If this file is not found, FHEM doesn't seem to create any sort of error message in your log, it just won't do anything!

Define your Database Log in FHEM

To define the Database log, simply enter the following in your FHEM configuration file or at an FHEM server client:

define FHT_Log DbLog c:\absolute\path\to\db.conf .*:.*

This will define a DbLog entity called "FHT_Log", using the database configuration in "db.conf".  This configuration is in a separate file for security purposes, so if you had a username and password against your database you might want to be careful that this is in a location your web server will not serve to the outside world.

Finally, you'll also need to create the "db.conf" file referenced in your define statement, as follows:

%dbconfig = (
   connection => "SQLite:dbname=fhem.db",
   user => "",
   password => ""
);

Note that as touched on earlier, there is no user or password required for sqlite databases.

Test

That should be all that's required - if you now start your FHEM server as normal, and inspect the server global logfile, you should see something like the following:

2010.10.21 22:28:50 5: Loading ./FHEM/93_DbLog.pm
2010.10.21 22:28:50 3: Connecting to database SQLite:dbname=fhem.db with user 
2010.10.21 22:28:50 3: Connection to db SQLite:dbname=fhem.db established
2010.10.21 22:28:50 5: Triggering global (1 changes)
.
.
.