NUMBER as integer

[Cory Foy]

Hi All,

I'm running into an issue that I don't quite understand. I have a legacy Oracle DB. My primary key for my tables is called "sequence_no" and is defined as a NUMBER.

By default, that means that I'm getting paths like /user/207.0/edit. So I defined an initializer like so:

  ActiveSupport.on_load(:active_record) do
    ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
      # id columns and columns which are called sequence_no will always be converted to integers
      self.emulate_integers_by_column_name = true

      #This forces columns named 'sequence_no' or 'composite_id' to always be integers
      #Otherwise, columns defined as NUMBER (not NUMBER(0)) will be considered decimals
      def self.is_integer_column?(name, table_name = nil)
        !!(name =~ /sequence_no|composite_id$/i)
      end
    end
  end

(we also have to deal with columns named composite_id).

This works fantastically for all of my tables - except one. Here's a comparison of a column in a table that is fine, and one that isn't.

2.1.2 :027 > conn.columns('mytable2').find { |col| col.name == 'sequence_no' }
 => #<ActiveRecord::ConnectionAdapters::OracleEnhancedColumn:0x00000006d34888 @table_name="mytable2", @forced_column_type=nil, @virtual=false, @returning_id=false, @name="sequence_no", @sql_type="NUMBER", @null=false, @limit=nil, @precision=nil, @scale=nil, @type=:integer, @default=nil, @default_function=nil, @primary=nil, @coder=nil, @object_type=false>

2.1.2 :028 > conn.columns('mytable1').find { |col| col.name == 'sequence_no' }
 => #<ActiveRecord::ConnectionAdapters::OracleEnhancedColumn:0x0000000320ba90 @table_name="mytable1", @forced_column_type=nil, @virtual=false, @returning_id=false, @name="sequence_no", @sql_type="NUMBER", @null=false, @limit=nil, @precision=nil, @scale=nil, @type=:decimal, @default=nil, @default_function=nil, @primary=nil, @coder=nil, @object_type=false>

So, on mytable2, the column is properly treated as an integer, but for mytable1, it isn't. I can't find any reason why this would be the case, and I'm stumped with what next steps I can do to troubleshoot this.

Has anyone run into something like this before? Any ideas I can try (or information I can provide?)

Thanks!

Cory

[Lori M Olson]

Can you also dump the actual schema definitions of those two tables? And, if there is anything relevant w.r.t. those columns, the model files might also be helpful.

(BTW, if this helps you feel better, I too would like to strangle the person who defined those primary keys on your tables)

Regards, Lori

(@wndxlori)

--
You received this message because you are subscribed to the Google Groups "Oracle enhanced adapter for ActiveRecord" group.
To unsubscribe from this group and stop receiving emails from it, send an email to oracle-enhanc...@googlegroups.com.
To post to this group, send email to oracle-...@googlegroups.com.
Visit this group at http://groups.google.com/group/oracle-enhanced.
For more options, visit https://groups.google.com/d/optout.

[Cory Foy]

Hi Lori,

Thanks. I have to redact a lot of info, but this is the DESC:

SQL> desc mytable1


 Name                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 USER_ID                    NOT NULL VARCHAR2(9)

 SEQUENCE_NO                   NOT NULL NUMBER



SQL> desc mytable2

 Name                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 USER_ID                         VARCHAR2(9)

 SEQUENCE_NO                   NOT NULL NUMBER

Nothing magical in the model files

class MyObject1 < ActiveRecord::Base
  def self.table_name
    "mytable1"
  end
  self.primary_key = "sequence_no"
end

class MyObject2 < ActiveRecord::Base
  def self.table_name
    "mytable2"
  end
  self.primary_key = "sequence_no"
end

The app has been running for about 3 years on Rails 3 and a version of EnhancedAdapter which I forked to deal with this scenario (https://github.com/CoryFoy/oracle-enhanced). We recently upgraded to Rails4, and I decided I wanted to dump the fork, especially when I saw how we could handle this scenario.

And trust me - this is the easiest part of this database.

Thanks for your help - I take it from your perspective, you don't see any reason off hand why it wouldn't work? What I did in the interim is add the code from http://www.alexrothenberg.com/2010/01/27/what-to-do-when-activerecord-thinks.html to just that model, which fixes the issue. That would tell me the issue is in the column mapping. I just don't understand why, yet.

Cory

[Yasuo Honda]

Hi,

Rails 4.1 or earlier `simplified_type(field_type)` method handles
which field type in database corresponds to which Rails data type.

https://github.com/CoryFoy/oracle-enhanced/blob/master/lib/active_record/connection_adapters/oracle_enhanced_column.rb#L76-L82

I do not know the reason why the same model(table) definitions make
different field_type, decimal or integer. I think it is worth to debug
this method.

Thanks,
--
Yasuo Honda


--
Yasuo Honda

[Cory Foy]

Hi Yasuo,

Thanks! I did some debugging. It turns out I'm accessing the problematic column in code which is being run prior to the Oracle initializer being run. We're doing a serious of specialized checks around the database integrity ahead of time, and that's causing my first model to be loaded early. Since that is the only model being hit during the early initializer run, every other model is fine since the OracleEnhancedColumn isn't run until the subsequent tables are requested for the first time - which happens after the custom code in the oracle initializer is run.

Forcing my Oracle initializer code to run first solved the issue.

Thanks Yasuo and Lori for your help!

Cory

[Cory Foy]

A follow up:

The reason this wasn't obvious is that the integrity checks happen by getting some information stored as a class method in my model which doesn't do any database access. But because behind the scenes Ruby spins up an instance of the object, which in turns triggers ActiveRecord initialization since it is an ActiveRecord object.

Hope that helps someone else!

Cory