Feature Request: MySQL database

[CapnJackOff]

Over at Grafana they give you a free hosted instance. I have successfully used that to connect to a MySQL server somewhere else. I do like the idea of not running Grafana or the db on the pi, and MySQL is ubiquitous. If Solarthing could log data to MySQL (remotely) or use REST API, then I would use it that way for sure!

[retrodaredevil]

I've never used any SQL databases before, so I'm not super familiar with them. I assume it would be similar enough to InfluxDB so it may not be to difficult to add. I probably won't get around to implementing this for a while.

Can I ask why you don't want to use an InfluxDB database? Are you more familiar with SQL? Also, if you could explain how a uploading data via a REST API would work more in detail, I could better understand it.

Going forward, I want to push newer users towards using CouchDB with a GraphQL application running, but the documentation for that isn't quite there yet.

[CapnJackOff]

I don't know much about db languages but my understanding is that the query syntax is ALL different. So convenient...not. I can't run Influx on my shared hosting account like cPanel. You either have to pay Influx for hosting or install it on your own dedicated server box. My home network is not the greatest for hosting a db and Grafana so I want to get away from the pi doing everything. I think I could get better quality access to the data by having the whole thing remote.

I use REST API on PVOutput, but I guess there are tons of other ways to log there. I wrote a little PHP script that parses parameters of a GET request, so you if you request a page www.mywebpage.com/script.php?current=2.4&voltage=6.4&uptime=8 or something like that, it logs those parameters to a MySQL database.

I will have to experiment with GraphQL and the Rover. I don't really understand how that would work or if it's better than what I do now with Solarthing.

[retrodaredevil]

Ok, now I understand that a MySQL database would work better for you because it can be hosted for free, which is definitely a plus. If I want to support InfluxDB 2.0, I need to change up some code anyways, so if I get around to making that change, I can probably try and generalize that part of the code a bit more to support relational databases.

I think a REST API would be useful for simple stuff like that, but I worry that it would get more complicated when you try to do more advanced stuff with SolarThing. Since you only get data from a single Rover device, it makes sense for you to have something as simple as you described. However, SolarThing is set up to log data from multiple devices and I worry that simplifying it too much might make it harder to expand in the future. I think it's doable, I'm just not sure what it would look like.

The GraphQL program requires CouchDB to be set up. The advantage of the GraphQL program with CouchDB is that you can have Grafana queries that can do more stuff with the data. With InfluxDB (and SQL if I eventually support that), the data goes into the database, then you query that data unaltered. The advantage of SolarThing's GraphQL program is that it's sort of a middleman between the database and what you see in Grafana. An example of an advantage of this is being able to alter metadata or a display name after its data goes into the database, or you can do additional calculations like find the average power output in a certain hour.

My guess is that switching to CouchDB probably wouldn't be much better for you, and would probably be a hassle for you to switch, but it does have many advantages. And if you like writing your own custom scripts to interact with SolarThing, the GraphQL program basically exposes the CouchDB database as an api that's very simple to interface with, although it's mostly designed to be queried by the GraphQL datasource in Grafana.

Also, I'm curious: How are you uploading data to PVOutput right now with SolarThing? If you use CouchDB and the PVOutput upload program, SolarThing can do it for you. But I understand that CouchDB isn't as easy to setup, so I wouldn't be surprised if that's why you aren't using it.

Sorry for this wall of text, I'm just trying to be thorough, lol. Please let me know if any of this confused you.

[CapnJackOff]

I have not tried Solarthing with PVOutput, but I have been logging to PVOutput from my home grid-tie solar array since 2016. Join my team, Grape Solar! My inverter had this proprietary network thing which didn't work so I built a monitoring system from a Particle Photon and the mighty EmonLib libraries. It samples the current, voltage, calculates the 10 minute average, the cumulative daily whr count, and publishes events which allows me to hit a number of webhooks from the Particle network, log data to Google Sheets, PVOutput, and grab the weather, sunup and sunset times etc. Hence my fondness for REST API. Sounds like I could do a lot of that with GraphQL which is very cool! One other user of Solarthing here has already said they are more familiar with MySQL. It is the McDonald's of databases, everywhere.

[retrodaredevil]

I looked into MySQL a little more and I've learned a bit more about SQL in general, and I don't think SolarThing will ever support SQL databases out of the box. The reason is this: For most SQL databases, you have to have a set in stone schema for the database, which means that if SolarThing adds a field, something has to be able to update the table that's being used to store the data. InfluxDB gets around this by just being very lenient whenever you want to add a new field. So while it's completely possible for SolarThing to support MySQL, it's not something that I want to support.

