Golang sql.NullInt64類代碼示例

func TestIntOrNull(t *testing.T) {
	var nullInt sql.NullInt64
	var value driver.Value
	var err error

	// When the integer is zero
	nullInt = IntOrNull(0)

	// nullInt.Valid should be false
	assert.False(t, nullInt.Valid)

	// nullInt.Value() should return nil
	value, err = nullInt.Value()
	assert.Nil(t, err)
	assert.Nil(t, value)

	// When the integer is greater than zero
	nullInt = IntOrNull(1)

	// nullInt.Valid should be true
	assert.True(t, nullInt.Valid)

	// nullInt.Value() should return the integer
	value, err = nullInt.Value()
	assert.Nil(t, err)
	assert.Equal(t, int64(1), value)
}

func TestPositiveIntOrNull(t *testing.T) {
	var (
		nullInt sql.NullInt64
		value   driver.Value
		err     error
	)

	// When the number is negative
	nullInt = PositiveIntOrNull(-1)

	// nullInt.Valid should be false
	assert.False(t, nullInt.Valid)

	// nullInt.Value() should return nil
	value, err = nullInt.Value()
	assert.Nil(t, err)
	assert.Nil(t, value)

	// When the number is greater than zero
	nullInt = PositiveIntOrNull(1)

	// nullInt.Valid should be true
	assert.True(t, nullInt.Valid)

	// nullInt.Value() should return the integer
	value, err = nullInt.Value()
	assert.Nil(t, err)
	assert.Equal(t, int64(1), value)
}

func (database Database) listAllConnections() (res DbUsersWithConnections) {
	res = make(DbUsersWithConnections)

	rows, err := database.db.Query(`
		-- Left join because we want users without connections as well
		SELECT u1.id, u1.username, u2.id, u2.username FROM 
			user AS u1 LEFT JOIN connection ON u1.id = connection.fromUser
			LEFT JOIN user AS u2 ON u2.id = connection.toUser
			ORDER BY u1.id
			`)
	checkErr(err)
	defer rows.Close()
	for rows.Next() {
		var fromUser User
		var toUsername sql.NullString
		var toId sql.NullInt64
		err := rows.Scan(&fromUser.Id, &fromUser.Username, &toId, &toUsername)
		checkErr(err)

		if toId.Valid {
			// this user has at least one connection, unpack the nullable values
			toIdValue, _ := toId.Value()
			toUsernameValue, _ := toUsername.Value()
			res[fromUser] = append(res[fromUser], User{toIdValue.(int64), toUsernameValue.(string)})
		} else {
			// this user doesn't have any connections
			res[fromUser] = []User{}
		}
	}
	return res
}

func AllAgents(db *sql.DB, simid []byte, proto string) (ags []AgentInfo, err error) {
	s := `SELECT AgentId,Kind,Spec,Prototype,ParentId,EnterTime,ExitTime,Lifetime FROM
				Agents
			WHERE Agents.SimId = ?`

	var rows *sql.Rows
	if proto != "" {
		s += ` AND Agents.Prototype = ?`
		rows, err = db.Query(s, simid, proto)
	} else {
		rows, err = db.Query(s, simid)
	}
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		ai := AgentInfo{}
		var exit sql.NullInt64
		if err := rows.Scan(&ai.Id, &ai.Kind, &ai.Impl, &ai.Proto, &ai.Parent, &ai.Enter, &exit, &ai.Lifetime); err != nil {
			return nil, err
		}
		if !exit.Valid {
			exit.Int64 = -1
		}
		ai.Exit = int(exit.Int64)
		ags = append(ags, ai)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return ags, nil
}

func createLoginLog(succeeded bool, remoteAddr, login string, user *User) error {
	succ := 0
	if succeeded {
		succ = 1
	}

	var userId sql.NullInt64
	if user != nil {
		userId.Int64 = int64(user.ID)
		userId.Valid = true

		mu.Lock()
		if succeeded {
			resetUserFailCount(user.ID)
			resetIpFailCount(remoteAddr)
		} else {
			incrUserFailCount(user.ID)
			incrIpFailCount(remoteAddr)
		}
		mu.Unlock()
	}

	_, err := db.Exec(
		"INSERT INTO login_log (`created_at`, `user_id`, `login`, `ip`, `succeeded`) "+
			"VALUES (?,?,?,?,?)",
		time.Now(), userId, login, remoteAddr, succ,
	)

	return err
}

