Forum: Ruby on Rails Oracle tablespaces

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.
Eustáquio R. (Guest)
on 2005-11-22 21:42
Hello there!

I know some Ruby programming, but I'm starting with Rails today, so
excuse me if it's a basic question, I searched about it but did not find
an answer.

Is there a way to create a controller using something like
tablespace.table?

I have two distinct tablespaces here and need to specify where is the
table I'm using with the controller, like bigtablespace.people for ex. I
could put a default tablespace for the user who connects to the
database, but the same user must have access on both tablespaces.

Thanks!
jeremy (Guest)
on 2005-11-22 23:40
(Received via mailing list)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 22, 2005, at 11:42 AM, Eustáquio Rangel wrote:
> Is there a way to create a controller using something like
> tablespace.table?
>
> I have two distinct tablespaces here and need to specify where is the
> table I'm using with the controller, like bigtablespace.people for
> ex. I
> could put a default tablespace for the user who connects to the
> database, but the same user must have access on both tablespaces.

I'm not an Oracle user, but you can try this:

class Foobar < ActiveRecord::Base
   self.table_name = 'sometablespace.foobars'
end


jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDg4+6AQHALep9HFYRAnpdAJ4tOZHC74/EqM395jsdJEzyDjCAXQCfckpb
UHnxUC5wSHzI/rAK9b+w2HI=
=aa+b
-----END PGP SIGNATURE-----
blair (Guest)
on 2005-11-23 00:24
(Received via mailing list)
Jeremy K. wrote:
>> could put a default tablespace for the user who connects to the
>> database, but the same user must have access on both tablespaces.
>
>
> I'm not an Oracle user, but you can try this:
>
> class Foobar < ActiveRecord::Base
>   self.table_name = 'sometablespace.foobars'
> end

Unfortunately, that won't work.  All output here with 'rake
freeze_edge'.

For our helpdesk system in Oracle, we have a model named Issue for an
its.issue
table.  The application logs in as 'its', so the Issue model has no
set_tablename.

As the user who owns the tablespace, this works:

 >> i = Issue.find(123185)
=> #<Issue:0xb6ccd2bc @attributes=........

As another user who doesn't have 'its' that as a default tablespace, you
get this:

 >> i = Issue.find(123185)
