一個jsqlparse+git做的小工具幫我節省時間摸魚

背景

前些時間做了個小工具解決了團隊內資料庫指令碼檢驗&多測試環境自動執行的問題，感覺挺有意思，在這跟大家分享一下。

工具誕生之前的流程是這樣：

1.開發人員先在開發環境編寫指令碼&執行；

2.執行沒問題之後記錄到程式碼目錄下的upgrade目錄；

3.提測時手動將upgrade目錄下的指令碼檔案在測試庫執行。

大概長這樣

這套流程在我之前就有了，剛進來的時候感覺有點low，畢竟老東家解決這類問題是通過一款自研的資料庫自動化運維平臺-iDB，其誕生的目的是「解決絕大部分重複、複雜的資料庫運維工作，滿足業務對資料庫資訊查詢和快速變更需求，藉此提升研發效率，保證資料庫操作符合審計要求，有可追溯的變更和稽核紀錄檔」，內心一度排斥過一段時間，後來轉念一想這套手工機制對於目前的團隊規模來說是夠用，況且沒有配備dba，搞一套iDB上來誰稽核誰呢？想到這，心裡自然也就釋懷了，不用過於追求太時尚的工具、技術，夠用就可以了。

問題出現

伴隨著近兩年業務快速發展，團隊也迎來了擴編，往往在這種時候就容易出一些低階故障，俗話說越忙越亂。

遇到過哪些問題呢？

0.程式碼提測了，但是指令碼忘記執行了，測試走流程的時候發現有報錯然後反饋給開發處理，耽誤進度；

1.刷資料未加where條件，導致測試環境崩潰，有一次還波及了線上，幸好只是一張設定表，從其他私有化環境快速同步一份即可，但也是心驚肉跳，要是業務表可能團隊要團滅了；

2.一些高危sql，比如drop table if exists，原意是想順暢的建一張新表，但是誰能保證同樣的語句不會再出現？

3.一些高階語法導致部分私有化環境不相容，一般情況下開發執行一些資料庫操作都是直接通過navicat等工具在開發庫執行，然後再把工具生成的指令碼記錄下來，但是也有當時沒整理，發版時再整理的情況，這時只能手寫sql了，不排除會寫一些高階的語法，導致發版時部分環境失敗的情況，因為資料庫版本有差異（有些客戶分配的庫，統一版本比較難）；

人肉解決

最開始研發經理是專門安排了一個測試同學去處理這事，他需要定時做以下事項：

1.檢查是否有新指令碼提交，如果有就繼續後續流程；

2.檢查是否為高危指令碼，如果高危就線下告知相關的開發整改，否則就繼續後續流程；

3.在navicat等工具中執行，如果執行失敗了就線下告知相關的開發處理。

起初幾天確實是解決了前面提到的一些問題，但是人畢竟不是機器，會忘記、會疲倦、會煩躁、會走神，兩週以後「人肉」方案又出現一些新問題：

1.忙的時候會忘；

2.頻率不好把握，幾小時一次太慢了，幾分鐘一次人會崩潰；

3.這個活太low了，臨時幹幾天還行，長期沒人願意幹；

機器解決

機器不知疲倦、一絲不苟、戒驕戒躁，最適合幹這類重複性而且枯燥的活，鑑於此我利用半天時間構思&開發了一個小工具用來解決這一問題，解救了那個悲慘的測試同學。

這個工具需要具備以下特點：

1.定時拉取程式碼判斷指令碼檔案是否有變化；

2.如果指令碼檔案變化了解析指令碼看是否有語法錯誤，如果有語法錯誤傳送郵件給提交人；

3.如果沒有語法錯誤判斷是否有高危語句，如果有高危發郵件給提交人和研發組長&經理們；

4.一切正常，開始執行sql語句，執行結果需要發郵件給相關人員，需要避免重複執行；

接下來一步步看如何解決上面的問題：

1.定時拉取程式碼

這個比較簡單，因為是執行在我的開發機器上，定時使用Runtime執行git pull即可。

Process process = Runtime.getRuntime().exec("git pull ",null,new File(程式碼目錄));

2.判斷指令碼檔案是否發生變化

記錄指令碼檔案的md5，拉取程式碼以後計算md5是否發生變化。

3.語法解析

利用jsqlparser工具將指令碼檔案內容解析為Statements物件，代表一組解析之後的sql語句物件，如果有語法錯誤jsqlparser會丟擲異常，異常資訊中包含具體的行號和錯誤資訊，以下面這組sql語句為例：

