Ruby script to generate rudimentary Doctrine 2 Entity files from SQL "CREATE TABLE" queries.

sql2ent.rb

#!/Users/mnutter/.rvm/rubies/ruby-2.0.0-p247/bin/ruby
 
def toCamel(s)
    while s =~ /([^_]+)_(.)(.*)$/ do
        s = $1 + $2.upcase + $3
    end
    return s
end
 
def toSnake(s)
    s =~ /^(.)(.*)$/;
    s = $1.downcase + $2
    while s =~ /^([^A-Z]*)([A-Z])(.*)/
        s = $1 + "_" + $2.downcase + $3
    end
    return s
end
 
def getSize(spec)
    if spec =~ /\((\d+)(,|\))/ then
        return $1
    end
    return ""
end
 
def nullable(spec)
    if spec.index(/NOT NULL/i) != nil then
        return "false"
    else
        return "true"
    end
end
 
def assert(spec)
    if spec.index(/NOT NULL/i) != nil then
        return "\t * @Assert\\NotBlank()\n"
    else
        return ""
    end
end
 
def primaryKey(spec)
    return spec.index(/auto_increment/i) != nil
end
 
def defBoolean(snakeName, spec)
    puts "BOOL: #{snakeName} >> #{spec}"
    camelName = toCamel(snakeName)
    return <<-END
    /**
     * @var boolean $#{camelName}
     *
     * @ORM\\Column(name="#{snakeName}", type="boolean")
#{ assert(spec) }    */
    protected $#{camelName};
    END
end
 
def defInt(snakeName, spec)
    puts "INT: #{snakeName} >> #{spec}"
    camelName = toCamel(snakeName)
    if primaryKey(spec) then
        return <<-END.gsub(/^\t/, "")
    /**
     * @var integer $#{camelName}
     *
     * @ORM\\Column(name="#{snakeName}", type="bigint", nullable=false)
     * @ORM\\Id
     * @ORM\\GeneratedValue(strategy="IDENTITY")
     */
    protected $#{snakeName};
    END
    else
        return <<-END
    /**
     * @var integer $#{camelName}
     * 
     * @ORM\\Column(name="#{snakeName}", type="integer", nullable=#{ nullable(spec) })
#{ assert(spec) }    */
    protected $#{camelName};
        END
    end
end
 
def defString(snakeName, spec)
    puts "STRING: #{snakeName} >> #{spec}"
    camelName = toCamel(snakeName)
    size = getSize(spec)
    if size != "" then
        size = ", length=#{size}"
    end
    return <<-END
    /**
     * @var string $#{camelName}
     *
     * @ORM\\Column(name="#{snakeName}", type="string"#{size}, nullable=#{ nullable(spec) })
#{ assert(spec) }    */
    protected $#{camelName};
    END
end
 
def defText(snakeName, spec)
    puts "TEXT: #{snakeName} >> #{spec}"
    camelName = toCamel(snakeName)
    return <<-END
    /**
     * @var string $#{camelName}
     *
     * @ORM\\Column(name="#{snakeName}", type="text", nullable=#{ nullable(spec) })
#{ assert(spec) }    */
    protected $#{camelName};
    END
end
 
def defDate(snakeName, spec)
    puts "DATE: #{snakeName} >> #{spec}"
    camelName = toCamel(snakeName)
    return <<-END
    /**
     * @var datetime $#{camelName}
     *
     * @ORM\\Column(name="#{snakeName}", type="datetime", nullable=#{ nullable(spec) })
#{ assert(spec) }    */
    protected $#{camelName};
    END
end
 
def defDecimal(snakeName, spec)
    puts "DECIMAL: #{snakeName} >> #{spec}"
    camelName = toCamel(snakeName)
    precScale = ""
    if spec =~ /\((\d+)\s*,\s*(\d+)\)/ then
        precScale = ", precision=#{$1}, scale=#{$2}"
    end
    return <<-END
    /**
     * @var decimal $#{camelName}
     *
     * @ORM\\Column(name="#{snakeName}", type="decimal"#{ precScale }, nullable=#{ nullable(spec) })
#{ assert(spec) }    */
    protected $#{camelName};
    END
end
 
def gettersAndSetters(camelName, type, spec)
    funcPart = toCamel("et_" + camelName)
    if primaryKey(spec) == false then
        setter = <<-END.gsub(/^\t/, "")
    /**
     * Set #{ camelName }
     *
     * @param #{type} $#{ camelName }
     */
    public function s#{ funcPart }($#{ camelName }) 
    {
        $this->#{ camelName } = $#{ camelName };
        return $this;
    }

        END
    else
        setter = ""
    end
    getter = <<-END.gsub(/^\t/, "")
    /**
     * Get #{ camelName }
     *
     * @return string 
     */
    public function g#{ funcPart }() 
    {
        return $this->#{ camelName };
    }
    END
    return setter + getter
end
 
ARGV.each do |f|
    vars = Array.new
    gsetters = Array.new
    IO.foreach(f) { |line|
        line = line.chomp
        if line =~ /^\s*\`([^`]+)\` ([a-z0-9_]+)(.*)$/ then
            puts line
            genericType = "int" # default value
            case $2
            when "tinyint" then
                if getSize($3) == "1" then
                    vars << defBoolean($1, $3) + "\n"
                    genericType = "bool"
                else
                    vars << defInt($1, $3) + "\n"
                end
            when "int","smallint","mediumint" then
                vars << defInt($1, $3) + "\n"
            when "char","varchar","text", "mediumtext", "longtext", "enum" then
                vars << defString($1, $3) + "\n"
                genericType = "string"
            when "date","time","datetime" then
                vars << defDate($1, $3) + "\n"
                genericType = "datetime"
            when "decimal", "double" then
                vars << defDecimal($1, $3) + "\n"
                genericType = "double"
            when "tinyblob", "mediumblob", "blob" then
                vars << defText($1, $3)
                genericType = "text"
            else
                vars << "//  !!!!!!!! unknown line: " + line + "\n"
                genericType = ""
            end
            gsetters << gettersAndSetters(toCamel($1), genericType, $3) + "\n"
            puts ""
        end
    }
    o = File.new(f.gsub(/sql$/, "php"), "w")
    className = File.basename(f).gsub(/\.sql$/, "")
    o.puts <<-END.gsub(/^\t/, "")
    <?php
    namespace ....FIXME....\\Entity;
 
    use Doctrine\\ORM\\Mapping as ORM;
    use Doctrine\\Common\\Collections\\ArrayCollection;
    use Symfony\\Component\\Validator\\Constraints as Assert;
 
    /**
     * ....FIXME....\\Entity\\#{ className }
     *
     * @ORM\\Table("#{ toSnake(className) }")
     * @ORM\\Entity(repositoryClass="....FIXME....\\Entity\\#{ className }Repository")
     */
    class #{ className } extends BaseEntity 
    {
 
    END
    o.puts vars
    o.puts gsetters
    o.puts "}\n"
    o.close
    puts "Wrote " + o.path
end