RenewCMS/test/mysql_database_driver_test.go

package test

import (
	"RenewCMS/internal/infrastructure/config"
	"RenewCMS/internal/infrastructure/persistence/database"
	"bytes"
	"context"
	"database/sql"
	"fmt"
	"os/exec"
	"path/filepath"
	"strings"
	"testing"
	"time"

	mysqldriver "github.com/go-sql-driver/mysql"
)

const mysqlTestPassword = "s3cr3t/Value@42:x"

func setMySQLEnv(t *testing.T) {
	t.Helper()
	setBaseSettingsEnv(t)
	t.Setenv("DB_DRIVER", "mysql")
	t.Setenv("DB_HOST", "db.local")
	t.Setenv("DB_USER", "app")
	t.Setenv("DB_PASSWORD", mysqlTestPassword)
	t.Setenv("DB_NAME", "cms")
	for _, optional := range []string{"DB_PORT", "DB_TLS", "DB_MAX_OPEN_CONNS", "DB_MAX_IDLE_CONNS", "DB_CONN_MAX_LIFETIME_SECONDS", "DATA_DIR"} {
		t.Setenv(optional, "")
	}
}

func lookupFrom(values map[string]string) func(string) (string, bool) {
	return func(name string) (string, bool) {
		value, found := values[name]
		return value, found
	}
}

func TestMySQLDriverSelection(t *testing.T) {
	t.Run("Driver selection", func(t *testing.T) {
		cases := []struct {
			value  string
			set    bool
			driver string
		}{
			{"", false, "sqlite"},
			{"sqlite", true, "sqlite"},
			{"mysql", true, "mysql"},
			{"oracle", true, "oracle"},
		}
		for _, tc := range cases {
			t.Run(fmt.Sprintf("DB_DRIVER %q", tc.value), func(t *testing.T) {
				setMySQLEnv(t)
				if tc.set {
					t.Setenv("DB_DRIVER", tc.value)
				} else {
					t.Setenv("DB_DRIVER", "")
				}

				settings, err := config.NewSettingsFromEnv()

				if err != nil {
					t.Fatalf("settings: %v", err)
				}
				if settings.Database.Driver != tc.driver {
					t.Errorf("expected driver %q, got %q", tc.driver, settings.Database.Driver)
				}
			})
		}
	})

	t.Run("an unregistered driver fails with its name", func(t *testing.T) {
		_, err := database.Connect(database.Config{Driver: "oracle", Source: "x"})

		if err == nil || err.Error() != `unsupported database driver "oracle"` {
			t.Errorf("unexpected error %v", err)
		}
	})
}

func TestMySQLEnvironmentValidation(t *testing.T) {
	complete := map[string]string{"DB_HOST": "h", "DB_USER": "u", "DB_PASSWORD": "p", "DB_NAME": "n"}

	t.Run("Missing MySQL variable", func(t *testing.T) {
		for _, missing := range []string{"DB_HOST", "DB_USER", "DB_PASSWORD", "DB_NAME"} {
			t.Run(missing, func(t *testing.T) {
				values := map[string]string{}
				for name, value := range complete {
					if name != missing {
						values[name] = value
					}
				}

				err := config.MissingEnvVarError(config.DatabaseRequiredEnvVars("mysql"), lookupFrom(values))

				expected := "Environment variable " + missing + " is not set"
				if err == nil || err.Error() != expected {
					t.Errorf("expected %q, got %v", expected, err)
				}
			})
		}
	})

	t.Run("SQLite does not require MySQL variables", func(t *testing.T) {
		for _, driver := range []string{"sqlite", "", "oracle"} {
			if err := config.MissingEnvVarError(config.DatabaseRequiredEnvVars(driver), lookupFrom(nil)); err != nil {
				t.Errorf("driver %q: expected success, got %v", driver, err)
			}
		}
	})

	t.Run("all MySQL variables set passes validation", func(t *testing.T) {
		if err := config.MissingEnvVarError(config.DatabaseRequiredEnvVars("mysql"), lookupFrom(complete)); err != nil {
			t.Errorf("expected success, got %v", err)
		}
	})
}

