-
Notifications
You must be signed in to change notification settings - Fork 6
homeWork0
In this assignment you will set up a simple Postgres database, and put it on the web using Ruby on Rails. It's not going to be anything fancy, but it will be surprisingly easy!
Please skim through this document to the end before getting to work!
PostgreSQL is the Database Management System (DBMS) we'll be using throughout the semester. It's one of the most full-function open-source DBMSs on the market -- in some aspects it's more advanced than the leading commercial systems. And it began as a research project here at Berkeley so you know it's good! During the semester you will not only be using and tuning Postgres, you'll be modifying its internals with new functionality.
In your instructional account, we've got you set up already so that you can administer your own databases with Postgres. If you like, you can also set up Postgres on your own machine -- it runs on most operating systems. See [http://www.postgresql.org] for more information.
Ruby on Rails (RoR) is a framework for building data-centric web applications. It has only been around for a few years, but it has already become extremely popular for building websites. Ruby is a scripting language that is easy to use and has nice clean object-oriented features. Rails is a set of packages implemented in Ruby that simplify the development of web applications. Rails also includes a webserver-based runtime environment for the code, a piece of software sometimes referred to as an "application server".
Rails offers an unusually easy-to-use framework to map from database records to programming language objects and back again (an "Object-Relational Mapping" or "ORM" layer). It also provides a fairly handy environment to generate dynamic HTML pages programmatically, including support for shiny interactive features using AJAX. Finally, it is designed to encourage good programming practices, including "Test-Driven Development" (TDD). We'll try to adhere to the ideal of TDD during this class, though we won't have any tests to write in this assignment.
All our assignments will be set up for the Solaris x86 machines in the instructional lab. (See the note at the end of this page if you want to use your own machine.) To see the list of Solaris x86 machines, log into any instructional machine and type "clients", choose menu item 13, and look for machines labeled "Solaris 10 X86" (rhombus, pentagon, cube, sphere, po, torus.)
Throughout these instructions we will be using rhombus as our example machine, but you are encouraged to pick one of the others to balance the workload; simply replace the name of that machine in the instructions below.
Your first assignment is to set up a simple database, and build a default RoR front-end to it.
-
Initialize and create your database. In your CS186 account, start by issuing the command:
% initdb
In order to be able to access databases remotely, you will need to copy over some configuration files as follows:
% cd ~/pgdata
% cp ~cs186/hw0/postgresql.conf .
cp: overwrite ./postgresql.conf (yes/no)? yes
% cp ~cs186/hw0/pg_hba.conf .
cp: overwrite ./pg_hba.conf (yes/no)? yes
% cd
The first of these files controls various features of the Postgres server, the second controls network access to the database. (If you're curious about them, you can read more at [http://www.postgresql.org/docs/8.2/interactive/config-setting.html] and [http://www.postgresql.org/docs/8.2/interactive/auth-pg-hba-conf.html] respectively, but feel free to skip this for now.) You are now ready to start the server, and create your database:
% pg_ctl start
% createdb hw0
CREATE DATABASE
%
Now, start the Postgres command-line tool, psql. It accepts SQL statements, and special control commands which start with backslash. You are encouraged to poke around in the help system via \h and \?. Here we'll simply list the tables in our database via the "\dt" command, and quit via "\q" (or control-D works as well).
% psql hw0
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
Type: \\copyright for distribution terms
\\h for help with SQL commands
\\? for help with psql commands
\\g or terminate with semicolon to execute query
\\q to quit
hw0=# \\dt
No relations found.
hw0=# \\q
%
-
Now we're ready to create our Rails application! The 'rails' command sets things up; it takes the name of the project as an argument:
% rails itunes
This will tell you that it has created the itunes subdirectory and a number of things within it. You now have the skeleton of an application built.
The next thing we need to do is to tell Rails about the "models" (database-backed object classes) we want to have in our application. Rails has a default naming scheme for classes and tables, in which we describe the class and table using a singular noun, but use the plural of that noun in cases where it's obvious. (Rails embraces the software engineering idea of convention over configuration, and this naming scheme is a prime example of that idea.) In our case, we have two classes, "track" and "playlist". We generate skeleton Ruby code for those classes with a built-in Rails script as follows:
% cd itunes
% ruby script/generate model track
...
%
Notice that each command above resulted in a number of files being generated. For now, notice in particular three files:
- app/models/track.rb: the Ruby file that holds the class description for the class called Track. Have a look at it: notice that it inherits (via the "<<" syntax) from a class called ActiveRecord. ActiveRecord is the Rails class for objects that are to be automatically stored in and retrieved from the database.
- test/unit/track_test.rb: an auto-generated "unit" test file, to encourage you to write tests to exercise your Track code.
- db/migrate/001_create_tracks.rb: we'll return to this one in a moment!
Let's do the same thing for the playlist class
% ruby script/generate model playlist
....
- Now that we've got the Ruby classes defined, we need to get the tables set up in the database to store the class instances that get generated. Rather than do this directly with psql, we'll use a special feature of Rails called "migrations", which are a very clean way to evolve a database over time (something we'll talk about a lot this semester!). Recall from the previous step that the "generate" script created two files in the db/migrate directory. Each of them is there to define the database structure for the table holding object instances of their respective class.
We attached a "filled-in" copy of each file at the bottom of this page, for you to look at. right here:
- [wiki:001_create_tracks.rb]
- [wiki:002_create_playlists.rb] Copy the ruby text from those versions of the files to overwrite the skeletons in your itunes/db/migrate directory.
#!html
<div style="color: blue">
(NOTE: the homework originally had the singular form of the controller names in the preceding box. But <a href="http://slash7.com/articles/2005/11/17/rails-howto-pluralizing">Rails convention is to use the plural for controllers</a>, so we changed it (2:11PM Thursday).
</div>
The structure ("schema") of these tables comes from iTunes. (If you're curious, peek at an iTunes Library.xml file on somebody's computer!) Read through these files and see if they make sense to you. They define Ruby classes that inherit from a Rails class called ActiveRecord::Migration. Each class defines an "up" method to change the database (in this case to create a table), and a "down" method to undo the change. This makes it relatively easy to try evolving the database, and then change your mind and roll back your changes without forgetting what you did. VERY useful in practice! (The syntax for creating and modifying tables here is Ruby-ish, and a little different than the SQL we'll learn in class. It's sort of annoying that there's another syntax to learn here, but that's the way it is.)
What's cool here is that Rails will do two things with these files:
- auto-generate tables in the database for tracks and playlists
- auto-generate Ruby methods in the Table and Playlist classes to manipulate the table columns as attributes. E.g. if we have a Track object t, we'll be able to say "t.artist = 'Miles Davis'", and that will automagically update the database record for that object!
-
Now that we have Ruby classes and database tables for both Tracks and Playlists, we need to inform Ruby of the relationship between them. To do this, we'll modify the Playlists method. Open up app/models/playlist.rb and change it to look as follows:
class Playlist < ActiveRecord::Base has_many :tracks end
This tells Ruby that each Playlist object is related to many Track objects. (Note the use of the plural "tracks" here .. this is Rails trying to keep things more readable by automatically dealing with plurals.)
The cool thing about this is that there's now a method of Playlist objects called "tracks" that returns an array of Track objects! We won't use this in this homework, but this is a first introduction to the kinds of relationships that one can have between databases entities, something that we will revisit later in the semester.
- The next step is to write some "controllers" that do interesting things with the objects. Controllers are distinguished from Models as follows. Model files hold class definitions for basic object manipulation. Controllers contain the algorithms and logic for tasks that we want to accomplish with one or more objects. For example, if we wanted a way to assign a track to a playlist, we might put the code for that in a controller. (Rails uses the Model-View-Controller software design pattern for websites; read more at the link if you're curious.)
As with models, there are Rails scripts that auto-generate the controller files and associated things:
% script/generate controller tracks
...
% script/generate controller playlists
...
%
#!html
<div style="color: blue">
(NOTE: the homework originally had the singular form of the controller names in the preceding box. But <a href="http://slash7.com/articles/2005/11/17/rails-howto-pluralizing">Rails convention is to use the plural for controllers</a>, so we changed it (2:11PM Thursday).
</div>
Notice the files that are auto-generated. Rather than write our own controllers for this assignment, we're going to use some default controllers from Rails called "scaffolds." To do this, you simply modify the controller files as follows:
-
app/controllers/tracks_controller.rb
class TracksController < ApplicationController scaffold :track end
-
app/controllers/playlists_controller.rb
class PlaylistsController < ApplicationController
scaffold :playlist
end
The scaffolds reference the models we defined previously, and under the covers define a bunch of methods to view and edit objects in the model.
-
It's time to tell Rails how to connect to our database. This is configured in the file config/database.yml. On the EECS instructional machines, edit your file to look like this:
development: adapter: postgresql database: hw0 username: <%= ENV[ 'USER' ] %> port: <%= ENV[ 'PGPORT' ] %> password: host: rhombus.cs.berkeley.edu
test: <<: *login database: hw0_test
production: <<: *login database: hw0_prod }}}
Once we have the configuration set up, we run the Rails command to apply all migration files to the database: {{{ % rake db:migrate If all went well you'll get some messages about CREATE TABLE, the SQL command that did the work in the database.
-
Fire it up! At this point, you have a fully-prepared, database-backed web application ready to go. To fire it up, type:
% server => Booting WEBrick... => Rails application started on http://0.0.0.0:16157 => Ctrl-C to shutdown server; call with --help for options
Notice the output on your screen. It should contain a URL with a port number (in the example above it was 16157). Now using a web browser, open the URL !http://rhombus.cs.berkeley.edu:16157/track !http://rhombus.cs.berkeley.edu:16157/tracks, replacing the "16157" with the port number you saw on the screen (your $WEBPORT environment variable).
There you see a crude web page with the columns from the track table, and a link that says "New track". Click the link -- it's letting you add data to the database. Play around with the links, and enter in some data. Do the same with the playlists at !http://rhombus.cs.berkeley.edu:16157/track !http://rhombus.cs.berkeley.edu:16157/playlists, again replacing "16157" with your webport. If you can't think of any data to enter, have a look at somebody's iTunes Music Library.xml file, which provided our format.
All during this process, Rails has been writing out a log file with (among other things) all the SQL statements it issued to Postgres. It's very helpful to see the SQL being generated as you click around on your site. To do this, open a new terminal window and type
% tail -f log/development.log | grep SQL
Click around your data and have a look at the output!
-
To get credit for Homework 0, you have to send us evidence of your development log. To generate the file to turn in, run the following command:
% tail -1000 log/development.log | grep SQL > ~/hw0.txt -
Finally, some questions. You don't have to answer these correctly for "credit" on this assignment, but you have to try in order to get credit.
- Question 1: Why do you think Rails is generating queries that try to count up the number of rows in a table ("SELECT count(*)...") ?
- Question 2: Can you name a problem with the migrations we defined? Hint: think about the relationship between tracks and playlists.
-
Turnin instructions: You should create the "hw0.txt" file, as indicated above. Also, create an empty "answers.txt" file, where you can write your answers to the questions above. Do your best to answer them, but it's OK if you don't know the answers -- submit something anyhow. Submit the homework by Friday, 08/31/2007, 10pm. To do so, login to your instructional account, create a directory called "hw0", copy both files in it, cd to "hw0" and submit as follows:
% cd hw0 % submit hw0
During the course of the semester, we will assume that you'll use the instructional computers for all assignments, and our instructions will be targeted at that. But all of the software we'll be using is open source, and you are encouraged to set up this stuff on your own computer. In fact, it's a pretty useful experience to do that. However, we will not be able to support you to turn in assignments generated on machines other than the instructional machines; they will need to be running on the EECS instructional machines so our autograders can access them. There is always some kind of configuration problem when you move an application to another machine, so if you choose to work on a different machine, leave yourself plenty of time to get things running smoothly on the EECS machines before the deadline.