Module: OnlineMigrations::SchemaStatements

Includes:
BackgroundMigrations::MigrationHelpers, BackgroundSchemaMigrations::MigrationHelpers, ChangeColumnTypeHelpers
Defined in:
lib/online_migrations/schema_statements.rb

Instance Method Summary collapse

Methods included from BackgroundSchemaMigrations::MigrationHelpers

#add_index_in_background, #enqueue_background_schema_migration, #ensure_background_schema_migration_succeeded, #remove_index_in_background, #validate_constraint_in_background, #validate_foreign_key_in_background

Methods included from BackgroundMigrations::MigrationHelpers

#backfill_column_for_type_change_in_background, #backfill_column_in_background, #backfill_columns_for_type_change_in_background, #backfill_columns_in_background, #copy_column_in_background, #copy_columns_in_background, #delete_associated_records_in_background, #delete_orphaned_records_in_background, #enqueue_background_data_migration, #ensure_background_data_migration_succeeded, #perform_action_on_relation_in_background, #remove_background_data_migration, #reset_counters_in_background

Methods included from ChangeColumnTypeHelpers

#backfill_column_for_type_change, #backfill_columns_for_type_change, #cleanup_column_type_change, #cleanup_columns_type_change, #finalize_column_type_change, #finalize_columns_type_change, #initialize_column_type_change, #initialize_columns_type_change, #revert_finalize_column_type_change, #revert_finalize_columns_type_change, #revert_initialize_column_type_change, #revert_initialize_columns_type_change

Instance Method Details

#add_check_constraint(table_name, expression, **options) ⇒ Object

Extends default method to be idempotent

See Also:



835
836
837
838
839
840
841
842
843
844
 
# File 'lib/online_migrations/schema_statements.rb', line 835

def add_check_constraint(table_name, expression, **options)
  if __check_constraint_exists?(table_name, expression: expression, **options)
    Utils.say(<<~MSG.squish)
      Check constraint was not created because it already exists (this may be due to an aborted migration or similar).
      table_name: #{table_name}, expression: #{expression}
    MSG
  else
    super
  end
end

#add_column_with_default(table_name, column_name, type, **options) ⇒ void

Note:

This method should not be run within a transaction

Note:

For PostgreSQL 11+ you can use add_column instead

This method returns an undefined value.

Adds a column with a default value without durable locks of the entire table

This method runs the following steps:

  1. Add the column allowing NULLs
  2. Change the default value of the column to the specified value
  3. Backfill all existing rows in batches
  4. Set a NOT NULL constraint on the column if desired (the default).

These steps ensure a column can be added to a large and commonly used table without locking the entire table for the duration of the table modification.

For large tables (10/100s of millions of records) you may consider implementing the steps from this helper method yourself as a separate migrations, replacing step #3 with the help of background migrations (see backfill_column_in_background).

Examples:

add_column_with_default(:users, :admin, :boolean, default: false, null: false)

Additional column options

add_column_with_default(:users, :twitter, :string, default: "", limit: 64)

Additional batching options

add_column_with_default(:users, :admin, :boolean, default: false,
                        batch_size: 10_000, pause_ms: 100)

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • type (Symbol) —

    type of new column

  • options (Hash) —

    :batch_size, :batch_column_name, :progress, and :pause_ms are directly passed to update_column_in_batches to control the backfilling process. Additional options (like :limit, etc) are forwarded to add_column

Options Hash (**options):

  • :default (Object) —

    The column's default value

  • :null (Boolean) — default: true —

    Allows or disallows NULL values in the column



439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
 
# File 'lib/online_migrations/schema_statements.rb', line 439

def add_column_with_default(table_name, column_name, type, **options)
  default = options.fetch(:default)

  if Utils.volatile_default?(self, type, default)
    __ensure_not_in_transaction!

    batch_options = options.extract!(:batch_size, :batch_column_name, :progress, :pause_ms)

    if column_exists?(table_name, column_name)
      Utils.say("Column was not created because it already exists (this may be due to an aborted migration " \
                "or similar) table_name: #{table_name}, column_name: #{column_name}")
    else
      transaction do
        add_column(table_name, column_name, type, **options, default: nil, null: true)
        change_column_default(table_name, column_name, default)
      end
    end

    update_column_in_batches(table_name, column_name, default, **batch_options)

    allow_null = options.delete(:null) != false
    if !allow_null
      # A `NOT NULL` constraint for the column is functionally equivalent
      # to creating a CHECK constraint `CHECK (column IS NOT NULL)` for the table
      add_not_null_constraint(table_name, column_name, validate: false)
      validate_not_null_constraint(table_name, column_name)

      # In PostgreSQL 12+ it is safe to "promote" a CHECK constraint to `NOT NULL` for the column
      change_column_null(table_name, column_name, false)
      remove_not_null_constraint(table_name, column_name)
    end
  else
    add_column(table_name, column_name, type, **options)
  end
