Project 2.0

Jan 16, 2012

Objective

Import an existing flat-file database into a SQLite table.

Create a simple Ruby program that reads and writes to the database.

Scenario

Your roommate has started to acquire a pretty large collection of wine tasting notes, and to stay organized has attempted to use an Excel spreadsheet to keep track of his data.

"Maaaaaan," he says, "I don't think this excel spreadsheet thing is worth the trouble. Every time I want to add new data to the spreadsheet I have to find the right row position, insert a new blank row, and then enter in the data. Also, it's a pain to find things quickly and to make changes to certain things if I make a mistake. Maybe I should just have another glass of wine and forget about this whole spreadsheet thing."

"Dude, you should totally use a database rather than Excel," you reply. And you explain the merits of of managing data with a proper RDBMS, which you learned about in your CSCI403 class (what a coincidence!).

You ask your roommate for the Excel file that he was using, and he also provides it to you in pipe-delimited format.

"Maaaaan," he smiles, "if you can help me out I'll definitely look for your car keys... which I lost yesterday."

Requirements

First you need to explore the data and get a feel for the domain (what are you modeling?).

Create a subdirectory of cs403 called project02. This is your dedicated working directory for all Project 2 files and data.

At this point your directory structure should look like this:

Step 1: Explore the Data

Navigate to your project02 directory and download the wine.xlsx file and wine.pd.txt file.

Create a README file inside your project02 directory.

In your README file, describe what Project 2 is. For example:

Next, take a look at the excel and pipe-delimited files. What problems or issues do you notice about the pipe-delimited file? Make a note in your README file; we will address these issues later.

Step 2: Import the Data into SQLite

Quickly read SQLite chapter 2 pages 32 - 46 ("The Command-Line Program" to "Summary"). Refer to page 40, "Importing Data" as a guide.

Create a database in your project02 directory called wine.db. Import the data from wine.pd.txt into this SQLite database. (This may seem like a no-brainer, but you will face some issues that will educate you about importing external data into a SQLite database. Start early, ask questions on Piazza, and feel free to share the steps you undertook with your fellow students.)

At the end of this step, you should have a table called wine that has appropriate "columns" matching what you see in the excel spreadsheet / pipe-delimited file.

Step 3: Create Your First Database-Backed Application

Some Ruby guidance will be given in class mid-week for this step. Your goal is to create a simple Ruby program that can read from and write to your wine database. When executed, it should display a list of all the wine in the database, and then prompt the user to enter new data. For example:

In order to implement this program, I recommend the following order of steps:

• Install Ruby 1.9.2. here's an approachable guide to installing.
• Install the sqlite3 gem (gem install sqlite3)
• Edit the file wine.rb and try to open a connection to your sqlite3 database.
• Get your program to execute the query SELECT * FROM wine and give you a set of results.
• Iterate over the list of results, printing them to the screen (using Ruby's puts function, eg puts "hello!")
• Prompt the user (using gets and chomp for the six wine attributes, saving the values in some variables.
• Create a string representing a SQL INSERT query, which should add the data as a new record in the database.

If some of these ideas sound totally foreign to you, then you are in the right class! We will discuss these tasks in class, and of course, when you're stuck please post a message on Piazza or drop by office hours.

Grading Criteria (200 pts)

Create a .zip archive of your project02 directory and submit to blackboard.

This is due by midnight on Monday, January 23.