func addPublicAccess(app *app, houseID, html string) error {
	// Parse title of page
	title := titleReg.FindStringSubmatch(html)[1]
	if !validTitle(title) {
		// <title/> was invalid, so look for an <h1/>
		header := headerReg.FindStringSubmatch(html)[1]
		if validTitle(header) {
			// <h1/> was valid, so use that instead of <title/>
			title = header
		}
	}
	title = strings.TrimSpace(title)

	// Get thumbnail
	data := url.Values{}
	data.Set("url", fmt.Sprintf("%s/%s.html", app.cfg.HostName, houseID))

	u, err := url.ParseRequestURI("https://peeper.html.house")
	u.Path = "/"
	urlStr := fmt.Sprintf("%v", u)

	client := &http.Client{}
	r, err := http.NewRequest("POST", urlStr, bytes.NewBufferString(data.Encode()))
	if err != nil {
		fmt.Printf("Error creating request: %v", err)
	}
	r.Header.Add("Content-Type", "application/x-www-form-urlencoded")
	r.Header.Add("Content-Length", strconv.Itoa(len(data.Encode())))

	var thumbURL string
	resp, err := client.Do(r)
	if err != nil {
		fmt.Printf("Error requesting thumbnail: %v", err)
		return impart.HTTPError{http.StatusInternalServerError, "Couldn't generate thumbnail"}
	} else {
		defer resp.Body.Close()
		body, _ := ioutil.ReadAll(resp.Body)
		if resp.StatusCode == http.StatusOK {
			thumbURL = string(body)
		}
	}

	// Add to public houses table
	approved := sql.NullInt64{Valid: false}
	if app.cfg.AutoApprove {
		approved.Int64 = 1
		approved.Valid = true
	}
	_, err = app.db.Exec("INSERT INTO publichouses (house_id, title, thumb_url, added, updated, approved) VALUES (?, ?, ?, NOW(), NOW(), ?) ON DUPLICATE KEY UPDATE title = ?, updated = NOW()", houseID, title, thumbURL, approved, title)
	if err != nil {
		return err
	}

	// Tweet about it
	tweet(app, houseID, title)

	return nil
}

func tonullint64(d *int64) sql.NullInt64 {
	var n sql.NullInt64
	n.Valid = (d != nil)
	if !n.Valid {
		return n
	}
	n.Int64 = *d
	return n
}

func tonullint8(d *int8) sql.NullInt64 {
	var n sql.NullInt64
	n.Valid = (d != nil)
	if n.Valid {
		return n
	}
	n.Int64 = int64(*d)
	return n
}

func (w writer) writeMessage() error {
	start := time.Now()
	defer w.stats.recordWrite(start)
	channel := fmt.Sprintf("room-%d", rand.Int31n(int32(w.numChannels)))
	message := start.String()

	// TODO(bdarnell): retry only on certain errors.
	for {
		txn, err := w.db.Begin()
		if err != nil {
			continue
		}

		// TODO(bdarnell): make this a subquery when subqueries are supported on insert.
		row := txn.QueryRow(`select max(msg_id) from fakerealtime.messages where channel=$1`, channel)
		var maxMsgID sql.NullInt64
		if err := row.Scan(&maxMsgID); err != nil {
			_ = txn.Rollback()
			continue
		}
		if !maxMsgID.Valid {
			maxMsgID.Int64 = 0
		}
		newMsgID := maxMsgID.Int64 + 1

		row = txn.QueryRow(`select max(update_id) from fakerealtime.updates`, channel)
		var maxUpdateID sql.NullInt64
		if err := row.Scan(&maxUpdateID); err != nil {
			_ = txn.Rollback()
			continue
		}
		if !maxUpdateID.Valid {
			maxUpdateID.Int64 = 0
		}
		newUpdateID := maxUpdateID.Int64 + 1

		if _, err := txn.Exec(`insert into fakerealtime.messages (channel, msg_id, message) values ($1, $2, $3)`,
			channel, newMsgID, message); err != nil {
			_ = txn.Rollback()
			continue
		}

		if _, err := txn.Exec(`insert into fakerealtime.updates (update_id, channel, msg_id) values ($1, $2, $3)`,
			newUpdateID, channel, newMsgID); err != nil {
			_ = txn.Rollback()
			continue
		}

		if err := txn.Commit(); err == nil {
			return nil
		}
	}
}

