Perl Database Documentation version 0.5

About Perl Database

This database was programmed back in 1999, when there was no widespread adoption of MySQL, PostgreSQL or any other free database by ISPs, particularly in Mexico. It is not intended to replace a larger relational database, but to provide really simple database services to small applications, for customers that are on a budget but require web-based systems to share their data. It might also serve as an example Perl module with complex file input and output operations.

Perl Database is, as its name implies a Database written in Perl. I wrote this simple database because it can be installed easily on any web server that supports Perl and keeps cost down. If you require a lightweight database to hold a few records with reduced maintenance costs –you will find low price packages from ISPs with support for Perl– this database might be what you need. Features:

Known Issues


Requirements

1. Any computer with a web server already installed that supports Perl CGI, for example Apache.

2. CGI.pm perl module. If it is not already installed, you can download it from: http://stein.cshl.org/WWW/software/CGI/


Installation

1. Expand the downloaded file using any compression tool such as Winzip, PKZip or Stuffit Expander.To expand from the command line use:

2. The distribution contains the following files and directories:

3. You should copy : .pl files and .pm files to the cgi-bin directory in your web server, or any other directory where CGIs can be executed, provided you configured your web server previously. This files should have execute permission. For example:

	cd /Downloads/perl_database
	cp *.pl /Library/WebServer/CGI-Executables/
	cp *.pm /Library/WebServer/CGI-Executables/
	cd /Library/WebServer/CGI-Executables/
	chmod 755 *.pl
	chmod 755 *.pm

The directories DBCONF and DBDATA may reside anywhere in your hard disk.

4. All the files inside DBDATA should have read and write permissions for the user that runs the web server in your system. That user should own the files. In UNIX systems, you can change the permissions with the following commands. Note that the user and group values for the chown command might differ in your installation.

	cd DBDATA
	chmod 644 *.txt
	chown www:admin *.txt

5. Files inside DBCONF should be readable by the user that runs the web server. In UNIX systems, you might use the following commands:

	cd DBCONF
	chmod 444 *.cfg
	chown www:admin *.cfg

6. Copy all the files in the htdocs directory of Perl Database distribution to your www root directory (you may copy the files to any directory that is configured to support html and css files in your web server, but you will need to modify the links to database.css in .pl files if you choose another directory). In Apache, this directory is usually called htdocs.

7. If you want to install the examples, copy .pl files from the examples directory to the directory where you installed the rest of the .pl and .pm files (CGI-Executables, or cgi-bin, typically), and modify permissions to execute (chmod 755 *.pl). The example provided shows how to implement a search page for one of your tables. It uses a table called directorio, which is also included in DBCONF and DBDATA directories as an example. You will also need to copy the file "SearchPage.html" to the www root directory that contains your HTML pages (htdocs directory in Apache). Once you're done, you may access the examples by visiting: http://<server>/SearchPage.html, where <server> is the server name or IP address of your computer.


Configuration

You should change the values of three variables in .pl and .pm files to suit your configuration. Sample values correspond to a Mac OS X installation:

$cgiDir: should contain an absolute reference to the directory where you copied the files, usually cgi-bin. Substitute the value of this variable in the following files: ResultSet.pm, DBdetail.pl, DBchoose.pl, DBlist.pl, DBinterface.pl and DBaccess.pl. It contains no trailing slash. For example:
$cgiDir = "/cgi-bin";

$DBCONFpath: should contain the complete path to the directory DBCONF. You can get the whole path by using the UNIX command pwd. Substitute the value of this variable in the following files: DBdetail.pl, DBchoose.pl, DBlist.pl, DBinterface.pl and DBaccess.pl. Don't forget to include a trailing slash after the directory name. For example:
$DBCONFpath = "/Library/WebServer/CGI-Executables/DBCONF/";

$DBDATApath: should contain the complete path to the directory DBDATA. You can get the whole path by using the UNIX command pwd. Substitute the value of this variable only in Database.pm. Don't forget to include a trailing slash after the directory name. For example:
$DBDATApath = "/Library/WebServer/CGI-Executables/DBDATA/";


About Tables

Creating a new table

1. Using your favorite text editor, create a new file on the DBCONF directory. The name of this file determines the name of your table. The file should be called <tablename>.cfg, where <tablename> is the name of your new table. For example, if you want to create a table called users, create a file called "users.txt".

