Past, present and future of Active Record Oracle enhanced adapter

Transcript

1. "Past, present and future of Active Record Oracle enhanced adapter” Railsdm 2019 @yahonda
2. self • Yasuo Honda (@yahonda) • Maintainer of Active Record Oracle enhanced adapter • Rails contributor • Oracle ACE • OSSύονձ / Asakusa.rb • Software enginer at freee K.K.
3. News
4. Oracle enhanced adapter 6.0.0.beta1 released
5. Topics • What is Oracle enhanced adapter • Release cycle strategy • Past, present and future of Oracle enhanced adapter
6. What is Oracle enhanced adapter
7. Oracle enhanced adapter • 3rd party Active Record connection adapter • Working with Oracle database 18c • 19c is only available for Exadata, not for me yet • Working with Rails master branch • Supports CRuby using ruby-oci8 and JRuby using JDBC Driver
8. 1st party adapters • Bundled with Rails itself • Supports SQLite3, MySQL and PostgreSQL databases • Supports CRuby
9. 3rd party adapters • Not bundled with Rails • Support other databases i.e. Oracle, SQL Server • JRuby support
10. Other 3rd party adapters supporting Rails 5.2 • "ActiveRecord SQL Server Adapter" for Microsoft SQL Server • "activerecord-jdbc-adapter" for SQLite3, PostgreSQL and MySQL • Other third party adapter development have ended with Rails 4.2
11. Release cycle strategy • Release on the same day as Rails • Focus on master branch to support Rails master branch • Backport only bug fixes, no new features
12. Rails and Oracle enhanced adapter release dates • 4.0.0 on 2013-06-25 - 1.5.0 on 2013-11-01 • 4.1.0 on 2014-04-08 - 1.5.4 on 2014-03-25 • 4.2.0 on 2014-12-20 - 1.6.0 on 2015-06-25 • 5.0.0 on 2016-06-30 - 1.7.0 on 2016-08-04 • 5.1.0 on 2017-04-27 - 1.8.0 on 2017-04-27 • 5.2.0 on 2018/04/09 - 5.2.0 on 2018-04-10
13. ETA?
14. ETA means "Estimated time of arrival" • Frequent question made while developing Oracle enhanced adapter 1.6 • My answer would be "When unit tests are green" • Users do not expect my answer, the need to know date/time
15. Rails 4.2 support was behind about 6 month • Attributes API internal changes • Hard to support attributes API changes for Oracle enhanced adapter • Took longer hours of development
16. Rails 5.0 support was behind about 2 month • Attributes API are public in Rails 5.0 • When Oracle enhanced adapter supported Rails 4.2, Rails master branch were heavily developed for Rails 5.0
17. Branch strategy for Rails 5.0 or earlier • Developing two active branches, master and topic branch • Master branch supported released version of Rails • Topic branch "rails42" were developed to support Rails master branch
18. Conflict with pull requests from users • Pull requests from users usually opened to master branch • Users want new features "now" • Benefits users in the short term • These new features could introduce incompatibilities
19. Conflict with Rails development cycle • Next version of Rails development starts once RC released • Too late to change Rails if 3rd party adapter needs one • Too late to find which commit changes new behavior • New features usually implemented on top of refactored code
20. Trigger to change branch stratecy • https://twitter.com/sgrif/status/804037955241312258
21. Trigger to change branch stratecy • https://groups.google.com/forum/#!topic/oracle-enhanced/ 6R7SybOk_Ow
22. Why branch strategy can be changed • Not big changes for Rails 5.1 and later • Learned some Active Record connection adapter internal • Informed in advance in pull requests which could introduce incompatibility for 3rd party adapters
23. Release model of Oracle enhanced adapter • 80% for master branch, 20% for stable branch • Scheduled to run CI every day to find incompatible Rails changes • New features and bug fixes for master branch • Backport only bug fixes for stable branch, no new features • Released new version from stable branch like "release52"
24. Rails 5.1 and Rails 5.2 support on the same day
25. Oracle enhanced adapter 6.0.0 will be released on the same day as Rails 6.0.0.
26. Past, present and future
27. RubyKaigi 2014 on 2014/09/18 • " Introduce Oracle enhanced adapter for ActiveRecord, another choice for your Rails database." • http://rubykaigi.org/2014/presentation/S-YasuoHonda
28. Known restrictions presented at RubyKaigi 2014 • Empty string as NULL • Maximum identifier length is 30 byte • No “auto_increment” type support for primary key • ORDER BY in sub query causes ORA-00907: missing right parenthesis • No better top-N query “limit, offset” support
29. New features available for Oracle enhanced adapter • Better Top-N query support (fetch first n rows, like limit) • Maximum identifier length is 128 byte
30. New features available for Oracle database only • Not available for Oracle enhanced adapter yet • “IDENTITY” type support for primary key • Workaround ORDER BY in sub query causes ORA-00907: missing right parenthesis by using “fetch first n rows only”
31. Past Rails 4.2 to 5.2
32. Oracle enhanced adapter 1.6 for Rails 4.2 • Rails: Internal implementation attribute API • Support Rails native foreign key syntax • Drop multi column foreign key support
33. Oracle enhanced adapter 1.7 for Rails 5.0 • Rails: attribute API • Support better top N query using “fetch first n rows” • Drop its own “set_date_columns” methods to use Rails attributes API
34. Oracle enhanced adapter 1.8 for Rails 5.1 • Rails: Primary key bigint by default • Introduced “supports_datetime_with_precision?”
35. Primary key bigint by default • Oracle enhanced adapter keeps using :integer for primary key • Oracle enhanced adapter :integer is mapped to NUMBER(38) • :bigint is mapped to NUMBER(19) • 38 or 19 means number of precisions, not number of bytes • :integer is larger than :bigint
36. supports_datetime_with_precision? • :datetime mapped to Oracle DATE “YYYY-MM-DD MI:SS” • :datetime with precision needs Oracle TIMESTAMP(6) • Asked users to run these commands manually • "ALTER TABLE "POSTS" MODIFY "CREATED_AT" TIMESTAMP"
37. Oracle enhanced adapter 5.2.0 • Use cursor_sharing database default value, 'exact' • Drop class(model) based column cache
38. Oracle enhanced adapter 5.2.3 • Slowness reported for Oracle enhanced adapter 5.2 • Fixed these slowness by reverting changes made to 5.2.0 • Restore using cursor_sharing = 'force' • Introduce column cache per connection
39. Present Rails 6.0.0.beta
40. Oracle enhanced adapter 6.0.0.beta1 for Rails 6.0.0.beta3 • Maximum identifier length is 128 byte • Primary key “id” value starts with 1, not 10000 • “empty_statement_value” method signature change
41. Maximum identifier length is 128 byte • "Support longer identifier for Oracle database 12.2 or higher" • rsim/oracle-enhanced#1703 • 30 byte to 128 byte
42. default_sequence_start_value starts with 1 • “Change `default_sequence_start_value` from 10000 to 1” • rsim/oracle-enhanced#1636 • by “reset_pk_sequence!” support (rsim/oracle- enhanced##287)
43. empty_insert_statement_value method arity change • “Allow `primary_key` argument to `empty_insert_statement_value`” • rails/rails#32667 • Oracle database does not support "INSERT .. DEFAULT VALUES" • Insert statement needs at least one column name specified • To prepare Identity data support, like auto_increment
44. Future Rails 6.1
45. 6.1 or later • “IDENTITY” type support for primary key • Address "ORA-01795: maximum number of expressions in a list is 1000” by splitting in list by changing Arel visitor • Address "ORA-00907: missing right parenthesis" order by in sub query
46. Address “ORA-01795" • Address "ORA-01795: maximum number of expressions in a list is 1000” by splitting in list by changing Arel visitor • https://github.com/rails/rails/blob/ efb706daad0e2e1039c6abb4879c837ef8bf4d10/activerecord/lib/ arel/visitors/to_sql.rb#L511-L523
47. Address “ORA-00907" • ORA—00907 • UPDATE "POSTS" SET body = 'bulk update!' WHERE "POSTS"."ID" IN (SELECT "POSTS"."ID" FROM "POSTS" WHERE "POSTS"."AUTHOR_ID" = :a1 ORDER BY posts.id) [["author_id", 1]] • No ORA-00907 error • DELETE FROM "POSTS" WHERE "POSTS"."ID" IN (SELECT "POSTS"."ID" FROM "POSTS" WHERE "POSTS"."AUTHOR_ID" = :a1 ORDER BY "POSTS"."ID" ASC FETCH FIRST :a2 ROWS ONLY) [["author_id", 1], ["LIMIT", 1]]
48. Address “ORA-00907" • “ORDER BY posts.id" in sub query is unnecessary if no fetch first n rows • UPDATE "POSTS" SET body = 'bulk update!' WHERE "POSTS"."ID" IN (SELECT "POSTS"."ID" FROM "POSTS" WHERE "POSTS"."AUTHOR_ID" = :a1 ORDER BY posts.id) [["author_id", 1]] • UPDATE "POSTS" SET body = 'bulk update!' WHERE "POSTS"."ID" IN (SELECT "POSTS"."ID" FROM "POSTS" WHERE "POSTS"."AUTHOR_ID" = :a1) [["author_id", 1]]
49. Address “ORA-00907" • Arel::Visitors::ToSql#prepare_update_statement(o) can handle it • https://github.com/rails/rails/blob/master/activerecord/lib/ arel/visitors/to_sql.rb#L774-L790
50. Ask Me Anything and Thank you!