ActiveRecord::StatementInvalid: OCIError: ORA-00942: table or view does
not
exist: select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT *
FROM
issue WHERE (issue.issueid = 123185) ) raw_sql_ where rownum <= 1) where
raw_rnum_ > 0        from
./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:78:in
`log'

Adding

set_table_name 'its.issue'

to the model gets this

 >> i = Issue.find(123185)
ActiveRecord::StatementInvalid: OCIError: ORA-00904:
"ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE
(its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where
raw_rnum_ > 0

Note the duplicate 'its' here.

I'll be glad to help out and get this resolved, if somebody else wants
to supply
patches :)

Regards,
Blair

--
Blair Z., Ph.D.
<removed_email_address@domain.invalid>
Subversion and Orca training and consulting
http://www.orcaware.com/svn/
nicksieger (Guest)
on 2005-11-23 01:01
(Received via mailing list)
> I'll be glad to help out and get this resolved, if somebody else wants to
> supply
> patches :)



This is only a workaround, not a full solution, but will using Oracle
synonyms work? I have a rails app running against Oracle and the db user
does not own the tables, but there are public synonyms that make the
tables
accessible to the user.

But then you might run into the problem of rails using user_tab_columns
and
not finding the column definitions -- I worked around this with the
following override of OCIAdapter#columns:

module ActiveRecord
module ConnectionAdapters
class OCIAdapter < AbstractAdapter
# Use all_tab_columns instead of user_tab_columns
def columns(table_name, name = nil)
cols = select_all(%Q{
select column_name, data_type, data_default, data_length, data_scale
from all_tab_columns where table_name = '#{table_name.upcase}'}
).map { |row|
# strip quotes from default values
default = row['data_default']
default = default.gsub(/'/,'').strip if default
OCIColumn.new row['column_name'].downcase, default,
row['data_length'], row['data_type'], row['data_scale']
}
cols
end
end
end
end

This is with AR 0.11. I haven't noticed if this has been addressed in
edge
rails or not, does anyone know? I can file an item in Trac if not.

Cheers,
/Nick
Simon.Santoro (Guest)
on 2005-11-23 01:01
(Received via mailing list)
Eustáquio Rangel wrote:
> table I'm using with the controller, like bigtablespace.people for ex. I
> could put a default tablespace for the user who connects to the
> database, but the same user must have access on both tablespaces.

I think by tablespace you mean schema. Is that correct? In that case you
can use synonyms on the database, after granting the apropriate rights
to the user you connect with. Or you can use Jeremys solution (but you
have to grant [select|insert|update|whatever] to your connecting user
first).
Simon.Santoro (Guest)
on 2005-11-23 01:09
(Received via mailing list)
Blair Z. wrote:
>>> I have two distinct tablespaces here and need to specify where is the
>>   self.table_name = 'sometablespace.foobars'
>
> rownum <= 1) where raw_rnum_ > 0        from
> ActiveRecord::StatementInvalid: OCIError: ORA-00904:
> "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select
> raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE
> (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where
> raw_rnum_ > 0
>
> Note the duplicate 'its' here.
>
> I'll be glad to help out and get this resolved, if somebody else wants
> to supply patches :)

This is a bug in AR for sure.
Maybe you can use
alter session set current schema=its;
before executing the query?
I think that should work, but it's a very ugly hack.
rick (Guest)
on 2005-11-23 01:09
(Received via mailing list)
* Nick S. (removed_email_address@domain.invalid) [051122 18:00]:
> This is only a workaround, not a full solution, but will using Oracle synonyms
> work?  I have a rails app running against Oracle and the db user does not own
> the tables, but there are public synonyms that make the tables accessible to
> the user.

We were using public synonyms sort of by default (i.e., our DBA just
added them as a matter of course), but as we scaled up development we
had each user with his own schema for doing testing and we noticed that
what was going on in one schema could affect what was going on in
another.  After a hair-pulling session one afternoon we identified the
problem, nuked the public synonyms and haven't looked back.

Rick
--
 http://www.rickbradley.com    MUPRN: 533
                       |  friends climb in and say,
   random email haiku  |  wow, that fresh clean leather >scent
                       |  is incredible.
Simon.Santoro (Guest)
on 2005-11-23 01:17
(Received via mailing list)
Rick B. wrote:
> We were using public synonyms sort of by default (i.e., our DBA just
> added them as a matter of course), but as we scaled up development we
> had each user with his own schema for doing testing and we noticed that
> what was going on in one schema could affect what was going on in
> another.  After a hair-pulling session one afternoon we identified the
> problem, nuked the public synonyms and haven't looked back.
>
> Rick

But you can use private synoyms.
for example:
connect as user a
grant select on person to user_b;
connect as user b
create synonym person for user_a.person;
select * from person;

This works, and the synonym person is only visible to user b
blair (Guest)
on 2005-11-23 01:25
(Received via mailing list)
Simon S. wrote:
>>>> tablespace.table?
>>> I'm not an Oracle user, but you can try this:
>> its.issue table.  The application logs in as 'its', so the Issue model
>>  >> i = Issue.find(123185)
>>
>>
>> I'll be glad to help out and get this resolved, if somebody else wants
>> to supply patches :)
>
>
> This is a bug in AR for sure.
> Maybe you can use
> alter session set current schema=its;
> before executing the query?
> I think that should work, but it's a very ugly hack.

I tried adding this to my AR class, but it didn't work.  Here's from the
command
line:

$ sqlplus username/passwd@dbhost

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 22 15:20:54 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining
options

SQL> alter session set current schema=its;
alter session set current schema=its
                   *
ERROR at line 1:
ORA-00922: missing or invalid option


What's the correct syntax?

Regards,
Blair

--
Blair Z., Ph.D.
<removed_email_address@domain.invalid>
Subversion and Orca training and consulting
http://www.orcaware.com/svn/
blair (Guest)
on 2005-11-23 01:25
(Received via mailing list)
Simon S. wrote:
>
>
> But you can use private synoyms.
> for example:
> connect as user a
> grant select on person to user_b;
> connect as user b
> create synonym person for user_a.person;
> select * from person;
>
> This works, and the synonym person is only visible to user b

To help Rails to make inroads into corporate environments by sneaking
under the
radar, it would be great Rails could use Oracle databases without having
to make
any modifications of the db, such as adding synonyms, otherwise the DBA
gets
involved and may ask more questions than you'd like :)  So anything to
use a
schema would help a lot.

Regards,
Blair

--
Blair Z., Ph.D.
<removed_email_address@domain.invalid>
Subversion and Orca training and consulting
http://www.orcaware.com/svn/
simon.santoro (Guest)
on 2005-11-23 09:24
(Received via mailing list)
Blair Z. wrote:
> $ sqlplus username/passwd@dbhost
> options
>
> SQL> alter session set current schema=its;
> alter session set current schema=its
>                   *
> ERROR at line 1:
> ORA-00922: missing or invalid option
>
>
> What's the correct syntax?

oh serry. i wrote this without checking. the correct syntax is
alter session set current_schema=its;
Eustáquio R. (Guest)
on 2005-11-23 12:25
Hello guys, thanks for all the answers and tips.

> I think by tablespace you mean schema. Is that correct? In that case you
> can use synonyms on the database, after granting the apropriate rights
> to the user you connect with. Or you can use Jeremys solution (but you
> have to grant [select|insert|update|whatever] to your connecting user
> first).

The tablespaces are really different files, one not so big named like

not_so_big_and_not_so_often_updated

with a table called PEOPLE, for example, and other

big_and_often_updated

with a table called ORDERS, for example, so we keep it on different
files.
To refer to a table with a user, a need something like

not_so_big_and_not_so_often_updated.PEOPLE
big_and_often_updated.ORDERS

Even if I can insert the "alias" after created the controller, I can't
see a way to create the controller with the "alias" of them and seems
that I'll need to hack it after created, as you told me. And using the
current_schema will not allow me to make queries like

select a.customer_id, a.name, b.order, b.value
from
not_so_big_and_not_so_often_updated.PEOPLE a,
big_and_often_updated.ORDERS b
where
a.customer_id=b.customer_id
rick (Guest)
on 2005-11-23 17:54
(Received via mailing list)
* Blair Z. (removed_email_address@domain.invalid) [051122 18:25]:
> To help Rails to make inroads into corporate environments by sneaking under
> the radar, it would be great Rails could use Oracle databases without
> having to make any modifications of the db, such as adding synonyms,
> otherwise the DBA gets involved and may ask more questions than you'd like
> :)  So anything to use a schema would help a lot.

We've been using Rails on an Oracle database (and on Postgres) since
before RubyConf and we're not currently using any sort of synonyms, etc.
Our DBAs set up a development schema, a production schema, and N testing
schemas (1 for each developer, one for nightly demo builds, one for
continuous integration builds).  I'm not a DBA so perhaps I'm missing a
salient detail, but after removing the troublesome synonyms we haven't
had any problems in this configuration.

Here's our database.yml file (with some names changed to protect the
guilty):

<%
 # determine our database user name from an environment variable
 user = (ENV['PHOENIX_USER'] || ENV['LOGNAME'] || ENV['USERNAME'])

 puts "User [#{user}]"

 # determine which database we are connecting to via an environment
variable
 case ENV['PHOENIX_DB']
   when 'oracle' # connecting to an Oracle database
   puts "Using Oracle"
%>
production:
  adapter: oci
  host: CNPROD
  username: unknown
  password: unknown

test:
  adapter: oci
  host: CNVOL
  username: <%= user ? user : 'cnet' %>
  password: XXXXXXXX

development:
  adapter: oci
  host: CNDEV
  username: cnet
  password: cnet

<%
   else # connecting to a PostgreSQL database
%>
production:
  adapter: postgresql
  database: centernet
  host: dbhost
  username: centernet
  password: XXXXXXXXX

test:
  adapter: postgresql
  database: rewrite_<%= user ? "#{user}_" : '' %>vol
  host: dbhost
  username: centernet
  password: XXXXXXXX

development:
  adapter: postgresql
  database: rewrite
  host: dbhost
  username: centernet
  password: XXXXXXXXX
<% end %>

(Yes, I'm going to make another pass through there and merge some of the
commonalities between rules).

So, by default we're using Postgres, but we can use Oracle by setting an
environment variable (PHOENIX_DB=oracle).

In the Oracle environment, each login has its own schema, so everyone's
got an isolated space for test_unit/test_functional.

Again, maybe there's some voodoo they pulled on the back-end that I'm
not clear on, but I don't know of anything at the moment.

Rick
--
 http://www.rickbradley.com    MUPRN: 156
                       |  it is based on
   random email haiku  |  the Prism2 chipset, then it
                       |  is compatible.
schoenm (Guest)
on 2005-11-23 20:52
(Received via mailing list)
> To help Rails to make inroads into corporate environments by sneaking
> under the radar, it would be great Rails could use Oracle databases
> without having to make any modifications of the db, such as adding
> synonyms, otherwise the DBA gets involved and may ask more questions
> than you'd like :)  So anything to use a schema would help a lot.

Accessing tables in a different schema is supported in edge rails.

Personally I find that using private synonyms is the cleanest approach,
but in environments where that isn't allowed, you can specify a table as

	set_table_name "owner.table_name"

If that's not working for you (again, with edge rails), let me know and
I'll help you work through it.
schoenm (Guest)
on 2005-11-23 21:04
(Received via mailing list)
I believe you've muddled the concepts of tablespace and schema.

Tablespaces can be implemented behind the scenes as files in the
underlying OS (though often a single tablespace is also made up of
several files), and it's fairly common to put tables with different
characteristics into different tablespaces.

But within SQL you don't reference a table with it's tablespace. If
you're doing something like...

	SELECT * FROM foo.people

...then "foo" is a schema/user name, NOT a tablespace name.
Eustáquio R. (Guest)
on 2005-11-23 21:43
> I believe you've muddled the concepts of tablespace and schema.

You're right! I messed with some concepts because the tablespaces are
the same name as the schemas. I have a "parameters" tablespace and user,
and a "data" tablespace and user. :-p

> But within SQL you don't reference a table with it's tablespace. If
> you're doing something like...
> 	SELECT * FROM foo.people
> ...then "foo" is a schema/user name, NOT a tablespace name.

That's the way the queries are here. Using schema/user.table.
But, being a tablespace or schema, the best way is using the synonyms or
edge rails, right? :-)
You gave me a very good tip. I think we'll be able to use synonyms.
Thanks!
blair (Guest)
on 2005-11-23 21:44
(Received via mailing list)
Simon S. wrote:
>>command line:
>>Production
>>What's the correct syntax?
>
>
> oh serry. i wrote this without checking. the correct syntax is
> alter session set current_schema=its;

Thanks, that works like a charm.  Changing my model to read like this:

class Issue < ActiveRecord::Base
   connection.execute('alter session set current_schema=its')
end

got the user to properly see the table.

Regards,
Blair
blair (Guest)
on 2005-11-23 21:52
(Received via mailing list)
Michael Schoen wrote:
>
>     set_table_name "owner.table_name"
>
> If that's not working for you (again, with edge rails), let me know and
> I'll help you work through it.

Hi Michael,

Yes, using set_table_name isn't working for me.

When I do that, I get this error:

 >> i = Issue.find(123185)
ActiveRecord::StatementInvalid: OCIError: ORA-00904:
"ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE
(its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where
raw_rnum_ > 0

Note the double appearance of its in the WHERE clause.

Regards,
Blair

--
Blair Z., Ph.D.
<removed_email_address@domain.invalid>
Subversion and Orca training and consulting
http://www.orcaware.com/svn/
blair (Guest)
on 2005-11-23 22:04
(Received via mailing list)
Rick B. wrote:
>>>This works, and the synonym person is only visible to user b
> Our DBAs set up a development schema, a production schema, and N testing
> schemas (1 for each developer, one for nightly demo builds, one for
> continuous integration builds).  I'm not a DBA so perhaps I'm missing a
> salient detail, but after removing the troublesome synonyms we haven't
> had any problems in this configuration.

I'm not saying it doesn't work when it's all set up properly.

In my case, I needed to write a new Rails script using script/runner and
didn't
want to have the DBA make changes for the app, as the less work he did,
the
better for me and getting Rails used.

For the new app, they set up a new user 'svn2its', which didn't have
'its' as
its default schema and is also not the owner.

So anything in Rails to make this case easier to work with (no synonyms,
no
default schema, no ownership) would be great.

Maybe that's asking for too much :)  But using the

class Issue < ActiveRecord::Base
   connection.execute('alter session set current_schema=its')
end

trick did work.  Although, this may not work if you have Rails working
with more
than one schema.  In this case, I think the easiest way to do this is to
introduce an intermediate class and have the final model subclass it:

class OracleSchemaA < ActiveRecord::Base
   establish_connection
   connection.execute('alter session set current_schema=SCHEMAA')
end

class OracleSchemaB < ActiveRecord::Base
   establish_connection
   connection.execute('alter session set current_schema=SCHEMAB')
end

class Post < OracleSchemaA
   set_table_name 'post'
end

class Author < OracleSchemaB
   set_table_name 'author'
end

There are two classes that explicitly create separate connections to the
database, each with its own current_schema.

The set_table_names are required, otherwise AR seems to want to use
oracleschemaX as the table name.

Regards,
Blair
schoenm (Guest)
on 2005-11-23 22:41
(Received via mailing list)
> Yes, using set_table_name isn't working for me.
> When I do that, I get this error:
> Note the double appearance of its in the WHERE clause.

That may be a bug then, I'll take a look.

Executing the "current_schema" statement is a nice hack, but as you
pointed out in your other note, it's not a terribly good one. Let's
track down the bug and fix it.
schoenm (Guest)
on 2005-11-23 22:53
(Received via mailing list)
>
> Note the double appearance of its in the WHERE clause.

What revision of Rails are you using? I just confirmed again that with
edge rails this works properly.
blair (Guest)
on 2005-11-23 23:01
(Received via mailing list)
Michael Schoen wrote:
>>
>> Note the double appearance of its in the WHERE clause.
>
>
> What revision of Rails are you using? I just confirmed again that with
> edge rails this works properly.

I just retried with Rails edge (with 'rake freeze_edge'), now at
revision 3166.

Regards,
Blair
schoenm (Guest)
on 2005-11-23 23:58
(Received via mailing list)
>> What revision of Rails are you using? I just confirmed again that with
>> edge rails this works properly.
>
> I just retried with Rails edge (with 'rake freeze_edge'), now at
> revision 3166.

And? Did you get the same error? Edge is currently 3173, though I don't
see any changes since 3166 that should matter.

I'm going offline for a bit. If this still isn't working let me know and
let's take it off the list -- I'd like to send you some debug statements
to apply that may help identify the issue.
blair (Guest)
on 2005-11-24 00:14
(Received via mailing list)
Michael Schoen wrote:
>>> What revision of Rails are you using? I just confirmed again that
>>> with edge rails this works properly.
>>
>>
>> I just retried with Rails edge (with 'rake freeze_edge'), now at
>> revision 3166.
>
>
> And? Did you get the same error? Edge is currently 3173, though I don't
> see any changes since 3166 that should matter.

Yes, same error, even with r3174.

> I'm going offline for a bit. If this still isn't working let me know and
> let's take it off the list -- I'd like to send you some debug statements
> to apply that may help identify the issue.

OK.  Send me the debug statements privately and I'll test them out.

It would be interesting to see if you can find a similar select
statement in
your rails application and see what queries its doing.

Regards,
Blair
schoenm (Guest)
on 2005-11-26 04:20
(Received via mailing list)
> OK.  Send me the debug statements privately and I'll test them out.
>
> It would be interesting to see if you can find a similar select
> statement in your rails application and see what queries its doing.

That's what I tried -- I created what I think is an exact replica or
your scenario, and it worked properly -- the schema name was not
duplicated.
This topic is locked and can not be replied to.