Data Persistence

Larger applications need to store data and retrieve it later
Different options:
- Text files are simple, but become quickly inefficient as data becomes more complex or too large
- DBM hashes provide faster access, and are easy to use, but limited as data grows more complex
- Relational databases provide high performance even with complex queries, but are more complicated
You should try to abstract code that stores and retrieves data so rest of code is not aware of how data is stored

Text Files

Perl is very good at parsing text
Text files work well for small amounts of data, and are very common for CGI scripts
A common issue with text files is file locking

Locking

Problem:
- Web servers support multiple connections
- Two users could be trying to write to the same file at the same time from within the same script
- As a result data is usually corrupted
Solutions:
- flock
- Manual lock files

flock

flock is easiest way of locking files
Offers two modes of locking:
- exclusive
- shared
Exclusive locks prevent other processes from writing to the file or reading from it while it is modified
Shared locks should be used when multiple processes read from a file, but no process is writing to it
There can be multiple processes reading from a file, but only one process writing to it at a time, ever

flock (Syntax)

Call flock with a handle to an open file, and indicating which kind of lock you want

Use Fctnl module to obtain constants for the types of locks

use Fcntl qw( :flock );

open FILE, "some/file" ||
	die $!;		# $! contains the error message
flock FILE, LOCK_EX;	# exclusive lock
flock FILE, LOCK_SH;	# shared lock
flock FILE, LOCK_UN;	# release lock

flock (Example)

use Fcntl qw( :flock );

open FILE, ">>messages.log";
flock FILE, LOCK_EX;	# if the lock is currently
	#  held, this will wait for it to be released
print FILE, "$message\n";
flock FILE, LOCK_UN;	# release lock (unblocking
	# other processes that were waiting)

Manual Locking

A simpler alternative to that described in the book

my $EEXIST = 17;  # file exists

sub lockFile {
  my $alias = shift;
  my $n = 0;
  $lock = "$alias.lock";
  while (mkdir($lock, 0555) == 0) {
    $! == $EEXIST || 
      &AbortScript("can't make $lock: $!\n");  
    $n++ < 30 || 
      &AbortScript("timed out waiting for $lock\n");
    sleep(1);
  }
}

It make use of the fact that mkdir atomically tests whether the directory exists, and only creates it if it doesn't

DBM

DBM is a simple file-based Unix database
Allows a hash to be associated with a database in a file
```
dbmopen(%db, "some/database", 0666);
dmbclose(%db);
```

The hash (%db) is then used to access and modify the database

$db{$key}             # retrieve
$db{$key} = $value;   # store

The exists and delete operators also work just as for a hash
Two files are created on disk: .pag and .dir

DBM (Example)

Lookup in a simple email database

use CGI;
use CGI::Carp;

dbmopen(%db, "email_db", 0666) ||
  die "could not open email_db";

my $q = new CGI;
print $q->header("text/html");

my $user = $q->param("user");
if (exists $db{$user}) {
  print "The email address for user $user is: ";
  print $db{$user}, "\n";
} else {
  print "User $user not found\n";
}

dbmclose(%db);

DBM (Example)

Updating emails

use CGI;
use CGI::Carp;

dbmopen(%db, "email_db", 0666) ||
  die "could not open email_db";

my $q = new CGI;
print $q->header("text/html");

my $user = $q->param("user");
my $email = $q->param("email");

$db{$user} = $email;
print "The following record has been added:<br>\n";
print "$user => $email\n";	

dbmclose(%db);

Concurrent Access

The above example did not deal with concurrent read/writes to the database

One way to control concurrent access it by using the manual locking technique described earlier

sub requestLock {
  my $alias = shift;
  my $n = 0;
  my $lock = $alias . ".lock";
  while (mkdir($lock, 0555) == 0) {
    $! == $EEXIST || 
      die "can't make $lock: $!";  
    $n++ < 30 || 
      die "timed out waiting for $lock";
    sleep(1);
  }
}

sub releaseLock {
  my $alias = shift;
  my $lock = $alias . ".lock";
  rmdir($lock);
}

