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

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

E2cb998e15d8e8fdef4f11a1f510fe74?s=128

Yasuo Honda

July 14, 2018
Tweet

Transcript

  1. 4.

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

    twitter) • Maintainer of Oracle enhanced adapter • Rails contributor • Asakusa.rb / OSS ύονձ • freee גࣜձࣾ
  2. 7.

    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. 8.

    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. 9.

    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. 11.

    Changes to support new databases • MySQL 5.7 • MySQL

    8.0 • PostgreSQL 10 • MariaDB 10.3 • MariaDB 10.2
  6. 12.

    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. 13.

    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. 14.

    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. 15.

    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. 16.

    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. 17.

    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. 18.

    PostgreSQL 10 • rails/rails#28864 “Support PostgreSQL 10 `pg_sequence`” • PostgreSQL

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

    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. 20.

    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. 21.

    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. 22.

    “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. 25.

    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. 27.

    Database versions tested at Travis CI (2) • "αϙʔτ"͞Ε͍ͯΔσʔλϕʔεόʔδϣϯ͕ ͢΂ͯςετ͞Ε͍ͯΔΘ͚Ͱ͸ͳ͍

    • PostgreSQL 10΁ͷpull request(rails/ rails#33112)͸Φʔϓϯ͞Ε͍ͯΔ • PostgreSQL 10 native partition support (rails/ rails#31336)
  19. 28.

    ͜Μͳ͜ͱ΋͓͜Δ • 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. 31.

    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. 33.

    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. 36.
  23. 37.

    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͕ར༻͍ͯ͠Δ
  24. 38.

    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͕ධՁ͕Մೳʹͳͬͨ
  25. 39.

    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
  26. 41.

    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ʹҠಈ ͨ͠
  27. 42.

    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͸มߋͰ͖ͳ͍ • σʔλϕʔε͝ͱͷৼΔ෣͍ΛผϞδϡʔϧ͔Ϋϥεʹ෼཭͠Α͏ͱ͢ΔࢼΈ
  28. 43.