automating sql joins using perl

I have a front end web application which queries the MySQL database and presents the user with the requested data. The database contains 25 tables; there are foreign keys relating tables. The problem here is the users are completely new to SQL; I cannot expect them to join the tables.

I want to write a script which generates the joins automatically. I want to know if there are any modules available in Perl which make this task easier?

My own experience with SQL is just a few months. 开发者_Go百科 Please let me know your opinion on this.

I'm not aware of any module which does it generically, and creating one is very non-trivial. The issues range from mapping which primary/foreign keys match (they aren't always named the same), to how to deal with NULL values (inner or outer joins?) to ensuring that the full set of tables is even completely joinaable.

Also, at least in Sybase (not sure about MySQL), there are performance optimization angles to doing this automatically which aren't trivial, even if you get the correct data.

To be perfectly honest, what I would do instead would be the following:

• First off, force the data definition to be recorded in a configuration (including, tables, columns, primary and foreign keys, and joinability as far as inner/outer joins).

• Split between the "main" tables and "enrichment" tables.

  An enrichment table is a table whose ONLY purpose is to be joined on its primary key to some of the main tables to add more columns to each row retrieved from main tables (I'd go as far as to say there should not be any where clauses on those tables except for joins to make life simpler). As an example, consider a notional library database. "reviews" table would be "enrichment" table - you only use the data there to enrich the book info, not to query against. "patrons" table would also be (for certain business uses) an "enrichment table", merely to join patron name/address to patron_id in another table.

• For main tables, forget letting the users build a query. Simply enumerate EVERY single query they might want to run as far as business scenarios (most possible joins just don't make sense/don't need to be done); then for each scenario write up VERY clear user-friendly description of what they're selecting and then an optimized query against main tables for that scenario. Plus, for each of those canned queries, build a list of FOREIGN keys selected in that query which might be of use to the users to enrich from enrichment tables. (e.g. if "patron_id" field is selected in some query, you add "patrons" as possible extra data listed)

• Then allow your users in the GUI to pick a specific use case scenario. Find the canned query matching that scenario. Then display the possible enrichment options (include patron address? y/n?). Adding those extra enrichment joins to the pre-canned query is fairly easy.

My best advice would be to parse the user input via the front end app and then use that to parse queries for the Perl DBI.