Raimonds Simanovskis

Subscribe to Raimonds Simanovskis feed
Updated: 13 hours 37 min ago

How to setup Ruby and new Oracle Instant Client on Leopard

Wed, 2008-04-23 16:00

UPDATE: New version of instructions for Snow Leopard is available here.

Introduction

We waited for it long, long time and finally it has arrived – Oracle Instant Client for Intel Mac. I was lucky to test beta version of the client already for the last couple of weeks and so far everything was working OK. Therefore as the final version is now available to everybody I am here rewriting my previous instructions on how to get Ruby working with Oracle on Mac.

Installing Oracle Instant Client for Intel Mac

At first you need to download Oracle Instant Client for Intel Mac. Download “Instant Client Package – Basic” and “Instant Client Package – SDK” and also I suggest “Instant Client Package – SQL*Plus” if you would like to have command line sqlplus utility.

Unzip downloaded archives and move it where you would like to have it – I am keeping it in /usr/local/oracle/instantclient_10_2. Then go to this directory and make symbolic links for dynamic libraries

sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib
sudo ln -s libocci.dylib.10.1 libocci.dylib

Then I recommend to create and place somewhere your tnsnames.ora file where you will keep your database connections definitions – I place this file in directory /usr/local/oracle/network/admin.

Then finally you need to set up necessary environment variables – I place the following definitions in my .bash_profile script:

export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2"
export SQLPATH="/usr/local/oracle/instantclient_10_2"
export TNS_ADMIN="/usr/local/oracle/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export PATH=$PATH:$DYLD_LIBRARY_PATH

Use your path to Oracle Instant Client if it differc from /usr/local/oracle/instantclient_10_2. And as you see I also define NLS_LANG environment variable – this is necessary if your database is not in UTF8 encoding but in Ruby you want to get UTF-8 encoded strings from the database. Specifying this NLS_LANG environment variable you will force that Oracle Instant Client will do character set translation.

After these steps relaunch Terminal application (so that new environment variables are set), specify database connection in tnsnames.ora file and try if you can access your database with sqlplus from command line.

Ruby installation

If you are using Leopard then I assume that you are using preinstalled Ruby which is the simplest option. I tried to compile Ruby from sources on Mac OS X Leopard but when I compared performance then original Ruby was a little bit faster on some benchmarks and therefore I sticked with original one.

Compile and install ruby-oci8

Download the latest version of ruby-oci8 (version 1.0.0 at time of writing this post).

As Oracle Instant Client is available just for Intel i386 architecture you need to change in file /usr/lib/ruby/1.8/universal-darwin9.0/rbconfig.rb line 17 to:

'-arch i386'

Then go to directory where you extracted ruby-oci8 source and execute ruby-oci8 standard installation sequence:

ruby setup.rb config
make
sudo make install

After that you can change back file /usr/lib/ruby/1.8/universal-darwin9.0/rbconfig.rb line 17 to:

'-arch ppc -arch i386'

Now try

ruby -r oci8 -e "OCI8.new('scott', 'tiger','orcl').exec('select * from emp') do |r| puts r.join(','); end"

or similar to verify that you can access Oracle database from ruby.

That’s it! Please write in comments if something is not working according to these instructions.

Categories: Development

Video from Euruko 2008

Fri, 2008-04-04 16:00

I made short video from Euruko 2008 conference where you can see Matz, Koichi, JRuby guys, DrNic ar me as well :)

I posted my presentation slides in my previous post.

Categories: Development

My presentation on using Ruby with Oracle at Euruko conference

Wed, 2008-04-02 16:00

I gave short presentation about “Using Ruby with Oracle” at European Ruby conference Euruko 2008. You can download presentation slides at their site. My collegue took a video of my presentation so probably after some time I will post it as well :)

Either because of this presentation or maybe just because more people are interested in Ruby on Oracle the number of visits to this blog is fast growing during the last days. Which makes me more motivated to do more investigations in Ruby and Oracle area.

