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

Transcript

1. 1.

   “Active Record connection adapterʹ͍ͭͯࢲ͕஌ͬͯ ͍Δ͍͔ͭ͘ͷࣄฑ” 2018/7/13 at Rails Developer Meetup

   @yahonda
2. 2.

   “Conceptual compression means beginners don’t need to know SQL —  hallelujah!”

   - DHH
3. 3.

   Connection adapters΋ conceptual compressionsͷ ͻͱͭ ͜ͷ20෼ؒ ”஌Βͳͯ͘΋͍͍͜ͱ”Λ ೷͍ͯΈ·͠ΐ͏

4. 4.

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

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

   ͸ͳ͢͜ͱ • Rails͕αϙʔτ͢Δσʔλϕʔε • σʔλϕʔε͝ͱʹΑΔৼΔ෣͍ͷҧ͍ • Rails 6Ͱى͖ΔมԽ

6. 6.

   ͸ͳ͞ͳ͍͜ͱ • Active Record Oracle enhanced adapterͷ࿩ • Connection adapterҎ֎ͷActive

   Recordͷ࿩ • RailsΞϓϦέʔγϣϯ։ൃͷ࿩
7. 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
8. 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
9. 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”͙Β͍ͷҙຯ
10. 10.

    ͋ͨΒ͍͠MySQL΍ PostgreSQLʹRails͸ͲͷΑ ͏ʹରԠ͍ͯ͠Δͷ͔

11. 11.

    Changes to support new databases • MySQL 5.7 • MySQL

    8.0 • PostgreSQL 10 • MariaDB 10.3 • MariaDB 10.2
