Ray::Apps.blog

Posts tagged with "rails"

August 09, 2011

Oracle enhanced adapter 1.4.0 and Readme Driven Development

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

I just released Oracle enhanced adapter version 1.4.0 and here is the summary of main changes.

Rails 3.1 support

Oracle enhanced adapter GitHub version was working with Rails 3.1 betas and release candidate versions already but it was not explicitly stated anywhere that you should use git version with Rails 3.1. Therefore I am releasing new version 1.4.0 which is passing all tests with latest Rails 3.1 release candidate. As I wrote before main changes in ActiveRecord 3.1 are that it using prepared statement cache and using bind variables in many statements (currently in select by primary key, insert and delete statements) which result in better performance and better database resources usage.

To follow up how Oracle enhanced adapter is working with different Rails versions and different Ruby implementations I have set up Continuous Integration server to run tests on different version combinations. At the moment of writing everything was green :)

Other bug fixes and improvements

Main fixes and improvements in this version are the following:

  • On JRuby I switched from using old ojdbc14.jar JDBC driver to latest ojdbc6.jar (on Java 6) or ojdbc5.jar (on Java 5). And JDBC driver can be located in Rails application ./lib directory as well.

  • RAW data type is now supported (which is quite often used in legacy databases instead of nowadays recommended CLOB and BLOB types).

  • rake db:create and rake db:drop can be used to create development or test database schemas.

  • Support for virtual columns in improved (only working on Oracle 11g database).

  • Default table, index, CLOB and BLOB tablespaces can be specified (if your DBA is insisting on putting everything in separate tablespaces :)).

  • Several improvements for context index additional options and definition dump.

See list of all enhancements and bug fixes

If you want to have a new feature in Oracle enhanced adapter then the best way is to implement it by yourself and write some tests for that feature and send me pull request. In this release I have included commits from five new contributors and two existing contributors - so it is not so hard to start contributing to open source!

Readme Driven Development

One of the worst parts of Oracle enhanced adapter so far was that for new users it was quite hard to understand how to start to use it and what are all additional features that Oracle enhanced adapter provides. There were many blog posts in this blog, there were wiki pages, there were posts in discussion forums. But all this information was in different places and some posts were already outdated and therefore for new users it was hard to understand how to start.

After reading about Readme Driven Development and watching presentation about Readme Driven Development I knew that README of Oracle enhanced adapter was quite bad and should be improved (in my other projects I am already trying to be better but this was my first one :)).

Therefore I have written new README of Oracle enhanced adapter which includes main installation, configuration, usage and troubleshooting tasks which previously was scattered across different other posts. If you find that some important information is missing or outdated then please submit patches to README as well so that it stays up to date and with relevant information.

If you have any questions please use discussion group or report issues at GitHub or post comments here.

January 05, 2011

Oracle enhanced adapter 1.3.2 is released

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

I just released Oracle enhanced adapter version 1.3.2 with latest bug fixes and enhancements.

Bug fixes and improvements

Main fixes and improvements are the following:

  • Previous version 1.3.1 was checking if environment variable TNS_NAME is set and only then used provided database connection parameter (in database.yml) as TNS connection alias and otherwise defaulted to connection to localhost with provided database name. This was causing issues in many setups.
    Therefore now it is simplified that if you provide only database parameter in database.yml then it by default will be used as TNS connection alias or TNS connection string.
  • Numeric username and/or password in database.yml will be automatically converted to string (previously you needed to quote them using "...").
  • Database connection pool and JNDI connections are now better supported for JRuby on Tomcat and JBoss application servers.
  • NLS connection parameters are supported via environment variables or in database.yml. For example, if you need to have NLS_DATE_FORMAT in your database session to be DD-MON-YYYY then either you specify nls_date_format: DD-MON-YYYY in database.yml for particular database connection or set ENV['NLS_DATE_FORMAT'] = 'DD-MON-YYYY' in e.g. config/initializers/oracle.rb. You can see the list of all NLS parameters in source code.
    It might be necessary to specify these NLS session parameters only if you work with some existing legacy database which has, for example, some stored procedures that require particular NLS settings. If this is new database just for Rails purposes then there is no need to change any settings.
  • If you have defined foreign key constraints then they are now correctly dumped in db/schema.rb after all table definitions. Previously they were dumped after corresponding table which sometimes caused that schema could not be recreated from schema dump because it tried to load constraint which referenced table which has not yet been defined.
  • If you are using NCHAR and NVARCHAR2 data types then now NCHAR and NVARCHAR2 type values are correctly quoted with N'...' in SQL statements.

Upcoming changes in Rails 3.1

Meanwhile Oracle enhanced adapter is updated to pass all ActiveRecord unit tests in Rails development master branch and also updated according to Arel changes. Arel library is responsible for all SQL statement generation in Rails 3.0.

Rails 3.0.3 is using Arel version 2.0 which was full rewrite of Arel 1.0 (that was used initial Rails 3.0 version) and as a result of this rewrite it is much faster and now Rails 3.0.3 ActiveRecord is already little bit faster than in ActiveRecord in Rails 2.3.

There are several improvements in Rails master branch which are planned for Rails 3.1 version which are already supported by Oracle enhanced adapter. One improvement is that ActiveRecord will support prepared statement caching (initially for standard simple queries like find by primary key) which will reduce SQL statement parsing time and memory usage (and probably Oracle DBAs will complain less about Rails dynamic SQL generation :)). The other improvement is that ActiveRecord will correctly load included associations with more than 1000 records (which currently fails with ORA-01795 error).

But I will write more about these improvements sometime later when Rails 3.1 will be released :)

Install

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.7 or Ruby 1.9.2 or JRuby 1.5) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or report issues at GitHub or post comments here. And the best way how to contribute is to fix some issue or create some enhancement and send me pull request at GitHub.

September 09, 2010

Oracle enhanced adapter 1.3.1 and how to use it with Rails 3

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

Rails 3.0 was released recently and therefore I am releasing new Oracle enhanced adapter version 1.3.1 which is tested and updated against latest Rails 3.0.0 version. You can read about main changes in oracle_enhanced adapter for Rails 3 support in my previous blog post. Latest version 1.3.1 mainly contains several bug fixes (which you can find in change log as well as in detailed commit list) as well as several new features that I will describe here.

