第三章 系統設計
3.4 系統實作方法
3.4.5 SELECT 語句的轉換
國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
46
合中的每筆資料來說，本系統工具會各自產生兩個 DELETE 語句：共用資料表 DELETE 語句、私有資料表 DELETE 語句，分別刪除在共用、私有資料表中的資料，
其語句中的資料表名稱分別是共用資料表名稱(此為 CourseInfoCommonFileds)、
私有資料表名稱(此為 NccuCourseInfo)，WHERE 條件式則是集合中每筆資料的 詮釋欄位組合。圖 3.32 為轉換後的結果。
對詮釋欄位(‘Nccu’,1)所產生的共用、私有資料表 DELETE 語句：
對詮釋欄位(‘Nccu’,2)所產生的共用、私有資料表 DELETE 語句：
圖 3.32 DELETE 語句轉換後的結果
3.4.5 SELECT 語句的轉換
SELECT 語句主要是讓軟體開發人員對現有的資料進行查詢、統計等動作。其可 搭配 WHERE 條件式、JOIN 指令等增加查詢的多樣性。
在轉換之前，先了解 SELECT 語句的結構，從圖 3.33(line 6)清楚得知最基 本的 SELECT 語句是由回傳資料欄位、資料表名稱這兩項語句元素所組合而成；
此外，可以選擇性地加上 WHERE 條件式以縮小查詢的範圍。轉換 SELECT 語句主 要有三個步驟，歸納如下：
1. 透過改寫 JDBC 的部分 API 去攔截軟體開發人員所撰寫的 SQL 語句，此外，
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
47
軟體開發人員在 setTenantId()設定其 TenantId 以協助系統進行語句轉 換。在此，透過 TenantId 能夠清楚得知其 SQL 語句所要執行的對象(租戶)。
2. 將攔截到的語句藉由 JSqlParser 進行語句結構剖析的動作，從其結果獲得 轉換語句所需要的語句元素，同時，判斷回傳資料欄位是否為”*”關鍵 字。
3. 以 Extension Table Layout 的邏輯進行語句轉換。
圖 3.33 SELECT 語句範例
在這個部分以圖 3.33 為例詳細說明 SELECT 語句的轉換細節。首先，透過 改寫 JDBC 的部分 API 去攔截到軟體開發人員所撰寫的 SQL 語句，同時，也可以 取得 TenantId(此為 Nccu)以協助系統工具進行語句轉換(line 7)。再來，透過 JSqlParser 來取得其他語句元素，例如：回傳資料欄位(此為”*”關鍵字)、資 料表名稱(此為 CourseInfo)以及 WHERE 條件式(此為 Instructors LIKE“%陳恭
%”)。接下來，根據剖析結果判斷回傳資料欄位是否為”*”關鍵字(此為 True)，
系統工具發現回傳資料欄位存在”*”關鍵字，所以，必需先查詢 Columns_
Metadata Table 找出相對應記錄共用資料表(此為 CourseInfoCommonFields)、
私有資料表(NccuCourseInfo)的欄位資訊部份，如圖 3.34 以及圖 3.36 的紅字
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
48
部分所示，取得兩資料表的所有欄位名稱之後，將之組合起來取代”*”關鍵字 形成下一階段轉換後所產生之 SELECT 語句的回傳資料欄位。
最後，依據 Extension Table Layout 邏輯進行語句轉換，為了查詢出共用 資料表以及私有資料表的所有欄位資料，所以，進行 INNER JOIN 將共用資料表 以及私有資料表作鏈結並將鏈結後的資料表進行別名(Alias)的動作以達到利 用鏈結後的資料表來進行實際查詢的目的，如圖 3.35 的紅色方框所示。
圖 3.34 回傳資料欄位“*”關鍵字的轉換過程：
查詢 Columns_Metadata Table 找出相對應記錄共用資料表(此為 CourseInfoCommonFields)、
私有資料表(NccuCourseInfo)的欄位資訊部份，
將兩資料表的欄位資訊進行組合取代”*”成為轉換後語句的回傳資料欄位。
圖 3.35 建立實際查詢的資料表示意圖：進行 INNER JOIN 將共用資料表以及私有資 料表作鏈結並將鏈結後的資料表進行別名(Alias)的動作以達到利用鏈結後的資料表
進行實際查詢的目的。
圖 3.36 取代”*”關鍵字示意圖。
CourseId, CourseName, Instructors, Credit, Days, Time Location, Language
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
49
第四章 系統實作與評估
此章節針對系統工具進行實作展示與效能評估。在展示的部份，以採用本系統 工具實際協助開發一個多校(租戶)選課系統為範例，展示本系統工具的優點。
在效能評估的部份，透過一連串的實驗並根據結果推斷影響本系統工具執行時 間的因素。最後，分別在(1)Private Table Layout、(2) Extension Table Layout、
(3) Extension Table Layout 的詮釋欄位建立索引(Index) 這三種測試環境中 進行多項測試並根據其結果評估本系統工具的可行性。
4.1 系統實作展示
採用本系統工具實際協助開發一個多校(租戶)選課系統為例來展現其所帶來的 兩項優點：(1)提供租戶適度地客製化其資料表綱要的能力、(2)提升軟體開發 人員統一管理租戶的共有欄位以及其資料的能力。
4.1.1 多租戶應用程式範例情境描述
在這個多校(租戶)選課系統的範例中，仿造一般選課系統的使用情境，將設定 三個使用角色，分別是：選課系統維運管理者(軟體開發人員)、選課系統承租 學校(租戶)以及使用選課系統進行選課的學生(租戶的學生)。以下將針對這三 個角色的操作行為作簡單地介紹：
1. 選課系統維運管理者：意指軟體開發人員，負責選課系統的開發以及維運。
2. 選課系統承租學校：意指租戶，同時，也是軟體開發人員的客戶。這裡的
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
50
學校數量不只一個，可能有兩間以上的學校。每間學校可以新增、更改其 課程資訊或是客製化其部份資料表欄位以及查詢部分統計資訊。
3. 使用選課系統進行選課的學生：意指租戶的學生，也是進行選課的學生。
選課系統提供帳戶管理讓學生進行註冊、登入、查詢課程、在選課清單上 加入或移除課程以及排列選課志願順序等動作。
4.1.2 多租戶應用程式範例系統設計與實作
在資料層級設計方面，選課系統維運管理者根據一般選課系統的需求，擬 訂共用資料表的資料表綱要。以下為該系統的三個共用資料表說明以及資料表 綱要展示(schema)(圖 4.1)：
(1) StudentInfoCommonFields：此資料表用來儲存所有學校的學生基本資 料(僅含共有欄位部份)。
(2) SelectCourseCommonFields：此資料表用來儲存所有學校其每位學生 的選課記錄(僅含共有欄位部份)。
(3) CourseInfoCommonFields：此資料表用來儲存所有學校的課程資料(僅 含共有欄位部份)。
圖 4.1 多校(租戶)選課系統的範例之共用資料表(StudentInfoCommonFields、
SelectCourseCommonFields 以及 CourseInfoCommonFields)的資料表綱要設計。
選課系統維運管理者必須撰寫 CREATE 語句在資料庫中建立這三個共有資料 表，同時，對共有欄位設定一些限制(圖 4.2)。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
51
圖 4.2 CREATE 語句範例展示－建立共用資料表
完成建立共有資料表的動作後，在 CREATE EXTENSION TABLE 語句的協助下，
針對租戶(此為 Nccu)建立其使用的私有資料表集合。同時，Columns_Metadata Table 也會隨著 CREATE、CREATE EXTENSION TABLE 語句的執行，更新其欄位資 訊(圖 4.3)。
圖 4.3 CREATE、CREATE EXTENSION TABLE 語句執行後，Columns_Metadata Table 的更新結果：
CREATE 語句執行後，產生了共用資料表欄位資訊(即新增了 1~3 行的部分)；
CREATE EXTENSION TABLE 語句執行後，產生了私有資料表欄位資訊(即新增了 4~6 行的部分)。
在應用程式設計方面，藉著對資料庫進行新增資料(例如：學校新增課程資 料、學生將課程加入選課清單時，將選課記錄新增到資料庫)、更改資料(例如:
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
52
學校更改課程資料)、更改資料表綱要(例如：學校自行新增 CourseInfo 資料表 的客製化欄位、對其客製化欄位進行更名或刪除的動作)、刪除資料(例如:學生 從選課清單中刪除一門課程)亦或是查詢資料(例如：學生查詢選課清單)的動作 來協助完成選課系統的基本功能。上述的動作皆可依據一租戶一資料表的寫法 來撰寫 SQL 語句，再透過本系統工具依 Extension Table Layout 邏輯進行語句 轉換並在資料庫中執行產生影響。
假定應用程式會逐一分配給每間學校(租戶)一個獨一無二的租戶辨別碼，
即 TenantId。此 TenantId 透過 session 的方式從應用程式迭代到資料層級，所 以，系統工具在執行這些 SQL 語句時可藉由 TenantId 了解其執行對象。接下來 的例子，以 Nccu 該間學校(租戶)的角度示範如何藉由 SQL 語句的表達去實作多 校(租戶)選課系統的各種功能。
1. INSERT 語句：
通常在新增資料的時候會撰寫 INSERT 語句來表達，以學校新增課程資料為 例，先在網頁上提供一個 form 表單(圖 4.4)，表單欄位內容為該校 (即 Nccu) 的 CourseInfo 資料表的欄位(除了 CourseId 欄位，此欄位是經網站內部處理所 產生的一個獨一無二課程代碼)。
圖 4.4 學校新增課程資料的展示。
在網站內部處理的部份，為了產生一個獨一無二的課程號碼(即 CourseId)，
先撰寫 SELECT 語句，找出該校 CourseInfo 資料表中的課程資料筆數，然後，
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
53
以課程總數遞增的方式建立該新增課程的 CourseId。
緊接著，網站內部經由網頁表單取得其所需欄位資料後，組合產生的 INSERT 語句如下：
最後，可以清楚地從頁面看到其新增結果(圖 4.5)：
圖 4.5 學校新增課程資料的結果展示。
2. UPDATE 語句：
一般來說，透過 UPDATE 語句可以協助更改資料的動作，以學校(即 Nccu) 更改課程資料為例，首先，先讓學校選擇其欲更改資料的課程(圖 4.6)：
圖 4.6 學校點選欲修改課程的展示。
待選擇以後，則會出現該課程的原始資料，學校可以透過欄位去更改課程
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
54
資料，假設將 CourseId 為 Nccu14 的 Days 欄位從 Wed 更改成 Thu(圖 4.7)：
圖 4.7 學校進行課程資料修改的展示。
此時，藉由 UPDATE 語句去進行更改課程資料的動作。網站內部經由網頁表 單取得其所需欄位資料後，組合產生的 UPDATE 語句如下：
更改課程資料後的結果顯示在圖 4.8：
圖 4.8 學校修改課程資料的結果展示。
3. ALTER 語句：
學校可以透過網頁新增 CourseInfo 資料表的客製化欄位(即滿足租戶適度 地客製化其資料表綱要的需求)。除此之外，亦可以對客製化欄位進行更名、刪 除。以上這些對客製化欄位影響的動作皆利用 ALTER 語句即可完成。
以圖 4.9 為例，該學校(即 Nccu)將新增一個客製化欄位，其欄位名稱為
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
55
Location，型態為 char，長度為 50，以用來表示該課程的上課地點。
圖 4.9 學校新增客製化欄位的展示。
學校在輸入完該客製化欄位(即 Location)的資料型態等資訊後，網站內部 經由網頁表單取得其所需欄位資料後，組合產生以下的 ALTER 語句去完成新增 客製化欄位的動作：
ATTER 語句執行以後，課程資料的顯示頁面增加了一個 Location 欄位(圖 4.10)：
圖 4.10 學校新增客製化欄位的結果展示。
另外，也可以對客製化欄位進行更名以及刪除的動作，以上述新增的客製 化欄位(即 Location)為例，如果要將其名稱更名為 Classroom(圖 4.11)，則可 以撰寫圖 4.13 中的 ATTER 語句一；若是撰寫圖 4.13 的 ATTER 語句二，則執行 刪除該客製化欄位的動作(圖 4.12)，其執行後的結果如圖 4.14。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
56
圖 4.11 客製化欄位更名的展示。
圖 4.12 刪除客製化欄位的展示。
ALTER 語句一:
ALTER 語句二:
圖 4.13 客製化欄位更名以及刪除客製化欄位的 ALTER 語句展示。
圖 4.14 客製化欄位更名的結果展示。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
57
4. DELETE 語句
DELETE 語句主要是對過時或是錯誤的資料進行刪除的動作。以學生從選課 清單中刪除一門課程為例，如圖 4.15 所示學生可以透過頁面勾取其欲刪除的課 程。
圖 4.15 學生從選課清單刪除課程的展示。
網站內部取得學生欲刪除課程的 CourseId 之後，組合產生下列的 DELETE
網站內部取得學生欲刪除課程的 CourseId 之後，組合產生下列的 DELETE