PHPStorm - Generate database fixture file for Integration Tests

XML-PHPUnit.xml.groovy

/*
 * Available context bindings:
 *   COLUMNS     List<DataColumn>
 *   ROWS        Iterable<DataRow>
 *   OUT         { append() }
 *   FORMATTER   { format(row, col); formatValue(Object, col) }
 *   TRANSPOSED  Boolean
 * plus ALL_COLUMNS, TABLE, DIALECT
 *
 * where:
 *   DataRow     { rowNumber(); first(); last(); data(): List<Object>; value(column): Object }
 *   DataColumn  { columnNumber(), name() }
 */


import java.util.regex.Pattern

NEWLINE = System.getProperty("line.separator")

def isNotSelection() {
  return ALL_COLUMNS.size() == COLUMNS.size()
}

pattern = Pattern.compile("[^\\w\\d]")

def escapeTag(name) {
  name = pattern.matcher(name).replaceAll("_")
  return name.isEmpty() || !Character.isLetter(name.charAt(0)) ? "_$name" : name
}

def printRow = { values, rowTag, namer, valueToString ->
  OUT.append("$NEWLINE      <$rowTag>$NEWLINE")
  values.eachWithIndex { it, index ->
    def tag = namer(it, index)
    def str = valueToString(it)
    def nullValue = ""

    if (str == "NULL") {
      OUT.append("      <field name=\"$tag\" xsi:nil=\"true\"/>$NEWLINE")
    }
    else {
      OUT.append("      <field name=\"$tag\">$str</field>$NEWLINE")
    }
  }
  OUT.append("    </$rowTag>")
}

def tableName = TABLE.getName()

if (isNotSelection()) {
  def databaseName = TABLE.getParent().getName()

  OUT.append("""<?xml version=\"1.0\"?>
<mysqldump xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">
  <database name=\"$databaseName\">$NEWLINE""")
}

OUT.append("""    <table_data name=\"$tableName\">""")

if (!TRANSPOSED) {
  ROWS.each { row -> printRow(COLUMNS, "row", {it, _ -> escapeTag(it.name())}) { FORMATTER.format(row, it) } }
}
else {
  def values = COLUMNS.collect { new ArrayList<String>() }
  ROWS.each { row -> COLUMNS.eachWithIndex { col, i -> values[i].add(FORMATTER.format(row, col)) } }
  values.eachWithIndex { it, index -> printRow(it, escapeTag(COLUMNS[index].name()), { _, i -> "row${i + 1}" }, { it }) }
}

OUT.append("$NEWLINE    </table_data>$NEWLINE")

if (isNotSelection()) {
  OUT.append("  </database>$NEWLINE</mysqldump>$NEWLINE")
}

1 - Install PHPStorm extractor script

Save the file at extractors directory. In my case was ~/Library/Preferences/PhpStorm2018.1/extensions/com.intellij.database/data/extractors

2 - Dumping data

3 - Coping only selected records and columns

4 - More info about PHPStorm Data Extractor

https://blog.jetbrains.com/datagrip/2017/11/08/export-data-in-any-way-with-intellij-based-ides/