IntelliJ OracleDB Merge Statements Data Extractor based off SQL-Insert-Statements.sql.groovy

SQL-Merge-Statements.sql.groovy

import java.util.stream.Collectors

/*
 * 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     = "\'"
STRING_PREFIX = DIALECT.getDbms().isMicrosoft() ? "N" : ""
NEWLINE   = System.getProperty("line.separator")
KEYWORDS_LOWERCASE = com.intellij.database.util.DbSqlUtil.areKeywordsLowerCase(PROJECT)
KW_NULL = KEYWORDS_LOWERCASE ? "null" : "NULL"


def record(columns, dataRow) {
    TABLE_NAME = TABLE == null ? "MY_TABLE" : TABLE
    OUT.append("MERGE INTO ").append(TABLE_NAME).append(NEWLINE)
    OUT.append("USING (SELECT ")
    ALL_COLUMNS.eachWithIndex { column, idx -> // #1 select to upsert all columns from a table
        appendValue(dataRow, column, idx, ALL_COLUMNS)
        OUT.append("  ").append(column.name())
        appendCharacter(SEP, idx, ALL_COLUMNS)
    }
    OUT.append(" FROM DUAL) upsert").append(NEWLINE) // #2 alias the source of the condition/update/insert
    OUT.append("ON (")
    columns.eachWithIndex { column, idx -> // #3 upsert on condition created from selected columns
        OUT.append("(").append(TABLE_NAME).append(".").append(column.name()).append(" = ")
                .append("upsert.").append(column.name()).append(" OR (") // #4 handle nulls in the condition
                .append(TABLE_NAME).append(".").append(column.name()).append(" IS NULL AND ")
                .append("upsert.").append(column.name()).append(" IS NULL))")
        appendCharacter(' AND ', idx, columns)
    }
    OUT.append(")").append(NEWLINE)
    OUT.append(ALL_COLUMNS.stream() // #4 for the update statement, remove columns from the ON clause
            .filter(column -> columns.stream().noneMatch(selectedColumn -> selectedColumn.name().equals(column.name())))
            .map(nonOnReferencedColumn -> nonOnReferencedColumn.name() + " = upsert." + nonOnReferencedColumn.name())
            .reduce((updateColumn, updateColumn2) -> updateColumn + SEP + updateColumn2)
            .map(columnUpdates -> "WHEN MATCHED THEN UPDATE SET " + columnUpdates + NEWLINE)
            .orElse(""))
    OUT.append("WHEN NOT MATCHED THEN INSERT (")
    ALL_COLUMNS.eachWithIndex { column, idx ->
        OUT.append(column.name())
        appendCharacter(SEP, idx, ALL_COLUMNS)
    }
    OUT.append(") VALUES (")
    ALL_COLUMNS.eachWithIndex { column, idx ->
        OUT.append("upsert.").append(column.name())
        appendCharacter(SEP, idx, ALL_COLUMNS)
    }
    OUT.append(");").append(NEWLINE)
}

private void appendValue(dataRow, column, idx, columns) {
    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(isStringLiteral ? stringValue.replace(QUOTE, QUOTE + QUOTE) : stringValue)
            .append(isStringLiteral ? QUOTE : "")
}

private void appendCharacter(character, idx, columns) {
    OUT.append(idx != columns.size() - 1 ? character : "")
}

ROWS.each { row -> record(COLUMNS, row) }