以關聯式代數設計多租戶應用程式之SQL轉換規則 - 政大學術集成
81
0
0
全文
(2) ⇢fl6…(↵✏K SQL I€èG. ‹o✏„x-. Design and Implementation of SQL Rewriting Rules for Multitenant Applications Based on Relational Algebra. v. 政 治 Advisor: 大. Y à ⇢ sm ZÎ. 立. Kung Chen. ↵À?ª'x «. ⌦. —. 學. ‧ 國. x. ˚. ‧. © Î ÷ á. io. sit. y. Nat. n. al. Ch. A Thesis. er. ⌥. Student: Yu-Tse Chou. ⇢ hܧ. i Un. v. Submitted to Department e n g c h i of Computer Science National Chengchi University in Partial Fulfillment of the Requirements for the Degree of Master in Computer Science November 2013. -Ô⌘↵. ~åtA i.
(3) ‹o✏„x-. ⇢fl6…(↵✏K SQL I€èG X. ®WÚÔKóÑ F(–õ º/8. Å. Ÿfl(⌃ıäÜä◊0Õñ. ŸÑB⇡!. ÔÂÂ. ⌘Ñ⇣,. SaaS Ÿ. Ü1⇢Ñv⇧ïeæõ Bì. ⇢Ñ¢6. ÔÂ-. ˙ìfl. 6⌘q´«ê»˝. ¢˝. )®„⇢fl6fl‘ã|. ⇧‹uÄS//Ù SQL ûÂÍ’I 政v- 治 大. €_6. 立. zìÑ„zπH⇥. Ÿõ…. Ÿõ…F∫Ü⌃. ÓÑ/T© SQL ûÂI€⇣Â⇢fl6«ô∂Àè/hTÑ. SQL ûÂ. Ë(Í´Ñ…(. ‧ 國. 學. ìfl6…(↵✏Ñfl‘ã|∫·˝. ↵✏ã|⇥. ≈. ‧. ã| SQL ûÂÍ’I€_6. Hxö«ô´Ñ«ô∂À. sit. y. Nat. ,÷á°( Universal Table Layout ⇡↵ SaaS ⇢fl6«ôq´8ã Layout «ô∂À a l&–˙. õÓc⇥v!i v ⌘⌘…(‹o✏„xΩ. n. a. Ü-. n U i e h n g c ⇡õ⌃÷Pú SQL ûÂÑI€èG. Ch. 6⌦Ùw. ,'. Â⇡õ⌃÷® ¿fl ¢. er. SaaS ‡F Force.com Ñ Universal Table. io. Ñ«ô∂À⇥ ⌘⌘√⇤Â. ∫˙. ≈(hTI€_. &ìI€èGÑ⌃„_äóÙ†π◆⇥⌘⌘ Ê˛. ↵ÊWÑÂw. ÊWx⁄@H˛Ñ✏©⇥. ‹uW⇢SaaS Multi-tenant. Relational Algebra. ii. å✏N~↵ÊW.
(4) Design and Implementation of SQL Rewriting Rules for Multitenant Applications Based on Relational Algebra ABSTRACT Recently, the service models of cloud computing get more and more attention. This thesis focuses on the Software as a Service(SaaS) model. SaaS providers hope to serve maximal number of clients at a minimal cost. Many researchers devote their time and efforts to invent solutions. 政 治 大. for sharing resources yet supporting customization. In particular, ser-. 立. vice providers want to adopt the multi-tenant service model to maximize. ‧ 國. 學. their profits. One of the key enabling technologies in SaaS is the SQL. ‧. rewriting mechanisms for supporting milti-tenant data architecture. The purpose of these mechanisms is to automatically rewrite the SQL state-. y. Nat. io. sit. ments from the common Private Table Layout into the chosen form of. n. al. er. multi-tenant data architecture at runtime so that software developers. iv. n for single tenant. can focus on their application development C hengchi U. Universal Table Layout is a common form of multi-tenant data architecture. In this thesis, we modified the data architecture of Force.com, which is also based on the Universal Table Layout. Secondly, we design the SQL rewriting rules for our multi-tenant data architecture based on relational algebra. These theoretical results not only make the representation more generic, but also make the rewriting rules more comprehensible. We then implement a tool for doing experiments on the SQL rewriting mechanism. Finally, we conduct some experiments and discuss the meaning of our experimental results.. iii.
(5) Keyword⇢SaaS Multi-tenant. 立. Relational Algebra. 政 治 大. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. Ch. engchi. iv. i Un. v.
(6) å ñHÅH. ⌥. Yà smZÎ. +(⇡. mÂX v÷⌅πbÑ⌥. ˘⌦x. ⇤Ñ˝õ. +. v. v!Å. ÷˚“. ©⌘⌘pà⇢§âÔ. ..OL. ˝. ÑÅ. å. x⌘. ✏}xw(vÑ. xw.  ≥ìW. ÷áå⇣å. PÑ. ∂áx. er. ⇥Ê a ;⌦˝ó01⇢v iv l C n T©~˙á-ÑF§U⇥ hengchi U. åÅ⌥⇡˝úÖ⌥∂∫q´ ı. ì⌘ÔÂâ√. œ/xw. ˙⇣. xm. Øè. xœ. ⇢. ⇡it⇢(x!ѯU. n. v. _⇥ºk©⌘. ì⌘(. ⌥˙p⇥. io. /≤m. ?ü. Ø. P. y. hâ. ´ñÑ/. k©N⌘Ñx. sit. á. Nat. @—KB. ⇥º⌥. ⌘–˙Ñ. @. èxw. ‧. Øäxw. É. Å. dÜj4⌘it⇢Ñv@. „z⌅✏⌅#ÑOL⇥ ◊ëxw. ÷. fÜ⌘1⇢Ñk©⌥◆. 學. ‧ 國. wx. •z(e-mail⌦⌥⌘. EÊ⇥. 政 治 大 ì⌘ÑvÔÂàÎe⌦ÃS Ê 立. ı. ÙÏ„ÑñŒÂ hÀ.  „f‘·⌘(„f⌦. ˝ì⇡«÷áóÂÙ†åt. d. t⇢ÂÜ(. ‡∫. _Å. ÷⌘!ùˆÑ/. Ë(≤m⌥v⌦⌃)påh↵⇥. v. á◊x. ÷ (. ⌥◆.
(7) Ó. ⌅. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. i. -áXÅ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. ii. ÒáXÅ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. iii. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. v. ¯. å. 政 治 大. Ó⌅ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. 立. hÓ⌅ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. ‧ 國. 學. Ó⌅ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. x xi. ‧. xii xiii. sit. 8(&_.Î. y. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. Nat. &_™. vi. er. io. Ω . . . . . . . . . . . . . . . . . . . . . . . . .. 1. 1.1 vÃo . . . . . . . . . . . . . . . . . . . . . . . . . .. 1. 1.2 v’_ . . . . . . . . . . . . . . . . . . . . . . . . . .. 3. 1.3 vÓÑ . . . . . . . . . . . . . . . . . . . . . . . . . .. 3. 1.4 ⇣. v⇣ú . . . . . . . . . . . . . . . . . . . . . . .. 4. ,å‡ ¯‹v⌥ÄSÃo . . . . . . . . . . . . . . . . . . .. 5. 2.1 ⇢fl6«ôd⇢ . . . . . . . . . . . . . . . . . . . . . .. 5. 2.2 h<±Å. 7. n. ‡ “÷ . . a. l . . . . . . . . . . . . .i v. . . . . . . . . . . .. ,. Ch. n engchi U. ⌅ÄS . . . . . . . . . . . . . . . . . . . . vi.
(8) 2.2.1. Universal Table Layout . . . . . . . . . . . . . .. 7. 2.2.2. Other Table Layout . . . . . . . . . . . . . . . .. 8. 2.3 Force.com Universal Table Layout . . . . . . . . . . . .. 8. 2.4. 2.3.1. Objects. Fields. Data h< . . . . . . . . . . .. 10. 2.3.2. Indexes. Uniquefields h< . . . . . . . . . . . .. 11. 2.3.3. Relationships h< . . . . . . . . . . . . . . . .. 12. 治 政 SQL ûÂ9ÎÑ- ⌥⌃ê .大 . . . . . . . . . . . . . . 立. 2.6 Apache Jmeter . . . . . . . . . . . . . . . . . . . . . . .. 14. ‧. ‧ 國. 13. 學. 2.5 JSqlParser . . . . . . . . . . . . . . . . . . . . . . . . .. Jmeter Ñy' . . . . . . . . . . . . . . . . . . .. sit. y. Nat. 2.6.1. er. ‡ SQL ûÂ9ÎèG . . . . . . . . . . . . . . . . . . . .. io. ,. 13. al. n. iv n C SQL ûÂ9ÎèG⌃ı . . . . h e n g c h. i. U. 3.1. 14 16. . . . . . . . . . . .. 16. 3.2 ûÂ9ÎèGÑ˙,ö© . . . . . . . . . . . . . . . .. 21. 3.2.1. iˆ. 1I€˝x(Object Name Transformation). 21. 3.2.2. ⌅M. 1I€˝x(Field Name Transformation). 22. 3.3 INSERT INTO ûÂ9ÎèG . . . . . . . . . . . . . .. 23. 3.3.1. Data h< . . . . . . . . . . . . . . . . . . . . .. 24. 3.3.2. Uniquefields h< . . . . . . . . . . . . . . . . .. 25. 3.3.3. Indexes h< . . . . . . . . . . . . . . . . . . . .. 26. vii.
(9) 3.3.4. Relationships h< . . . . . . . . . . . . . . . .. 28. 3.3.5. !“e⇢F«ôÑ INSERT INTO ûÂ9Î .. 29. 3.4 DELETE ûÂ9ÎèG . . . . . . . . . . . . . . . . .. 30. 3.4.1. Data h< . . . . . . . . . . . . . . . . . . . . .. 31. 3.4.2. Uniquefields h< . . . . . . . . . . . . . . . . .. 33. 3.4.3. Indexes h< . . . . . . . . . . . . . . . . . . . .. 36. 治 政 Relationships h< . .大 . . . . . . . . . . . . . . 立. 3.4.4. ‧ 國. 學. 3.5 UPDATE ûÂ9ÎèG . . . . . . . . . . . . . . . . .. 39. Data h< . . . . . . . . . . . . . . . . . . . . .. 41. 3.5.2. Uniquefields h< . . . . . . . . . . . . . . . . .. 43. 3.5.3. Indexes h< . . . . . . . . . . . . . . . . . . . .. ‧. 3.5.1. er. io. sit. y. Nat. al. iv n C CREATE TABLE . . h eûÂ9ÎèG ngchi U n. 3.6. 37. 46. . . . . . . . . . . .. 48. 3.6.1. Objects h< . . . . . . . . . . . . . . . . . . . .. 49. 3.6.2. Fields h< . . . . . . . . . . . . . . . . . . . . .. 50. 3.6.3. Associations h< . . . . . . . . . . . . . . . . .. 52. 3.7 CREATE TABLE ûÂ CONSTRAINTS. ûÂÑ#6. qˇ . . . . . . . . . . . . . . . . . . . . . . . . . . . .. 53. 3.7.1. DELETE CASCADE policy . . . . . . . . . . .. 54. 3.7.2. UPDATE CASCADE policy . . . . . . . . . . .. 54. viii.
(10) 3.7.3. DELETE SET null policy . . . . . . . . . . . . .. 55. 3.7.4. UPDATE SET null policy. . . . . . . . . . . . .. 56. 3.7.5. DELETE SET Default policy . . . . . . . . . . .. 56. 3.7.6. UPDATE SET Default policy. . . . . . . . . . .. 57. ⌥⌃ê . . . . . . . . . . . . . . . . . . . . . .. 58. 4.1 ÊWÂwÊ˛ . . . . . . . . . . . . . . . . . . . . . . .. 58. 4.2 ÊW-. 58. ,€‡ ÊW-. 治 政 . . . . . . . . . . . .大 . . . . . . . . . . . . . . 立. 59. 4.2.2. GÛfl6«ôh< . . . . . . . . . . . . . . . .. 59. 4.2.3. ÊW. - 9äfl6«ô´'✏ . . . . . . . . . .. 60. 4.2.4. ÊWå - 9ä. y. sit. io. n. 4.3 =P . . .. BÕ\Ñ Clients ∫x . . . . .. er. Nat. al. ‧. ‧ 國. ÊW∞É∂- . . . . . . . . . . . . . . . . . . .. 學. 4.2.1. iv n C . . h . . . . . . . . U engchi . . . .. 62. . . . . . . . . . . .. 63. ,î‡ P÷⌥*Üvπ⌘ . . . . . . . . . . . . . . . . . . .. 64. 5.1 P÷ . . . . . . . . . . . . . . . . . . . . . . . . . . . .. 64. 5.2 *Üvπ⌘ . . . . . . . . . . . . . . . . . . . . . . .. 64. √⇤á{ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. 66. ix.
(11) h. Ó. ⌅. 3.1 Data h<ƒã . . . . . . . . . . . . . . . . . . . . . .. 35. 3.2 Foreign key ‹¬h<ƒã . . . . . . . . . . . . . . . .. 38. 3.3 Store_Information h<. . . . . . . . . . . . . . . . . .. 39. 4.1 Product h<±Å . . . . . . . . . . . . . . . . . . . . .. 60. 4.2. 60. 政 治 大 Order h<±Å 立 ....................... ‧ 國. 學. 4.3 OrderLineitem h<±Å . . . . . . . . . . . . . . . . .. ‧. n. er. io. sit. y. Nat. al. Ch. engchi. x. i Un. v. 60.
(12) Ó. ⌅. 1.1 é↵?ú≤Ÿ Info.Apps.gov . . . . . . . . . . . . . . .. 2. 2.1 ⇢fl6«ô∂À⌃^ . . . . . . . . . . . . . . . . . . .. 5. 2.2 Universal Table Layout [1] . . . . . . . . . . . . . . . .. 7. 2.3 Force.com Universal Table «ô∂À [2] . . . . . . . . .. 9. 學. ‧ 國. 2.4 Objects. 政 治 大 Fields Data h<±Å [2] . . . . . . . . . . . 立. 10. Indexes h<±Å [2] . . . . . . . . . . . . . . .. 11. 2.6 Data. Relationships h<±Å . . . . . . . . . . . . . .. 12. 2.7 JSqlParser (ƒã . . . . . . . . . . . . . . . . . . .. 14. ‧. 2.5 Data. 18. 4.1 ÊW∞ÉMn. . . . . . . . . . . . . . . . . . . . . . .. 59. ⌅û turnaround time . . . . . . . . . . . . . .. 61. 4.3 ÊWå⌅û turnaround time . . . . . . . . . . . . . .. 62. n. er. io. sit. y. Nat. 3.1 «ô∂Àa. . . . . . . . . . . . . . v. . . . . . . . . . . .. 4.2 ÊW. i l C n U hengchi. xi.
(13) & _ ™ ⇠ object (n, t). iˆ. 1I€˝x p.20. ⇠ f ield (no , nf , t). ⌅M. 1I€˝x p.21. ‹o✏„x - Selection ‹o✏„x - Projection. ⇡f1 ,f2 ,f3 ,··· ,fn [. 立. ‹o✏„x - Difference. ‧ 國. 學. \. ‹o✏„x - Union 政 治 大. ‹o✏„x - Generalized Projection. ⇧F1 ,F2 ,F3 ,··· ,Fn. ‧. W22•KóP. n. er. io. sit. y. Nat. al. Ch. engchi. xii. i Un. v.
(14) 8(&_.Î. Ω. b td. fl6A4_-tenantId. c sod. êh<A4_-sourceObjectId. oc n. h<. b od. h<A4_-objectId. c tod. ÓÑh<A4_-targetObjectId. gb. y er. sit. ê⌅MMn-sourceFieldNum. aÓÑ⌅MMn-targetFieldNum iv l C n hengchi U. ‧ 國. n. d tf u. io. du sf. 1-fieldName. ⌅MMn-fieldNum. Nat. fcu. ⌅M. ‧. fca. 立 ⌅MA4_-fieldId. 學. fcd. 治 1-objectName 政 大. «ôA4_(Data)-dataGuid. sg b. ê«ôA4_(Relationships)-sourceDataGuid. c ad. ‹ometa-dataA4_-associationId. b tg. ÓÑ«ôA4_(Relationships)-targetDataGuid. b rd. ‹oA4_-relationId. xio. ⇠ object (n, t). xif. ⇠ f ield (n, f, t). xiii.
(15) ,. ‡. “÷. Ÿ˙,Çıêͺ1960t˚f—x∂ John McCarthy. ÚÔ ˝õ. )Ô˝⇤ä⇣¯ÇÍÜ4. ^vÊ ing) @⇥Ñ. /∞ÄS. åç. ãP´Nº1999t_’Ñ. k. ⌫B⇢EÑÂ\⇥. ⇥r. k)(ÖN500,. 60Ñ⌦_2L⌃ê. l‘∂À. W. Ñ. √⌥⇧. i⇥ ;ÅÑyfi(⌥⌅.. I⇢Ñ˚f✏N⌃c✏KóÑπ✏Zt. K. 學. (⇥. SETI@home. ‧ 國. Ñs. ˚f!õ. ⇣⇥. ⌃%§1>⇢˚f⌅. 政 治 大ÔÂ~0 G/⌥⌃c✏KóÑÇı†Âˆ8ÑÇı 立. Ñ↵∫˚fÑzì ≤<Kó. Âå⇣Æ. ÚÔKó⇡↵. ŸÑÇı…. 1/⌥'ã˚Ÿ⌥⌃⇣✏ÆMå uPú. ⇥. ˚fKó. (Distributed Comput-. ⌃c✏Kó. (Grid Computing)ç†⌦ÚÔ. ⌃c✏Kó. Í2LKó. 5ÔK^Ñü(-Ω. v,Í'Ù•Í. ≤<Kó. ÷ç∫. ÚÔKóZ˙Üc✏Ñö©. ‧. é↵↵∂⇡ñÄS@(the National Institute of Standards and Technology ÚÔKó≈. wôî↵˙. NIST). yµ⇢(1)®. Í©. sit. y. Nat. Ÿ(On-demand Self-service) (2)®B®0(˚U≤Ô›nX÷(Broad Network Elasticity) (5)´„ß⌥œ,Ñ. Ÿ(Measured Serice) [3]. n. al. 1.1. engchi. i Un. v. vÃo. 9⁄éóI80 `h. Ch. Õ ∞ Ë l H ; ¶(Rapid. er. io. Access) (3)⇢ ∫ q ´ « ê `(Resource Pooling) (4)Î. *Üîth⇤ÚÔKó⇥4è!⌥T0]~îA⌅éC. Lfl‘⇥4Ñ~⌃KAå⇥é↵}Æ«⌦wNS(Vivek Kundra)£⇤. K ( Ú Ô — Ä ã | ˙ Ü Ñ ∞ ? ú ≤ Ÿ Info.Apps.Gov. ÑIT«⌦˚q⇥⁄⌦⇣⌅B1↵ ⌥ˇé↵?úœt. (New York Times)1. ↵⇤~îA⌅éC. ⌦ ì fl x ∫ ↵(The Economist)Ù Â fl(Microsoft) 2. ↵ú. e⌥ÚÔKó. Apple Ÿ⌥⌃⇣. ã. ⌅. 1.1. ⌥÷„⌦. Info.Apps.Gov⇣0. ∞cåF€C⌅CÑ/˙⇥. ÚÔ⌦Ñ0-. ∫L. fi ˙Google. Æ. Ë-(-6ÚÔKóF_Ñ¿»↵¶⇥ [3]ö© .!✏⇢Software as a Service(SaaS) Platform. as Service(PaaS)  Infrastructure as Serice(IassS) 1. ›. Ÿ!ã-Ñ Software.
(16) 1.1: é↵?ú≤Ÿ Info.Apps.gov as a Service(SaaS) Gartner,Inc (2012t⇣0h⇤Ñt=6e⌥ÔT0145⌅é e⌥˙ 治 政 大 h⇤ Software as a Service(SaaS)⌥⇤ie0⇣wÛ2015t &⇣,6eÔT221⌅ 立 éC -✏ m88˙º⇣ó !’hÀ†‘ ↵¡ …(↵✏ã| ≠K⇣ ‘w2011tÑ6e123⌅éC⇣wÜ17.9↵~⌃‘⇥Gartner,Inc _2. ,. Ù%™Å˝Z0 Platform. ˙⇢fl6q´«ê!✏Â. MN≠K⇣,. ÂÅ⌥⇢fl6ÑÄS. io. ⇢CÑfl6. al. Ch. m⇢Ñ…(ã|. engchi. i Un. 1. q(l‘(Shared Hardware)⇢✏N[Ï fl6⌘(⌦b(. Wl‘ …(↵✏. Æ. v-Â, ø˝2 ô´. Æ. ŸÙ⇢CÑ¢. ÔÂ⇤n. ŸÙ .. d. v. ÑÄS. q(. Wl‘-Ω. …(↵✏(Single Application)⇢q(. Ffl6⌘Ñ«ô´/|dhÀÑ⇥. 3. q(l‘ «ô´(Shared Hardware U-. ⇡. Ñ«ô´Â …(↵✏⇥. 2. q(l‘(Shared Hardware). plication)⇢q(@. Ü1ÔÂ. –õfl6⌘ÙÊ‡Ñ˘<. n. ⇢Ñq´«ê⇢. e. Çd. B. y. ⇢fl6ÑÓÑ1/∫Ü˝. fl6Ñ6ª⌦. sit. Nat. 6 [4,5]⇥. Õ…(. er. ⌘Ñ⇣,˝. @Âfl‘ã. ⌘ã|⇣, ≠K⇣,. ‧. õ@. ⇢Û Infrastructure ˙n⌥≠wÜ. 學. |F-. ‧ 國. C. Ñ«ê. Database). Æ. («ô´-Ô›. …(↵✏(Single Ap-. fl6⌘Ñ. BçzöÇ. «ôh±Å [1]⇥ .q(Ñ!✏/. ¿. eMN«ô´zìÑjª …(↵✏. fl6⇣,Ñ. Çú˝. ŸÙ⇢Ñfl6. «ôh±ÅÑ…(↵✏/ 2. ¿. -Mq(«ôh±Å. ‡d˙n. ↵q(l‘ «. ⇣,Ñã|π✏⇥.
(17) 1.2. v’_. q(l‘ «ô´. Æ. …(↵✏. «ôh±ÅÑq(∂À/. ã|π✏. Ç Aulbach et al. [1]@–˙Ñ. Ñ¢˝. 'Ë⌃Ñ SaaS …(↵✏Ü™_/. ≤˚q. ¸iFŒ. œ↵fl6. ö. v. ìflH Ñ. q(∂À–õÜo}Ñt ↵ÕÅÑ. Å¢˝. ãÇœ↵x!Ñã≤P6. 9⁄⇡õP6«ô. ≤. fl6ÔÂÍÒç¢˝. ÑL∫. 6. B. s. '. fl6. #˝/Ó. Ñ0π–õ⌥%∫. Ÿ. fi…x. w. Êx. Ñ. ⁄⌦xÓÜ⇡Ä. ‡∫q(Ñ«ô∂À. ⇤n«ôÑâh' fl6⌘ã|ÍÒ…(↵✏è/Ñ⌥‹¶. ≈. M. ∫Üfl6⌘Ñ. Ñ˚q«ô∂Àìfl6⌘ÍÒ9Î Query language. / ˆ ⌃ 治 政 Tgâh'ÑL∫ @Â✏N ˚qÑ„zπHk©fl6å⇣ûfl6<-Ñè 大 立 /«ô±ÅI€⇣Ê‘(q´«ô´-Ñ«ô±Å⌥⇤/ÕÅÑvpL⇥. ã|–õ@. ‧ 國. ,8ãÑ«ôh±Å. Table Layout /. H'Ñ. ⌅ Ä S(schema-mapping technique).. ‡∫É/. 學. (. .. w. ,'Ñ∂À. /((Col0, Col1, ...,)Ñπ✏Ü2X«ô. ÑB⇡⇤. Ñ«ôh±ÅI€. io. ⇣ü/ÙÜÖN55,000. a6l. SalesForce.com /. ∂W. y. F⇡.∂ÀÚì´ CRM. sit. Nat. SalesForce.com ⇣ü®L. í. ˙. ÷6ɇd(Âb ∂ SaaS õ…F ŸÑõ…F. fl6 [2]⇥Weissman and Bobrowski –˙Ü. er. ÅM. ‧. öÑ«ôh±Å. Universal. Úì. ↵ Force.com. n. (⇡«÷á-&*⇧Z– i v Force.com SaaS …(↵✏ n C hengchi U /ÇU⌥fl68eÑè/ÂbûÂI€0ÔÂ(Ê‘«ô´⌦˜L @Âà„ª. sÑÇÏ*” [2]. U0 Force.com Ñ\’. ⇢Û–˙Ô˝92. ˆ8Ü. ‡d⌘⌘ç∫⇡/. ↵. <óªï«Ñvπ⌘⇥. 1.3. vÓÑ. ,«÷á)(‹o✏„x⌫f® Ñ_6fΩa. )(®. ⌥fl6⌘è/ÂbûÂI€⇣Ê‘ÂbûÂ. ˙ÜÑPú9Î Apache Jmeter d. ã>üÀº”õ. (⇧ÔÂnfl6Ñ“r( GUI. ,fÂwÊ˛⌘⌘@®. Ñ‹o✏„x✏. ⌦8eè/ÑÂbûÂ. ✏N9ÎåÑ Apache Jmeter ÔÂ⌥ûÂ9Î⇣óÂÂ. bÊõ«ô∂ÀÑûÂ. &ÔÂû⇡↵ÂwóÂÊõÑfi…Bì(turnaround. time). &⌥⌃÷ÑB쮂Z⌃ê⌥‘⇤⇥ 3.
(18) 1.4. ⇣. v⇣ú. ,vÑ¢{ÔÂ⌃⇣. ↵Ë⌃⇢. (1) SQL û‹o✏„x® • INSERT INTO ûÂ9ÎèG • DELETE ûÂ9ÎèG • UPDATE ûÂ9ÎèG. 政 治 大. • CREATE TABLE ûÂ9ÎèG. 立. • CREATE TABLE CONSTRAINTS. DELETE. UPDATE ûÂÑqˇ. ‧ 國. 學. (2) ÊWÂwÑÊ˛. ‧. (3) I€èGB쮂⌃ê. n. er. io. sit. y. Nat. al. Ch. engchi. 4. i Un. v.
(19) ,å‡ ,‡¿⌥›. (SaaS∞É-. ±ÅÑ∂ÀÑM–↵ «ôÑî‚' ÄSÃo ⌥e. À9®. q(h<±Å-. ¯‹vZ. …(↵✏ h<. ⇢fl6‹o✏„x✏Â Ê˛⇢fl6…(↵✏⌥  ¢˝⇠flè/@. wôѯ‹vÂX⌥ Hûh<±Å. ⌅ÄS. eœÇUÊ˛Force.com}Ưѫô∂À;,åË⌃⌥. (⇢fl. 6SaaS∞É↵. ,v)(‹o✏„xÜhTΩaÑSQLûÂ9Î_6@√⇤Ñ. ↵ú. œ. 立. &⇧+(Ê˛,vÂw@. 政 治 大. wôÑ˙. ÂX⇥. ⇢fl6«ôd⇢. ↵«ô´˝⌥˝ 'Ë⌃Ñ. Nat. éCÑ⇣,⇥6. '(data isolation)MN. io. 'P¶Ñfl6x. m. Ù_∆«ô. )1Õ. ⇡⇧˝. MN⇣,ÑÄS". («ôq(d⇢B. Ch. h,⇣+Ü. ↵x~, ⇣«ô@î. @Â⇢fl6ÑÄ. e⇡⇧ÄSÂMNflK⇣,. i Un. v. €Âq. Üw>H…(Long Tail) [4] [5]6ÜÜ∞Ñ⇥4⇥. engchi. ⇤n0âh' «ôœ'✏ÑpL. x«q(Ñ↵¶. ìflH Ñ\. œt‡d. er. mÑ…(/x«ÔÂlãÑ«ô. al. &. Çú‡q(«ô<±Å. n. S(. Ÿv. …(↵✏/. y. ‡∫œ. «ô´Â Æ. sit. ’. q(l‘. ‧. ‧ 國. 學. (⇢fl6Ñ∞É↵. ™. ⇢fl6(q(l‘ «ô´⌥Æ. ‡d(•↵Üч¿-@⌃∫i↵'⇧Ó. ç2. 2.1. ¯‹v⌥ÄSÃo. ã|⇧ÔÂ9⁄v. Liao et al. [6]⌥«ôd⇢Ñ⇢fl6ÄSù«ô⌃î↵¶. ⇢Ñ⇢fl6ÄSù«ô⌃î↵¶ ⌅fl6Í⇥±ÅÑH'⌥˛LÄS⌃∫€↵x ã. ã. 2.1⇥. • Shared Table Shared Schema⇢ » Ô 1 ∫ Basic Layout [1]. 2.1: ⇢fl6«ô∂À⌃^ 5. Shared Table.
(20) „hfl6⌘Ñ«ô˝›X( ô´Ñ Ñ. ↵2Xzì. ↵‹o✏«. Shared Schema „hfl6⌘Ñ«ôh±Å˝/. Dh<. dÄSxã(Ê˛⌦. fl6. G(Sharding)ÄS'EÙE«ôшU'. àπ◆1ÔÂ✏N«ô⌥ (. ⇤⇢fl6(⇥6. v;ÅOLs∫H'. ¯. _:Oâh'. ‡∫@. Å›. yöfl6«ôfiØ. PÑl‘∞É-ÕÔ–õf ‡∫@. fl6Ñ«ôGMº. fl6Ñ Schema ≈ ↵«ôh⇥d. Shared Schema⇢‡∫fl6⌘X>«ôÑzì. ‡dw 政 治 大 ' F‡∫ q´«ô´ sœ↵fl6Êõ⌦Ñ Schema ¯ 立 6Å⌃%≠w  100↵fl6 ã|⇧1Å≠w100↵«ô´. Private Schema⇢⇡w. ã|⇧Õ ‡d⇣,¯. ÿâh' ìfl6. Nat. fl6π◆¢˝. Ñ*fi. ÷6(. ↵2Xzì-. n. al. ⇡⇤. Ch. engchi. i Un. • Shared Table Private Schema⇢⇡/ÓM ⇤º Private Table _6Ü‹7. sh<±Å¯. ¶o6⇤ÿ. œ. ⇣zì⌦ÑjªÂ Ê\. v. ⇢∫. Private Schema âh'⇤N. F1ºw. ÷Ñ«ô∂À. ⇣,N(Shared Table)H'ÿÑ*‚. _‡d(Ê\Ve⌦_X(⇢.. xSL@–˙Ku∞«ô2XPÀ9o. (Copeland & Khoshafian [7]). ÷6¯. Å✏Nv÷X÷âh'. „∫°(Ñ«ô∂À⇥1º°( Private Schema. ˛(mL. ì. er. io. ↵fl6Õ6(Ñ/ÍÒhÀÑh<. π◆¢˝. Private Schema ÷6. y. Private Table Ñ:fi⌥Mb@¯. sit. ‧ 國. ⇤ÿÑâh. ‧. • Private Table. /˙ºÈ. «ô@. 學. vÿ⇥. „⇥. Â. äó„ [1]⇥. ⌥⇤¯. î'⌥ Shared Table Shared Schema ¯‘ÜÑ}. Ñ∂À. #. ÍÅ( Shared Table -†⌦fl6. ∫π◆. X % º ⌅(TenantId)s Ô @ ⌃ @. • Private Table. ãÇ⇢. ã✏-. ‡d_/. ‡dvÊ\„ π✏. 'Ë˝˝. ÜãÇ Extension Table. XML Table(Foping et al. [8];Du et al. [9]) ⌥. Universal Table (Maier et al. [10])⇥. 6.
(21) 2.2. h<±Å. 1M. ✏¿Ñ. wÑ⇣,. ÷. «ôd⇢Ñq(ÔÂì. ŸÑ–õ⇧(service provider)MN≠. ⌥. Ÿ. 6. 2. ↵«ô´-. ⌅ÄS. a…0لу. ÇUª@⌃œ. »˝ìfl6⌘›. ¢˝. ÑH'. Õ6Å–õfl6ù⁄⌅ÍÑ Üm.h<±Å. ↵fl6Ñ«ô. fl6Ñ«ôX>(.  ⇢Ûq(. (·Ô˝MN«ê. B¢˝. ⌅ÑÄS. ⌥@. ↵h<<. ÇU. ≠w⇣,ÑÓÑ↵. ⌘⌘. ÍÒh<ÑH'. ÄSÑ-√. Û. @ Aulback et al. [1]x. ˙q(h<±Å ¢˝. i. ⇧⇥Â↵!ÆÀ9,«÷á⇤(0Ñh<±ÅÄS⇥. d.π’/⌥@. fl6Ñ«ô>nº. 6ì«ô⌅MÑÓp. fl6Ño∆. ↵q(Ñ«ô´h<. B. ∫Ü/Ùfl. èÈcÑπ✏ÜX>«ô⇥w‘ !ÆÂA4_}. Ñ Tenant ⌅M⌥ Table ⌅MÜ@⌃. ‧. Ñ«ô⌅M/@. É/°. 學. ‧ 國. 2.2.1. 政 治 大 Universal立 Table Layout (Col1. q(h<. Clo2. ...). &Â∞û. n. al. er. io. sit. y. Nat. fl6Ñ«ô⇥. Ch. engchi. i Un. v. 2.2: Universal Table Layout [1]. Universal Table Layout Ñ;Å*fi/!÷⇢⌘↵fl6˝Í ˇ≥⇢fl6q(«ô´Ñ˙, ⇣. B⇥F‡∫. Å. fl6ìÑ«ôÓp. ↵h< @Â.  _. õ:fi⇢. (1)‡∫. fl6ìÑ«ôÓp. Ë˝⌅M⇤. jªÑ≈¡|. fl6ìÑ«ôÓp. Ë˝«ô⌅MÑ«ô⇤. 2.2-Ñ. s. ∫d⌅M⇥ (2)‡∫ Ç. Col3. W2_. (3) ÅM. xW)⇥. Ñ meta-data ª⇠⌅⌅M. 1⇥. Universal Table (è!ìflH ⌦h˛ Å. ↵. M. pÍ'ã%Ñ≈¡(ã. s. 1º!÷⇢⌘fl6. «ô⌥GÄS(Database Sharding) 7. Table ˝Í. ÔÂ⇤π◆†ÂˆU⇥d.
(22) 1º. ◊ã%«⌦ÑP6. et al. [1])⇥Â. Ñ SaaS. Universal Table 9o. 2.2.2. ¯⇤ºvÉ«ôMnπ✏. ⇤π◆ˆUÙE(Aulbach. Ÿ–õ‡F Force.com gË@°K⇢fl6∂Às∫˙º. Ü(Weissman & Bobrowski [2])⇥. Other Table Layout. d Ü2.2.1✏ ¿ - @ – 0 Ñ Universal Table Layout Aulback et al. Ñ – ˙ v ⇠ î.(Private Table Layout Pivot Table Layout Table Layout Chunk-Folding Table Layout) *:fi. SaaS. ⇡m.h<±Å. ⌅ÄS⌅. ⌅Ñ. Bx« iÑÄSD 治 政 _1≈ –õ¯大…Ñ9Î_6 (,v-. 立. ·⌥Nã|Ñ⌥‹¶. &(Âw-–õûÂÍ’9ÎÑ_6. ⌘⌘Ñ∂À/˙À(W. (d}Ư-. –õ0¿≈*– . ⌘. ìã|∫. ÚÔ SaaS Ñõ…F@–˙Ñ. ÙÊ\⌦. „. d. ⌘⌘_. ‧. Ó9ÜË⌃÷⌘@–˙Ñ«ô∂À. F. 學. ‧ 國. ⌘x« Universal Table Layout. Âì–õûÂ9ÎèGäóÔL⇥. sit. y. Nat. er. Force.com Universal Table Layout. io. 2.3. Chunk. ŸÑõ…FÔÂ9⁄Êõ(⇧Ñ. /9⁄ïdÑ«ô∂À. }Ư [2]. Extension Table Layout. al. n. iv n C h e n g c h i U 9⁄ Weissman et al. @œÑ ,v)( Force.com Ñ«ô∂À†ÂÓ9 Force.com Universal Table Layout. ⌥h<⌃⇣. table Data table  Pivot table. )(⇡. .^ã. ⌃ % ∫ meta-data. .^ãÑh<˙À⇢fl6Ê‘«ô. ´ [2]⇥ • meta-data ^ãÑh</(Üœfl6h<Ñ«⌦ 2Xh<-@. Ñè/h<&XI€0Ê‘«ô´-@. Fields h<G/ìfl6⌘X>œ 1. …⇥. ↵è/h<Ñ@. ѯ‹«⌦. ⌅M«⌦. ⇧+⌅M. ⌅Mã%II⇥. • Data ^ãÑh<q. Objects h<ìfl6⌘. Data h<-›XÜ@. (W2«ôã%. fl6Ñ«ô. Kåç)( Fields v-@⇠. Clobs h<G›XÜfl6^PÀ 8. áW«ô⇥. «ôÑ›X. ÑÊõã%2L.
(23) 政 治 大. 學. ‧ 國. 立. 2.3: Force.com Universal Table «ô∂À [2] «ô. &)(v. Pivot h<l⇤. y. ÷ófl6( Data h<-Ñ«ô⇥÷6 Force.com &*ähËÑ. Nat. Indexes UniqueFields. F;Å. sit. -«ô†. ‧. • Specialized Pivot h<;Å(≠w Data h<-Ñ^cè. Relationships. b. ¶. ‡d. v. i Un. Ô˝(‹o✏«ô´Ñ Index ü˝Ü˙n". Ch. engchi. †. Â. @Âä Data h<- Value ⌅MÑ<⌥˝0 Indexes åç˙À‹o. ✏«ô´Ñ" ". al. n. ô˝/W2. er. io. NameDenorm  FallBackIndex h<⇥1º Data h<-@›XÑfl6«. ⇥UniqueFields Ñü˝Ç. </ÔÂÕ⌥Ñ. UniqueFields /. ⌅Ü Objects Kì‹oÑ" Œ˙OL. F Indexes -Ñ. ÔÕ⌥⇥Relationships G/⇠. Å(0 join 2LÂbB. v. dh<⇥NameDenorm G/–õÎ v Force.com ⌧". Indexes h<. /D. Âb Object NÿB. ø⇤)(0. 1⌥ ID Ñ". ⇥d. Force.com ÑÂb_6ø⇤. ( FallBackIndex -«ôÜå⇣ÂbÀB⇥ ÷6( Weissman et al. [2] ÷á-&*hËlãvh<<±Å ÷Ô®÷˙Ë⌃Ñ«ô∂À. ,vÂd\∫˙. 9. 2. F9⁄á{Ñ. eÓ9&Ê˛˙Ü⇥.
(24) 2.3.1. Objects. Fields. Data h<. Data h<-›XÜfl6Ñ«ô ô(Meta-data). Objects ⌥ Fields G›XÜ«ôÑnÀ«. Objects v-ÑœF«ô1Ç. Fields -Ñ«ô1Ç. ‹o✏«ô´Ñh<(Table) 2.4∫⇡↵. h<-Ñ⌅M(Column)⇥. ↵h<ÑgËÑ0. ¿⌅M⇥. Data h<±Å [2]. Data h < - Ñ GUID ∫ primary key. ↵/. ObjID G∫dfl6@¡. 1. ‡d✏N OrgID  ObjID ÜÂbh< Ñ«ô. Ch. (è/iˆ-Ñœ. h<Ñ Value0~Value500 ⇡. layout Ñy' ⌥è/iˆÑ⌅Mx. < ⇥OrgID ∫ fl 6 A 4 Name G/riˆÑ. Ñ⇡↵iˆÑA4_. n. h<@. io. _. al. ⌘⌘1ÔÂÂS(dfl6↵Ñ⇡↵. iv ÉÑœ n U. F«ô. engchi. ↵⌅M˝⇤. …0 Data. »d Data h</( Universal table layout. É⇤⌥. ⇢Ô˝Ñ⌅M˝˙>2h<±Å-. '<-∫500⇥G-fl68evè/ÂbûÂ. @Â9⁄. Force.com / ⌘⌘Ñ. v÷Ñ«⌦Ük©⌥ûÂI€⇣Êõ⌦ÔÂ(d«ô∂À↵ÂbÑû ⌥ Fields h<–õÜ⇡↵ü˝. ( Objects h<-. %„hÊ‘iˆ„_ è/iˆ HLI€ÛÊ‘Ñ ÜFieldName è/ ⇣". ⇧". 1 1. 1. ∫öÅÂbÑ. aå. Objects. ObjID ⌥ ObjName. ç)( Fields h<. Datatype è/⌅Mã%. FieldName Âbå. Å. ⌃. ÔÂ⌥fl6@8eÑûÂ-è/h<. ^ã∫UI⌅Mѯ‹«ô. OrgID  ObjID. ®. y. hÜ". (,v-/(hfl/. sit. Nat. X º(Global Unique Identifier)". ‧. ‧ 國. Fields. 學. 2.4: Objects. er. 立. 政 治 大. v-. isIndexed d⌅M/&´˙À ÕÅÑ/ FieldNum. ÔÂó0 FieldNum. :⇡↵⌅M/ Data h<- Value ˚⌫⌅MÑ,~↵⌅M ⌅MÔÂ✏N Fields h<å⇣è/0Ê‘ÑI€⇥ 10. É⇧+. )(. FieldNum (Üh. @ÂûÂ-@. è/.
(25) 2.5: Data. 2.3.2. Indexes. Indexes h<±Å [2]. Uniquefields h<. @⌃« 治 政 ôÊõã%Ñ ‡d!’(‹o✏«ô´" 大 Ñü˝Ü† Âb⇥Force.com y%˙ÀÜ Indexes 立 ⌥ UniqueFields ⇡i↵«ôhÜì•(‹o✏«ô´Ñ 1º Data «ôh-@›XÑfl6«ôÑ Value ⌅M-˝/(W2. Indexes ⌥ UniqueFields «ôhgË⌅M˝/. UniqueFields «ôhv-" ѱÅ⇥. </. ÔÕ⌥Ñ. @Â. i⇧ÑÓ%(. 2.5ÍU:Ü Indexes h<. FieldNum  GUID ⇡€. y. Nat. Indexes ⌥ UniqueFields «ôh( OrgID ObjID ÑÍ↵ Object. FieldNum ⌅MG⌥ Fields «ôh-Ñ FieldNum (✏. io. ;uÜ›. sit. '⇥OrgID „h∫Í↵fl6. ObjID „hdfl6. ↵⌅MZ∫⌥. er. /. #Ñ. ‧. ‧ 國. ü˝. 學. ". al. n. iv n C Force.com (2X«ôB h ⇤e n eä h i U g c Å˙À‹o✏«ô´". #. ⇠. ⌅⇡↵ Field / Data «ôh-Í↵ Value ⌅M⇥. ˝0 Indexes. UniqueFields -. vÊõÑ«ôã%Ü›X. dB. Ñ«ô⌥˝. Ñ Value ⌅M<ø⇤9⁄. Å˙À". ‡d⌘⌘(ÔÂ↵0 Indexes UniqueFields -. StringValue NumValue DateValue. . . Iw ôø⇤2X( StringValue -. ã%✏©Ñ⌅M. lº String Ñ«. lºxWÑ«ôø⇤2X( NumValue -. ùd. ^®⇥ …1⌦Ñ-. π✏. Force.com Ñ Query Optimizer (•◊0ÂbÀBB. ª$∑Âbùˆ-/&. ˙À". UniqueFields -HÂ˙ GUID. Ñ⌅M. Çú. Ñqø⇤H0 Indexes. /. ⇡B1º Indexes ⌥ UniqueFields 2X«ôÑ⌅. M˝˝ùgvÊõ«ôã%2X. øÔÂ)(‹o✏«ô´Ñ". b⇥÷ó GUID åçÛ Data «ôh÷óåtÑ«ô Ñ;u. ⇤. @Â(d⌅M2LÂb. (Âb⌦Ñ. 11. ¶. ü˝†. Â. GUID ∫ Data «ôh #_⇤◊‡º‹o✏«ô.
(26) ´". ü˝⇥. 2.3.3. Relationships h<. vfl6. Å( Join ÂbB. ObjID. Ù∫⌦Û. ì÷i↵h< A ⌥ B Ñ«ôå. 1000F«ô ÑÙ}∑. G⌘⌘≈. Çúû Data h<fà OrgID  G- A h<. Z500*1000!Ñ«ô‘. ‡d Force.com y%-. Ü. 500F«ô. B h<. ⇡(H˝⌦⇤/. ↵à'. ↵ Relationships «ôhÜ9Ñ Join Â. ¶⇥F Force.com &*(v÷á-lã Relationships «ôhÑ⌅M«⌦. b. 治 政 大 Relationships «ôh‹¬Ç↵: 立. /(vã|}Ư [11]-lãÜ Relationships «ôhÑ⌅M«⌦. ‧. ‧ 國. 學. n. Ch. Relationships h<±Å. er. io. ›. 2.6: Data. sit. y. Nat. al. 9⁄ Force.com -. engchi. D. RelationID ∫dF‹¬ÑA. ⇤⌥di⇧Ñ‹o'«ô. Relationships h<-⌅M-. ∫ OrgID ⌥ GUID ∫. v. TargetObjID ∫ÓÑh<ÑA4_. –iˆ˙À⌥v÷iˆÑ‹o'å. Relationships h<. i Un. Ñ Relationships ±Å®÷. ObjID ∫êh<ÑA4_. 4_. Data ⌥. (ÜiD⌥.  OrgID ObjID RelationID. ". vfl6 u. e0. u. ⌃%. TargetObjID ∫. D⇥ ,. D⌥. ". uÓÑ(ºì˚qÔÂ✏Nfl6A4_⌥ Data h<-Ñ GUID. ~˙dF«ô@lÑiˆÂ ⌥v÷iˆÑ‹o' ‹oÑ ObjID ⌥ TargetObjID. ‡∫Ô(ÂbPú-~0. Çdiˆ⌥⇢↵iˆ. ‹o'. ÂbPú⇤. ⇢. F«ô⇥ (,åD⌥. ". u. „h˚qÚìÂS ObjID ⌥ TargetObjID. (iˆÑ‹o'Â˙«ô GUID. ˚q⌥. •Wç)(d GUID PúªÂb Data h< 12.
(27) …dT0 Join Õ\Ñü˝. (±'œÑBìZ table scan⇥Force.com. Relationships h<Ñ…((÷á [2]⌥}Ư [11]˝* Join ÊõÔL. ≈. ⇡õ⌅MvÊ/. Ñ/êiˆÑ GUID ë (Z Join BÕ6≈. ÕK¶6⇥vå. FÊõ⌦⌘⌘Õ6. M2Lè/ùˆÑWI⇥,vÂ⌦«ô∂ÀÑ. Ådi⇧iˆ. ÷∫˙. 2LÓ9. s0Ô. 3.1⇥. ã,3‡. 政 治 大. SQL û  立9ÎÑ-. Â⌘⌘@Â. ÓM'Ë⌃Ñ⇢fl6vWÕ(-. ÷6(ÂÄÑv-Ú. ⌅ÄSÑÕÅ'. FÕ6à⌘. –0ûÂ9ÎèG. v. Nat. n. Selection. Projection. Ch. ÷. ,' fl6. sit. y. ˚ q0. ⌥⌃êπbÑ.  ⌃ê⇡õûÂ9ÎèGÑH˝. ÓM. er. io. al. (Liao et al. [6,13]). @Â≈. Ê˛⇢fl6±Å. ÷0ûÂ9ÎèG-. pL(Pereira and Chiueh [12];Aulbach et al. [1]) ÂÂbûÂ9ÎèG⌃÷-. ↵∞ÑH'⇢fl6±Å. ‧. ⌅ÄS. ⌥⌃ê. 學. ‧ 國. 2.4. 2.5. ÷X>. ⇡M˝ìÂbûÂÑùˆ✏ÂSª Data h<-ì÷Íõ⌅. ‹oÑ/Íõ⌅M. ,. ÂÅì. GX>Ñ/êiˆÑ GUID. Ñh<Â~0‹o'«ô. û⇡õ⌅M-≈ÔÂÂSiˆ⌥iˆÑ‹o. 6. ⇠⌅ GUID ⌅M. Ñ⇥v. /ÓÑiˆÑ GUID. Éœv÷@. s0™. º. 1÷⌘ÑvPú. i U e n g c h⌥⌃ê. Join Ñ⌃÷-. / aggregation function œ/ SUM. v ni. ûÂ9ÎèGÓM–õÜ. ûÂ9Î. Œ-Õ6. Å [,. / COUNT II⇥. JSqlParser. JSqlParser /. ↵(Ü parse SQL ûÂÑ Java Library. Ñπ✏Ü✏Ω parse ˙ÜÑ SQL ûÂéd∂À ↵ Java compiler compiler π’¯vπ◆⌦K. gË)( Visitor pattern. ⇡↵ Library /)( JavaCC ⇡. Ó9Í Guido Draheim @∞ÎÑá’Ü". ãÇ )Åä≤↵. 1∫ÚÔ↵✏-. 13. (Ñ. Ñ«ôZÙ∞⇥.
(28) 2.7: JSqlParser (ƒã. 2.71ÔÂ'ÙÜ„ JSqlParser API Ñ(π’⇥. ✏N. 政 治 大. 立. Apache Jmeter. ‧ 國. 學. 2.6. Apache Jmeter / Apache fl‘˙—⇤ã|Ñ˙º Java Ñ”õ,fÂw. É. ‧. (º Web …(,fFåÜÙU0v÷,f⇠fl⇥ÉÔÂ(º,f\Kå. hII⇥Jmeter ÔÂ(º. :. h. ≤Ô. io. n. ˝/fix,f. 'P¶ÑH;'. 2.6.1. iv. Jmeter ˝. n C h∑ Ñs,ÜWI`Ñ↵✏‘fi` U i e h ngc JmeterA1(cèhT✏˙À∑ ⇥. ⇢N˙À6. Jmeter Ñy'. 1. /Ù⇢. protocol Ñ,f Web - HTTP, HTTPS SOAP FTP Database via JDBC LDAP Message-oriented middleware (MOM) via JMS 14. «ô´. iˆ!ÏË'ц. õ^%↵,f÷⌘Ñ7¶å⌃êt‘'˝⇥Ê. al. Java iˆ. y. Java Servlet CGI s,. sit. Nat. ’K«êãÇ\Káˆ. er. ´-. FTP :. &(. ”. …(↵✏Zü ÑPú⇥∫Ü.
(29) Mail - SMTP(S), POP3(S) and IMAP(S) Native commands or shell scripts TCP 2. 100%Ô˚. 'å Java Ê\. 3. o}Ñ⇢˜L“F∂A1 L“§D,f. !1⇢↵˜L“&L÷#. Ñü˝. 4. æ√Ñ GUI -. ÔÂì,f. 5. ÿ¶ÔÙE'. 立. kÎ. ".  d/. 政 治 大. ‧. ‧ 國. 學. n. er. io. sit. y. Nat. al. Ch. engchi. 15. i Un. v. /. Bì. ј.
(30) , 1º( SaaS ∞É↵ ãÑ„zπH. ∫Üìfl⇣,H ⌦Ñ⇤œ. F4®. ÜÑâh' (security). fl6ìq(«ô´/8. H˝ (performance)  v÷. †Âv„z⇥,‡¿⌥WÕ( SQL ûÂûÂ9Îè. πbÑpL_1≈ GÑ®. SQL ûÂ9ÎèG. ‡.  IO counts H˝⌦Ñó0. ( [6,13]ÑvK⌦2. 8( ÕÅÑ SQL ûÂ9ÎèG. ∫*ÜÑÂb*. vÍÔ⇥,‡¿Ñ∂ÀÔÂ⌃⇣. ↵Ë⌃⇢,. Â9ÎèG⌥Âb*. hìÑ“r‹¬Â –õåb®. 政 治 大 ⇡↵«ô∂ÀÑ-√/ 立 Data h<X>W@ Associations. ( Create table Bså⇣“e. ÅÑ˙,ö. ⇧*d. Nat. meta-data h<Üå⇣iˆ. 6. «ô. v÷Ê. Fields) X>ÔÂ(Ü–⌥H˝Ñ~↵. Relationships d^. Uniquefields). ⇡↵h<. çÓ9⇡õh<. meta-data h<. /⌅MÑ. 1. ⌅MÑ. @Â,v;Å/)(. 1I€˝x(object name transformation) ⌅M Pivot table GÔ˝⇤(«ôZû*Ó. er. io. 1I€˝x(field name transformation) ÅflW´Ó9⇥. n. al. fl6Ñ@. y. «ôã%ZÙ9. Ch. engchi. i Un. v. SQL û Â9 ÎèG⌃ ı SQL /(('Ë⌃ RDBMS ÑÂbû. ÊŸ⌦ q-. @. SQL û. ‧. ‧ 國. Pivot table(Indexes. 3.1. ã™. 學. X> meta-data Ñh<(Objects. 9B. Ë⌃!Æ. Force.com «ô∂ÀÑÓ9H–˙ûÂ9ÎèGÑv⇣. ,åË⌃›. ú. h (query optimizer) . sit. ©. e–õ⇢↵. (⇡õ‹o✏«ô´°⌃˚. ÷⌘⇤Hä SQL ûÂI€⇣I˘ÑÙE‹o✏„x (extended relational. algebra). @⇥ÑÙE‹o✏„xs/⌥≥qÑ‹o✏„xÂM. aggregation KóP܆7‹o✏„xÑhT˝õ. •W‹o✏«ô´°⌃˚q⇤. ÂÂb9 (query tree) d.«ôPÀÜhTI€åÑ‹o✏„x e⌃ê&Z*. Ñ’\⇥Ùw‘0™. b@J (query block) ÑKóP&*. 6å1ÔÂ2. ↵ SQL ÂbûÂÔÂ⌃r⇣⇢↵Â. ⇡õÂb@J\∫˙,ÆMÔÂ2. Â↵bÑ⇡↵ SQL ûÂ∫ã⇢. 16. ÑKóPÇ. eI€⇣‹o✏„x.
(31) SELECT Lname, Fname FROM EMPLOYEE WHERE Salary > (SELECT MAX(Salary) FROM EMPLOYEE WHERE Dno=5); ÔÂ⌃r⇣i↵Âb@J. ↵/gËÂb@J.  b5_ Ë Ä -. ÿÑ™. «⇢ (SELECT MAX(Salary) FROM EMPLOYEE WHERE Dno=5); Ê. ↵∫. 政 治 ™«'ºgËÂb@J@óÑ<ѷ” 大. ËÂb@J. Âb@. 立. ⇢. FROM EMPLOYEE WHERE Salary > c. I€⇣‹o✏„xÑhT✏. al. n ËÂb@J:. Ch. ñH˝d✏3.1ÑgLW„x✏ &. @x˙ÜÑ«ô-÷˙™«<∫. < cÑ⇧Ó. EE). W˜L. (3.1). EE)). v. Salary>c (EM P LOY. 1dÄ 'Ñ«ô. (3.2). EE)). EE) û EMPLOYEE ‹¬-x. ËLW˝dÂbÑPú ç˝d✏3.2Ñ,. û EMPLOYEE ‹¬-x÷@. &. gLWû. dLW„x✏ Salary 'º–↵. ,ådLWçû⇡õ«ô-÷˙Lname  Fname ⌅MÑ«ô. N⇡.\’Âb* ˙. i Un. Dno=5 (EM P LOY. Dno ∫5Ñ⇧Ó(item). Salary>c (EM P LOY. Dno=5 (EM P LOY. engchi. ⇡Lname,F name (. ÷@. sit. =M AX(Salary) (. er. io.  . gËÂb@J:. y. Nat. •W⌘⌘äÂb@J. ‧. ‧ 國. 學. SELECT Lname, Fname. h (query optimizer) 1ÔÂ⌃ê‹o✏„xhT✏. ✏. &6ö. k (execution plan) Üó0Ù}ÑÂbH˝⇥. 9⁄ Force.com Ñ÷á [2] }Ư [11]Ê˛Ü Join Õ\Ñ\’œ. åt. ↵«ô∂À. Ù(U⌃ Join Õ\⌦⇤ 17. 6. v-. /Ù. H˝⌦ÑpL. @Â.
(32) 政 治 大 3.1: «ô∂À. 立. ‧ 國. • data h<X>Ñ/@. õÓ9. °( Universal Table layout œ↵⌅M. ‧. fl6Ñ«ô. @„hÑ✏©ÇÇ↵⇢. sit. y. Nat. er. io. dataGuid - «ôhflA4_. al. n. tenantId - fl6ÑA4_. i n C U hengchi objectId - Ê‘iˆÑA4_ name - è/iˆ. v. 1. value0,value1,... - œ. F«ôÑœ. • Indexes h<X>Ñ/@ index «ô. 3.1@:⇢. Ç. 學. ,v(÷⌘@–˙Ñ«ô∂À⌦Z˙. ↵⌅M@„hÑ<. Ñfl6◊↵œ. ↵è/iˆ(logical object)Ñ@. œ↵⌅M@„hÑ✏©Ç↵⇢. tenantId - fl6ÑA4_ objectId - Ê‘iˆÑA4_ fieldNum - dè/⌅M(dè/iˆÑ dataGuid - «ôhflA4_ stringValue - W2Ñ< 18. F«ô-T,~↵⌅M.
(33) • Uniquefields h<X>Ñ/@ @. Ñ primary key «ô. (d. ↵è/iˆ(logical object)Ñ. Ñfl6◊↵œ. œ↵⌅M@„hÑ✏©⌥⌦Ñ Indexes h<¯. ç⇧⇥. • Objects h<X>Ñ/@. fl6◊↵Ñœ. ↵è/iˆ(logical object)«ô. œ↵⌅M@„hÑ✏©Ç↵⇢ objectId - Ê‘iˆÑA4_ tenantId - fl6ÑA4_ objectName - è/iˆÑ. 1. 治 ↵è/iˆ(logical object)Ñœ 政fl6◊↵Ñœ 大. • Fields h<X>Ñ/@ ↵⌅M«ô. 立. œ↵⌅M@„hÑ✏©Ç↵⇢. ‧ 國. 學. fieldsId - ⌅MÑA4_ tenantId - fl6ÑA4_. ‧. objectId - Ê‘iˆÑA4_. y. 1. sit. Nat. fieldName - è/⌅MÑ. al. n. fieldNum - dè/⌅M(dè/iˆÑ. C isIndex - /&∫ indexh. engchi. • Associations h<X>Ñ/@. er. io. fieldType - è/⌅MÑã%. i Un. fl6Ñœ. v. F«ô-T,~↵⌅M. ↵è/iˆ⌥v÷iˆÑ‹¬«. ôÑ meta-data œ↵⌅M@„hÑ✏©Ç↵⇢ associationId - ‹¬meta-dataÑA4_ tenantId - fl6ÑA4_ sourceObjectId - êÊ‘iˆÑA4_ targetObjectId - ÓÑÊ‘iˆÑA4_ sourceFieldId - êÊ‘⌅MÑA4_ targetFieldId - ÓÑÊ‘⌅MÑA4_ sourceFieldNum - êÊ‘⌅MÑMn targetFieldNum - ÓÑÊ‘⌅MÑMn 19.
(34) • Relationships h<X>Ñ/@. fl6◊↵Ñœ. F«ôÑ DataGuid ⌥v÷. «ôÑ DataGuid œ↵⌅M@„hÑ✏©Ç↵⇢. ‹¬Ñ@. relationId - ‹¬ÑA4_ associationId - ‹¬meta-dataÑA4_ tenantId - fl6ÑA4_ sourceDataGuid - ê«ôhflA4_ targetDataGuid - ÓÑ«ôhflA4_ sourceObjectId - êÊ‘iˆÑA4_. 政 治 大. targetObjectId - ÓÑÊ‘iˆÑA4_. 立. F˙ „hdè/iˆÑ@. è/⌅M. EMPLOYEE F˙ ∫ EMPLOYEE h<↵Ñ@ Ñ–. Ñ⌅MÑ∆. ⇠ f ield (EM P LOY EE, f˙, 667) /. ↵è/⌅M. f˙ „h F˙ ∆. Nat. …0. er. y. ZÜ field name transformation 1v⇣Úì(MbM¥Ü value. ∫Üπ. sit. øh. io. ä(è/iˆ EMPLOYEE -Ñf˙ ⌅MI€⇣Ê‘⌅MMn. al. n. ÇdägËÂb@J√⇤✏3.19Î⇣⇢. =M AX(Salary)  . -. ↵ field name transformation. Ñ˝x. Data h<Ñ⌅M. ‰. (dãP-è/iˆ∫. ‧. ‧ 國. TenantId ∫667. ⇢fl6Ñ∞É @Ê˛Ñ Force.com «ô∂À. M. 學. ç⇤n⌦bÑãP. Ch. engchi. i Un. v. Dno=5 (⇢F˙ ⇡{⇠ f ield (EM P LOY EE,f˙,667)|f˙2F˙ } (Data)). (3.3). ËÂb@J√⇤ ✏3.29Î⇣⇢ ⇡Lname,F name (. KM@ ôZ2. Salary>c (⇢F˙ ⇡{⇠ f ield (EM P LOY EE,f˙,667)|f˙2F˙ } (Data))). Ó%(ug Force.com «ô∂À⌘⌘. eÕ\. ˝ d ✏3.3Ñ. EMPLOYEE ‹¬-@ I€⇣Ê‘⌅MMn. Åû Data h<-÷˙«. g L W ⇡{⇠f ield (EM P LOY EE,f˙,667)|f˙2F˙ } (Data) ⌥. Ñè/⌅M✏N ⇠ f ield field name transformation ˝xÜ. _1/™⌘⌘1ÔÂ( Data h<-÷ó⇡õ⌅MÑ«. ô. &✏N rename operator(⇢). ∫. …Ñè/⌅M. ⌥÷óÑ´B«ôÑ@. ®0⇡·1ÔÂ⌥. Dno=5 Ñ«ô÷˙. (3.4). Ê‘⌅MÕ∞}. ãÀÑÈxùˆ>2„x✏-. &~˙™«<∫ 20. s⌥&. 'Ñ«ô1ÔÂå⇣✏3.3(⇢fl6Ñ.
(35) ≈¡↵ÑI˘Õ\ Salary>c ~˙&. #0. ˝d3.4⌥Õ∞}. åÑ«ô1ÔÂ9⁄KMùˆ. ç✏N projection operator Èx˙⌘⌘ÛÅÑ⌅. ùˆÑ«ô. M«ô⇥ (•↵ÜÑ✏¿®. -. ⌘⌘⇤H. ûÂ9ÎèG2Ls0Ñ. 3.2 (®. e˙,ö©. ç. œ. ↵. Ç⌥è/iˆ. 1. ÷⇥. ûÂ9ÎèGÑ˙,ö© ûÂ9ÎèGÑN↵-⇤. I€⇣Ê‘iˆ. 1. ∑(0. 立. 政 治 1I€⇣Ê‘⌅M 大. y. ‧ 國.  ⇠ object ⇠ K. n. er. io. al. ⌥è/iˆ. sit. ⇡↵˝xÑÓÑ/. i Un. v. ⌃ % / LObjName s è / i ˆ. Ch  fl 6 A 4 _(TenantId) 1(physicla object name) º ⇡<od> b. )(⇡i↵˙,ö©⌥ÔÂ(à⇢0π. 1I€˝x (Object Name Transformation). 1ÑÕ\ÂÙæ!Ñπ✏ÜhT Åi↵8e. (,v-⌘⌘|. ìÕfiÇıÑ≥TÙ†⇧Z⇥. Nat. 1@:. 1. ‧. iˆ. ÑKó. 學. MNhTÑ⌥‹↵¶. 3.2.1. õq. /è/⌅M. åø( Liao et al. 2012 [6]⌦Ñi↵ö©. Ç. πøKåÑ®. eón0g c ↵ h i8 ˙. ( ✏3.6- 2. 1I€⇣Ê‘iˆ 1 ✏3.5ö © ⇡ ↵ ˝ x. 1(logical object name). PObjName s / Ê ‘ i ˆ Ñ. eö©iˆ. 1I€˝xvÊI˘. b (Objects)⇢ oc n=n td=t. ⇠ object ⇢ LObjName ⇥ T enantId ! P ObjN ame ⇠ object (n, t) ⌘ ⇡<od> b. 9⁄ Objects h<ѱÅ⇢. b (Objects) oc n=n td=t. b td, b oc Objects(od, n). fö tenantId  objectName. 1ÔÂû Objects h<-~˙¯. tId⇥ 21. (3.5) (3.6). …Ñ objec-.
(36) ↵ãP. ↵è/iˆ EMPLOYEE. Çú )fl6667. 1ÔÂó0dè/iˆ. …ÑÊ‘iˆ. ÍÅÎ⇣↵bÑ˝x. 1⇢. ⇠ object (EM P LOY EE, 667). 3.2.2. 1I€˝x (Field Name Transformation). ⌅M. d˝xÑÓÑ π✏ÜhT. ⌥è/⌅M  ⇠ f ield ⇠ K. 1I€⇣Ê‘⌅M. 1 ✏3.7ö © ⇡ ↵ ˝ x. 1ÑÕ\ÂÙæ!Ñ Å. ↵8e. ⌃%/. LFieldName s è / ⌅ M 治 政 1(logical field name) fl6A4_(TenantId) 大 ó0 ↵8˙ PFieldName _ 立 field name) 0✏3.8-2 eö© ⇠ ˝xI˘º 1/Ê‘⌅M 1(physical LObjName s è / i ˆ. 1(logical object name). f ield. ⇠ f ield ⇢ LObjName⇥LFieldName⇥TenantId!PFieldName. (3.8). object (n ,t) (F ields)) b b td=t od=⇠ o. er. io. 9⁄ Fields h<±ÅÑö©⇢. fc a=nf. sit. Nat. ⇠ f ield (no , nf , t) ⌘ ⇡fcu (. (3.7). y. object (n ,t) (F ields)⇢ b b od=⇠ fc a=n td=t o. ‧. ‧ 國. 學. ⇡fcu (. al. n. iv n C b od, F ields(fcd,h td, ype, fcu, indexT ype) ebnfc hi U ga,cf ieldT. fö objectId tenantId fieldName û Fields h<-÷˙& …Ñ fieldNum. ˙¯. ⇡·≈. è/iˆ-T⁄,~↵⌅M. @ÂÇú. Ë✏. fieldNum @„hÑ✏©/d. 1ÔÂ~ è/⌅M(. 9⁄ Data h<±Å⇢. b od, b n, value0, value1, value2, . . .) Data(b g , td,. Å( Data h<-÷ód. ⌅MÑ«ô. Î⇣ value ] fieldNum Ñb✏MÔÂx0&. value?. ùˆ⇧. ↵ãP. (⌅MùˆÑhT⌦≈. Ê‘⌅M( Data h<-Ñ. ✏3.4@⇡value]{⇠f ield (EM P LOY EE,f˙,667|f˙2F˙ } (Data). M. 1I€˝x⌥fl6667Ñè/iˆ EMPLOYEE @. M. 2•KóP ] (Mb†⌦M¥W value 22. 1. )(Ê‘⌅. ⌅MI€⇣Ê‘Ñ⌅. 1Ô0 Datah<-÷ó@Å⌅MÑ.
(37) «ô⇥. 3.3. INSERT INTO ûÂ9ÎèG ⌘⌘⇤H)(‹o✏„xhT⌅↵ SQL ûÂ. (⇤n⇢fl6↵Ñ9ÎèGM ç↵0⇢fl6Ñ≈¡9ÎèG⇤ ±Å. ⇣Íõä. 1(Ñ/(,凯‹v-@ , SQL INSERT INTO Ñû’. ,⇥ ùg. INSERT INTO "h<. ÅË✏Ñ/⇡·–0Ñ«ôh ÷Ñ Force.com Ñ«ôh±ÅÊ˛H ö©Ç↵⇢. " ("⌅M1", "⌅M2", ...). 政 治 大. VALUES ("<1", "<2", ...). @⇥Ñ INSERT INTO 1/(‚. 學. F«ô. Â↵Ñ‹o✏„xhT✏˝/9⁄⌦Ñû’∫˙. R @„hÑ/. E @„hÑ/Å´ INSERT Ñ. ↵‹¬. ↵è/⌅MÑ<⇥‡d2. n. C hE. R (✏3.10-. (3.9) eÔÂh:⇣⇢. er. io. al. ⇥‰. F«ô@À⇣Ñ‹¬⇢. sit. Nat. a1 , a2 , a3 , . . . , an „h⇡F«ôœ. ↵ª®. ‧. R[E. R. Ñh<-“. y. e. 立. ⇤n^⇢fl6Ñ≈¡. ‧ 國. v!. i Un. v. {(a1 , a2 , a3 , . . . , an )}. (3.10). R [ E ⌘ R [ {(a1 , a2 , a3 , . . . , an )}. (3.11). engchi. ↵✏Ï_@⇧Ñ@. ⇧Ó„h. ↵ row. ⇡.Í⇧+Æ. FÑ. ‹¬»Î8x‹¬ (constant relation)⇥ ⇤n⇢fl6≈¡↵Ñ9ÎèG. ÷0⌥è/⌅M (logical. dÜ(KM,å✏¿@. field)  è/iˆ (logical object) ⌃%I€⇣Ê‘⌅M (physical field) Â Ê ‘iˆ (physical object) <. ⌘⌘Ñ≈. @Â(^⇢fl6Ñ≈¡↵®. ÅM. ˙Ñ✏PÔ˝Í/. (⇢fl6Ñ≈¡↵1⇤äó⌥‹1⇢ π✏. Â↵⇤®. ˙. ⇤n⇡↵ SQL Õ\⇤qˇ0Íõh. ,✏çÂ. õË⌃Ñ≈. iù1ÔÂhT˙Ü. ↵Ê\ÑB⇡/°÷U.. õÊõÑãPZ™. Ñ9Î⇤9⁄fl6@8eÑ SQL ûÂ. ⇥INSERT INTO ûÂ. ⌃êœ↵⌅MÑ<&. ⇧v÷Ô˝Ó90Ñh<±Å⌃%“e⇥ 23. F. …0 Datah<.
(38) 3.3.1. Data h< G - fl 6667Û Å ( ÷ Ñ è / i ˆ EMPLOYEE - ∞ û. ↵ãP. EMPLOYEE Ñ«ôh±ÅÇ↵. ô Ë_. name „h·Â. W. v- sn „h·ÂË_. birthday „h·Â. Â. F«. Mgr_sn „h;°. sex „h·Â'%. address. „h·Â⇢⌦O@⇢. 政 治 大. (3.12). EM P LOY EE(sn, M gr_sn, name, birthday, sex, address) ˛(Å“e. 立 (6666, 8888,. F«ô. ‧ 國. ,✏⌥9ÎèG9Î⇣⇢. {(0x110ae, 667, ⇠ object (EM P LOY EE, 667), EM P LOY EE,. ‧. ã,700101,7,⌫⇥ooÔxx_)}. 6666, 8888,. io. al. D [ E. sit. y. Nat. D. er. E. Ñ. dataGuid. 學. G-∫0x110ae (⌦b@®. ã,700101,7,⌫⇥ooÔxx_). n. iv n C h e n g c h3.11 ,✏G^<⌦b@® ˙Ñ✏3.10 i U‰ E ∫. sÔÂå⇣ INSERT INTO ûÂ( Data h<@ZÑ9Î⇥ ±Å¯. kï⁄⇧∫ primary key s⇢. t ∫fl6ÑA4_ 1 <. ↵´X‹¬⌥ Data h<. b od, b n, value0, value1, value2, . . .) Data(b g , td, ⇠ object (n, t) ∫ iˆ. 1I€˝x. ååb@•Ñ a1 , a2 , a3 , . . . , an G∫“eÑ D „h Data h< E. v- n ∫è/iˆ. F«ôœ. ↵⌅M@. …0Ñ. ⇢ {(dataGuid, t, xio, n, a1 , a2 , a3 , . . . , an )}. (3.13). D [E. (3.14). D. 24.
(39) Ê. ÅË✏Ñ/ dataGuid ⌥Ñ/h<-ÑA4_. ÷zºÊõ⌦Ê\B@°÷ÑVe. Í/⇡↵A4_Ñ". ,Ü™ÔÂ⌃⇣i.. _. 1/™œ↵A4_(t↵«ô´-//. ✏. /œ↵A4_Í(h<-/. ⇧. ‡∫Çú(⌘⌘÷Ñ«ô-⇧ÏÜ primary key. ÔÂH0 Uniquefields + dataGuid. X(. X(Ñ. Ê. (⇡«÷á-. / Indexes h<-÷˙&. ./hflA4 ./@ËA4_. ⌘⌘°÷ÑVe/M. ⇧/ index Ñq ùˆÑ«ô. ç)(⇡õ dataGuid 0 Data h<-÷˙⌘⌘. ⇡.\’ÔÂ(Ü–⌥⌘⌘X÷«ôÑH˝. π✏. ⌘⌘1. ⇡õ«ô-⇧ åÛÅÑPú. ,åù✏P)(‹o✏„x-Ño. ∆(*)Kó⌥i↵‹¬fo∆wÜ⇥. ô. 學. ↵ãP. ‧ 國. 3.3.2. 政 治 大 Uniquefields h< 立 G - fl 6667Û Å ( ÷ Ñ è / i ˆ EMPLOYEE - ∞ û. EMPLOYEE Ñ«ôh±Å|å*(Mã. kï⁄⇧„h primary. ‧. key⇢. \Ó9. F«. y. sit. (6666, 8888,. ã,700101,7,⌫⇥ooÔxx_). er. G-∫0x110ae. F«ô. io. ˛(Å“e. Nat. EM P LOY EE(sn, M gr_sn, name, birthday, sex, address). ‡∫ Uniquefields h<(∫ pivot table layout. al. @Â. dataGuid ÅÂS. n. i v (dG- sn  Mgr_sn Ñ n primary key Ñã%∫U CMÔÂke …Ñ⌅M hengchi U ã%˝/ string (⌦b@® xio xif 1 E1 xif 2 E2. Ñ. ,✏⌥9ÎèG9Î⇣⇢. ⇠ object (EM P LOY EE, 667) ⇠ f ield (EM P LOY EE, sn, 667) {(0x110ae, 667, xio, xif 1, 6666)}. ⇠ f ield (EM P LOY EE, M gr_sn, 667) {(0x110ae, 667, xio, xif 2, 8888)} U. U [ E1 [ E 2. 25.
(40) sÔÂå⇣ INSERT INTO ûÂ( Uniquefields h<-@ZÑ9Î⇥ ,✏. ⌦@. 1∫ f. (d⌘⌘HG-. (⇡↵è/iˆ-. primary key Ñè/⌅M. ‰ E ∫´X‹¬⌥ Uniquefields h<±Å¯. kï⁄⇧∫ primary. key s b od, b fcu, stringV alue) U niquef ields(b g , td,. t „hfl6ÑA4_. ⇠ object (n, t) ∫ i ˆ. ⇠ f ield (n, f, t) ∫ ⌅ M. 1. E. ,✏ÔÂÎ⇣⇢. {(dataGuid, 治 t, xio, xif , v )} 政 大. (3.16). U [E. ç)(o∆(*)Kóäi↵‹¬fo∆wÜ. primary key Ñq. £º⌘⌘Ñ„x✏1≈. öÍ⇤. ↵. ãÇ⌘⌘. ↵. 9Î⇣⇢. sit. {(dataGuid, t, xio, ⇠ f ield (n, f1 , t), v1 )}. E1. io. n. al. er. Nat. y. B⇡⌘⌘Ñ primary key. (d≈. ‧. ÅË✏Ñ/. E2 E3. åçä⇡. (3.15). 1. U. ó0 E ´X‹¬KPúå. v1 G „. 學. ‧ 國. 立. dataGuid ∫ h fl A 4 _. 1I€˝x. hstringValue U „h Uniquefieldsh<. v- n ∫è/iˆ. 1I€˝x. i Un. v. {(dataGuid, t, xio, ⇠ f ield (n, f2 , t), v2 )}. Ch. engchi. {(dataGuid, t, xio, ⇠ f ield (n, f3 , t), v3 )}. ù✏P⌥Uo∆wÜsÔ. ⌥MbÑ✏PS⌃/¯. Ñ. @ÂÅ↵. Êõ⌦Ñè/iˆ/ÇUÊ\Üzöd„x✏K9Î⇥. 3.3.3. Indexes h<. ↵ãP ô. G - fl 6667Û Å ( ÷ Ñ è / i ˆ EMPLOYEE - ∞ û. EMPLOYEE Ñ«ôh±Å|å*(Mã. \Ó9. kï⁄⇧„h primary. key ó‘W„h index⇢ EM P LOY EE(sn, M gr_sn, name, birthday, sex, address). 26. F«.
(41) ˛(Å“e. F«ô(6666, 8888,. ã,700101,7,⌫⇥ooÔxx_). dataGuid G. -∫0x110ae. ‡∫ Indexes h<(∫ pivot table layout @ÂÑ. ÅÂS index. Ñã%∫U. MÔÂke. b@®. ,✏⌥9ÎèG9Î⇣⇢. Ñ. …Ñ⌅M. (dG- name Ñã%∫ string. (⌦. ⇠ object (EM P LOY EE, 667). xio. ⇠ f ield (EM P LOY EE, name, 667). xif. ã)}. {(0x110ae, 667, xio, xif,. E. 政 I 治I [ E大. 立. sÔÂå⇣ INSERT INTO ûÂ( Indexes h<-@ZÑ9Î⇥ ‰ E ∫´B‹¬⌥ Indexes h<±Å¯. G- index Ñè/⌅. s⇢. ‧. ⇠ object (n, t) ∫ iˆ. 1I€˝x. io. t „hfl6ÑA4_. sit. y. Nat. b od, b fcu, stringV alue) Indexes(b g , td,. n ∫è/iˆ. ⇠ f ield (n, f, t). er. 1∫ f. ‧ 國. M. \’⌦⌥,åfiÑ primary key ^<. 學. ( Indexes h<-. ∫hflA4_ a lf ∫ index Ñ fieldName dataGuid v •Ñ v1 G„h stringValue n i @ ,✏® Ç↵⇢ C hI „hIndexes h< U engchi 1I€˝x. n. ∫⌅M. E. {(dataGuid, t, xio, xif , v1 )}. (3.17). I [E. (3.18). I ó0 E K´X‹¬Pú. ç(o∆(*)Kóäi↵‹¬fo∆wÜ. Index ⌥ primary key ÑOL ↵ index Ñq. åb@. #. £º„x✏1≈. Ô˝G0i↵ index Â⌦Ñ¿¡ 9Î⇣⇢. E1. {(dataGuid, t, xio, ⇠ f ield (n, f1 , t), v1 )}. E2. {(dataGuid, t, xio, ⇠ f ield (n, f2 , t), v2 )}. E3. {(dataGuid, t, xio, ⇠ f ield (n, f3 , t), v3 )} 27. (⇡·. G-⌘⌘.
(42) åçä⇡. å9ÎåÑ‹o„x✏≈. ö⇥. Relationships h<. ( Relationships h<-“e H. @ÂÊõ⌦. Ñè/iˆ@⇧+Ñ index xÓ. ñ@-. 3.3.4. ù✏Po∆(*)wÜsÔ. ‰ E ∫⌥ Relationships ±Å¯. Ñ´X‹¬. ñ. ∫Ü✏PÑ!T. G. ÅH~˙ sourceObjectId  targetObjectId⇢ ⇠ object (EM P LOY EE, 667). xiso xito. 立. 治 M EN T, 667) 政 (DEP ART大 ⇠ object. 學. ‧ 國. ç0 Associations h<‹o«ôÑ meta-data ~˙A4_ - sourceFieldNum=p targetFieldNum=k⇢. y. b d c d d td=667^ sod=xiso^ tod=xito^ sf u=p^tf u=k (Associations). io. sit. {(AID + 1, 0x110ae, 0x2343f, 667, xiso, xito)}. E. R[E. R. n. al. er. Nat. ⇡ad c. ‧. AID. ni Ch sÔå⇣ Relationships h<ÑÕ\⇥ U engchi •↵Ü/. ,✏ÑË˝. Çúfl6ÛÅ(vè/iˆ-∞û«ô. ⇡F«ô-. ( Relationships h<-∞û. F⇡↵iF. ‰ E ∫´X‹¬⌥ Relationships «ôh±Å¯. kï⁄⇧. Ñ⌅M√⇤Üv÷Ñè/iˆ «ôÑ‹o⇠⌅. v. G≈. ∫ primary key s⇢. t „hfl6A4_ ⇠ object (n, t) ∫iˆ. c sg, b sod, c tod) c b ad, b td, b tg, Relationships(rd,. =COU N T „h aggregation function -Ñ COUNT ˝x 1I€˝x. 1. ÓÑiˆ(target object). M. 1I€˝x. flA4_. 1. sf ∫êè/⌅M. n1 , n2 ⌃%„hêiˆ(source object). v-. sf u = ⇠ f ield (n, sf, t) 1. tg ∫ÓÑ«ôhflA4_. tf u = ⇠ f ield (n, tf, t) ∫⌅. tf ∫ÓÑè/⌅M. 1. sg ∫ê«ôh. aid ∫dF«ôÑ AssociationId G 28. ,✏.
(43) ®. Ç↵⇢ E. {(aid, sg, tg, t, sf u, tf u)}. (3.19). R[E. (3.20). R. 3.3.5. !“e⇢F«ô Ñ INSERT INTO û Â9 Î. dÜ⌦Ñ. vÊ INSERT INTO Ñ. ÷K. Ê. .û’ÔÂ. !“e⇢F«. ô⇢ INSERT INTO "h<1" ("⌅M1", "⌅M2", ...). 政 治 大. SELECT "⌅M3", "⌅M4", .... 立. …ÑÓ9sÔ⇥. sit. ‧ 國. EMPLOYEE Ñ«ôh±Å|å*(Mã. n. al. key⇢. ˛(“eÑ. er. G-fl6667ÛÅ(÷Ñè/iˆ EMPLOYEE -. io. ô. ‧. Nat. ↵ãP. Åä✏3.10 fÓ9⇣⇢. {(a1 , a2 , a3 , . . . , an ), (b1 , b2 , b3 , . . . , bn ), . . .}. E. v⇠Ñ✏PçZ¯. Í. 學. F˙,S⌃⌥Mb@¯. y. FROM "h<2". \Ó9 v i. !∞û. F«. kï⁄⇧∫ primary. n U i e h n g c name, birthday, sex, address) EM P LOY EE(sn, M gr_sn,. Ch. F«ô⌃%∫(6666, 8888,. G-∫0x22222. ã,700101,7,⌫⇥ooÔxx_). (7777, 8888, ✏h,720202,7,)ç↵). (7779, 8888, è∑,730303,7,k ®. Å⌃%›. Data h<. Î. ⇡·≈⌫˙. åÑ®. ). dataGuid. dataGuid G-∫0x33333. dataGuid G-∫0x55555. •↵Ü1œKMÑ. Uniquefields h< Indexes h<ªZèGÑ9. Pú. S⌃. ç⇧⇢. T ⌘ EM P LOY EE xio xif 1. ⇠ object (T, 667) ⇠ f ield (T, sn, 667). 29.
(44) ⇠ f ield (T, M gr_sn, 667). xif 2. 1. Data h< E1. {(0x22222, 667, xio, T, 6666, 8888,. ã,700101,7,⌫⇥ooÔxx_)}. {(0x33333, 667, xio, T, 7777, 8888, ✏h, 720202, 7,)ç↵)}. E2. {(0x55555, 667, xio, T, 7779, 8888, è∑,730303,7,k. E3. D [ E1 [ E 2 [ E 3. D. 政 治 大. 2. Uniquefields h<. {(0x22222, 667, xio, xif 2, 8888)}. E21. {(0x33333, 667, xio, xif 1, 7777)}. E22. {(0x33333, 667, xio, xif 2, 8888)}. E31. {(0x55555, 667, xio, xif 1, 7779)}. E32. {(0x55555, 667, xio, xif 2, 8888)}. n. E12. I. Ch. y. sit. er. io. {(0x22222, 667, xio, xif 1, 6666)}. ‧. Nat. al. 11. 學. ‧ 國. 立E. )}. engchi. i Un. v. I [ E11 [ E12 [ E21 [ E22 [ E31 [ E32. 3. Indexes h< (!). 3.4 ñH F«ô. DELETE ûÂ9Îè G ⇤n^⇢fl6Ñ≈¡. @⇥Ñ DELETE 1/û‚. ‹o✏„xÜhTÑq⇢. E. p (Data). 30. Ñ«ôh<-*d.
相關文件
Using MS Access to design database, learning SQL commands and create forms and
Because simultaneous localization, mapping and moving object tracking is a more general process based on the integration of SLAM and moving object tracking, it inherits the
• One of the main problems of using pre-trained word embeddings is that they are unable to deal with out-of- vocabulary (OOV) words, i.e.. words that have not been seen
this: a Sub-type reference variable pointing to the object itself super: a Base-type reference variable pointing to the object itself. same reference value, different type
Type case as pattern matching on values Type safe dynamic value (existential types).. How can we
• 訓練課程之設計格式，請參用 本分署規範之課程申請相關表件-學、術科
• Instead of uploading and downloading the dat a from cloud to client for computing , we shou ld directly computing on the cloud ( public syst em ) to save data transferring time.
溫度轉換 自行設計 溫度轉換 自行設計 統計程式 簡單 簡單 統計程式.
