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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
package db
import (
"os"
"time"
"io/fs"
"errors"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
type User struct {
Id int
Name string
PasswordHash []byte
Salt []byte
}
type Session struct {
Id string
UserId int
Created time.Time
Modified time.Time
}
type Endpoint struct {
Id int
Name string
Path string
Address string
}
type Model interface {
Create(filename string) error
Open(filename string) error
Close() error
GetSchemaVersion() (int, error)
SetHostAddress(addr string) error
GetHostAddress() (string, error)
SetLoginPage(path string) error
GetLoginPage() (string, error)
CreateUser(username, password string) (User, error)
DeleteUser(user User) error
SetPassword(user User, password string) error
AuthenticateUser(username, password string) (User, error)
GetByUsername(username string) (User, error)
GetById(id string) (User, error)
AllUsers() ([]User, error)
CreateSession(user User) (Session, error)
DeleteSession(session Session) error
TouchSession(session Session) error
CheckSession(session Session) (bool, error)
CleanSessions(maxIdle time.Duration) error
AllSessions() ([]Session, error)
CreateEndpoint(name, path, address string) (Endpoint, error)
DeleteEndpoint(endpoint Endpoint) error
SetEndpointPath(endpoint Endpoint, path string) error
SetEndpointAddress(endpoint Endpoint, address string) error
GetEndpointByName(name string) (Endpoint, error)
GetEndpointByPath(path string) (Endpoint, error)
GetEndpointByAddress(address string) (Endpoint, error)
AllEndpoints() ([]Endpoint, error)
}
// interface to wrap sql.Row and sql.Rows
type Scanner interface {
Scan(dest ...any) error
}
type Phlox struct {
db *sql.DB
}
func fileExists(filename string) (bool, error) {
_, err := os.Stat(filename)
if err == nil {
return true, nil
} else if errors.Is(err, fs.ErrNotExist) {
return false, nil
} else {
// unknown error
return false, err
}
}
func (p *Phlox) Create(filename string) error {
exist, err := fileExists(filename)
if err != nil {
return err
}
if exist {
// file already exists
return fs.ErrExist
}
p.db, err = sql.Open("sqlite3", filename)
if err != nil {
return err
}
_, err = p.db.Exec(`
create table schema(version integer);
insert into schema values(0);
create table config(key string primary key, value string);
insert into config values("hostaddress", "localhost:3333");
insert into config values("loginpage", "");
create table users (userid integer not null primary key, username string, passwordhash string, salt string);
create table sessions (sessionid string not null primary key, userid integer, created string, modified string, foreign key(userid) references users(userid));
create table endpoints (endpointid integer not null primary key, name string, path string, address string);
`)
if err != nil {
return err
}
return nil
}
func (p *Phlox) Open(filename string) error {
exist, err := fileExists(filename)
if err != nil {
return err
}
if !exist {
// no such file!
return fs.ErrNotExist
}
p.db, err = sql.Open("sqlite3", filename)
if err != nil {
return err
}
return nil
}
func (p *Phlox) Close() error {
err := p.db.Close()
return err
}
func (p *Phlox) GetSchemaVersion() (int, error) {
row := p.db.QueryRow("select max(version) from schema;")
if row.Err() != nil {
return -1, row.Err()
}
var version int
err := row.Scan(&version)
if err != nil {
return -1, err
}
return version, nil
}
func (p *Phlox) SetHostAddress(addr string) error {
_, err := p.db.Exec("update config set value=? where key='hostaddress';", addr)
return err
}
func (p *Phlox) GetHostAddress() (string, error) {
row := p.db.QueryRow("select value from config where key='hostaddress';")
var addr string
err := row.Scan(&addr)
if err != nil {
return "", err
}
return addr, nil
}
func (p *Phlox) SetLoginPage(path string) error {
_, err := p.db.Exec("update config set value=? where key='loginpage';", path)
return err
}
func (p *Phlox) GetLoginPage() (string, error) {
row := p.db.QueryRow("select value from config where key='loginpage';")
var path string
err := row.Scan(&path)
if err != nil {
return "", err
}
return path, nil
}
|
