詳細
MySQLのデータベースのカラム定義からDB定義書の元となる
TSVファイルを出力します。
サンプル
サンプルテーブルの定義
CREATE TABLE HOGE_USER ( ID nchar(6), UserID text, InputDate datetime, UpdateDate datetime )
サンプルデータ作成後にDescを試す
mysql> desc HOGE_USER; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | ID | char(6) | YES | | NULL | | | UserID | text | YES | | NULL | | | InputDate | datetime | YES | | NULL | | | UpdateDate | datetime | YES | | NULL | | +------------+----------+------+-----+---------+-------+
上記の内容をTSVに変換します。
変換用Rubyスクリプト
# encoding: utf-8 TABLE=<<EOS | ID | char(6) | YES | | NULL | | | UserID | text | YES | | NULL | | | InputDate | datetime | YES | | NULL | | | UpdateDate | datetime | YES | | NULL | | EOS class MySqlDescToCsvConverter def self.get_csv_table_from_desc(str) return str.gsub("\|", ",").gsub(" ", "").gsub(/^,/, "").gsub(/,$/, "") end end class TableCsvToExcelSpecConverter # MySQLの定義出力位置と呼び出しメソッドの関連ハッシュ DEF = {0=> "plane:name",1=>"split_type:type,type_seze",2=>"not_null:not_null",3=>"key:key",4=>"plane:default_value",5=>"plane:memo"} def convert_line(row) row_map = Hash.new("") row.split(",").each_with_index do |column, index| method_name, method_param = DEF[index].split(":") row_map = self.method(method_name).call(column, method_param, row_map) end row_map end def hash_list_to_tsv(hash_list) ret = "" hash_list.each do |row| ret << row.values.join("\t") ret << "\n" end ret end private def plane(value, param, row_map) row_map[param] = value row_map end def split_type(value, param,row_map) keys = param.split(",") if value =~ /\((\d*)\)/ row_map[keys[0]] = $` row_map[keys[1]] = $1 else row_map[keys[0]] = value row_map[keys[1]] = "" end row_map end def not_null(value, param, row_map) row_map[param] = value == "NO"? "Not Null" : "" row_map end def key(value, param, row_map) case value when "PRI" row_map[param] ="Primary Key" when "UNI" row_map[param] ="Unique Key" end row_map end end tmp_table = MySqlDescToCsvConverter.get_csv_table_from_desc TABLE converter = TableCsvToExcelSpecConverter.new rows = [] tmp_table.split("\n").each do |row| rows << converter.convert_line(row) end puts converter.hash_list_to_tsv(rows)
出力
TSVが出力されるのでExcelのDB定義書にそのまま貼り付け可能です。
ID char 6 NULL UserID text NULL InputDate datetime NULL UpdateDate datetime NULL