Liquibase集成金仓数据库KingBase

Liquibase集成金仓数据库KingBase

1. KingBase针对数据库Boolean类型做特殊处理，sql语句只能解析字符串样式的boolean类型，对应数据库字段类型为bit类型
   在BooleanType.java中，修改如下方法

@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) || (database instanceof KingBaseDatabase)) {
        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);
}

/**
 * The database-specific value to use for "false" "boolean" columns.
 */
public String getFalseBooleanValue(Database database) {
    if (isNumericBoolean(database)) {
        return "0";
    }
    if (database instanceof KingBaseDatabase) {
        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 KingBaseDatabase) {
        return "'1'";
    }
    if (database instanceof InformixDatabase) {
        return "'t'";
    }
    return "TRUE";
}

2.新增GetViewDefinitionGeneratorKingBase.java

package liquibase.sqlgenerator.core;

import liquibase.CatalogAndSchema;
import liquibase.database.Database;
import liquibase.database.core.KingBaseDatabase;
import liquibase.sql.Sql;
import liquibase.sql.UnparsedSql;
import liquibase.sqlgenerator.SqlGeneratorChain;
import liquibase.statement.core.GetViewDefinitionStatement;

public class GetViewDefinitionGeneratorKingBase extends GetViewDefinitionGenerator {
    @Override
    public int getPriority() {
        return PRIORITY_DATABASE;
    }

    @Override
    public boolean supports(GetViewDefinitionStatement statement, Database database) {
        return database instanceof KingBaseDatabase;
    }

    @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 definition from pg_views where viewname='" + statement.getViewName() + "' AND schemaname='" + schema.getSchemaName() + "'" )
            };
    }
}

3. liquibase创建对应数据库索引，需要用到CreateIndexGeneratorKingBase.java

package liquibase.sqlgenerator.core;

import liquibase.change.AddColumnConfig;
import liquibase.database.Database;
import liquibase.database.core.*;
import liquibase.sql.Sql;
import liquibase.sql.UnparsedSql;
import liquibase.sqlgenerator.SqlGeneratorChain;
import liquibase.statement.core.CreateIndexStatement;
import liquibase.structure.core.Index;
import liquibase.util.StringUtil;

import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

public class CreateIndexGeneratorKingBase extends CreateIndexGenerator {
    @Override
    public int getPriority() {
        return PRIORITY_DATABASE;
    }

    @Override
    public boolean supports(CreateIndexStatement statement, Database database) {
        return database instanceof KingBaseDatabase;
    }

