package mariadb import ( "errors" "fmt" "strings" "gorm.io/gorm" ) type snapshotTrigger struct { Name string `gorm:"column:TRIGGER_NAME"` Table string `gorm:"column:EVENT_OBJECT_TABLE"` Event string `gorm:"column:EVENT_MANIPULATION"` Timing string `gorm:"column:ACTION_TIMING"` Order int `gorm:"column:ACTION_ORDER"` Body string `gorm:"column:ACTION_STATEMENT"` SQLMode string `gorm:"column:SQL_MODE"` Definer string `gorm:"column:DEFINER"` Charset string `gorm:"column:CHARACTER_SET_CLIENT"` Collation string `gorm:"column:COLLATION_CONNECTION"` DatabaseCollation string `gorm:"column:DATABASE_COLLATION"` } func readSnapshotTriggers(db *gorm.DB, m *snapshotManifest) error { query := `SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE, EVENT_MANIPULATION, ACTION_TIMING, ACTION_ORDER, ACTION_STATEMENT, SQL_MODE, DEFINER, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA=? ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION, ACTION_ORDER` if err := freshDB(db).Raw(query, m.Schema).Scan(&m.Triggers).Error; err != nil { return fmt.Errorf("read snapshot triggers: %w", err) } for _, trigger := range m.Triggers { if err := validateSnapshotTriggerReferences(trigger, m.Schema); err != nil { return err } if trigger.DatabaseCollation != m.Collation { return fmt.Errorf("trigger %s has a legacy database collation; restore support is required before migration", trigger.Name) } } return nil } func restoreSnapshotTriggers(db *gorm.DB, m *snapshotManifest) (err error) { if len(m.Triggers) == 0 { return nil } var mode, charset, collation, schema string if err := freshDB(db).Raw("SELECT @@sql_mode, @@character_set_client, @@collation_connection, DATABASE()").Row().Scan(&mode, &charset, &collation, &schema); err != nil { return fmt.Errorf("read trigger restore session: %w", err) } defer func() { for _, reset := range []struct{ sql, value string }{{"SET sql_mode=?", mode}, {"SET character_set_client=?", charset}, {"SET collation_connection=?", collation}} { if e := freshDB(db).Exec(reset.sql, reset.value).Error; e != nil { err = errors.Join(err, fmt.Errorf("reset trigger restore session: %w", e)) } } }() for _, trigger := range m.Triggers { if trigger.Timing != "BEFORE" && trigger.Timing != "AFTER" { return fmt.Errorf("invalid trigger timing") } if trigger.Event != "INSERT" && trigger.Event != "UPDATE" && trigger.Event != "DELETE" { return fmt.Errorf("invalid trigger event") } at := strings.LastIndex(trigger.Definer, "@") if at < 1 || at == len(trigger.Definer)-1 { return fmt.Errorf("invalid trigger definer") } for _, setting := range []struct{ sql, value string }{{"SET sql_mode=?", trigger.SQLMode}, {"SET character_set_client=?", trigger.Charset}, {"SET collation_connection=?", trigger.Collation}} { if err := freshDB(db).Exec(setting.sql, setting.value).Error; err != nil { return fmt.Errorf("set trigger restore session: %w", err) } } // Explicitly bind name and target table to the destination. Trigger bodies // retain their original SQL; verification performs no DML after creation. statement := "CREATE DEFINER=" + quoteIdentifier(trigger.Definer[:at]) + "@" + quoteIdentifier(trigger.Definer[at+1:]) + " TRIGGER " + quoteIdentifier(schema) + "." + quoteIdentifier(trigger.Name) + " " + trigger.Timing + " " + trigger.Event + " ON " + quoteIdentifier(schema) + "." + quoteIdentifier(trigger.Table) + " FOR EACH ROW " + trigger.Body if _, err := db.Statement.ConnPool.ExecContext(db.Statement.Context, statement); err != nil { return fmt.Errorf("restore trigger %s: %w", trigger.Name, err) } } return nil }