Forum: JRuby urgent: activerecord-jdbc-0.9.1 doesn't "see" tables or views in another schema and MRI doesn't hav

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
03ebcbbface06500195e3d3fe4d69119?d=identicon&s=25 Renato Santos (Guest)
on 2009-03-20 00:59
(Received via mailing list)
Hi all.
I have a very peculiar problem that I'm not remotely capable of solving
(or
so it seems...): my application uses Oracle 10g database and, for safety
and
manageability reasons, has to use an user that is not the owner of the
production schema - and this policy is set in stone here, no way to
change
it (not that I would want it anyway, since I agree with it and we've
never
had any kind of trouble before).

To make things easier, the DBA created a logon trigger to the app's user
(let's suppose the name of the app user is "my_app_user") that does
something like this:

alter session set current_schema = 'app_production_schema'

So, when the application logs on to the database using 'my_app_user',
that
trigger makes the tables, views and sequences of 'app_production_schema'
readily available to 'my_app_user' without the need to use stuff like
set_table_name = 'app_production_schema.my_table' in every model.

Now, here's the catch: it's been working like a charm with MRI and the
activerecord-oracle-adapter-1.0.0.9250 - no problem at all, as everyone
would expect. But, when I try to run the application in JRuby 1.2.0 +
activerecord-jdbc-adapter-0.9.1, I get messages like "Table ORGAOS does
not
exist" for all tables and views of the application!

And things get worse - here's my situation: I've had just "fallen in
love"
with Ruby on Rails when I was designated to "convert" this application
(already in production for quite some time) to JRuby, since my
organisation
(I work for government, by the way), for now, can't handle managing more
than one kind of app server or deployment environment because of the
tiny
size of the team running the everyday management duties related to
server
machines, physical and logical network config, and app servers.

I had to learn some RoR, then JRuby (and the "ecosystem" around it) and
finaly managed to make everything work in development environment (keep
in
mind I don't know jack of Java language and didn't know anything about
Tomcat and the like when the project started).

Problem is, the homologation (and permission to use in future projects)
of
the whole (J)Ruby on Rails platform is pending on the success of this
particular "conversion" project, since this court doesn't want to keep
the
Apache + mod_rails + Mongrel + Webistrano setup - meaning the app has to
run, at the least, in Tomcat using JNDI. But I suppose it is not
possible if
I can't see any tables on the production schema when I run JRuby +
Mongrel... And the deadline to deliver the application with some load
and
stress tests is this March 25 already.

There are already other government organisations in my country very
interested in the results of this project, since they want to experiment
with JRuby on Rails but they would like to not waste the same
(inordinate)
amount of time I did to get around the pitfalls of this endeavour.

When this problem first appeared, I was using JRuby 1.1.6,
activerecord-jdbc-adapter-0.8.2 and Ruby on Rails 1.2.3. Just to be sure
I
was not being the victim of obsolete stuff, I gave myself the trouble of
converting this app to RoR 2.1.2 so I could plug JRuby 1.2.0RC1 and the
"trunk" of activerecord-jdbc-adapter from GitHub (version 0.9.1 had not
yet
been released...). And the problem persisted.

After that, I did some runs with ar-jdbc-adapter 0.8.2, 0.9 and 0.9.1,
all
with the same results. Then, I removed the plugins of the application
(classic_pagination, acts_as_list and the latest version of
acts_as_paranoid) just to be sure they weren't getting in the way (I've
had
some bad experience with redhillonrails-core plugin provoking similar
bugs)
and did the same three runs - getting the same results. Since my machine
is
hideously slow (can't even use and IDE), it took a hell of a lot of time
just to do that. Finally, after looking at the stack trace and at the
code
involved, I finally gave up - I don't know jack of Java (I did look at
JdbcAdapterInternalService.java
and its columns_internal method), my knowledge about the guts of the
Oracle
Database Engine is non-existent and my knowledge of the internals of the
RoR
framework is kinda small yet. And I don't have the mental stamina or
time to
keep going at it anymore since I still must get other projects rolling
at
the same time.

Right now, I'm near desperate and kinda sorry for bothering you with
such a
long post, but I didn't know what to do. I fear a complete fiasco
because of
this bug, and people around the whole country did use this application
already to great success and satisfaction (running in MRI and Rails
1.2.6,
of course) and I fear thousands of users will lose this application -
that
will have to be converted to Java, after only God knows how much time,
since
it is quite big for a RoR app (and will get even bigger in Java, I'm
sure ;p
). And, while my official deadline is March 25, I have to plan and
execute
load and stress tests and, since it is the first time I do that (do you
notice a sad trend here?), I think I would need a solution by March 23.
:(

Me thinks it's about time I present you a couple stack traces (without
any
plugins in the application already):

