3

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. Please let me know your opinion on this.

Improve this question
6
  • your GUI lets end-users write SQL? Commented Dec 27, 2010 at 18:07
  • 1
    GUI lets the user run sql query, but the GUI abstracts out most of the things, user will be just selecting columns, writing conditions using comboboxes... etc Commented Dec 27, 2010 at 18:14
  • Have a look into DBI module, which allows you to connect to mysql. Then just use appropriate GUI module (Win32::GUI) to make it easier to the users. If this solution is web based, use CGI instead... Commented Dec 27, 2010 at 18:40
  • @DVK Thanks, I will read more about sql injection Commented Dec 27, 2010 at 18:41
  • 3
    This is a very non-trivial kind of problem. Oh, sure, it may seem like a simple idea: map your search fields to one or more tables, joining automatically! It's such a brilliant idea that I've done it several times. However simple you think it is I assure you that it is less-so. You have to carefully document at least the relationships between your tables and which fields are in which tables, which is no small task. On top of that you are probably going to be maintaining a mapping between your columns and your fields and you may need to document the data types of every column. Good luck! Commented Dec 27, 2010 at 19:02

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

2 Comments

Thanks, My question is how can i join the selected tables automatically. For example: Lets say i have two tables "table1" "table2", and they are related by table1.book_idx=table2.book_idx. Whenever user selects the data from table1 and table2, user will not join the tables using the condition table1.book_idx=table2.book_idx, my script has to do it automatically. It seems like i can solve it by predefining the relations between all the table pairs, but it becomes a problem when data from more tables is selected
Yes, brute-force encoding the keys into the script is probably not optimal. However, if, for any two tables, the key columns are the only columns in common, then you could simply do a natural join. Or, if all else fails, you could include a script that takes all of the tables that are selected and finds the relations (in terms of common column names or foreign keys) between all pairs of the tables selected, and parses the joins that way. In other words, do the MySQL equivalent of \d [table_name] in PostgreSQL, grab the column names, parse out key constraints, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.