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:
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/
1. Expand the downloaded file using any compression tool such as Winzip, PKZip or Stuffit Expander.To expand from the command line use:
unzip perl_database.zip
tar xvf perl_database.tar
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.
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, usuallycgi-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 directoryDBCONF
. 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 directoryDBDATA
. You can get the whole path by using the UNIX commandpwd
. 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/";
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:
<input type="text">
in add and edit views on the web-based administration.<textarea></textarea>
<select>
list based on the values specified in a hash array. Described below.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éxico", "GUANAJUATO"=>"Guanajuato", "GUERRERO"=>"Guerrero", "HIDALGO"=>"Hidalgo", "JALISCO"=>"Jalisco", "MEXICO D.F."=>"México D.F.", "MICHOACAN"=>"Michoacán", "MORELOS"=>"Morelos", "NAYARIT"=>"Nayarit", "NUEVO LEON"=>"Nuevo León", "OAXACA"=>"Oaxaca", "PUEBLA"=>"Puebla", "QUERETARO"=>"Querétaro", "SAN LUIS POTOSI"=>"San Luis Potosí", "SINALOA"=>"Sinaloa", "SONORA"=>"Sonora", "TABASCO"=>"Tabasco", "TAMAULIPAS"=>"Tamaulipas", "TLAXCALA"=>"Tlaxcala", "VERACRUZ"=>"Veracruz", "YUCATAN"=>"Yucatá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.
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.
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:
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.
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.
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.
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.
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.
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
.
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:
|
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.
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:
"TABLE"
: name of the table to use. If you require
the table's configuration file, we may use the $table
variable,
otherwise, you may type the name of the table as a string. If you need to
access many tables in the same CGI you won't be able to use values from the
configuration files."FIELDS"
: Array containing the field names of the
table. The configuration file contains an array called @campos
,
containing a list of fields. You may use this one or explicitly include the
field list.Examples:
$db = new Database("TABLE"=>"$table", "FIELDS"=>@campos); @users_fields = ["name","lastname","email","telephone", "state", "notes"]; $db = new Database("TABLE"=>"users", "FIELDS"=>@users_fields);
Used to query a table, very similar to a SELECT SQL statement. Parameters:
"FIELDS"
: Array containing the field names that will
be selected in the query."WHERE"
: Array containing the conditions to be met
in the query. See below for details."ORDER BY"
: String that contains the name of the
sort field. To specify if the records should be sorted in an ascending way,
type the name of the field, a comma and then the keyword ASC, to specify descending
order use DESC.By default, records are sorted in ascending order.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
Inserts a value into the table. Parameters:
"VALUES"
: Array that contains the values to be inserted
in the order in which they where defined by the "FIELDS"
parameter of the new function. "KEY"
: Optional parameter to use as a unique id for
the record. If it is not provided, the time function (number of seconds) will
be used to obtain one. 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")
Updates the records that meet the given conditions in a table. Parameters:
"WHERE"
: Array containing the conditions to be met
in the query. Similar to the "WHERE"
parameter of the
db_select function."SET"
: Array containing "<field>=<value>"
pairs that determines which fields should be updated. Similar to the syntax
used for defining conditions.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%'
Deletes all the records that meet the given conditions in a table. Parameter:
"WHERE"
: Array containing the conditions to be met
in the query. Similar to the "WHERE"
parameter of the
db_select function.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
ID
: key of the record to be displayed in a table.Example:
$db->info(2);
base_url
: if display_sort is true, this is the URL used to
make a link to change the sort field of the table. It supports parameters,
so if it doesn't find a ?, the sort table parameter will be added as ?ORDER_BY=<fieldname>,
otherwise it will be &ORDER_BY=<fieldname>display_sort
: two possible values, true (t) or false (f). When
true, the field name will be a link to change the order by field.border
: HTML table bordercellspacing
: HTML table cellspacingcellpadding
: HTML table cellpaddingid_column
: two possible values, true (t) or false (f). If it
is true, the field containing the key will be included.admin
: two possible values, true (t) or false (f). If it is
true, extra columns will be added for Edit and Delete buttons.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>
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.
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"););
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();
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);
Returns the key (id) of the current record.
Example:
$rs->getKey();
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);
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");
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