db_mysql.go 5.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208
  1. package db
  2. import (
  3. MYSQL "database/sql"
  4. "encoding/json"
  5. "fmt"
  6. "io/ioutil"
  7. "os"
  8. "os/exec"
  9. "path"
  10. "path/filepath"
  11. "rtzh_elec_temperature/conf"
  12. "rtzh_elec_temperature/global"
  13. "rtzh_elec_temperature/logger"
  14. "rtzh_elec_temperature/tools"
  15. "strings"
  16. "github.com/astaxie/beego/orm"
  17. _ "github.com/go-sql-driver/mysql"
  18. )
  19. type DbConfig struct {
  20. Host string `json:"host"`
  21. Port string `json:"port"`
  22. User string `json:"user"`
  23. Pwd string `json:"pwd"`
  24. Db string `json:"dbname"`
  25. Charset string `json:"charset"`
  26. }
  27. var DbConfigJSON DbConfig
  28. //返回一个新的orm对象
  29. func CreateOrm() orm.Ormer {
  30. return orm.NewOrm()
  31. }
  32. func ConnDB(cnfpath string) (*MYSQL.DB, error) {
  33. logger.Logger.Println("加载数据库配置文件:" + cnfpath)
  34. fileHanlder, err := os.Open(cnfpath)
  35. if err != nil {
  36. logger.Logger.Error(fmt.Sprintf(" 配置文件%s加载失败:%s\r", cnfpath, err))
  37. os.Exit(1)
  38. }
  39. txt, _ := ioutil.ReadAll(fileHanlder)
  40. fileHanlder.Close()
  41. txtStr := strings.Trim(string(txt), " ")
  42. if txtStr == "" {
  43. logger.Logger.Error("数据库配置文件内容为空,请正确配置")
  44. os.Exit(1)
  45. }
  46. logger.Logger.Debug(txtStr)
  47. if txtStr[0:1] != "[" {
  48. txtStr = "[" + txtStr + "]"
  49. }
  50. cfgdata := []DbConfig{}
  51. err = json.Unmarshal([]byte(txtStr), &cfgdata)
  52. if err != nil {
  53. logger.Logger.Error("配置文件解析失败:" + err.Error())
  54. os.Exit(1)
  55. }
  56. var conn *MYSQL.DB
  57. for i, cfgItem := range cfgdata {
  58. if cfgItem.Port == "" {
  59. cfgItem.Port = "3306"
  60. }
  61. if cfgItem.Charset == "" {
  62. cfgItem.Charset = "utf8"
  63. }
  64. //urll:=root:11111111@tcp(127.0.0.1:3306)/mytest?charset=utf8
  65. url := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", cfgItem.User, cfgItem.Pwd, cfgItem.Host, cfgItem.Port, cfgItem.Db, cfgItem.Charset)
  66. conn, err := MYSQL.Open("mysql", url)
  67. if err != nil {
  68. return conn, err
  69. }
  70. global.MysqlConn = conn
  71. aliasName := cfgItem.Db
  72. if i == 0 {
  73. aliasName = "default" //第一个定义的数据库为默认数据库
  74. }
  75. err = orm.RegisterDataBase(aliasName, "mysql", url, 30)
  76. if err != nil {
  77. logger.Logger.Error("数据库[" + cfgItem.Db + "]连接失败:" + err.Error())
  78. os.Exit(0)
  79. }
  80. logger.Logger.Println("Mysql数据库[" + cfgItem.Db + "]连接成功")
  81. if conf.GlobalConfig["loglevel"] == "5" {
  82. orm.Debug = true
  83. }
  84. orm.SetMaxIdleConns(aliasName, 2)
  85. orm.SetMaxOpenConns(aliasName, 50)
  86. //测试数据库连接
  87. sql := "select 1 "
  88. testdb := orm.NewOrm()
  89. testdb.Using(aliasName)
  90. lst := []orm.Params{}
  91. _, err = testdb.Raw(sql).Values(&lst)
  92. if err != nil {
  93. logger.Logger.Println(fmt.Sprintf("数据库%s访问失败", cfgItem.Db))
  94. logger.Logger.Error(err)
  95. os.Exit(0)
  96. }
  97. if cfgItem.Db == "iss" {
  98. new(IssDB).Conn(cfgItem)
  99. }
  100. }
  101. //检查默认数据是否初始化
  102. sql := "select param_value from global_sys_param where param_name=? "
  103. o := orm.NewOrm()
  104. o.Using("default")
  105. datas := []orm.Params{}
  106. _, dberr := o.Raw(sql, "db_script_version").Values(&datas)
  107. //CacheMonitorPoint()
  108. if dberr != nil {
  109. fileHanlder, err = os.Open("db/db_init.sql")
  110. if err != nil {
  111. logger.Logger.Error("数据库初始化脚本文件db/db_init.sql加载失败:" + err.Error())
  112. return nil, err
  113. }
  114. //txt, _ = ioutil.ReadAll(fileHanlder)
  115. fileHanlder.Close()
  116. cmd := exec.Command("mysql", "-h"+cfgdata[0].Host, "-P"+cfgdata[0].Port,
  117. "-u"+cfgdata[0].User, "-p"+cfgdata[0].Pwd, "-D"+cfgdata[0].Db,
  118. "-e", "source db/db_init.sql")
  119. err = cmd.Start()
  120. if err != nil {
  121. logger.Logger.Error(err)
  122. }
  123. cmd.Wait()
  124. }
  125. db_script_version := ""
  126. if len(datas) > 0 {
  127. db_script_version = tools.IsEmpty(datas[0]["param_value"])
  128. } else {
  129. o.Raw("insert into global_sys_param (param_name,param_value,param_memo)values(?,?,?)", "db_script_version", "0", "数据库更新版本").Exec()
  130. }
  131. //检查是否有数据库更新脚本
  132. dbUpdatefile, err2 := ioutil.ReadDir("db")
  133. if err2 == nil {
  134. for _, fsitem := range dbUpdatefile {
  135. fs_n := fsitem.Name()
  136. filepath.Walk(fs_n, func(fpath string, fi os.FileInfo, err error) error {
  137. if path.Ext(fpath) == ".sql" && strings.HasPrefix(fpath, "db_update_v") {
  138. script_v := strings.Replace(fs_n, "db_update_v", "", 1)
  139. script_v = strings.Trim(strings.Replace(script_v, ".sql", "", 1), " ")
  140. if db_script_version == "" || script_v > db_script_version {
  141. fileHanlder, err = os.Open("db/" + fs_n)
  142. if err != nil {
  143. logger.Logger.Error("数据库初始化脚本文件db/" + fs_n + "加载失败:" + err.Error())
  144. return nil
  145. }
  146. //txt, _ = ioutil.ReadAll(fileHanlder)
  147. fileHanlder.Close()
  148. cmd := exec.Command("mysql", "-h"+cfgdata[0].Host, "-P"+cfgdata[0].Port,
  149. "-u"+cfgdata[0].User, "-p"+cfgdata[0].Pwd, "-D"+cfgdata[0].Db,
  150. "-e", "source db/"+fs_n)
  151. err = cmd.Start()
  152. if err != nil {
  153. logger.Logger.Error(err)
  154. } else {
  155. o.Raw("update global_sys_param set param_value=? where param_name=?", script_v, "db_script_version").Exec()
  156. }
  157. cmd.Wait()
  158. }
  159. }
  160. return nil
  161. })
  162. }
  163. } else {
  164. logger.Logger.Error(err2)
  165. }
  166. DbConfigJSON = cfgdata[0]
  167. return conn, nil
  168. }
  169. func CloseDB() {
  170. if global.MysqlConn != nil {
  171. global.MysqlConn.Close()
  172. }
  173. }
  174. func CheckErr(err error) {
  175. if err != nil {
  176. fmt.Println(tools.NowTime() + ":" + err.Error())
  177. panic(err)
  178. }
  179. }
  180. //测试数据库切换
  181. func DbSwitchTest() {
  182. db1 := orm.NewOrm() //操作默认数据库
  183. tmplst1 := []orm.Params{}
  184. db1.Raw("select * from global_sys_param").Values(&tmplst1)
  185. fmt.Println(tmplst1)
  186. err := db1.Using("gzpd1") //切换到其他数据库
  187. if err != nil {
  188. logger.Logger.Println(err.Error())
  189. } else {
  190. tmplst2 := []orm.Params{}
  191. db1.Raw("select * from global_sys_param").Values(&tmplst2)
  192. fmt.Println(tmplst2)
  193. }
  194. db1.Using("default") //切换到默认数据库
  195. db1.Raw("select * from global_const_code").Values(&tmplst1)
  196. fmt.Println(tmplst1)
  197. }