KEMBAR78
Oracle adapters for Ruby ORMs | PDF
+

Oracle adapters
for Ruby ORMs
Raimonds Simanovskis

   TietoEnator Alise




   @rsim     github.com/rsim
What is
 Ruby
  on
Rails?
Ruby is
   object-oriented
      dynamic
programming language

 simple from outside

   powerful inside
Ruby on Rails
 Web applications development framework
            Developed in Ruby

Extracted from 37signals Basecamp application
           Open source software

     Focused on developer productivity

    Agile software development approach
Main principles
 DRY - Don’t Repeat Yourself


Convention over Configuration


    Opinionated software
Components
Active Record (Model)
Action Controller
Action View
Ruby platforms
MRI 1.8.7


Ruby/YARV
            JRuby
  1.9.1


                    Rubinius   IronRuby   MacRuby



                    MagLev     BlueRuby
Ruby => Oracle
                             require 'oci8'
  Ruby application           OCI8.new('scott', 'tiger').exec(
                             'select * from emp') do |r|
                               puts r.join(',')
                             end
  ruby-oci8


Oracle [Instant]
                                Oracle Database
    Client
                   SQL*Net
JRuby => Oracle
                        require "java"
                        sql =
Ruby application        JavaSQL::DriverManager.getConnec
                        tion(db, user, password)
                        statement = sql.createStatement
                        status = statement.execute
                        "select * from parts;"
  JRuby                 rs = statement.getResultSet()




JDBC driver                Oracle Database
              SQL*Net
ActiveRecord
Oracle Enhanced
    Adapter
Ruby on Rails
            => Oracle
gem install activerecord-oracle_enhanced-adapter


database.yml
development:
  adapter: oracle_enhanced
  database: XE
  username: blogdemo
  password: blogdemo
SQL bind variables

                               Employee.find(1)
database.yml
                                           ActiveRecord
development:
  adapter: oracle_enhanced   SELECT * FROM employees
  database: XE               WHERE (employees.id = 1)
  username: blogdemo                       Oracle optimizer
  password: blogdemo
  cursor_sharing: force      SELECT * FROM employees WHERE
                             (employees.id = :SYS_B_0)
Oracle Data Types
    Ruby            Rails             Oracle
    Fixnum          :integer        NUMBER
     Float             :float        NUMBER
  BigDecimal       :decimal      NUMBER, DECIMAL
     Time         :datetime          DATE
     Time              :time         DATE
     Date              :date         DATE
     String           :string      VARCHAR2
     String            :text         CLOB
     String          :binary         BLOB
True/FalseClass    :boolean     NUMBER(1), CHAR(1)
ActiveRecord
          Extensions
     set_date_columns
                                 add_foreign_key
  set_datetime_columns
   set_boolean_columns
                                  add_synonym
     emulate_booleans
emulate_integers_by_column
                             add_primary_key_trigger
          _name
                               set_create_method
   ignore_table_columns
                               set_update_method
      table_comment
                               set_delete_method
         comment
Issues & Limitations
    Identifiers up to 30
                           slow Data Dictionary views
        characters


CLOB / BLOB usage in SQL
                             empty String == NULL
       statements


                              Mac OS X is not well
