[feature] utilize system's libsqlite3

Add support for using the system's libsqlite3 library but switching from
"modernc.org/sqlite" to "github.com/mattn/go-sqlite3". This changeset
also allows the server administrator to fully configure the connection
string, including adjustments to journaling and vacuum modes.

2 files changed, 11 insertions, 46 deletions

diff --git a/internal/db/bundb/bundb.go b/internal/db/bundb/bundb.go
index 2fc65364f..8909e0f60 100644
--- a/internal/db/bundb/bundb.go
+++ b/internal/db/bundb/bundb.go
@@ -31,7 +31,6 @@ import (
 	"strings"
 	"time"
 
-	"github.com/google/uuid"
 	"github.com/jackc/pgx/v4"
 	"github.com/jackc/pgx/v4/stdlib"
 	"github.com/superseriousbusiness/gotosocial/internal/cache"
@@ -47,7 +46,6 @@ import (
 	"github.com/uptrace/bun/migrate"
 
 	grufcache "codeberg.org/gruf/go-cache/v2"
-	"modernc.org/sqlite"
 )
 
 const (
@@ -233,48 +231,19 @@ func sqliteConn(ctx context.Context) (*DBConn, error) {
 		return nil, fmt.Errorf("'%s' was not set when attempting to start sqlite", config.DbAddressFlag())
 	}
 
-	// Drop anything fancy from DB address
-	dbAddress = strings.Split(dbAddress, "?")[0]
-	dbAddress = strings.TrimPrefix(dbAddress, "file:")
-
-	// Append our own SQLite preferences
-	dbAddress = "file:" + dbAddress + "?cache=shared"
-
-	var inMem bool
-
-	if dbAddress == "file::memory:?cache=shared" {
-		dbAddress = fmt.Sprintf("file:%s?mode=memory&cache=shared", uuid.NewString())
-		log.Infof("using in-memory database address " + dbAddress)
-		log.Warn("sqlite in-memory database should only be used for debugging")
-		inMem = true
-	}
-
 	// Open new DB instance
 	sqldb, err := sql.Open("sqlite", dbAddress)
 	if err != nil {
-		if errWithCode, ok := err.(*sqlite.Error); ok {
-			err = errors.New(sqlite.ErrorCodeString[errWithCode.Code()])
-		}
-		return nil, fmt.Errorf("could not open sqlite db: %s", err)
+		return nil, fmt.Errorf("could not open sqlite db: %w", err)
 	}
 
 	tweakConnectionValues(sqldb)
 
-	if inMem {
-		// don't close connections on disconnect -- otherwise
-		// the SQLite database will be deleted when there
-		// are no active connections
-		sqldb.SetConnMaxLifetime(0)
-	}
-
 	conn := WrapDBConn(bun.NewDB(sqldb, sqlitedialect.New()))
 
 	// ping to check the db is there and listening
 	if err := conn.PingContext(ctx); err != nil {
-		if errWithCode, ok := err.(*sqlite.Error); ok {
-			err = errors.New(sqlite.ErrorCodeString[errWithCode.Code()])
-		}
-		return nil, fmt.Errorf("sqlite ping: %s", err)
+		return nil, fmt.Errorf("sqlite ping: %w", err)
 	}
 
 	log.Info("connected to SQLITE database")
diff --git a/internal/db/bundb/errors.go b/internal/db/bundb/errors.go
index 7d0157373..1c0f8a7ff 100644
--- a/internal/db/bundb/errors.go
+++ b/internal/db/bundb/errors.go
@@ -1,10 +1,11 @@
 package bundb
 
 import (
+	"errors"
+
 	"github.com/jackc/pgconn"
+	"github.com/mattn/go-sqlite3"
 	"github.com/superseriousbusiness/gotosocial/internal/db"
-	"modernc.org/sqlite"
-	sqlite3 "modernc.org/sqlite/lib"
 )
 
 // processPostgresError processes an error, replacing any postgres specific errors with our own error type
@@ -27,17 +28,12 @@ func processPostgresError(err error) db.Error {
 
 // processSQLiteError processes an error, replacing any sqlite specific errors with our own error type
 func processSQLiteError(err error) db.Error {
-	// Attempt to cast as sqlite
-	sqliteErr, ok := err.(*sqlite.Error)
-	if !ok {
-		return err
+	var sqlError sqlite3.Error
+	if errors.As(err, &sqlError) {
+		if sqlError.Code == sqlite3.ErrConstraint && (sqlError.ExtendedCode == sqlite3.ErrConstraintUnique || sqlError.ExtendedCode == sqlite3.ErrConstraintPrimaryKey) {
+			return db.ErrAlreadyExists
+		}
 	}
 
-	// Handle supplied error code:
-	switch sqliteErr.Code() {
-	case sqlite3.SQLITE_CONSTRAINT_UNIQUE, sqlite3.SQLITE_CONSTRAINT_PRIMARYKEY:
-		return db.ErrAlreadyExists
-	default:
-		return err
-	}
+	return err
 }