1 files changed, 140 insertions, 3 deletions
diff --git a/db/db.go b/db/db.go
index 2c59694..4824a6d 100644
--- a/db/db.go
+++ b/db/db.go
@@ -1,10 +1,147 @@
 package db
 
 import (
-	"fmt"
+	"os"
+	"io/fs"
+	"errors"
+	"database/sql"
+	_ "github.com/mattn/go-sqlite3"
 )
 
 
-func HelloWorld() {
-	fmt.Println("hello, world!")
+type User struct {
+	Id int
+	Name string
+	PasswordHash []byte
+	Salt []byte
+}
+
+
+type Session struct {
+	Id string
+	UserId string
+}
+
+type Endpoint struct {
+	Id string
+	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)
+	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)
+	AllSessions() ([]Session, error)
+
+	CreateEndpoint(name, path, address string) (Endpoint, error)
+	DeleteEndpoint(endpoint Endpoint) error
+	GetEndpointByName(name string) (Endpoint, error)
+	GetEndpointByPath(path string) (Endpoint, error)
+	GetEndpointByAddress(address string) (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, 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
 }