Felix Niederwanger
d534856fb6
Add a storage module to store all received geopoints into a sqlite3 database.
84 lines
1.9 KiB
Go
84 lines
1.9 KiB
Go
package main
|
|
|
|
import (
|
|
"database/sql"
|
|
|
|
_ "github.com/mattn/go-sqlite3"
|
|
)
|
|
|
|
type Storage struct {
|
|
db *sql.DB
|
|
}
|
|
|
|
func (stor *Storage) init() error {
|
|
// initialization
|
|
sql_table := `
|
|
CREATE TABLE IF NOT EXISTS geopoints (
|
|
device VARCHAR(256), timestamp INTEGER, lon REAL, lat REAL, alt REAL, acc REAL, velocity REAL, bearing REAL, PRIMARY KEY(device, timestamp)
|
|
);
|
|
`
|
|
tx, err := stor.db.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.Exec(sql_table); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func (stor *Storage) IsReady() bool {
|
|
return stor.db != nil
|
|
}
|
|
|
|
func CreateDatabase(filename string) (Storage, error) {
|
|
var stor Storage
|
|
var err error
|
|
stor.db, err = sql.Open("sqlite3", filename)
|
|
if err != nil {
|
|
return stor, err
|
|
}
|
|
|
|
return stor, stor.init()
|
|
}
|
|
|
|
func (stor *Storage) InsertLocation(device string, loc Location) error {
|
|
tx, err := stor.db.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
sql := "INSERT INTO geopoints (device, timestamp, lon, lat, alt, acc, velocity, bearing) VALUES (?,?,?,?,?,?,?,?)"
|
|
stmt, err := tx.Prepare(sql)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
_, err = stmt.Exec(device, loc.Timestamp, loc.Lon, loc.Lat, loc.Alt, loc.Acc, loc.Velocity, loc.Bearing)
|
|
if err != nil {
|
|
stmt.Close()
|
|
return err
|
|
}
|
|
if err := stmt.Close(); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
func (stor *Storage) GetLastLocation(device string) (Location, error) {
|
|
var loc Location
|
|
sql := "SELECT timestamp, lon, lat, alt, acc, velocity, bearing FROM geopoints WHERE device LIKE ? ORDER BY timestamp DESC LIMIT 1"
|
|
stmt, err := stor.db.Prepare(sql)
|
|
if err != nil {
|
|
return loc, err
|
|
}
|
|
defer stmt.Close()
|
|
rows, err := stmt.Query(device)
|
|
if err != nil {
|
|
return loc, err
|
|
}
|
|
defer rows.Close()
|
|
if rows.Next() {
|
|
err := rows.Scan(&loc.Timestamp, &loc.Lon, &loc.Lat, &loc.Alt, &loc.Acc, &loc.Velocity, &loc.Bearing)
|
|
return loc, err
|
|
}
|
|
return loc, nil
|
|
}
|