town/towndb/towndb.go

177 lines
2.8 KiB
Go
Raw Permalink Normal View History

2023-03-04 22:19:27 +00:00
package towndb
import (
"database/sql"
2023-10-24 05:15:04 +00:00
"errors"
2023-03-04 22:19:27 +00:00
"time"
_ "github.com/mattn/go-sqlite3"
)
const dsn = "/town/var/town.db?mode=rw"
type UserState string
const (
StateActive = "active"
StateTempBan = "temp_banned"
StateBan = "banned"
StateDeleted = "deleted" // some users request deletion
)
type AdminNote struct {
ID int64
Created time.Time
AuthorID int64
Content string
UserID int64
}
func (n *AdminNote) Insert(db *sql.DB) error {
2023-03-16 06:41:22 +00:00
var (
err error
stmt *sql.Stmt
result sql.Result
liid int64
)
tx, err := db.Begin()
2023-03-04 22:19:27 +00:00
if err != nil {
return err
}
2023-03-16 06:41:22 +00:00
defer func() {
if err != nil {
tx.Rollback()
}
}()
stmt, err = tx.Prepare(`
INSERT INTO notes (created, author, content)
VALUES (?, ?, ?)`)
if err != nil {
return err
}
result, err = stmt.Exec(
2023-03-04 22:19:27 +00:00
n.Created.Unix(),
n.AuthorID,
2023-03-16 06:41:22 +00:00
n.Content)
2023-03-04 22:19:27 +00:00
if err != nil {
return err
}
2023-03-16 06:41:22 +00:00
liid, err = result.LastInsertId()
2023-03-04 22:19:27 +00:00
if err != nil {
return err
}
n.ID = liid
2023-03-16 06:41:22 +00:00
stmt, err = tx.Prepare(`
INSERT INTO user_notes (noteid, userid) VALUES (?, ?)`)
if err != nil {
return err
}
_, err = stmt.Exec(n.ID, n.UserID)
if err != nil {
return err
}
return tx.Commit()
2023-03-04 22:19:27 +00:00
}
type TownUser struct {
ID int64
2023-03-06 03:04:27 +00:00
Created time.Time
2023-03-04 22:19:27 +00:00
Emails []string
Username string
Notes []AdminNote
State UserState
IsAdmin bool
}
2023-03-06 03:04:27 +00:00
func (u *TownUser) Insert(db *sql.DB) (err error) {
var tx *sql.Tx
var stmt *sql.Stmt
var result sql.Result
var liid int64
if tx, err = db.Begin(); err != nil {
return err
}
defer func() {
if err != nil {
tx.Rollback()
}
}()
if stmt, err = tx.Prepare(`
INSERT INTO users (created, username, state, admin)
VALUES (?, ?, ?, ?)`); err != nil {
2023-03-06 03:04:27 +00:00
return err
}
if result, err = stmt.Exec(
u.Created.Unix(),
u.Username,
2023-03-16 06:17:06 +00:00
u.State,
u.IsAdmin); err != nil {
2023-03-06 03:04:27 +00:00
return err
}
if liid, err = result.LastInsertId(); err != nil {
return err
}
u.ID = liid
if len(u.Emails) > 0 {
for _, e := range u.Emails {
if stmt, err = tx.Prepare(`
INSERT INTO emails (address, userid)
VALUES (?, ?)`); err != nil {
return err
}
if result, err = stmt.Exec(e, u.ID); err != nil {
return err
}
}
}
2023-03-06 20:34:29 +00:00
return tx.Commit()
2023-03-06 03:04:27 +00:00
}
2023-10-24 05:15:04 +00:00
// UserForEmail returns the user associated with an email or nil if no matching user is found
func UserForEmail(db *sql.DB, address string) (*TownUser, error) {
stmt, err := db.Prepare(`
SELECT u.id, u.username FROM users u
JOIN emails e ON e.userid = u.id
WHERE e.address = ?
`)
if err != nil {
return nil, err
}
2023-10-24 19:17:16 +00:00
defer stmt.Close()
2023-10-24 05:15:04 +00:00
row := stmt.QueryRow(address)
u := &TownUser{}
2023-10-24 05:16:51 +00:00
if err = row.Scan(&u.ID, &u.Username); err != nil {
2023-10-24 05:15:04 +00:00
if errors.Is(err, sql.ErrNoRows) {
return nil, nil
}
return nil, err
}
return u, nil
}
2023-03-04 22:19:27 +00:00
func ConnectDB() (*sql.DB, error) {
db, err := sql.Open("sqlite3", dsn)
if err != nil {
return nil, err
}
return db, nil
}