Legacy database Oracle


#1

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 H.


#2

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.


#3

David M. 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


#4

Derek M. 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


#5

Hi,

when the “Agile Web D.” 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


#6

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


#7

This is how you do it, using eager nested loading:

House.find(:all,:conditions=>“states.name=‘Massachusetts’”,
:include=>[:city=>:state])


#8

Per-Olof H. wrote:

David M. 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.


#9

On 4/4/06, Per-Olof H. removed_email_address@domain.invalid
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 T…
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.


#10

the above is assuming that your state table is named states


#11

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 B. wrote:

On 4/4/06, Per-Olof H. removed_email_address@domain.invalid
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 T…
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
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


#12

Wilson B. 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 T…
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