    @Override
    public Sql[] generateSql(CreateIndexStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {

        // Default filter of index creation:
        // creation of all indexes with associations are switched off.
        List<String> associatedWith = StringUtil.splitAndTrim(statement.getAssociatedWith(), ",");
        if ((associatedWith != null) && (associatedWith.contains(Index.MARK_PRIMARY_KEY) || associatedWith.contains
                (Index.MARK_UNIQUE_CONSTRAINT) || associatedWith.contains(Index.MARK_FOREIGN_KEY))) {
            return new Sql[0];
        }

        StringBuilder buffer = new StringBuilder();

        buffer.append("CREATE ");
        if ((statement.isUnique() != null) && statement.isUnique()) {
            buffer.append("UNIQUE ");
        }
        buffer.append("INDEX ");

        if (statement.getIndexName() != null) {
            // for postgres setting the schema name for the index name is invalid
            buffer.append(database.escapeObjectName(statement.getIndexName(), Index.class)).append(" ");
        }
        buffer.append("ON ");
        buffer.append(database.escapeTableName(statement.getTableCatalogName(), statement.getTableSchemaName(), statement.getTableName())).append("(");
        Iterator<AddColumnConfig> iterator = Arrays.asList(statement.getColumns()).iterator();
        while (iterator.hasNext()) {
            AddColumnConfig column = iterator.next();
            if (column.getComputed() == null) {
                buffer.append(database.escapeColumnName(statement.getTableCatalogName(), statement.getTableSchemaName(), statement.getTableName(), column.getName(), false));
            } else {
                if (column.getComputed()) {
                    buffer.append(column.getName());
                } else {
                    buffer.append(database.escapeColumnName(statement.getTableCatalogName(), statement.getTableSchemaName(), statement.getTableName(), column.getName()));
                }
            }
            if (column.getDescending() != null && column.getDescending()) {
                buffer.append(" DESC");
            }
            if (iterator.hasNext()) {
                buffer.append(", ");
            }
        }
        buffer.append(")");

        if ((StringUtil.trimToNull(statement.getTablespace()) != null) && database.supportsTablespaces()) {
            if ((database instanceof MSSQLDatabase) || (database instanceof SybaseASADatabase)) {
                buffer.append(" ON ").append(statement.getTablespace());
            } else if ((database instanceof AbstractDb2Database) || (database instanceof InformixDatabase)) {
                buffer.append(" IN ").append(statement.getTablespace());
            } else {
                buffer.append(" TABLESPACE ").append(statement.getTablespace());
            }
        }

        if ((statement.isClustered() != null) && statement.isClustered()) {
            return new Sql[]{
                    new UnparsedSql(buffer.toString(), getAffectedIndex(statement)),
                    new UnparsedSql("CLUSTER " + database.escapeTableName(statement.getTableCatalogName(), statement.getTableSchemaName(), statement.getTableName()) + " USING " + database.escapeObjectName(statement.getIndexName(), Index.class))
            };
        } else {
            return new Sql[]{new UnparsedSql(buffer.toString(), getAffectedIndex(statement))};
        }
    }
}

4. 修改ChangedColumnChangeGenerator.java中的如下方法

/**
 * For {@link PostgresDatabase} if column is of autoIncrement/SERIAL type we can ignore 'defaultValue' differences
 * (because its execution of sequence.next() anyway)
 */
private boolean shouldTriggerAddDefaultChange(Column column, Difference difference, Database comparisonDatabase) {
    if (!(comparisonDatabase instanceof PostgresDatabase) || !(comparisonDatabase instanceof KingBaseDatabase)) {
        return true;
    }
    if (column.getAutoIncrementInformation() != null && difference.getReferenceValue() instanceof DatabaseFunction) {
        return false;
    }
    return true;
}

5. 修改JdbcDatabaseSnapshot.java中的如下方法

public List<CachedRow> getTables(final String catalogName, final String schemaName, final String table) throws DatabaseException {
    return getResultSetCache("getTables").get(new ResultSetCache.SingleResultSetExtractor(database) {

        @Override
        boolean shouldBulkSelect(String schemaKey, ResultSetCache resultSetCache) {
            return table == null || getAllCatalogsStringScratchData() != null || super.shouldBulkSelect(schemaKey, resultSetCache);
        }

        @Override
        public ResultSetCache.RowData rowKeyParameters(CachedRow row) {
            return new ResultSetCache.RowData(row.getString("TABLE_CAT"), row.getString("TABLE_SCHEM"), database, row.getString("TABLE_NAME"));
        }

        @Override
        public ResultSetCache.RowData wantedKeyParameters() {
            return new ResultSetCache.RowData(catalogName, schemaName, database, table);
        }

        @Override
        public boolean bulkContainsSchema(String schemaKey) {
            return database instanceof OracleDatabase;
        }

        @Override
        public String getSchemaKey(CachedRow row) {
            return row.getString("TABLE_SCHEM");
        }

        @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);
            } else if ( database instanceof PostgresDatabase) {
                return queryPostgres(catalogAndSchema, table);
            } else if ( database instanceof KingBaseDatabase) {
                return queryPostgres(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"}));
        }

        @Override
        public List<CachedRow> bulkFetchQuery() throws SQLException, DatabaseException {
            CatalogAndSchema catalogAndSchema = new CatalogAndSchema(catalogName, schemaName).customize(database);

            if (database instanceof OracleDatabase) {
                return queryOracle(catalogAndSchema, null);
            } else if (database instanceof MSSQLDatabase) {
                return queryMssql(catalogAndSchema, null);
            } else if (database instanceof Db2zDatabase) {
                return queryDb2Zos(catalogAndSchema, null);
            } else if ( database instanceof PostgresDatabase) {
                return queryPostgres(catalogAndSchema, table);
            }

            String catalog = ((AbstractJdbcDatabase) database).getJdbcCatalogName(catalogAndSchema);
            String schema = ((AbstractJdbcDatabase) database).getJdbcSchemaName(catalogAndSchema);
            return extract(databaseMetaData.getTables(catalog, schema, SQL_FILTER_MATCH_ALL, new String[]{"TABLE"}));
        }

        private List<CachedRow> queryMssql(CatalogAndSchema catalogAndSchema, String tableName) throws DatabaseException, SQLException {
            String ownerName = database.correctObjectName(catalogAndSchema.getSchemaName(), Schema.class);

            String databaseName = StringUtil.trimToNull(database.correctObjectName(catalogAndSchema.getCatalogName(), Catalog.class));
            String dbIdParam;
            String databasePrefix;
            if (databaseName == null) {
                databasePrefix = "";
                dbIdParam = "";
            } else {
                dbIdParam = ", db_id('"+databaseName+"')";
                databasePrefix = "[" + databaseName + "].";
            }


            //From select object_definition(object_id('sp_tables'))
            String sql = "select " +
                    "db_name(" + (databaseName == null ? "" : "db_id('" + databaseName + "')") + ") AS TABLE_CAT, " +
                    "convert(sysname,object_schema_name(o.object_id" + dbIdParam + ")) AS TABLE_SCHEM, " +
                    "convert(sysname,o.name) AS TABLE_NAME, " +
                    "'TABLE' AS TABLE_TYPE, " +
                    "CAST(ep.value as varchar(max)) as REMARKS " +
                    "from " + databasePrefix + "sys.all_objects o " +
                    "left outer join sys.extended_properties ep on ep.name='MS_Description' and major_id=o.object_id and minor_id=0 " +
                    "where " +
                    "o.type in ('U') " +
                    "and has_perms_by_name(" + (databaseName == null ? "" : "quotename('" + databaseName + "') + '.' + ") + "quotename(object_schema_name(o.object_id&#