Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DataGrip extractors
/*
* 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() }
*/
SEPARATOR = ","
QUOTE = "'"
first = true
ROWS.each { row ->
COLUMNS.each { column ->
value = FORMATTER.format(row, column)
def q = !value.isNumber()
OUT.append(first ? "" : SEPARATOR)
.append(q ? QUOTE : "")
.append(value.replace(QUOTE, QUOTE + QUOTE))
.append(q ? QUOTE : "")
first = false
}
}
/*
* 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 static com.intellij.openapi.util.text.StringUtil.escapeStringCharacters as escapeStr
NEWLINE = System.getProperty("line.separator")
INDENT = " "
def printJSON(level, col, o) {
switch (o) {
case null: OUT.append("null"); break
case Number: OUT.append(FORMATTER.formatValue(o, col)); break
case Boolean: OUT.append("$o"); break
case String: OUT.append("\"${escapeStr(o)}\""); break
case Tuple: printJSON(level, o[0], o[1]); break
case Map:
OUT.append("{")
o.entrySet().eachWithIndex { entry, i ->
OUT.append("${i > 0 ? "," : ""}$NEWLINE${INDENT * (level + 1)}")
OUT.append("\"${escapeStr(entry.getKey().toString())}\"")
OUT.append(": ")
printJSON(level + 1, null, entry.getValue())
}
OUT.append("$NEWLINE${INDENT * level}}")
break
case Object[]:
case Iterable:
OUT.append("[")
def plain = true
o.eachWithIndex { item, i ->
plain = item == null || item instanceof Number || item instanceof Boolean || item instanceof String
if (plain) OUT.append(i > 0 ? ", " : "")
else OUT.append("${i > 0 ? "," : ""}$NEWLINE${INDENT * (level + 1)}")
printJSON(level + 1, null, item)
}
if (plain) OUT.append("]") else OUT.append("$NEWLINE${INDENT * level}]")
break
default:
if (col != null) printJSON(level, null, FORMATTER.formatValue(o, col))
else OUT.append("$o")
break
}
}
printJSON(0, null, ROWS.transform { row ->
def map = new LinkedHashMap<String, String>()
COLUMNS.each { col ->
def val = row.value(col)
map.put(col.name(), new Tuple(col, val))
}
map
})
SEP = ", "
QUOTE = "\'"
NEWLINE = System.getProperty("line.separator")
begin = true
def record(columns, dataRow) {
if (begin) {
OUT.append("INSERT INTO ")
if (TABLE == null) OUT.append("MY_TABLE")
else OUT.append(TABLE.getParent().getName()).append(".").append(TABLE.getName())
OUT.append(" (")
columns.eachWithIndex { column, idx ->
OUT.append(column.name()).append(idx != columns.size() - 1 ? SEP : "")
}
OUT.append(")").append(NEWLINE)
OUT.append("VALUES").append(" (")
begin = false
}
else {
OUT.append(",").append(NEWLINE)
OUT.append(" (")
}
columns.eachWithIndex { column, idx ->
def skipQuote = dataRow.value(column).toString().isNumber() || dataRow.value(column) == null
def stringValue = FORMATTER.format(dataRow, column)
if (DIALECT.getFamilyId().isMysql()) stringValue = stringValue.replace("\\", "\\\\")
OUT.append(skipQuote ? "": QUOTE).append(stringValue.replace(QUOTE, QUOTE + QUOTE))
.append(skipQuote ? "": QUOTE).append(idx != columns.size() - 1 ? SEP : "")
}
OUT.append(")")
}
ROWS.each { row -> record(COLUMNS, row) }
@TheAndroidGuy

This comment has been minimized.

Copy link

@TheAndroidGuy TheAndroidGuy commented Feb 3, 2018

Hello, thanks a lot for the scripts, but the second script does not work out of the box. I had to fix the quotes. Just saying, if someone else has the same problem.

@leo-from-spb

This comment has been minimized.

Copy link

@leo-from-spb leo-from-spb commented Feb 12, 2018

There are a bit mangled double quotes in the second script. Just replace them with usual ones. Also don't forget to replace the single one in the line 2.

@leo-from-spb

This comment has been minimized.

@moscas

This comment has been minimized.

Copy link
Owner Author

@moscas moscas commented Sep 5, 2018

Fixed, thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment