예제 #1
0
// 删除时间范围的表的数据
func deleteTbl(ctx *cli.Context) {
	layout := "20060102"
	var err error

	delTable := ctx.String("table")
	if delTable == "" {
		log.Fatal("ERR: arg 'table' empty")
	}

	var start time.Time
	if start, err = time.Parse(layout, ctx.String("start")); err != nil {
		log.Fatal("ERR: arg 'start' 格式非法,正确的格式是, %s", layout)
	}

	var end time.Time
	if end, err = time.Parse(layout, ctx.String("end")); err != nil {
		log.Fatal("ERR: arg 'end' 格式非法,正确的格式是, %s", layout)
	}
	if start.After(end) {
		log.Fatal("ERR:  'start' after 'end'")
	}

	dates := dateSlice(start, end)
	log.Printf("dates: %v", dates)
	jsonconf := prod.NewJsonConf(ctx.String("json"))

	sqlTemp := make(map[string]string)
	sqlTemp[delTable] = "DELETE FROM petLog_%d." + delTable + "_%s"

	DbExecute(jsonconf, dates, sqlTemp)
}
예제 #2
0
// 根据模板文件,json配置生成建表语言,并执行
func creatTbl(ctx *cli.Context) {
	sqlTemp := readSqlTemplate(ctx.String("temp"))
	jsonconf := prod.NewJsonConf(ctx.String("json"))
	for _, conf := range jsonconf.Log_list {
		table := conf.Table
		// 新加的表的建表语句,直接由配置文件生成
		if _, ok := sqlTemp[table]; !ok {
			sqlTemp[table] = createSql(&conf)
			log.Printf("INF: table=%s", table)
		}
	}

	var start time.Time
	var err error
	if ctx.String("milestone") == "" {
		start = time.Now().AddDate(0, 0, 1)
	} else {
		start, err = time.Parse("20060102", ctx.String("milestone"))
		if err != nil {
			log.Panicf("ERR: %s", err)
		}
	}
	days := ctx.Int("days")
	if days < 1 {
		log.Panicf("ERR: arg 'days' invalid, %d", days)
	}
	end := start.AddDate(0, 0, days)

	dates := dateSlice(start, end)
	log.Printf("INF: dates, %s", dates)
	DbExecute(jsonconf, dates, sqlTemp)
}
예제 #3
0
파일: server.go 프로젝트: samuelyao314/mygo
func (s *ConsServer) loadConf() {
	log.Printf("INF, loadConf")

	s.jsonconf = prod.NewJsonConf(s.opts.JsFile)
	fi, _ := os.Stat(s.opts.JsFile)
	s.jsonMtime = fi.ModTime()

	s.dbpool.Start(s.jsonconf)
	s.updateReader(s.dbpool.InputChan)
}
예제 #4
0
// 检查表是否成功创建,如果失败,产生Agent告警
func checkTbl(ctx *cli.Context) {
	tomorrow := time.Now().AddDate(0, 0, 1)
	datestr := tomorrow.Format("20060102")

	jsonconf := prod.NewJsonConf(ctx.String("json"))

	table := ctx.String("table")
	// 最后1个号段的db
	dbname := "petLog_99"
	user := jsonconf.Default_db_user
	passwd := jsonconf.Default_db_pw
	port := jsonconf.Default_db_port
	// 配置文件中最后一台机器
	host := jsonconf.Route_list[len(jsonconf.Route_list)-1].Db_host

	dns := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=gbk", user, passwd, host, port, dbname)
	conn, err := sql.Open("mysql", dns)
	if err != nil {
		log.Panicf("ERR: sql.Open, %s, %s", dns, err)
	}

	expectTable := fmt.Sprintf("%s_%s", table, datestr)
	sqlstr := fmt.Sprintf("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='%s' and TABLE_NAME='%s'",
		dbname, expectTable)
	rows, err := conn.Query(sqlstr)
	if err != nil {
		util.AgentWarn(fmt.Sprintf("logAdmin, check, conn.Query, %s, %s, %s", dbname, table, datestr))
		log.Panic("ERR: db.Query, %s", err)
	}

	log.Printf("INF: host=%s, sql=%s", host, sqlstr)
	result := false
	for rows.Next() {
		var tablename string
		err = rows.Scan(&tablename)
		if err != nil {
			log.Panic("ERR: rows.Scan, %s", err)
			break
		}
		if tablename == expectTable {
			result = true
			break
		}
	}
	if !result {
		util.AgentWarn("logAdmin, check, fail to create tomorrow table")
	}
	conn.Close()
}
예제 #5
0
// 统计一天的一个指定日志的总量
func countTbl(ctx *cli.Context) {
	table := ctx.String("table")

	date := ctx.String("date")
	if _, err := time.Parse("20060102", date); err != nil {
		log.Panic("ERR: arg 'date' invalid")
	}

	jsonconf := prod.NewJsonConf(ctx.String("json"))
	user := jsonconf.Default_db_user
	passwd := jsonconf.Default_db_pw
	port := jsonconf.Default_db_port

	sum := 0
	for _, conf := range jsonconf.Route_list {
		host := conf.Db_host
		for u := conf.UinStartInt; u <= conf.UinEndInt; u++ {
			dbname := fmt.Sprintf("petLog_%d", u)
			dns := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=gbk", user, passwd, host, port, dbname)
			conn, err := sql.Open("mysql", dns)
			if err != nil {
				log.Panicf("ERR: sql.Open, %s, %s", dns, err)
			}
			sqlstr := fmt.Sprintf("SELECT count(*) FROM %s_%s", table, date)
			rows, err := conn.Query(sqlstr)
			if err != nil {
				log.Panic("ERR: db.Query, %s", err)
			}
			for rows.Next() {
				var cnt int
				_ = rows.Scan(&cnt)
				sum += cnt
			}

			log.Printf("INF: host=%s, db=%s, sql=%s", host, dbname, sqlstr)
			conn.Close()
		}
	}
	log.Printf("INF: table=%s, date=%s, sum=%d", table, date, sum)
}
예제 #6
0
// 根据模板文件,生成sql, 删除无用的表
func dropTbl(ctx *cli.Context) {
	var start time.Time
	var end time.Time

	layout := "20060102"
	//  第1个格式:是指定日期
	//  第2个格式是整数,起始点就是今天之前的第几天
	var err error
	if end, err = time.Parse(layout, ctx.String("milestone")); err != nil {
		if i, err := strconv.Atoi(ctx.String("milestone")); err != nil {
			log.Panicf("ERR: arg 'milestone' invalid, %s", ctx.String("milestone"))
		} else if i < 0 {
			log.Panicf("ERR: arg 'milestone' must >= 0")
		} else {
			end = time.Now().AddDate(0, 0, (-1)*i)
		}
	}

	days := ctx.Int("days")
	if days < 1 {
		log.Panicf("ERR: arg 'days' invalid, %d", days)
	}
	start = end.AddDate(0, 0, (-1)*days)
	log.Println(start.Format(layout), end.Format(layout))
	dates := dateSlice(start, end)
	log.Printf("INF: dates, %s", dates)

	jsonconf := prod.NewJsonConf(ctx.String("json"))
	sqlTemp := make(map[string]string)
	for _, conf := range jsonconf.Log_list {
		table := conf.Table
		// 新加的表的建表语句,直接由配置文件生成
		sqlTemp[table] = dropSql(table)
		log.Printf("INF: table=%s, sql=%s", table, sqlTemp[table])
	}

	DbExecute(jsonconf, dates, sqlTemp)
}
예제 #7
0
// 统一的搜索语句
func selectTbl(ctx *cli.Context) {
	layout := "20060102"
	var start time.Time
	var err error

	if start, err = time.Parse(layout, ctx.String("start")); err != nil {
		log.Panicf("ERR: arg 'start' 格式非法,正确的格式是, %s", layout)
	}

	var end time.Time
	if end, err = time.Parse(layout, ctx.String("end")); err != nil {
		log.Panicf("ERR: arg 'end' 格式非法,正确的格式是, %s", layout)
	}
	if start.After(end) {
		log.Panic("ERR: 开始时间start,晚于结束时间end")
	}
	dates := dateSlice(start, end)

	var uin int
	if uin, err = strconv.Atoi(ctx.String("uin")); err != nil {
		log.Panic("ERR: arg 'uin' 非法")
	}
	mod := uin % 100

	jsonconf := prod.NewJsonConf(ctx.String("json"))
	var conf *prod.RouteConf
	for i, c := range jsonconf.Route_list {
		if c.UinStartInt <= mod && mod <= c.UinEndInt {
			conf = &jsonconf.Route_list[i]
			break
		}
	}
	if conf == nil {
		log.Panicf("ERR: 没有找到数据库配置,uin=%d", uin)
	}

	table := ctx.String("table")
	if table == "" {
		log.Panic("ERR: 需要参数'table'")
	}

	dbname := fmt.Sprintf("petLog_%d", uin%100)
	user := jsonconf.Default_db_user
	passwd := jsonconf.Default_db_pw
	port := jsonconf.Default_db_port
	host := conf.Db_host

	dns := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=gbk", user, passwd, host, port, dbname)
	conn, err := sql.Open("mysql", dns)
	if err != nil {
		log.Panicf("ERR: sql.Open, %s, %s", dns, err)
	}
	for _, d := range dates {
		sqlstr := fmt.Sprintf("SELECT * FROM %s_%s WHERE uin=%d AND date >= '%s' AND  date < '%s'",
			table, d, uin, start, end)
		rows, err := conn.Query(sqlstr)
		if err != nil {
			log.Panic("ERR: db.Query, %s", err)
		}
		log.Printf("INF: execute, sql=%s", sqlstr)
		// 表的列数不固定
		cols, _ := rows.Columns()
		buff := make([]interface{}, len(cols)) // 临时slice,用来通过类型检查
		data := make([]string, len(cols))
		for i, _ := range buff {
			buff[i] = &data[i] // 把两个slice关联起来
		}
		uinCol := 0
		for i, col := range cols {
			if col == "uin" {
				uinCol = i
			}
		}
		for rows.Next() {
			rows.Scan(buff...)
			if cols[uinCol] == "" {
				// 跳过空结果集
				continue
			}
			fmt.Println()
			for k, col := range data {
				if cols[k] != "" {
					fmt.Printf("%10s:\t%10s\n", cols[k], col)
				}
			}
			fmt.Println()
		}

	}
	conn.Close()

}
예제 #8
0
func loadSqlFile(ctx *cli.Context) {
	filename := ctx.String("filename")
	key := "petLog_"
	if strings.Index(filename, key) == -1 {
		log.Printf("WAR: substr '%s' not in filename", key)
		return
	}

	re, _ := regexp.Compile("petLog_([0-9]+)")
	slice := re.FindStringSubmatch(filename)
	if len(slice) < 2 {
		log.Printf("WAR, not match, %s", filename)
		return
	}
	log.Printf("INF: filename=%s, dbIdx=%s", filename, slice[1])
	dbIdx, err := strconv.Atoi(slice[1])
	if err != nil {
		log.Printf("WAR: Atoi fail, %s, %s", slice[1], err)
		return
	}
	dbname := fmt.Sprintf("petLog_%d", dbIdx)
	lines, err := util.ReadLines(filename)
	if err != nil {
		log.Printf("WAR: fail to read file, %s,, %s", filename, err)
		return
	}

	jsonconf := prod.NewJsonConf(ctx.String("json"))
	user := jsonconf.Default_db_user
	passwd := jsonconf.Default_db_pw
	port := jsonconf.Default_db_port

	var host string
	for _, conf := range jsonconf.Route_list {
		if conf.UinStartInt <= dbIdx && dbIdx <= conf.UinEndInt {
			host = conf.Db_host
			break
		}
	}
	if host == "" {
		fmt.Printf("WAR: uin=%d, host not found", dbIdx)
		return
	}

	dns := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=gbk", user, passwd, host, port, dbname)
	conn, err := sql.Open("mysql", dns)
	if err != nil {
		log.Panicf("ERR: sql.Open, %s, %s", dns, err)
	}

	for _, line := range lines {
		sqlstr := line
		stmt, err := conn.Prepare(sqlstr)
		if err != nil {
			log.Panicf("ERR, conn.Prepare, %s, sql=%s", err, sqlstr)
		}
		_, err = stmt.Exec()
		if err != nil {
			log.Panicf("ERR, stmt.Exec, %s, sql=%s", err, sqlstr)
		}
		stmt.Close()
	}
	log.Printf("INF: execute, host=%s, filename=%s, lines=%d", host, filename, len(lines))
	conn.Close()
}