Web Application with Database Backend
In the previous chapter you saw how you can evaluate arbitrary Ruby code and use Ruby's loops and conditionals to affect which parts of a template are sent to the client. Given this capability, why not connect to a database and loop through database content.
Project Unix Group and Database
CSCF has configured a postgres database specially for your group project. Each group has a group number (1, 2, 3, 4, or 5). Every individual user in a group is also assigned to a unix group just for this project called "cs445_grN", where N is the group number. (Type groups at the ed lab prompt to see it.) Your group assignment gives you access to a postgres database for your project also named "cs445_grN". Finally, each user in the unix group has privileges to connect to the project database as "user" cs445_N. So, if you're in group 2, then you can connect to your project database with full privileges by typing
elnux> psql445 -U cs445_2 cs445_gr2
Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
cs445_gr2=#
Project Web Site
CSCF has also created a special location where your web application files should be placed. These are located in /courses/cs400/cs445/cs445_grN/www/. If you place an RHTML file, say AAA.rhtml, in this directory, then it is accessible via the URL http://cs445.cs.umass.edu/erb-wrapper/cs445_grN/AAA.rhtml, replacing N with your group number.
Tieing it all together
For demonstration purposes I have added several tables to each of your group project databases. (You can feel free to drop these tables as you begin your project.) The first is one that all of you should create for authentication. It holds usernames and passwords. The remainder are tables containing the fruit data from our first assignment.
CREATE TABLE users (
name VARCHAR(20) PRIMARY KEY,
passwd VARCHAR(100)
);
CREATE TABLE fruits (
id INTEGER PRIMARY KEY,
name VARCHAR(20),
cultivar VARCHAR(100),
season VARCHAR(100)
);
...etc...
The following file allows a client to display entries. This file is located in /courses/cs400/cs445/cs445_grN/www/fruits.rhtml. (Look for it in your group's directory.) When this file is requested, it will display all of the fruits in an HTML table. The script takes parameters "season" and "name" and will filter the display. It also generates links to edit individual records (fruit_edit.rhtml). The edit action requires that the user be authenticated. More on this in a moment.
Look in your groups directory and you'll see something like this:
elnux3> ls -l /courses/cs400/cs445/cs445_gr1/www
total 16
-rw-r----- 1 dkulp cs445_gr1 2320 Mar 13 23:40 common445.rb
-rw-r----- 1 dkulp cs445_gr1 3110 Mar 13 23:51 edit_fruit.rhtml
-rw-r----- 1 dkulp cs445_gr1 1555 Mar 13 23:51 fruits.rhtml
lrwxrwxrwx 1 dkulp cs445_gr1 12 Mar 13 22:42 index.rhtml -> fruits.rhtml
-rw-r----- 1 dkulp cs445_gr1 948 Mar 13 23:17 login.rhtml
Let's try it out using group 1: http://cs445.cs.umass.edu/erb-wrapper/cs445_gr1/
Since no page was named, the default is index.rhtml, which is a soft-link to fruits.rhtml.
That loads the following page:
fruits.rhtml
% require 'common445'
% include Common445
% init_common
% # authenticate # allow unauthenticated users to view
<%= cgi.header('text/html') %>
<HTML><HEAD><TITLE>Fruits</TITLE></HEAD>
<BODY>
...continued below...
First, the 'common445.rb' is "required" (read). This files looks like:
common445.rb
module Common445
# set this to the name of the group database
DBNAME = "cs445_gr1"
require 'cgi'
require 'cgi/session'
require 'rubygems' # this is very slow!
require 'dbi'
# this creates getters for the @ instance variables
attr :cgi
attr :sess
attr :dbh
def init_common(new_sess=false)
@cgi=CGI.new('html3')
...
It loads additional files, defines variables, etc. -- all within a "module". Then, back in 'fruits.rhtml' we "include" the module, which adds all the variables and functions into your local namespace. Then the function 'init_common' is called, which sets up a CGI object, creates a CGI session, and connects to your group project database. A useful function that I provide is "par0", which returns a hash where the first value is returned for a named CGI parameter. E.g. http://acme.com/page.rhtml?foo=bar&one=2&one=3 would result in
par0 = { foo => 'bar', one => '2' }
Since you usually expect and want just a single value for each parameter, this is useful.
The next part of the page runs a query:
fruits.rhtml continued...
% qpar = []
% query = %{SELECT f.oid, f.name, f.cultivar, f.season
% FROM fruits f
% WHERE 1=1
% }
% if par0.has_key?('season')
% query << "AND f.season = ? "
% qpar.push(par0['season'].to_s)
% end
% if par0.has_key?('name')
% query << "AND f.name = ? "
% qpar.push(par0['name'].to_s)
% end
...continued further below
What's happening? qpar is an array to hold the positional parameters. query is a string containing a query. Depending on the parameters provided by the user, the query and the number of parameters in it vary. For example, if the user sends the URL http://.../fruits.rhtml?name=Grape&season=Early, then query will be the string:
SELECT f.oid, f.name, f.cultivar, f.season
FROM fruits f
WHERE 1=1
AND f.season = ?
AND f.name = ?
Moreover, the array qpar will be set to ['Early', 'Grape']. Now, once the query is constructed, it must be executed.
fruits.rhtml continued...
<h1>Fruit Table</h1>
Below is a list of fruits. ...snip...
<table border=1>
<tr><th><th>Name<th>Cultivar<th>Season</tr>
% dbh.prepare(query) do |sth|
% sth.execute(*qpar)
% sth.fetch do |row|
<tr><td><a href="edit_fruit.rhtml?oid=<%=row['oid']%>">Edit</a><td><%=row['name']%><td><%=row
['cultivar']%><td><a href="fruits.rhtml?season=<%=row['season']%>"><%=row['season']%></a></tr>
% end
% end
<tr><td>
<td><form method="GET"><input name="name"><input type="submit" value="Go"></form><td>
<td><form method="GET"><input name="season"><input type="submit" value="Go"></form>
</tr>
</table>
</BODY>
</HTML>
% # In case you've modified the session variables, always place this at the end of script
% sess.close
The code, above, should look familiar. But note that execute(*qpar) is called with a single parameter containing a '*' prefix. This will cause the array to automatically be exploded out into parameters -- one per element in the array.
Note that I create links to edit_fruit.rhtml and construct URLs that are specific to each row by modifying the CGI parameter in the URL. Also note that I use 'oid', which is a special Postgres hidden column that provides a single attribute unique integer key to each row in a table.
Let's take a quick peak at edit_fruit.rhtml:
edit_fruit.rhtml
% require 'common445'
% include Common445
% init_common
% authenticate # you must be logged in to modify data
...etc...
Notice that in this page that the function "authenticate()" is called. This checks to see whether a user cookie is set and if not redirects the browser to a login screen. For this demo I've added a user for group 1 with the username of 'a' and password 'b'. You can set this to anything you wish by modifying your "users" table.
cs445_gr1=> SELECT * FROM users;
name | passwd
------+----------------------------------
a | 92eb5ffee6ae2fec3ad71c777531578f
(1 row)
cs445_gr1=> INSERT INTO users (name,passwd) VALUES ('joe',md5('blow'));
INSERT 46523 1
cs445_gr1=> SELECT * FROM users;
name | passwd
------+----------------------------------
a | 92eb5ffee6ae2fec3ad71c777531578f
joe | 5144abbeb84239f038276f8e36b2251d
(2 rows)
All Done
Study all of the files in your group project directory carefully. Once you've got that figured out you should be on your way to making an amazing application.
