fix: migrator force modification of fields with no default value by iTanken · Pull Request #134 · go-gorm/sqlserver

iTanken · 2024-04-30T08:14:17Z

Do only one thing
Non breaking API changes
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.

16b5ee3#r141507691

User Case Description

db.AutoMigrate(&User{})

go-gorm/playground#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 · 2024-04-30T08:18:17Z

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

jinzhu · 2024-06-12T07:45:41Z

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.

iTanken · 2024-06-13T03:35:58Z

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.

fix: migrator force modification of fields with no default value

aa48936

test: re-migrate table fields with or without default value

3d7b3e9

jinzhu merged commit 64af933 into go-gorm:master Jun 17, 2024

a631807682 mentioned this pull request Nov 8, 2024

[#6372] Fixed nullable constraint bug for columns during auto migration go-gorm/gorm#7269

Merged

3 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

fix: migrator force modification of fields with no default value#134

fix: migrator force modification of fields with no default value#134
jinzhu merged 2 commits intogo-gorm:masterfrom
iTanken:fix_migrator

iTanken commented Apr 30, 2024 •

edited

Loading

Uh oh!

iTanken commented Apr 30, 2024

Uh oh!

jinzhu commented Jun 12, 2024

Uh oh!

iTanken commented Jun 13, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

iTanken commented Apr 30, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What did this pull request do?

User Case Description

Uh oh!

iTanken commented Apr 30, 2024

Uh oh!

jinzhu commented Jun 12, 2024

Uh oh!

iTanken commented Jun 13, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

iTanken commented Apr 30, 2024 •

edited

Loading