sqlserver icon indicating copy to clipboard operation
sqlserver copied to clipboard

Published 20 hours ago • verified publisher icon go-gorm

fix: migrator force modification of fields with no default value

Open iTanken opened this issue 9 months ago • 1 comments

  • [x] Do only one thing
  • [x] Non breaking API changes
  • [x] Tested

What did this pull request do?

column.DefaultValueValue.Valid = true causes almost all fields that do not have default values ​​when automatic migration, and this PR will repair this problem.

https://github.com/go-gorm/sqlserver/commit/16b5ee33eede9947c52f7a65c5f156be745cd0d8#r141507691

User Case Description

db.AutoMigrate(&User{})

https://github.com/go-gorm/playground/pull/727

9 times ALTER TABLE "users" ALTER COLUMN ... appeared when re-migrating the model without modifications.

TestReMigrate 
=== RUN   TestReMigrate

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:25
[0.411ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:25
[0.457ms] [rows:-] SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users' AND TABLE_CATALOG = 'gorm' and TABLE_SCHEMA like '%'  AND TABLE_TYPE = 'BASE TABLE'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.389ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.493ms] [rows:-] SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'companies' AND TABLE_CATALOG = 'gorm' and TABLE_SCHEMA like '%'  AND TABLE_TYPE = 'BASE TABLE'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.946ms] [rows:-] SELECT * FROM "companies" ORDER BY (SELECT NULL) OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.405ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[24.518ms] [rows:-] SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'gorm' AND TABLE_NAME = 'companies'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.521ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[112.[33](https://github.com/go-gorm/playground/actions/runs/8892161010/job/24415649192?pr=727#step:6:34)3ms] [rows:-] SELECT c.COLUMN_NAME, t.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c ON c.CONSTRAINT_NAME=t.CONSTRAINT_NAME WHERE t.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND c.TABLE_CATALOG = 'gorm' AND c.TABLE_NAME = 'companies'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[1.855ms] [rows:0] ALTER TABLE "companies" ALTER COLUMN "name" nvarchar(MAX) NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.460ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.550ms] [rows:-] SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users' AND TABLE_CATALOG = 'gorm' and TABLE_SCHEMA like '%'  AND TABLE_TYPE = 'BASE TABLE'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[1.012ms] [rows:-] SELECT * FROM "users" ORDER BY (SELECT NULL) OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.402ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[11.680ms] [rows:-] SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'gorm' AND TABLE_NAME = 'users'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.382ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[85.960ms] [rows:-] SELECT c.COLUMN_NAME, t.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c ON c.CONSTRAINT_NAME=t.CONSTRAINT_NAME WHERE t.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND c.TABLE_CATALOG = 'gorm' AND c.TABLE_NAME = 'users'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[1.5[35](https://github.com/go-gorm/playground/actions/runs/8892161010/job/24415649192?pr=727#step:6:36)ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "created_at" datetimeoffset NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[1.012ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "updated_at" datetimeoffset NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[1.290ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "deleted_at" datetimeoffset NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.910ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "name" nvarchar(MAX) NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.938ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "age" bigint NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.893ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "birthday" datetimeoffset NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[1.153ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "company_id" bigint NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.889ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "manager_id" bigint NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.907ms] [rows:0] ALTER TABLE "users" ALTER COLUMN "active" bit NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.450ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[26.299ms] [rows:-] SELECT count(*) FROM sys.foreign_keys as F inner join sys.tables as T on F.parent_object_id=T.object_id inner join INFORMATION_SCHEMA.TABLES as I on I.TABLE_NAME = T.name WHERE F.name = 'fk_users_company'  AND I.TABLE_NAME = 'users' AND I.TABLE_SCHEMA like '%' AND I.TABLE_CATALOG = 'gorm';

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.411ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[26.268ms] [rows:-] SELECT count(*) FROM sys.foreign_keys as F inner join sys.tables as T on F.parent_object_id=T.object_id inner join INFORMATION_SCHEMA.TABLES as I on I.TABLE_NAME = T.name WHERE F.name = 'fk_users_team'  AND I.TABLE_NAME = 'users' AND I.TABLE_SCHEMA like '%' AND I.TABLE_CATALOG = 'gorm';

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[4.240ms] [rows:-] SELECT count(*) FROM sys.indexes WHERE name='idx_users_deleted_at' AND object_id=OBJECT_ID('users')

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.422ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.511ms] [rows:-] SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user_friends' AND TABLE_CATALOG = 'gorm' and TABLE_SCHEMA like '%'  AND TABLE_TYPE = 'BASE TABLE'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.972ms] [rows:-] SELECT * FROM "user_friends" ORDER BY (SELECT NULL) OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.427ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[10.961ms] [rows:-] SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'gorm' AND TABLE_NAME = 'user_friends'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.479ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[84.046ms] [rows:-] SELECT c.COLUMN_NAME, t.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c ON c.CONSTRAINT_NAME=t.CONSTRAINT_NAME WHERE t.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND c.TABLE_CATALOG = 'gorm' AND c.TABLE_NAME = 'user_friends'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.488ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[26.240ms] [rows:-] SELECT count(*) FROM sys.foreign_keys as F inner join sys.tables as T on F.parent_object_id=T.object_id inner join INFORMATION_SCHEMA.TABLES as I on I.TABLE_NAME = T.name WHERE F.name = 'fk_user_friends_user'  AND I.TABLE_NAME = 'user_friends' AND I.TABLE_SCHEMA like '%' AND I.TABLE_CATALOG = 'gorm';

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.460ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[25.704ms] [rows:-] SELECT count(*) FROM sys.foreign_keys as F inner join sys.tables as T on F.parent_object_id=T.object_id inner join INFORMATION_SCHEMA.TABLES as I on I.TABLE_NAME = T.name WHERE F.name = 'fk_user_friends_friends'  AND I.TABLE_NAME = 'user_friends' AND I.TABLE_SCHEMA like '%' AND I.TABLE_CATALOG = 'gorm';

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.467ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.5[36](https://github.com/go-gorm/playground/actions/runs/8892161010/job/24415649192?pr=727#step:6:37)ms] [rows:-] SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'languages' AND TABLE_CATALOG = 'gorm' and TABLE_SCHEMA like '%'  AND TABLE_TYPE = 'BASE TABLE'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.910ms] [rows:-] SELECT * FROM "languages" ORDER BY (SELECT NULL) OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.418ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.764ms] [rows:-] SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'gorm' AND TABLE_NAME = 'languages'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.413ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.770ms] [rows:-] SELECT c.COLUMN_NAME, t.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c ON c.CONSTRAINT_NAME=t.CONSTRAINT_NAME WHERE t.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND c.TABLE_CATALOG = 'gorm' AND c.TABLE_NAME = 'languages'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[1.483ms] [rows:0] ALTER TABLE "languages" ALTER COLUMN "name" nvarchar(MAX) NULL

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.405ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.495ms] [rows:-] SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user_speaks' AND TABLE_CATALOG = 'gorm' and TABLE_SCHEMA like '%'  AND TABLE_TYPE = 'BASE TABLE'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.883ms] [rows:-] SELECT * FROM "user_speaks" ORDER BY (SELECT NULL) OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.390ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[10.930ms] [rows:-] SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'gorm' AND TABLE_NAME = 'user_speaks'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.453ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[83.401ms] [rows:-] SELECT c.COLUMN_NAME, t.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c ON c.CONSTRAINT_NAME=t.CONSTRAINT_NAME WHERE t.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND c.TABLE_CATALOG = 'gorm' AND c.TABLE_NAME = 'user_speaks'

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.520ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[26.027ms] [rows:-] SELECT count(*) FROM sys.foreign_keys as F inner join sys.tables as T on F.parent_object_id=T.object_id inner join INFORMATION_SCHEMA.TABLES as I on I.TABLE_NAME = T.name WHERE F.name = 'fk_user_speaks_user'  AND I.TABLE_NAME = 'user_speaks' AND I.TABLE_SCHEMA like '%' AND I.TABLE_CATALOG = 'gorm';

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[0.455ms] [rows:-] SELECT DB_NAME() AS [Current Database]

2024/04/30 09:05:59 /home/runner/work/playground/playground/main_test.go:30
[26.[38](https://github.com/go-gorm/playground/actions/runs/8892161010/job/24415649192?pr=727#step:6:39)4ms] [rows:-] SELECT count(*) FROM sys.foreign_keys as F inner join sys.tables as T on F.parent_object_id=T.object_id inner join INFORMATION_SCHEMA.TABLES as I on I.TABLE_NAME = T.name WHERE F.name = 'fk_user_speaks_language'  AND I.TABLE_NAME = 'user_speaks' AND I.TABLE_SCHEMA like '%' AND I.TABLE_CATALOG = 'gorm';
--- PASS: TestReMigrate (0.63s)

iTanken avatar Apr 30 '24 08:04 iTanken

@jinzhu If you have time, please review this PR. Feel free to close it if there are any issues.

iTanken avatar Apr 30 '24 08:04 iTanken

Could you write some test cases? If we remove the Valid = true, it means we haven't found a valid default value from the database schema information.

jinzhu avatar Jun 12 '24 07:06 jinzhu

Could you write some test cases? If we remove the Valid = true, it means we haven't found a valid default value from the database schema information.

Yeah, my understanding is that when Valid = false, it means that this field doesn't have a default value set in the database, so we shouldn't forcefully set Valid = true.

I have added tests. Please review the code again when you have time.

iTanken avatar Jun 13 '24 03:06 iTanken