Usage with Rails 3

I have improved a little bit configuration and loading of oracle_enhanced adapter in Rails 3 and here are the initial steps that you should do to use oracle_enhanced adapter in Rails 3 application. I assume that you are using latest Rails 3.0.0 version as well as latest Bundler 1.0.0 version.

At first you need to include necessary gems in Gemfile of your application:

gem 'ruby-oci8', '~> 2.0.4'
gem 'activerecord-oracle_enhanced-adapter', '~> 1.3.1'

It is recommended to use ~> version (requires specified version or later minor version update where only the last digit of version has changed) or = version in your Gemfile and not >= (which might include major version changes). In this way you ensure that your application will not break when major API changes will happen in gem that you are using.

If you want to run your application both on MRI and JRuby then you can specify

platforms :ruby do
  gem 'ruby-oci8', '~> 2.0.4'
end

which will load ruby-oci8 gem only when using MRI 1.8 or 1.9 and not when using JRuby.

If you would like to use the latest development version of oracle_enhanced then change Gemfile to:

gem 'activerecord-oracle_enhanced-adapter', '~> 1.3.1', :git => 'git://github.com/rsim/oracle-enhanced.git'

If you will use also ruby-plsql gem in your application then include as well (and specify version as needed)

gem "ruby-plsql", "~> 0.4.3"

After these changes in Gemfile run bundle update to install necessary gems and generate corresponding Gemfile.lock.

If you want to use all default oracle_enhanced settings then you need just to specify your database connection in database.yml, for example, something like this:

development:
  adapter: oracle_enhanced
  database: orcl
  username: user
  password: secret

and you can start to use Rails with Oracle database. If you would like to change some oracle_enhanced adapter settings then it is recommended to create initializer file config/initializers/oracle.rb where you can specify necessary defaults, for example:

ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
    self.emulate_integers_by_column_name = true
    self.emulate_dates_by_column_name = true
    self.emulate_booleans_from_strings = true

    # to ensure that sequences will start from 1 and without gaps
    self.default_sequence_start_value = "1 NOCACHE INCREMENT BY 1"

    # other settings ...
  end
end

It is important to use ActiveSupport.on_load(:active_record) as Rails 3 does lazy loading of all components and we need to ensure that oracle_enhanced adapter defaults are set only after ActiveRecord is loaded.

You can take a look at sample Rails 3 application on Oracle to see sample configuration files that I mentioned here.

Database connection options

There are several ways how to specify database connection in database.yml file.

Using tnsnames.ora file with TNS aliases

If you are using tnsnames.ora file with TNS names and connection descriptions then you need to set TNS_ADMIN environment variable to point to directory where tnsnames.ora file is located. If oracle_enhanced adapter will detect that ENV[‘TNS_ADMIN’] is not empty then it will try to use TNS name in :database parameter to connect to database. So in this case in database.yml you need to specify:

development:
  adapter: oracle_enhanced
  database: connection_name_from_tnsnames
  username: user
  password: secret

Connection using tnsnames is supported both for MRI with ruby-oci8 as well as for JRuby with JDBC. Use this option if you would not like to hardcode database server address, port and database name in your application and want to specify separately in tnsnames.ora file.

Using host, port and database option

If you do not want to create separate tnsnames.ora file and want to specify database server, port and database name directly in application, then you can specify these options separately in database.yml file, for example:

development:
  adapter: oracle_enhanced
  host: localhost
  port: 1521
  database: orcl
  username: user
  password: secret

port default value is 1521 and can be omitted. It is also possible to specify host, port and database name is Oracle specific format in database option:

development:
  adapter: oracle_enhanced
  database: //localhost:1521/orcl
  username: user
  password: secret

It is also possible to specify TNS connection description directly in database.yml file (if you do not want to create separate tnsnames.ora file), for example:

development:
  adapter: oracle_enhanced
  database: "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
  username: user
  password: secret

Using JNDI connections in JRuby

If you deploy your JRuby application in Java application server that supports JNDI connections then it is possible to specify also JNDI connection in database.yml file, for example:

production: 
  adapter: oracle_enhanced
  jndi: "jdbc/jndi_connection_name"

I am not using this connection option but some oracle_enhanced users are using it.

Contributing to oracle_enhanced adapter

If you experience any issues with oracle_enhanced adapter then please report issues at GitHub issue tracker or discuss them at oracle_enhanced discussion group.

But even better if you want some new feature in oracle_enhanced adapter then fork oracle_enhanced git repository and make your changes and send me pull requests for review.

For all changes please add also RSpec tests as well as verify if all existing tests are passing after your changes. I added description how to set up environment for running tests – please let me know if something is missing there.

Big thanks to all contributors who have submitted patches so far :)

June 21, 2010

Oracle enhanced adapter 1.3.0 is Rails 3 compatible

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

Rails 3 is in final finishing stage (currently in beta4) and therefore I released new Oracle enhanced adapter version 1.3.0 which I was working on during last months.

Rails 3 compatibility
rails3.gif

The major enhancement is that Oracle enhanced adapter is now compatible with Rails 3. To achieve that I also developed Oracle SQL compiler for Arel gem which is used now by ActiveRecord to generate SQL statements. When using Oracle enhanced adapter with Rails 3 you will notice several major changes:

  • Table and column names are always quoted and in uppercase to avoid the need for checking Oracle reserved words.
    E.g. now Post.all will generate query
    SELECT "POSTS".* FROM "POSTS"
    
  • Better support for limit and offset options (when possible just ROWNUM condition in WHERE clause is used without using subqueries).
    E.g. Post.first (or Post.limit(1)) will generate query
    SELECT "POSTS".* FROM "POSTS" WHERE ROWNUM <= 1
    
    but Post.limit(1).offset(1) will generate
    select * from (select raw_sql_.*, rownum raw_rnum_
      from (SELECT "EMPLOYEES".* FROM "EMPLOYEES") raw_sql_ where rownum <= 2)
      where raw_rnum_ > 1
    

When using Oracle enhanced adapter with current version of Rails 3 and Arel it is necessary to turn on table and column caching option in all environments as otherwise Arel gem will cause very many SQL queries on data dictionary tables on each request. To achieve that you need to include in some initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.cache_columns = true