func TestMySQLSettingsFromEnv(t *testing.T) {
	t.Run("defaults apply when the optional variables are empty", func(t *testing.T) {
		setMySQLEnv(t)

		settings, err := config.NewSettingsFromEnv()

		if err != nil {
			t.Fatalf("settings: %v", err)
		}
		database := settings.Database
		if database.Port != "3306" || database.TLS != "false" || database.MaxOpenConns != 10 || database.MaxIdleConns != 5 || database.ConnMaxLifetime != 300*time.Second {
			t.Errorf("unexpected defaults %+v", database)
		}
		if database.Host != "db.local" || database.User != "app" || database.Password != mysqlTestPassword || database.Name != "cms" {
			t.Errorf("unexpected connection fields %+v", database)
		}
	})

	t.Run("Invalid TLS option is rejected", func(t *testing.T) {
		setMySQLEnv(t)
		t.Setenv("DB_TLS", "maybe")

		_, err := config.NewSettingsFromEnv()

		if err == nil {
			t.Fatal("expected an error")
		}
		for _, expected := range []string{"DB_TLS", "false", "true", "skip-verify", "preferred"} {
			if !strings.Contains(err.Error(), expected) {
				t.Errorf("error %q must mention %q", err, expected)
			}
		}
	})

	t.Run("every accepted TLS option is read", func(t *testing.T) {
		for _, mode := range []string{"false", "true", "skip-verify", "preferred"} {
			setMySQLEnv(t)
			t.Setenv("DB_TLS", mode)

			settings, err := config.NewSettingsFromEnv()

			if err != nil || settings.Database.TLS != mode {
				t.Errorf("mode %q: settings %+v, err %v", mode, settings.Database, err)
			}
		}
	})

	t.Run("a non numeric pool variable names the variable", func(t *testing.T) {
		for _, name := range []string{"DB_MAX_OPEN_CONNS", "DB_MAX_IDLE_CONNS", "DB_CONN_MAX_LIFETIME_SECONDS"} {
			setMySQLEnv(t)
			t.Setenv(name, "many")

			_, err := config.NewSettingsFromEnv()

			if err == nil || !strings.Contains(err.Error(), name) {
				t.Errorf("%s: expected an error naming it, got %v", name, err)
			}
		}
	})

	t.Run("a negative pool variable is rejected", func(t *testing.T) {
		setMySQLEnv(t)
		t.Setenv("DB_MAX_OPEN_CONNS", "-1")

		if _, err := config.NewSettingsFromEnv(); err == nil {
			t.Error("expected an error")
		}
	})

	t.Run("zero is an accepted pool value", func(t *testing.T) {
		setMySQLEnv(t)
		t.Setenv("DB_MAX_OPEN_CONNS", "0")

		settings, err := config.NewSettingsFromEnv()

		if err != nil || settings.Database.MaxOpenConns != 0 {
			t.Errorf("unexpected settings %+v, err %v", settings.Database, err)
		}
	})

	t.Run("pool variables are read", func(t *testing.T) {
		setMySQLEnv(t)
		t.Setenv("DB_MAX_OPEN_CONNS", "4")
		t.Setenv("DB_MAX_IDLE_CONNS", "2")
		t.Setenv("DB_CONN_MAX_LIFETIME_SECONDS", "60")
		t.Setenv("DB_PORT", "3307")

		settings, err := config.NewSettingsFromEnv()

		database := settings.Database
		if err != nil || database.MaxOpenConns != 4 || database.MaxIdleConns != 2 || database.ConnMaxLifetime != 60*time.Second || database.Port != "3307" {
			t.Errorf("unexpected settings %+v, err %v", database, err)
		}
	})

	t.Run("SQLite ignores the MySQL variables", func(t *testing.T) {
		setMySQLEnv(t)
		t.Setenv("DB_DRIVER", "sqlite")
		t.Setenv("DB_TLS", "maybe")
		t.Setenv("DB_MAX_OPEN_CONNS", "many")

		settings, err := config.NewSettingsFromEnv()

		if err != nil || settings.Database.Driver != "sqlite" || settings.Database.Host != "" {
			t.Errorf("unexpected settings %+v, err %v", settings.Database, err)
		}
	})
}

