summaryrefslogtreecommitdiff
path: root/db/db.go
blob: d9b457801e80f5da82c6e5ec0fa556cb4e92c1dd (plain)
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
}