支援多租戶應用程式的SQL語句轉換機制 - 政大學術集成

全文

(1)國立政治大學資訊科學系 Department of Computer Science National Chengchi University 碩士論文 Master‟s Thesis. 立. 政治大. ‧ 國. 學. 支援多租戶應用程式的 SQL 語句轉換機制. ‧. A SQL Rewriting Mechanism for Enabling Multi-tenant Applications. n. er. io. sit. y. Nat. al. Ch. engchi. i n U. v. 研究生：吳定威指導教授：陳. 恭. 中華民國一百一年一月 December 2011. I.

(2) 支援多租戶應用程式的 SQL 語句轉換機制 A SQL Rewriting Mechanism for Enabling Multi-tenant Applications. 研究生：吳定威. Student：Ding-Wei Wu. 指導教授：陳. Advisor：Kung Chen. 恭. 立. 政治大. 國立政治大學. ‧ 國. 學. 資訊科學系碩士論文. ‧ y. Nat. A Thesis. io. sit. Subm. er. itted to Department of Computer Science. a. n. v l C Chengchi University National ni U. h. e n gofcthe h i Requirements in partial fulfillment for the degree of Master in Computer Science 中華民國一百一年一月 January 2012. II.

(3) 支援多租戶應用程式的 SQL 語句轉換機制. 摘要. 軟體即服務（Software as a Service, SaaS）是雲端運算環境下一種新興的服務模式，它的特色是將特定的應用軟體以共享軟硬體資. 政治大. 源的方式供眾多的租戶（tenants）使用。因此，如何提供共用且. 立. 有客制化功能的應用軟體就是 SaaS 模式的重要成功關鍵。本研究. ‧ 國. 學. 聚焦於資料庫層提供多租戶共用表格但有客制化功能的機制。讓. ‧. 開發人員延續一租戶一資料表的架構為基礎設計資料庫，透過. Nat. io. sit. y. SQL 語句轉換機制，將 SQL 語句轉換為操作共享表格的語句執. er. 行，以大幅減化開發人員的工作。本研究包含兩個部份，一是資. al. n. v i n 料綱要對應表(SchemaCMapping h e n gTable)，協助開發人員將單租戶資 chi U 料庫架構轉換為塊狀表格(chunk table)的共用資料綱要方式；另一. 個是 SQL 語句自動轉換工具，透過剖面技術織入 JDBC 程式集中，在應用系統執行期間，將 SQL 語句，從一租戶一資料表寫法轉換為共享表格的方式執行。故只需將資料表改為共用架構即可使用，達到提升多租戶系統開發速度並提高資源使用率的目的。. 關鍵字： SaaS、Multi-tenant、AOP、AspectJ、SQL Rewriting 1.

(4) A SQL Rewriting Mechanism for Enabling Multi-tenant Applications. Abstract. Software as a service (SaaS) is an emerging service model of cloud computing, which is characterized by providing software that charges on a usage basis over shared hardware and software resources for a large. 政治大. number of tenants to use. Therefore, how to provide shared tables and. 立. customization of the applications is an important key to success in the. ‧ 國. 學. SaaS model. This study focuses on how to enable multi-tenancy on the database layer Developers can extend a private table structure-based. ‧. application to a shared database one through the proposed SQL rewriting. y. Nat. mechanism. This approach can greatly simplify the developer's work. Our. io. sit. approach consists of two parts, first is Schema Mapping Table that helps. a. er. developers to convert single-tenant database architecture into the chunk. n. v table schema; another isl SQL statement conversion n i tools that works by Ch. U. e naspect g c hinithe AspectJ language. During enhancing the JDBC library as an the execution of the application, a SQL statement is rewritten from the private table based format into chunk table based statement. In this way, we are able to enhance an application with multitenant shared tables that improve resource utilization.. Keywords: SaaS、Multi-tenant、AOP、AspectJ、SQL Rewriting. 2.

(5) 誌謝能夠走到這一步，我最感謝親愛的家人的支持與鼓勵，在我最辛苦的這段日子是他們給我堅持下去的力量，也很感謝陳恭老師的指導，因為老師的認真教學經常到晚上還會收到老師的回信，讓我知道自己要更加努力才能讓研究有一點成果，還要感. 政治大. 謝身邊的朋友、同事、學長姐們，有了他們的幫忙讓我在求學. 立. 的路上遭遇到任何困難時，總有人會傾聽我、幫助我、鼓勵我、. ‧ 國. 學. 給我意見，也謝謝他們這段時間對我的包容。. ‧. 另外，要特別感謝就是辛苦的媽媽，因為媽媽的關心與付. Nat. io. sit. y. 出，讓我可以沒有其他負擔的專心在課業上努力。還要感謝葛. er. 伯伯對我們的照顧與鼓勵，因為葛伯伯求知學習的態度讓我知. al. n. v i n C h有感謝天星的王總，感謝他的知遇之道學習是沒有止盡的。還 engchi U. 恩以及對我的提攜，才讓我能夠順利的完成學業。最後，我希望能夠把這份榮耀與天上的爸爸一起分享。. 國立政治大學. 吳定威. 資訊科學研究所在職專班 January 2012. 3.

(6) 目. 錄. 第一章緒論 ....................................................... 8 1.1 前言.................................................................................................................. 8 1.2 研究動機.......................................................................................................... 8 1.3 研究目的.......................................................................................................... 9 1.4 研究成果........................................................................................................ 11 1.5 論文大綱........................................................................................................ 11 第二章相關研究與技術背景 ........................................ 12 2.1 Aspect-Oriented Programming................................................................... 12 2.2 AspectJ .......................................................................................................... 16 2.3 Jsqlparser...................................................................................................... 19 2.4 SaaS Mutil-tenant System ........................................................................... 19 2.5 SaaS 多租戶系統的資料綱要 ...................................................................... 21 第三章系統設計與架構 ............................................ 26. 立. 政治大. ‧ 國. 學. ‧. 3.1 系統設計理念 .............................................................................................. 26 3.2 系統設計考量 .............................................................................................. 27 3.2.1 資料隔離性......................................................................................... 27 3.2.2 可客製化能力..................................................................................... 28 3.2.3 設備資源共享..................................................................................... 28. sit. y. Nat. n. al. er. io. 3.3 系統流程....................................................................................................... 29 3.4 設計方法........................................................................................................ 31 3.4.1 資料綱要對應表檔案設計................................................................ 31 3.4.2 Data Definition Language 設計方法 .................................................. 33 3.4.3 Data Manipulation Language 設計方法 ............................................. 34 3.4.4 Access SQL 改寫 Aspect 設計 .......................................................... 40 3.4.5 產生具 SQL 語法改寫功能之 JDBC 驅動程式 .............................. 42 第四章系統實作與展示 ............................................ 44. Ch. engchi. i n U. v. 4.1 實作語言與工具 ........................................................................................... 44 4.2 系統實作展示 ............................................................................................... 45 4.2.1 實例情境說明與轉換機制................................................................ 45 4.2.2. JDBC 驅動程式 with SQL Rewriting 實作展示 .............................. 53 4.3 測詴數據........................................................................................................ 66 4.3.1 Chunk table 與 SQL 語句自動轉換測詴數據................................... 66 4.3.2. 建立 index 測詴數據 ........................................................................ 68. 4.

(7) 第五章結論與建議 ................................................ 71 5.1 結論............................................................................................................... 71 5.2 未來發展....................................................................................................... 72 參考文獻 ......................................................... 73. 立. 政治大. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. Ch. engchi. 5. i n U. v.

(8) 表目錄. 圖 4.1 一租戶一資料庫 ...................................... 68 圖 4.2 多租戶共享一資料庫 .................................. 70. 圖目錄. 政治大. 圖 1.1 一租戶一資料庫 ....................................... 9 圖 1.2 多租戶共享一資料庫 ................................... 9 圖 2.1 重覆出現的程式碼片段 ................................ 13 圖 2.2 利用 AOP 織入重覆程式碼片段 .......................... 14 圖 2.3 以 AOP 方式組合成應用系統的各類模組 .................. 15 圖 2.4 AOP 編譯程式流程..................................... 16 圖 2.5 AspectJ 的編譯流程................................... 18 圖 2.6 SQL 語句剖析與自動轉換概念圖......................... 19 圖 2.7 資料隔離與資源共享關係圖 ............................ 20. 立. ‧. ‧ 國. 學. sit. y. Nat. n. al. er. io. 圖 2.8 Separate Database 圖................................. 21 圖 2.9 Shared Database, Separate Schemas 圖................. 21 圖 2.10 Shared Database, Share Schemas 圖................... 22 圖 2.11 隔離方式與共享方式時間成本比較圖 ................... 22 圖 2.12 Private table layout ............................... 23 圖 2.13 Extension table layout ............................. 24 圖 2.14 Universal table layout ............................. 24 圖 2.15 Pivot table layout ................................. 25 圖 2.16 Chunk table layout ................................. 25 圖 3.1 SQL 語句轉換機制運作模式............................. 29. Ch. engchi. i n U. v. 圖 3.2 系統架構與運作模式 .................................. 30 圖 3.3 Schema-Mapping Table ............................... 31 圖 3.4 Schema-Mapping Table 建立步驟虛擬碼................. 32 圖 3.5 Schema-Mapping Table 存放檔案格式................... 33 圖 3.6 Tenant31 資料表的 Schema-Mapping Table .............. 35 圖 3.7 INSERT 轉換步驟虛擬碼............................... 37. 6.

