Golang DB.Ping方法代碼示例

func (s *sphinxPools) newConn() (*SphinxDB, error) {
	var (
		err error
		db  *sql.DB
		o   orm.Ormer
	)

	if db, err = sql.Open("sphinx", "root:[email protected]("+setting.SphinxHost+")/?loc=UTC"); err != nil {
		return nil, err
	}

	if err = db.Ping(); err != nil {
		db.Close()
		return nil, err
	}

	db.SetMaxIdleConns(1)
	db.SetMaxOpenConns(2)

	o, err = orm.NewOrmWithDB("sphinx", "sphinx", db)
	if err != nil {
		return nil, err
	}

	sdb := &SphinxDB{
		alive: true,
		pools: s,
		db:    db,
		orm:   o,
	}
	return sdb, nil
}

func ReadEpisodeResource(db *sql.DB, id int64) (EpisodeResource, error) {
	err := db.Ping()
	if err != nil {
		return EpisodeResource{}, err
	}

	m := "SELECT Series_ID, Name, URL FROM %v WHERE ID = ?"
	q := fmt.Sprintf(m, EpisodesResourceTable)
	var seriesID int64
	var name string
	var url string
	err = db.QueryRow(q, id).Scan(&seriesID, &name, &url)
	if err != nil {
		return EpisodeResource{}, err
	}

	r := EpisodeResource{
		ID:       id,
		SeriesID: seriesID,
		Name:     name,
		URL:      url,
	}

	return r, nil

}

func FindUserByName(db *sql.DB, name string) (User, error) {
	err := db.Ping()
	if err != nil {
		return User{}, err
	}

	m := "SELECT ID,Name,Password FROM %v WHERE Name = ?"
	q := fmt.Sprintf(m, UserTable)

	var id int64
	var nameTmp string
	var pass string

	err = db.QueryRow(q, name).Scan(&id, &nameTmp, &pass)
	if err != nil {
		return User{}, err
	}

	user := User{
		ID:       id,
		Name:     nameTmp,
		Password: pass,
	}

	return user, nil
}

func ReadUser(db *sql.DB, id int64) (User, error) {
	err := db.Ping()
	if err != nil {
		return User{}, err
	}

	m := "SELECT ID,Name,Password FROM %v WHERE ID = ?"
	q := fmt.Sprintf(m, UserTable)

	var idTmp int64
	var name string
	var pass string

	err = db.QueryRow(q, id).Scan(&idTmp, &name, &pass)
	if err != nil {
		return User{}, err
	}

	user := User{
		ID:       idTmp,
		Name:     name,
		Password: pass,
	}

	return user, nil
}

func (c *Connection) Connect(tries uint) error {
	if tries == 0 {
		return nil
	}

	var err error
	var db *sql.DB
	for i := tries; i > 0; i-- {
		// Wait before attempt.
		time.Sleep(c.backoff.Wait())

		// Open connection to MySQL but...
		db, err = sql.Open("mysql", c.dsn)
		if err != nil {
			continue
		}

		// ...try to use the connection for real.
		if err = db.Ping(); err != nil {
			// Connection failed.  Wrong username or password?
			db.Close()
			continue
		}

		// Connected
		c.conn = db
		c.backoff.Success()
		return nil
	}

	return errors.New(fmt.Sprintf("Failed to connect to MySQL after %d tries (%s)", tries, err))
}

func GetDbConn(connName string) (c *sql.DB, e error) {
	dbLock.Lock()
	defer dbLock.Unlock()

	var err error
	var dbConn *sql.DB
	dbConn = dbConnMap[connName]
	if dbConn == nil {
		dbConn, err = makeDbConn()
		if err != nil {
			closeDbConn(dbConn)
			return nil, err
		}
		dbConnMap[connName] = dbConn
	}

	err = dbConn.Ping()
	if err != nil {
		closeDbConn(dbConn)
		delete(dbConnMap, connName)
		return nil, err
	}

	return dbConn, err
}

func CreateTables(db *sql.DB) error {
	if err := db.Ping(); err != nil {
		return err
	}

	q := `
	CREATE TABLE IF NOT EXISTS %v (
		id BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(255) NOT NULL,
		first_name VARCHAR(255),
		last_name VARCHAR(255),
		email VARCHAR(500),
		password VARCHAR(255),
		is_active BOOL,
		last_login DATETIME
	)
	`

	query := fmt.Sprintf(q, UserTable)
	_, err := db.Exec(query)
	if err != nil {
		return err
	}

	return nil
}

func (q *query) Commit(db *sql.DB) error {

	err := db.Ping()
	if err != nil {
		fmt.Println(err)
	}

	for _, v := range q.sql {

		i := strings.Index(v.Key, "?")
		if i >= 0 {
			v.Key = v.Key[:i] + v.Value
		}

		v.Key += DOUBLE_NL

		_, err := db.Exec(v.Key)
		if err != nil {
			log.Println("Error Exec")
			return err
		}

		fmt.Println(v.Key)
	}

	return nil
}