I have published simple Rails 3 demo application using Rails 3 and Oracle enhanced adapter. You can take a look at Gemfile and Oracle initializer file to see examples how to configure Oracle enhanced adapter with Rails 3.

Rails 2.3 compatibility

Oracle enhanced adapter version 1.3.0 is still compatible with Rails 2.3 (I am testing it against Rails 2.3.5 and 2.3.8) and it is recommended to upgrade if you are on Rails 2.3 and plan to upgrade to Rails 3.0 later. But if you are still on Rails 2.2 or earlier then there might be issues with Oracle enhanced adapter 1.3.0 as I am using some Rails methods which appeared just in Rails 2.3 – so in this case it might be safer to stay on previous Oracle enhanced adapter version 1.2.4 until you upgrade to latest Rails version.

Oracle CONTEXT index support

Every edition of Oracle database includes Oracle Text option for free which provides different full text indexing capabilities. Therefore in Oracle database case you don’t need external full text indexing and searching engines which can simplify your application deployment architecture.

The most commonly used index type is CONTEXT index which can be used for efficient full text search. Most of CONTEXT index creation examples show how to create simple full text index on one table and one column. But if you want to create more complex full text indexes on multiple columns or even on multiple tables and columns then you need to write your custom procedures and custom index refreshing logic.

Therefore to make creation of more complex full text indexes easier I have created additional add_context_index and remove_context_index methods that can be used in migrations and which creates additional stored procedures and triggers when needed in standardized way.

This is how you can create simple single column index:

add_context_index :posts, :title

And you can perform search using this index with

Post.contains(:title, 'word')

This is how you create index on several columns (which will generate additional stored procedure for providing XML document with specified columns to indexer):

add_context_index :posts, [:title, :body]

And you can search either in all columns or specify in which column you want to search (as first argument you need to specify first column name as this is the column which is referenced during index creation):

Post.contains(:title, 'word')
Post.contains(:title, 'word within title')
Post.contains(:title, 'word within body')

See Oracle Text documentation for syntax that you can use in CONTAINS function in SELECT WHERE clause.

You can also specify some dummy main column name when creating multiple column index as well as specify to update index automatically after each commit (as otherwise you need to synchronize index manually or schedule periodic update):

add_context_index :posts, [:title, :body], :index_column => :all_text,
  :sync => 'ON COMMIT'
Post.contains(:all_text, 'word')

Or you can specify that index should be updated when specified columns are updated (e.g. in ActiveRecord you can specify to trigger index update when created_at or updated_at columns are updated). Otherwise index is updated only when main index column is updated.

add_context_index :posts, [:title, :body], :index_column => :all_text,
  :sync => 'ON COMMIT', :index_column_trigger_on => [:created_at, :updated_at]

And you can even create index on multiple tables by providing SELECT statements which should be used to fetch necessary columns from related tables:

add_context_index :posts,
  [:title, :body,
  # specify aliases always with AS keyword
  "SELECT comments.author AS comment_author, comments.body AS comment_body FROM comments WHERE comments.post_id = :id"
  ],
  :name => 'post_and_comments_index',
  :index_column => :all_text,
  :index_column_trigger_on => [:updated_at, :comments_count],
  :sync => 'ON COMMIT'
# search in any table columns
Post.contains(:all_text, 'word')
# search in specified column
Post.contains(:all_text, "aaa within title")
Post.contains(:all_text, "bbb within comment_author")

In terms of Oracle Text performance in most cases it is good enough (typical response in not more that hundreds of milliseconds). But from my experience it is still slower compared to dedicated full text search engines like Sphinx. So in case if Oracle Text performance is not good enough (if you need all search operations return in tens of milliseconds) then you probably need to evaluate dedicated search engines like Sphinx or Lucene.

Other changes

Please see change history file or commit list to see more detailed list of changes in this version.

Install

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.7 or Ruby 1.9.1/1.9.2 or JRuby) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or report issues at GitHub or post comments here.

June 17, 2010

Please vote for my Ruby session proposals at Oracle OpenWorld

Posted by Raimonds Simanovskis • Tags: oracle, ruby, rails, plsql, conferenceShow comments

oow2010.pngI am trying to tell more people at Oracle OpenWorld about Ruby and Rails and how it can be used with Oracle database. Unfortunately my session proposals were rejected by organizers but now there is a second chance to propose sessions at mix.oracle.com and top voted sessions will be accepted for conference. But currently my proposed sessions do not have enough votes :(

I would be grateful if my blog readers and Ruby on Oracle supporters would vote for my sessions Fast Web Applications Development with Ruby on Rails on Oracle and PL/SQL Unit Testing Can Be Fun!.

You need to log in to mix.oracle.com with your oracle.com login (or you should create new one if you don’t have it). And also you need to vote for at least one more session as well (as votes are counted if you have voted for at least 3 sessions). Voting should be done until end of this week (June 20).

And if you have other oracle_enhanced or ruby-plsql users in your
organization then please ask their support as well :)

Thanks in advance!

February 24, 2010

ActiveRecord Oracle enhanced adapter version 1.2.4

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

I have released maintenance version of ActiveRecrod Oracle enhanced adapter with some bug fixes and some new features. This is the last maintenance version for Rails 2, I have already done majority of work to support also Rails 3 in next adapter versions, but that deserves another post when it will be ready :).

Detailed changes can be found in change history file and commit log, here I will point out the main changes.

Schema and structure dump

There are several improvements in schema (rake db:schema:dump) and structure dump (rake db:structure:dump) tasks. Now structure dump is improved to contain all schema objects in SQL statements format.

Also db:test:purge rake task (which is run before recreating test schema when running rake test or rake spec) is changed that it will delete all schema objects from test schema – including also views, packages, procedures and functions which are not recreated from schema.rb. So if you need to have additional database objects in your schema besides tables, indexes, sequences and synonyms (which are dumped in schema.rb) then you need to recreate them after standard rake task db:schema:load is run. Here is example how to execute any additional tasks after db:schema:load (include this in some .rake file in lib/tasks directory):

namespace :db do
  namespace :schema do
    task :load do
      Rake::Task["db:schema:create_other_objects"].invoke
    end
    task :create_other_objects do
      # include code here which creates necessary views, packages etc.
    end
  end
end

Additional options for schema definition methods

You can pass :temporary => true option for create_table method to create temporary tables.

