mknx/smarthome — pull request #69 by ohinckel (2013-12-31 14:28:55+00:00)
Title: DB API2 support in SmartHome.py used in SQLite plugin

This pull request implements three things:
* make it possible to use different database backends in core or plugins
* make use of database backend accessor in SQLite plugin
* implement new plugin `dblog` which logs item changes to a database and use different implementation (currently only tested with SQLite, but should also with with any MySQL DB API2 implementation)

I implemented this in one pull request, since it the `dblog` plugin depends on the DB API enhancement. The documentation is also updated and can be [provided](https://github.com/ohinckel/smarthome/tree/gh-pages-dbapi-support) in a new pull request when this is merged.

# Using database backends
I thought about how to use different database implementation / backends with SmartHome.py and came to this implementation.

It adds a new configuration setting to `smarthome.conf` called `db`. This configures the database backend implementation (need to implement the [DB API 2 specifications](http://www.python.org/dev/peps/pep-0249/) to have a common interface available) and defaults to `sqlite:sqlite3`, which means the SQLite API is available using the alias `sqlite` and the Python module `sqlite3`.

Since SQLite is bundled with Python directly you do not need to explicitely configure this - it's always added as default if not registered explicitely. It's also possible to register multiple database backends using `|`, e.g.
`db = sqlite:sqlite3 | mysql:mysqldb`.

Additionally to this configuration the SmartHome.py core have a new function called `dbapi(alias)` which returns the module for the given alias (e.g. in the case above `sh.dbapi('sqlite')` will return the `sqlite3` module and you can do something like this:
```
db = sh.dbapi('sqlite')
db.connect(...)
db.execute(...)
```

# Use DB API in SQLite plugin

This pull request will make use of this new feature in the SQLite plugin already to be able to use other database backends later (not part of this pull request and may be implemented in a new pull request and maybe a new plugin which could obsolete the SQLite plugin; suggestions are welcome!).

Since the SQLite module already implements the DB API2 specification it was not really hard to change it. The plugin implementation was already using the specification and the patch is very simple (just use the `sh.dbapi('sqlite')` - everything else seems to be working well.

The 'sqlite' backend is always automatically registered and available.

# New Plugin `dblog`

The new plugin is logging item changes to the database. It was tested using a SQLite database to log changes to. It will not do any pack mechanism which is the SQLite plugin doing. It's something like a log and stores all changes to the database.

The amount of data could be large when SmartHome.py is running long time. That's why it also support other database backends (e.g. MySQL or PostgreSQL).

The configuration of this plugin is simple:
* db - defines which database backend should be used (e.g. `sqlite` - which is registered as default)
* connect - the list of parameters to pass to database backend's `connect()` function (e.g. for SQLite something like `connect = database:/path/to/log.db | check_same_thread:0`) - the parameters depends on the database implementation

```
[dblog]
class_name = DbLog
class_path = plugins.dblog
db = sqlite
connect = database:/home/service/apps/smarthome/var/db/log.db | check_same_thread:0
```