One area of further research could be standardization of different ActiveRecord Oracle adapter patches – otherwise now I have different patches in each project and this becomes quite hard to manage.

Categories: Development

ruby-plsql gem: simple Ruby API for PL/SQL procedures

Fri, 2008-03-14 17:00

In several projects I have used Ruby and Rails to access legacy Oracle databases which have both tables with data as well as PL/SQL packages with lot of existing business logic. Sometimes it is easier just to redo business logic in Ruby but sometimes you need to reuse existing PL/SQL packages and procedures.

Let’s use this simple PL/SQL function as an example:

CREATE OR REPLACE FUNCTION test_uppercase
  ( p_string VARCHAR2 )
  RETURN VARCHAR2
IS
BEGIN
  RETURN UPPER(p_string);
END test_uppercase;

If you are using ruby-oci8 library to connect to Oracle then you can call this PL/SQL procedure from Ruby in the following way (more details can be found in ruby-oci8 documentation):

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

This does not look like Ruby-style as it is too long and complex code which just calls one simple PL/SQL function :(

Wouldn’t it be much nicer if you could get the same result with the following code?

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

This idea served as inspiration to create ruby-plsql gem which would provide such nice Ruby API to access existing PL/SQL procedures and functions. Here are other examples how you can use it.

Call procedure with named parameters:

plsql.test_uppercase(:p_string => 'xxx')

Call procedure with specified schema:

plsql.hr.test_uppercase('xxx')

Call procedure from specified package in specified schema:

plsql.hr.test_package.test_uppercase('xxx')

Call procedure with output arguments:

plsql.test_copy("abc", nil, nil) # returns output arguments { :p_to => "abc", :p_to_double => "abcabc" }

To install this gem execute

sudo gem install ruby-plsql

As I mentioned before it also requires ruby-oci8 library to access Oracle. I have plans to create JRuby / JDBC support in the future versions of this gem.

Current limitation is that this API support just NUMBER, VARCHAR2, DATE and TIMESTAMP types for PL/SQL procedures which are dynamically mapped to Ruby Fixnum/Bignum/Float, String, DateTime and Time types.

If you find this gem interesting then please let me know in comments what additional features you would like to have for it.

Categories: Development

How to log DB statistics at info log level in Rails 2.0

Fri, 2008-02-22 16:00

When I switched some my applications to Rails 2.0 I noticed that I do not see anymore DB execution time statistics in my production log file (which I set at info level). Now I saw zero database processing time for all my requests, e.g.:

Completed in 0.22203 (4 reqs/sec) | Rendering: 0.13277 (59%) | DB: 0.00000 (0%)

When investigating why is it so I found the following small changeset that is done in Rails 2.0 – http://dev.rubyonrails.org/changeset/8162.

In Rails 1.2 at debug level you got both SQL and database processing time in log file and at info level you got just database processing time. In Rails 2.0 at debug level you get the same in log file but at info level you do not get database processing time.

So if you are like me and want to analyze your production log file with tools like pl_analyze then you can monkey patch ActiveRecord to get back the previous behaviour as in Rails 1.2:

module ActiveRecord
  module ConnectionAdapters
    class AbstractAdapter
      protected
        def log(sql, name)
          if block_given?
            # RSI: changed to get DB statistics in log file at info level
            # if @logger and @logger.debug?
            if @logger and @logger.level <= Logger::INFO 
              result = nil
              seconds = Benchmark.realtime { result = yield }
              @runtime += seconds
              log_info(sql, name, seconds)
              result
            else
              yield
            end
          else
            log_info(sql, name, 0)
            nil
          end
        rescue Exception => e
          # Log message and raise exception.
          # Set last_verification to 0, so that connection gets verified
          # upon reentering the request loop
          @last_verification = 0
          message = "#{e.class.name}: #{e.message}: #{sql}"
          log_info(message, name, 0)
          raise ActiveRecord::StatementInvalid, message
        end
    end
  end
end

And now you will get database processing statistics back in log file at info level:

Completed in 0.21853 (4 reqs/sec) | Rendering: 0.10835 (49%) | DB: 0.09657 (44%)
Categories: Development

Make Rails database migrations faster on Oracle

Fri, 2008-01-25 16:00

When using Ruby on Rails on Oracle I noticed that my database migration tasks are much slower than on MySQL.

I just found the cause for that – rake db:schema:dump task was taking very long time on my Oracle databases (and this task is executed at the end of rake db:migrate). As a result of this task Oracle Rails adapter is executing

SELECT LOWER(table_name) FROM user_tables

statement. If your database contains a lot of other schemas with a lot of tables (as in my case I deploy Rails user schema in Oracle E-Business Suite database) then this statement is quite slow (it took more than 10 seconds in my case).

As all Rails tables are located in Rails user schema then you can get the same result with

SELECT LOWER(table_name) FROM all_tables WHERE owner = SYS_CONTEXT('userenv','session_user')

which now executes much faster than original statement.

So you can make the following patch for Oracle Rails adapter to make this change

module ActiveRecord
  module ConnectionAdapters
    class OracleAdapter
      def tables(name = nil)
        select_all("select lower(table_name) from all_tables where owner = sys_context('userenv','session_user')").inject([]) do | tabs, t |
          tabs << t.to_a.first.last
        end
      end
    end
  end
end

and include it in environment.rb file or in some separate patch file which you load at Rails startup.

P.S. I have published all my patches for Oracle Rails 2.0 adapter. You can download this file, place it in Rails application lib directory and then just include

require "oracle_adapter_20_patch"

in your environment.rb file. Look at comments in this file to find out what each patch is doing.

Categories: Development

Fix for Rails 2.0 on Oracle with database session store

Mon, 2008-01-07 16:00

As I started to explore Rails 2.0 I tried to migrate one application to Rails 2.0 which is using Oracle as a database. Here are some initial tips for Rails 2.0 on Oracle that I found out.

Oracle adapter is no more included in Rails 2.0 so you need to install it separately. It is also not yet placed on gems.rubyforge.org therefore you need to install it with:

sudo gem install activerecord-oracle-adapter --source http://gems.rubyonrails.org

The next issue that you will get is error message “select_rows is an abstract method”. You can find more information about it in this ticket. As suggested I fixed this issue with the following Oracle adapter patch that I call from anvironment.rb file:

module ActiveRecord
  module ConnectionAdapters
    class OracleAdapter
      def select_rows(sql, name = nil)
        result = select(sql, name)
        result.map{ |v| v.values}
      end
    end
  end
end

And then I faced very strange behaviour that my Rails application was not working with database session store – no session data was saved. When I changed session store to cookies then everything worked fine.

When I continued investigation I found out that the issue was that for each new session new row was created in “sessions” table but no session data was saved in “data” column. As “data” column is text field which translates to CLOB data type in Oracle then it is not changed in Oracle adapter by INSERT or UPDATE statements but with special “write_lobs” after_save callback (this is done so because in Oracle there is limitation that literal constants in SQL statements cannot exceed 4000 characters and therefore such hack with after_save callback is necessary). And then I found that class CGI::Session::ActiveRecordStore::Session (which is responsible for database session store) does not have this write_lobs after_save filter. Why so?

As I understand now in Rails 2.0 ActiveRecord class definition sequence has changed – now at first CGI::Session::ActiveRecordStore::Session class is defined which inherits from ActiveRecord::Base and only afterwards OracleAdapter is loaded which adds write_lobs callback to ActiveRecord::Base but at this point it is not adding this callback to already defined Session class. As in Rails 1.2 OracleAdapter was loaded together with ActiveRecord and before Session class definition then there was no such issue.

So currently I solved this issue with simple patch in environment.rb file:

class CGI::Session::ActiveRecordStore::Session 
  after_save :write_lobs
end

Of course it would be nicer to force that OracleAdapter is loaded before CGI::Session::ActiveRecordStore::Session definition (when ActionPack is loaded). If somebody knows how to do that please write a comment :)

