Dynamic Graphics

The above example introduced us to the notion of creating a chart based on data stored on disk. While this is certainly the right idea, storing such data in a text file has its drawbacks. It is more common and more useful to put such data in a relational database.

Creating a chart based on a table in a relational database is not very different from creating one based on a text file. The main difference is with the loop we use to iterate over our input data. In vote.pl, we iterated over each line of votes.txt, turning each line of text into a name,value pair, which we then added to @data. When we retrieve information from a database, the information is already split into name,value pairs for us.

Before we can begin to write db-vote.pl (a database version of vote.pl), we must create a table in our database. As usual, I will use MySQL, a “mostly free” database described in Resources. MySQL's syntax is standard enough for most purposes, and most of the following should work with other databases as well.

Relational databases expect to receive input in SQL, the “structured query language”. SQL is not a programming language—so while we can create all sorts of queries to manipulate data in our table, we must embed those queries within a program written in a full programming language. Perl's DBI (“database interface”) module allows us to embed SQL statements inside our Perl programs.

We can create a new table by issuing the following SQL command:

CREATE TABLE Votes (
         candidate_name VARCHAR(30),
         votes_received BIGINT UNSIGNED
         );

While we could send the above to our database server from within a Perl program, it is more usual to type it directly from within an interactive database client. MySQL comes with an interactive client called mysql which allows you to send queries to the database (and receive responses) without having to embed your statements inside a Perl program.

After you issue the above SQL query, the database server will create a new table, Votes, with two columns. The first column, candidate_name, allows for up to 30 characters. The second column is defined to be a BIGINT UNSIGNED, that is, a large integer. We name this column votes_received.

We will now take a leap of faith and assume that, after the polls close on election night, our database table will magically be filled with appropriate values for each candidate. (In a real application, we would probably design things differently, storing each candidate's name in a second table and perhaps even storing each vote in its own row. We will ignore real-world concerns for the time being, so as to concentrate on how to create a graph with this data.)

Assuming our table has been populated with a list of candidates' names and their votes, how can we rewrite vote.pl so it takes its input from a database? As mentioned above, we will rely on DBI, Perl's database interface, which provides a uniform, object-oriented interface to most popular relational databases. Each database is described in a DBD, or database driver, and is imported automatically when we open a connection.

Opening a connection to the database creates a “database handle” object, traditionally called $dbh. We use this object to create a “statement handle”, traditionally called $sth, with which we send the SQL to the database server. Our query, in this case, is rather simple:

SELECT candidate_name, votes_received
    FROM Votes

When it executes this query, the database server will return a two-column table to the user—in this particular case, the entire contents of the Votes table. Each row of the table corresponds to a line in the text file votes.txt which we saw earlier.

DBI provides us with a number of methods by which to retrieve data from $sth. The most commonly used methods retrieve a row as an array, either in its usual form (using $sth->fetchrow_array) or as a reference (using $sth->fetchrow_arrayref). While the arrayref method is more efficient, beginning Perl programmers often prefer to avoid references, which sometimes confuse them. In both cases, the order of elements in the returned list is determined by the order in which columns were named in the query.

Listing 4.

The rest of db-vote.pl (see Listing 4) continues in almost the same way as vote.pl, pushing the values in each row onto @names and @values, then using those to create @data.

It is generally preferable to put such information in a database, because of the reliability and flexibility offered by relational databases. Remember, though, there is no free lunch: a relational database is inherently much slower than a flat ASCII text file. Moreover, our CGI program opens a connection to the database each time it is invoked, an expensive and time-consuming operation. For these reasons, vote.pl will almost certainly execute faster than db-vote.pl. Whether this is an appropriate trade-off depends on the number of visitors to your site, as well as the nature of your web applications.