For this assignment you will write a "simple" database driven web application. For this assignment you can work with a partner from this course. This assignment is due Friday, December 5.

This assignment will reuse the following CSV (comma separated values) file of county census results from the U.S. Census Bureau web site that we used in programming assignment 4.

You are going to write a web based application that lets users look up the census results for any county in any state. Your web application will consist of a SQLite database made up of data from the above file, one very simple web page, and three CGI programs written in C that present the user with an HTML based user interface that accesses the SQLite database.

The first step of this assignment is to write an SQL script that uses the census data to create an SQLite database named CS442Hw6.db that contains a single table called CO_EST2007. Remember that the above data file contains a large number of columns, almost all of which are not needed for this assignment. Write your script so that your resulting table only contains the needed columns, which are SNAME, CTYNAME, and CENSUS2000POP. By not having all of the original columns in your table, you can reduce the size of the database from about 3MB to about 124KB, which will make your web application run much better. Note: Don't reduce the size of the table by editing the original CSV data file. Find SQL commands that will reduce the size of the table.

After you have created the database file CS442Hw6.db, copy it to the folder

In this zip file there is an HTML file called start.html. Copy this file to the folder

Your web application will consist of three CGI programs called state.c, county.c and population.c. The state.c program lets a user select a state. The county.c program lets a user select a county from their chosen state. And the population.c program looks up that county's population and reports it back to the user. The following paragraphs describe these three CGI programs in more detail.

Write a C program called state.c. This should be a CGI program. The compiled version state.exe will go in the C:\xampp\cgi-bin\ directory and it will be called by the Apache web server when a user clicks on the one link in the start.html web page. The state.c program should do an SQLite SQL query and look up the names of all the states in the CS442Hw6.db database. The result of the query should be used to populate a HTML drop down list in the user interface produced by state.c. An HTML drop down list is created by the HTML <select> tag inside of a HTML <form> tag. Here is a screen shot of the user interface that state.c should generate.

Note: What really makes a program a "CGI program" is that it gets its input (if there is any) from an environment variable (called QUERY_STRING) and its output, sent to standard out, is a bunch of HTML code (that gets sent to a browser to form a user interface). A CGI program is executed by a web server whenever a user clicks on a URL that points to an executable program in the web server's cgi-bin directory. Since the CGI program is executed by a web server, it is notoriously difficult to debug (how would you get the web server to start up the Visual Studio debugger for you?). There are a couple of tricks to make debugging CGI programs doable. First, instead of having your CGI program look up the QUERY_STRING environment variable, just hard code into your program a reasonable query string and run the program yourself in a console window. Redirect the output to a file, like temp.html, and open that file in a browser to see how it looks. Also, you can have your program write debugging messages to stderr and you can see them in the console window. If you do not want to hard code a query string into your program, you can use the console window's set command to create a value for the QUERY_STRING environment variable, and then have your program look up the value that you set. Finally, when you really have your program running as a CGI process under the server, you can use the server log file

In this zip file there is a folder cgi-examples containing three sample CGI programs, one written in Perl and two written in C. Use the two C sample programs to help you get an idea of how CGI processes work. Write your program state.c and get it working before your start working on the other two programs. Once your get state.c working, you will find that the other two programs are very similar to it.

Now back to your web application. When the user clicks on the "Look up State" button in the user interface from state.c, the web server will start up the county.exe process and pass it a QUERY_STRING environment variable containing the chosen state name as the value of a key called state. Here is a screen shot of the user interface that county.c should generate.

Notice that the query string is actually part of the URL showing in the browser's address window. (The URL is how the browser communicated the user's choice back to the server. The QUERY_STRING environment variable is how the server communicated the user's choice to the county.exe process. The URL and the environment variable are examples of "Interprocess Communication (IPC)".) Your county.c program should use the C Standard Library function getenv() to get the value of the QUERY_STRING environment variable and then your program should extract the state name from QUERY_STRING. Then your program should do an SQLite SQL query to look up all the counties in the database for the chosen state and then use the result to populate another drop down list. The drop down list's <form> tag's action attribute should have the value population.exe, where population.c is the next CGI program that you need to write. But you also need to comumicate to the population.exe process the user's previous choice of a state. The state name should be passed to the server by using a <input> control along with the <select> control inside the <form> tag. The <input> control should have its type attribute with the value hidden and its value attribute should be the state name. (A "hidden" control passes information to the server without having any noticable effect on the user interface.) Also, your county.c program will pretty much duplicate most of the code from the state.c program so that county.c can display the drop down list with the chosen state selected.

When the user clicks on the "Look up County" button in the user interface from county.c, the web server will start up the population.exe process and pass it a QUERY_STRING environment variable containing the chosen state name as the value of a key called state and the chosen county name as the value of a key called county. Here is a screen shot of the user interface that population.c should generate.

Notice that the query string with the two key-value pairs is actually part of the URL showing in the browser's address window. Your population.c program should get the QUERY_STRING environment variable and then extract from it the state and county names. Your program should then do an SQL query and look up in the database the population for the chosen county, which should then be displayed in the user interface. Your population.c program will also pretty much duplicate most of the code from the state.c and county.c programs so that population.c can display the two drop down lists with the chosen state and county names selected.

NOTE: Here are a few issues that you will need to consider.

1. When the user progresses from the state page to the county page, the state that they selected should stay selected in the county page. And when the user progresses from the county page to the population page, both the state and the county that they selected should remain selected.
2. Some states, like South Carolina, and some counties, like St. Joseph in Indiana, have spaces in their names. The web server treats these spaces in a pretty strange way. You will have to deal with that. But don't worry about this until you have your application working for states and counties with no spaces in their names.
3. In the sample CGI programs adder.c and errorLogger.c included in this zip file, there are examples of how to parse the QUERY_STRING environment variable
4. You will need a copy of the GCC compiler in order to link your programs with the sqlite3.o object file included in this zip file. You can download the MinGW version of GCC from the following link. MinGW compiler system installer
5. For a basic example of how to issue an SLQite SQL query in a C program, see the example in the following zip file from class. suppliersparts-C.zip

Turn in a zip file containing the following files. Remember to put in each of your files your name and assignment number. If you worked with a partner, be sure to put both of your names in each file. Turn in your SQL file that creates the database (please do not send me the dataset file or the SQLite database file). Turn in your three C programs that implement the CGI processes. And turn in the HTML start page. This assignment is due Friday, December 5.