func TestMySQLDSN(t *testing.T) {
	t.Run("DSN built from variables", func(t *testing.T) {
		cases := []struct {
			host, port, expectedPort, tls string
		}{
			{"db.local", "", "3306", "false"},
			{"db.local", "3307", "3307", "true"},
			{"mysql", "3306", "3306", "skip-verify"},
		}
		for _, tc := range cases {
			t.Run(tc.host+":"+tc.port+" tls "+tc.tls, func(t *testing.T) {
				port := tc.port
				if port == "" {
					port = config.DefaultDatabasePort
				}

				dsn := database.MySQLDSN(database.MySQLConfig{Host: tc.host, Port: port, User: "app", Password: "pw", Name: "cms", TLS: tc.tls})

				parsed, err := mysqldriver.ParseDSN(dsn)
				if err != nil {
					t.Fatalf("parse: %v", err)
				}
				if parsed.Addr != tc.host+":"+tc.expectedPort || parsed.DBName != "cms" || parsed.User != "app" {
					t.Errorf("unexpected target %q db %q user %q", parsed.Addr, parsed.DBName, parsed.User)
				}
				for _, fragment := range []string{"parseTime=true", "loc=UTC", "charset=utf8mb4", "clientFoundRows=true", "tls=" + tc.tls} {
					if !strings.Contains(dsn, fragment) {
						t.Errorf("dsn must contain %q", fragment)
					}
				}
				if parsed.Timeout <= 0 || parsed.Timeout > 10*time.Second {
					t.Errorf("connect timeout must be short, got %v", parsed.Timeout)
				}
			})
		}
	})

	t.Run("a password with reserved characters survives the DSN", func(t *testing.T) {
		dsn := database.MySQLDSN(database.MySQLConfig{Host: "db.local", Port: "3306", User: "app", Password: mysqlTestPassword, Name: "cms", TLS: "false"})

		parsed, err := mysqldriver.ParseDSN(dsn)

		if err != nil || parsed.Passwd != mysqlTestPassword || parsed.DBName != "cms" || parsed.Addr != "db.local:3306" {
			t.Errorf("unexpected parse %+v err %v", parsed, err)
		}
	})

	t.Run("the connection uses a binary collation so Bob and bob stay distinct", func(t *testing.T) {
		parsed, _ := mysqldriver.ParseDSN(database.MySQLDSN(database.MySQLConfig{Host: "h", Port: "1", User: "u", Name: "n", TLS: "false"}))

		if parsed.Collation != "utf8mb4_bin" {
			t.Errorf("unexpected collation %q", parsed.Collation)
		}
	})
}

func TestMySQLDataDir(t *testing.T) {
	t.Run("DataDir resolution", func(t *testing.T) {
		cases := []struct{ dbFile, dataDir, result string }{
			{"./data/renewcms.db", "", "./data"},
			{"/var/lib/cms/cms.db", "", "/var/lib/cms"},
			{"./data/renewcms.db", "/srv/x", "/srv/x"},
		}
		for _, tc := range cases {
			t.Run(tc.dbFile+" "+tc.dataDir, func(t *testing.T) {
				settings := config.Settings{DBFile: tc.dbFile, DataDirPath: tc.dataDir}

				if got := settings.DataDir(); filepath.Clean(got) != filepath.Clean(tc.result) {
					t.Errorf("expected %q, got %q", tc.result, got)
				}
			})
		}
	})

	t.Run("Data directory with MySQL", func(t *testing.T) {
		setMySQLEnv(t)
		t.Setenv("DATA_DIR", "/data")
		t.Setenv("DB_FILE", "./elsewhere/ignored.db")

		settings, err := config.NewSettingsFromEnv()

		if err != nil || filepath.Clean(settings.DataDir()) != filepath.Clean("/data") {
			t.Errorf("unexpected data dir %q err %v", settings.DataDir(), err)
		}
	})
}

func TestMySQLConnectionPool(t *testing.T) {
	t.Run("Connection pool", func(t *testing.T) {
		pool := database.PoolSettings{MaxOpenConns: 4, MaxIdleConns: 2, ConnMaxLifetime: 60 * time.Second}
		connection, err := database.Connect(database.Config{Driver: database.DriverSQLite, Source: filepath.ToSlash(filepath.Join(t.TempDir(), "pool.db")), Pool: &pool})
		if err != nil {
			t.Fatalf("connect: %v", err)
		}
		defer connection.Close()
		sqlDb, _ := connection.DB.DB()

		held := make([]*sql.Conn, 0, 4)
		for range 4 {
			conn, err := sqlDb.Conn(context.Background())
			if err != nil {
				t.Fatalf("conn: %v", err)
			}
			held = append(held, conn)
		}
		blocked, cancel := context.WithTimeout(context.Background(), 100*time.Millisecond)
		defer cancel()
		_, fifthErr := sqlDb.Conn(blocked)
		for _, conn := range held {
			_ = conn.Close()
		}

		stats := sqlDb.Stats()
		if stats.MaxOpenConnections != 4 {
			t.Errorf("expected 4 open connections at most, got %d", stats.MaxOpenConnections)
		}
		if fifthErr == nil {
			t.Error("a fifth concurrent connection must wait for the pool")
		}
		if stats.Idle != 2 {
			t.Errorf("expected 2 idle connections, got %d", stats.Idle)
		}
	})

	t.Run("the connection lifetime closes old connections", func(t *testing.T) {
		pool := database.PoolSettings{MaxOpenConns: 1, MaxIdleConns: 1, ConnMaxLifetime: 20 * time.Millisecond}
		connection, err := database.Connect(database.Config{Driver: database.DriverSQLite, Source: filepath.ToSlash(filepath.Join(t.TempDir(), "life.db")), Pool: &pool})
		if err != nil {
			t.Fatalf("connect: %v", err)
		}
		defer connection.Close()
		sqlDb, _ := connection.DB.DB()
		_ = sqlDb.Ping()

		time.Sleep(100 * time.Millisecond)
		_ = sqlDb.Ping()

		if sqlDb.Stats().MaxLifetimeClosed == 0 {
			t.Error("an expired connection must be closed")
		}
	})

	t.Run("settings without a pool leave the defaults untouched", func(t *testing.T) {
		connection, err := database.Connect(database.Config{Driver: database.DriverSQLite, Source: filepath.ToSlash(filepath.Join(t.TempDir(), "plain.db"))})
		if err != nil {
			t.Fatalf("connect: %v", err)
		}
		defer connection.Close()
		sqlDb, _ := connection.DB.DB()

		if sqlDb.Stats().MaxOpenConnections != 0 {
			t.Errorf("expected an unlimited pool, got %d", sqlDb.Stats().MaxOpenConnections)
		}
	})
}

