func mysqlVersion(conn mysqlc.Conn) (uint, uint, uint, error) {
rows, _, err := conn.Query("SELECT VERSION()")
if err != nil {
return 0, 0, 0, err
}
if len(rows) == 0 {
return 0, 0, 0, fmt.Errorf("SELECT VERSION() returned an empty set")
}
versionString := rows[0].Str(0)
version := strings.Split(versionString, ".")
invalidVersionErr := fmt.Errorf("Invalid major.minor.patch in %q", versionString)
if len(version) != 3 {
return 0, 0, 0, invalidVersionErr
}
major, err := strconv.ParseUint(version[0], 10, 32)
if err != nil {
return 0, 0, 0, invalidVersionErr
}
minor, err := strconv.ParseUint(version[1], 10, 32)
if err != nil {
return 0, 0, 0, invalidVersionErr
}
patch, err := strconv.ParseUint(version[2], 10, 32)
if err != nil {
return 0, 0, 0, invalidVersionErr
}
return uint(major), uint(minor), uint(patch), nil
}
func (m MySQLPlugin) fetchProcesslist(db mysql.Conn, stat map[string]float64) error {
rows, _, err := db.Query("SHOW PROCESSLIST")
if err != nil {
log.Fatalln("FetchMetrics (Processlist): ", err)
return err
}
for k := range processState {
stat[k] = 0
}
for _, row := range rows {
if len(row) > 1 {
var state string
if row[6] == nil {
state = "NULL"
} else {
state = string(row[6].([]byte))
}
parseProcesslist(state, &stat)
} else {
log.Fatalln("FetchMetrics (Processlist): row length is too small: ", len(row))
}
}
return nil
}
func (m MySQLPlugin) fetchShowStatus(db mysql.Conn, stat map[string]float64) error {
rows, _, err := db.Query("show /*!50002 global */ status")
if err != nil {
log.Fatalln("FetchMetrics (Status): ", err)
return err
}
for _, row := range rows {
if len(row) > 1 {
variableName := string(row[0].([]byte))
if err != nil {
log.Fatalln("FetchMetrics (Status Fetch): ", err)
return err
}
stat[variableName], _ = atof(string(row[1].([]byte)))
} else {
log.Fatalln("FetchMetrics (Status): row length is too small: ", len(row))
}
}
if m.EnableExtended {
err = fetchShowStatusBackwardCompatibile(stat)
if err != nil {
log.Fatalln("FetchExtendedMetrics (Status Fetch): ", err)
}
}
return nil
}
func (mu *MysqlUser) GrantExists(db mysql.Conn) (bool, error) {
rows, _, err := db.Query(
"SHOW GRANTS FOR '%v'@'%v'",
mu.Username,
mu.Hostname,
)
if err != nil {
return false, err
}
if len(rows) == 0 {
return false, nil
}
for _, row := range rows {
grant := row.Str(0)
// This regex hasn't been extensively tested but works in my local tests
regex := fmt.Sprintf("GRANT ALL (.+) ON .%v.(.+) TO .%v.@.%v.", mu.Database, mu.Username, mu.Hostname)
matched, err := regexp.MatchString(regex, grant)
if err != nil {
return false, err
}
if matched {
return true, nil
}
}
return false, nil
}
func udpateMySqlCdrImportStatus(db mysql.Conn, uniqueid string, status int) (err error) {
var query = fmt.Sprintf("UPDATE cdr SET import = %d WHERE uniqueid = '%s'", status, uniqueid)
log.Tracef("update cdr status [%s].\n", query)
_, _, err = db.Query(query)
//
return err
}
func (mydb *MysqlDatabase) CreateDatabase(db mysql.Conn) error {
// IF NOT EXISTS isn't strictly necessary since we are checking
// to make sure it doesn't exist first but it doesn't hurt.
_, _, err := db.Query("CREATE DATABASE IF NOT EXISTS %v", mydb.Name)
if err != nil {
return err
}
mydb.Result.Changed = true
return nil
}
func serverVersion(conn mysqlc.Conn) (*version.Version, error) {
rows, _, err := conn.Query("SELECT VERSION()")
if err != nil {
return nil, err
}
if len(rows) == 0 {
return nil, fmt.Errorf("SELECT VERSION() returned an empty set")
}
return version.NewVersion(rows[0].Str(0))
}
func del(db mysql.Conn, bag_id int, stuff_id int) {
_, _, err := db.Query("delete from player_bag where bag_id=%d", bag_id)
if err != nil {
panic(err)
}
_, _, err = db.Query("delete from paladin where paladin_id=%d", stuff_id)
if err != nil {
panic(err)
}
fmt.Printf("delete %d, %d\n", bag_id, stuff_id)
}
// Get list of existing tables in database
func getTables(db mysql.Conn) (tables []string) {
tables = make([]string, 0)
rows, _, err := db.Query("SHOW TABLES")
checkError(err)
for _, row := range rows {
for k, _ := range row {
tables = append(tables, row.Str(k))
}
}
return
}
func QueryCheck(db mysql.Conn, sql string, params ...interface{}) ([]mysql.Row, mysql.Result) {
rows, res, err := db.Query(sql)
if err != nil {
errlog.Error("QueryCheck =%s", sql)
errlog.Error("QueryCheck err=%s", err.Error())
fmt.Println(sql)
fmt.Println(err)
os.Exit(1)
}
return rows, res
}
func ProcessNum(db mysql.Conn, cfg *base.Cfg) (*model.MetaData, error) {
sql := "SHOW PROCESSLIST"
rows, _, err := db.Query(sql)
if err != nil {
return nil, err
}
monitorName := "process_nums"
threadNum := len(rows) - 1
data := model.NewMetric(monitorName, cfg)
data.SetValue(threadNum)
return data, nil
}
func queryByPath(w http.ResponseWriter, db mysql.Conn, pt string) {
rows, res := checkedResult(db.Query("select full_result from risks_check_history where apk='%s' ORDER BY id DESC Limit 1", pt))
full_result := res.Map("full_result")
for _, row := range rows {
//fmt.Ffmt.Printlnf(w,
fmt.Fprintf(w,
"------[ %s 包含以下风险]---------\n%s\n",
pt,
row.Str(full_result),
)
RightLine()
}
}
// Get the column list for the SELECT, applying the select map
// from config file.
func getColumnListForSelect(db mysql.Conn, table string) string {
columns := make([]string, 0)
rows, res, err := db.Query("SHOW COLUMNS FROM `%s`", table)
checkError(err)
for _, row := range rows {
column := row.Str(res.Map("Field"))
replacement, ok := selectMap[table][column]
if ok {
column = fmt.Sprintf("%s AS `%s`", replacement, column)
}
columns = append(columns, column)
}
return strings.Join(columns, ", ")
}
func (m MySQLPlugin) fetchShowSlaveStatus(db mysql.Conn, stat map[string]float64) error {
rows, res, err := db.Query("show slave status")
if err != nil {
log.Fatalln("FetchMetrics (Slave Status): ", err)
return err
}
for _, row := range rows {
idx := res.Map("Seconds_Behind_Master")
Value := row.Int(idx)
stat["Seconds_Behind_Master"] = float64(Value)
}
return nil
}
func deleteMySqlCelRecord(db mysql.Conn, uniqueid string) (err error) {
if config.PurgeCelEvents == false {
return nil
}
var query = fmt.Sprintf("DELETE FROM cel WHERE uniqueid = '%s' OR linkedid = '%s'", uniqueid, uniqueid)
_, _, err = db.Query(query)
log.Debugf("getMySqlCallDetails execute : [%s] .\n", query)
if err != nil {
log.Errorf("deleteMySqlCelRecord Failed delete record into cel table for uniqueid [%s].\n", uniqueid)
}
return err
}
//Execute customize requests provisted by configuration file
func executeCustomRequests(db mysql.Conn) (err error) {
for i := range config.CleanupRequests {
var query = config.CleanupRequests[i]
if strings.Contains(strings.ToLower(query), "delete") == true {
return errors.New("Using delete keyword is forbiden. Please check your configuration file(config.json)")
}
_, _, err = db.Query(query)
if err != nil {
return err
}
}
return nil
}
func (mydb *MysqlDatabase) DatabaseExists(db mysql.Conn) (bool, error) {
rows, _, err := db.Query(
"SELECT count(*) AS dbs FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '%v'",
mydb.Name,
)
if err != nil {
return false, err
}
if len(rows) != 1 {
return false, errors.New("Unexpected number of rows in DatabaseExists()")
}
if rows[0].Int(0) > 0 {
return true, nil
}
return false, nil
}
func (m MySQLPlugin) fetchShowVariables(db mysql.Conn, stat map[string]float64) error {
rows, _, err := db.Query("SHOW VARIABLES")
if err != nil {
log.Fatalln("FetchMetrics (Variables): ", err)
}
for _, row := range rows {
if len(row) > 1 {
variableName := string(row[0].([]byte))
if err != nil {
log.Println("FetchMetrics (Fetch Variables): ", err)
}
stat[variableName], _ = atof(string(row[1].([]byte)))
} else {
log.Fatalln("FetchMetrics (Variables): row length is too small: ", len(row))
}
}
return nil
}
func (mu *MysqlUser) UserExists(db mysql.Conn) (bool, error) {
rows, _, err := db.Query(
"SELECT COUNT(*) FROM mysql.user WHERE User = '******' and Host = '%v' and Password = password('%v')",
mu.Username,
mu.Hostname,
mu.Password,
)
if err != nil {
return false, err
}
if len(rows) != 1 {
return false, errors.New("Unexpected number of rows in UserExists()")
}
if rows[0].Int(0) > 0 {
return true, nil
}
return false, nil
}
func (c *Config) startDataBase() chan<- Item {
dbc := make(chan Item, DB_BUFFER_SIZE)
go func() {
var con mysql.Conn
for it := range dbc {
if con == nil {
con = c.connectDataBase()
}
if con != nil && it.Board != "" && it.Number != "" {
var query string
if it.Insert {
query = fmt.Sprintf(
"INSERT INTO thread_title (board,number,title,master,resnum) VALUES('%s',%s,'%s','%s',%d)",
it.Board,
it.Number,
con.Escape(it.Title),
con.Escape(utf8Substr(it.Master, 100)),
it.Resnum)
} else {
query = fmt.Sprintf(
"UPDATE thread_title SET resnum=%d WHERE board='%s' AND number=%s",
it.Resnum,
it.Board,
it.Number)
}
_, _, err := con.Query(query)
if err != nil {
log.Printf("mysql query error [%s]", query)
con.Close()
con = nil
}
}
}
if con != nil {
con.Close()
con = nil
}
}()
return dbc
}
func (m MySQLPlugin) FetchShowStatus(db mysql.Conn, stat map[string]float64) error {
rows, _, err := db.Query("show /*!50002 global */ status")
if err != nil {
log.Fatalln("FetchMetrics (Status): ", err)
return err
}
for _, row := range rows {
if len(row) > 1 {
Variable_name := string(row[0].([]byte))
if err != nil {
log.Fatalln("FetchMetrics (Status Fetch): ", err)
return err
}
//fmt.Println(Variable_name, Value)
stat[Variable_name], _ = _atof(string(row[1].([]byte)))
} else {
log.Fatalln("FetchMetrics (Status): row length is too small: ", len(row))
}
}
return nil
}
/**
* Process selection CEL events for given unique id
*/
func getMySqlCel(db mysql.Conn, uniqueid string) (cel Cel, err error) {
myCelQuery := "select UNIX_TIMESTAMP(eventtime) as eventtime from cel where eventtype LIKE 'ANSWER' AND linkedid!=uniqueid AND linkedid='" + uniqueid + "'"
//
rows, res, err := db.Query(myCelQuery)
if err != nil {
return cel, err
}
log.Tracef("getMySqlCel request executed and get [%d] rows\r\n", len(rows))
if len(rows) > 0 {
log.Tracef("getMySqlCel get rows for uniqueid [%s]", uniqueid)
row := rows[0]
eventtime := res.Map("eventtime")
cel.EventTime = row.Int64(eventtime)
} else {
log.Tracef("getMySqlCel faied to get rows for uniqueid [%s].", uniqueid)
cel.EventTime = 0
}
return cel, nil
}
func mysqlState(m *MysqlIns, db mysql.Conn, sql string) ([]*MetaData, error) {
rows, _, err := db.Query(sql)
if err != nil {
return nil, err
}
data := make([]*MetaData, len(rows))
i := 0
for _, row := range rows {
key_ := row.Str(0)
v, err := row.Int64Err(1)
// Ignore non digital value
if err != nil {
continue
}
data[i] = NewMetric(key_)
data[i].SetValue(v)
i++
}
return data[:i], nil
}
func deleteMySqlCdrRecord(db mysql.Conn, uniqueid string) (err error) {
var query = fmt.Sprintf("DELETE FROM cdr WHERE uniqueid = '%s'", uniqueid)
_, _, err = db.Query(query)
//
return err
}
func getMysqlCdr(db mysql.Conn) (results []RawCall, err error) {
log.Tracef("Enter into getMysqlCdr")
myQuery := "SELECT UNIX_TIMESTAMP(calldate) as calldate, clid, src, dst, channel, dcontext, disposition,billsec,duration,uniqueid,dstchannel, dnid, recordfile from asteriskcdrdb.cdr WHERE import = 0 and dcontext NOT LIKE 'app-alive-test' LIMIT " + config.DbMySqlFetchRowNumber
//
log.Debugf("Executing request [%s]\r\n", myQuery)
rows, res, err := db.Query(myQuery)
//
if err != nil {
log.Errorf("Executing request [%s] and get error [%s] \r\n", myQuery, err)
return nil, err
}
//
log.Tracef("getMysqlCdr request executed and get [%d] rows\r\n", len(rows))
//prepare results array
results = make([]RawCall, len(rows))
i := 0
for _, row := range rows {
//
var c RawCall //Cdr
//mapping databases fields
calldate := res.Map("calldate")
clid := res.Map("clid")
src := res.Map("src")
dst := res.Map("dst")
channel := res.Map("channel")
dcontext := res.Map("dcontext")
disposition := res.Map("disposition")
billsec := res.Map("billsec")
duration := res.Map("duration")
uniqueid := res.Map("uniqueid")
dnid := res.Map("dnid")
recordfile := res.Map("recordfile")
dstchannel := res.Map("dstchannel")
//
raw_clid := strings.FieldsFunc(row.Str(clid), bracket)
caller_name := ""
caller_number := ""
if len(raw_clid) == 2 {
caller_name = raw_clid[0]
caller_number = raw_clid[1]
} else if len(raw_clid) == 1 {
caller_number = raw_clid[0]
}
/*if len(raw_clid) == 2 {
caller_name := raw_clid[0]
caller_number := raw_clid[1]
}else len(raw_clid) == 1 {
caller_number := raw_clid[0]
}*/
//
c = RawCall{Id: bson.NewObjectId(),
Calldate: time.Unix(row.Int64(calldate)+int64(timeZoneOffset), 0),
MetadataDt: time.Unix(time.Now().Unix()+int64(timeZoneOffset), 0),
ClidName: caller_name,
ClidNumber: caller_number,
Src: row.Str(src),
Channel: row.Str(channel),
Dcontext: row.Str(dcontext),
DispositionStr: row.Str(disposition),
Disposition: 0,
AnswerWaitTime: 0,
Billsec: row.Int(billsec),
Duration: row.Int(duration),
Uniqueid: row.Str(uniqueid),
InoutStatus: 0,
RecordFile: row.Str(recordfile),
Dst: row.Str(dst),
Dnid: row.Str(dnid),
Dstchannel: row.Str(dstchannel)}
//
results[i] = c
i++
}
return results, nil
}
func getMySqlCallDetails(db mysql.Conn, uniqueid string) (results []CallDetail, err error) {
var sqlBase = "SELECT eventtype, UNIX_TIMESTAMP(eventtime) as eventtime, cid_num, cid_dnid, exten, context, peer, uniqueid, linkedid FROM cel WHERE "
var sqlOrder = " order by eventtime, id"
var sqlStart = sqlBase + " uniqueid = '" + uniqueid + "' OR linkedid = '" + uniqueid + "' " + sqlOrder
rows, res, err := db.Query(sqlStart)
//
if err != nil {
log.Errorf("getMySqlCallDetailsExecuting request get error [%s]\n", err)
return nil, err
}
if len(rows) == 0 {
log.Infof("getMySqlCallDetails 0 records from cel table for uniqueid [%s]\n", uniqueid)
return nil, nil
}
//
//var searchIdMap = make(map[string]string)
//for _, row := range rows {
// uniqueid := res.Map("uniqueid")
// linkedid := res.Map("linkedid")
// keyUniqueid := fmt.Sprintf("'%s'", row.Str(uniqueid))
// keyLinkedId := fmt.Sprintf("'%s'", row.Str(linkedid))
// searchIdMap[keyUniqueid] = keyUniqueid
// searchIdMap[keyLinkedId] = keyLinkedId
//}
//var keys []string
//for k := range searchIdMap {
// keys = append(keys, k)
//}
//var strIds = strings.Join(keys, ",")
//var sqlNext = sqlBase + "uniqueid IN (" + strIds + ") OR linkedid IN (" + strIds + ")" + sqlOrder
////
//log.Tracef("Call details query : [%s]", sqlNext)
//rowsNext, resNext, errNext := db.Query(sqlNext)
//if errNext != nil {
// log.Errorf(" getMySqlCallDetailsExecuting request [%s] and get error [%s] \r\n", sqlNext, errNext)
// return nil, errNext
//}
//if len(rowsNext) == 0 {
// return nil, nil
//}
rowsNext := rows
resNext := res
////prepare results array
results = make([]CallDetail, len(rowsNext))
i := 0
for _, rowNext := range rowsNext {
//
var c CallDetail
//mapping databases fields
eventtype := resNext.Map("eventtype")
eventtime := resNext.Map("eventtime")
cid_num := resNext.Map("cid_num")
cid_dnid := resNext.Map("cid_dnid")
exten := resNext.Map("exten")
uniqueid := resNext.Map("uniqueid")
linkedid := resNext.Map("linkedid")
context := resNext.Map("context")
peer := resNext.Map("peer")
//
c.EventType = rowNext.Str(eventtype)
c.EventTime = time.Unix(rowNext.Int64(eventtime)+int64(timeZoneOffset), 0)
c.CidNum = rowNext.Str(cid_num)
c.CidDnid = rowNext.Str(cid_dnid)
c.Exten = rowNext.Str(exten)
c.UniqueId = rowNext.Str(uniqueid)
c.LinkedId = rowNext.Str(linkedid)
c.Peer = rowNext.Str(peer)
c.Context = rowNext.Str(context)
results[i] = c
i++
}
log.Tracef("getMySqlCallDetails Return [%d] results .\r\n", len(results))
return results, nil
}