2. All tables use a key (or id) unique value for referring to a particular record. Add all the fields that you need (there is no established limit) by typing the following lines in the file:

	@campos = ["field1","field2","field3","fieldn"];
	@fields = ("field1","field2","field3","fieldn");
	$table=<tablename>;

The second line is similar to the first but uses parenthesis instead of squared braces. This file will be reduced to one line in future versions of this database. For example:

	@campos = ["name","lastname","email","telephone", "state", "notes"];
	@fields = ("name","lastname","email","telephone", "state", "notes");
	$table=users;

These are the only required lines to define a new table. Additional lines are only used by the web-based administration interface.

3. Optionally add the following line to determine how should each field be displayed in the web-based administration interface.

@display= ("text|textarea|select", "text|textarea|select", "text|textarea|select", "text|textarea|select");

The first value corresponds to field1, the second value to field2, and so on.

For example:

@display= ("text", "text", "text", "text", "select", "textarea");

You should specify one display value for each field. In the example, the first four fields (name, lastname, email and telephone) will use text display value; the second to last field (state) will use a select value. The last field (notes) will use textarea value.There are three display values: text, select and textarea:

Display value select takes values from a hash, that contains values to be stored in the database (dbvalue) and values to be displayed in the HTML based interface (displayvalue). The syntax for the hash array is:

%hash_<fieldname> = ("dbvalue1"=>"displayvalue1",    "dbvalue2"=>"displayvalue2", "dbvaluen"=>"displayvaluen");

where <fieldname> is the name of the field with a select display value.

For example, the following hash array contains Mexican states:

%hash_state = (
   "AGUASCALIENTES"=>"Aguascalientes",
   "BAJA CALIFORNIA"=>"Baja California",
   "BAJA CALIFORNIA SUR"=>"Baja California Sur",
   "CAMPECHE"=>"Campeche",
   "CHIAPAS"=>"Chiapas",
   "CHIHUAHUA"=>"Chihuahua",
   "COAHUILA"=>"Coahuila",
   "COLIMA"=>"Colima",
   "DURANGO"=>"Durango",
   "ESTADO DE MEXICO"=>"Estado de M&eacute;xico",
   "GUANAJUATO"=>"Guanajuato",
   "GUERRERO"=>"Guerrero",
   "HIDALGO"=>"Hidalgo",
   "JALISCO"=>"Jalisco",
   "MEXICO D.F."=>"M&eacute;xico D.F.",
   "MICHOACAN"=>"Michoac&aacute;n",
   "MORELOS"=>"Morelos",
   "NAYARIT"=>"Nayarit",
   "NUEVO LEON"=>"Nuevo Le&oacute;n",
   "OAXACA"=>"Oaxaca",
   "PUEBLA"=>"Puebla",
   "QUERETARO"=>"Quer&eacute;taro",
   "SAN LUIS POTOSI"=>"San Luis Potos&iacute;",
   "SINALOA"=>"Sinaloa",
   "SONORA"=>"Sonora",
   "TABASCO"=>"Tabasco",
   "TAMAULIPAS"=>"Tamaulipas",
   "TLAXCALA"=>"Tlaxcala",
   "VERACRUZ"=>"Veracruz",
   "YUCATAN"=>"Yucat&aacute;n",
   "ZACATECAS"=>"Zacatecas"
   );

You should specify a hash array for each field whose display value is of type select.

4. Change permissions to your new file as explained in the Installation section.

Table data

