第五章 結論與未來研究方向
5.2 未來研究方向
行語句轉換的執行時間。最後，實際進行多項效能測試和 Private Table Layout 比較，從結果顯示，以 Extension Table Layout 的概念並且在其詮釋欄位建立 索引(Index)的方式去設計多租戶應用程式之資料層級，軟體開發人員沿用一租
1. 本研究暫以租戶識別碼(TenantId)作為資料隔離的主要機制，在資料安全 性的考量下，從租戶的心靈層面看來仍有些疑慮，但若能在應用程式層級 提供更完善的機制去防止惡意的用戶利用 TenantId 來偽裝自己的身分竊取 其他用戶的資料，相信一定能夠提高資料的安全性。
2. 期望未來能夠在極少的更動之下，開發一套系統工具協助資料進行系統化 地轉移，改以 Extension Table Layout 的資料存放方式來儲存資料。接下
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
69
來，透過本系統工具進行語句轉換，讓軟體開發人員能夠沿用一租戶一資 料表的寫法來撰寫 SQL 語句。
3. 由於本研究在部份設計上採取共用資料表的架構，從 4.2 小節所進行的實 驗推斷租戶的數量會影響本系統工具進行語句轉換的執行時間，因此，當 租戶的數量大幅增加以後，要如何維持資料庫存取、查詢的效能在一定的 水準也是未來在實際使用本系統工具時需要解決的問題。
4. 如何在共用資料表的架構下，妥善處理 Integrity Constraints 是未來待 解決的問題。如同 3.4.2 小節所提到的，目前本系統工具在實作 Integrity Constraints 的處理，主要是透過資料庫所提供的多欄位限制加上 TenantId 的概念來完成，其他目前尚未支援的部份或許之後也可以仿造上述的方式 來解決。
‧
[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/
‧
[[ ]] :: SQL statement using Private Table Layout SQL statement using Extension Table Layout .
//Notations introduction
Bold fonts is a string in the SQL statement, for example, CREATE TABLE
Italic fonts is a element in the SQL statement, for example, TableName is the table
name of the statement , ColumnNames is a list of impacted column names in the statement ; ColumnValues is a list of impacted column values in the statement . string1 string2: return a new string of the concatenation string1and string2 .
string substring : delete a substring (substring) from the end of the string (string).
GetAllCN() : scan Column_Metadata Table to return a list of all common table names .
Mcoln( ColumnNames ) : return two lists : CommonColumnNames , PrivateColumnNames .
CommonColumnNames = col | col is a common table column name ,
col in ColumnNames.
PrivateColumnNames = col | col is a private table column name ,
col in ColumnNames. Mcolv( ColumnValues ) : return two list : CommonColumnValues , PrivateColumnValues
CommonColumnValues = val | val is a common table column value ,
val in ColumnValues.
PrivateColumnValues = val | val is a private table column value , val in ColumnValues.
Mue( UpdateExpressions ) : return two lists : CommonUpdateExpressions ,
PrivateUpdateExpressions .
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
73
CommonUpdateExpressions = col = val | col = val in UpdateExpressions , col is a
common table column name.PrivateUpdateExpressions = col = val | col = val in UpdateExpressions , col is a
private table column name. CtoP(TableName , TenantId ) : TenantId TableName CommonFields , return a private table name that converted from a given
common table name (TableName) and tenant id (TenantId) .
Mcn(TableName) : TableName CommonFields , return a common table name.
Mpn(TableName,TenantId ) : TenantId TableName , return a private table name.
GetMR( TableName ) : get the maximum row in a given table (TableName ).
GenS(Statement ) : generate a statement from a given statement(Statement )
‧
//Rules for CREATE statement, for example, CREATE TABLE TableName ( ColumnDefinitions ) 1. [[ (CREATE TABLE TableName ( ColumnDefinitions )) ]]
= GenS( CREATE TABLE TableName
( TenantId Char (50) not null , Row Integer not null , ColumnDefinitions ,
Primary key (TenantId , Row)) )
//Rules for CREATE EXTENSION TABLE statement, for example, CREATE EXTENSION TABLE TenantId
2. [[ (CREATE EXTENSION TABLE TenantId ) ]]
= FOR CommonTableName IN GetAllCN() DO
GenS( CREATE TABLE CtoP(CommonTableName , tenantId ) ( TenantId Char (50) not null ,
Row Integer not null ,
Primary key (TenantId , Row) ) )
END//Rules for ALTER statement, for example,
ALTER TABLE TableName AlterType AlterExpression
3. [[ (ALTER TABLE TableName AlterType AlterExpression, TenantId ) ]]
= GenS ( ALTER Mpn(TableName , TenantId ) AlterType AlterExpression )
4. [[ (ALTER TABLE TableName AlterType AlterExpression) ]]
= GenS ( ALTER TableName AlterType AlterExpression )
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
75
//Rules for INSERT statement, for example, INSERT INTO TableName ( ColumnNames )
VALUES ( ColumnValues )
5. [[ (INSERT INTO TableName ( ColumnNames ) VALUES ( ColumnValues ) , TenantId )]]
= (CommonColumnNames , PrivateColumnNames ) Mcoln(ColumnNames ) ; (CommonColumnValues , PrivateColumnValues ) Mcolv(ColumnValues ) ;
GenS ( INSERT INTO Mcn(TableName)
( TenantId , Row , CommonColumnNames ) VALUES
( tenantid , GetMR( Mpn( TableName,TenantId ) ) , CommonColumnValues ) ) ; GenS ( INSERT INTO Mpn( TableName , TenantId )
( TenantId , Row , PrivateColumnNames ) VALUES
( TenantId , GetMR( Mpn( TableName , TenantId ) ) , PrivateColumnValues ) )
‧
//Rules for UPDATE statement, for example, UPDATE TableName
SET UpdateExpressions
WHERE Conditions , where UpdateEexpressions is like ColumnName
1= ColumnValue
1, ..,ColumnName
n= ColumnValue
n6. [[ (UPDATE TableName SET UpdateExpressions WHERE Conditions, TenantId ) ]]
= (CommonUpdateExpressions , PrivateUpdateExpressions ) Mue(UpdateExpressions ) ; FOR row
IN ( SELECT Row
FROM
( Mcn( TableName )
INNER JOIN Mpn( TableName , TenantId )
ON Mcn( TableName ).TenantId = Mpn( TableName , TenantId ).TenantId AND Mcn( TableName ).Row = Mpn( TableName , TenantId ).Row ) WHERE TenantId = TenantId AND (Conditions ))
DO
GenS( UPDATE Mcn( TableName ) SET CommonUpdateExpressions
WHERE TenantId = TenantId AND Row = Row ) ; GenS( UPDATE Mpn( TableName,TenantId )
SET PrivateUpdateExpressions
WHERE TenantId = TenantId AND Row = Row )
END‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
77
//Rules for DELETE statement, for example, DELETE FROM TableName
WHERE Conditions
7. [[ (DELETE FROM TableNme WHERE Conditions, TenantId ) ]]
= FOR Row
IN ( SELECT Row
FROM
( Mcn( TableName )
INNER JOIN Mpn( TableName , TenantId )
ON Mcn( TableName ).TenantId = Mpn( TableName , TenantId ).TenantId AND Mcn( TableName ).Row = Mpn( TableName , TenantId ).Row ) WHERE TenantId = TenantId AND (Conditions ))
DO
GenS( DELETE Mcn(TableName )
WHERE TenantId = TenantId AND Row = Row ) ; GenS( DELETE Mpn( TableName , TenantId )
WHERE TenantId = TenantId AND Row = Row )
END‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
78
//Rules for SELECT statement, for example, SELECT ColumnNames
FROM TableName WHERE Conditions
8. [[ (SELECT ColumnNames FROM TableName WHERE Conditions, TenantId ) ]]
= GenS( SELECT ColumnNames