Write to database options

[Michael McSharry]

What I posted in this thread is for your testing with HS4. There is also the myself dll that you will likely need that was also mentioned in this thread.

[Dweber85rc]

Ok, cool. I'll be happy to do some testing as soon as you have a version ready

[Michael McSharry]

I creates them. You just need to have mySQL server available.

[Dweber85rc]

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

[Michael McSharry]

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.

[Michael McSharry]

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.



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

[Michael McSharry]

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

[Dweber85rc]

Thank you.

[Michael McSharry]

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

[Dweber85rc]

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

[kenm]

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.

[Dweber85rc]

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

[Michael McSharry]

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.

[Dweber85rc]

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.

[kenm]

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.