(9) 圖 3.8. UPDATE 轉換步驟虛擬碼............................... 39. 圖 3.9 package(java.sql)的 pointcut 定義 .................... 40 圖 3.10 依不同 SQL 語法的 advice 虛擬碼...................... 41 圖 3.11 本研究編譯流程..................................... 42 圖 4.1 一租戶一表格之範例 .................................. 46 圖 4.2 CREATE 語法執行結果.................................. 47 圖 4.3 ALTER add 語法執行結果............................... 47 圖 4.4 ALTER modify 語法執行結果............................ 48 圖 4.5 ALTER change 語法執行結果............................ 48 圖 4.6 ALTER drop 語法執行結果.............................. 49 圖 4.7 多欄位查詢語法執行結果 .............................. 49 圖 4.8 SELECT *查詢語法執行結果 ............................ 50 圖 4.9 多 Table 查詢語法執行結果 ............................ 50 圖 4.10 JOIN 查詢語法執行結果............................... 51 圖 4.11 多對多資料表關聯圖 ................................. 51. 立. 多對多查詢語法執行結果 ............................. 52 INSERT 語法執行結果................................. 52 Chunk Table 之多租戶資料綱要........................ 54 Schema mapping table 圖............................. 55 CREATE 語法執行後的 Schema mapping .................. 55 ALTER add 語法執行後的 Schema mapping table ......... 56 ALTER modify 語法執行後的 Schema mapping table ...... 56. sit. y. Nat. n. al. er. ALTER change 語法執行後的 Schema mapping table ...... 57 ALTER drop 語法執行後的 Schema mapping table ........ 57 ALTER drop 語法執行後的 Schema mapping table ........ 57 多欄位查詢語法 chunk table 執行結果 ................. 59 SELECT *語句置換 ................................... 59 多 Table 語句轉換 parser tree........................ 60 JOIN 查詢語法 chunk table 執行結果................... 61 多對多查詢語法 chunk table 執行結果 ................. 62 UPDATE 轉 SELECT 查詢結果............................ 63 沒建 index 的 JOIN 查詢效能 .......................... 65. io. 圖 4.19 圖 4.20 圖 4.21 圖 4.22 圖 4.23 圖 4.24 圖 4.25 圖 4.26 圖 4.27 圖 4.28. ‧. ‧ 國. 學. 圖 4.12 圖 4.13 圖 4.14 圖 4.15 圖 4.16 圖 4.17 圖 4.18. 政治大. Ch. engchi. i n U. v. 圖 4.29 有建 index 的 JOIN 查詢效能 .......................... 65 圖 4.30 沒建 index 的測詴數據圖 ............................. 67 圖 4.31 有建 index 的測詴數據圖 ............................. 69. 7.

(10) 第一章緒論 1.1 前言隨著雲端運算環境的興起，軟體即服務（Software as a Service, SaaS）的營運模式也開始受到軟體開發商的注意，紛紛投入資源，將一些既有的應用軟體以及新. 政治大單一用戶使用的模式，SaaS 的其中一個特色是在資料中心安裝應用系統，以多立. 開發的軟體採用此種模式推向市場。不同於以往在客戶端安裝（on-premises）供. 租戶（multi-tenants）方式營運：每個租戶的使用者透過網路使用該應用軟體，. ‧ 國. 學. 其運作所需的軟硬體設施由資料中心與應用軟體開發商負責維運，租戶只需按使. ‧. 用量與服務品質等因素依合約付費。. y. Nat. 服務提供商可以用充分共享軟硬體資源的方式，提供多租戶服務，而不是一. er. io. sit. 租戶一套設備的方式運作。在資源共享方面，除了關鍵的虛擬化（virtualization）技術外，如何讓各個租戶之間可以共享資料庫也是一個重要技術指標。. n. al. Ch. 1.2 研究動機. engchi. i n U. v. 因為開發資訊系統所需耗費的人力經費有限，往往只能選擇滿足大部分使用者需求的功能項目來開發，對於剩下少部分的使用者需求無法滿足，根據長尾效應理論，這些少部分使用著的需求加總起來也將會是一個具有規模的市場。有少數特殊功能需求的中小企業使用者通常也因為開發預算的不足而無法將特殊功能加到資訊系統，因此 SaaS 資訊系統可用來滿足這些需求，多租戶 SaaS 應用系統可依租戶的需求客製化系統取用需要的功能，可以在有限的預算下選用有用的功能，發揮最大的效益。. 8.

(11) 本研究將整理歸納不同資料綱要間的 SQL 語句轉換步驟，提出系統化的規則並實作出 SQL 語句自動轉換工具，搭配 schema mapping table 可以滿足資料欄位客製化的需求，並且本研究認為可以進一步搭配 AspectJ 實作一個工具，此工具適用於任何使用 JDBC 介面去存取資料庫管理系統（DBMS）的 JAVA 應用程式，它能攔截透過 JDBC 驅動程式連結資料庫管理系統（DBMS）的 SQL 程式語法，再依目前資料庫資料綱要的架構重新改寫應用程式的 SQL 語法，達到改變多租戶應用程式資料綱而不用修改應用程式的目的。. 政治大為使系統提供多租戶服務，可以用立 2 種極端的例子來達到目的，一種是一租戶一 1.3 研究目的. ‧ 國. 學. 資料庫的寄存服務（圖 1.1），但完全沒有資源共享；另一個是所有的租戶都共用一資料庫（圖 1.2），這種方式則缺乏擴充性，所有租戶都必頇使用同一種資料綱. ‧. 要（data schema），不易提供租戶客製化的服務。兩者之間，應該有互補的作法. n. al. er. io. sit. y. Nat. 存在，本研究的目的就是要提供介於二者之間的多租戶系統服務。. Ch. engchi. 圖 1.1 一租戶一資料庫. i n U. v. 圖 1.2 多租戶共享一資料庫. 本研究主要目標有： 1.. 提供多租戶 SaaS 應用系統從獨立資料表架構轉換成共用資料表架構的 SQL 語法轉換機制。. 2.. 減少因改變資料綱要而增加的額外修改程式的負擔，提高維運的效率。 9.

(12) 實作可客製化且共用資料表架構多租戶應用程式。. 3.. 根據上述目標，本研究實作一套依目前應用程式資料綱要自動修改程式 SQL 語法的工具，當程式中有任何使用 JDBC 介面去存取資料庫管理系統（DBMS）的 JAVA 應用程式，它能自動攔截應用程式透過 JDBC 驅動程式連結資料庫管理系統（DBMS）的 SQL 語法並轉換成符合目前資料綱要的語法取代原本程式的資料庫存取動作。此工具可適用在不同的 JDK 版本與資料庫管理系統，配合應用程式開發環境，利用 Aspect-Oriented Programming(AOP)特性，將 JDBC 驅動程式加上自動. 政治大業人力集中在資料庫修改及移轉這個部份，減少應用程式修改的人力及時間之成立轉換 SQL 語法的 AspectJ 程式，將 SaaS 多租戶應用程式改變資料綱要架構的作. 本浪費，而不因資料綱要架構的修改而造成程式設計者需將程式邏輯重新撰寫的. ‧ 國. 學. 負擔，並可達成無需修改應用程式的目的。應用此工具時，只需置換目前使用的. y. Nat. 應用系統。. ‧. JDBC 驅動程式即可，大幅簡化繁雜的安裝流程，方便應用於開發中及已上線的. er. io. sit. 另外，在資料庫自動轉換、資料綱要比對表(Schema-Mapping Table)自動建立及資料自動移轉方面，或其他更進一步應用，如多租戶架構安全性、共用資料. n. al. i n 表架構的執行效能問題等，亦是未來值得研究的議題。 Ch engchi U. v. 本研究主要的方向如以下項目：. 1. 探討現行多租戶應用程式共用資料表的建置技術，並歸納出適用於一租戶一資料表與多租戶共用資料表之間的 SQL 語法轉換機制。 2. 討論如何取出 SQL 語句的元素並重新取代組合成符合新架構的 SQL 語句。 3. 利用 AOP 結合 SQL 語句轉換機制實作出自動化轉換工具，協助程式開發人員在移轉資料綱要架構之後減少需要修改程式的負擔。. 10.

