第二章 相關研究與技術背景
2.3 Jsqlparser
國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
2.3 Jsqlparser
JsqlParser 用來剖析 SQL 語句並將句子結構轉換成階層式的 java 類別，可以使用 Visitor pattern 來操作及瀏覽這些階層式的類別。JsqlParser 是用 JavaCC (Java Compiler Compiler) Java 剖析產生器所建立出來，為產生階層式的 Java 類別，作 者修改了從 Guido Draheim's site 得到的 SQL 文法搭配 JavaCC 的方式建構出 Jsqlparser。
本研究就是利用 Jsqlparser 來完成 SQL 語法結構剖析與轉換的工作。首先 將 jsqlparser.jar 引入到 java 的 classpath 下，我們就可以在編譯時使用到 Jsqlparser 提供的 SQL 語句剖析功能，協助我們將 SQL 語句關鍵字或變數值識別出來，搭 配 SQL 語句轉換機制以重組方式將關鍵資料表改寫達到自動轉換 SQL 語法的目 的(圖 2.6)。
圖 2.6 SQL 語句剖析與自動轉換概念圖
2.4 SaaS Multi-tenant System
在 SaaS 的雲端平台架構中，多租戶技術指的是大量的用戶共同使用、分享一組 軟硬體資源。在多租戶技術中，常見的資料隔離的架構設計共有 3 種(圖 2.7)，(1)
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
Separate Database 、 (2) Shared Database, Separate Schemas 、 (3) Shared Database, Shared Schema，以下分別說明這 3 種資料隔離架構的特性：
圖 2.7 資料隔離與資源共享關係圖
(Berthold Reinwald, 2010)
1. Separate Database
對於每一個租戶(tenant)，其所存取的資料庫是分離(Separate)的。即使運算 資源或程式碼可能是多個租戶共用，但在資料的儲存或取得上，是完全隔離 (isolate)的狀態。這種架構可以根據用戶需求容易的來擴展資料結構，而備份和 回復資料也相對容易。但每一租戶需提供一個完整的資料庫，因此維護成本高，
另外硬體成本較其他兩種架構高。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
2. Shared Database, Separate Schemas
這種架構是多個租戶(Multi-Tenant)共用一個資料庫，但每個租戶的資料會 存在一個屬於自己的資要綱要(Schema)中，這個資料綱要裡面包含了數個資料 表。這種架構在硬體成本上相較於 separate database 來的低，每一台伺服器可以 承受的租戶也比較多。但資料庫出問題時回復較難，當使用 Separate Schemas 的 架構且需要復原整個資料庫時，同時也會將其他租戶的 schemas 都覆蓋掉。
圖 2.9 Shared Database, Separate Schemas 圖 (Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006)
3. Shared Database, Shared Schema
所有的租戶使用相同的資料庫，且同時用同一組資料表來維護其資料，在
圖 2.8 Separate Database 圖
(Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006)
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
這種架構中，會用 tenant ID 來當作每個租戶的識別。使用這種架構讓每一台伺 服器可以負擔的租戶數量最多、單位成本最低且資源利用率最高。由於所有的租 戶都共用同一個資料庫，在安全維護和資料隔離程度上最差，當單一租戶的資料 需要復原時，必需在資料表中尋找所有相同的 tenant ID，數量多時就會造成其他 租戶的效能降低。
圖 2.10 Shared Database, Share Schemas 圖
(Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006)
共用資料庫與資料表的設計方式比起獨立資料庫或資料表的設計方式需要 更多的人力成本來開發，因為共用資料表方式的設計方式其資料庫關聯性更複 雜。雖然共用資料表方式初期開發成本較高，但共享資源的方法讓每台伺服器可 以服務更多租戶，因此長期來看，共享資源的總成本反而較低(圖 2.11.)。
圖 2.11 隔離方式與共享方式時間成本比較圖
(Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006)
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
2.5 SaaS 多租戶系統的資料綱要
提供應用系統多租戶服務的運作方式可以是一租戶一資料庫或是所有的租戶都 共用一資料庫，第一種完全沒有資源共享，第二種則缺乏擴充性，所有租戶都必 頇使用同一種資料綱要（data schema），不易提供租戶客製化的服務。兩者之間，
應該有一些比較彈性的作法存在。本節將介紹 5 種介於二者之間的資料綱要設計 技巧。
1. Private Table
這種方式沒有共用資料表，每個租戶共用資料庫但卻有各自的資料表使 用，以這種方式開發多租戶應用系統最為容易，因為的資料表的關聯性簡單，雖 然可以輕易達到客製化的目的，但租戶數多時將會有大量的資料表存在，無法有 效達到資源共享的目的，本研究就是以這種資料綱要為基礎。
圖 2.12 Private table layout
(Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,)
2. Extension Table
這個作法是將租戶共有的欄位抽出放置於一個共用表格，另外以 TenantID 欄位與 Row 欄位來區分不同租戶的資料，個別租戶客製化的資料欄位則分別寫 入各自的表格中。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 2.13 Extension table layout
(Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,)
3. Universal Table
將所有租戶的資料共同放置於一個包含大量欄位的資料表，採稀疏矩陣的 方式來存放資料，使用 TenantID 欄位與 Table 欄位來區分不同租戶的資料，因為 不同租戶間的資料差異會造成部份的欄位會有浪費的情況發生(造成許多空矩 陣)。
圖 2.14 Universal table layout
(Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,)
4. Pivot Table
將所有租戶的資料依資料型別分別存放在不同的表格，即同一型別的資料 放置於同一個資料庫表格，並以 TenantID 欄位、Table 欄位、Col 欄位以及 Row 欄位來區分不同租戶的資料，租戶資料則放置於其餘的欄位。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 2.15 Pivot table layout
(Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,)
5.
Chunk Table這種設計方式是改良 Pivot Table 與 Universal Table。首先，將所有租戶的資 料放置於同一個資料表，但它改良 Universal Table，加入了 Pivot Table 資料型別 的概念，將租戶資料依型別存放於不同的欄位內，不同於 Pivot table 一型別一表 格的作法，改以一組固定數量的型別欄位稱之為 chunk，。以 TenantID 欄位、Table 欄位、ChunkNo 欄位以及 Row 欄位來區分不同租戶的資料。
相對於 Pivot Table 的作法有下列優點：（1）減少詮釋欄位與實際資料的比 例。（2）只使用一個表格，所以方便還原建立各租戶的原始資料。另外相對於 Universal Table 的優點：（1）可以透過詮釋欄位來建立索引。（2）減少欄位的數 量。（3）加入資料型別的概念可減少資料欄位浪費的現象。基於以上的優點，本 研究將著重於討論如何將 Private Table 架構的多租戶系統在盡量不修改程式的方 式下轉換成 Chunk Table 資料綱要架構。
圖 2.16 Chunk table layout
(Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,)
‧
到上述的開發目的，資料綱要會以 Private Table 方式設計，除了方便隔離每個租 戶間的資料外，還可達到租戶系統客製化的目的。但這種資料綱要的設計方式當本研究包含二階段研究。第一階段研究目標為整理歸納將 Private Table 資料 綱要轉換為 Chunk Table 資料綱要的方法，提出系統化的步驟處理 DDL (Data
‧
Definition Language)與 DML (Data Manipulation Language) 陳述式 SQL 語法轉換 的作業，讓本研究提供的工具可以達到自動 SQL 語法重新改寫的目的。搭配 Jsqlparser 語法剖析工具協助我們更精確的將 SQL 語法分類，再透過系統化的轉 換步驟重組 SQL 語法完成任務。
‧
們則頇仰賴資料綱要對應表(Schema-Mapping Table)來達到可客製化的目的。本 研究主要目的在幫助程式開發人員減少應用系統因資料綱要修改而造成的額外 工作負擔，而非幫助資料綱要修改時資料轉換的作業，故只針對 Private Table 資 料綱要與 Chunk Table 資料綱要相互對應轉換的問題。本章 3.4.1 Schema-Mapping Table 檔案設計，將針對此考量進行實作。3.2.3. 設備資源共享
在開發 SaaS 多租戶應用系統時，最容易達到上述需求也最容易理解開發的 資料綱要設計方式就是 Private Table 架構，因為 Private Table 是分配每個租戶有 各自的資料表，所有商業邏輯對應到資料表上也很直覺，故利於系統開發。但租
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
3.3 系統流程
本研究適用於透過 JDBC 驅動程式存取資料庫的 Java 應用程式， 此 Java 應用程 式利用 JDBC API 來進行資料庫存取，利用統一的介面達到寫一個 Java 程式而可 以連接所有資料庫的目的。
本研究共包含二部分研究目標，目的皆為減少程式設計人員因資料綱要修改 而造成的工作負擔，故所開發的工具，皆為實現及輔助該目的，系統架構與運作 模式是透過 JDBC 攔截並加上 SQL 語法改寫機制的 Aspect，編譯之後成為含有 SQL 語法自動改寫的 JDBC 介面，使用時只要取代系統中原本的 JDBC 驅動程 式，只要資料庫改為共享資料表的架構之後，我們執行共享資料表版本的 SaaS 多租戶應用程式實際上還是執行租戶間獨立資料表的 SaaS 多租戶應用程式，就 可以達到共享資源共享資要表的目的。
圖 3.1 SQL 語句轉換機制運作模式
本研究分二部分研究目標，第一部分研究目標為整理歸納 SQL 語法如何由 Private Table 資料綱要設計方法轉換成 Chunk Table 資料綱要的系統化步驟與 Schema-Mapping table 相互搭配，後面章節將分為 DDL 與 DML 兩個部分來討 論，將 SQL 句子經過 SQL Parser 進行分類，利用語法剖析工具 Jsqlparser 把分類
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
過的 SQL 句子類別透過語句改寫機制的轉換規則，進行重組句子達到轉換的目 的。
第二部分研究架構如圖 3.2 所示，運用 AspectJ 從中攔截系統程式的 SQL 語法，將語法剖析工具產生重新改寫的 SQL 取代原本的語法交由系統執行，更 進一步達到不用修改應用系統的目的。
圖 3.2 系統架構與運作模式
由於 Java API 己定義 JDBC 的 Interface，故利用該介面開發 AspectJ 程式，
使其具備 SQL 語法自動轉換的功能，將實作的 SQL Rewriting Aspect 配合資料庫 原廠的驅動程式，利用 AspectJ 的編譯器將原廠的驅動程式重新編譯成新的 JDBC driver。系統先嘗詴實作 AspectJ，該 AspectJ 可依據語法剖析器設定的規則來決 定如何重組。此目的(1)可適用在任何的 JDK 版本(2)設計人員不需學習撰寫 AspectJ 程式(3)依實際資料綱要架構設計語句轉換條件。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
3.4 設計方法
本研究依照上述的流程進行設計， 首先說明如何設計 DDL(Data Definition Language)與 DML(Data Manipulation Language)語句轉換機制，接下來說明如何 設計 Aspect 與 SQL 語句轉換機制搭配，針對各步驟的內部運作進行詳細的說明。
3.4.1 資料綱要對應表(Schema-Mapping Table)檔案設計
資料庫結構由 Private Table 轉換為 Chunk Table 時，需建立 Schema-Mapping Table 以利轉換對照(如圖 3.3)。
圖 3.3 Schema-Mapping Table
資料綱要對應表(Schema-Mapping Table)的建立方式可以系統化的歸納出下 列步驟：
1. 取得所有 Tables 列表 (SHOW TABLES)
2. 針對每個 Table 取得欄位資訊 (DESCRIBE table_name) 3. 取出欄位型態並依照 chunk width 計算 chunkno
4. 建出 Schema-Mapping Table，內容值為：column:表格欄位名稱、table:流 水號、tenant:從 SESSION 取得、chunkno:依 chunk width 計算 chunk 編號、
colType:欄位資料型態
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 3.4 Schema-Mapping Table 建立步驟虛擬碼
系統化歸納出建立 Schema-mapping table 的步驟後，進一步將系統化步驟轉
系統化歸納出建立 Schema-mapping table 的步驟後，進一步將系統化步驟轉