Module: PgEnumMigrations

Defined in:
lib/rails_string_enum/pg_enum_migration.rb

Instance Method Summary collapse

Instance Method Details

#add_enum_value(enum_name, value, before: nil, after: nil, schema: 'public') ⇒ Object

add_enum_value :color, ‘black’ add_enum_value :color, ‘purple’, after: ‘red’ add_enum_value :color, ‘pink’, before: ‘purple’ add_enum_value :color, ‘white’, schema: ‘public’ WARN cannot run inside a transaction block



15
16
17
18
19
20
21
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 15

def add_enum_value(enum_name, value, before: nil, after: nil, schema: 'public')
  opts = if    before then "BEFORE #{escape_enum_value(before)}"
         elsif after  then "AFTER #{escape_enum_value(after)}"
         else  ''
         end
  execute "ALTER TYPE #{enum_name(enum_name, schema)} ADD VALUE IF NOT EXISTS #{escape_enum_value(value)} #{opts}"
end

#create_enum(enum_name, values, schema: 'public') ⇒ Object

create_enum :color, %w(red green blue) # default schema is ‘public’ create_enum :color, %w(red green blue), schema: ‘cmyk’



5
6
7
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 5

def create_enum(enum_name, values, schema: 'public')
  execute "CREATE TYPE #{enum_name(enum_name, schema)} AS ENUM (#{escape_enum_values(values)})"
end

#delete_enum_value(enum_name, value_name, scheme: 'public') ⇒ Object

you should delete record with deleting value Product.only_purple.delete_all or Product.purple.update_all(color: nil)

if exists index with condition - add_index :products, :color, where: “color NOT IN (‘white’, ‘black’)” this method show exeption ERROR: operator does not exist: color <> color_new you must first remove and then create an index

delete_enum_value :color, ‘black’



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 38

def delete_enum_value(enum_name, value_name, scheme: 'public')
  old_values = select_values("SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid = '#{scheme}.#{enum_name}'::regtype::oid")
  new_values = old_values - Array(value_name)

  execute <<-SQL
    ALTER TYPE #{enum_name} rename to #{enum_name}_old;
    CREATE TYPE #{enum_name} AS enum (#{escape_enum_values(new_values)});
  SQL

  cols_using_enum = select_rows("SELECT table_name, column_name, column_default FROM information_schema.columns WHERE udt_name = '#{enum_name}_old'")
  cols_using_enum.each do |table_name, column_name, column_default|
    unless column_default.nil?
      raise "column #{table_name}.#{column_name} has default value #{column_default}, you must manually drop default"
    end
    execute <<-SQL
      ALTER TABLE #{table_name}
      ALTER COLUMN #{column_name} TYPE #{enum_name} USING #{column_name}::text::#{enum_name};
    SQL
  end

  execute <<-SQL
    DROP TYPE #{enum_name}_old
  SQL
end

#drop_enum(enum_name, schema: nil) ⇒ Object

drop_enum :color drop_enum :color, schema: ‘cmyk’



25
26
27
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 25

def drop_enum(enum_name, schema: nil)
  execute "DROP TYPE #{enum_name(enum_name, schema)}"
end

#int_to_enums(table, col_name, enum_name:, definitions: nil, default: nil, use_exist_enum: false) ⇒ Object

int_to_enums :users, :partner_type, enum_name: ‘user_partner_type_enum’, definitions: { retail: 0, affiliate: 1, wholesale: 2 }



100
101
102
103
104
105
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 100

def int_to_enums(table, col_name, enum_name:, definitions: nil, default: nil, use_exist_enum: false)
  convert_sql = definitions.map {|str, int| "WHEN #{int} THEN '#{str}'" }.join(' ')
  convert_sql = "CASE #{col_name} #{convert_sql} END"

  convert_to_enum table, col_name, enum_name, definitions.keys, convert_sql, default, use_exist_enum
end

#rename_enum_value(enum_name, old_value_name, new_value_name, scheme: 'public') ⇒ Object

rename_enum_value :color, ‘white’, ‘pale’



66
67
68
69
70
71
72
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 66

def rename_enum_value(enum_name, old_value_name, new_value_name, scheme: 'public')
  execute <<-SQL
    UPDATE pg_catalog.pg_enum
    SET enumlabel = '#{new_value_name}'
    WHERE enumtypid = '#{scheme}.#{enum_name}'::regtype::oid AND enumlabel = '#{old_value_name}'
  SQL
end

#reorder_enum_values(enum_name, ordered_values, scheme: 'public') ⇒ Object

reorder_enum_values :color, %w(green pale red blue)



77
78
79
80
81
82
83
84
85
86
87
88
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 77

def reorder_enum_values(enum_name, ordered_values, scheme: 'public')
  all_values = select_values("SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid = '#{scheme}.#{enum_name}'::regtype::oid")
  max_order =  select_value("SELECT max(enumsortorder) FROM pg_catalog.pg_enum WHERE enumtypid = '#{scheme}.#{enum_name}'::regtype::oid").to_i + 1

  ordered_sql = (ordered_values | all_values).map.with_index{|v, i| "WHEN '#{v}' THEN #{i + max_order}"}.join(' ')

  execute <<-SQL
    UPDATE pg_catalog.pg_enum
    SET enumsortorder = CASE enumlabel #{ordered_sql} END
    WHERE enumtypid = '#{scheme}.#{enum_name}'::regtype::oid
  SQL
end

#string_to_enums(table, col_name, enum_name:, definitions: nil, default: nil, use_exist_enum: false) ⇒ Object

string_to_enums :order, :state, enum_name: ‘order_state_enum’, definitions: %w(accept confirmed)



93
94
95
 
# File 'lib/rails_string_enum/pg_enum_migration.rb', line 93

def string_to_enums(table, col_name, enum_name:, definitions: nil, default: nil, use_exist_enum: false)
  convert_to_enum table, col_name, enum_name, definitions, col_name, default, use_exist_enum
end