I have a file that contains relational information for a database schema that I am working with and the file is in the following format. Each line has two tables and the keys they join on. (table 1 contains key 1 and table 2 contains key 2, table 1 and table 2 join on key 1 and key 2)

Example file

table 1  table 2  key 1  key 2
-------  -------  -----  -----
apple    bug      unid1  unid2
apple    cat      unid1  unid3
bug      dog      unid2  unid4
apple    dog      unid1  unid4
[download]

The above is a very generic example of the format i am dealing with and my goal is the create a perl script the will allow me to specify two table names and then the script will output how they join.

Example 1:

[dmathis@wildman dmathis]$ ./script apple bug

The output:

apple a
join bug b on a.unid1 = b.unid2
[download]

Example 2:

[dmathis@wildman dmathis]$ ./script apple dog

The output:

Option 1:
apple a
join bug b on a.unid1 = b.unid2
join dog d on b.unid2 = d.unid4

Option 2:
apple a
join dog d on a.unid1 = d.unid4
[download]

As you can see apple and dog don't join directly to each other in one scenario, but instead through bug. This has the potential to become very complex as with a bigger more complex example there would be multiple paths from table to table and I have no idea where to start. I have been brainstorming for a couple of weeks now but have come up fruitless so far.

Does anyone have any suggestions or modules that might help me?

Best Regards

David