Data Manipulation Language 設計方法

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) 資料庫中的表 格內選出 (SELECT)資料欄位，有時會進一步搭配條件篩選(WHERE)過濾選出的 資料。查詢語法 SELECE 從 private table 架構轉換為 chunk table 架構步驟如下：

1. 收集資料表(table)、欄位名稱

2. 查詢 Schema-mapping table 獲得資料表(table)、欄位的 metadata 3. 生成過濾查詢(filter query)語句

4. 置換原本語句裡的查詢參考(query reference)資料表

直接用範例來說明轉換步驟是如何將多租戶應用程式 private table 架構的 SQL 語 句轉換成 chunk table 架構的 SQL 語句：

假設 SELECT 查詢範例 Q1 為 SELECT name FROM tenant31 WHERE accountid= 1 步驟 1.要先取出資料表及欄位名稱，資料表 tenant31；欄位 accountid, name。

步驟 2.將取得的資料表欄位查找 schema-mapping table(圖 3.6)比對出位於 chunk table 上相對應欄位的 metadata 值，tenant31.accountid：table = 0、tenantid = 31、

chunkno = 0；tenant31. name：table = 0、tenantid = 31、chunkno = 0

圖 3.6 Tenant31 資料表的 Schema-Mapping Table

步驟 3. 生成過濾查詢(filter query)語句：

SELECT `str1` as `name`,`int1` as `accountid`

FROM chunktable

‧

WHERE tenantid=31 AND `table`=0 AND `chunkno`=0

步驟 4. 置換原本語句裡的查詢參考(query reference)資料表如下：

SELECT name FROM (

SELECT `str1` as `name`, `int1` as `accountid`

FROM chunktable WHERE tenantid=31 AND `table`=0 AND `chunkno`=0 ) AS tenant31 WHERE accountid= 1

經過上述 4 個步驟就完成了 private table 查詢 SELECT 語法轉換成 chunk table 查詢語法，並支援多租戶應用程式。Jsqlpaser 是我們利用的 SQL Parser 工 具，它協助我們將 SQL 轉換步驟用 SQL parser tree 方式處理，收集必要的欄位 node 並取出要置換的 node，最後完成 SQL 語句轉換作業。

在第 4 章系統實作及展示裡，SELECT 操作語法的轉換會依下列幾種常見的 陳述式來做討論：多欄位查詢、SELECE *查詢、多 Table 查詢、JOIN 陳述式及 多對多關聯 SELECT 查詢來驗證轉換步驟是否也可應用到上述情境。 INSERT 從 private table 架構轉換為 chunk table 架構步驟如下：

1. 收集所有相關欄位的 chunks、mete-data 2. 將每個 Insert 的 row 拆解成以 chunk 為一組 3. 將 insert 語法依每個 chunk 執行新增

‧ 國

立 政 治 大 學

‧

N a tio na

l C h engchi U ni ve rs it y

轉換步驟的程式虛擬碼如圖 3.7. INSERT 轉換步驟虛擬碼，與 SELECT 步驟 1 一樣先收集語法中所有的欄位名稱、資料表與輸入值，接下來將前面收集到的 欄位名稱搭配 schema-mapping table 做依照 chunk number 排序(小到大)的動作。

圖 3.7 INSERT 轉換步驟虛擬碼

下一步把排序過的欄位名稱 List 放到迴圈逐一檢查，迴圈裡面的一個關鍵點 就是要比對目前這個欄位的 chunk number 與上一個欄位的 chunk number 是否相 同，目的是讓相同 chunk number 的欄位資料放在同一句 INSERT 語句中，比對 結果若不相同就代表是新的一塊 chunk 所以前面一組相同 chunk 的資料就可以執 行 INSERT 動作先寫入到 chunk table 中；比對結果若是相同表示與前面一個欄位 是屬於同一塊 chunk，此時就先將欄位名稱與資料值記錄下來等待換到下一塊 chunk 時在進行寫入的動作，可以避免同樣一塊 chunk 的值被分開寫入到不同的 資料列。

