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

Slide 1

Slide 1 text

EC-CUBE3ͷ σʔλϕʔεߏ଄ͷ͓࿩ EC-CUBEؔ੢Ϣʔβάϧʔϓ 2015-11-04

Slide 2

Slide 2 text

ࣗݾ঺հ גࣜձࣾΩϡʔϧ ɹࢁଜ ਗ਼࢙ γεςϜΤϯδχΞ EC-CUBE3ͷίϛολʔ(@k-yamamura) ͯ͠·͢

Slide 3

Slide 3 text

αϚϦʔ 1.৽͘͠ͳͬͨDBεΩʔϚ 2.EC-CUBE3ʹͳΓ࡟আ͞Εͨςʔϒϧ 3.PostgreSQLͱMySQLͲͪΒΛ࢖͑͹ྑ͍ͷ? 4.EC-CUBE3Ξοϓσʔτ࣌ͷDB·ΘΓͷ஫ҙ఺ 5.QAͳͲ

Slide 4

Slide 4 text

1.৽͘͠ͳͬͨDBεΩʔϚ

Slide 5

Slide 5 text

৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2͔ΒEC-CUBE3ͷDBεΩʔϚͷݟ௚͠ʹ͋ͨΓORMͰѻ͍΍ ͍͢ܗࣜʹมߋ • EC-CUBE2͔ΒҎԼͷ಺༰Λݟ௚͠(v2.13Λର৅)  ςʔϒϧ໊͸໋໊ن໿ΛఆΊͯमਖ਼Λ࣮ࢪ  ར༻͍ͯ͠ͳ͍ςʔϒϧ/ΧϥϜͷ࡟আ  ֎෦Ωʔͷ௥Ճ  ϓϥάΠϯͰར༻͢Δςʔϒϧ͸ຊମ͔Β෼཭ • ෳ߹ओΩʔ͸ར༻ͤͣɺαϩήʔτΩʔΛར༻(ଟରଟ͸আ͘) • ͨͩ͠ɺEC-CUBE2ܥͷΧϥϜ໊শɺσʔλܕ͸ݪଇมߋ͠ͳ͍

Slide 6

Slide 6 text

৽͘͠ͳͬͨDBεΩʔϚ • ໋໊ن໿ͷϧʔϧ  ল໊ུশͷഇࢭ : dtb_deliv → dtb_delivery  ୯਺ܗʹมߋ : dtb_products → dtb_product  εωʔΫέʔεͰ໋໊ : dtb_class_category • ςʔϒϧʹ͸छผʹΑΓϓϨϑΟΫεΛ͚ͭΔ  Ϛελςʔϒϧ : mtb_hogehoge  σʔλςʔϒϧ : dtb_hogehoge  ϓϥάΠϯؔ࿈ : plg_hogehoge

Slide 7

Slide 7 text

৽͘͠ͳͬͨDBεΩʔϚ • ֎෦Ωʔͷ௥Ճ  dtb_productɹɹdtb_product_class • ϓϥάΠϯ͔Βຊମͷඪ४ςʔϒϧʹରͯ͠Χελ ϚΠζ͸ඇਪ঑  όʔδϣϯΞοϓ͕ࠔ೉ɺଞͷϓϥάΠϯͱͷڝ߹ • ෳ߹ओΩʔͰ͸ͳ͘αϩήʔτΩʔΛར༻  ORMͰѻ͍΍͘͢͢ΔͨΊαϩήʔτΩʔΛར༻

Slide 8

Slide 8 text

৽͘͠ͳͬͨ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  →ςʔϒϧఆٛॻ͸ҰԠ࠷৽

Slide 9

Slide 9 text

৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2Ͱͷ঎඼ؔ࿈৘ใ

Slide 10

Slide 10 text

৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE3Ͱͷ঎඼ؔ࿈৘ใ

Slide 11

Slide 11 text

৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2Ͱͷड஫ؔ࿈৘ใ

Slide 12

Slide 12 text

৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE3Ͱͷड஫ؔ࿈৘ใ

