class Object

Constants

IgnoreTables
NATIVE_DATABASE_PRECISION

taken from comments in ActiveRecord::ConnectionAdapters::TableDefinition

NATIVE_DATABASE_SCALE

Public Instance Methods

add_foreign_key(from_table, to_table, opts={}) click to toggle source
# File lib/tasks/db.rb, line 456
def add_foreign_key(from_table, to_table, opts={})
  opts = HashWithIndifferentAccess.new opts
  opts[:from_table] = from_table.to_s
  opts[:to_table] = to_table.to_s
  opts[:column] = to_table.to_s.singularize + "_id" unless opts[:column].present?
  opts[:primary_key] = "id" unless opts[:primary_key].present?
  opts[:name] = "fk #{from_table.to_s.parameterize}.#{opts[:column].to_s.parameterize} to #{to_table.to_s.parameterize}.#{opts[:primary_key].to_s.parameterize}" unless opts[:name].present?
  opts[:column] = opts[:column].to_s
  opts[:primary_key] = opts[:primary_key].to_s
  opts[:name] = opts[:name].to_s
  $foreign_keys.append(opts)
end
add_index(table, columns, opts) click to toggle source
# File lib/tasks/db.rb, line 443
def add_index(table, columns, opts)
  opts[:table] = table
  opts[:columns] = columns
  if !opts.has_key? :unique
    opts[:unique] = false
  end
  # this is the default index type for mysql and postgres
  opts[:using] = :btree unless opts.has_key?(:using)
  $schema_indexes.append(opts)
end
build_pg_connection() click to toggle source
# File lib/tasks/db.rb, line 78
def build_pg_connection
  return PG::Connection.open(build_pg_connection_config)
end
build_pg_connection_config() click to toggle source
# File lib/tasks/db.rb, line 69
def build_pg_connection_config
  config = HashWithIndifferentAccess.new get_connection_config
  config.delete(:adapter)
  config.delete(:pool)
  config[:dbname] = config.delete(:database)
  config[:user] = config.delete(:username) || ENV['USER'] || ENV['USERNAME']
  return config
end
calc_column_changes(tbl, existing_cols, schema_cols) click to toggle source
# File lib/tasks/db.rb, line 601
def calc_column_changes(tbl, existing_cols, schema_cols)

  existing_cols_by_name = Hash[existing_cols.collect { |c| [c.name, c] }]
  schema_cols_by_name = Hash[schema_cols.collect { |c| [c.name, c] }]
  existing_col_names = Set.new existing_cols_by_name.keys
  schema_col_names = Set.new schema_cols_by_name.keys
  new_cols = schema_col_names - existing_col_names
  delete_cols = existing_col_names - schema_col_names
  rename_cols = {}

  new_cols.each do |cn|
    sc = schema_cols_by_name[cn]
    if sc.akas
      sc.akas.each do |aka|
        if delete_cols.include? aka
          ec = existing_cols_by_name[aka]
          if can_convert(sc.type.to_s, ec.type.to_s)
            rename_cols[ec.name] = sc.name
            new_cols.delete cn
            delete_cols.delete aka
          end
        end
      end
    end
  end

  to_run = []

  pg_a = gen_pg_adapter()

  if new_cols.size > 0
