sync_sqldesigner_pos.rb

sync_sqldesigner_pos.rb

#!/usr/local/bin/ruby

#
# WWW SQL Designer を他の ER 図作成ツールと併用している場合に、
# 変更された DB から再インポートする度にテーブルの座標を直すのが面倒で作成したスクリプト。
# インポート後に実行すれば以前のバージョンで調整済みのダイアグラムの位置については同じになるので、
# 後は追加で変更した部分の座標のみ手で調整してやれば OK。
#

require 'rubygems'
require 'mysql2'
require 'rexml/document'

# db connection settings
DB_HOST     = '127.0.0.1'
DB_USER     = 'wwwsqldesigner'
DB_PASSWORD = 'wwwsqldesigner'
DB_DATABASE = 'wwwsqldesigner'
DB_ENCODING = 'UTF8'

if ARGV.count < 2
    print "usage: ruby sync_sqldesigner_pos.rb before_keyword after_keyword"
    exit 1
end

before_keyword = ARGV[0]
after_keyword  = ARGV[1]

# save file path
before_filename = "/home/akishin/src/ruby/data/#{before_keyword}.xml"
after_filename  = "/home/akishin/src/ruby/data/#{after_keyword}.xml"
merged_filename = "/home/akishin/src/ruby/data/#{before_keyword}_#{after_keyword}_merged.xml"

# load from db
def load_data(client, keyword)
    results = client.query("SELECT * FROM wwwsqldesigner WHERE keyword = '#{keyword}'")
    data = ''
    results.each do |row|
        data = row[:data]
    end
    return data
end

# save to db
def save_data(client, keyword, data)
    escaped = client.escape(data)
    results = client.query("UPDATE wwwsqldesigner SET data = '#{escaped}' WHERE keyword = '#{keyword}'")
end

# save to file
def save_file(data, filename)
    begin
        f = open(filename, 'w+')
        f.puts data
    ensure
        f.close unless f.nil?
    end
end

begin
    client = Mysql2::Client.new(:host => DB_HOST,
                                :username => DB_USER,
                                :password => DB_PASSWORD,
                                :database => DB_DATABASE,
                                :encoding => DB_ENCODING)
    client.query_options.merge!(:symbolize_keys => true)

    before_xml = load_data(client, before_keyword)
    after_xml  = load_data(client, after_keyword)

    save_file(before_xml, before_filename)
    save_file(after_xml, after_filename)

    xmldoc1 = REXML::Document.new(before_xml)
    xmldoc2 = REXML::Document.new(after_xml)

    # sync table position
    xmldoc1.elements.each('sql/table') { |t1|
        xmldoc2.elements.each('sql/table') { |t2|
            if t1.attributes['name'] == t2.attributes['name']
                t2.attributes['x'] = t1.attributes['x']
                t2.attributes['y'] = t1.attributes['y']
            end
        }
    }

    save_file(xmldoc2.to_s, merged_filename)

    # update database
    save_data(client, after_keyword, xmldoc2.to_s)

    print 'done.'
ensure
end