Slide 13

Slide 13 text

৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE2Ͱͷސ٬ؔ࿈৘ใ

Slide 14

Slide 14 text

৽͘͠ͳͬͨDBεΩʔϚ • EC-CUBE3Ͱͷސ٬ؔ࿈৘ใ

Slide 15

Slide 15 text

2.EC-CUBE3ʹͳΓ࡟আ͞Εͨ ςʔϒϧ

Slide 16

Slide 16 text

EC-CUBE3ʹͳΓ࡟আ͞Εͨςʔϒϧ • EC-CUBE3΁όʔδϣϯΞοϓ͢Δʹ͋ͨΓػ ೳͷݟ௚͕͠ߦΘΕͨͨΊɺςʔϒϧ΋ෆཁ ͱͳΓ࡟আ • 3ܥͰ৽ͨʹ৽ن࡞੒͞Εͨςʔϒϧ΋ଘࡏ͢ Δ͕ɺػೳͷݟ௚͠ʹΑΓ৽ن࡞੒͞Εͨςʔ ϒϧ͕ຆͲͰ͋Γ2ܥ͔Βಛʹมߋ఺ͳ͠

Slide 17

Slide 17 text

3ܥͰ࡟আ͞Εͨςʔϒϧ

Slide 18

Slide 18 text

3ܥͰ࡟আ͞Εͨςʔϒϧ ςʔϒϧ໊ ࿦ཧςʔϒϧ໊ dtb_api_account APIΞΧ΢ϯτ dtb_api_conﬁg 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 ϝϧϚΨςϯϓϨʔτ৘ใ

Slide 19

Slide 19 text

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 ϓϥάΠϯϑοΫϙΠϯτ

Slide 20

Slide 20 text

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 ఆ਺Ϛελ

Slide 21

Slide 21 text

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 ΦʔφʔζετΞΤϥʔछผ

Slide 22

Slide 22 text

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 ༵೔

Slide 23

Slide 23 text

3ܥͰ৽ن࡞੒͞Εͨςʔϒϧ

Slide 24

Slide 24 text

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 λά

Slide 25

Slide 25 text

2ܥͱ3ܥͷςʔϒϧରൺ

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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 ϝʔΧʔΧ΢ϯτ ×

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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 ϝʔϧςϯϓϨʔτ ×

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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 εςʔλεը૾ ×

Slide 36

Slide 36 text

2ܥͱ3ܥͷςʔϒϧରൺ 2ܥςʔϒϧ ࿦ཧςʔϒϧ໊ 3ܥςʔϒϧ mtb_target ϒϩοΫλʔήοτ × mtb_taxrule ՝੫نଇϚελ mtb_taxrule mtb_wday ༵೔ × mtb_work ؅ཧऀՔಇঢ়ଶ mtb_work mtb_zip ༣ศ൪߸ mtb_zip

Slide 37

Slide 37 text

3.PostgreSQLͱMySQLͲͪΒ Λ࢖͑͹ྑ͍ͷ?

Slide 38

Slide 38 text

PostgreSQLͱMySQLͲͪΒΛ࢖ ͑͹ྑ͍ͷ? • EC-CUBE3͸Doctrineͱ͍͏ORMΛ࢖͍ͬͯΔͨ ΊɺPostgreSQLͱMySQLͷҧ͍͸ΞϓϦ͔Βͷ؍ ఺ͰݟΔͱͲͪΒΛ࢖ͬͯ΋ҧ͍͸ແ͠ • ύϑΥʔϚϯεͷ؍఺ͰΈΔͱαʔό؀ڥʹґΔ ͱ͜Ζ͕େ͖͍ͨΊɺࣗ෼͕ಘҙͱ͢ΔํΛ࢖͑ ͹ྑ͍ • ҰݴͰ͓఻͑͢Δͱҧ͍͸ͳ͠ʂ

Slide 39

Slide 39 text