end

#add_exclusion_constraint(table_name, expression, **options) ⇒ Object 



878
879
880
881
882
883
884
885
886
887
 
# File 'lib/online_migrations/schema_statements.rb', line 878

def add_exclusion_constraint(table_name, expression, **options)
  if __exclusion_constraint_exists?(table_name, expression: expression, **options)
    Utils.say(<<~MSG.squish)
      Exclusion constraint was not created because it already exists (this may be due to an aborted migration or similar).
      table_name: #{table_name}, expression: #{expression}
    MSG
  else
    super
  end
end

#add_foreign_key(from_table, to_table, **options) ⇒ Object

Extends default method to be idempotent.

See Also:



787
788
789
790
791
792
793
794
795
796
797
798
 
# File 'lib/online_migrations/schema_statements.rb', line 787

def add_foreign_key(from_table, to_table, **options)
  options = foreign_key_options(from_table, to_table, options)

  if foreign_key_exists?(from_table, to_table, **options.slice(:column, :primary_key))
    Utils.say(<<~MSG.squish)
      Foreign key was not created because it already exists (this can be due to an aborted migration or similar).
      from_table: #{from_table}, to_table: #{to_table}, options: #{options.inspect}
    MSG
  else
    super
  end
end

#add_index(table_name, column_name, **options) ⇒ Object

Extends default method to be idempotent and automatically recreate invalid indexes.

See Also:



704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
 
# File 'lib/online_migrations/schema_statements.rb', line 704

def add_index(table_name, column_name, **options)
  __ensure_not_in_transaction! if options[:algorithm] == :concurrently

  index =
    if column_name.is_a?(String) && column_name.match?(/\W/)
      # Use only name to check if index exists, because it does not work for complex expressions.
      index_name = (options[:name] || index_name(table_name, column_name)).to_s
      indexes(table_name).find { |i| i.name == index_name }
    else
      # Rewrite this with `IndexDefinition#defined_for?` when Active Record >= 7.1 is supported.
      # See https://github.com/rails/rails/pull/45160.
      indexes(table_name).find { |i| __index_defined_for?(i, column_name, **options) }
    end

  if index
    schema = __schema_for_table(table_name)

    if __index_valid?(index.name, schema: schema)
      Utils.say("Index was not created because it already exists.")
      return
    else
      Utils.say("Recreating invalid index: table_name: #{table_name}, column_name: #{column_name}")
      remove_index(table_name, column_name, **options)
    end
  end

  # "CREATE INDEX CONCURRENTLY" requires a "SHARE UPDATE EXCLUSIVE" lock.
  # It only conflicts with constraint validations, creating/removing indexes,
  # and some other "ALTER TABLE"s.
  super
end

#add_not_null_constraint(table_name, column_name, name: nil, validate: true) ⇒ void

This method returns an undefined value.

Adds a NOT NULL constraint to the column

Examples:

add_not_null_constraint(:users, :email, validate: false)

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • name (String, Symbol) (defaults to: nil) —

    the constraint name. Defaults to chk_rails_<identifier>

  • validate (Boolean) (defaults to: true) —

    whether or not the constraint should be validated



488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
 
# File 'lib/online_migrations/schema_statements.rb', line 488

def add_not_null_constraint(table_name, column_name, name: nil, validate: true)
  column = column_for(table_name, column_name)
  if column.null == false ||
     __not_null_constraint_exists?(table_name, column_name, name: name)
    Utils.say("NOT NULL constraint was not created: column #{table_name}.#{column_name} is already defined as `NOT NULL`")
  else
    expression = "#{quote_column_name(column_name)} IS NOT NULL"
    name ||= __not_null_constraint_name(table_name, column_name)
    add_check_constraint(table_name, expression, name: name, validate: false)

    if validate
      validate_not_null_constraint(table_name, column_name, name: name)
    end
  end
