ray_apps_blog

September 28, 2009

New features in ActiveRecord Oracle enhanced adapter version 1.2.2

Filed under: jruby,oracle,oracle-enhanced,rails,ruby — Raimonds Simanovskis @ 7:55 pm

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.

Advertisements

10 Comments

  1. Nicely done!

    Comment by Roy van der Meij — September 29, 2009 @ 1:40 pm

  2. Nice work Raimonds and co!

    I recommend that people do read about the cursor_sharing option; some may want to change it to similar for their applications. Unfortunately, it is a bit of a blunt hammer, since it is either system-wide or session-wide, rather than per-statement.

    I assume here you are setting the session value?

    Comment by Kevin Neel — September 30, 2009 @ 12:33 am

    • Yes, this setting will apply just for sessions created by ActiveRecord.
      I think that cursor_sharing = force is safer to use for simple ActiveRecord generated statements to ensure that always all parameters will be extracted as bind variables.
      If you have complex SQL statements then anyway you probably need to use find_by_sql method and then you can put there Oracle optimizer hints as well :)

      Comment by Raimonds Simanovskis — September 30, 2009 @ 8:11 am

  3. Thanks for the support, relying on this adapter for my prototyping… Though your DataMapper’s alternative, and DataMapper with it, is looking more and more interesting, actually…

    Comment by Martijn van Rheenen — September 30, 2009 @ 9:54 am

  4. I’m trying to deploy an prodution app in a new enviroment (jruby+glassfish now, I was using mongrel before).

    SEVERE: ERROR: ActiveRecord oracle_enhanced adapter could not load Oracle JDBC driver. Please install ojdbc14.jar library

    What am I doing wrong? (Yes, odbc14.jar is in the right place). Enhanced adapter is my last attempt to get my old application to run smoothly with jruby. Unfortunaly I’ll have to abandon ruby if I don’t get this working (glassfish and tomcat are the only appservers allowed in my company now). I’ve been trying to use activerecord-jdbc-adapter, but that is not even close to be ready for a production environment.

    Comment by Eduardo — December 8, 2009 @ 4:40 am

  5. I love that you now have support for them in the generated schema.rb. However, Now I get Oracle errors whenever I run rake db:test:prepare because my schema is trying to add a FK constraint for a table that is created later in the schema. You should probably put the add_foreign_key calls to the end of the schema, to make sure that the referenced table has been created.

    Comment by ed lebert — December 9, 2009 @ 5:15 pm

    • This issue is fixed in new oracle_enhanced version 1.2.3 that I just released.

      Comment by Raimonds Simanovskis — December 10, 2009 @ 12:31 pm

  6. Is there a way to force a column to a specific data type within the model? We are using a legacy Oracle DB which has columns like ‘personuid’ which is a number with no scale so it comes back as a float. We can’t simply use the ’emulate_integers_by_column_name’ because it assumes the column is called ‘_id’.

    I was hoping we could explicitly set the columns in the model somehow and the adapter would read that assignment.

    Any ideas?

    Comment by Tate — May 1, 2010 @ 1:25 am

  7. Raimonds — do you have or use any tools for generating ActiveRecord (or DataMapper) models from
    an existing database? Including foreign keys, constraints, … I see the following out there:
    http://magicmodels.rubyforge.org/magic_model_generator/

    Thanks!
    Kevin

    Comment by Kevin Neel — July 14, 2010 @ 4:17 am


RSS feed for comments on this post.

Create a free website or blog at WordPress.com.

%d bloggers like this: