liquibase Groovy腳本用法示例

databaseChangeLog

屬性

屬性 描述
logicalFilePath 路徑+文件名進(jìn)行唯一標(biāo)識(shí)，當(dāng)重命名文件或者重構(gòu)文件路徑時(shí)可通過(guò)該屬性經(jīng)行唯一標(biāo)識(shí)

子標(biāo)簽

preConditions

用途：

  • 記錄執(zhí)行的先決條件或者注釋
  • 測(cè)試執(zhí)行該databaseChangeLog的先決條件是否完備
  • 執(zhí)行數(shù)據(jù)檢查
  • 根據(jù)條件控制執(zhí)行哪些changesets

屬性：

屬性 描述
onFail 不滿足測(cè)試條件，執(zhí)行以下選項(xiàng)：HALT/CONTINUE/MARK_RAN/WARN
onError 執(zhí)行過(guò)程中出現(xiàn)錯(cuò)誤，執(zhí)行以下選項(xiàng)：HALT/CONTINUE/MARK_RAN/WARN
onUpdateSQL RUN/FAIL/IGNORE
onFailMessage 失敗后輸出的信息
onErrorMessage 拋錯(cuò)后輸出的信息

http://www.liquibase.org/documentation/preconditions.html

詳細(xì)用法示例如下：

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)
            }
          }
        }
      }
}
property

可以自定義一些參數(shù)：

databaseChangeLog(logicalFilePath: '') {
    clobType = 0
}
changeSet
屬性 描述
id 必輸 ，一般以時(shí)間戳+名稱(chēng)作為唯一標(biāo)識(shí)
author 必輸 ，創(chuàng)建者
dbms 指定數(shù)據(jù)庫(kù)類(lèi)型
runAlways 該changeSet不論之前是否執(zhí)行過(guò)，每次都會(huì)執(zhí)行 true/false
runOnChange 在第一次或者修改后執(zhí)行 true/false
context 特定的上下文通過(guò)后執(zhí)行
runInTransaction 執(zhí)行是否事務(wù)化，默認(rèn)為true，大部分情況下都為true
failOnError 是否忽略錯(cuò)誤繼續(xù)執(zhí)行

屬性：

屬性 描述
id 必輸 ，一般以時(shí)間戳+名稱(chēng)作為唯一標(biāo)識(shí)
author 必輸 ，創(chuàng)建者
dbms 指定數(shù)據(jù)庫(kù)類(lèi)型
runAlways 該changeSet不論之前是否執(zhí)行過(guò)，每次都會(huì)執(zhí)行 true/false
runOnChange 在第一次或者修改后執(zhí)行 true/false
context 特定的上下文通過(guò)后執(zhí)行
runInTransaction 執(zhí)行是否事務(wù)化，默認(rèn)為true，大部分情況下都為true
failOnError 是否忽略錯(cuò)誤繼續(xù)執(zhí)行

子標(biāo)簽：

  • comment: changeSet的描述
  • preConditions：必須通過(guò)后才能執(zhí)行該changeSet，主要用于一些不可恢復(fù)操作之前的數(shù)據(jù)檢查
  • <Any Refactoring Tag(s)> ：具體的數(shù)據(jù)、表操作
  • validCheckSum：一般不用，使用來(lái)區(qū)分?jǐn)?shù)據(jù)庫(kù)中儲(chǔ)存的changeSet與該changeSet是否一致，一般會(huì)自動(dòng)生成。
  • rollback：會(huì)滾操作

具體詳細(xì)用例如下：

  1. rollback