String sql = "ALTER TABLE `wf_position` ADD COLUMN `c1` VARCHAR (10);" +
                "ALTER TABLE `wf_position` ADD COLUNM `c2` VARCHAR (10)";
CCJSqlParserUtil.parseStatements(sql);

解析的時候會丟擲如下異常，很明顯是因為COLUNM寫錯了

net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "`c2`" <S_QUOTED_IDENTIFIER>
    at line 1, column 93.
Was expecting:
    "COMMENT"
  at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:188)

至此我們已經做完了語法解析，但是怎麼根據丟擲的語法錯誤找到對應的提交人呢？這裡分兩步完成：

3.1正則匹配出異常堆疊中的line

Pattern pattern = Pattern.compile("line (\\d+), column (\\d+)");
try{
    CCJSqlParserUtil.parseStatements(sql);
}catch(exception){
  String message = exception.getMessage();
  Matcher m = pattern.matcher(message);
  int line = -1;
  int column = -1;
  while(m.find()){
      int groupCount = m.groupCount();
      if(groupCount > 0){
          line = Integer.parseInt(m.group(1));
          column = Integer.parseInt(m.group(2));
          break;
      }
  }
}

3.2前一步的line可以對應到指令碼檔案中的行，利用git blame命令可以獲得對應行的提交記錄，裡面包含提交者的姓名和郵箱　　

String blameParams = scriptFile.getName()+" -L "+lineNum+","+lineNum;
Process process = Runtime.getRuntime().exec("git blame "+blameParams,null,new File(scriptFile所在目錄));

輸出格式如下，紅框所示區域就是提交者的郵箱（組內約定git user.name必須攜帶郵箱，所以這裡能拿到）　　

接下來就各種的擷取，最終提取郵箱，比較簡單，這裡就不囉嗦了。

4.高危判斷

遍歷所有的Statement物件，目前主要識別三類：

1.drop table

2.update不帶where條件

3.delete不帶where條件

Statements statements = CCJSqlParserUtil.parseStatements(sql);
List<RiskScript> riskScripts = new ArrayList<>()
for(Statement statement : statements.getStatements()){
    RiskScript riskScript = new RiskScript();
    
    //drop table
    if(statement instanceof Drop 
      && (((Drop) statement).getType().equals("table") 
      || ((Drop) statement).getType().equals("TABLE"))){
        riskScript.setErrorMsg("drop table高危，放棄自動執行，請確認，如有需要請手動執行");
        riskScript.setSql(statement.toString());
        this.riskScripts.add(riskScript);
        continue;
    }
    
    //update不帶where條件
    if(statement instanceof Update 
        && ((Update) statement).getWhere() == null){
        riskScript.setErrorMsg("update 不帶where條件，放棄自動執行，請確認，如有需要請手動執行");
        riskScript.setSql(statement.toString());
        this.riskScripts.add(riskScript);
        continue;
    }
    
    //delete不帶where條件
    if(statement instanceof Delete && 
       ((Delete) statement).getWhere() == null){
        riskScript.setErrorMsg("delete 不帶where條件，放棄自動執行，請確認，如有需要請手動執行");
        riskScript.setSql(statement.toString());
        this.riskScripts.add(riskScript);
        continue;
    }

}

5.避免重複執行

這個比較簡單，每次執行完以後記錄下每條sql的執行歷史，執行前判斷。

效果展示

總結

是不是應該引入一個高大上的資料庫自動化運維平臺呢？我的判斷是暫時不需要，究其原因我認為有以下幾點：

1.雖說沒有專人稽核那麼精細，但依賴工具把一些高危的sql已經排除在外，已然是夠用了，想想老東家為什麼需要dba嚴格稽核是因為toC的資料量較大，欄位型別、索引等對效能的影響不容小覷，而目前toB的業務，資料量不會特別大，欄位型別、索引等因素對效能的影響姑且可以忽略，起碼現階段差別不大；

2.每個迭代產生的指令碼變更較多，如果引入太繁瑣的流程，對開發效率是一種制約，不求設計出精妙的表結構，只願你不要寫出「團滅」的指令碼；

一個jsqlparse+git做的小工具幫我節省時間摸魚

背景

問題出現

人肉解決

機器解決

效果展示

總結

推薦閱讀