package db_test
import (
"context"
"database/sql"
"math/rand"
"os"
"path/filepath"
"time"
"github.com/navidrome/navidrome/conf"
"github.com/navidrome/navidrome/conf/configtest"
. "github.com/navidrome/navidrome/db"
"github.com/navidrome/navidrome/tests"
"github.com/navidrome/navidrome/utils/singleton"
. "github.com/onsi/ginkgo/v2"
. "github.com/onsi/gomega"
)
func shortTime(year int, month time.Month, day, hour, minute int) time.Time {
return time.Date(year, month, day, hour, minute, 0, 0, time.UTC)
}
const (
gooseTableDDL = "CREATE TABLE goose_db_version(version_id, is_applied)"
navidromeTablesDDL = "CREATE TABLE user(id); CREATE TABLE media_file(id)"
)
func sqliteFile(statements string) func(path string) {
return func(path string) {
GinkgoHelper()
other, err := sql.Open(Driver, path)
Expect(err).ToNot(HaveOccurred())
defer other.Close()
_, err = other.Exec(statements)
Expect(err).ToNot(HaveOccurred())
}
}
var _ = Describe("database backups", func() {
When("there are a few backup files", func() {
var ctx context.Context
var timesShuffled []time.Time
timesDecreasingChronologically := []time.Time{
shortTime(2024, 11, 6, 5, 11),
shortTime(2024, 11, 6, 5, 8),
shortTime(2024, 11, 6, 4, 32),
shortTime(2024, 11, 6, 2, 4),
shortTime(2024, 11, 6, 1, 52),
shortTime(2024, 11, 5, 23, 0),
shortTime(2024, 11, 5, 6, 4),
shortTime(2024, 11, 4, 2, 4),
shortTime(2024, 11, 3, 8, 5),
shortTime(2024, 11, 2, 5, 24),
shortTime(2024, 11, 1, 5, 24),
shortTime(2024, 10, 31, 5, 9),
shortTime(2024, 10, 30, 5, 9),
shortTime(2024, 10, 23, 14, 3),
shortTime(2024, 10, 22, 3, 6),
shortTime(2024, 10, 11, 14, 3),
shortTime(2024, 9, 21, 19, 5),
shortTime(2024, 9, 3, 8, 5),
shortTime(2024, 7, 5, 1, 1),
shortTime(2023, 8, 2, 19, 5),
shortTime(2021, 8, 2, 19, 5),
shortTime(2020, 8, 2, 19, 5),
}
BeforeEach(func() {
DeferCleanup(configtest.SetupConfig())
tempFolder, err := os.MkdirTemp("", "navidrome_backup")
Expect(err).ToNot(HaveOccurred())
conf.Server.Backup.Path = conf.NewDir(tempFolder)
DeferCleanup(func() {
_ = os.RemoveAll(tempFolder)
})
timesShuffled = make([]time.Time, len(timesDecreasingChronologically))
copy(timesShuffled, timesDecreasingChronologically)
rand.Shuffle(len(timesShuffled), func(i, j int) {
timesShuffled[i], timesShuffled[j] = timesShuffled[j], timesShuffled[i]
})
for _, time := range timesShuffled {
path := BackupPath(time)
file, err := os.Create(path)
Expect(err).ToNot(HaveOccurred())
_ = file.Close()
}
ctx = context.Background()
})
DescribeTable("prune", func(count, expected int) {
conf.Server.Backup.Count = count
pruneCount, err := Prune(ctx)
Expect(err).ToNot(HaveOccurred())
for idx, time := range timesDecreasingChronologically {
_, err := os.Stat(BackupPath(time))
shouldExist := idx < conf.Server.Backup.Count
if shouldExist {
Expect(err).ToNot(HaveOccurred())
} else {
Expect(err).To(MatchError(os.ErrNotExist))
}
}
Expect(len(timesDecreasingChronologically) - pruneCount).To(Equal(expected))
},
Entry("preserve latest 5 backups", 5, 5),
Entry("delete all files", 0, 0),
Entry("preserve all files when at length", len(timesDecreasingChronologically), len(timesDecreasingChronologically)),
Entry("preserve all files when less than count", 10000, len(timesDecreasingChronologically)))
It("ignores SQLite sidecar files when counting backups", func() {
for _, suffix := range []string{"-shm", "-wal"} {
file, err := os.Create(BackupPath(timesDecreasingChronologically[0]) + suffix)
Expect(err).ToNot(HaveOccurred())
_ = file.Close()
}
conf.Server.Backup.Count = len(timesDecreasingChronologically)
pruneCount, err := Prune(ctx)
Expect(err).ToNot(HaveOccurred())
Expect(pruneCount).To(BeZero())
})
})
Describe("backup and restore", Ordered, func() {
var ctx context.Context
BeforeAll(func() {
ctx = context.Background()
DeferCleanup(configtest.SetupConfig())
conf.Server.DbPath = "file::memory:?cache=shared&_foreign_keys=on"
DeferCleanup(Init(ctx))
})
BeforeEach(func() {
tempFolder, err := os.MkdirTemp("", "navidrome_backup")
Expect(err).ToNot(HaveOccurred())
conf.Server.Backup.Path = conf.NewDir(tempFolder)
DeferCleanup(func() {
_ = os.RemoveAll(tempFolder)
})
})
It("successfully backups the database", func() {
path, err := Backup(ctx)
Expect(err).ToNot(HaveOccurred())
backup, err := sql.Open(Driver, path)
Expect(err).ToNot(HaveOccurred())
Expect(IsSchemaEmpty(ctx, backup)).To(BeFalse())
})
It("successfully restores the database", func() {
path, err := Backup(ctx)
Expect(err).ToNot(HaveOccurred())
err = tests.ClearDB()
Expect(err).ToNot(HaveOccurred())
Expect(IsSchemaEmpty(ctx, Db())).To(BeTrue())
err = Restore(ctx, path, false)
Expect(err).ToNot(HaveOccurred())
Expect(IsSchemaEmpty(ctx, Db())).To(BeFalse())
})
})
Describe("backup and restore with a file-based database", Ordered, func() {
var ctx context.Context
var tempFolder string
var dbFilePath string
BeforeAll(func() {
ctx = context.Background()
DeferCleanup(configtest.SetupConfig())
var err error
tempFolder, err = os.MkdirTemp("", "navidrome_restore")
Expect(err).ToNot(HaveOccurred())
conf.Server.Backup.Path = conf.NewDir(tempFolder)
DeferCleanup(func() {
Close(ctx)
_ = os.RemoveAll(tempFolder)
})
dbFilePath = filepath.Join(tempFolder, "navidrome.db")
conf.Server.DbPath = dbFilePath + "?_busy_timeout=15000&_journal_mode=WAL&_foreign_keys=on&synchronous=normal"
singleton.DeleteInstance[*sql.DB]()
DeferCleanup(Init(ctx))
})
It("restores data into a database whose stale WAL sidecar files were left behind", func() {
By("seeding user data in the current database")
_, err := Db().ExecContext(ctx, `INSERT INTO user (id, user_name, name, email, password, is_admin, created_at, updated_at)
VALUES ('u-restore-1', 'drilladmin', 'drilladmin', 'drilladmin@example.com', 'x', 1, datetime('now'), datetime('now'))`)
Expect(err).ToNot(HaveOccurred())
By("creating a backup containing the user row")
path, err := Backup(ctx)
Expect(err).ToNot(HaveOccurred())
By("simulating the CLI exiting without closing the pool: sidecar files stay behind")
_, err = Db().ExecContext(ctx, "CREATE TABLE IF NOT EXISTS _restore_probe(x)")
Expect(err).ToNot(HaveOccurred())
singleton.DeleteInstance[*sql.DB]()
err = tests.ClearDB()
Expect(err).ToNot(HaveOccurred())
By("restoring the backup")
Expect(Restore(ctx, path, false)).To(Succeed())
By("verifying the restored data is readable through a fresh connection")
singleton.DeleteInstance[*sql.DB]()
var userName string
Expect(Db().QueryRowContext(ctx, "SELECT user_name FROM user WHERE id = 'u-restore-1'").Scan(&userName)).To(Succeed())
Expect(userName).To(Equal("drilladmin"))
})
expectRestoreRefused := func(path, expectedErr string) {
GinkgoHelper()
By("seeding user data in the current database")
_, err := Db().ExecContext(ctx, `INSERT OR REPLACE INTO user (id, user_name, name, email, password, is_admin, created_at, updated_at)
VALUES ('u-restore-2', 'keepme', 'keepme', 'keepme@example.com', 'x', 1, datetime('now'), datetime('now'))`)
Expect(err).ToNot(HaveOccurred())
By("attempting the restore")
Expect(Restore(ctx, path, false)).To(MatchError(ContainSubstring(expectedErr)))
By("verifying the database was not wiped")
var userName string
Expect(Db().QueryRowContext(ctx, "SELECT user_name FROM user WHERE id = 'u-restore-2'").Scan(&userName)).To(Succeed())
Expect(userName).To(Equal("keepme"))
}
It("fails to restore from a backup file that does not exist, leaving the database intact", func() {
missingPath := filepath.Join(tempFolder, "does_not_exist.db")
expectRestoreRefused(missingPath, "backup file not available")
_, statErr := os.Stat(missingPath)
Expect(statErr).To(MatchError(os.ErrNotExist))
})
DescribeTable("fails to restore from a file that is not a Navidrome database, leaving the database intact",
func(createFile func(path string), expectedErr string) {
invalidPath := filepath.Join(GinkgoT().TempDir(), "invalid_backup.db")
createFile(invalidPath)
expectRestoreRefused(invalidPath, expectedErr)
},
Entry("empty file", func(path string) {
Expect(os.WriteFile(path, nil, 0600)).To(Succeed())
}, "is not a Navidrome database"),
Entry("file that is not a SQLite database", func(path string) {
Expect(os.WriteFile(path, []byte("this is not a database, just some text"), 0600)).To(Succeed())
}, "reading backup file"),
Entry("SQLite database without the Navidrome schema",
sqliteFile("CREATE TABLE something_else(x)"), "is not a Navidrome database"),
Entry("another project's goose database",
sqliteFile(gooseTableDDL+"; INSERT INTO goose_db_version VALUES (1, 1)"), "is not a Navidrome database"),
Entry("goose database with only one of the Navidrome tables",
sqliteFile(gooseTableDDL+"; INSERT INTO goose_db_version VALUES (1, 1); CREATE TABLE media_file(id)"), "is not a Navidrome database"),
Entry("SQLite database with an empty goose_db_version table",
sqliteFile(gooseTableDDL+"; "+navidromeTablesDDL), "has no applied migrations"),
Entry("SQLite database with only goose's initial version row",
sqliteFile(gooseTableDDL+"; "+navidromeTablesDDL+"; INSERT INTO goose_db_version VALUES (0, 1)"), "has no applied migrations"),
Entry("SQLite database whose migrations were all rolled back",
sqliteFile(gooseTableDDL+"; "+navidromeTablesDDL+"; INSERT INTO goose_db_version VALUES (1, 0)"), "has no applied migrations"),
Entry("backup with garbage over its last pages", func(path string) {
_, err := Db().ExecContext(ctx, "CREATE TABLE _restore_filler AS SELECT zeroblob(1000000) AS x")
Expect(err).ToNot(HaveOccurred())
backupFile, err := Backup(ctx)
Expect(err).ToNot(HaveOccurred())
data, err := os.ReadFile(backupFile)
Expect(err).ToNot(HaveOccurred())
for i := len(data) * 4 / 5; i < len(data); i++ {
data[i] = 0xFF
}
Expect(os.WriteFile(path, data, 0600)).To(Succeed())
}, "is damaged"),
)
It("restores a good backup with the integrity check", func() {
path, err := Backup(ctx)
Expect(err).ToNot(HaveOccurred())
Expect(Restore(ctx, path, true)).To(Succeed())
})
It("refuses an index that doesn't match its table only with the integrity check", func() {
path, err := Backup(ctx)
Expect(err).ToNot(HaveOccurred())
writeMismatchedIndex(ctx, path, 1)
restored := func() int {
var count int
Expect(Db().QueryRowContext(ctx, "SELECT count(*) FROM sqlite_master WHERE name = 't'").Scan(&count)).To(Succeed())
return count
}
Expect(Restore(ctx, path, true)).To(MatchError(ContainSubstring("is damaged")))
Expect(restored()).To(BeZero())
Expect(Restore(ctx, path, false)).To(Succeed())
Expect(restored()).To(Equal(1))
})
})
})
