使用mssqlserver复制备份,某天突然发现复制显示一切正常,但是记录数又不一样,明显是停止了,虽然还没有搞懂怎么回事,所以想了这样一个工具来检查复制。程序用go写的,顺便练习一下golang 代码。
package main
import (
"fmt"
"log"
// "os"
// "strings"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mssql"
_ "github.com/jinzhu/gorm/dialects/sqlite"
"github.com/lxn/walk"
. "github.com/lxn/walk/declarative"
"github.com/lxn/win"
)
var (
err error
sqliteDB *gorm.DB
mssqlDB_src *gorm.DB
mssqlDB_dsc *gorm.DB
tvChan chan TbRow
)
type DsnEnrty struct {
Id uint `gorm:"primary_key"`
Name string `gorm:"size:64"`
Src string `gorm:"size:512"`
Dest string `gorm:"size:512"`
}
type MyMainWindow struct {
*walk.MainWindow
model *EnvModel
lb *walk.ListBox
te *walk.TextEdit
addButton *walk.Action
deleteButton *walk.Action
Name_edit *walk.LineEdit
Src_edit *walk.LineEdit
Dest_edit *walk.LineEdit
Tv *walk.TableView
ModelTV *TvModel
}
type EnvModel struct {
walk.ListModelBase
items []DsnEnrty
}
func NewEnvModel() *EnvModel {
//env := os.Environ()
//var ListDsn []DsnEnrty
//ListDsn, err = sqliteDB.Model(&DsnEnrty{}).Find(&ListDsn)
m := &EnvModel{items: make([]DsnEnrty, 100)}
sqliteDB.Find(&m.items)
return m
}
func (m *EnvModel) ItemCount() int {
return len(m.items)
}
func (m *EnvModel) Value(index int) interface{} {
return m.items[index].Name
}
type TbRow struct {
Index string
Tname string
SrcNum int
DestNum int
Result string
}
type TvModel struct {
walk.TableModelBase
items []TbRow
}
func NewTvModel() *TvModel {
//m := new(TvModel)
//m.items = make([]TbRow, 100)
m := &TvModel{items: []TbRow{}}
//m.ResetRows()
return m
}
func (m *TvModel) ResetRows() {
//m.items = make([]TbRow, 1)
item := TbRow{}
item.Tname = "fy"
item.SrcNum = 100
item.DestNum = 200
m.items = append(m.items, item)
//m.items[0] = item
m.PublishRowsReset()
}
func (m *TvModel) RowCount() int {
return len(m.items)
}
func (m *TvModel) Value(row, col int) interface{} {
item := m.items[row]
switch col {
case 0:
return item.Index
case 1:
return item.Tname
case 2:
return item.SrcNum
case 3:
return item.DestNum
case 4:
return item.Result
}
panic("unexpected col")
}
func (mw *MyMainWindow) lb_CurrentIndexChanged() {
idx := mw.lb.CurrentIndex()
if idx < 0 || idx > len(mw.model.items) {
return
}
dsn := mw.model.items[idx]
mw.Name_edit.SetText(dsn.Name)
mw.Src_edit.SetText(dsn.Src)
mw.Dest_edit.SetText(dsn.Dest)
}
func (mw *MyMainWindow) lb_ItemActivated() {
value := mw.model.items[mw.lb.CurrentIndex()].Dest
walk.MsgBox(mw, "Value", value, walk.MsgBoxIconInformation)
}
func (mw *MyMainWindow) tv_showtableview() {
for {
select {
case row := <-tvChan:
//fmt.Printf("%#v\n", row)
mw.ModelTV.items = append(mw.ModelTV.items, row)
mw.ModelTV.PublishRowsReset()
}
}
}
type tablelist struct {
Name string
}
func (mw *MyMainWindow) run_get_sql_tables_info() {
go mw.get_sql_tables_info()
}
func (mw *MyMainWindow) get_sql_tables_info() {
idx := mw.lb.CurrentIndex()
if idx < 0 {
walk.MsgBox(mw, "出错了", "请选择左边条目再操作", walk.MsgBoxIconError)
return
}
conninfo := mw.model.items[idx]
mssqlDB_src, err = gorm.Open("mssql", conninfo.Src)
_, err = get_mssql_version(mssqlDB_src)
if err != nil {
walk.MsgBox(mw, "连接源服务器出错", err.Error(), walk.MsgBoxIconError)
return
}
mssqlDB_dsc, err := gorm.Open("mssql", conninfo.Dest)
_, err = get_mssql_version(mssqlDB_src)
if err != nil {
walk.MsgBox(mw, "连接源服务器出错", err.Error(), walk.MsgBoxIconError)
return
}
rows, err := mssqlDB_src.Raw("SELECT Name FROM SysObjects Where XType='U' ORDER BY Name").Rows()
defer rows.Close()
//var tmp string
//var tblist []string
//var tab_items []TbRow
for rows.Next() {
var tmp string
rows.Scan(&tmp)
tb := TbRow{}
tb.Index = conninfo.Name
tb.Tname = tmp
mssqlDB_src.Table(tmp).Count(&tb.SrcNum)
mssqlDB_dsc.Table(tmp).Count(&tb.DestNum)
//tab_items = append(tab_items, tb)
if tb.SrcNum == tb.DestNum {
tb.Result = "-"
} else {
tb.Result = "X"
}
tvChan <- tb
}
defer mssqlDB_src.Close()
defer mssqlDB_dsc.Close()
}
func get_mssql_version(db *gorm.DB) (versions string, err error) {
sqlRow := db.Raw("select @@VERSION").Row()
err = sqlRow.Scan(&versions)
if err != nil {
return "", err
}
return versions, nil
}
func main() {
sqliteDB, err = gorm.Open("sqlite3", "./sqlserver_sync_checker.db")
if err != nil {
log.Fatal("sqlite3 open error:", err)
}
if !sqliteDB.HasTable(&DsnEnrty{}) {
sqliteDB.AutoMigrate(&DsnEnrty{})
}
mw := &MyMainWindow{model: NewEnvModel(), ModelTV: NewTvModel()}
tvChan = make(chan TbRow, 10)
go mw.tv_showtableview()
if _, err := (MainWindow{
AssignTo: &mw.MainWindow,
Title: "mssql server 表记录总数对比",
MinSize: Size{600, 520},
Size: Size{600, 600},
Layout: VBox{MarginsZero: true},
//ToolBar: mw.newToolBar(),
Children: []Widget{
HSplitter{
Children: []Widget{
Composite{
Layout: Grid{Columns: 2},
Children: []Widget{
ListBox{
AssignTo: &mw.lb,
Model: mw.model,
OnCurrentIndexChanged: mw.lb_CurrentIndexChanged,
OnItemActivated: mw.lb_ItemActivated,
},
Label{
ColumnSpan: 2,
Text: "名称:",
},
LineEdit{
AssignTo: &mw.Name_edit,
ColumnSpan: 2,
Text: "新名字",
},
Label{
ColumnSpan: 2,
Text: "源DSN:",
},
LineEdit{
AssignTo: &mw.Src_edit,
ColumnSpan: 2,
Text: "sqlserver://username:password@localhost:1433?database=dbname;encrypt=disable",
},
Label{
ColumnSpan: 2,
Text: "目标DSN:",
},
LineEdit{
AssignTo: &mw.Dest_edit,
ColumnSpan: 2,
Text: "sqlserver://username:password@localhost:1433?database=dbname;encrypt=disable",
},
PushButton{
// AssignTo: &acceptPB,
Text: "更新",
ColumnSpan: 2,
OnClicked: func() {
idx := mw.lb.CurrentIndex()
if idx < 0 {
walk.MsgBox(mw, "出错了", "请选择数据再操作", walk.MsgBoxIconError)
return
}
dsn := mw.model.items[idx]
dsn.Name = mw.Name_edit.Text()
dsn.Src = mw.Src_edit.Text()
dsn.Dest = mw.Dest_edit.Text()
sqliteDB.Model(&DsnEnrty{}).Where("id=?", dsn.Id).Update(&dsn)
//mw.lb.SetModel(NewEnvModel())
//mw.model = NewEnvModel()
mw.model = NewEnvModel()
mw.lb.SetModel(mw.model)
walk.MsgBox(mw, "提示", "保存成功", walk.MsgBoxIconInformation)
},
},
PushButton{
// AssignTo: &cancelPB,
Text: "新建",
ColumnSpan: 2,
OnClicked: func() {
dsn := DsnEnrty{}
dsn.Name = mw.Name_edit.Text()
dsn.Src = mw.Src_edit.Text()
dsn.Dest = mw.Dest_edit.Text()
sqliteDB.Model(&DsnEnrty{}).Create(&dsn)
mw.model = NewEnvModel()
mw.lb.SetModel(mw.model)
walk.MsgBox(mw, "提示", "保存成功", walk.MsgBoxIconInformation)
},
},
PushButton{
// AssignTo: &cancelPB,
Text: "删除",
ColumnSpan: 2,
OnClicked: func() {
idx := mw.lb.CurrentIndex()
if idx < 0 {
walk.MsgBox(mw, "出错了", "请选择数据再操作", walk.MsgBoxIconError)
return
}
dsn := mw.model.items[idx]
message := fmt.Sprintf("确定要删除记录 '%s'?", dsn.Name)
ret := walk.MsgBox(mw, "删除记录", message, walk.MsgBoxYesNo)
if ret == win.IDYES {
sqliteDB.Where("id=?", dsn.Id).Delete(DsnEnrty{})
mw.model = NewEnvModel()
mw.lb.SetModel(mw.model)
walk.MsgBox(mw, "提示", "删除成功", walk.MsgBoxIconInformation)
}
},
},
PushButton{
// AssignTo: &cancelPB,
Text: "源-测试连接",
ColumnSpan: 2,
OnClicked: func() {
dsn := mw.Src_edit.Text()
if len(dsn) == 0 {
walk.MsgBox(mw, "出错了", "DSN不能为空", walk.MsgBoxIconError)
return
}
db, err := gorm.Open("mssql", dsn)
defer db.Close()
sqlver, err := get_mssql_version(db)
if err != nil {
walk.MsgBox(mw, "出错了", err.Error(), walk.MsgBoxIconError)
} else {
walk.MsgBox(mw, "提示", sqlver, walk.MsgBoxIconInformation)
}
},
},
PushButton{
// AssignTo: &cancelPB,
Text: "目标-测试连接",
ColumnSpan: 2,
OnClicked: func() {
dsn := mw.Src_edit.Text()
if len(dsn) == 0 {
walk.MsgBox(mw, "出错了", "DSN不能为空", walk.MsgBoxIconError)
return
}
dbdest, err := gorm.Open("mssql", dsn)
defer dbdest.Close()
sqlver, err := get_mssql_version(dbdest)
if err != nil {
walk.MsgBox(mw, "出错了", err.Error(), walk.MsgBoxIconError)
} else {
walk.MsgBox(mw, "提示", sqlver, walk.MsgBoxIconInformation)
}
},
},
},
},
Composite{
Layout: Grid{Columns: 2},
Children: []Widget{
PushButton{
// AssignTo: &cancelPB,
Text: "获取对比信息",
ColumnSpan: 1,
OnClicked: mw.run_get_sql_tables_info,
},
PushButton{
// AssignTo: &cancelPB,
Text: "清空结果",
ColumnSpan: 1,
OnClicked: func() {
mw.ModelTV.items = []TbRow{}
mw.ModelTV.PublishRowsReset()
},
},
TableView{
ColumnSpan: 2,
AssignTo: &mw.Tv,
AlternatingRowBGColor: walk.RGB(239, 239, 239),
CheckBoxes: false,
ColumnsOrderable: false,
MultiSelection: true,
Columns: []TableViewColumn{
{Title: "配置", Width: 150},
{Title: "表", Width: 250},
{Title: "源记录数", Width: 100},
{Title: "目标记录数", Width: 100},
{Title: "结果", Width: 50},
},
StyleCell: func(style *walk.CellStyle) {
},
Model: mw.ModelTV,
OnSelectedIndexesChanged: func() {
//fmt.Printf("SelectedIndexes: %v\n", tv.SelectedIndexes())
},
},
},
},
},
},
},
}.Run()); err != nil {
log.Fatal(err)
}
}
