Default 字段不生效 #1731

Open
opened 2020-06-30 22:57:20 +00:00 by mayinghan · 6 comments

当engine.Insert(data)时,data中不含某些字段,但是这些字段在model中定义了default值,然后数据库中的记录并没有应用那个default值,而是个空值或者0

当engine.Insert(data)时,data中不含某些字段,但是这些字段在model中定义了default值,然后数据库中的记录并没有应用那个default值,而是个空值或者0
Owner

Maybe we should add a Default(cols...) method when insert

Maybe we should add a `Default(cols...)` method when insert
lunny added the
kind
proposal
label 2021-06-12 08:44:03 +00:00
  1. 数值类的default无法生效。如默认Status值无法使用。
  2. 自定义类型的default无法使用,被写死当作null值添加了。

type TestDefaultVal1 struct {
	ID     int64 `json:"id" xorm:"id bigint pk autoIncr"`
	Status int   `json:"status" xorm:"status int not null default 3131"`
	Tmp    int   `json:"tmp" xorm:"tmp int default 0"`
}

func TestDefaultInsert1(t *testing.T) {
	//must := require.New(t)
	must := assert.New(t)

	db, err := testOpen()
	must.NoError(err)
	db.ShowSQL(true)

	err = db.Sync2(&TestDefaultVal1{})
	must.NoError(err)
	///////////

	n, err := db.InsertOne(TestDefaultVal1{})
	// BAD: INSERT INTO `test_default_val1` (`status`) VALUES (?) [0]
	// NOT USE DEFAULT VALUE "3131"
	must.NoError(err)
	must.True(n == 1)

	rs := TestDefaultVal1{}
	has, err := db.OrderBy("id desc").Get(&rs)
	must.NoError(err)
	must.True(has)

	t.Log(rs)
	must.Equal(rs.Status, 3131) //BAD

	n, err = db.Update(TestDefaultVal1{
		Tmp: int(rand.Uint32() % math.MaxInt32),
	}, TestDefaultVal1{
		ID: rs.ID,
	})
	must.NoError(err)
	must.True(n >= 0)

	r, err := db.Exec("INSERT INTO `test_default_val1` () values()")
	must.NoError(err)
	n, err = r.RowsAffected()
	must.NoError(err)
	must.True(n >= 1)

	rs = TestDefaultVal1{}
	has, err = db.OrderBy("id desc").Get(&rs)
	must.NoError(err)
	must.True(has)

	t.Log(rs)
	must.Equal(rs.Status, 3131) //GOOD

}

type TestDefaultVal2 struct {
	ID     int64     `json:"id" xorm:"id bigint pk autoIncr"`
	Amount MyDecimal `json:"amount" xorm:"amount decimal(10,5) not null default 30.12345"`
	Tmp    int       `json:"tmp" xorm:"tmp int default 0"`
}

func TestDefaultInsert2(t *testing.T) {
	//must := require.New(t)
	must := assert.New(t)

	db, err := testOpen()
	must.NoError(err)
	db.ShowSQL(true)

	err = db.Sync2(&TestDefaultVal2{})
	must.NoError(err)
	///////////

	n, err := db.InsertOne(TestDefaultVal2{})
	// BAD: INSERT INTO `test_default_val2` (`amount`) VALUES (?) [<nil>]
	// NOT USE DEFAULT VALUE "30.12345"
	must.NoError(err)
	must.True(n == 1)

	rs := TestDefaultVal2{}
	has, err := db.OrderBy("id desc").Get(&rs)
	must.NoError(err)
	must.True(has)

	t.Log(rs)
	must.Equal(rs.Amount.v, 30.12345) //BAD

	n, err = db.Update(TestDefaultVal2{
		Tmp: int(rand.Uint32() % math.MaxInt32),
	}, TestDefaultVal2{
		ID: rs.ID,
	})
	must.NoError(err)
	must.True(n >= 0)

	r, err := db.Exec("INSERT INTO `test_default_val2` () values()")
	must.NoError(err)
	n, err = r.RowsAffected()
	must.NoError(err)
	must.True(n >= 1)

	rs = TestDefaultVal2{}
	has, err = db.OrderBy("id desc").Get(&rs)
	must.NoError(err)
	must.True(has)

	t.Log(rs)
	must.Equal(rs.Amount.v, 30.12345) //GOOD

}

type MyDecimal struct {
	v      float64
	hasVal bool
}

//  Value is implement driver.Valuer
func (m MyDecimal) Value() (driver.Value, error) {
	if !m.hasVal {
		// not support use default value
		// if use nil, please use XXX.NULL or XXX.Default??
		return nil, nil
	}
	return []byte(fmt.Sprint(m.v)), nil // sample..
}

//  Scan is implement sql.Scanner
func (m *MyDecimal) Scan(src any) error {
	switch v := src.(type) {
	//case float32:
	//	m.hasVal = true
	//	m.v = float32(v)
	case float64:
		m.v = (v)
		m.hasVal = true
		return nil
	case nil:
		m.hasVal = false
		m.v = 0
		return nil
	case string:
		v2, err := strconv.ParseFloat(v, 64)
		if err != nil {
			return err
		}
		m.v = v2
		m.hasVal = true
		return nil
	case []byte:
		v2, err := strconv.ParseFloat(string(v), 64)
		if err != nil {
			return err
		}
		m.v = v2
		m.hasVal = true
		return nil
	default:
		return errors.New("invalid type")
	}
}


1. 数值类的default无法生效。如默认Status值无法使用。 2. 自定义类型的default无法使用,被写死当作null值添加了。 ```go type TestDefaultVal1 struct { ID int64 `json:"id" xorm:"id bigint pk autoIncr"` Status int `json:"status" xorm:"status int not null default 3131"` Tmp int `json:"tmp" xorm:"tmp int default 0"` } func TestDefaultInsert1(t *testing.T) { //must := require.New(t) must := assert.New(t) db, err := testOpen() must.NoError(err) db.ShowSQL(true) err = db.Sync2(&TestDefaultVal1{}) must.NoError(err) /////////// n, err := db.InsertOne(TestDefaultVal1{}) // BAD: INSERT INTO `test_default_val1` (`status`) VALUES (?) [0] // NOT USE DEFAULT VALUE "3131" must.NoError(err) must.True(n == 1) rs := TestDefaultVal1{} has, err := db.OrderBy("id desc").Get(&rs) must.NoError(err) must.True(has) t.Log(rs) must.Equal(rs.Status, 3131) //BAD n, err = db.Update(TestDefaultVal1{ Tmp: int(rand.Uint32() % math.MaxInt32), }, TestDefaultVal1{ ID: rs.ID, }) must.NoError(err) must.True(n >= 0) r, err := db.Exec("INSERT INTO `test_default_val1` () values()") must.NoError(err) n, err = r.RowsAffected() must.NoError(err) must.True(n >= 1) rs = TestDefaultVal1{} has, err = db.OrderBy("id desc").Get(&rs) must.NoError(err) must.True(has) t.Log(rs) must.Equal(rs.Status, 3131) //GOOD } type TestDefaultVal2 struct { ID int64 `json:"id" xorm:"id bigint pk autoIncr"` Amount MyDecimal `json:"amount" xorm:"amount decimal(10,5) not null default 30.12345"` Tmp int `json:"tmp" xorm:"tmp int default 0"` } func TestDefaultInsert2(t *testing.T) { //must := require.New(t) must := assert.New(t) db, err := testOpen() must.NoError(err) db.ShowSQL(true) err = db.Sync2(&TestDefaultVal2{}) must.NoError(err) /////////// n, err := db.InsertOne(TestDefaultVal2{}) // BAD: INSERT INTO `test_default_val2` (`amount`) VALUES (?) [<nil>] // NOT USE DEFAULT VALUE "30.12345" must.NoError(err) must.True(n == 1) rs := TestDefaultVal2{} has, err := db.OrderBy("id desc").Get(&rs) must.NoError(err) must.True(has) t.Log(rs) must.Equal(rs.Amount.v, 30.12345) //BAD n, err = db.Update(TestDefaultVal2{ Tmp: int(rand.Uint32() % math.MaxInt32), }, TestDefaultVal2{ ID: rs.ID, }) must.NoError(err) must.True(n >= 0) r, err := db.Exec("INSERT INTO `test_default_val2` () values()") must.NoError(err) n, err = r.RowsAffected() must.NoError(err) must.True(n >= 1) rs = TestDefaultVal2{} has, err = db.OrderBy("id desc").Get(&rs) must.NoError(err) must.True(has) t.Log(rs) must.Equal(rs.Amount.v, 30.12345) //GOOD } type MyDecimal struct { v float64 hasVal bool } // Value is implement driver.Valuer func (m MyDecimal) Value() (driver.Value, error) { if !m.hasVal { // not support use default value // if use nil, please use XXX.NULL or XXX.Default?? return nil, nil } return []byte(fmt.Sprint(m.v)), nil // sample.. } // Scan is implement sql.Scanner func (m *MyDecimal) Scan(src any) error { switch v := src.(type) { //case float32: // m.hasVal = true // m.v = float32(v) case float64: m.v = (v) m.hasVal = true return nil case nil: m.hasVal = false m.v = 0 return nil case string: v2, err := strconv.ParseFloat(v, 64) if err != nil { return err } m.v = v2 m.hasVal = true return nil case []byte: v2, err := strconv.ParseFloat(string(v), 64) if err != nil { return err } m.v = v2 m.hasVal = true return nil default: return errors.New("invalid type") } } ```

