Installation Linux SQLite

Translation by Google: [NL] - [FR] - [DE] - [ES] - [IT]

SBFspot with SQLite

SQLite is a software library that implements a self-contained, serverless, zeroconfiguration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.
SQLite is used on Android smartphones and in popular software such as Firefox, Google Chrome. It is the preferred choice for use with SBFspot.

⭐️ sbfspot-config

If you're planning to run SBFspot on Raspberry Pi, you can skip this installation manual and just run sbfspot-config
Run this command on your Pi and follow the self-explaining menu:

sudo bash -c "$(curl -s https://raw.githubusercontent.com/sbfspot/sbfspot-config/master/sbfspot-config)"

It runs as root, so you might want to take a look at the script first.

⭐️ Installation

Update package lists:

sudo apt update

Install required build packages:

sudo apt -y install make g++

Install Bluetooth (required, even with Speedwire):

sudo apt -y --no-install-recommends install bluetooth libbluetooth-dev

Install boost:

• Required libraries only (Recommended):

sudo apt -y install libboost-date-time-dev libboost-system-dev libboost-filesystem-dev libboost-regex-dev

• Complete build system (Optional - not needed if above libraries are installed):

sudo apt -y install libboost-all-dev

Install SQLite:

sudo apt -y install sqlite3 libsqlite3-dev

Create directories:

cd ~
mkdir smadata
mkdir SBFspot
sudo mkdir /var/log/sbfspot.3
sudo chown -R $USER:$USER /var/log/sbfspot.3

Download sourcecode to your Raspberry Pi:
Take a look here for the latest version and set sbfspot_version variable accordingly:

sbfspot_version=3.9.7
wget –c https://github.com/SBFspot/SBFspot/archive/V$sbfspot_version.tar.gz

Untar the sourcecode:

tar -xvf V$sbfspot_version.tar.gz -C SBFspot --strip-components 1

If you want to build the latest master branch:

sudo apt -y install git
git clone https://github.com/SBFspot/SBFspot.git

Compile SBFspot with SQLite support:

cd ~/SBFspot/SBFspot
make sqlite

Install SBFspot in /usr/local/bin/sbfspot.3:

sudo make install_sqlite

⭐️ Database setup

cd ~/smadata
sqlite3 SBFspot.db < ~/SBFspot/SBFspot/CreateSQLiteDB.sql

This should create ~/smadata/SBFspot.db
As a quick test, you can already execute a simple query:

sqlite3 SBFspot.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> select * from config;
SchemaVersion|1
sqlite>.quit

⭐️ Configuration

For new installations, copy or rename SBFspot.cfg.default to SBFspot.cfg

cd /usr/local/bin/sbfspot.3
sudo cp SBFspot.default.cfg SBFspot.cfg

Edit the config file:

sudo nano SBFspot.cfg

Following configuration options have to be modified – The others can keep their default values.

• BTAddress : Bluetooth address of device closest to Raspberry Pi (Use hcitool scan to find it)
  If you have more than one inverter, each inverter should have the same NetID > 1 and Config option MIS_Enabled=1

❗ Make sure to disable IP_Address with # in front.

• IP_Address : Fixed IP address for Speedwire connected devices. If you have more than one inverter, enter the comma separated IP-addresses. MIS_Enabled is only used for Bluetooth

❗ Make sure to disable BTaddress with # in front.

• Password : Password for user – default is 0000
• Plantname : Name for your installation
• Latitude and Longitude : co-ordinates of your installation, used for sunrise and sunset calculation. https://www.gps-coordinates.net/maps
• Timezone : See date_time_zonespec.csv for a valid timezone or type cat /etc/timezone

❗ Following paths are hardcoded for user pi. Change this if required.

• SQL_Database : Path to SQLite database – default /home/pi/smadata/SBFspot.db
• OutputPath : Path to CSV files – default /home/pi/smadata/%Y
• OutputPathEvents : Path to CSV files for inverter events – default /home/pi/smadata/%Y/Events

⭐️ Testing

/usr/local/bin/sbfspot.3/SBFspot -v -finq -nocsv

Now we can check if there is something in the db (don’t forget the ‘;’ at the end of each query):

