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.
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.
|Designing Electronics with Linux||May 22, 2013|
|Dynamic DNS—an Object Lesson in Problem Solving||May 21, 2013|
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
- Linux Systems Administrator
- New Products
- Senior Perl Developer
- Technical Support Rep
- UX Designer
- Designing Electronics with Linux
- Dynamic DNS—an Object Lesson in Problem Solving
- Using Salt Stack and Vagrant for Drupal Development
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Favorite (and easily brute-forced) pw's
41 min 20 sec ago
- Have you tried Boxen? It's a
6 hours 33 min ago
- seo services in india
11 hours 4 min ago
- For KDE install kio-mtp
11 hours 5 min ago
- Evernote is much more...
13 hours 5 min ago
- Reply to comment | Linux Journal
21 hours 50 min ago
- Dynamic DNS
22 hours 24 min ago
- Reply to comment | Linux Journal
23 hours 23 min ago
- Reply to comment | Linux Journal
1 day 13 min ago
- Not free anymore
1 day 4 hours ago
Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- 5-21-13, Prototyping Pi Plate Kit: Philip Kirby
- Next winner announced on 5-27-13!
Free Webinar: Hadoop
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers
Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.
Some of key questions to be discussed are:
- What is the “typical” Hadoop cluster and what should be installed on the different machine types?
- Why should you consider the typical workload patterns when making your hardware decisions?
- Are all microservers created equal for Hadoop deployments?
- How do I plan for expansion if I require more compute, memory, storage or networking?