読者です 読者をやめる 読者になる 読者になる

sqlite3でカラム定義の変更

sqlite3 rails

sqlite3だとalter table change columnみたいのがないらしいのでnot nullとかdefault valueを変更するのどうすればいいんだろうと思ってrailsがどうしてるか見てみた。

class CreateTodos < ActiveRecord::Migration[5.0]
  def change
    create_table :todos do |t|
      t.string :text
      t.boolean :completed

      t.timestamps
    end
  end
end
class UpdateTodos < ActiveRecord::Migration[5.0]
  def change
    change_column :todos, :text, :string, null: false
  end
end

こんな感じのmigrationを実行してみるとログはこうなった。

   (0.1ms)  begin transaction
   (0.2ms)  CREATE TEMPORARY TABLE "atodos" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "text" varchar NOT NULL, "completed" boolean, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL)
   (0.4ms)  INSERT INTO "atodos" ("id","text","completed","created_at","updated_at")
                     SELECT "id","text","completed","created_at","updated_at" FROM "todos"
   (0.6ms)  DROP TABLE "todos"
   (0.1ms)  CREATE TABLE "todos" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "text" varchar NOT NULL, "completed" boolean, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL)
   (0.1ms)  INSERT INTO "todos" ("id","text","completed","created_at","updated_at")
                     SELECT "id","text","completed","created_at","updated_at" FROM "atodos"
   (0.1ms)  DROP TABLE "atodos"
  SQL (0.1ms)  INSERT INTO "schema_migrations" ("version") VALUES (?)  [["version", "20161013104634"]]
   (0.7ms)  commit transaction

一時テーブル作ってinsert selectでデータ退避させて作り直してるらしい。なるほど・・。