If you did want data to go into a MySQL database, having SolarThing send data to a REST API as a middleman might work. The reason this is doable and SQL support isn't is because the program that you create could decide what data goes into your MySQL database, and you would be in charge of maintaining your MySQL database, rather than SolarThing.

Maybe SolarThing could do a post request like this: POST http://localhost:8080/solarthing, then it could have the body just be the JSON data of all the packets like this:

{
        "_id": "2020,12,19,16,(05/30),[389bbe83]",
        "packets": [
          {
            "packetType": "DEVICE_CPU_TEMPERATURE",
            "cpuTemperatureCelsius": 26.6,
            "cpuTemperatureFahrenheit": 79.880005
          },
          {
            "packetType": "TEMPERATURE",
            "dataId": 1,
            "source": {
              "type": "w1",
              "name": "28-0301a279ffb2"
            },
            "temperatureCelsius": 2.062,
            "temperatureFahrenheit": 35.7116
          },
          {
            "packetType": "TEMPERATURE",
            "dataId": 2,
            "source": {
              "type": "w1",
              "name": "28-0301a279f5ff"
            },
            "temperatureCelsius": 9.187,
            "temperatureFahrenheit": 48.5366
          },
          {
            "packetType": "RENOGY_ROVER_STATUS",
            "maxVoltage": 24,
            "ratedChargingCurrent": 40,
            "ratedDischargingCurrent": 20,
            "productType": 0,
            "productModelEncoded": "Uk5HLUNUUkwtUlZSUEc0MA==",
            "softwareVersion": 773,
            "hardwareVersionString": "V00.05.00",
            "productSerialNumber": 100,
            "controllerDeviceAddress": 1,
            "batteryCapacitySOC": 50,
            "batteryVoltage": 23.2,
            "chargingCurrent": 0.21,
            "controllerTemperatureRaw": 14,
            "batteryTemperatureRaw": 12,
            "loadVoltage": 0,
            "loadCurrent": 0,
            "loadPower": 0,
            "inputVoltage": 33.1,
            "pvCurrent": 0.15,
            "chargingPower": 5,
            "dailyMinBatteryVoltage": 21.2,
            "dailyMaxBatteryVoltage": 25.3,
            "dailyMaxChargingCurrent": 7.36,
            "dailyMaxDischargingCurrent": 0,
            "dailyMaxChargingPower": 154,
            "dailyMaxDischargingPower": 0,
            "dailyAH": 11,
            "dailyAHDischarging": 0,
            "dailyKWH": 0.257,
            "dailyKWHConsumption": 0,
            "operatingDaysCount": 153,
            "batteryOverDischargesCount": 200,
            "batteryFullChargesCount": 27,
            "chargingAmpHoursOfBatteryCount": 6894,
            "dischargingAmpHoursOfBatteryCount": 0,
            "cumulativeKWH": 42.577,
            "cumulativeKWHConsumption": 0,
            "streetLightValue": 0,
            "chargingState": 2,
            "errorMode": 1,
            "nominalBatteryCapacity": 200,
            "systemVoltageSetting": 24,
            "recognizedVoltage": 0,
            "batteryType": 0,
            "overVoltageThresholdRaw": 156,
            "chargingVoltageLimitRaw": 154,
            "equalizingChargingVoltageRaw": 151,
            "boostChargingVoltageRaw": 147,
            "floatingChargingVoltageRaw": 136,
            "boostChargingRecoveryVoltageRaw": 120,
            "overDischargeRecoveryVoltageRaw": 112,
            "underVoltageWarningLevelRaw": 112,
            "overDischargeVoltageRaw": 110,
            "dischargingLimitVoltageRaw": 110,
            "endOfChargeSOC": 100,
            "endOfDischargeSOC": 50,
            "overDischargeTimeDelaySeconds": 120,
            "equalizingChargingTimeRaw": 120,
            "boostChargingTimeRaw": 90,
            "equalizingChargingIntervalRaw": 0,
            "temperatureCompensationFactorRaw": 5,
            "operatingStage1": {
              "durationHours": 4,
              "operatingPowerPercentage": 100
            },
            "operatingStage2": {
              "durationHours": 0,
              "operatingPowerPercentage": 70
            },
            "operatingStage3": {
              "durationHours": 4,
              "operatingPowerPercentage": 50
            },
            "operatingMorningOn": {
              "durationHours": 0,
              "operatingPowerPercentage": 20
            },
            "loadWorkingMode": 15,
            "lightControlDelayMinutes": 5,
            "lightControlVoltage": 5,
            "ledLoadCurrentSettingRaw": 660,
            "specialPowerControlE021Raw": 5,
            "sensed1": {
              "workingHoursRaw": 4,
              "powerWithPeopleSensedRaw": 4,
              "powerWithNoPeopleSensedRaw": 4
            },
            "sensed2": {
              "workingHoursRaw": 4,
              "powerWithPeopleSensedRaw": 4,
              "powerWithNoPeopleSensedRaw": 4
            },
            "sensed3": {
              "workingHoursRaw": 4,
              "powerWithPeopleSensedRaw": 4,
              "powerWithNoPeopleSensedRaw": 4
            },
            "sensingTimeDelayRaw": 4,
            "ledLoadCurrentRaw": 4,
            "specialPowerControlE02DRaw": 4,
            "productModelString": "RNG-CTRL-RVRPG40",
            "softwareVersionString": "V00.03.05",
            "hardwareVersion": 1280,
            "streetLightBrightness": 0,
            "streetLightOn": false,
            "chargingStateName": "MPPT charging",
            "errors": "",
            "batteryTypeName": "user-unlocked",
            "loadWorkingModeName": "MANUAL"
          },
          {
            "packetType": "SOURCE",
            "sourceId": "default"
          },
          {
            "packetType": "FRAGMENT_INDICATOR",
            "fragmentId": 2
          }
        ],
        "dateMillis": 1608419285899
      }