func closedLocalPort(t *testing.T) string {
	t.Helper()
	return freeTCPPort(t)
}

func mysqlDSNWithTimeout(config database.MySQLConfig, timeout time.Duration) string {
	parsed, _ := mysqldriver.ParseDSN(database.MySQLDSN(config))
	parsed.Timeout = timeout
	return parsed.FormatDSN()
}

func TestMySQLFailFast(t *testing.T) {
	t.Run("nothing listens on DB_PORT", func(t *testing.T) {
		port := closedLocalPort(t)
		dsn := mysqlDSNWithTimeout(database.MySQLConfig{Host: "127.0.0.1", Port: port, User: "app", Password: mysqlTestPassword, Name: "cms", TLS: "false"}, time.Second)
		started := time.Now()

		_, err := database.Connect(database.Config{Driver: database.DriverMySQL, Source: dsn})

		if err == nil {
			t.Fatal("expected an error")
		}
		if !strings.Contains(err.Error(), "127.0.0.1:"+port) {
			t.Errorf("the error must name the host and port: %v", err)
		}
		if strings.Contains(err.Error(), mysqlTestPassword) {
			t.Errorf("the error must not contain the password: %v", err)
		}
		if elapsed := time.Since(started); elapsed > 4*time.Second {
			t.Errorf("startup must fail fast, took %v", elapsed)
		}
	})

	t.Run("an empty password leaves the error message untouched", func(t *testing.T) {
		port := closedLocalPort(t)
		dsn := mysqlDSNWithTimeout(database.MySQLConfig{Host: "127.0.0.1", Port: port, User: "app", Name: "cms", TLS: "false"}, time.Second)

		_, err := database.Connect(database.Config{Driver: database.DriverMySQL, Source: dsn})

		if err == nil {
			t.Fatal("expected an error")
		}
		if strings.Contains(err.Error(), "***") {
			t.Errorf("an empty password must not be redacted into the message: %v", err)
		}
		if !strings.Contains(err.Error(), "127.0.0.1:"+port) {
			t.Errorf("the error must name the host and port: %v", err)
		}
	})

	t.Run("an unusable DSN is rejected without echoing it", func(t *testing.T) {
		_, err := database.Connect(database.Config{Driver: database.DriverMySQL, Source: "user:" + mysqlTestPassword + "@broken"})

		if err == nil || strings.Contains(err.Error(), mysqlTestPassword) {
			t.Errorf("unexpected error %v", err)
		}
	})
}

func startupEnvironment(t *testing.T, extra ...string) []string {
	t.Helper()
	dataDir := t.TempDir()
	return append(emptyEnvironment(),
		append([]string{
			"ENVIRONMENT=development", "PORT=" + freeTCPPort(t), "PUBLIC_URL=http://localhost", "JWT_SECRET=test-secret-for-startup",
			"SMTP_LOGIN=a@example.com", "SMTP_SENDER=a@example.com", "SMTP_PASSWORD=x", "SMTP_HOST=localhost", "SMTP_PORT=2525",
			"DB_FILE=" + filepath.Join(dataDir, "renewcms.db"), "UPLOAD_DIR=" + filepath.Join(dataDir, "uploads"),
			"UPDATE_FEED_URL=https://git.example",
		}, extra...)...)
}

