Golang sql.NullString類代碼示例

func (w *DatabaseWorld) SaveThing(thing *Thing) (ok bool) {
	tabletext, err := json.Marshal(thing.Table)
	if err != nil {
		log.Println("Error serializing table data for thing", thing.Id, ":", err.Error())
		return false
	}

	var parent sql.NullInt64
	if thing.Parent != 0 {
		parent.Int64 = int64(thing.Parent)
		parent.Valid = true
	}
	var owner sql.NullInt64
	if thing.Owner != 0 && thing.Type.HasOwner() {
		owner.Int64 = int64(thing.Owner)
		owner.Valid = true
	}
	var program sql.NullString
	if thing.Program != nil {
		program.String = thing.Program.Text
		program.Valid = true
	}

	// TODO: save the allow list
	_, err = w.db.Exec("UPDATE thing SET name = $1, parent = $2, owner = $3, adminlist = $4, denylist = $5, tabledata = $6, program = $7 WHERE id = $8",
		thing.Name, parent, owner, thing.AdminList, thing.DenyList,
		types.JsonText(tabletext), program, thing.Id)
	if err != nil {
		log.Println("Error saving a thing", thing.Id, ":", err.Error())
		return false
	}
	return true
}

func TestStringOrNull(t *testing.T) {
	var (
		nullString sql.NullString
		value      driver.Value
		err        error
	)

	// When the string is empty
	nullString = StringOrNull("")

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

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

	// When the string is not empty
	nullString = StringOrNull("foo")

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

	// nullString.Value() should return the string
	value, err = nullString.Value()
	assert.Nil(t, err)
	assert.Equal(t, "foo", 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
}

// Scan implements the Scanner interface.
func (ns *ByteSlice) Scan(value interface{}) error {
	n := sql.NullString{String: base64.StdEncoding.EncodeToString(ns.ByteSlice)}
	err := n.Scan(value)
	//ns.Float32, ns.Valid = float32(n.Float64), n.Valid
	ns.ByteSlice, err = base64.StdEncoding.DecodeString(n.String)
	ns.Valid = n.Valid
	return err
}

func checkStringForNull(eventStr string, event *sql.NullString) {
	if len(eventStr) == 0 {
		event.Valid = false
	} else {
		event.String = eventStr
		event.Valid = true
	}
}

func (this *ERole) Scan(value interface{}) error {
	var x sql.NullString
	err := x.Scan(value)
	if !x.Valid || err != nil {
		return err
	}

	*this = ERole(x.String)
	return nil
}

func (this *Str) Scan(value interface{}) error {
	var s sql.NullString
	err := s.Scan(value)
	if !s.Valid || err != nil {
		return err
	}

	*this = Str(s.String)
	return nil
}

func main() {
	c := configFromFile()
	s := slack.New(c.SlackToken)
	db, err := sql.Open("mysql", c.Datasource)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	err = updateChannels(s, db)
	if err != nil {
		log.Fatal(err)
	}

	err = updateUsers(s, db)
	if err != nil {
		log.Fatal(err)
	}

	rows, err := db.Query("SELECT id, name, latest FROM channels")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id string
		var name string
		var latest sql.NullString

		if err := rows.Scan(&id, &name, &latest); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("import history: #%s\n", name)
		if !latest.Valid {
			latest.String = ""
		}
		l, err := addChannelHistory(s, db, id, latest.String)
		if err != nil {
			log.Fatal(err)
		}
		if l != "" {
			_, err = db.Exec(`UPDATE channels SET latest = ? WHERE id = ?`, l, id)
			if err != nil {
				log.Fatal(err)
			}
		}
	}
}

func main() {
	db, err := sqlx.Connect("mysql", "root:[email protected](localhost:3306)/bookszilla?parseTime=true")
	if err != nil {
		log.Fatalln(err)
	}
	db.Ping()

	for i := 1; i <= 1000; i++ {
		url := "http://wmate.ru/ebooks/book" + strconv.Itoa(i) + ".html"
		response, err := http.Get(url)
		println(url + " " + response.Status)

		if err != nil {
			fmt.Printf("%s", err)
			// os.Exit(1)
		} else if response.StatusCode == 200 {
			defer response.Body.Close()

			doc, err := goquery.NewDocumentFromResponse(response)
			if err != nil {
				log.Fatal(err)
			}

			doc.Find("article.post").Each(func(i int, s *goquery.Selection) {
				title := s.Find("h1").Text()
				println(title)

				// author := s.Find("ul.info li").Eq(2).Find("em").Text()
				// println(author)

				var desctiption sql.NullString
				desc := ""
				s.Find("p").Each(func(j int, sp *goquery.Selection) {
					desc += strings.TrimSpace(sp.Text()) + "\n"
				})

				if len(desc) > 0 {
					desctiption.Scan(desc)
				}

				// println(desctiption)

				sql := "INSERT INTO `books` (`name`, `description`) VALUES (?, ?);"
				db.Exec(sql, title, desctiption)
			})
		}
	}
}

