SQL query mapper

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

I'm looking for a tool that will help me map a rather large application. The database is kind of horrid as it has no foreign keys or primary keys. Also the columns follow no particular naming convention.

Basically what I want to do is take a query that looks like the following:

SQL Code: [ Select ]
SELECT BookTitle, Copyright, ab.AuthID
FROM Books AS b JOIN AuthorBook AS ab
    ON b.BookID=ab.BookID
ORDER BY BookTitle;
  1. SELECT BookTitle, Copyright, ab.AuthID
  2. FROM Books AS b JOIN AuthorBook AS ab
  3.     ON b.BookID=ab.BookID
  4. ORDER BY BookTitle;


And create a hash like the following:

Code: [ Select ]
# Books.BookID => [AuthorBook.BookID]
# AuthorBook.BookID => [Books.BookID]
  1. # Books.BookID => [AuthorBook.BookID]
  2. # AuthorBook.BookID => [Books.BookID]


Note it has both directions. The key of the hash is the look up column, and the value is an array of table.columns that it references.

Another example:

SQL Code: [ Select ]
SELECT BookTitle, Copyright, a.AuthFN, a.AuthMN, a.AuthLN
FROM Books AS b JOIN AuthorBook AS ab
    ON b.BookID=ab.BookID
JOIN Authors AS a
    ON ab.AuthID=a.AuthID
ORDER BY BookTitle;
  1. SELECT BookTitle, Copyright, a.AuthFN, a.AuthMN, a.AuthLN
  2. FROM Books AS b JOIN AuthorBook AS ab
  3.     ON b.BookID=ab.BookID
  4. JOIN Authors AS a
  5.     ON ab.AuthID=a.AuthID
  6. ORDER BY BookTitle;


Code: [ Select ]
# Books.BookID => [AuthorBook.BookID]
# AuthorBook.BookID => [Books.BookID]
# Authors.AuthID => [AuthorBook.AuthID]
# AuthorBook.AuthID => [Authors.AuthID]
  1. # Books.BookID => [AuthorBook.BookID]
  2. # AuthorBook.BookID => [Books.BookID]
  3. # Authors.AuthID => [AuthorBook.AuthID]
  4. # AuthorBook.AuthID => [Authors.AuthID]


Database examples from: http://www.java2s.com/Tutorial/MySQL/00 ... ptable.htm

Don't have an example right off the top of my head to illustrate the array part, but the idea is to have a single key for a column in a table and then show all the other tables/columns it maps to via the SQL queries in the app.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • this213
  • Guru
  • Guru
  • User avatar
  • Posts: 1260
  • Loc: ./

Post 3+ Months Ago

I can't think of anything that does this. If the database is frustrating enough, it might be worth the time to build something yourself, but there's nothing saying the column names have to be the same in each table - ie: Books.Author may be a foreign key for Authors.AuthorID. You'd have to actually parse each query in the script to get relationships. Certainly not impossible, but also certainly not a short script either.

If you come across anything that does do this, let me know. I can think of a lot of instances where this would come in handy.

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 144 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.