You can use :tablespace => "tablespace name" option for add_index method to create index in non-default Oracle tablespace that is specified for user (e.g. if it is requested by your DBA for performance reasons). You can also define function based indexes using add_index and they will be correctly dumped in schema.rb.

Savepoints and nested ActiveRecord transactions

oracle_enhanced adapter now supports ActiveRecord nested transactions using database savepoints.

ruby-oci8 version

As I am using and testing oracle_enhanced adapter just with ruby-oci8 2.0.3 then I have made this as precondition (if you use MRI 1.8 or 1.9). So if you haven’t yet upgraded to latest ruby-oci8 version then please do so before upgrading to oracle_enhanced 1.2.4.

JNDI connection support

If you are using oracle_enhanced with JRuby then now you can also use JNDI database connections – please see this issue with comments to see some examples.

Install

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.6 / 1.8.7 or Ruby 1.9.1 or JRuby) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or post comments here.

September 28, 2009

New features in ActiveRecord Oracle enhanced adapter version 1.2.2

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

During the last months many new features have been implemented for ActiveRecord Oracle enhanced adapter which are now included in Oracle enhanced adapter version 1.2.2. You can find full list in change history file, here I will tell about the main ones.

Documentation

Now Oracle enhanced adapter has improved RDoc documentation for all public methods. So you can go to RDoc documentation of installed gem or go and view published documentation on-line.

Schema definition

There are many new features in schema definition methods that you can use in migration files:

  • When you use add_index then ActiveRecord is automatically generating index name using format index_table_name_on_column1_and_column2_… which previously could cause Oracle errors as Oracle identifiers should be up to 30 characters long. Now default index names are automatically shortened down to 30 or less characters (of course you can always use also :name option to specify shortened version by yourself).
  • Now adapter is ignoring :limit option for :text and :binary columns (as in Oracle you cannot specify limit for CLOB and BLOB data types). Previously it could cause errors if you tried to migrate Rails application from e.g. MySQL where :text and :binary columns could have :limit in schema definition.
  • If you define :string column with* :limit option then it will define VARCHAR2 column with size in characters and not in bytes (this makes difference if you use UTF-8 with language where one character might be stored as several bytes). This is expected behavior from ActiveRecord that you define maximum string size in UTF-8 characters.
  • Now you can use add_foreign_key and remove_foreign_key to define foreign key constraints in migrations (see RDoc documentation for details). Syntax and some implemenatation for foreign key definition was taken from foreigner Rails plugin as well as some ideas taken from active_record_oracle_extensions plugin.
  • add_foreign_key definitions will be also extracted in schema.rb by rake db:schema:dump task. Therefore they will be also present in test database when you will recreate it from schema.rb file.
  • Foreign keys are also safe for loading of fixtures (in case you are still using them instead of factories :)). disable_referential_integrity method is implemented for Oracle enhanced adapter which is called by ActiveRecord before loading fixtures and which disables all currently active foreign key constraints during loading of fixtures.
  • You can use add_synonym and remove_synonym to define database synonyms to other tables, views or sequences. add_synonym definitions will also be extracted in schema.rb file.
  • It is possible to create tables with primary key trigger. There will be no difference in terms how you would create new records in such table using ActiveRecord but in case you have also need to do direct INSERTs into the table then it will be easier as you can omit primary key from INSERT statement and primary key trigger will populate it automatically from corresponding sequence.
  • ActiveRecord schema dumper is patched to work correctly when default table prefixes or suffixes are used – they are now removed from schema.rb dump to avoid duplicate prefixes and suffixes when recreating schema from schema.rb.

Legacy schema support

Some features which can support “weird” legacy database schemas:

  • If you are using ActiveRecord with legacy schema which have tables with triggers that populate primary key triggers (and not using default Rails and Oracle enhanced adapter conventions) then you can use set_sequence_name :autogenerated in class definition to tell adapter to omit primary key value from INSERTs.
  • You can use ActiveRecord also with tables that you can access over database link. To do that you need to define local synonym to remote table (and also remote sequence if you want to insert records as well) and then use local synonym in set_table_name in class definition. Previously adapter could not get remote table columns, now it will get table columns also over database link.
    But still you cannot specify remote table (like “table_name@db_link”) directly in set_table_name as table_name will be used as column prefix in generated SQL statements where “@db_link” will not be valid syntax.
    And when you define local synonyms then please use the new add_synonym feature :)

Connection options

  • cursor_sharing option default value is changed from “similar” to “force” – please read explanation in discussion group post what it is and why the new default value is recommended choice.
  • When using JRuby and JDBC you can set TNS_ADMIN environment variable to tnsnames.ora directory and then use TNS database alias in database.yml file (specify just database: option and remove host: option). This might be useful for more complex TNS connection definitions, e.g. connection to load balanced Oracle RAC.
  • Adapter will not raise error if it cannot locate ojdbc14.jar* file. So either put it in $JRUBY_HOME/lib or ensure that it will be loaded by application server. Would love to hear feedback from people who are using this adapter with JRuby to find out if this behaves well now :)

Logging

  • Now you can get PL/SQL debugging information into your ActiveRecord log file. Use dbms_output.put_line in your PL/SQL procedures and functions (that are called from ActiveRecord models) and in your ActiveRecord model use connection.enable_dbms_output and connection.disable_dbms_output around your database calls to get dbms_output logging information into ActiveRecord log file. But please use it just in development environment with debug log level as in production it would add too much overhead for each database call. And this feature also requires that you install ruby-plsql gem.

As you see this probably is the largest “point” release that I have had :) Thanks also to other contributors which patches were included in this release.

As always you can install Oracle enhanced adapter on any Ruby platform (Ruby 1.8.6 / 1.8.7 or Ruby 1.9.1 or JRuby) with

gem install activerecord-oracle_enhanced-adapter

If you have any questions please use discussion group or post comments here.

March 23, 2009

ActiveRecord Oracle enhanced adapter also on JRuby and Ruby 1.9

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

So far if you wanted to use Ruby on Rails on Oracle database you needed to use different adapters depending on the Ruby platform that you wanted to use. If you used original MRI (Matz Ruby interpreter) 1.8.6 then hopefully you were using Oracle enhanced adapter. But if you wanted to use JRuby then you needed to use JDBC adapter that is maintained by JRuby team (and which sometimes might work differently than Oracle enhanced adapter). And if you wanted to use new Ruby 1.9.1 then you were out of luck as no adapter supported it.

