EC-CUBE3のデータベース構造のお話

Transcript

1. EC-CUBE3ͷ σʔλϕʔεߏ଄ͷ͓࿩ EC-CUBEؔ੢Ϣʔβάϧʔϓ 2015-11-04
2. ࣗݾ঺հ גࣜձࣾΩϡʔϧ ɹࢁଜ ਗ਼࢙ γεςϜΤϯδχΞ EC-CUBE3ͷίϛολʔ(@k-yamamura) ͯ͠·͢
3. αϚϦʔ 1.৽͘͠ͳͬͨDBεΩʔϚ 2.EC-CUBE3ʹͳΓ࡟আ͞Εͨςʔϒϧ 3.PostgreSQLͱMySQLͲͪΒΛ࢖͑͹ྑ͍ͷ? 4.EC-CUBE3Ξοϓσʔτ࣌ͷDB·ΘΓͷ஫ҙ఺ 5.QAͳͲ
4. 1.৽͘͠ͳͬͨDBεΩʔϚ
5. ৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2͔ΒEC-CUBE3ͷDBεΩʔϚͷݟ௚͠ʹ͋ͨΓORMͰѻ͍΍ ͍͢ܗࣜʹมߋ • EC-CUBE2͔ΒҎԼͷ಺༰Λݟ௚͠(v2.13Λର৅)
 ςʔϒϧ໊͸໋໊ن໿ΛఆΊͯमਖ਼Λ࣮ࢪ
 ར༻͍ͯ͠ͳ͍ςʔϒϧ/ΧϥϜͷ࡟আ
 ֎෦Ωʔͷ௥Ճ
 ϓϥάΠϯͰར༻͢Δςʔϒϧ͸ຊମ͔Β෼཭ • ෳ߹ओΩʔ͸ར༻ͤͣɺαϩήʔτΩʔΛར༻(ଟରଟ͸আ͘) • ͨͩ͠ɺEC-CUBE2ܥͷΧϥϜ໊শɺσʔλܕ͸ݪଇมߋ͠ͳ͍
6. ৽͘͠ͳͬͨDBεΩʔϚ • ໋໊ن໿ͷϧʔϧ
 ল໊ུশͷഇࢭ : dtb_deliv → dtb_delivery
 ୯਺ܗʹมߋ : dtb_products → dtb_product
 εωʔΫέʔεͰ໋໊ : dtb_class_category • ςʔϒϧʹ͸छผʹΑΓϓϨϑΟΫεΛ͚ͭΔ
 Ϛελςʔϒϧ : mtb_hogehoge
 σʔλςʔϒϧ : dtb_hogehoge
 ϓϥάΠϯؔ࿈ : plg_hogehoge
7. ৽͘͠ͳͬͨDBεΩʔϚ • ֎෦Ωʔͷ௥Ճ
 dtb_productɹɹdtb_product_class • ϓϥάΠϯ͔Βຊମͷඪ४ςʔϒϧʹରͯ͠Χελ ϚΠζ͸ඇਪ঑
 όʔδϣϯΞοϓ͕ࠔ೉ɺଞͷϓϥάΠϯͱͷڝ߹ • ෳ߹ओΩʔͰ͸ͳ͘αϩήʔτΩʔΛར༻
 ORMͰѻ͍΍͘͢͢ΔͨΊαϩήʔτΩʔΛར༻
8. ৽͘͠ͳͬͨDBεΩʔϚ • 3ܥͷERਤ͸ҎԼͷURLΛࢀর
 https://github.com/EC-CUBE/eccube3-doc/tree/ master/ER-D • 2ܥͷERਤ(όʔδϣϯ͸ݹ͍Ͱ͕͢)΋ҎԼͷ URLͷPDFϑΝΠϧΛࢀর
 https://github.com/EC-CUBE/eccube3-doc
 →ςʔϒϧఆٛॻ͸ҰԠ࠷৽
9. ৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2Ͱͷ঎඼ؔ࿈৘ใ
10. ৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE3Ͱͷ঎඼ؔ࿈৘ใ
11. ৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2Ͱͷड஫ؔ࿈৘ใ
12. ৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE3Ͱͷड஫ؔ࿈৘ใ
13. ৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2Ͱͷސ٬ؔ࿈৘ใ
14. ৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE3Ͱͷސ٬ؔ࿈৘ใ
15. 2.EC-CUBE3ʹͳΓ࡟আ͞Εͨ ςʔϒϧ
16. EC-CUBE3ʹͳΓ࡟আ͞Εͨςʔϒϧ • EC-CUBE3΁όʔδϣϯΞοϓ͢Δʹ͋ͨΓػ ೳͷݟ௚͕͠ߦΘΕͨͨΊɺςʔϒϧ΋ෆཁ ͱͳΓ࡟আ • 3ܥͰ৽ͨʹ৽ن࡞੒͞Εͨςʔϒϧ΋ଘࡏ͢ Δ͕ɺػೳͷݟ௚͠ʹΑΓ৽ن࡞੒͞Εͨςʔ ϒϧ͕ຆͲͰ͋Γ2ܥ͔Βಛʹมߋ఺ͳ͠
17. 3ܥͰ࡟আ͞Εͨςʔϒϧ
18. 3ܥͰ࡟আ͞Εͨςʔϒϧ ςʔϒϧ໊ ࿦ཧςʔϒϧ໊ dtb_api_account APIΞΧ΢ϯτ dtb_api_config APIઃఆ dtb_best_products ͓͢͢Ί঎඼৘ใ dtb_bkup όοΫΞοϓ dtb_class ن֨৘ใ(dtb_class_name΁໊শมߋ) dtb_csv_sql CSVৄࡉઃఆSQL৘ใ dtb_holiday ٳ೔৘ใ dtb_index_list ΠϯσοΫεҰཡ dtb_kiyaku ن໿৘ใ(dtb_help΁໊শมߋ) dtb_mailmaga_template ϝϧϚΨςϯϓϨʔτ৘ใ
19. 3ܥͰ࡟আ͞Εͨςʔϒϧ ςʔϒϧ໊ ࿦ཧςʔϒϧ໊ dtb_maker ϝʔΧʔ dtb_maker_count ϝʔΧʔΧ΢ϯτ dtb_mobile_ext_session_id ϞόΠϧηογϣϯ৘ใ dtb_module Ϟδϡʔϧ৘ใ dtb_module_update_logs Ϟδϡʔϧߋ৽ϩά৘ใ dtb_order_temp ड஫Ұ࣌৘ใ dtb_other_deliv ͦͷଞͷ഑ૹઌ৘ใ (dtb_customer_address΁໊শมߋ) dtb_ownersstore_settings ΦʔφʔζετΞઃఆ dtb_plugin_hookpoint ϓϥάΠϯϑοΫϙΠϯτ
20. 3ܥͰ࡟আ͞Εͨςʔϒϧ ςʔϒϧ໊ ࿦ཧςʔϒϧ໊ dtb_product_status ঎඼εςʔλε৘ใ dtb_recommend_products ؔ࿈঎඼৘ใ dtb_review ϨϏϡʔ৘ใ dtb_send_customer ސ٬ϝʔϧૹ৴ཤྺ dtb_send_history ϝʔϧૹ৴ཤྺ dtb_update Ϟδϡʔϧߋ৽৘ใ mtb_allowed_tag ڐՄλά mtb_auth_excludes ؅ཧը໘ೝূআ֎ mtb_constants ఆ਺Ϛελ
21. 3ܥͰ࡟আ͞Εͨςʔϒϧ ςʔϒϧ໊ ࿦ཧςʔϒϧ໊ mtb_delivery_date ঎඼ൃૹ೔(dtb_delivery_dateʹมߋ) mtb_disable_logout ϩάΞ΢τແޮϖʔδ mtb_magazine_type ϝϧϚΨछผ mtb_mail_magazine_type ϝʔϧϚΨδϯछผ mtb_mail_template ϝʔϧςϯϓϨʔτ mtb_mail_tpl_path ϝʔϧςϯϓϨʔτύε mtb_mail_type ϝʔϧछผ mtb_mobile_domain ϞόΠϧυϝΠϯ໊ mtb_ownersstoer_err ΦʔφʔζετΞΤϥʔछผ
22. 3ܥͰ࡟আ͞Εͨςʔϒϧ ςʔϒϧ໊ ࿦ཧςʔϒϧ໊ mtb_permission ؅ཧը໘ΞΫηεݖݶ mtb_product_status_color ঎඼εςʔλε৭ mtb_recommend ͓͢͢ΊϨϕϧ mtb_reminder ύεϫʔυ࣭໰Ϛελ mtb_review_deny_url ϨϏϡʔڋ൱URL mtb_status ঎඼εςʔλε mtb_status_image εςʔλεը૾ mtb_target ϒϩοΫλʔήοτ mtb_wday ༵೔
23. 3ܥͰ৽ن࡞੒͞Εͨςʔϒϧ
24. 3ܥͰ৽ن࡞੒͞Εͨςʔϒϧ ςʔϒϧ໊ ࿦ཧςʔϒϧ໊ dtb_plugin_event_handler ϓϥάΠϯΠϕϯτϋϯυϥʔ༻ dtb_product_image ঎඼ը૾ dtb_product_stock ঎඼ࡏݿ৘ใ dtb_product_tag ঎඼λά mtb_csv_type CSVग़ྗ৘ใ mtb_product_list_order_by ฒͼସ͑৘ใ mtb_tag λά
25. 2ܥͱ3ܥͷςʔϒϧରൺ
26. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ dtb_api_account APIΞΧ΢ϯτ × dtb_api_config APIઃఆ × dtb_baseinfo ళجຊ৘ใ dtb_base_info dtb_best_products ͓͢͢Ί঎඼৘ใ × dtb_bkup όοΫΞοϓ × dtb_bloc ϒϩοΫ৘ใ dtb_block dtb_blocposition ϒϩοΫهࣄ৘ใ dtb_block_position dtb_category ΧςΰϦ৘ใ dtb_category dtb_category_count ΧςΰϦ঎඼਺৘ใ dtb_category_count
27. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ dtb_category_total_count ΧςΰϦ঎඼߹ܭ਺৘ใ dtb_category_total_count dtb_class ن֨৘ใ dtb_class_name dtb_classcategory ن֨෼ྨ৘ใ dtb_class_category dtb_csv CSV৘ใ dtb_csv dtb_csv_sql CSVৄࡉઃఆSQL৘ใ × dtb_customer ސ٬৘ใ dtb_customer dtb_customer_favorite_pro ducts ͓ؾʹೖΓ঎඼৘ใ dtb_customer_favorite_pro duct dtb_deliv ഑ૹۀऀ৘ใ dtb_delivery dtb_delivfee ૹྉ৘ใ dtb_delivery_fee
28. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ dtb_delivtime ഑ૹ࣌ؒ৘ใ dtb_delivery_time dtb_holiday ٳ೔৘ใ × dtb_index_list ΠϯσοΫεҰཡ × dtb_kiyaku ن໿৘ใ dtb_help dtb_mail_history ड஫ϝʔϧૹ৴ཤྺ dtb_mail_history dtb_mailmaga_template ϝϧϚΨςϯϓϨʔτ৘ ใ × dtb_mailtemplate ϝʔϧςϯϓϨʔτ৘ใ dtb_mail_template dtb_maker ϝʔΧʔ × dtb_maker_count ϝʔΧʔΧ΢ϯτ ×
29. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ dtb_member ؅ཧऀ৘ใ dtb_member dtb_mobile_ext_session_id ϞόΠϧηογϣϯ৘ใ × dtb_module Ϟδϡʔϧ৘ใ × dtb_module_update_log Ϟδϡʔϧߋ৽ϩά৘ใ × dtb_news ৽ண৘ใ dtb_news dtb_order ड஫৘ใ dtb_order dtb_order_detail ड஫ৄࡉ৘ใ dtb_order_detail dtb_order_temp ड஫Ұ࣌৘ใ × dtb_other_deliv ͦͷଞͷ഑ૹઌ৘ใ dtb_customer_address
30. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ dtb_ownersstore_settings ΦʔφʔζετΞઃఆ × dtb_pagelayout ϖʔδϨΠΞ΢τ৘ใ dtb_page_layout dtb_payment ࢧ෷ํ๏৘ใ dtb_payment dtb_payment_options ࢧ෷બ୒ࢶ৘ใ dtb_payment_option dtb_plugin ϓϥάΠϯ৘ใ dtb_plugin dtb_plugin_hookpoint ϓϥάΠϯϑοΫϙΠϯ τ × dtb_products ঎඼৘ใ dtb_product dtb_product_categories ঎඼ΧςΰϦؔ࿈ dtb_product_category dtb_product_class ঎඼ن֨৘ใ dtb_product_class
31. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ dtb_product_status ঎඼εςʔλε৘ใ × dtb_recommend_products ؔ࿈঎඼৘ใ × dtb_review ϨϏϡʔআ๏ × dtb_send_customer ސ٬ϝʔϧૹ৴ཤྺ × dtb_send_history ϝʔϧૹ৴ཤྺ × dtb_session ηογϣϯ৘ใ dtb_session dtb_shipment_item ഑ૹ঎඼৘ใ dtb_shipment_item dtb_shipping ͓ಧ͚ઌ৘ใ dtb_shipping dtb_tax_rule ফඅ੫ϧʔϧ dtb_tax_rule
32. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ dtb_templates ςϯϓϨʔτ৘ใ dtb_template dtb_update Ϟδϡʔϧߋ৽৘ใ × mtb_allowed_tag ڐՄλά × mtb_auth_excludes ؅ཧը໘ೝূআ֎ × mtb_authority ؅ཧऀϚελ mtb_authority mtb_constants ఆ਺Ϛελ × mtb_country ࠃϚελ mtb_country mtb_customer_order_statu s ސ٬දࣔ༻ड஫εςʔλ ε mtb_cusotmer_order_statu s mtb_customer_status ސ٬εςʔλε mtb_customer_status
33. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ mtb_db σʔλϕʔεछผ mtb_db mtb_delivery_date ঎඼ൃૹ೔ dtb_delivery_date mtb_device_type ୺຤छผ mtb_device_type mtb_disable_logout ϩάΞ΢τແޮϖʔδ × mtb_disp ެ։ϑϥά mtb_disp mtb_job ৬ۀϚελ mtb_job mtb_magazine_type ϝϧϚΨछผ × mtb_mail_magazine_type ϝʔϧϚΨδϯछผ × mtb_mail_template ϝʔϧςϯϓϨʔτ ×
34. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ mtb_mail_tpl_path ϝʔϧςϯϓϨʔτύε × mtb_mail_type ϝʔϧछผ × mtb_mobile_domain ϞόΠϧυϝΠϯ໊ × mtb_order_status ड஫ঢ়ଶ mtb_order_status mtb_order_status_color ड஫εςʔλε৭ mtb_order_status_color mtb_ownersstoer_err ΦʔφʔζετΞΤϥʔ छผ × mtb_page_max ϖʔδ࠷େදࣔ਺ mtb_page_max mtb_permission ؅ཧը໘ΞΫηεݖݶ × mtb_pref ౎ಓ෎ݝϚελ mtb_pref
35. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ mtb_product_list_max ঎඼Ұཡ࠷େදࣔ਺ mtb_product_list_max mtb_product_status_color ঎඼εςʔλε৭ × mtb_product_type ঎඼छผϚελ mtb_product_type mtb_recommend ͓͢͢ΊϨϕϧ × mtb_reminder ύεϫʔυ࣭໰Ϛελ × mtb_review_deny_url ϨϏϡʔڋ൱URL × mtb_sex ੑผϚελ mtb_sex mtb_status ঎඼εςʔλε × mtb_status_image εςʔλεը૾ ×
36. 2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ mtb_target ϒϩοΫλʔήοτ × mtb_taxrule ՝੫نଇϚελ mtb_taxrule mtb_wday ༵೔ × mtb_work ؅ཧऀՔಇঢ়ଶ mtb_work mtb_zip ༣ศ൪߸ mtb_zip
37. 3.PostgreSQLͱMySQLͲͪΒ Λ࢖͑͹ྑ͍ͷ?
38. PostgreSQLͱMySQLͲͪΒΛ࢖ ͑͹ྑ͍ͷ? • EC-CUBE3͸Doctrineͱ͍͏ORMΛ࢖͍ͬͯΔͨ ΊɺPostgreSQLͱMySQLͷҧ͍͸ΞϓϦ͔Βͷ؍ ఺ͰݟΔͱͲͪΒΛ࢖ͬͯ΋ҧ͍͸ແ͠ • ύϑΥʔϚϯεͷ؍఺ͰΈΔͱαʔό؀ڥʹґΔ ͱ͜Ζ͕େ͖͍ͨΊɺࣗ෼͕ಘҙͱ͢ΔํΛ࢖͑ ͹ྑ͍ • ҰݴͰ͓఻͑͢Δͱҧ͍͸ͳ͠ʂ
39. PostgreSQLͱMySQLͲͪΒΛ࢖ ͑͹ྑ͍ͷ? • ঎඼݅਺(dtb-product)ʹ20,000݅Λ༻ҙͯ͠ PostgreSQLͱMySQLͷಈ࡞Λ͓ݟͤ͠·͢ɻ
40. 4.EC-CUBE3Ξοϓσʔτ࣌ͷ DB·ΘΓͷ஫ҙ఺
41. EC-CUBE3Ξοϓσʔτ࣌ͷDB ·ΘΓͷ஫ҙ఺ • EC-CUBE3͔Β͸ຊମͷόʔδϣϯΞοϓʹ൐ͳ͍ɺDBͷ όʔδϣϯΞοϓ΋migrationϑΝΠϧΛ࢖ͬͯΞοϓσʔ τՄೳ
 ˠsrc/Eccube/Resource/doctrine/migration௚Լʹଘࡏ͢Δ ϑΝΠϧΛݩʹmigration͕࣮ߦ͞ΕΔ • ຊମΞοϓσʔτʹ൐ͳ͍DBͷมߋ͕ൃੜͨ͠৔߹ɺ
 http://υϝΠϯ/install.php/migration
 Λ࣮ߦ͢Δ͜ͱͰɺmigration͕࣮ߦ͞ΕDB͕࠷৽Խ͞ΕΔ