Would this be useful to you? This is something that's a lot easier to do that to just start supporting MySQL. You could write a program that parses the JSON, finds the RENOGY_ROVER_STATUS packet, and then does whatever you want with the data (maybe puts the data in a MySQL database).

The point of this would be to avoid needing to have CouchDB installed. If you're already going to install CouchDB, then you could just run the GraphQL program alongside it and get data from it in your script.

[CapnJackOff]

Yes! My plan is then to use something like this to parse the JSON packet and insert the values into MySQL db. The example in the annoying video is pretty simple, but with a little modification it will be able to parse Solarthing packets. https://www.youtube.com/watch?v=MbWc0mdRa2g

[retrodaredevil]

Ok, I added a post database type. You can see the template file here.

To test this, you'll have to compile SolarThing with ./compile_and_move.sh. If the device you're using (Raspberry Pi) can't handle compiling it, let me know and I can do it for you.

[CapnJackOff]

So I should copy the template file into /opt/solarthing/config_templates/databases, then run ./compile_and_move.sh in /opt/solarthing? Then I will have to move the template file into /opt/solarthing/program/rover/config, like I did during setup, correct?

[retrodaredevil]

You should run git pull to get all the latest files, then create a file in program/rover/config with the contents of that template (copy, like you said), then reference that file in your base.json as a database. Then compile and run like you said.

[CapnJackOff]

Well, it's absolutely working. I set up a mock url in Postman and Solarthing is hitting it with JSON packets. I have to set up a php page to parse the JSON. It's so cool that I can have multiple databases in base.json because Solarthing will continue to log to InfluxDB while I try to get the SQL working. It will take this n00b a few days I think. I'll post the script back here when I finish it. Thanks!!

[CapnJackOff]

I'm pleased to report that Solarthing now uploads to MySQL courtesy of the super neat post feature, and solarthingMySQL.php, which is available here:

