Fix migration compatibility to create SQLite references/belongs_to column as a integer when migration version is 6.0

[marcelolx]

Since #38717 the ReferenceDefinition wasn't being instantiated with the column type as :integer in the compatibility module when the adapter is SQLite for 6.0 migrations which make such columns be created as bigint instead of integer. Differing from the way they are created in prior versions or newer versions, which is always integer.

Fixes #43168

[pixeltrix]

Getting some failures locally after this change:

Failure:
ActiveRecord::Migration::CompatibilityTest#test_references_stays_as_integer_column_on_add_reference_6_0 [/Users/andyw/Repositories/rails/rails/activerecord/test/cases/migration/compatibility_test.rb:560]:
Expected: "integer"
  Actual: "INTEGER"

and

Failure:
ActiveRecord::Migration::CompatibilityTest#test_references_stays_as_integer_column_on_create_table_with_reference_6_0 [/Users/andyw/Repositories/rails/rails/activerecord/test/cases/migration/compatibility_test.rb:535]:
Expected: "integer"
  Actual: "INTEGER"

After a bit of digging I think it might be related to the STRICT tables option in sqlite3 3.37.0 and later - it only allows the following for types:

INT
INTEGER
REAL
TEXT
BLOB
ANY

Not sure what the correct fix is - not seeing any other failures on 3.37.1 which is what I'm running on my Mac. Maybe just a case-insensitive regex match?

[marcelolx]

@pixeltrix Oh that's intriguing, but probably yeah, a case-insensitive regex match should fix it. I'll take a look into it better tomorrow, but I think no matter which is the problem it would make sense to case insensitive match it , it needs to be integer, no matter if it is INTEGER or integer (I believe it is the same thing, or at least I would expect it to be)

[marcelolx]

I did install sqlite3 3.37.2 here and I can see the same errors, so I think yes the best option is to do a case insensitive match.

Opened a PR to fix it, let me know if that works #44135

[nasmorn]

This PR actually causes my PostgreSQL migration to run as integer instead of big int. It only happens on 6.1.5 when this was introduced. I removed it by monkey patching the class and only reversed the order of these prepend statements.

             prepend TableDefinition
             prepend SQLite3::TableDefinition

What would be the best way to do something here? Oddly enough this patch is not on 6.1-stable so idk how to proceed.

[marcelolx]

@nasmorn The PR linked to this issue here #44268 does solve your problem?

And I think the patch is in 6.1-stable, at least this commit here df47587 says that it is

[nasmorn]

@marcelolx thank you for the quick reply. Yes that actually solves it and also explains why the code is no longer on stable, since it has already been patched.

[marcelolx]

@nasmorn yes, sorry for causing this problem for you'll

[nasmorn]

No problem. I actually feel it is a case of the system working. The only weird thing is that I couldn't find anything in Google for

Rails 6.0 migrations using integer instead of bigint under 6.1

Which I hope this comment remedies

[marcelolx]

@nasmorn The problem was that with some changes for SQLite the migration when version 6.0 was changing the column type from integer to bigint (SQLite default is Integer while MySQL/Postgres is bigint), this PR here did aim to not change the type of the column when SQLite but did end affecting MySQL/Postgres while the intent was not it.

I think you haven't found anything on google because most people do upgrade to the latest version of 6.1 which did already include the fix for what I did broke on this PR

[arturopie]

@nasmorn The problem was that with some changes for SQLite the migration when version 6.0 was changing the column type from integer to bigint (SQLite default is Integer while MySQL/Postgres is bigint), this PR here did aim to not change the type of the column when SQLite but did end affecting MySQL/Postgres while the intent was not it.

I think you haven't found anything on google because most people do upgrade to the latest version of 6.1 which did already include the fix for what I did broke on this PR

We just upgraded to 6.1.5.1, the latest 6.1 version right now, and I don't think the fix you are referring to is in 6.1.5.1.

[marcelolx]

@arturopie Well, the fix f617ec1 was merged into 6-1-stable, only if that fix doesn't fix your problem.