如何在 Rails 中的迁移中编写 SQL

How to write SQL in a migration in Rails

提问by Nick Ginanto

I have the following SQL which I need to do

我有以下需要执行的 SQL

CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;

DROP TABLE cars_users;

ALTER TABLE cars_users2 RENAME TO cars_users;

since I cannot use heroku dataclips to drop a table, I cannot use dataclips.

因为我不能使用 heroku dataclips 来删除表，所以我不能使用 dataclips。

So I guess I need to do this in a migration.

所以我想我需要在迁移中做到这一点。

How do I write this sql as a migration?

我如何将这个 sql 编写为迁移？

回答by Tomdarkness

For your up migration:

对于您的向上迁移：

execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;" 
drop_table :car_users  
rename_table :car_users2, :car_users  

and for down:

和下来：

raise ActiveRecord::IrreversibleMigration

Full migration:

完全迁移：

class TheMigration < ActiveRecord::Migration
    def up
        execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * from cars_users;" 
        drop_table :car_users  
        rename_table :car_users2, :car_users  
    end

    def down
        raise ActiveRecord::IrreversibleMigration
    end
end

回答by ConcurrentHashMap

You could try to use the executemethod.

您可以尝试使用该execute方法。

Something like this (it's untested, some kind of brainchild)

像这样的东西（未经测试，某种创意）

class UpdateCarUserTable < ActiveRecord::Migration
  def up
    execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users"
    execute "DROP TABLE cars_users"
    execute "ALTER TABLE cars_users2 RENAME TO cars_users"
  end

As there is no equivalent downmethod, an ActiveRecord::IrreversibleMigrationshould be raised when trying to migrate down.

由于没有等效的down方法，ActiveRecord::IrreversibleMigration因此在尝试向下迁移时应引发。

回答by fangxing

I prefer here doc:

我更喜欢这里的文档：

execute <<-SQL
  CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;
  DROP TABLE cars_users;
  ALTER TABLE cars_users2 RENAME TO cars_users;
SQL

notice:This only works for PostgreSQL, if you are using MySQL you should set CLIENT_MULTI_STATEMENTSfor the adapter.

注意：这仅适用于 PostgreSQL，如果您使用的是 MySQL，则应为适配器设置CLIENT_MULTI_STATEMENTS。

回答by Jo?o Paulo Motta

In case you need to use changeinstead of upand downyou can use reversible. It works on Rails 4 or above.

如果你需要使用change，而不是up和down你可以使用reversible。它适用于 Rails 4 或更高版本。

class ExampleMigration < ActiveRecord::Migration
  def change
    create_table :distributors do |t|
      t.string :zipcode
    end

    reversible do |dir|
      dir.up do
        # add a CHECK constraint
        execute <<-SQL
          ALTER TABLE distributors
            ADD CONSTRAINT zipchk
              CHECK (char_length(zipcode) = 5) NO INHERIT;
        SQL
      end
      dir.down do
        execute <<-SQL
          ALTER TABLE distributors
            DROP CONSTRAINT zipchk
        SQL
      end
    end

    add_column :users, :home_page_url, :string
    rename_column :users, :email, :email_address
  end
end

Sources: http://edgeguides.rubyonrails.org/active_record_migrations.html#using-reversible

来源：http: //edgeguides.rubyonrails.org/active_record_migrations.html#using-reversible

https://apidock.com/rails/ActiveRecord/Migration/reversible

https://apidock.com/rails/ActiveRecord/Migration/reversible