flowable适配人大金仓Kingbase数据库
背景
因为国产化的需求，需要把现有项目的数据库改成人大金仓，适配某个项目的时候因为使用了未适配Kingbase的flowable，导致无法启动。
原本使用的是Oracle数据库，kingbase兼容Oracle数据库，可以直接当成Oracle来使用。
不用修改flowable源码，其他数据库也可以参考这个思路。
错误1: couldn't deduct database type from database product name 'KingbaseES'
Caused by: org.flowable.common.engine.api.FlowableException: couldn't deduct database type from database product name 'KingbaseES'
at org.flowable.common.engine.impl.AbstractEngineConfiguration.initDatabaseType(AbstractEngineConfiguration.java:486)
at org.flowable.common.engine.impl.AbstractEngineConfiguration.initDataSource(AbstractEngineConfiguration.java:456)
at org.flowable.app.engine.AppEngineConfiguration.init(AppEngineConfiguration.java:198)
at org.flowable.app.engine.AppEngineConfiguration.buildAppEngine(AppEngineConfiguration.java:183)
at org.flowable.app.spring.SpringAppEngineConfiguration.buildAppEngine(SpringAppEngineConfiguration.java:63)
at org.flowable.app.spring.AppEngineFactoryBean.getObject(AppEngineFactoryBean.java:59)
at org.flowable.app.spring.AppEngineFactoryBean.getObject(AppEngineFactoryBean.java:31)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:169)
... 213 common frames omitted
解决方法
先说解决方法，通过AOP直接指定databaseType为oracle
import lombok.RequiredArgsConstructor;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.flowable.app.engine.AppEngineConfiguration;
import org.springframework.stereotype.Component;
@Aspect
@Component
@RequiredArgsConstructor
public class KingbaseSupport {
private final AppEngineConfiguration appEngineConfiguration;
@Pointcut("execution(* org.flowable.app.engine.AppEngineConfiguration.buildAppEngine())")
public void access() {
}
@Before("access()")
public void before() {
appEngineConfiguration.setDatabaseType("oracle");
}
}
分析原因
查看报错的堆栈信息，报错是在AbstractEngineConfiguration.initDatabaseType方法上, 代码如下(省略部分代码):
public void initDatabaseType() {
Connection connection = null;
try {
connection = this.dataSource.getConnection();
DatabaseMetaData databaseMetaData = connection.getMetaData();
String databaseProductName = databaseMetaData.getDatabaseProductName();
this.logger.debug("database product name: '{}'", databaseProductName);
this.databaseType = this.databaseTypeMappings.getProperty(databaseProductName);
if (this.databaseType == null) {
throw new FlowableException("couldn't deduct database type from database product name '" + databaseProductName + "'");
}
this.logger.debug("using database type: {}", this.databaseType);
} catch (SQLException var56) {
this.logger.error("Exception while initializing Database connection", var56);
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException var49) {
this.logger.error("Exception while closing the Database connection", var49);
}
}
if ("mssql".equals(this.databaseType)) {
this.maxNrOfStatementsInBulkInsert = this.DEFAULT_MAX_NR_OF_STATEMENTS_BULK_INSERT_SQL_SERVER;
}
}
调试后发现，databaseProductName的值是KingbaseES, 从databaseTypeMappings中没有获取到名为KingbaseES的Property。
继续查看代码，databaseTypeMappings的值来自AbstractEngineConfiguration.getDefaultDatabaseTypeMappings()方法
protected Properties databaseTypeMappings = getDefaultDatabaseTypeMappings();
public static Properties getDefaultDatabaseTypeMappings() {
Properties databaseTypeMappings = new Properties();
databaseTypeMappings.setProperty("H2", "h2");
databaseTypeMappings.setProperty("HSQL Database Engine", "hsql");
databaseTypeMappings.setProperty("MySQL", "mysql");
databaseTypeMappings.setProperty("MariaDB", "mysql");
databaseTypeMappings.setProperty("Oracle", "oracle");
databaseTypeMappings.setProperty("PostgreSQL", "postgres");
databaseTypeMappings.setProperty("Microsoft SQL Server", "mssql");
databaseTypeMappings.setProperty("db2", "db2");
databaseTypeMappings.setProperty("DB2", "db2");
databaseTypeMappings.setProperty("DB2/NT", "db2");
databaseTypeMappings.setProperty("DB2/NT64", "db2");
databaseTypeMappings.setProperty("DB2 UDP", "db2");
databaseTypeMappings.setProperty("DB2/LINUX", "db2");
databaseTypeMappings.setProperty("DB2/LINUX390", "db2");
databaseTypeMappings.setProperty("DB2/LINUXX8664", "db2");
databaseTypeMappings.setProperty("DB2/LINUXZ64", "db2");
databaseTypeMappings.setProperty("DB2/LINUXPPC64", "db2");
databaseTypeMappings.setProperty("DB2/400 SQL", "db2");
databaseTypeMappings.setProperty("DB2/6000", "db2");
databaseTypeMappings.setProperty("DB2 UDB iSeries", "db2");
databaseTypeMappings.setProperty("DB2/AIX64", "db2");
databaseTypeMappings.setProperty("DB2/HPUX", "db2");
databaseTypeMappings.setProperty("DB2/HP64", "db2");
databaseTypeMappings.setProperty("DB2/SUN", "db2");
databaseTypeMappings.setProperty("DB2/SUN64", "db2");
databaseTypeMappings.setProperty("DB2/PTX", "db2");
databaseTypeMappings.setProperty("DB2/2", "db2");
databaseTypeMappings.setProperty("DB2 UDB AS400", "db2");
databaseTypeMappings.setProperty("CockroachDB", "cockroachdb");
return databaseTypeMappings;
}
这里完全写死了，没办法修改，同时也没有找到添加的方法。
通过不停的下断点, 调用链是:
AppEngineConfiguration.buildAppEngine()
↓
AppEngineConfiguration.init()
↓
AbstractEngineConfiguration.initDataSource()
↓
AbstractEngineConfiguration.initDatabaseType()
在initDataSource方法中，有判断databaseType是否为空，如果为则会调用initDatabaseType()方法：
if (this.databaseType == null) {
this.initDatabaseType();
}
因此提前设置好databaseType就可以了。
错误2: Unknown database: KingbaseES
解决方法
添加一个KingbaseDatabase类:
import liquibase.database.DatabaseConnection;
import liquibase.database.core.OracleDatabase;
import liquibase.exception.DatabaseException;
public class KingbaseDatabase extends OracleDatabase {
@Override
public boolean isCorrectDatabaseImplementation(DatabaseConnection conn) throws DatabaseException {
return "KingbaseES".equals(conn.getDatabaseProductName());
}
@Override
public String getShortName() {
return "kingbase";
}
@Override
protected String getDefaultDatabaseProductName() {
return "KingbaseES";
}
}
在SpringBoot启动前进行注册
public static void main(String[] args) {
DatabaseFactory.getInstance().register(new KingbaseDatabase());
SpringApplication.run(WebApplication.class, args);
}
分析原因
断点后发现在JdbcExecutor的public Object execute(CallableStatementCallback action, List<SqlVisitor> sqlVisitors) throws DatabaseException方法执行了一条sqlcall current_schema，不支持这种语法就报错了。
通过调用堆栈发现sql语句在这个方法中获取AbstractJdbcDatabase.getConnectionSchemaName：
protected String getConnectionSchemaName() {
if (this.connection == null) {
return null;
} else if (this.connection instanceof OfflineConnection) {
return ((OfflineConnection)this.connection).getSchema();
} else {
try {
SqlStatement currentSchemaStatement = this.getConnectionSchemaNameCallStatement();
return (String)ExecutorService.getInstance().getExecutor(this).queryForObject(currentSchemaStatement, String.class);
} catch (Exception var2) {
LogService.getLog(this.getClass()).info(LogType.LOG, "Error getting default schema", var2);
return null;
}
}
}
关键点聚焦在getConnectionSchemaNameCallStatement()这个方法上, 默认返回的就是call current_schema这条SQL。
protected SqlStatement getConnectionSchemaNameCallStatement() {
return new RawCallStatement("call current_schema");
}
getConnectionSchemaName()方法只有在getDefaultSchemaName()方法中被调用，而且getDefaultSchemaName()方法是实现Database接口的方法。
public abstract class AbstractJdbcDatabase implements Database {
// 省略部分代码
public String getDefaultSchemaName() {
if (!this.supportsSchemas()) {
return this.getDefaultCatalogName();
} else {
if (this.defaultSchemaName == null && this.connection != null) {
this.defaultSchemaName = this.getConnectionSchemaName();
}
return this.defaultSchemaName;
}
}
}
也就是说不同类型的数据库应该会有不同类型的Database实现，接下来要找到是如何确定Database实现的。
继续通过调用堆栈向上查找，发现在LiquibaseBasedSchemaManager的schemaUpdate()方法上database消失了，说明databse在createLiquibaseInstance()方法上被确定下来。
protected Liquibase createLiquibaseInstance(LiquibaseDatabaseConfiguration databaseConfiguration) throws SQLException {
Connection jdbcConnection = null;
boolean closeConnection = false;
try {
CommandContext commandContext = Context.getCommandContext();
if (commandContext == null) {
jdbcConnection = databaseConfiguration.getDataSource().getConnection();
closeConnection = true;
} else {
jdbcConnection = ((DbSqlSession)commandContext.getSession(DbSqlSession.class)).getSqlSession().getConnection();
}
if (!jdbcConnection.getAutoCommit()) {
jdbcConnection.commit();
}
DatabaseConnection connection = new JdbcConnection(jdbcConnection);
Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(connection);
database.setDatabaseChangeLogTableName(this.changeLogPrefix + database.getDatabaseChangeLogTableName());
database.setDatabaseChangeLogLockTableName(this.changeLogPrefix + database.getDatabaseChangeLogLockTableName());
String databaseSchema = databaseConfiguration.getDatabaseSchema();
if (StringUtils.isNotEmpty(databaseSchema)) {
database.setDefaultSchemaName(databaseSchema);
database.setLiquibaseSchemaName(databaseSchema);
}
String databaseCatalog = databaseConfiguration.getDatabaseCatalog();
if (StringUtils.isNotEmpty(databaseCatalog)) {
database.setDefaultCatalogName(databaseCatalog);
database.setLiquibaseCatalogName(databaseCatalog);
}
return new Liquibase(this.changeLogFile, new ClassLoaderResourceAccessor(), database);
} catch (Exception var9) {
if (jdbcConnection != null && closeConnection) {
jdbcConnection.close();
}
throw new FlowableException("Error creating " + this.context + " liquibase instance", var9);
}
}
关键在这一行:
Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(connection);
findCorrectDatabaseImplementation方法从implementedDatabases变量中获取对应的database
Database implementedDatabase = (Database)var3.next();
if (connection instanceof OfflineConnection) {
if (((OfflineConnection)connection).isCorrectDatabaseImplementation(implementedDatabase)) {
foundDatabases.add(implementedDatabase);
}
} else if (implementedDatabase.isCorrectDatabaseImplementation(connection)) {
foundDatabases.add(implementedDatabase);
}
那implementedDatabases从哪里来?
原来在DatabaseFactory的构造方法中进行了初始化, 搜索所有实现了Database的类并调用register方法注册。
注册方法register是public，而且DatabaseFactory还是单例模式，有一个instance，可以直接获取instance调用register方法注册自己的Database。
public class DatabaseFactory {
// 省略无关代码
private static DatabaseFactory instance;
public static synchronized DatabaseFactory getInstance() {
if (instance == null) {
instance = new DatabaseFactory();
}
return instance;
}
public void register(Database database) {
Map<String, SortedSet<Database>> map = null;
if (database instanceof InternalDatabase) {
map = internalDatabases;
} else {
map = implementedDatabases;
}
if (!map.containsKey(database.getShortName())) {
map.put(database.getShortName(), new TreeSet<>(new TreeSet<>(new DatabaseComparator())));
}
map.get(database.getShortName()).add(database);
}
}
错误3: 关系 "flw_ev_databasechangelog" 已经存在
解决方法
把错误2创建的KingbaseDatabase从继承OracleDatabase修改成继承AbstractJdbcDatabase, 直接复制OracleDatabase中的代码到这里，把代码中的PRODUCT_NAME值修改为KingbaseES, 把getShortName返回值改成kingbase, getDefaultDatabaseProductName返回值改成KingbaseES, 按错误2的方法注册。
完整代码如下:
public class KingbaseDatabase extends AbstractJdbcDatabase {
public static final String PRODUCT_NAME = "KingbaseES";
public static final int ORACLE_12C_MAJOR_VERSION = 12;
private static final Pattern PROXY_USER = Pattern.compile(".*(?:thin|oci)\\:(.+)/@.*");
private static final ResourceBundle coreBundle = getBundle("liquibase/i18n/liquibase-core");
protected final int SHORT_IDENTIFIERS_LENGTH = 30;
protected final int LONG_IDENTIFIERS_LEGNTH = 128;
private final Set<String> reservedWords = new HashSet<>();
private Set<String> userDefinedTypes;
private Map<String, String> savedSessionNlsSettings;
private Boolean canAccessDbaRecycleBin;
private Integer databaseMajorVersion;
private Integer databaseMinorVersion;
/**
* Default constructor for an object that represents the Oracle Database DBMS.
*/
public KingbaseDatabase() {
super.unquotedObjectsAreUppercased = true;
//noinspection HardCodedStringLiteral
super.setCurrentDateTimeFunction("SYSTIMESTAMP");
// Setting list of Oracle's native functions
//noinspection HardCodedStringLiteral
dateFunctions.add(new DatabaseFunction("SYSDATE"));
//noinspection HardCodedStringLiteral
dateFunctions.add(new DatabaseFunction("SYSTIMESTAMP"));
//noinspection HardCodedStringLiteral
dateFunctions.add(new DatabaseFunction("CURRENT_TIMESTAMP"));
//noinspection HardCodedStringLiteral
super.sequenceNextValueFunction = "%s.nextval";
//noinspection HardCodedStringLiteral
super.sequenceCurrentValueFunction = "%s.currval";
}
@Override
public String getShortName() {
return "kingbase";
}
@Override
protected String getDefaultDatabaseProductName() {
return "KingbaseES";
}
@Override
public int getPriority() {
return PRIORITY_DEFAULT;
}
private final void tryProxySessionn(final String url, final Connection con) {
Matcher m = PROXY_USER.matcher(url);
if (m.matches()) {
Properties props = new Properties();
props.put("PROXY_USER_NAME", m.group(1));
try {
Method method = con.getClass().getMethod("openProxySession", int.class, Properties.class);
method.setAccessible(true);
method.invoke(con, 1, props);
} catch (Exception e) {
Scope.getCurrentScope().getLog(getClass()).info(LogType.LOG, "Could not open proxy session on OracleDatabase: " + e.getCause().getMessage());
}
}
}
@Override
public void setConnection(DatabaseConnection conn) {
//noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,
// HardCodedStringLiteral
//noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,
// HardCodedStringLiteral
reservedWords.addAll(Arrays.asList("GROUP", "USER", "SESSION", "PASSWORD", "RESOURCE", "START", "SIZE", "UID", "DESC", "ORDER")); //more reserved words not returned by driver
Connection sqlConn = null;
if (!(conn instanceof OfflineConnection)) {
try {
/*
* Don't try to call getWrappedConnection if the conn instance is
* is not a JdbcConnection. This happens for OfflineConnection.
* see https://liquibase.jira.com/browse/CORE-2192
*/
if (conn instanceof JdbcConnection) {
sqlConn = ((JdbcConnection) conn).getWrappedConnection();
}
} catch (Exception e) {
throw new UnexpectedLiquibaseException(e);
}
if (sqlConn != null) {
tryProxySessionn(conn.getURL(), sqlConn);
try {
//noinspection HardCodedStringLiteral
reservedWords.addAll(Arrays.asList(sqlConn.getMetaData().getSQLKeywords().toUpperCase().split(",\\s*")));
} catch (SQLException e) {
//noinspection HardCodedStringLiteral
LogService.getLog(getClass()).info(LogType.LOG, "Could get sql keywords on OracleDatabase: " + e.getMessage());
//can not get keywords. Continue on
}
try {
Method method = sqlConn.getClass().getMethod("setRemarksReporting", Boolean.TYPE);
method.setAccessible(true);
method.invoke(sqlConn, true);
} catch (Exception e) {
//noinspection HardCodedStringLiteral
LogService.getLog(getClass()).info(LogType.LOG, "Could not set remarks reporting on OracleDatabase: " + e.getMessage());
//cannot set it. That is OK
}
Statement statement = null;
ResultSet resultSet = null;
try {
statement = sqlConn.createStatement();
//noinspection HardCodedStringLiteral
resultSet = statement.executeQuery("SELECT value FROM v$parameter WHERE name = 'compatible'");
String compatibleVersion = null;
if (resultSet.next()) {
//noinspection HardCodedStringLiteral
compatibleVersion = resultSet.getString("value");
}
if (compatibleVersion != null) {
Matcher majorVersionMatcher = Pattern.compile("(\\d+)\\.(\\d+)\\..*").matcher(compatibleVersion);
if (majorVersionMatcher.matches()) {
this.databaseMajorVersion = Integer.valueOf(majorVersionMatcher.group(1));
this.databaseMinorVersion = Integer.valueOf(majorVersionMatcher.group(2));
}
}
} catch (SQLException e) {
@SuppressWarnings("HardCodedStringLiteral") String message = "Cannot read from v$parameter: " + e.getMessage();
//noinspection HardCodedStringLiteral
LogService.getLog(getClass()).info(LogType.LOG, "Could not set check compatibility mode on OracleDatabase, assuming not running in any sort of compatibility mode: " + message);
} finally {
JdbcUtils.close(resultSet, statement);
}
}
}
super.setConnection(conn);
}
@Override
public int getDatabaseMajorVersion() throws DatabaseException {
if (databaseMajorVersion == null) {
return super.getDatabaseMajorVersion();
} else {
return databaseMajorVersion;
}
}
@Override
public int getDatabaseMinorVersion() throws DatabaseException {
if (databaseMinorVersion == null) {
return super.getDatabaseMinorVersion();
} else {
return databaseMinorVersion;
}
}
@Override
public Integer getDefaultPort() {
return 1521;
}
@Override
public String getJdbcCatalogName(CatalogAndSchema schema) {
return null;
}
@Override
public String getJdbcSchemaName(CatalogAndSchema schema) {
return correctObjectName((schema.getCatalogName() == null) ? schema.getSchemaName() : schema.getCatalogName(), Schema.class);
}
@Override
protected String getAutoIncrementClause(final String generationType, final Boolean defaultOnNull) {
if (StringUtils.isEmpty(generationType)) {
return "";
}
String autoIncrementClause = "GENERATED %s AS IDENTITY"; // %s -- [ ALWAYS | BY DEFAULT [ ON NULL ] ]
String generationStrategy = generationType;
if (Boolean.TRUE.equals(defaultOnNull) && generationType.equalsIgnoreCase("BY DEFAULT")) {
generationStrategy += " ON NULL";
}
return String.format(autoIncrementClause, generationStrategy);
}
@Override
public String generatePrimaryKeyName(String tableName) {
if (tableName.length() > 27) {
//noinspection HardCodedStringLiteral
return "PK_" + tableName.toUpperCase(Locale.US).substring(0, 27);
} else {
//noinspection HardCodedStringLiteral
return "PK_" + tableName.toUpperCase(Locale.US);
}
}
@Override
public boolean supportsInitiallyDeferrableColumns() {
return true;
}
@Override
public boolean isReservedWord(String objectName) {
return reservedWords.contains(objectName.toUpperCase());
}
@Override
public boolean supportsSequences() {
return true;
}
/**
* Oracle supports catalogs in liquibase terms
*
* @return false
*/
@Override
public boolean supportsSchemas() {
return false;
}
@Override
protected String getConnectionCatalogName() throws DatabaseException {
if (getConnection() instanceof OfflineConnection) {
return getConnection().getCatalog();
}
try {
//noinspection HardCodedStringLiteral
return ExecutorService.getInstance().getExecutor(this).queryForObject(new RawCallStatement("select sys_context( 'userenv', 'current_schema' ) from dual"), String.class);
} catch (Exception e) {
//noinspection HardCodedStringLiteral
LogService.getLog(getClass()).info(LogType.LOG, "Error getting default schema", e);
}
return null;
}
@Override
public boolean isCorrectDatabaseImplementation(DatabaseConnection conn) throws DatabaseException {
return PRODUCT_NAME.equalsIgnoreCase(conn.getDatabaseProductName());
}
@Override
public String getDefaultDriver(String url) {
//noinspection HardCodedStringLiteral
if (url.startsWith("jdbc:oracle")) {
return "oracle.jdbc.OracleDriver";
}
return null;
}
@Override
public String getDefaultCatalogName() {//NOPMD
return (super.getDefaultCatalogName() == null) ? null : super.getDefaultCatalogName().toUpperCase(Locale.US);
}
/**
* <p>Returns an Oracle date literal with the same value as a string formatted using ISO 8601.</p>
*
* <p>Convert an ISO8601 date string to one of the following results:
* to_date('1995-05-23', 'YYYY-MM-DD')
* to_date('1995-05-23 09:23:59', 'YYYY-MM-DD HH24:MI:SS')</p>
* <p>
* Implementation restriction:<br>
* Currently, only the following subsets of ISO8601 are supported:<br>
* <ul>
* <li>YYYY-MM-DD</li>
* <li>YYYY-MM-DDThh:mm:ss</li>
* </ul>
*/
@Override
public String getDateLiteral(String isoDate) {
String normalLiteral = super.getDateLiteral(isoDate);
if (isDateOnly(isoDate)) {
//noinspection HardCodedStringLiteral
String val = "TO_DATE(" +
normalLiteral +
//noinspection HardCodedStringLiteral
", 'YYYY-MM-DD')";
return val;
} else if (isTimeOnly(isoDate)) {
//noinspection HardCodedStringLiteral
String val = "TO_DATE(" +
normalLiteral +
//noinspection HardCodedStringLiteral
", 'HH24:MI:SS')";
return val;
} else if (isTimestamp(isoDate)) {
//noinspection HardCodedStringLiteral
String val = "TO_TIMESTAMP(" +
normalLiteral +
//noinspection HardCodedStringLiteral
", 'YYYY-MM-DD HH24:MI:SS.FF')";
return val;
} else if (isDateTime(isoDate)) {
int seppos = normalLiteral.lastIndexOf('.');
if (seppos != -1) {
normalLiteral = normalLiteral.substring(0, seppos) + "'";
}
//noinspection HardCodedStringLiteral
String val = "TO_DATE(" +
normalLiteral +
//noinspection HardCodedStringLiteral
", 'YYYY-MM-DD HH24:MI:SS')";
return val;
}
//noinspection HardCodedStringLiteral
return "UNSUPPORTED:" + isoDate;
}
@Override
public boolean isSystemObject(DatabaseObject example) {
if (example == null) {
return false;
}
if (this.isLiquibaseObject(example)) {
return false;
}
if (example instanceof Schema) {
//noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral
if ("SYSTEM".equals(example.getName()) || "SYS".equals(example.getName()) || "CTXSYS".equals(example.getName()) || "XDB".equals(example.getName())) {
return true;
}
//noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral
if ("SYSTEM".equals(example.getSchema().getCatalogName()) || "SYS".equals(example.getSchema().getCatalogName()) || "CTXSYS".equals(example.getSchema().getCatalogName()) || "XDB".equals(example.getSchema().getCatalogName())) {
return true;
}
} else if (isSystemObject(example.getSchema())) {
return true;
}
if (example instanceof Catalog) {
//noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral
if (("SYSTEM".equals(example.getName()) || "SYS".equals(example.getName()) || "CTXSYS".equals(example.getName()) || "XDB".equals(example.getName()))) {
return true;
}
} else if (example.getName() != null) {
//noinspection HardCodedStringLiteral
if (example.getName().startsWith("BIN$")) { //oracle deleted table
boolean filteredInOriginalQuery = this.canAccessDbaRecycleBin();
if (!filteredInOriginalQuery) {
filteredInOriginalQuery = StringUtils.trimToEmpty(example.getSchema().getName()).equalsIgnoreCase(this.getConnection().getConnectionUserName());
}
if (filteredInOriginalQuery) {
return !((example instanceof PrimaryKey) || (example instanceof Index) || (example instanceof
liquibase.statement.UniqueConstraint));
} else {
return true;
}
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("AQ$")) { //oracle AQ tables
return true;
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("DR$")) { //oracle index tables
return true;
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("SYS_IOT_OVER")) { //oracle system table
return true;
} else //noinspection HardCodedStringLiteral,HardCodedStringLiteral
if ((example.getName().startsWith("MDRT_") || example.getName().startsWith("MDRS_")) && example.getName().endsWith("$")) {
// CORE-1768 - Oracle creates these for spatial indices and will remove them when the index is removed.
return true;
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("MLOG$_")) { //Created by materliaized view logs for every table that is part of a materialized view. Not available for DDL operations.
return true;
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("RUPD$_")) { //Created by materialized view log tables using primary keys. Not available for DDL operations.
return true;
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("WM$_")) { //Workspace Manager backup tables.
return true;
} else //noinspection HardCodedStringLiteral
if ("CREATE$JAVA$LOB$TABLE".equals(example.getName())) { //This table contains the name of the Java object, the date it was loaded, and has a BLOB column to store the Java object.
return true;
} else //noinspection HardCodedStringLiteral
if ("JAVA$CLASS$MD5$TABLE".equals(example.getName())) { //This is a hash table that tracks the loading of Java objects into a schema.
return true;
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("ISEQ$$_")) { //System-generated sequence
return true;
} else //noinspection HardCodedStringLiteral
if (example.getName().startsWith("USLOG$")) { //for update materialized view
return true;
} else if (example.getName().startsWith("SYS_FBA")) { //for Flashback tables
return true;
}
}
return super.isSystemObject(example);
}
@Override
public boolean supportsTablespaces() {
return true;
}
@Override
public boolean supportsAutoIncrement() {
// Oracle supports Identity beginning with version 12c
boolean isAutoIncrementSupported = false;
try {
if (getDatabaseMajorVersion() >= 12) {
isAutoIncrementSupported = true;
}
// Returning true will generate create table command with 'IDENTITY' clause, example:
// CREATE TABLE AutoIncTest (IDPrimaryKey NUMBER(19) GENERATED BY DEFAULT AS IDENTITY NOT NULL, TypeID NUMBER(3) NOT NULL, Description NVARCHAR2(50), CONSTRAINT PK_AutoIncTest PRIMARY KEY (IDPrimaryKey));
// While returning false will continue to generate create table command without 'IDENTITY' clause, example:
// CREATE TABLE AutoIncTest (IDPrimaryKey NUMBER(19) NOT NULL, TypeID NUMBER(3) NOT NULL, Description NVARCHAR2(50), CONSTRAINT PK_AutoIncTest PRIMARY KEY (IDPrimaryKey));
} catch (DatabaseException ex) {
isAutoIncrementSupported = false;
}
return isAutoIncrementSupported;
}
// public Set<UniqueConstraint> findUniqueConstraints(String schema) throws DatabaseException {
// Set<UniqueConstraint> returnSet = new HashSet<UniqueConstraint>();
//
// List<Map> maps = new Executor(this).queryForList(new RawSqlStatement("SELECT UC.CONSTRAINT_NAME, UCC.TABLE_NAME, UCC.COLUMN_NAME FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC WHERE UC.CONSTRAINT_NAME=UCC.CONSTRAINT_NAME AND CONSTRAINT_TYPE='U' ORDER BY UC.CONSTRAINT_NAME"));
//
// UniqueConstraint constraint = null;
// for (Map map : maps) {
// if (constraint == null || !constraint.getName().equals(constraint.getName())) {
// returnSet.add(constraint);
// Table table = new Table((String) map.get("TABLE_NAME"));
// constraint = new UniqueConstraint(map.get("CONSTRAINT_NAME").toString(), table);
// }
// }
// if (constraint != null) {
// returnSet.add(constraint);
// }
//
// return returnSet;
// }
@Override
public boolean supportsRestrictForeignKeys() {
return false;
}
@Override
public int getDataTypeMaxParameters(String dataTypeName) {
//noinspection HardCodedStringLiteral
if ("BINARY_FLOAT".equalsIgnoreCase(dataTypeName)) {
return 0;
}
//noinspection HardCodedStringLiteral
if ("BINARY_DOUBLE".equalsIgnoreCase(dataTypeName)) {
return 0;
}
return super.getDataTypeMaxParameters(dataTypeName);
}
public String getSystemTableWhereClause(String tableNameColumn) {
List<String> clauses = new ArrayList<String>(Arrays.asList("BIN$",
"AQ$",
"DR$",
"SYS_IOT_OVER",
"MLOG$_",
"RUPD$_",
"WM$_",
"ISEQ$$_",
"USLOG$",
"SYS_FBA"));
for (int i = 0; i < clauses.size(); i++) {
clauses.set(i, tableNameColumn + " NOT LIKE '" + clauses.get(i) + "%'");
}
return "(" + StringUtils.join(clauses, " AND ") + ")";
}
@Override
public boolean jdbcCallsCatalogsSchemas() {
return true;
}
public Set<String> getUserDefinedTypes() {
if (userDefinedTypes == null) {
userDefinedTypes = new HashSet<>();
if ((getConnection() != null) && !(getConnection() instanceof OfflineConnection)) {
try {
try {
//noinspection HardCodedStringLiteral
userDefinedTypes.addAll(ExecutorService.getInstance().getExecutor(this).queryForList(new RawSqlStatement("SELECT DISTINCT TYPE_NAME FROM ALL_TYPES"), String.class));
} catch (DatabaseException e) { //fall back to USER_TYPES if the user cannot see ALL_TYPES
//noinspection HardCodedStringLiteral
userDefinedTypes.addAll(ExecutorService.getInstance().getExecutor(this).queryForList(new RawSqlStatement("SELECT TYPE_NAME FROM USER_TYPES"), String.class));
}
} catch (DatabaseException e) {
//ignore error
}
}
}
return userDefinedTypes;
}
@Override
public String generateDatabaseFunctionValue(DatabaseFunction databaseFunction) {
//noinspection HardCodedStringLiteral
if ((databaseFunction != null) && "current_timestamp".equalsIgnoreCase(databaseFunction.toString())) {
return databaseFunction.toString();
}
if ((databaseFunction instanceof SequenceNextValueFunction) || (databaseFunction instanceof
SequenceCurrentValueFunction)) {
String quotedSeq = super.generateDatabaseFunctionValue(databaseFunction);
// replace "myschema.my_seq".nextval with "myschema"."my_seq".nextval
return quotedSeq.replaceFirst("\"([^\\.\"]+)\\.([^\\.\"]+)\"", "\"$1\".\"$2\"");
}
return super.generateDatabaseFunctionValue(databaseFunction);
}
@Override
public ValidationErrors validate() {
ValidationErrors errors = super.validate();
DatabaseConnection connection = getConnection();
if ((connection == null) || (connection instanceof OfflineConnection)) {
//noinspection HardCodedStringLiteral
LogService.getLog(getClass()).info(LogType.LOG, "Cannot validate offline database");
return errors;
}
if (!canAccessDbaRecycleBin()) {
errors.addWarning(getDbaRecycleBinWarning());
}
return errors;
}
public String getDbaRecycleBinWarning() {
//noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,
// HardCodedStringLiteral
//noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral
return "Liquibase needs to access the DBA_RECYCLEBIN table so we can automatically handle the case where " +
"constraints are deleted and restored. Since Oracle doesn't properly restore the original table names " +
"referenced in the constraint, we use the information from the DBA_RECYCLEBIN to automatically correct this" +
" issue.\n" +
"\n" +
"The user you used to connect to the database (" + getConnection().getConnectionUserName() +
") needs to have \"SELECT ON SYS.DBA_RECYCLEBIN\" permissions set before we can perform this operation. " +
"Please run the following SQL to set the appropriate permissions, and try running the command again.\n" +
"\n" +
" GRANT SELECT ON SYS.DBA_RECYCLEBIN TO " + getConnection().getConnectionUserName() + ";";
}
public boolean canAccessDbaRecycleBin() {
if (canAccessDbaRecycleBin == null) {
DatabaseConnection connection = getConnection();
if ((connection == null) || (connection instanceof OfflineConnection)) {
return false;
}
Statement statement = null;
try {
statement = ((JdbcConnection) connection).createStatement();
@SuppressWarnings("HardCodedStringLiteral") ResultSet resultSet = statement.executeQuery("select 1 from dba_recyclebin where 0=1");
resultSet.close(); //don't need to do anything with the result set, just make sure statement ran.
this.canAccessDbaRecycleBin = true;
} catch (Exception e) {
//noinspection HardCodedStringLiteral
if ((e instanceof SQLException) && e.getMessage().startsWith("ORA-00942")) { //ORA-00942: table or view does not exist
this.canAccessDbaRecycleBin = false;
} else {
//noinspection HardCodedStringLiteral
LogService.getLog(getClass()).warning(LogType.LOG, "Cannot check dba_recyclebin access", e);
this.canAccessDbaRecycleBin = false;
}
} finally {
JdbcUtils.close(null, statement);
}
}
return canAccessDbaRecycleBin;
}
@Override
public boolean supportsNotNullConstraintNames() {
return true;
}
/**
* Tests if the given String would be a valid identifier in Oracle DBMS. In Oracle, a valid identifier has
* the following form (case-insensitive comparison):
* 1st character: A-Z
* 2..n characters: A-Z0-9$_#
* The maximum length of an identifier differs by Oracle version and object type.
*/
public boolean isValidOracleIdentifier(String identifier, Class<? extends DatabaseObject> type) {
if ((identifier == null) || (identifier.length() < 1))
return false;
if (!identifier.matches("^(i?)[A-Z][A-Z0-9\\$\\_\\#]*$"))
return false;
/*
* @todo It seems we currently do not have a class for tablespace identifiers, and all other classes
* we do know seem to be supported as 12cR2 long identifiers, so:
*/
return (identifier.length() <= LONG_IDENTIFIERS_LEGNTH);
}
/**
* Returns the maximum number of bytes (NOT: characters) for an identifier. For Oracle <=12c Release 20, this
* is 30 bytes, and starting from 12cR2, up to 128 (except for tablespaces, PDB names and some other rather rare
* object types).
*
* @return the maximum length of an object identifier, in bytes
*/
public int getIdentifierMaximumLength() {
try {
if (getDatabaseMajorVersion() < ORACLE_12C_MAJOR_VERSION) {
return SHORT_IDENTIFIERS_LENGTH;
} else if ((getDatabaseMajorVersion() == ORACLE_12C_MAJOR_VERSION) && (getDatabaseMinorVersion() <= 1)) {
return SHORT_IDENTIFIERS_LENGTH;
} else {
return LONG_IDENTIFIERS_LEGNTH;
}
} catch (DatabaseException ex) {
throw new UnexpectedLiquibaseException("Cannot determine the Oracle database version number", ex);
}
}
}
分析原因
这个错误是因为执行的查找数据表的语句后，没有发现创建flowable相关的表，于是会主动创建数据表，但实际表已经存在导致的。
在JdbcDatabaseSnapshot.getTables()方法上有判断databse是否是OracleDatabase，是则调用queryOracle方法，执行oracle的查表语句, 但是这个语句和kingbase不兼容，虽然不会报错，但是查询不出任何数据。
@Override
public List<CachedRow> fastFetchQuery() throws SQLException, DatabaseException {
CatalogAndSchema catalogAndSchema = new CatalogAndSchema(catalogName, schemaName).customize(database);
if (database instanceof OracleDatabase) {
return queryOracle(catalogAndSchema, table);
} else if (database instanceof MSSQLDatabase) {
return queryMssql(catalogAndSchema, table);
} else if (database instanceof Db2zDatabase) {
return queryDb2Zos(catalogAndSchema, table);
}
String catalog = ((AbstractJdbcDatabase) database).getJdbcCatalogName(catalogAndSchema);
String schema = ((AbstractJdbcDatabase) database).getJdbcSchemaName(catalogAndSchema);
return extract(databaseMetaData.getTables(catalog, schema, ((table == null) ?
SQL_FILTER_MATCH_ALL : table), new String[]{"TABLE"}));
}
如果是oracle数据库，则会执行以下sql去查询，在Oracle中正常，但是kingbase中没有结果：
SELECT
NULL AS TABLE_CAT,
a.OWNER AS TABLE_SCHEM,
a.TABLE_NAME AS TABLE_NAME,
a.TEMPORARY AS TEMPORARY,
a.DURATION AS DURATION,
'TABLE' AS TABLE_TYPE,
c.COMMENTS AS REMARKS,
CASE
WHEN A.tablespace_name = (
SELECT
DEFAULT_TABLESPACE
FROM
USER_USERS
) THEN NULL
ELSE tablespace_name
END AS tablespace_name
FROM
ALL_TABLES a
JOIN ALL_TAB_COMMENTS c ON
a.TABLE_NAME = c.table_name
AND a.owner = c.owner
非指定类型则会调用databaseMetaData.getTables方法，实际是调用了KbDatabaseMetaData.getTables(), 这样就正常了，因此只要让KingbaseDatabase不继承OracleDatabase就可以了。
错误4: 操作符不存在: numeric = boolean
解决方法
添加一个KingbaseBooleanType类，代码如下:
import liquibase.database.Database;
import liquibase.datatype.core.BooleanType;
public class KingbaseBooleanType extends BooleanType {
@Override
protected boolean isNumericBoolean(Database database) {
return true;
}
@Override
public int getPriority() {
return super.getPriority() + 1;
}
}
然后在Spring启动前进行注册
public static void main(String[] args) {
// 省略无关代码
DataTypeFactory.getInstance().register(KingbaseBooleanType.class);
SpringApplication.run(WebApplication.class, args);
}
分析原因
在flowable启动时会执行acquireLock操作，会对数据库进行一次更新，执行SQL时没有正确把布尔值转换，导致这个错误。
在UpdateGenerator.convertToString方法中，有对布尔值进行转换。
private String convertToString(Object newValue, Database database) {
String sqlString;
if ((newValue == null) || "NULL".equalsIgnoreCase(newValue.toString())) {
sqlString = "NULL";
} else if ((newValue instanceof String) && !looksLikeFunctionCall(((String) newValue), database)) {
sqlString = DataTypeFactory.getInstance().fromObject(newValue, database).objectToSql(newValue, database);
} else if (newValue instanceof Date) {
// converting java.util.Date to java.sql.Date
Date date = (Date) newValue;
if (date.getClass().equals(java.util.Date.class)) {
date = new java.sql.Date(date.getTime());
}
sqlString = database.getDateLiteral(date);
} else if (newValue instanceof Boolean) {
if (((Boolean) newValue)) {
sqlString = DataTypeFactory.getInstance().getTrueBooleanValue(database);
} else {
sqlString = DataTypeFactory.getInstance().getFalseBooleanValue(database);
}
} else if (newValue instanceof DatabaseFunction) {
sqlString = database.generateDatabaseFunctionValue((DatabaseFunction) newValue);
} else {
sqlString = newValue.toString();
}
return sqlString;
}
会获取Boolean类型的转换器并转换成字符串，默认的情况下会把布尔转换成TRUE和FALSE这样的字符串(不带双引号)，所以默认执行的SQL是这样的：
UPDATE
SR_DB_6.FLW_EV_DATABASECHANGELOGLOCK
SET
"LOCKED" = TRUE,
LOCKEDBY = 'DESKTOP-35UQ0PK (100.79.201.141)',
LOCKGRANTED = TO_TIMESTAMP('2024-01-12 14:29:03.122', 'YYYY-MM-DD HH24:MI:SS.FF')
WHERE
ID = 1
AND "LOCKED" = FALSE
转换布尔类型时实际会执行BooleanType.objectToSql方法
@Override
public String objectToSql(Object value, Database database) {
if ((value == null) || "null".equals(value.toString().toLowerCase(Locale.US))) {
return null;
}
String returnValue;
if (value instanceof String) {
value = ((String) value).replaceAll("'", "");
if ("true".equals(((String) value).toLowerCase(Locale.US)) || "1".equals(value) || "b'1'".equals(((String) value).toLowerCase(Locale.US)) || "t".equals(((String) value).toLowerCase(Locale.US)) || ((String) value).toLowerCase(Locale.US).equals(this.getTrueBooleanValue(database).toLowerCase(Locale.US))) {
returnValue = this.getTrueBooleanValue(database);
} else if ("false".equals(((String) value).toLowerCase(Locale.US)) || "0".equals(value) || "b'0'".equals(
((String) value).toLowerCase(Locale.US)) || "f".equals(((String) value).toLowerCase(Locale.US)) || ((String) value).toLowerCase(Locale.US).equals(this.getFalseBooleanValue(database).toLowerCase(Locale.US))) {
returnValue = this.getFalseBooleanValue(database);
} else {
throw new UnexpectedLiquibaseException("Unknown boolean value: " + value);
}
} else if (value instanceof Long) {
if (Long.valueOf(1).equals(value)) {
returnValue = this.getTrueBooleanValue(database);
} else {
returnValue = this.getFalseBooleanValue(database);
}
} else if (value instanceof Number) {
if (value.equals(1) || "1".equals(value.toString()) || "1.0".equals(value.toString())) {
returnValue = this.getTrueBooleanValue(database);
} else {
returnValue = this.getFalseBooleanValue(database);
}
} else if (value instanceof DatabaseFunction) {
return value.toString();
} else if (value instanceof Boolean) {
if (((Boolean) value)) {
returnValue = this.getTrueBooleanValue(database);
} else {
returnValue = this.getFalseBooleanValue(database);
}
} else {
throw new UnexpectedLiquibaseException("Cannot convert type "+value.getClass()+" to a boolean value");
}
return returnValue;
}
再向下调用getTrueBooleanValue或者getFalseBooleanValue
/**
* The database-specific value to use for "false" "boolean" columns.
*/
public String getFalseBooleanValue(Database database) {
if (isNumericBoolean(database)) {
return "0";
}
if (database instanceof InformixDatabase) {
return "'f'";
}
return "FALSE";
}
/**
* The database-specific value to use for "true" "boolean" columns.
*/
public String getTrueBooleanValue(Database database) {
if (isNumericBoolean(database)) {
return "1";
}
if (database instanceof InformixDatabase) {
return "'t'";
}
return "TRUE";
}
可以看出代码中调用的isNumericBoolean方法是判断是否应该将布尔转成数字的
protected boolean isNumericBoolean(Database database) {
if (database instanceof DerbyDatabase) {
return !((DerbyDatabase) database).supportsBooleanDataType();
} else if (database.getClass().isAssignableFrom(DB2Database.class)) {
return !((DB2Database) database).supportsBooleanDataType();
}
return (database instanceof Db2zDatabase) || (database instanceof DB2Database) || (database instanceof FirebirdDatabase) || (database instanceof
MSSQLDatabase) || (database instanceof MySQLDatabase) || (database instanceof OracleDatabase) ||
(database instanceof SQLiteDatabase) || (database instanceof SybaseASADatabase) || (database instanceof
SybaseDatabase);
}
本来如果KingbaseDatabase继承OracleDatabase是能正常转成数字的，但是因为上面错误3的修改，取消了继承OracleDatabase，需要新增一个LiquibaseDataType并通过设置优先级覆盖默认的BooleanType。