cd ~/smadata
sqlite3 SBFspot.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from vwspotdata;
2014-08-26 10:32:08|2014-08-26 10:30:00|SB4000TL|SB 4000TL-20|2100276197|127|56|0.37|0.339|345.3|166.73|178|0|0|0.769|0.0|0.0|232.98|0.0|0.0|183|178|0.0|332|14969473|49.96|15967.7|15502.0|64.7059|OK|Closed|36.84
sqlite> select * from inverters;
2100276197|SB4000TL|SB 4000TL-20|03.01.05.R|1409041928|178|0.332|14969.5|15967.7|15502.0|OK|Closed|0.0
sqlite> .quit

❗ Make sure you are in the ~/smadata directory (or provide the fullpath to the db) If not, you will create an empty db

⭐️ Automation

To collect data, SBFspot should run every 5 minutes from 06:00 till 22:55 and once a day (05:55) for month data and events. To achieve this, create two scripts and cronjobs:

cd /usr/local/bin/sbfspot.3
sudo nano daydata

#!/bin/bash
#
log=/var/log/sbfspot.3/MyPlant_$(date '+%Y%m%d').log
/usr/local/bin/sbfspot.3/SBFspot -v -ad1 -am0 -ae0 >>$log

sudo nano monthdata

#!/bin/bash
#
log=/var/log/sbfspot.3/MyPlant_$(date '+%Y%m').log
/usr/local/bin/sbfspot.3/SBFspot -v -sp0 -ad0 -am1 -ae1 -finq >>$log

Make scripts executable:

sudo chmod +x daydata
sudo chmod +x monthdata

Create cron jobs:

crontab -e

## Avoid "No MTA installed, discarding output" syslog messages
MAILTO=""

## SBFspot
*/5 6-22 * * * /usr/local/bin/sbfspot.3/daydata
55 05 * * * /usr/local/bin/sbfspot.3/monthdata

SBFspotUploadDaemon with SQLite

To upload your data to PVoutput you need a (free) account and enable API access in the settings.

⭐️ Installation

Install Curl:

sudo apt -y install libcurl4-openssl-dev

Compile SBFspotUploadDaemon with SQLite support:

cd ~/SBFspot/SBFspotUploadDaemon
make sqlite

Install SBFspotUploadDaemon in /usr/local/bin/sbfspot.3:

sudo make install_sqlite

⭐️ Configuration

For new installations, copy or rename SBFspotUpload.cfg.default to SBFspotUpload.cfg

cd /usr/local/bin/sbfspot.3
sudo cp SBFspotUpload.default.cfg SBFspotUpload.cfg

Edit the config file:

sudo nano SBFspotUpload.cfg

Following configuration options have to be modified.

• PVoutput_SID : Serial number of inverter and PVoutput System ID
  Example: 200212345:4321
  If you have more than one inverter, you'll need a PVoutput System ID for each inverter. Separate all systems with a comma.
  Example: 200212345:4321,200223456:4322
  ❗ Don't use the same System ID for multiple inverters, or data of this system will be overwritten each time.
• PVoutput_Key : Your personal API key
• SQL_Database : Path to SQLite db – Must be the same as in SBFspot.cfg
• LogDir : Path for logfiles /var/log/sbfspot.3

Create a service file:

sudo nano /usr/local/bin/sbfspot.3/SBFspotUpload.service

Add these lines:

[Unit]
Description=SBFspot Upload Daemon
After=mysql.service mariadb.service network.target

[Service]
User=pi
Type=simple
TimeoutStopSec=10
ExecStart=/usr/local/bin/sbfspot.3/SBFspotUploadDaemon
Restart=on-success
RestartSec=10

[Install]
WantedBy=multi-user.target

• When running daemon as a user other than pi, make sure you change the line User=pi accordingly.
• If SBFspotUpload.cfg file is located in another directory, adapt the command line (e.g. -c /etc/SBFspotUpload.cfg)

Enable & start the service:

sudo systemctl enable /usr/local/bin/sbfspot.3/SBFspotUpload.service
sudo systemctl start SBFspotUpload

Check status:

sudo systemctl status SBFspotUpload

● SBFspotUpload.service - SBFspot Upload Daemon
   Loaded: loaded (/usr/local/bin/sbfspot.3/SBFspotUpload.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2018-04-11 22:53:11 CEST; 4s ago
  Process: 11521 ExecStart=/usr/local/bin/sbfspot.3/SBFspotUploadDaemon (code=exited, status=0/SUCCESS)
 Main PID: 11522 (SBFspotUploadDa)
   CGroup: /system.slice/SBFspotUpload.service
           └─11522 /usr/local/bin/sbfspot.3/SBFspotUploadDaemon

Apr 11 22:53:11 raspberrypi systemd[1]: Starting SBFspot Upload Daemon...
Apr 11 22:53:11 raspberrypi systemd[1]: Started SBFspot Upload Daemon.

⭐️ Testing

If all goes well, you should see a logfile in /var/log/sbfspot.3:

[23:37:26] INFO: : Starting...
[23:37:57] INFO: : Uploading 30 datapoints, starting with 20140611,12:10,13775864,2256 => OK (200)
[23:39:36] INFO: : Uploading 30 datapoints, starting with 20140611,14:40,13782249,3432,,,62.94,234.95 => OK (200)
[23:41:14] INFO: : Uploading 30 datapoints, starting with 20140611,17:10,13788507,2736,,,60.94,236.69 => OK (200)
[23:42:51] INFO: : Uploading 25 datapoints, starting with 20140611,19:40,13793775,1704,,,54.31,237.53 => OK (200)

A few minutes later, your PVoutput graphs should be updated.

⭐️ Tweaking

By default, Inverter Temperature (V5) and Uac1 (V6) are uploaded to PVoutput. This can be changed relatively easy by modifying the view used for PVoutput data upload.

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,
        spot.Temperature AS V5,
        spot.Uac1 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;

Suppose we want ambient temperature (Weather Underground) instead of inverter temperature and DC voltage instead of AC. In the example below this is achieved by replacing the data for V5 and V6.

cd ~/smadata
sqlite3 SBFspot

Since SQLite doesn’t allow us to alter a view, we have to delete it first:

DROP VIEW IF EXISTS vwPvoData;

Then, recreate the view with the changes:

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,      -- Don't upload internal inverter temperature 
        spot.Udc1 AS V6, -- DC Voltage String 1 instead of AC mains
        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; 

❗ You can only change the V5..V12 parameters. V1..V4 are fixed. Also, make sure all V1..V12 are present.

When in PVoutput donation mode, you can make use of V7..V12 (BTW, I'm not affiliated with PVoutput)

The next example shows the query to upload Udc1(V7), Udc2(V8), Pdc1(V9) and Pdc2(V10)

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.Uac1 AS V6,
        spot.Udc1 AS V7,  -- DC Voltage String 1
        spot.Udc2 AS V8,  -- DC Voltage String 2
        spot.Pdc1 AS V9,  -- DC Power String 1
        spot.Pdc2 AS V10, -- DC Power String 2
        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; 

⭐️ Join the Team

• Go to the SBFspot Team page on PVoutput
• Click Join or Leave Team link
• Select each system and click Join button

⭐️ Clean up

If everything works fine, delete the source files to recover some diskspace:

cd ~
rm -r SBFspot
rm V$sbfspot_version.tar.gz

❗ When running for a few months, the log directory will get pretty full. Until now I cleaned this manually, but I found a nice command here to automate the cleaning. The line below creates a scheduled task cron at 01:00 to delete all logs older than 1 week:

(crontab -l ; echo '0 1 * * * find /var/log/sbfspot.3/ -name "*.log" -mtime +7 -delete')|crontab -

❗ As from SBFspot V3.9.6, the above cleaning of log files is not needed anymore.
Syslog is used instead. Check with journalctl -r -u SBFspotUpload

⭐️ Remarks

In some circumstances, auto-detection of inverters won't work. If this is the case, you can use a config file for each inverter. Change BTAddress or IP_Address and the plantname in each config and run SBFspot for every inverter.

#SB4000.cfg
BT_address=80:ab:cd:ef:00:01
PlantName=SB4000

#SB3600.cfg
BT_address=80:ab:cd:ef:00:02
PlantName=SB3600

Run SBFspot for every inverter with -cfg argument

SBFspot -v -cfgSB4000.cfg
SBFspot -v -cfgSB3600.cfg

❗ Run SBFspot -? to explore all other arguments

See also:

HowTo add Ambient Temperature to your PVoutput.org graphs
PVoutput User Community
Zonstraal Forum (Dutch)