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 "    ALTER TYPE \#{enum_name} rename to \#{enum_name}_old;\n    CREATE TYPE \#{enum_name} AS enum (\#{escape_enum_values(new_values)});\n  SQL\n\n  cols_using_enum = select_rows(\"SELECT table_name, column_name, column_default FROM information_schema.columns WHERE udt_name = '\#{enum_name}_old'\")\n  cols_using_enum.each do |table_name, column_name, column_default|\n    unless column_default.nil?\n      raise \"column \#{table_name}.\#{column_name} has default value \#{column_default}, you must manually drop default\"\n    end\n    execute <<-SQL\n      ALTER TABLE \#{table_name}\n      ALTER COLUMN \#{column_name} TYPE \#{enum_name} USING \#{column_name}::text::\#{enum_name};\n    SQL\n  end\n\n  execute <<-SQL\n    DROP TYPE \#{enum_name}_old\n  SQL\nend\n"

#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 "    UPDATE pg_catalog.pg_enum\n    SET enumlabel = '\#{new_value_name}'\n    WHERE enumtypid = '\#{scheme}.\#{enum_name}'::regtype::oid AND enumlabel = '\#{old_value_name}'\n  SQL\nend\n"

#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 "    UPDATE pg_catalog.pg_enum\n    SET enumsortorder = CASE enumlabel \#{ordered_sql} END\n    WHERE enumtypid = '\#{scheme}.\#{enum_name}'::regtype::oid\n  SQL\nend\n"

#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