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
|
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)
CreateUser(username, password string) (User, error)
SetPassword(user User, password string) error
AuthenticateUser(username, password string) (User, error)
GetById(id string) (User, error)
AllUsers() ([]User, error)
CreateSession(user User) (Session, error)
DeleteSession(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 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) 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
}
|
