{"action":"created","issue":{"url":"https://api.github.com/repos/smarthomeNG/smarthome/issues/131","repository_url":"https://api.github.com/repos/smarthomeNG/smarthome","labels_url":"https://api.github.com/repos/smarthomeNG/smarthome/issues/131/labels{/name}","comments_url":"https://api.github.com/repos/smarthomeNG/smarthome/issues/131/comments","events_url":"https://api.github.com/repos/smarthomeNG/smarthome/issues/131/events","html_url":"https://github.com/smarthomeNG/smarthome/pull/131","id":181814563,"number":131,"title":"DB API2 support in SmartHome.py used in SQLite plugin","user":{"login":"ohinckel","id":717217,"avatar_url":"https://avatars.githubusercontent.com/u/717217?v=3","gravatar_id":"","url":"https://api.github.com/users/ohinckel","html_url":"https://github.com/ohinckel","followers_url":"https://api.github.com/users/ohinckel/followers","following_url":"https://api.github.com/users/ohinckel/following{/other_user}","gists_url":"https://api.github.com/users/ohinckel/gists{/gist_id}","starred_url":"https://api.github.com/users/ohinckel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ohinckel/subscriptions","organizations_url":"https://api.github.com/users/ohinckel/orgs","repos_url":"https://api.github.com/users/ohinckel/repos","events_url":"https://api.github.com/users/ohinckel/events{/privacy}","received_events_url":"https://api.github.com/users/ohinckel/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":185,"created_at":"2016-10-08T09:03:30Z","updated_at":"2016-11-26T15:15:18Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/smarthomeNG/smarthome/pulls/131","html_url":"https://github.com/smarthomeNG/smarthome/pull/131","diff_url":"https://github.com/smarthomeNG/smarthome/pull/131.diff","patch_url":"https://github.com/smarthomeNG/smarthome/pull/131.patch"},"body":"(copied from https://github.com/mknx/smarthome/pull/69)\r\n\r\nThis pull request implements three things:\r\n- make it possible to use different database backends in core or plugins\r\n- make use of database backend accessor in SQLite plugin\r\n- implement new plugin `dblog` which logs item changes to a database and use different implementation (currently only tested with SQLite, but should also work with any MySQL DB API2 implementation)\r\n\r\nI implemented this in one pull request, since 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.\r\n# Using database backends\r\n\r\nI thought about how to use different database implementation / backends with SmartHome.py and came to this implementation.\r\n\r\nIt 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`.\r\n\r\nSince 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.\r\n`db = sqlite:sqlite3 | mysql:mysqldb`.\r\n\r\nAdditionally 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:\r\n\r\n```\r\ndb = sh.dbapi('sqlite')\r\ndb.connect(...)\r\ndb.execute(...)\r\n```\r\n\r\nFor convenience a new class `lib.db.Database` was added which can be constructed by using the following code\r\n\r\n```\r\n lib.db.Database(\"NameIdentifier\", sh.dbapi(\"sqlite\"), {\"database\" : \"/path/to/sqlite.db\"})\r\n```\r\n\r\nThe first identifier argument is just a name which is used when something is written to the log and one want to identifier which database connection is related to a log message. Additionally to this, it is also used internally (e.g. to name the `version` table) - so just us characters like `[a-zA-Z0-9_]` and do not start with numbers.\r\n\r\nThe second DB API parameter is the module of the database backend implementation. In this example it will use the SQLite backend implementation which is registered as default.\r\n\r\nThe third connect parameter is a `dict` containing all connection parameters required to invoke the module's `connect()` function. Alternatively this can also be a simple string using a map-like syntax of parameters, e.g. `host:localhost | port:1234 | ...` - see also below.\r\n# Use DB API in SQLite plugin\r\n\r\nThis 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!).\r\n\r\nSince 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.\r\n\r\nThe 'sqlite' backend is always automatically registered and available.\r\n# New Plugin `dblog`\r\n\r\nThe 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.\r\n\r\nThe 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).\r\n\r\nThe configuration of this plugin is simple:\r\n- db - defines which database backend should be used (e.g. `sqlite` - which is registered as default)\r\n- 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\r\n\r\n```\r\n[dblog]\r\n  class_name = DbLog\r\n  class_path = plugins.dblog\r\n  db = sqlite\r\n  connect = database:/home/service/apps/smarthome/var/db/log.db | check_same_thread:0\r\n```\r\n\r\nThis plugin is already using the new `lib.db.Database` class to do something with the database backend.\r\n\r\nSince the name `dblog` does not fit well, here are some suggestions for the plugin name:\r\n* `dblog` - could confuse users assuming it has something to do with a message log written to the database\r\n* `history` - maybe a to much generic name\r\n* `database` - conflicts with #71 \r\n* `rdbms`\r\n* `itemlog`\r\n* `dbconnector`\r\n* ... add further suggestions here!\r\n\r\n# Additional notes\r\n\r\nFurther I start testing the `dblog` plugin with another DB API: [PyMySQL](https://github.com/PyMySQL/PyMySQL). It working well with it and using this I was able to log the data into a MySQL database.\r\n\r\nAdded a new class `lib.db.Database` which can be used to talk to different database backend implementations using DB API2. It have the following functions:\r\n- `__init__()`: will get a identifier name, backend implementation module and connect parameter (see introduction)\r\n- `connect()`: will connect to the database\r\n- `close()`: will close the connection\r\n- `setup()`: can be used to run multiple database setup queries (e.g. create tables / indexes)\r\n- `execute()`: execute the given query with given parameters (use correct quoting style of backend module)\r\n- `fetchone()`: execute given query and return one row\r\n- `fetchall()`: execute given query and return all rows\r\n- `cursor()`: create a cursor, which can be passed to the `execute()` and `fetch*()` functions\r\n- `lock()`: create a lock\r\n- `release()`: release the lock\r\n- `verify()`: verify if the connection is still available and try to reconnect if required\r\n\r\nIt can be used to execute simple queries, query for data and return one or all of them, make use of cursors explicitely by using `cursor()` function and create/release locks - if required. It also checks the database backend module's parameter style in SQL queries and replaces the placeholders accordingly. The queries passed to the query function of the `Database` class should always use `?` (`qmark` style)!\r\n\r\nI just commited a solution for:\r\n\r\n> The dblog plugin tries to create tables / indexes and log errors but continue with the plugin. I think this behaviour is not very clean, but works at the moment. Maybe one of you have another good idea how to solve this (maybe trying to select data from a table to check if it exist).\r\n\r\nThe `setup()` method will not accept not only a list of queries to setup a database, instead it will accept a `dict` where the keys are just incrementing version numbers and the values are the queries to execute. The `lib.db.Database` class will automatically create a (internal) table called `<name>_version` to track the current status of the database (where name is the name given in the `Database` constructor). This table is used to find out which queries need to be executed to reach the newest version of the database.\r\n\r\nF.e. using the `setup()` method with the following dictionary\r\n\r\n```\r\n  db.setup({\r\n    '1' : 'CREATE TABLE item(name varchar)',\r\n    '2' : 'CREATE INDEX item_name on test (name)',\r\n    '3' : 'CREATE TABLE log(name varchar, date datetime, data varchar)',\r\n\r\n    // These lines were added in a later release\r\n    '4' : 'ALTER TABLE item add id integer autoincrement',\r\n    '5' : 'ALTER TABLE log add item_id integer',\r\n    '6' : 'UPDATE log set item_id=id FROM item, log where item.name= log.name'\r\n  });\r\n```\r\n\r\n... will automatically migrate the table structure and data to the new layout (don't know if all of the queries are working, it should only show what should be possible with this). The layout was changed to introduce a new ID column (in item and log table) and the data was migrated (based on the name in log table the ID in item table will be updated).\r\n"},"comment":{"url":"https://api.github.com/repos/smarthomeNG/smarthome/issues/comments/263068522","html_url":"https://github.com/smarthomeNG/smarthome/pull/131#issuecomment-263068522","issue_url":"https://api.github.com/repos/smarthomeNG/smarthome/issues/131","id":263068522,"user":{"login":"ohinckel","id":717217,"avatar_url":"https://avatars.githubusercontent.com/u/717217?v=3","gravatar_id":"","url":"https://api.github.com/users/ohinckel","html_url":"https://github.com/ohinckel","followers_url":"https://api.github.com/users/ohinckel/followers","following_url":"https://api.github.com/users/ohinckel/following{/other_user}","gists_url":"https://api.github.com/users/ohinckel/gists{/gist_id}","starred_url":"https://api.github.com/users/ohinckel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ohinckel/subscriptions","organizations_url":"https://api.github.com/users/ohinckel/orgs","repos_url":"https://api.github.com/users/ohinckel/repos","events_url":"https://api.github.com/users/ohinckel/events{/privacy}","received_events_url":"https://api.github.com/users/ohinckel/received_events","type":"User","site_admin":false},"created_at":"2016-11-26T15:15:18Z","updated_at":"2016-11-26T15:15:18Z","body":"Danke fuer euer Feedback! Folgende Aufgaben ergeben sich nun fuer mich:\r\n- [ ] Plugin umbenennen: `dblog` -> `database` (siehe auch Vorschlaege in der Beschreibung)\r\n- [ ] Fix fuer das Logging: muss im Plugin direkt initialisiert werde (siehe auch andere Plugins)\r\n- [ ] Skript fuer die Migration von SQLite zum `database` Plugin\r\n- [ ] Verbesserung der Dokumentation (bin offen fuer Vorschlaege)\r\n- [ ] Erweiterung der Einstellungsmoeglichkeiten: `dblog = init`, `dblog = yes` um Steuern zu koennen fuer welche Items ein init gemacht werden soll (z.B. sollen KNX-Items in die DB geschrieben werden, ein init wuerde aber ueber den Bus gemacht werden)\r\n"}}
{"actor":{"display_login":"ohinckel"}}