Course outline

• About databases

• Text based ("flat file") databases

• Relational databases

• Tables and relationships

• Structured Query Language (SQL)

• MySQL and other database servers

• Features of MySQL

• Getting MySQL

• Setting up MySQL databases

• The MySQL client

• The DBI and DBD modules

• What is DBI?

• DBI syntax

• DBI exercises

• Extended exercises

• References (optional topic)

Assumed knowledge

It is assumed that you know and understand the following topics:

• Unix - logging in, creating and editing files

• Perl - variable types, operators and functions, conditional constructs, subroutines, basic regular expressions

• Basic database theory - tables, records, fields

If you need help with editing files under Unix, a cheat-sheet is available in Appendix A and an editor command summary in Appendix B. The Unix operating system commands you will need are mentioned and explained very briefly throughout the course - please feel free to ask if you need more help. The required Perl knowledge was covered in Netizen's "Introduction to Perl" training module. Some of the material taught in "Intermediate Perl" is also useful to this module.

Module objectives

• Understand what a database is and use correct terminology to describe types of databases and parts of databases

• Understand and use flat file or textual databases with Perl

• Understand the advantages and limitations of flat file or textual databases and relational databases

• Understand and use Structured Query Language (SQL) to manipulate data in a relational database

• Know about MySQL and other relational databases suitable for small to medium applications

• Use the MySQL command line client to perform SQL queries

• Understand and use Perl's DBI module to interact with databases

• Use the skills and knowledge learnt in this module to create a sample application

Platform and version details

This module is taught using Unix or a Unix-like operating system. Most of what is learnt will work equally well on Windows NT or other operating systems; your instructor will inform you throughout the course of any areas which differ.

All Netizen's Perl training courses use Perl 5, the most recent major release of the Perl language. Perl 5 differs signficantly from previous versions of Perl, so you will need a Perl 5 interpreter to use what you have learnt. However, older Perl programs should work fine under Perl 5.

The database server used during this module is MySQL, available from http://www.mysql.com. We have chosen it because it is free for most purposes, runs on many platforms, is the most common database used by ISPs offering database services to web hosting clients, and has a good feature set for our purposes. However, all the Perl code examples given in this module will work equally well with any of a number of database systems, including PostgreSQL, Oracle, Sybase, and Informix.

The course notes

These course notes contain material which will guide you through the topics listed above, as well as appendices containing other useful information.

The following typographic conventions are used in these notes:

System commands appear in this typeface

Literal text which you should type in to the command line or editor appears as monospaced font.

Keystrokes which you should type appear like this: ENTER. Combinations of keys appear like this: CTRL-D

Program listings and other literal listings of what appears on the
screen appear in a monospaced font like this.

Parts of commands or other literal text which should be replaced by your own specific values appears like this

Other materials