2.自定义类型的default无法使用,被写死当作null值添加了。

#2101

这个问题就有点冲突了。
如果是 sql.NullInt32 / sql.NullTime / sql.NullFloat64 这类的就有点冲突了
也就是无法判断 到底是使用 null 值,还是 default 值。因为这是三种状态(有值,null值,default值),
如果使用 driver.Valuer 来判断,只能判断两种状态,是无法实现3状态区分。无法细分猜测到用户到底要哪种





Maybe we should add a Default(cols...) method when insert

这个方案不会冲突了,由用户在外层来决定。但是缺点就是没办法类型内部决定到底要用3值中的哪一种值,也是比较头疼的冲突。

> 2.自定义类型的default无法使用,被写死当作null值添加了。 #2101 这个问题就有点冲突了。 如果是 sql.NullInt32 / sql.NullTime / sql.NullFloat64 这类的就有点冲突了 也就是无法判断 到底是使用 null 值,还是 default 值。因为这是三种状态(有值,null值,default值), 如果使用 driver.Valuer 来判断,只能判断两种状态,是无法实现3状态区分。无法细分猜测到用户到底要哪种 --- --- --- --- > Maybe we should add a `Default(cols...)` method when insert 这个方案不会冲突了,由用户在外层来决定。但是缺点就是没办法类型内部决定到底要用3值中的哪一种值,也是比较头疼的冲突。

先不说自定义类型和sql.NullXXX,就是基础类型int都不行???default成摆设了(xorm.io/xorm v1.3.4)?


type TestDefaultVal1 struct {
	ID                  int `json:"id" xorm:"id int pk autoIncr"`
	Num1_NotNull_DefNum int `json:"num1" xorm:"num1 int not null default 111"`
	Num2_NotNull_DefStr int `json:"num2" xorm:"num2 int not null default '222'"`
	Num3_DefNum         int `json:"num3" xorm:"num3 int default 333"`
	Num4_DefStr         int `json:"num4" xorm:"num4 int default '444'"`
	//Num5_NotNull_NullType_DefNum sql.NullInt32 `json:"num5" xorm:"num5 int not null default 555"`
	//Num6_NotNull_NullType_DefStr sql.NullInt32 `json:"num6" xorm:"num6 int not null default '666'"`
	Num7_NullType_DefNum sql.NullInt32 `json:"num7" xorm:"num7 int default 777"`
	Num8_NullType_DefStr sql.NullInt32 `json:"num8" xorm:"num8 int default '888'"`
	//Num9_Ptr_NotNull_DefNum     *int          `json:"num9" xorm:"num9 int not null default 999"`
	//Num10_Ptr_NotNull_DefStr     *int          `json:"num10" xorm:"num10 int not null default '101010'"`
	Num11_Ptr_DefNum *int `json:"num11" xorm:"num11 int  default 111111"`
	Num12_Ptr_DefStr *int `json:"num12" xorm:"num12 int  default '121212'"`
	Test             int  `json:"test" xorm:"test int"`
}

 func TestDefaultInsert(t *testing.T) {
        //must := assert.New(t)
		must := require.New(t)
		rs := &TestDefaultVal1{}
		n, err := db.InsertOne(rs)
		// BAD: INSERT INTO `test_default_val1` (`status`) VALUES (?) [0]
		// NOT USE DEFAULT VALUE "3131"
		must.NoError(err)
		must.True(n == 1)

		rs = query(must, rs.ID)
		//t.Log(rs)
		must.Equal(rs.Num1_NotNull_DefNum, 111) //BAD
		must.Equal(rs.Num2_NotNull_DefStr, 222) //BAD
		must.Equal(rs.Num3_DefNum, 333)         //BAD
		must.Equal(rs.Num4_DefStr, 444)         //BAD
		//must.Equal(int(rs.Num5_NotNull_NullType_DefNum.Int32), 555) //BAD
		//must.Equal(int(rs.Num6_NotNull_NullType_DefStr.Int32), 666) //BAD
		must.Equal(int(rs.Num7_NullType_DefNum.Int32), 777) //BAD
		must.Equal(int(rs.Num8_NullType_DefStr.Int32), 888) //BAD
		// 9,10
		must.NotNil(rs.Num11_Ptr_DefNum) //BAD
		if rs.Num11_Ptr_DefNum != nil {
			must.Equal(*rs.Num11_Ptr_DefNum, 111111) //BAD
		}
		must.NotNil(rs.Num12_Ptr_DefStr) //BAD
		if rs.Num12_Ptr_DefStr != nil {
			must.Equal(*rs.Num12_Ptr_DefStr, 121212) //BAD
		}
}