Therefore I wanted to announce great news that ActiveRecord Oracle enhanced adapter 1.2.0 is released and it supports all three major Ruby platforms!

  • Use Oracle enhanced adapter on MRI 1.8.6 with ruby-oci8 1.0.x library or gem
  • Use Oracle enhanced adapter on JRuby (so far tested with 1.1.6) with JDBC Oracle driver
  • Use Oracle enhanced adapter on Ruby/YARV 1.9.1 with ruby-oci8 2.0 library or gem

This provides you with much more flexibility to develop on one Ruby platform but deploy on another and on all three platforms you can use the same additional functionality that Oracle enhanced adapter provides on top of standard ActiveRecord functionality.

And during testing of Oracle enhanced adapter on all platforms additional milestone was achieved – Oracle enhanced adapter passes 100% ActiveRecord unit tests! But to be honest I need to tell that I needed to patch quite many unit tests for Oracle specifics as not all SQL that runs on MySQL is also valid on Oracle. I published my patched branch of ActiveRecord unit tests at my GitHub fork of Rails – you can clone the repository and verify by yourself.

So please try out new version of Oracle enhanced adapter on any Ruby platform:

gem install activerecord-oracle_enahnced-adapter

If you have any questions please use discussion group or post comments here. In nearest future I will also add more instructions how to install Oracle enhanced adapter on JRuby and Ruby 1.9.1 at GitHub wiki page.

January 03, 2009

More information sources on ActiveRecord Oracle enhanced adapter

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

I’m glad to see that there are many users of ActiveRecord Oracle enhanced adapter and therefore comments in this blog is not anymore the best way how to communicate with Oracle enhanced adapter users. Therefore I created several other information exchange places which I hope will be more effective.

The latest addition is wiki pages on GitHub where I put usage description and examples as well as some troubleshooting hints that previously were scattered in README file and different blog posts. This is the first place where to look for information about Oracle enhanced adapter. And if you are GitHub user then you can correct mistakes or add additional content also by yourself.

If you have some question or you would like to discuss some feature then you can use Google discussion group. I will use this discussion group also for new release announcements as well so subscribe to it if you would like to get Oracle enhanced adapter news.

If you would like to report some bug or new feature (and patch would be greatly appreciated) then please use Lighthouse issue tracker.

And source code of Oracle enhanced adapter is still located in Github repository. If you are GitHub user then you can watch it or even fork it and experiment with some new features.

And BTW I just released Oracle enhanced adapter version 1.1.9 with some new features and it has been also tested with latest Rails 2.2 release.

December 02, 2008

NTLM Windows domain authentication for Rails application

Posted by Raimonds Simanovskis • Tags: ntlm, windows, rails, apache, authenticationShow comments

Introduction

RailsPlusWindows.pngIn one “enterprise” Ruby on Rails project we had an idea to integrate Windows domain user authentication with Rails application — as majority of users were using Windows and Internet Explorer and always were logged in Windows domain then it would be very good if they could log in automatically to the new Rails application without entering their username and password.

Windows is using NTLM protocol to provide such functionality — basically it uses additional HTTP headers to negotiate authentication information between web server and browser. It is tightly integrated into Microsoft Internet Information Server and if you live in pure Windows world then implementation of NTLM authentication is just a checkbox in IIS.

But if you are using Ruby on Rails with Apache web server in front of it and running everything on Linux or other Unix then this is not so simple. Therefore I wanted to share my solution how I solved this problem.

mod_ntlm Apache module installation

The first step is that we need NTLM protocol support for Apache web server so that it could handle Windows domain user authentication with web browser.

The first thing I found was mod_ntlm, but unfortunately this project is inactive for many years and do not have support for Apache 2.2 that I am using.

The other option I found was mod_auth_ntlm_winbind from Samba project but this solution requires Samba’s winbind daemon on the same server which makes the whole configuration more complex and therefore I was not eager to do that.

Then finally I found that someone has patched mod_ntlm to work with Apache 2.2 and this looked promising. I took this version of mod_ntlm but in addition I needed to make some additional patches to it and as a result I published my final mod_ntlm version in my GitHub repository.

If you would like to install mod_ntlm module on Linux then at first ensure that you have Apache 2.2 installed together with Apache development utilities (check that you have either apxs or apxs2 utility in your path). Then from the source directory of mod_ntlm (that you downloaded from my GitHub repository) do:

apxs -i -a -c mod_ntlm.c

If everything goes well then it should install mod_ntlm.so module in the directory where all other Apache modules is installed. It also tries to add module load directive in Apache configuration file httpd.conf but please check by yourself that you have

LoadModule ntlm_module ...directory.path.../mod_ntlm.so

line in your configuration file and directory path is the same as for other Apache modules. Try to restart Apache server to see if the module will be successfully loaded.

I also managed to install mod_ntlm on my Mac OS X Leopard so that I could later test NTLM authentication locally. Installation on Mac OS X was a little bit more tricky as I needed to compile 64-bit architecture module to be able to load it with preinstalled Apache:

sudo ln -s /usr/include/malloc/malloc.h /usr/include/malloc.h
sudo ln -s /usr/include/sys/statvfs.h /usr/include/sys/vfs.h
apxs -c -o mod_ntlm.so -Wc,"-shared -arch i386 -arch x86_64" -Wl,"-arch i386 -arch x86_64" mod_ntlm.c
sudo apxs -i -a -n 'ntlm' mod_ntlm.so

After this check /etc/apache2/httpd.conf file that it includes:

LoadModule ntlm_module        libexec/apache2/mod_ntlm.so

and try to restart Apache with

sudo apachectl -k restart

mod_ntlm Apache module configuration

The next thing is that you need to configure mod_ntlm. Put these configuration directories in the same place where you have your virtual host configuration directives related to your Rails application. Let’s assume that we have domain “domain.com” with domain controllers “dc01.domain.com” and “dc02.domain.com”. And let’s use /winlogin as a URL which will be used for Windows domain authentication.

RewriteEngine On
<Location /winlogin>
  AuthName "My Application"
  AuthType NTLM
  NTLMAuth on
  NTLMAuthoritative on
  NTLMDomain domain.com
  NTLMServer dc01.domain.com
  NTLMBackup dc02.domain.com
  require valid-user
