golang mssqlserver复制记录总数对比工具

使用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)
	}
}