summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--db/db.go11
-rw-r--r--db/endpoint.go85
-rw-r--r--db/session.go127
3 files changed, 220 insertions, 3 deletions
diff --git a/db/db.go b/db/db.go
index 4824a6d..d9faff3 100644
--- a/db/db.go
+++ b/db/db.go
@@ -2,6 +2,7 @@ package db
import (
"os"
+ "time"
"io/fs"
"errors"
"database/sql"
@@ -19,11 +20,13 @@ type User struct {
type Session struct {
Id string
- UserId string
+ UserId int
+ Created time.Time
+ Modified time.Time
}
type Endpoint struct {
- Id string
+ Id int
Name string
Path string
Address string
@@ -45,6 +48,7 @@ type Model interface {
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)
@@ -52,6 +56,7 @@ type Model interface {
GetEndpointByName(name string) (Endpoint, error)
GetEndpointByPath(path string) (Endpoint, error)
GetEndpointByAddress(address string) (Endpoint, error)
+ AllEndpoints() ([]Endpoint, error)
}
@@ -100,7 +105,7 @@ func (p *Phlox) Create(filename string) error {
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, foreign key(userid) references users(userid));
+ 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 {
diff --git a/db/endpoint.go b/db/endpoint.go
new file mode 100644
index 0000000..f22d77b
--- /dev/null
+++ b/db/endpoint.go
@@ -0,0 +1,85 @@
+package db
+
+func (p *Phlox) CreateEndpoint(name, path, address string) (Endpoint, error) {
+ var id int
+ row := p.db.QueryRow("select max(endpointid) from endpoints;")
+ err := row.Scan(&id)
+ if err != nil {
+ return Endpoint{}, err
+ }
+ id += 1
+
+ _, err = p.db.Exec(
+ "insert into endpoints values (?, ?, ?, ?)",
+ id, name, path, address,
+ )
+ if err != nil {
+ return Endpoint{}, err
+ }
+
+ endpoint := Endpoint{
+ Id: id,
+ Name: name,
+ Path: path,
+ Address: address,
+ }
+
+ return endpoint, nil
+}
+
+
+func (p *Phlox) DeleteEndpoint(endpoint Endpoint) error {
+ _, err := p.db.Exec("delete from endpoints where endpointid = ?;", endpoint.Id)
+ return err
+}
+
+
+func extractEndpoint(s Scanner) (Endpoint, error) {
+ var endpoint Endpoint
+ err := s.Scan(
+ &endpoint.Id,
+ &endpoint.Name,
+ &endpoint.Path,
+ &endpoint.Address,
+ )
+ return endpoint, err
+}
+
+
+func queryEndpoint(p *Phlox, query, param string) (Endpoint, error) {
+ row := p.db.QueryRow(query, param)
+ endpoint, err := extractEndpoint(row)
+ return endpoint, err
+}
+
+func (p *Phlox) GetEndpointByName(name string) (Endpoint, error) {
+ return queryEndpoint(p, "select * from endpoints where name = ?;", name)
+}
+
+func (p *Phlox) GetEndpointByPath(path string) (Endpoint, error) {
+ return queryEndpoint(p, "select * from endpoints where path = ?;", path)
+}
+
+func (p *Phlox) GetEndpointByAddress(address string) (Endpoint, error) {
+ return queryEndpoint(p, "select * from endpoints where address = ?;", address)
+}
+
+
+func (p *Phlox) AllEndpoints() ([]Endpoint, error) {
+ endpoints := make([]Endpoint, 0)
+ rows, err := p.db.Query("select * from endpoints;")
+ if err != nil {
+ return endpoints, err
+ }
+ defer rows.Close()
+
+ for rows.Next() {
+ endpoint, err := extractEndpoint(rows)
+ if err != nil {
+ return endpoints, err
+ }
+ endpoints = append(endpoints, endpoint)
+ }
+
+ return endpoints, nil
+}
diff --git a/db/session.go b/db/session.go
new file mode 100644
index 0000000..bddedda
--- /dev/null
+++ b/db/session.go
@@ -0,0 +1,127 @@
+package db
+
+import (
+ "database/sql"
+ "crypto/rand"
+ "encoding/base64"
+ "errors"
+ "time"
+)
+
+
+func (p *Phlox) CreateSession(user User) (Session, error) {
+ bytes := make([]byte, 32)
+ _, err := rand.Read(bytes)
+ if err != nil {
+ return Session{}, err
+ }
+
+ sessionid := base64.StdEncoding.EncodeToString(bytes)
+ userid := user.Id
+ now := time.Now().UTC()
+ nowStr := now.Format(time.RFC3339)
+
+ _, err = p.db.Exec(
+ "insert into sessions (sessionid, userid, created, modified) values (?, ?, ?, ?);",
+ sessionid,
+ userid,
+ nowStr, nowStr,
+ )
+ if err != nil {
+ return Session{}, err
+ }
+
+ return Session{
+ Id: sessionid,
+ UserId: userid,
+ Created: now,
+ Modified: now,
+ }, nil
+}
+
+
+func (p *Phlox) DeleteSession(session Session) error {
+ _, err := p.db.Exec("delete from sessions where sessionid = ?;", session.Id)
+ return err
+}
+
+
+func extractSession(s Scanner) (Session, error) {
+ var (
+ session Session
+ createdStr string
+ modifiedStr string
+ )
+
+ // scan
+ err := s.Scan(&session.Id, &session.UserId, &createdStr, &modifiedStr)
+ if err != nil {
+ return Session{}, err
+ }
+
+ // parse times
+ session.Created, err = time.Parse(time.RFC3339, createdStr)
+ if err != nil {
+ return Session{}, err
+ }
+ session.Modified, err = time.Parse(time.RFC3339, modifiedStr)
+ if err != nil {
+ return Session{}, err
+ }
+
+ return session, nil
+}
+
+
+func (p *Phlox) CheckSession(session Session) (bool, error) {
+ row := p.db.QueryRow("select * from sessions where sessionid = ?", session.Id)
+ session, err := extractSession(row)
+ if err != nil {
+ if errors.Is(err, sql.ErrNoRows) {
+ // no row returned, so invalid session
+ return false, nil
+ } else {
+ // some other error
+ return false, err
+ }
+ }
+
+ return true, nil
+}
+
+
+func (p *Phlox) TouchSession(session Session) error {
+ now := time.Now().UTC().Format(time.RFC3339)
+ _, err := p.db.Exec(
+ "update sessions set modified = ? where sessionid = ?;",
+ now, session.Id,
+ )
+ return err
+}
+
+
+func (p *Phlox) CleanSessions(maxIdle time.Duration) error {
+ expire := time.Now().UTC().Add(-maxIdle).Format(time.RFC3339)
+ _, err := p.db.Exec("delete from sessions where modified < ?;", expire)
+ return err
+}
+
+
+func (p *Phlox) AllSessions() ([]Session, error) {
+ sessions := make([]Session, 0)
+ rows, err := p.db.Query("select * from sessions;")
+ if err != nil {
+ return sessions, err
+ }
+ defer rows.Close()
+
+ for rows.Next() {
+ session, err := extractSession(rows)
+ if err != nil {
+ return sessions, err
+ }
+ sessions = append(sessions, session)
+ }
+
+ return sessions, nil
+}