Golang sql.Rows类代码示例

/*
ScanAll accepts a pointer to a slice of a type and fills it with repeated calls to Scan.

ScanAll only works if you're trying to extract a single object from each row
of the query results. Additionally, it closes the passed sql.Rows object. ScanAll
effectively replaces this code

  // old code
  defer rows.Close()
  objs := []Object{}
  for rows.Next() {
	  var obj Object
	  Scan(rows, &obj)
	  objs = append(objs, obj)
  }

With simply

  // new code
  objs := []Object{}
  ScanAll(rows, &objs)
*/
func ScanAll(rows *sql.Rows, slicePtr interface{}) error {
	defer rows.Close()

	sliceVal := reflect.ValueOf(slicePtr).Elem()

	if sliceVal.Kind() != reflect.Slice {
		return fmt.Errorf("Argument to crud.ScanAll is not a slice")
	}

	elemType := sliceVal.Type().Elem()

	if elemType.Kind() != reflect.Struct {
		return fmt.Errorf("Argument to crud.ScanAll must be a slice of structs")
	}

	for rows.Next() {
		newVal := reflect.New(elemType)

		if er := Scan(rows, newVal.Interface()); er != nil {
			return er
		}

		sliceVal.Set(reflect.Append(sliceVal, newVal.Elem()))
	}

	return nil
}

func scanUser(user *data.User, rows *sql.Rows) error {
	var createTimeString string

	err := rows.Scan(
		&user.Username,
		&user.Password,
		&user.Salt,
		&user.Role,
		&user.TrustLevel,
		&createTimeString,
	)

	if err != nil {
		return err
	}

	createTime, err := time.Parse("2006-01-02 15:04:05", createTimeString) // this assumes UTC as timezone

	if err != nil {
		log.Println("User scanner failed to parse time.")
		return err
	}

	user.CreateTime = createTime

	return nil
}

func scanItemComment(comment *data.ItemComment, rows *sql.Rows) error {
	var updateTimeString string

	err := rows.Scan(
		&comment.ID,
		&comment.ItemID,
		&comment.Author,
		&comment.Body,
		&updateTimeString,
	)

	if err != nil {
		return err
	}

	updateTime, err := time.Parse("2006-01-02 15:04:05", updateTimeString) // this assumes UTC as timezone

	if err != nil {
		log.Println("ItemComment scanner failed to parse time. " + updateTimeString)
		return err
	}

	comment.UpdateTime = updateTime

	return nil
}

func sexDomain(dom string) (sexAnswer bool, err error) {
	var stmt *sql.Stmt

	stmt, err = db.Prepare(`SELECT dom FROM domains WHERE dom = $1 AND sex = true LIMIT 1`)

	if err != nil {
		fmt.Printf("db.Prepare error: %v\n", err)
		return false, err
	}

	var rows *sql.Rows

	rows, err = stmt.Query(dom)
	if err != nil {
		fmt.Printf("stmt.Query error: %v\n", err)
		return false, err
	}

	defer stmt.Close()
	if rows.Next() {
		var domain string

		err = rows.Scan(&domain)
		if err != nil {
			fmt.Printf("rows.Scan error: %v\n", err)
			return false, err
		}
		if configuration.Debug == "Yes" {
			fmt.Println("domain => \"" + domain + "\"")
		}
		return true, err
	}

	return false, nil
}

/*
	Copies *sql.Rows into the slice of maps or structs given by the pointer dst.
*/
func (self *T) FetchRow(dst interface{}, rows *sql.Rows) error {

	dstv := reflect.ValueOf(dst)

	if dstv.IsNil() || dstv.Kind() != reflect.Ptr {
		return db.ErrExpectingPointer
	}

	itemv := dstv.Elem()

	columns, err := getRowColumns(rows)

	if err != nil {
		return err
	}

	next := rows.Next()

	if next == false {
		return db.ErrNoMoreRows
	}

	item, err := self.fetchResult(itemv.Type(), rows, columns)

	if err != nil {
		return err
	}

	itemv.Set(reflect.Indirect(item))

	return nil
}

