219 lines
4.5 KiB
Go
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
|
|
}
|