absence of LIMIT, OFFSET
                             supported by Oracle :(
Multi-platform
           support
                oracle_enhanced adapter




                         Ruby/YARV
MRI 1.8.6/1.8.7                           JRuby
                           1.9.1

ruby-oci8 1.x
                        ruby-oci8 2.x     JDBC
   or 2.x
DataMapper

• Alternative Ruby ORM
                persistence framework

• Not just for relational databases
DataMapper Model
DataMapper differences
Conditions




               Eager Loading
Identity Map


               Lazy Loading
DataMapper & Oracle
          DataMapper DataObjects adapter


                DataMapper Oracle adapter


DataObjects Oracle driver            DO JDBC Oracle driver


                     Ruby/YARV
MRI 1.8.6/1.8.7                              JRuby
                       1.9.1

ruby-oci8 2.x        ruby-oci8 2.x           JDBC
DataMapper & Oracle
Type mapping       Composite primary keys



   Legacy schemas




  Bind variables       CLOB and BLOB values
PL/SQL calls from
       Ruby
require "oci8"
conn = OCI8.new("hr","hr","xe")

cursor = conn.parse <<-EOS
BEGIN
  :return := test_uppercase(:p_string);
END;
EOS
cursor.bind_param(':p_string',"xxx",String)
cursor.bind_param(':return',nil,String,4000)
cursor.exec
puts cursor[':return']
cursor.close
ruby-plsql gem

gem install ruby-plsql

require "ruby-plsql"
plsql.connection = OCI8.new("hr","hr","xe")

puts plsql.test_uppercase('xxx')
ruby-plsql gem
plsql.connection = OCI8.new("hr","hr","xe")

plsql.test_uppercase('xxx')              # => "XXX"
plsql.test_uppercase(:p_string => 'xxx') # => "XXX"
plsql.test_copy("abc", nil, nil)         # => { :p_to => "abc",
                                         # :p_to_double => "abcabc" }
plsql.test_copy(:p_from => "abc", :p_to => nil, :p_to_double => nil)
                                         # => { :p_to => "abc",
                                         # :p_to_double => "abcabc" }
plsql.hr.test_uppercase('xxx')           # => "XXX"
plsql.test_package.test_uppercase('xxx') # => 'XXX'
plsql.hr.test_package.test_uppercase('xxx') # => 'XXX'

plsql.logoff
class Employee < ActiveRecord::Base
                  set_create_method do
                    plsql.employees_pkg.create_employee(
                      :p_first_name => first_name,
                      :p_last_name => last_name,

PL/SQL CRUD           :p_employee_id => nil
                    )[:p_employee_id]
procedures in     end
                  set_update_method do
    legacy          plsql.employees_pkg.update_employee(
                      :p_employee_id => id,
 applications         :p_first_name => first_name,
                      :p_last_name => last_name
     (with          )
                  end
ActiveRecord)     set_delete_method do
                    plsql.employees_pkg.delete_employee(
                      :p_employee_id => id
                    )
                  end
                end
Thanks!

     http://blog.rayapps.com
     http://github.com/rsim
http://groups.google.com/group/
         oracle-enhanced

Oracle adapters for Ruby ORMs

  • 1.
  • 2.
    Raimonds Simanovskis TietoEnator Alise @rsim github.com/rsim
  • 3.
    What is Ruby on Rails?
  • 4.
    Ruby is object-oriented dynamic programming language simple from outside powerful inside
  • 5.
    Ruby on Rails Web applications development framework Developed in Ruby Extracted from 37signals Basecamp application Open source software Focused on developer productivity Agile software development approach
  • 6.
    Main principles DRY- Don’t Repeat Yourself Convention over Configuration Opinionated software
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
    Ruby platforms MRI 1.8.7 Ruby/YARV JRuby 1.9.1 Rubinius IronRuby MacRuby MagLev BlueRuby
  • 12.
    Ruby => Oracle require 'oci8' Ruby application OCI8.new('scott', 'tiger').exec( 'select * from emp') do |r| puts r.join(',') end ruby-oci8 Oracle [Instant] Oracle Database Client SQL*Net
  • 13.
    JRuby => Oracle require "java" sql = Ruby application JavaSQL::DriverManager.getConnec tion(db, user, password) statement = sql.createStatement status = statement.execute "select * from parts;" JRuby rs = statement.getResultSet() JDBC driver Oracle Database SQL*Net
  • 14.
  • 15.
    Ruby on Rails => Oracle gem install activerecord-oracle_enhanced-adapter database.yml development: adapter: oracle_enhanced database: XE username: blogdemo password: blogdemo
  • 16.
    SQL bind variables Employee.find(1) database.yml ActiveRecord development: adapter: oracle_enhanced SELECT * FROM employees database: XE WHERE (employees.id = 1) username: blogdemo Oracle optimizer password: blogdemo cursor_sharing: force SELECT * FROM employees WHERE (employees.id = :SYS_B_0)
  • 17.
    Oracle Data Types Ruby Rails Oracle Fixnum :integer NUMBER Float :float NUMBER BigDecimal :decimal NUMBER, DECIMAL Time :datetime DATE Time :time DATE Date :date DATE String :string VARCHAR2 String :text CLOB String :binary BLOB True/FalseClass :boolean NUMBER(1), CHAR(1)
  • 18.
    ActiveRecord Extensions set_date_columns add_foreign_key set_datetime_columns set_boolean_columns add_synonym emulate_booleans emulate_integers_by_column add_primary_key_trigger _name set_create_method ignore_table_columns set_update_method table_comment set_delete_method comment
  • 19.
    Issues & Limitations Identifiers up to 30 slow Data Dictionary views characters CLOB / BLOB usage in SQL empty String == NULL statements Mac OS X is not well absence of LIMIT, OFFSET supported by Oracle :(
  • 20.
    Multi-platform support oracle_enhanced adapter Ruby/YARV MRI 1.8.6/1.8.7 JRuby 1.9.1 ruby-oci8 1.x ruby-oci8 2.x JDBC or 2.x
  • 21.
    DataMapper • Alternative RubyORM persistence framework • Not just for relational databases
  • 22.
  • 23.
    DataMapper differences Conditions Eager Loading Identity Map Lazy Loading
  • 24.
    DataMapper & Oracle DataMapper DataObjects adapter DataMapper Oracle adapter DataObjects Oracle driver DO JDBC Oracle driver Ruby/YARV MRI 1.8.6/1.8.7 JRuby 1.9.1 ruby-oci8 2.x ruby-oci8 2.x JDBC
  • 25.
    DataMapper & Oracle Typemapping Composite primary keys Legacy schemas Bind variables CLOB and BLOB values
  • 26.
    PL/SQL calls from Ruby require "oci8" conn = OCI8.new("hr","hr","xe") cursor = conn.parse <<-EOS BEGIN :return := test_uppercase(:p_string); END; EOS cursor.bind_param(':p_string',"xxx",String) cursor.bind_param(':return',nil,String,4000) cursor.exec puts cursor[':return'] cursor.close
  • 27.
    ruby-plsql gem gem installruby-plsql require "ruby-plsql" plsql.connection = OCI8.new("hr","hr","xe") puts plsql.test_uppercase('xxx')
  • 28.
    ruby-plsql gem plsql.connection =OCI8.new("hr","hr","xe") plsql.test_uppercase('xxx') # => "XXX" plsql.test_uppercase(:p_string => 'xxx') # => "XXX" plsql.test_copy("abc", nil, nil) # => { :p_to => "abc", # :p_to_double => "abcabc" } plsql.test_copy(:p_from => "abc", :p_to => nil, :p_to_double => nil) # => { :p_to => "abc", # :p_to_double => "abcabc" } plsql.hr.test_uppercase('xxx') # => "XXX" plsql.test_package.test_uppercase('xxx') # => 'XXX' plsql.hr.test_package.test_uppercase('xxx') # => 'XXX' plsql.logoff
  • 29.
    class Employee <ActiveRecord::Base set_create_method do plsql.employees_pkg.create_employee( :p_first_name => first_name, :p_last_name => last_name, PL/SQL CRUD :p_employee_id => nil )[:p_employee_id] procedures in end set_update_method do legacy plsql.employees_pkg.update_employee( :p_employee_id => id, applications :p_first_name => first_name, :p_last_name => last_name (with ) end ActiveRecord) set_delete_method do plsql.employees_pkg.delete_employee( :p_employee_id => id ) end end
  • 30.
    Thanks! http://blog.rayapps.com http://github.com/rsim http://groups.google.com/group/ oracle-enhanced