Liquibase集成达梦数据库
一、Liquibase适配达梦数据库
Jhipster项目下，Liquibase集成达梦数据库会报一系列错误，例如：
类型不支持、数据库字段使用到了保留字、主键自增等，本文针对达梦数据库的问题，进行解答
- Error querying database, Cause: dm.jdbc.driver.DMException: 第1 行附近出现错误：无效的模型名[INFORMATION_SCHEMA]
- Liquibase 报警告【无法识别DM数据库类型】
以上两个问题的解决办法相同
- 使用liquibase4.3.5版本,下载源码,下载地址如下
https://github.com/liquibase/liquibase/tree/v4.3.5
- 在
liquibase.database.core包下，创建DMDatabase
package liquibase.database.core;
import liquibase.CatalogAndSchema;
import liquibase.Scope;
import liquibase.database.AbstractJdbcDatabase;
import liquibase.database.DatabaseConnection;
import liquibase.database.OfflineConnection;
import liquibase.database.jvm.JdbcConnection;
import liquibase.exception.DatabaseException;
import liquibase.exception.UnexpectedLiquibaseException;
import liquibase.executor.ExecutorService;
import liquibase.statement.DatabaseFunction;
import liquibase.statement.SequenceCurrentValueFunction;
import liquibase.statement.SequenceNextValueFunction;
import liquibase.statement.core.RawCallStatement;
import liquibase.statement.core.RawSqlStatement;
import liquibase.structure.DatabaseObject;
import liquibase.structure.core.Schema;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Locale;
import java.util.Set;
public class DMDatabase extends AbstractJdbcDatabase {
private static final String PRODUCT_NAME="DM DBMS";
private static final Integer PORT=5236;
private Set<String> reservedWords = new HashSet<>(); // 关键字集合
private Set<String> userDefinedTypes;
/**
* Default constructor for an object that represents the Oracle Database DBMS.
*/
public DMDatabase() {
super.unquotedObjectsAreUppercased=true;
//noinspection HardCodedStringLiteral
super.setCurrentDateTimeFunction("SYSDATE");
// 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 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) {
try {
//noinspection HardCodedStringLiteral
reservedWords.addAll(Arrays.asList(sqlConn.getMetaData().getSQLKeywords().toUpperCase().split(",\\s*")));
} catch (SQLException e) {
//noinspection HardCodedStringLiteral
Scope.getCurrentScope().getLog(getClass()).info("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
Scope.getCurrentScope().getLog(getClass()).info("Could not set remarks reporting on OracleDatabase: " + e.getMessage());
//cannot set it. That is OK
}
}
}
super.setConnection(conn);
}
@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);
}
/**
* 当前方法用于解决 【Liquibase创建数据库表，主键自增报[GENERATED]附近出现错误】问题
* 原因：DM数据库创建表时，不支持 GENERATED BY DEFAULT AS IDENTITY
* 解决方案： 达梦数据库创建数据库表的SQL语句中，使用 IDENTITY(1,1) 来设置主键自增， 括号内数字的含义：第一个数字代表从1开始，第二个数字代表每次递增1（步长为1）
* @return
*/
@Override
protected String getAutoIncrementClause() {
return "IDENTITY(1,1)";
}
@Override
public String generatePrimaryKeyName(String tableName) {
if (tableName.length() > 50) {
//noinspection HardCodedStringLiteral
return "PK_" + tableName.toUpperCase(Locale.US).substring(0, 27);
} else {
//noinspection HardCodedStringLiteral
return "PK_" + tableName.toUpperCase(Locale.US);
}
}
@Override
public String getDateLiteral(String isoDate) {
String normalLiteral = super.getDateLiteral(isoDate);
if (isDateOnly(isoDate)) {
StringBuffer val = new StringBuffer();
//noinspection HardCodedStringLiteral
val.append("TO_DATE(");
val.append(normalLiteral);
//noinspection HardCodedStringLiteral
val.append(", 'YYYY-MM-DD')");
return val.toString();
} else if (isTimeOnly(isoDate)) {
StringBuffer val = new StringBuffer();
//noinspection HardCodedStringLiteral
val.append("TO_DATE(");
val.append(normalLiteral);
//noinspection HardCodedStringLiteral
val.append(", 'HH24:MI:SS')");
return val.toString();
} else if (isTimestamp(isoDate)) {
StringBuffer val = new StringBuffer(26);
//noinspection HardCodedStringLiteral
val.append("TO_TIMESTAMP(");
val.append(normalLiteral);
//noinspection HardCodedStringLiteral
val.append(", 'YYYY-MM-DD HH24:MI:SS.FF')");
return val.toString();
} else if (isDateTime(isoDate)) {
int seppos = normalLiteral.lastIndexOf('.');
if (seppos != -1) {
normalLiteral = normalLiteral.substring(0, seppos) + "'";
}
StringBuffer val = new StringBuffer(26);
//noinspection HardCodedStringLiteral
val.append("TO_DATE(");
val.append(normalLiteral);
//noinspection HardCodedStringLiteral
val.append(", 'YYYY-MM-DD HH24:MI:SS')");
return val.toString();
}
//noinspection HardCodedStringLiteral
return "UNSUPPORTED:" + isoDate;
}
@Override
public boolean supportsAutoIncrement() {
return true;
}
@Override
public boolean supportsRestrictForeignKeys() {
return false;
}
@Override
public int getDataTypeMaxParameters(String dataTypeName) {
//noinspection HardCodedStringLiteral
if ("BINARY_FLOAT".equals(dataTypeName.toUpperCase())) {
return 0;
}
//noinspection HardCodedStringLiteral
if ("BINARY_DOUBLE".equals(dataTypeName.toUpperCase())) {
return 0;
}
return super.getDataTypeMaxParameters(dataTypeName);
}
@Override
public boolean jdbcCallsCatalogsSchemas() {
return true;
}
@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);
}
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() <= 128);
}
@Override
public boolean supportsSequences() {
return true;
}
@Override
public boolean supportsSchemas() {
return false;
}
@Override
protected String getConnectionCatalogName() throws DatabaseException {
if (getConnection() instanceof OfflineConnection) {
return getConnection().getCatalog();
}
try {
//noinspection HardCodedStringLiteral
return Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", this).queryForObject(new RawCallStatement("select sys_context( 'userenv', 'current_schema' ) from dual"), String.class);
} catch (Exception e) {
//noinspection HardCodedStringLiteral
Scope.getCurrentScope().getLog(getClass()).info("Error getting default schema", e);
}
return null;
}
@Override
public boolean isReservedWord(String objectName) {
return reservedWords.contains(objectName.toUpperCase());
}
@Override
protected String getDefaultDatabaseProductName() {
return PRODUCT_NAME;
}
@Override
public boolean isCorrectDatabaseImplementation(DatabaseConnection conn) throws DatabaseException {
return PRODUCT_NAME.equals(conn.getDatabaseProductName());
}
@Override
public String getDefaultDriver(String url) {
//noinspection HardCodedStringLiteral
if (url.startsWith("jdbc:dm")) {
return "dm.jdbc.driver.DmDriver";
}
return null;
}
@Override
public String getShortName() {
return "dm";
}
@Override
public Integer getDefaultPort() {
return PORT;
}
@Override
public boolean supportsInitiallyDeferrableColumns() {
return true;
}
@Override
public boolean supportsTablespaces() {
return false;
}
@Override
public int getPriority() {
return PRIORITY_DEFAULT;
}
public Set<String> getUserDefinedTypes() {
if (userDefinedTypes == null) {
userDefinedTypes = new HashSet<>();
if ((getConnection() != null) && !(getConnection() instanceof OfflineConnection)) {
try {
try {
//noinspection HardCodedStringLiteral
userDefinedTypes.addAll(Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", 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(Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", this).queryForList(new RawSqlStatement("SELECT TYPE_NAME FROM USER_TYPES"), String.class));
}
} catch (DatabaseException e) {
//ignore error
}
}
}
return userDefinedTypes;
}
}
- 打开
liquibase.snapshot.JdbcDatabaseSnapshot文件,修改私有化方法createSql
private String createSql(String catalogName, String schemaName, String tableName) throws SQLException {
CatalogAndSchema catalogAndSchema = new CatalogAndSchema(catalogName, schemaName).customize(database);
String jdbcCatalogName = database.correctObjectName(((AbstractJdbcDatabase) database).getJdbcCatalogName(catalogAndSchema), Catalog.class);
String jdbcSchemaName = database.correctObjectName(((AbstractJdbcDatabase) database).getJdbcSchemaName(catalogAndSchema), Schema.class);
Database database = JdbcDatabaseSnapshot.this.getDatabase();
String sql;
if( database instanceof Ingres9Database ) {
sql = "select CONSTRAINT_NAME, TABLE_NAME from iiconstraints where schema_name ='"
+ schemaName + "' and constraint_type='U'";
if (tableName != null) {
sql += " and table_name='" + tableName + "'";
}
} else if ((database instanceof MySQLDatabase) || (database instanceof HsqlDatabase) || (database
instanceof MariaDBDatabase)) {
sql = "select CONSTRAINT_NAME, TABLE_NAME "
+ "from " + database.getSystemSchema() + ".table_constraints "
+ "where constraint_schema='" + jdbcCatalogName + "' "
+ "and constraint_type='UNIQUE'";
if (tableName != null) {
sql += " and table_name='" + tableName + "'";
}
} else if (database instanceof PostgresDatabase) {
sql = "select CONSTRAINT_NAME, TABLE_NAME "
+ "from " + database.getSystemSchema() + ".table_constraints "
+ "where constraint_catalog='" + jdbcCatalogName + "' "
+ "and constraint_schema='" + jdbcSchemaName + "' "
+ "and constraint_type='UNIQUE'";
if (tableName != null) {
sql += " and table_name='" + tableName + "'";
}
} else if (database instanceof MSSQLDatabase) {
sql =
"SELECT " +
"[TC].[CONSTRAINT_NAME], " +
"[TC].[TABLE_NAME], " +
"[TC].[CONSTRAINT_CATALOG] AS INDEX_CATALOG, " +
"[TC].[CONSTRAINT_SCHEMA] AS INDEX_SCHEMA, " +
"[IDX].[TYPE_DESC], " +
"[IDX].[name] AS INDEX_NAME " +
"FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [TC] " +
"JOIN sys.indexes AS IDX ON IDX.name=[TC].[CONSTRAINT_NAME] AND object_schema_name(object_id)=[TC].[CONSTRAINT_SCHEMA] " +
"WHERE [TC].[CONSTRAINT_TYPE] = 'UNIQUE' " +
"AND [TC].[CONSTRAINT_CATALOG] = N'" + database.escapeStringForDatabase(jdbcCatalogName) + "' " +
"AND [TC].[CONSTRAINT_SCHEMA] = N'" + database.escapeStringForDatabase(jdbcSchemaName) + "'";
if (tableName != null) {
sql += " AND [TC].[TABLE_NAME] = N'" + database.escapeStringForDatabase(database.correctObjectName(tableName, Table.class)) + "'";
}
} else if (database instanceof OracleDatabase) {
warnAboutDbaRecycleBin();
sql = "select uc.owner AS CONSTRAINT_SCHEM, uc.constraint_name, uc.table_name,uc.status,uc.deferrable,uc.deferred,ui.tablespace_name, ui.index_name, ui.owner as INDEX_CATALOG, uc.VALIDATED as VALIDATED, ac.COLUMN_NAME as COLUMN_NAME " +
"from all_constraints uc " +
"join all_indexes ui on uc.index_name = ui.index_name and uc.owner=ui.table_owner and uc.table_name=ui.table_name " +
"LEFT OUTER JOIN " + (((OracleDatabase) database).canAccessDbaRecycleBin() ? "dba_recyclebin" : "user_recyclebin") + " d ON d.object_name=ui.table_name " +
"LEFT JOIN all_cons_columns ac ON ac.OWNER = uc.OWNER AND ac.TABLE_NAME = uc.TABLE_NAME AND ac.CONSTRAINT_NAME = uc.CONSTRAINT_NAME "+
"where uc.constraint_type='U' ";
if (tableName != null || getAllCatalogsStringScratchData() == null) {
sql += "and uc.owner = '" + jdbcSchemaName + "'";
} else {
sql += "and uc.owner IN ('" + jdbcSchemaName + "', " + getAllCatalogsStringScratchData() + ")";
}
sql += "AND d.object_name IS NULL ";
if (tableName != null) {
sql += " and uc.table_name = '" + tableName + "'";
}
} else if (database instanceof DB2Database) {
// if we are on DB2 AS400 iSeries
if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
sql = "select constraint_name as constraint_name, table_name as table_name from QSYS2.TABLE_CONSTRAINTS where table_schema='" + jdbcSchemaName + "' and constraint_type='UNIQUE'";
if (tableName != null) {
sql += " and table_name = '" + tableName + "'";
}
// DB2 z/OS
}
// here we are on DB2 UDB
else {
sql = "select distinct k.constname as constraint_name, t.tabname as TABLE_NAME from syscat.keycoluse k, syscat.tabconst t "
+ "where k.constname = t.constname "
+ "and t.tabschema = '" + jdbcSchemaName + "' "
+ "and t.type='U'";
if (tableName != null) {
sql += " and t.tabname = '" + tableName + "'";
}
}
} else if (database instanceof Db2zDatabase) {
sql = "select distinct k.constname as constraint_name, t.tbname as TABLE_NAME from SYSIBM.SYSKEYCOLUSE k, SYSIBM.SYSTABCONST t "
+ "where k.constname = t.constname "
+ "and k.TBCREATOR = t.TBCREATOR "
+ "and t.TBCREATOR = '" + jdbcSchemaName + "' ";
if (tableName != null) {
sql += " and t.tbname = '" + tableName + "'";
}
} else if (database instanceof FirebirdDatabase) {
sql = "SELECT TRIM(RDB$INDICES.RDB$INDEX_NAME) AS CONSTRAINT_NAME, " +
"TRIM(RDB$INDICES.RDB$RELATION_NAME) AS TABLE_NAME " +
"FROM RDB$INDICES "
+ "LEFT JOIN RDB$RELATION_CONSTRAINTS "
+ "ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDICES.RDB$INDEX_NAME "
+ "WHERE RDB$INDICES.RDB$UNIQUE_FLAG IS NOT NULL "
+ "AND ("
+ "RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_TYPE IS NULL "
+ "OR TRIM(RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_TYPE)='UNIQUE') "
+ "AND NOT(RDB$INDICES.RDB$INDEX_NAME LIKE 'RDB$%')";
if (tableName != null) {
sql += " AND TRIM(RDB$INDICES.RDB$RELATION_NAME)='" + tableName + "'";
}
// TODO 以下方法是新增内容 START
} else if(database instanceof DMDatabase){
sql = "select uc.owner AS CONSTRAINT_SCHEM, uc.constraint_name, uc.table_name,uc.status,uc.deferrable,uc.deferred,ui.tablespace_name, ui.index_name, ui.owner as INDEX_CATALOG " +
"from all_constraints uc " +
"join all_indexes ui on uc.index_name = ui.index_name and uc.owner=ui.table_owner and uc.table_name=ui.table_name " +
"where uc.constraint_type='U' ";
if (tableName != null || getAllCatalogsStringScratchData() == null) {
sql += "and uc.owner = '" + jdbcSchemaName + "'";
} else {
sql += "and uc.owner IN ('" + jdbcSchemaName + "', " + getAllCatalogsStringScratchData() + ")";
}
if (tableName != null) {
sql += " and uc.table_name = '" + tableName + "'";
}
}
// TODO 以上方法是新增内容 END
else if (database instanceof DerbyDatabase) {
sql = "select c.constraintname as CONSTRAINT_NAME, tablename AS TABLE_NAME "
+ "from sys.systables t, sys.sysconstraints c, sys.sysschemas s "
+ "where s.schemaname='" + jdbcCatalogName + "' "
+ "and t.tableid = c.tableid "
+ "and t.schemaid=s.schemaid "
+ "and c.type = 'U'";
if (tableName != null) {
sql += " AND t.tablename = '" + tableName + "'";
}
} else if (database instanceof InformixDatabase) {
sql = "select unique sysindexes.idxname as CONSTRAINT_NAME, sysindexes.idxtype, systables.tabname as TABLE_NAME "
+ "from sysindexes, systables "
+ "left outer join sysconstraints on sysconstraints.tabid = systables.tabid and sysconstraints.constrtype = 'P' "
+ "where sysindexes.tabid = systables.tabid and sysindexes.idxtype = 'U' "
+ "and sysconstraints.idxname != sysindexes.idxname "
+ "and sysconstraints.tabid = sysindexes.tabid";
if (tableName != null) {
sql += " and systables.tabname = '" + database.correctObjectName(tableName, Table.class) + "'";
}
} else if (database instanceof SybaseDatabase) {
Scope.getCurrentScope().getLog(getClass()).warning("Finding unique constraints not currently supported for Sybase");
return null; //TODO: find sybase sql
} else if (database instanceof SybaseASADatabase) {
sql = "select sysconstraint.constraint_name, sysconstraint.constraint_type, systable.table_name " +
"from sysconstraint, systable " +
"where sysconstraint.table_object_id = systable.object_id " +
"and sysconstraint.constraint_type = 'U'";
if (tableName != null) {
sql += " and systable.table_name = '" + tableName + "'";
}
} else {
sql = "select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME "
+ "from " + database.getSystemSchema() + ".constraints "
+ "where constraint_schema='" + jdbcSchemaName + "' "
+ "and constraint_catalog='" + jdbcCatalogName + "' "
+ "and constraint_type='UNIQUE'";
if (tableName != null) {
sql += " and table_name='" + tableName + "'";
}
}
return sql;
}
- 打开
liquibase.snapshot.jvm.SequenceSnapshotGenerator文件，修改getSelectSequenceSql内容
protected String getSelectSequenceSql(Schema schema, Database database) {
if (database instanceof DB2Database) {
if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
return "SELECT SEQNAME AS SEQUENCE_NAME FROM QSYS2.SYSSEQUENCES WHERE SEQSCHEMA = '" + schema.getCatalogName() + "'";
}
return "SELECT SEQNAME AS SEQUENCE_NAME FROM SYSCAT.SEQUENCES WHERE SEQTYPE='S' AND SEQSCHEMA = '" + schema.getCatalogName() + "'";
} else if (database instanceof Db2zDatabase) {
return "SELECT NAME AS SEQUENCE_NAME, " +
"START AS START_VALUE, " +
"MINVALUE AS MIN_VALUE, " +
"MAXVALUE AS MAX_VALUE, " +
"CACHE AS CACHE_SIZE, " +
"INCREMENT AS INCREMENT_BY, " +
"CYCLE AS WILL_CYCLE, " +
"ORDER AS IS_ORDERED " +
"FROM SYSIBM.SYSSEQUENCES WHERE SEQTYPE = 'S' AND SCHEMA = '" + schema.getCatalogName() + "'";
} else if (database instanceof DerbyDatabase) {
return "SELECT " +
" seq.SEQUENCENAME AS SEQUENCE_NAME " +
"FROM " +
" SYS.SYSSEQUENCES seq, " +
" SYS.SYSSCHEMAS sch " +
"WHERE " +
" sch.SCHEMANAME = '" + new CatalogAndSchema(null, schema.getName()).customize(database).getSchemaName() + "' AND " +
" sch.SCHEMAID = seq.SCHEMAID";
} else if (database instanceof FirebirdDatabase) {
return "SELECT TRIM(RDB$GENERATOR_NAME) AS SEQUENCE_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0";
} else if (database instanceof H2Database) {
return "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA = '" + schema.getName() + "' AND IS_GENERATED=FALSE";
} else if (database instanceof HsqlDatabase) {
return "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES WHERE SEQUENCE_SCHEMA = '" + schema.getName() + "'";
} else if (database instanceof InformixDatabase) {
return "SELECT tabname AS SEQUENCE_NAME FROM systables t, syssequences s WHERE s.tabid = t.tabid AND t.owner = '" + schema.getName() + "'";
} else if (database instanceof OracleDatabase) {
/*
* Return an SQL statement that only returns the non-default values so the output changeLog is cleaner
* and less polluted with unnecessary values.
* The the following pages for the defaults (consistent for all supported releases ATM):
* 12cR2: http://docs.oracle.com/database/122/SQLRF/CREATE-SEQUENCE.htm
* 12cR1: http://docs.oracle.com/database/121/SQLRF/statements_6017.htm
* 11gR2: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm
*/
return "SELECT sequence_name, \n" +
"CASE WHEN increment_by > 0 \n" +
" THEN CASE WHEN min_value=1 THEN NULL ELSE min_value END\n" +
" ELSE CASE WHEN min_value=(-999999999999999999999999999) THEN NULL else min_value END\n" +
"END AS min_value, \n" +
"CASE WHEN increment_by > 0 \n" +
" THEN CASE WHEN max_value=999999999999999999999999999 THEN NULL ELSE max_value END\n" +
" ELSE CASE WHEN max_value=last_number THEN NULL else max_value END \n" +
"END AS max_value, \n" +
"CASE WHEN increment_by = 1 THEN NULL ELSE increment_by END AS increment_by, \n" +
"CASE WHEN cycle_flag = 'N' THEN NULL ELSE cycle_flag END AS will_cycle, \n" +
"CASE WHEN order_flag = 'N' THEN NULL ELSE order_flag END AS is_ordered, \n" +
"LAST_NUMBER as START_VALUE, \n" +
"CASE WHEN cache_size = 20 THEN NULL ELSE cache_size END AS cache_size \n" +
"FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = '" + schema.getCatalogName() + "'";
} else if (database instanceof PostgresDatabase) {
int version = 9;
try {
version = database.getDatabaseMajorVersion();
} catch (Exception ignore) {
Scope.getCurrentScope().getLog(getClass()).warning("Failed to retrieve database version: " + ignore);
}
if (version < 10) { // 'pg_sequence' view does not exists yet
return "SELECT c.relname AS \"SEQUENCE_NAME\" FROM pg_class c " +
"join pg_namespace on c.relnamespace = pg_namespace.oid " +
"WHERE c.relkind='S' " +
"AND nspname = '" + schema.getName() + "' " +
"AND c.oid not in (select d.objid FROM pg_depend d where d.refobjsubid > 0)";
} else {
return "SELECT c.relname AS \"SEQUENCE_NAME\", " +
" s.seqmin AS \"MIN_VALUE\", s.seqmax AS \"MAX_VALUE\", s.seqincrement AS \"INCREMENT_BY\", " +
" s.seqcycle AS \"WILL_CYCLE\", s.seqstart AS \"START_VALUE\", s.seqcache AS \"CACHE_SIZE\", " +
" pg_catalog.format_type(s.seqtypid, NULL) AS \"SEQ_TYPE\" " +
"FROM pg_class c " +
"JOIN pg_namespace ns on c.relnamespace = ns.oid " +
"JOIN pg_sequence s on c.oid = s.seqrelid " +
"WHERE c.relkind = 'S' " +
"AND ns.nspname = '" + schema.getName() + "' " +
"AND c.oid not in (select d.objid FROM pg_depend d where d.refobjsubid > 0)";
}
} else if (database instanceof MSSQLDatabase) {
return "SELECT SEQUENCE_NAME, " +
"cast(START_VALUE AS BIGINT) AS START_VALUE, " +
"cast(MINIMUM_VALUE AS BIGINT) AS MIN_VALUE, " +
"cast(MAXIMUM_VALUE AS BIGINT) AS MAX_VALUE, " +
"CAST(INCREMENT AS BIGINT) AS INCREMENT_BY, " +
"CYCLE_OPTION AS WILL_CYCLE " +
"FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA = '" + schema.getName() + "'";
} else if (database instanceof MariaDBDatabase) {
StringJoiner j = new StringJoiner(" \n UNION\n");
try {
List<Map<String, ?>> res = Scope.getCurrentScope().getSingleton(ExecutorService.class)
.getExecutor("jdbc", database)
.queryForList(new RawSqlStatement("select table_name AS SEQUENCE_NAME " +
"from information_schema.TABLES " +
"where TABLE_SCHEMA = '" + schema.getName() + "' " +
"and TABLE_TYPE = 'SEQUENCE' order by table_name;"));
if (res.size() == 0) {
return "SELECT 'name' AS SEQUENCE_NAME from dual WHERE 1=0";
}
for (Map<String, ?> e : res) {
String seqName = (String) e.get("SEQUENCE_NAME");
j.add(String.format("SELECT '%s' AS SEQUENCE_NAME, " +
"START_VALUE AS START_VALUE, " +
"MINIMUM_VALUE AS MIN_VALUE, " +
"MAXIMUM_VALUE AS MAX_VALUE, " +
"INCREMENT AS INCREMENT_BY, " +
"CYCLE_OPTION AS WILL_CYCLE " +
"FROM %s ", seqName, seqName));
}
} catch (DatabaseException e) {
throw new UnexpectedLiquibaseException("Could not get list of schemas ", e);
}
return j.toString();
} else if (database instanceof SybaseASADatabase) {
return "SELECT SEQUENCE_NAME, " +
"START_WITH AS START_VALUE, " +
"MIN_VALUE, " +
"MAX_VALUE, " +
"INCREMENT_BY, " +
"CYCLE AS WILL_CYCLE " +
"FROM SYS.SYSSEQUENCE s " +
"JOIN SYS.SYSUSER u ON s.OWNER = u.USER_ID " +
"WHERE u.USER_NAME = '" + schema.getName() + "'";
// TODO 以下方法是新增内容 START
} else if (database instanceof DMDatabase) {
String sql = "SELECT sequence_name, \n" +
"CASE WHEN increment_by > 0 \n" +
" THEN CASE WHEN min_value=1 THEN NULL ELSE min_value END\n" +
" ELSE CASE WHEN min_value=(-999999999999999999999999999) THEN NULL else min_value END\n" +
"END AS min_value, \n" +
"CASE WHEN increment_by > 0 \n" +
" THEN CASE WHEN max_value=999999999999999999999999999 THEN NULL ELSE max_value END\n" +
" ELSE CASE WHEN max_value=last_number THEN NULL else max_value END \n" +
"END AS max_value, \n" +
"CASE WHEN increment_by = 1 THEN NULL ELSE increment_by END AS increment_by, \n" +
"CASE WHEN cycle_flag = 'N' THEN NULL ELSE cycle_flag END AS will_cycle, \n" +
"CASE WHEN order_flag = 'N' THEN NULL ELSE order_flag END AS is_ordered, \n" +
"LAST_NUMBER as START_VALUE, \n" +
"CASE WHEN cache_size = 20 THEN NULL ELSE cache_size END AS cache_size \n" +
"FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = '" + schema.getCatalogName() + "'";
return sql;
}
// TODO 以上方法是新增内容 END
else {
throw new UnexpectedLiquibaseException("Don't know how to query for sequences on " + database);
}
}
- 打开
liquibase.snapshot.jvm.UniqueConstraintSnapshotGenerator,修改listColumns方法
protected List<Map<String, ?>> listColumns(UniqueConstraint example, Database database, DatabaseSnapshot snapshot) throws DatabaseException {
Relation table = example.getRelation();
Schema schema = table.getSchema();
String name = example.getName();
boolean bulkQuery;
String sql;
String cacheKey = "uniqueConstraints-" + example.getClass().getSimpleName() + "-" + example.getSchema().toCatalogAndSchema().customize(database).toString();
String queryCountKey = "uniqueConstraints-" + example.getClass().getSimpleName() + "-queryCount";
Map<String, List<Map<String, ?>>> columnCache = (Map<String, List<Map<String, ?>>>) snapshot.getScratchData(cacheKey);
Integer columnQueryCount = (Integer) snapshot.getScratchData(queryCountKey);
if (columnQueryCount == null) {
columnQueryCount = 0;
}
if (columnCache == null) {
bulkQuery = false;
if (columnQueryCount > 3) {
bulkQuery = supportsBulkQuery(database);
}
snapshot.setScratchData(queryCountKey, columnQueryCount + 1);
if ((database instanceof MySQLDatabase) || (database instanceof HsqlDatabase)) {
sql = "select const.CONSTRAINT_NAME, COLUMN_NAME, const.constraint_schema as CONSTRAINT_CONTAINER "
+ "from " + database.getSystemSchema() + ".table_constraints const "
+ "join " + database.getSystemSchema() + ".key_column_usage col "
+ "on const.constraint_schema=col.constraint_schema "
+ "and const.table_name=col.table_name "
+ "and const.constraint_name=col.constraint_name "
+ "where const.constraint_schema='" + database.correctObjectName(schema.getCatalogName(), Catalog.class) + "' ";
if (!bulkQuery) {
sql += "and const.table_name='" + database.correctObjectName(example.getRelation().getName(), Table.class) + "' "
+ "and const.constraint_name='" + database.correctObjectName(name, UniqueConstraint.class) + "'";
}
sql += "order by ordinal_position";
} else if (database instanceof PostgresDatabase) {
sql = "select const.CONSTRAINT_NAME, COLUMN_NAME, const.constraint_schema as CONSTRAINT_CONTAINER "
+ "from " + database.getSystemSchema() + ".table_constraints const "
+ "join " + database.getSystemSchema() + ".key_column_usage col "
+ "on const.constraint_schema=col.constraint_schema "
+ "and const.table_name=col.table_name "
+ "and const.constraint_name=col.constraint_name "
+ "where const.constraint_catalog='" + database.correctObjectName(schema.getCatalogName(), Catalog.class) + "' ";
if (database instanceof CockroachDatabase) {
sql += " and (select count(*) from (select indexdef from pg_indexes where schemaname='" + database.correctObjectName(schema.getSchema().getName(), Schema.class) + "' AND indexname='" + database.correctObjectName(name, UniqueConstraint.class) + "' AND (position('DESC,' in indexdef) > 0 OR position('DESC)' in indexdef) > 0))) = 0"
+ "and const.constraint_name != 'primary' ";
}
sql += "and const.constraint_schema='" + database.correctObjectName(schema.getSchema().getName(), Schema.class) + "' ";
if (!bulkQuery) {
sql += "and const.table_name='" + database.correctObjectName(example.getRelation().getName(), Table.class) + "' "
+ "and const.constraint_name='" + database.correctObjectName(name, UniqueConstraint.class) + "'";
}
sql += "order by ordinal_position";
} else if (database instanceof MSSQLDatabase) {
sql =
"SELECT " +
"[kc].[name] AS [CONSTRAINT_NAME], " +
"s.name AS constraint_container, " +
"[c].[name] AS [COLUMN_NAME], " +
"CASE [ic].[is_descending_key] WHEN 0 THEN N'A' WHEN 1 THEN N'D' END AS [ASC_OR_DESC] " +
"FROM [sys].[schemas] AS [s] " +
"INNER JOIN [sys].[tables] AS [t] " +
"ON [t].[schema_id] = [s].[schema_id] " +
"INNER JOIN [sys].[key_constraints] AS [kc] " +
"ON [kc].[parent_object_id] = [t].[object_id] " +
"INNER JOIN [sys].[indexes] AS [i] " +
"ON [i].[object_id] = [kc].[parent_object_id] " +
"AND [i].[index_id] = [kc].[unique_index_id] " +
"INNER JOIN [sys].[index_columns] AS [ic] " +
"ON [ic].[object_id] = [i].[object_id] " +
"AND [ic].[index_id] = [i].[index_id] " +
"INNER JOIN [sys].[columns] AS [c] " +
"ON [c].[object_id] = [ic].[object_id] " +
"AND [c].[column_id] = [ic].[column_id] " +
"WHERE [s].[name] = N'" + database.escapeStringForDatabase(database.correctObjectName(schema.getName(), Schema.class)) + "' ";
if (!bulkQuery) {
sql += "AND [t].[name] = N'" + database.escapeStringForDatabase(database.correctObjectName(example.getRelation().getName(), Table.class)) + "' " +
"AND [kc].[name] = N'" + database.escapeStringForDatabase(database.correctObjectName(name, UniqueConstraint.class)) + "' ";
}
sql += "ORDER BY " +
"[ic].[key_ordinal]";
} else if (database instanceof OracleDatabase) {
sql = "select ucc.owner as constraint_container, ucc.constraint_name as constraint_name, ucc.column_name, f.validated as constraint_validate " +
"from all_cons_columns ucc " +
"INNER JOIN all_constraints f " +
"ON ucc.owner = f.owner " +
"AND ucc.constraint_name = f.constraint_name " +
"where " +
(bulkQuery ? "" : "ucc.constraint_name='" + database.correctObjectName(name, UniqueConstraint.class) + "' and ") +
"ucc.owner='" + database.correctObjectName(schema.getCatalogName(), Catalog.class) + "' " +
"and ucc.table_name not like 'BIN$%' " +
"order by ucc.position";
} else if (database instanceof DB2Database) {
if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
sql = "select T1.constraint_name as CONSTRAINT_NAME, T2.COLUMN_NAME as COLUMN_NAME, T1.CONSTRAINT_SCHEMA as CONSTRAINT_CONTAINER from QSYS2.TABLE_CONSTRAINTS T1, QSYS2.SYSCSTCOL T2\n"
+ "where T1.CONSTRAINT_TYPE='UNIQUE' and T1.CONSTRAINT_NAME=T2.CONSTRAINT_NAME\n"
+ "and T1.CONSTRAINT_SCHEMA='" + database.correctObjectName(schema.getName(), Schema.class) + "'\n"
+ "and T2.CONSTRAINT_SCHEMA='" + database.correctObjectName(schema.getName(), Schema.class) + "'\n"
//+ "T2.TABLE_NAME='"+ database.correctObjectName(example.getTable().getName(), Table.class) + "'\n"
//+ "\n"
+ "order by T2.COLUMN_NAME\n";
} else {
sql = "select k.constname as constraint_name, k.colname as column_name from syscat.keycoluse k, syscat.tabconst t "
+ "where k.constname = t.constname "
+ "and k.tabschema = t.tabschema "
+ "and t.type='U' "
+ (bulkQuery? "" : "and k.constname='" + database.correctObjectName(name, UniqueConstraint.class) + "' ")
+ "and t.tabschema = '" + database.correctObjectName(schema.getName(), Schema.class) + "' "
+ "order by colseq";
}
} else if (database instanceof DerbyDatabase) {
//does not support bulkQuery, supportsBulkQuery should return false()
sql = "SELECT cg.descriptor as descriptor, t.tablename "
+ "FROM sys.sysconglomerates cg "
+ "JOIN sys.syskeys k ON cg.conglomerateid = k.conglomerateid "
+ "JOIN sys.sysconstraints c ON c.constraintid = k.constraintid "
+ "JOIN sys.systables t ON c.tableid = t.tableid "
+ "WHERE c.constraintname='" + database.correctObjectName(name, UniqueConstraint.class) + "'";
List<Map<String, ?>> rows = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement(sql));
List<Map<String, ?>> returnList = new ArrayList<>();
if (rows.isEmpty()) {
return returnList;
} else if (rows.size() > 1) {
throw new UnexpectedLiquibaseException("Got multiple rows back querying unique constraints");
} else {
Map rowData = rows.get(0);
String descriptor = rowData.get("DESCRIPTOR").toString();
descriptor = descriptor.replaceFirst(".*\\(", "").replaceFirst("\\).*", "");
for (String columnNumber : StringUtil.splitAndTrim(descriptor, ",")) {
String columnName = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForObject(new RawSqlStatement(
"select c.columnname from sys.syscolumns c "
+ "join sys.systables t on t.tableid=c.referenceid "
+ "where t.tablename='" + rowData.get("TABLENAME") + "' and c.columnnumber=" + columnNumber), String.class);
Map<String, String> row = new HashMap<>();
row.put("COLUMN_NAME", columnName);
returnList.add(row);
}
return returnList;
}
} else if (database instanceof FirebirdDatabase) {
//does not support bulkQuery, supportsBulkQuery should return false()
// Careful! FIELD_NAME and INDEX_NAME in RDB$INDEX_SEGMENTS are CHAR, not VARCHAR columns.
sql = "SELECT TRIM(RDB$INDEX_SEGMENTS.RDB$FIELD_NAME) AS column_name " +
"FROM RDB$INDEX_SEGMENTS " +
"LEFT JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME " +
"WHERE UPPER(TRIM(RDB$INDICES.RDB$INDEX_NAME))='" + database.correctObjectName(name, UniqueConstraint.class) + "' " +
"ORDER BY RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION";
} else if (database instanceof SybaseASADatabase) {
//does not support bulkQuery, supportsBulkQuery should return false()
sql = "select sysconstraint.constraint_name, syscolumn.column_name " +
"from sysconstraint, syscolumn, systable " +
"where sysconstraint.ref_object_id = syscolumn.object_id " +
"and sysconstraint.table_object_id = systable.object_id " +
"and sysconstraint.constraint_name = '" + database.correctObjectName(name, UniqueConstraint.class) + "' " +
"and systable.table_name = '" + database.correctObjectName(example.getRelation().getName(), Table.class) + "'";
} else if(database instanceof Ingres9Database) {
//does not support bulkQuery, supportsBulkQuery should return false()
sql = "select constraint_name, column_name " +
"from iikeys " +
"where constraint_name = '" + database.correctObjectName(name, UniqueConstraint.class) + "' " +
"and table_name = '" + database.correctObjectName(example.getTable().getName(), Table.class) + "'";
} else if (database instanceof InformixDatabase) {
//does not support bulkQuery, supportsBulkQuery should return false()
sql = getUniqueConstraintsSqlInformix((InformixDatabase) database, schema, name);
// TODO 以下方法是新增内容 START
} else if(database instanceof DMDatabase){
sql = "select ucc.owner as constraint_container, ucc.constraint_name as constraint_name, ucc.column_name, f.validated as constraint_validate " +
"from all_cons_columns ucc " +
"INNER JOIN all_constraints f " +
"ON ucc.owner = f.owner " +
"AND ucc.constraint_name = f.constraint_name " +
"where " +
(bulkQuery ? "" : "ucc.constraint_name='" + database.correctObjectName(name, UniqueConstraint.class) + "' and ") +
"ucc.owner='" + database.correctObjectName(schema.getCatalogName(), Catalog.class) + "' " +
"and ucc.table_name not like 'BIN$%' "+
"order by ucc.position";
// TODO 以上方法是新增内容 END
} else {
// If we do not have a specific handler for the RDBMS, we assume that the database has an
// INFORMATION_SCHEMA we can use. This is a last-resort measure and might fail.
String catalogName = database.correctObjectName(schema.getCatalogName(), Catalog.class);
String schemaName = database.correctObjectName(schema.getName(), Schema.class);
String constraintName = database.correctObjectName(name, UniqueConstraint.class);
String tableName = database.correctObjectName(table.getName(), Table.class);
sql = "select CONSTRAINT_NAME, COLUMN_LIST as COLUMN_NAME, constraint_schema as CONSTRAINT_CONTAINER "
+ "from " + database.getSystemSchema() + ".constraints "
+ "where constraint_type='UNIQUE' ";
if (catalogName != null) {
sql += "and constraint_catalog='" + catalogName + "' ";
}
if (schemaName != null) {
sql += "and constraint_schema='" + schemaName + "' ";
}
if (!bulkQuery) {
if (tableName != null) {
sql += "and table_name='" + tableName + "' ";
}
if (constraintName != null) {
sql += "and constraint_name='" + constraintName + "'";
}
}
}
List<Map<String, ?>> rows = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement(sql));
if (bulkQuery) {
columnCache = new HashMap<>();
snapshot.setScratchData(cacheKey, columnCache);
for (Map<String, ?> row : rows) {
String key = row.get("CONSTRAINT_CONTAINER") + "_" + row.get("CONSTRAINT_NAME");
List<Map<String, ?>> constraintRows = columnCache.get(key);
if (constraintRows == null) {
constraintRows = new ArrayList<>();
columnCache.put(key, constraintRows);
}
constraintRows.add(row);
}
return listColumns(example, database, snapshot);
} else {
return rows;
}
} else {
String lookupKey = schema.getName() + "_" + example.getName();
List<Map<String, ?>> rows = columnCache.get(lookupKey);
if (rows == null) {
rows = new ArrayList<>();
}
return rows;
}
}
- 打开
src\main\resources\META-INF\services\liquibase.database.Database,新增liquibase.database.core.DMDatabase,如下
liquibase.database.core.CockroachDatabase
liquibase.database.core.DB2Database
liquibase.database.core.Db2zDatabase
liquibase.database.core.DerbyDatabase
liquibase.database.core.Firebird3Database
liquibase.database.core.FirebirdDatabase
liquibase.database.core.H2Database
liquibase.database.core.HsqlDatabase
liquibase.database.core.InformixDatabase
liquibase.database.core.Ingres9Database
liquibase.database.core.MSSQLDatabase
liquibase.database.core.MariaDBDatabase
liquibase.database.core.MockDatabase
liquibase.database.core.MySQLDatabase
liquibase.database.core.OracleDatabase
liquibase.database.core.PostgresDatabase
liquibase.database.core.SQLiteDatabase
liquibase.database.core.SybaseASADatabase
liquibase.database.core.SybaseDatabase
liquibase.database.core.UnsupportedDatabase
liquibase.database.core.DMDatabase
liquibase.database.core.KingBaseDatabase
7.在 liquibase.sqlgenerator.core 包下,新增文件
- AddDefaultValueGeneratorDM
package liquibase.sqlgenerator.core;
import liquibase.database.Database;
import liquibase.database.core.DMDatabase;
import liquibase.datatype.DataTypeFactory;
import liquibase.sql.Sql;
import liquibase.sql.UnparsedSql;
import liquibase.sqlgenerator.SqlGeneratorChain;
import liquibase.statement.core.AddDefaultValueStatement;
public class AddDefaultValueGeneratorDM extends AddDefaultValueGenerator {
@Override
public int getPriority() {
return PRIORITY_DATABASE;
}
@Override
public boolean supports(AddDefaultValueStatement statement, Database database) {
return database instanceof DMDatabase;
}
@Override
public Sql[] generateSql(AddDefaultValueStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
Object defaultValue = statement.getDefaultValue();
return new Sql[]{
new UnparsedSql("ALTER TABLE " + database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName()) + " MODIFY " + database.escapeColumnName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName(), statement.getColumnName()) + " DEFAULT " + DataTypeFactory.getInstance().fromObject(defaultValue, database).objectToSql(defaultValue, database),
getAffectedColumn(statement))
};
}
}
- InsertOrUpdateGeneratorDM
package liquibase.sqlgenerator.core;
import liquibase.Scope;
import liquibase.database.Database;
import liquibase.database.core.DMDatabase;
import liquibase.executor.ExecutorService;
import liquibase.executor.LoggingExecutor;
import liquibase.statement.core.InsertOrUpdateStatement;
public class InsertOrUpdateGeneratorDM extends InsertOrUpdateGenerator {
@Override
public boolean supports(InsertOrUpdateStatement statement, Database database) {
return database instanceof DMDatabase;
}
@Override
protected String getRecordCheck(InsertOrUpdateStatement insertOrUpdateStatement, Database database, String whereClause) {
StringBuffer recordCheckSql = new StringBuffer();
recordCheckSql.append("DECLARE\n");
recordCheckSql.append("\tv_reccount NUMBER := 0;\n");
recordCheckSql.append("BEGIN\n");
recordCheckSql.append("\tSELECT COUNT(*) INTO v_reccount FROM " + database.escapeTableName(insertOrUpdateStatement.getCatalogName(), insertOrUpdateStatement.getSchemaName(), insertOrUpdateStatement.getTableName()) + " WHERE ");
recordCheckSql.append(whereClause);
recordCheckSql.append(";\n");
recordCheckSql.append("\tIF v_reccount = 0 THEN\n");
return recordCheckSql.toString();
}
@Override
protected String getElse(Database database){
return "\tELSIF v_reccount = 1 THEN\n";
}
@Override
protected String getPostUpdateStatements(Database database){
StringBuffer endStatements = new StringBuffer();
endStatements.append("END IF;\n");
endStatements.append("END;\n");
if (Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database) instanceof LoggingExecutor) {
endStatements.append("/\n");
}
return endStatements.toString();
}
}
- GetViewDefinitionGeneratorDM
package liquibase.sqlgenerator.core;
import liquibase.CatalogAndSchema;
import liquibase.database.Database;
import liquibase.database.core.DMDatabase;
import liquibase.sql.Sql;
import liquibase.sql.UnparsedSql;
import liquibase.sqlgenerator.SqlGeneratorChain;
import liquibase.statement.core.GetViewDefinitionStatement;
public class GetViewDefinitionGeneratorDM extends GetViewDefinitionGenerator {
@Override
public int getPriority() {
return PRIORITY_DATABASE;
}
@Override
public boolean supports(GetViewDefinitionStatement statement, Database database) {
return database instanceof DMDatabase;
}
@Override
public Sql[] generateSql(GetViewDefinitionStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
CatalogAndSchema schema = new CatalogAndSchema(statement.getCatalogName(), statement.getSchemaName()).customize(database);
return new Sql[]{
new UnparsedSql("SELECT TEXT FROM ALL_VIEWS WHERE upper(VIEW_NAME)='" + statement.getViewName().toUpperCase() + "' AND OWNER='" + schema.getSchemaName() + "'")
};
}
}
- 打开
src\main\resources\META-INF\services\liquibase.sqlgenerator.SqlGenerator,新增
liquibase.sqlgenerator.core.AddDefaultValueGeneratorDM、
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorDM、liquibase.sqlgenerator.core.InsertOrUpdateGeneratorDM,如下
liquibase.sqlgenerator.core.AddAutoIncrementGenerator
liquibase.sqlgenerator.core.AddAutoIncrementGeneratorDB2
liquibase.sqlgenerator.core.AddAutoIncrementGeneratorHsqlH2
liquibase.sqlgenerator.core.AddAutoIncrementGeneratorInformix
liquibase.sqlgenerator.core.AddAutoIncrementGeneratorMySQL
liquibase.sqlgenerator.core.AddAutoIncrementGeneratorSQLite
liquibase.sqlgenerator.core.AddColumnGenerator
liquibase.sqlgenerator.core.AddColumnGeneratorDefaultClauseBeforeNotNull
liquibase.sqlgenerator.core.AddColumnGeneratorSQLite
liquibase.sqlgenerator.core.AddDefaultValueGenerator
liquibase.sqlgenerator.core.AddDefaultValueGeneratorDerby
liquibase.sqlgenerator.core.AddDefaultValueGeneratorDM
liquibase.sqlgenerator.core.AddDefaultValueGeneratorInformix
liquibase.sqlgenerator.core.AddDefaultValueGeneratorMSSQL
liquibase.sqlgenerator.core.AddDefaultValueGeneratorMySQL
liquibase.sqlgenerator.core.AddDefaultValueGeneratorOracle
liquibase.sqlgenerator.core.AddDefaultValueGeneratorKingBase
liquibase.sqlgenerator.core.AddDefaultValueGeneratorPostgres
liquibase.sqlgenerator.core.AddDefaultValueGeneratorSQLite
liquibase.sqlgenerator.core.AddDefaultValueGeneratorSybase
liquibase.sqlgenerator.core.AddDefaultValueGeneratorSybaseASA
liquibase.sqlgenerator.core.AddForeignKeyConstraintGenerator
liquibase.sqlgenerator.core.AddPrimaryKeyGenerator
liquibase.sqlgenerator.core.AddPrimaryKeyGeneratorInformix
liquibase.sqlgenerator.core.AddUniqueConstraintGenerator
liquibase.sqlgenerator.core.AddUniqueConstraintGeneratorInformix
liquibase.sqlgenerator.core.AddUniqueConstraintGeneratorTDS
liquibase.sqlgenerator.core.AlterSequenceGenerator
liquibase.sqlgenerator.core.BatchDmlExecutablePreparedStatementGenerator
liquibase.sqlgenerator.core.ClearDatabaseChangeLogTableGenerator
liquibase.sqlgenerator.core.CommentGenerator
liquibase.sqlgenerator.core.CopyRowsGenerator
liquibase.sqlgenerator.core.CreateDatabaseChangeLogLockTableGenerator
liquibase.sqlgenerator.core.CreateDatabaseChangeLogTableGenerator
liquibase.sqlgenerator.core.CreateDatabaseChangeLogTableGeneratorSybase
liquibase.sqlgenerator.core.CreateIndexGenerator
liquibase.sqlgenerator.core.CreateIndexGeneratorFirebird
liquibase.sqlgenerator.core.CreateIndexGeneratorPostgres
liquibase.sqlgenerator.core.CreateIndexGeneratorKingBase
liquibase.sqlgenerator.core.CreateProcedureGenerator
liquibase.sqlgenerator.core.CreateSequenceGenerator
liquibase.sqlgenerator.core.CreateTableGenerator
liquibase.sqlgenerator.core.CreateTableGeneratorInformix
liquibase.sqlgenerator.core.CreateViewGenerator
liquibase.sqlgenerator.core.CreateViewGeneratorInformix
liquibase.sqlgenerator.core.DeleteGenerator
liquibase.sqlgenerator.core.DropColumnGenerator
liquibase.sqlgenerator.core.DropDefaultValueGenerator
liquibase.sqlgenerator.core.DropForeignKeyConstraintGenerator
liquibase.sqlgenerator.core.DropIndexGenerator
liquibase.sqlgenerator.core.DropPrimaryKeyGenerator
liquibase.sqlgenerator.core.DropProcedureGenerator
liquibase.sqlgenerator.core.DropSequenceGenerator
liquibase.sqlgenerator.core.DropTableGenerator
liquibase.sqlgenerator.core.DropUniqueConstraintGenerator
liquibase.sqlgenerator.core.DropViewGenerator
liquibase.sqlgenerator.core.GetNextChangeSetSequenceValueGenerator
liquibase.sqlgenerator.core.GetNextChangeSetSequenceValueGeneratorKingBase
liquibase.sqlgenerator.core.GetViewDefinitionGenerator
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorDB2
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorDerby
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorDM
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorFirebird
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorHsql
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorInformix
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorMSSQL
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorOracle
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorPostgres
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorKingBase
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorSybase
liquibase.sqlgenerator.core.GetViewDefinitionGeneratorSybaseASA
liquibase.sqlgenerator.core.InitializeDatabaseChangeLogLockTableGenerator
liquibase.sqlgenerator.core.InsertDataChangeGenerator
liquibase.sqlgenerator.core.InsertGenerator
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorDB2
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorDM
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorH2
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorHsql
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorInformix
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorMSSQL
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorMySQL
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorOracle
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorKingBase
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorPostgres
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorSQLite
liquibase.sqlgenerator.core.InsertOrUpdateGeneratorSybaseASA
liquibase.sqlgenerator.core.InsertSetGenerator
liquibase.sqlgenerator.core.LockDatabaseChangeLogGenerator
liquibase.sqlgenerator.core.MarkChangeSetRanGenerator
liquibase.sqlgenerator.core.ModifyDataTypeGenerator
liquibase.sqlgenerator.core.RawSqlGenerator
liquibase.sqlgenerator.core.ReindexGeneratorSQLite
liquibase.sqlgenerator.core.RemoveChangeSetRanStatusGenerator
liquibase.sqlgenerator.core.RenameColumnGenerator
liquibase.sqlgenerator.core.RenameSequenceGenerator
liquibase.sqlgenerator.core.RenameTableGenerator
liquibase.sqlgenerator.core.RenameViewGenerator
liquibase.sqlgenerator.core.ReorganizeTableGeneratorDB2
liquibase.sqlgenerator.core.RuntimeGenerator
liquibase.sqlgenerator.core.SelectFromDatabaseChangeLogGenerator
liquibase.sqlgenerator.core.SelectFromDatabaseChangeLogLockGenerator
liquibase.sqlgenerator.core.SetColumnRemarksGenerator
liquibase.sqlgenerator.core.SetNullableGenerator
liquibase.sqlgenerator.core.SetTableRemarksGenerator
liquibase.sqlgenerator.core.StoredProcedureGenerator
liquibase.sqlgenerator.core.TableRowCountGenerator
liquibase.sqlgenerator.core.TagDatabaseGenerator
liquibase.sqlgenerator.core.UnlockDatabaseChangeLogGenerator
liquibase.sqlgenerator.core.UpdateChangeSetChecksumGenerator
liquibase.sqlgenerator.core.UpdateDataChangeGenerator
liquibase.sqlgenerator.core.UpdateGenerator
8.找到liquibase.sqlgenerator.core.InsertGenerator.java文件，修改其中的内容，因数据库表主键为IDENTITY，并且CSV指定主键值加载初始化数据时，会报否则会报java.sql.BatchUpdateException: 仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值错误。需要开启SET IDENTITY_INSERT 表名 ON;
public void generateHeader(StringBuilder sql,InsertStatement statement, Database database) {
sql.append("INSERT INTO ")
.append(database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName()))
.append(" (");
for (String column : statement.getColumnValues().keySet()) {
// TODO 达梦数据库下，当存在字段名为ID时，打开手动指定主键值开关，运行完语句后会自动关闭。因此处无法获取到当前数据库表是否存在IDENTITY的主键，所以需要各自项目中进行特殊的处理。
if (database instanceof DMDatabase && "id".equals(column)) {
sql.insert(0, "SET IDENTITY_INSERT " + database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName()) + " ON;\n");
}
sql.append(database.escapeColumnName(statement.getCatalogName(), statement.getSchemaName(), statement.getTableName(), column)).append(", ");
}
sql.deleteCharAt(sql.lastIndexOf(" "));
int lastComma = sql.lastIndexOf(",");
if (lastComma >= 0) {
sql.deleteCharAt(lastComma);
}
sql.append(") VALUES ");
}
- 找到
liquibase.statement.InsertExecutablePreparedStatement.java，修改如下代码，作用和8相同.否则会报java.sql.BatchUpdateException: 仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值错误
@Override
protected String generateSql(List<ColumnConfig> cols) {
sql.append("INSERT INTO ");
StringBuilder params = new StringBuilder("VALUES(");
sql.append(database.escapeTableName(getCatalogName(), getSchemaName(), getTableName()));
sql.append("(");
for(ColumnConfig column : getColumns()) {
if(database.supportsAutoIncrement()
&& Boolean.TRUE.equals(column.isAutoIncrement())) {
continue;
}
// TODO 达梦数据库下，当存在字段名为ID时，打开手动指定主键值开关，运行完语句后会自动关闭。因此处无法获取到当前数据库表是否存在IDENTITY的主键，所以需要各自项目中进行特殊的处理。
if (database instanceof DMDatabase && "id".equals(column.getName())) {
sql.insert(0, "SET IDENTITY_INSERT " + database.escapeTableName(getCatalogName(), getSchemaName(), getTableName()) + " ON;\n");
}
sql.append(database.escapeColumnName(getCatalogName(), getSchemaName(), getTableName(), column.getName()));
sql.append(", ");
params.append("?, ");
cols.add(column);
}
sql.deleteCharAt(sql.lastIndexOf(" "));
sql.deleteCharAt(sql.lastIndexOf(","));
params.deleteCharAt(params.lastIndexOf(" "));
params.deleteCharAt(params.lastIndexOf(","));
params.append(")");
sql.append(") ");
sql.append(params);
return sql.toString();
}
- 找到
liquibase.sqlgenerator.core.ModifyDataTypeGenerator.java文件，修改如下代码
/**
* @return either "MODIFY" or "ALTER COLUMN" depending on the current db
*/
protected String getModifyString(Database database) {
if ((database instanceof SybaseASADatabase) || (database instanceof SybaseDatabase) || (database instanceof
MySQLDatabase) || (database instanceof OracleDatabase) || (database instanceof InformixDatabase) || (database instanceof DMDatabase )
) {
return "MODIFY";
} else {
return "ALTER COLUMN";
}
}
/**
* @return the string that comes before the column type
* definition (like 'set data type' for derby or an open parentheses for Oracle)
*/
protected String getPreDataTypeString(Database database) {
if ((database instanceof DerbyDatabase) || (database instanceof AbstractDb2Database)) {
return " SET DATA TYPE ";
} else if ((database instanceof SybaseASADatabase) || (database instanceof SybaseDatabase) || (database
instanceof MSSQLDatabase) || (database instanceof MySQLDatabase) || (database instanceof HsqlDatabase) ||
(database instanceof H2Database) || (database instanceof OracleDatabase) || (database instanceof
InformixDatabase) || (database instanceof DMDatabase )) {
return " ";
} else {
return " TYPE ";
}
}
11.修改boolean对应的bit类型。不修改会报如下错误：【Caused by: liquibase.exception.DatabaseException: Error executing SQL UPDATE FLW_EV_DATABASECHANGELOGLOCK SET LOCKED = TRUE, LOCKEDBY = ‘LAPTOP-RO0A74NR (192.168.6.154)’, LOCKGRANTED = ‘2020-08-06 17:06:10.275’ WHERE ID = 1 AND LOCKED = FALSE: 第1 行附近出现错误: 数据类型不匹配】
解决办法：找到文件
liquibase.datatype.core.BooleanType，修改为如下内容
package liquibase.datatype.core;
import liquibase.change.core.LoadDataChange;
import liquibase.database.Database;
import liquibase.database.core.*;
import liquibase.datatype.DataTypeInfo;
import liquibase.datatype.DatabaseDataType;
import liquibase.datatype.LiquibaseDataType;
import liquibase.exception.UnexpectedLiquibaseException;
import liquibase.statement.DatabaseFunction;
import liquibase.util.StringUtil;
import java.util.Locale;
@DataTypeInfo(name = "boolean", aliases = {"java.sql.Types.BOOLEAN", "java.lang.Boolean", "bit", "bool"}, minParameters = 0, maxParameters = 0, priority = LiquibaseDataType.PRIORITY_DEFAULT)
public class BooleanType extends LiquibaseDataType {
@Override
public DatabaseDataType toDatabaseDataType(Database database) {
String originalDefinition = StringUtil.trimToEmpty(getRawDefinition());
if ((database instanceof Firebird3Database)) {
return new DatabaseDataType("BOOLEAN");
}
if ((database instanceof Db2zDatabase) || (database instanceof FirebirdDatabase)) {
return new DatabaseDataType("SMALLINT");
} else if (database instanceof MSSQLDatabase) {
return new DatabaseDataType(database.escapeDataTypeName("bit"));
} else if (database instanceof MySQLDatabase) {
if (originalDefinition.toLowerCase(Locale.US).startsWith("bit")) {
return new DatabaseDataType("BIT", getParameters());
}
return new DatabaseDataType("BIT", 1);
} else if (database instanceof OracleDatabase) {
return new DatabaseDataType("NUMBER", 1);
} else if ((database instanceof SybaseASADatabase) || (database instanceof SybaseDatabase)) {
return new DatabaseDataType("BIT");
} else if (database instanceof DerbyDatabase) {
if (((DerbyDatabase) database).supportsBooleanDataType()) {
return new DatabaseDataType("BOOLEAN");
} else {
return new DatabaseDataType("SMALLINT");
}
} else if (database.getClass().isAssignableFrom(DB2Database.class)) {
if (((DB2Database) database).supportsBooleanDataType())
return new DatabaseDataType("BOOLEAN");
else
return new DatabaseDataType("SMALLINT");
} else if (database instanceof HsqlDatabase) {
return new DatabaseDataType("BOOLEAN");
} else if (database instanceof PostgresDatabase) {
if (originalDefinition.toLowerCase(Locale.US).startsWith("bit")) {
return new DatabaseDataType("BIT", getParameters());
}
// TODO 以下方法是新增内容
} else if (database instanceof DMDatabase) {
return new DatabaseDataType("bit");
}
return super.toDatabaseDataType(database);
}
@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;
}
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
// TODO 以下方法是新增内容
|| (database instanceof DMDatabase));
}
/**
* 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";
}
@Override
public LoadDataChange.LOAD_DATA_TYPE getLoadTypeName() {
return LoadDataChange.LOAD_DATA_TYPE.BOOLEAN;
}
}
二、达梦数据库报错问题解答
- 数据类型的变更无效[Failed SQL:(-6160) ALTER TABLE ROOT.bpm_form_definition MODIFY form_json CLOB]
达梦数据库本身不支持通过语句直接将varchar字段修改为text或blob、clob等字段，所以liquibase的modify标签不生效。但可以修改varchar的长度。
- 不能同时包含聚集KEY和大字段
-- 在指定模式下运行sql语句，重启数据库 -- 禁止达梦数据库创建表时，对主键字段创建索引 sp_set_para_value(1,'PK_WITH_CLUSTER',0); -- 如果表已经存在，设置了当前项之后，需要删除表重新建
- 达梦数据库集成ACTIVITI、Springboot，需要指定如下信息，除此之外，还会出现大字段和主键冲突问题，需要在指定模式下运行sql语句，才可启动项目。
@Configuration
public class ActivitiConfig extends AbstractProcessEngineAutoConfiguration {
private final Logger logger = LoggerFactory.getLogger(ActivitiConfig.class);
// 工作流特殊数据库类型配置
@Value("${spring.activiti.database-type:oracle}")
private String databaseType;
// 数据库模式
@Value("${spring.activiti.database-schema:ROOT}")
private String databaseSchema;
@Bean
public SpringProcessEngineConfiguration springProcessEngineConfiguration(
DataSource dataSource,
PlatformTransactionManager transactionManager,
SpringAsyncExecutor springAsyncExecutor) throws IOException {
logger.debug("springProcessEngineConfiguration is called...");
this.activitiProperties.setRestApiEnabled(false);
this.activitiProperties.setRestApiMapping("/bpmapi/*");
SpringProcessEngineConfiguration baseSpringProcessEngineConfiguration = baseSpringProcessEngineConfiguration(dataSource, transactionManager, springAsyncExecutor);
baseSpringProcessEngineConfiguration.setDbIdentityUsed(false);
baseSpringProcessEngineConfiguration.setAsyncExecutorActivate(false);
baseSpringProcessEngineConfiguration.setDeploymentResources(null);
baseSpringProcessEngineConfiguration.setActivityFontName("宋体");
baseSpringProcessEngineConfiguration.setLabelFontName("宋体");
baseSpringProcessEngineConfiguration.setXmlEncoding("UTF-8");
baseSpringProcessEngineConfiguration.setDatabaseSchemaUpdate("true");
// databaseSchema 数据库模式，当前用户使用哪个模式，就指定哪个模式，会在当前模式下进行activiti表的创建,默认使用创建用户后的默认模式
baseSpringProcessEngineConfiguration.setDatabaseSchema(databaseSchema);
// 若存在特殊数据库类型，则使用此项配置
// databaseType 指定数据库类型，因达梦兼容oracle，故使用达梦数据库时，指定oracle即可。Kingbase数据库兼容postgres和oracle，故使用金仓数据库时指定postgres即可。
if (StringUtil.nonNullorEmpty(databaseType)) {
baseSpringProcessEngineConfiguration.setDatabaseType(databaseType);
}
Map<String, List<ActivitiEventListener>> typedListeners = new HashMap<>();
// 流程结束监听
List<ActivitiEventListener> activitiProcessCompleteListener = new ArrayList<>();
activitiProcessCompleteListener.add(processCompleteListener());
typedListeners.put("PROCESS_COMPLETED", activitiProcessCompleteListener);
baseSpringProcessEngineConfiguration.setTypedEventListeners(typedListeners);
return baseSpringProcessEngineConfiguration;
}
}
- activiti 部署在oracle多用户下不能自动建表问题的解决！
需要手动指定databaseSchema，值为当前登录用户的用户名。
baseSpringProcessEngineConfiguration.setDatabaseSchema(databaseSchema);
- 达梦数据库创建实例后无法登录，报网络通信异常。
客户端实例名称写错了，直接使用LOCALHOST，修改端口，输入账号密码即可。
- 达梦数据库实例禁用指定关键字
# 找到对应的数据库实例下的dm.ini D:\TOOLS\dm\dmdbms\data\ebp_ms_mbse\dm.ini # 找到对应项，若没有则手动添加。SEALED,LINK,LOGIN为关键字,XXZXGCH需要配置以下三个参数 EXCLUDE_RESERVED_WORDS = SEALED,LINK,LOGIN # Reserved words to be exclude修改后需要重启数据库服务，国产化专用机目录下运行如下命令，windows下直接重启服务的实例即可
# 进入达梦数据库的运行目录 cd /opt/dmdbms/bin/bin # 重新启动达梦数据库 ./ dm_services restart
- 达梦数据库不支持integer(1),默认integer即可。对应的floatType为float，对应liquibase生成的创建表的xml中，不存在
${clobType},${floatType}即可。否则会报数组下标越界异常ArrayIndexOutOfBoundsException 1
<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
<property name="autoIncrement" value="true"/>
<!--
Added the entity BkyMileStone.
-->
<changeSet id="202111091416-1" author="jhipster">
<createTable tableName="bky_mile_stone" remarks="里程碑节点信息表">
<column name="guid" type="varchar(50)" remarks="唯一编码（GUID）">
<constraints primaryKey="true" nullable="false"/>
</column>
<!--
错误写法
<column name="sn" type="integer(1)" remarks="排序号">
<constraints nullable="true" />
</column>
如果在master.xml中指定了floatType、clobType使用的数据库，即可正常使用如下写法，见下面的第二个XML
<column name="amount" type="${floatType}" remarks="节点经费">
<constraints nullable="true" />
</column>
<column name="goal" type="${clobType}" remarks="节点研究目标">
<constraints nullable="true" />
</column>
-->
<!--
正确写法
-->
<column name="sn" type="integer" remarks="排序号">
<constraints nullable="true" />
</column>
<column name="amount" type="float" remarks="节点经费">
<constraints nullable="true" />
</column>
<column name="goal" type="longblob" remarks="里程碑节点研究目标">
<constraints nullable="true" />
</column>
</createTable>
</changeSet>
</databaseChangeLog>
- 正确使用
${floatType}、${clobType},主要的代码如下
<property name=“floatType” value=“float” dbms=“mysql, oracle, mssql, mariadb, kingbase, DM DBMS”/>
<property name=“clobType” value=“clob” dbms=“mysql, oracle, mssql, mariadb, postgresql, kingbase, DM DBMS”/>
<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<property name="now" value="now()" dbms="h2"/>
<property name="now" value="now()" dbms="mysql"/>
<property name="floatType" value="float4" dbms="postgresql, h2"/>
<property name="floatType" value="float" dbms="mysql, oracle, mssql, mariadb, kingbase, DM DBMS"/>
<property name="clobType" value="clob" dbms="h2"/>
<property name="clobType" value="clob" dbms="mysql, oracle, mssql, mariadb, postgresql, kingbase, DM DBMS"/>
<property name="uuidType" value="varchar(36)" dbms="h2, mysql, mariadb"/>
<changeSet id="202111091416-1" author="jhipster">
<createTable tableName="bky_mile_stone" remarks="里程碑节点信息表">
<column name="guid" type="varchar(50)" remarks="唯一编码（GUID）">
<constraints primaryKey="true" nullable="false"/>
</column>
<!--
正确写法
-->
<column name="sn" type="integer" remarks="排序号">
<constraints nullable="true" />
</column>
<column name="amount" type="${floatType}" remarks="节点经费">
<constraints nullable="true" />
</column>
<column name="goal" type="${clobType}" remarks="节点研究目标">
<constraints nullable="true" />
</column>
</createTable>
</changeSet>
</databaseChangeLog>
- 达梦数据库之修改字段类型-varchar改为text
一个有效的变更方法
（1）增加一个为text类型的字段case_name1
alter table KF.BASE_CASE add case_name1 text;
（2）将case_name字段的值赋给case_name1
update KF.BASE_CASE set case_name1=trim(case_name);
（3）删除字段case_name
alter table KF.BASE_CASE drop column CASE_NAME;
（4）将字段case_name1改名为case_name
alter table KF.BASE_CASE rename column case_name1 to case_name;
三、注意事项
- 达梦数据库 varchar 最大支持长度
8188 - 达梦数据库集成liquibase删除非空约束报错，不适用删除约束
<dropNotNullConstraint>标签即可。 - 达梦数据库一个汉字3个字节，mysql UTF8编码下一个汉字3个字节 GBK编码下一个汉字两个字节
- 达梦数据库默认大小写敏感，大小写敏感的情况下，category字段在jpa框架中进行查询时，会报错。
- Liquibase创建数据库表，主键自增报[GENERATED]附近出现错误
解决方案：在DMDatabase中，重写如下方法，上文代码中已经包含此代码，仅此提示
/**
* 当前方法用于解决 【Liquibase创建数据库表，主键自增报[GENERATED]附近出现错误】问题
* 原因：DM数据库创建表时，不支持 GENERATED BY DEFAULT AS IDENTITY
* 解决方案： 达梦数据库创建数据库表的SQL语句中，使用 IDENTITY(1,1) 来设置主键自增， 括号内数字的含义：第一个数字代表从1开始，第二个数字代表每次递增1（步长为1）
* @return
*/
@Override
protected String getAutoIncrementClause() {
return "IDENTITY(1,1)";
}
- 达梦数据库不支持longtext类型
- 打包liquibase-core即可，打包时需要禁用掉Test测试类
-Dmaven.test.skip=true - 使用方式：
- 将target下的liquibase-core-4.3.5-local-SNAPSHOT.jar放入到Maven仓库中
- 在pom.xml文件中，引用
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>4.3.5-local-SNAPSHOT</version>
</dependency>
- 使用jpa集成达梦数据库时，需要将达梦的hibernate方言包引入
- 东方通启动liquibase的war包时，需要注意驱动包需和当前达梦数据库版本匹配，使用达梦数据库自带的驱动，位于~/dmdbms/drivers/jdbc/DmJdbcDriver18.jar，18.jar是jdk1.8的驱动版本，否则会出现如下错误
* 代码报错是因为liquibase源码获取数据库版本，但是数据库驱动中自带的获取版本方法解析出现问题，未取到数据库版本号，渠道的是“”，通过Integer.value()转换时出现此问题。
liquibase源码获取数据库代码位于JdbcConnection.java中
@Override
public int getDatabaseMajorVersion() throws DatabaseException {
try {
return con.getMetaData().getDatabaseMajorVersion();
} catch (SQLException e) {
throw new DatabaseException(e);
}
}
最后一张图片的位置转换报错。