Class: Shiba::Connection::Mysql

Inherits:
Object
  • Object
show all
Defined in:
lib/shiba/connection/mysql.rb

Instance Method Summary collapse

Constructor Details

#initialize(hash) ⇒ Mysql

Returns a new instance of Mysql.



8
9
10
 
# File 'lib/shiba/connection/mysql.rb', line 8

def initialize(hash)
  @connection = Mysql2::Client.new(hash)
end

Instance Method Details

#analyze! ⇒ Object 



86
87
88
89
90
 
# File 'lib/shiba/connection/mysql.rb', line 86

def analyze!
  tables.each do |t|
    @connection.query("analyze table `#{t}`") rescue nil
  end
end

#count_indexes_by_table ⇒ Object 



92
93
94
95
96
97
98
99
100
101
 
# File 'lib/shiba/connection/mysql.rb', line 92

def count_indexes_by_table
  sql =<<-EOL
    select TABLE_NAME as table_name, count(*) as index_count
    from information_schema.statistics where table_schema = DATABASE()
    and seq_in_index = 1 and index_name not like 'fk_rails%'
    group by table_name order by index_count
  EOL

  @connection.query(sql).to_a
end

#each_column_size ⇒ Object 



29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/shiba/connection/mysql.rb', line 29

def each_column_size
  tables.each do |t|
    sql = <<-EOL
      select * from information_schema.columns where table_schema = DATABASE()
      and table_name = '#{t}'
    EOL
    columns = @connection.query(sql)
    col_hash = Hash[columns.map { |c| [c['COLUMN_NAME'], c] }]
    estimate_column_sizes(t, col_hash)

    col_hash.each do |c, h|
      yield(t, c, h['size'])
    end
  end
end

#estimate_column_sizes(table, hash) ⇒ Object 



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/shiba/connection/mysql.rb', line 45

def estimate_column_sizes(table, hash)
  columns_to_sample = []
  hash.each do |name, row|
    row['size'] = case row['DATA_TYPE']
    when 'tinyint', 'year', 'enum', 'bit'
      1
    when 'smallint'
      2
    when 'mediumint', 'date', 'time'
      3
    when 'int', 'decimal', 'float', 'timestamp'
      4
    when 'bigint', 'datetime', 'double'
      8
    else
      columns_to_sample << name
      nil
    end
  end

  return unless columns_to_sample.any?

  select_fields = columns_to_sample.map do |c|
    "AVG(LENGTH(`#{c}`)) as `#{c}`"
  end.join(', ')

  res = @connection.query("select #{select_fields}, count(*) as cnt from ( select * from `#{table}` limit 10000 ) as v").first
  if res['cnt'] == 0
    # muggles, no data. impossible to know actual size of blobs/varchars, safer to err on side of 0
    res.keys.each do |c|
      hash[c] && hash[c]['size'] = 0
    end
  else
    res.each do |k, v|
      hash[k] && hash[k]['size'] = v.to_i
    end
  end

  hash
end

#explain(sql) ⇒ Object 



103
104
105
106
107
108
 
# File 'lib/shiba/connection/mysql.rb', line 103

def explain(sql)
  rows = query("EXPLAIN FORMAT=JSON #{sql}").to_a
  explain = JSON.parse(rows.first['EXPLAIN'])
  warnings = query("show warnings").to_a
  [explain, parse_select_fields(warnings)]
end

#fetch_indexes ⇒ Object 



16
17
18
19
20
21
22
23
 
# File 'lib/shiba/connection/mysql.rb', line 16

def fetch_indexes
  sql =<<-EOL
    select * from information_schema.statistics where
    table_schema = DATABASE()
    order by table_name, if(index_name = 'PRIMARY', '', index_name), seq_in_index
  EOL
  @connection.query(sql)
end

#mysql? ⇒ Boolean

Returns:

  • (Boolean) 


116
117
118
 
# File 'lib/shiba/connection/mysql.rb', line 116

def mysql?
  true
end

#parse_select_fields(warnings) ⇒ Object 



110
111
112
113
114
 
# File 'lib/shiba/connection/mysql.rb', line 110

def parse_select_fields(warnings)
  normalized_sql = warnings.detect { |w| w["Code"] == 1003 }["Message"]

  Parsers::MysqlSelectFields.new(normalized_sql).parse_fields
end

#query(sql) ⇒ Object 



12
13
14
 
# File 'lib/shiba/connection/mysql.rb', line 12

def query(sql)
  @connection.query(sql)
end

#tables ⇒ Object 



25
26
27
 
# File 'lib/shiba/connection/mysql.rb', line 25

def tables
  @connection.query("show tables").map { |r| r.values.first }
end