第二章 相關研究與技術背景
2.5 SaaS 多租戶系統的資料綱要
國
立 政 治 大 學
‧
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 的步驟後，進一步將系統化步驟轉 為程式虛擬碼(圖 3.4)。本研究設計 Schema-mapping table 的格式有 table name + column name、table 編號、tenant id、chunk number 與 column type，5 個欄位，在 執行 private schema 轉換為 schema-mapping table 動作時，tenant id 可以是由登入 的 tenant session 方式取得，接著程式執行”SHOW TABLE”指令可以取得所有資 料表，利用一個迴圈執行”DESCRIBE 資料表名稱”指令則可以取得每個資料表 的屬性欄位，利用一個迴圈把資料表裡每個欄位的欄位名稱存到 column、資料 型別存到 colType 及依資料行別計算出 chunk number 存到 chunkno，就可以將所 有資料表的資料欄位建立到 schema-mapping table，本研究用文字檔(圖 3.5)來存 放 schema-mapping table 方便維運及查找。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 3.5 Schema-Mapping Table 存放檔案格式
資料綱要對應表建立的目的就是協助不同資料綱要設計方法間相互對應，以 本研究為例，建立此對應表之後雖然 Private Table 修改為 Chunk Table 架構了，
只要透過對應表查找，就能快速的取出某欄位在 2 個資料表間的實際欄位，對於 本研究以 SQL 語法自動轉換的研究目的有很大的幫助。
3.4.2 Data Definition Language 設計方法
本研究以 Private Table 資料綱要設計方法的 SaaS 多租戶應用系統為基礎，
以修改為 Chunk Table 資料綱要設計方法的程式架構為目的。本節將討論 DDL (Data Definition Language)的設計方法。
DDL 是用以執行管理工作並強制執行影響資料庫的商業規則，常見的陳述 式有 CREATE、ALTER、DROP 和其他執行類似 DDL 作業的預存程序，它們 適用於整個資料庫或整個伺服器之單一類型的所有命令。本研究助要著重在減少
‧
CREATE、ALTER、DROP 來做說明。DDL 操作語法的轉換我們則著重於資料綱要對應表(Schema-Mapping Table) 的欄位值修改，處理方式如下：
1. CREATE statements，執行新增資料綱要對應表欄位的動作。把新增的資料 表欄位增加到如圖 3.3 的資料綱要對應表中，新增的步驟與圖 3.4.1 資料 綱要對應表(Schema-Mapping Table)檔案設計，所說明的建立步驟一樣。
2. ALTER statements，異動資料綱要對應表欄位。依照觸發程序陳述式的關鍵 字(ADD、SET、DROP)來決定是要對資料綱要對應表欄位執行新增、修改 或是移除某欄位的動作。
3. DROP statements，刪除資料綱要對應表欄位。當執行觸發程序陳述式 DROP Table name 時，會把資料綱要對應表上屬於這個 table name 的欄位全部移 除，只要尋找欄位名稱包含 table name 的就可輕易完成移除欄位的動作。
3.4.3 Data Manipulation Language 設計方法
本節針對 DML (Data Manipulation Language)的部分，討論常用語法的系統化 語句轉換步驟，以各種使用情境的案例來證明語句轉換機制的適用範圍，DML 強制執行商業規則，以及當資料表或檢視中的資料修改時協助擴充資料的完整 性，操作的陳述式通常是 SELECT、INSERT、UPDATE 及 DELETE，後面章節 會針對各種操作語法轉換方式做系統化步驟歸納。
1. SELECT 語法轉換步驟
SELECT 在 SQL 語法中最常用的方式是將資料從資料庫中的表格內選出。
最基本的語法架構：
SELECT "欄位名" FROM "表格名"
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
從這句基本語法中，我們馬上可以看到兩個關鍵字，從 (FROM) 資料庫中的表
從這句基本語法中，我們馬上可以看到兩個關鍵字，從 (FROM) 資料庫中的表