Purdue Perl Mongers' data presentation challenge #1

The challenge is to display data from simple database as web page(s). There is no "best" solution to be found. Rather this is an exercise that should allow all of us to show each other interesting web presentation technologies and to allow an individual, if he wants, to spread his wings in order to explore a new-to-him presentation technology.

• While the use of Perl or a Perl-based library is encouraged any language or technique is allowed.
• If possible construct the program (web output) so that a non-programmer can easily modify the text of the output.
• If possible construct the program to be tolerate of extra DB fields (at end of data -- should be easy to do) and deletion of DB fields.
• Data security is assumed. In other words we will no be concerned about customers being able to view each other's data.
• Data is obtained via web calls. Results come back as JSON, TSV, CSV or Dumper. If another format is required (e.g., XML or pack) then ask.
• The database and web pages are lightly used. We do not have to be concerned with DB/web efficency. We should be concerned with end user efficency.
• While initially the DB does not have much data, for the challenge there will be a larger dataset.

Rick's example solution using Template::Toolkit

The DB design has three levels.

• Customers
  • ID [numeric]
  • Last name [40 char]
  • First name [40 char]
  • Phone [12 char]
  • Email address [60 char]
  • Address [200 char]
  • Projects
    • ID [numeric]
    • Status [14 char; fixed vocabulary]
    • Description [200 char]
    • Date Created [12 char]
    • HTML link [200 char]
    • Jobs
      • ID [numeric]
      • Status [14 char; fixed vocab]
      • Description [60 char]
      • Date completed [12 char]
      • Results [200 char]
      • HTML link [200 char]

Web call is to lab.genomics.purdue.edu/cgi-bin/cgiwrap/westerm/PMDB/data.cgi?select=XYZ&id=ID&format=ABC&html where:

• 'html' is optional; if present will create HTML-formatted data. Otherwise will be a text format. This is often best for further processing. 'html' may be good for web viewing.
• The 'format' option ('ABC') is one of:
  • tsv (default)
  • csv
  • json
  • dumper
• And 'select' is one of the following. The 'select; is in the format 'what_to_work_on-what_to_return'. The current 'select' values are as follows. Use the 'help' select to see the most up-to-date list.
  • help -- returns a list of all valid selects
  • all_customers-everything ... alias: a_c-e ... All information for all customers
  • all_customers-id_name ... alias: a_c-i_n ... All customers IDs with first and last names
  • customer-everything ... alias: c-e ... All information for a given customer (job, project and customer information)
  • customer-jobs ... alias: c-j ... For a given customer, job IDs, status and description
  • customer-meta ... alias: c-m ... All meta information for a given customer
  • customer-projects ... alias: c-p ... For a given customer, project IDs, status and description
  • customer-projects_everything ... alias: c-p_e ... For a given customer, all project and customer information
  • job-meta ... alias: j-m ... All information for a given job
  • project-jobs ... alias: p-j ... For a given project, job IDs, status and description
  • project-jobs_everything ... alias: p-j_e ... For a given project all job and project information
  • project-meta ... alias: p-m ... All information for a given project
  • sql ... alias: sql ... General SQL call; put SQL after "id="; limited to SELECT and DESCRIBE
  • tables ... alias: table ... Show table names in order to construct SQL queries
  Data is returned unsorted (unless, of course, you make up some custom SQL). Do not assume that the data is non-empty. More selects can be added.

Example of the various selects (these actually have '&html' added to them in order to see the results via a web browser):

• Show current select list -- lab.genomics.purdue.edu/cgi-bin/cgiwrap/westerm/PMDB/data.cgi?select=help

• TSV format ... /data.cgi?select=all_customers-id_name&format=tsv
• CSV format ... /data.cgi?select=all_customers-id_name&format=csv
• JSON format ... /data.cgi?select=a_c-i_n&format=json
• Dumper format ... /data.cgi?select=a_c-i_n&format=dumper

• Customer #4 ... /data.cgi?select=customer-meta&id=4
• Project #2 ... /data.cgi?select=project-meta&id=2
• Job #4 ... /data.cgi?select=job-meta&id=4

• Customer #4's jobs ... /data.cgi?select=customer-jobs&id=4
• Customer #4's projects ... /data.cgi?select=customer-projects&id=4
• Customer #4's complete projects information ... /data.cgi?select=customer-projects_everything&id=4
• Customer #4 everything ... /data.cgi?select=customer-everything&id=4
• Project #2's jobs ... /data.cgi?select=project-jobs&id=2
• Project #2's complete job info ... /data.cgi?select=project-jobs_everything&id=2
• Everything about everyone ... /data.cgi?select=all_customers-everything

• Tables ... /data.cgi?select=tables
• Raw SQL ... /data.cgi?select=sql&id='select id, project_id from PMjobs'