A Baseball Database Server: PartI, the Master list

You can use the SHOW COLUMNS FROM Master to see all the column data and data types from the Master table. It should be self-explanatory, except for the first column, playerID. This uniquely identifies each player and will be used next week to cross-reference the Master table with all other tables.

SQL SELECT Command

You will be using a more robust SELECT command than last week:

     SELECT columns FROM Master
     WHERE where_conditions
     LIMIT offset, count

• columns is a list of column names or the wild card asterisk ('*')
• where_conditions are a joining (using the SQL conjunction AND) of string matching blocks

  	column LIKE 'regex'
     

  where regex is a string with possible wild card characters '%', which match any sequence of characters. Try the following

  	SELECT last,first,nickname,final FROM Master
  	WHERE  final LIKE '9/22/%' AND last LIKE 'W%';
     

  This matches any player whose final game was September 22 of any year, and whose last name begins with 'W'. ("No Neck" was a South-sider. Do you think "The People's Cherce" may be a typo?)
• offset and count are non-negative integers. offset refers to the first record to be displayed, offset from 0; count refers to the maximum number of records to be displayed. Try the following

     	SELECT last, first, college FROM Master
  	LIMIT 10000, 25;
     

  The offset is optional, in which case it is implicitly assumed to be 0:

          SELECT last, first, college FROM Master
          LIMIT 25;
     

Java Class code

You will supply your html package and ResultTable class code for the next two labs. These classes must be absolutely correct, so you will need to fix errors before you can begin this week's lab. The ResultTable will be placed in a new package database, so you will have to make the following modifications:

1. Change the package

   	package database;
       

2. Add import

   	import sql;
       

Download the new sql package, and the database package and unarchive it by

	tar xfz sql.tgz
	tar xfz database.tgz

Create your lab7 project. Remember to set your compliance to 5.0, and make sure you create separate folders for source code and compiled class code. Import your html package, the new sql package, and the new database. Import your ResultTable class into the database package. Before continuing rewrite some tests to make simple queries to the database in Eclipse. Your old tests will not work, since you are connecting to a new database; the old SelectQuery constructor works, but will return 17,000 records, unless the query is modified using the additions to SelectQuery. You will need to learn about the new SelectQuery class code and try the new type of queries. It is up to you to read the documentation and learn how to create queries. You need to make sure everything is working before you try to write new code.

When you have finished writing and testing your code, you can deploy the compiled .class files in their package directories under Tomcat. Move your three packages to

	/stage/tomcat-cmsc/username/webapps/WEB-INF/classes

	chmod -R 755 classes

Your Baseball Database Server

I have written a skeletal HTML form to give you an idea of the range of queries a client needs to be able make through your database server. The layout is only a suggestion, and you will need to create a nicer client friendly webpage. Your challenge is to turn the client request for data into a SQL query for selecting records from the Master database, and displaying these records in a nice HTML response table.

The HTML page returns parameters from the <FORM>, and these must be converted into an object of the java class SelectQuery, which ResultTable can use to request data from MySQL. ResultTable returns a Table object, which is easily converted into an HTML table for display. The challenge is that your JSP response page must not contain any java code, except calling constructors and methods from a java class you will write ResultTableJSP. This class is very similar to PizzaJSP from lab5, it turns raw parameter information collected from the HTML <FORM> on the webpage into a SelectQuery which can be passed on to ResultTable. It should also return an HTML table that your JSP can use to display the response.

Requirements for your JSP response

1. All records must contain the last name then the first name, as the first two columns. The user specifies the rest of the columns to be displayed. The order of the remaining columns is not important.
2. The first row of your HTML table must be a row of headers, containing the name of each column requested by the client.
3. All records, this week, are displayed in the order they appear in the database. Most players are alphabetized by last name and first name. Players who began after 2002 are placed at the end of the database, alphabetic, by year of debut. There is an example of this below.
4. You must NEVER display the playerID column. This is for internal use only. If the client requests all columns, this means all but playerID.
5. The client must be able to modify the search by string matching on the following columns

   last first nickname college

   Additionally, the client should be able to modify the search based on the bats and throws column of a player. These fields can take one of three values: 'R', 'L' or 'B'. It is not necessary that the user choose to display any of nickname, college, bats or throws to modify the search.
6. The HTML table returned must be readable and attractive. It should probably contain a border to help separate data. This may mean modifying your HTML Table class. Here is a simple example based on the query

   	SELECT last,first,nickname FROM Master 
   	WHERE nickname LIKE '%big%' AND last LIKE 't%';
       

   last	first	nickname	debut
   Thomas	Frank	Big Hurt	8/2/1990
   Thompson	Sam	Big Sam	1885-07-02
   Tipple	Dan	Rusty,Big Dan	9/18/1915
   Taylor	Aaron	Big Country	9/9/2002

   Note that Aaron Taylor is out of order, but debuted in 2002. He really does appear after the other players in the database.

Allow the user to modify the query by selecting from the debut and final game columns. These fields are strings of the form

        month/day/year

If you were paying attention you will notice that Big Sam Thompson debuted in '1885-07-02'. The Baseball Archive is a conglomeration of several databases, and there are inconsistencies. In this case, it only effects the Old Timers like Big Sam, so you can ignore these inconsistencies and search only using the format I have given above.

Handin

Your lab is due Sunday at 11:59pm. See handin.