先不说自定义类型和sql.NullXXX,就是基础类型int都不行???default成摆设了(xorm.io/xorm v1.3.4)? ```go type TestDefaultVal1 struct { ID int `json:"id" xorm:"id int pk autoIncr"` Num1_NotNull_DefNum int `json:"num1" xorm:"num1 int not null default 111"` Num2_NotNull_DefStr int `json:"num2" xorm:"num2 int not null default '222'"` Num3_DefNum int `json:"num3" xorm:"num3 int default 333"` Num4_DefStr int `json:"num4" xorm:"num4 int default '444'"` //Num5_NotNull_NullType_DefNum sql.NullInt32 `json:"num5" xorm:"num5 int not null default 555"` //Num6_NotNull_NullType_DefStr sql.NullInt32 `json:"num6" xorm:"num6 int not null default '666'"` Num7_NullType_DefNum sql.NullInt32 `json:"num7" xorm:"num7 int default 777"` Num8_NullType_DefStr sql.NullInt32 `json:"num8" xorm:"num8 int default '888'"` //Num9_Ptr_NotNull_DefNum *int `json:"num9" xorm:"num9 int not null default 999"` //Num10_Ptr_NotNull_DefStr *int `json:"num10" xorm:"num10 int not null default '101010'"` Num11_Ptr_DefNum *int `json:"num11" xorm:"num11 int default 111111"` Num12_Ptr_DefStr *int `json:"num12" xorm:"num12 int default '121212'"` Test int `json:"test" xorm:"test int"` } func TestDefaultInsert(t *testing.T) { //must := assert.New(t) must := require.New(t) rs := &TestDefaultVal1{} n, err := db.InsertOne(rs) // BAD: INSERT INTO `test_default_val1` (`status`) VALUES (?) [0] // NOT USE DEFAULT VALUE "3131" must.NoError(err) must.True(n == 1) rs = query(must, rs.ID) //t.Log(rs) must.Equal(rs.Num1_NotNull_DefNum, 111) //BAD must.Equal(rs.Num2_NotNull_DefStr, 222) //BAD must.Equal(rs.Num3_DefNum, 333) //BAD must.Equal(rs.Num4_DefStr, 444) //BAD //must.Equal(int(rs.Num5_NotNull_NullType_DefNum.Int32), 555) //BAD //must.Equal(int(rs.Num6_NotNull_NullType_DefStr.Int32), 666) //BAD must.Equal(int(rs.Num7_NullType_DefNum.Int32), 777) //BAD must.Equal(int(rs.Num8_NullType_DefStr.Int32), 888) //BAD // 9,10 must.NotNil(rs.Num11_Ptr_DefNum) //BAD if rs.Num11_Ptr_DefNum != nil { must.Equal(*rs.Num11_Ptr_DefNum, 111111) //BAD } must.NotNil(rs.Num12_Ptr_DefStr) //BAD if rs.Num12_Ptr_DefStr != nil { must.Equal(*rs.Num12_Ptr_DefStr, 121212) //BAD } } ```
Owner

We don't know whether 0 is a meaningful value except you use *int. This is decided by Golang's features.

We don't know whether 0 is a meaningful value except you use *int. This is decided by Golang's features.

We don't know whether 0 is a meaningful value except you use *int. This is decided by Golang's features.

你看 Num11_Ptr_DefNum 和 Num12_Ptr_DefStr ,int* 一样不支持 。。 @lunny

> We don't know whether 0 is a meaningful value except you use *int. This is decided by Golang's features. 你看 Num11_Ptr_DefNum 和 Num12_Ptr_DefStr ,int* 一样不支持 。。 @lunny
Sign in to join this conversation.
No Milestone
No Assignees
3 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#1731
No description provided.