76 lines
2.8 KiB
Go
76 lines
2.8 KiB
Go
// Package databasePlatform
|
|
// Path: /internal/platform/database
|
|
// File: schema.go
|
|
//
|
|
// Purpose
|
|
// Bootstrap and verify the initial application schema for MySQL using
|
|
// embedded SQL. Applies the full schema only when the target database
|
|
// is detected as "empty" via a probe query.
|
|
//
|
|
// Responsibilities (as implemented here)
|
|
// 1) Detect whether the schema has been initialized by probing the users table.
|
|
// 2) If empty, apply the embedded initial schema inside a single transaction.
|
|
// 3) Use helper ExecScript to execute multi-statement SQL safely.
|
|
// 4) Fail fast with contextual errors on probe/apply failures.
|
|
//
|
|
// Idempotency strategy
|
|
// - Uses migrationSQL.ProbeUsersTable to query a known table and count rows.
|
|
// - If count > 0, assumes schema exists and exits without applying SQL.
|
|
// - This makes startup safe to repeat across restarts.
|
|
//
|
|
// Design notes
|
|
// - InitialSchema is embedded (no external SQL files at runtime).
|
|
// - Application is all-or-nothing via a single transaction.
|
|
// - Console prints currently provide debug visibility during boot.
|
|
// - Probe focuses on the "users" table as the presence indicator.
|
|
//
|
|
// TODOs (observations from current implementation)
|
|
// - Replace debug prints with structured logging (levelled).
|
|
// - Consider probing for table existence rather than row count to avoid
|
|
// the edge case where users table exists but has zero rows.
|
|
// - Introduce a schema version table for forward migrations.
|
|
// - Expand error context to include which statement failed in ExecScript.
|
|
//
|
|
// Change log
|
|
// [2025-10-29] Documentation aligned with embedded migrations and probe logic.
|
|
|
|
package databasePlatform
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
|
|
databaseHelpers "synlotto-website/internal/helpers/database"
|
|
migrationSQL "synlotto-website/internal/storage/migrations"
|
|
)
|
|
|
|
// EnsureInitialSchema ensures the database contains the baseline schema.
|
|
// If the probe indicates an existing install, the function is a no-op.
|
|
func EnsureInitialSchema(db *sql.DB) error {
|
|
fmt.Println("✅ EnsureInitialSchema called") // temp debug
|
|
|
|
// Probe: if users table exists & has rows, treat schema as present.
|
|
var cnt int
|
|
if err := db.QueryRow(migrationSQL.ProbeUsersTable).Scan(&cnt); err != nil {
|
|
return fmt.Errorf("probe users table failed: %w", err)
|
|
}
|
|
fmt.Printf("👀 Probe users count = %d\n", cnt) // temp debug
|
|
if cnt > 0 {
|
|
return nil
|
|
}
|
|
|
|
// Sanity: visibility for embedded SQL payload size.
|
|
fmt.Printf("📦 Initial SQL bytes: %d\n", len(migrationSQL.InitialSchema)) // temp debug
|
|
|
|
// Apply full schema atomically.
|
|
tx, err := db.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if err := databaseHelpers.ExecScript(tx, migrationSQL.InitialSchema); err != nil {
|
|
_ = tx.Rollback()
|
|
return fmt.Errorf("apply schema: %w", err)
|
|
}
|
|
return tx.Commit()
|
|
}
|