PostgreSQLͱMySQLͲͪΒΛ࢖ ͑͹ྑ͍ͷ? • ঎඼݅਺(dtb-product)ʹ20,000݅Λ༻ҙͯ͠ PostgreSQLͱMySQLͷಈ࡞Λ͓ݟͤ͠·͢ɻ

Slide 40

Slide 40 text

4.EC-CUBE3Ξοϓσʔτ࣌ͷ DB·ΘΓͷ஫ҙ఺

Slide 41

Slide 41 text

EC-CUBE3Ξοϓσʔτ࣌ͷDB ·ΘΓͷ஫ҙ఺ • EC-CUBE3͔Β͸ຊମͷόʔδϣϯΞοϓʹ൐ͳ͍ɺDBͷ όʔδϣϯΞοϓ΋migrationϑΝΠϧΛ࢖ͬͯΞοϓσʔ τՄೳ  ˠsrc/Eccube/Resource/doctrine/migration௚Լʹଘࡏ͢Δ ϑΝΠϧΛݩʹmigration͕࣮ߦ͞ΕΔ • ຊମΞοϓσʔτʹ൐ͳ͍DBͷมߋ͕ൃੜͨ͠৔߹ɺ  http://υϝΠϯ/install.php/migration  Λ࣮ߦ͢Δ͜ͱͰɺmigration͕࣮ߦ͞ΕDB͕࠷৽Խ͞ΕΔ

Slide 42

Slide 42 text

EC-CUBE3Ξοϓσʔτ࣌ͷDB ·ΘΓͷ஫ҙ఺ • Πϯετʔϧ࣌doctrine_migraion_versionsͱ͍ ͏ςʔϒϧ͕࡞੒͞ΕΔ͕ɺmigration؅ཧ༻ ςʔϒϧͱͯࣗ͠ಈతʹ࡞੒͞ΕΔςʔϒϧͷ ͨΊ৮Βͳ͍͜ͱ • ༨ஊͰ͕͢ɺEC-CUBE2ܥ͔ΒEC-CUBE3΁ͷ σʔλϕʔεҠߦπʔϧ͸ݱঢ়ఏڙ༧ఆͳ͠

Slide 43

Slide 43 text

EC-CUBE3Ξοϓσʔτ࣌ͷDB ·ΘΓͷ஫ҙ఺ • ϓϥάΠϯ͔Βຊମͷςʔϒϧʹରͯ͠ͷΧελϚΠζ͸ඇ ਪ঑  ˠ࣮ݱ͢ΔͨΊʹ͸ϓϥάΠϯΠϯετʔϧ࣌ʹϚΠάϨʔ γϣϯػೳΛ࢖ͬͯϓϥάΠϯ༻ͷςʔϒϧΛ࡞੒ͯ͠ѻ͏ ͷ͕๬·͍͠ • mtb_constants͕ແ͘ͳΓɺ୅ΘΓʹconstant.ymlΛ࢖༻͢Δ ͜ͱʹͳ͕ͬͨɺݱࡏEC-CUBE3Ͱ࢖༻͞Ε͍ͯͳ͍߲໨΋ ଘࡏ͢ΔͨΊɺকདྷ࢖༻͞Ε͍ͯͳ͍߲໨͸࡟আ͞ΕΔՄ ೳੑ͋Γ

Slide 44

Slide 44 text

ࠓ೔͓࿩͠ग़དྷͳ͔ͬͨ͜ͱ • େ෯ʹมΘͬͨΧϥϜͷςʔϒϧ • ֤ςʔϒϧͷؔ࿈ੑ • ֤ػೳͰ࢖༻͞Ε͍ͯΔςʔϒϧ • 2ܥ͔Β3ܥ΁σʔλҠߦ͸Ͳ͏͢Δͷ? • ଞʹ΋͋Γ·͕͢·ͨػձ͕͋Ε͹͓࿩͠͠·͢ɻΑ ͘෼͔Βͳ͍Αɺͱ͍͏ํ͸ޙͰ௚઀ฉ͍͍ͯͩ͘͞ɻ

Slide 45

Slide 45 text

͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