func runServerWithEnvironment(t *testing.T, binary string, environment []string) (string, int) {
	t.Helper()
	command := exec.Command(binary)
	command.Dir = t.TempDir()
	command.Env = environment
	var output bytes.Buffer
	command.Stdout, command.Stderr = &output, &output
	err := command.Run()
	if exitErr, ok := err.(*exec.ExitError); ok {
		return output.String(), exitErr.ExitCode()
	}
	if err != nil {
		t.Fatal(err)
	}
	return output.String(), 0
}

func TestMySQLStartupFailures(t *testing.T) {
	binary := buildServerBinary(t, "")

	t.Run("an unsupported driver stops the server", func(t *testing.T) {
		output, code := runServerWithEnvironment(t, binary, startupEnvironment(t, "DB_DRIVER=oracle"))

		if code == 0 || !strings.Contains(output, `unsupported database driver "oracle"`) {
			t.Errorf("code %d output %q", code, output)
		}
	})

	t.Run("Missing MySQL variable stops the server", func(t *testing.T) {
		complete := map[string]string{"DB_HOST": "127.0.0.1", "DB_USER": "app", "DB_PASSWORD": mysqlTestPassword, "DB_NAME": "cms"}
		for missing := range complete {
			t.Run(missing, func(t *testing.T) {
				extra := []string{"DB_DRIVER=mysql"}
				for name, value := range complete {
					if name != missing {
						extra = append(extra, name+"="+value)
					}
				}

				output, code := runServerWithEnvironment(t, binary, startupEnvironment(t, extra...))

				if code == 0 || !strings.Contains(output, "Environment variable "+missing+" is not set") {
					t.Errorf("code %d output %q", code, output)
				}
				if strings.Contains(output, mysqlTestPassword) {
					t.Errorf("the output must not contain the password: %q", output)
				}
			})
		}
	})

	t.Run("an invalid DB_TLS stops the server naming the variable", func(t *testing.T) {
		output, code := runServerWithEnvironment(t, binary, startupEnvironment(t,
			"DB_DRIVER=mysql", "DB_HOST=127.0.0.1", "DB_USER=app", "DB_PASSWORD="+mysqlTestPassword, "DB_NAME=cms", "DB_TLS=maybe"))

		if code == 0 || !strings.Contains(output, "DB_TLS") || strings.Contains(output, mysqlTestPassword) {
			t.Errorf("code %d output %q", code, output)
		}
	})

	t.Run("Fail fast when the server cannot be used", func(t *testing.T) {
		port := closedLocalPort(t)

		output, code := runServerWithEnvironment(t, binary, startupEnvironment(t,
			"DB_DRIVER=mysql", "DB_HOST=127.0.0.1", "DB_PORT="+port, "DB_USER=app", "DB_PASSWORD="+mysqlTestPassword, "DB_NAME=cms"))

		if code == 0 || !strings.Contains(output, "Unable to open the database") {
			t.Errorf("code %d output %q", code, output)
		}
		if !strings.Contains(output, "127.0.0.1:"+port) {
			t.Errorf("the message must name the host and port: %q", output)
		}
		if strings.Contains(output, mysqlTestPassword) {
			t.Errorf("the message must not contain the password: %q", output)
		}
	})

	t.Run("the SQLite default starts without any MySQL variable", func(t *testing.T) {
		output, code := runServerWithEnvironment(t, binary, startupEnvironment(t, "PORT=99999"))

		bindFailed := strings.Contains(output, "invalid port")
		if code == 0 || !bindFailed || strings.Contains(output, "Environment variable DB_") {
			t.Errorf("startup must get as far as binding the port, code %d output %q", code, output)
		}
	})
}

func TestMySQLSettingsAreNeverPrinted(t *testing.T) {
	t.Run("Secrets are never exposed", func(t *testing.T) {
		setMySQLEnv(t)
		settings, err := config.NewSettingsFromEnv()
		if err != nil {
			t.Fatal(err)
		}

		dsn := mysqlDSNWithTimeout(database.MySQLConfig{Host: "127.0.0.1", Port: closedLocalPort(t), User: settings.Database.User, Password: settings.Database.Password, Name: settings.Database.Name, TLS: settings.Database.TLS}, time.Second)
		_, err = database.Connect(database.Config{Driver: database.DriverMySQL, Source: dsn})

		if err == nil || strings.Contains(err.Error(), settings.Database.Password) {
			t.Errorf("unexpected error %v", err)
		}
	})
}