Introduce Oracle enhanced adapter with Rails another choice for your Rails database Yasuo Honda @yahonda

Which database are you using with Rails?

No content

No content

Yes, I know the answer. but...

Cross the border — Minero Aoki, Oedo Ruby Conference 04

Who am I • Yasuo Honda @yahonda • Oracle enhanced adapter maintener since Nov 2011 • Rails Contributor since September 2011

What is Oracle enhanced adapter? • 3rd party ActiveRecord adapter • https://github.com/rsim/oracle-enhanced • Supports Rails 2.3* to Rails 4.1 • Rails 4 support started on the same day Rails 4.0.1 released • Rails 4.2 support is in progress, 79 failures/errors to fix • Want to release the same day Rails 4.2 released

What is Oracle enhanced adapter? (cont) • Built in foreign key support • Data type difference mapping • “Legacy schemas” support

No content

"Legacy schemas" class Employee < ActiveRecord::Base # specify schema and table name self.table_name "hr.hr_employees" # specify primary key name self.primary_key "employee_id" # specify sequence name self.sequence_name "hr.hr_employee_s" # set which DATE columns should be converted to Ruby Date set_date_columns :hired_on, :birth_date_on # set which DATE columns should be converted to Ruby Time set_datetime_columns :last_login_time # set which VARCHAR2 columns should be converted to true and false set_boolean_columns :manager, :active # set which columns should be ignored in ActiveRecord ignore_table_columns :attribute1, :attribute2 end

Supported Ruby

Gemfile gem "activerecord-oracle_enhanced-adapter", "~> 1.5.0" gem "ruby-oci8", "~> 2.1.0"

What is good thing to use Oracle with ActiveRecord?

Optimizer and prepared statements • Prepared statement can • Save your CPU time to parse sql statements • Make use of shared pool area • Prevent SQL injection • ActiveRecord 3.1 implemented prepared statements

Optimizer and prepared statement(cont) • Usually a prepared statement chooses only one "generic" execution plan • 11g - Adaptive cursor sharing The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. - Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)

How does it work with ActiveRecord? • ActiveRecord supports prepared statements since Rails 3.1, but not every method • Every sql statement will be modified to prepared statement when you use oracle enhanced adapter • Adaptive cursor sharing may address side effects of prepared statements by having multiple execution plans for one sql statement based on bind values

Example based on database usage survey $ rails generate model User database:string $ rails generate migration AddIndexToUser class AddIndexToUser < ActiveRecord::Migration def change add_index :users, :database end end $ rake db:migrate

Generate database usage data insert into users values (users_seq.nextval,'Oracle',sysdate,sysdate); begin for i in 1..50000 loop insert into users values (users_seq.nextval,dbms_random.string('X',10), sysdate,sysdate); end loop; end; / begin for i in 1..50000 loop insert into users values (users_seq.nextval,'PostgreSQL',sysdate, sysdate); end loop; end; /

User.where(:database => 'Oracle').last.id • ActiveRecord SELECT "USERS".* FROM "USERS" WHERE "USERS"."DATABASE" = 'Oracle' ORDER BY "USERS"."ID" DESC FETCH FIRST 1 ROWS ONLY • Oracle modifies sql statement SELECT "USERS".* FROM "USERS" WHERE "USERS"."DATABASE" = :"SYS_B_0" ORDER BY "USERS"."ID" DESC FETCH FIRST :"SYS_B_1" ROWS ONLY • Execution plan using adaptive cursor sharing TABLE ACCESS BY INDEX ROWID BATCHED USERS INDEX RANGE SCAN INDEX_USERS_ON_DATABASE

User.where(:database => 'PostgreSQL').last.id • ActiveRecord SELECT "USERS".* FROM "USERS" WHERE "USERS"."DATABASE" = 'PostgreSQL' ORDER BY "USERS"."ID" DESC FETCH FIRST 1 ROWS ONLY • Oracle modifies sql statement SELECT "USERS".* FROM "USERS" WHERE "USERS"."DATABASE" = :"SYS_B_0" ORDER BY "USERS"."ID" DESC FETCH FIRST :"SYS_B_1" ROWS ONLY • Execution plan using adaptive cursor sharing TABLE ACCESS FULL USERS

With Adaptive Cursor Sharing

Without Adaptive Cursor Sharing

Any incompatibility with Rails? • 4-1-stable test results $ rake test_oracle 3965 runs, 11064 assertions, 2 failures, 4 errors, 0 skips

2 failures, 4 errors?

Known Restrictions • Empty string as NULL • Identifier length <= 30 byte • "id" needs to set explicitly • No limit in sub queries • No release savepoint • CLOB/BLOB require specific implementation • Absence of LIMIT, OFFSET

Empty string as NULL $ ARCONN=oracle ruby -Itest test/cases/calculations_test.rb -n test_pluck_without_column_names 1) Failure: CalculationsTest#test_pluck_without_column_names [test/cases/calculations_test.rb:494]: --- expected +++ actual @@ -1 +1 @@ -[[1, "Firm", 1, nil, "37signals", nil, 1, nil, ""]] +[[1, "Firm", 1, nil, "37signals", nil, 1, nil, nil]] 1 runs, 1 assertions, 1 failures, 0 errors, 0 skips • "" expected but nil returned

"Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases" - Oracle® Database SQL Language Reference 12c Release 1 (12.1), July 2014

“Oracle7 currently treats a character value with a length of zero as null. However, this may not continue to be true in future versions of Oracle7.” –Oracle7 Server SQL Reference Release 7.3, February 1996