(13) 1.4 研究成果本研究基於前述研究動機、研究目的並蒐集相關文獻與技術探討，實作文獻的技術並開發適用於共用資料庫共用資料表，但每個租戶又能夠資料獨立的多租戶應用程式，實作 SQL 語法自動剖析及轉換工具讓一租戶一資料表的多租戶應用程式轉換成共用資料表架構的多租戶應用程式，以達到資源共享、租戶可客製化的目的，並且最後再探討未來發展方向。本論文主要的成果有： 1.. 以一租戶一資料表的資料綱要架構為多租戶應用程式的基礎，整理出常. 政治大 Language 的部分)，歸納出通用的語句轉換規則。立. 用的 SQL 執行語句(包含 Data Definition Language 及 Data Manipulation. 實作具剖析及 SQL 語句轉換機制的 Aspect 程式：本研究可適用在不同. ‧ 國. 學. 2.. 的 JDK 版本與資料庫，實作具客製化儲存存取紀錄功能的 Aspect 程. ‧. 式，使用者不需修改原本的 SaaS 多租戶應用程式即可達到轉換共用資. y. sit. al. er. 建立獨立資料表架構的測詴程式，並於系統中應用所實作的工具。當應. io. 3.. Nat. 料表資料綱要的目的。. v. n. 用系統轉換成共用資料庫共用資料表的架構時，測詴本研究提出的工具. Ch. engchi. i n U. 轉換效果是否能正確應用在共用資料表架構的資料庫。. 1.5 論文大綱本論文主要分為五個章節，第一章為緒論，主要在介紹整個研究的源起與概略性解說，包括前言、研究動機、研究目的、研究成果及各章節概述；第二章，主要會介紹 Aspect-Oriented Programming 概念並包含 AspectJ 的相關技術及 SQL Parser Tree 工具-Jsqlparser 相關技術與 SaaS 多租戶應用系統及其資料綱要設計方法技術；第三章，完整的介紹本論文的核心概念及技術；第四章，實際的展示研究成果；最後，我們在第五章提出結論及未來可能的發展。 11.

(14) 第二章相關研究與技術背景在開發 SaaS 多租戶應用程式時，我們希望能以共用資料表共享資源的方式提高資源使用率，但是在多租戶應用程式資料隔離的架構設計方法中，卻是以資源使用率低的獨立資料表方式最為容易開發，為了提高資源利用率並降低多租戶應用. 政治大立資料表 SQL 語句自動轉換成共用資料表架構的 SQL 語句，因此利用剖面導向立. 程式資料隔離架構設計的複雜度，我們盡量在不修改應用程式情況下，提供將獨. ‧ 國. 學. 語言(Aspect-Oriented Programming, AOP)可以快速的達到此目的。本章節一開始將會介紹剖面導向語言的概念；接著會介紹我們在設計、開發工具時用到的相關. ‧. 技術，內容包含 2.2 節介紹 AspectJ、2.3 節介紹 Jsqlparser、2.4 節介紹 SaaS. a 2.1 Aspect-Oriented Programming. er. io. sit. y. Nat. Multi-tenant System、2.5 節則介紹 SaaS 多租戶系統的資料綱要。. n. iv l C n h需e求n，g軟c體h系i 統U的設計會包含許多的考量為了滿足整體系統目標的特定（concern）。有人認為必頇將這些特定需求功能分離（separation of concerns, SOC），也就是所謂模組化。而設計一套軟體系統，最重要的就是它的主要功能，不管是早期的程序性程式設計（Procedural programming）或物件導向程式設計（object-oriented programming）都是為此目標而發展的。而横跨系統多個模組中的共通功能（crosscutting concern）就是希望能交由剖面導向語言(Aspect-Oriented Programming, AOP)處理。剖面導向語言的概念是於 1997 年所提出，一提出此概念立即引起程式語言與軟體工程方面的學者專家重視及迴響，之後，便陸續發佈許多的相關研究。目. 12.

(15) 前有不少支援 AOP 的語言及工具，其中又以 Xerox Palo Alto 實驗室開發出以 Java 為基礎的 AOP 程式語言“AspectJ”最盛行。剖面導向語言是一種新的程式機制。舉例來說，在一般應用程式當中除了業務面的功能性需求外，經常需要做許多授權檢查與 Log 紀錄等等的非功能性需求，而這些需求在實作上往往都會分散在應用程式的各個功能模組當中，造成不易集中維護等等的缺點。現有的程式機制(programming mechanism) 在實作這方面需求時並無法提供有效的封裝與模組化的支援，所以必頇將相同的程式碼重複於功能模組當中。雖然目前的程式機制可以將程式碼集中開發及維運，但還是必頇在引用該段程式碼的地方加入引用. 政治大. 的程式碼。例如，下列程式碼(圖 2.1)，每個功能皆需單獨加入 Log 紀錄的程式碼。. 立. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. Ch. engchi. 圖 2.1. i n U. v. 重覆出現的程式碼片段. 13.

(16) 剖面導向程式設計就因為此種需求而產生，此種設計方法可先將重覆的程式碼移出，並且集中在另一個模式，之後再透過程式的整合機制去將兩個模組做結合，並完成實作。即便將來需要變更 Log 紀錄模組部份，也只需要去修改 Log 紀錄模組本身程式碼，不需要變更所有與 Log 紀錄相關的所有程式碼。. 政治大. 立. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. Ch. 圖 2.2. engchi. i n U. v. 利用 AOP 織入重覆程式碼片段. 想要了解 AOP 的精神，則必需要先了解三項議題：一、考量的分離（separation of concerns）：考量如果有做良好的模組化會使程式開發的效率提升，並且可以更容易的去修改模組程式。二、横跨（crosscutting）：程式設計與撰寫時常常會造成系統中反覆出現類似的程式碼及不同需求的程式碼夾雜不清，可將程式碼嵌入到應用系統的各個模組中，具有橫跨的特性。 14.

(17) 三、相依性反向（dependencies inversion）：要依賴於抽象，而不要依賴於具體類別。當名稱愈抽象，則受到變動而影響的機會就會愈小。進一步使用 AspectJ 來說明 AOP 的基本想法。AspectJ 程式分為 class 模組與 aspect 模組，Aspect 模組主要是定義 pointcuts 與 advice。而 Pointcuts 是規範 aspect 與應用系統介接的 join points，即 pointcut 是 join points 的集合；advice 則是定義由 join points 定義的 point 所要執行的程式碼。每個 advice 都需要一個 pointcut，二者共同界定應用程式在何時以及如何實現橫跨性的需求。AOP 最後會再透過織入(weaving)的機制將 aspect 的程式碼整合到功能模組中，而不會更動到原有的軟體架構。AOP 的架構如下：. 立. 政治大. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. 圖 2.3. Ch. engchi. i n U. v. 以 AOP 方式組合成應用系統的各類模組. 而 AOP 在編譯時，利用 AOP 本身的編譯工具做為執行工具。且編譯時並非一定需要原始程式碼檔案，編譯好的.class 一樣可以進行 weaving Rules 的動作。如圖 2.4 所示，將 AOP Complier 的流程皆表示出來。. 15.

(18) 政治大. 圖 2.4. 學. ‧ 國. 立. AOP 編譯程式流程. AOP 的優點有下列幾項：一、單一模組的責任更簡單. ‧. 二、可以做更好的模組化. y. Nat. sit. 三、使系統更容易發展. n. al. er. io. 四、使程式設計上更具彈性五、使程式的重用率更高. Ch. engchi. 六、加快開發速度並滅少實作成本. i n U. v. 2.2 AspectJ AspectJ 是由 Java 語言延伸的 AOP 語言。每一個有效的 Java 程式就是一個有效的 AspectJ 程式。AspectJ 的編譯器產生出符合 Java byte-code 規範，可執行在 Java 的 virtual machine (VM)。它具備所有的 Java 優點，也方便 Java 程式設計師去學習 AspectJ。在 AspectJ，經由編譯織入(weaving)規則的實作稱為橫跨(crosscutting)。AspectJ 定義兩種問題的 crosscutting：靜態橫跨(Static crosscutting)和動態橫跨(Dynamic 16.

(19) crosscutting)。動態橫跨動態橫跨是織入新的行為至執行程序之中。大多數 AspectJ 橫跨都是動態的。動態橫跨的擴展，甚至取代了核心程序執行流程，橫跨模組，從而改變系統的行為。靜態橫跨靜態橫跨是修改系統靜態結構(如：類別 Class、介面 Interface 和剖面 Aspect) 的織入。本身並不改變系統的執行行為。靜態橫跨最重要的功能是支援動態橫跨. 政治大和行為。這可被使用在動態橫跨的行為上。另外，靜態橫跨可以被使用在宣告編立. 的實作。例如：想要新增變數或方法至類別或介面中，為了確定特定類別的狀態. 譯時的警告和錯誤的橫跨多個模組。. ‧ 國. 學. AspectJ 延伸使用下面的構造指定織入規則程序：. ‧. 一、Join Point. y. Nat. Join Point 是應用程式在執行時加入流程的確認點 (identifiable. er. io. sit. point)。具體來說，就是 Advice 在應用程式中被呼叫執行的點，這個點可能是某個方法被呼叫之前或之後（或兩者都有），或是某個例外發生. n. al. 的時候。二、Pointcut. Ch. engchi. i n U. v. Pointcut 是一種定義，可以藉由這個定義指定某個 Aspect 在哪些 Joinpoint 時被應用至應用程式之上。可以在定義檔中撰寫 Pointcut，當中說明了哪些 Aspect 要應用至應用程式中的哪些 Joinpoint。三、Advice Aspect 的具體實作稱之為 Advice，以儲存存取紀錄為例，Advice 中會包括真正的儲存存取紀錄程式碼是如何實作的，Advice 中包括了 Cross-cutting concerns 的行為或所要提供的服務。四、Introduction 17.

(20) Introduction 可以對現存的類別增加行為，而不用修改該類別的程式，也就是說，可以為某個已撰寫、編譯完成的類別，在執行時期動態加入一些方法或行為，而不用修改或新增任何一行程式碼。五、Compile-time declaration Complie-time declearation 是靜態的 crosscut 通知，可以增加在執行時期偵測到錯誤或警告時，就通知使用何種 pattern。六、Aspect 將散落於各個物件之中的 Cross-cutting concerns 收集起來，設計各. 政治大 Aspect 從程式流程中獨立出來，在需要該服務的時候，縫合（Weave）立個獨立可重用的物件，這些物件稱之為 Aspect。在 AOP 中著重於將. 應用程式中的可重用元件不用作任何的修改。. 學. ‧ 國. 至應用程式之上，不需要服務的時候，也可以馬上從應用程式中脫離，. ‧. 另一方面，對應用程式中可重用的元件來說， AOP 不用知道處理. y. Nat. 提供服務的物件之存在，具體的說，與服務相關的 API 不會出現在可重. er. io. sit. 用的應用程式元件之中，因而可提高這些元件的重用性，您可以將這些元件應用至其它的應用程式之中，而不會因為加入了某些服務而與目前. n. al. Ch. 的應用程式框架發生耦合。. engchi. 圖 2.5. i n U. AspectJ 的編譯流程. 18. v.

(21) 2.3 Jsqlparser JsqlParser 用來剖析 SQL 語句並將句子結構轉換成階層式的 java 類別，可以使用 Visitor pattern 來操作及瀏覽這些階層式的類別。JsqlParser 是用 JavaCC (Java Compiler Compiler) Java 剖析產生器所建立出來，為產生階層式的 Java 類別，作者修改了從 Guido Draheim's site 得到的 SQL 文法搭配 JavaCC 的方式建構出 Jsqlparser。本研究就是利用 Jsqlparser 來完成 SQL 語法結構剖析與轉換的工作。首先. 政治大提供的 SQL 語句剖析功能，協助我們將 SQL 語句關鍵字或變數值識別出來，搭立. 將 jsqlparser.jar 引入到 java 的 classpath 下，我們就可以在編譯時使用到 Jsqlparser. 配 SQL 語句轉換機制以重組方式將關鍵資料表改寫達到自動轉換 SQL 語法的目. ‧. ‧ 國. 學. 的(圖 2.6)。. n. er. io. sit. y. Nat. al. Ch. engchi. i n U. v. 圖 2.6 SQL 語句剖析與自動轉換概念圖. 2.4 SaaS Multi-tenant System 在 SaaS 的雲端平台架構中，多租戶技術指的是大量的用戶共同使用、分享一組軟硬體資源。在多租戶技術中，常見的資料隔離的架構設計共有 3 種(圖 2.7)，(1). 19.

(22) Separate Database 、 (2) Shared Database, Separate Schemas 、 (3) Shared Database, Shared Schema，以下分別說明這 3 種資料隔離架構的特性：. 立. ‧ 國. 學圖 2.7 資料隔離與資源共享關係圖. ‧. (Berthold Reinwald, 2010). sit. y. Nat. n. al. er. Separate Database. io. 1.. 政治大. v. 對於每一個租戶(tenant)，其所存取的資料庫是分離(Separate)的。即使運算. Ch. engchi. i n U. 資源或程式碼可能是多個租戶共用，但在資料的儲存或取得上，是完全隔離 (isolate)的狀態。這種架構可以根據用戶需求容易的來擴展資料結構，而備份和回復資料也相對容易。但每一租戶需提供一個完整的資料庫，因此維護成本高，另外硬體成本較其他兩種架構高。. 20.

(23) 圖 2.8 Separate Database 圖 (Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006). 2.. Shared Database, Separate Schemas. 政治大存在一個屬於自己的資要綱要(Schema)中，這個資料綱要裡面包含了數個資料立這種架構是多個租戶(Multi-Tenant)共用一個資料庫，但每個租戶的資料會. ‧ 國. 學. 表。這種架構在硬體成本上相較於 separate database 來的低，每一台伺服器可以承受的租戶也比較多。但資料庫出問題時回復較難，當使用 Separate Schemas 的. ‧. 架構且需要復原整個資料庫時，同時也會將其他租戶的 schemas 都覆蓋掉。. n. er. io. sit. y. Nat. al. Ch. engchi. i n U. v. 圖 2.9 Shared Database, Separate Schemas 圖 (Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006). 3.. Shared Database, Shared Schema 所有的租戶使用相同的資料庫，且同時用同一組資料表來維護其資料，在 21.

(24) 這種架構中，會用 tenant ID 來當作每個租戶的識別。使用這種架構讓每一台伺服器可以負擔的租戶數量最多、單位成本最低且資源利用率最高。由於所有的租戶都共用同一個資料庫，在安全維護和資料隔離程度上最差，當單一租戶的資料需要復原時，必需在資料表中尋找所有相同的 tenant ID，數量多時就會造成其他租戶的效能降低。. 政治大. 圖 2.10 Shared Database, Share Schemas 圖. 立. (Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006). ‧ 國. 學 ‧. 共用資料庫與資料表的設計方式比起獨立資料庫或資料表的設計方式需要更多的人力成本來開發，因為共用資料表方式的設計方式其資料庫關聯性更複. y. Nat. io. sit. 雜。雖然共用資料表方式初期開發成本較高，但共享資源的方法讓每台伺服器可. n. al. er. 以服務更多租戶，因此長期來看，共享資源的總成本反而較低(圖 2.11.)。. Ch. engchi. i n U. v. 圖 2.11 隔離方式與共享方式時間成本比較圖 (Frederick Chong, Gianpaolo Carraro, and Roger Wolter, 2006). 22.

(25) 2.5 SaaS 多租戶系統的資料綱要提供應用系統多租戶服務的運作方式可以是一租戶一資料庫或是所有的租戶都共用一資料庫，第一種完全沒有資源共享，第二種則缺乏擴充性，所有租戶都必頇使用同一種資料綱要（data schema），不易提供租戶客製化的服務。兩者之間，應該有一些比較彈性的作法存在。本節將介紹 5 種介於二者之間的資料綱要設計技巧。 1.. Private Table 這種方式沒有共用資料表，每個租戶共用資料庫但卻有各自的資料表使. 治政用，以這種方式開發多租戶應用系統最為容易，因為的資料表的關聯性簡單，雖大立然可以輕易達到客製化的目的，但租戶數多時將會有大量的資料表存在，無法有 ‧ 國. 學. 效達到資源共享的目的，本研究就是以這種資料綱要為基礎。. ‧. n. er. io. sit. y. Nat. al. Ch. engchi. i n U. v. 圖 2.12 Private table layout (Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,). 2.. Extension Table 這個作法是將租戶共有的欄位抽出放置於一個共用表格，另外以 TenantID. 欄位與 Row 欄位來區分不同租戶的資料，個別租戶客製化的資料欄位則分別寫入各自的表格中。. 23.

(26) 圖 2.13 Extension table layout (Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,). 3.. Universal Table. 政治大方式來存放資料，使用 TenantID 欄位與 Table 欄位來區分不同租戶的資料，因為立. 將所有租戶的資料共同放置於一個包含大量欄位的資料表，採稀疏矩陣的. 不同租戶間的資料差異會造成部份的欄位會有浪費的情況發生(造成許多空矩. ‧. ‧ 國. 學. 陣)。. n. er. io. sit. y. Nat. al. Ch. engchi. i n U. v. 圖 2.14 Universal table layout. (Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,). 4.. Pivot Table 將所有租戶的資料依資料型別分別存放在不同的表格，即同一型別的資料. 放置於同一個資料庫表格，並以 TenantID 欄位、Table 欄位、Col 欄位以及 Row 欄位來區分不同租戶的資料，租戶資料則放置於其餘的欄位。. 24.

(27) 圖 2.15 Pivot table layout (Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,). 5. Chunk Table. 政治大料放置於同一個資料表，但它改良 Universal Table，加入了 Pivot Table 資料型別立. 這種設計方式是改良 Pivot Table 與 Universal Table。首先，將所有租戶的資. 的概念，將租戶資料依型別存放於不同的欄位內，不同於 Pivot table 一型別一表. ‧ 國. 學. 格的作法，改以一組固定數量的型別欄位稱之為 chunk，。以 TenantID 欄位、Table. ‧. 欄位、ChunkNo 欄位以及 Row 欄位來區分不同租戶的資料。. sit. y. Nat. 相對於 Pivot Table 的作法有下列優點：（1）減少詮釋欄位與實際資料的比. io. er. 例。（2）只使用一個表格，所以方便還原建立各租戶的原始資料。另外相對於 Universal Table 的優點：（1）可以透過詮釋欄位來建立索引。（2）減少欄位的數. al. n. v i n Ch 量。（3）加入資料型別的概念可減少資料欄位浪費的現象。基於以上的優點，本 engchi U. 研究將著重於討論如何將 Private Table 架構的多租戶系統在盡量不修改程式的方式下轉換成 Chunk Table 資料綱要架構。. 圖 2.16 Chunk table layout (Stefan Aulbach, Torsten Grust, Dean Jacobs, Alfons Kemper, Jan Rittinger, 2008,) 25.

(28) 第三章系統設計與架構為提高 SaaS 多租戶應用程式的設備資源使用率，降低程式因資料庫架構修改所造成的工作負擔，本系統利用 AOP 特點來進行設計規劃，在不修改應用程式前提下，將應用程式 JDBC 資料庫連線從中攔截，透過 Jsqlparser 將 SQL 語法結構. 政治大而不需修改應用程式的目的。本章節主要針對系統架構來進行介紹，包含簡介系立. 剖析，再依現有的資料庫綱要重新改寫 SQL 語法，以達到改變資料庫綱要結構. 統設計架構、系統設計方法及如何使用 AOP 特點來達成本系統的設計理念，都. ‧ 國. 學. 將進一步說明。. ‧ y. Nat. 3.1 系統設計理念. io. sit. 隨著雲端運算的興起，SaaS 多租戶應用系統的服務也將是未來的趨勢，在開發. n. al. er. 多租戶應用程式時，需要考量到租戶系統客製化，也需要達到資源共享的目的，. Ch. i n U. v. 才能在有限的資源設備下服務更多的租戶。常見的 SaaS 多租戶應用系統為了達. engchi. 到上述的開發目的，資料綱要會以 Private Table 方式設計，除了方便隔離每個租戶間的資料外，還可達到租戶系統客製化的目的。但這種資料綱要的設計方式當租戶數量多時，會產生大量的資料表，無法達到設備資源共享也限制了服務租戶的數量。為了改善 Private Table 資料綱要架構的 SaaS 多租戶應用系統，本研究取 Chunk Table 可由租戶客製化且共用資料表的優點，討論如何將多租戶應用系統資料綱要修改為 Chunk Table 的設計方式，期望能達到修改資料綱要但不需修改程式的目的來改善 Private Table 資料綱要架構的 SaaS 多租戶應用。本研究包含二階段研究。第一階段研究目標為整理歸納將 Private Table 資料綱要轉換為 Chunk Table 資料綱要的方法，提出系統化的步驟處理 DDL (Data 26.

(29) Definition Language)與 DML (Data Manipulation Language) 陳述式 SQL 語法轉換的作業，讓本研究提供的工具可以達到自動 SQL 語法重新改寫的目的。搭配 Jsqlparser 語法剖析工具協助我們更精確的將 SQL 語法分類，再透過系統化的轉換步驟重組 SQL 語法完成任務。本研究第二階段研究目的利用 AOP 機制，將具有 SQL 語法自動重新改寫的 Aspect 加至各資料庫廠商提供的 JDBC 驅動程式，此做法的優點在於不需修改應用程式，只需將原廠 JDBC 驅動程式加上 SQL 語法改寫的 Aspect 重新編譯後，在應用系統中置換，即可將資料綱要設計方式修改而不用修改多租戶應用系統，. 政治大需再將 JDBC 驅動程式置換回資料庫原廠提供的 JDBC 驅動程式，方便使用。立. 方便程式設計人員使用。當多租戶應用程式要將資料綱要修改回來的時候，亦只. ‧ 國. 學. 3.2 系統設計考量. ‧. 本研究主要的設計以 SaaS 多租戶應用系統的資料隔離性、可客製化能力與設備資源共享等三大部份進行考量。並且在系統實作時，會一一針對系統設計考量進. n. al. er. io. sit. y. Nat. 行開發。. 3.2.1. 資料隔離性. Ch. engchi. i n U. v. SaaS 多租戶應用系統是指大量租戶在同一台設備上共同使用一套系統，並且共用資料庫，以何種方式能夠達到租戶間資料獨立且相互隔離是我們討論的重點，而一租戶一資料表的方式雖然可以達到目的，如租戶數量增加時，將會產生大量重複資料綱要的資料表，會花費許多設備資源成本。若能讓所有租戶都使用同一個資料表，透過表格裡的資料描述欄位“Metadata”也可讓租戶間達到資要隔離的目的。由於使用額外的資訊來描述資料的租戶屬性，並不影響原有資料儲存的運作。本章 3.4 會有更詳細介紹。. 27.

(30) 3.2.2. 可客製化能力由於 SaaS 多租戶應用系統在實際執行時，內部租戶會有客製化的需求。例如：租戶在設計資料欄位時，會依照實際的商業需求來建立不同的資料欄位。如果使用獨立資料表的方式可以輕易的達到這個功能，對共用資料表的設計方式我們則頇仰賴資料綱要對應表(Schema-Mapping Table)來達到可客製化的目的。本研究主要目的在幫助程式開發人員減少應用系統因資料綱要修改而造成的額外工作負擔，而非幫助資料綱要修改時資料轉換的作業，故只針對 Private Table 資料綱要與 Chunk Table 資料綱要相互對應轉換的問題。本章 3.4.1 Schema-Mapping. 政治大. Table 檔案設計，將針對此考量進行實作。. 立. 3.2.3. 設備資源共享. ‧ 國. 學. 在開發 SaaS 多租戶應用系統時，最容易達到上述需求也最容易理解開發的. ‧. 資料綱要設計方式就是 Private Table 架構，因為 Private Table 是分配每個租戶有. y. Nat. 各自的資料表，所有商業邏輯對應到資料表上也很直覺，故利於系統開發。但租. er. io. sit. 戶數量如量大時，會導致重複的資料表大量存在於資料庫的問題，大大降低了資料庫的效能也減少了設備資源的利用率。若將資料綱要設計方式改為 Chunk. al. n. v i n Table 架構，不但保留可客製化與資料隔離的優點，更可增加設備資源的使用率， Ch engchi U 改善 Private Table 資料綱要設計方式的缺點。本章 3.4.2 Private Table 與 Chunk. Table 資料綱要 SQL 語法轉換步驟，針對此考量進行實作。. 當系統資料綱要做了改變時，應用程式理論上也需要搭配資料綱要的架構進行修改。本研究提供可自動轉換 SQL 語法的工具供系統開發人員使用，在不需修該應用程式的情況下直接使用，不但可以保留原有程式的商業邏輯，也降低程式開發人員額外的工作負擔，並降低因資料綱要改變造成程式邏輯錯誤而將應用系統改壞的風險。本章 3.4.4 Access SQL 改寫 Aspect 設計，皆有針對此考進行實作。 28.

(31) 3.3 系統流程本研究適用於透過 JDBC 驅動程式存取資料庫的 Java 應用程式，此 Java 應用程式利用 JDBC API 來進行資料庫存取，利用統一的介面達到寫一個 Java 程式而可以連接所有資料庫的目的。本研究共包含二部分研究目標，目的皆為減少程式設計人員因資料綱要修改而造成的工作負擔，故所開發的工具，皆為實現及輔助該目的，系統架構與運作模式是透過 JDBC 攔截並加上 SQL 語法改寫機制的 Aspect，編譯之後成為含有 SQL 語法自動改寫的 JDBC 介面，使用時只要取代系統中原本的 JDBC 驅動程. 治政式，只要資料庫改為共享資料表的架構之後，我們執行共享資料表版本的 SaaS 大立多租戶應用程式實際上還是執行租戶間獨立資料表的 SaaS 多租戶應用程式，就 ‧ 國. 學. 可以達到共享資源共享資要表的目的。. ‧. n. er. io. sit. y. Nat. al. Ch. engchi. i n U. v. 圖 3.1 SQL 語句轉換機制運作模式. 本研究分二部分研究目標，第一部分研究目標為整理歸納 SQL 語法如何由 Private Table 資料綱要設計方法轉換成 Chunk Table 資料綱要的系統化步驟與 Schema-Mapping table 相互搭配，後面章節將分為 DDL 與 DML 兩個部分來討論，將 SQL 句子經過 SQL Parser 進行分類，利用語法剖析工具 Jsqlparser 把分類. 29.

(32) 過的 SQL 句子類別透過語句改寫機制的轉換規則，進行重組句子達到轉換的目的。第二部分研究架構如圖 3.2 所示，運用 AspectJ 從中攔截系統程式的 SQL 語法，將語法剖析工具產生重新改寫的 SQL 取代原本的語法交由系統執行，更進一步達到不用修改應用系統的目的。. 立. 政治大. ‧. ‧ 國. 學. n. al. er. io. sit. y. Nat. 圖 3.2 系統架構與運作模式. Ch. engchi. i n U. v. 由於 Java API 己定義 JDBC 的 Interface，故利用該介面開發 AspectJ 程式，使其具備 SQL 語法自動轉換的功能，將實作的 SQL Rewriting Aspect 配合資料庫原廠的驅動程式，利用 AspectJ 的編譯器將原廠的驅動程式重新編譯成新的 JDBC driver。系統先嘗詴實作 AspectJ，該 AspectJ 可依據語法剖析器設定的規則來決定如何重組。此目的(1)可適用在任何的 JDK 版本(2)設計人員不需學習撰寫 AspectJ 程式(3)依實際資料綱要架構設計語句轉換條件。. 30.

(33) 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)。. 政治大. 立. ‧. ‧ 國. 學 Schema-Mapping Table. n. er. io. al. sit. y. Nat 圖 3.3. Ch. engchi. i n U. v. 資料綱要對應表(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:欄位資料型態. 31.

(34) 立. 圖 3.4. 政治大. Schema-Mapping Table 建立步驟虛擬碼. ‧ 國. 學. 系統化歸納出建立 Schema-mapping table 的步驟後，進一步將系統化步驟轉. ‧. 為程式虛擬碼(圖 3.4)。本研究設計 Schema-mapping table 的格式有 table name +. sit. y. Nat. column name、table 編號、tenant id、chunk number 與 column type，5 個欄位，在. n. al. er. io. 執行 private schema 轉換為 schema-mapping table 動作時，tenant id 可以是由登入. v. 的 tenant session 方式取得，接著程式執行”SHOW TABLE”指令可以取得所有資. Ch. engchi. i n U. 料表，利用一個迴圈執行”DESCRIBE 資料表名稱”指令則可以取得每個資料表的屬性欄位，利用一個迴圈把資料表裡每個欄位的欄位名稱存到 column、資料型別存到 colType 及依資料行別計算出 chunk number 存到 chunkno，就可以將所有資料表的資料欄位建立到 schema-mapping table，本研究用文字檔(圖 3.5)來存放 schema-mapping table 方便維運及查找。. 32.

(35) 圖 3.5. 學. ‧ 國. 立. 政治大 Schema-Mapping Table 存放檔案格式. ‧. 資料綱要對應表建立的目的就是協助不同資料綱要設計方法間相互對應，以. y. Nat. sit. 本研究為例，建立此對應表之後雖然 Private Table 修改為 Chunk Table 架構了，. n. al. er. io. 只要透過對應表查找，就能快速的取出某欄位在 2 個資料表間的實際欄位，對於. i n U. v. 本研究以 SQL 語法自動轉換的研究目的有很大的幫助。. 3.4.2. Ch. engchi. Data Definition Language 設計方法. 本研究以 Private Table 資料綱要設計方法的 SaaS 多租戶應用系統為基礎，以修改為 Chunk Table 資料綱要設計方法的程式架構為目的。本節將討論 DDL (Data Definition Language)的設計方法。 DDL 是用以執行管理工作並強制執行影響資料庫的商業規則，常見的陳述式有 CREATE、ALTER、DROP 和其他執行類似 DDL 作業的預存程序，它們適用於整個資料庫或整個伺服器之單一類型的所有命令。本研究助要著重在減少. 33.

(36) 應用系統程式開發的額外負擔，因此對 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 的欄位全部移. io. er. Data Manipulation Language 設計方法. sit. y. Nat. 3.4.3. ‧. 除，只要尋找欄位名稱包含 table name 的就可輕易完成移除欄位的動作。. 本節針對 DML (Data Manipulation Language)的部分，討論常用語法的系統化. al. n. v i n 語句轉換步驟，以各種使用情境的案例來證明語句轉換機制的適用範圍，DML Ch engchi U. 強制執行商業規則，以及當資料表或檢視中的資料修改時協助擴充資料的完整性，操作的陳述式通常是 SELECT、INSERT、UPDATE 及 DELETE，後面章節會針對各種操作語法轉換方式做系統化步驟歸納。. 1.. SELECT 語法轉換步驟 SELECT 在 SQL 語法中最常用的方式是將資料從資料庫中的表格內選出。. 最基本的語法架構： SELECT "欄位名" FROM "表格名". 34.

(37) 從這句基本語法中，我們馬上可以看到兩個關鍵字，從 (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 語. 政治大假設 SELECT 查詢範例 Q1 為 SELECT name FROM tenant31 WHERE accountid= 1 立句轉換成 chunk table 架構的 SQL 語句：. 步驟 1.要先取出資料表及欄位名稱，資料表 tenant31；欄位 accountid, name。. ‧ 國. 學. 步驟 2.將取得的資料表欄位查找 schema-mapping table(圖 3.6)比對出位於 chunk. ‧. table 上相對應欄位的 metadata 值，tenant31.accountid：table = 0、tenantid = 31、. n. al. er. io. sit. y. Nat. chunkno = 0；tenant31. name：table = 0、tenantid = 31、chunkno = 0. 圖 3.6. Ch. engchi. i n U. v. Tenant31 資料表的 Schema-Mapping Table. 步驟 3. 生成過濾查詢(filter query)語句： SELECT `str1` as `name`,ìnt1` as àccountid` FROM chunktable. 35.

(38) WHERE tenantid=31 AND `table`=0 AND `chunkno`=0 步驟 4. 置換原本語句裡的查詢參考(query reference)資料表如下： SELECT name FROM ( SELECT `str1` as `name`, ìnt1` as àccountid` 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 工. 政治大 node 並取出要置換的 node，最後完成 SQL 語句轉換作業。立. 具，它協助我們將 SQL 轉換步驟用 SQL parser tree 方式處理，收集必要的欄位. 在第 4 章系統實作及展示裡，SELECT 操作語法的轉換會依下列幾種常見的. ‧ 國. 學. 陳述式來做討論：多欄位查詢、SELECE *查詢、多 Table 查詢、JOIN 陳述式及. ‧. 多對多關聯 SELECT 查詢來驗證轉換步驟是否也可應用到上述情境。. er. io. sit. y. Nat INSERT 語法轉換步驟. al. v i n 前一節說明如何把資料由表格中取出，如果是要將資料寫入到這些表格這就 Ch engchi U n. 2.. 是要使用 INSERT 語法。我們這邊介紹一次輸入一筆資料的語法如下：. INSERT INTO "表格名" ("欄位 1", "欄位 2", ...) VALUES ("值 1", "值 2", ...) 從基本語法結構中，我們可以看到要寫入資料的每個欄位值(VALUES)內容是什麼("值 1", "值 2", ...)，這些資料要寫入到(INSERT INTO)哪個表格。新增語法 INSERT 從 private table 架構轉換為 chunk table 架構步驟如下： 1.. 收集所有相關欄位的 chunks、mete-data. 2.. 將每個 Insert 的 row 拆解成以 chunk 為一組. 3.. 將 insert 語法依每個 chunk 執行新增. 36.

(39) 轉換步驟的程式虛擬碼如圖 3.7. INSERT 轉換步驟虛擬碼，與 SELECT 步驟 1 一樣先收集語法中所有的欄位名稱、資料表與輸入值，接下來將前面收集到的欄位名稱搭配 schema-mapping table 做依照 chunk number 排序(小到大)的動作。. 政治大. 立. sit. INSERT 轉換步驟虛擬碼. n. er. io. al. y. ‧. ‧ 國. 學. Nat. 圖 3.7. i n U. v. 下一步把排序過的欄位名稱 List 放到迴圈逐一檢查，迴圈裡面的一個關鍵點. Ch. engchi. 就是要比對目前這個欄位的 chunk number 與上一個欄位的 chunk number 是否相同，目的是讓相同 chunk number 的欄位資料放在同一句 INSERT 語句中，比對結果若不相同就代表是新的一塊 chunk 所以前面一組相同 chunk 的資料就可以執行 INSERT 動作先寫入到 chunk table 中；比對結果若是相同表示與前面一個欄位是屬於同一塊 chunk，此時就先將欄位名稱與資料值記錄下來等待換到下一塊 chunk 時在進行寫入的動作，可以避免同樣一塊 chunk 的值被分開寫入到不同的資料列。寫入資料時所需要的 metadata 值(table、tenantid、欄位資料型別)都可透過 schema-mapping table 查找出來，其中 row 的部分是需要另外計算，在程式虛擬 37.

(40) 碼中所使用的方法是用 SELECT 在 chunk table 資料表中找出 WHERE tenantid 與 table 屬於該 INSERT 資料表的 row 欄位內容裡的最大值，把這個最大值加 1 就是目前這一筆資料的 row 值，最後就完成 INSERT 語句轉換作業。在第 4 章系統實作及展示裡，INSERT 操作語法的轉換會以一次輸入一筆資料的語法為例，實際將轉換過程展示說明。一次輸入多筆的語法： INSERT INTO "表格名" ("欄位 1", "欄位 2", ...) VALUES ("值 1", "值 2", ...), ("值 1", "值 2", ...), ("值 1", "值 2", ...); 差別只是在 VALUE 的分割與 row 值的增加計算，其他轉換過程與一次輸入一筆. 政治大. 資料的語法並無顯著差異，因此在第 4 章系統實作及展示裡就沒有再另外說明這個使用案例。. ‧ 國. 學. 3.. 立. Update (Delete)語法轉換. y. Nat. 需要用到 UPDATE 指令。這個指令的語法是：. ‧. 在資料維護的過程中有時候會需要修改表格中的資料。在這個時候，我們就. er. io. sit. UPDATE "表格名" SET "欄位 1" ="設定值 1" WHERE “條件” 從基本語法結構中，我們可以看到在甚麼條件(WHERE)下的哪個欄位的內容要. al. n. v i n 設定(SET)成什麼("設定值 1")，這些資料要修改到(UPDATE)哪個表格。從 private Ch engchi U. 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 語法轉換的 38.

(41) 不足，實際運作的程式虛擬碼如圖 3.8，所轉換的語句範例為：update tenant31_score set score=100, comment=„AA‟ where accountid=1。. 立. 政治大. er. io. sit. y. UPDATE 轉換步驟虛擬碼. ‧. ‧ 國. 學. Nat. 圖 3.8. 程式執行時首先將 UPDATE 語法轉換成 SELECT 語法，條件式就取 UPDATE. al. n. v i n C h 欄位就取 UPDATE 語句裡 WHERE 的部分，而 SELECT 語句裡 SET 的部分，經 engchi U 過第一次轉換可以得到 private table 結構的查詢語法，之後將查詢語法轉換為 chunk table 結構的查詢語法，轉換的過程中同時將所有相關資料表及欄位的 table、tenantid、chunkno、columnType 和 columnNamae 欄位記錄下來。轉換查詢語法為 chunk table 架構之後，另外會再附加一個查詢欄位”row”，接著將查詢結果的 row 欄位進入到迴圈，迴圈內再進入到一個包含所有修改欄位的巢狀迴圈並在這裡執行 chunk table 的 UPDATE 動作，由 row 欄位再搭配所有修改欄位的巢狀迴圈方式就可以處理修改多筆資料的狀況。如程式虛擬碼邏輯所示，將 UPDATE 轉換為 SELECT 方式找出被影響欄位的 row 值，再搭配迴圈更新相關欄位就完成了 chunk table 的 UPDATE 語法轉換。 39.

(42) DELETE 的執行步驟與 UPDATE 完全相同，差別只在於最後一步實際執行 chunk table UPDATE 的語句修改為執行 DELETE 語句就能夠達到刪除資料列的目的。. Access SQL 改寫 Aspect 設計. 3.4.4. 本研究利用 AOP 特性將具有 SQL 語句轉換機制功能之 AspectJ 程式，織入 JDBC 驅動程式中，為了減少程式開發人員因為 DB schema 架構改變而造成額外程式修改的負擔，故實作好具 SQL 語句自動轉換之 Aspect。. 政治大由於 AspectJ 主要需先定義立 pointcuts 與 advice。pointcuts 是規範 Aspect 與應. 1.. 定義 pointcuts. ‧ 國. 學. 用系統介接的 join points，即 pointcut 是 join points 的集合。本研究利用 JDBC API 統一定義的特性，首先定義 package(java.sql)的 pointcut 如圖 3.9：. ‧ sit. y. Nat. n. al. er. io. pointcut dbAccess(String sqlStmt) : (call(* java.sql.Statement.execute*(String))) && !within(SQLconn) && args(sqlStmt);. Ch. engchi. i n U. v. 圖 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 參 40.

(43) 數帶入，本研究主要是協助 JDBC 驅動程式的 SQL 語句自動轉換機制，只要符合 JDBC 執行 SQL 命令的 pointcut，就會被攔截下來交由 SQL 語句自動轉換機制處理，轉換後的 SQL 語句再交由原來的 process 執行，不會影響到程式輸出的結果。. 2.. 定義 advice. advice 則是定義由 join points 所定義的 point 要執行的程式碼。每個 advice 都需要一個 pointcut，二者共同界定應用程式在何時以及如何實現橫跨性的需求。為了達到自動轉換 SQL 語句的目的，本研究在此部份依 sqlStmt 執行命令的. 政治大. 關鍵字，決定要呼叫的 SQL 語法轉換類別。針對 SQL 語法轉換的 advice 虛擬碼，. 立. 如圖 3.10 下：. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. Ch. engchi. i n U. v. 圖 3.10 依不同 SQL 語法的 advice 虛擬碼. 符合已定義 SQL 語句自動轉換類型關鍵字的 SQL 命令即執行此 advice，此 41.

(44) advice 使用 around 方式，會將 SQL 執行命令以參數 s 帶入並且指定傳出值為 object 型態。SELECT 語法經過轉換後會交由 proceed 以新的 SQL 語句取代原本的來執行，查詢結果也會以轉換後的 SQL 語句執行結果回傳。 AOP 最後會再透過織入(weaving)的機制將 aspect 的程式碼整合到 JDBC 功能模組中，而不會更動到原有的軟體架構。. 3.4.5. 產生具 SQL 語法改寫功能之 JDBC 驅動程式. 本工具利用 3.3.1 所產生的 AspectＪ程式，利用 AspectJ 的編譯工具(AJK)來. 政治大 ajc -inpath c:\ mysql-connector-java-5.1.17-bin.jar c:\jsqlparser.jar nccu\Rewrite.aj 立進行編譯，並將其他所需程式一併加入。其執行語法如下：. -outjar nccu\ mysql-connector-java-5.1.17-bin.jar. ‧ 國. 學. -inpath ，首先指定原廠的 JDBC 驅動程式 (mysql-connector-java-5.1.17. ‧. -bin.jar)。之後將 SQL Parser 工具(jsqlparser.jar)與 3.4.3 所產生的 AspectJ 程式. y. sit. io. n. al. er. 如圖 3.11 所示。. Nat. (Rewrite.aj)一起編譯。-outjar，指定編譯完成後，產出的 jar 檔名稱。編譯架構圖，. Ch. engchi. i n U. 圖 3.11 本研究編譯流程. 42. v.

(45) 本研究產生的 JDBC 驅動程式，只需在執行應用程式的 Java VM 環境中，將原廠的 JDBC 驅動程式置換，並配合上 schema-mapping table 的設定檔，即可使用。當資料庫 schema 架構改變時本研究保留彈性，可依照不同 schema 設計不同 SQL 語句轉換機制。而系統將來需要改回原本資料庫 schema 架構時，亦可直接將 JDBC 驅動程式置換回原廠 JDBC 驅動程式。. 立. 政治大. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. Ch. engchi. 43. i n U. v.

(46) 第四章系統實作與展示本章節我們將由使用者的角度來介紹本論文設計的工具，本研究的程式皆以 Java 程式語言開發，利用 Eclipse IDE 開發環境，加速開發速度。4.1 節介紹實作語言與工具；4.2 節介紹系統實作展示，並包含二階段系統展示及實例情情境說明與分析。. 立. 政治大. ‧ 國. 學. 4.1 實作語言與工具. 在本章節當中，會介紹本研究所使用的開發語言與工具：. ‧. . AspectJ：AspectJ[9]是 Java 語言延伸的 AOP 語言。每一個有效的 Java 程式. sit. y. Nat. 就是一個有效的 AspectJ 程式。AspectJ 的編譯器產生出符合 Java byte-code. io. al. 方便 Java 程式設計師去學習 AspectJ。. v i n Ch Eclipse：Eclipse[10]是一個開放程式碼的計畫，其宗旨是希望能夠增進軟體 engchi U n. . er. 規範，可執行在 Java 的 virtual machine (VM)。它具備所有的 Java 優點，也. 開發的效率。Eclipse 是 Java IDE 工具，提供了強大的功能，可以幫助開發人員編寫、編譯與部署 Java 程式，並將版本控制和 XML 編輯等多功都融入其中。 . Java 程式語言：Java[11]是源自 Sun Microsystems 在 1991 年的 the Green Project。Java 的跨平台特性且可產生更動態的網頁功能都受到歡迎。. . Jsqlparser：Jsqlparser[12]可以方便程式設計人員在程式中加入 SQL Parser 機制，並可將 SQL 語句分類。. . Mysql 資料庫：Mysql[8] 是一個快速穩定多執行緒多使用者的資料庫伺服. 44.

(47) 器，可以與 C、C++、Java、Perl、PHP 等語言很容易的連結，可以運作在許多平台上例如： Sun Solaris 、RedHat 、Linux 、FreeBSD 、OS/2 、 Windows ...等平台，目前已經有相當多的企業單位採用。. 4.2 系統實作展示在本章節中，我們先以 private table 資料庫 schema 架構的多租戶實例來介紹本研究。之後，介紹將 JDBC 驅動程式加上 SQL 語句自動轉換功能的實作與展示，展示將資料庫 schema 架構修改成 chunk table 之後程式操作的案例。. 治政 4.2.1 實例情境說明與轉換機制大立在實作之前，系統開發人員必需先事先準備完成 private table schema 資料庫 ‧ 國. 學. 架構的多租戶應用程式，並安裝搭配 MySQL 的 JDBC 驅動程式。在本節，我們. ‧. 以 private table 多租戶應用程式為基礎，以各種 SQL 實例來說明本系統的設計，實際展示 SQL 語句轉換機制如何將 DDL (CREATE、ALTER、DROP)及 DML. y. Nat. n. al. er. io. 構的多租戶應用程式。. sit. (SELECT、INSERT、UPDATE、DELETE)的等動作轉換成 chunk table schema 架. Ch. i n U. v. 當 private table schema 架構的多租戶應用程式要修改成 chunk table schema. engchi. 架構的多租戶應用程式時，如何利用 SQL 語句轉換機制處理 DDL 及 DML 的操作動作，並幫助系統開發者減少架構改變所產生的額外程式修改負擔。下列介紹幾種執行情境。假設我們有三個租戶： Tenant 31, 32, 33，以下（圖 4.1）是他們都要使用的一個資料庫表格：學生資料。三個表格都有 AccountID 與 Name 這兩個共同欄位，但也有個別的欄位，以表達客製化的需求。. 45.

(48) Tenant31 AccountID. Name. Major. Grade. 1. 李小白. 資訊科學系. 2. 2. 黃小明. 應用數學系. 3. AccountID. Name. Age. 1. 陳小春. 21. Tenant32. Tenant33 AccountID. Name. 1. 林小萱周小芳. 圖 4.1 一租戶一表格之範例. Nat. sit. y. ‧. ‧ 國. 學. 2. 立. 政治大. io. er. 根據以上的表格設計，如果我們要查詢 Tenant 31 裡 AccountID 為 1 的 Major. al. v i n Ch 1」來完成。接著我們以此範例為基礎，介紹幾種常見的一租戶一表格使用情境。 engchi U n. 欄位資料，可用 SQL 指令「SELECT Major FROM Tenant31 WHERE AccountID =. 1. 實例情境說明與分析本節第一個部分先說明 private table 架構下，常見的 DDL 如 CREATE、 ALTER、DROP 等，SQL 語句的實際操作例子及語法作用，第二個部分再說明 DML 常見的操作語法，如 SELECT、INSERT、UPDATE、DELETE 等，SQL 語句實際操作例子及語法作用。第一部份，我們將 DDL 操作語法依下列幾種常見的陳述式來做討論：. 46.

(49) CREATE 陳述式：CREATE TABLE tenant32_student (address varchar(50), city varchar(50), country varchar(25), age integer(8))。語法執行的目的是要新增一個資料表名稱為 tenant32_student，資料表欄位包含有 address 屬於 varchar 型別、city 屬於 varchar 型別、country 屬於 varchar 型別及 age 屬於 integer 型別，在 private table schema 架構下執行的結果如圖 4.2，會協助我們建立一個新的資料表。. 學. ‧ 國. 立. 政治大. 圖 4.2 CREATE 語法執行結果. ‧. ALTER add 陳述式：ALTER table tenant32_student add project varchar(10)。. y. Nat. sit. 語法執行的目的是要在資料表 tenant32_student 新增一個欄位名稱為 project 屬於. n. al. er. io. varchar 型別的欄位，在 private table schema 架構下執行的結果如圖 4.3，會協助我們新增一個欄位。. Ch. engchi. i n U. 圖 4.3 ALTER add 語法執行結果. 47. v.

(50) ALTER modify 陳述式： ALTER table tenant32_student modify project integer(3)。語法執行的目的是要在資料表 tenant32_student 修改欄位名稱為 project 的欄位型別，更改為 Integer 型別，在 private table schema 架構下執行的結果如圖 4.4，會協助我們修改欄位型別。. 立. 政治大. ‧. ‧ 國. 學. 圖 4.4 ALTER modify 語法執行結果. ALTER change 陳述式：ALTER table tenant32_student change project proj. sit. y. Nat. varchar(50)。語法執行的目的是要在資料表 tenant32_student 修改欄位名稱為. n. al. er. io. project 的欄位名稱與型別，更改名稱為 proj 型別為 varchar，在 private table schema. v. 架構下執行的結果如圖 4.5，會協助我們修改欄位名稱與型別。. Ch. engchi. i n U. 圖 4.5 ALTER change 語法執行結果. 48.

(51) ALTER drop 陳述式：ALTER table tenant32_student drop proj。語法執行的目的是要在資料表 tenant32_student 刪除欄位名稱為 proj 的欄位，在 private table schema 架構下執行的結果如圖 4.6，會協助我們移除指定的欄位。. 政治大. 圖 4.6 ALTER drop 語法執行結果. 立. ‧ 國. 學. DROP 陳述式：DROP TABLE tenant32_student。語法執行的目的是要刪除資料表 tenant32_student 包含所有欄位名稱與型別，會協助我們移除指定的資料表。. ‧. 第二部份，我們將說明 DML 操作語法在 private table 上的實例可依下列幾. y. Nat. sit. 種常見的陳述式來做討論：. n. al. er. io. SELECT 陳述式，依照不同使用情境共提出了多欄位查詢、SELECE *查詢、. i n U. v. 多 Table 查詢、JOIN 查詢和多對多關係查詢等，幾種常用的查詢關係來做說明。. Ch. engchi. 首先，說明多欄位查詢語句： SELECT tenant31.accountid, tenant31.name, tenant31.major, tenant31.grade FROM tenant31 WHERE tenant31.accountid= 1。語法執行的目的是要查詢資料表 tenant31 裡，accountid 是 1 的 accountid、name、 major 與 grade 等，指定的欄位資料，在 private table schema 架構下執行的結果如圖 4.7，會協助我們查詢指定的資料表與欄位。. 圖 4.7 多欄位查詢語法執行結果 49.

(52) SELECE *查詢語句：select * from tenant31。語法執行的目的是要查詢資料表 tenant31 裡，所有欄位資料，在 private table schema 架構下執行的結果如圖 4.8，會協助我們查詢指定的資料表所有欄位。. 圖 4.8 SELECT *查詢語法執行結果. 立. 政治大. 多 Table 查詢語句：SELECT tenant31.major, tenant31_class.accountid FROM. ‧ 國. 學. tenant31,tenant31_class WHERE tenant31.accountid= 1。語法執行的目的是要查詢資料表 tenant31 裡 major 欄位與 tenant31_class 裡 accountid 欄位的資料，並限制. ‧. 條件是資料表 tenant31 中 accountid 欄位為 1 的資料才取出，在 private table schema. y. Nat. n. al. er. io. sit. 架構下執行的結果如圖 4.9，會協助我們查詢指定的資料表與欄位的資料。. Ch. engchi. i n U. v. 圖 4.9 多 Table 查詢語法執行結果. JOIN 查詢語句： select name, classname, room from tenant31 inner join tenant31_class ON tenant31.accountid = tenant31_class.accountid。語法執行的目的是要查詢符合指定等值連接的條件 accountid 相等的兩個資料表 tenant31 與 tenant31_class 裡 name、classname、room 欄位，並連接資料表 tenant31 與 50.

(53) tenant31_class，在 private table schema 架構下執行的結果如圖 4.10，會協助我們連接不同資料表並查詢指定的欄位資料。. 圖 4.10 JOIN 查詢語法執行結果. 政治大多對多關係查詢我們可以先建立如圖 4.11 的關聯表格，除了 Tenant31 原有立. 的資料表之外，另外又加上 tenant31_class 與 tenant31_score 資料表，如果我們今. ‧ 國. 學. 天要查詢 tenant31 學生所修過的課及每堂課的修業分數，就需要依靠. ‧. tenant31_score 做為我們的連接資料表，將另兩份資料表的主索引作為自己的外. sit. y. Nat. 部索引鍵。例如，[學生] 資料表 tenant31 和 [開課資料] 資料表 tenant31_class. io. n. al. er. 有多對多關聯性。一位學生可選修多堂課，而每堂課程可出現在許多學生的選課. Ch. engchi. i n U. 圖 4.11 多對多資料表關聯圖. 51. v.

(54) 單上。連接資料表 tenant31_score 對另外 2 個資料表都有一對多的關聯性，靠內含參照資訊即可與其他資料表發生關聯產生多對多的關係。多對多關係查詢語句： 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 。語法執行的目的是要查詢兩個資料表 tenant31 與 tenant31_class 多對多關聯的 name 、 classname 、 score 欄位，並將資料表 tenant31_score 連接資料表 tenant31 與 tenant31_class，在 private table schema 架構. 政治大. 下執行的結果如圖 4.12，會協助我們連接不同資料表並查詢指定的欄位資料。. 立. ‧. ‧ 國. 學. n. al. er. io. sit. y. Nat. 圖 4.12 多對多查詢語法執行結果. Ch. engchi. i n U. v. INSERT 陳述式：INSERT INTO tenant32 (àccountid`, `name`, àge`) VALUES (2, '林小白', 25)。語法執行的目的是要新增一筆資料列到資料表 tenant32 裡，欄位 accountid、name、age 的值分別是 2、林小白、25，在 private table schema 架構下執行的結果如圖 4.13，會協助我們查詢指定的資料表與欄位的資料。. 圖 4.13 INSERT 語法執行結果. 52.