12. 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”Λ࡟আ͢Δ
13. 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ϦϑΝϨ ϯεΨΠυʹࡌͤͯ΋Β͍·ͨ͠
14. 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 @@…`ʹॻ͖׵͑
15. 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Ͱɺͦ΋ͦ΋ϑΥʔϧόοΫ͕ෆཁʹͳͬͨ
16. 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
17. 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ͰϦϦʔε͞Ε·ͨ͠
18. 18.

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

    10Ͱ֤γʔέϯε͕”increment_by”Λ ࣋ͨͳ͘ͳΓɺ”pg_sequence”Χλϩά͔Βऔಘ͢ ΔΑ͏ʹͳͬͨ • Fix: PostgreSQL 10Ҏ্ͳΒpg_sequence͔Βऔಘ ͢ΔΑ͏ʹมߋ͞Ε·ͨ͠
19. 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ͷಈ࡞ʹैͬͨςετίʔυͷมߋ
20. 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Ͱมߋʹͳͬͨ
21. 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͢Δ
22. 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
23. 23.

    ৽͍͠σʔλϕʔεαϙʔτ • Fix͸σʔλϕʔε͕GAʹͳΔલʹߦΘΕ͍ͯΔ • Fixର৅͕MySQLσʔλϕʔεࣗମ΍ؔ࿈gem(mysql2)ͷ͜ͱ΋͋Δ • σʔλϕʔε΍ؔ࿈gem΋ΦʔϓϯιʔεͩͬͨΓɺ৽͍͠ৼΔ෣͍ʹࢸΔ ٞ࿦͕ެ։͞Ε͍ͯΔ͜ͱ͕ଟ͍ • Ұ࣍৘ใ΁ͷϦϯΫΛGitHubͷissue΍pull

    requestʹ͔͍͓͖ͯɺٞ࿦Λ ੵΈॏͶΔ • RailsҎ֎Ͱͷ໰୊ͷใࠂ͸ͦ͜ͷݴޠͰߦ͍ɺRails͸࢖Θͳ͍ • ྫ: MySQLσʔλϕʔεͳΒɺ࠶ݱέʔε͸SQLจ͚ͩͰ࡞Δ
24. 24.

    ৽͍͠σʔλϕʔεαϙʔτ • मਖ਼ʹࢸΒͳ͍৔߹Ͱ΋ɺ࠶ݱέʔεͷ࡞੒͸͓ͯ͘͠ͱɺ୭͔ (ະདྷͷࣗ෼ͩͬͨΓଞͷਓͩͬͨΓ)͕΍ͬͯ͘ΕΔ͜ͱ΋͋Δ • σϑΥϧτͰಈ࡞ͤ͞Δඞཁ͕͋Δ • ύϥϝʔλมߋͳͲͷϫʔΫΞϥ΢ϯυΛRailsϢʔβʔશһʹ ظ଴Ͱ͖ͳ͍ •

    SQLͱͯ͠͸୯७ͳมߋ(NOT NULLΛ࡟আ͢Δ)Ͱ͋ͬͯ΋ɺϑ ϨʔϜϫʔΫͷόʔδϣϯΞοϓ͕ඞཁͱͳΔ
25. 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
26. 26.

    Not all of "supported" versions are tested.

27. 27.

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

    • PostgreSQL 10΁ͷpull request(rails/ rails#33112)͸Φʔϓϯ͞Ε͍ͯΔ • PostgreSQL 10 native partition support (rails/ rails#31336)
28. 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Ҏ ্Λαϙʔτ͢ΔΑ͏ʹมߋ͞Ε·ͨ͠
29. 29.

    Connection adapter͸ σʔλϕʔε͝ͱʹɺ ͳʹ͕ϋϯυϧͰ͖ͯ ͳʹ͕ϋϯυϧͰ͖ͳ͍ͷ͔

30. 30.

    Ͱ͖Δ͜ͱͱͰ͖ͳ͍͜ͱ • Ͱ͖Δ͜ͱ: SQLจΛੜ੒͠ɺ࣮ߦ͢Δͱ͜Ζ • Ͱ͖ͳ͍͜ͱ: ͦͷ࣮ߦ͞ΕͨSQL͕ͲͷΑ͏ ʹৼΔ෣͏͔

31. 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 • ࠷΋ҹ৅ਂ͍मਖ਼Λ঺հ͠·͢
32. 32.

    MySQL͚ͩͰΤϥʔʹͳΔྫ • rails/rails#30294 “Move `test_index_is_created_for_both_timestamps` to `TimestampsWithoutTransactionTest`” • ಛఆͷॱং͔ͭMySQL͚ͩͰൃੜ͢Δ •

    ಉ͡ॱংͰ΋PostgreSQLͱSQLiteͰ͸࠶ݱ͠ͳ͍ • मਖ਼͸ςετͷ৔ॴΛҠ͚ͩ͢
33. 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͕҉໧తʹίϛοτ͞ΕΔ
34. 34.

    MySQL͚ͩͰΤϥʔʹͳΔྫ • IntegrationTest#test_cache_key_format_for_existing_record_wit h_updated_atΛ࣮ߦ͢Δ • ಉ͡Developer.firstͷupdated_at͕ઌ΄Ͳͷ҉໧ͷίϛοτͰ 1೔ਐΈɺظ଴ͱҧ͏஋ʹͳ͍ͬͯΔ • ಉ͡ཧ༝Ͱ͜ͷ2ͭͷςετ΋Fail͢Δ •

    test_cache_key_format_for_existing_record_with_updated_at _and_custom_cache_timestamp_formattest_cache_key_for_n ewer_updated_at
35. 35.

    Unlock minitest • Fix: self.use_transactional_tests = false • MySQL͸DDLൃߦ࣌ʹ҉໧తʹίϛοτ͢Δ •

    PostgreSQLͱSQLite͸͠ͳ͍
36. 36.

    Rails 6

37. 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͕ར༻͍ͯ͠Δ
38. 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͕ධՁ͕Մೳʹͳͬͨ
39. 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
40. 40.

    σʔλϕʔε͝ͱʹҧ͏࣮૷͕ඞཁͳ ͷ͸connection adapter͚ͩͰ͸ͳ͍ • ActiveRecord::SchemaDumper • ActiveRecord::Migrations

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

    ͍͞͝ʹ

44. 44.

    ͜͏͍͏࿩ʹڵຯ͕͋Ε͹ɺ OSSύονձ΁Ͳ͏ͧ ࣍ճ͸7/19