In addition to these notes, you should have a copy of the required text book for this course: Programming Perl (2nd ed.) by Schwartz, Wall, and Christiansen (published by O'Reilly and Associates) -- more commonly referred to as "the Camel book". The Camel book will be used throughout the day, and will be a valuable reference to take home and keep next to your computer.

You will also have received a floppy disk containing these notes in HTML form (with working links to external resources etc) and all the example scripts and data used in this course.

Lastly, you will have been given a nametag with your name and company on the front, and a username and password on the back.

In this chapter...

This chapter talks about databases in general, and the different types of databases which can be used with Perl.

What is a database?

• A database is a collection of related information.

• The data stored in a database is persistent.

Types of databases

There are many different types of databases, including:

• Flat-file text databases

• Associative flat-file databases such as Berkeley DB

• Relational databases

• Object databases

• Network databases

• Hierarchical databases such as LDAP

Relational databases are by far the most useful type commonly available, and this training module focusses largely on them, after looking briefly at flat file text databases.

Database management systems

A database management system (DBMS) is a collection of software which can be used to create, maintain and work with databases. A client/server database system is one in which the database is stored and managed by a database server, and client software is used to request information from the server or to send commands to the server.

Uses of databases

Databases are commonly used to store bodies of data which are too large to be managed on paper or through simple spreadsheets. Most businesses use databases for accounts, inventory, personnel, and other record keeping. Databases are also becoming more widely used by home users for address books, cd collections, recipe archives, etc. There are very few fields in which databases cannot be used.

Chapter summary

• A database is a collection of related information.

• Data stored in a database is persistent

• There are a number of different types of databases, including flat file, relational, and others

• Database management systems are collections of software used to manage databases

• Databases are widely used in many fields

In this chapter...

In this chapter we investigate text-based or "flat file" databases and how to use Perl to manipulate them. We also discuss some of the limitations of this database format.

Delimited text files

A delimited text file is one in which each line of text is a record, and the fields are separated by a known character.

The character used to delimit the data varies according to the type of data. Common delimiters include the tab character (\t in Perl) or various punctuation characters. The delimiter should always be one which does not appear in the data.

Delimited text files are easily produced by most desktop spreadsheet and database applications (eg Microsoft Excel, Microsoft Access). You can usually choose "File" then "Save As" or "Export", then select the type of file you would like to save as.

Imagine a file which contains peoples' given names, surnames, and ages, delimited by the pipe (|) symbol:

Fred|Flintstone|40
Wilma|Flintstone|36
Barney|Rubble|38
Betty|Rubble|34
Homer|Simpson|45
Marge|Simpson|39
Bart|Simpson|11
Lisa|Simpson|9

The file above is available in your exercises directory as delimited.txt.

#!/usr/bin/perl -w

use strict;

open (INPUT, "delimited.txt") or die "Can't open data file: $!";

while (<INPUT>) {
        chomp;                  # remove newline
        my @fields = split(/\|/, $_);
        print "$fields[1], $fields[0]: $fields[2]\n";
}

close INPUT;

Flintstone, Fred: 40
Flintstone, Wilma: 36
...

#!/usr/bin/perl -w

use strict;

# Find out what record the user wants:

print "Search for: ";
chomp (my $search_string = <STDIN>);

open (INPUT, "delimited.txt") or die "Can't open data file: $!";

while (<INPUT>) {
        chomp;                  # remove newline
        my @fields = split(/\|/, $_);

        # test whether the search string matches given or family name
        if ($fields[0] =~ /$search_string/ 
                or $fields[1] =~ /$search_string/) {
                print "$fields[1], $fields[0]: $fields[2]\n";
        }
}

close INPUT;

#!/usr/bin/perl -w

use strict;

open (INPUT, "delimited.txt") or die "Can't open data file: $!";

my @records = sort <INPUT>;

foreach (@records) {
        chomp;                  # remove newline
        my @fields = split(/\|/, $_);
        print "$fields[1], $fields[0]: $fields[2]\n";
}

close INPUT;

#!/usr/bin/perl -w

use strict;

open (INPUT, "delimited.txt") or die "Can't open data file: $!";

while (<INPUT>) {
        chomp;
        my @this_record = split(/\|/, $_);
        
        # build a list-of-lists containing references to each record
        push (@records, \@this_record);
}

# sort takes an optional argument of what subroutine to use to sort
# the data...

my @sorted = sort given_name_order @records;

foreach $record (@sorted) {
        # we have to print the items via a reference to the array...
        print "$record->[1], $record->[0]: $record->[2]\n";
}

# subroutine to implement sorting order
sub given_name_order {
        $a->[0] cmp $b->[0];
}

#!/usr/bin/perl -w

use strict;

open OUTPUT, ">>delimited.txt" or die "Can't open output file: $!";

my @record = qw(George Jetson 35);

print OUTPUT join("|", @record), "\n";

Comma-separated variable (CSV) files

Comma separated variable files are another format commonly produced by spreadsheet and database programs. CSV files delimit their fields with commas, and wrap textual data in quotation marks, allowing the textual data to contain commas if required:

"Fred","Flintstone",40
"Wilma","Flintstone",36
"Barney","Rubble",38
"Betty","Rubble",34
"Homer","Simpson",45
"Marge","Simpson",39
"Bart","Simpson",11
"Lisa","Simpson",9

CSV files are harder to parse than ordinary delimited text files. The best way to parse them is to use the Text::ParseWords module:

#!/usr/bin/perl -w

use strict;
use Text::ParseWords;

open INPUT, "csv.txt" or die "Can't open input file: $!";

while (<INPUT>) {
	my @fields = quotewords("," 0, $_);
}

The three arguments to the quotewords() routine are:

• The delimiter to use

• Whether to keep any backslashes that appear in the data (zero for no, one for yes)

• A list of lines to parse (in our case, one line at a time)

Problems with flat file databases

Chapter summary

• The two main types of text database use either delimited text or comma separated variables to store data

• Delimited text can be read using Perl's split function and written using the join function

• Comma separated files are most easily read using the Text::ParseWords module

• There are several problems with flat file databases including locking, efficiency, and difficulties in handling more complex data

In this chapter...

The first section of this training session focuses on database theory, and covers relational database systems, and SQL - the language used to talk to them.

Tables and relationships

In a relational database, data is stored in tables. Each table contains data about a particular type of entity (either physical or conceptual).

For instance, our sample database is the inventory and sales system for Acme Widget Co. It has tables containing data for the following entities:

Tables in a database contain fields and records. Each record describes one entity. Each field describes a single item of data for that entity. You can think of it like a spreadsheet, with the rows being the records and the columns being the fields, thus:

Every table must have a primary key, which is a field which uniquely identifies the record. In the example above, the Stock ID number is the primary key.

The following figures show the tables used in our database, along with their field names and primary keys (in bold type).

Structured Query Language

SQL is a semi-English-like language used to manipulate relational databases. It is based on an ANSI standard, though very few SQL implementations actually adhere to the standard.

SQL statements are mostly case insensitive these days. While most books and references use upper-case, these notes use lower-case throughout for readability, and because the likelihood of needing to deal with older databases which only understand upper-case is becoming increasingly slim.

The syntax given in these coursenotes is cut down for simplicity; for full information, consult your database system's documentation. The MySQL documentation is available on our system in /usr/doc/mysql-doc and /usr/doc/mysql-manual, or by pointing your web browser at http://training.netizen.com.au/.

select field1 [, field2, field3] from table1 [, table2]
        where condition
        order by field [desc]

select id, name from customer;
select id, name from customer order by name;
select id, name from customer order by name desc;

select * from customer, sales where customer.id = sales.customer_id

insert into tablename (col_name1, col_name2, col_name3) values (value1, value2, value3);

insert into stock_item (id, description, price, quantity) values (0, 'doodad', 9.95, 12);

delete from tablename where condition

delete from stock_item where quantity = 0;

update tablename set field1 = expression, field2 = expression 
        where condition

update stock_item set quantity = (quantity - 1) where id = 4;

create table tablename (
        column coltype options,
        column coltype options,
        ...
        primary key (colname)
)

create table contactlist (
        id int not null auto_increment, 
        name varchar(30), 
        phone varchar(30), 
        primary key (id)
)

drop table tablename

drop table contactlist

Chapter summary

• A database table contains fields and records of data about one entity

• SQL (Structured Query Language) can be used to manipulate and retrieve data in a database

• A SELECT query may be used to retrieve records which match certain criteria

• An INSERT query may be used to add new records to the database

• A DELETE query may be used to delete records from the database

• An UPDATE query may be used to modify records in the database

• A CREATE query may be used to create new tables in the database

• A DROP query may be used to remove tables from the database

In this chapter...

In this section we examine the popular database MySQL, which is available for free for many platforms. MySQL is just one of many database systems which can be accessed via Perl's DBI module.

MySQL features

Comparisions with other popular DBMSs

Getting MySQL

MySQL can be downloaded from http://www.mysql.com/or mirror sites worldwide. It is also available in packaged binary format for various operating system distributions, including RedHat and Debian linux.

Installation instructions come with the software, but in brief:

Setting up MySQL databases

A tool called mysqladmin is distributed with MySQL. This tool allows the database administrator (DBA) to create, remove, or otherwise manage databases.

More help for this command is available by typing mysqladmin --help from the command line or by reading the MySQL reference manual.

% mysqladmin create inventory
% mysqladmin reload

#
# Table structure for table 'customer'
#
CREATE TABLE customer (
  id int(11) DEFAULT '0' NOT NULL auto_increment,
  name varchar(80),
  address varchar(255),
  suburb varchar(50),
  state char(3),
  postcode char(4),
  PRIMARY KEY (id)
);

#
# Table structure for table 'sales'
#
CREATE TABLE sales (
  id int(11) DEFAULT '0' NOT NULL auto_increment,
  sale_date date,
  customer_id int(11),
  salesperson_id int(11),
  stock_item_id int(11),
  quantity int(11),
  price float(4,2),
  PRIMARY KEY (id)
);

#
# Table structure for table 'salesperson'
#
CREATE TABLE salesperson (
  id int(11) DEFAULT '0' NOT NULL auto_increment,
  name varchar(80),
  PRIMARY KEY (id)
);

#
# Table structure for table 'stock_item'
#
CREATE TABLE stock_item (
  id int(11) DEFAULT '0' NOT NULL auto_increment,
  description varchar(80),
  price float(4,2),
  quantity int(11),
  PRIMARY KEY (id)
);

The MySQL client

To talk to any database server, you will need to use a client of some kind. MySQL comes with a text-based client by default, but there are graphical clients available, as well as ODBC drivers to allow you to interact with a MySQL database from Windows applications such as Microsoft Access.

The command line client can be invoked from the command line with the mysql command. The mysql command takes a database name as a required argument, as well as other optional arguments such as -p, which causes the client to ask for a password for access to the database if access controls have been set up.

You can see all the options available on the command line by typing mysql --help.

% mysql -p databasename
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.21.33b

Type 'help' for help.

mysql>

The MySQL client allows you to type in commands on one or many lines. When you finish a statement, type ; to end, same as for Perl.

To quit the client, type quit or \q.

For a full outline of commands available in the client, type help or \h. It will give you this message:

mysql> \h

MySQL commands:
help    (\h)    Display this text
?       (\h)    Synonym for `help'
clear   (\c)    Clear command
connect (\r)    Reconnect to the server. Optional arguments are db and host
edit    (\e)    Edit command with $EDITOR
exit    (\)     Exit mysql. Same as quit
go      (\g)    Send command to mysql server
print   (\p)    print current command
quit    (\q)    Quit mysql
rehash  (\#)    Rebuild completion hash
status  (\s)    Get status information from the server
use     (\u)    Use another database. Takes database name as argument

Connection id: 1  (Can be used with mysqladmin kill)

Understanding the MySQL client prompts

The prompt that shows when you are using the MySQL client tells you a lot about what's going on.

The normal prompt looks like this:

mysql>

This means it is waiting for you to enter an SQL query.

If you are in the middle of entering an SQL query, it will be waiting for a semi-colon to terminate the query, and will look like this:

    ->

If you have opened a set of quotes but not closed them, you will see one of these prompts:

    '>
    ">

Exercises

1. Connect to a database which has the same name as your login (for instance, train01) by typing mysql -p train01 (the -p flag causes it to ask you for your password, which in this case is the same as your login password). The database you are connecting to is your own personal copy of the Acme Widget Co. inventory and sales database mentioned in the previous section

2. Type show tables to show a list of tables in this database

3. Type describe customer to see a description of the fields in the table customer

4. Type select * from customer to perform a simple SQL query

5. Try selecting fields from other tables. Try both select * and select field1, field2 type queries.

6. Use the where clause to limit which records you select

7. Use the order by clause to change the order in which records are returned

8. Insert a record into the customer table which contains your own name and address details

9. Update the price of widgets in the stock_item table to change their price to $19.95

When developing database applications, it is often useful to keep a client program such as this one open to test queries or check the state of your data. You can open multiple telnet sessions to our training system to do this if you wish.

Chapter summary

• MySQL is one of many database systems which can be used as the back-end to a web site

• MySQL can be downloaded from http://www.mysql.com/ or mirror sites

• The MySQL command line client can be used to interact with MySQL databases

• The MySQL client allows the user to type in SQL queries and prints results to the screen.

In this chapter...

In this section we look at the Perl module which can be used to interact with many database servers: DBI.

What is DBI?

Like the Perl modules discussed in last week's CGI programming course, the DBI and DBD modules are written by Perl people and distributed free via CPAN (the Comprehensive Perl Archive Network).

DBI stands for "Database Interface" while DBD stands for "Database Driver". You need both types of modules, working together, in order to access databases using Perl.

Supported database types

Databases supported by Perl's DBI module include:

• Oracle

• Sybase

• Informix

• MySQL

• Msql

• Ingres

• Postgres

• Xbase

• DB2

• ... and more

How does DBI work?

DBI is a generic interface which acts as a "funnel" between the programmer and multiple databases.

DBI protects you from needing to know the minutiae of connecting to different databases by providing a consistent interface for the programmer. The only thing you need to vary is the connection string, to indicate what sort of database you wish to connect to.

To use DBI, you need to install the DBI module from CPAN, as well as any DBD modules for the databases you use. For instance, to use MySQL you need to install the DBD::Mysql module.

DBI/DBD syntax

The syntax of the database modules is best found by using the perldoc command. perldoc DBI will give you general information applicable to all DBI scripts, while perldoc DBD::yourdatabase will give information specific to your own database. In our case, we use perldoc DBD::mysql.

DBI is an object oriented Perl module, like the Text::Template and Mail::Mailer modules covered in the CGI Programming in Perl training module. This means that when we connect to the database we will be creating an object which is called a "database handle" which refers to a specific session with the database. Thus we can have multiple sessions open at once by creating multiple database handles.

We can also create statement handle objects, which are Perl objects which refer to a previously prepared SQL statement. Once we have a statement handle, we can use it to execute the underlying SQL as often as we want.

Connecting to the database

use DBI;

my $driver = 'mysql';
my $database = 'database_name';         # name of your database here
my $username = undef;                   # your database username
my $password = undef;                   # your database password

# note that username and password should be assigned to if your database
# uses authentication (ie requires you to log in)

# we set up a connection string specific to this database
my $dsn = "DBI:$driver:database=$database";

# make the actual connection - this returns a database handle we can use later
my $dbh = DBI->connect($dsn, $username, $password);

# when you're done (at the end of your script) 
$dbh->disconnect();

Executing an SQL query

# set up an SQL statement
my $sql_statement = "select * from customer";
my $sth = $dbh->prepare($sql_statement) 
	|| die "Could not prepare: " . $dbh->errstr();

# execute it 
$sth->execute() || die "Could not execute: " . $dbh->errstr();

# how many rows did we get?
my $num_rows = $sth->rows();
my $num_fields = $sth->{'NUM_OF_FIELDS'};

# close the sql query, if we don't want it any more.
$sth->finish();

Doing useful things with the data

# get an array full of the next row of data that matches the query
# (the most common, and simplest, case)
while (my @ary = $sth->fetchrow_array()) {
        print "The first field is $ary[0]\n";
}

# get a hash reference instead
# (the more complicated, but more useful, version)
while (my $hashref= $sth->fetchrow_hashref()) {
        print "Name is $hashref->{'name'}\n";
}

# you can also get an arrayref
# (equally complicated and not quite as useful)
while (my $ary_ref = $sth->fetchrow_arrayref()) {
	print "The first field is $ary_ref->[0]\n";
}

An easier way to execute non-SELECT queries

If you wish to execute a query such as INSERT, UPDATE, or DELETE, you may find it easier to use the do() method:

$dbh->do("delete from sales")
    || warn("Can't delete from sales table");

This method returns the number of rows affected, or undef if there is an error.

Quoting special characters in SQL

Sometimes you want to use a value in your SQL which may contain characters which have special behaviour in SQL, such as a percent sign or a quote mark. Luckily, there is a method which can automatically escape all special characters:

my $string = "20% off all stock";
my $clean_string = $dbh->quote($string);

Exercises

1. Use exercises/scripts/easyconnect.pl to connect to your Acme Widget Co. database. You will need to edit some of the lines at the top.

2. Use a while loop to output data a row at a time

3. Check all your statements for indications of failure, and output messages to the user using warn() if any of the steps fail.

Chapter summary

• The DBI module provides a consistent interface to a variety of database systems

• The DBI module can be downloaded from CPAN

• Documentation for the DBI module can be found by typing perldoc DBI

In this chapter...

In the second half of this training module, we will be tying together what we have learnt about SQL and DBI, and creating a simple application for Acme Widget Co. to assist them in inventory management, sales, and billing.

The Acme inventory application

In your exercises/ directory you will find a subdirectory called acme/ which contains the outline of the Acme inventory application which you will build upon for the rest of today.

Listing stock items

The shell of a stock-listing script is available in your exercises/acme/ directory as stocklist.pl.

#!/usr/bin/perl -w
use strict;
use DBI;

my $driver = 'mysql';
my $database = 'trainXX';
my $username = 'trainXX';
my $password = 'your_password_here';

my $dsn = "DBI:$driver:database=$database";
my $dbh = DBI->connect($dsn, $username, $password) 
        || die $DBI::errstr;

my $sql_statement = "select * from stock_item";
my $sth = $dbh->prepare($sql_statement);
$sth->execute() or die ("Can't execute SQL: " . $dbh->errstr());

while (my @ary = $sth->fetchrow_array()) {
        print <<"END";
ID:            $ary[0]
Description:   $ary[1]
Price:         $ary[2]
Quantity:      $ary[3]
END
}

$dbh->disconnect();

1. Fill in the variables indicated ($database, $sql_statement, etc)

2. Test your script from the command line

3. Sort the output in alphabetical order by Description

Adding new stock items

1. Write a script which prompts the user for input, asking for values for description, quantity and price. Remember that the stock item's ID will be automatically filled in by the database, as it is an "auto increment" field.

2. Next, create an SQL query to add a record to the database. Output a message to the user indicating the success (or failure) of the operation. A sample script to get you started is available in exercises/acme/addstock.pl

Entering a sale into the system

1. The program exercises/acme/sale.pl provides an interface which can be used to input data pertinent to the occurence of a sale

2. Write a script which records the sale in the sales table

3. Your script will also have to update the stock_item table to reduce the number of items still in stock.

4. What happens if you try to buy/sell more items than are available? Put in a check to stop this from happening.

Creating sales reports

1. Copy the code from the previous example's script to create a script that asks the user for a salesperson's ID number and a start and end date.

2. Use the script to output a sales report for the chosen salesperson for the period between the two dates.

Searching for stock items

1. Create a script which asks a user for a string to search for in a stock item's description (eg "dynamite").

2. Allow the user to choose either "Full name", "Beginning of name" or "Part of name" as a search type.

3. Create different SQL queries using LIKE to search the data depending on their choices

In this chapter...

This section is included as an optional topic. It is intended for those who have experience in C or other languages which use pointers and references.

Uses for Perl references:

• creating complex data structures, for example multi-dimensional arrays

• passing multiple arrays and hashes to subroutines and functions without them getting smushed together

• creating anonymous data structures

Creating and deferencing

To create a reference to a scalar, array or hash, we prefix its name with a backslash:

my $scalar = "This is a scalar";
my @array  = qw(a b c);
my %hash   = ('sky' => 'blue', 'apple' => 'red', 'grass' => 'green');

my $scalar_ref = \$scalar;
my $array_ref  = \@array;
my $hash_ref   = \%hash;

Note that all references are scalars, because they contain a single item of information - the memory address of the actual data.

Dereferencing (getting at the value that a reference points to) is achieved by prepending the appropriate variable-type punctuation to the name of the reference. For instance, if we have a hash reference $hash_reference we can dereference it by looking for %$hash_reference.

my $new_scalar = $$scalar_ref;
my @new_array  = @$array_ref;
my %new_hash   = %$hash_ref;

In other words, wherever you would normally put a variable name (like new_scalar) you can put a reference variable (like $scalar_ref).

Here's how you access array elements or slices, and hash elements:

print $$array_ref[0];           # prints the first element of the array
                                # referenced by $array_ref
print $$array_ref[0..2];        # prints an array slice
print $$hash_ref{'sky'};        # prints a hash element's value

The other way to access the value that a reference points to is using the "arrow" notation. This notation is usually considered to be better Perl style than the one shown above, which can have precedence problems and is less visually clean.

print $array_ref->[0];
print $hash_ref->{'sky'};

Complex data structures

We can use references to create complex data structures, such as this hash in which the values are arrays rather than scalars. Actually, they are scalars, since the array references are scalars, but they point to arrays.

my @fruits = qw(apple orange pear banana);
my @rodents = qw(mouse rat hamster gerbil rabbit);
my @books = qw(camel llama panther);

my %categories = (
        'fruits'        =>              \@fruits,
        'rodents'       =>              \@rodents,
        'books'         =>              \@books,
);

# to print out "gerbil"...
print $categories->{'rodents'}->[3];

Passing multiple arrays/hashes as arguments

If we were to attempt to pass two arrays together to a function or subroutine, they would be flattened out to form one large array:

mylist(@fruit, @rodents);

# print out all the fruits then all the rodents
sub mylist {
        my @list = @_;
        foreach (@list) {
                print "$_\n";
        }
}

If we want them kept separate, pass references:

myreflist(@fruit, @rodents);

sub myreflist {
        my ($firstref, $secondref) = @_;
        print "First list:\n";
        foreach (@$firstref) {
                print "$_\n";
        }
        print "Second list:\n";
        foreach (@$secondref) {
                print "$_\n";
        }
}

Anonymous data structures

Lastly, references can be used to create anonymous data structures which are destroyed once you're done with them. An anonymous array is created by using square brackets instead of round ones. An anonymous hash uses curly brackets instead of round ones.

# the old two-step way:
my @array = qw(a b c d);
my $array_ref = \@array;

# if we get rid of $array_ref, @array will still hang round using up
# memory.  Here's how we do it without the intermediate step:

my $array_ref = ['a', 'b', 'c', 'd'];

# look, we can still use qw() too...

my $array_ref = [qw(a b c d)];

# more useful yet:

my %transport = (
        'cars'          =>      [qw(toyota ford holden porsche)],
        'planes'        =>      [qw(boeing harrier)],
        'boats'         =>      [qw(clipper skiff dinghy)],
);

Chapter summary

• References may be used to create complex data structures, pass multiple arrays and hashes to subroutines, and to create anonymous data structures

• References are created by prefixing the name of a variable with a backslash

• References are dereferenced by using the name of a reference (including the dollar sign) where we would usually use the alphanumeric name of a variable, or by using the arrow notation.

• References can be included in Perl data structures anywhere you might ordinarily find scalars.

• References to anonymous arrays may be created by initialising an array using square brackets instead of round ones.

• References to anonymous hashes may be created by initialising an hash using curly brackets instead of round ones.

What you've learnt

Now you've completed Netizen's Database Programming with Perl module, you should be confident in your knowledge of the following fields:

• Database terminology, including tables and relationships, fields and records, etc

• Flat file database manipulation including delimited and CSV text files

• Basic SQL queries, including SELECT, INSERT, DELETE, and UPDATE queries

• Features of MySQL, where to get MySQL from, and how to set up MySQL databases

• Using the MySQL command line client to perform SQL queries

• Using Perl's DBI module to interact with databases

• Applying Perl skills from previous training modules to create database applications

Where to now?

To further extend your knowledge of Perl, you may like to:

• Borrow or purchase the books listed in our "Further Reading" section (below)

• Follow some of the URLs given throughout these course notes, especially the ones marked "Readme"

• Install Perl and MySQL (or other database servers) on your home or work computer

• Practice using Perl to interact with databases

• Join a Perl user group such as Perl Mongers

Information about other Netizen courses can be found on Netizen's website. A diagram of Netizen's courses and the careers they can lead to is included with these training materials.

Further reading