H2からSolidbase用のXMLを生成するときに使ったやつ

ExportMigrationXML.scala

package gitbucket.core

import java.sql.{Connection, DriverManager}
import scala.collection.mutable
import scala.collection.mutable.ArrayBuffer

object ExportMigrationXML extends App {

  Class.forName("org.h2.Driver")
  val conn = DriverManager.getConnection("jdbc:h2:~/.gitbucket/data;MVCC=true", "sa", "sa")
  try {
    val tables = new StringBuilder()
    val indices = new StringBuilder()

    val meta = conn.getMetaData
    val rs1 = meta.getTables(null, null, null, Array("TABLE", "VIEW"))
    while(rs1.next()){
      val tableName = rs1.getString("TABLE_NAME")

      tables.append(s"""  <createTable tableName="${tableName}">""" + "\n")

      val rs2 = meta.getColumns(null, null, tableName, null)
      while(rs2.next()){
        val columnName = rs2.getString("COLUMN_NAME")
        val columnType = rs2.getString("TYPE_NAME")
        val columnSize = rs2.getString("COLUMN_SIZE")
        val nullable = rs2.getString("IS_NULLABLE")
        val autoIncrement = rs2.getString("IS_AUTOINCREMENT")

        val dataType = columnType match {
          case "CLOB" => "text"
          case "VARCHAR" => s"varchar(${columnSize})"
          case "INTEGER" => "int"
          case "BIGINT" => "int"
          case "TIMESTAMP" => "datetime"
          case "CHAR" => s"char(${6})"
          case "BOOLEAN" => "boolean"
        }


        tables.append(s"""    <column name="${columnName}" type="${dataType}" nullable="${if(nullable == "YES") "true" else "false"}"${if(autoIncrement == "YES") " autoIncrement=\"true\"" else ""}/>""" + "\n")

      }
      rs2.close()
      tables.append("  </createTable>\n")
      tables.append("\n")

      val primaryKey = getPrimaryKey(conn, tableName)
      primaryKey.foreach { key =>
        indices.append(s"""  <addPrimaryKey constraintName="${key.name}" tableName="${tableName}" columnNames="${key.columnNames.mkString(", ")}"/>""" + "\n")
      }

      val uniqueIndices = getUniqueIndex(conn, tableName)
      uniqueIndices.foreach { index =>
        indices.append(s"""  <addUniqueConstraint constraintName="${index.name}" tableName="${tableName}" columnNames="${index.columnNames.mkString(", ")}"/>""" + "\n")
      }

      val foreignKeys = getForeignKey(conn, tableName)
      foreignKeys.foreach { key =>
        indices.append(s"""  <addForeignKeyConstraint constraintName="${key.name}" baseTableName="${tableName}" baseColumnNames="${key.columnNames.mkString(", ")}" referencedTableName="${key.parentTableName}" referencedColumnNames="${key.parentColumnNames.mkString(", ")}"/>""" + "\n")
      }

      indices.append("\n")
    }
    rs1.close()

    println(tables.toString())
    println(indices.toString())
  } finally {
    conn.close
  }


  private def getPrimaryKey(conn: Connection, tableName: String): Option[PrimaryKey] = {
    val meta = conn.getMetaData
    val rs = meta.getPrimaryKeys(null, null, tableName)
    var name: String = null
    var columns = new mutable.HashMap[Int, String]()

    while(rs.next()){
      name = rs.getString("PK_NAME")
      columns += (rs.getInt("KEY_SEQ") -> rs.getString("COLUMN_NAME"))
    }
    rs.close()

    if(name != null){
      Some(PrimaryKey(name, columns.toSeq.sortBy(_._1).map(_._2)))
    } else {
      None
    }
  }

  private def getUniqueIndex(conn: Connection, tableName: String): Seq[UniqueIndex] = {
    val meta = conn.getMetaData
    val rs = meta.getIndexInfo(null, null, tableName, true, true)
    val map = new mutable.HashMap[String, ArrayBuffer[String]]()

    while(rs.next()){
      if(!rs.getString("INDEX_NAME").startsWith("PRIMARY_KEY_")){
        val indexName = rs.getString("INDEX_NAME")

        map.get(indexName) match {
          case Some(columns) => {
            columns += rs.getString("COLUMN_NAME")
          }
          case None => {
            map += (indexName -> ArrayBuffer(rs.getString("COLUMN_NAME")))
          }
        }
      }
    }
    rs.close()

    map.map { case (name, columns) =>
      UniqueIndex(name, columns.toSeq)
    }.toSeq
  }

  private def getForeignKey(conn: Connection, tableName: String): Seq[ForeignKey] = {
    val meta = conn.getMetaData
    val rs = meta.getImportedKeys(null, null, tableName)
    val map = new mutable.HashMap[String, mutable.HashMap[Int, (String, String, String)]]()

    while(rs.next()){
      val keyName = rs.getString("FK_NAME")
      map.get(keyName) match {
        case Some(key) =>
          key += (rs.getInt("KEY_SEQ") -> (rs.getString("FKCOLUMN_NAME"), rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")))
        case None => {
          map += (keyName -> mutable.HashMap(rs.getInt("KEY_SEQ") -> (rs.getString("FKCOLUMN_NAME"), rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME"))))
        }
      }
    }
    rs.close()

    map.map { case (name, columns) =>
      val sorted = columns.toSeq.sortBy(_._1)
      ForeignKey(name, sorted.map(_._2._1), sorted.head._2._2, sorted.map(_._2._3))
    }.toSeq
  }

  case class PrimaryKey(name: String, columnNames: Seq[String])
  case class UniqueIndex(name: String, columnNames: Seq[String])
  case class ForeignKey(name: String, columnNames: Seq[String], parentTableName: String, parentColumnNames: Seq[String])

}