refactor: drop http api, move to sqlc/postgresql

10 files changed, 111 insertions, 357 deletions

diff --git a/db/account.go b/db/account.go
deleted file mode 100644
index 0668016..0000000
--- a/db/account.go
+++ /dev/null
@@ -1,129 +0,0 @@
-package db
-
-import (
-	"errors"
-	"fmt"
-	"log"
-)
-
-type Account struct {
-	UserName string `validate:"required,alphanum,max=64"`
-	Password string `json:",omitempty" validate:"required,min=10,max=128"`
-
-	Info *Login
-}
-
-type Login struct {
-	id                  uint
-	Level               uint   `validate:"gte=0,lte=100"`
-	FirstName, LastName string `validate:"required,alphanumunicode"`
-	Bearer              *Bearer
-}
-
-func (ac *Account) CreateAccount(safe *SafeDB) error {
-	const sqlStatement string = `
-		INSERT INTO Accounts (
-			id,
-			UserName,
-			Password,
-			Level,
-			FirstName,
-			LastName
-		)
-		VALUES (NULL, ?, ?, ?, ?, ?);
-	`
-
-	err := safe.validate.Struct(ac)
-	if err != nil {
-		return err
-	}
-
-	safe.mu.Lock()
-	defer safe.mu.Unlock()
-
-	_, err = safe.db.Exec(
-		sqlStatement,
-		ac.UserName,
-		ToBlake3(ac.Password),
-
-		ac.Info.FirstName,
-		ac.Info.LastName,
-		ac.Info.Level,
-	)
-
-	return err
-}
-
-func (ac *Account) Login(safe *SafeDB) error {
-	const sqlStatementQuery string = `
-		SELECT id, Password, Level, FirstName, LastName
-		FROM Accounts
-		WHERE Accounts.UserName = ?
-	`
-
-	err := safe.validate.Struct(ac)
-	fmt.Println(ac.Password, ac.UserName)
-	if err != nil {
-		log.Println(err)
-		return err
-	}
-
-	ac.Info = &Login{}
-	ac.Info.Bearer = &Bearer{}
-	safe.mu.Lock()
-	row := safe.db.QueryRow(sqlStatementQuery, ac.UserName)
-	safe.mu.Unlock()
-
-	var Password string
-	err = row.Scan(
-		&ac.Info.id,
-		&Password,
-		&ac.Info.FirstName,
-		&ac.Info.LastName,
-		&ac.Info.Level,
-	)
-	if err != nil {
-		return err
-	}
-	if Password != ToBlake3(ac.Password) {
-		return errors.New("Auth failed")
-	}
-	ac.Password = ""
-
-	err = ac.Info.Bearer.Generate(safe, ac.Info)
-	if err != nil {
-		return err
-	}
-
-	return err
-}
-
-func (ac *Account) fromBearer(safe *SafeDB, b *Bearer) error {
-	const sqlStatementAccount string = `
-		SELECT UserName, Password, Level, FirstName, LastName
-		FROM Accounts
-		WHERE Accounts.id = ?
-	`
-
-	safe.mu.Lock()
-	row := safe.db.QueryRow(sqlStatementAccount, b.accountId)
-	safe.mu.Unlock()
-
-	ac.Info = &Login{}
-	ac.Info.id = b.accountId
-	ac.Info.Bearer = b
-	err := row.Scan(
-		&ac.UserName,
-		&ac.Password,
-
-		&ac.Info.FirstName,
-		&ac.Info.LastName,
-		&ac.Info.Level,
-	)
-	if err != nil {
-		return err
-	}
-	ac.Info.Bearer = b
-
-	return err
-}
diff --git a/db/bearer.go b/db/bearer.go
deleted file mode 100644
index b16d506..0000000
--- a/db/bearer.go
+++ /dev/null
@@ -1,127 +0,0 @@
-package db
-
-import (
-	"errors"
-	"time"
-
-	_ "github.com/mattn/go-sqlite3"
-)
-
-type Bearer struct {
-	id, accountId uint
-	Token         string
-	ValidUpTo     time.Time
-}
-
-func (b *Bearer) FromToken(safe *SafeDB, Token string) error {
-	const sqlStatementBearer string = `
-		SELECT id, ValidUpTo, accountId
-		FROM Bearer
-		WHERE Bearer.Token = ?
-	`
-
-	b.Token = Token
-	var ValidUpToString string
-	safe.mu.Lock()
-	row := safe.db.QueryRow(sqlStatementBearer, Token)
-	safe.mu.Unlock()
-
-	err := row.Scan(
-		&b.id,
-		&ValidUpToString,
-		&b.accountId,
-	)
-	if err != nil {
-		return err
-	}
-
-	layout := "2006-01-02 15:04:05.999999999-07:00"
-	b.ValidUpTo, err = time.Parse(layout, ValidUpToString)
-	if err != nil {
-		return err
-	}
-
-	timeNow := time.Now()
-	if timeNow.After(b.ValidUpTo) {
-		return errors.New("Outdated Bearer Token")
-	}
-
-	return err
-}
-
-func (b *Bearer) Update(safe *SafeDB) error {
-	const sqlStatementBearer string = `
-		UPDATE Bearer
-		SET ValidUpTo = ?
-		WHERE id = ?
-	`
-
-	validUpTo := time.Now().Add(time.Hour * 24)
-	safe.mu.Lock()
-	_, err := safe.db.Exec(sqlStatementBearer, validUpTo, b.id)
-	safe.mu.Unlock()
-	if err != nil {
-		return err
-	}
-	b.ValidUpTo = validUpTo
-
-	return nil
-}
-
-func (b *Bearer) VerifyAndUpdate(safe *SafeDB, token string) error {
-	err := b.FromToken(safe, token)
-	if err != nil {
-		return err
-	}
-
-	err = b.Update(safe)
-	if err != nil {
-		return err
-	}
-
-	return nil
-}
-
-func (b *Bearer) Generate(safe *SafeDB, lg *Login) error {
-	const sqlGenBearer string = `
-		INSERT INTO Bearer (
-			id,
-			Token,
-			ValidUpTo,
-			accountId
-		)
-		VALUES (NULL, ?, ?, ?);
-	`
-
-	Token, err := GenRandomString(128)
-	if err != nil {
-		return err
-	}
-
-	timeNow := time.Now()
-	ValidUpTo := timeNow.Add(time.Hour * 24)
-	safe.mu.Lock()
-	res, err := safe.db.Exec(
-		sqlGenBearer,
-		Token,
-		ValidUpTo,
-		lg.id,
-	)
-	safe.mu.Unlock()
-	if err != nil {
-		return err
-	}
-
-	id, err := res.LastInsertId()
-	if err != nil {
-		return err
-	}
-
-	b.id = uint(id)
-	b.accountId = lg.id
-	b.Token = Token
-	b.ValidUpTo = ValidUpTo
-	lg.Bearer = b
-
-	return err
-}
diff --git a/db/db.go b/db/db.go
new file mode 100644
index 0000000..5b8c8f5
--- /dev/null
+++ b/db/db.go
@@ -0,0 +1,32 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.26.0
+
+package db
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgconn"
+)
+
+type DBTX interface {
+	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
+	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
+	QueryRow(context.Context, string, ...interface{}) pgx.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx pgx.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/db/main.go b/db/main.go
deleted file mode 100644
index 49658cd..0000000
--- a/db/main.go
+++ /dev/null
@@ -1,77 +0,0 @@
-package db
-
-import (
-	"database/sql"
-	"os"
-	"path/filepath"
-	"sync"
-
-	"github.com/go-playground/validator/v10"
-	_ "github.com/mattn/go-sqlite3"
-)
-
-type SafeDB struct {
-	mu       sync.Mutex
-	validate *validator.Validate
-
-	path string
-	db   *sql.DB
-}
-
-func (safe *SafeDB) setupPath() error {
-	const path string = "/var/lib/redq/"
-	const name string = "redq.sqlite3"
-
-	err := os.MkdirAll(path, os.ModeDir)
-	if err != nil {
-		return err
-	}
-
-	safe.path = filepath.Join(path, name)
-	return nil
-}
-
-func NewSafeDB() (*SafeDB, error) {
-	const create string = `
-		CREATE TABLE IF NOT EXISTS Accounts(
-			id        INTEGER PRIMARY KEY,
-			UserName  CHAR(64)	NOT NULL UNIQUE,
-			Password  CHAR(128)	NOT NULL,
-
-			Level     INTEGER	NOT NULL,
-			FirstName CHAR(32)	NOT NULL,
-			LastName  CHAR(32)	NOT NULL
-		);
-
-		CREATE TABLE IF NOT EXISTS Bearer(
-			id	  INTEGER PRIMARY KEY,
-			Token     CHAR(128)	NOT NULL UNIQUE,
-			ValidUpTo TIME		NOT NULL,
-			accountId INTEGER	NOT NULL,
-
-			FOREIGN KEY (accountId)
-			REFERENCES Accounts (id)
-		);
-	`
-	safe := &SafeDB{}
-	err := safe.setupPath()
-	if err != nil {
-		return nil, err
-	}
-
-	safe.mu.Lock()
-	defer safe.mu.Unlock()
-
-	safe.db, err = sql.Open("sqlite3", safe.path)
-	if err != nil {
-		return nil, err
-	}
-
-	_, err = safe.db.Exec(create)
-	if err != nil {
-		return nil, err
-	}
-
-	safe.validate = validator.New(validator.WithRequiredStructEnabled())
-	return safe, nil
-}
diff --git a/db/models.go b/db/models.go
new file mode 100644
index 0000000..83e4051
--- /dev/null
+++ b/db/models.go
@@ -0,0 +1,17 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.26.0
+
+package db
+
+import (
+	"github.com/jackc/pgx/v5/pgtype"
+)
+
+type Usage struct {
+	Hardwareaddr int32
+	Starttime    pgtype.Timestamp
+	Stoptime     pgtype.Timestamp
+	Egress       int32
+	Ingress      int32
+}
diff --git a/db/query.sql b/db/query.sql
new file mode 100644
index 0000000..75d5b61
--- /dev/null
+++ b/db/query.sql
@@ -0,0 +1,6 @@
+-- name: EnterUsage :exec
+INSERT INTO Usage (
+  HardwareAddr, StartTime, StopTime, Egress, Ingress
+) VALUES (
+  $1, $2, $3, $4, $5
+);
diff --git a/db/query.sql.go b/db/query.sql.go
new file mode 100644
index 0000000..de68384
--- /dev/null
+++ b/db/query.sql.go
@@ -0,0 +1,39 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.26.0
+// source: query.sql
+
+package db
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5/pgtype"
+)
+
+const enterUsage = `-- name: EnterUsage :exec
+INSERT INTO Usage (
+  HardwareAddr, StartTime, StopTime, Egress, Ingress
+) VALUES (
+  $1, $2, $3, $4, $5
+)
+`
+
+type EnterUsageParams struct {
+	Hardwareaddr int32
+	Starttime    pgtype.Timestamp
+	Stoptime     pgtype.Timestamp
+	Egress       int32
+	Ingress      int32
+}
+
+func (q *Queries) EnterUsage(ctx context.Context, arg EnterUsageParams) error {
+	_, err := q.db.Exec(ctx, enterUsage,
+		arg.Hardwareaddr,
+		arg.Starttime,
+		arg.Stoptime,
+		arg.Egress,
+		arg.Ingress,
+	)
+	return err
+}
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..b8f8540
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,7 @@
+CREATE TABLE Usage (
+  HardwareAddr INTEGER NOT NULL,
+  StartTime TIMESTAMP NOT NULL,
+  StopTime TIMESTAMP NOT NULL,
+  Egress INTEGER NOT NULL,
+  Ingress INTEGER NOT NULL
+);
diff --git a/db/sqlc.yaml b/db/sqlc.yaml
new file mode 100644
index 0000000..0cd3357
--- /dev/null
+++ b/db/sqlc.yaml
@@ -0,0 +1,10 @@
+version: "2"
+sql:
+  - engine: "postgresql"
+    queries: "query.sql"
+    schema: "schema.sql"
+    gen:
+      go:
+        package: "db"
+        out: "./"
+        sql_package: "pgx/v5"
diff --git a/db/utils.go b/db/utils.go
deleted file mode 100644
index 0b0f1cb..0000000
--- a/db/utils.go
+++ /dev/null
@@ -1,24 +0,0 @@
-package db
-
-import (
-	"encoding/base64"
-	"lukechampine.com/blake3"
-	"math/rand"
-)
-
-func ToBlake3(pass string) string {
-	hash := blake3.Sum512([]byte(pass))
-	hash64b := base64.StdEncoding.EncodeToString(hash[:])
-
-	return "blake3-" + hash64b
-}
-
-func GenRandomString(n int) (string, error) {
-	b := make([]byte, n)
-	_, err := rand.Read(b)
-	if err != nil {
-		return "", err
-	}
-
-	return base64.URLEncoding.EncodeToString(b)[:n], nil
-}