end

#add_reference_concurrently(table_name, ref_name, **options) ⇒ void

Note:

This method should not be run within a transaction

This method returns an undefined value.

Adds a reference to the table with minimal locking

Active Record adds an index non-CONCURRENTLY to references by default, which blocks writes. It also adds a validated foreign key by default, which blocks writes on both tables while validating existing rows.

This method makes sure that an index is added CONCURRENTLY and the foreign key creation is performed in 2 steps: addition of invalid foreign key and a separate validation.

Examples:

add_reference_concurrently(:projects, :user)

Parameters:



654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
 
# File 'lib/online_migrations/schema_statements.rb', line 654

def add_reference_concurrently(table_name, ref_name, **options)
  __ensure_not_in_transaction!

  column_name = "#{ref_name}_id"
  type_column_name = "#{ref_name}_type"

  if !column_exists?(table_name, column_name)
    type = options[:type] || :bigint
    allow_null = options.fetch(:null, true)
    add_column(table_name, column_name, type, null: allow_null)
  end

  if options[:polymorphic] && !column_exists?(table_name, type_column_name)
    allow_null = options[:polymorphic].is_a?(Hash) ? options[:polymorphic][:null] : true
    add_column(table_name, type_column_name, :string, null: allow_null)
  end

  # Always added by default in 5.0+
  index = options.fetch(:index, true)

  if index
    index = {} if index == true
    index_columns = [column_name]

    if options[:polymorphic]
      index_columns.unshift(type_column_name)
      index[:name] ||= "index_#{table_name}_on_#{ref_name}"
    end

    add_index(table_name, index_columns, **index, algorithm: :concurrently)
  end

  foreign_key = options[:foreign_key]

  if foreign_key
    foreign_key = {} if foreign_key == true

    foreign_table_name = Utils.foreign_table_name(ref_name, foreign_key)
    add_foreign_key(table_name, foreign_table_name, **foreign_key, column: column_name, validate: false)

    if foreign_key[:validate] != false
      validate_foreign_key(table_name, foreign_table_name, **foreign_key)
    end
  end
end

#add_text_limit_constraint(table_name, column_name, limit, name: nil, validate: true) ⇒ void

Note:

This helper must be used only with text columns

This method returns an undefined value.

Adds a limit constraint to the text column

Examples:

add_text_limit_constraint(:users, :bio, 255)

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • name (String, Symbol) (defaults to: nil) —

    the constraint name. Defaults to chk_rails_<identifier>

  • validate (Boolean) (defaults to: true) —

    whether or not the constraint should be validated



569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
 
# File 'lib/online_migrations/schema_statements.rb', line 569

def add_text_limit_constraint(table_name, column_name, limit, name: nil, validate: true)
  column = column_for(table_name, column_name)
  if column.type != :text
    raise "add_text_limit_constraint must be used only with :text columns"
  end

  name ||= __text_limit_constraint_name(table_name, column_name)

  if __text_limit_constraint_exists?(table_name, column_name, name: name)
    Utils.say("Text limit constraint was not created: #{table_name}.#{column_name} is already has a limit")
  else
    add_check_constraint(
      table_name,
      "char_length(#{column_name}) <= #{limit}",
      name: name,
      validate: false
    )

    if validate
      validate_text_limit_constraint(table_name, column_name, name: name)
    end
  end
end

#finalize_column_rename(table_name, column_name, new_column_name) ⇒ void

This method returns an undefined value.

Finishes the process of column rename

Examples:

finalize_column_rename(:users, :name, :first_name)

Parameters:

  • table_name (String, Symbol) —

    table name

  • column_name (String, Symbol) —

    the name of the column to be renamed

  • new_column_name (String, Symbol) —

    new new name of the column



231
232
233
 
# File 'lib/online_migrations/schema_statements.rb', line 231

def finalize_column_rename(table_name, column_name, new_column_name)
  finalize_columns_rename(table_name, { column_name => new_column_name })
end

#finalize_columns_rename(table_name, old_new_column_hash) ⇒ void

This method returns an undefined value.

Same as finalize_column_rename but for multiple columns.

Examples:

finalize_columns_rename(:users, { fname: :first_name, lname: :last_name })

