How much database poking can I do?

I’ve got the assignment to make some sense out of our legacy database. I
thought a neat way to do that might be via ActiveRecord so that I could
present my findings like “A user has many letters, each letter belongs
to a recipient, each recipient has many email addresses…” like dat.

So what I’m wondering is whether ActiveRecord (or some other feature)
gives me the ability to poke around a connection, querying things like
“show tables”. If I could do that, then I could use what I know of our
naming scheme to start making some educated guesses, like:
if you find a table named “foo_table”, then it houses objects of type
Foo
the primary key for table Foo is foo_id
if you find a column called bar_id in a table, then assume that there
is a
Bar_table
and so on. I’d want to take a global view of the entire database so I
could see how much coverage I was getting, and what I was missing.

Ideally I’ll use ActiveRecord as much as possible to setup Ruby objects
whenever I find a business object to point to (probably auto generating
the code). But to fill in the cracks I’ll need to hit up the database
directly.

Can I do that? I’m running this all on my local snapshot of the
database so it’s entirely a personal project, not gonna hurt anybody but
myself. Just wondering, really, if I can do such a thing.

Duane M. wrote:

Ideally I’ll use ActiveRecord as much as possible to setup Ruby objects
whenever I find a business object to point to (probably auto generating
the code). But to fill in the cracks I’ll need to hit up the database
directly.

If you have a local snapshot of the database, you can simply dump the
SQL schema and look at the table structures :wink: Hope I didn’t
misunderstand your question.

Regards,
Robert

Robert S. wrote:

Duane M. wrote:

Ideally I’ll use ActiveRecord as much as possible to setup Ruby objects
whenever I find a business object to point to (probably auto generating
the code). But to fill in the cracks I’ll need to hit up the database
directly.

If you have a local snapshot of the database, you can simply dump the
SQL schema and look at the table structures :wink: Hope I didn’t
misunderstand your question.

Regards,
Robert

Thanks Robert :). I was hoping to put together something of an agent to
do some of the more obvious ones automatically, rather than scanning a
few hundred tables myself. In theory I’d end up with something that,
with programmable business rules, could be turned loose on any database,
not just the specific one I’m currently working with.

What I’ll probably end up doing is a combination – dump out the schemas
manually, but then write something that can parse them and build an
object model.

D

Honestly, I’m not sure how much help ActiveRecord will be able to give
you with something like this. It really only interprets database schema
information at the individual table level. It doesn’t look at foreign
key constraints in the database.

If your database has naming conventions for foreign key columns that are
strictly adhered to (a pretty huge if, in my experience with legacy
databases), you could probably at least write a Ruby script using
ActiveRecord to pull out all the column names, identify which ones match
your foreign key column naming conventions, and do some text parsing on
them to figure out what table and column they relate to. But if your
foreign key column names are random and arbitrary (like the ones I’ve
seen in every legacy database I’ve ever worked with), even this won’t be
possible.

Honestly, your best bet might be to dump the actual database schema (as
SQL) to text and use Ruby to parse that into a structure you can analyze
… should be simple enough to scan for foreign key definitions and so
forth in text.

Chris G. wrote:

Honestly, your best bet might be to dump the actual database schema (as
SQL) to text and use Ruby to parse that into a structure you can analyze

Oh yeah, just like you wrote an hour ago! Somehow I missed your last
reply there. My apologies!

On Nov 2, 2006, at 3:31 PM, Duane M. wrote:

gives me the ability to poke around a connection, querying things like
Bar_table
Can I do that? I’m running this all on my local snapshot of the
database so it’s entirely a personal project, not gonna hurt
anybody but
myself. Just wondering, really, if I can do such a thing.

Have you looked at “Dr. Nic’s Magic Models”? It’s hosted on
RubyForge at http://magicmodels.rubyforge.org/

While it doesn’t do exactly what you ask, I bet you’d get a pretty
good jump-start by seeing behind the curtain.

-Rob

Rob B. http://agileconsultingllc.com
[email protected]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs