Application for Web-IO and pure.box
Store Web-IO switching states in MariaDB
with Golang on the pure.box
This tutorial shows a simple Go program for the pure.box. Using the REST interface of a Web-IO its switching states are read out and stored in the MariaDB on the pure.box.
Preparation
1. Activate database on the pure.box
2. Set up database
The Maria-DB on the pure.box contains a preconfigured but empty database named userdb from which the device user can be accessed.
CREATE TABLE `iostates` (
`id` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`in0` int(11) NOT NULL,
`in1` int(11) NOT NULL,
`out0` int(11) NOT NULL,
`out1` int(11) NOT NULL,
`count0` int(11) NOT NULL,
`count1` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;
To create this database scheme in the database of your purebox, use the following command:
mysql -u username -h box-ip -p userdb userdb.sql
or use a tool such as phpMyAdmin or the Mysql Workbench.
3. Install MySQL driver for Go
To access the database of the pure.box from Go, first a database driver is needed:
go get github.com/go-sql-driver/mysql
4. Activate REST interface of the Web-IO
Short paragraph
Short and sweet: REST and JSON
The Web-IO 4.0 is equipped with a REST interface which provides system information and the current states and counter states in various data formats. Since Golang inherently supports XML, JSON and Plaintext and JSON is more readable than XML at least for smaller data records, we will refer to JSON in this guide. More detailed information about REST support by the Web-IOs can be found in the operating and programming manuals.
The states of the Web-IO can be retrieved using the following URL:
http://ADRESSE/rest/json/iostate
The reply (without header data) is structured as follows:
{"iostate": {
"input": [{
"number": 0,
"state": 0
}, {
"number": 1,
"state": 0
}],
"output": [{
"number": 0,
"state": 0
}, {
"number": 1,
"state": 0
}],
"counter": [{
"number": 0,
"state": 0
}, {
"number": 1,
"state": 0
}]
}
}
The Go program
The packageencoding/json reads in a JSON string and represents it as types. Therefore, first types are defined which represent the structure of the JSONs:
package main
//Result ist the root element of the JSOBN
type Result struct {
Iostate IOState `json:"iostate"`
}
//IOState keeps collections of a device’s inputs, outputs and counters
type IOState struct {
Input []IO `json:"input"`
Output []IO `json:"output"`
Counter []IO `json:"counter"`
}
//IO keeps the number if an IO and its current state
type IO struct {
Number int `json:"number"`
State int `json:"state"`
}
Two things must be considered here: When importing, relevant fields begin with a capital letter as per the GO convention. The JSON flags explicitly assign a JSON identifier to a field.
Reading out the Web-IO via HTTP
To read out the I/O states using REST, three packets are imported in the file header:
import(
"encoding/json"
"io/ioutil"
"net/http"
)
encoding/json: This packet provides the routines for quickly and elegantly parsing JSON.
io/ioutil: This packet helps to write the entire data of a reader to a byte slice
net/http: This provides the necessary HTTP Get function
IP and Data URL are provided as global variables:
var(
WebIOIP string = "10.40.38.100"
WebIODataURL string = "http://" + WebIOIP + "/rest/json/iostate"
)
The function getWebIOStates queries the current states using REST and returns a Result-type from the jsontypes.go:
func getWebIOStates() Result {
//get HTTP response via REST
response, err := http.Get(WebIODataURL)
if err != nil {
log.Fatal(err)
}
defer response.Body.Close()
//use ioutil to get json data as byte slice from response
jsondata, err := ioutil.ReadAll(response.Body)
//use encoding/json to unmarshal JSON
var result Result
err = json.Unmarshal(jsondata, &result)
if err != nil {
log.Fatal("Could not decode JSON!", err)
}
return result
}
Line 3: The states are queried via
http.Get().Line 7: The keyword
deferensures that the response resource is enabled when ending the function.Line 10:
ioutil.ReadAll()returns the HTTP body as a byte slice.Line 14:
json.Unmarshall()parsesjsondataand writes the result toresult.
Linking to the database
Linking to the database requires the packet database/sql and the previously downloaded driver. The type of incorporation may seem somewhat confusing at first: the packet database/sql/Driver provides an interface. As soon as an implementation of it is imported, it can database/sql use this.
But since this driver is normally no longer accessed in the source code of a program, the Go compiler throws an error. The solution is the "side-effect import," with which the packet is renamed to the empty identifier ’_’ when importing.
import(
"encoding/json"
"io/ioutil"
"net/http"
"database/sql
_ "github.com/go-sql-driver/mysql"
)
A few parameters are also required for connecting the pure.box:
var (
PureBoxIP string = "192.168.100.25"
PureBoxDBPort string = "3306"
PureBoxDBName string = "userdb"
PureBoxUser string = "admin"
PureBoxPassword string = ""
WebIOIP string = "192.168.100.20"
WebIODataURL string = "http://" + WebIOIP + "/rest/json/iostate"
)
Now the function which writes the values to the database can be implemented:
func writeDatabase(webiodata Result) {
//Shorten variable names for the IO states
i0 := webiodata.Iostate.Input[0].State
i1 := webiodata.Iostate.Input[0].State
o0 := webiodata.Iostate.Output[0].State
o1 := webiodata.Iostate.Output[0].State
c0 := webiodata.Iostate.Counter[0].State
c1 := webiodata.Iostate.Counter[0].State
//Create dsn
dsn := PureBoxUser + ":" + PureBoxPassword +
"@" + "tcp(" + PureBoxIP + ":" + PureBoxDBPort + ")" +
"/" + PureBoxDBName
//Connect to database
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatal("No database connection!", err)
}
defer db.Close()
//insert data
sqlstring := "insert into iostates (in0, in1, out0, out1, count0, count1) values(?,?,?,?,?,?)"
_, err = db.Query(sqlstring, i0, i1, o0, o1, c0, c1)
if err != nil {
log.Fatal("Could not write to Database! ", err)
}
}
Lines 4-9: Here local variables are stored an abbreviated form for receiving states.
Lines 12-14: The Data-Source-Name is used for connecting to the database. In this example it is:
admin:foo@192.168.100.25:3306/userdbLines 17-19: Here the database connection is opened and closed when the function is quit.
Lines 24-28: Here follows the entry in the database. The function
db.Query()replaces the ? in the SQL string with the transferred parameters.
The main program
For full implementation only one parameter is still missing: the time between two queries:
var (
LoggingIntervall time.Duration = 10 * time.Second
PureBoxIP string = "192.168.100.25"
PureBoxDBPort string = "3306"
PureBoxDBName string = "userdb"
PureBoxUser string = "admin"
PureBoxPassword string = "foo"
WebIOIP string = "192.168.100.20"
WebIODataURL string = "http://" + WebIOIP + "/rest/json/iostate"
)
The main program is uncomplicated:
func main() {
for {
iostates := getWebIOStates()
writeDatabase(iostates)
time.Sleep(LoggingIntervall)
}
}
Line 2: A
forwithout conditions introduces an endless loop.Line 3: In this line first the IOs are read out
Line 4: then written to the database
Line 5: and finally before another loop pass the system waits for a
LoggingIntervall.
The complete program
As a complete source text we then have:
//purebox_webio project main.go
package main
import (
"log"
"time"
"encoding/json"
"io/ioutil"
"net/http"
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
var (
LoggingIntervall time.Duration = 10 * time.Second
PureBoxIP string = "10.40.38.10"
PureBoxDBPort string = "3306"
PureBoxDBName string = "userdb"
PureBoxUser string = "admin"
PureBoxPassword string = ""
WebIOIP string = "10.40.38.100"
WebIODataURL string = "http://" + WebIOIP + "/rest/json/iostate"
)
//Result ist the root element of the JSOBN
type Result struct {
Iostate IOState `json:"iostate"`
}
//IOState keeps collections of a device’s inputs, outputs and counters
type IOState struct {
Input []IO `json:"input"`
Output []IO `json:"output"`
Counter []IO `json:"counter"`
}
//IO keeps the number if an IO and its current state
type IO struct {
Number int `json:"number"`
State int `json:"state"`
}
func getWebIOStates() Result {
//get HTTP response via REST
response, err := http.Get(WebIODataURL)
if err != nil {
log.Fatal(err)
}
defer response.Body.Close()
//use ioutil to get json data as byte slice from response
jsondata, err := ioutil.ReadAll(response.Body)
//use encoding/json to unmarshal JSON
var result Result
err = json.Unmarshal(jsondata, &result)
if err != nil {
log.Fatal("Could not decode JSON!", err)
}
return result
}
func writeDatabase(webiodata Result) {
//Shorten IO names fore readability
i0 := webiodata.Iostate.Input[0].State
i1 := webiodata.Iostate.Input[0].State
o0 := webiodata.Iostate.Output[0].State
o1 := webiodata.Iostate.Output[0].State
c0 := webiodata.Iostate.Counter[0].State
c1 := webiodata.Iostate.Counter[0].State
//Create dsn
dsn := PureBoxUser + ":" + PureBoxPassword +
"@" + "tcp(" + PureBoxIP + ":" + PureBoxDBPort + ")" +
"/" + PureBoxDBName
//Connect to database
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatal("No database connection!", err)
}
defer db.Close()
//insert data
sqlstring := "insert into iostates (in0, in1, out0, out1, count0, count1) values(?,?,?,?,?,?)"
_, err = db.Query(sqlstring, i0, i1, o0, o1, c0, c1)
if err != nil {
log.Fatal("Could not write to Database! ", err)
}
}
func main() {
// Never stop doing
for {
iostates := getWebIOStates()
log.Println(iostates)
writeDatabase(iostates)
time.Sleep(LoggingIntervall)
}
}