#    puts "tbl: #{tbl} new_cols: #{new_cols}"
    new_cols.each do |cn|
      sc = schema_cols_by_name[cn]
      pg_a.add_column(tbl, cn, sc.type.to_sym, sc.opts)
    end
    to_run += $tmp_to_run
  end

  $tmp_to_run = []
  rename_cols.each do |ecn, scn|
    pg_a.rename_column(tbl, ecn, scn)
  end
  to_run += $tmp_to_run
  delete_cols.each do |cn|
    to_run.append("ALTER TABLE #{escape_table(tbl)} DROP COLUMN #{escape_table(cn)}")
  end

  same_names = existing_col_names - delete_cols
  same_names.each do |ecn|
    $tmp_to_run = []
    ec = existing_cols_by_name[ecn]
    if rename_cols.include? ecn
      sc = schema_cols_by_name[rename_cols[ecn]]
    else
      sc = schema_cols_by_name[ecn]
    end
    type_changed = sc.type.to_s != ec.type.to_s
    # numeric and decimal are equiv in postges, and the db always returns numeric
    if type_changed and sc.type.to_s=="decimal" and ec.type.to_s=="numeric"
      type_changed = false
    end
    # ruby turns decimal(x,0) into integer when reading meta-data
    if type_changed and sc.type.to_s=="decimal" and ec.type.to_s=="integer" and sc.opts[:scale]==0
      type_changed = false
    end
    sc_limit = sc.opts.has_key?(:limit) ? sc.opts[:limit] : ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[sc.type.to_sym][:limit]
    limit_changed = (sc.type=="string" and sc_limit!=ec.limit) # numeric types in postgres report the precision as the limit - ignore non string types for now
    sc_precision = sc.opts.has_key?(:precision) ? sc.opts[:precision] : NATIVE_DATABASE_PRECISION[sc.type]
    precision_changed = (sc.type=="decimal" and sc_precision!=ec.precision) # by type_to_sql in schema_statements.rb, precision is only used on decimal types
    sc_scale = sc.opts.has_key?(:scale) ? sc.opts[:scale] : NATIVE_DATABASE_SCALE[sc.type]
    scale_changed = (sc.type=="decimal" and sc_scale!=ec.scale)
    if type_changed or limit_changed or precision_changed or scale_changed
      pg_a.change_column(tbl, sc.name, sc.type.to_sym, sc.opts)
      while $tmp_to_run.length > 1
        $tmp_to_run.pop # in later versions of rails change_column runs extra crap we don't want
      end
    end
    if normalize_default(ec.default, type: ec.type) != normalize_default(sc.opts[:default], type: sc.type)
      pg_a.change_column_default(tbl, sc.name, sc.opts[:default])
    end
    sc_null = sc.opts.has_key?(:null) ? sc.opts[:null] : true
    if ec.null != sc_null
      if !sc_null and !sc.opts.has_key?(:default)
        raise "\nERROR: In order to set #{tbl}.#{sc.name} as NOT NULL you need to add a :default value.\n\n"
      end
      pg_a.change_column_null(tbl, sc.name, sc_null, sc.opts[:default])
    end
    to_run += $tmp_to_run
  end

  if !to_run.empty?
    to_run.unshift("\n-- column changes for table #{tbl}")
  end

  return to_run, rename_cols
