Building MySQL column definition with Scala

Yesterday I wrote about differences between Alter Column commands in MySQL and PostgreSQL. To change even a single parameter of a column, MySQL requires the whole definition of column to be specified. I decided to write the definition building login in Scala not SQL.

Scala’s pattern matching comes in handy and after a long session of trial and error I had a flexible method to create column definitions for any table. I have also learned about a dozen ways how to not solve this problem.

// pseudo code for val column = Query("SHOW FULL COLUMNS FROM `some_table`").head {
  case (key, value) =>
    key match {
      case "Field" => => s"`$x`").getOrElse("")
      case "Type" => => s"TYPE $x").getOrElse("")
      case "Collation" => => s"CHARACTER SET $x").getOrElse("")
      case "Null" if value == Some("NO") => "NOT NULL"
      case "Null" if value == Some("YES") => "DEFAULT NULL"
      case "Default" => => s"DEFAULT $x").getOrElse("")
      case "Comment" => => s"COMMENT $x").getOrElse("")
      case _ => ""
}.filter(_.nonEmpty).mkString(" ")

The code yields a column definition that can be later used to create an ALTER COLUMN statement.

`title` TYPE varchar(200) CHARACTER SET latin1_swedish_ci NOT NULL

A side note: ListMap must be used to preserve the order of a map. The order is important in my case, because otherwise it would result in a invalid column definition.