first exception: Table ORGAOS does not exist  (ORGAOS is a view in fact)

D:/RDev/interpreters/jruby/releases/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/base.rb:1149:in
`columns'
D:/RDev/interpreters/jruby/releases/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/base.rb:1157:in
`columns_hash'
D:/RDev/interpreters/jruby/releases/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/base.rb:1375:in
`find_one'
D:/RDev/interpreters/jruby/releases/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/base.rb:1366:in
`find_from_ids'
D:/RDev/interpreters/jruby/releases/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/base.rb:541:in
`find'
app/controllers/adm_controller.rb:28:in `cge'
:1:in `initialize'

second exception: Table QUESTIONARIOS does not exist (QUESTIONARIOS is
a normal table)

D:/RDev/interpreters/jruby/releases/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/base.rb:1149:in
`columns'
D:/RDev/interpreters/jruby/releases/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/base.rb:1162:in
`column_names'
(__DELEGATION__):2:in `column_names'
app/controllers/questionarios_controller.rb:30:in `list'
:1:in `initialize'


Note: I tried to use set_table_name "<schema_name>.orgaos" and
set_table_name "<schema_name>.questionarios" and got the exact same
results. Again, that problem doesn't occur with MRI +
activerecord-oracle-adapter.

I think it is somewhat disturbing the fact that
activerecord-jdbc-adapter doesn't appear in any of those stack-traces,
although I could be very wrong. And both of them end in the method
'columns' of ActiveRecord::Base.

A very small clue that I got while examining
activerecord-oracle-adapter whas this: it treats the names of synonyms
in a much different way than it treats the name of other objects. Take
a look at this code:

def columns(table_name, name = nil) #:nodoc:

  (owner, table_name) = connection.describe(table_name)

...lots of other code...

end

def describe(name)

  @desc ||= @@env.alloc(OCIDescribe)

  @desc.attrSet(OCI_ATTR_DESC_PUBLIC, -1) if VERSION >= '0.1.14'

  @desc.describeAny(@svc, name.to_s, OCI_PTYPE_UNK) rescue raise
%Q{"DESC #{name}" failed; does it exist?}

  info = @desc.attrGet(OCI_ATTR_PARAM)

  case info.attrGet(OCI_ATTR_PTYPE)

  when OCI_PTYPE_TABLE, OCI_PTYPE_VIEW

    owner      = info.attrGet(OCI_ATTR_OBJ_SCHEMA)

    table_name = info.attrGet(OCI_ATTR_OBJ_NAME)

    [owner, table_name]

  when OCI_PTYPE_SYN

    schema = info.attrGet(OCI_ATTR_SCHEMA_NAME)

    name   = info.attrGet(OCI_ATTR_NAME)

    describe(schema + '.' + name)

  else raise %Q{"DESC #{name}" failed; not a table or view.}

  end

end

Could it be possible that, behind the stage, the Oracle Database
Engine does the "magic" of alter session set current_schema =
"prod_schema" automatically creating a lot of synonyms for my user? If
that is the case (and I'm wild guessing here, I don't know jack of the
Oracle engine), could the problem be that ar-jdbc doesn't
retrieve/treat the names differently like ar-oracle-adapter? Or
ar-jdbc reads meta-data in a place different form where ar-oracle does
- and the app user doesn't have the necessary permissions?

Last, there is a very minor bug and a suggestion to be made - first,
the minor bug: I thought the following lines weren't needed in
environment.rb if I'm using Rails 2.1.2:

    if RUBY_PLATFORM =~ /java/
      require 'rubygems'
      gem 'activerecord-jdbc-adapter', '0.9.1'
      require 'jdbc_adapter'
    end

But I have to keep them, or I get the "uninitialized constant
ActiveRecord::VERSION (NameError)" exception - stack trace follows:

