Before considering the Perl Database Programming topic, let’s take a moment do discuss about databases from a general point of view.
A database is a structured collection of data which can be stored in your computer system and can be easily accessed and modified, according with your own purposes.
The most simple database is a flat file database which consists of a number of records, separated by a separator – in the most cases a character.
If the character is the new line “\n”, we say that each record is specified in a single line. A record consists by one or more fields of fixed width, delimited by some character (whitespace, comma, etc).
The main disadvantage of this kind of database – or flat file database – is that it can be accessed sequentially only, and if the file becomes too large it is difficult enough to interrogate it. There are many scripts examples in Perl which allow you to see how you can add, modify, sort or delete data from a flat file.
But if you have a serious application or web site to deal with, I am afraid that not the flat file is the way to consider. From this point of view, we can organize data as:
- relational database as Microsoft Access, Microsoft SQL Server, Oracle, DB2, MySql
- hierarchical structures as XML files
- miscellaneous types, such as Excel files, text files or other formats
A relational database consists of a collection of tables, each table contains records (also called rows), and each record contains a number of fields or columns, that organize values by their type. I will show you a very simple example, for demonstrative purpose only, below. For instance, let’s assume we have a shop database with only 2 tables: a table named Customers and another named Orders. The structure of the table Customers could include the fields: customer id, name, phone, zip code. If this table has 2 records, we have a graphical representation below:
where CustomerId, Name, Phone, and ZipCode represent the field names of our table structure. CustomerId is a primary key for this table, it is supposed to be unique and its value is used to identify a record from this table. Keys are used to create the table indexes, which allow the quick retrieval of data from large tables. As a key, we can choose one or more columns, grouped together into a compound key.
The table Orders has the following structure: CustomerId, DateOrderPlaced, ProductName, Quantity as in the following figure:
||January 23, 2005
||Perl Database Programming
||February 12, 2006
||Perl for Oracle
||August 13, 2005
||Programming the Perl DBI
There is a relationship between the two tables: the value of the field CustomerId in Orders table identify the corespondent record of the Customers table.
Such a database allows four operations to be performed on the data: retrieval, insertion, modification and deletion. In order to request data from a relational database, you must send it a query usually written in a dialect of SQL. The database will return a list of rows, the answer at your query.
In order to use the Perl Database Programming from a Perl script and connect to a specific database, you must install the database software first, which will allow you to create the database files and define the structure of the data in the database. There are commercial and free database systems. Microsoft SQL Server, Oracle, DB2 are some of the most popular commercial database systems, but their cost are very high. Fortunately, there are also some popular free database systems which include PostgreSQL, MySQL, Microsoft SQL Server 2005 Express Edition, which you may consider if it is appropriate for you.
Hierarchical Structures as XML Files
XML files are organized in a different way from the relational databases. The elements in XML files are organized hierarchically: each element contains some other elements, each of which contains other elements, and so on. A simple example of XML file is shown below:
It doesn’t matter what kind of database you have in view, Perl database programming is very easy to do because of Perl portability and last but not least the great number of modules you can use as a interface.
The best way to consider the Perl Database Programming it is by using DBI – the DataBase-Independent Interface module which allows you to connect to a database from a Perl script using a specific database driver (DBD). The architecture of DBI let you use multiple databases from different vendors in the same program, regardless the database system you use.