Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/usr/bin/ruby
require 'rubygems'
require 'mysql'
require 'optparse'
options = {
:database => '',
:host=> 'localhost',
:create=> false,
:count=> false,
:index=> false,
:port=> 3306,
:user=> 'root',
:password => '',
}
begin
OptionParser.new do |opt|
opt.banner = "Usage: #{File.basename($0)} [Option]"
opt.on("-d","--database String","database name") { |o|
options[:database] = o
}
opt.on("-H","--hostname String","hostname. default: localhost") {|o|
options[:host] = o
}
opt.on("-n","--number","count record number. caution! don't use ovarload") {
options[:count] = true
}
opt.on("-c","--create","show create table list.") {
options[:create] = true
}
opt.on("-i","--index","show index from table list.") {
options[:index] = true
}
opt.on("-P","--port Integer","port. default: 3306.") {|o|
options[:port] = o
}
opt.on("-u","--user String","user name. default:root"){|o|
options[:user] = o
}
opt.on("-p","--password String","password. default:''"){|o|
options[:password] = o
}
opt.on("-h","--help","print this message and quit") {
puts opt.help
exit 0
}
opt.parse!(ARGV)
end
rescue OptionParser::ParseError => err
$stderr.puts(err.message)
exit 1
end
if options[:database] == nil or options[:database] == ""
$stderr.puts("no database name.")
exit 1
end
Mysql.init()
#接続開始
db = Mysql::new(options[:host], options[:user], options[:password],options[:database],options[:port])
#SQL文の実行
st = db.query("show tables")
st.each do |tbl|
puts "TABLE=#{tbl[0]}"
if options[:create]
st2 = db.query("show create table #{tbl[0]}")
st2.each do |attr|
printf "%s\n"%attr[1]
end
end
if options[:index]
st3 = db.query("show index from #{tbl[0]}")
key_name = {}
st3.each do |idx|
unless key_name[idx[2]]
key_name[idx[2]] = {}
key_name[idx[2]]["Cardinality"] = idx[6]
if idx[1] == 1
key_name[idx[2]]["Non_unique"] = "True"
else
key_name[idx[2]]["Non_unique"] = "False"
end
key_name[idx[2]]["Index_type"] = idx[10]
key_name[idx[2]]["Column_name"] = []
end
key_name[idx[2]]["Column_name"].push idx[4]
end
next if key_name.size == 0
key_data = []
key_name.each do |key,val|
if key == "PRIMARY"
val["Key_name"] = key
key_data.unshift val
else
val["Key_name"] = key
key_data.push val
end
end
key_data.each do |data|
puts "key_name=>#{data['Key_name']} Non_unique=>#{data['Non_unique']} "+
"Cardinality=>#{data['Cardinality']} "+
"Index_type=>#{data['Index_type']} Column_name=>#{data['Column_name'].join(',')}"
end
end
if options[:count]
st4 = db.query("select count(*) from #{tbl[0]}")
st4.each do |attr|
printf "total count=%d\n"%attr[0]
end
end
printf "\n\n"
end
#接続を切断する
db.close
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.