Categories: Development

Some issues with Oracle views as ActiveRecord source

Thu, 2007-11-15 16:00

I am using Ruby on Rails to publish data from existing “legacy” application on Oracle database which already have existing complex data model. I am defining additional database views on existing legacy data to which I grant select rights to Rails schema. And I am using Rails conventions when defining these views – view names as pluralized / underscored version of Rails model name, ID column as primary key and %_ID columns as foreign keys.

Typically this works quite well and I can use Rails find methods to automatically generate SQL SELECTs from these views. But for some legacy data I got the problem with Oracle number type mapping to Ruby integer type.

Rails standard convention for database NUMBER type mapping is the following:

  • NUMBER with specified scale and without precision (e.g. NUMBER) is mapped to :integer
  • NUMBER with specified scale and with precision (e.g. NUMBER) is mapped to :decimal
  • NUMBER without scale and precision (just NUMBER) is mapped to :decimal

If primary keys and foreign keys in legacy tables are defined as e.g. NUMBER then everything is OK and they will be mapped to :integer in Rails. But if primary keys or foreign keys in legacy tables are defined as NUMBER then they will be mappec to :decimal in Rails.

And what happens if e.g. primary key is mapped to :decimal in Rails? Then, for example, you get that customer.id is equal to “123.0” and you get ugly URLs like “/customers/123.0”.