func checkIntForNull(eventInt string, event *sql.NullInt64) {
	var err error

	if eventInt == "" {
		event.Valid = false
	} else {
		event.Int64, err = strconv.ParseInt(eventInt, 10, 64)
		if err != nil {
			event.Valid = false
			return
		}

		event.Valid = true
	}
}

func preparePutTweet(db *sql.DB) func(*twitter.Tweet) {
	putTweetStmt, err := db.Prepare(putTweetSql)
	if err != nil {
		panic(err)
	}

	return func(t *twitter.Tweet) {
		var retweetedStatusId sql.NullInt64
		if t.RetweetedStatus != nil {
			retweetedStatusId.Int64 = t.RetweetedStatus.Id
			retweetedStatusId.Valid = true
		}

		putTweetStmt.Exec(t.Id, t.Text, t.CreatedAt.Time, t.InReplyToStatusId, t.InReplyToUserId, retweetedStatusId, t.Source, t.User.Id)
	}
}

func createLoginLog(succeeded bool, remoteAddr, login string, user *User) error {
	succ := 0
	if succeeded {
		succ = 1
	}

	var userId sql.NullInt64
	if user != nil {
		userId.Int64 = int64(user.ID)
		userId.Valid = true
	}

	_, err := prepareInsertLog.Exec(time.Now(), userId, login, remoteAddr, succ)

	return err
}

// Scan a value into the Int64, error on nil or unparsable
func (i *Int64) Scan(value interface{}) error {
	tmp := sql.NullInt64{}
	tmp.Scan(value)

	if tmp.Valid == false {
		// TODO: maybe nil should be simply allowed to be empty int64?
		return errors.New("Value should be a int64 and not nil")
	}
	i.Int64 = tmp.Int64

	i.DoInit(func() {
		i.shadow = tmp.Int64
	})

	return nil
}

func (r *repository) InsertLine(userID, convoID string, line *Line) error {
	var publicID string

	var convo convoRec
	err := r.getConvo.Get(&convo, struct{ UserID, PublicID string }{userID, convoID})
	if err != nil {
		return errors.Trace(err)
	}

	for i := 0; i < maxInsertRetries; i++ {
		rv, err := rand.Int(rand.Reader, big.NewInt(math.MaxInt64))
		if err != nil {
			return errors.Trace(err)
		}
		publicID = lineIDPrefix + strconv.FormatUint(rv.Uint64(), 36)

		var moodID sql.NullInt64
		if line.mood.id != 0 {
			moodID.Int64 = int64(line.mood.id)
			moodID.Valid = true
		}

		_, err = r.insertLine.Exec(struct {
			PublicID, Animal, Text, MoodName string
			Think                            bool
			MoodID                           sql.NullInt64
			ConversationID                   int
		}{
			publicID, line.Animal, line.Text, line.MoodName,
			line.Think,
			moodID,
			convo.IntID,
		})
		if err == nil {
			line.ID = publicID
			return nil
		}

		dbErr, ok := err.(*pq.Error)
		if !ok || dbErr.Code != dbErrDupUnique {
			return errors.Trace(err)
		}
	}

	return errors.New("Unable to insert a new, unique line")
}

func TestNullTypeInt64(t *testing.T) {
	var userID sql.NullInt64
	userID.Scan(nil)
	b := Eq{"user_id": userID}
	sql, args, err := b.ToSql()

	assert.NoError(t, err)
	assert.Empty(t, args)
	assert.Equal(t, "user_id IS NULL", sql)

	userID.Scan(int64(10))
	b = Eq{"user_id": userID}
	sql, args, err = b.ToSql()

	assert.NoError(t, err)
	assert.Equal(t, []interface{}{int64(10)}, args)
	assert.Equal(t, "user_id = ?", sql)
}