/jruby-1.2.0/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:279:in
`load_missing_constant': uninitialized constant ActiveRecord::VERSION
(NameError)
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:468:in
`const_missing'
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.1/lib/active_record/connection_adapters/jdbc_adapter.rb:14
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.1/lib/active_record/connection_adapters/jdbc_adapter.rb:31:in
`require'
        from
/jruby-1.2.0/bin/../lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in
`require'
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:510:in
`require'
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:355:in
`new_constants_in'
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:510:in
`require'
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.1/lib/active_record/connection_adapters/oracle_adapter.rb:1
         ... 36 levels...
        from
/jruby-1.2.0/lib/ruby/gems/1.8/gems/rails-2.1.2/lib/commands/server.rb:31:in
`require'
        from
/jruby-1.2.0/bin/../lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in
`require'
        from script\server:3


And, finally, the suggestion (feature request): could we have an easy,
quick "schema" parameter in database.yml? Something like:

homologation:
  adapter: oracle
  host: <my host address>
  port: <usually 1521>
  database: <my SID>
  schema: <the schema not owned by me...>
  username: <user>
  password: <pass>

Since I come from Delphi, I really can't get over the fact that I
can't just configure my working schema with whatever user in a quick &
easy way like I used to (I used the Direct Oracle Access components
and I could config user_name, pass, connection string - or
TNSNAMES.ORA alias - and working schema on my OracleSession object).
We, Oracle users, are not to blame if Oracle uses a database address (
a SID) and a schema address on top of that... =)

Sorry for the terribly long post. I really appreciate the attention
and, if I need to give any extra details or do any further
investigation, I'll gladly do it - and I'll be lurking my Inbox this
night...

-Renato.
526d60de6472502bb570a9df2842b33b?d=identicon&s=25 Nick Sieger (Guest)
on 2009-03-20 01:47
(Received via mailing list)
On Thu, Mar 19, 2009 at 6:58 PM, Renato Santos
<renato.passos.santos@gmail.com> wrote:
> To make things easier, the DBA created a logon trigger to the app's user
> (let's suppose the name of the app user is "my_app_user") that does
> something like this:
>
> alter session set current_schema = 'app_production_schema'

What if you put this in an environment initializer?

ActiveRecord::Base.connection.execute "alter session set
current_schema = 'app_production_schema'"

I agree that if possible it would be nice to have the adapter do this
for you. But how do you configure the schema for MRI?

So, it's quite likely that the Oracle support has drifted a bit. None
of the maintainers use Oracle, and though we are able to run some
tests against it, we don't have any experience with things like
synonyms and non-standard schemas and the like.

It's possible that some of the logic you showed from the native Oracle
adapter needs to be ported over in order for jdbc to function the same
way.

At this point I'd suggest getting a copy of the
activerecord-jdbc-adapter code and starting to modify it to see if you
can get it to do what you want.

Cheers,
/Nick

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email
03ebcbbface06500195e3d3fe4d69119?d=identicon&s=25 Renato Santos (Guest)
on 2009-03-20 04:28
(Received via mailing list)
I've just arrived at home (it's 23:00h here).
Many, many thanks for the quick response.

On Thu, Mar 19, 2009 at 9:46 PM, Nick Sieger <nicksieger@gmail.com>
wrote:

>
> What if you put this in an environment initializer?
>
> ActiveRecord::Base.connection.execute "alter session set
> current_schema = 'app_production_schema'"
>

Will try tomorrow morning. Can I put that in any file I create inside
config/initializers? (I've been working mostly with Rails 1.2.6 - I've
just
"migrated" to 2.1.2).

I agree that if possible it would be nice to have the adapter do this
> for you. But how do you configure the schema for MRI?


Unfortunately, most people has to use the same solution you gave to me -
I
used to have that line in environment.rb. After a while, I thought it
was
too ugly to live with and tried to summon the courage (I was a complete
noob
- still feel like one...) to hack into the oracle_adapter.rb of
activerecord-1.2.6 - which I eventually did, way before hearing of the
existence of JRuby. Here are the relevant classes and methods (taken
from
current activerecord-oracle-adapter) before my hacks:

  class OCI8AutoRecover < DelegateClass(OCI8) #:nodoc:
    attr_accessor :active
    alias :active? :active

    cattr_accessor :auto_retry
    class << self
      alias :auto_retry? :auto_retry
    end
    @@auto_retry = false

    def initialize(config, factory = OracleConnectionFactory.new)
      @active = true
      @username, @password, @database, = config[:username].to_s,
config[:password].to_s, config[:database].to_s
      @async = config[:allow_concurrency]
      @prefetch_rows = config[:prefetch_rows] || 100
      @cursor_sharing = config[:cursor_sharing] || 'similar'
      @factory = factory
      @connection  = @factory.new_connection @username, @password,
@database, @async, @prefetch_rows, @cursor_sharing
      super @connection
    end

    (...other methods...)
  end

  class OracleConnectionFactory #:nodoc:
    def new_connection(username, password, database, async,
prefetch_rows,
cursor_sharing)
      conn = OCI8.new username, password, database
      conn.exec %q{alter session set nls_date_format = 'YYYY-MM-DD
HH24:MI:SS'}
      conn.exec %q{alter session set nls_timestamp_format = 'YYYY-MM-DD
HH24:MI:SS'} rescue nil
      conn.autocommit = true
      conn.non_blocking = true if async
      conn.prefetch_rows = prefetch_rows
      conn.exec "alter session set cursor_sharing = #{cursor_sharing}"
rescue nil
      conn
    end
  end

Off the top of my head, what I did at the time was check the existence
of a
"schema" config parameter in OCI8AutoRecover.initialize, pass it to
OracleConnectionFactory.new_connection, and then add a line that looks
like conn.exec
"alter session set current_schema = #{schema}" to that method. Worked
very
well, made my work a lot more confortable (the hack was only in the
adapter,
not in the environment.rb - and I finally had my "schema" config
parameter
in database.yml...) but I didn't know for sure if it was the most
correct
way of doing it and I was swamped by projects after that - I should have
sent that to the Rails team and their e-mail list... My bad. Will do it
as
soon as I can.

So, it's quite likely that the Oracle support has drifted a bit. None
> can get it to do what you want.
Well, already have "trunk" at my work, built it some times before
release of
0.9.1. But I have huge difficulties to understand where the Java part of
the
code "plugs" into the Ruby part (looks like wizardry), and that really
gets
in the way.

And then, there's the problem of my Java near-illiteracy, although I can
overcome that if needed. My real problem is my complete illiteracy
regarding
the Oracle engine and its drivers, and I don't really know where to
begin
learning about it - and while I will do it for sure, I fear I will not
acquire the knowledge needed to solve my problem before my deadline. And
I'm
still swamped by more than one work...

Well, thanks again for the quick reply - it did give me some things to
try,
really appreciated. I will keep you informed of my progress.

...But any help, from anyone, no matter how small, is still greatly
needed
and appreciated.  =)

-Renato
526d60de6472502bb570a9df2842b33b?d=identicon&s=25 Nick Sieger (Guest)
on 2009-03-20 16:50
(Received via mailing list)
We have a new mechanism for extending the connection class per
database. So it seems like you could do something like this:

diff --git a/lib/jdbc_adapter/jdbc_oracle.rb
b/lib/jdbc_adapter/jdbc_oracle.rb
index b417f28..8896071 100644
--- a/lib/jdbc_adapter/jdbc_oracle.rb
+++ b/lib/jdbc_adapter/jdbc_oracle.rb
@@ -94,6 +94,20 @@ module ::JdbcSpec
       end
     end

+    def self.jdbc_connection_class
+      OracleJdbcConnection
+    end
+
+    class OracleJdbcConnection <
::ActiveRecord::ConnectionAdapters::JdbcConnection
+      def initialize(config)
+        super
+        if config[:schema]
+          sql = ActiveRecord::Base.send(:sanitize_sql_array, ['alter
session set current_schema = ?', config[:schema]])
+          connection.execute sql
+        end
+      end
+    end
+
     def adapter_name
       'oracle'
     end

Cheers,
/Nick


On Thu, Mar 19, 2009 at 10:27 PM, Renato Santos
<renato.passos.santos@gmail.com> wrote:
> config/initializers? (I've been working mostly with Rails 1.2.6 - I've just
> existence of JRuby. Here are the relevant classes and methods (taken from
>       @active = true
>     (...other methods...)
>       conn.non_blocking = true if async
> like conn.exec "alter session set current_schema = #{schema}" to that
>> synonyms and non-standard schemas and the like.
> 0.9.1. But I have huge difficulties to understand where the Java part of the
> ...But any help, from anyone, no matter how small, is still greatly needed
>>    http://xircles.codehaus.org/manage_email
>>
>>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email
03ebcbbface06500195e3d3fe4d69119?d=identicon&s=25 Renato Santos (Guest)
on 2009-03-20 17:31
(Received via mailing list)
Will try that ASAP. Many thanks.

-Renato.
This topic is locked and can not be replied to.