</Location>

mod_ntlm will set REMOTE_USER environment variable with authenticated Windows username. If we are using Mongrel servers cluster behind Apache web server then we need to add the following configuration lines to put REMOTE_USER in HTTP header X-Forwarded-User of forwarded request to Mongrel cluster.

RewriteCond %{LA-U:REMOTE_USER} (.+)
RewriteRule . - [E=RU:%1]
RequestHeader add X-Forwarded-User %{RU}e

Please remember to put all previous configuration lines before any other URL rewriting directives. In my case I have the following configuration lines which will forward all non-static requests to my Mongrel servers cluster (which in my case have HAproxy on port 3000 before them):

# Redirect all non-static requests to haproxy
RewriteCond %{DOCUMENT_ROOT}/%{REQUEST_FILENAME} !-f
RewriteRule ^/(.*)$ http://127.0.0.1:3000%{REQUEST_URI} [L,P,QSA]

Rails sessions controller

Now the final part is to handle authenticated Windows users in Rails sessions controller. Here are examples how I am doing this.

routes.rb:

map.winlogin 'winlogin', :controller => 'sessions', :action => 'create_from_windows_login'

sessions_controller.rb:

def create_from_windows_login
  if !(login = forwarded_user)
    flash[:error] = "Browser did not provide Windows domain user name"
    user = nil
  elsif user = User.authenticated_by_windows_domain(login)
    # user has access rights to system
  else
    flash[:error] = "User has no access rights to application"
  end
  self.current_user = user
  if logged_in?
    # store that next time automatic login should be made
    cookies[:windows_domain] = {:value => 'true', :expires => Time.now + 1.month}
    # Because of IE NTLM strange behavior need to give 401 response with Javascript redirect
    @redirect_to = redirect_back_or_default_url(root_path)
    render :status => 401, :layout => 'redirect'
  else
    render :action => 'new'
  end
end
private
  def forwarded_user
    return nil unless x_forwarded_user = request.headers['X-Forwarded-User']
    users = x_forwarded_user.split(',')
    users.delete('(null)')
    users.first
  end

User.authenticated_by_windows_domain is model method that either find existing or creates new user based on authenticated Windows username in parameter and checks that user has access rights. Private method forwarded_user extracts Windows username from HTTP header — in my case it always was formatted as “(null),username” therefore I needed to remove unnecessary “(null)” from it.

In addition I am storing browser cookie that user used Windows domain authentication — it means that next time we can forward this user directly to /winlogin instead of showing login page if user has this cookie. We cannot forward all users to /winlogin as then for all users browser will prompt for Windows username and password (and probably we are also using other authentication methods).

The last thing is that we need to do a little hack as a workaround for strange Internet Explorer behavior. If Internet Explorer has authenticated with some web server using NTLM protocol then IE will think that this web server will require NTLM authentication for all POST requests. And therefore it does “performance optimization” when doing POST requests to this web server — the first POST request from browser will have no POST data in it, just header with NTLM authentication message. In Rails application case we do not need these NTLM authentications for all POST requests as we are maintaining Rails session to identify logged in users. Therefore we are making this trick that after successful authentication we return HTTP 401 code which makes IE think that it is not authenticated anymore with this web server. But together with HTTP 401 code we return HTML page which forces client side redirect to home page either using JavaScript or

create_from_windows_login.html.erb:

<% content_for :head do %>
  <script language="javascript">
    <!--
      location.replace("<%= @redirect_to %>");
    //-->
  </script>
  <noscript>
    <meta http-equiv="Refresh" content="0; URL=<%= @redirect_to %>" />
  </noscript>
<% end %>
<%= link_to 'Redirecting...', @redirect_to %>

content_for :head is used to specify which additional content should be put in <header> part of layout.

As a result you now have basic Windows domain NTLM authentication working. Please let me know in comments if you have any issues with this solution or if you have other suggestions how to use Windows domain NTLM authentication in Rails applications.

Additional hints

NTLM authentication can be used also in Firefox. Enter about:config in location field and then search for network.automatic-ntlm-auth.trusted-uris. There you can enter servers for which you would like to use automatic NTLM authentication.

September 26, 2008

OpenWorld unconference presentation about Rails on Oracle

Posted by Raimonds Simanovskis • Tags: oracle, conference, ruby, rails, oracleShow comments

On last day of Oracle OpenWorld I did my unconference presentation – Using Ruby on Rails with legacy Oracle databases.

As I did not know if anyone will come to listen to it I was glad that six people attended (including Kuassi Mensah from Oracle who is helping to promote Ruby support inside Oracle). And on the previous day I also managed to show parts of my presentation to Rich and Anthony from Oracle AppsLab team.

I published my slides on Slideshare:

And I published my demo project on GitHub:

hr_schema_demo.png

Thanks to all Oracle people who recognize my work on Ruby and Oracle integration and I hope that our common activities will increase number of Ruby and Rails projects on Oracle :)

September 06, 2008

Oracle enhanced adapter presentation at RejectConf in Berlin

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, conference, oracleShow comments
rejectconf08.jpg

I just returned from RailsConf Europe in Berlin and attended a lot of good Ruby and Rails related sessions.

I also tried to submit there my session proposal about using Rails with Oracle but as there were too many good proposals and as my topic was with too narrow topic then it was not accepted. Therefore I used opportunity to give 5 minutes presentation about Oracle enhanced adapter in RejectConf where anybody could present anything.

Here are my slides that I used in this presentation. And at least one participant was interested in this topics as he uses Rails and Oracle and did not know about my adapter.

My next public appearance is planned at Oracle OpenWorld unconference where I have recerved time slot on Thursday, September 25th at 10am. Please come there if you are attending Oracle OpenWorld and are interested in how to use Ruby on Rails with your legacy Oracle databases.

July 27, 2008

Latest additions to Oracle enhanced adapter

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

Short information about latest enhancements in ActiveRecord Oracle enhanced adapter:

  • Oracle enhanced adapter is now compatible with composite_primary_keys gem which is quite useful if you are working with legacy databases.
  • Adapter now is also working correctly with Rails 2.1 partial_updates enabled. Previously I mentioned that you needed to disable partial_updates when using CLOB/BLOB columns. Now the issue is found and fixed and partial_updates are working with CLOB/BLOB columns.
  • Support for other date and time formats when assigning string to :date or :datetime column. For example, if you would like to assign strings with format dd.mm.yyyy to date and datetime columns then add the following configuration options:
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_date_format = "%d.%m.%Y"
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_time_format = "%d.%m.%Y %H:%M:%S"
    

