Tweaking V5..V12 parameters command

Topics: 5. Support
Sep 21, 2014 at 5:43 PM
First verry happy with the new version 3.01 Thanks! Everything works great but, want to see DC1 also on PVoutput. Where can I give the command CREATE VIEW vwPvoData AS in Putty. In what folder can I give this command. Thanks for your help...
Coordinator
Sep 22, 2014 at 7:04 AM
create a text file (e.g. myNewPVO.sql) containing your SQL query and import it using this command:
cd /home/pi/smadata
sqlite3 SBFspot.db < /home/pi/sbfspot.3/SBFspot/myNewPVO.sql
Marked as answer by SBF on 9/22/2014 at 3:49 AM
Sep 22, 2014 at 11:29 AM
Edited Sep 22, 2014 at 11:31 AM
Indeed, many thanks for this new version V3.01!!

But may I suggest: First create a text file, containing another SQL query:

DROP VIEW IF EXISTS vwPvoData;

This has to be done first since SQLite doesn't allow us to alter a view, it has to be deleted first ;-)

SBF wrote:
create a text file (e.g. myNewPVO.sql) containing your SQL query and import it using this command:
cd /home/pi/smadata
sqlite3 SBFspot.db < /home/pi/sbfspot.3/SBFspot/myNewPVO.sql
Sep 22, 2014 at 12:38 PM
Edited Sep 23, 2014 at 1:36 PM
I managed to do the change from AC to DC voltage and Inverter temp to Wunderground temp by using the CommandLineShell from de smadata directory after stopping the daemon first:

sudo killall SBFspotUploadDaemon
cd /home/pi/smadata
sqlite3 SBFspot.db

Then dropped the existing view:
sqlite> DROP VIEW IF EXISTS vwPvoData;

and created a new one:
sqlite> CREATE VIEW vwPvoData AS
...> SELECT dd.Timestamp,
...> dd.Name,
...> dd.Type,
...> dd.Serial,
...> dd.TotalYield AS V1,
...> dd.Power AS V2,
...> cons.EnergyUsed AS V3,
...> cons.PowerUsed AS V4,
...> NULL AS V5,
...> spot.Udc1 AS V6,
...> NULL AS V7,
...> NULL AS V8,
...> NULL AS V9,
...> NULL AS V10,
...> NULL AS V11,
...> NULL AS V12,
...> dd.PVoutput
...> FROM vwDayData AS dd
...> LEFT JOIN vwAvgSpotData AS spot
...> ON dd.Serial = spot.Serial AND dd.Timestamp = spot.Nearest5min
...> LEFT JOIN vwAvgConsumption AS cons
...> ON dd.Timestamp = cons.Nearest5min
...> ORDER BY dd.Timestamp DESC;
sqlite> .quit

(the yellow highlight slightly disturbed an integral copy-paste from <SBFspot V3 Quick Reference Linux EN 1.0.PDF> so I copy-pasted all the above lines in one go from notepad).

In between I verified with the .tables command that the drop and create had actually worked.
Then restarted the RPi and the two changes now show up in my PVoutput graphs.

(Not sure whether it is really necessary to stop the daemon first but that did the trick for me after a first tweak attempt failed)
Coordinator
Sep 22, 2014 at 2:13 PM
Snowmiss wrote:
But may I suggest: First create a text file, containing another SQL query:
Better (or easier) is to put this in the same file:
DROP VIEW IF EXISTS vwPvoData;
CREATE VIEW....
Sep 23, 2014 at 11:30 AM
Aha, I am not that familiar with SQL but learning every day.
Sep 24, 2014 at 5:25 PM
Thanks all for your help. It works great I see te DC Voltage and DCwatt on PVoutput !