Share
Embed
Past, present and future of Active Record Oracle enhanced adapter
Yasuo Honda
March 22, 2019
Transcript
50 slides
-
"Past, present and future of Active Record Oracle enhanced adapter”
Railsdm 2019 @yahonda -
self • Yasuo Honda (@yahonda) • Maintainer of Active Record
Oracle enhanced adapter • Rails contributor • Oracle ACE • OSSύονձ / Asakusa.rb • Software enginer at freee K.K. -
Topics • What is Oracle enhanced adapter • Release cycle
strategy • Past, present and future of Oracle enhanced adapter -
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 -
1st party adapters • Bundled with Rails itself • Supports
SQLite3, MySQL and PostgreSQL databases • Supports CRuby -
3rd party adapters • Not bundled with Rails • Support
other databases i.e. Oracle, SQL Server • JRuby support -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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" -
Oracle enhanced adapter 6.0.0 will be released on the same
day as Rails 6.0.0. -
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 -
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 -
New features available for Oracle enhanced adapter • Better Top-N
query support (fetch first n rows, like limit) • Maximum identifier length is 128 byte -
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” -
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 -
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 -
Oracle enhanced adapter 1.8 for Rails 5.1 • Rails: Primary
key bigint by default • Introduced “supports_datetime_with_precision?” -
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 -
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" -
Oracle enhanced adapter 5.2.0 • Use cursor_sharing database default value,
'exact' • Drop class(model) based column cache -
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 -
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 -
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 -
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) -
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 -
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 -
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 -
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]] -
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]]