表更新规范
概述
为了便于服务升级、发布时表结构自动更新，必须规范表结构更新规范。总体要求不能没有任何记录而直接在数据库执行DDL语句更改表结构，必须同步更新 Excel 表设计，然后添加 liquibase groovy 脚本 changeSet 来更新表结构，这样 DATABASECHANGELOG 表中才能保留更新记录。
更新步骤
-
第一步，无论是新增表、还是增删改字段，先更新 Excel 表设计。
-
第二步，更新数据库
liquibase groovy脚本：如果是新增的表，可先到代码生成器平台上生成表的 groovy 脚本；如果增删改字段，或调整了字段的某些属性，则在 liquibase groovy 脚本后添加
changeSet。
注意：新增的 changeSet 添加author信息;id不能重复，标注更新日期。具体语法可参考 #liquibase 语法#
-
第三步，更新开发库表结构，执行 init-local-database.sh 脚本，将该脚本下载至项目根目录，运行即可。
该脚本会先下载
choerodon-tool-liquibase.jar到target目录下用于执行 liquibase groovy 脚本。
然后会提示输入要更新的数据库，输入数据库名即可；数据库信息若不同，需先更改脚本再执行。更新成功，便可在DATABASECHANGELOG表中看到更新记录。
-
重要：注意开发环境更新后，同样不能手动更新测试环境表结构或直接复制表过去，项目在测试环境部署时会自动执行 liquibase 脚本更新表结构，否则会报错。
-
重要：建议在创建表时就建好索引，TiDB 对索引的修改支持不是很好。具体支持情况参考：[TiDB 数据定义语言DDL]
问题记录：
1、删除字段时，若字段有默认值，需要先删除字段默认值，再删除字段
changeSet(author: 'xx', id: 'xxxxx') {
dropDefaultValue(tableName: 'x', columnName: 'xx')
dropColumn(tableName: 'x', columnName: 'xx')
addColumn(tableName: 'x') {
column(name: "xxx", type: "", remarks: "")
}
}
2、删除字段时，若字段为索引字段，需要先删除索引，再删除字段
changeSet(author: 'xx', id: 'xxxxx') {
dropUniqueConstraint(tableName: 'x', columnNames: 'xx,xxx', constraintName: 'xx')
dropColumn(tableName: 'x', columnName: 'xx')
addColumn(tableName: 'x') {
column(name: "xxx", type: "", remarks: "")
}
}
liquibase 语法
参考 liquibase 文档：groovy-liquibase
databaseChangeLog(logicalFilePath: '') {
preConditions(onFail: 'WARN') {
and {
dbms(type: 'mysql')
runningAs(username: 'root')
or {
changeSetExecuted(id: '', author: '', changeLogFile: '')
columnExists(schemaName: '', tableName: '', columnName: '')
tableExists(schemaName: '', tableName: '')
viewExists(schemaName: '', viewName: '')
foreignKeyConstraintExists(schemaName: '', foreignKeyName: '')
indexExists(schemaName: '', indexName: '')
sequenceExists(schemaName: '', sequenceName: '')
primaryKeyExists(schemaName: '', primaryKeyName: '', tableName: '')
sqlCheck(expectedResult: '') {
"SELECT COUNT(1) FROM monkey WHERE status='angry'"
}
customPrecondition(className: '') {
tableName('our_table')
count(42)
}
}
}
}
include(file: '', relative: true)
//TODO figure out what properties are all about
clobType = 0
changeSet(id: '', author: '', dbms: '', runAlways: true, runOnChange: false, context: '', runInTransaction: true, failOnError: false) {
// Comments supported through Groovy
comment "Liquibase can be aware of this comment"
preConditions {
// just like changelog preconditions
}
validCheckSum 'd0763edaa9d9bd2a9516280e9044d885'
// If rollback takes a string, it's just the SQL to execute
rollback "DROP TABLE monkey_table"
rollback """
UPDATE monkey_table SET emotion='angry' WHERE status='PENDING';
ALTER TABLE monkey_table DROP COLUMN angry;
"""
// If rollback takes a closure, it's more Liquibase builder (a changeSet?)
rollback {
dropTable(tableName: 'monkey_table')
}
// If rollback takes a map, it identifies the changeset to re-run to do the rollback (this file assumed)
rollback(changeSetId: '', changeSetAuthor: '')
}
changeSet(id: 'add-column', author: 'tlberglund') {
addColumn(tableName: '', schemaName: '') {
column(name: '', type: '', value: '', defaultValue: '', autoIncrement: false, remarks: '') {
// Seems like you should have two ways of representing constraints
// Pass a closure
constraints {
nullable(false)//
primaryKey(true)//
unique(true)
uniqueConstraintName('make_it_unique_yo')
foreignKeyName('key_to_monkey')//
references('monkey_table')//
deleteCascade(true)//
deferrable(true)
initiallyDeferred(false)
}
// Or pass a map
// Can put all constraints in one call, or split them up as shown
constraints(nullable: false, primaryKey: true)
constraints(unique: true, uniqueConstraintName: 'make_it_unique_yo')
constraints(foreignKeyName: 'key_to_monkey', references: 'monkey_table')
constraints(deleteCascase: true)
constraints(deferrable: true, initiallyDeferred: false)
}
// Examples of other value types (only one would apply inside addColumn)
column(name: '', type: '', valueNumeric: '', defaultValueNumeric: '')
column(name: '', type: '', valueBoolean: '', defaultValueBoolean: '')
column(name: '', type: '', valueDate: '', defaultValueDate: '')
}
}
changeSet(id: 'rename-column', author: 'tlberglund') {
renameColumn(schemaName: '', tableName: '', oldColumnName: '', newColumnName: '', columnDataType: '')
}
// 删除自增
changeSet(author: 'xx', id: 'delete-auto-increment') {
renameColumn(tableName: '', oldColumnName: 'ID', newColumnName: 'ID1', columnDataType: 'BIGINT UNSIGNED')
renameColumn(tableName: '', oldColumnName: 'ID1', newColumnName: 'ID', columnDataType: 'BIGINT UNSIGNED', remarks: '表ID，主键，供其他表做外键')
}
// 添加默认值
changeSet(author: 'xx', id: 'add-default-value') {
dropDefaultValue(tableName: "", columnName: '')
addDefaultValue(tableName: "", columnName: "", defaultValue: "")
}
changeSet(id: 'drop-column', author: 'tlberglund') {
dropColumn(schemaName: '', tableName: '', columnName: '')
}
changeSet(id: 'alter-sequence', author: 'tlberglund') {
alterSequence(sequenceName: '', incrementBy: '')
}
changeSet(id: 'create-table', author: 'tlberglund') {
createTable(schemaName: '', tablespace: '', tableName: '', remarks: '') {
column() {}
column() {}
column() {}
column() {}
}
}
changeSet(id: 'rename-table', author: 'tlberglund') {
renameTable(schemaName: '', oldTableName: '', newTableName: '')
}
changeSet(id: 'drop-table', author: 'tlberglund') {
dropTable(schemaName: '', tableName: '')
}
changeSet(id: 'create-view', author: 'tlberglund') {
createView(schemaName: '', viewName: '', replaceIfExists: true) {
"SELECT id, emotion FROM monkey"
}
}
changeSet(id: 'rename-view', author: 'tlberglund') {
renameView(schemaName: '', oldViewName: '', newViewName: '')
}
changeSet(id: 'drop-view', author: 'tlberglund') {
dropView(schemaName: '', viewName: '')
}
changeSet(id: 'merge-columns', author: 'tlberglund') {
mergeColumns(schemaName: '', tableName: '', column1Name: '', column2Name: '', finalColumnName: '', finalColumnType: '', joinString: ' ')
}
changeSet(id: 'create-stored-proc', author: 'tlberglund') {
createStoredProcedure """
CREATE OR REPLACE PROCEDURE testMonkey
IS
BEGIN
-- do something with the monkey
END;
"""
}
changeSet(id: 'add-lookup-table', author: 'tlberglund') {
addLookupTable(existingTableName: '', existingColumnName: '', newTableName: '', newColumnName: '', constraintName: '')
}
changeSet(id: 'add-not-null-constraint', author: 'tlberglund') {
addNotNullConstraint(tableName: '', columnName: '', defaultNullValue: '')
}
changeSet(id: 'drop-not-null-constraint', author: 'tlberglund') {
dropNotNullConstraint(schemaName: '', tableName: '', columnName: '', columnDataType: '')
}
changeSet(id: 'add-unique-constraint', author: 'tlberglund') {
addUniqueConstraint(tableName: '', columnNames: '', constraintName: '')
}
changeSet(id: 'drop-unique-constraint', author: 'tlberglund') {
dropUniqueConstraint(schemaName: '', tableName: '', constraintName: '')
}
changeSet(id: 'create-sequence', author: 'tlberglund') {
createSequence(sequenceName: '', schemaName: '', incrementBy: '', minValue: '', maxValue: '', ordered: true, startValue: '')
}
changeSet(id: 'drop-sequence', author: 'tlberglund') {
dropSequence(sequenceName: '')
}
changeSet(id: 'add-auto-increment', author: 'tlberglund') {
addAutoIncrement(schemaName: '', tableName: '', columnName: '', columnDataType: '')
}
changeSet(id: 'add-default-value', author: 'tlberglund') {
addDefaultValue(schemaName: '', tableName: '', columnName: '', defaultValue: '')
addDefaultValue(schemaName: '', tableName: '', columnName: '', defaultValueNumeric: '')
addDefaultValue(schemaName: '', tableName: '', columnName: '', defaultValueBoolean: '')
addDefaultValue(schemaName: '', tableName: '', columnName: '', defaultValueDate: '')
}
changeSet(id: 'drop-default-value', author: 'tlberglund') {
dropDefaultValue(schemaName: '', tableName: '', columnName: '')
}
changeSet(id: 'add-foreign-key-constraint', author: 'tlberglund') {
addForeignKeyConstraint(constraintName: '',
baseTableName: '', baseTableSchemaName: '', baseColumnNames: '',
referencedTableName: '', referencedTableSchemaName: '', referencedColumnNames: '',
deferrable: true,
initiallyDeferred: false,
deleteCascase: true,
onDelete: 'CASCADE|SET NULL|SET DEFAULT|RESTRICT|NO ACTION',
onUpdate: 'CASCADE|SET NULL|SET DEFAULT|RESTRICT|NO ACTION')
}
changeSet(id: 'drop-foreign-key', author: 'tlberglund') {
dropForeignKeyConstraint(constraintName: '', baseTableName: '', baseTableSchemaName: '')
}
changeSet(id: 'add-primary-key', author: 'tlberglund') {
addPrimaryKey(schemaName: '', tablespace: '', tableName: '', columnNames: '', constraintName: '')
}
changeSet(id: 'drop-primary-key', author: 'tlberglund') {
dropPrimaryKey(schemaName: '', tableName: '', constraintName: '')
}
changeSet(id: 'insert-data', author: 'tlberglund') {
insert(schemaName: '', tableName: '') {
column(name: '', value: '')
column(name: '', valueNumeric: '')
column(name: '', valueDate: '')
column(name: '', valueBoolean: '')
}
}
changeSet(id: 'load-data', author: 'tlberglund') {
loadData(schemaName: '', tableName: '', file: '', encoding: 'UTF8|etc') {
column(name: '', index: 2, type: 'NUMERIC')
column(name: '', index: 3, type: 'BOOLEAN')
column(name: '', header: 'shipDate', type: 'DATE')
column(name: '', index: 5, type: 'STRING')
}
}
changeSet(id: 'load-update-data', author: 'tlberglund') {
loadUpdateData(schemaName: '', tableName: '', primaryKey: '', file: '', encoding: '') {
column(name: '', index: 2, type: 'NUMERIC')
column(name: '', index: 3, type: 'BOOLEAN')
column(name: '', header: 'shipDate', type: 'DATE')
column(name: '', index: 5, type: 'STRING')
}
}
changeSet(id: 'update', author: 'tlberglund') {
update(schemaName: '', tableName: '') {
column(name: '', value: '')
column(name: '', valueNumeric: '')
column(name: '', valueDate: '')
column(name: '', valueBoolean: '')
where "species='monkey' AND status='angry'"
}
}
changeSet(id: 'delete-data', author: 'tlberglund') {
delete(schemaName: '', tableName: '') {
where "id=39" // optional
}
}
changeSet(id: 'tag', author: 'tlberglund') {
tagDatabase(tag: 'monkey')
}
changeSet(id: 'stop', author: 'tlberglund') {
stop('Migration stopped because something bad went down')
}
changeSet(id: 'create-index', author: 'tlberglund') {
createIndex(schemaName: '', tablespace: '', tableName: '', indexName: '', unique: true) {
column(name: '')
column(name: '')
column(name: '')
}
}
changeSet(id: 'drop-index', author: 'tlberglund') {
dropIndex(tableName: '', indexName: '')
}
changeSet(id: 'custom-sql', author: 'tlberglund') {
sql(stripComments: true, splitStatements: false, endDelimiter: ';') {
"INSERT INTO ANIMALS (id, species, status) VALUES (1, 'monkey', 'angry')"
}
}
changeSet(id: 'sql-file', author: 'tlberglund') {
sqlFile(path: '', stripComments: true, splitStatements: '', encoding: '', endDelimiter: '')
}
changeSet(id: 'custom-refactoring', author: 'tlberglund') {
customChange(class: 'net.saliman.liquibase.MonkeyRefactoring') {
tableName('animal')
species('monkey')
status('angry')
}
}
changeSet(id: 'shell-command', author: 'tlberglund') {
executeCommand(executable: '') {
arg('--monkey')
arg('--skip:1')
}
}