github.com/orofarne/hammy@v0.0.0-20130409105742-374fadfd6ecb/src/hammy/mysql_datareader.go (about) 1 package hammy 2 3 import ( 4 "fmt" 5 "strings" 6 "database/sql" 7 _ "github.com/ziutek/mymysql/godrv" 8 ) 9 10 // Driver for retriving historical data from MySQL database 11 // See mysql_saver.go for details about schema 12 type MySQLDataReader struct { 13 db *sql.DB 14 tableName string 15 logTableName string 16 hostTableName string 17 itemTableName string 18 pool chan int 19 } 20 21 func NewMySQLDataReader(cfg Config) (dr *MySQLDataReader, err error) { 22 dr = new(MySQLDataReader) 23 dr.db, err = sql.Open("mymysql", cfg.MySQLDataReader.Database + "/" + cfg.MySQLDataReader.User + "/" + cfg.MySQLDataReader.Password) 24 if err != nil { 25 return 26 } 27 28 dr.tableName = cfg.MySQLDataReader.Table 29 dr.logTableName = cfg.MySQLDataReader.LogTable 30 dr.hostTableName = cfg.MySQLDataReader.HostTable 31 dr.itemTableName = cfg.MySQLDataReader.ItemTable 32 33 dr.pool = make(chan int, cfg.MySQLDataReader.MaxConn) 34 for i := 0; i < cfg.MySQLDataReader.MaxConn; i++ { 35 dr.pool <- 1 36 } 37 38 return 39 } 40 41 func (dr *MySQLDataReader) Read(hostKey string, itemKey string, from uint64, to uint64) (data []IncomingValueData, err error) { 42 data = make([]IncomingValueData, 0) 43 44 var tName string 45 logValue := false 46 if strings.HasSuffix(itemKey, "#log") { 47 tName = dr.logTableName 48 logValue = true 49 } else { 50 tName = dr.tableName 51 } 52 53 sqlq := fmt.Sprintf("SELECT UNIX_TIMESTAMP(`history`.`timestamp`), `history`.`value` FROM `%s` `history` JOIN `%s` `item` ON `item`.`id` = `history`.`item_id` JOIN `%s` `host` ON `host`.`id` = `item`.`host_id` WHERE `host`.`name` = ? AND `item`.`name` = ? AND `history`.`timestamp` >= FROM_UNIXTIME(?) AND `history`.`timestamp` <= FROM_UNIXTIME(?) ORDER BY `history`.`timestamp`", tName, dr.itemTableName, dr.hostTableName) 54 rows, err := dr.db.Query(sqlq, hostKey, itemKey, from, to) 55 if err != nil { 56 return 57 } 58 59 for rows.Next() { 60 var ts uint64 61 var value interface{} 62 63 if logValue { 64 var val string 65 err = rows.Scan(&ts, &val) 66 if err != nil { 67 return 68 } 69 value = val 70 } else { 71 var val float64 72 err = rows.Scan(&ts, &val) 73 if err != nil { 74 return 75 } 76 value = val 77 } 78 79 elem := IncomingValueData{ 80 Timestamp: ts, 81 Value: value, 82 } 83 data = append(data, elem) 84 } 85 86 return 87 }