寫入資料時所需要的 metadata 值(table、tenantid、欄位資料型別)都可透過 schema-mapping table 查找出來，其中 row 的部分是需要另外計算，在程式虛擬

‧

碼中所使用的方法是用 SELECT 在 chunk table 資料表中找出 WHERE tenantid 與 table 屬於該 INSERT 資料表的 row 欄位內容裡的最大值，把這個最大值加 1 就 設定(SET)成什麼("設定值 1")，這些資料要修改到(UPDATE)哪個表格。從 private table 架構轉換為 chunk table 架構的操作步驟分成 2 個階段：

1. query 階段：將 update 轉換為 select 語句，收集所有被 update 影響的 row。

用前述 select 語法轉換技巧於 chunk table 中取出對應於 private table 上 的 table 欄位(取出`row`為主)

2. update 階段：套用 UPDATE(Delete)於(a)階段的查詢結果:

update chunktable(by metadata: tenant, table, chunk, row)。

因為前面介紹的 SELECT 與 INSERT 都能夠透過 SQL 語法轉換機制把一種 schema 語法轉換成適合另外一種 schema 的語法執行結果就可以達到目的，但 UPDATE 無法直接轉換與法達到目的，必頇以程式邏輯來補足 SQL 語法轉換的

‧ 國

立 政 治 大 學

‧

N a tio na

l C h engchi U ni ve rs it y

不足，實際運作的程式虛擬碼如圖 3.8，所轉換的語句範例為：update tenant31_score set score=100, comment=„AA‟ where accountid=1。

圖 3.8 UPDATE 轉換步驟虛擬碼

程式執行時首先將 UPDATE 語法轉換成 SELECT 語法，條件式就取 UPDATE 語句裡 WHERE 的部分，而 SELECT 欄位就取 UPDATE 語句裡 SET 的部分，經 過第一次轉換可以得到 private table 結構的查詢語法，之後將查詢語法轉換為 chunk table 結構的查詢語法，轉換的過程中同時將所有相關資料表及欄位的 table、tenantid、chunkno、columnType 和 columnNamae 欄位記錄下來。

轉換查詢語法為 chunk table 架構之後，另外會再附加一個查詢欄位”row”，

接著將查詢結果的 row 欄位進入到迴圈，迴圈內再進入到一個包含所有修改欄位 的巢狀迴圈並在這裡執行 chunk table 的 UPDATE 動作，由 row 欄位再搭配所有 修改欄位的巢狀迴圈方式就可以處理修改多筆資料的狀況。如程式虛擬碼邏輯所 示，將 UPDATE 轉換為 SELECT 方式找出被影響欄位的 row 值，再搭配迴圈更 新相關欄位就完成了 chunk table 的 UPDATE 語法轉換。

‧

DELETE 的執行步驟與 UPDATE 完全相同，差別只在於最後一步實際執行 chunk table UPDATE 的語句修改為執行 DELETE 語句就能夠達到刪除資料列的 目的。 用系統介接的 join points，即 pointcut 是 join points 的集合。本研究利用 JDBC API 統一定義的特性，首先定義 package(java.sql)的 pointcut 如圖 3.9：

圖 3.9 package(java.sql)的 pointcut 定義

此 pointcout 名稱為 dbAccess，定義內容指執行到 package：java.sql 內的所 有 class 中的所有方法，不管傳入參數多少，回傳值型態為何，皆符合此 pointcut 的定義。

!within(SQLconn)是指在 Class SQLconn 之外呼叫的 Method 才符合此 pointcut，Class SQLconn 為 SQL 語句自動轉換程式如為此程式呼叫的 Method 部份則不符合，args(sqlStmt)指的是符合此 pointcut 的 SQL 語句以 sqlStmt 參

pointcut dbAccess(String sqlStmt) :

(call(* java.sql.Statement.execute*(String))) && !within(SQLconn)

&& args(sqlStmt);