42. EC-CUBE3Ξοϓσʔτ࣌ͷDB ·ΘΓͷ஫ҙ఺ • Πϯετʔϧ࣌doctrine_migraion_versionsͱ͍ ͏ςʔϒϧ͕࡞੒͞ΕΔ͕ɺmigration؅ཧ༻ ςʔϒϧͱͯࣗ͠ಈతʹ࡞੒͞ΕΔςʔϒϧͷ ͨΊ৮Βͳ͍͜ͱ • ༨ஊͰ͕͢ɺEC-CUBE2ܥ͔ΒEC-CUBE3΁ͷ σʔλϕʔεҠߦπʔϧ͸ݱঢ়ఏڙ༧ఆͳ͠
43. EC-CUBE3Ξοϓσʔτ࣌ͷDB ·ΘΓͷ஫ҙ఺ • ϓϥάΠϯ͔Βຊମͷςʔϒϧʹରͯ͠ͷΧελϚΠζ͸ඇ ਪ঑
 ˠ࣮ݱ͢ΔͨΊʹ͸ϓϥάΠϯΠϯετʔϧ࣌ʹϚΠάϨʔ γϣϯػೳΛ࢖ͬͯϓϥάΠϯ༻ͷςʔϒϧΛ࡞੒ͯ͠ѻ͏ ͷ͕๬·͍͠ • mtb_constants͕ແ͘ͳΓɺ୅ΘΓʹconstant.ymlΛ࢖༻͢Δ ͜ͱʹͳ͕ͬͨɺݱࡏEC-CUBE3Ͱ࢖༻͞Ε͍ͯͳ͍߲໨΋ ଘࡏ͢ΔͨΊɺকདྷ࢖༻͞Ε͍ͯͳ͍߲໨͸࡟আ͞ΕΔՄ ೳੑ͋Γ
44. ࠓ೔͓࿩͠ग़དྷͳ͔ͬͨ͜ͱ • େ෯ʹมΘͬͨΧϥϜͷςʔϒϧ • ֤ςʔϒϧͷؔ࿈ੑ • ֤ػೳͰ࢖༻͞Ε͍ͯΔςʔϒϧ • 2ܥ͔Β3ܥ΁σʔλҠߦ͸Ͳ͏͢Δͷ? • ଞʹ΋͋Γ·͕͢·ͨػձ͕͋Ε͹͓࿩͠͠·͢ɻΑ ͘෼͔Βͳ͍Αɺͱ͍͏ํ͸ޙͰ௚઀ฉ͍͍ͯͩ͘͞ɻ
45. ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