class Myq::Core
Public Class Methods
new(profile)
click to toggle source
# File lib/myq/core.rb, line 11 def initialize(profile) @profile = profile @client = Mysql2::Client.new(profile) end
Public Instance Methods
auto_create_table(table, hash)
click to toggle source
# 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()
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# File lib/myq/core.rb, line 90 def create_database_utf8(database) @client.xquery("CREATE DATABASE #{database} CHARACTER SET 'UTF8'") end
databases()
click to toggle source
# File lib/myq/core.rb, line 102 def databases query('show databases') end
dump(filepath = "
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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()
click to toggle source
# File lib/myq/core.rb, line 106 def processlist query('SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST') end
query(query)
click to toggle source
# 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)
click to toggle source
# 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, params)
click to toggle source
# File lib/myq/core.rb, line 190 def render_template(template_path = nil, output_template, format, params) 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 = "
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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
Private Instance Methods
parse_table(table_name, format)
click to toggle source
# File lib/myq/core.rb, line 245 def parse_table(table_name, format) return table_name if format.nil? eval("table_name.#{format}") end