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

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

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

commented Sep 5, 2018

Fixed, thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.