To get the new release of Oracle enhanced adapter do as always:

sudo gem install activerecord-oracle_enhanced-adapter

July 10, 2008

Custom ActiveRecord create, add and delete methods for legacy databases

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracle, ruby-plsqlShow comments

In some Ruby on Rails projects I am putting ActiveRecord interface on top of existing legacy databases. It is quite easy to specify legacy table names and primary keys in ActiveRecord models so that ActiveRecord would generate correct SQL SELECT statements to read data from legacy databases.

But it is more difficult to insert, update and delete in legacy databases using ActiveRecord. When using Oracle legacy databases then quite often they have exposed PL/SQL APIs for writing to tables and you are typically not allowed to directly modify tables with INSERT, UPDATE and DELETE statements.

Therefore I created support for custom create, update and delete methods in the version 1.1.3 of ActiveRecord Oracle enhanced adapter which uses also my ruby-plsql gem.

Let’s look at the following example. Assume that we have the following table:

CREATE TABLE test_employees (
  employee_id   NUMBER(6,0),
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25),
  hire_date     DATE,
  salary        NUMBER(8,2),
  version       NUMBER(15,0),
  create_time   DATE,
  update_time   DATE
)

And we have the following PL/SQL API package that should be used to write to this table:

CREATE OR REPLACE PACKAGE test_employees_pkg IS
  PROCEDURE create_employee(
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER,
      p_employee_id   OUT NUMBER);
  PROCEDURE update_employee(
      p_employee_id   NUMBER,
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER);
  PROCEDURE delete_employee(
      p_employee_id   NUMBER);
END;

Then we define ActiveRecord model in the following way:

class TestEmployee < ActiveRecord::Base
  set_primary_key :employee_id
  # should return ID of new record
  set_create_method do
    plsql.test_employees_pkg.create_employee(
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary,
      :p_employee_id => nil
    )[:p_employee_id]
  end
  # return value is ignored
  set_update_method do
    plsql.test_employees_pkg.update_employee(
      :p_employee_id => id,
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary
    )
  end
  # return value is ignored
  set_delete_method do
    plsql.test_employees_pkg.delete_employee(
      :p_employee_id => id
    )
  end
end

And as a result we can use this model in the same way as other ActiveRecord models:

@employee = TestEmployee.create(
  :first_name => "First",
  :last_name => "Last",
  :hire_date => Date.today
)
@employee.reload
@employee.first_name = "Second"
@employee.save!
@employee.destroy

And all writing to the database will be done using defined API procedures.

Currently this functionality is embedded into Oracle enhanced adapter but if somebody needs it also for other databases this functionality could easily be extraced from the adapter.

To get the new release of Oracle enhanced adapter just do:

sudo gem install activerecord-oracle_enhanced-adapter

Source code of Oracle enhanced adapter is located at GitHub and you can submit bug reports and enhancement requests in Lighthouse.

July 08, 2008

Vote for my session proposal at Oracle OpenWorld

Posted by Raimonds Simanovskis • Tags: oracle, conference, ruby, railsShow comments

oow.pngIf you still have not voted for my session proposal “Using Ruby on Rails with Oracle E-Business Suite” then please do so :) Voting deadline is near – July 13th.

In any case I am going to be at Oracle OpenWorld conference in San Francisco in September. So if some of my blog readers will also be there and would like to have some chat with me about Oracle and Ruby then please let me know.

June 28, 2008

ActiveRecord Oracle enhanced adapter version 1.1.1 released

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

