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

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

Sponsored · SiteGround - Reliable hosting with speed, security, and support you can count on. →

Past, present and future of Active Record Oracle enhanced adapter

Yasuo Honda

March 22, 2019

Transcript

50 slides
  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]]

More Decks by Yasuo Honda

Other Decks in Programming