使用DataGrip编写Groovy提取工具
DataGrip extractors介绍
DataGrip在extractors目录中提供了许多复杂的脚本提取工具,可以通过切换脚本从而切换粘贴板或导出sql的格式,如果需要更加特殊的场景,也可以自己实现脚本,只需要在extractors目录下创建一个脚本即可(除groovy,也可用js实现)
常用的对象方法
DataGrip提供了一些对象与工具类
COLUMNS( List< DataColumn >): 代表字段集
DataColumn: columnNumber() 获取当前字段是第几个
name() 获取字段名
ROWS: 代表行
FORMATTER: 提供了一些方法,如:
formatValue() 格式化value
getTypeName() 获取字段类型
TABLE: 可以获取表的信息,如:
TABLE.getName() 获取表名
TABLE.getParent().getName() 获取库名
用Groovy实现同时导出Delete和Insert
效果如下:
import com.intellij.database.model.ObjectKind
/*
* 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")
EQUALS = " = "
NEWLINE = System.getProperty("line.separator")
KEYWORDS_LOWERCASE = com.intellij.database.util.DbSqlUtil.areKeywordsLowerCase(PROJECT)
KW_INSERT_INTO = KEYWORDS_LOWERCASE ? "insert into " : "INSERT INTO "
KW_DELETE_FROM = KEYWORDS_LOWERCASE ? "delete from " : "DELETE FROM "
KW_VALUES = KEYWORDS_LOWERCASE ? ") values (" : ") VALUES ("
KW_NULL = KEYWORDS_LOWERCASE ? "null" : "NULL"
KW_WHERE = KEYWORDS_LOWERCASE ? " where " : " WHERE "
def delete(columns, dataRow,primaryKeys) {
OUT.append(KW_DELETE_FROM)
if (TABLE == null) OUT.append("MY_TABLE")
else OUT.append(TABLE.getParent().getName()).append(".").append(TABLE.getName())
OUT.append(KW_WHERE)
def i = 0;
columns.eachWithIndex { column, idx ->
def value = dataRow.value(column)
def stringValue = value == null ? KW_NULL : FORMATTER.formatValue(value, column)
if(primaryKeys.contains(column.name())) {
i++;
OUT.append(column.name())
.append(EQUALS)
.append(stringValue)
if(i == primaryKeys.size()) {
OUT.append("")
.append(";")
.append(NEWLINE)
i = 0;
}else {
OUT.append(" AND ")
}
}
}
}
def update(columns, dataRow) {
OUT.append(KW_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(KW_VALUES)
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(isStringLiteral ? stringValue.replace(QUOTE, QUOTE + QUOTE) : stringValue)
.append(isStringLiteral ? QUOTE : "")
.append(idx != columns.size() - 1 ? SEP : "")
}
OUT.append(");").append(NEWLINE)
}
def record(columns,row) {
/*获取主键*/
primary = TABLE.getDasChildren(ObjectKind.KEY).find{ it.isPrimary() }
primaryKeys = primary.getColumnsRef().names()
if(primaryKeys){
delete(COLUMNS, row, primaryKeys)
} else {
OUT.append("No primary key configured")
}
update(COLUMNS, row)
}
ROWS.each { row -> record(COLUMNS, row) }
注: 获取主键需要导入com.intellij.database.model.ObjectKind包