add dia2pg.rb

1 files changed, 338 insertions, 0 deletions

diff --git a/dia2pg.rb b/dia2pg.rb
new file mode 100755
index 0000000..36c4299
--- /dev/null
+++ b/dia2pg.rb
@@ -0,0 +1,338 @@
+#! /usr/bin/env ruby
+# -*- coding: UTF-8 -*-
+
+#----------------------------------------------------------------------------
+#
+# File     : dia2pg.rb
+# Author   : Jérémy Zurcher <jeremy@asynk.ch>
+# Date     : 24/08/10
+# License  :
+#
+#  Permission is hereby granted, free of charge, to any person obtaining
+#  a copy of this software and associated documentation files (the
+#  "Software"), to deal in the Software without restriction, including
+#  without limitation the rights to use, copy, modify, merge, publish,
+#  distribute, sublicense, and/or sell copies of the Software, and to
+#  permit persons to whom the Software is furnished to do so, subject to
+#  the following conditions:
+#
+#  The above copyright notice and this permission notice shall be
+#  included in all copies or substantial portions of the Software.
+#
+#  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+#  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
+#  MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+#  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
+#  LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
+#  OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
+#  WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
+#
+#----------------------------------------------------------------------------
+#
+require 'ostruct'
+require 'optparse'
+#
+class OpenStruct
+    def to_h; @table end
+end
+#
+options = OpenStruct.new
+#
+options.verbose = false
+options.input = nil
+options.drop = false
+options.create = false
+options.odids = false
+options.user = 'postgres'
+options.tbl_prefix = nil
+#
+opts = OptionParser.new do |opts|
+    opts.banner = "Usage: #{$0} [options]"
+    opts.on( "-i",  "--input FILENAME",             "input file" )                          { |n| options.input = n }
+    opts.on( "-V",  "--verbose",                    "produce verbose output" )              { options.verbose = true }
+    opts.on( "-d",  "--drop",                       "generate drop table statements" )      { options.drop = true }
+    opts.on( "-c",  "--create",                     "generate create table statements" )    { options.create = true }
+    opts.on( "-u",  "--user USER",                  "set table owner's" )                   { |n| options.user = n }
+    opts.on( "-p",  "--prefix PREFIX",              "add a table prefix" )                  { |n| options.tbl_prefix = n }
+    opts.on( "-o",  "--oids",                       "set OIDS" )                            { options.oids = true }
+end
+#
+opts.parse!(ARGV)
+#
+if options.input.nil?
+    $stderr << "missing input file argument\n"
+    exit 1
+end
+#
+options.tbl_prefix = ( options.tbl_prefix ? options.tbl_prefix+(options.tbl_prefix[-1]=='_' ? '' : '_') : '' )
+#
+HEADER="\n -- generated with dia2pg\n"
+SEP="/**********************************************************************/"
+DATABASE = {}
+RELATIONS = []
+#
+class Table
+    #
+    def initialize opts
+        @opts = opts
+        @attributes = []
+        @attr_prefix = nil
+        @tbl_prefix = nil
+    end
+    #
+    attr_reader :attr_prefix
+    attr_writer :name
+    attr_accessor :id
+    #
+    def to_s
+        r = "Table #{@id} - #{@name} - #{@comment}\n"
+        @attributes.inject(r) do |r,a| r<<a.to_s end
+    end
+    #
+    def comment= c
+        @comment = c
+        if c=~/attr_prefix=(.*)$/
+            @attr_prefix = $1
+            @attr_prefix+='_' if @attr_prefix[-1]!='_'
+        end
+        if c=~/tbl_prefix=(.*)$/
+            @tbl_prefix = $1
+            @tbl_prefix+='_' if @tbl_prefix[-1]!='_'
+        end
+    end
+    #
+    def no_export?
+        return false if @comment.nil?
+        @comment=~/no_export=1/
+    end
+    #
+    def << a
+        @attributes << a
+    end
+    #
+    def attr n
+        @attributes[((n-10)/2)-1]
+    end
+    #
+    def name
+        ( @tbl_prefix ? @tbl_prefix+@name : @opts.tbl_prefix+@name )
+    end
+    #
+    def to_sql
+        r = ''
+        if @attr_prefix or @opts.tbl_prefix
+            r << "-- dia2pg"
+            r << " tbl_prefix=#{@opts.tbl_prefix}" if @opts.tbl_prefix
+            r << " attr_prefix=#{@attr_prefix}" if @attr_prefix
+            r << "\n"
+        end
+        r << "CREATE TABLE #{name}\n(\n"
+        pk = []
+        uq = { :all=>[] }
+        @attributes.each do |attr|
+            sql = attr.to_sql
+            next if sql.nil?
+            r << sql
+            pk << attr.real_name if attr.primary_key
+            if attr.unique and not attr.primary_key
+                if attr.comment=~/U./
+                    uq[attr.comment] ||= []
+                    uq[attr.comment]<< attr.real_name
+                else
+                    uq[:all] << attr.real_name
+                end
+            end
+        end
+        uq.each do |k,v| r << "    UNIQUE(#{v.join ','}),\n" unless v.empty? end
+        r << "    CONSTRAINT pk_#{name} PRIMARY KEY (#{pk.join ','})\n" if pk.length>0
+        r.sub!(/,\n$/,"\n")
+        r << ")\nWITH (\n    OIDS=#{@opts.oids ? 'TRUE' : 'FALSE'}\n);\n"
+        r << "ALTER TABLE #{name} OWNER TO #{@opts.user};\n"
+        r
+    end
+    #
+    def drop
+        puts "DROP TABLE IF EXISTS #{name} CASCADE;\n"
+    end
+    #
+end
+#
+class Attribute
+    #
+    def initialize tbl
+        @tbl = tbl
+    end
+    #
+    attr_reader :name, :primary_key, :unique, :comment
+    attr_writer :name, :type, :comment, :primary_key, :nullable, :unique
+    #
+    def to_s
+        "  # #{@name} - #{@type} - #{@comment} - #{@primary_key} - #{@nullable} - #{@unique}\n"
+    end
+    #
+    def foreign?
+        @type=~/foreign/
+    end
+    #
+    def no_rename?
+        return false if @comment.nil?
+        @comment=~/no_rename=1/
+    end
+    #
+    def no_prefix?
+        return false if @comment.nil?
+        @comment=~/no_prefix=1/
+    end
+    #
+    def no_export?
+        return false if @comment.nil?
+        @comment=~/no_export=1/
+    end
+    #
+    def real_name
+        if foreign? and not  no_rename?
+            rl = RELATIONS.find { |r| r.tbl_to==@tbl and r.attr_to.name==@name }
+            if rl.nil?
+                $stderr << "MISSING RELATION FOR FOREIGN KEY table:#{@tbl.name} attribute:#{@name}\n"
+                raise Exception .new "MISSING RELATION FOR FOREIGN KEY table:#{@tbl.name} attribute:#{@name}\n"
+            end
+            if rl.tbl_from==@tbl
+                ( (@tbl.attr_prefix.nil? or no_prefix?) ? @name : @tbl.attr_prefix+@name )
+            else
+                rl.attr_from.real_name
+            end
+        else
+            ( (@tbl.attr_prefix.nil? or no_prefix?) ? @name : @tbl.attr_prefix+@name )
+        end
+    end
+    #
+    def type
+        foreign? ? 'integer' : @type
+    end
+    #
+    def to_sql
+        return if no_export?
+        r = "    #{format "%-35s", real_name}"
+        if foreign?
+            rl = RELATIONS.find { |r| r.tbl_to==@tbl and r.attr_to.name==@name }
+            if rl.nil?
+                $stderr << "MISSING RELATION FOR FOREIGN KEY #{@tbl.name} #{@name}\n"
+                raise Excpetion.new "MISSING RELATION FOR FOREIGN KEY #{@tbl.name} #{@name}\n"
+            else
+                r << "integer REFERENCES #{rl.tbl_from.name}(#{rl.attr_from.real_name})"
+                r << " NOT NULL" if not @nullable
+            end
+        elsif @nullable
+            r << type
+        else
+            r << "#{type} NOT NULL"
+        end
+        r << ",\n"
+    end
+    #
+end
+#
+class Relation
+    #
+    attr_accessor :tbl_from, :attr_from, :m_from, :tbl_to, :attr_to, :m_to
+    #
+    def to_s
+        "  # #{tbl_from.name}.#{attr_from.name} (#{m_from}) => #{tbl_to.name}.#{attr_to.name} (#{m_to})"
+    end
+    #
+end
+#
+require 'zlib'
+require 'nokogiri'
+doc = Nokogiri::XML( Zlib::GzipReader.new( File.open(options.input) ).read )
+#
+doc.xpath('//dia:object[@type="Database - Table"]').each do |node|
+    tbl = Table.new options
+    tbl.id = node.xpath('@id').to_s
+    tbl.name = node.xpath('dia:attribute[@name="name"]/dia:string').first.content[1..-2]
+    $stderr << "# parse #{tbl.id} #{tbl.name}\n" if options.verbose
+    tbl.comment = node.xpath('dia:attribute[@name="comment"]/dia:string').first.content[1..-2]
+#    tbl.comment = $1.to_s
+    node.xpath('dia:attribute[@name="attributes"]').each do |el|
+        el.xpath('dia:composite[@type="table_attribute"]').each do |a|
+            attr = Attribute.new tbl
+            attr.name = a.xpath('dia:attribute[@name="name"]/dia:string').first.content[1..-2]
+            attr.type = a.xpath('dia:attribute[@name="type"]/dia:string').first.content[1..-2]
+            $stderr << "   # #{attr.name} #{attr.type}\n" if options.verbose
+            attr.comment = a.xpath('dia:attribute[@name="comment"]/dia:string').first.content[1..-2]
+            attr.primary_key = a.xpath('dia:attribute[@name="primary_key"]/dia:boolean/@val').to_s=='true'
+            attr.nullable = a.xpath('dia:attribute[@name="nullable"]/dia:boolean/@val').to_s=='true'
+            attr.unique = a.xpath('dia:attribute[@name="unique"]/dia:boolean/@val').to_s=='true'
+            tbl << attr
+        end
+    end
+    DATABASE[tbl.id]=tbl
+end
+# DATABASE REFERENCES
+doc.xpath('//dia:object[@type="Database - Reference"]').each do |node|
+    r = Relation.new options
+    r.m_from = node.xpath('dia:attribute[@name="start_point_desc"]/dia:string').first.content[1..-2]
+    r.m_to = node.xpath('dia:attribute[@name="end_point_desc"]/dia:string').first.content[1..-2]
+    r.tbl_from = DATABASE[node.xpath('dia:connections/dia:connection[@handle="0"]/@to').to_s]
+    r.tbl_to = DATABASE[node.xpath('dia:connections/dia:connection[@handle="1"]/@to').to_s]
+    next if r.tbl_from.nil? or r.tbl_to.nil?
+    r.attr_from = r.tbl_from.attr node.xpath('dia:connections/dia:connection[@handle="0"]/@connection').to_s.to_i
+    r.attr_to = r.tbl_to.attr node.xpath('dia:connections/dia:connection[@handle="1"]/@connection').to_s.to_i
+    $stderr << "# connection : #{r.to_s}\n" if options.verbose
+    RELATIONS << r
+end
+#
+doc.xpath('//dia:object[@type="UML - Association"]').each do |node|
+    r = Relation.new
+    node.xpath('dia:attribute[@name="name"]/dia:string').first.content[1..-2]
+    r.m_from = node.xpath('dia:attribute[@name="multipicity_a"]/dia:string').first.content[1..-2]
+    r.m_to = node.xpath('dia:attribute[@name="multipicity_b"]/dia:string').first.content[1..-2]
+    node.xpath('dia:attribute[@name="role_a"]/dia:string').first.content[1..-2]
+    node.xpath('dia:attribute[@name="role_b"]/dia:string').first.content[1..-2]
+    r.tbl_from = DATABASE[node.xpath('dia:connections/dia:connection[@handle="0"]/@to').to_s]
+    r.tbl_to = DATABASE[node.xpath('dia:connections/dia:connection[@handle="1"]/@to').to_s]
+    r.attr_from = r.tbl_from.attr node.xpath('dia:connections/dia:connection[@handle="0"]/@connection').to_s.to_i
+    r.attr_to = r.tbl_to.attr node.xpath('dia:connections/dia:connection[@handle="1"]/@connection').to_s.to_i
+    RELATIONS << r
+end
+#
+TBL_DEBS= {}
+#
+RELATIONS.each do |r|
+    TBL_DEBS[r.tbl_to.id] ||=[]
+    TBL_DEBS[r.tbl_to.id] << r.tbl_from.id
+end
+#
+FLUSHED = []
+#
+def flush tbl, l
+    if l==DATABASE.length
+        $stderr << "Can't resolve dependecy loop\n"
+        exit 1
+    end
+    return if FLUSHED.include? tbl.id or tbl.no_export?
+    if not TBL_DEBS[tbl.id].nil?
+        TBL_DEBS[tbl.id].each do |t|
+            # not self depend
+            flush( DATABASE[t], l+1) if not t==tbl.id
+        end
+    end
+    puts SEP
+    puts tbl.to_sql
+    puts ""
+    FLUSHED << tbl.id
+end
+#
+puts HEADER
+if options.drop
+    DATABASE.each do |k,tbl|
+        tbl.drop unless tbl.no_export?
+    end
+    puts ''
+end
+if options.create
+    DATABASE.each do |k,tbl|
+        flush tbl, 0
+    end
+end
+#