Liquibase集成达梦数据库、Activiti集成达梦数据库

Liquibase集成达梦数据库

一、Liquibase适配达梦数据库

Jhipster项目下，Liquibase集成达梦数据库会报一系列错误，例如：
类型不支持、数据库字段使用到了保留字、主键自增等，本文针对达梦数据库的问题，进行解答

• Error querying database, Cause: dm.jdbc.driver.DMException: 第1 行附近出现错误：无效的模型名[INFORMATION_SCHEMA]
• Liquibase 报警告【无法识别DM数据库类型】
  以上两个问题的解决办法相同

1. 使用liquibase4.3.5版本,下载源码,下载地址如下

https://github.com/liquibase/liquibase/tree/v4.3.5

1. 在 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;
    }

}

1. 打开 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 ";