Forum: Ruby on Rails Legacy database Oracle

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.
3a3e856b665333316075a35f78a7f8a3?d=identicon&s=25 Per-olof Hermansson (perherman)
on 2006-04-03 11:50
I am new to RoR, but totally sold to the concepts, so I have decided to
use RoR to develop a web client to our Oracle-based software that our
customers today use with a Windows client. At first we will develope
simple things like registering to receive email newsletters, and
registering/updating your name, address and telephone etc. Eventually we
hope to do a more full-bodied web-side client.

The database has a lot of tables, and the naming convention of course
differs from RoR.

Can anyone recommend a tutorial on how to tackle this issue?

Per-Olof Hermansson
F3dc06f587d1ff4c7366b102bfda9204?d=identicon&s=25 David Mitchell (Guest)
on 2006-04-03 12:18
(Received via mailing list)
I looked at doing this myself as my first Rails project, but quickly
decided it was too much to bite off.  I found a bunch of problems that
needed Edge fixes to be applied, and these promptly disappeared when
the next Rails release came out.  I had problems with sequences that
eventually required an Oracle-specific patch; I struggled get Rails to
talk to Oracle stored procs and views; there was some issue with
either text or large VARCHAR2 fields that bit me; probably quite a few
more that I've since forgotten.

Things have improved since then - new Rails releases are now much less
frequent than they were - but my recommendation would still be to try
implementing something using MySQL or Postgres as your first Rails
project.  Start with something relatively simple, or something that be
broken down into relatively simple pieces, get that working - then try
buidling your 2nd app to link to your legacy Oracle database.