I only pulled about 30 of the values from the packet (there are almost 100) for writing to the db. I think all the important ones are in there, the rest are mostly settings. If someone thinks I missed an important one, I'll be happy to add it. The script will create a table, but not a database. You will have to do that manually. It's pretty simple in cPanel which is what I use. There is packetType validation which passes only for the Rover, so I doubt this would work for any other Solarthing program without modification which you are free to do. One thing that confuses me a little is the JSON packet I get doesn't look like the one posted above. I'm not sure why because I compiled with the latest files and I seem to remember the pi CPU update a while ago. The packet I pulled from Postman looks like this:

 "packets": [

           {

            "packetType": "RENOGY_ROVER_STATUS",
            "maxVoltage": 24,
            "ratedChargingCurrent": 40,
            "ratedDischargingCurrent": 20,
            "productType": 0,
            "productModelEncoded": "Uk5HLUNUUkwtUlZSUEc0MA==",
            "softwareVersion": 773,
            "hardwareVersionString": "V00.05.00",
            "productSerialNumber": 100,
            "controllerDeviceAddress": 1,
            "batteryCapacitySOC": 50,
            "batteryVoltage": 23.2,
            "chargingCurrent": 0.21,
            "controllerTemperatureRaw": 14,
            "batteryTemperatureRaw": 12,
            "loadVoltage": 0,
            "loadCurrent": 0,
            "loadPower": 0,
            "inputVoltage": 33.1,
            "pvCurrent": 0.15,
            "chargingPower": 5,
            "dailyMinBatteryVoltage": 21.2,
            "dailyMaxBatteryVoltage": 25.3,
            "dailyMaxChargingCurrent": 7.36,
            "dailyMaxDischargingCurrent": 0,
            "dailyMaxChargingPower": 154,
            "dailyMaxDischargingPower": 0,
            "dailyAH": 11,
            "dailyAHDischarging": 0,
            "dailyKWH": 0.257,
            "dailyKWHConsumption": 0,
            "operatingDaysCount": 153,
            "batteryOverDischargesCount": 200,
            "batteryFullChargesCount": 27,
            "chargingAmpHoursOfBatteryCount": 6894,
            "dischargingAmpHoursOfBatteryCount": 0,
            "cumulativeKWH": 42.577,
            "cumulativeKWHConsumption": 0,
            "streetLightValue": 0,
            "chargingState": 2,
            "errorMode": 1,
            "nominalBatteryCapacity": 200,
            "systemVoltageSetting": 24,
            "recognizedVoltage": 0,
            "batteryType": 0,
            "overVoltageThresholdRaw": 156,
            "chargingVoltageLimitRaw": 154,
            "equalizingChargingVoltageRaw": 151,
            "boostChargingVoltageRaw": 147,
            "floatingChargingVoltageRaw": 136,
            "boostChargingRecoveryVoltageRaw": 120,
            "overDischargeRecoveryVoltageRaw": 112,
            "underVoltageWarningLevelRaw": 112,
            "overDischargeVoltageRaw": 110,
            "dischargingLimitVoltageRaw": 110,
            "endOfChargeSOC": 100,
            "endOfDischargeSOC": 50,
            "overDischargeTimeDelaySeconds": 120,
            "equalizingChargingTimeRaw": 120,
            "boostChargingTimeRaw": 90,
            "equalizingChargingIntervalRaw": 0,
            "temperatureCompensationFactorRaw": 5,
            "operatingStage1": {
              "durationHours": 4,
              "operatingPowerPercentage": 100
            },
            "operatingStage2": {
              "durationHours": 0,
              "operatingPowerPercentage": 70
            },
            "operatingStage3": {
              "durationHours": 4,
              "operatingPowerPercentage": 50
            },
            "operatingMorningOn": {
              "durationHours": 0,
              "operatingPowerPercentage": 20
            },
            "loadWorkingMode": 15,
            "lightControlDelayMinutes": 5,
            "lightControlVoltage": 5,
            "ledLoadCurrentSettingRaw": 660,
            "specialPowerControlE021Raw": 5,
            "sensed1": {
              "workingHoursRaw": 4,
              "powerWithPeopleSensedRaw": 4,
              "powerWithNoPeopleSensedRaw": 4
            },
            "sensed2": {
              "workingHoursRaw": 4,
              "powerWithPeopleSensedRaw": 4,
              "powerWithNoPeopleSensedRaw": 4
            },
            "sensed3": {
              "workingHoursRaw": 4,
              "powerWithPeopleSensedRaw": 4,
              "powerWithNoPeopleSensedRaw": 4
            },
            "sensingTimeDelayRaw": 4,
            "ledLoadCurrentRaw": 4,
            "specialPowerControlE02DRaw": 4,
            "productModelString": "RNG-CTRL-RVRPG40",
            "softwareVersionString": "V00.03.05",
            "hardwareVersion": 1280,
            "streetLightBrightness": 0,
            "streetLightOn": false,
            "chargingStateName": "MPPT charging",
            "errors": "",
            "batteryTypeName": "user-unlocked",
            "loadWorkingModeName": "MANUAL"
          },
          {
            "packetType": "SOURCE",
            "sourceId": "default"
          },
          {
            "packetType": "FRAGMENT_INDICATOR",
            "fragmentId": 2
          }
        ],
        "dateMillis": 1608419285899
      }

[retrodaredevil]

The packets I posted above have the raspberry pi CPU temperature, and data from a temperature sensor. If you want the CPU temperature, you'll have to follow the updated RPi CPU Temperature docs. That got updated some time ago, so the extra_options or whatever I called that field a while back doesn't work with new versions of SolarThing anymore.