A script to help us pass our DB exam. Created mostly by d0ivanov with a little bit of help from me.

solver.rb

#====================================================================================
#==============================HERE BE DRAGONS=======================================
#
#
#                                                 __----~~~~~~~~~~~------___
#                                      .  .   ~~//====......          __--~ ~~
#                      -.            \_|//     |||\\  ~~~~~~::::... /~
#                   ___-==_       _-~o~  \/    |||  \\            _/~~-
#           __---~~~.==~||\=_    -_--~/_-~|-   |\\   \\        _/~
#       _-~~     .=~    |  \\-_    '-~7  /-   /  ||    \      /
#     .~       .~       |   \\ -_    /  /-   /   ||      \   /
#    /  ____  /         |     \\ ~-_/  /|- _/   .||       \ /
#    |~~    ~~|--~~~~--_ \     ~==-/   | \~--===~~        .\
#             '         ~-|      /|    |-~\~~       __--~~
#                         |-~~-_/ |    |   ~\_   _-~            /\
#                              /  \     \__   \/~                \__
#                          _--~ _/ | .-~~____--~-/                  ~~==.
#                         ((->/~   '.|||' -_|    ~~-/ ,              . _||
#                                    -_     ~\      ~~---l__i__i__i--~~_/
#                                    _-~-__   ~)  \--______________--~~
#                                  //.-~~~-~_--~- |-------~~~~~~~~
#                                         //.-~~~--\
#
#
#
#====================================================================================
# WARNING! "Useless OO in your face" follows.
#====================================================================================

module Solution
  USERNAME = ""
  PASSWORD = ""
  class Task
    attr_accessor :tables

    def initialize
      @problem = ARGF.read
      @tables = []
    end

    def parse
      @problem.each_line do |line|
        if (match = parse_table line)
          @tables << Table.new(match["table"])
        end
        if (match = parse_column line)
          @tables.last.fields[match["name"]] = match["type"]
        end
        if (conn = parse_connections line)
          @tables.each do |table|
            if table.name == conn["ltable"]
              table.connections = conn
            end
          end
        end
      end
      if match = parse_migration(@problem)
        @tables.find { |table| table.name == match["table"] }.migrate = match
      end
      self
    end

    private
      def parse_table string
        /table\s(?<table>\w+)/.match(string)
      end

      def parse_column definition
        /(?<name>\w+):(?<type>\w+)/.match(definition)
      end

      def parse_connections connections
        pattern = /(?<ltable>\w+)\shas\sa\s(?<type>
                   (many\sto\smany|one\sto\sone|many\sto\sone|one\sto\smany))\s
                   connection\sto\s(?<rtable>\w+)
                  /x
         pattern.match(connections)
      end

      def parse_migration string
        pattern = /Separate (?<table>\w+).*\n(?<part1>\w+) containing (?<part1_field>\w+)\n(?<part2>\w+)/
        pattern.match(string)
      end
  end

  class Database
    attr_accessor :name

    def initialize name
      @name = name
    end

    def create
      "CREATE DATABASE `#{@name}` DEFAULT CHARSET UTF8;\nUSE #{@name};"
    end

    def export name
      `mysqldump -u#{USERNAME} -p#{PASSWORD} #{@name} > #{name}.sql`
    end
  end

  class Table
    attr_accessor :name, :fields, :connections, :migrate

    def initialize name
      @name = name
      @fields = {}
      @connections = {}
      @migrate = false
    end

    def connections= conn
      @connections[conn["type"]] = [] if [email protected]_key? conn["type"]
      @connections[conn["type"]] << conn["rtable"]
    end

    def create
      sql = "CREATE TABLE `#{name.downcase}` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,"
      fields.each {|field, type| sql += "\n\t`#{field}` #{type.upcase},"}
      sql += "\n\tPRIMARY KEY (`id`)"
      sql += "\n);"
      sql
    end

    def connect
      sql = ""
      @connections.each do |connection, tables|
       tables.each do |table|
         sql += send("#{connection.gsub(" ", "_")}", table)
       end
      end
      sql
    end

    def insert rows = 2
      sql = "INSERT INTO #{@name.downcase} (`id`"
      @fields.keys.map do |field|
        sql += ",`#{field}`"
      end
      sql += ") VALUES\n\t"
      rows.times do  |i|
        sql += "(''"
        @fields.keys.count.times do
          sql += ",''"
        end
        sql += "),\n\t" if i != rows - 1
        sql += ");" if i == rows - 1
      end
      sql
    end

    def migrate
      return "" unless match = @migrate
      id = "`id` INT UNSIGNED NOT NULL AUTO_INCREMENT"
      sql = "CREATE TABLE #{match["part1"]} (#{id}, `#{match["part1_field"]}` #{@fields[match["part1_field"]].upcase}, PRIMARY KEY (`id`));\n"
      other_fields = fields.reject { |field, _| field == match["part1_field"] }.to_a
      sql += "CREATE TABLE #{match["part2"]} (#{id}, "
      last_field = other_fields.last
      other_fields[0..-2].each do |field, type|
        sql += "`#{field}`" + " " + type.upcase + ", "
      end
      sql += "`" + last_field.first + "` " + last_field.last.upcase + ", PRIMARY KEY (`id`));\n"
      sql += "INSERT INTO #{match["part1"]} (`#{match["part1_field"]}`) SELECT `#{match["part1_field"]}` FROM #{name};\n"
      sql += "INSERT INTO #{match["part2"]} ("
      other_fields[0..-2].each do |field, _|
        sql += "`" + field + "`, "
      end
      sql += "`" + last_field.first + "`) "
      sql += "SELECT "
      other_fields[0..-2].each do |field, _|
        sql += "`" + field + "`, "
      end
      sql += "`" + last_field.first + "` FROM #{name};\n"
      sql += "DROP TABLE #{name};\n"
    end

    private
      def many_to_many table
        "CREATE TABLE `#{table.downcase}_#{@name.downcase}` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `#{@name.downcase}_id` INT UNSIGNED NOT NULL,
            `#{table.downcase}_id` INT UNSIGNED NOT NULL,
            PRIMARY KEY(`id`)
         );"
      end

      def one_to_one table
        "ALTER TABLE `#{@name.downcase}` ADD FOREIGN KEY (id) REFERENCES #{table.downcase}(id);"
      end

      def many_to_one table
        "ALTER TABLE `#{@name.downcase}` ADD COLUMN #{table.downcase}_id INT UNSIGNED NOT NULL;"
      end

      def one_to_many table
        "ALTER TABLE `#{table.downcase}` ADD COLUMN #{@name.downcase}_id INT UNSIGNED NOT NULL;"
      end
  end
end

#Parse our task
tables = Solution::Task.new.parse.tables
db = Solution::Database.new("exam")

#Create a database with which we will be working
puts db.create

#Create the tables
tables.each do |table|
  puts table.create
end

#Crete table connections
tables.each do |table|
  puts table.connect
end

#Insert some data
tables.each do |table|
  puts table.insert
end

#Export all we have done
#db.export

# If Mitov change the migrations part of the exam, you can comment the following block to save your soul from exploding ruby code
tables.each do |table|
  puts table.migrate
end