Integrating a poll application using MySQL database

Tags: #<Tag:0x00007f616ed96f08> #<Tag:0x00007f616ed96df0> #<Tag:0x00007f616ed96d00>

Hello openHAB community,

I am currently working on a smart building project at my university in Stuttgart and would like to hear your thoughts and ideas about it.

The basic idea of the project is to integrate a poll application in the openHAB system. With an external application the students of the university can vote about values regarding the building ‘characteristics’, for example is it too hot (-> open window), too cold (-> turn on the heater), or too loud in the lecture room? This application should be connected to a database, where openHAB can pull out the results of the poll.

For the realization of the project, I am using a MySQL database. My plan for the external poll application is to create a webform which is connected to the database. It is mandatory that with each poll result, information like which building, room, lecture and professor is provided. So after the data is in the MySQL database, openHAB should be able to implement the data using the MySQL persistence. In the end, the poll results should be visualized in the HABpanel. A big question for me here is, how exactly can openHAB display the results graphically (e.g. in a pie chart) with just the raw data in the MySQL database as input?

I will document the progress of my project in this post. Feel free to comment your thoughts and ideas and you are welcome to ask me any questions about it.

Any advice will be appreciated.

Thanks.

1 Like

As for charting, i’m using Highcharts, they’re free for personal and non-profit use (see FAQs), but i’m toying around with Grafana too, which is very powerful for visualizations too.

To make use of your poll results, you could write a wrapper in python for example, call that within a rule every minute or so and populate an item with the result.

Best regards,
Alex

This might be a challenge as Persistence in OH doesn’t really work like that. It is really designed to create and persist data based on changes to Item states and it only supports querying using Item states. You can’t do arbitrary SQL queries from OH using Persistence.

If I were implementing this I would use a different architecture:

  • Create your external polling application so that in addition to saving to a database or instead of that it also publishes the results to OH directly.

  • Create an Item to represent each room and each result of each poll for each room. You can do this through the REST API from the polling application or you can do it manually. You might want to do so based on how you plan on representing this info on your sitemap or HABPanel. Neighter support dynamically created sitemaps very well.

  • Use Grafana to chart your results either based on the values stored in the MySQL database from the poll application itself or from the persisted Items in openHAB that represent the results of the poll. You have way more flexibility in configuring the graphs and tables that way.

It really can’t. Natively it can only chart data stored in Items. Hence the approach above.

hmmm… that could be tricky - but let’s think this one through:

  1. openHAB is a means for automating Tasks based on Information from sensors.
  2. the sensors can be (nearly) everything
  3. you want to capture data from sensors plus want the students decide what to do with it? based on graphical Information?

so, I would Setup the sensors in each room (temperature, noise, …) plus one “proxy” item, which would represent the polling. So depending on the sensors thresholds you can show a polling in HABPanel for “open window” - yes/no and then if reached enough votes and/or times do (automatically?) what the majority wants to do.

Is this something in the direction you’re thinking?

…besides: you are at least the second one asking questions regarding openHAB from the University Stuttgart, do you have a Professor, who wants you to use openHAB? :wink:

edit:

Rich is right, openHAB does only simple Charts withing HABPanel, but as Alexander mentioned above, openHAB can use Grafana as charting tool. You could implement Grafana-Charts in a HABPanel widget and build your polling in another.

Thanks for replying and giving your thoughts about my project!

@astrnad I am not an expert with those tools, but I only read about Grafana in combination with an InfluxDB and openHAB. Is it possible to use Grafana with a MySQL database?

But is it even possible to send the data directly to openHAB? I am now in the creation of the webform and thought the only way to get the results into openHAB is via a database like MySQL.

I have to add here: I am not working with any physical sensors here. The students vote about values as they feel (e.g. it is too cold or too hot), the result of the poll will be sent to openHAB and will be displayed. After that, an authoritarian person for example the professor has the right to take actions like turning on the heater using openHAB. But this part is not a task for me in this project. I have to just get the results from the poll into openHAB and visualize it suitable. So no working with sensors here.

And yes, we are a few students who are currently working with the same professor using openHAB :slightly_smiling_face:

Yes, absolutely. See here for example. I’m using it to visualize power consumption data from my home.

