Help for Domoticz integration

[aldodemi]

Need help (little understanding of the use of databases):

Before the breakdown by month I used these commands to extract data and put them in Domoticz:

#!/bin/sh
result=mysql -u UTILISATEUR -pMOTDEPASSE torque -N -e "SELECT kff126b FROM raw_logs ORDER BY time DESC LIMIT 1"
echo $result
kmrestant=mysql -u UTILISATEUR -pMOTDEPASSE torque -N -e "SELECT kff126a FROM raw_logs ORDER BY time DESC LIMIT 1"
echo $kmrestant
curl -g "http://192.168.0.21:8080/json.htm?type=command&param=udevice&idx=376&nvalue=0&svalue=$result"
curl -g "http://192.168.0.21:8080/json.htm?type=command&param=udevice&idx=377&nvalue=0&svalue=$kmrestant"

Please can you help me to convert using new DB structure?
Thank
Aldo

[surfrock66]

Hmm, I don't know Domoticz, but the jist is rather than one "raw_logs" table, the table names are appended with the year and month. Because of that, this would become a loop with a 2 part process; the first would be getting a list of tables whose names are like "raw_logs_", then doing a for-each through them. Here's the format of the table list:

Database changed
mysql> show tables;
+----------------------+
| Tables_in_hda_torque |
+----------------------+
| raw_logs_2014_10     |
| raw_logs_2014_11     |
| raw_logs_2014_12     |
| raw_logs_2015_01     |
| raw_logs_2015_02     |
| raw_logs_2015_03     |
| raw_logs_2015_04     |
| raw_logs_2015_05     |
| raw_logs_2015_06     |
| raw_logs_2015_07     |
| raw_logs_2015_08     |
| raw_logs_2015_09     |
| raw_logs_2015_10     |
| raw_logs_2015_11     |
| raw_logs_2015_12     |
| raw_logs_2016_01     |
| raw_logs_2016_02     |
| raw_logs_2016_03     |
| raw_logs_2016_04     |
| raw_logs_2016_05     |
| raw_logs_2016_06     |
| raw_logs_2016_07     |
| raw_logs_2016_08     |
| raw_logs_2016_09     |
| raw_logs_2016_10     |
| raw_logs_2016_11     |
| raw_logs_2016_12     |
| raw_logs_2017_01     |
| raw_logs_2017_02     |
| raw_logs_2017_03     |
| raw_logs_2017_04     |
| raw_logs_2017_05     |
| raw_logs_2017_06     |
| raw_logs_2017_07     |
| raw_logs_2017_08     |
| raw_logs_2017_09     |
| raw_logs_2017_10     |
...

So, if you ran that in the following way, you get a pretty clean list of tables:

mysql -u root hda_torque -N -e "SHOW TABLES" | grep -e "raw_logs_"

From there, you could for-each them, swapping in the table name; I'm not sure if you need them all at once, or if you're running it once a month at the end or something, but that should get you code that will work with the split tables. Does that help?