Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Active Record connection adaptersについて私が知っているいくつ...

Active Record connection adaptersについて私が知っているいくつかの事柄

Avatar for Yasuo Honda

Yasuo Honda

July 14, 2018
Tweet

More Decks by Yasuo Honda

Other Decks in Programming

Transcript

  1. Who am I • Yasuo Honda (@yahonda at GitHub and

    twitter) • Maintainer of Oracle enhanced adapter • Rails contributor • Asakusa.rb / OSS ύονձ • freee גࣜձࣾ
  2. Databases supported by Rails • σʔλϕʔεɺActive RecordσʔλϕʔεΞμϓλɺgemͷ૊Έ߹Θͤ • SQLite database

    • “sqlite" adapter with “sqlite3” gem • MySQL database • “mysql2” adapter with “mysql2” gem • PostgreSQL database • “postgresql” adapter with “pg” gem
  3. Database versions supported by Rails 5.2 • raise "Your version

    of MySQL (#{version_string}) is too old. Active Record supports MySQL >= 5.1.10." • raise "Your version of PostgreSQL (#{postgresql_version}) is too old. Active Record supports PostgreSQL >= 9.1.” • No “too old” in SQLite adapter yet
  4. too new? • “too new”͸ఆٛ͞Ε͍ͯͳ͍ • Rails 5.2Ͱ“αϙʔτ”͞Ε͍ͯΔόʔδϣϯ: • MySQL

    5.1, 5.5, 5.6, 5.7 and 8.0 • PostgreSQL 9.1, 9.2, 9.3, 9.4, 9.5, 9.6 and 10 • Note: ”αϙʔτ”͸”it should work”͙Β͍ͷҙຯ
  5. Changes to support new databases • MySQL 5.7 • MySQL

    8.0 • PostgreSQL 10 • MariaDB 10.3 • MariaDB 10.2
  6. MySQL 5.7 (1) • rails/rails#13247 Remove `DEFAULT NULL` for primary

    key column to support MySQL 5.7.3 • MySQL 5.7.3͔ΒPRIMARY KEYͱDEFAULT NULLͷ૊Έ߹Θ ͕ͤΤϥʔͱͳͬͨ • Migration͕ੜ੒͢ΔPrimary Key͕͜ͷ૊Έ߹Θͤͩͬͨ • ”int(11) DEFAULT NULL auto_increment PRIMARY KEY” • Fix: “DEFAULT NULL”Λ࡟আ͢Δ
  7. MySQL 5.7 (2) • rails/rails#19359 "Materialize subqueries by adding `DISTINCT`

    to suport MySQL 5.7.6 and later" • MySQL 5.7.6͔Β”optimizer_switch=‘derived_merge=on’"͕σϑΥϧτ Ͱಋೖ͞ΕɺಉҰDML಺Ͱߋ৽ର৅ͷςʔϒϧΛαϒΫΤϦ಺ͰࢀরͰ ͖ͳ͘ͳͬͨ • Fix: αϒΫΤϦʹඞͣDISTINCTΛ෇༩͠ɺϚςϦΞϧԽ͢Δ͜ͱͰผ ςʔϒϧѻ͍ʹ͢Δ(Refer rails/rails#22241 for the better fix) • https://bugs.mysql.com/bug.php?id=76259 ܦ༝ͰMySQL 5.7ϦϑΝϨ ϯεΨΠυʹࡌͤͯ΋Β͍·ͨ͠
  8. MySQL 5.7 (3) • rails/rails#21318 "Support MySQL 5.7.8 which enables

    show_compatibility_56=off” • MySQL 5.7.8͔Β`show variables .. where` ͕ ΤϥʔΛฦ͢Α͏ʹͳͬͨ • Fix: `select @@…`ʹॻ͖׵͑
  9. MySQL 8.0 (1) • rails/rails#28730 “MySQL 8.0.1 DMR : ActiveRecord::StatementInvalid:

    Mysql2::Error: Unknown collation: ‘utf8_0900_ai_ci'" • Fix : rails/rails#28733 "Don't fallback to utf8mb3 after MySQL 8.0.0” • ࠷େ௕͕4byteҎ্ͷΩϟϥΫληοτ(ྫ: utf8mb4)ར༻࣌ɺar_internal_metadata ΍schema_migrationsͷ಺෦ςʔϒϧ࡞੒࣌ɺ Mysql2::Error: Index column size too large. ΤϥʔΛճආ͢ΔͨΊɺcollationΛม׵͍ͤͯͨ͞ • MySQL 8.0Ҏ߱ͰͦͷϑΥʔϧόοΫΛߦΘͳ͍Α͏ʹͨ͠ • ϑΥʔϧόοΫϧʔϧʹ͋͏collation͕MySQL 8.0Ͱ͸ͳ͘ͳͬͨ • ΠϯσοΫεͷ࠷େ௕͕3072byteͰɺͦ΋ͦ΋ϑΥʔϧόοΫ͕ෆཁʹͳͬͨ
  10. MySQL 8.0 (2) • rails/rails#26476 "MySQL 8.0.0-dmr test_multiple_foreign_keys_can_be_added_to_the_same_tabl e fails

    due to only 1 fk information shown” • MySQL 8.0.0Ͱ1ͭͷςʔϒϧʹ2ͭͷforeign keys͕ுΒΕ͍ͯ Δͷʹɺinfomation_schema͔Β͸1͔ͭ͠ݕࡧ͞Εͳ͍ • Fix: MySQL 8.0ͷͲ͔͜ͷDMRͰमਖ਼͞Ε·ͨ͠ • https://bugs.mysql.com/bug.php?id=82961 "Information_schema Foreign key meta data differs in 8.0.0
  11. MySQL 8.0 (3) • brianmario/mysql2# 840 "Use `bool` instead of

    `my_bool` which has been removed since MySQL 8.0.1” • MySQL 8.0.1Ͱmy_bool͕ഇࢭʹͳͬͨ • Fix: #include <stdbool.h> ͯ͠ɺmy_boolΛboolʹ͓͖ ͔͑Δ • mysql2 0.4.6ͰϦϦʔε͞Ε·ͨ͠
  12. PostgreSQL 10 • rails/rails#28864 “Support PostgreSQL 10 `pg_sequence`” • PostgreSQL

    10Ͱ֤γʔέϯε͕”increment_by”Λ ࣋ͨͳ͘ͳΓɺ”pg_sequence”Χλϩά͔Βऔಘ͢ ΔΑ͏ʹͳͬͨ • Fix: PostgreSQL 10Ҏ্ͳΒpg_sequence͔Βऔಘ ͢ΔΑ͏ʹมߋ͞Ε·ͨ͠
  13. MariaDB 10.3 • rails/rails#33028 CI against MariaDB 10.3 #33028 •

    MariaDB 10.3ͰOracleޓ׵ػೳͷ෭࡞༻ ͱͯ͠ɺ `LENGTH()`ؔ਺͕`OCTET_LENGTH()`ʹม׵͞ΕΔΑ ͏ʹͳͬͨ • MySQLͱ͸ҟͳΔಈ࡞ • Fix: MariaDB 10.3ͷಈ࡞ʹैͬͨςετίʔυͷมߋ
  14. MariaDB 10.2 • rails/rails#30485 "test_remove_column_with_multi_column_ind ex gets error with MariaDB

    10.2.8" • multi column ͔ͭ uniqueΠϯσοΫεͷ͏ ͪɺҰͭͷΧϥϜΛremove_column͠Α͏ͱ͠ ͨͱ͖ͷಈ࡞͕MariaDB 10.2Ͱมߋʹͳͬͨ
  15. MariaDB 10.2 - cont • MariaDB 10.2.7Ҏલ & MySQL •

    ΧϥϜ͕࡟আ͞Εɺ࢒ΓͷΧϥϜͰ୯ҰΧϥϜΠϯσοΫεͱͯ͠࢒Δ • MariaDB 10.2.8Ҏ߱ • ΧϥϜ͕࡟আ͕ڐՄ͞Εͳ͍ • “Key column 'hat_size' doesn't exist in table: ALTER TABLE `test_models` DROP `hat_size`” • PostgreSQL ͱ Oracle • ΧϥϜ͕࡟আ͞Εෳ߹ΠϯσοΫεશ෦ΛDROP͢Δ
  16. “Every database and/or database adapter has their own behavior if

    it drops the multi-column index when any of the indexed columns dropped by remove_column.” rails/rails#8678
  17. Database versions tested • Refer https://travis-ci.org/rails/rails • PostgreSQL 9.2 •

    PostgreSQL 9.6 • MySQL 5.6 • MariaDB 10.3 • SQLite 3.8.2
  18. Database versions tested at Travis CI (2) • "αϙʔτ"͞Ε͍ͯΔσʔλϕʔεόʔδϣϯ͕ ͢΂ͯςετ͞Ε͍ͯΔΘ͚Ͱ͸ͳ͍

    • PostgreSQL 10΁ͷpull request(rails/ rails#33112)͸Φʔϓϯ͞Ε͍ͯΔ • PostgreSQL 10 native partition support (rails/ rails#31336)
  19. ͜Μͳ͜ͱ΋͓͜Δ • rails/rails#27422 “Upgrading from Rails 5.0.0.1 to Rails 5.0.1

    breaks MySQL 5.0 compatibility” • Rails 5.0͕”αϙʔτ”͍ͯ͠ΔMySQL 5.0ʹଘࡏ͠ͳ ͍`infomation_schema.referential_constraints`Λར ༻͍ͯͨ͠ • rails/rails#27435ͰRails 5.1͔Β͸MySQL 5.1.10Ҏ ্Λαϙʔτ͢ΔΑ͏ʹมߋ͞Ε·ͨ͠
  20. Example of unlock minitest • rails/rails#29271 “Unlock minitest for Rails'

    test suite” • minitestΛ5.3.3͔Β5.10.2ʹόʔδϣϯΞοϓ • 5.3.4 ”Test classes are randomized before running.” • RailsϑϨʔϜϫʔΫunit test͸ॱংʹґଘ͍ͯͨ͠ͷ Λղܾ͢ΔͨΊͷIssue • ࠷΋ҹ৅ਂ͍मਖ਼Λ঺հ͠·͢
  21. MySQL͚ͩͰΤϥʔʹͳΔྫ • TimestampTest#test_index_is_created_for_both_timestamps`Λ։࢝͢Δ • TimestampTest#setup ͕࣮ߦ͞ΕΔ • @developer.update_columns(updated_at: Time.now.prev_month)Ͱ ΧϥϜͷ஋͕ߋ৽͞ΕΔ

    • TimestampTest#test_index_is_created_for_both_timestamps`Λ࣮ߦ͢Δ • `create_table`ͱ`create_index`͕࣮ߦ͞ΕΔ • ্ه@developer.update_columns͕҉໧తʹίϛοτ͞ΕΔ
  22. Rails 6 requires SQLite 3.8 • SQLiteͷϛχϚϜόʔδϣϯ͕3.8ʹͳΓ·͢ • raise "Your

    version of SQLite (#{sqlite_version}) is too old. Active Record supports SQLite >= 3.8." • 3.8͕ϦϦʔε͞Εͨͷ͸2013-08-26 (3.8.0) • Ubuntu 14.04 LTS͕ར༻͍ͯ͠Δ
  23. SQLite 3.8 to support modify column for foreign key referenced

    tables • SQLiteσʔλϕʔε͸ςʔϒϧఆٛΛมߋͰ͖ͳ͍ • SQLite adapterͰɺalter_tableϝιου͕ผ໊Ͱ৽͍͠ఆٛͷςʔϒϧΛ ࡞੒͠ɺಉҰτϥϯβΫγϣϯ಺Ͱɺςʔϒϧ໊Λݩʹ໭͍ͯͨ͠ • ςʔϒϧ͕Foreign KeyͰࢀর͞Ε͍ͯͨ৔߹ɺݩͷςʔϒϧΛDrop͢Δ ͜ͱ͸Ͱ͖ͳ͔ͬͨ • τϥϯβΫγϣϯ಺Ͱ΋Foreign Key͸ଈ࣌ධՁ͞ΕΔ͔Β(ͷ͸ͣ) • SQLite 3.8Ͱɺ஗Ԇ੍໿(defer_foreign_keys)Λαϙʔτ͠ɺτϥϯβΫ γϣϯͷ࠷ޙͰForeign Key͕ධՁ͕Մೳʹͳͬͨ
  24. SQLite adapterͷίʔυ # alter_table def alter_table(table_name, options = {}) altered_table_name

    = "a#{table_name}" caller = lambda { |definition| yield definition if block_given? } transaction do disable_referential_integrity do move_table(table_name, altered_table_name, options.merge(temporary: true)) move_table(altered_table_name, table_name, &caller) end end end # move_table def move_table(from, to, options = {}, &block) copy_table(from, to, options, &block) drop_table(from) end
  25. Rails 5.2 introduces SchemaDumper per database adapter • rails/rails#30337 “Refactor

    `SchemaDumper` to make it possible to adapter specific customization “ • rails/rails#30984 “Move extensions to PostgreSQL::SchemaDumper” • Rails 5.2Ͱ֤σʔλϕʔεΞμϓλʔ͕SchemaDumper Λ࣋ͭΑ͏ʹͳΓɺPostgreSQLʹ͔͠ଘࡏ͠ͳ͍ #extensionsͷ৔ॴ͕PostgreSQL::SchemaDumperʹҠಈ ͨ͠
  26. Migration compatibility support per database adapter • rails/rails#33269 "[WIP] Introduce

    migration compatibility per database adapters” • ActiveRecord::Migration::Compatibility͕ҎԼͷ2ͭΛٵऩ͍ͯ͠Δ • MigrationόʔδϣϯʹΑΔৼΔ෣͍ͷҧ͍ - ActiveRecord::Migration[5.1] • ྫ: 5.1ͰͷPrimary key͕σϑΥϧτͰbigintʹͳͬͨ • σʔλϕʔεΞμϓλʔʹΑΔৼΔ෣͍ͷҧ͍ • ྫ: options: “ENGINE=InnoDB” Λmysql2Ͱ௥Ճ͢Δ • ͜Ε͸bundled adapterʹݶΒΕɺ3rd party adapter͸มߋͰ͖ͳ͍ • σʔλϕʔε͝ͱͷৼΔ෣͍ΛผϞδϡʔϧ͔Ϋϥεʹ෼཭͠Α͏ͱ͢ΔࢼΈ