Rails迁移和非标准数据类型？

Initial problem

In my Rails application I need to work with data from a legacy database, with large numbers as primary keys. Numbers that exceed the limits of both MySQL's and PostgreSQL's 4-byte INT or INTEGER data type. But that's exactly the data type that Rails' adapters translate "integer" into.

在我的Rails应用程序中，我需要处理来自旧数据库的数据，其中大数字作为主键。超过MySQL和PostgreSQL的4字节INT或INTEGER数据类型限制的数字。但这正是Rails的适配器将“整数”转换为的数据类型。

My workaround

That was not an issue in development, since SQLite has only one integer type accepting large numbers. But for staging/production (i.e. for use with anything but SQLite), I had to manually edit the schema.rb file to replace t.integer "id" with t.column "id", 'BIGINT'. Then load the schema, import the data, and it works.

这不是开发中的问题，因为SQLite只有一个接受大数字的整数类型。但对于登台/制作（即除了SQLite以外的任何东西），我不得不手动编辑schema.rb文件，用t.column“id”，“BIGINT”替换t.integer“id”。然后加载架构，导入数据，它的工作原理。

The problem with the workaround

Now, everytime I run a Rails migration to make a minor change to the database schema, the entire schema.rb file is automatically re-generated, and all my id columns are just t.integer again. As before, this isn't a problem in development (SQLite), but it probably will be in production, won't it? I'd hate to have to drop the database, load the schema and reimport all data every time I need to add or rename a column – I'd much rather use migrations for this.

现在，每次运行Rails迁移以对数据库模式进行微小更改时，都会自动重新生成整个schema.rb文件，并且我的所有id列都只是t.integer。和以前一样，这不是开发中的问题（SQLite），但它可能会在生产中，不是吗？我不想每次需要添加或重命名列时都必须删除数据库，加载模式并重新导入所有数据 - 我更倾向于使用迁移。

The workaround for me thus far has been to make a backup copy of schema.rb before migrating, and then to merge the additions made by the migration with my own ones. Does anyone have a better idea?

到目前为止，我的解决方法是在迁移之前制作schema.rb的备份副本，然后将迁移所做的添加与我自己的添加合并。有没有人有更好的主意？

1 个解决方案

#1

Here is a solution to hack in bigint primary keys in mysql2 and postgresql:

这是一个解决mysql2和postgresql中的bigint主键的解决方案：

In environment.rb:

在environment.rb中：

    # Load the rails application
require File.expand_path('../application', __FILE__)

require 'active_record/connection_adapters/mysql2_adapter'
require 'active_record/connection_adapters/postgresql_adapter'

ActiveRecord::ConnectionAdapters::Mysql2Adapter::NATIVE_DATABASE_TYPES[:big_primary_key] = "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY".freeze
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:big_primary_key] = "bigserial primary key".freeze

# Initialize the rails application
YourAppName::Application.initialize!

#1