Announcement

Collapse
No announcement yet.

Write to database options

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Write to database options

    Wondering if it would be possible to add support for writing values to a mysql/mariadb instance? I already have a mysql/mariadb server on an rpi on my network so wondering what would be possible without adding a separate server for influxdb since the other application using mysql does not support influxdb. Also wondering about how/if there would be a way for a non mcsmqtt device to write to said database through mscmqtt?

    #2
    I use mySQL and MS SQLServer for mcsSprinklers so that part is easy to add. My thinking is that existing UI for external database would remain intact and there would be a global setting for which external database would be used. Is this something like you were thinking?

    Also wondering about how/if there would be a way for a non mcsmqtt device to write to said database through mscmqtt?
    I do not understand what is being requested. Currently mcsMQTT will write changed DeviceValues to an internal (SQLite) and/or external (InfluxDB) database for any HS device that has been selected for routing to the database. There is no dependency on a device being created by mcsMQTT. There is also a history capability to store MQTT messages to internal database. Obviously MQTT messages need to be routed through mcsMQTT to accomplish this.

    mcsMQTT also supports a scripting interface so that anything that is being done by mcsMQTT can also be done using scripting.

    Can you clarify your request?

    Comment


      #3
      Originally posted by Michael McSharry View Post
      I use mySQL and MS SQLServer for mcsSprinklers so that part is easy to add. My thinking is that existing UI for external database would remain intact and there would be a global setting for which external database would be used. Is this something like you were thinking?



      I do not understand what is being requested. Currently mcsMQTT will write changed DeviceValues to an internal (SQLite) and/or external (InfluxDB) database for any HS device that has been selected for routing to the database. There is no dependency on a device being created by mcsMQTT. There is also a history capability to store MQTT messages to internal database. Obviously MQTT messages need to be routed through mcsMQTT to accomplish this.

      mcsMQTT also supports a scripting interface so that anything that is being done by mcsMQTT can also be done using scripting.

      Can you clarify your request?
      For the first question, Yes that is what I was inquiring about. Similar to the options currently to write to SQLite or an external InfluxDB.
      For your 2nd question I think your explanation answered my question. I was looking to be able to send, for example, energy data from a zwave outlet to this mysql database as well, which from your explanation sounds like it is possible. My goal here is to eventually get Granfana going, which can use mysql data sources, and again try to avoid having a separate database server for this since I have one that is pretty much snoozing all the time.

      Comment


        #4
        Dweber85rc - For energy data, you should consider using a time-series centric DB such as InfluxDB. MySQL is a relational database and while it can store TS data, the queries can be a bit more complex. I run MySQL, Django, Apache, InfluxDB, and Grafana all on the same Intel Atom box with no performance issues.

        Just my 2 cents.
        "if I have seen further [than others], it is by standing on the shoulders of giants." --Sir Isaac Newton (1675)

        Comment


          #5
          Originally posted by kenm View Post
          Dweber85rc - For energy data, you should consider using a time-series centric DB such as InfluxDB. MySQL is a relational database and while it can store TS data, the queries can be a bit more complex. I run MySQL, Django, Apache, InfluxDB, and Grafana all on the same Intel Atom box with no performance issues.

          Just my 2 cents.
          Thanks for the tip, I did not know that about the databases. It isn't all just energy data though that I will be tracking, although initially that is what my intent would be to get started.

          Comment


            #6
            I am a novice with InfluxDB, but I have not observed any difference in ability to query data from it vs. a traditional relational database as long as one of the fields in the database is a timestamp of when the record was inserted or updated. I know that InfluxDB 2.x and flux query language has additional capabilities beyond the InfluxDB 1. x and other databases SQL, but since I was never lacking in ability to do what I wanted with SQL, I have never tried to look at what else flux query can do.

            Comment


              #7
              I saw in the mcsmqtt change log there was a change for databases, is there a version you would like me to test?

              Comment


                #8
                Originally posted by Michael McSharry View Post
                I am a novice with InfluxDB, but I have not observed any difference in ability to query data from it vs. a traditional relational database as long as one of the fields in the database is a timestamp of when the record was inserted or updated. I know that InfluxDB 2.x and flux query language has additional capabilities beyond the InfluxDB 1. x and other databases SQL, but since I was never lacking in ability to do what I wanted with SQL, I have never tried to look at what else flux query can do.
                I agree with you in the case where mcsMQTT is the only one writing the data into the database. In the past, I've had bad experiences when records are coming from various sources and the timestamp was not in the same format nor all using UTC. To be fair, this wasn't really the fault of the DB but more of the implementation. A common library of functions to manage DB writes would have solved the formatting issues. I think this is what Influx does when it comes to timestamps in a series.
                "if I have seen further [than others], it is by standing on the shoulders of giants." --Sir Isaac Newton (1675)

                Comment


                  #9
                  So is this something you are planning on adding or not? If not it's fine, just want to know so I can go a different direction if that is the case

                  Comment


                    #10
                    I have it implemented for mySQL and SQL Server. Just need to test now. Should be done in a day or two.

                    Comment


                      #11
                      Thank you.

                      Comment


                        #12
                        I cleared my backlog and now will be able to start testing the mySQL implementation tomorrow. I did post the required dll that supports mySQL. When I do post the mcsMQTT dll you will also need to capture this support file. Updates are at https://forums.homeseer.com/forum/hs...ge-log-hs4-hs3

                        Comment


                          #13
                          The HS4 version of the plugin that supports multiple external databases is attached for your evaluation. I have not tested SQL Server. I am now going to port the implementation to HS3 plugin.

                          A radio has been added to the top of the History page for long term database to select among the four available. I suggest completing the database parameters of Database name, Table name, Username and Password before selecting MySQL as the database so that when the database is created it will have all the info it needs. It should not need a plugin restart, but if you have issue then give that a try.

                          I have tested storage of DeviceValue to the database. Reading the table of values for a given date range. Charting for a Device for a date range.

                          Click image for larger version  Name:	Capture1.JPG Views:	0 Size:	43.6 KB ID:	1535944

                          When viewed from MySQL Workbench the following schema is what should be expected to be created by the plugin.

                          Click image for larger version  Name:	Capture.JPG Views:	0 Size:	40.8 KB ID:	1535941Click image for larger version  Name:	Capture.JPG Views:	0 Size:	40.8 KB ID:	1535943

                          Comment


                            #14
                            The setup I used for my testing is a mySQL install on the same computer as HS. I also added an additional field later "Ref". For mySQL I forced all schema names to be lower case, but the data in table can be any case. All additional fields are handled as text even if they may be a number.

                            Click image for larger version

Name:	Capture2.JPG
Views:	193
Size:	54.5 KB
ID:	1535956
                            Click image for larger version

Name:	Capture3.JPG
Views:	194
Size:	46.7 KB
ID:	1535957

                            Comment


                              #15
                              Thank you. Does this create the tables on first run or do those need to be set up manually first?

                              Comment

                              Working...
                              X