func DatabaseConnect(c conf.Database) (*sql.DB, error) {
	var db *sql.DB
	var err error

	if c.Type == "mysql" {
		if c.Password == "" {
			db, err = sql.Open("mysql", c.Username+"@"+c.Protocol+"("+c.Address+":"+strconv.Itoa(c.Port)+")/")
		} else {
			db, err = sql.Open("mysql", c.Username+":"+c.Password+"@"+c.Protocol+"("+c.Address+":"+strconv.Itoa(c.Port)+")/")
		}
	}

	if err != nil {
		slog.Errorf("%v: %v: %v: %v", "Database", c.Type, "connect open error", err)
		return nil, fmt.Errorf("%v: %v", "connect open error", err)
	}

	// check if can connect to database, if fail, check again every minute until connected
	for {
		err = db.Ping()
		if err == nil {
			break
		}
		slog.Errorf("%v: %v: %v: %v", "Database", c.Type, "connect ping error", err)
		next := time.After(time.Minute)
		select {
		case <-next:
		case <-ContinuousCollectorVars.quit:
			db.Close()
			return nil, fmt.Errorf("connect while quitting")
		}
	}

	return db, nil
}

func (d *DB) setDB(dbh *sql.DB) (err error) {
	if err = dbh.Ping(); err != nil {
		return err
	}

	d.DB = dbh
	return nil
}

func SetKey(db *sql.DB, senderID int, mentionID string, payloadMsg string) (string, error) {

	var keyText string
	var keyType string

	payloadMsg = strings.Replace(payloadMsg, "/set_key ", "", -1)
	//Get the type of key
	var strParts = strings.Split(payloadMsg, " ")
	for _, pair := range strParts {
		tokens := strings.Split(pair, "=")
		if strings.ToLower(tokens[0]) == "type" {
			keyType = tokens[1]
		}
	}
	//Get the start of key
	if strings.Contains(payloadMsg, "-----BEGIN") {
		keyText = payloadMsg[strings.Index(payloadMsg, "-----BEGIN"):]
	}
	if strings.Contains(payloadMsg, "----- BEGIN") {
		keyText = payloadMsg[strings.Index(payloadMsg, "----- BEGIN"):]
	}
	if strings.Contains(payloadMsg, "ssh-rsa") {
		keyText = payloadMsg[strings.Index(payloadMsg, "ssh-rsa"):]
	}

	uk := UserKey{
		keyText: keyText,
		keyType: keyType,
		userID:  senderID,
	}

	if db == nil {
		log.Printf("db is nil")
	}

	var err = db.Ping()
	if err == nil {
		log.Printf("No ping to db")
	} else {
		log.Printf("Ping successful")
	}
	stmt, dberr := db.Prepare("INSERT INTO keys(userid, keytype, keytext) VALUES($1,$2,$3)")
	if dberr != nil {
		log.Fatal(dberr)
	}
	res, dberr := stmt.Exec(uk.userID, uk.keyType, uk.keyText)
	if dberr != nil {
		return "Error saving", dberr
		log.Fatal(dberr)
	}
	rowCnt, dberr := res.RowsAffected()
	if err != nil {
		log.Fatal(dberr)
	}
	return fmt.Sprintf("Added = %d key\n", rowCnt), nil

}

func SaveUser(user User, db *sql.DB) (sql.Result, error) {
	err := db.Ping()
	result, err := db.Exec("INSERT INTO companies(name, website) VALUES($1,$2);", user.FirstName, user.LastName)
	if err != nil {
		fmt.Printf("This is my Error - %+v\n", err)
	} else {
		fmt.Printf("Object Saved!")
	}
	return result, err
}

func SaveProduct(product Product, db *sql.DB) (sql.Result, error) {
	err := db.Ping()
	result, err := db.Exec("INSERT INTO product(name, description) VALUES($1,$2);", product.Name, product.Description)
	if err != nil {
		fmt.Printf("This is my Error - %+v\n", err)
	} else {
		fmt.Printf("Product Saved!")
	}
	return result, err
}

// PingServer uses a passed database handle to check if the database server works
func PingServer(db *sql.DB) string {

	err := db.Ping()
	if err != nil {
		return ("From Ping() Attempt: " + err.Error())
	}

	return ("Database Ping Worked...")

}

// helper function to ping the database with backoff to ensure
// a connection can be established before we proceed with the
// database setup and migration.
func pingDatabase(db *sql.DB) (err error) {
	for i := 0; i < 30; i++ {
		err = db.Ping()
		if err == nil {
			return
		}
		logrus.Infof("database ping failed. retry in 1s")
		time.Sleep(time.Second)
	}
	return
}