Class: Myq::Core

Inherits:
Object
  • Object
show all
Defined in:
lib/myq/core.rb

Instance Method Summary collapse

Constructor Details

#initialize(profile) ⇒ Core

Returns a new instance of Core.



11
12
13
14
 
# File 'lib/myq/core.rb', line 11

def initialize(profile)
  @profile = profile
  @client = Mysql2::Client.new(profile)
end

Instance Method Details

#auto_create_table(table, hash) ⇒ Object 



82
83
84
85
86
87
88
 
# File 'lib/myq/core.rb', line 82

def auto_create_table(table, hash)
  res = table_info(table)
  if res.size == 0
    create_table_sql = %Q{CREATE TABLE #{table} (\n#{generate_create_table(hash)}\n)}
    query(create_table_sql)
  end
end

#console ⇒ Object 



206
207
208
209
210
211
212
213
214
215
 
# File 'lib/myq/core.rb', line 206

def console
  cmd = <<-EOF
  mysql -A\
  -u #{@profile['username']}\
  -h #{@profile['host']}\
  -p #{@profile['database']}\
  --password='#{@profile['password']}'
  EOF
  system(cmd)
end

#count(table, keys) ⇒ Object 



67
68
69
70
71
 
# File 'lib/myq/core.rb', line 67

def count(table, keys)
  select_query = keys.empty? ? '' : "#{keys.join(',')},"
  group_by_query = keys.empty? ? '' : "group by #{keys.join(',')}"
  query(%Q{select #{select_query} count(*) as count from #{table} #{group_by_query} order by count desc})
end

#create_database_utf8(database) ⇒ Object 



90
91
92
 
# File 'lib/myq/core.rb', line 90

def create_database_utf8(database)
  @client.xquery("CREATE DATABASE #{database} CHARACTER SET 'UTF8'")
end

#databases ⇒ Object 



102
103
104
 
# File 'lib/myq/core.rb', line 102

def databases
  query('show databases')
end

#dump(filepath = "#{@profile['database']}.dump") ⇒ Object 



217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/myq/core.rb', line 217

def dump(filepath = "#{@profile['database']}.dump")
  cmd = <<-EOF
  mysqldump \
  -u #{@profile['username']}\
  -h #{@profile['host']}\
  -p #{@profile['database']}\
  --password='#{@profile['password']}'\
  --default-character-set=binary\
   > #{filepath}
  EOF
  system(cmd)
end

#generate_alter(k, v) ⇒ Object 



142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/myq/core.rb', line 142

def generate_alter(k, v)
  if v.nil?
    "\`#{k}\` varchar(255)"
  elsif k =~ /^id$/i
    "\`id\` integer NOT NULL auto_increment PRIMARY KEY"
  elsif v.class == String
    to_time_or_nil(v).nil? ? "\`#{k}\` varchar(255)" : "\`#{k}\` datetime"
  elsif v.class == Fixnum
    "\`#{k}\` integer"
  elsif v.class == Array
    "\`#{k}\` text"
  elsif v.class == Hash
    "\`#{k}\` text"
  elsif v.respond_to?(:strftime)
    "\`#{k}\` datetime"
  end
end

#generate_create_table(hash) ⇒ Object 



134
135
136
137
138
139
140
 
# File 'lib/myq/core.rb', line 134

def generate_create_table(hash)
  results = hash.map do |k, v|
    generate_alter(k, v)
  end
  results << 'id integer NOT NULL auto_increment PRIMARY KEY' unless hash.keys.map(&:downcase).include?('id')
  results.compact.join(",\n")
end

#generate_value(record, column) ⇒ Object 



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/myq/core.rb', line 114

def generate_value(record, column)
  value = record[column['COLUMN_NAME']]
  return 'NULL' if value.nil?
  if value.class == String
    # is_time_format
    time = to_time_or_nil(value)
    if !time.nil?
      return "'" + time.strftime('%Y-%m-%d %H:%M:%S') + "'"
    end
    max_length = column['CHARACTER_MAXIMUM_LENGTH']
    return "'" + Mysql2::Client.escape(value) + "'" if max_length.nil?
    value = value.size > max_length ? value.slice(0, max_length) : value
    return "'" + Mysql2::Client.escape(value) + "'"
  elsif value.class == Hash
    escaped = Mysql2::Client.escape(Yajl::Encoder.encode(value))
    return "'" + escaped + "'"
  end
  "'#{value}'"
end

#make_bulk_insert_sql(table, data, update_columns) ⇒ Object 



16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/myq/core.rb', line 16

def make_bulk_insert_sql(table, data, update_columns)
  first = data.class == Array ? data.first : data
  auto_create_table(table, first)
  columns = table_info(table).to_a
  values_array = []
  if data.class == Array
    data.each do |record|
      values_array << to_value_string(columns, record)
    end
  else
    values_array << to_value_string(columns, data)
  end
  sql = %Q{
  INSERT INTO #{table}
  (#{columns.map { |column| "\`" + column['COLUMN_NAME'] + "\`" }.join(',')})
  VALUES
  #{values_array.join(",\n")}
  #{make_duplicate_key_update_sql(update_columns)}
  }
  sql
end

#make_duplicate_key_update_sql(update_columns) ⇒ Object 



38
39
40
41
42
43
44
45
 
# File 'lib/myq/core.rb', line 38

def make_duplicate_key_update_sql(update_columns)
  return "" if update_columns.empty?
  updates = []
  update_columns.each do |update_column|
    updates << "#{update_column}=VALUES(\`#{update_column}\`)"
  end
  "ON DUPLICATE KEY UPDATE " + updates.join(', ')
end

#parse_json(buffer) ⇒ Object 



170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/myq/core.rb', line 170

def parse_json(buffer)
  begin
    data = Yajl::Parser.parse(buffer)
  rescue => e
    data = []
    buffer.split("\n").each do |line|
      data << Yajl::Parser.parse(line)
    end
  end
  data
end

#processlist ⇒ Object 



106
107
108
 
# File 'lib/myq/core.rb', line 106

def processlist
  query('SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST')
end

#query(query) ⇒ Object 



54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/myq/core.rb', line 54

def query(query)
  result = []
  query.split(';').each do |sql|
    next if sql.blank?
    res = @client.xquery(sql)
    next if res.nil?
    res.each do |record|
      result << record
    end
  end
  result
end

#query_single(query) ⇒ Object 



73
74
75
76
77
78
79
80
 
# File 'lib/myq/core.rb', line 73

def query_single(query)
  begin
    res = @client.xquery(query)
  rescue => e
    puts "\n#{e.message}\n#{e.backtrace.join("\n")}"
    puts query
  end
end

#render_template(template_path = nil, output_template, format) ⇒ Object 



190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/myq/core.rb', line 190

def render_template(template_path = nil, output_template, format)
  system 'mkdir -p ' + File.dirname(output_template)
  database = @profile['database']
  tables = @client.xquery("SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '#{database}'")
  tables.each do |table|
    table_name = table['TABLE_NAME']
    sql = %Q{SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '#{database}' and TABLE_NAME = '#{table_name}'}
    columns = @client.xquery(sql)
    filepath = sprintf(output_template, parse_table(table_name, format))
    filewrite = File.open(filepath,'w')
    filewrite.puts ERB.new(File.read(template_path)).result(binding)
    filewrite.close
    puts "create #{table_name} => #{filepath}"
  end
end

#restore(filepath = "#{@profile['database']}.dump") ⇒ Object 



230
231
232
233
234
235
236
237
238
239
240
241
 
# File 'lib/myq/core.rb', line 230

def restore(filepath = "#{@profile['database']}.dump")
  cmd = <<-EOF
  mysql -A\
  -u #{@profile['username']}\
  -h #{@profile['host']}\
  -p #{@profile['database']}\
  --password='#{@profile['password']}'\
  --default-character-set=binary\
  -f < #{filepath}
  EOF
  system(cmd)
end

#table_info(table) ⇒ Object 



110
111
112
 
# File 'lib/myq/core.rb', line 110

def table_info(table)
  @client.xquery("SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '#{table}'")
end

#tables(table_name) ⇒ Object 



94
95
96
97
98
99
100
 
# File 'lib/myq/core.rb', line 94

def tables(table_name)
  if table_name.nil?
    query('SELECT * FROM INFORMATION_SCHEMA.TABLES')
  else
    query("show full columns from #{table_name}")
  end
end

#to_time_or_nil(value) ⇒ Object 



160
161
162
163
164
165
166
167
168
 
# File 'lib/myq/core.rb', line 160

def to_time_or_nil(value)
  return nil if value.slice(0, 4) !~ /^[0-9][0-9][0-9][0-9]/
  begin
    time = value.to_time
    time.to_i >= 0 ? time : nil
  rescue => e
    nil
  end
end

#to_value_string(columns, record) ⇒ Object 



47
48
49
50
51
52
 
# File 'lib/myq/core.rb', line 47

def to_value_string(columns, record)
  values_string = columns.map do |column|
    generate_value(record, column)
  end.join(',')
  '(' + values_string + ')'
end

#variables(like = nil) ⇒ Object 



182
183
184
185
186
187
188
 
# File 'lib/myq/core.rb', line 182

def variables(like = nil)
  if like.nil?
    query('SHOW VARIABLES')
  else
    query("SHOW VARIABLES LIKE '%#{like}%'")
  end
end