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

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

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

Who am I • Yasuo Honda (@yahonda at GitHub and twitter) • Maintainer of Oracle enhanced adapter • Rails contributor • Asakusa.rb / OSS ύονձ • freee גࣜձࣾ

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

͸ͳ͞ͳ͍͜ͱ • Active Record Oracle enhanced adapterͷ࿩ • Connection adapterҎ֎ͷActive Recordͷ࿩ • RailsΞϓϦέʔγϣϯ։ൃͷ࿩

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

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

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”͙Β͍ͷҙຯ

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

Changes to support new databases • MySQL 5.7 • MySQL 8.0 • PostgreSQL 10 • MariaDB 10.3 • MariaDB 10.2

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”Λ࡟আ͢Δ

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ϦϑΝϨ ϯεΨΠυʹࡌͤͯ΋Β͍·ͨ͠

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 @@…`ʹॻ͖׵͑

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Ͱɺͦ΋ͦ΋ϑΥʔϧόοΫ͕ෆཁʹͳͬͨ

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

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 ͯ͠ɺmy_boolΛboolʹ͓͖ ͔͑Δ • mysql2 0.4.6ͰϦϦʔε͞Ε·ͨ͠

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

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

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Ͱมߋʹͳͬͨ

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͢Δ

“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

৽͍͠σʔλϕʔεαϙʔτ • Fix͸σʔλϕʔε͕GAʹͳΔલʹߦΘΕ͍ͯΔ • Fixର৅͕MySQLσʔλϕʔεࣗମ΍ؔ࿈gem(mysql2)ͷ͜ͱ΋͋Δ • σʔλϕʔε΍ؔ࿈gem΋ΦʔϓϯιʔεͩͬͨΓɺ৽͍͠ৼΔ෣͍ʹࢸΔ ٞ࿦͕ެ։͞Ε͍ͯΔ͜ͱ͕ଟ͍ • Ұ࣍৘ใ΁ͷϦϯΫΛGitHubͷissue΍pull requestʹ͔͍͓͖ͯɺٞ࿦Λ ੵΈॏͶΔ • RailsҎ֎Ͱͷ໰୊ͷใࠂ͸ͦ͜ͷݴޠͰߦ͍ɺRails͸࢖Θͳ͍ • ྫ: MySQLσʔλϕʔεͳΒɺ࠶ݱέʔε͸SQLจ͚ͩͰ࡞Δ

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

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

Not all of "supported" versions are tested.

Database versions tested at Travis CI (2) • "αϙʔτ"͞Ε͍ͯΔσʔλϕʔεόʔδϣϯ͕ ͢΂ͯςετ͞Ε͍ͯΔΘ͚Ͱ͸ͳ͍ • PostgreSQL 10΁ͷpull request(rails/ rails#33112)͸Φʔϓϯ͞Ε͍ͯΔ • PostgreSQL 10 native partition support (rails/ rails#31336)

͜Μͳ͜ͱ΋͓͜Δ • 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Ҏ ্Λαϙʔτ͢ΔΑ͏ʹมߋ͞Ε·ͨ͠

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

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

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

MySQL͚ͩͰΤϥʔʹͳΔྫ • rails/rails#30294 “Move `test_index_is_created_for_both_timestamps` to `TimestampsWithoutTransactionTest`” • ಛఆͷॱং͔ͭMySQL͚ͩͰൃੜ͢Δ • ಉ͡ॱংͰ΋PostgreSQLͱSQLiteͰ͸࠶ݱ͠ͳ͍ • मਖ਼͸ςετͷ৔ॴΛҠ͚ͩ͢

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͕҉໧తʹίϛοτ͞ΕΔ

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

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

Rails 6

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

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

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

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

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

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

͍͞͝ʹ

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