One workaround is to use customer.id.to_i everywhere but it is quite annoying. Therefore I created patch for Oracle adapter (this is tested with Rails 1.2.3) which always sets data type as :integer for all primary keys (column name ID) and foreign keys (column name like %_ID). This includes also date columns patch that I wrote about previously.

module ActiveRecord::ConnectionAdapters
  class OracleColumn
    def simplified_type(field_type)
      return :boolean if OracleAdapter.emulate_booleans && field_type == 'NUMBER(1)'
      case self.name
        # RSI: treat columns which end with 'date' as ruby date columns
        when /date$/i then :date
        # RSI: removed 'date' from regex
        when /time/i then :datetime
        # RSI: treat id columns (primary key) as integer
        when /^id$/i then :integer
        # RSI: treat _id columns (foreign key) as integer
        when /_id$/i then :integer
        else super
      end
    end
  end

  # RSI: patch to change selected results NUMBER to integer for primary and foreign keys
  class OracleAdapter
    def select(sql, name = nil)
      cursor = execute(sql, name)
      cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
      rows = []

      while row = cursor.fetch
        hash = Hash.new

        cols.each_with_index do |col, i|
          hash[col] =
            case row[i]
            when OCI8::LOB
              name == 'Writable Large Object' ? row[i]: row[i].read
            when OraDate
              (row[i].hour == 0 and row[i].minute == 0 and row[i].second == 0) ?
              row[i].to_date : row[i].to_time
            else row[i]
            end unless col == 'raw_rnum_'
          # RSI: patch - convert to integer if column is ID or ends with _ID
          hash[col] = hash[col].to_i if (col =~ /^id$/i || col =~ /_id$/i) && hash[col]
        end

        rows << hash
      end

      rows
    ensure
      cursor.close if cursor
    end
  end  
end

I have not yet verified this with Rails 2.0. And probably I will collect all my Oracle adapter patches and will release it as plugin. Is anybody interested in this?

Categories: Development

Oracle E-Business Suite Authentication in Ruby

Mon, 2007-10-29 17:00

I was developing Ruby on Rails application that access data from existing Oracle E-Business Suite application and I wanted to add to this application possibility for users to authenticate themselves by their existing Oracle E-Business Suite user names and passwords.

Oracle is not publicly providing algorythms which are used for user passwords encryption (which are stored in FND_USER table) and therefore some googling was necessary. I was lucky to find Jira eBusiness Suite Authenticator with full source code in Java which also included all Oracle E-Business Suite password encryption / decryption functions. Actually it seems that the >1000 lines source code is decompiled from Oracle Java classes as there are no comments and variable names don’t tell anything.