:null => false, :default => ""

Identifier length <= 30 byte $ ARCONN=oracle ruby -Itest test/cases/associations/has_and_belongs_to_many_associations_test.rb \ -n test_join_table_alias 1) Error: HasAndBelongsToManyAssociationsTest#test_join_table_alias: ActiveRecord::StatementInvalid: OCIError: ORA-00972: identifier is too long: SELECT "DEVELOPERS"."ID" AS t0_r0, "DEVELOPERS"."NAME" AS t0_r1, .. WHERE (projects_developers_projects_join.joined_on IS NOT NULL) • irb(main):001:0> 'projects_developers_projects_join'.size => 33

128 byte? No, not yet. SQL> desc all_objects Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(128) OBJECT_NAME NOT NULL VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NOT NULL NUMBER … • Only data dictionary length changed from 30 to 128 in 12c

"id" needs to set explicitly $ ARCONN=oracle ruby -Itest test/cases/serialized_attribute_test.rb \ -n test_json_db_null 1) Error: SerializedAttributeTest#test_json_db_null: ActiveRecord::StatementInvalid: OCIError: ORA-01400: cannot insert NULL into ("ARUNIT"."TOPICS"."ID"): INSERT INTO topics (content) VALUES(NULL) • Needs to change as follows INSERT INTO topics (id, content) VALUES(topics_seq.nextval, NULL)

No limit in sub queries $ ARCONN=oracle ruby -Itest test/cases/associations/eager_test.rb \ -n test_including_association_based_on_sql_condition_and_no_database_column 1) Error: EagerAssociationTest#test_including_association_based_on_sql_condition_and_no_database_column: ActiveRecord::StatementInvalid: OCIError: ORA-00907: missing right parenthesis: SELECT * FROM (SELECT owners.*, ( select p.pet_id from pets p where p.owner_id = owners.owner_id order by p.name desc limit 1 ) as last_pet_id FROM "OWNERS" ORDER BY "OWNERS"."OWNER_ID" ASC) WHERE ROWNUM <= 1 stmt.c:230:in oci8lib_210.so • This error message is kind of misleading...

No release savepoint $ ARCONN=oracle ruby -Itest test/cases/transactions_test.rb \ -n test_releasing_named_savepoints 1) Failure: TransactionTest#test_releasing_named_savepoints [test/cases/transactions_test.rb:408]: ActiveRecord::StatementInvalid expected but nothing was raised. • Do you need release savepoint?

Living with Rails

Rails 4.0 and 4.1 • AbstractAdapter::SchemaCreation behavior changed between beta and RC. • columns_for_distinct(columns, orders) • rails/rails#6792 • rails/arel#211 • I've learned Visitor patterns and how to use debugger

Rails 4.2 • Adequate Record • Removed BindSubstitution Class • Refactoring Columns • initialize_type_map • "Legacy Schema" support... • Foreign Key support • A lot of things to learn

Any Oracle new features?

Oracle 12c features for ActiveRecord • Better Top-N query support • VARCHAR2 length <= 32k • IDENTITY data type • JSON data type

Better Top-N query support • Mandatory features for pagination • Experimental implementation based on arel 5-0-stable • https://github.com/yahonda/arel/tree/sandbox_limit • Removed order_hacks from Arel • Will open a pull request to rails/arel

Current Arel visitors/oracle.rb def visit_Arel_Nodes_SelectStatement o, collector o = order_hacks(o) …… if o.limit o = o.dup limit = o.limit.expr collector << "SELECT * FROM (" collector = super(o, collector) collector << ") WHERE ROWNUM <= " return visit limit, collector end …… super end • Since ROWNUM is set before order by executed

Updated Arel visitors/oracle.rb def visit_Arel_Nodes_SelectStatement o, a if o.limit && o.lock o = o.dup o.limit = [] end super end • select for update statement not compatible with LIMIT

Current test visitors/test_oracle.rb it 'modifies order when there is distinct and first value' do # *sigh* select = "DISTINCT foo.id, FIRST_VALUE(projects.name) OVER (foo) AS alias_0__" stmt = Nodes::SelectStatement.new stmt.cores.first.projections << Nodes::SqlLiteral.new(select) stmt.orders << Nodes::SqlLiteral.new('foo') sql = @visitor.accept(stmt) sql.must_be_like %{ SELECT #{select} ORDER BY alias_0__ } end

Updated test visitors/test_oracle.rb • It has gone. • No "# *sigh" in any tests

Entrant.order("id ASC").limit(2) • Current Oracle SELECT * FROM (SELECT "ENTRANTS".* FROM "ENTRANTS" ORDER BY id ASC) WHERE ROWNUM <= 2 • Current PostgreSQL SELECT "entrants".* FROM "entrants" ORDER BY id ASC LIMIT 2 • Oracle with better Top-N support SELECT "ENTRANTS".* FROM "ENTRANTS" ORDER BY id ASC FETCH FIRST 2 ROWS ONLY

Ready to support • Absence of LIMIT, OFFSET by better Top-N query • "id" needs to set explicitly by IDENTITY datatype • CLOB/BLOB require specific implementation by 32k VARCHAR2

Wish list • Identifier length <= 128 byte • Empty string should not be NULL • Release savepoint support

"I'm interested, But Oracle installation must be painful."

rails-dev-box-runs-oracle • Fork of rails-dev-box • http://github.com/yahonda/rails-dev-box-runs-oracle • Add support to install Oracle 11g XE using Puppet • Work with x86-64 Vagrant and Virtualbox

Thank you