英文:
Rails adding artifacts to schema.rb after migration
问题
在Rails 7中：
迁移：
class AddSavedSearchAPIKeyIndex < ActiveRecord::Migration[7.0]
def change
execute <<~SQL
ALTER TABLE saved_searches
ADD INDEX ecomm_analyze_api_key ((CAST(configuration->"$.ecomm_analyze_api_key" as CHAR(255)) COLLATE utf8mb4_bin)) USING BTREE;
SQL
end
end
我的模式最终看起来像这样：
t.index "(cast(json_unquote(json_extract(`configuration`,_utf8mb4\\'$.ecomm_analyze_api_key\\')) as char(255) charset utf8mb4) collate utf8mb4_bin)", name: "ecomm_analyze_api_key"
当我使用以下命令时，我无法加载这个schema.rb，会出现错误：
RAILS_ENV=test rake db:test:prepare
错误信息：
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
我可以通过手动编辑schema.rb来解决这个问题，但这将在下一个迁移时再次出现问题。
英文:
In Rails 7:
Migration:
class AddSavedSearchAPIKeyIndex < ActiveRecord::Migration[7.0]
def change
execute <<~SQL
ALTER TABLE saved_searches
ADD INDEX ecomm_analyze_api_key ((
CAST(configuration->>"$.ecomm_analyze_api_key" as CHAR(255)) COLLATE utf8mb4_bin
)) USING BTREE;
SQL
end
end
My schema ends up looking like this:
t.index "(cast(json_unquote(json_extract(`configuration`,_utf8mb4\\'$.ecomm_analyze_api_key\\')) as char(255) charset utf8mb4) collate utf8mb4_bin)", name: "ecomm_analyze_api_key"
I can't load this schema.rb without getting an error when I use:
RAILS_ENV=test rake db:test:prepare
Error message
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
I am able to fix this by manually editing the schema.rb but this will just break again on the next migration.
答案1
得分: 2
Ruby模式转储器只能理解迁移DSL可以创建的SQL的有限子集，实际上只有在您的应用程序达到需要使用特定于数据库的功能的复杂度时才有用。
Ruby模式转储器无法正确理解的任何其他内容，比如这个索引，当它尝试解析转储数据库模式的结果SQL时，要么会被破坏，要么会丢失。
解决方案是使用SQL模式转储：
module YourApp
class Application < Rails::Application
# 添加这一行：
config.active_record.schema_format = :sql
end
end
这将转储文件structure.sql，它应该作为数据库模式的真实来源。将此文件检入版本控制，并删除schema.rb以避免混淆。
参考：
英文:
The Ruby schema dumper only actually understands a limited subset of SQL that can be created by the migrations DSL and is only actually useful until your app reaches the degree of complexity where you want to use database specific features.
Anything else that the Ruby schema dumper doesn't properly understand like this index will either be mangled or just lost in translation when it tries to parse the resulting SQL from dumping the database schema.
The solution is to use SQL schema dumps instead:
module YourApp
class Application < Rails::Application
# Add this line:
config.active_record.schema_format = :sql
end
end
This will dump the file structure.sql which should serve as the source of truth for the database schema. Check this file into version control and delete schema.rb to avoid confusion.
See:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论