Past, present and future of Active Record Oracle enhanced adapter

by Yasuo Honda

Slide 1

Slide 1 text

"Past, present and future of Active Record Oracle enhanced adapter” Railsdm 2019 @yahonda

Slide 2

Slide 2 text

self • Yasuo Honda (@yahonda) • Maintainer of Active Record Oracle enhanced adapter • Rails contributor • Oracle ACE • OSSύονձ / Asakusa.rb • Software enginer at freee K.K.

Slide 3

Slide 3 text

News

Slide 4

Slide 4 text

Oracle enhanced adapter 6.0.0.beta1 released

Slide 5

Slide 5 text

Topics • What is Oracle enhanced adapter • Release cycle strategy • Past, present and future of Oracle enhanced adapter

Slide 6

Slide 6 text

What is Oracle enhanced adapter

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

1st party adapters • Bundled with Rails itself • Supports SQLite3, MySQL and PostgreSQL databases • Supports CRuby

Slide 9

Slide 9 text

3rd party adapters • Not bundled with Rails • Support other databases i.e. Oracle, SQL Server • JRuby support

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Release cycle strategy • Release on the same day as Rails • Focus on master branch to support Rails master branch • Backport only bug ﬁxes, no new features

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

ETA?

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

Conﬂict with pull requests from users • Pull requests from users usually opened to master branch • Users want new features "now" • Beneﬁts users in the short term • These new features could introduce incompatibilities

Slide 19

Slide 19 text

Conﬂict 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 ﬁnd which commit changes new behavior • New features usually implemented on top of refactored code

Slide 20

Slide 20 text

Trigger to change branch stratecy • https://twitter.com/sgrif/status/804037955241312258

Slide 21

Slide 21 text

Trigger to change branch stratecy • https://groups.google.com/forum/#!topic/oracle-enhanced/ 6R7SybOk_Ow

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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"

Slide 24

Slide 24 text

Rails 5.1 and Rails 5.2 support on the same day

Slide 25

Slide 25 text

Oracle enhanced adapter 6.0.0 will be released on the same day as Rails 6.0.0.

Slide 26

Slide 26 text

Past, present and future

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

Known restrictions presented at RubyKaigi 2014 • Empty string as NULL • Maximum identiﬁer 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

Slide 29

Slide 29 text

New features available for Oracle enhanced adapter • Better Top-N query support (fetch ﬁrst n rows, like limit) • Maximum identiﬁer length is 128 byte

Slide 30

Slide 30 text

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 ﬁrst n rows only”

Slide 31

Slide 31 text

Past Rails 4.2 to 5.2

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

Oracle enhanced adapter 1.7 for Rails 5.0 • Rails: attribute API • Support better top N query using “fetch ﬁrst n rows” • Drop its own “set_date_columns” methods to use Rails attributes API

Slide 34

Slide 34 text

Oracle enhanced adapter 1.8 for Rails 5.1 • Rails: Primary key bigint by default • Introduced “supports_datetime_with_precision?”

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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"

Slide 37

Slide 37 text

Oracle enhanced adapter 5.2.0 • Use cursor_sharing database default value, 'exact' • Drop class(model) based column cache

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

Present Rails 6.0.0.beta

Slide 40

Slide 40 text

Oracle enhanced adapter 6.0.0.beta1 for Rails 6.0.0.beta3 • Maximum identiﬁer length is 128 byte • Primary key “id” value starts with 1, not 10000 • “empty_statement_value” method signature change

Slide 41

Slide 41 text

Maximum identiﬁer length is 128 byte • "Support longer identiﬁer for Oracle database 12.2 or higher" • rsim/oracle-enhanced#1703 • 30 byte to 128 byte

Slide 42

Slide 42 text

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)

Slide 43

Slide 43 text

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 speciﬁed • To prepare Identity data support, like auto_increment

Slide 44

Slide 44 text

Future Rails 6.1

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Address “ORA-00907" • “ORDER BY posts.id" in sub query is unnecessary if no fetch ﬁrst 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]]

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

Ask Me Anything and Thank you!