1. Table data is stored in plain text files. Each record should be in one line, with fields separated by | (pipe) characters. The first field should be the key value; it should be a unique integer, preferably serial. The table fields have no type, so you can type either text or numbers indistinctly. Using your favorite text editor, create a new file on the DBDATA directory. The name of this file should be the same as your table. The file should be called <tablename>.txt, where <tablename> is the name of your table (previously defined in <tablename>.cfg, inside DBCONF. For example, if your table is called users, create a file called "users.txt". If you wish to create an empty file, you could do so by using:

On UNIX based operating systems:

	cd DBDATA
	touch users.txt

On Windows:

	cd DBDATA
	copy nul users.txt

2. If you'd like to fill in some data into your table, you should use the following format:

	key1|field11_value|field12_value|field13_value|field1n_value
	key2|field21_value|field22_value|field23_value|field2n_value
	key1|field31_value|field32_value|field33_value|field3n_value

You SHOULD include a new line after the last record.
It is easier to create this file using a spreadsheet (such as Microsoft Excel, Lotus 1-2-3 or AppleWorks), export it as tab-delimited text and replace tabs with pipes (|).

Example:

	1|Steve|Jobs|steve@apple.com|12345678|CA|Apple's CEO
	2|Bill|Gates|bill@microsoft.com|23456789|WA|Microsoft's Owner
	3|Sam|Palmisano|sam@ibm.com|34567891|NY|IBM's CEO
	4|Larry|Ellison|larry@oracle.com|45678912|CA|Oracle's CEO

3. Change permissions to your new file as explained in the Installation section.

Using table tools

Perl Database includes simple web-based tools to manage tables. Using your favorite browser, visit the following URL:

http://<your_server>/<your_cgi_directory>/DBchoose.pl

DBchoose will display a list of all your tables (as defined in your DBCONF directory) along with controls for common operations. After selecting a table, you can:

Edit a record

Fill in the Key field and click on Edit to update an existing record. You will be presented with a screen with all the fields of this table (DBinterface.pl). Fields that are marked as select in the table configuration file will display a list of possible values. Modify all the fields you want and press Edit. The next screen (DBaccess.pl) will display a message indicating that the operation was successful.

Delete a record

Fill in the Key field and click on Delete to delete an existing record. The next screen (DBaccess.pl) will display a message indicating that the operation was successful.

Add a record

Click on Add Record to create a new record. You will be presented with a screen with all the fields of this table (DBinterface.pl). Fields that were marked as select in the table config file will display a list of possible values. Fill in some or all fields and press Add. The next screen (DBaccess.pl) will display a message indicating that the operation was successful.

View All Records

Click on View All Records to display a list of all the records in a table. You will be presented with a screen (DBlist.pl) that contains a table with all the data stored in the table. You can click on the record's key to display a new page with only that record. At the end of each row you'll find Edit and Delete links, click them to perform one of this operations. At the bottom of the screen, the total number of records is displayed.

Additional Tools

Some additional tools (also freeware) are still in development; if you'd like to try beta versions, send me a message. You will find contact information at my homepage: http://homepage.mac.com/jjgb/. Additional tools will let you perform searches on any table (and display query statement), create tables through a web-based interface, backup your tables and select which fields to view in a query.


Programming

Perl Database has SQL-like commands to perform typical table operations, along with some nifty utilities to ease up your work.

Perl Database consists of two Perl Modules: Database.pm and ResultSet.pm.

Creating your own CGIs

If you'd like to create your own Perl CGI that relies on Perl Database, the following example may be the base for your CGI. It contains sample commands to query the table users:

01 #!/usr/bin/perl
02 
03 ###########################################################
04 $DBCONFpath = "/Library/WebServer/CGI-Executables/DBCONF/";
05 $cgiDir = "/cgi-bin";
06 ###########################################################
07 use CGI;
08 use CGI::Carp qw(fatalsToBrowser);
09 $query = new CGI();
10 print $query->header;
11 $config = $DBCONFpath . "users.cfg";
12 unless(require $config){
13 print "No Configuration File Specified!\n";
14 exit;
15 }
16 
17 print<<HTML;
18 <html>
19 <head>
20 <title>Sample CGI</title>
21 </head>
22 <body>
23 HTML
24 
25 use Database;
26 use ResultSet;
27 $db = new Database("TABLE"=>"$table", "FIELDS"=>@campos);
28 @thefields = ["name", "lastname", "notes"];
29 @conditions = ["notes=CEO"];
30 $rs = new ResultSet($db->db_select("FIELDS"=>@thefields, "WHERE"=>@conditions, "ORDER_BY"=>"lastname,ASC"));
31 print "<table border=\"1\" cellspacing=\"0\" cellpadding=\"5\">\n";
32 while ($rs->nextrs() == 1) {
33 	print "<tr><td>" . $rs->get(1) . "</td><td>" . $rs->get(2) . "</td><td>" . $rs->get(3) . "</td></tr>\n";
34 }
35 print "</table>";
36 print <<END;
37 </body>
38 </html>
39 END

Line by line explanation:

01 Specify language for CGI
03 - 06 Declare global variables for DBCONF directory and cgi-bin directory
07 Use module CGI.pm
08 Send errors to browser (useful for debugging)
09 Define a new instance of CGI
10 Print appropriate header to return HTML
11 Specify table name, by setting the $config variable to the filename of the table configuration file, in this case, the users table.
12 - 15 If the configuration file is not found, exit this CGI
17 - 23 Output HTML required tags.
25 Use module Database.pm
26 Use module ResultSet.pm
27 Define a new instance of the Database module, it's "constructor" takes two parameters:
  • "TABLE": name of the table to use. Since we are importing the table's configuration file, we may use the $table variable.
  • "FIELDS": Array containing the field names of the table. The configuration file contains an array called @campos, containing a list of fields.
28 @thefields is an array with the fields to be retrieved from the table in the select statement, if you'd like to retrieve all fields, use the array @campos instead.
29 @conditions is an array with the conditions to be met in the select statement, more on this later.
30 Queries the database with the command $db->select. This is similar to the SQL statement:
select name, lastname, notes from users where notes like '%CEO%' order by lastname asc
The results of the query are stored in the variable $rs, that is an instance of the module ResultSet.
31 Print the <table> tag to display results.
32 The ResultSet ($rs) contains all the records that match the given conditions. When a ResultSet is given a value, it returns one record at a time. A ResultSet has a pointer to indicate which is the actual record, but the pointer is initialized one position before the first record.
$rs->nextrs() is an instruction that moves the pointer to the next record. It it is successful (the next record exists) the return value is 1 (true). If the record doesn't exist, the return value is 0 (false). This cycle will loop through all the records that match the given conditions.
33 The instruction $rs->get(n) returns field n of the select. In this example, when n=1 ($rs->get(1)) this function will return the field 'name' of the current record, when n=2, it will return the field 'lastname' and n=3 will retrieve the field 'notes'.
35 Closes the HTML table.
36 - 39 Tags for closing the HTML document.

You should now understand the basic mechanics of the Perl Database. For further examples, view the Examples directory. Sample tables and configuration files are also included. The next sections of this manual will focus on describing the functions of the modules Database.pm and ResultSet.pm, with some examples on how to use them.

 


Database module (Database.pm)

new

Used to define a new instance of the Database module, it's similar to a constructor, if you're familiar with C++ or Java (or any other OOP language). It receives two parameters:

Examples:

$db = new Database("TABLE"=>"$table", "FIELDS"=>@campos);
@users_fields = ["name","lastname","email","telephone", "state", "notes"];
$db = new Database("TABLE"=>"users", "FIELDS"=>@users_fields);

db_select

Used to query a table, very similar to a SELECT SQL statement. Parameters:

WHERE parameter

Array of conditions. The conditions are evaluated in the order in which they appear, so you should plan how to create your query so it is optimized.

The first condition takes the form:
"<field>=<value>"

The first condition takes the form:
"<operator>,<field>=<value>"

<field> is the name of any field in the table.
<value> is the string that will be compared against each record to determine if it matches the condition.
<operator> is either AND or OR

Conditions are evaluated in the order in which they appear multiple AND operators will continuosly reduce the number of records to search (considering conditions are met). When an OR operator is found, all the records in the table that match the OR condition will be joined with the previously found records.

All fields are treated as strings; the fields are checked to contain the value ignoring case (in other words, NOT case sensitive), not an exact match.

There are two special fields that you can use:

Examples:

@thefields = ["name", "lastname", "notes"];
@conditions = ["notes=CEO", "OR,id=5"];
$db->db_select("FIELDS"=>@thefields, "WHERE"=>@conditions, "ORDER_BY"=>"lastname");

Similar to:
select name, lastname, notes from users where notes like '%CEO%' or id=5 order by lastname asc

@thefields = ["lastname", "name", "email", "notes", "state"];
@conditions = ["notes=CEO", "AND,email=microsoft", "OR,name=steve",];
$db->db_select("FIELDS"=>@thefields, "WHERE"=>@conditions, "ORDER_BY"=>"name,DESC");

Similar to:
select lastname, name, email, notes, state from users where (notes like '%CEO%' and email like '%microsoft%') OR (name like '%steve%') order by name desc


db_insert

Inserts a value into the table. Parameters:

Example:

@values = ["Clark","Kent","superman\@smallville.com", "55-94-34-12", "CHIHUAHUA", "Superhero"];
$db->db_insert("VALUES"=>@values);

Similar to:
insert into users(name,lastname,email,telephone, state, notes) values("Clark","Kent","superman\@smallville.com", "55-94-34-12", "CHIHUAHUA", "Superhero")


db_update

Updates the records that meet the given conditions in a table. Parameters:

Example:

@conditions = ["notes=CEO"];
@set = ["notes=Chief Executive Officer", "state=CHIHUAHUA"];
$db->db_update("WHERE"=>@conditions, "SET"=>@set);

Similar to:
update users set notes='Chief Executive Officer', state='CHIHUAHUA' where notes like '%CEO%'


db_delete

Deletes all the records that meet the given conditions in a table. Parameter:

Example:

@conditions = ["notes=CEO", "OR,id=5"];
@set = ["notes=Chief Executive Officer", "state=CHIHUAHUA"];
$db->db_delete("WHERE"=>@conditions, "SET"=>@set);

Similar to:
delete from users where notes like '%CEO%' or id=5


info

Returns an HTML table with the record whose id is the parameter id. Parameter:

Example:

$db->info(2);

table_header

Returns the head of a table containing the column (field) names, with optional sort parameters.You may have to tweak this function to suit your needs. Parameters:

Example:

$db->table_header("$cgiDir/DBlist.pl", "t", 1, 2, 2, "t", "f");

It will return: (this will vary depending on your current table)

<table border="1" cellspacing="2" cellpadding="2" align="center" class="listTable">
<tr class="titlerow">
<th>id</th>
<th><a href="/cgi-bin/DBlist.pl?ORDER_BY=name&ORDER=ASC">Name</a></th>
<th><a href="/cgi-bin/DBlist.pl?ORDER_BY=lastname&ORDER=ASC">Lastname</a></th>
<th><a href="/cgi-bin/DBlist.pl?ORDER_BY=notes&ORDER=ASC">Notes</a></th>
</tr>

ResultSet module (ResultSet.pm)

A ResultSet is a table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The nextrs method moves the cursor to the next row, and because it returns 0 when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set. A default ResultSet object has a cursor that moves forward only. Thus, it is possible to iterate through it only once and only from the first row to the last row.

The ResultSet interface provides get and getKey methods for retrieving column (field) values from the current row. Values can be retrieved using the index number of the column. Columns are numbered from 1. Result set columns within each row should be read in left-to-right order.

new

Creates a new ResultSet. A ResultSet is used to store the records returned by the db_select function. It provides easy to use methods for displaying the results in a syntax that may remind you of JDBC methods, but simpler. It receives only one parameter, which is the result of a db_select operation. It could also receive an array where each element is a pipe-delimited record.

 

Examples:

$rs = new ResultSet($db->db_select("FIELDS"=>@thefields, "WHERE"=>@conditions, "ORDER_BY"=>"name,DESC"););

nextrs

Moves the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method nextrs makes the first row the current row; the second call makes the second row the current row, and so on.

If the resultset contains another record beyond the current position of the pointer, this function increases the pointer to the next position and returns 1. Otherwise, it returns 0.

Example:

$rs->nextrs();

get

Returns the value of the designated column in the current row of this ResultSet object.

You should use rs->nextrs() before the first call to rs->get(n), in order to advance to the first record.. This function receives one parameter, the order of the field that should be returned. The first column is 1, the second is 2, ... . It returns the value for that particular field in the current record. To advance to the next record, use the instruction nextrs.

Maybe this will help to make it easier to understand:
If you selected field1, field2, field3 and field4 (in that particular order) with the "FIELDS" array of the command db_select, then by using rs->get(1), you will get the value of field1 for the current record, by using rs->get(2), you will get the value of field2 for the current record, and so on.

Example:

$rs->get(50);

getKey

Returns the key (id) of the current record.

Example:

$rs->getKey();

getFormatted

Similar to get, but instead of returning the raw value of the field, it returns an HTML cell. Check displayResults for further information, since it is used in conjunction with that function.

Example:

$rs->getFormatted(15);

displayResults

This function was created for the web-based administration interface (DBlist.pl). It builds an HTML table with all the results of a query. However it is highly customized, so it's not really useful for direct use in any application, If you'd like to use it, I suggest that you duplicate this function and modify it to suit your needs. Future versions of this database might contain a similar function with parameters for many scenarios. Check out the source code of DBlist.pl if you're curious.

Example:

$rs->displayResults($db->table_header("$base_url", "f", 1, 2, 2, "t"), "admin", "$table");

LICENSE:

Copyright © 2000-2003 Jorge Juan G—mez Basanta.

This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this library; if not, you will find more information at: http://www.opensource.org/licenses/lgpl-license.php


Copyright © 2000-2003 Jorge Juan G—mez Basanta.
The most recent version and complete docs are available at:
http://homepage.mac.com/jjgb/
http://perldatabase.sourceforge.net

 

counter