-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.go
135 lines (116 loc) · 3.45 KB
/
database.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
// Package for handling DB operations
package database
import (
"database/sql"
"errors"
"fmt"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
// DBRow is the structure of the sensor table in the database. Each field is
// exported so that it can be json encoded. Record can be empty (null) so it
// uses the sql type NullString.
type DBRow struct {
Id int
Date string
Beacon string
Name string
Record sql.NullString
UserActions sql.NullString
}
// Parse DBItem into string format for logging purpose
func (item DBRow) String() string {
return fmt.Sprintf("\n\tID:\t%d\n\tDate:\t%s\n\tBeacon:\t%s\n\tName:\t%s\n\tRecord:\t%s\n\tUserActions:\t%s\n", item.Id, item.Date, item.Beacon, item.Name, item.Record.String, item.UserActions.String)
}
// Initialize the database by creating the sql file if it does not already exist
func Init() {
log.Println("Initializing DB file...")
// Return if the file exists
if _, err := os.Stat("./sensor.db"); !errors.Is(err, os.ErrNotExist) {
log.Println("DB file exists, skipping creation...")
return
}
log.Println("DB file does not exist, proceeding initialization...")
db, err := sql.Open("sqlite3", "./sensor.db")
if err != nil {
log.Panic(err)
}
defer db.Close()
// Create table
query := `CREATE TABLE sensor
(
id INTEGER PRIMARY KEY,
date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
beacon TEXT NOT NULL,
name TEXT NOT NULL,
record BLOB,
userActions BLOB
)`
if _, err = db.Exec(query); err != nil {
log.Panicf("%q: %s\n", err, query)
}
}
// Create a new record in the database with the given arguments. Should probably
// validate the arguments.
func CreateRecord(beacon string, name string, record string, userActions string) (err error, errMsg string) {
// Connect to database
db, err := sql.Open("sqlite3", "./sample.db") // TODO: for testing
if err != nil {
return err, "Connection to database failed"
}
defer db.Close()
// Prepare transaction and prepare query statement
tx, err := db.Begin()
if err != nil {
return err, "Failed to start transaction"
}
stmt, err := tx.Prepare("INSERT INTO sensor(beacon, name, record, userActions) values(?, ?, ?, ?)")
if err != nil {
return err, "Failed to prepare SQL query statement"
}
defer stmt.Close()
_, err = stmt.Exec(beacon, name, record, userActions)
if err != nil {
return err, "Failed to execute SQL query statement"
}
tx.Commit()
return nil, ""
}
// Retrieve all data from the database. If isTesting is true, get records from
// sample database instead of the real one. If error has occurred, return the
// error along with the descriptive error message
func ReadRecords(useSampleDB bool) ([]DBRow, error, string) {
var path string
if useSampleDB {
path = "./sample.db"
} else {
path = "./sensor.db"
}
// Connect to database
db, err := sql.Open("sqlite3", path)
if err != nil {
return nil, err, "Connection to database failed"
}
defer db.Close()
// Request sensor values from the database
rows, err := db.Query("SELECT * FROM sensor")
if err != nil {
return nil, err, "Query failed"
}
defer rows.Close()
data := make([]DBRow, 0)
for rows.Next() {
var row DBRow
if err := rows.Scan(&row.Id, &row.Date, &row.Beacon, &row.Name, &row.Record, &row.UserActions); err != nil {
return nil, err, "Row scan failed"
}
data = append(data, row)
}
log.Print("Row successfully scanned...")
if len(data) != 0 {
log.Print("Printing scan results...")
fmt.Println(data)
}
return data, nil, ""
}