-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.go
155 lines (137 loc) · 3.55 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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
package main
import (
"database/sql"
_ "github.com/mattn/go-sqlite3"
"log"
"strconv"
)
type DbUser struct {
id int
uname string
name string
pass string
}
var database *sql.DB
func dbInit() *sql.DB{
//open the DB
data, err := sql.Open("sqlite3", PROJECT_FOLDER + "/server/data/database.db")
if err != nil {
log.Print("Error loading database.")
log.Fatal("Error was: ", err.Error())
return nil
}
//create tables if they dont exist
statement, err := data.Prepare(
"CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, uname TEXT, name TEXT, pass TEXT)")
if err != nil {
log.Print("Error preapring init users")
log.Fatal("Error was: ", err.Error())
return nil
}
_, err = statement.Exec()
if err != nil {
log.Print("Error initialising users table.")
log.Fatal("Error was: ", err.Error())
return nil
}
statement, _ = data.Prepare(
"CREATE TABLE IF NOT EXISTS samples (id INTEGER PRIMARY KEY, timestamp TEXT, value INTEGER)")
_, err = statement.Exec()
if err != nil {
log.Print("Error initialising database.")
log.Fatal("Error was: ", err.Error())
return nil
}
//return the DB controller
return data
}
func dbUsersLog( db *sql.DB ) {
log.Print("Logging all users...")
//get all users
data, err := db.Query("SELECT * from users")
if err != nil {
log.Print("Error logging database.")
log.Print("Error was: ", err.Error())
}
var userTmp DbUser
//for each user dump it's information
for data.Next() {
if data == nil{
return
}
_ = data.Scan(&userTmp.id, &userTmp.name, &userTmp.uname, &userTmp.pass)
log.Print(strconv.Itoa(userTmp.id) + " : " + userTmp.name + " : " + userTmp.uname + " " + userTmp.pass)
}
}
func dbSamplesLog( db *sql.DB ) {
log.Print("Logging all samples...")
//get all users
data, err := db.Query("SELECT * from samples")
if err != nil {
log.Print("Error logging database.")
log.Print("Error was: ", err.Error())
}
var sampleTmp WsData
var id int
//for each user dump it's information
for data.Next() {
if data == nil{
return
}
_ = data.Scan(&id, &sampleTmp.Timestamp, &sampleTmp.Value)
log.Print(strconv.Itoa(id) + " : " + sampleTmp.Timestamp + " : " + strconv.Itoa(sampleTmp.Value))
}
}
func dbUserInsert(db *sql.DB, u DbUser){
//insert a new user
st, _ := db.Prepare("INSERT INTO users (uname, name, pass) values (?, ?, ?)")
_, _ = st.Exec(u.uname, u.name, u.pass)
}
func dbEntryInsert(db *sql.DB, u []WsData){
//insert a new sample
qr := "INSERT INTO samples (timestamp, value) values "
for i, l := range u {
if i != 0 {
qr += ","
}
qr = qr + "('"+l.Timestamp+"','"+strconv.Itoa(l.Value)+"')"
}
log.Print(qr)
st, _ := db.Prepare(qr)
_, _ = st.Exec()
}
func dbGetSamples(db *sql.DB) []WsData {
var ret []WsData
//get all samples
data, err := database.Query(
"select distinct * from samples where timestamp!='' and value!=0")
if err != nil {
log.Print("Could not get samples.")
log.Print("Error was: ", err.Error())
return nil
}
//for each sample save it to slice
for data.Next(){
var toIns WsData
var dummy int
err = data.Scan(&dummy, &toIns.Timestamp, &toIns.Value)
if err != nil{
log.Print("Could not parse row of samples.")
log.Print("Error was: ", err.Error())
return nil
}
ret = append(ret, toIns)
}
//return sample slice
return ret
}
func userExists(uid string, password string) bool{
st := "select * from users where uname='" + uid + "' and pass='" + password + "';"
data, err := database.Query(st)
if err != nil {
log.Print("Error querying db.")
log.Print("Error was: ", err.Error())
return false
}
return data.Next()
}