func (h Hstore) Value() (driver.Value, error) {
	hstore := hstore.Hstore{Map: map[string]sql.NullString{}}
	if len(h) == 0 {
		return nil, nil
	}

	for key, value := range h {
		var s sql.NullString
		if value != nil {
			s.String = *value
			s.Valid = true
		}
		hstore.Map[key] = s
	}
	return hstore.Value()
}

// Input: email string, password string
// Output:
// - Success: session string
// - Failed: {}
func Login(w http.ResponseWriter, r *http.Request) {
	var email sql.NullString
	email.Scan(r.FormValue("email"))
	password := r.FormValue("password")

	user := models.User{Email: email, Password: password}

	if !repository.Login(&user) {
		w.WriteHeader(http.StatusForbidden)
		w.Write([]byte(`{"error":"Mat khau hoac email khong dung"}`))
		return
	}

	createUserToken(&user)
	repository.UpdateUser(&user)

	json.NewEncoder(w).Encode(user)
}

func TestNullTypeString(t *testing.T) {
	var b Sqlizer
	var name sql.NullString

	b = Eq{"name": name}
	sql, args, err := b.ToSql()

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

	name.Scan("Name")
	b = Eq{"name": name}
	sql, args, err = b.ToSql()

	assert.NoError(t, err)
	assert.Equal(t, []interface{}{"Name"}, args)
	assert.Equal(t, "name = ?", sql)
}

func (s *scanner) Scan(src interface{}) error {
	var err error

	switch s.value.Type().Kind() {
	case reflect.Struct:
		nt := mysql.NullTime{}
		err := nt.Scan(src)
		if err != nil {
			return err
		}
		s.value.Set(reflect.ValueOf(nt.Time))
	case reflect.Bool:
		nb := sql.NullBool{}
		err := nb.Scan(src)
		if err != nil {
			return err
		}
		s.value.SetBool(nb.Bool)
	case reflect.String:
		ns := sql.NullString{}
		err = ns.Scan(src)
		if err != nil {
			return err
		}
		s.value.SetString(ns.String)
	case reflect.Int64:
		ni := sql.NullInt64{}
		err = ni.Scan(src)
		if err != nil {
			return err
		}
		s.value.SetInt(ni.Int64)
	case reflect.Float64:
		ni := sql.NullFloat64{}
		err = ni.Scan(src)
		if err != nil {
			return err
		}
		s.value.SetFloat(ni.Float64)
	}
	return nil
}

func main() {
	db, err := sql.Open("postgres", "user=postgres password=pass dbname=mecab sslmode=disable")
	checkErr(err)
	//データの検索
	//rows, err := db.Query("SELECT * FROM words LIMIT 3")
	rows, err := db.Query("SELECT * FROM words WHERE first_char IS NULL")
	checkErr(err)

	for rows.Next() {
		var surface string
		var original string
		var reading string
		var first_c sql.NullString
		var last_c sql.NullString
		err = rows.Scan(&surface, &original, &reading, &first_c, &last_c)
		checkErr(err)
		/*
			fmt.Println(surface)
			fmt.Println(original)
			fmt.Println(reading)
			fmt.Println(reading[0:3])
			fmt.Println(reading[len(reading)-3 : len(reading)])
			fmt.Println(first_c)
			fmt.Println(last_c)
		*/

		first_c.String = reading[0:3]
		last_c.String = reading[len(reading)-3 : len(reading)]

		//データの更新
		stmt, err := db.Prepare("update words set first_char=$1, last_char=$2 where original=$3")
		checkErr(err)

		first_c.Valid = true
		last_c.Valid = true
		_, err = stmt.Exec(first_c.String, last_c.String, original)
		checkErr(err)

		/*
			affect, err := res.RowsAffected()
			checkErr(err)
			fmt.Println(affect)
		*/
	}

	db.Close()
	fmt.Println("finish")

}

func (b *BaseConditionQuery) FRES(value interface{}) interface{} {
	if (*b.SqlClause).IsAllowEmptyStringQuery() {
		return value
	}
	switch value.(type) {
	case sql.NullString:
		var nstr sql.NullString = value.(sql.NullString)
		if nstr.Valid && nstr.String == "" {
			nstr.Valid = false
		}
		return nstr
	case *sql.NullString:
		var nstr *sql.NullString = value.(*sql.NullString)
		if nstr.Valid && nstr.String == "" {
			nstr.Valid = false
		}
		return nstr
	case string:
		var str string = value.(string)
		if str == "" {
			var null sql.NullString
			null.Valid = false
			return null
		}
	case *string:
		var strx string = *value.(*string)
		if strx == "" {
			var null sql.NullString
			null.Valid = false
			return null
		}
	default:
		panic("This type not supported :" + GetType(value))
	}
	return value
}