I don’t think that’s the preferred way - openHAB handles everything in items and stores data only for points in time for each item. So the way openHAB uses mysql (one table for each item, with only a time and a value column) and a developer would typically use mysql (multicolumn tables, PK-FK, triggers, etc) are not compatible in my eyes.

It is fairly easy to push the results of your polls to corresponding items in openHAB (MQTT, REST,…) where they in turn can be reacted upon by rules so that something happens if this or that result comes in.

ok. So you have to use item-incrementation like:

Number Temperature_Hoersaal1  "Temperatur Hörsaal 1" (WiWi_Fakultaet, WiWi_HS1) 
Number Noise_Hoersaal1        "Lärm Hörsaal 1"       (WiWi_Fakultaet, WiWi_HS1)

So you can have e.g. in a sitemap

Setpoint item=Temperature_Hoersaal1 minValue=-10 maxValue=10 step=1
Setpoint item=Noise_Hoersaal1 minValue=0 maxValue=10 step=1

With this you can have votes for everybody, who has access to the sitemap. In my Setpoint Config I defined a Range von 10, which is the Maximum number (you can either leave it completely - or adjust it to your needs). In a rule I would implement an action if the maximum (or a threshold) is reached, which could be a LED-lamp (a color for each “task”) or something and the professor could then take the appropriate manual action, if the LED is lit.

Note: openHAB is completely user-agnostic. So everybody could just vote multiple times without problems! You have to implement a special user layer for yourself (reverse proxy or some other technical hurdle).

Yes.

Let me count the ways:

  • MQTT messages
  • HTTP REST API
  • TCP/UDP
  • Polling from OH using HTTP binding
  • Polling from OH using Exec binding

I’m sure there are more.

This is actually completely wrong. There is NO way I know of to get data directly into openHAB through MySQL. You would benefit from a review of the Beginner’s Guide and the Concepts and Persistence sections of the User’s Guide.

From an openHAB perspective that is immaterial. An Item in OH represents a single piece of data (e.g. temperature) or a single actuator (e.g. a Switch). Whether that actuator or piece of data is physical or not is abstracted from OH itself.

1 Like

Thanks for the reply!

I created a few items like temperature, lighting, noise or pace. In the sitemap I implemented a new frame for my poll application and created groups for the different buildings at my university. In these groups I implemented sub groups for each room where the students should use the application.

Group item=Bau_2 label="Bau 2"{
    	Text item=Raum_221 label="Raum 221" icon="corridor"{ 
			Setpoint item=B2_R221_temperature minValue=-10 maxValue=30 step=1 
			Setpoint item=B2_R221_noise minValue=0 maxValue=10 step=1
                        Setpoint item=B2_R221_pace minValue=-10 maxValue=10 step=1
                        Setpoint item=B2_R221_lighting minValue=-10 maxValue=10 step=1
   		}

So I have a few questions here and would like to hear your thoughts about it.
Is there a way to write a rule in which I can set the values for the items back to null after a period of time? For example after a lecture of 90 Minutes the values should go back to null that the next students in the same room can vote.

Yes that’s the major problem for using openHAB directly as a poll application. @binderth Can you imagine any way where it is possible to implement any kind of profiles or a solution that not everybody can vote as often as they want?

In addition: When I upvote or downvote for example the temperature item in the myopenhab app on my phone the value at my localhost on my desktop will go up or down but the value on my phone stays at 0. What could be the reason here?

https://docs.openhab.org/addons/bindings/expire1/readme.html

Short of writing your own UI there is nothing you can do from within OH. It might be possible to code something up in a NGINX reverse proxy but that would probably be as much work as coding a whole new interface.

Though realize you can present your user interface inside the OH sitemap or HABPanel using a webview or frame element respectively.

As Rich already pointed out: you’ll have to build your own UI for that. You can easily use everything you like, within the UI you have to make sure with some kind of authentification, that I can only change the item once per lecture (or whatever you like). For changing the openHAB items you could just use the REST API (https://docs.openhab.org/configuration/restdocs.html) What I hear from my colleagues, who are actual developers (I’m not), that should be a no-brainer for a NodeJS application.