mpfeuer-firebird-migration/main.go
2025-05-01 15:13:44 +02:00

219 lines
4.5 KiB
Go

package main
import (
"database/sql"
"fmt"
"log"
"os"
"strings"
"time"
_ "github.com/denisenkom/go-mssqldb"
"github.com/joho/godotenv"
_ "github.com/nakagami/firebirdsql"
)
func main() {
err := godotenv.Load()
if err != nil {
log.Fatalf("Error loading .env file: %v", err)
}
firebirdDSN := os.Getenv("FIREBIRD_DSN")
mssqlDSN := os.Getenv("MSSQL_DSN")
fbDB, err := sql.Open("firebirdsql", firebirdDSN)
if err != nil {
log.Fatalf("Failed to connect to Firebird: %v", err)
}
defer fbDB.Close()
mssqlDB, err := sql.Open("sqlserver", mssqlDSN)
if err != nil {
log.Fatalf("Failed to connect to MSSQL: %v", err)
}
defer mssqlDB.Close()
tables, err := getSortedTablesByDependency(mssqlDB)
if err != nil {
log.Fatalf("Failed to get dependency-ordered tables: %v", err)
}
for _, table := range tables {
fmt.Printf("Migrating table: %s\n", table)
err := migrateTable(fbDB, mssqlDB, table)
if err != nil {
log.Printf("Failed to migrate table %s: %v", table, err)
}
}
}
func getSortedTablesByDependency(db *sql.DB) ([]string, error) {
// Get all tables
allTablesQuery := `
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'
`
allRows, err := db.Query(allTablesQuery)
if err != nil {
return nil, fmt.Errorf("failed to query all tables: %w", err)
}
defer allRows.Close()
tableSet := map[string]bool{}
for allRows.Next() {
var name string
if err := allRows.Scan(&name); err != nil {
return nil, err
}
tableSet[name] = true
}
// Get dependencies
depQuery := `
SELECT
OBJECT_NAME(fk.parent_object_id) AS child_table,
OBJECT_NAME(fk.referenced_object_id) AS parent_table
FROM sys.foreign_keys AS fk
`
rows, err := db.Query(depQuery)
if err != nil {
return nil, err
}
defer rows.Close()
type pair struct{ child, parent string }
deps := []pair{}
for rows.Next() {
var child, parent string
if err := rows.Scan(&child, &parent); err != nil {
return nil, err
}
deps = append(deps, pair{child, parent})
tableSet[child] = true
tableSet[parent] = true
}
graph := map[string][]string{}
incoming := map[string]int{}
for table := range tableSet {
graph[table] = []string{}
incoming[table] = 0
}
for _, dep := range deps {
graph[dep.parent] = append(graph[dep.parent], dep.child)
incoming[dep.child]++
}
var sorted []string
queue := []string{}
for table, inc := range incoming {
if inc == 0 {
queue = append(queue, table)
}
}
for len(queue) > 0 {
t := queue[0]
queue = queue[1:]
sorted = append(sorted, t)
for _, child := range graph[t] {
incoming[child]--
if incoming[child] == 0 {
queue = append(queue, child)
}
}
}
if len(sorted) < len(tableSet) {
// Append remaining tables not in sorted (no FKs)
existing := map[string]bool{}
for _, t := range sorted {
existing[t] = true
}
for t := range tableSet {
if !existing[t] {
sorted = append(sorted, t)
}
}
}
return sorted, nil
}
func migrateTable(src *sql.DB, dst *sql.DB, table string) error {
rows, err := src.Query(fmt.Sprintf("SELECT * FROM %s", table))
if err != nil {
return err
}
defer rows.Close()
columns, err := rows.Columns()
if err != nil {
return err
}
namedParams := make([]string, len(columns))
for i := range namedParams {
namedParams[i] = fmt.Sprintf("@p%d", i+1)
}
tx, err := dst.Begin()
if err != nil {
return err
}
defer tx.Rollback()
_, err = tx.Exec(fmt.Sprintf("DELETE FROM [%s]", table))
if err != nil {
return fmt.Errorf("failed to clear table: %w", err)
}
insertSQL := fmt.Sprintf("INSERT INTO [%s] (%s) VALUES (%s)",
table,
join(columns, ", "),
strings.Join(namedParams, ", "))
stmt, err := tx.Prepare(insertSQL)
if err != nil {
return err
}
defer stmt.Close()
for rows.Next() {
values := make([]interface{}, len(columns))
pointers := make([]interface{}, len(columns))
for i := range pointers {
pointers[i] = &values[i]
}
if err := rows.Scan(pointers...); err != nil {
return err
}
for i, v := range values {
if t, ok := v.(time.Time); ok {
if t.Before(time.Date(1753, 1, 1, 0, 0, 0, 0, time.UTC)) {
values[i] = time.Date(1753, 1, 1, 0, 0, 0, 0, time.UTC)
}
}
}
_, err = stmt.Exec(values...)
if err != nil {
return fmt.Errorf("insert failed: %w", err)
}
}
return tx.Commit()
}
func join(items []string, sep string) string {
out := ""
for i, item := range items {
if i > 0 {
out += sep
}
out += fmt.Sprintf("[%s]", item)
}
return out
}