module PGEnums

Public Instance Methods

add_to_enum(enum_type:, new_value:) click to toggle source
# File lib/pg_enums.rb, line 32
  def add_to_enum(enum_type:, new_value:)
    ActiveRecord::Base.connection.execute <<-SQL
      ALTER TYPE #{enum_type} ADD VALUE IF NOT EXISTS '#{new_value}';
    SQL
  end
create_enum(enum_type:, table:, column:, values:) click to toggle source
# File lib/pg_enums.rb, line 51
  def create_enum(enum_type:, table:, column:, values:)
    ActiveRecord::Base.connection.execute <<-SQL
      CREATE TYPE #{enum_type} AS ENUM (#{"'" + values.join("', '") + "'"});
    SQL
    add_column table, column, enum_type
  end
delete_enum(enum:, table:, column:) click to toggle source
# File lib/pg_enums.rb, line 44
  def delete_enum(enum:, table:, column:)
    remove_column table, column
    ActiveRecord::Base.connection.execute <<-SQL
      DROP TYPE #{enum};
    SQL
  end
delete_from_enum(table:, column:, enum_type:, value_to_drop:, map_to:) click to toggle source
# File lib/pg_enums.rb, line 20
def delete_from_enum(table:, column:, enum_type:, value_to_drop:, map_to:)
  # Select default store it and drop it if defined
  default = default(table, column, enum_type, value_to_drop, map_to)
  drop_default(table, column) if default
  # Generate the new labels
  new_enumlabels = new_enumlabels(enum_type, value_to_drop)
  # update the enum and the column
  change_enum_labels(table, column, enum_type, value_to_drop, map_to, new_enumlabels)
  # restaure the default value
  restaure_default(table, column, default, enum_type) if default
end
rename_enum(enum:, new_name:) click to toggle source
# File lib/pg_enums.rb, line 38
  def rename_enum(enum:, new_name:)
    ActiveRecord::Base.connection.execute <<-SQL
      ALTER TYPE #{enum} RENAME TO #{new_name};
    SQL
  end
update_enum(table:, column:, enum_type:, old_value:, new_value:) click to toggle source
# File lib/pg_enums.rb, line 4
  def update_enum(table:, column:, enum_type:, old_value:, new_value:)
    # Select default store it and drop it if defined
    default = default(table, column, enum_type, old_value, new_value)
    drop_default(table, column) if default
    # Generate the new labels
    new_enumlabels = new_enumlabels(enum_type, old_value, new_value)
    # Add the new label to the enum
    ActiveRecord::Base.connection.execute <<-SQL
      ALTER TYPE #{enum_type} ADD VALUE IF NOT EXISTS '#{new_value}';
    SQL
    # update the enum and the column
    change_enum_labels(table, column, enum_type, old_value, new_value, new_enumlabels)
    # restaure the default value
    restaure_default(table, column, default, enum_type) if default
  end

Private Instance Methods

change_enum_labels(table, column, enum_type, old_value, new_value, new_enumlabels) click to toggle source
# File lib/pg_enums.rb, line 60
  def change_enum_labels(table, column, enum_type, old_value, new_value, new_enumlabels)
    # Rename the enum
    # Create a new enum with the new labels
    # Update the table to set each old value to the new one
    # Update the table to map the column to the new enum
    # Drop the old enum
    ActiveRecord::Base.connection.execute <<-SQL
      ALTER TYPE #{enum_type} RENAME TO old_#{enum_type};
      CREATE TYPE #{enum_type} AS ENUM (#{new_enumlabels});
      UPDATE #{table} SET #{column} = '#{new_value}' WHERE #{table}.#{column} = '#{old_value}';
      ALTER TABLE #{table} ALTER COLUMN #{column} TYPE #{enum_type} USING #{column}::text::#{enum_type};
      DROP TYPE old_#{enum_type};
    SQL
  end
default(table, column, enum_type, old_value, new_value) click to toggle source
# File lib/pg_enums.rb, line 89
  def default(table, column, enum_type, old_value, new_value)
    default = ActiveRecord::Base.connection.execute <<-SQL
      SELECT column_default FROM information_schema.columns
      WHERE (table_schema, table_name, column_name) = ('public', '#{table}', '#{column}')
    SQL
    return unless default.first["column_default"]
    default = default.first["column_default"]
    default.slice!("::#{enum_type}")
    (default == "'#{old_value}'") ? "'#{new_value}'" : default
  end
drop_default(table, column) click to toggle source
# File lib/pg_enums.rb, line 100
  def drop_default(table, column)
    ActiveRecord::Base.connection.execute <<-SQL
      ALTER TABLE #{table} ALTER #{column} DROP default
    SQL
  end
new_enumlabels(enum_type, old_value, new_value = nil) click to toggle source

Fetch the enum labels from the data base

# File lib/pg_enums.rb, line 76
  def new_enumlabels(enum_type, old_value, new_value = nil)
    enumlabels = ActiveRecord::Base.connection.execute <<-SQL
      SELECT enumlabel from pg_enum
      WHERE enumtypid=(
        SELECT oid FROM pg_type WHERE typname='#{enum_type}'
      )
      ORDER BY enumsortorder;
    SQL
    enumlabels = enumlabels.map { |e| "'#{e["enumlabel"]}'" } - ["'#{old_value}'"]
    enumlabels << "'#{new_value}'" if new_value
    enumlabels.uniq.join(", ").chomp(", ")
  end
restaure_default(table, column, default, enum_type) click to toggle source
# File lib/pg_enums.rb, line 106
  def restaure_default(table, column, default, enum_type)
    ActiveRecord::Base.connection.execute <<-SQL
      ALTER TABLE #{table} ALTER #{column} SET DEFAULT #{default}::#{enum_type}
    SQL
  end