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.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
|Fancy Tricks for Changing Numeric Base||May 29, 2016|
|Working with Command Arguments||May 28, 2016|
|Secure Desktops with Qubes: Installation||May 28, 2016|
|CentOS 6.8 Released||May 27, 2016|
|Secure Desktops with Qubes: Introduction||May 27, 2016|
|Chris Birchall's Re-Engineering Legacy Software (Manning Publications)||May 26, 2016|
- Tips for Optimizing Linux Memory Usage
- Working with Command Arguments
- Secure Desktops with Qubes: Introduction
- Secure Desktops with Qubes: Installation
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- CentOS 6.8 Released
- Fancy Tricks for Changing Numeric Base
- The Italian Army Switches to LibreOffice
- Linux Mint 18
- Chris Birchall's Re-Engineering Legacy Software (Manning Publications)
Until recently, IBM’s Power Platform was looked upon as being the system that hosted IBM’s flavor of UNIX and proprietary operating system called IBM i. These servers often are found in medium-size businesses running ERP, CRM and financials for on-premise customers. By enabling the Power platform to run the Linux OS, IBM now has positioned Power to be the platform of choice for those already running Linux that are facing scalability issues, especially customers looking at analytics, big data or cloud computing.
￼Running Linux on IBM’s Power hardware offers some obvious benefits, including improved processing speed and memory bandwidth, inherent security, and simpler deployment and management. But if you look beyond the impressive architecture, you’ll also find an open ecosystem that has given rise to a strong, innovative community, as well as an inventory of system and network management applications that really help leverage the benefits offered by running Linux on Power.Get the Guide