Kicking off a large "enterprise" application in RoR (switching over
from
the J2EE world), and I''m looking to make a few improvements to the
Oracle OCI adapter as we get started.
As written, the adapter assumes the tables are in the schema of the user
that RoR is using to connect to the db. In the Oracle world at least,
it''s better practice to have your apps connect as a less privileged
user, one that has the necessary privs of the app (select on most
tables, insert/update on some, perhaps delete on fewer) -- it reduces
the risk that the app does something bad by mistake.
We generally handle this by creating synonyms in the application schema,
pointing to the actual tables. Better than hardcoding the actual schema
name. The change to make the adapter handle synonyms is straightforward
-- just change the sql in the method OCIAdapter::columns from:
select column_name, data_type, data_default, data_length, data_scale
from user_tab_columns where table_name =
''#{table_name.upcase}''}
to:
select column_name, data_type, data_default, data_length, data_scale
from user_catalog cat, user_synonyms syn, all_tab_columns col
where cat.table_name = ''#{table_name.upcase}''
and syn.synonym_name (+)= cat.table_name
and col.owner = nvl(syn.table_owner, user)
and col.table_name = nvl(syn.table_name, cat.table_name)}
I''ll submit this as a patch shortly (once I get subversion up and
running). I''m also planning on making the following enhancements...
1. Handle table-specific sequences (eg, {#table_name}_seq, rather that
just rails_sequence).
2. Oracle 10g has a built-in "lock-version" column, called ora_rowscn.
Not a big deal, but it shouldn''t be too hard to let Rails use this
instead of lock version if the OCI adapter is being used.
If anyone else has taken a crack at implementing these or similar
changes, please let me know, so I don''t reinvent the wheel. Or if there
are just other folks interested in using the enhancement, you can help
me test. ;-)
thanks,
Michael
You are my hero. The DBAs at work are always giving me grief about the user schema thing. If you need any help with testing, we''re running Rails on 9iR2 here, with a migration to 10g in the semi-near future. It would be nice if something ''slicker'' could be done with the DATE vs. DATETIME thing, but I haven''t been able to come up with any good suggestions that don''t mangle up everyone else. Further, it would be cool if AR had a built-in way to append ''FOR UPDATE'' to various select statements, but now I''m reaching. Either way, thanks for your efforts. Michael Schoen wrote:> Kicking off a large "enterprise" application in RoR (switching over from > the J2EE world), and I''m looking to make a few improvements to the > Oracle OCI adapter as we get started.
I''m racing the clock trying to get a mildly complex Oracle/Rails app up and going now, and am looking forward to your patch. It''s not easy explaining to a DBA that "no, I REALLY REALLY want to have the one sequence for all tables. Yes, I understand the pain that goes with it, but that''s really what I want". Happy to be a tester, for that functionality at least. Regards Dave M. On 7/19/05, Michael Schoen <schoenm-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:> Kicking off a large "enterprise" application in RoR (switching over from > the J2EE world), and I''m looking to make a few improvements to the > Oracle OCI adapter as we get started. > > As written, the adapter assumes the tables are in the schema of the user > that RoR is using to connect to the db. In the Oracle world at least, > it''s better practice to have your apps connect as a less privileged > user, one that has the necessary privs of the app (select on most > tables, insert/update on some, perhaps delete on fewer) -- it reduces > the risk that the app does something bad by mistake. > > We generally handle this by creating synonyms in the application schema, > pointing to the actual tables. Better than hardcoding the actual schema > name. The change to make the adapter handle synonyms is straightforward > -- just change the sql in the method OCIAdapter::columns from: > > select column_name, data_type, data_default, data_length, data_scale > from user_tab_columns where table_name = ''#{table_name.upcase}''} > > to: > > select column_name, data_type, data_default, data_length, data_scale > from user_catalog cat, user_synonyms syn, all_tab_columns col > where cat.table_name = ''#{table_name.upcase}'' > and syn.synonym_name (+)= cat.table_name > and col.owner = nvl(syn.table_owner, user) > and col.table_name = nvl(syn.table_name, cat.table_name)} > > I''ll submit this as a patch shortly (once I get subversion up and > running). I''m also planning on making the following enhancements... > > 1. Handle table-specific sequences (eg, {#table_name}_seq, rather that > just rails_sequence). > > 2. Oracle 10g has a built-in "lock-version" column, called ora_rowscn. > Not a big deal, but it shouldn''t be too hard to let Rails use this > instead of lock version if the OCI adapter is being used. > > If anyone else has taken a crack at implementing these or similar > changes, please let me know, so I don''t reinvent the wheel. Or if there > are just other folks interested in using the enhancement, you can help > me test. ;-) > > > thanks, > Michael > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Would the presumption that the sequences are named
{#table_name}_seq
work for you? Or do you need something more flexible?
David Mitchell wrote:> I''m racing the clock trying to get a mildly complex Oracle/Rails
app
> up and going now, and am looking forward to your patch.
>
> It''s not easy explaining to a DBA that "no, I REALLY REALLY
want to
> have the one sequence for all tables. Yes, I understand the pain that
> goes with it, but that''s really what I want".
>
> Happy to be a tester, for that functionality at least.
>
> Regards
>
> Dave M.
>
> On 7/19/05, Michael Schoen
<schoenm-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:
>
>>Kicking off a large "enterprise" application in RoR (switching
over from
>>the J2EE world), and I''m looking to make a few improvements to
the
>>Oracle OCI adapter as we get started.
>>
>>As written, the adapter assumes the tables are in the schema of the user
>>that RoR is using to connect to the db. In the Oracle world at least,
>>it''s better practice to have your apps connect as a less
privileged
>>user, one that has the necessary privs of the app (select on most
>>tables, insert/update on some, perhaps delete on fewer) -- it reduces
>>the risk that the app does something bad by mistake.
>>
>>We generally handle this by creating synonyms in the application schema,
>>pointing to the actual tables. Better than hardcoding the actual schema
>>name. The change to make the adapter handle synonyms is straightforward
>>-- just change the sql in the method OCIAdapter::columns from:
>>
>> select column_name, data_type, data_default, data_length, data_scale
>> from user_tab_columns where table_name =
''#{table_name.upcase}''}
>>
>>to:
>>
>> select column_name, data_type, data_default, data_length, data_scale
>> from user_catalog cat, user_synonyms syn, all_tab_columns col
>> where cat.table_name = ''#{table_name.upcase}''
>> and syn.synonym_name (+)= cat.table_name
>> and col.owner = nvl(syn.table_owner, user)
>> and col.table_name = nvl(syn.table_name, cat.table_name)}
>>
>>I''ll submit this as a patch shortly (once I get subversion up
and
>>running). I''m also planning on making the following
enhancements...
>>
>>1. Handle table-specific sequences (eg, {#table_name}_seq, rather that
>>just rails_sequence).
>>
>>2. Oracle 10g has a built-in "lock-version" column, called
ora_rowscn.
>>Not a big deal, but it shouldn''t be too hard to let Rails use
this
>>instead of lock version if the OCI adapter is being used.
>>
>>If anyone else has taken a crack at implementing these or similar
>>changes, please let me know, so I don''t reinvent the wheel. Or
if there
>>are just other folks interested in using the enhancement, you can help
>>me test. ;-)
>>
>>
>>thanks,
>>Michael
>>
>>_______________________________________________
>>Rails mailing list
>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
On 7/19/05, Wilson <defiler-ifvz4xmYPRU@public.gmane.org> wrote:> You are my hero. The DBAs at work are always giving me grief about the > user schema thing. If you need any help with testing, we''re running > Rails on 9iR2 here, with a migration to 10g in the semi-near future. > It would be nice if something ''slicker'' could be done with the DATE vs. > DATETIME thing, but I haven''t been able to come up with any good > suggestions that don''t mangle up everyone else. > > Further, it would be cool if AR had a built-in way to append ''FOR > UPDATE'' to various select statements, but now I''m reaching. Either way, > thanks for your efforts.SELECT FOR UPDATE won''t work with idiomatic rails usage. Assume that you use action show_thing to display the thing to edit, then action edit_thing to handle the post. def show_thing @thing = Thing.find(params[:id]) # select 1 end User edits the Thing, sets a few attributes then posts the form. def edit @thing = Thing.find(params[:thing][:id]) # select 2 @thing.attributes=params[:thing] if @thing.save # update ... else ... end end Pessimistic locking won''t work with rails as there''s no way to guarantee that the two requests go to the same fcgi listener. So the only place you could use SELECT FOR UPDATE is between select 2 and update. That''s likely to be a few ms, and if your data is that highly contended, you probably need to rethink your model or the UI is going to suck. As for the other improvements, lock_version shouldn''t be hard to change, just look in lib/locking.rb. However be aware that the way it works is to explicitly do the UPDATE itself, if oracle expects this to behave differently, you''re probably better off creating your own module to include based on the current Locking one -- Cheers Koz
Wow, you got to use 10g AND RoR. What a lucky guy! On 7/18/05, Michael Schoen <schoenm-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:> Kicking off a large "enterprise" application in RoR (switching over from > the J2EE world), and I''m looking to make a few improvements to the > Oracle OCI adapter as we get started. > > As written, the adapter assumes the tables are in the schema of the user > that RoR is using to connect to the db. In the Oracle world at least, > it''s better practice to have your apps connect as a less privileged > user, one that has the necessary privs of the app (select on most > tables, insert/update on some, perhaps delete on fewer) -- it reduces > the risk that the app does something bad by mistake. > > We generally handle this by creating synonyms in the application schema, > pointing to the actual tables. Better than hardcoding the actual schema > name. The change to make the adapter handle synonyms is straightforward > -- just change the sql in the method OCIAdapter::columns from: > > select column_name, data_type, data_default, data_length, data_scale > from user_tab_columns where table_name = ''#{table_name.upcase}''} > > to: > > select column_name, data_type, data_default, data_length, data_scale > from user_catalog cat, user_synonyms syn, all_tab_columns col > where cat.table_name = ''#{table_name.upcase}'' > and syn.synonym_name (+)= cat.table_name > and col.owner = nvl(syn.table_owner, user) > and col.table_name = nvl(syn.table_name, cat.table_name)} > > I''ll submit this as a patch shortly (once I get subversion up and > running). I''m also planning on making the following enhancements... > > 1. Handle table-specific sequences (eg, {#table_name}_seq, rather that > just rails_sequence). > > 2. Oracle 10g has a built-in "lock-version" column, called ora_rowscn. > Not a big deal, but it shouldn''t be too hard to let Rails use this > instead of lock version if the OCI adapter is being used. > > If anyone else has taken a crack at implementing these or similar > changes, please let me know, so I don''t reinvent the wheel. Or if there > are just other folks interested in using the enhancement, you can help > me test. ;-) > > > thanks, > Michael > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Actually, I''d rather use just about anything other than Oracle. Rails'' integration with Oracle is "clumsy" at present, for want of a better term - Michael''s efforts will hopefully go a way towards fixing that. After using Postgres for the last few years, I find picking up Oracle again to be pretty painful. Why is there no boolean datatype? Why is there no text type, so I''m stuck with using a VARCHAR2(4000) type to hold text? Why do I have to explicitly insert a value for each primary key (i.e. rails_sequence.nextval) every time I insert a record? Why is debugging PL/SQL stored procedures such a painful and time consuming task? I know there''s a variety of reasons why Oracle is stuck with these things (e.g. backwards compatibility), but switching from Postgres to Oracle is like stepping back 20 years... Enough ranting, because I can see this kicking off a flame war. Dave M. On 7/19/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Wow, you got to use 10g AND RoR. What a lucky guy!
Happy to test as well, I''m developing against 10g in my home app, to get to know it a bit better. I''ve been thinking about making a change to the adapter as well, currently it selects the column details every time you create an object, I plan to fix that pronto (unless there''s a good reason for it :) ). On 7/19/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Actually, I''d rather use just about anything other than Oracle. > > Rails'' integration with Oracle is "clumsy" at present, for want of a > better term - Michael''s efforts will hopefully go a way towards fixing > that. > > After using Postgres for the last few years, I find picking up Oracle > again to be pretty painful. Why is there no boolean datatype? Why is > there no text type, so I''m stuck with using a VARCHAR2(4000) type to > hold text? Why do I have to explicitly insert a value for each > primary key (i.e. rails_sequence.nextval) every time I insert a > record? Why is debugging PL/SQL stored procedures such a painful and > time consuming task? I know there''s a variety of reasons why Oracle > is stuck with these things (e.g. backwards compatibility), but > switching from Postgres to Oracle is like stepping back 20 years... > > Enough ranting, because I can see this kicking off a flame war. > > Dave M. > > On 7/19/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Wow, you got to use 10g AND RoR. What a lucky guy! > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
I haven''t confirmed this yet for the OCI adapter, but my understanding is that the columns are only retrieved on every request when you''re in development mode -- so that the system refreshes immediately if the db structure changes. In production mode, it should only do this once (per fcgi process). If that''s not the case, then yes, we''ll need to fix that. Colin Fleming wrote:> Happy to test as well, I''m developing against 10g in my home app, to > get to know it a bit better. I''ve been thinking about making a change > to the adapter as well, currently it selects the column details every > time you create an object, I plan to fix that pronto (unless there''s a > good reason for it :) ). > > > On 7/19/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >>Actually, I''d rather use just about anything other than Oracle. >> >>Rails'' integration with Oracle is "clumsy" at present, for want of a >>better term - Michael''s efforts will hopefully go a way towards fixing >>that. >> >>After using Postgres for the last few years, I find picking up Oracle >>again to be pretty painful. Why is there no boolean datatype? Why is >>there no text type, so I''m stuck with using a VARCHAR2(4000) type to >>hold text? Why do I have to explicitly insert a value for each >>primary key (i.e. rails_sequence.nextval) every time I insert a >>record? Why is debugging PL/SQL stored procedures such a painful and >>time consuming task? I know there''s a variety of reasons why Oracle >>is stuck with these things (e.g. backwards compatibility), but >>switching from Postgres to Oracle is like stepping back 20 years... >> >>Enough ranting, because I can see this kicking off a flame war. >> >>Dave M. >> >>On 7/19/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >>> Wow, you got to use 10g AND RoR. What a lucky guy! >> >>_______________________________________________ >>Rails mailing list >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>http://lists.rubyonrails.org/mailman/listinfo/rails >> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
I would test eval whatever OCI improvements. Ive had 2 10g apps that i had to do in coldfusion as im just not comfy with oracles support in rails. Especially with id fields etc. Sam On 7/19/05, Michael Schoen <schoenm-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:> I haven''t confirmed this yet for the OCI adapter, but my understanding > is that the columns are only retrieved on every request when you''re in > development mode -- so that the system refreshes immediately if the db > structure changes. > > In production mode, it should only do this once (per fcgi process). > > If that''s not the case, then yes, we''ll need to fix that. > > > Colin Fleming wrote: > > Happy to test as well, I''m developing against 10g in my home app, to > > get to know it a bit better. I''ve been thinking about making a change > > to the adapter as well, currently it selects the column details every > > time you create an object, I plan to fix that pronto (unless there''s a > > good reason for it :) ). > > > > > > On 7/19/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > >>Actually, I''d rather use just about anything other than Oracle. > >> > >>Rails'' integration with Oracle is "clumsy" at present, for want of a > >>better term - Michael''s efforts will hopefully go a way towards fixing > >>that. > >> > >>After using Postgres for the last few years, I find picking up Oracle > >>again to be pretty painful. Why is there no boolean datatype? Why is > >>there no text type, so I''m stuck with using a VARCHAR2(4000) type to > >>hold text? Why do I have to explicitly insert a value for each > >>primary key (i.e. rails_sequence.nextval) every time I insert a > >>record? Why is debugging PL/SQL stored procedures such a painful and > >>time consuming task? I know there''s a variety of reasons why Oracle > >>is stuck with these things (e.g. backwards compatibility), but > >>switching from Postgres to Oracle is like stepping back 20 years... > >> > >>Enough ranting, because I can see this kicking off a flame war. > >> > >>Dave M. > >> > >>On 7/19/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> > >>> Wow, you got to use 10g AND RoR. What a lucky guy! > >> > >>_______________________________________________ > >>Rails mailing list > >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >>http://lists.rubyonrails.org/mailman/listinfo/rails > >> > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
I don''t think so, the code looks like this:
def columns(table_name, name = nil)
cols = select_all(%Q{
select column_name, data_type, data_default, data_length, data_scale
from user_tab_columns where table_name =
''#{table_name.upcase}''}
).map { |row|
OCIColumn.new row[''column_name''].downcase,
row[''data_default''],
row[''data_length''], row[''data_type''],
row[''data_scale'']
}
cols
end
No caching of any kind, or decisions about development modes...
On 7/19/05, Michael Schoen
<schoenm-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org>
wrote:> I haven''t confirmed this yet for the OCI adapter, but my
understanding
> is that the columns are only retrieved on every request when
you''re in
> development mode -- so that the system refreshes immediately if the db
> structure changes.
>
> In production mode, it should only do this once (per fcgi process).
>
> If that''s not the case, then yes, we''ll need to fix that.
On Jul 19, 2005, at 4:12 PM, Colin Fleming wrote:> I don''t think so, the code looks like this: > > def columns(table_name, name = nil) > cols = select_all(%Q{ > select column_name, data_type, data_default, data_length, > data_scale > from user_tab_columns where table_name = ''#{table_name.upcase}''} > ).map { |row| > OCIColumn.new row[''column_name''].downcase, row[''data_default''], > row[''data_length''], row[''data_type''], row[''data_scale''] > } > cols > end > > No caching of any kind, or decisions about development modes...Look in AR::Base. The AR class caches its columns rather than the adapter. In development mode classes are reloaded so the instance variable caching the column info will be wiped as well. Regards, jeremy
Ooops, my bad. Will check the code tonight, but I''d personally like to turn that off. I never change my DB schema without restarting the app, and since I''m doing a lot of bulk data loading at the moment it pollutes the logs no end.... On 7/20/05, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:> On Jul 19, 2005, at 4:12 PM, Colin Fleming wrote: > > I don''t think so, the code looks like this: > > > > def columns(table_name, name = nil) > > cols = select_all(%Q{ > > select column_name, data_type, data_default, data_length, > > data_scale > > from user_tab_columns where table_name = ''#{table_name.upcase}''} > > ).map { |row| > > OCIColumn.new row[''column_name''].downcase, row[''data_default''], > > row[''data_length''], row[''data_type''], row[''data_scale''] > > } > > cols > > end > > > > No caching of any kind, or decisions about development modes... > > Look in AR::Base. The AR class caches its columns rather than the > adapter. > > In development mode classes are reloaded so the instance variable > caching > the column info will be wiped as well. > > Regards, > jeremy >