func (scan *Scan) One(rows *sql.Rows) error {
	for rows.Next() {
		return scan.Scan(rows, meta.DirectValueOf(scan.To))
	}

	return errors.New("No matching rows found.")
}

func bmPreparedQuery(b *testing.B) {
	stmt, err := db.Prepare("SELECT number, str FROM test")
	if err != nil {
		panic(err)
	}

	var num int64
	var str string
	var i int64
	var rows *sql.Rows
	for rep := 0; rep < 10000; rep++ {
		rows, err = stmt.Query()
		if err != nil {
			panic(err)
		}

		i = 0
		for rows.Next() {
			rows.Scan(&num, &str)
			if num != i {
				panic(fmt.Sprintf("Result didn't match: %d!=%d", num, i))
			}
			i++
		}

		if i != 100 {
			panic(fmt.Sprintf("Rows count doesn't match: %d!=100", i))
		}
	}

	stmt.Close()
}

// Scan values from the database
func scan(cols []string, rows *sql.Rows) map[string]interface{} {

	var rtn = map[string]interface{}{}

	// Create scanner array
	var values []interface{}
	var generic = reflect.TypeOf(values).Elem()
	for i := 0; i < len(cols); i++ {
		values = append(values, reflect.New(generic).Interface())
	}

	// Scan!
	var err = rows.Scan(values...)
	if err != nil {
		n.Log("Driver failed to scan values: %s", err.Error())
		return nil
	}

	// Convert into native types
	for i := 0; i < len(cols); i++ {
		var raw_value = *(values[i].(*interface{}))
		var raw_type = reflect.TypeOf(raw_value)
		switch {
		case raw_type == reflect.TypeOf(int64(0)):
			rtn[cols[i]] = raw_value.(int64)
		}
	}

	return rtn
}

func setTimer(rows *sql.Rows) (*Timer, error) {
	var finishedAt pq.NullTime
	var createdAt pq.NullTime

	timer := Timer{}

	err := rows.Scan(&timer.ID, &timer.User, &timer.Name, &createdAt, &finishedAt)
	if err != nil {
		return nil, err
	}

	val, err := createdAt.Value()
	if err != nil {
		return nil, err
	}

	if val != nil {
		timer.CreatedAt = &createdAt.Time
	}

	val, err = finishedAt.Value()
	if err != nil {
		return nil, err
	}

	if val != nil {
		timer.FinishedAt = &finishedAt.Time
	}

	return &timer, nil
}

func findNotesByUser(user *User, query string) []*Note {
	var err error
	var rows *sql.Rows

	if len(query) == 0 {
		rows, err = db.Query("SELECT * FROM notes WHERE user_id=?", user.ID)
	} else {
		queryFmt := fmt.Sprintf("%%%s%%", query)
		rows, err = db.Query(
			"SELECT * FROM notes WHERE user_id=? AND (body LIKE ? OR title LIKE ?)",
			user.ID,
			queryFmt,
			queryFmt)
	}

	if err != nil {
		checkErr(err, "findNotesByUser")
	} else {
		defer rows.Close()
	}

	var notes []*Note
	for rows.Next() {
		notes = append(notes, noteFromDbRows(rows))
	}
	return notes
}

//List ...
func (vs VictualService) List() []Victual {
	db, err := sql.Open("postgres", "host=localhost port=5432 user=pober dbname=pober sslmode=disable")
	checkErr(err)
	defer db.Close()

	var rows *sql.Rows
	rows, err = db.Query("SELECT id, description, brand, source, energyCal, energyJoule, fat, saturatedFat, carbohydrates, sugars, protein, salt, fiber, barCode, weightVolumeRatio FROM dc.victual")
	checkErr(err)

	victuals := []Victual{}

	for rows.Next() {
		var victual Victual
		err = rows.Scan(&victual.ID,
			&victual.Description,
			&victual.Brand,
			&victual.Source,
			&victual.EnergyCal,
			&victual.EnergyJoule,
			&victual.Fat,
			&victual.SaturatedFat,
			&victual.Carbohydrates,
			&victual.Sugars,
			&victual.Protein,
			&victual.Salt,
			&victual.Fiber,
			&victual.BarCode,
			&victual.WeightVolumeRatio)
		checkErr(err)
		victuals = append(victuals, victual)
	}
	return victuals
}