Concurrent Access (Example)

Updating emails (Revised)

use CGI;
use CGI::Carp;

# include definitions of
# requestLock and releaseLock here

dbmopen(%db, "email_db", 0666) ||
  die "could not open email_db";

requestLock("email_db");

my $q = new CGI;
print $q->header("text/html");

my $user = $q->param("user");
my $email = $q->param("email");

$db{$user} = $email;
print "The following record has been added:<br>\n";
print "$user => $email\n";	

releaseLock("email_db");

dbmclose(%db);

Records with Multiple Fields

DBM on its own only supports a key-value data format
That is sufficient for many applications
However, often you need to store more complex structures
We can treat each value stored in a DBM file as several "fields"
Fields delimited by a delimiter string (e.g. ":")

Encoding and Decoding Data

The following subroutines encode and decode a hash to/from a string of delimiter-separated values

my $FieldSeparator = ":";

sub encode {
  my $valueRef = shift;
  my %value = %$valueRef;
  my @value = %value;    # (key1 val1 key2 val2 ...)
  my $value = join($FieldSeparator, @value);
  return $value;
}

sub decode {
  my $value = shift;
  my @value = split($FieldSeparator, $value);
  my %value = @value;
  return %value;
}

Encoding and Decoding Data (Example)

Database of profile data

Store data:

my %profile = (
  name => "Bob",
  email => "bob\@home.com",
  age => 32);

dbmopen(%db, "profiles", 0666);

# encoded data
# email:[email protected]:name:Bob:age:32

my $encoded = encode(\%profile);
my $key = $profile{name};

$db{$key, $encoded};  # store in database

dbmclose(%db);

Retrieve data:

dbmopen(%db, "profiles", 0666);

my $key = "Bob";
my $decoded = $db{$key};

# decoded data
# (name => "Bob", email => "bob\@home.com", age => 32);

my %decoded = decode($encoded);

dbmclose(%db);

Relational Databases

In a relational database data is organized in tables
Each table consists of rows and columns

SQL

Database vendors have standardized on a query language (SQL) to access and manipulate databases
Some of these providers are:
- Oracle
- Informix
- Sybase
- PostgreSQL
- MySQL
- mSQL
The latter three are open-source projects and, similarly to Linux, are being widely adopted in industry
Given this widespread support, code to access and manipulate a database can be ported quickly in SQL

Creating a Database

To create a database, you need to define the format or schema for the data to be stored in the database
Suppose we have the above profile data, the SQL code to create a database for it is:
```
CREATE TABLE address (
  name 	    CHAR(20),
  location  CHAR(30),
  age       INTEGER
)
```
CREATE TABLE creates a table
This table has three columns: name, location, and age
The column fields are defined to be:
- name: a string of 20 characters
- location: a string of 30 characters
- age: an integer
There are several basic data types (including strings of variable length, dates, and floating-point numbers)

Inserting Data

INSERT populates a database table with data

INSERT INTO address 
  (name, location, age)
VALUES
  ('Bob', 'Ottawa', 33)

It is generally useful to specify the names of the columns before the VALUES keyword

Accessing Data

SQL is a rich language for retrieving data from a database
Retrieve all table rows:
```
SELECT * 
    FROM address
```
Retrieve some columns within each row:
```
SELECT name, location 
    FROM address
```
Retrieve all rows which meet certain conditions:
```
SELECT *
    FROM address
    WHERE age > 30
```

More Complex Retrieval Queries

Boolean expressions as conditions:

SELECT name
    FROM address
    WHERE location = 'Ottawa'
    AND age > 30

Wildcard string matches:

SELECT *
    FROM address
    WHERE name LIKE 'B%'

This will match any names starting with 'B'

Updating Data

When modifying data using UPDATE, a WHERE clause indicates which rows should be changed
```
UPDATE address 
    SET age = age + 1
    WHERE name = 'Alice'
    AND location = 'Toronto'
```

Deleting Data

DELETE removes rows from the database

DELETE FROM address 
    WHERE name = 'Alice'

References

Reference guide on SQL-92