Skip to content

Instantly share code, notes, and snippets.

@PeterBooker
Created September 7, 2018 14:14
Show Gist options
  • Save PeterBooker/8a3144d29d5712bd5239349055553024 to your computer and use it in GitHub Desktop.
Save PeterBooker/8a3144d29d5712bd5239349055553024 to your computer and use it in GitHub Desktop.
Example Migration Script
package main
import (
"encoding/json"
"encoding/xml"
"log"
"strconv"
"time"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var (
oldDB *sqlx.DB
newDB *sqlx.DB
err error
)
type Client struct {
ID int `db:"hp_c_id"`
Ready string `db:"hp_c_ready"`
Invited string `db:"hp_c_invited"`
Client string `db:"hp_c_client"`
User string `db:"hp_c_user"`
Year string `db:"hp_c_year"`
Content string `db:"hp_c_content"`
Creator string `db:"hp_c_creator"`
Editor string `db:"hp_c_editor"`
}
func main() {
oldDB, err = sqlx.Open("mysql", "user:pass@tcp(hostname)/dbname?parseTime=true")
if err != nil {
log.Fatalf("Error connecting to old database: %s\n", err)
}
newDB, err = sqlx.Open("mysql", "user:pass@tcp(hostname)/dbname?parseTime=true")
if err != nil {
log.Fatalf("Error connecting to new database: %s\n", err)
}
clients := []Client{}
oldDB.Select(&clients, `
SELECT
hp_c_id,
hp_c_ready,
hp_c_invited,
hp_c_client,
hp_c_user,
hp_c_year,
hp_c_content,
hp_c_creator,
hp_c_editor
FROM
hp_client
ORDER BY
hp_c_id ASC
`)
if _, err := oldDB.Exec(`TRUNCATE handlingsplan_client_entry`); err != nil {
log.Fatalf("Truncate command failed: %s\n", err)
}
for i := 0; i < len(clients); i++ {
if clients[i].ID <= 867 {
newDB.Exec(`
INSERT INTO handlingsplan_client_entry (
hp_c_id,
hp_c_ready,
hp_c_invited,
hp_c_client,
hp_c_user,
hp_c_year,
hp_c_content,
hp_c_creator,
hp_c_editor
) VALUES (
:hp_c_id,
:hp_c_ready,
:hp_c_invited,
:hp_c_client,
:hp_c_user,
:hp_c_year,
:hp_c_content,
:hp_c_creator,
:hp_c_editor
)
`, &clients[i])
continue
}
var xmlData interface{}
err = xml.Unmarshal([]byte(clients[i].Content), &xmlData)
if err != nil {
log.Printf("Failed to parse XML: %s\n", err)
}
var form Form
// create new structure
counting := 0
subcounting := 0
for k, v := range xmlData["maincat"] {
// add to json structure
}
bytes, err := json.Marshal(form)
if err != nil {
log.Printf("Error encoding JSON: %s\n", err)
}
newDB.Exec(`
INSERT INTO handlingsplan_client_entry (
hp_c_client,
hp_c_year,
hp_c_content,
hp_c_creator
) VALUES ($1, $2, $3, $4)
`,
clients[i].Client,
strconv.FormatInt(int64(time.Now().Year()), 10),
bytes,
clients[i].Creator,
)
}
}
type Form struct {
Meta Meta `json:"meta"`
Fields []string `json:"fields"`
}
type Meta struct {
Company string `json:"company"`
Department string `json:"department"`
Collaborators []Collaborator `json:"collaborators"`
}
type Collaborator struct {
date time.Time `json:"date"`
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment