Perl Tutorials – Chapter 18 – Intro To Perl’s DB

Perl has a number of built-in databases (or databases which can be used via modules) that are freely used to store data. Along with these databases, Perl can also connect to the major databases such as Oracle, MySQL and Excel.

Like everything else in Perl, TIMTYWTDI (There Is More Than One Way To Do It) so if you come from a scripting or programming background and have relational database experience, chances are you’d prefer the power of MySQL or Oracle over the built-in databases already available for you.

Each database comes at a price. The use of MySQL or Oracle can create initial overheard while it loads and connects to the appropriate DBIs to speak with the database. Unless the database is expected to get quite large or the script requires related data or advanced sorting techniques, using a flatfile database such as DB_File might be perform better.

Flatfile databases don’t have the overheard that relational databases do which makes them load faster and require less CPU processing power. These are great if you have only a few sets of data that needs to be saved such as traffic logs, usernames and passwords, dates, etc. There are limitations when it comes to information stored within a flatfile database. SDBM_File allows only a certain number of characters to be stored within a key or value.

If you were scraping web sites for data (let’s say lyrics for example), you’d exceed the character limitations in the database and the data stored would be truncated and thus be inaccurate. The other problem with flatfile databases is the ability (or lacktherof) to do advanced sorting on the data.

Relational databases have larger overhead and are a million fold more powerful than flatfiles while flatfiles process faster and require less code to perform.

In this tutorial we will be taking a brief look at Perl’s built-in database DB_File. These flatfiles are key/value pairs which sounds awfully too similar to the %hash datatype. Because of this, we store the contents of the database into our hash for manipulation or printing.


Calling the DB_File database

DB_File is the module DB_File.pm and is used like the CGI module or strict that you’re familiar with.

Before we can use the database, we need to tell our script to use the database module.

use DB_File;


Connecting to the DB_File database

Unlike relational databases which require usernames and passwords, the only information needed to connect to DB_File is the name of the variable you’re tying to it, the database name and location and the file permissions of the database.

my %db;
my $storage = “counter.db”;

tie %db, “DB_File”, “$storage”, O_CREAT|O_RDWR, 0644,
or die “Cannot open file $storage: $!\n”;

Above, we are declaring %db so we can tie it to the database. The scalar $storage is the database name and location. If there isn’t a file path within the $scalar assignment, it will assume the database is in the same directory as the script that’s running.

Next, we are tieing our variable %db from above TO the database DB_File which is followed by the variable $storage which contains the filename and location of the database. O_CREAT|O_RDWR, 0644, are the file permissions used on the database. Be careful not to give out too many permissions or other people will be able to tamper with your data. 0644 or 0666 is generally as high as you want to go to keep the database relatively secure.

Some, or all, of the parameters can be left out when connecting to DB_File. For example, the following two snippets are identical.

tie %db, “DB_File”, $storage, O_CREAT|O_RDWR, 0666 ;

tie %db, “DB_File”, $storage;

 

Tying variables

When using databases such as DB_File and SDBM_File, variables are tied to the contents of the database. This is to say, when %db is tied to $storage using DB_File, the hash %db contains all the data inside %storage.

Similar to filehandles, data is read from and written to the database using the variable tied variable instead of speaking to it directly. Since the data is stored within our hash variable, it makes data retrieval and manipulation simple.


Using the stored data

The tied variable to the DB_File database is a hash. The hash is no different than any other hash you’ve used within your scripts.

To print out all the contents, a foreach() loop could iterate over all the key/value pairs like so:

foreach my $key (%db)
{
  print “$key => $key{$key}<br>”;
}

Saving the information to the database is identical to the procedure used to add another key/value pair to the original hash. Just create a unique hash key and assign it a value. If the hash key already exists, the previous value will be overwritten with the new information.

$db{“username”} = “password”;

If the database was designed for usernames and passwords, you could check to see whether or not a username (key) exists using an if conditional.

if (exists $db{“username”})
{
  # do this
}


Joining and Splitting

When using flatfile databases associated in key/pair values, we are limited in how much information we can store for each key in order to keep the information grouped together.

The previous example was a username/password combination which would run cleanly. But if you wanted to store more than just one piece of information to the value, joining the data to save and splitting it back into usable variables when you need it is the workaround.

Our scenario: We have an employee database where the key is an employee ID number and the value consists of their name, hourly wage, position and their favorite caffeinated beverage.

%employees = (“100″ => “Fred Flinstone::14.08::scribe::coffee”, “101″ => “Lisa Simpson::23:17::educator::red bull”);

From the hash (assume it’s the database), the value of employee 100 is Fred Flinstone::14.08::scribe::coffee.

All these values are contained within one inseparable string, right? Wrong. When the information is grouped together with a common delimiter the string can be broken up into smaller variables. The most commonly used delimiter is a double colon :: but if the data being stored can contain ::, a different delimiter is required or the data will be broken prematurely.

Take for instance:

$db{“100″} = “Fred Flintstone::14.08::scribe::coffee”;

There are 4 groups of information within the single value which means we’ll create 4 new variables and split() on the common delimiter.

my ($name, $pay, $position, $drink) = split(/::/, $employees{“100″});

The first step is to create meaningful scalar variables which the splitted results will be assigned to. These new variables are assigned (in order they care created!) to the value of the splitted value our hash key 100. Inside split(/ /) is the delimiter in which to separate the data. If two 8s were used as delimiters, it would have looked like

split(/88/,

After the values are extracted into the new variables, they are accessible anywhere throughout the rest of the script.

use DB_File;

my %db;
my $storage = “employees.db”;

tie %db, “DB_File”, $storage;

my %employees = (“100″ => “Fred Flinstone::14.08::scribe::coffee”, “101″ => “Lisa Simpson::23:17::educator::red bull”);

my ($name, $pay, $position, $drink) = split(/::/, $employees{“100″});

print “Their name was $name, their pay was $pay, their position was $position and their favorite drink is $drink”;

Joining information into one storable string is the same idea but is in reverse of the split().

The variable is assigned to the conjoined values of the list of strings or variables at the end of the assignment. Similar to split(), the delimiter to join on is specified before the variables or strings.

my $single_string = join(“::”, $name, $pay, $position, $drink);

print $single_string;

The printout would look something similar to: Superman::1000::superhero::kryptonite. Then to assign the new string back into our database, we’d use the hash assignment.

$employees{“102″} = $single_string;

 

A complete example

#!/usr/bin/perl

use warnings;
use strict;

use CGI qw/:standard/;
use DB_File;

my %db;
my $storage = “employees.db”;

tie %db, “DB_File”, $storage;

my %employees = (“100″ => “Fred Flinstone::14.08::scribe::coffee”, “101″ => “Lisa Simpson::23:17::educator::red bull”);

### example of splitting
my ($name, $pay, $position, $drink) = split(/::/, $employees{“100″});

print “Their name was $name, their pay was $pay, their position was $position and their favorite drink is $drink”;

### example of joining

my $single_string = join(“::”, $name, $pay, $position, $drink);

print $single_string;

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)