databaseChangeLog(logicalFilePath: '') {
  
  //定義參數(shù)
  clobType = 0
  
  //rollback的用法
  changeSet(id: '', author: '', dbms: '', runAlways: true, runOnChange: false, context: '', runInTransaction: true, failOnError: false) {
    // 添加描述
    comment "Liquibase can be aware of this comment"

    preConditions {
      // 與changeLog的preConditions一致
    }
    
    validCheckSum 'd0763edaa9d9bd2a9516280e9044d885'
    
    // rollback可以直接是一個(gè)string，會(huì)當(dāng)作SQL直接執(zhí)行
    rollback "DROP TABLE monkey_table"
    rollback """
      UPDATE monkey_table SET emotion='angry' WHERE status='PENDING';
      ALTER TABLE monkey_table DROP COLUMN angry;
    """
    
    // 也可以是liquibase腳本
    rollback {
      dropTable(tableName: 'monkey_table')
    }
    
    // 也可以指定執(zhí)行某個(gè)changeSet
    rollback(changeSetId: '', changeSetAuthor: '')
    
  }
  1. addColumn語(yǔ)法：
  changeSet(id: 'add-column', author: 'tlberglund') {
    addColumn(tableName: '', schemaName: '') {
      column(name: '', type: '', value: '', defaultValue: '', autoIncrement: false, remarks: '') {
        
        // 約束
        
        // 寫(xiě)法一
        constraints {
          nullable(false)//是否必輸
          primaryKey(true)//主鍵約束
          unique(true)//唯一約束
          uniqueConstraintName('make_it_unique_yo')//唯一約束名稱(chēng)
          foreignKeyName('key_to_monkey')//外鍵約束名稱(chēng)
          references('monkey_table')//外鍵約束
          deleteCascade(true)//是否級(jí)聯(lián)刪除
          deferrable(true)//約束驗(yàn)證是否可延時(shí)（事務(wù)中或者事務(wù)完成后）
          initiallyDeferred(false)//事務(wù)結(jié)束的時(shí)候才去檢查約束
        }
        
        // 寫(xiě)法二，推薦使用寫(xiě)法二，可讀性較好
        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)
      }
      

      // 列的其他屬性
      column(name: '', type: '', valueNumeric: '', defaultValueNumeric: '')
      column(name: '', type: '', valueBoolean: '', defaultValueBoolean: '')
      column(name: '', type: '', valueDate: '', defaultValueDate: '')
    }
  }
  1. 列column操作:
  // renameColumn
  changeSet(id: 'rename-column', author: 'tlberglund') {
    renameColumn(schemaName: '', tableName: '', oldColumnName: '', newColumnName: '', columnDataType: '')
  }
  
  // modifyColumn
  changeSet(id: 'modify-column', author: 'tlberglund') {
    modifyColumn(schemaName: '', tableName: '') {
      column() { }
    }
  }
  // dropColumn
  changeSet(id: 'drop-column', author: 'tlberglund') {
    dropColumn(schemaName: '', tableName: '', columnName: '')
  }
  1. 自增長(zhǎng)列
 changeSet(id: 'alter-sequence', author: 'tlberglund') {
   alterSequence(sequenceName: '', incrementBy: '')
 }
  1. 表table操作：
  // createTable
  changeSet(id: 'create-table', author: 'tlberglund') {
    createTable(schemaName: '', tablespace: '', tableName: '', remarks: '') {
      column() {}
      column() {}
      column() {}
      column() {}
    }
  }
  // renameTable
  changeSet(id: 'rename-table', author: 'tlberglund') {
    renameTable(schemaName: '', oldTableName: '', newTableName: '')
  }
  
  // dropTab
  changeSet(id: 'drop-table', author: 'tlberglund') {
    dropTab(schemaName: '', tableName: '')
  }
  1. 視圖view相關(guān)操作:
  
  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: '')
  }
  1. 列合并：
  changeSet(id: 'merge-columns', author: 'tlberglund') {
    mergeColumns(schemaName: '', tableName: '', column1Name: '', column2Name: '', finalColumnName: '', finalColumnType: '', joinString: ' ')
  }
  1. 存儲(chǔ)過(guò)程
 changeSet(id: 'create-stored-proc', author: 'tlberglund') {
   createStoredProcedure """
     CREATE OR REPLACE PROCEDURE testMonkey
     IS
     BEGIN
      -- do something with the monkey
     END;
   """
 }
  1. 單獨(dú)列操作，如約束、序列、默認(rèn)值等等操作：
 
 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: '')
 }
  1. 數(shù)據(jù)操作，我們一般采用excel進(jìn)行數(shù)據(jù)導(dǎo)入，這里用法一般不用：
  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
    }
  }
  1. 索引：
  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: '')
  }
  1. sql執(zhí)行:
  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: '')
  }
  1. 其他：
 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')
   }
 }
   
 changeSet(id: 'tag', author: 'tlberglund') {
   tagDatabase(tag: 'monkey')
 }
 
 
 changeSet(id: 'stop', author: 'tlberglund') {
   stop('Migration stopped because something bad went down')
 }
include
屬性 描述
file 要引入的文件名
relativeToChangelogFile 關(guān)聯(lián)的文件路徑是否為相對(duì)路徑，默認(rèn)false

類(lèi)似js中的引入，可以通過(guò)該標(biāo)簽引入其他的文件
屬性：

屬性 描述
file 要引入的文件名
relativeToChangelogFile 關(guān)聯(lián)的文件路徑是否為相對(duì)路徑，默認(rèn)false 
databaseChangeLog(logicalFilePath: '') {
 include(file: '', relative: true)  
}
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 此時(shí)此刻，夜晚的高速公路上疾馳著多輛豪車(chē)。言璃戴著一個(gè)銀白色的面具，坐在紅色的敞篷車(chē)內(nèi)。左手掌握方向盤(pán)，右手拿著一...
    夏儆言閱讀 363評(píng)論 0贊 1
  • 怎樣才能將《紅樓夢(mèng)》讀下去？ 滿紙荒唐言，一把辛酸淚。都云作者癡，誰(shuí)解其中味？ 好多朋友和我說(shuō)起讀書(shū)的時(shí)候，聽(tīng)說(shuō)我...
    長(zhǎng)弓凌閱讀 1,107評(píng)論 0贊 2
  • 不知道也不記得自己是多久沒(méi)有寫(xiě)字了，雖然每天看書(shū)，看文章，每天都會(huì)花時(shí)間讓自己取閱！因?yàn)槲覑?ài)閱讀，我需要閱讀！在閱...
    拾月藍(lán)林閱讀 371評(píng)論 3贊 3

友情鏈接更多精彩內(nèi)容