xorm/xorm
xorm/xorm
63
360
Fork 208
Code Issues 373 Pull Requests 17 Actions Releases 35 Activity

Fix the issue of incorrect insertion of data in non UTC time zone zero for numeric types (#2413)

Browse Source 
Fix the issue of incorrect insertion of data in non-UTC time zone zero for numeric types

Co-authored-by: CyJay <cyjay@MacBook-Pro.lan>
Reviewed-on: #2413
Reviewed-by: Lunny Xiao <xiaolunwen@gmail.com>
Co-authored-by: CyJaySong <CyJaySong@gmail.com>
Co-committed-by: CyJaySong <CyJaySong@gmail.com>
This commit is contained in:
CyJaySong 2024-02-25 16:16:56 +00:00 committed by Lunny Xiao
parent 0c9a2f6a70
commit 63222312b2
16 changed files with 242 additions and 43 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

43
convert/time.go
View File

@ -28,14 +28,19 @@ func String2Time(s string, originalLocation *time.Location, convertedLocation *t
dt = dt.In(convertedLocation)
return &dt, nil
} else if len(s) == 20 && s[10] == 'T' && s[19] == 'Z' {
if strings.HasPrefix(s, "0000-00-00T00:00:00") || strings.HasPrefix(s, "0001-01-01T00:00:00") {
return &time.Time{}, nil
}
dt, err := time.ParseInLocation("2006-01-02T15:04:05", s[:19], originalLocation)
if err != nil {
return nil, err
}
dt = dt.In(convertedLocation)
dt.IsZero()
return &dt, nil
} else if len(s) == 25 && s[10] == 'T' && s[19] == '+' && s[22] == ':' {
if strings.HasPrefix(s, "0000-00-00T00:00:00") || strings.HasPrefix(s, "0001-01-01T00:00:00") {
return &time.Time{}, nil
}
dt, err := time.Parse(time.RFC3339, s)
if err != nil {
return nil, err
@ -43,6 +48,10 @@ func String2Time(s string, originalLocation *time.Location, convertedLocation *t
dt = dt.In(convertedLocation)
return &dt, nil
} else if len(s) >= 21 && s[10] == 'T' && s[19] == '.' {
if strings.HasPrefix(s, "0000-00-00T00:00:00."+strings.Repeat("0", len(s)-20)) ||
strings.HasPrefix(s, "0001-01-01T00:00:00."+strings.Repeat("0", len(s)-20)) {
return &time.Time{}, nil
}
dt, err := time.Parse(time.RFC3339Nano, s)
if err != nil {
return nil, err
@ -50,6 +59,10 @@ func String2Time(s string, originalLocation *time.Location, convertedLocation *t
dt = dt.In(convertedLocation)
return &dt, nil
} else if len(s) >= 21 && s[19] == '.' {
if strings.HasPrefix(s, "0000-00-00T00:00:00."+strings.Repeat("0", len(s)-20)) ||
strings.HasPrefix(s, "0001-01-01T00:00:00."+strings.Repeat("0", len(s)-20)) {
return &time.Time{}, nil
}
layout := "2006-01-02 15:04:05." + strings.Repeat("0", len(s)-20)
dt, err := time.ParseInLocation(layout, s, originalLocation)
if err != nil {
@ -68,20 +81,20 @@ func String2Time(s string, originalLocation *time.Location, convertedLocation *t
dt = dt.In(convertedLocation)
return &dt, nil
} else if len(s) == 8 && s[2] == ':' && s[5] == ':' {
currentDate := time.Now()
dt, err := time.ParseInLocation("15:04:05", s, originalLocation)
if err != nil {
return nil, err
}
// add current date for correct time locations
dt = dt.AddDate(currentDate.Year(), int(currentDate.Month()), currentDate.Day())
dt = dt.In(convertedLocation)
dt = dt.AddDate(2006, 01, 02).In(convertedLocation)
// back to zero year
dt = dt.AddDate(-currentDate.Year(), int(-currentDate.Month()), -currentDate.Day())
dt = dt.AddDate(-2006, -01, -02)
return &dt, nil
} else {
i, err := strconv.ParseInt(s, 10, 64)
if err == nil {
if i == 0 {
return &time.Time{}, nil
}
tm := time.Unix(i, 0).In(convertedLocation)
return &tm, nil
}
@ -108,6 +121,9 @@ func AsTime(src interface{}, dbLoc *time.Location, uiLoc *time.Location) (*time.
if !t.Valid {
return nil, nil
}
if utils.IsTimeZero(t.Time) {
return &time.Time{}, nil
}
z, _ := t.Time.Zone()
if len(z) == 0 || t.Time.Year() == 0 || t.Time.Location().String() != dbLoc.String() {
tm := time.Date(t.Time.Year(), t.Time.Month(), t.Time.Day(), t.Time.Hour(),
@ -117,6 +133,9 @@ func AsTime(src interface{}, dbLoc *time.Location, uiLoc *time.Location) (*time.
tm := t.Time.In(uiLoc)
return &tm, nil
case *time.Time:
if utils.IsTimeZero(*t) {
return &time.Time{}, nil
}
z, _ := t.Zone()
if len(z) == 0 || t.Year() == 0 || t.Location().String() != dbLoc.String() {
tm := time.Date(t.Year(), t.Month(), t.Day(), t.Hour(),
@ -126,6 +145,9 @@ func AsTime(src interface{}, dbLoc *time.Location, uiLoc *time.Location) (*time.
tm := t.In(uiLoc)
return &tm, nil
case time.Time:
if utils.IsTimeZero(t) {
return &time.Time{}, nil
}
z, _ := t.Zone()
if len(z) == 0 || t.Year() == 0 || t.Location().String() != dbLoc.String() {
tm := time.Date(t.Year(), t.Month(), t.Day(), t.Hour(),
@ -135,12 +157,21 @@ func AsTime(src interface{}, dbLoc *time.Location, uiLoc *time.Location) (*time.
tm := t.In(uiLoc)
return &tm, nil
case int:
if t == 0 {
return &time.Time{}, nil
}
tm := time.Unix(int64(t), 0).In(uiLoc)
return &tm, nil
case int64:
if t == 0 {
return &time.Time{}, nil
}
tm := time.Unix(t, 0).In(uiLoc)
return &tm, nil
case *sql.NullInt64:
if t.Int64 == 0 {
return &time.Time{}, nil
}
tm := time.Unix(t.Int64, 0).In(uiLoc)
return &tm, nil
}

4
dialects/dameng.go
View File

@ -618,8 +618,8 @@ func (db *dameng) SQLType(c *schemas.Column) string {
res = t
}
hasLen1 := (c.Length > 0)
hasLen2 := (c.Length2 > 0)
hasLen1 := c.Length > 0
hasLen2 := c.Length2 > 0
if hasLen2 {
res += "(" + strconv.FormatInt(c.Length, 10) + "," + strconv.FormatInt(c.Length2, 10) + ")"

12
dialects/mssql.go
View File

@ -330,15 +330,11 @@ func (db *mssql) SQLType(c *schemas.Column) string {
res += "(MAX)"
}
case schemas.TimeStamp, schemas.DateTime:
if c.Length > 3 {
res = "DATETIME2"
} else {
return schemas.DateTime
}
return "DATETIME2"
case schemas.TimeStampz:
res = "DATETIMEOFFSET"
c.Length = 7
case schemas.MediumInt, schemas.TinyInt, schemas.SmallInt, schemas.UnsignedMediumInt, schemas.UnsignedTinyInt, schemas.UnsignedSmallInt:
case schemas.MediumInt, schemas.SmallInt, schemas.UnsignedMediumInt, schemas.UnsignedTinyInt, schemas.UnsignedSmallInt:
res = schemas.Int
case schemas.Text, schemas.MediumText, schemas.TinyText, schemas.LongText, schemas.Json:
res = db.defaultVarchar + "(MAX)"
@ -381,8 +377,8 @@ func (db *mssql) SQLType(c *schemas.Column) string {
return res
}
hasLen1 := (c.Length > 0)
hasLen2 := (c.Length2 > 0)
hasLen1 := c.Length > 0
hasLen2 := c.Length2 > 0
if hasLen2 {
res += "(" + strconv.FormatInt(c.Length, 10) + "," + strconv.FormatInt(c.Length2, 10) + ")"

4
dialects/mysql.go
View File

@ -326,8 +326,8 @@ func (db *mysql) SQLType(c *schemas.Column) string {
res = t
}
hasLen1 := (c.Length > 0)
hasLen2 := (c.Length2 > 0)
hasLen1 := c.Length > 0
hasLen2 := c.Length2 > 0
if res == schemas.BigInt && !hasLen1 && !hasLen2 {
c.Length = 20

4
dialects/oracle.go
View File

@ -585,8 +585,8 @@ func (db *oracle) SQLType(c *schemas.Column) string {
res = t
}
hasLen1 := (c.Length > 0)
hasLen2 := (c.Length2 > 0)
hasLen1 := c.Length > 0
hasLen2 := c.Length2 > 0
if hasLen2 {
res += "(" + strconv.FormatInt(c.Length, 10) + "," + strconv.FormatInt(c.Length2, 10) + ")"

6
dialects/postgres.go
View File

@ -957,8 +957,8 @@ func (db *postgres) SQLType(c *schemas.Column) string {
// for bool, we don't need length information
return res
}
hasLen1 := (c.Length > 0)
hasLen2 := (c.Length2 > 0)
hasLen1 := c.Length > 0
hasLen2 := c.Length2 > 0
if hasLen2 {
res += "(" + strconv.FormatInt(c.Length, 10) + "," + strconv.FormatInt(c.Length2, 10) + ")"
@ -1185,7 +1185,7 @@ WHERE n.nspname= s.table_schema AND c.relkind = 'r' AND c.relname = $1%s AND f.a
col.IsPrimaryKey = true
}
col.Nullable = (isNullable == "YES")
col.Nullable = isNullable == "YES"
switch strings.ToLower(dataType) {
case "character varying", "string":

7
dialects/time.go
View File

@ -7,20 +7,23 @@ package dialects
import (
"strings"
"time"
"xorm.io/xorm/internal/utils"
"xorm.io/xorm/schemas"
)
// FormatColumnTime format column time
func FormatColumnTime(dialect Dialect, dbLocation *time.Location, col *schemas.Column, t time.Time) (interface{}, error) {
if t.IsZero() {
if utils.IsTimeZero(t) {
if col.Nullable {
return nil, nil
}
if col.SQLType.IsNumeric() {
return 0, nil
}
if col.SQLType.Name == schemas.TimeStamp || col.SQLType.Name == schemas.TimeStampz {
t = time.Unix(0, 0)
}
}
tmZone := dbLocation

2
engine.go
View File

@ -1212,7 +1212,7 @@ func (engine *Engine) Insert(beans ...interface{}) (int64, error) {
func (engine *Engine) InsertOne(bean interface{}) (int64, error) {
session := engine.NewSession()
defer session.Close()
return session.InsertOne(bean)
return session.Insert(bean)
}
// Update records, bean's non-empty fields are updated contents,

2
internal/statements/order_by.go
View File

@ -50,7 +50,7 @@ var ErrNoColumnName = errors.New("no column name")
func (statement *Statement) writeOrderBy(w *builder.BytesWriter, orderBy orderBy) error {
switch t := orderBy.orderStr.(type) {
case (*builder.Expression):
case *builder.Expression:
if _, err := fmt.Fprint(w.Builder, statement.dialect.Quoter().Replace(t.Content())); err != nil {
return err
}

15
internal/statements/statement.go
View File

@ -170,7 +170,7 @@ func (statement *Statement) Reset() {
// SQL adds raw sql statement
func (statement *Statement) SQL(query interface{}, args ...interface{}) *Statement {
switch t := query.(type) {
case (*builder.Builder):
case *builder.Builder:
var err error
statement.RawSQL, statement.RawParams, err = t.ToSQL()
if err != nil {
@ -616,7 +616,7 @@ func (statement *Statement) BuildConds(table *schemas.Table, bean interface{}, i
// MergeConds merge conditions from bean and id
func (statement *Statement) MergeConds(bean interface{}) error {
if !statement.NoAutoCondition && statement.RefTable != nil {
addedTableName := (len(statement.joins) > 0)
addedTableName := len(statement.joins) > 0
autoCond, err := statement.BuildConds(statement.RefTable, bean, true, true, false, true, addedTableName)
if err != nil {
return err
@ -713,11 +713,14 @@ func (statement *Statement) CondDeleted(col *schemas.Column) builder.Cond {
cond := builder.NewCond()
if col.SQLType.IsNumeric() {
cond = builder.Eq{colName: 0}
} else {
// FIXME: mssql: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
if statement.dialect.URI().DBType != schemas.MSSQL {
cond = builder.Eq{colName: utils.ZeroTime1}
} else if col.SQLType.Name == schemas.TimeStamp || col.SQLType.Name == schemas.TimeStampz {
tmZone := statement.defaultTimeZone
if col.TimeZone != nil {
tmZone = col.TimeZone
}
cond = builder.Eq{colName: time.Unix(0, 0).In(tmZone).Format("2006-01-02 15:04:05.999999999")}
} else {
cond = builder.Eq{colName: utils.ZeroTime1}
}
if col.Nullable {

4
internal/utils/zero.go
View File

@ -146,6 +146,6 @@ const (
// IsTimeZero return true if a time is zero
func IsTimeZero(t time.Time) bool {
return t.IsZero() || t.Format("2006-01-02 15:04:05") == ZeroTime0 ||
t.Format("2006-01-02 15:04:05") == ZeroTime1
return t.IsZero() || t.Format("2006-01-02 15:04:05.999999999") == ZeroTime0 ||
t.Format("2006-01-02 15:04:05.999999999") == ZeroTime1
}

2
names/mapper.go
View File

@ -149,7 +149,7 @@ func isASCIIUpper(r rune) bool {
func toASCIIUpper(r rune) rune {
if 'a' <= r && r <= 'z' {
r -= ('a' - 'A')
r -= 'a' - 'A'
}
return r
}

3
session_insert.go
View File

@ -471,7 +471,8 @@ func (session *Session) genInsertColumns(bean interface{}) ([]string, []interfac
}
if col.IsDeleted {
arg, err := dialects.FormatColumnTime(session.engine.dialect, session.engine.DatabaseTZ, col, time.Time{})
zeroTime := time.Date(1, 1, 1, 0, 0, 0, 0, session.engine.DatabaseTZ)
arg, err := dialects.FormatColumnTime(session.engine.dialect, session.engine.DatabaseTZ, col, zeroTime)
if err != nil {
return nil, nil, err
}

2
tags/tag.go
View File

@ -163,7 +163,7 @@ func PKTagHandler(ctx *Context) error {
// NULLTagHandler describes null tag handler
func NULLTagHandler(ctx *Context) error {
ctx.col.Nullable = (strings.ToUpper(ctx.preTag) != "NOT")
ctx.col.Nullable = strings.ToUpper(ctx.preTag) != "NOT"
return nil
}

167
tests/session_insert_test.go
View File

@ -943,7 +943,7 @@ func TestMultipleInsertTableName(t *testing.T) {
assert.NoError(t, testEngine.Table(tableName).Sync(new(NightlyRate)))
trans := testEngine.NewSession()
defer trans.Close()
defer func(trans *xorm.Session) { _ = trans.Close() }(trans)
err := trans.Begin()
assert.NoError(t, err)
@ -1031,7 +1031,7 @@ func TestInsertTwice(t *testing.T) {
}
ssn := testEngine.NewSession()
defer ssn.Close()
defer func(ssn *xorm.Session) { _ = ssn.Close() }(ssn)
err := ssn.Begin()
assert.NoError(t, err)
@ -1209,3 +1209,166 @@ func TestInsertMultipleMap(t *testing.T) {
Name: "xiaolunwen",
}, res[1])
}
func TestInsertNotDeleted(t *testing.T) {
assert.NoError(t, PrepareEngine())
zeroTime := time.Date(1, 1, 1, 0, 0, 0, 0, testEngine.GetTZDatabase())
type TestInsertNotDeletedStructNotRight struct {
ID uint64 `xorm:"'ID' pk autoincr"`
DeletedAt time.Time `xorm:"'DELETED_AT' deleted notnull"`
}
// notnull tag will be ignored
err := testEngine.Sync(new(TestInsertNotDeletedStructNotRight))
assert.NoError(t, err)
type TestInsertNotDeletedStruct struct {
ID uint64 `xorm:"'ID' pk autoincr"`
DeletedAt time.Time `xorm:"'DELETED_AT' deleted"`
}
err = testEngine.Sync(new(TestInsertNotDeletedStruct))
assert.NoError(t, err)
var v1 TestInsertNotDeletedStructNotRight
_, err = testEngine.Insert(&v1)
assert.NoError(t, err)
var v2 TestInsertNotDeletedStructNotRight
has, err := testEngine.Get(&v2)
assert.NoError(t, err)
assert.True(t, has)
assert.Equal(t, v2.DeletedAt.In(testEngine.GetTZDatabase()).Format("2006-01-02 15:04:05"), zeroTime.Format("2006-01-02 15:04:05"))
var v3 TestInsertNotDeletedStruct
_, err = testEngine.Insert(&v3)
assert.NoError(t, err)
var v4 TestInsertNotDeletedStruct
has, err = testEngine.Get(&v4)
assert.NoError(t, err)
assert.True(t, has)
assert.Equal(t, v4.DeletedAt.In(testEngine.GetTZDatabase()).Format("2006-01-02 15:04:05"), zeroTime.Format("2006-01-02 15:04:05"))
}
func TestInsertNotDeletedNum(t *testing.T) {
assert.NoError(t, PrepareEngine())
type TestInsertNotDeletedNumStructNotRight struct {
ID uint64 `xorm:"'ID' pk autoincr"`
DeletedAt int64 `xorm:"'DELETED_AT' deleted notnull INT(11)"`
}
// notnull tag will be ignored
err := testEngine.Sync(new(TestInsertNotDeletedNumStructNotRight))
assert.NoError(t, err)
type TestInsertNotDeletedNumStruct struct {
ID uint64 `xorm:"'ID' pk autoincr"`
DeletedAt int64 `xorm:"'DELETED_AT' deleted INT(11)"`
}
err = testEngine.Sync(new(TestInsertNotDeletedNumStruct))
assert.NoError(t, err)
var v1 TestInsertNotDeletedNumStructNotRight
_, err = testEngine.Insert(&v1)
assert.NoError(t, err)
var v2 TestInsertNotDeletedNumStructNotRight
has, err := testEngine.Get(&v2)
assert.NoError(t, err)
assert.True(t, has)
assert.Equal(t, v2.DeletedAt, int64(0))
var v3 TestInsertNotDeletedNumStruct
_, err = testEngine.Insert(&v3)
assert.NoError(t, err)
var v4 TestInsertNotDeletedNumStruct
has, err = testEngine.Get(&v4)
assert.NoError(t, err)
assert.True(t, has)
assert.Equal(t, v4.DeletedAt, int64(0))
}
func TestInsertNotDeletedTimeStamp(t *testing.T) {
assert.NoError(t, PrepareEngine())
// IN MYSQL DB
// The time range that timestamps can store is from '1970 01 01 00:00:01.000000' to '2038 01 19 03:14:07.999999'
// PASS notnull timestamp IN MYSQL DB
if testEngine.Dialect().URI().DBType == schemas.MSSQL ||
testEngine.Dialect().URI().DBType == schemas.SQLITE ||
testEngine.Dialect().URI().DBType == schemas.POSTGRES {
type TestInsertNotDeletedTimeStampStructNotRight struct {
ID uint64 `xorm:"'ID' pk autoincr"`
DeletedAt time.Time `xorm:"'DELETED_AT' deleted notnull TIMESTAMP"`
}
err := testEngine.Sync(new(TestInsertNotDeletedTimeStampStructNotRight))
assert.NoError(t, err)
var v1 TestInsertNotDeletedTimeStampStructNotRight
_, err = testEngine.Insert(&v1)
assert.NoError(t, err)
var v2 TestInsertNotDeletedTimeStampStructNotRight
has, err := testEngine.Get(&v2)
assert.NoError(t, err)
assert.True(t, has)
assert.Equal(t, v2.DeletedAt, time.Unix(0, 0))
}
type TestInsertNotDeletedTimeStampStruct struct {
ID uint64 `xorm:"'ID' pk autoincr"`
DeletedAt time.Time `xorm:"'DELETED_AT' deleted TIMESTAMP"`
}
err := testEngine.Sync(new(TestInsertNotDeletedTimeStampStruct))
assert.NoError(t, err)
var v3 TestInsertNotDeletedTimeStampStruct
_, err = testEngine.Insert(&v3)
assert.NoError(t, err)
var v4 TestInsertNotDeletedTimeStampStruct
has, err := testEngine.Get(&v4)
assert.NoError(t, err)
assert.True(t, has)
assert.Equal(t, v4.DeletedAt, time.Time{})
}
type MyAutoTimeFields1 struct {
Id int64
Dt time.Time `xorm:"created DATETIME"`
}
func (MyAutoTimeFields1) TableName() string {
return "my_auto_time_fields"
}
type MyAutoTimeFields2 struct {
Id int64
Dt time.Time `xorm:"created"`
}
func (MyAutoTimeFields2) TableName() string {
return "my_auto_time_fields"
}
func TestAutoTimeFields(t *testing.T) {
assert.NoError(t, PrepareEngine())
assertSync(t, new(MyAutoTimeFields1))
_, err := testEngine.Insert(&MyAutoTimeFields1{})
assert.NoError(t, err)
var res []MyAutoTimeFields2
assert.NoError(t, testEngine.Find(&res))
assert.EqualValues(t, 1, len(res))
_, err = testEngine.Insert(&MyAutoTimeFields2{})
assert.NoError(t, err)
res = []MyAutoTimeFields2{}
assert.NoError(t, testEngine.Find(&res))
assert.EqualValues(t, 2, len(res))
}

8
tests/tags_test.go
View File

@ -7,11 +7,11 @@ package tests
import (
"fmt"
"sort"
"strings"
"testing"
"time"
"github.com/stretchr/testify/assert"
"xorm.io/xorm/convert"
"xorm.io/xorm/internal/utils"
"xorm.io/xorm/names"
"xorm.io/xorm/schemas"
@ -1201,8 +1201,10 @@ func TestTagTime(t *testing.T) {
has, err = testEngine.Table("tag_u_t_c_struct").Cols("created").Get(&tm)
assert.NoError(t, err)
assert.True(t, has)
assert.EqualValues(t, s.Created.UTC().Format("2006-01-02 15:04:05"),
strings.ReplaceAll(strings.ReplaceAll(tm, "T", " "), "Z", ""))
tmTime, err := convert.String2Time(tm, time.UTC, time.UTC)
assert.NoError(t, err)
assert.EqualValues(t, s.Created.UTC().Format("2006-01-02 15:04:05"), tmTime.Format("2006-01-02 15:04:05"))
}
func TestTagAutoIncr(t *testing.T) {