1. NCCU Academic Hub
  2. Publications
  3. College of Informatics
  4. Theses
  5. Item

Publications-Theses

Article View/Open

Publication Export

Google ScholarTM

NCCU Library

Citation Infomation

Related Publications in TAIR

題名 基於可延伸性表格的多租戶應用程式資料綱要轉換工具
Implementing Customizable Data Schemas for Multi-tenant Applications Using Extension Table Layout
作者 李明憲
貢獻者 陳恭
李明憲
關鍵詞 雲端
多租戶
SQL語句轉換
SaaS
Multi-tenant
SQL Rewriting
日期 2012
上傳時間 2-Jan-2013 13:26:30 (UTC+8)
摘要 雲端運算環境的興起，軟體即服務(Software as a Service, SaaS)的營運模式也開始受到軟體開發商的注意，其中一項關鍵技術是支援多租戶的軟體開發。在設計多租戶應用程式時有許多需要考量的因素，包含每個租戶各自的客製化設定、資料安全性等等。本論文著重於資料層級的客製化部份，如何讓各個租戶之間可以共用資料庫，但又能提供租戶適度地更改其資料表綱要(schema)以達到客製化的需求。
為了解決資料層級客製化所面臨的問題，一種常見的作法是可延伸性表格(Extension Table Layout)。但是，在關聯式資料庫中實作可延伸性表格，軟體開發人員為了配合可延伸性表格的資料存放方式，所使用的SQL語句會變得相當複雜且易錯。因此，本研究實作一個系統工具協助軟體開發人員將SQL語句，從一租戶一資料表寫法自動轉換成以可延伸性表格邏輯表達的SQL語句，透過執行轉換後的語句來對儲存在可延伸性表格的資料進行增刪修改。
軟體開發人員在本系統工具的協助下，加入租戶識別碼(TenanId)的開發概念後，即可實際建立一個多租戶應用程式。為了評估本系統工具的效用，我們以其建置了一個多校(租戶)選課系統，並進行多項效能實驗以探討影響本系統工具效能的因素。初步的結果顯示，本系統工具可以協助軟體開發人員，以較低的代價滿足資料層級客製化應用的需求。
Software as a service (SaaS) is an emerging service model of cloud computing. One of the key technology in SaaS is supporting multi-tenant applications. There are many considerations in the design of multi-tenant applications, including customized configuration of tenants and data security. In this thesis, we focus on data-level customized configuration, and propose an approach for not only sharing database between tenants but also supporting tenants to modify their table schemas within limits.
Extension Table Layout is one solution for solving the problems in data-level customized configuration for multi-tenant applications. However, SQL statemenst based on the Extension Table Layout are rather complicated and error-prone. Thus, we develop a tool to help software developers that will automatically rewrite the SQL statements from the common Private Table Layout into those from the Extension Table Layout at runtime. In other words, our tool enable software developers to write SQL statements in a multi-tenant application like in a single tenant application.
Indeed, software developers could develop a multi-tenant application easily by using our tool and the multi-tenant enabler - TenantId. In order to assess the feasibility of our tool, we develop an online multi-tenant course election application. Besides, we disscus the effectiveness of the factors that affecting our tool and work on a number of experiments and performance tests. The preliminary results show that our SQL rewriting tool can help software developers at a lower cost to meet the needs of data-level customized applications to a degree.
參考文獻 [1] [Chong et al.06] F. Chong, G. Carraro, and R. Wolter, “Multi-Tenant DataArchitecture,” http://msdn.microsoft.com/en-us/library/aa479086.aspx
[2] [Mell et al.11] P. Mell, and T. Grance, “Recommendations of the National Institute of Standards and Technology,” in The NIST Definition of Cloud Computing, 2011.
[3] [Force.com] The Design of the Force.com Multitenant Internet Application Development Platform
[4] [Aulbach et al.09] Stefan Aulbach, D. Jacobs, A. Kemper, M. Seibold, “A Comparison of Flexible Schemas for Software as a Service,” in SIGMOD’09, June 29–July 2, 2009.
[5] [Aulbach et al.08] Stefan Aulbach, T. Grust, D. Jacobs, A. Kemper, J. Rittinger, “Multi-Tenant Databases for Software as a Service: Schema-Mapping Techniques,” in SIGMOD’08, June 9–12, 2008.
[6] [Foping et al. 07] Franclin S. Foping, I. M. Dokas, J. Feehan, S. Imran, “A New Hybrid Schema-Sharing Technique for Multitenant Applications”.
[7] [Univ et al. 10] Zhejiang Univ., Hangzhou, “Transforming relational database into HBase: A case study,” in IEEE’10, July 16–18, 2010.
[8] [Hisashi et al. 10] SHIMAMURA Hisashi, S. Kenji, K. Takayuki, N.Shoji, “Realization of the High-density SaaS Infrastructure with a Fine-grained Multitenant Framework”.
[9] [Ramasubramanian Thiyagarajan et al. 10] Ramasubramanian Thiyagarajan, S. Kuppusamy, “Enabling Multi-Tenancy in Web Applications,” in devx.com, June 10, 2010.
[10] [Scott Chate 10] Scott Chate, “Convert your web application to a multi-tenant SaaS solution,” in ibm.com, Dec 14, 2010.
[11] [Martin Grund et al. 08] Martin Grund, M. Schapranow, J. Krueger, J. Schaffner, A. Bog, “Shared Table Access Pattern Analysis for Multi-Tenant Applications,” in IEEE’08, 2008.
[12] [Alfons Kemper] Alfons Kemper, “Database Technology for SaaS(Software as a Service)”.
[13] 吳定威，「支援多租戶應用程式的SQL語句轉換機制」，國立政治大學資訊科學系碩士學位論文，台北，民國101年1月。
[14] JSqlParser http://jsqlparser.sourceforge.net/
[15] SQL Statement tutorial http://www.w3schools.com/sql/default.asp
[16] Eclipse http://www.eclipse.org/
[17] JSP tutorial http://www.jsptut.com/
[18] Apache Tomcat http://tomcat.apache.org/
[19] MySQL http://www.mysql.com/
[20] Databse Design-Design pattern: many-to-many http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php
[21] Data Integrity http://msdn.microsoft.com/en-us/library/aa933058(v=sql.80).aspx
[22] Bootstrap http://twitter.github.com/bootstrap/
[23] XAMPP http://www.apachefriends.org/en/xampp.html
[24] XAMPP tutorial
http://www.mediawiki.org/wiki/Manual:Running_MediaWiki_on_XAMPP
[25] JDBC http://www.oracle.com/technetwork/java/javase/jdbc/index.html
[26] W3C http://www.w3.org/
[27] The Long Tail http://www.longtail.com/the_long_tail/about.html
[28] UML http://www.uml.org/
描述 碩士
國立政治大學
資訊科學學系
99753020
101
資料來源 http://thesis.lib.nccu.edu.tw/record/#G0099753020
資料類型 thesis
dc.contributor.advisor 陳恭zh_TW
dc.contributor.author (Authors) 李明憲zh_TW
dc.creator (作者) 李明憲zh_TW
dc.date (日期) 2012en_US
dc.date.accessioned 2-Jan-2013 13:26:30 (UTC+8)-
dc.date.available 2-Jan-2013 13:26:30 (UTC+8)-
dc.date.issued (上傳時間) 2-Jan-2013 13:26:30 (UTC+8)-
dc.identifier (Other Identifiers) G0099753020en_US
dc.identifier.uri (URI) http://nccur.lib.nccu.edu.tw/handle/140.119/56532-
dc.description (描述) 碩士zh_TW
dc.description (描述) 國立政治大學zh_TW
dc.description (描述) 資訊科學學系zh_TW
dc.description (描述) 99753020zh_TW
dc.description (描述) 101zh_TW
dc.description.abstract (摘要) 雲端運算環境的興起，軟體即服務(Software as a Service, SaaS)的營運模式也開始受到軟體開發商的注意，其中一項關鍵技術是支援多租戶的軟體開發。在設計多租戶應用程式時有許多需要考量的因素，包含每個租戶各自的客製化設定、資料安全性等等。本論文著重於資料層級的客製化部份，如何讓各個租戶之間可以共用資料庫，但又能提供租戶適度地更改其資料表綱要(schema)以達到客製化的需求。
為了解決資料層級客製化所面臨的問題，一種常見的作法是可延伸性表格(Extension Table Layout)。但是，在關聯式資料庫中實作可延伸性表格，軟體開發人員為了配合可延伸性表格的資料存放方式，所使用的SQL語句會變得相當複雜且易錯。因此，本研究實作一個系統工具協助軟體開發人員將SQL語句，從一租戶一資料表寫法自動轉換成以可延伸性表格邏輯表達的SQL語句，透過執行轉換後的語句來對儲存在可延伸性表格的資料進行增刪修改。
軟體開發人員在本系統工具的協助下，加入租戶識別碼(TenanId)的開發概念後，即可實際建立一個多租戶應用程式。為了評估本系統工具的效用，我們以其建置了一個多校(租戶)選課系統，並進行多項效能實驗以探討影響本系統工具效能的因素。初步的結果顯示，本系統工具可以協助軟體開發人員，以較低的代價滿足資料層級客製化應用的需求。		zh_TW
dc.description.abstract (摘要) Software as a service (SaaS) is an emerging service model of cloud computing. One of the key technology in SaaS is supporting multi-tenant applications. There are many considerations in the design of multi-tenant applications, including customized configuration of tenants and data security. In this thesis, we focus on data-level customized configuration, and propose an approach for not only sharing database between tenants but also supporting tenants to modify their table schemas within limits.
Extension Table Layout is one solution for solving the problems in data-level customized configuration for multi-tenant applications. However, SQL statemenst based on the Extension Table Layout are rather complicated and error-prone. Thus, we develop a tool to help software developers that will automatically rewrite the SQL statements from the common Private Table Layout into those from the Extension Table Layout at runtime. In other words, our tool enable software developers to write SQL statements in a multi-tenant application like in a single tenant application.
Indeed, software developers could develop a multi-tenant application easily by using our tool and the multi-tenant enabler - TenantId. In order to assess the feasibility of our tool, we develop an online multi-tenant course election application. Besides, we disscus the effectiveness of the factors that affecting our tool and work on a number of experiments and performance tests. The preliminary results show that our SQL rewriting tool can help software developers at a lower cost to meet the needs of data-level customized applications to a degree.		en_US
dc.description.tableofcontents 第一章 緒論 1
1.1 前言 1
1.2 研究動機 2
1.3 研究目的 5
1.4 研究成果 6
1.5 論文大綱 7
第二章 技術背景與相關研究 8
2.1 多租戶應用程式之資料層級設計 9
2.1.1 多租戶應用程式之資料層級設計分類 9
2.1.2 Separate Databases 10
2.1.3 Separate Schemas 11
2.1.4 Shared Schema 12
2.1.5 多租戶應用程式之資料層級設計考量 14
2.2 多租戶應用程式之資料表綱要類型 15
2.2.1 Private Table Layout 15
2.2.2 Universal Table Layout 16
2.2.3 Extension Table Layout 17
2.2.4 Pivot Table Layout 18
2.3語句結構剖析工具 19
第三章 系統設計 21
3.1 系統設計理念 21
3.2 系統設計考量 23
3.3 系統流程 24
3.4 系統實作方法 26
3.4.1 Extension Table Layout概念實作 27
3.4.2 CREATE語句的轉換、CREATE EXTENSION TABLE的設計與實作 28
3.4.3 ALTER語句的轉換 34
3.4.4 INSERT、UPDATE、DELETE語句的轉換 37
3.4.5 SELECT語句的轉換 46
第四章 系統實作與評估 49
4.1 系統實作展示 49
4.1.1 多租戶應用程式範例情境描述 49
4.1.2 多租戶應用程式範例系統設計與實作 50
4.2 系統效能之影響因素 59
4.3 系統效能測試 62
4.3.1 效能測試環境、測試項目、測試對象 62
4.3.2 Extension Table Layout效能測試 63
4.3.3 Extension Table Layout詮釋欄位建立Index之效能測試 64
第五章 結論與未來研究方向 67
5.1 結論 67
5.2 未來研究方向 68
參考文獻 70		zh_TW
dc.language.iso en_US-
dc.source.uri (資料來源) http://thesis.lib.nccu.edu.tw/record/#G0099753020en_US
dc.subject (關鍵詞) 雲端zh_TW
dc.subject (關鍵詞) 多租戶zh_TW
dc.subject (關鍵詞) SQL語句轉換zh_TW
dc.subject (關鍵詞) SaaSen_US
dc.subject (關鍵詞) Multi-tenanten_US
dc.subject (關鍵詞) SQL Rewritingen_US
dc.title (題名) 基於可延伸性表格的多租戶應用程式資料綱要轉換工具zh_TW
dc.title (題名) Implementing Customizable Data Schemas for Multi-tenant Applications Using Extension Table Layouten_US
dc.type (資料類型) thesisen
dc.relation.reference (參考文獻) [1] [Chong et al.06] F. Chong, G. Carraro, and R. Wolter, “Multi-Tenant DataArchitecture,” http://msdn.microsoft.com/en-us/library/aa479086.aspx
[2] [Mell et al.11] P. Mell, and T. Grance, “Recommendations of the National Institute of Standards and Technology,” in The NIST Definition of Cloud Computing, 2011.
[3] [Force.com] The Design of the Force.com Multitenant Internet Application Development Platform
[4] [Aulbach et al.09] Stefan Aulbach, D. Jacobs, A. Kemper, M. Seibold, “A Comparison of Flexible Schemas for Software as a Service,” in SIGMOD’09, June 29–July 2, 2009.
[5] [Aulbach et al.08] Stefan Aulbach, T. Grust, D. Jacobs, A. Kemper, J. Rittinger, “Multi-Tenant Databases for Software as a Service: Schema-Mapping Techniques,” in SIGMOD’08, June 9–12, 2008.
[6] [Foping et al. 07] Franclin S. Foping, I. M. Dokas, J. Feehan, S. Imran, “A New Hybrid Schema-Sharing Technique for Multitenant Applications”.
[7] [Univ et al. 10] Zhejiang Univ., Hangzhou, “Transforming relational database into HBase: A case study,” in IEEE’10, July 16–18, 2010.
[8] [Hisashi et al. 10] SHIMAMURA Hisashi, S. Kenji, K. Takayuki, N.Shoji, “Realization of the High-density SaaS Infrastructure with a Fine-grained Multitenant Framework”.
[9] [Ramasubramanian Thiyagarajan et al. 10] Ramasubramanian Thiyagarajan, S. Kuppusamy, “Enabling Multi-Tenancy in Web Applications,” in devx.com, June 10, 2010.
[10] [Scott Chate 10] Scott Chate, “Convert your web application to a multi-tenant SaaS solution,” in ibm.com, Dec 14, 2010.
[11] [Martin Grund et al. 08] Martin Grund, M. Schapranow, J. Krueger, J. Schaffner, A. Bog, “Shared Table Access Pattern Analysis for Multi-Tenant Applications,” in IEEE’08, 2008.
[12] [Alfons Kemper] Alfons Kemper, “Database Technology for SaaS(Software as a Service)”.
[13] 吳定威，「支援多租戶應用程式的SQL語句轉換機制」，國立政治大學資訊科學系碩士學位論文，台北，民國101年1月。
[14] JSqlParser http://jsqlparser.sourceforge.net/
[15] SQL Statement tutorial http://www.w3schools.com/sql/default.asp
[16] Eclipse http://www.eclipse.org/
[17] JSP tutorial http://www.jsptut.com/
[18] Apache Tomcat http://tomcat.apache.org/
[19] MySQL http://www.mysql.com/
[20] Databse Design-Design pattern: many-to-many http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php
[21] Data Integrity http://msdn.microsoft.com/en-us/library/aa933058(v=sql.80).aspx
[22] Bootstrap http://twitter.github.com/bootstrap/
[23] XAMPP http://www.apachefriends.org/en/xampp.html
[24] XAMPP tutorial
http://www.mediawiki.org/wiki/Manual:Running_MediaWiki_on_XAMPP
[25] JDBC http://www.oracle.com/technetwork/java/javase/jdbc/index.html
[26] W3C http://www.w3.org/
[27] The Long Tail http://www.longtail.com/the_long_tail/about.html
[28] UML http://www.uml.org/		zh_TW