I just released new version of ActiveRecord Oracle enhanced adapter which includes several new enhancements which could be useful if you use ActiveRecord with legacy Oracle databases:

  • Added ignore_table_columns option for class definitions
    You can specify which table (or view) columns should be ignored by ActiveRecord – these could be either columns which you do not need in Rails application or which have currently unsupported data types. For example:
    class Employee < ActiveRecord::Base
      ignore_table_columns  :phone_number, :hire_date
    end
    
  • Added support for TIMESTAMP columns
    You can create tables with :timestamp data type which will create TIMESTAMP columns and you can access values from TIMESTAMP columns. Unfortunately due to current ruby-oci8 limitations when you will retrieve TIMESTAMP values it will be without fractional seconds (but if you pass Time value with fractional seconds then it will be stored in database).
  • NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT independent DATE and TIMESTAMP columns support
    By default Oracle adapter (and enhanced adapter as well) changes NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT to ‘DD-MON-YYYY HH24:MI:SS’. But if you are working with some legacy database which requires different NLS date settings then now you can change these settings to different ones. You can put in some initialization file some other NLS settings, e.g.:
    ActiveRecord::Base.connection.execute %q{alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'}
    ActiveRecord::Base.connection.execute %q{alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS'}
    

    and ActiveRecord will continue to work correctly with date values.

Also one Rails 2.1 related issue was solved. Previously Oracle enhanced adapter always added after_save callback to ActiveRecord session store which stores BLOB data in the database (as in Rails 2.0 there was issue that this callback was not added). Now in Rails 2.1 this callback is added by ActiveRecord therefore Oracle enhanced adapter checks if this callback is already added or not. So now this should work correctly both in Rails 2.0 and Rails 2.1.

Also I have noticed that in some Rails 2.1 applications Oracle adapter is failing on ActiveRecord session store updates when partial updates are enabled. If you also have such issue then put this into your initialization file which will work as workaround until I will find what is causing this problem:

CGI::Session::ActiveRecordStore::Session.partial_updates = false

To get the new release just do:

sudo gem install activerecord-oracle_enhanced-adapter

Source code of Oracle enhanced adapter is located at GitHub. And you can submit bug reports and enhancement requests in Lighthouse. There you can see also some identified enhancements – please add comments to them if you also would like to have them. Then it will be easier for me to select what to include in next version of adapter.

May 21, 2008

Using mod_rails with Rails applications on Oracle

Posted by Raimonds Simanovskis • Tags: ruby, rails, oracle, apacheShow comments

As many others I also got interested in new mod_rails deployment solution for Rails applications. And when I read how to use it for development environment needs I decided to try it out.

As you probably know I am using Mac for development and using Oracle database for many Rails applications. So if you do it as well then at first you need to setup Ruby and Oracle on your Mac.

After that I installed and did setup of mod_rails according to these instructions and these additional notes.

One additional thing that I had to do was to change the user which will be used to run Apache httpd server as otherwise default www user did not see my Rails applications directories. You should do it in /etc/apache2/httpd.conf:

User yourusername
Group yourusername

And then I started to fight with the issue that ruby which was started from mod_rails could not load ruby-oci8 library as it could not find Oracle Instant Client shared library. And the reason for that was that mod_rails launched ruby with very minimal list of environment variables. E.g. as DYLD_LIBRARY_PATH environment variable was not specified then ruby-oci8 could not find Oracle Instant Client libraries.

The issue is that there is no documented way how to pass necessary environment variables to mod_rails. Unfortunately mod_rails is ignoring SetEnv settings from Apache httpd.conf file. Therefore I needed to find some workaround for the issue and finally I did the following solution.

I created executable script file /usr/local/bin/ruby_with_env:

#!/bin/bash
export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2:$DYLD_LIBRARY_PATH"
export SQLPATH=$DYLD_LIBRARY_PATH
export TNS_ADMIN="/usr/local/oracle/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"
/usr/bin/ruby $*

and then in Apache httpd.conf file I changed RailsRuby line to

RailsRuby /usr/local/bin/ruby_with_env

As a result in this way I was able to specify necessary environment variables before Ruby and Rails was started and after this change ruby-oci8 libraries were successfully loaded.

You can use this solution also on Linux hosts where you will deploy Rails applications in production.

Currently I still have issue with mod_rails that it fails to execute RMagick library methods (which is compiled with ImageMagick). I get strange errors in Apache error_log:

The process has forked and you cannot use this CoreFoundation functionality safely. You MUST exec().
Break on __THE_PROCESS_HAS_FORKED_AND_YOU_CANNOT_USE_THIS_COREFOUNDATION_FUNCTIONALITY___YOU_MUST_EXEC__() to debug.
[error] [client ::1] Premature end of script headers:

When I was running the same application with Mongrel then everything was running correctly. If anyone has any ideas what could be the reason please write some comment.

May 13, 2008

ActiveRecord Oracle enhanced adapter

Posted by Raimonds Simanovskis • Tags: oracle_enhanced, ruby, rails, oracleShow comments

In all Ruby on Rails on Oracle projects where I am using original ActiveRecord Oracle adapter I always create some “monkey patches” of Oracle adapter to support my needs. I have written about several of these patches in this blog (see 1, 2, 3).

As such monkey patches are not easily maintainable and reusable between projects I decided to fork existing Oracle adapter and create my own ActiveRecord Oracle “enhanced” adapter where I will add all my enhancements.

Today I released first version (1.1.0) of Oracle enhanced adapter on RubyForge as well as source code is available at GitHub.

To install Oracle enhanced adapter execute (should be available soon):

sudo gem install activerecord-oracle_enhanced-adapter

To use it you need to use “oracle_enhanced” as the adapter name in database.yml configuration file instead of “oracle”. In addition I recommend to create config/initializers/oracle_enhanced.rb file where to put any adapter configuration options that you can see below.

Initial version contains the following enhancements:

  • Improved perfomance of schema dump methods when used on large data dictionaries
  • Added LOB writing callback for sessions stored in database (see previous post)
  • Added emulate_dates_by_column_name option
    Set the option below and as a result columns with DATE in their name will be emulated as Date (and not as Time which is default for DATE columns in database)
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates_by_column_name = true
    
  • Added emulate_integers_by_column_name option
    Set the option below and as a result number columns with ID at the end of column always will be emulated as Fixnum (useful if in legacy database column type is specified just as NUMBER without precision information which by default is mapped to BigDecimal Ruby type)
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_integers_by_column_name = true
    
  • Added emulate_booleans_from_strings option
    Set the option below and as a result CHAR, VARCHAR2 columns or VARCHAR2 columns with FLAG or YN at the end of their name will be emulated as booleans (and “Y” and “N” will be used to store true and false values). This is useful for legacy databases where Rails default convention of NUMBER for boolean values cannot be used (e.g. if you are using Oracle E-Business Suite where booleans are stored as “Y” and “N”).
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true
    

If you need to override how date, integer or boolean columns are identified then you can redefine class methods is_date_column?, is_integer_column? and is_boolean_column? definitions in OracleEnhancedAdapter class.

Please comment if you find these enhancements useful in your projects and also what other Oracle adapter enhancements you need in your Ruby on Rails on Oracle projects.

May 04, 2008

Session proposal for OOW - Using Ruby on Rails with Oracle E-Business Suite

Posted by Raimonds Simanovskis • Tags: conference, oracle, ruby, railsShow comments

OOW_submit_session.pngmix.oracle.com team created opportunity to suggest and vote for sessions at Oracle OpenWorld conference. Therefore I will also try this opportunity and will suggest topic which is covered by many posts in this blog – I proposed session Using Ruby on Rails with Oracle E-Business Suite.

So if you would like me to present this topic in Oracle OpenWorld conference then please go to mix.oracle.com and vote for it!

February 23, 2008

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

Posted by Raimonds Simanovskis • Tags: ruby, railsShow comments

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%)

January 26, 2008

Make Rails database migrations faster on Oracle

Posted by Raimonds Simanovskis • Tags: ruby, rails, oracleShow comments

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.

January 08, 2008

Fix for Rails 2.0 on Oracle with database session store

Posted by Raimonds Simanovskis • Tags: ruby, rails, oracleShow comments

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 :)

November 16, 2007

Some issues with Oracle views as ActiveRecord source

Posted by Raimonds Simanovskis • Tags: ruby, rails, oracleShow comments

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?

August 30, 2007

Rails patch for Oracle CLOB defaults

Posted by Raimonds Simanovskis • Tags: ruby, rails, oracleShow comments

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

August 27, 2007

How to explicitly set Oracle DATE column as Ruby Date attribute

Posted by Raimonds Simanovskis • Tags: ruby, rails, oracleShow comments

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.

Fork me on GitHub