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 (s *sphinxPools) newConn() (*SphinxDB, error) {
var (
err error
db *sql.DB
o orm.Ormer
)
if db, err = sql.Open("sphinx", "root:root@tcp("+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 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
}
func update(db *sql.DB) {
err := db.Ping()
if err != nil {
log.Fatal(err)
}
stmt, err := db.Prepare("UPDATE test SET username = ? where id = ?")
_, err = stmt.Exec("newname", 1456)
if err != nil {
log.Fatal(err)
}
}
// Init must be called once before DB is used.
//
// Since it used glog, make sure it is also called after flag.Parse is called.
func (c *DatabaseConfig) NewVersionedDB() (*VersionedDB, error) {
if err := c.validate(); err != nil {
return nil, err
}
// If there is a connection string then connect to the MySQL server.
// This is for testing only. In production we get the relevant information
// from the metadata server.
var err error
var DB *sql.DB = nil
glog.Infoln("Opening SQL database.")
DB, err = sql.Open(DEFAULT_DRIVER, c.MySQLString())
if err != nil {
return nil, fmt.Errorf("Failed to open connection to SQL server: %v", err)
}
glog.Infoln("Sending Ping.")
if err := DB.Ping(); err != nil {
return nil, fmt.Errorf("Failed to ping SQL server: %v", err)
}
// As outlined in this comment:
// https://github.com/go-sql-driver/mysql/issues/257#issuecomment-48985975
// We can remove this once we have determined it's not necessary.
DB.SetMaxIdleConns(0)
DB.SetMaxOpenConns(200)
result := &VersionedDB{
DB: DB,
migrationSteps: c.MigrationSteps,
}
// Make sure the migration table exists.
if err := result.checkVersionTable(); err != nil {
return nil, fmt.Errorf("Attempt to create version table returned: %v", err)
}
glog.Infoln("Version table OK.")
// Ping the database occasionally to keep the connection fresh.
go func() {
c := time.Tick(1 * time.Minute)
for _ = range c {
if err := result.DB.Ping(); err != nil {
glog.Warningln("Database failed to respond:", err)
}
glog.Infof("db: Successful ping")
}
}()
return result, nil
}
func AppendSeriesList(db *sql.DB, userID, seriesID int64) error {
err := db.Ping()
if err != nil {
return err
}
q := fmt.Sprintf("INSERT INTO %v VALUES(?, ?)", SeriesListTable)
_, err = db.Exec(q, userID, seriesID)
if err != nil {
return err
}
return nil
}
func RemoveSeries(db *sql.DB, id int64) error {
if err := db.Ping(); err != nil {
return err
}
s := "DELETE FROM %v WHERE ID = ?"
q := fmt.Sprintf(s, SeriesTable)
if _, err := db.Exec(q, id); err != nil {
return err
}
return nil
}
func AppendToken(db *sql.DB, id, token string) error {
err := db.Ping()
if err != nil {
return err
}
q := fmt.Sprintf("UPDATE %v SET Token = ? WHERE ID = ?", TokenRequestTable)
_, err = db.Exec(q, token, id)
if err != nil {
return err
}
return nil
}
func main() {
config_file := flag.String("config", "config.yaml", "Config file")
flag.Parse()
// Create and load config.yaml
config := new(postfixadmin.Config)
contents, e := ioutil.ReadFile(*config_file)
if e != nil {
fmt.Printf("Config File Error: %v\n", e)
fmt.Printf("create one with -w \n")
os.Exit(1)
}
if err := yaml.Unmarshal(contents, &config); err != nil {
fmt.Println(err)
os.Exit(0)
}
// Setup TLS config
//config.Tls = new(tls.Config)
//config.Tls.ServerName = config.IMAPAddress
//config.Tls.InsecureSkipVerify = true
fmt.Printf("Config: %v\n", config.Db.TableNames)
// Create Database connection
var Db *sql.DB
var err_db error
Db, err_db = sql.Open(config.Db.Engine, config.Db.Datasource)
if err_db != nil {
fmt.Printf("Db Login Failed: ", err_db, "=", config.Db.Engine, config.Db.Datasource)
os.Exit(1)
}
err_ping := Db.Ping()
if err_ping != nil {
fmt.Printf("Db Ping Failed: ", err_ping, "=", config.Db.Engine, config.Db.Datasource)
os.Exit(1)
}
defer Db.Close()
postfixadmin.Initialize(config, Db)
// Setup router and config mods
router := mux.NewRouter()
postfixadmin.SetupRoutes(router)
fmt.Println("Serving on " + config.HTTPListen)
http.Handle("/", router)
http.ListenAndServe(config.HTTPListen, nil)
}
func RemoveTokenRequest(db *sql.DB, id string) error {
err := db.Ping()
if err != nil {
return err
}
q := fmt.Sprintf("DELETE FROM %v WHERE ID = ?", TokenRequestTable)
_, err = db.Exec(q, id)
if err != nil {
return err
}
return nil
}
func delete(db *sql.DB) {
err := db.Ping()
if err != nil {
log.Fatal(err)
}
stmt, err := db.Prepare("DELETE FROM test where id = ?")
if err != nil {
log.Fatal(err)
}
_, err = stmt.Exec(1457)
if err != nil {
log.Fatal(err)
}
}
func dbQueryRun(db *sql.DB) (rows *sql.Rows) {
db.Ping()
query := dbQueryInit()
//Run db Query
rows, err := db.Query(query)
if err != nil {
log.Fatal(err)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
return rows
}
// PingWithTimeout fires a normal sql Ping, guarded by a timeout
func PingWithTimeout(dbh *sql.DB, delay time.Duration) error {
ch := make(chan error, 1)
go func() { ch <- dbh.Ping() }()
var err error
select {
case err = <-ch:
// nothing
case <-time.After(delay):
err = errors.New("bookingsdb: database ping timeout")
}
return err
}
//PingHandler - Ping the databases
func PingHandler(d db.Database, dh *sql.DB, cmd *sqlcommand.SQLCommand, ctx *action.Context) error {
tstart := time.Now()
err := dh.Ping()
//TODO: переделать вывод статистики через триггер т.к. snap фиксирует старт и завершение
mSec := time.Now().Sub(tstart).Nanoseconds() / 1000 / 1000
if err != nil {
/*if !ctx.GetDef("silent",true).(bool) {
fmt.Fprintf(os.Stderr, "%s ping: %v\n", d.Code, strings.TrimSpace(err.Error()))
}*/
return err
}
fmt.Printf("%s ping: %v msec\n", d.Code, mSec)
return nil
}
func insert(db *sql.DB) {
err := db.Ping()
if err != nil {
log.Fatal(err)
}
stmt, err := db.Prepare("INSERT INTO test(id, username, password) VALUES(?, ?, ?)")
if err != nil {
log.Fatal(err)
}
_, err = stmt.Exec(1456, "name", "pass")
if err != nil {
log.Fatal(err)
}
}
// VerifyConnection pings the database to verify a connection is established. If the connection cannot be established,
// it will retry with an exponential back off.
func VerifyConnection(c *sql.DB) error {
pingDB := func() error {
return c.Ping()
}
expBackoff := backoff.NewExponentialBackOff()
expBackoff.MaxElapsedTime = time.Duration(30) * time.Second
err := backoff.Retry(pingDB, expBackoff)
if err != nil {
return err
}
return nil
}
func WaitForDatabase(t *testing.T, db *sql.DB) {
fmt.Println("--- Waiting for pg to be ready")
for {
err := db.Ping()
if err, ok := err.(*pq.Error); ok {
if err.Code.Name() == "cannot_connect_now" {
fmt.Println(err.Message)
time.Sleep(1000 * time.Millisecond)
continue
}
t.Fatal(err)
}
fmt.Println("Ready")
break
}
}
func CountSeriesWithImage(db *sql.DB, image string) (int, error) {
if err := db.Ping(); err != nil {
return 0, err
}
s := "SELECT COUNT(ID) as Images FROM %v WHERE Image = ?"
q := fmt.Sprintf(s, SeriesTable)
var amount int
err := db.QueryRow(q, image).Scan(&amount)
if err != nil {
return 0, err
}
return amount, nil
}
