linux_monitor/server/db.go

package main

import (
	"database/sql"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	client "iaun.cn/linux/services/client"
)

const (
	USERNAME = "root"
	PASSWORD = "123456"
	NETWORK  = "tcp"
	SERVER   = "127.0.0.1"
	PORT     = 3306
	DATABASE = "monitor"
)

func sqlOpen() (*sql.DB, error) {
	dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		fmt.Println(err)
		return &sql.DB{}, errors.New("can not connect to db")
	}
	return db, nil
}

func sqlQuery(db *sql.DB, sql string, args ...interface{}) (*sql.Rows, error) {
	rows, err := db.Query(sql, args...)
	if err != nil {
		fmt.Println(err)
		return rows, err
	}
	return rows, nil
}

func addCPUPercent(cpu *client.CPUReq) bool {
	result, err := db.Exec("call insert_cpu_percent(?,?,?)",
		cpu.Server.Id, cpu.Server.Timestamp, cpu.Percent)
	if err != nil {
		fmt.Println(err)
		return false
	}
	fmt.Println(result)
	return true
}

func addMemory(mem *client.MemoryReq) bool {
	result, err := db.Exec("call insert_memory(?,?,?,?,?,?)",
		mem.Server.Id, mem.Server.Timestamp, mem.Total, mem.Free, mem.Cached, mem.UsedPercent)
	if err != nil {
		fmt.Println(err)
		return false
	}
	fmt.Println(result)
	return true
}

type CPUPercentDB struct {
	Time    int64   `db:"time" json:"time"`
	Percent float64 `db:"percent" json:"percent"`
}
type MaxTimestamp struct {
	Time int64 `db:"time"`
}
func getCPUPercent(id string, timestamp int64, limit int) (*[]CPUPercentDB, int64, error) {
	s := "call select_cpu_percent_with_time(?,?,?)"
	rows, err := sqlQuery(db, s, id, timestamp, limit)
	if err != nil {
		return nil, 0, err
	}
	defer func() {
		if rows != nil {
			_ = rows.Close()
		}
	}()
	percent := make([]CPUPercentDB, 0)
	for rows.Next() {
		temp := CPUPercentDB{}
		if err := rows.Scan(&temp.Time, &temp.Percent); err != nil {
			continue
		}
		percent = append(percent, temp)
	}
	if !rows.NextResultSet() {
		return nil, 0, errors.New("could not get max timestamp")
	}
	t := MaxTimestamp{}
	for rows.Next() {
		if err := rows.Scan(&t.Time); err != nil {
			continue
		}
	}
	return &percent, t.Time, nil
}

type MemoryDB struct {
	Time        int64   `db:"time" json:"time"`
	Total       uint64  `db:"total" json:"total"`
	Free        uint64  `db:"free" json:"free"`
	Cached      uint64  `db:"cached" json:"cached"`
	UsedPercent float64 `db:"used_percent" json:"used_percent"`
}

func getMemory(id string, timestamp int64, limit int) (*[]MemoryDB, int64, error) {
	s := "call select_memory(?,?,?)"
	rows, err := sqlQuery(db, s, id, timestamp, limit)
	if err != nil {
		return nil, 0, err
	}
	defer func() {
		if rows != nil {
			_ = rows.Close()
		}
	}()
	percent := make([]MemoryDB, 0)
	for rows.Next() {
		temp := MemoryDB{}
		if err := rows.Scan(&temp.Time, &temp.Total, &temp.Free, &temp.Cached, &temp.UsedPercent); err != nil {
			fmt.Println("scan failed")
			fmt.Println(err.Error())
			continue
		}
		percent = append(percent, temp)
	}
	if !rows.NextResultSet() {
		return nil, 0, errors.New("could not get max timestamp")
	}
	t := MaxTimestamp{}
	for rows.Next() {
		if err := rows.Scan(&t.Time); err != nil {
			fmt.Println("scan failed")
			fmt.Println(err.Error())
			continue
		}
	}
	return &percent, t.Time, nil
}

type ClientDB struct {
	Id string `json:"id"`
}

// 获取所有客户端ID
func getClient() (*[]ClientDB, error) {
	s := "select id from info"
	rows, err := sqlQuery(db, s)
	if err != nil {
		return nil, err
	}
	defer func() {
		if rows != nil {
			_ = rows.Close()
		}
	}()
	clients := make([]ClientDB, 0)
	for rows.Next() {
		temp := ClientDB{}
		if err := rows.Scan(&temp.Id); err != nil {
			fmt.Println("scan failed")
			fmt.Println(err.Error())
			continue
		}
		clients = append(clients, temp)
	}
	return &clients, nil
}

func registerClient(data *client.InitReq) bool {
	s := "call register_client(?,?,?,?,?)"
	reg, err := db.Prepare(s)
	if err != nil {
		return false
	}
	defer func() {
		_ = reg.Close()
	}()
	results, err := reg.Exec(data.Server.Id, data.CPU[0].Name, data.CPU[0].Count, data.CPU[0].Mhz, data.CPU[0].CacheSize)
	if err != nil {
		return false
	}
	fmt.Println()
	count, err := results.RowsAffected()
	if err != nil || count == 0 {
		return false
	}
	return true
}