You'll find that you can implement relatively simple projects
extremely quickly using Rails, so the amount of "non productive" time
spent learning and hacking on your first MySQL project will be
remarkably small.  Personally, I think that if you try to link into a
legacy Oracle DB before you acquire a reasonably sound understand how
Rails hangs together (in particular, how to interpret some of the more
common error messages you'll encounter as a newbie), you'll:
- ask a whole lot of questions of the newsgroup, and be disappointed
at the apparent quality of the answers (because what you're trying to
do is relatively complex for a Rails app, and you possibly won't have
the Rails background to interpret the answers properly)
- get frustrated at the lack of Oracle expertise in this mailing list
- get frustrated at the lack of expertise in connecting to legacy
databases in this mailing list
- probably struggle with HABTM relationships where there's extra
fields in the joining table (I'm guessing that's probably the case in
your database if it's got lots of tables and thus a non-trivial
design)
- find the documentation about connecting to Oracle lacking, and will
struggle getting Rails to work with stored procs, views, sequences and
foreign key constraints (based on my own experience)
- probably hit the point where you decide Rails is nothing but hype,
because you can't get your apparently simple app working properly and
no-one will appear to be able to help

Of course, maybe that's just me...  However, after going close to
dumping Rails out of frustration on *my* Oracle legacy DB, I
definitely found building a new app using Postgres was THE way to come
up to speed with Rails; there's lots of Postgres people in this
mailing list, Postgres works very well out of the box with Rails, and
incorporating things such as views and stored procs became a lot
easier because other people here could help me when I got stuck.

Bottom line: Rails has a definite sweet spot in terms of app
architecture, and integrating with large legacy Oracle databases is
quite a way from that sweet spot!

HTH

Dave M.
3a3e856b665333316075a35f78a7f8a3?d=identicon&s=25 Per-olof Hermansson (perherman)
on 2006-04-04 08:46
David Mitchell wrote:
> I looked at doing this myself as my first Rails project, but quickly
> decided it was too much to bite off.  ---
>
> Bottom line: Rails has a definite sweet spot in terms of app
> architecture, and integrating with large legacy Oracle databases is
> quite a way from that sweet spot!
>
> HTH
>
> Dave M.

Thanks for your lengthy and insightfull answer. I understand - what you
say it confirms what I have senced when reading RoR websites and
following forum discussions.

I will do as you suggest, start with MySQL for learning and training,
then do a simple solution with Postgres, and then see where I go from
there. Maybe it is easier to port our application from Oracle to
Postgres than getting RoR to work with our Oracle database. We have only
some 50 views, procedures, triggers.

Per-Olof H
Fd00fdfc23ab2cea4ae7a5b429d590ee?d=identicon&s=25 Derek Mailer (Guest)
on 2006-04-04 11:13
Per-Olof Hermansson wrote:
> David Mitchell wrote:
>> I looked at doing this myself as my first Rails project, but quickly
>> decided it was too much to bite off.  ---
>>
>> Bottom line: Rails has a definite sweet spot in terms of app
>> architecture, and integrating with large legacy Oracle databases is
>> quite a way from that sweet spot!
>>
>> HTH
>>
>> Dave M.
>
> Thanks for your lengthy and insightfull answer. I understand - what you
> say it confirms what I have senced when reading RoR websites and
> following forum discussions.
>
> I will do as you suggest, start with MySQL for learning and training,
> then do a simple solution with Postgres, and then see where I go from
> there. Maybe it is easier to port our application from Oracle to
> Postgres than getting RoR to work with our Oracle database. We have only
> some 50 views, procedures, triggers.
>
> Per-Olof H

I too am relatively new to RoR and climbing that learning curve with a
legacy Oracle database strapped to my back.

I agree with Dave, it's always going to help to get familiar with RoR by
trying out a few simple examples. I've messed around with a few MySQL
legacy databases, tried out some basic tutorials and all of that has
helped me get up to speed with RoR. I now recognise a lot of the common
errors which should make any new oracle-related errors easier to spot.

My next step on the ladder involves working with a large legacy oracle
database. So far I've defined all the relationships between tables and
have a web app that just let's me browse and search the legacy database.
I've not yet got around to messing with updates, deletes, stored
procedures, sequences and have no doubts that I'll encounter challenges
when doing so, but I've not encountered any Oracle-specific problems so
far.

If you want to eventually use Rails with your legacy Oracle database,
then why not do your experimenting with a test Oracle database. Create a
few simple tables with a variety of field types, set your foreign keys,
create views, triggers and everything else that your legacy database
has. Then use your test database in your experiment. That way you'll
have the proof that it can be done when you're finished experimenting.
If you hit a brick wall then there'll be no harm done and I bet there's
more than you, Dave and I on this mailing list developing oracle-backed
apps who would be willing to help out.

Rails abstracts a lot of the SQL to keep things simple for us, but bear
in mind you can still write sql to solve some of the problems you might
encounter. I've had to do this with some complex queries in order to
improve performance but I can see it being a useful way of overcoming
other problems too. If the worst comes to the worst you may be able to
replicate the function of your stored procedures in rails too.
3a3e856b665333316075a35f78a7f8a3?d=identicon&s=25 Per-Olof Hermansson (Guest)
on 2006-04-04 14:00
Derek Mailer wrote:
> I too am relatively new to RoR and climbing that learning curve with a
> legacy Oracle database strapped to my back.
>
Thanks for your input. I appreciate your views, and am glad that I am
not alone with interest in using RoR and Oracle toegether,

It gives me encouragement to go further into RoR,

Per-Olof
94baf3cd7c30715565b63dcdb2fe31eb?d=identicon&s=25 Ingo Weiss (Guest)
on 2006-04-04 14:09
(Received via mailing list)
Hi,

when the "Agile Web Development" book talks about routes, there are what
looks like console transcripts like the following:

URL> store
@params = {:controller => 'store', :action = 'index'}

Is there something like a console for testing routes (where you could
put in a url and see how rails would route it)??

Ingo
94baf3cd7c30715565b63dcdb2fe31eb?d=identicon&s=25 Ingo Weiss (Guest)
on 2006-04-04 16:06
(Received via mailing list)
Hello,

Let's say I have a model for a "house". Each house has_a "city", which
in turn has_a "state" which in turn has_a "country". The objective is to
retrieve all houses in a given state, say "Massachusetts".

Being new to Ruby on Rails what I like is that things that should be
simple usually are simple, and since we have easy access to associated
tables via properties, I would expect to be able to write something like
this:

House.find(:all, conditions => [house.city.state.name ==
"Massachusetts"])

Instead, it seems like I need to use SQL in the condition, and I have no
idea how to solve the above problem with SQL. How can this be done? Or
am I missing something? I hope I am!

Ingo
Cd8c9864d88bcafc164d8fdb820cc451?d=identicon&s=25 Chris (Guest)
on 2006-04-04 17:28
This is how you do it, using eager nested loading:

House.find(:all,:conditions=>"states.name='Massachusetts'",
:include=>[:city=>:state])
Cd8c9864d88bcafc164d8fdb820cc451?d=identicon&s=25 Chris (Guest)
on 2006-04-04 17:29
the above is assuming that your state table is named states
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-04-04 22:52
(Received via mailing list)
On 4/4/06, Per-Olof Hermansson <per-olof.hermansson@massmarketing.se>
wrote:
>
I'd like to chime in and say that Oracle works extremely well with
Rails.  I'm using it almost exclusively (with Postgres for
side/personal projects), and I haven't run into any serious
difficulties in the last eight months or so.

