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

Past, present and future of Active Record Oracl...

Past, present and future of Active Record Oracle enhanced adapter

Yasuo Honda

March 22, 2019
Tweet

More Decks by Yasuo Honda

Other Decks in Programming

Transcript

  1. self • Yasuo Honda (@yahonda) • Maintainer of Active Record

    Oracle enhanced adapter • Rails contributor • Oracle ACE • OSSύονձ / Asakusa.rb • Software enginer at freee K.K.
  2. Topics • What is Oracle enhanced adapter • Release cycle

    strategy • Past, present and future of Oracle enhanced adapter
  3. 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
  4. 1st party adapters • Bundled with Rails itself • Supports

    SQLite3, MySQL and PostgreSQL databases • Supports CRuby
  5. 3rd party adapters • Not bundled with Rails • Support

    other databases i.e. Oracle, SQL Server • JRuby support
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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"
  17. 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
  18. 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
  19. New features available for Oracle enhanced adapter • Better Top-N

    query support (fetch first n rows, like limit) • Maximum identifier length is 128 byte
  20. 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”
  21. 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
  22. 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
  23. Oracle enhanced adapter 1.8 for Rails 5.1 • Rails: Primary

    key bigint by default • Introduced “supports_datetime_with_precision?”
  24. 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
  25. 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"
  26. Oracle enhanced adapter 5.2.0 • Use cursor_sharing database default value,

    'exact' • Drop class(model) based column cache
  27. 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
  28. 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
  29. 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
  30. 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)
  31. 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
  32. 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
  33. 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
  34. 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]]
  35. 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]]