end
calc_fk_changes(foreign_keys, existing_tables, renames) click to toggle source
# File lib/tasks/db.rb, line 209
def calc_fk_changes(foreign_keys, existing_tables, renames)
  existing_foreign_keys = []
  unless existing_tables.empty?
    existing_tables_sql = (existing_tables.map {|tn| ActiveRecord::Base.sanitize(tn)}).join(',')
    sql = %{
      SELECT
          tc.constraint_name, tc.table_name, kcu.column_name,
          ccu.table_name AS foreign_table_name,
          ccu.column_name AS foreign_column_name
      FROM
          information_schema.table_constraints AS tc
          JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
          JOIN information_schema.constraint_column_usage AS ccu
            ON ccu.constraint_name = tc.constraint_name
      WHERE constraint_type = 'FOREIGN KEY'
        AND tc.table_name in (#{existing_tables_sql});
    }
    build_pg_connection.exec(sql).each do |row|
      from_table = row['table_name']
      from_table = renames[from_table] if renames[from_table].present?
      to_table = row['foreign_table_name']
      to_table = renames[to_table] if renames[to_table].present?
      existing_foreign_keys << HashWithIndifferentAccess.new({
        :from_table => from_table,
        :to_table => to_table,
        :column => row['column_name'],
        :primary_key => row['foreign_column_name'],
        :name => row['constraint_name'],
      })
    end
  end

  existing_foreign_keys = Set.new existing_foreign_keys
  foreign_keys = Set.new foreign_keys
  add_fks = foreign_keys - existing_foreign_keys
  delete_fks = existing_foreign_keys - foreign_keys

  rename_fks = []
  delete_fks.each do |delete_fk|
    dfk = delete_fk.clone
    dfk.delete(:name)
    add_fks.each do |add_fk|
      afk = add_fk.clone
      afk.delete(:name)
      if afk==dfk
        delete_fks.delete delete_fk
        add_fks.delete add_fk
        rename_fks << {
          :table => delete_fk[:from_table],
          :from_name => delete_fk[:name],
          :to_name => add_fk[:name]
        }
        # ALTER TABLE name RENAME CONSTRAINT "error_test_id_fkey" TO "the_new_name_fkey";
      end
    end
  end

  to_run = []
  delete_fks.each do |fk|
    to_run << "ALTER TABLE #{escape_table(fk[:from_table])} DROP CONSTRAINT IF EXISTS #{escape_table(fk[:name])}"
  end
  add_fks.each do |fk|
    to_run << "ALTER TABLE #{escape_table(fk[:from_table])} ADD CONSTRAINT #{escape_table(fk[:name])} FOREIGN KEY (#{escape_table(fk[:column])}) REFERENCES #{escape_table(fk[:to_table])} (#{escape_table(fk[:primary_key])}) MATCH FULL"
  end
  rename_fks.each do |fk|
    to_run << "ALTER TABLE #{escape_table(fk[:table])} RENAME CONSTRAINT #{escape_table(fk[:from_name])} TO #{escape_table(fk[:to_name])}"
  end

  if !to_run.empty?
    to_run.unshift("\n-- update foreign keys")
  end

  return to_run
end
calc_index_changes(existing_indexes, schema_indexes, table_renames, rename_cols_by_table) click to toggle source
# File lib/tasks/db.rb, line 173
def calc_index_changes(existing_indexes, schema_indexes, table_renames, rename_cols_by_table)
  # rename_cols_by_table is by the new table name
  existing_indexes = Set.new existing_indexes
  existing_indexes.each do |index|
    if table_renames[index[:table]].present?
      index[:table] = table_renames[index[:table]]
    end
  end
  schema_indexes = Set.new schema_indexes

  add_indexes = schema_indexes - existing_indexes
  delete_indexes = existing_indexes - schema_indexes

  $tmp_to_run = []

  connection = ActiveRecord::Base.connection

  delete_indexes.each do |index|
    table = index.delete(:table)
    name = index[:name]
    $tmp_to_run << "DROP INDEX IF EXISTS #{escape_table(name)}"
  end

  add_indexes.each do |index|
    table = index.delete(:table)
    columns = index.delete(:columns)
    connection.add_index table, columns, index
  end

  if !$tmp_to_run.empty?
    $tmp_to_run.unshift("\n-- update indexes")
  end

  return $tmp_to_run
end
calc_perms_changes(schema_tables, noop) click to toggle source
# File lib/tasks/db.rb, line 285
def calc_perms_changes schema_tables, noop
  users = ($check_perms_for.map { |user| ActiveRecord::Base::sanitize(user) }).join ","
  database = ActiveRecord::Base.connection_config[:database]
  sql = %{
    select grantee, table_name, privilege_type
    from information_schema.role_table_grants
    where table_catalog=#{ActiveRecord::Base::sanitize(database)}
      and grantee in (#{users})
      and table_schema='public';
  }
  existing_perms = Hash.new { |h, k| h[k] = Hash.new { |h, k| h[k] = Set.new } }
  build_pg_connection.exec(sql).each do |row|
    existing_perms[row['grantee']][row['table_name']].add(row['privilege_type'])
  end
  to_run = []
  schema_tables.each do |table_name, tbl|
    $check_perms_for.each do |user|
      to_grant = (tbl.perms_for_user[user] - existing_perms[user][table_name]).to_a
      to_revoke = (existing_perms[user][table_name] - tbl.perms_for_user[user]).to_a
      to_run.push("GRANT "+ to_grant.join(',') +" ON #{escape_table(table_name)} TO #{user}") unless to_grant.empty?
      to_run.push("REVOKE "+ to_revoke.join(',') +" ON #{escape_table(table_name)} FROM #{user}") unless to_revoke.empty?
    end
  end

  if !to_run.empty?
    to_run.unshift("\n-- update permissions")
  end

  return to_run
end
calc_table_changes(existing_tables, schema_tables, akas_tables) click to toggle source
# File lib/tasks/db.rb, line 505
def calc_table_changes(existing_tables, schema_tables, akas_tables)
  existing_tables = Set.new existing_tables
  schema_tables = Set.new schema_tables
  adds = schema_tables - existing_tables
  deletes = existing_tables - schema_tables
  renames = {}
  adds.each do |newt|
    akas = Set.new akas_tables[newt]
    possibles = akas & deletes
    if possibles.size > 1
      raise "Too many possible table matches (#{possibles}) for #{newt}.  Please trim your akas."
    end
    if possibles.size == 1
      oldt = possibles.to_a()[0]
      renames[oldt] = newt
      adds.delete(newt)
      deletes.delete(oldt)
    end
  end
  return adds, deletes, renames
end
can_convert(type1, type2) click to toggle source
# File lib/tasks/db.rb, line 583
def can_convert(type1, type2)
  if type1==type2
    return true
  end
  if type1=='integer' and type2=='decimal'
    return true
  end
  return false
end
check_perm(perm) click to toggle source
# File lib/tasks/db.rb, line 472
def check_perm perm
    perm = perm.to_s.upcase
    return Set.new($allowed_perms) if perm=="ALL"
    raise ArgumentError.new("permission #{perm} is not one of #{$allowed_perms.to_a}") unless $allowed_perms.include? perm
    return Set.new [perm]
end
create_table(name, opts={}) { |tbl| ... } click to toggle source
# File lib/tasks/db.rb, line 407
def create_table(name, opts={})
  tbl = Table.new
  tbl.name = name
  tbl.opts = opts
  tbl.perms_for_user = Hash.new { |h, k| h[k] = Set.new }
  $default_perms_for.each do |k,v|
    tbl.perms_for_user[k] += v
  end
  if opts
    if opts.has_key? 'id'
      tbl.id = opts['id']
    else
      tbl.id = true
    end
  end
  if tbl.id
    c = Column.new
    c.type = "integer"
    c.name = "id"
    c.opts = { :null=>false }
    tbl.columns.append c
  end
  yield tbl
  $schema_tables[name] = tbl
  aka = tbl.opts[:aka]
  if !aka.nil?
    if aka.respond_to?('each')
      $akas_tables[tbl.name].merge(aka)
    else
      $akas_tables[tbl.name].add(aka)
    end
  end
end
do_evolve(noop, yes, nowait) click to toggle source
# File lib/tasks/db.rb, line 82
def do_evolve(noop, yes, nowait)
  existing_tables, existing_indexes = load_existing_tables()
  server_version = load_server_version()

  require_relative 'db_mock'

  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.existing_tables = existing_tables
  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.server_version = server_version

  require Rails.root + 'db/schema'

  adds, deletes, renames = calc_table_changes(existing_tables.keys, $schema_tables.keys, $akas_tables)

  to_run = []

  to_run += sql_adds(adds)
  to_run += sql_renames(renames)

  rename_cols_by_table = {}

  existing_tables.each do |etn, ecols|
    next if deletes.include? etn
    ntn = renames[etn] || etn
    commands, rename_cols = calc_column_changes(ntn, existing_tables[etn], $schema_tables[ntn].columns)
    to_run += commands
    rename_cols_by_table[ntn] = rename_cols
  end

  to_run += calc_index_changes(existing_indexes, $schema_indexes, renames, rename_cols_by_table)

  to_run += calc_fk_changes($foreign_keys, Set.new(existing_tables.keys), renames)

  to_run += calc_perms_changes($schema_tables, noop) unless $check_perms_for.empty?

  to_run += sql_drops(deletes)

  # prompt and execute

  if to_run.empty?
    if !noop
      puts "\nYour database is up to date!"
      puts
    end
  else
    to_run.unshift("\nBEGIN TRANSACTION")
    to_run.append("\nCOMMIT")

    require_relative 'sql_color'
    to_run.each do |sql|
      puts SQLColor.colorize(sql)
    end
    puts

    if noop
      return
    end

    puts "Connecting to database:"
    build_pg_connection_config.each do |k,v|
      v = "*" * v.length if k.present? && k.to_s=='password'
      puts "\t#{k} => #{v}"
    end

    if !yes
      print "Run this SQL? (type yes or no) "
    end
    if yes || STDIN.gets.strip=='yes'
      if !nowait
        print "\nExecuting in "
        [3,2,1].each do |c|
          print "#{c}..."
          sleep(1)
        end
      end
      puts
      conn = build_pg_connection
      to_run.each do |sql|
        puts SQLColor.colorize(sql)
        conn.exec(sql)
      end
      puts "\n--==[ COMPLETED ]==--"
    else
      puts "\n--==[ ABORTED ]==--"
    end
    puts
  end


end
escape_table(k) click to toggle source
# File lib/tasks/db.rb, line 527
def escape_table(k)
  k = k.to_s if k.is_a? Symbol
  return PG::Connection.quote_ident k
end
gen_pg_adapter() click to toggle source
# File lib/tasks/db.rb, line 532
def gen_pg_adapter()
  $tmp_to_run = []
  a = ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.allocate
  ActiveRecord::ConnectionAdapters::AbstractAdapter.instance_method(:initialize).bind(a).call build_pg_connection
  return a
end
get_connection_config() click to toggle source
# File lib/tasks/db.rb, line 55
def get_connection_config
  config_name = "#{Rails.env}_dbevolve"
  if Rails.configuration.database_configuration[config_name].present?
    config = Rails.configuration.database_configuration[config_name]
  else
    unless $i_nagged || Rails.env=='development'
      puts "Your database.yml file does not contain an entry for '#{config_name}', so we're using '#{Rails.env}'.  This works if your database user has permission to edit your schema, but this is not recommended outside of development.  For more information visit: https://github.com/keredson/ruby-db-evolve/blob/master/README.md#schema-change-permissions"
      $i_nagged = true
    end
    config = Rails.configuration.database_configuration[Rails.env]
  end
  return config
end
grant(*perms, to: nil) click to toggle source
# File lib/tasks/db.rb, line 479
def grant(*perms, to: nil)
  to = $db_username if to.nil?
  $check_perms_for.add(to)
  perms.each do |perm|
    $default_perms_for[to] |= check_perm(perm)
  end
end
load_existing_tables() click to toggle source
# File lib/tasks/db.rb, line 319
def load_existing_tables()
  existing_tables = {}
  existing_indexes = []
  ActiveRecord::Base.establish_connection(get_connection_config)
  connection = ActiveRecord::Base.connection
  connection.tables.sort.each do |tbl|
    next if IgnoreTables.include? tbl
    columns = connection.columns(tbl)
    existing_tables[tbl] = columns
    connection.indexes(tbl).each do |i|
      index = {:table => i.table, :name => i.name, :columns => i.columns, :unique => i.unique, using: i.using}
      existing_indexes.append(index)
    end
  end
  return existing_tables, existing_indexes
end
load_server_version() click to toggle source
# File lib/tasks/db.rb, line 336
def load_server_version()
  server_version = build_pg_connection.server_version rescue nil
  return server_version
end
normalize_default(default, type: nil) click to toggle source
# File lib/tasks/db.rb, line 699
def normalize_default default, type: nil
  type = type.to_s unless type==nil
  unless default==nil
    default = default.to_i if type=='integer'
    default = default.truish? if type=='boolean' && (default.is_a?(String) || default.is_a?(Symbol))
    default = default.to_s if default.is_a? Symbol
  end
  if (default.respond_to?(:infinite?) && default.infinite?) || default.is_a?(String) && (default.downcase == 'infinity' || default.downcase == '-infinity')
    default = default.to_s.downcase
  end
  return default
end
revoke(*perms, from: nil) click to toggle source
# File lib/tasks/db.rb, line 487
def revoke(*perms, from: nil)
  from = $db_username if from.nil?
  $check_perms_for.add(from)
  perms.each do |perm|
    $default_perms_for[from] -= check_perm(perm)
  end
end
sql_adds(tables) click to toggle source
# File lib/tasks/db.rb, line 563
def sql_adds(tables)
  a = gen_pg_adapter()
  tables.each do |tn|
    tbl = $schema_tables[tn]
    a.create_table tbl.name, :force => true do |t|
      tbl.columns.each do |c|
        next if c.type=='integer' && c.name=='id'
        t.send(c.type.to_sym, *[c.name, c.opts])
      end
    end
  end
  if !$tmp_to_run.empty?
    $tmp_to_run.unshift("\n-- add tables")
    if !$check_perms_for.empty?
      $tmp_to_run << "REVOKE ALL ON #{(tables.map {|t| escape_table(t)}).join(',')} FROM #{$check_perms_for.to_a.join(',')}"
    end
  end
  return $tmp_to_run.clone
end
sql_drops(tables) click to toggle source
# File lib/tasks/db.rb, line 551
def sql_drops(tables)
  to_run = []
  tables.each do |tbl|
    sql = "DROP TABLE #{escape_table(tbl)}"
    to_run.append sql
  end
  if !to_run.empty?
    to_run.unshift("\n-- remove tables")
  end
  return to_run
end
sql_renames(renames) click to toggle source
# File lib/tasks/db.rb, line 539
def sql_renames(renames)
  pg_a = gen_pg_adapter()
  renames.each do |k,v|
    pg_a.rename_table(k, v)
  end
  to_run = $tmp_to_run.clone
  if !to_run.empty?
    to_run.unshift("\n-- rename tables")
  end
  return to_run
end