460 lines
17 KiB
Go
460 lines
17 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"errors"
|
|
"fmt"
|
|
"regexp"
|
|
"strings"
|
|
"time"
|
|
|
|
mysqldriver "github.com/go-sql-driver/mysql"
|
|
)
|
|
|
|
// ── SQLite → MySQL schema translation ──────────────────────────────────
|
|
//
|
|
// OpsLog's migrations are authored in SQLite dialect (the default backend).
|
|
// For the shared-MySQL backend we translate that DDL on the fly rather than
|
|
// maintaining a second set of migration files, so the two backends can never
|
|
// drift apart. The translation is deliberately narrow — it only rewrites the
|
|
// SQLite-isms that actually appear in our migrations:
|
|
//
|
|
// - INTEGER PRIMARY KEY [AUTOINCREMENT] → BIGINT AUTO_INCREMENT PRIMARY KEY
|
|
// - bare INTEGER → BIGINT (so FK child/parent types match)
|
|
// - DEFAULT (strftime(... 'now')) → DEFAULT '' (timestamps come from Go)
|
|
// - TEXT → VARCHAR(255), except a few free-text
|
|
// columns that stay TEXT/LONGTEXT
|
|
// - reserved column `key` → backticked
|
|
// - CREATE INDEX IF NOT EXISTS → CREATE INDEX (MySQL has no IF NOT EXISTS)
|
|
//
|
|
// REAL is left as-is (MySQL accepts it as an alias for DOUBLE). CREATE/DROP
|
|
// TABLE IF [NOT] EXISTS, RENAME TO, INSERT…SELECT, CHECK(...) and foreign keys
|
|
// are all valid MySQL as written.
|
|
|
|
var (
|
|
reStrftimeDefault = regexp.MustCompile(`\(strftime\('%Y-%m-%dT%H:%M:%fZ',\s*'now'\)\)`)
|
|
reBareInteger = regexp.MustCompile(`\bINTEGER\b`)
|
|
reColText = regexp.MustCompile(`(\w+)\s+TEXT\b`)
|
|
reKeyColumn = regexp.MustCompile(`(?m)^(\s*)key\b`)
|
|
// SQLite quotes identifiers with double quotes — e.g. a table renamed by
|
|
// ALTER … RENAME TO ends up as CREATE TABLE "name" in sqlite_master. MySQL
|
|
// uses backticks. Our schema has no double-quoted string literals, so it's
|
|
// safe to convert every "ident" to `ident`.
|
|
reDoubleQuoteIdent = regexp.MustCompile(`"([^"\n]*)"`)
|
|
)
|
|
|
|
// MySQL's InnoDB row-size limit is 65535 bytes (excluding off-page TEXT/BLOB).
|
|
// The qso table has ~150 columns, so making them all VARCHAR(255) (1020 bytes
|
|
// each in utf8mb4) overflows the row. We therefore emit TEXT (stored off-page,
|
|
// ~20 bytes in-row) for the bulk of columns, and VARCHAR only where a column
|
|
// actually needs in-row storage: it's indexed, a primary key, or carries a
|
|
// DEFAULT (TEXT can't hold a literal default in MySQL).
|
|
|
|
// varcharColumns must be VARCHAR because they're indexed or part of a primary
|
|
// key declared on a separate line (so the DEFAULT/PRIMARY-KEY line heuristic
|
|
// below can't catch them). MySQL can't index a TEXT column without a prefix
|
|
// length. Keyed by column name (a name may repeat across tables — harmless).
|
|
var varcharColumns = map[string]bool{
|
|
// qso indexes (0001, 0003, 0019)
|
|
"callsign": true, "qso_date": true, "band": true, "mode": true,
|
|
"grid": true, "station_callsign": true, "state": true, "contest_id": true,
|
|
"sat_name": true, "prop_mode": true, "sig": true, "wwff_ref": true, "skcc": true,
|
|
// integrations_udp index (0011)
|
|
"direction": true,
|
|
// award_references composite primary key (0017)
|
|
"award_code": true, "ref_code": true,
|
|
}
|
|
|
|
// longTextColumns override the default TEXT with a bigger type where the
|
|
// content can be large (a full QSL-template document).
|
|
var longTextColumns = map[string]string{
|
|
"json": "LONGTEXT",
|
|
}
|
|
|
|
// mysqlDDL rewrites a SQLite DDL/DML statement (or multi-statement migration
|
|
// file) into the MySQL dialect. See the package note above for the rules.
|
|
func mysqlDDL(stmt string) string {
|
|
s := stmt
|
|
// SQLite double-quoted identifiers → MySQL backticks (renamed tables in the
|
|
// baseline dump, etc.).
|
|
s = reDoubleQuoteIdent.ReplaceAllString(s, "`$1`")
|
|
// MySQL has no IF NOT EXISTS for CREATE INDEX (migrations run once anyway).
|
|
s = strings.ReplaceAll(s, "CREATE INDEX IF NOT EXISTS", "CREATE INDEX")
|
|
// Auto-increment primary keys. Both the AUTOINCREMENT form and the bare
|
|
// "INTEGER PRIMARY KEY" (SQLite rowid alias) must become AUTO_INCREMENT so
|
|
// inserts that omit the id still get one.
|
|
s = strings.ReplaceAll(s, "INTEGER PRIMARY KEY AUTOINCREMENT", "BIGINT AUTO_INCREMENT PRIMARY KEY")
|
|
s = strings.ReplaceAll(s, "INTEGER PRIMARY KEY", "BIGINT AUTO_INCREMENT PRIMARY KEY")
|
|
// Timestamp defaults: Go supplies ISO strings, so drop the SQLite function
|
|
// default (TEXT/VARCHAR can't carry it in MySQL anyway).
|
|
s = reStrftimeDefault.ReplaceAllString(s, "''")
|
|
// Remaining INTEGER columns → BIGINT so foreign-key parent/child types match.
|
|
s = reBareInteger.ReplaceAllString(s, "BIGINT")
|
|
// TEXT columns → TEXT (off-page) by default, VARCHAR(255) only when indexed,
|
|
// a primary key, or default-bearing — keeping the row under MySQL's limit.
|
|
s = translateTextColumns(s)
|
|
// `key` is a MySQL reserved word — backtick the settings column declaration.
|
|
s = reKeyColumn.ReplaceAllString(s, "$1`key`")
|
|
return s
|
|
}
|
|
|
|
// translateTextColumns rewrites each "<col> TEXT" column declaration to the
|
|
// right MySQL type, deciding per line so it can see whether the line carries a
|
|
// DEFAULT or an inline PRIMARY KEY. See varcharColumns / longTextColumns.
|
|
func translateTextColumns(s string) string {
|
|
lines := strings.Split(s, "\n")
|
|
for i, line := range lines {
|
|
m := reColText.FindStringSubmatchIndex(line)
|
|
if m == nil {
|
|
continue
|
|
}
|
|
col := line[m[2]:m[3]]
|
|
var typ string
|
|
switch {
|
|
case longTextColumns[col] != "":
|
|
typ = longTextColumns[col]
|
|
case varcharColumns[col],
|
|
strings.Contains(line, "DEFAULT"),
|
|
strings.Contains(line, "PRIMARY KEY"):
|
|
typ = "VARCHAR(255)"
|
|
default:
|
|
typ = "TEXT"
|
|
}
|
|
lines[i] = line[:m[0]] + col + " " + typ + line[m[1]:]
|
|
}
|
|
return strings.Join(lines, "\n")
|
|
}
|
|
|
|
// OpenMySQL opens the shared MySQL logbook, creating the database if needed,
|
|
// then applies the (translated) embedded migrations. multiStatements is enabled
|
|
// so multi-statement migration files run in a single Exec.
|
|
func OpenMySQL(c MySQLConfig) (*sql.DB, error) {
|
|
if strings.TrimSpace(c.Host) == "" {
|
|
return nil, fmt.Errorf("host is required")
|
|
}
|
|
name := strings.TrimSpace(c.Database)
|
|
if !validDBIdent(name) {
|
|
return nil, fmt.Errorf("invalid database name %q (letters, digits, underscore only)", name)
|
|
}
|
|
// Ensure the database exists (connect server-level first).
|
|
if err := PingMySQL(c); err != nil {
|
|
return nil, err
|
|
}
|
|
conn, err := sql.Open("mysql", c.dsn())
|
|
if err != nil {
|
|
return nil, fmt.Errorf("open mysql: %w", err)
|
|
}
|
|
// The UI fires bursts of concurrent queries (the Preferences dialog alone
|
|
// loads ~8 settings in parallel, plus the grid and live cluster status). A
|
|
// low cap turns such a burst into a deadlock-like stall against a remote
|
|
// server, so keep a generous pool with idle reaping. SQLite ran uncapped.
|
|
conn.SetMaxOpenConns(50)
|
|
conn.SetMaxIdleConns(10)
|
|
conn.SetConnMaxIdleTime(90 * time.Second)
|
|
// No max lifetime: a slow server's first migration can run for minutes on a
|
|
// single connection, and reaping it mid-migration drops the selected database
|
|
// (surfacing as "Unknown database"). Idle connections are still recycled
|
|
// after 90s, and the driver retries stale pooled connections.
|
|
conn.SetConnMaxLifetime(0)
|
|
if err := conn.Ping(); err != nil {
|
|
_ = conn.Close()
|
|
return nil, fmt.Errorf("connect to %s: %w", name, err)
|
|
}
|
|
// Set the dialect before migrating so the runner takes the MySQL path
|
|
// (per-statement, idempotent) rather than the SQLite transaction path.
|
|
Dialect = "mysql"
|
|
fresh, err := isFreshMySQL(conn)
|
|
if err != nil {
|
|
_ = conn.Close()
|
|
Dialect = "sqlite"
|
|
return nil, fmt.Errorf("inspect database: %w", err)
|
|
}
|
|
if fresh {
|
|
// Empty database: build the whole final schema in one pass (one CREATE
|
|
// per table, no ALTERs) — far faster than replaying 21 migrations,
|
|
// especially on a server with slow DDL.
|
|
err = applyMySQLBaseline(conn)
|
|
} else {
|
|
// Existing database: apply only the migrations it's missing.
|
|
err = migrate(conn, mysqlDDL)
|
|
}
|
|
if err != nil {
|
|
_ = conn.Close()
|
|
Dialect = "sqlite" // migration failed; we'll fall back to SQLite
|
|
return nil, err
|
|
}
|
|
return conn, nil
|
|
}
|
|
|
|
// isFreshMySQL reports whether the database has no OpsLog schema yet (no qso
|
|
// table), so the baseline fast-path applies. A partially-migrated database is
|
|
// NOT fresh and goes through the incremental migrator.
|
|
func isFreshMySQL(conn *sql.DB) (bool, error) {
|
|
var name string
|
|
err := conn.QueryRow("SHOW TABLES LIKE 'qso'").Scan(&name)
|
|
if err == sql.ErrNoRows {
|
|
return true, nil
|
|
}
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return false, nil
|
|
}
|
|
|
|
// applyMySQLBaseline creates the entire current schema on a fresh MySQL database
|
|
// in a single pass, then records every migration as already applied. The schema
|
|
// is derived from the migrations themselves (replayed on a throwaway in-memory
|
|
// SQLite, whose sqlite_master holds each table's FINAL CREATE statement with all
|
|
// ALTER-added columns folded in) and translated to MySQL — so there's no second
|
|
// schema to maintain and the two backends can't drift. Future migrations apply
|
|
// incrementally on top.
|
|
func applyMySQLBaseline(conn *sql.DB) error {
|
|
mem, err := sql.Open("sqlite", "file:opslog_baseline?mode=memory&cache=shared")
|
|
if err != nil {
|
|
return fmt.Errorf("open baseline sqlite: %w", err)
|
|
}
|
|
defer mem.Close()
|
|
if err := migrate(mem, nil); err != nil {
|
|
return fmt.Errorf("build baseline schema: %w", err)
|
|
}
|
|
|
|
var migNames []string
|
|
mrows, err := mem.Query("SELECT name FROM schema_migrations ORDER BY name")
|
|
if err != nil {
|
|
return fmt.Errorf("read baseline migrations: %w", err)
|
|
}
|
|
for mrows.Next() {
|
|
var n string
|
|
if err := mrows.Scan(&n); err != nil {
|
|
mrows.Close()
|
|
return err
|
|
}
|
|
migNames = append(migNames, n)
|
|
}
|
|
mrows.Close()
|
|
|
|
var tables, indexes []string
|
|
srows, err := mem.Query("SELECT type, sql FROM sqlite_master WHERE sql IS NOT NULL AND name NOT LIKE 'sqlite_%' AND name != 'schema_migrations'")
|
|
if err != nil {
|
|
return fmt.Errorf("read baseline schema: %w", err)
|
|
}
|
|
for srows.Next() {
|
|
var typ, s string
|
|
if err := srows.Scan(&typ, &s); err != nil {
|
|
srows.Close()
|
|
return err
|
|
}
|
|
switch typ {
|
|
case "table":
|
|
tables = append(tables, s)
|
|
case "index":
|
|
indexes = append(indexes, s)
|
|
}
|
|
}
|
|
srows.Close()
|
|
|
|
// Apply on one connection with FK checks off so table order doesn't matter.
|
|
ctx := context.Background()
|
|
c, err := conn.Conn(ctx)
|
|
if err != nil {
|
|
return fmt.Errorf("acquire conn: %w", err)
|
|
}
|
|
defer func() {
|
|
_, _ = c.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=1")
|
|
_ = c.Close()
|
|
}()
|
|
if _, err := c.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=0"); err != nil {
|
|
return fmt.Errorf("disable fk checks: %w", err)
|
|
}
|
|
if _, err := c.ExecContext(ctx, mysqlDDL(schemaMigrationsDDL)); err != nil {
|
|
return fmt.Errorf("create schema_migrations: %w", err)
|
|
}
|
|
for _, tb := range tables {
|
|
if _, err := c.ExecContext(ctx, mysqlDDL(tb)); err != nil {
|
|
if isIgnorableDDLError(err) {
|
|
continue
|
|
}
|
|
return fmt.Errorf("baseline table: %w", err)
|
|
}
|
|
}
|
|
for _, ix := range indexes {
|
|
if _, err := c.ExecContext(ctx, mysqlDDL(ix)); err != nil {
|
|
if isIgnorableDDLError(err) {
|
|
continue
|
|
}
|
|
return fmt.Errorf("baseline index: %w", err)
|
|
}
|
|
}
|
|
for _, n := range migNames {
|
|
if _, err := conn.Exec("INSERT INTO schema_migrations(name) VALUES(?)", n); err != nil {
|
|
if isIgnorableDDLError(err) {
|
|
continue
|
|
}
|
|
return fmt.Errorf("record migration %s: %w", n, err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// applyMySQLMigration executes a translated migration one statement at a time.
|
|
// MySQL has no multi-statement Exec without a special flag and auto-commits DDL,
|
|
// so running statements individually (and tolerating "already exists" errors)
|
|
// keeps migrations idempotent and lets a half-applied database self-heal.
|
|
//
|
|
// It runs on a single dedicated connection with FOREIGN_KEY_CHECKS=0, because a
|
|
// few migrations recreate tables by DROP/RENAME in an order that trips MySQL's
|
|
// foreign-key enforcement (SQLite is laxer). The flag is reset to 1 before the
|
|
// connection returns to the pool, so runtime queries keep FK enforcement.
|
|
func applyMySQLMigration(conn *sql.DB, sqlText string) error {
|
|
ctx := context.Background()
|
|
c, err := conn.Conn(ctx)
|
|
if err != nil {
|
|
return fmt.Errorf("acquire conn: %w", err)
|
|
}
|
|
defer func() {
|
|
_, _ = c.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=1")
|
|
_ = c.Close()
|
|
}()
|
|
if _, err := c.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=0"); err != nil {
|
|
return fmt.Errorf("disable fk checks: %w", err)
|
|
}
|
|
for _, stmt := range coalesceAddColumns(splitStatements(sqlText)) {
|
|
if _, err := c.ExecContext(ctx, stmt); err != nil {
|
|
if isIgnorableDDLError(err) {
|
|
// A coalesced multi-column ALTER fails wholesale if even one
|
|
// column already exists (partial prior apply). Re-run it column
|
|
// by column so the missing ones still get added.
|
|
if cols := decomposeAddColumns(stmt); len(cols) > 1 {
|
|
for _, one := range cols {
|
|
if _, e := c.ExecContext(ctx, one); e != nil && !isIgnorableDDLError(e) {
|
|
return fmt.Errorf("statement %q: %w", firstLine(one), e)
|
|
}
|
|
}
|
|
}
|
|
continue
|
|
}
|
|
return fmt.Errorf("statement %q: %w", firstLine(stmt), err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
var reAddColumn = regexp.MustCompile(`(?is)^\s*ALTER\s+TABLE\s+(\S+)\s+ADD\s+COLUMN\s+(.+)$`)
|
|
|
|
// coalesceAddColumns merges consecutive "ALTER TABLE t ADD COLUMN …" statements
|
|
// on the same table into a single multi-column ALTER. SQLite needs one ALTER per
|
|
// column, but each is a full table operation in MySQL — and on a slow server
|
|
// that's seconds apiece, turning a ~50-column migration into minutes (long
|
|
// enough to trip connection lifetimes and stall startup). One combined ALTER is
|
|
// a single table rebuild instead of fifty.
|
|
func coalesceAddColumns(stmts []string) []string {
|
|
out := make([]string, 0, len(stmts))
|
|
for i := 0; i < len(stmts); {
|
|
m := reAddColumn.FindStringSubmatch(stmts[i])
|
|
if m == nil {
|
|
out = append(out, stmts[i])
|
|
i++
|
|
continue
|
|
}
|
|
table := m[1]
|
|
cols := []string{strings.TrimSpace(m[2])}
|
|
j := i + 1
|
|
for j < len(stmts) {
|
|
m2 := reAddColumn.FindStringSubmatch(stmts[j])
|
|
if m2 == nil || m2[1] != table {
|
|
break
|
|
}
|
|
cols = append(cols, strings.TrimSpace(m2[2]))
|
|
j++
|
|
}
|
|
out = append(out, "ALTER TABLE "+table+" ADD COLUMN "+strings.Join(cols, ", ADD COLUMN "))
|
|
i = j
|
|
}
|
|
return out
|
|
}
|
|
|
|
// decomposeAddColumns splits a (possibly coalesced) ADD COLUMN ALTER back into
|
|
// one ALTER per column, for the idempotent fallback path.
|
|
func decomposeAddColumns(stmt string) []string {
|
|
m := reAddColumn.FindStringSubmatch(stmt)
|
|
if m == nil {
|
|
return nil
|
|
}
|
|
table := m[1]
|
|
var out []string
|
|
for _, col := range strings.Split(m[2], ", ADD COLUMN ") {
|
|
out = append(out, "ALTER TABLE "+table+" ADD COLUMN "+strings.TrimSpace(col))
|
|
}
|
|
return out
|
|
}
|
|
|
|
// splitStatements breaks a migration file into individual SQL statements,
|
|
// dropping comments (full-line and inline "-- …") and blank fragments. Our
|
|
// migrations never embed a ';' or '--' inside a string literal, so this is safe.
|
|
func splitStatements(sqlText string) []string {
|
|
var b strings.Builder
|
|
for _, line := range strings.Split(sqlText, "\n") {
|
|
if i := strings.Index(line, "--"); i >= 0 {
|
|
line = line[:i] // strip inline / full-line comment
|
|
}
|
|
if strings.TrimSpace(line) == "" {
|
|
continue
|
|
}
|
|
b.WriteString(line)
|
|
b.WriteByte('\n')
|
|
}
|
|
var out []string
|
|
for _, part := range strings.Split(b.String(), ";") {
|
|
if strings.TrimSpace(part) != "" {
|
|
out = append(out, part)
|
|
}
|
|
}
|
|
return out
|
|
}
|
|
|
|
func firstLine(s string) string {
|
|
s = strings.TrimSpace(s)
|
|
if i := strings.IndexByte(s, '\n'); i >= 0 {
|
|
return s[:i]
|
|
}
|
|
return s
|
|
}
|
|
|
|
// isAccessDenied reports whether a MySQL error is a privilege denial — e.g. a
|
|
// restricted user trying to CREATE DATABASE. Such users can still operate on a
|
|
// pre-existing database they've been granted, so the caller treats this as
|
|
// non-fatal and verifies database access separately.
|
|
func isAccessDenied(err error) bool {
|
|
var me *mysqldriver.MySQLError
|
|
if !errors.As(err, &me) {
|
|
return false
|
|
}
|
|
switch me.Number {
|
|
case 1044, // ER_DBACCESS_DENIED_ERROR — access denied to database
|
|
1045, // ER_ACCESS_DENIED_ERROR — access denied for user
|
|
1142: // ER_TABLEACCESS_DENIED_ERROR — command denied to user
|
|
return true
|
|
}
|
|
return false
|
|
}
|
|
|
|
// isIgnorableDDLError reports whether a MySQL error means the object the
|
|
// statement creates/drops is already in the intended state — safe to skip when
|
|
// re-applying a forward-only migration.
|
|
func isIgnorableDDLError(err error) bool {
|
|
var me *mysqldriver.MySQLError
|
|
if !errors.As(err, &me) {
|
|
return false
|
|
}
|
|
switch me.Number {
|
|
case 1050, // ER_TABLE_EXISTS_ERROR — CREATE TABLE on an existing table
|
|
1051, // ER_BAD_TABLE_ERROR — DROP TABLE on a missing table
|
|
1060, // ER_DUP_FIELDNAME — ADD COLUMN already present
|
|
1061, // ER_DUP_KEYNAME — CREATE INDEX already present
|
|
1091: // ER_CANT_DROP_FIELD_OR_KEY — DROP of something that doesn't exist
|
|
return true
|
|
}
|
|
return false
|
|
}
|