func getCallRoutes(lastNHours int, failedOnly bool) (callRoutes []*callRoute, err error) {
	var rows *sql.Rows
	callRouteSQL := `
      SELECT * FROM (
        SELECT c.app, c.route, COUNT(c.*) AS count, ROUND(AVG(extract(epoch from (c."end" - c.start))*1000000))::bigint AS avg_duration
        FROM "` + appmon.DBSchema + `".call c
        WHERE current_timestamp - c.start < ($1::int * interval '1 hour')
          AND c."end" IS NOT NULL
          AND ((NOT $2) OR (http_status_code < 200 OR http_status_code >= 400))
        GROUP BY app, route
      ) q ORDER BY count DESC
`
	rows, err = appmon.DB.Query(callRouteSQL, lastNHours, failedOnly)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		cr := new(callRoute)
		err = rows.Scan(&cr.App, &cr.Route, &cr.Count, &cr.AvgDuration)
		if err != nil {
			return
		}
		callRoutes = append(callRoutes, cr)
	}
	return
}

func bindComponent(name string, rs *sql.Rows, ctype componentType, manager *Manager) (*Component, error) {
	var id int64
	cols, err := rs.Columns()
	if err != nil {
		return nil, err
	}
	ifaces := make([]interface{}, len(cols))
	ifaceptrs := make([]interface{}, len(cols))
	for i := 0; i < len(ifaces); i++ {
		ifaceptrs[i] = &ifaces[i]
	}
	err = rs.Scan(ifaceptrs...)
	if err != nil {
		return nil, err
	}
	cv := reflect.New(ctype.typ).Elem()
	for i, field := range cols {
		if field == "entity_id" {
			id = ifaces[i].(int64)
			continue
		}
		f := cv.FieldByName(field)
		if !f.IsValid() {
			return nil, fmt.Errorf("Field %s is invalid for %s", field, name)
		}
		iv := reflect.ValueOf(ifaces[i])
		switch iv.Kind() {
		case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
			f.SetInt(iv.Int())
		default:
			f.Set(iv)
		}
	}
	return &Component{entity: id, name: name, isNew: false, manager: manager, data: cv.Addr().Interface()}, nil
}

func (d DAO) Select(key interface{}, result interface{}) error {
	if d.conn == nil {
		return errors.New("no connection to mysql!")
	}

	var err error
	var rows *sql.Rows
	sql := fmt.Sprintf("select * from %s where id = %d", d.table, key)
	rows, err = d.conn.Query(sql)
	if err != nil {
		fmt.Println(err)
		return err
	}

	t := reflect.ValueOf(result)
	e := t.Elem()

	e = e.Slice(0, e.Len())
	i := 0
	for rows.Next() && i < e.Len() {
		j := 0
		s := make([]interface{}, 0, 10)
		for j < e.Index(i).NumField() {
			s = append(s, e.Index(i).Field(j).Addr().Interface())
			j++
			//fmt.Println(j)
		}
		err = rows.Scan(s...)
		i++
	}

	return err
}

func validate_result_of_sp(dbt *DBTest, rows *sql.Rows) {

	// final OK packet , indicate last INSERT effected rows
	// https://dev.mysql.com/doc/internals/en/multi-resultset.html
	if res, ok := rows.Sibling(); false == ok {
		dbt.Fatal("there should be a last ok packet.")
	} else if nil == res {
		dbt.Fatal("last packet of CALL should return a Result.")
	} else {
		if affcted, err := res.RowsAffected(); nil != err {
			dbt.Fatal("should not return err when RowsAffected")
		} else if 1 != affcted {
			dbt.Fatal("Last INSERT STMT in SP should affected one row. ")
		}

		if insertId, err := res.LastInsertId(); nil != err {
			dbt.Fatal("should not return err when LastInsertId")
		} else if 0 != insertId {
			dbt.Fatalf("Last INSERT STMT in SP should not return LAST_INSERT_ID:%d ", insertId)
		}
		if true == rows.Next() {
			dbt.Fatal("last packet has no rows, but just affected rows and last insert id")
		}
	}

}