But as I did not have any better source material I started Ruby rewrite of this code and happily managed to finish it in couple of days. As a result I created Oracle EBS Authentication plugin and you can find out in README file how to install it.

Usage of this plugin is quite simple – here is an example:

database_name = ActiveRecord::Base.connection.current_database
authenticator = OracleEbsAuthentication::Authenticator.new(database_name)
if authenticator.validate_user_password(login, password)
  # user authenticated
  if authenticator.user_reponsibilities.include? "System Administrator"
    # user has System Administrator role
  end
  # ...
else
  # authentication failed
end

BTW Oracle EBS password encryption approach has quite a lot of weaknesses. So if you provide SQL*Net access to your Oracle EBS database then anyone with valid Oracle EBS user name and password can decrypt APPS database user password and also can decrypt passwords of all other users. You can have a deeper look in plugin source code to understand how it can be done :)

Categories: Development

Rails patch for Oracle CLOB defaults

Wed, 2007-08-29 16:00

If you are using Rails 1.2.3 with Oracle database then you might find that text attributes (which map to Oracle CLOB data type) get invalid default values – e.g. you might find that when you create new record it will get “empty_clob()” as default text attribute value.

I found out that this issue is corrected in current edge Rails. As I still primarily use Rails 1.2.3 I created the following patch according to the changes that are done in edge Rails. You can put it in environment.rb file or better put into a separate file and require it in environment.rb file.

# RSI: text defaults handling from http://dev.rubyonrails.org/ticket/7344 & http://dev.rubyonrails.org/changeset/6090
module ActiveRecord::ConnectionAdapters
  class OracleAdapter
    def quote(value, column = nil) #:nodoc:
      # RSI: patched
      if value && column && [:text, :binary].include?(column.type)
        %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
      else
        super
      end
    end

    def columns(table_name, name = nil) #:nodoc:
      (owner, table_name) = @connection.describe(table_name)
      table_cols = <<-SQL
        select column_name as name, data_type as sql_type, data_default, nullable,
               decode(data_type, 'NUMBER', data_precision,
                                 'FLOAT', data_precision,
                                 'VARCHAR2', data_length,
                                  null) as limit,
               decode(data_type, 'NUMBER', data_scale, null) as scale
          from all_tab_columns
         where owner      = '#{owner}'
           and table_name = '#{table_name}'
         order by column_id
      SQL
      select_all(table_cols, name).map do |row|
        limit, scale = row['limit'], row['scale']
        if limit || scale
          row['sql_type'] << "(#{(limit || 38).to_i}" + ((scale = scale.to_i) > 0 ? ",#{scale})" : ")")
        end
        # clean up odd default spacing from Oracle
        if row['data_default']
          row['data_default'].sub!(/^(.*?)\s*$/, '\1')
          row['data_default'].sub!(/^'(.*)'$/, '\1')
          # RSI: patched
          row['data_default'] = nil if row['data_default'] =~ /^(null|empty_[bc]lob\(\))$/i
        end
        OracleColumn.new(oracle_downcase(row['name']),
                         row['data_default'],
                         row['sql_type'],
                         row['nullable'] == 'Y')
      end
    end

    # RSI: added
    def add_column_options!(sql, options) #:nodoc: 
      # handle case of defaults for CLOB columns, which would otherwise get "quoted" incorrectly 
      if options_include_default?(options) && (column = options[:column]) && column.type == :text 
        sql << " DEFAULT #{quote(options.delete(:default))}"  
      end 
      super
    end

  end
end
Categories: Development

How to setup Ruby and Oracle client on Intel Mac

Sun, 2007-08-26 16:00

UPDATE: New version of this instruction for Intel Macs with Leopard is available here.
New version of instructions for Snow Leopard is available here.

Introduction

