Class: DBLeftovers::MysqlDatabaseInterface

Inherits:
GenericDatabaseInterface show all
Defined in:
lib/db_leftovers/mysql_database_interface.rb

Instance Method Summary collapse

Methods inherited from GenericDatabaseInterface

#execute_add_constraint, #execute_add_foreign_key, #execute_add_index, #execute_drop_constraint, #execute_sql

Constructor Details

#initialize(conn = nil, database_name = nil) ⇒ MysqlDatabaseInterface

Returns a new instance of MysqlDatabaseInterface.



5
6
7
8
 
# File 'lib/db_leftovers/mysql_database_interface.rb', line 5

def initialize(conn=nil, database_name=nil)
  @conn = conn || ActiveRecord::Base.connection
  @db_name = database_name || ActiveRecord::Base.configurations[Rails.env]['database']
end

Instance Method Details

#execute_drop_foreign_key(constraint_name, from_table, from_column) ⇒ Object 



78
79
80
 
# File 'lib/db_leftovers/mysql_database_interface.rb', line 78

def execute_drop_foreign_key(constraint_name, from_table, from_column)
  execute_sql %{ALTER TABLE #{from_table} DROP FOREIGN KEY #{constraint_name}}
end

#execute_drop_index(table_name, index_name) ⇒ Object 



71
72
73
74
75
76
 
# File 'lib/db_leftovers/mysql_database_interface.rb', line 71

def execute_drop_index(table_name, index_name)
  sql = <<-EOQ
      DROP INDEX #{index_name} ON #{table_name}
  EOQ
  execute_sql(sql)
end

#lookup_all_constraints ⇒ Object 



65
66
67
68
69
 
# File 'lib/db_leftovers/mysql_database_interface.rb', line 65

def lookup_all_constraints
  # TODO: Constrain it to the database for the current Rails project:
  # MySQL doesn't support CHECK constraints:
  return []
end

#lookup_all_foreign_keys ⇒ Object 



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

def lookup_all_foreign_keys
  # TODO: Support multi-column foreign keys:
  ret = {}
  sql = <<-EOQ
      SELECT  c.constraint_name,
              c.table_name,
              k.column_name,
              c.referenced_table_name,
              k.referenced_column_name,
              c.delete_rule
      FROM    information_schema.referential_constraints c,
              information_schema.key_column_usage k
      WHERE   c.constraint_schema = k.constraint_schema
      AND     c.constraint_name = k.constraint_name
      AND     c.constraint_schema IN (#{target_databases_quoted})
  EOQ
  @conn.select_rows(sql).each do |constr_name, from_table, from_column, to_table, to_column, del_type|
    del_type = case del_type
               when 'RESTRICT'; nil
               when 'CASCADE'; :cascade
               when 'SET NULL'; :set_null
               else; raise "Unknown del type: #{del_type}"
               end
    ret[constr_name] = ForeignKey.new(constr_name, from_table, from_column, to_table, to_column, :on_delete => del_type)
  end
  return ret
end

#lookup_all_indexes ⇒ Object 



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/db_leftovers/mysql_database_interface.rb', line 10

def lookup_all_indexes
  ret = {}
  @conn.select_values("SHOW TABLES").each do |table_name|
    indexes = {}
    # Careful, MySQL automatically creates indexes whenever you define a foreign key.
    # Use our foreign key naming convention to ignore these:
    @conn.select_rows("SHOW INDEXES FROM #{table_name} WHERE key_name NOT LIKE 'fk_%'").each do |_, non_unique, key_name, seq_in_index, column_name, collation, cardinality, sub_part, packed, has_nulls, index_type, comment|
      unless key_name == 'PRIMARY'
        # Combine rows for multi-column indexes
        h = (indexes[key_name] ||= { unique: non_unique == 0, name: key_name, columns: {} })
        h[:columns][seq_in_index.to_i] = column_name
      end
    end

    indexes.each do |index_name, h|
      ret[index_name] = Index.new(
        table_name,
        h[:columns].sort.map{|k, v| v},
        unique: h[:unique],
        name: h[:name]
      )
    end
  end

  return ret
end