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:
SELECT BookTitle, Copyright, ab.AuthID
FROM Books AS b JOIN AuthorBook AS ab
ON b.BookID=ab.BookID
ORDER BY BookTitle;
- SELECT BookTitle, Copyright, ab.AuthID
- FROM Books AS b JOIN AuthorBook AS ab
- ON b.BookID=ab.BookID
- ORDER BY BookTitle;
And create a hash like the following:
# Books.BookID => [AuthorBook.BookID]
# AuthorBook.BookID => [Books.BookID]
- # Books.BookID => [AuthorBook.BookID]
- # 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:
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;
- 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;
# Books.BookID => [AuthorBook.BookID]
# AuthorBook.BookID => [Books.BookID]
# Authors.AuthID => [AuthorBook.AuthID]
# AuthorBook.AuthID => [Authors.AuthID]
- # Books.BookID => [AuthorBook.BookID]
- # AuthorBook.BookID => [Books.BookID]
- # Authors.AuthID => [AuthorBook.AuthID]
- # AuthorBook.AuthID => [Authors.AuthID]
Database examples from:
http://www.java2s.com/Tutorial/MySQL/00 ... ptable.htmDon'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.
#define NULL (::rand() % 2)
July 12th, 2011, 9:08 am
