第四章 系統實作與展示
4.2 系統實作展示
4.2.2. JDBC 驅動程式 with SQL Rewriting 實作展示
國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
UPDATE / DELETE 陳述式：UPDATE tenant31 SET major=‘資訊管理系' WHERE tenant31.accountid=2。語法執行的目的是要修改一筆資料表 tenant31 裡 的資料列，當 accountid=2 時欄位 major 的值由應用數學系更改為資訊管理系，
執行結果會協助我們修改指定的資料表與欄位的資料。DELETE 語法：DELETE FROM tenant31 WHERE tenant31.accountid = 2 語法執行的目的是要刪除一筆資 料表 tenant31 裡的資料列，當欄位 accountid=2 時即刪除該筆資料，執行結果會 協助我們刪除指定的資料表與欄位的資料。
4.2.2. JDBC 驅動程式 with SQL Rewriting 實作展示
本節會將上一節所介紹的所有情境，利用第 3.4 章提出的 SQL 語句轉換機 制轉換為適合用在 chunk table schema 架構的多租戶應用程式 SQL 語句，Chunk Table 這個作法是參考 Pivot 與 Universal table 的方式並加以改良。首先，也是將 所有租戶的資料放置於同一個資料庫表格，但它改良 Universal table，加入了資 料型別的考量，將租戶資料依其型別存放於不同的欄位內，一欄位一型別，稱之 為 chunk，而不是像 Pivot table，一型別一表格的作法。並以新增的 TenantID 欄 位、Table 欄位、ChunkNo 欄位以及 Row 欄位來區分不同租戶的資料。其餘的欄 位用來放置該租戶的資料。圖 4.14 是以上述的基礎設計出來的 schema，在本例 中，有整數與字串兩種資料型別，所以有 Int1 與 Str1 兩個 Chunks，同一筆資料 紀錄的相同型別的資料以 ChunkNo 來區別。例如，Tenant 31 的 AccountId 與 Grade 欄位皆為整數型別，但它們的資料可以透過 ChunkNo 的值來區別。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
ChunkTable (Int and Str)
TenantID Table ChunkNo Row Int1 Str1
31 0 0 0 1 李小白
31 0 1 0 2 資訊科學系
31 0 0 1 2 黃小明
31 0 1 1 3 應用數學系
32 1 0 0 1 陳小春
32 1 1 0 21 (空)
33 2 0 0 1 林小萱
33 2 0 1 2 周小芳
圖 4.14 Chunk Table 之多租戶資料綱要
本節第一個部分先說明如何系統化轉換 DDL 語法的執行，第二個部分再說 明 DML 語句的轉換規則與步驟，並證明本研究提出的 SQL 語句自動轉換機制 適用於這幾種常見的語法操作。
第一部份，我們先說明 DDL 操作語法是如何轉換應用於 chunk table schema 架構，基本上都是在做 schema mapping table 的維護修改，private table 轉出的 schema mapping table 如圖 4.15，依前一節提出的陳述式來做實際轉換：
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 4.15 Schema mapping table 圖
CREATE 陳述式：CREATE TABLE tenant32_student (address varchar(50), city varchar(50), country varchar(25), age integer(8))。語法執行的目的是要新增一組 schema 結構到 schema mapping table 裡，轉換方式如 3.4.2 章節所述，執行的結 果如圖 4.16，會協助我們建立一組新的資料表 schema 到 schema mapping table。
圖 4.16 CREATE 語法執行後的 Schema mapping
table
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
ALTER add 陳述式：ALTER table tenant32_student add project varchar(10)。
語法執行的目的是要新增一行 schema 結構到 schema mapping table 裡，依前面章 節所述轉換方式執行的結果如圖 4.17，會協助我們新增一行 schema 結構到 schema mapping table。
圖 4.17 ALTER add 語法執行後的 Schema mapping table
ALTER modify 陳 述 式 ： ALTER table tenant32_student modify project integer(3)。語法執行的目的是要修改 schema mapping table 裡的一行 schema 結 構，把欄位名稱 project 的型別修改成 int 並且修改 chunkno，依前面章節所述轉 換方式執行的結果如圖 4.18，修改型別成 int 之外也重新修改 chunkno 為 1。
圖 4.18 ALTER modify 語法執行後的 Schema mapping table
ALTER change 陳述式：ALTER table tenant32_student change project proj varchar(50)。語法執行的目的是要修改 schema mapping table 裡的一行 schema 結 構，把欄位名稱 project 改為 proj，型別修改成 str 並且修改 chunkno，依前面章 節所述轉換方式執行的結果如圖 4.19，修改欄位名稱與型別之外也重新修改 chunkno。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 4.19 ALTER change 語法執行後的 Schema mapping table
ALTER drop 陳述式：ALTER table tenant32_student drop proj。語法執行的目 的是要移除 schema mapping table 裡的一行 schema 結構，把欄位名稱 proj 這一行 從 schema mapping table 裡刪除，依前面章節所述轉換方式執行的結果如圖 4.20。
圖 4.20 ALTER drop 語法執行後的 Schema mapping table
DROP 陳述式：DROP TABLE tenant32_student。語法執行的目的是要移除 schema mapping table 裡的一組 schema 結構，依前面章節所述轉換方式所述，執 行的結果如圖 4.21，會協助我們從 schema mapping table 裡移除資料表名稱 tenant32_student 的整組資料。
圖 4.21 ALTER drop 語法執行後的 Schema mapping table
‧
SELECT 陳述式，依照 3.4.3 Data Manipulation Language 設計方法提出的 SELECT 4 個轉換步驟，實際應用於多欄位查詢、SELECE *查詢、多 Table 查詢、
JOIN 查詢和多對多關係查詢等，SELECT 語句轉換，以證明本論文提出的系統 化 SQL 語句轉換機制適用於上述常見的陳述式。
多欄位查詢語句：SELECT tenant31.accountid, tenant31.name, tenant31.major, tenant31.grade FROM tenant31 WHERE tenant31.accountid= 1。主要目的是實驗跨 chunkno 的查詢語法轉換。依照前面章節提出的轉換步驟進行 SQL 語句轉換：(1) 收集資料表與欄位名稱：tenant31(資料表) & name、major、grade、accountid(欄 位)。(2) 查詢資料綱要比對表：tenant31 (tenantid=31、table=0)，accountid, name (chunkno=0)&grade, major (chunkno=1)。(3) 生成 filter query：JOIN 不同 chunkno 的 SELECT 語句，accountid, name (chunkno=0)的 filter query 是(SELECT int1,str1 FROM chunktable WHERE tenantid=31 AND table=0 AND chunkno=0)，grade, major (chunkno=1) 的 filter query 是(SELECT int1,str1 FROM chunktable WHERE tenantid=31 AND table=0 AND chunkno=1)，JOIN 起來的完整 filter query 為 (select a.int1 accountid, a.str1 name, b.int1 grade, b.str1 major FROM
(SELECT row, int1,str1 FROM chunktable WHERE tenantid=31 AND table=0 AND chunkno=0) a inner join
(SELECT row, int1,str1 FROM chunktable WHERE tenantid=31 AND table=0 AND chunkno=1) b on a.row=b.row
) AS tenant31
(4) 置換原本語句：SELECT Name, grade, major FROM (filter-query) WHERE accountid=1，透過語句轉換機制並不會改變原來 SELECT 語句的結構，只需針 對查詢參考(query reference)資料表做置換就可以在 chunk table schema 架構下執
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
行，為方便 JOIN、UPDATE、DELETE 等語法的使用，轉換後的語句都會額外 多查詢欄位”row”，經過轉換執行的結果如圖 4.22，會協助我們查詢指定的資料 表與欄位。
圖 4.22 多欄位查詢語法 chunk table 執行結果
SELECE *查詢語句：select * from tenant31。屬於多欄位查詢的一種特例，
執行語句轉換之前必頇先將”*”轉換為查詢參考資料表裡的所有欄位名稱(圖 4.23)，再依照前面章節提出的轉換步驟進行 SQL 語句轉換，即可在 chunk table 裡查詢指定的資料表的所有欄位。
圖 4.23 SELECT *語句置換
多 Table 查詢語句：SELECT tenant31.major, tenant31_class.accountid FROM tenant31,tenant31_class WHERE tenant31.accountid= 1。主要目的是實驗如何在 chunk table 查詢不同資料表裡的欄位資料，一樣依照前面單一資料表 SELECT 語法轉換步驟進行語句轉換，關鍵在於步驟 4 的 filter query 必需要置換到正確的 查詢參考資料表，執行多 table 查詢語句轉換的結構圖如圖 4.24。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 4.24 多 Table 語句轉換 parser tree
JOIN 查詢語句 ： select name, classname, room from tenant31 inner join tenant31_class ON tenant31.accountid = tenant31_class.accountid。主要目的是實驗 跨本研究的語句轉換機制是否適用於 JOIN 語法。依照前面章節提出的轉換步驟 進行 SQL 語句轉換：(1) 收集資料表與欄位名稱：tenant31(資料表) & name、
major、grade、accountid(欄位)；tenant31_class(資料表) & classname、room、accountid (欄位)。(2) 查詢資料綱要比對表：tenant31 (tenantid=31、table=0)，accountid, name (chunkno=0) ； tenant31_class (tenantid=31 、 table=3) ， classname (chunkno=0)&
accountid, room (chunkno=1)。(3) 生成 filter query：tenant31 的 filter query 是 (SELECT str1 as `name` , `int1` as accountid FROM chunk.chunktable WHERE tenantid=31 AND `table`=0 AND chunkno=0) AS tenant31；tenant31_class 的 filter query 是(select a.str1 classname, b.str1 room, b.`int1` accountid FROM
(SELECT `row`,str1 FROM chunk.chunktable WHERE tenantid=31 AND `table`=3 AND chunkno=0) a inner join
‧
(SELECT `row`,str1, `int1` FROM chunk.chunktable WHERE tenantid=31 AND
`table`=3 AND chunkno=1) b on a.`row`=b.`row` ) AS tenant31_class。
(4) 置換原本語句：select name, classname, room from (tenant31‟s filter query) inner join (tenant31_class‟s filter query) ON tenant31.accountid = tenant31_class.accountid，透過語句轉換機制並不會改變原來 JOIN 語句的結構，
只需針對查詢參考資料表做置換就可以在 chunk table schema 架構下執行，經過 轉換執行的結果如圖 4.25，會協助我們查詢指定的資料表與欄位。
圖 4.25 JOIN 查詢語法 chunk table 執行結果
多 對 多 關 係 查 詢 語 句 ： select tenant31.`name`, tenant31_class.classname, tenant31_score.score from tenant31 inner join tenant31_score on tenant31.accountid
= tenant31_score.accountid inner join tenant31_class on tenant31_score.classid = tenant31_class.classid。與 JOIN 語法的轉換步驟一樣，差別在於多對多關係的查 詢通同常需要至少 3 個資料表的 JOIN，次數比較多，但整個轉換的過程處理方 式都相同，轉換成 chunk table schema 架構的 SQL 語句如下：
select tenant31.`name`, tenant31_class.classname, tenant31_score.score from (select `int1` accountid,`str1` `name` from chunktable
where `table`=0 AND tenantid=31 AND chunkno=0 ) AS tenant31
inner join (select a.`int1` as accountid, b.`int1` as classid, c.`int1`as score FROM
(select `row`,`int1` from chunktable where `table`=2 AND tenantid=31 AND
chunkno=1) a inner join (select `row`,`int1` from chunktable where `table`=2 AND
‧ b.`row`=c.`row` ) AS tenant31_score
on tenant31.accountid=tenant31_score.accountid
inner join (select `int1` classid,`str1` classname from chunktable
where `table`=1 AND tenantid=31 AND chunkno=0 ) AS tenant31_class
on tenant31_score.classid=tenant31_class.classid灰底斜體字的部分就表示是經過語句轉換機制產生出來的 filter query 所置換原 本的參考查詢資料表，執行的結果如圖 4.26，會協助我們連接不同資料表並查詢 指定的欄位資料。
圖 4.26 多對多查詢語法 chunk table 執行結果
INSERT 陳述式：INSERT INTO tenant32 (`accountid`, `name`, `age`) VALUES (2, '林小白', 25)。語轉換方法詳述於第 3.4.3 章節，依照前面章節提出的轉換步驟 進行轉換：(1) 收集所有相關欄位，tenant32(資料表，table=1、tenantid=32) &
accounted、name、age (欄位) (2) 相同 chunk 為一組，accounted、name(chunkno=0)
& age(chunkno=1)， (3)每一組組合成為一句 INSERT 執行語句，將資料新增到 chunk table，實際上這段 INSERT 語句會轉換成下列方式執行：
(a) accountid、name：
INSERT INTO chunktable (`table`,tenantid,chunkno,`row`,`int1`,`str1`) VALUES (1,32,0, (MAX row+1),2, '林小白');
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
(b) age：
INSERT INTO chunktable (`table`,tenantid,chunkno,`row`,`int1`) VALUES (1,32,1, (MAX row+1), 25);
UPDATE / DELETE 陳述式：UPDATE tenant31 SET major=‘資訊管理系' WHERE tenant31.accountid=2。語法轉換步驟如 3.4.3 章節說明，操作步驟分成 2 個階段，第一階段將 UPDATE 轉換為 SELECT 語法，取出資料表(tenant31)與欄 位 (major 、 accountid) ， 轉 換 為 Select row, major FROM tenant31 WHERE tenant31 .accountid = 2，經過 SELECT 語句轉換步驟轉換成 chunk table 的查詢語 法，查詢結果如圖 4.27。
圖 4.27 UPDATE 轉 SELECT 查詢結果
第二階段將取回的 row 值套用到迴圈並搭配要修改的欄位資訊，重新執行新 的 UPDATE 語句，依本例句會將 row=1 (accountid=2)取出，實際執行的 UPDATE 語句為 UPDATE chunktable SET `str1`=`資訊管理系` WHERE table=0 AND tenant=31 AND chunk=1 AND row=1。若改執行 DELETE 語法，前述的步驟都一 樣，差別只在於最後實際執行的語法會組合成 DELETE chunktable WHERE table=0 AND tenant=31 AND chunk=1 AND row=1，刪除 chunk table 上對應的正 確資料列。
‧
Chunk table 是改良 Pivot 與 Universal table 的設計方式，可以依照系統資料 庫 schema 的資料型態定義合適的 chunk width，chunk width 指的是存放資料型別 的寬度，設定越大，效果就會類似 Universal table 而產生過多的空值欄位造成浪 費；設定越小，則需要搭配大量的 JOIN 查詢協助取出需要的資料列。
本研究為方便解說 SQL 語句轉換機制的設計，因此定義 chunk width 為 2(即 int 與 str)，因此在做語句轉換時必頇用到大量 JOIN 語句，這會造成 SELECT 效 能降低的問題，因此我們利用 chunk table 的 metadata 欄位建立 index 的方式來提 高 JOIN 的效率。相較於 Universal table 的作法，因為欄位沒有型別(types)，都 是 varchar 所以排序沒有意義，故無法建立傳統的 index，但是 chunk table 的作 法，因欄位有型別 types 觀念，因此還可以建立傳統式的 index 來提升查詢效率。
本節利用 SQL 效能分析工具：IBM DB2 SQL Performance Analyzer Tool，協助 我們進行 SQL 語句效能的量測，使用同樣的 JOIN 查詢語句在同樣的 chunk table 下比較有建立 index 與沒有建立 index 量測的結果，index 建立在 metadata 欄位：
tenantid、table、chunkno 及 row 欄位，執行測詴語法如下：
select a.str1 classname, b.str1 room, b.`int1` accountid FROM
(SELECT `row`,str1 FROM chunk.chunktable WHERE tenantid=31 AND
`table`=0 AND chunkno=0) a inner join
(SELECT `row`,str1, `int1` FROM chunk.chunktable WHERE tenantid=31 AND
`table`=0 AND chunkno=1) b on a.`row`=b.`row`
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
圖 4.28 沒建 index 的 JOIN 查詢效能
實驗結果沒有建立 index 的 JOIN 查詢語句量測的 return time 為 51.47(圖 4.28)，有建立 index 的 return time 為 25.76(圖 4.29)，量測結果證實將 chunk table 的 metadata 欄位建立 index 的確在 JOIN 查詢時效率可以提高。
實驗結果沒有建立 index 的 JOIN 查詢語句量測的 return time 為 51.47(圖 4.28)，有建立 index 的 return time 為 25.76(圖 4.29)，量測結果證實將 chunk table 的 metadata 欄位建立 index 的確在 JOIN 查詢時效率可以提高。