Thursday 26 January 2017

MQTT MySQL gateway

Previously I've made a couple of sensors that send data with MQTT. Of course that data should be stored in a database. Considering that I'm probably going to make more wireless sensors that send data for example from our heating system it's best to write a easily configurable MQTT to database gateway. This gateway should also be able to send new values from database to MQTT nodes for displaying, as some data is gathered in other ways and inserted directly to db, for example OWFS(1-wire) or homegrown pulse counters.

So the basic functionality would be:
  • Recieve data from MQTT, process and save it to MySQL
  • Follow MySQL binlog and send saved data to MQTT
For input I need to have a table that stores:
  • Subscribed topic.
  • How to get raw sensorid from topic, maybe regexp.
  • How to parse data from message.
  • Small script that processes the data so it's ready to store.
And a second table that's used to convert raw sensorid's to db sensorid's:
  • Rawid
  • Sensorid
For output I need a table that stores:
  • Sensorid
  • Topic to push data to
  • How to process data before sending
There are still some features to implement, like outputting data from db to MQTT, need to think how this should be done with some kind of plugin system, MySql will probably use binlog to read data, but other databases have other mechanisms.

There are depencies for Paho-MQTT clientpep3143daemon and SQLAlchemy. More detailed dependencies in requirements.txt

Code in GitHub https://github.com/mika-koivusaari/mqtt_db_gateway.

Tuesday 17 January 2017

Wooden urn

We had a death in the family and I wanted to make an urn for the ashes, before they are scattered.

For material I used birch and heat treated birch. I wanted to make the sides at an angle, and I found a nice calculator for compound miter angles. Sides are only at a 5 degree angle so the bevel is almost 45 degrees. Before I sawed bevels I routed a slot for bottom with my old adjustable jig(in finnish).


For the 5 degree angle I made a jig and tilted the saw blade for bevel.






Top was glued from four pieces, and one piece in the bottom to hold it in place, and cut to size with bottom. Finished with wax.