That being said, if you've got a legacy schema that isn't
Rails-friendly, you'll have some trouble no matter which database
adapter you use.  Other than that, I think you'll find the Oracle
support in Rails to be performant, well-documented, and well-tested.
The OCI8 adapter is better than most, thanks for the valiant efforts
of Kubo Takehiro.
An example: columns selected by the Oracle adapter are cleanly and
automatically converted to the appropriate Ruby types, even when you
write your own queries by hand.

If you run into trouble, just post here and we'll help you out.  If
you find a bug, write a unit test that fails, and open a ticket.

Good luck,
--Wilson.
6a657bbaaa704aa1fc7dfa6d680c06fc?d=identicon&s=25 Phillip Novess (Guest)
on 2006-04-04 23:36
(Received via mailing list)
I think the connectivity for Oracle is working great. However, the auto
typing you mention is a problem.

Can you override it or change it in your model? All my numbers come out
with decimals. This kinda sucks for things like ID's or phone
extensions.

Thanks,
phill

On Apr 4, 2006, at 2:50 PM, Wilson Bilkovich wrote:

On 4/4/06, Per-Olof Hermansson <per-olof.hermansson@massmarketing.se>
wrote:
>
I'd like to chime in and say that Oracle works extremely well with
Rails.  I'm using it almost exclusively (with Postgres for
side/personal projects), and I haven't run into any serious
difficulties in the last eight months or so.

That being said, if you've got a legacy schema that isn't
Rails-friendly, you'll have some trouble no matter which database
adapter you use.  Other than that, I think you'll find the Oracle
support in Rails to be performant, well-documented, and well-tested.
The OCI8 adapter is better than most, thanks for the valiant efforts
of Kubo Takehiro.
An example: columns selected by the Oracle adapter are cleanly and
automatically converted to the appropriate Ruby types, even when you
write your own queries by hand.

If you run into trouble, just post here and we'll help you out.  If
you find a bug, write a unit test that fails, and open a ticket.

Good luck,
--Wilson.
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails
3a3e856b665333316075a35f78a7f8a3?d=identicon&s=25 Per-Olof Hermansson (Guest)
on 2006-04-05 11:16
Wilson Bilkovich wrote:
> > I'd like to chime in and say that Oracle works extremely well with
> Rails.  I'm using it almost exclusively (with Postgres for
> side/personal projects), and I haven't run into any serious
> difficulties in the last eight months or so.
>
> That being said, if you've got a legacy schema that isn't
> Rails-friendly, you'll have some trouble no matter which database
> adapter you use.  Other than that, I think you'll find the Oracle
> support in Rails to be performant, well-documented, and well-tested.
> The OCI8 adapter is better than most, thanks for the valiant efforts
> of Kubo Takehiro.
> An example: columns selected by the Oracle adapter are cleanly and
> automatically converted to the appropriate Ruby types, even when you
> write your own queries by hand.
>
> If you run into trouble, just post here and we'll help you out.  If
> you find a bug, write a unit test that fails, and open a ticket.
>
> Good luck,
> --Wilson.

Thank you! Every reply I have got strengthens my decision to go with
RoR. It is great to be met with such willingness to help out!

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