IntelliJ Database tools (or DataGrip) Data extractor: SQL-Create-Insert

SQL-Create-Insert.groovy

// https://www.jetbrains.com/help/idea/data-extractors.html
// https://www.jetbrains.com/help/datagrip/data-extractors.html

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

SEP = ", "
QUOTE     = "\'"
DOUBLEQUOTE = "\""
STRING_PREFIX = DIALECT.getDbms().isMicrosoft() ? "N" : ""
NEWLINE   = System.getProperty("line.separator")

KEYWORDS_LOWERCASE = com.intellij.database.util.DbSqlUtil.areKeywordsLowerCase(PROJECT)
KW_DROP_TABLE_IF_EXISTS = KEYWORDS_LOWERCASE ? "drop table if exists " : "DROP TABLE IF EXISTS "
KW_CREATE_TABLE = KEYWORDS_LOWERCASE ? "create table " : "CREATE TABLE "
KW_INSERT_INTO = KEYWORDS_LOWERCASE ? "insert into " : "INSERT INTO "
KW_VALUES = KEYWORDS_LOWERCASE ? "values" : "VALUES"
KW_NULL = KEYWORDS_LOWERCASE ? "null" : "NULL"

begin = true

if (TABLE == null) tableName = "MY_TABLE"
else tableName = TABLE.getParent().getName() + "_" + TABLE.getName()
def nowString = java.time.LocalDateTime.now().toString().replaceAll("[\\-T:.]", "_")
tableName = tableName + "_" + nowString

def appendTableName() {
    OUT.append(tableName)
}

def record(columns, dataRow) {

    if (begin) {
        OUT.append(KW_DROP_TABLE_IF_EXISTS)
        appendTableName()
        OUT.append(";").append(NEWLINE)

        OUT.append(KW_CREATE_TABLE)
        appendTableName()
        OUT.append(" (")

        columns.eachWithIndex { column, idx ->
            def value = dataRow.value(column)
            def typeName = FORMATTER.getTypeName(value, column)
            OUT.append(DOUBLEQUOTE)
              .append(column.name())
              .append(DOUBLEQUOTE)
              .append(" ")
              .append(typeName)
              .append(idx != columns.size() - 1 ? SEP : "")
        }

        OUT.append(");").append(NEWLINE)

        OUT.append(KW_INSERT_INTO)
        appendTableName()
        OUT.append(" (")

        columns.eachWithIndex { column, idx ->
            OUT.append(DOUBLEQUOTE).append(column.name()).append(DOUBLEQUOTE).append(idx != columns.size() - 1 ? SEP : "")
        }

        OUT.append(")").append(NEWLINE)
        OUT.append(KW_VALUES).append("  (")
        begin = false
    }
    else {
        OUT.append(",").append(NEWLINE)
        OUT.append("        (")
    }

    columns.eachWithIndex { column, idx ->
        def value = dataRow.value(column)
        def stringValue = value == null ? KW_NULL : FORMATTER.formatValue(value, column)
        def isStringLiteral = value != null && FORMATTER.isStringLiteral(value, column)
        if (isStringLiteral && DIALECT.getDbms().isMysql()) stringValue = stringValue.replace("\\", "\\\\")
        OUT.append(isStringLiteral ? (STRING_PREFIX + QUOTE) : "")
          .append(stringValue ? stringValue.replace(QUOTE, QUOTE + QUOTE) : stringValue)
          .append(isStringLiteral ? QUOTE : "")
          .append(idx != columns.size() - 1 ? SEP : "")
    }
    OUT.append(")")
}

ROWS.each { row -> record(COLUMNS, row) }
OUT.append(";")