Parameters:

  • table_name (String, Symbol) —

    table name

  • old_new_column_hash (Hash) —

    the hash of old and new columns



243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/online_migrations/schema_statements.rb', line 243

def finalize_columns_rename(table_name, old_new_column_hash)
  transaction do
    execute("DROP VIEW #{quote_table_name(table_name)}")

    tmp_table = __tmp_table_name_for_column_rename(table_name)
    rename_table(tmp_table, table_name)
    old_new_column_hash.each do |column_name, new_column_name|
      rename_column(table_name, column_name, new_column_name)
    end
  end
end

#finalize_table_rename(table_name, _new_name = nil) ⇒ void

This method returns an undefined value.

Finishes the process of table rename

Examples:

finalize_table_rename(:users, :clients)

Parameters:

  • table_name (String, Symbol)
  • _new_name (String, Symbol) (defaults to: nil) —

    table's new name. Passing this argument will make this change reversible in migration



360
361
362
 
# File 'lib/online_migrations/schema_statements.rb', line 360

def finalize_table_rename(table_name, _new_name = nil)
  execute("DROP VIEW IF EXISTS #{quote_table_name(table_name)}")
end

#initialize_column_rename(table_name, column_name, new_column_name) ⇒ void

Note:

Prior to using this method, you need to register the database table so that it instructs Active Record to fetch the database table information (for SchemaCache) using the original table name (if it's present). Otherwise, fall back to the old table name:

OnlineMigrations.config.column_renames[table_name] = { old_column_name => new_column_name }

Deploy this change before proceeding with this helper. This is necessary to avoid errors during a zero-downtime deployment.

Note:

None of the DDL operations involving original table name can be performed until finalize_column_rename is run

This method returns an undefined value.

Renames a column without requiring downtime

The technique is built on top of database views, using the following steps:

  1. Rename the table to some temporary name
  2. Create a VIEW using the old table name with addition of a new column as an alias of the old one
  3. Add a workaround for Active Record's schema cache

For example, to rename name column to first_name of the users table, we can run:

BEGIN;
ALTER TABLE users RENAME TO users_column_rename;
CREATE VIEW users AS SELECT *, first_name AS name FROM users;
COMMIT;

As database views do not expose the underlying table schema (default values, not null constraints, indexes, etc), further steps are needed to update the application to use the new table name. Active Record heavily relies on this data, for example, to initialize new models.

To work around this limitation, we need to tell Active Record to acquire this information from original table using the new table name (see notes).

Examples:

initialize_column_rename(:users, :name, :first_name)

Parameters:

  • table_name (String, Symbol) —

    table name

  • column_name (String, Symbol) —

    the name of the column to be renamed

  • new_column_name (String, Symbol) —

    new new name of the column



163
164
165
 
# File 'lib/online_migrations/schema_statements.rb', line 163

def initialize_column_rename(table_name, column_name, new_column_name)
  initialize_columns_rename(table_name, { column_name => new_column_name })
end

#initialize_columns_rename(table_name, old_new_column_hash) ⇒ Object

Same as initialize_column_rename but for multiple columns.

This is useful to avoid multiple iterations of the safe column rename steps when renaming multiple columns.

Examples:

initialize_columns_rename(:users, { fname: :first_name, lname: :last_name })

Parameters:

  • table_name (String, Symbol) —

    table name

  • old_new_column_hash (Hash) —

    the hash of old and new columns

See Also:



180
181
182
183
184
 
# File 'lib/online_migrations/schema_statements.rb', line 180

def initialize_columns_rename(table_name, old_new_column_hash)
  transaction do
    __rename_table_and_create_view(table_name, old_new_column_hash)
  end
end

#initialize_table_rename(table_name, new_name) ⇒ void

Note:

Prior to using this method, you need to register the database table so that it instructs Active Record to fetch the database table information (for SchemaCache) using the new table name (if it's present). Otherwise, fall back to the old table name:

  OnlineMigrations.config.table_renames[old_table_name] = new_table_name

Deploy this change before proceeding with this helper. This is necessary to avoid errors during a zero-downtime deployment.

Note:

None of the DDL operations involving original table name can be performed until finalize_table_rename is run

This method returns an undefined value.

Renames a table without requiring downtime

The technique is built on top of database views, using the following steps:

  1. Rename the database table
  2. Create a database view using the old table name by pointing to the new table name
  3. Add a workaround for Active Record's schema cache

For example, to rename clients table name to users, we can run:

BEGIN;
ALTER TABLE clients RENAME TO users;
CREATE VIEW clients AS SELECT * FROM users;
COMMIT;

As database views do not expose the underlying table schema (default values, not null constraints, indexes, etc), further steps are needed to update the application to use the new table name. Active Record heavily relies on this data, for example, to initialize new models.

To work around this limitation, we need to tell Active Record to acquire this information from original table using the new table name (see notes).

Examples:

initialize_table_rename(:clients, :users)

Parameters:

  • table_name (String, Symbol)
  • new_name (String, Symbol) —

    table's new name



328
329
330
331
332
333
 
# File 'lib/online_migrations/schema_statements.rb', line 328

def initialize_table_rename(table_name, new_name)
  transaction do
    rename_table(table_name, new_name)
    execute("CREATE VIEW #{quote_table_name(table_name)} AS SELECT * FROM #{quote_table_name(new_name)}")
  end
end

#remove_check_constraint(table_name, expression = nil, **options) ⇒ Object

Extends default method to be idempotent

See Also:



866
867
868
869
870
871
872
873
874
875
 
# File 'lib/online_migrations/schema_statements.rb', line 866

def remove_check_constraint(table_name, expression = nil, **options)
  if __check_constraint_exists?(table_name, expression: expression, **options)
    super
  else
    Utils.say(<<~MSG.squish)
      Check constraint was not removed because it does not exist (this may be due to an aborted migration or similar).
      table_name: #{table_name}, expression: #{expression}, options: #{options.inspect}
    MSG
  end
end

#remove_foreign_key(from_table, to_table = nil, **options) ⇒ Object

Extends default method to be idempotent.

See Also:



820
821
822
823
824
825
826
827
828
829
 
# File 'lib/online_migrations/schema_statements.rb', line 820

def remove_foreign_key(from_table, to_table = nil, **options)
  if foreign_key_exists?(from_table, to_table, **options.slice(:name, :to_table, :column))
    super
  else
    Utils.say(<<~MSG.squish)
      Foreign key was not removed because it does not exist (this may be due to an aborted migration or similar).
      from_table: #{from_table}, to_table: #{to_table}, options: #{options.inspect}
    MSG
  end
end

#remove_index(table_name, column_name = nil, **options) ⇒ Object

Extends default method to be idempotent.

See Also:



740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
 
# File 'lib/online_migrations/schema_statements.rb', line 740

def remove_index(table_name, column_name = nil, **options)
  if column_name.blank? && options[:column].blank? && options[:name].blank?
    raise ArgumentError, "No name or columns specified"
  end

  __ensure_not_in_transaction! if options[:algorithm] == :concurrently

  column = column_name || options[:column]
  index_exists =
    if column.is_a?(String) && column.match?(/\W/)
      # Use only name to check if index exists, because it does not work for complex expressions.
      index_name = options[:name] || index_name(table_name, column)
      index_name_exists?(table_name, index_name)
    else
      index_exists?(table_name, column_name, **options)
    end

  if index_exists
    # "DROP INDEX CONCURRENTLY" requires a "SHARE UPDATE EXCLUSIVE" lock.
    # It only conflicts with constraint validations, other creating/removing indexes,
    # and some "ALTER TABLE"s.
    super
  else
    Utils.say("Index was not removed because it does not exist.")
  end
end

#remove_not_null_constraint(table_name, column_name, name: nil) ⇒ void

This method returns an undefined value.

Removes a NOT NULL constraint from the column

Examples:

remove_not_null_constraint(:users, :email)

Explicit name

remove_not_null_constraint(:users, :email, name: "check_users_email_null")

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • name (String, Symbol) (defaults to: nil) —

    the constraint name. Defaults to chk_rails_<identifier>



549
550
551
552
 
# File 'lib/online_migrations/schema_statements.rb', line 549

def remove_not_null_constraint(table_name, column_name, name: nil)
  name ||= __not_null_constraint_name(table_name, column_name)
  remove_check_constraint(table_name, name: name)
end

#remove_text_limit_constraint(table_name, column_name, _limit = nil, name: nil) ⇒ void

This method returns an undefined value.

Removes a limit constraint from the text column

Examples:

remove_text_limit_constraint(:users, :bio)

Explicit name

remove_not_null_constraint(:users, :bio, name: "check_users_bio_max_length")

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • name (String, Symbol) (defaults to: nil) —

    the constraint name. Defaults to chk_rails_<identifier>



628
629
630
631
 
# File 'lib/online_migrations/schema_statements.rb', line 628

def remove_text_limit_constraint(table_name, column_name, _limit = nil, name: nil)
  name ||= __text_limit_constraint_name(table_name, column_name)
  remove_check_constraint(table_name, name: name)
end

#revert_finalize_column_rename(table_name, column_name, new_column_name) ⇒ void

This method returns an undefined value.

Reverts operations performed by finalize_column_rename

Examples:

revert_finalize_column_rename(:users, :name, :first_name)

Parameters:

  • table_name (String, Symbol) —

    table name

  • column_name (String, Symbol) —

    the name of the column to be renamed

  • new_column_name (String, Symbol) —

    new new name of the column



263
264
265
 
# File 'lib/online_migrations/schema_statements.rb', line 263

def revert_finalize_column_rename(table_name, column_name, new_column_name)
  revert_finalize_columns_rename(table_name, { column_name => new_column_name })
end

#revert_finalize_columns_rename(table_name, old_new_column_hash) ⇒ void

This method returns an undefined value.

Same as revert_finalize_column_rename but for multiple columns.

Examples:

revert_finalize_columns_rename(:users, { fname: :first_name, lname: :last_name })

Parameters:

  • table_name (String, Symbol) —

    table name

  • old_new_column_hash (Hash) —

    the hash of old and new columns



275
276
277
278
279
280
281
282
 
# File 'lib/online_migrations/schema_statements.rb', line 275

def revert_finalize_columns_rename(table_name, old_new_column_hash)
  transaction do
    old_new_column_hash.each do |column_name, new_column_name|
      rename_column(table_name, new_column_name, column_name)
    end
    __rename_table_and_create_view(table_name, old_new_column_hash)
  end
end

#revert_finalize_table_rename(table_name, new_name) ⇒ void

This method returns an undefined value.

Reverts operations performed by finalize_table_rename

Examples:

revert_finalize_table_rename(:users, :clients)

Parameters:

  • table_name (String, Symbol)
  • new_name (String, Symbol) —

    table's new name



373
374
375
 
# File 'lib/online_migrations/schema_statements.rb', line 373

def revert_finalize_table_rename(table_name, new_name)
  execute("CREATE VIEW #{quote_table_name(table_name)} AS SELECT * FROM #{quote_table_name(new_name)}")
end

#revert_initialize_column_rename(table_name, column_name = nil, new_column_name = nil) ⇒ void

This method returns an undefined value.

Reverts operations performed by initialize_column_rename

Examples:

revert_initialize_column_rename(:users, :name, :first_name)

Parameters:

  • table_name (String, Symbol) —

    table name

  • column_name (String, Symbol) (defaults to: nil) —

    the name of the column to be renamed. Passing this argument will make this change reversible in migration

  • new_column_name (String, Symbol) (defaults to: nil) —

    new new name of the column. Passing this argument will make this change reversible in migration



199
200
201
 
# File 'lib/online_migrations/schema_statements.rb', line 199

def revert_initialize_column_rename(table_name, column_name = nil, new_column_name = nil)
  revert_initialize_columns_rename(table_name, { column_name => new_column_name })
end

#revert_initialize_columns_rename(table_name, _old_new_column_hash = nil) ⇒ void

This method returns an undefined value.

Same as revert_initialize_column_rename but for multiple columns.

Examples:

revert_initialize_columns_rename(:users, { fname: :first_name, lname: :last_name })

Parameters:

  • table_name (String, Symbol) —

    table name

  • _old_new_column_hash (Hash) (defaults to: nil) —

    the hash of old and new columns Passing this argument will make this change reversible in migration



214
215
216
217
218
219
220
221
 
# File 'lib/online_migrations/schema_statements.rb', line 214

def revert_initialize_columns_rename(table_name, _old_new_column_hash = nil)
  transaction do
    execute("DROP VIEW #{quote_table_name(table_name)}")

    tmp_table = __tmp_table_name_for_column_rename(table_name)
    rename_table(tmp_table, table_name)
  end
end

#revert_initialize_table_rename(table_name, new_name) ⇒ void

This method returns an undefined value.

Reverts operations performed by initialize_table_rename

Examples:

revert_initialize_table_rename(:clients, :users)

Parameters:

  • table_name (String, Symbol)
  • new_name (String, Symbol) —

    table's new name



343
344
345
346
347
348
 
# File 'lib/online_migrations/schema_statements.rb', line 343

def revert_initialize_table_rename(table_name, new_name)
  transaction do
    execute("DROP VIEW IF EXISTS #{quote_table_name(table_name)}")
    rename_table(new_name, table_name)
  end
end

#swap_column_names(table_name, column1, column2) ⇒ void

This method returns an undefined value.

Swaps two column names in a table

This method is mostly intended for use as one of the steps for concurrent column type change

Examples:

swap_column_names(:files, :size_for_type_change, :size)

Parameters:

  • table_name (String, Symbol)
  • column1 (String, Symbol)
  • column2 (String, Symbol) 


390
391
392
393
394
395
396
 
# File 'lib/online_migrations/schema_statements.rb', line 390

def swap_column_names(table_name, column1, column2)
  transaction do
    rename_column(table_name, column1, "#{column1}_tmp")
    rename_column(table_name, column2, column1)
    rename_column(table_name, "#{column1}_tmp", column2)
  end
end

#update_column_in_batches(table_name, column_name, value, **options) {|relation| ... } ⇒ void

Note:

This method should not be run within a transaction

Note:

Consider update_columns_in_batches when updating multiple columns to avoid rewriting the table multiple times.

Note:

For large tables (10/100s of millions of records) you may consider using backfill_column_in_background or copy_column_in_background.

This method returns an undefined value.

Updates the value of a column in batches.

Examples:

update_column_in_batches(:users, :admin, false)

With extra conditions

update_column_in_batches(:users, :name, "Guest") do |relation|
  relation.where(name: nil)
end

From other column

update_column_in_batches(:users, :name_for_type_change, Arel.sql("name"))

With computed value

truncated_name = Arel.sql("substring(name from 1 for 64)")
update_column_in_batches(:users, :name, truncated_name) do |relation|
  relation.where("length(name) > 64")
end

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • value —

    value for the column. It is typically a literal. To perform a computed update, an Arel literal can be used instead

  • options (Hash) —

    a customizable set of options

Options Hash (**options):

  • :batch_size (Integer) — default: 1_000 —

    size of the batch

  • :batch_column_name (String, Symbol) — default: primary key —

    option is for tables without primary key, in this case another unique integer column can be used. Example: :user_id

  • :progress (Proc, Boolean) — default: false —

    whether to show progress while running.

    • when true - will show progress (prints "." for each batch)
    • when false - will not show progress
    • when Proc - will call the proc on each iteration with the batched relation as argument. Example: proc { |_relation| print "." }
  • :pause_ms (Integer) — default: 50 —

    The number of milliseconds to sleep between each batch execution. This helps to reduce database pressure while running updates and gives time to do maintenance tasks

Yields:

  • (relation) —

    a block to be called to add extra conditions to the queries being executed

Yield Parameters:

  • relation (ActiveRecord::Relation) —

    an instance of ActiveRecord::Relation to add extra conditions to



55
56
57
 
# File 'lib/online_migrations/schema_statements.rb', line 55

def update_column_in_batches(table_name, column_name, value, **options, &block)
  update_columns_in_batches(table_name, [[column_name, value]], **options, &block)
end

#update_columns_in_batches(table_name, columns_and_values, batch_size: 1000, batch_column_name: primary_key(table_name), progress: false, pause_ms: 50) ⇒ Object

Same as update_column_in_batches, but for multiple columns.

This is useful to avoid multiple costly disk rewrites of large tables when updating each column separately.

columns_and_values is an array of arrays (first item is a column name, second - new value)

Parameters:

  • columns_and_values

See Also:



69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/online_migrations/schema_statements.rb', line 69

def update_columns_in_batches(table_name, columns_and_values,
                              batch_size: 1000, batch_column_name: primary_key(table_name), progress: false, pause_ms: 50)
  __ensure_not_in_transaction!

  if !columns_and_values.is_a?(Array) || !columns_and_values.all?(Array)
    raise ArgumentError, "columns_and_values must be an array of arrays"
  end

  if progress
    if progress == true
      progress = ->(_) { print(".") }
    elsif !progress.respond_to?(:call)
      raise ArgumentError, "The progress body needs to be a callable."
    end
  end

  model = Utils.define_model(table_name)

  conditions = columns_and_values.filter_map do |(column_name, value)|
    value = Arel.sql(value.call.to_s) if value.is_a?(Proc)

    # Ignore subqueries in conditions
    if !value.is_a?(Arel::Nodes::SqlLiteral) || !value.to_s.match?(/select\s+/i)
      arel_column = model.arel_table[column_name]
      if value.nil?
        arel_column.not_eq(nil)
      else
        arel_column.not_eq(value).or(arel_column.eq(nil))
      end
    end
  end

  batch_relation = model.where(conditions.inject(:or))
  batch_relation = yield batch_relation if block_given?

  iterator = BatchIterator.new(batch_relation)
  iterator.each_batch(of: batch_size, column: batch_column_name) do |relation|
    updates =
      columns_and_values.to_h do |(column, value)|
        value = Arel.sql(value.call.to_s) if value.is_a?(Proc)
        [column, value]
      end

    relation.update_all(updates)

    progress.call(relation) if progress

    sleep(pause_ms * 0.001) if pause_ms > 0
  end
end

#validate_check_constraint(table_name, **options) ⇒ Object

Extends default method with disabled statement timeout while validation is run

See Also:



850
851
852
853
854
855
856
857
858
859
860
 
# File 'lib/online_migrations/schema_statements.rb', line 850

def validate_check_constraint(table_name, **options)
  check_constraint = check_constraint_for!(table_name, **options)

  # Skip costly operation if already validated.
  return if check_constraint.validated?

  # "VALIDATE CONSTRAINT" requires a "SHARE UPDATE EXCLUSIVE" lock.
  # It only conflicts with other validations, creating/removing indexes,
  # and some other "ALTER TABLE"s.
  super
end

#validate_foreign_key(from_table, to_table = nil, **options) ⇒ Object

Extends default method with disabled statement timeout while validation is run

See Also:



804
805
806
807
808
809
810
811
812
813
814
 
# File 'lib/online_migrations/schema_statements.rb', line 804

def validate_foreign_key(from_table, to_table = nil, **options)
  foreign_key = foreign_key_for!(from_table, to_table: to_table, **options)

  # Skip costly operation if already validated.
  return if foreign_key.validated?

  # "VALIDATE CONSTRAINT" requires a "SHARE UPDATE EXCLUSIVE" lock.
  # It only conflicts with other validations, creating/removing indexes,
  # and some other "ALTER TABLE"s.
  super
end

#validate_not_null_constraint(table_name, column_name, name: nil) ⇒ void

This method returns an undefined value.

Validates a NOT NULL constraint on the column

Examples:

validate_not_null_constraint(:users, :email)

Explicit name

validate_not_null_constraint(:users, :email, name: "check_users_email_null")

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • name (String, Symbol) (defaults to: nil) —

    the constraint name. Defaults to chk_rails_<identifier>



519
520
521
522
523
524
525
526
527
528
529
530
531
532
 
# File 'lib/online_migrations/schema_statements.rb', line 519

def validate_not_null_constraint(table_name, column_name, name: nil)
  name ||= __not_null_constraint_name(table_name, column_name)
  column = column_for(table_name, column_name)

  if column.null == false &&
     !__not_null_constraint_exists?(table_name, column_name, name: name)
    Utils.say(<<~MSG.squish)
      NOT NULL constraint was not validated: it does not exist and
      column #{table_name}.#{column_name} is already defined as `NOT NULL`
    MSG
  else
    validate_check_constraint(table_name, name: name)
  end
end

#validate_text_limit_constraint(table_name, column_name, name: nil) ⇒ void

This method returns an undefined value.

Validates a limit constraint on the text column

Examples:

validate_text_limit_constraint(:users, :bio)

Explicit name

validate_text_limit_constraint(:users, :bio, name: "check_users_bio_max_length")

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • name (String, Symbol) (defaults to: nil) —

    the constraint name. Defaults to chk_rails_<identifier>



608
609
610
611
 
# File 'lib/online_migrations/schema_statements.rb', line 608

def validate_text_limit_constraint(table_name, column_name, name: nil)
  name ||= __text_limit_constraint_name(table_name, column_name)
  validate_check_constraint(table_name, name: name)
end