# How much database poking can I do? **URL:** \ **Category:** Rails\ **Created:** [November 2, 2006, 9:31pm UTC](https://www.ruby-forum.com/t/how-much-database-poking-can-i-do/74250 "2006-11-02T21:31:48Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![dmorin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f0a364/32.png) [@dmorin](https://www.ruby-forum.com/u/dmorin)\ **Post date:** [November 2, 2006, 9:31pm UTC](https://www.ruby-forum.com/t/how-much-database-poking-can-i-do/74250/1 "2006-11-02T21:31:48Z")
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. ---
**Author:** ![dmorin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f0a364/32.png) [@dmorin](https://www.ruby-forum.com/u/dmorin)\ **Post date:** [November 3, 2006, 1:49pm UTC](https://www.ruby-forum.com/t/how-much-database-poking-can-i-do/74250/2 "2006-11-03T13:49:46Z")
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 😉 Hope I didn’t misunderstand your question. Regards, Robert ---
**Author:** ![dmorin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f0a364/32.png) [@dmorin](https://www.ruby-forum.com/u/dmorin)\ **Post date:** [November 3, 2006, 3:02pm UTC](https://www.ruby-forum.com/t/how-much-database-poking-can-i-do/74250/3 "2006-11-03T15:02:01Z")
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 😉 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 ---
**Author:** ![dmorin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f0a364/32.png) [@dmorin](https://www.ruby-forum.com/u/dmorin)\ **Post date:** [November 3, 2006, 4:13pm UTC](https://www.ruby-forum.com/t/how-much-database-poking-can-i-do/74250/4 "2006-11-03T16:13:32Z")
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. ---
**Author:** ![dmorin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f0a364/32.png) [@dmorin](https://www.ruby-forum.com/u/dmorin)\ **Post date:** [November 3, 2006, 4:16pm UTC](https://www.ruby-forum.com/t/how-much-database-poking-can-i-do/74250/5 "2006-11-03T16:16:08Z")
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! ---
**Author:** ![dmorin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f0a364/32.png) [@dmorin](https://www.ruby-forum.com/u/dmorin)\ **Post date:** [November 7, 2006, 10:56am UTC](https://www.ruby-forum.com/t/how-much-database-poking-can-i-do/74250/6 "2006-11-07T10:56:59Z")
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/](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](http://agileconsultingllc.com) removed\_email\_address@domain.invalid