I have been using Oracle technologies for many years but just some time ago discovered Ruby and Rails. As I had ideas how to make Ruby on Rails frontends for existing Oracle based systems I started to explore how to use Ruby on Rails together with Oracle databases.

Just recently I switched from PC notebook to MacBook Pro and unfortunately found out that Oracle Instant Client is not yet released for Intel Macs. As there is no promise when it will be released and as I could not wait for that I decided to make old PowerPC version of Oracle Instant Client to run on Intel Mac. As I didn’t find any good description how to do that I decided to write description of it by myself – hopefully it will help others.
h3. Universal or “fat” binary Ruby

The first thing is that you need to get “universal” or “fat” binary installation of Ruby – it means that it contains both Intel and PowerPC (PPC) binary code. You will need PPC version of Ruby when you will work with Oracle and you will need Intel version of Ruby when you will do other things. PPC code is running in emulation mode on Intel processors and therfore is slower as well as there are some compatibility issues with some other libraries (I will mention later what issues I found out).

It is possible to get and install precompiled Ruby either from MacPorts or using Ruby One-Click Installer. But I prefer to compile Ruby from source code as it gives me more control what is installed and where. I used Hivelogic guide to compile Ruby from source code.

Here are my additional notes what I changed to Hivelogic guide to make “fat” binaries.

Installing “readline”

At first you need to install “readline” shared libraries. At first I tried to make them from “readline” library source code but I always got just Intel binaries. Therefore I found and downloaded “readline” library installer package with universal binaries and installed it. If you previously installed Intel binary of “readline” library then it is better to restart Mac to ensure that new dynamic library will be loaded.

Compiling Ruby

Next you need to download and extract Ruby 1.8.6 source code.

Before running “configure” command I made the following change in “configure” file:

# Choose a default set of architectures based upon platform.
case "$target_os" in
darwin*)
    TARGET_ARCHS="*ppc i386*"
    ;;

You can specify this also on command line but this change ensures that you will not forget it :) Then you need to run “configure” script with additional parameter at the end:

./configure --prefix=/usr/local --enable-pthread --with-readline-dir=/usr/local \
--enable-shared --enable-fat-binary

After running “make” hopefully you will get everything compiled and you should get “fat” ruby binary. You can verify it with the following command and should see the following result:

$ file ruby
ruby: Mach-O universal binary with 2 architectures
ruby (for architecture ppc):    Mach-O executable ppc
ruby (for architecture i386):   Mach-O executable i386

If you do not see that ruby binary contains both ppc and i386 executables then something went wrong. If it is OK then you can do “sudo make install” to install binaries in target directories.

Make PPC and “fat” versions of Ruby

As you will need to be able to force to run PPC version of Ruby later then we need to extract PPC executable in a separate file and store original “fat” binary in another file:

ditto -arch ppc /usr/local/bin/ruby /usr/local/bin/ruby_ppc
mv /usr/local/bin/ruby /usr/local/bin/ruby_fat

Then I recommend to create simple scripts that will help you to switch between “fat” and PPC versions of Ruby:

ppc_ruby.sh:

#!/bin/bash
sudo ln -fs /usr/local/bin/ruby_ppc /usr/local/bin/ruby

fat_ruby.sh:

#!/bin/bash
sudo ln -fs /usr/local/bin/ruby_fat /usr/local/bin/ruby

So when you need to have PPC version of Ruby then run “ppc_ruby.sh” script and when you need “fat” version (which will actually run Intel binary) then run “fat_ruby.sh” script.

Install Oracle Instant Client for PPC

Install Oracle Instant Client according to the following description.

Compile and install ruby-oci8

I used the following description as a basis but some additional changes were needed for ruby-oci8-1.0.0-rc3 compilation.

Open ruby-oci8 README file and find section “=== Intel Mac” where are described what to do to compile ruby-oci8 on Intel Mac.

Before running any ruby scripts you need to run ppc_ruby.sh script to switch to PPC binary.

If you compiled “fat” Ruby from source code then you need to modify file /usr/local/lib/ruby/1.8/fat-darwin8.10.1/rbconfig.rb according to README file (make backup of file before modifications). If you installed Ruby from binary distribution then find where is located your rbconfig.rb file.

  • find lines with CONFIG[“CFLAGS”] and CONFIG[“LDFLAGS”] and CONFIG[“ARCH_FLAG”]
  • remove “-arch i386” if present (was not present in my case)
  • and add “-arch ppc” to all of these lines.

Make and install ruby-oci8 and if it finishes successfully then restore rbconfig.rb file from backup.

Try

ruby -r oci8 -e "OCI8.new('scott', 'tiger','orcl').exec('select * from emp') do |r| puts r.join(','); end"

or similar to verify that you can access Oracle database from ruby.

Congratulations! You have managed to connect to Oracle DB from Ruby on Intel Mac! If you got some issues at some point then please write it in comments and I will try to help with that.

Post installation notes

If you previously installed (or if you will install in future) any Ruby gems with native extensions (e.g. Mongrel or MySQL C API) then you need to reinstall these gems with “fat” Ruby (i.e. running “fat_ruby.sh” script and then “sudo gem install”). It will ensure that all gems will also have “fat” native extensions so that you can run them both in PPC Ruby and Intel Ruby.

Known issues with PPC Ruby

So far I have got the following issues with running PPC version of Ruby:

  • Capistrano is failing to make SSH connection when running in PPC Ruby (I was testing with new Capistrano 2.0). Therefore you need always to switch to “fat” Ruby before running “cap deploy”.
  • As I also wanted that MySQL is running under PPC I got some issues with MySQL native extensions compilation – I solved them using the following description.
  • ruby-ldap connections were failing when running in PPC Ruby within Ruby on Rails. I managed to fix it by putting “require ldap” as a first line in config/environment.rb file.
Categories: Development

How to explicitly set Oracle DATE column as Ruby Date attribute

Sun, 2007-08-26 16:00

As you probably have noticed Oracle has just one DATE type for table columns which is supposed both for storing just dates and dates with time. From the other side Ruby has different classes Date and Time. If you are using Rails then Rails tries to guess from the database column types what are the classes for corresponding object attributes in Ruby. And as both dates and dates with time appear as DATE columns Rails has difficulties to guess whether it should be Ruby Date or Ruby Time.

Current Rails Oracle adapter has the following workaround implemented:

# * Oracle uses DATE or TIMESTAMP datatypes for both dates and times.
#   Consequently some hacks are employed to map data back to Date or Time
#   in Ruby. If the column_name ends in _time it's created as a Ruby Time.
#   Else if the hours/minutes/seconds are 0, I make it a Ruby Date. Else
#   it's a Ruby Time. This is a bit nasty - but if you use Duck Typing
#   you'll probably not care very much. In 9i and up it's tempting to
#   map DATE to Date and TIMESTAMP to Time, but too many databases use
#   DATE for both. Timezones and sub-second precision on timestamps are
#   not supported.

This workaround is problematic if you have date columns which can be NULL. In this case Rails cannot determine if this is date or datetime column. And if you use standard scaffolds or, for example, ActiveScaffold, it will use datetime_select helper and not date_select helper for this column – which means that you will be asked to specify also time for new dates.

Therefore, as I typically name all date columns with _DATE at the end, I created a patch which makes all such columns to be handled as Ruby Date attributes:

# RSI: OracleAdapter patch - treat columns which end with 'date' as ruby date type
module ActiveRecord::ConnectionAdapters
  class OracleColumn
    def simplified_type(field_type)
      return :boolean if OracleAdapter.emulate_booleans && field_type == 'NUMBER(1)'
      case self.name
        # RSI: treat columns which end with 'date' as ruby date columns
        when /date$/i then :date
        # RSI: removed 'date' from regex
        when /time/i then :datetime
        else super
      end
    end
  end
end

Include this in environment.rb file or put it into separate file in e.g. lib directory and require it in environment.rb file.

Categories: Development

Pages