Convert to SQLite

Signed-off-by: AKP <tom@tdpain.net>

10 files changed, 199 insertions, 114 deletions

diff --git a/walrss/internal/core/feeds.go b/walrss/internal/core/feeds.go
index 9ba6545..600977d 100644
--- a/walrss/internal/core/feeds.go
+++ b/walrss/internal/core/feeds.go
@@ -1,12 +1,13 @@
 package core
 
 import (
+	"context"
+	"database/sql"
 	"errors"
 	"github.com/codemicro/walrss/walrss/internal/core/opml"
 	"github.com/codemicro/walrss/walrss/internal/db"
 	"github.com/codemicro/walrss/walrss/internal/state"
 	"github.com/lithammer/shortuuid/v4"
-	bh "github.com/timshannon/bolthold"
 )
 
 func NewFeed(st *state.State, userID, name, url string) (*db.Feed, error) {
@@ -25,37 +26,34 @@ func NewFeed(st *state.State, userID, name, url string) (*db.Feed, error) {
 		UserID: userID,
 	}
 
-	if err := st.Data.Insert(feed.ID, feed); err != nil {
+	if _, err := st.Data.NewInsert().Model(feed).Exec(context.Background()); err != nil {
 		return nil, err
 	}
 
 	return feed, nil
 }
 
-func GetFeedsForUser(st *state.State, userID string) ([]*db.Feed, error) {
-	var feeds []*db.Feed
-	if err := st.Data.Find(&feeds, bh.Where("UserID").Eq(userID)); err != nil {
-		return nil, err
-	}
-	return feeds, nil
+func GetFeedsForUser(st *state.State, userID string) (res []*db.Feed, err error) {
+	err = st.Data.NewSelect().
+		Model(&res).
+		Relation("User").
+		Where("Feed.user_id = ?", userID).
+		Scan(context.Background())
+	return
 }
 
-func GetFeed(st *state.State, id string) (*db.Feed, error) {
-	feed := new(db.Feed)
-	if err := st.Data.FindOne(feed, bh.Where("ID").Eq(id)); err != nil {
-		if errors.Is(err, bh.ErrNotFound) {
-			return nil, ErrNotFound
-		}
-		return nil, err
+func GetFeed(st *state.State, id string) (res *db.Feed, err error) {
+	res = new(db.Feed)
+	err = st.Data.NewSelect().Model(res).Where("id = ?", id).Scan(context.Background())
+	if errors.Is(err, sql.ErrNoRows) {
+		return nil, ErrNotFound
 	}
-	return feed, nil
+	return
 }
 
 func DeleteFeed(st *state.State, id string) error {
-	if err := st.Data.Delete(id, new(db.Feed)); err != nil {
-		return err
-	}
-	return nil
+	_, err := st.Data.NewSelect().Model((*db.Feed)(nil)).Where("id = ?", id).Exec(context.Background())
+	return err
 }
 
 func UpdateFeed(st *state.State, feed *db.Feed) error {
@@ -67,13 +65,11 @@ func UpdateFeed(st *state.State, feed *db.Feed) error {
 		return err
 	}
 
-	if err := st.Data.Update(feed.ID, feed); err != nil {
-		if errors.Is(err, bh.ErrNotFound) {
-			return ErrNotFound
-		}
-		return err
+	_, err := st.Data.NewUpdate().Model(feed).WherePK().Exec(context.Background())
+	if errors.Is(err, sql.ErrNoRows) {
+		return ErrNotFound
 	}
-	return nil
+	return err
 }
 
 func ExportFeedsForUser(st *state.State, userID string) ([]byte, error) {
@@ -82,9 +78,14 @@ func ExportFeedsForUser(st *state.State, userID string) ([]byte, error) {
 	if err != nil {
 		return nil, err
 	}
-	if err := st.Data.Find(&feeds, bh.Where("UserID").Eq(userID)); err != nil {
+
+	if err := st.Data.NewSelect().
+		Model(&feeds).
+		Where("user_id = ?", userID).
+		Scan(context.Background()); err != nil {
 		return nil, err
 	}
+
 	return opml.FromFeeds(feeds, user.Email).ToBytes()
 }
 
diff --git a/walrss/internal/core/users.go b/walrss/internal/core/users.go
index 5071543..5c17251 100644
--- a/walrss/internal/core/users.go
+++ b/walrss/internal/core/users.go
@@ -1,11 +1,13 @@
 package core
 
 import (
+	"context"
+	"database/sql"
 	"errors"
 	"github.com/codemicro/walrss/walrss/internal/db"
 	"github.com/codemicro/walrss/walrss/internal/state"
 	"github.com/lithammer/shortuuid/v4"
-	bh "github.com/timshannon/bolthold"
+	"github.com/mattn/go-sqlite3"
 	"golang.org/x/crypto/bcrypt"
 )
 
@@ -31,9 +33,11 @@ func RegisterUser(st *state.State, email, password string) (*db.User, error) {
 
 	u.Password = hash
 
-	if err := st.Data.Insert(u.ID, u); err != nil {
-		if errors.Is(err, bh.ErrUniqueExists) {
-			return nil, NewUserError("email address in use")
+	if _, err := st.Data.NewInsert().Model(u).Exec(context.Background()); err != nil {
+		if e, ok := err.(*sqlite3.Error); ok {
+			if e.Code == sqlite3.ErrConstraint {
+				return nil, NewUserError("email address in use")
+			}
 		}
 		return nil, err
 	}
@@ -57,59 +61,42 @@ func AreUserCredentialsCorrect(st *state.State, email, password string) (bool, e
 	return true, nil
 }
 
-func GetUserByID(st *state.State, userID string) (*db.User, error) {
-	user := new(db.User)
-	if err := st.Data.FindOne(user, bh.Where("ID").Eq(userID)); err != nil {
-		if errors.Is(err, bh.ErrNotFound) {
-			return nil, ErrNotFound
-		}
-		return nil, err
+func GetUserByID(st *state.State, userID string) (res *db.User, err error) {
+	res = new(db.User)
+	err = st.Data.NewSelect().Model(res).Where("id = ?", userID).Scan(context.Background())
+	if errors.Is(err, sql.ErrNoRows) {
+		return nil, ErrNotFound
 	}
-	return user, nil
+	return
 }
 
-func GetUserByEmail(st *state.State, email string) (*db.User, error) {
-	user := new(db.User)
-	if err := st.Data.FindOne(user, bh.Where("Email").Eq(email)); err != nil {
-		if errors.Is(err, bh.ErrNotFound) {
-			return nil, ErrNotFound
-		}
-		return nil, err
+func GetUserByEmail(st *state.State, email string) (res *db.User, err error) {
+	res = new(db.User)
+	err = st.Data.NewSelect().Model(res).Where("email = ?", email).Scan(context.Background())
+	if errors.Is(err, sql.ErrNoRows) {
+		return nil, ErrNotFound
 	}
-	return user, nil
+	return
 }
 
 func UpdateUser(st *state.State, user *db.User) error {
-	if err := st.Data.Update(user.ID, user); err != nil {
-		if errors.Is(err, bh.ErrNotFound) {
-			return ErrNotFound
-		}
-		return err
+	_, err := st.Data.NewUpdate().Model(user).WherePK().Exec(context.Background())
+	if errors.Is(err, sql.ErrNoRows) {
+		return ErrNotFound
 	}
-	return nil
+	return err
 }
 
-func GetUsersBySchedule(st *state.State, day db.SendDay, hour int) ([]*db.User, error) {
-	// When trying to Or some queries, BH was weird, so it's easier to make two queries and combine them.
-	// This ensures that indexes are used.
-
-	var users []*db.User
-	if err := st.Data.Find(&users,
-		bh.Where("Schedule.Active").Eq(true).
-			And("Schedule.Day").Eq(day).
-			And("Schedule.Hour").Eq(hour),
-	); err != nil {
-		return nil, err
-	}
-
-	var users2 []*db.User
-	if err := st.Data.Find(&users2,
-		bh.Where("Schedule.Active").Eq(true).
-			And("Schedule.Day").Eq(db.SendDaily).
-			And("Schedule.Hour").Eq(hour),
-	); err != nil {
-		return nil, err
-	}
-
-	return append(users, users2...), nil
+func GetUsersBySchedule(st *state.State, day db.SendDay, hour int) (res []*db.User, err error) {
+	err = st.Data.NewSelect().
+		Model(&res).
+		Where(
+			"active = ? and (schedule_day = ? or schedule_day = ?) and schedule_hour = ?",
+			true,
+			day,
+			db.SendDaily,
+			hour,
+		).
+		Scan(context.Background())
+	return
 }
diff --git a/walrss/internal/db/00000000000001_initialise.go b/walrss/internal/db/00000000000001_initialise.go
new file mode 100644
index 0000000..aaa37d1
--- /dev/null
+++ b/walrss/internal/db/00000000000001_initialise.go
@@ -0,0 +1,38 @@
+package db
+
+import (
+	"context"
+	"github.com/uptrace/bun"
+)
+
+func init() {
+	tps := []any{
+		(*User)(nil),
+		(*Feed)(nil),
+	}
+
+	migs.MustRegister(
+		func(ctx context.Context, db *bun.DB) error {
+			migLogger.Debug().Msg("1 up")
+
+			for _, t := range tps {
+				if _, err := db.NewCreateTable().Model(t).Exec(ctx); err != nil {
+					return err
+				}
+			}
+
+			return nil
+		},
+		func(ctx context.Context, db *bun.DB) error {
+			migLogger.Debug().Msg("1 down")
+
+			for _, t := range tps {
+				if _, err := db.NewDropTable().Model(t).Exec(ctx); err != nil {
+					return err
+				}
+			}
+
+			return nil
+		},
+	)
+}
diff --git a/walrss/internal/db/db.go b/walrss/internal/db/db.go
index 989038c..4da74e0 100644
--- a/walrss/internal/db/db.go
+++ b/walrss/internal/db/db.go
@@ -1,40 +1,49 @@
 package db
 
 import (
-	"encoding/json"
-	bh "github.com/timshannon/bolthold"
+	"database/sql"
+	_ "github.com/mattn/go-sqlite3"
+	"github.com/rs/zerolog/log"
+	"github.com/uptrace/bun"
+	"github.com/uptrace/bun/dialect/sqlitedialect"
 	"strings"
 )
 
-func New(filename string) (*bh.Store, error) {
-	store, err := bh.Open(filename, 0644, &bh.Options{
-		Encoder: json.Marshal,
-		Decoder: json.Unmarshal,
-	})
+func New(filename string) (*bun.DB, error) {
+	dsn := filename
+	log.Info().Msg("connecting to database")
+	db, err := sql.Open("sqlite3", dsn)
 	if err != nil {
 		return nil, err
 	}
-	return store, nil
+
+	db.SetMaxOpenConns(1) // https://github.com/mattn/go-sqlite3/issues/274#issuecomment-191597862
+
+	return bun.NewDB(db, sqlitedialect.New()), nil
 }
 
 type User struct {
-	ID       string `boldholdKey:""`
-	Email    string `boltholdUnique:"UniqueEmail"`
-	Password []byte
-	Salt     []byte
+	bun.BaseModel `bun:"table:users"`
 
-	Schedule struct {
-		Active bool    `boltholdIndex:"Active"`
-		Day    SendDay `boltholdIndex:"Day"`
-		Hour   int     `boltholdIndex:"Hour"`
-	}
+	ID       string `bun:"id,pk"`
+	Email    string `bun:"email,notnull,unique"`
+	Password []byte `bun:"password"`
+	Salt     []byte `bun:"salt"`
+
+	Active       bool    `bun:"active,notnull"`
+	ScheduleDay  SendDay `bun:"schedule_day"`
+	ScheduleHour int     `bun:"schedule_hour"`
 }
 
 type Feed struct {
-	ID     string `boltholdKey:""`
-	URL    string
-	Name   string
-	UserID string `boldholdIndex:"UserID"`
+	bun.BaseModel `bun:"table:feeds"`
+
+	ID     string `bun:"id,pk"`
+	URL    string `bun:"url,notnull"`
+	Name   string `bun:"name,notnull"`
+	UserID string `bun:"user_id,notnull"`
+
+	User *User `bun:",rel:belongs-to,join:user_id=id"`
 }
 
 type FeedSlice []*Feed
diff --git a/walrss/internal/db/migrations.go b/walrss/internal/db/migrations.go
new file mode 100644
index 0000000..d4773fc
--- /dev/null
+++ b/walrss/internal/db/migrations.go
@@ -0,0 +1,40 @@
+package db
+
+import (
+	"context"
+	"github.com/rs/zerolog/log"
+	"github.com/uptrace/bun"
+	"github.com/uptrace/bun/migrate"
+	"time"
+)
+
+var (
+	migs      = migrate.NewMigrations()
+	migLogger = log.Logger.With().Str("location", "migrations").Logger()
+)
+
+func DoMigrations(db *bun.DB) error {
+	log.Info().Msg("running migrations")
+
+	mig := migrate.NewMigrator(db, migs)
+
+	ctx, cancel := context.WithTimeout(context.Background(), time.Minute)
+	defer cancel()
+
+	if err := mig.Init(ctx); err != nil {
+		return err
+	}
+
+	group, err := mig.Migrate(ctx)
+	if err != nil {
+		return err
+	}
+
+	if group.IsZero() {
+		log.Info().Msg("database up to date")
+	} else {
+		log.Info().Msg("migrations applied")
+	}
+
+	return nil
+}
diff --git a/walrss/internal/http/edit.go b/walrss/internal/http/edit.go
index 2cfc545..657989a 100644
--- a/walrss/internal/http/edit.go
+++ b/walrss/internal/http/edit.go
@@ -22,9 +22,9 @@ func (s *Server) editEnabledState(ctx *fiber.Ctx) error {
 	}
 
 	if strings.ToLower(ctx.FormValue("enable", "off")) == "on" {
-		user.Schedule.Active = true
+		user.Active = true
 	} else {
-		user.Schedule.Active = false
+		user.Active = false
 	}
 
 	if err := core.UpdateUser(s.state, user); err != nil {
@@ -33,9 +33,9 @@ func (s *Server) editEnabledState(ctx *fiber.Ctx) error {
 
 	fragmentEmitSuccess(ctx)
 	return ctx.SendString((&views.MainPage{
-		EnableDigests: user.Schedule.Active,
-		SelectedDay:   user.Schedule.Day,
-		SelectedTime:  user.Schedule.Hour,
+		EnableDigests: user.Active,
+		SelectedDay:   user.ScheduleDay,
+		SelectedTime:  user.ScheduleHour,
 	}).RenderScheduleCard())
 }
 
@@ -57,7 +57,7 @@ func (s *Server) editTimings(ctx *fiber.Ctx) error {
 		if x > db.SendOnSunday || x < 0 {
 			return core.NewUserError("invalid day: out of range 0<=x<=%d", int(db.SendOnSunday))
 		}
-		user.Schedule.Day = x
+		user.ScheduleDay = x
 	}
 
 	if n, err := strconv.ParseInt(ctx.FormValue("time"), 10, 8); err != nil {
@@ -67,7 +67,7 @@ func (s *Server) editTimings(ctx *fiber.Ctx) error {
 		if x > 23 || x < 0 {
 			return core.NewUserError("invalid time: out of range 0<=x<=23")
 		}
-		user.Schedule.Hour = x
+		user.ScheduleHour = x
 	}
 
 	if err := core.UpdateUser(s.state, user); err != nil {
@@ -76,9 +76,9 @@ func (s *Server) editTimings(ctx *fiber.Ctx) error {
 
 	fragmentEmitSuccess(ctx)
 	return ctx.SendString((&views.MainPage{
-		EnableDigests: user.Schedule.Active,
-		SelectedDay:   user.Schedule.Day,
-		SelectedTime:  user.Schedule.Hour,
+		EnableDigests: user.Active,
+		SelectedDay:   user.ScheduleDay,
+		SelectedTime:  user.ScheduleHour,
 	}).RenderScheduleCard())
 }
 
diff --git a/walrss/internal/http/mainpage.go b/walrss/internal/http/mainpage.go
index 1d94d2a..99b26b8 100644
--- a/walrss/internal/http/mainpage.go
+++ b/walrss/internal/http/mainpage.go
@@ -23,9 +23,9 @@ func (s *Server) mainPage(ctx *fiber.Ctx) error {
 	}
 
 	return views.SendPage(ctx, &views.MainPage{
-		EnableDigests: user.Schedule.Active,
-		SelectedDay:   user.Schedule.Day,
-		SelectedTime:  user.Schedule.Hour,
+		EnableDigests: user.Active,
+		SelectedDay:   user.ScheduleDay,
+		SelectedTime:  user.ScheduleHour,
 		Feeds:         feeds,
 	})
 }
diff --git a/walrss/internal/rss/processor.go b/walrss/internal/rss/processor.go
index 6fcca56..8e34466 100644
--- a/walrss/internal/rss/processor.go
+++ b/walrss/internal/rss/processor.go
@@ -80,7 +80,7 @@ func ProcessUserFeed(st *state.State, user *db.User, progressChannel chan string
 	}
 
 	var interval time.Duration
-	if user.Schedule.Day == db.SendDaily || user.Schedule.Day == db.SendDayNever {
+	if user.ScheduleDay == db.SendDaily || user.ScheduleDay == db.SendDayNever {
 		interval = time.Hour * 24
 	} else {
 		interval = time.Hour * 24 * 7
@@ -189,7 +189,7 @@ func filterFeedContent(feed *gofeed.Feed, earliestPublishTime time.Time) []*feed
 		}
 
 		*item.PublishedParsed = item.PublishedParsed.UTC()
-		
+
 		if item.PublishedParsed.After(earliestPublishTime) || item.PublishedParsed.Equal(earliestPublishTime) {
 			o = append(o, &feedItem{
 				Title:       strings.TrimSpace(item.Title),
diff --git a/walrss/internal/state/state.go b/walrss/internal/state/state.go
index 76fad36..a65a261 100644
--- a/walrss/internal/state/state.go
+++ b/walrss/internal/state/state.go
@@ -6,14 +6,14 @@ import (
 	"github.com/kkyr/fig"
 	"github.com/rs/zerolog"
 	"github.com/rs/zerolog/log"
-	bh "github.com/timshannon/bolthold"
+	"github.com/uptrace/bun"
 	"os"
 	"strings"
 )
 
 type State struct {
 	Config *Config
-	Data   *bh.Store
+	Data   *bun.DB
 }
 
 func New() *State {
diff --git a/walrss/main.go b/walrss/main.go
index a61ca51..6d2d135 100644
--- a/walrss/main.go
+++ b/walrss/main.go
@@ -6,6 +6,7 @@ import (
 	"github.com/codemicro/walrss/walrss/internal/rss"
 	"github.com/codemicro/walrss/walrss/internal/state"
 	"github.com/rs/zerolog/log"
+	"github.com/uptrace/bun/extra/bundebug"
 	"os"
 )
 
@@ -28,6 +29,15 @@ func run() error {
 	if err != nil {
 		return err
 	}
+
+	store.AddQueryHook(bundebug.NewQueryHook(
+		bundebug.WithEnabled(st.Config.Debug),
+	))
+
+	if err := db.DoMigrations(store); err != nil {
+		return err
+	}
+
 	st.Data = store
 
 	server, err := http.New(st)