1062 lines
40 KiB
HTML
1062 lines
40 KiB
HTML
<!--startcut ==============================================-->
|
|
<!-- *** BEGIN HTML header *** -->
|
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
|
|
<HTML><HEAD>
|
|
<title>Combining Perl and PostgreSQL, Part 2: Procedures with PL/pgSQL LG #69</title>
|
|
</HEAD>
|
|
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#0000AF"
|
|
ALINK="#FF0000">
|
|
<!-- *** END HTML header *** -->
|
|
|
|
<CENTER>
|
|
<A HREF="http://www.linuxgazette.com/">
|
|
<IMG ALT="LINUX GAZETTE" SRC="../gx/lglogo.png"
|
|
WIDTH="600" HEIGHT="124" border="0"></A>
|
|
<BR>
|
|
|
|
<!-- *** BEGIN navbar *** -->
|
|
<IMG ALT="" SRC="../gx/navbar/left.jpg" WIDTH="14" HEIGHT="45" BORDER="0" ALIGN="bottom"><A HREF="mathew.html"><IMG ALT="[ Prev ]" SRC="../gx/navbar/prev.jpg" WIDTH="16" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="index.html"><IMG ALT="[ Table of Contents ]" SRC="../gx/navbar/toc.jpg" WIDTH="220" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><A HREF="../index.html"><IMG ALT="[ Front Page ]" SRC="../gx/navbar/frontpage.jpg" WIDTH="137" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="http://www.linuxgazette.com/cgi-bin/talkback/all.py?site=LG&article=http://www.linuxgazette.com/issue69/nielsen.html"><IMG ALT="[ Talkback ]" SRC="../gx/navbar/talkback.jpg" WIDTH="121" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><A HREF="../faq/index.html"><IMG ALT="[ FAQ ]" SRC="./../gx/navbar/faq.jpg"WIDTH="62" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="okopnik.html"><IMG ALT="[ Next ]" SRC="../gx/navbar/next.jpg" WIDTH="15" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><IMG ALT="" SRC="../gx/navbar/right.jpg" WIDTH="15" HEIGHT="45" ALIGN="bottom">
|
|
<!-- *** END navbar *** -->
|
|
<P>
|
|
</CENTER>
|
|
|
|
<!--endcut ============================================================-->
|
|
|
|
<H4 ALIGN="center">
|
|
"Linux Gazette...<I>making Linux just a little more fun!</I>"
|
|
</H4>
|
|
|
|
<P> <HR> <P>
|
|
<!--===================================================================-->
|
|
|
|
<center>
|
|
<H1><font color="maroon">Combining Perl and PostgreSQL, Part 2: Procedures with PL/pgSQL</font></H1>
|
|
<H4>By <a href="mailto:articles@gnujobs.com">Mark Nielsen</a></H4>
|
|
</center>
|
|
<P> <HR> <P>
|
|
|
|
<!-- END header -->
|
|
|
|
|
|
|
|
|
|
<p>
|
|
<ol>
|
|
<li>
|
|
<a href="#Introduction">Introduction</a></li>
|
|
<li><a href="#perl">Perl script to create tables, procedures,
|
|
backup tables, and sequences.
|
|
</a></li>
|
|
<li><a href="#execute">Executing the Perl script</a></li>
|
|
<li><a href="#consider">Considerations to explore.</a></li>
|
|
<li><a href="#Conclusion">Conclusion</a></li>
|
|
<li><a href="#REF">References</a></li>
|
|
</ol>
|
|
|
|
<h3><a NAME="Introduction"></a>Introduction</h3>
|
|
After dealing with installing PostgreSQL, Perl, and embedding Perl in
|
|
PostgreSQL, I wanted a standard way to create tables, sequences,
|
|
stored procedures, and backup tables. Perhaps other people have nice GUI
|
|
solutions to do this, but I haven't seen any. I would like it if someone
|
|
would work with me to create a GUI interface to achieve what I am doing here.
|
|
My goals are:
|
|
<ol>
|
|
<li> Always backup data no matter what happens. </li>
|
|
<li> Always use stored procedures to insert, update, delete, copy, or
|
|
to do anything that changes data on the tables. One should even create
|
|
stored procedures to select data. </li>
|
|
<li> Have a Perl script create tables, sequences, backups tables, and the
|
|
stored procedures to manipulate the data. </li>
|
|
<li>Have the stored procedures clean data using Perl. </li>
|
|
<li>Backup data if someone runs the Perl script on a live system.</li>
|
|
<li> There should be an active column in the table so that you can specify
|
|
active or inactive rows in the table. We create a view which views
|
|
active rows of a table. </li>
|
|
<li> Stored procedures should record date created and date last updated.
|
|
</li>
|
|
<li>All rows have a unique id. Even if we choose not to use them,
|
|
they will still have them. It is not always good to use oid to get
|
|
unique rows. </li>
|
|
<li> Be able to delete inactive rows
|
|
with a purge procedure. The delete procedure
|
|
just makes it inactive. Also, unpurge data with the latest purged
|
|
data for a unique id. This
|
|
is cool.</li>
|
|
<li> All negative numbers returned from pl/sql procedures are considered
|
|
failures. All positive numbers (including 0) are considered to be
|
|
successes in the fact nothing errored out. They are either 0, in which
|
|
nothing happened, or something greater than 0 which indicates the
|
|
number of items affected or a id number. </li>
|
|
</ol>
|
|
My future goals include:
|
|
<ol>
|
|
<li> Creating a GUI interface. Preferrably one that is not dependent on GNOME
|
|
or KDE libraries but Python. You can create Python binaries easily, so I
|
|
would prefer Python/TK.</li>
|
|
<li>Allow the GUI design to make changes to live tables by either:
|
|
<ul>
|
|
<li>Making updates that really happen with full effects. Some changes
|
|
con't allow all options (at least in the past). </li>
|
|
<li>Creating a new table, and dumping all the data from the old table
|
|
into the new one while locking the old table. </li>
|
|
</ul>
|
|
</li>
|
|
<li>Record all database changes to review history.</li>
|
|
</ol>
|
|
|
|
<h3><a NAME="perl"></a>Perl script to create tables, procedures,
|
|
backup tables, and sequences.</h3>
|
|
Here is the Perl script I use. You can also get a copy here
|
|
<a href="misc/nielsen/Create_Functions.pl.txt">Create_Functions.pl.txt</a>.
|
|
<pre>
|
|
#!/usr/bin/perl
|
|
|
|
# Create Functions for Perl/PostgreSQL version 0.1
|
|
|
|
# Copyright 2001, Mark Nielsen
|
|
# All rights reserved.
|
|
# This Copyright notice was copied and modified from the Perl
|
|
# Copyright notice.
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of either:
|
|
|
|
# a) the GNU General Public License as published by the Free
|
|
# Software Foundation; either version 1, or (at your option) any
|
|
# later version, or
|
|
|
|
# b) the "Artistic License" which comes with this Kit.
|
|
|
|
# This program 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 either
|
|
# the GNU General Public License or the Artistic License for more details.
|
|
|
|
# You should have received a copy of the Artistic License with this
|
|
# Kit, in the file named "Artistic". If not, I'll be glad to provide one.
|
|
|
|
# You should also have received a copy of the GNU General Public License
|
|
# along with this program in the file named "Copying". If not, write to the
|
|
# Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
|
|
# 02111-1307, USA or visit their web page on the internet at
|
|
# http://www.gnu.org/copyleft/gpl.html.
|
|
|
|
use strict;
|
|
|
|
### We want to define some variables WHICH YOU SHOULD CHANGE FOR YOUR
|
|
### OWN COMPUTER.
|
|
my $Home = "/tmp/testdir";
|
|
my $File = "$Home/Tables.txt";
|
|
my $Template = "$Home/Generic.fun";
|
|
my $Custom = "$Home/Custom.sql";
|
|
my $Database = "testdatabase";
|
|
|
|
#------------------------------------------------------------------------
|
|
|
|
my @List = @ARGV;
|
|
|
|
## Let us create the two directories we need if they are not there.
|
|
if (!(-e "$Home/Tables")) {system "mkdir -p $Home/Tables"}
|
|
if (!(-e "$Home/Backups")) {system "mkdir -p $Home/Backups"}
|
|
|
|
### Open up the template for the functions and the file that contains
|
|
### the info to create the tables.
|
|
open(FILE,$Template); my @Template = <FILE>; close FILE;
|
|
open(FILE,$File); my @File = <FILE>; close FILE;
|
|
open(FILE,$Custom); my @Custom = <FILE>; close FILE;
|
|
|
|
### Filter out lines that have no numbers or letters.
|
|
@File = grep($_ =~ /[a-z0-9]/i, @File);
|
|
### Get rid of any line which contains a #
|
|
@File = grep(!($_ =~ /\#/), @File);
|
|
### Get rid of the newline.
|
|
grep(chomp $_, @File);
|
|
### Get rid of tabs and replace with spaces. .
|
|
grep($_ =~ s/\t/ /g, @File);
|
|
### Convert all multiple spaces to one.
|
|
grep($_ =~ s/ +/ /g, @File);
|
|
### Next two lines get rid of spaces and front and end.
|
|
grep($_ =~ s/^ //g, @File);
|
|
grep($_ =~ s/ $//g, @File);
|
|
### Delete any commas at the end, we will put them back on later.
|
|
grep($_ =~ s/\,$//g, @File);
|
|
|
|
my $Tables = {};
|
|
my $TableName = "";
|
|
### For each line in the file, either make a new array for the table,
|
|
### or store the lines in the array for a table.
|
|
foreach my $Line (@File)
|
|
{
|
|
my $Junk = "";
|
|
### If the line starts with "TABLENAME" then create a new array.
|
|
if ($Line =~ /^TABLENAME/)
|
|
{
|
|
($Junk,$TableName, $Junk) = split(/ /,$Line);
|
|
### This creates the aray for the table.
|
|
$Tables->{$TableName} = [];
|
|
}
|
|
else
|
|
{
|
|
### Storing lines for the table.
|
|
push (@{$Tables->{$TableName}}, $Line) ;
|
|
}
|
|
}
|
|
|
|
### If we listed specific tables, then only do those.
|
|
if (@List)
|
|
{
|
|
foreach my $TableName (sort keys %$Tables)
|
|
{ if (!(grep($_ eq $TableName, @List))) {delete $Tables->{$TableName};} }
|
|
}
|
|
|
|
### Get the keys of the reference to an array $Tables
|
|
### and get the data for that array, create our file, and then use the file.
|
|
foreach my $TableName (sort keys %$Tables)
|
|
{
|
|
my @Temp = @{$Tables->{$TableName}};
|
|
|
|
my $Backup_Columns = ""; my $Backup_Values = ""; my $Update_Fields = "";
|
|
my $Field_Copy_Values = ""; my $FieldTypes = "";
|
|
my $CleanVariables = ""; my $RemakeVariables = "";
|
|
### The two tables are different in one respect, the backup table
|
|
### does not require uniqueness and it doesn't use a sequence.
|
|
my $Table = qq($TableName\_id int4 NOT NULL UNIQUE DEFAULT nextval('$TableName\_sequence'),
|
|
date_updated timestamp NOT NULL default CURRENT_TIMESTAMP,
|
|
date_created timestamp NOT NULL default CURRENT_TIMESTAMP,
|
|
active int2 CHECK (active in (0,1)) DEFAULT 0,
|
|
);
|
|
## I should allow null for the id instead of 0, but since the sequence
|
|
### starts at 1, I use 0 as null. I hate nulls.
|
|
my $Table_Backup = qq(backup_id int4 NOT NULL UNIQUE DEFAULT nextval('$TableName\_sequence_backup'),
|
|
$TableName\_id int4 NOT NULL DEFAULT 0,
|
|
date_updated timestamp NOT NULL default CURRENT_TIMESTAMP,
|
|
date_created timestamp NOT NULL default CURRENT_TIMESTAMP,
|
|
active int2 CHECK (active in (0,1)) DEFAULT 0,
|
|
);
|
|
|
|
print "Creating functions for table '$TableName'\n";
|
|
my $No = 1;
|
|
### For each line for this table do this.
|
|
### We want to create a few variables that are going to be placed into
|
|
### the template.
|
|
foreach my $Line (@Temp)
|
|
{
|
|
$Table .= "$Line,\n";
|
|
$Table_Backup .= "$Line,\n";
|
|
my ($Name,$Type,$Ext) = split(/ /,$Line,3);
|
|
### The backup columns
|
|
$Backup_Columns .= ", $Name";
|
|
### The update fields
|
|
$No++; $Update_Fields .= ", $Name = var_$No";
|
|
### Backup values
|
|
$Backup_Values .= ", record_backup.$Name";
|
|
### Now the fields when we copy stuff in the cyop function.
|
|
$Field_Copy_Values .= ", clean_text(record2.$Name)";
|
|
### Now the field types for the update function.
|
|
$FieldTypes .= ", $Type";
|
|
### We need to define the variables for the updating function.
|
|
$CleanVariables .= " var_$No $Type;\n";
|
|
### We need to define the type, I only check for text and int4 for now.
|
|
my $Temp = "\$$No";
|
|
if ($Type eq "int4") {$Temp = "clean_numeric($Temp)";}
|
|
elsif ($Type eq "text") {$Temp = "clean_text($Temp)";}
|
|
### Now we need to set the variables.
|
|
$RemakeVariables .= " var_$No := $Temp;\n";
|
|
|
|
### We also need to add the function to clean out he variables before
|
|
### they are submitted.
|
|
}
|
|
### Record how many rows we had. Make a line for the update command for
|
|
### testing.
|
|
my $Number_Of_Rows = $No;
|
|
my $Update_Test = "1";
|
|
for (my $i = 1; $i < $Number_Of_Rows - 1; $i++) {$Update_Test .= ",$i";}
|
|
|
|
### We need to chop off the last comma.
|
|
chomp $Table; chop $Table; chomp $Table_Backup; chop $Table_Backup;
|
|
### Now let us setup dropping and creating of the table and backup table.
|
|
my $Tables = qq(drop table $TableName;\ncreate table $TableName (\n$Table\n););
|
|
$Tables .= "drop table $TableName\_backup;\n";
|
|
$Tables .= "create table $TableName\_backup (\n$Table_Backup, error_code text NOT NULL DEFAULT ''\n);\n";
|
|
### Let us create a view for active stuff in our table.
|
|
$Tables .= "drop view $TableName\_active;\n";
|
|
$Tables .= "create view $TableName\_active as select * from $TableName
|
|
where active = 1;\n";
|
|
### Create a view for inactive or deleted items.
|
|
$Tables .= "drop view $TableName\_deleted;\n";
|
|
$Tables .= "create view $TableName\_deleted as select * from $TableName
|
|
where active = 0;\n";
|
|
### Create a view for a list of unique backup ids.
|
|
$Tables .= "drop view $TableName\_backup_ids;\n";
|
|
$Tables .= "create view $TableName\_backup_ids as
|
|
select distinct $TableName\_id from $TableName\_backup;\n";
|
|
### Create a list of purged data (lastest data per id).
|
|
$Tables .= "drop view $TableName\_purged;\n";
|
|
$Tables .= "create view $TableName\_purged as
|
|
select * from $TableName\_backup where oid = ANY (
|
|
select max(oid) from $TableName\_backup where $TableName\_id = ANY
|
|
(
|
|
select distinct $TableName\_id from $TableName\_backup
|
|
where $TableName\_backup.error_code = 'purge'
|
|
and NOT $TableName\_id = ANY (select $TableName\_id from $TableName)
|
|
)
|
|
group by $TableName\_id
|
|
)
|
|
;\n";
|
|
|
|
### I use grep commands to search and replace stuff for arrays.
|
|
### I could use map, but I like greps.
|
|
my @Temp = @Template;
|
|
### now add the custom sql commands.
|
|
push (@Temp,@Custom);
|
|
|
|
grep($_ =~ s/TABLENAME/$TableName/g, @Temp);
|
|
grep($_ =~ s/BACKUPCOLUMNS/$Backup_Columns/g, @Temp);
|
|
grep($_ =~ s/BACKUPVALUES/$Backup_Values/g, @Temp);
|
|
grep($_ =~ s/UPDATEFIELDS/$Update_Fields/g, @Temp);
|
|
grep($_ =~ s/COPYFIELDS/$Field_Copy_Values/g, @Temp);
|
|
grep($_ =~ s/FIELDS/$FieldTypes/g, @Temp);
|
|
grep($_ =~ s/HOME/$Home/g, @Temp);
|
|
grep($_ =~ s/CLEANVARIABLES/$CleanVariables/g, @Temp);
|
|
grep($_ =~ s/REMAKEVARIABLES/$RemakeVariables/g, @Temp);
|
|
|
|
### Now move the stuff from the array @Temp to @Template_Copy.
|
|
my @Template_Copy = @Temp;
|
|
|
|
### Now we save the file. We won't delete it (unless you run this script
|
|
### again) so that we can figure out what was done.
|
|
open(FILE,">$Home/Tables/$TableName\.table_functions");
|
|
### Create the sequence for the table .
|
|
print FILE "drop sequence $TableName\_sequence;\n";
|
|
print FILE "create sequence $TableName\_sequence;\n";
|
|
print FILE "drop sequence $TableName\_sequence_backup;\n";
|
|
print FILE "create sequence $TableName\_sequence_backup;\n";
|
|
### Print out the table and backup table.
|
|
print FILE $Tables;
|
|
### Print out the 4 functions, insert, delete, update, and copy.
|
|
foreach my $Temp (@Template_Copy) {print FILE "$Temp";}
|
|
|
|
close FILE;
|
|
|
|
### Before we execute, let us backup the table in case some novice
|
|
### executes this on a live server.
|
|
my $Backup_File = "$Home/Backups/$TableName\_0.backup";
|
|
my $No = 0;
|
|
while (-e $Backup_File)
|
|
{$No++; $Backup_File = "$Home/Backups/$TableName\_$No\.backup";}
|
|
### Now we have the filename to store the backup, execute it.
|
|
system ("pg_dump -t $TableName -f $Backup_File $Database");
|
|
|
|
### Uncomment this option if you want to see what is in the file.
|
|
## system ("cat $Home/Tables/$TableName\.table_functions");
|
|
|
|
### Drop table and functions, create table and functions and backup table.
|
|
system ("psql -d $Database -c '\\i $Home/Tables/$TableName\.table_functions'");
|
|
print "Check the file\n $Home/Tables/$TableName\.table_functions.\n";
|
|
|
|
}
|
|
|
|
|
|
|
|
</pre>
|
|
Rename the perl script "Create_Functions.pl.txt". Here are the things
|
|
needed to get it to work:
|
|
<ul>
|
|
<li>You must change the options above the dotted line. </li>
|
|
<li> You must have PostgreSQL installed with Perl embedded in it.
|
|
<li>Enter the command "psql template1". Then type "create database
|
|
testdatabase;" or whatever you named your database. Press enter. If you have
|
|
errors along the way, you haven't set up the permissions yet. Login in as
|
|
root, then execute "su -l postgres". Then type "createuser" and press
|
|
enter. This will create a user in your postgresql database. Enter in the
|
|
username and give the username full privledges. Then try again
|
|
with your normal account. </li>
|
|
</ul>
|
|
|
|
<h3><a NAME="execute"></a>Executing the Perl script </h3>
|
|
You will need some more files. The <a href="misc/nielsen/Tables.txt">Tables.txt</a> file.
|
|
<pre>
|
|
|
|
TABLENAME contact
|
|
question_id int4 NOT NULL DEFAULT 0
|
|
company_name text NOT NULL default ''
|
|
first text NOT NULL default ''
|
|
middle text NOT NULL default ''
|
|
last text NOT NULL default ''
|
|
email text NOT NULL default ''
|
|
work_phone text NOT NULL default ''
|
|
home_phone text NOT NULL default ''
|
|
address_1 text NOT NULL default '',
|
|
address_2 text NOT NULL default ''
|
|
city text NOT NULL default ''
|
|
state text NOT NULL default ''
|
|
zip text NOT NULL default ''
|
|
|
|
TABLENAME account
|
|
username text NOT NULL DEFAULT '',
|
|
password text not NULL DEFAULT '',
|
|
|
|
TABLENAME contact_lists
|
|
account_id int4 not null default 0,
|
|
contact_id int4 not null default 0,
|
|
</pre>
|
|
You can use my file as an example, but I suggest to modify it for your
|
|
own needs. It is simulated to make three tables. One containing userame
|
|
and passwords, and the other associating a username to a list of
|
|
contacts. Another file you will need is <a href="misc/nielsen/Generic.fun">Generic.fun</a>
|
|
<pre>
|
|
--- Generic Functions for Perl/Postgresql version 0.1
|
|
|
|
--- Copyright 2001, Mark Nielsen
|
|
--- All rights reserved.
|
|
--- This Copyright notice was copied and modified from the Perl
|
|
--- Copyright notice.
|
|
--- This program is free software; you can redistribute it and/or modify
|
|
--- it under the terms of either:
|
|
|
|
--- a) the GNU General Public License as published by the Free
|
|
--- Software Foundation; either version 1, or (at your option) any
|
|
--- later version, or
|
|
|
|
--- b) the "Artistic License" which comes with this Kit.
|
|
|
|
--- This program 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 either
|
|
--- the GNU General Public License or the Artistic License for more details.
|
|
|
|
--- You should have received a copy of the Artistic License with this
|
|
--- Kit, in the file named "Artistic". If not, I'll be glad to provide one.
|
|
|
|
--- You should also have received a copy of the GNU General Public License
|
|
--- along with this program in the file named "Copying". If not, write to the
|
|
--- Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
|
|
--- 02111-1307, USA or visit their web page on the internet at
|
|
--- http://www.gnu.org/copyleft/gpl.html.
|
|
|
|
-- create a method to unpurge just one item.
|
|
-- create a method to purge one item.
|
|
-- \i HOME/TABLENAME.table
|
|
---------------------------------------------------------------------
|
|
|
|
drop function sql_TABLENAME_insert ();
|
|
CREATE FUNCTION sql_TABLENAME_insert () RETURNS int4 AS '
|
|
DECLARE
|
|
record1 record; oid1 int4; id int4 :=0; record_backup RECORD;
|
|
BEGIN
|
|
insert into TABLENAME (date_updated, date_created, active)
|
|
values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1);
|
|
-- Get the unique oid of the row just inserted.
|
|
GET DIAGNOSTICS oid1 = RESULT_OID;
|
|
-- Get the TABLENAME id.
|
|
FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where oid = oid1
|
|
LOOP
|
|
id := record1.TABLENAME_id;
|
|
END LOOP;
|
|
|
|
-- If id is NULL, insert failed or something is wrong.
|
|
IF id is NULL THEN return (-1); END IF;
|
|
-- It should also be greater than 0, otherwise something is wrong.
|
|
IF id < 1 THEN return (-2); END IF;
|
|
|
|
-- Now backup the data.
|
|
FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
|
|
LOOP
|
|
insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
|
|
active, error_code)
|
|
values (id, record_backup.date_updated, record_backup.date_created,
|
|
record_backup.active, ''insert'');
|
|
END LOOP;
|
|
|
|
-- Everything has passed, return id as TABLENAME_id.
|
|
return (id);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
---------------------------------------------------------------------
|
|
|
|
drop function sql_TABLENAME_delete (int4);
|
|
CREATE FUNCTION sql_TABLENAME_delete (int4) RETURNS int2 AS '
|
|
DECLARE
|
|
id int4 := 0;
|
|
id_exists int4 := 0;
|
|
record1 RECORD;
|
|
record_backup RECORD;
|
|
return_int4 int4 :=0;
|
|
|
|
BEGIN
|
|
-- If the id is not greater than 0, return error.
|
|
id := clean_numeric($1);
|
|
IF id < 1 THEN return -1; END IF;
|
|
|
|
-- If we find the id, set active = 0.
|
|
FOR record1 IN SELECT TABLENAME_id FROM TABLENAME
|
|
where TABLENAME_id = id
|
|
LOOP
|
|
update TABLENAME set active=0, date_updated = CURRENT_TIMESTAMP
|
|
where TABLENAME_id = id;
|
|
GET DIAGNOSTICS return_int4 = ROW_COUNT;
|
|
id_exists := 1;
|
|
END LOOP;
|
|
|
|
-- If we did not find the id, abort and return -2.
|
|
IF id_exists = 0 THEN return (-2); END IF;
|
|
|
|
FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
|
|
LOOP
|
|
insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
|
|
active BACKUPCOLUMNS ,error_code)
|
|
values (record_backup.TABLENAME_id, record_backup.date_updated,
|
|
record_backup.date_updated, record_backup.active
|
|
BACKUPVALUES , ''delete''
|
|
);
|
|
END LOOP;
|
|
|
|
-- If id_exists == 0, Return error.
|
|
-- It means it never existed.
|
|
IF id_exists = 0 THEN return (-1); END IF;
|
|
|
|
-- We got this far, it must be true, return ROW_COUNT.
|
|
return (return_int4);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
|
|
---------------------------------------------------------------------
|
|
drop function sql_TABLENAME_update (int4 FIELDS);
|
|
CREATE FUNCTION sql_TABLENAME_update (int4 FIELDS)
|
|
RETURNS int2 AS '
|
|
DECLARE
|
|
id int4 := 0;
|
|
id_exists int4 := 0;
|
|
record_update RECORD; record_backup RECORD;
|
|
return_int4 int4 :=0;
|
|
CLEANVARIABLES
|
|
BEGIN
|
|
REMAKEVARIABLES
|
|
-- If the id is not greater than 0, return error.
|
|
id := clean_numeric($1);
|
|
IF id < 1 THEN return -1; END IF;
|
|
|
|
FOR record_update IN SELECT TABLENAME_id FROM TABLENAME
|
|
where TABLENAME_id = id
|
|
LOOP
|
|
id_exists := 1;
|
|
END LOOP;
|
|
|
|
IF id_exists = 0 THEN return (-2); END IF;
|
|
|
|
update TABLENAME set date_updated = CURRENT_TIMESTAMP
|
|
UPDATEFIELDS
|
|
where TABLENAME_id = id;
|
|
GET DIAGNOSTICS return_int4 = ROW_COUNT;
|
|
|
|
FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = id
|
|
LOOP
|
|
insert into TABLENAME_backup (TABLENAME_id,
|
|
date_updated, date_created, active
|
|
BACKUPCOLUMNS, error_code)
|
|
values (record_update.TABLENAME_id, record_backup.date_updated,
|
|
record_backup.date_updated, record_backup.active
|
|
BACKUPVALUES, ''update''
|
|
);
|
|
END LOOP;
|
|
|
|
-- We got this far, it must be true, return ROW_COUNT.
|
|
return (return_int4);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
---------------------------------------------------------------------
|
|
|
|
drop function sql_TABLENAME_copy (int4);
|
|
CREATE FUNCTION sql_TABLENAME_copy (int4)
|
|
RETURNS int2 AS '
|
|
DECLARE
|
|
id int4 := 0;
|
|
id_exists int4 := 0;
|
|
record1 RECORD; record2 RECORD; record3 RECORD;
|
|
return_int4 int4 := 0;
|
|
id_new int4 := 0;
|
|
TABLENAME_new int4 :=0;
|
|
BEGIN
|
|
-- If the id is not greater than 0, return error.
|
|
id := clean_numeric($1);
|
|
IF id < 1 THEN return -1; END IF;
|
|
|
|
FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where TABLENAME_id = id
|
|
LOOP
|
|
id_exists := 1;
|
|
END LOOP;
|
|
IF id_exists = 0 THEN return (-2); END IF;
|
|
|
|
--- Get the new id
|
|
FOR record1 IN SELECT sql_TABLENAME_insert() as TABLENAME_insert
|
|
LOOP
|
|
TABLENAME_new := record1.TABLENAME_insert;
|
|
END LOOP;
|
|
-- If the TABLENAME_new is not greater than 0, return error.
|
|
IF TABLENAME_new < 1 THEN return -3; END IF;
|
|
|
|
FOR record2 IN SELECT * FROM TABLENAME where TABLENAME_id = id
|
|
LOOP
|
|
|
|
FOR record1 IN SELECT sql_TABLENAME_update(TABLENAME_new COPYFIELDS)
|
|
as TABLENAME_insert
|
|
LOOP
|
|
-- execute some arbitrary command just to get it to pass.
|
|
id_exists := 1;
|
|
END LOOP;
|
|
END LOOP;
|
|
|
|
-- We got this far, it must be true, return new id.
|
|
return (TABLENAME_new);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
|
|
------------------------------------------------------------------
|
|
drop function sql_TABLENAME_purge ();
|
|
CREATE FUNCTION sql_TABLENAME_purge () RETURNS int4 AS '
|
|
DECLARE
|
|
record_backup RECORD; oid1 int4 := 0;
|
|
return_int4 int4 :=0;
|
|
deleted int4 := 0;
|
|
delete_count int4 :=0;
|
|
delete_id int4;
|
|
|
|
BEGIN
|
|
|
|
-- Now delete one by one.
|
|
FOR record_backup IN SELECT * FROM TABLENAME where active = 0
|
|
LOOP
|
|
-- Record the id we want to delete.
|
|
delete_id = record_backup.TABLENAME_id;
|
|
|
|
insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
|
|
active BACKUPCOLUMNS ,error_code)
|
|
values (record_backup.TABLENAME_id, record_backup.date_updated,
|
|
record_backup.date_updated, record_backup.active
|
|
BACKUPVALUES , ''purge''
|
|
);
|
|
|
|
-- Get the unique oid of the row just inserted.
|
|
GET DIAGNOSTICS oid1 = RESULT_OID;
|
|
|
|
-- If oid1 less than 1, return -1
|
|
IF oid1 < 1 THEN return (-2); END IF;
|
|
-- Now delete this from the main table.
|
|
delete from TABLENAME where TABLENAME_id = delete_id;
|
|
|
|
-- Get row count of row just deleted, should be 1.
|
|
GET DIAGNOSTICS deleted = ROW_COUNT;
|
|
-- If deleted less than 1, return -3
|
|
IF deleted < 1 THEN return (-3); END IF;
|
|
delete_count := delete_count + 1;
|
|
|
|
END LOOP;
|
|
|
|
-- We got this far, it must be true, return the number of ones we had.
|
|
return (delete_count);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
|
|
------------------------------------------------------------------
|
|
drop function sql_TABLENAME_purgeone (int4);
|
|
CREATE FUNCTION sql_TABLENAME_purgeone (int4) RETURNS int4 AS '
|
|
DECLARE
|
|
record_backup RECORD; oid1 int4 := 0;
|
|
record1 RECORD;
|
|
return_int4 int4 :=0;
|
|
deleted int4 := 0;
|
|
delete_count int4 :=0;
|
|
delete_id int4;
|
|
purged_no int4 := 0;
|
|
|
|
BEGIN
|
|
|
|
delete_id := $1;
|
|
-- If purged_id less than 1, return -4
|
|
IF delete_id < 1 THEN return (-4); END IF;
|
|
|
|
FOR record1 IN SELECT * FROM TABLENAME
|
|
where active = 0 and TABLENAME_id = delete_id
|
|
LOOP
|
|
purged_no := purged_no + 1;
|
|
END LOOP;
|
|
|
|
-- If purged_no less than 1, return -1
|
|
IF purged_no < 1 THEN return (-1); END IF;
|
|
|
|
-- Now delete one by one.
|
|
FOR record_backup IN SELECT * FROM TABLENAME where TABLENAME_id = delete_id
|
|
LOOP
|
|
|
|
insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
|
|
active BACKUPCOLUMNS ,error_code)
|
|
values (record_backup.TABLENAME_id, record_backup.date_updated,
|
|
record_backup.date_updated, record_backup.active
|
|
BACKUPVALUES , ''purgeone''
|
|
);
|
|
|
|
-- Get the unique oid of the row just inserted.
|
|
GET DIAGNOSTICS oid1 = RESULT_OID;
|
|
|
|
-- If oid1 less than 1, return -2
|
|
IF oid1 < 1 THEN return (-2); END IF;
|
|
-- Now delete this from the main table.
|
|
delete from TABLENAME where TABLENAME_id = delete_id;
|
|
|
|
-- Get row count of row just deleted, should be 1.
|
|
GET DIAGNOSTICS deleted = ROW_COUNT;
|
|
-- If deleted less than 1, return -3
|
|
IF deleted < 1 THEN return (-3); END IF;
|
|
delete_count := delete_count + 1;
|
|
|
|
END LOOP;
|
|
|
|
-- We got this far, it must be true, return the number of ones we had.
|
|
return (delete_count);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
|
|
------------------------------------------------------------------------
|
|
drop function sql_TABLENAME_unpurge ();
|
|
CREATE FUNCTION sql_TABLENAME_unpurge () RETURNS int2 AS '
|
|
DECLARE
|
|
record1 RECORD;
|
|
record2 RECORD;
|
|
record_backup RECORD;
|
|
purged_id int4 := 0;
|
|
purge_count int4 :=0;
|
|
timestamp1 timestamp;
|
|
purged_no int4 := 0;
|
|
oid1 int4 := 0;
|
|
oid_found int4 := 0;
|
|
highest_oid int4 := 0;
|
|
|
|
BEGIN
|
|
|
|
-- Now get the unique ids that were purged.
|
|
FOR record1 IN select distinct TABLENAME_id from TABLENAME_backup
|
|
where TABLENAME_backup.error_code = ''purge''
|
|
and NOT TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)
|
|
LOOP
|
|
|
|
purged_id := record1.TABLENAME_id;
|
|
timestamp1 := CURRENT_TIMESTAMP;
|
|
purged_no := purged_no + 1;
|
|
oid_found := 0;
|
|
highest_oid := 0;
|
|
|
|
-- Now we have the unique id, find its latest date.
|
|
|
|
FOR record2 IN select max(oid) from TABLENAME_backup
|
|
where TABLENAME_id = purged_id and error_code = ''purge''
|
|
LOOP
|
|
-- record we got the date and also record the highest date.
|
|
oid_found := 1;
|
|
highest_oid := record2.max;
|
|
END LOOP;
|
|
|
|
-- If the oid_found is 0, return error.
|
|
IF oid_found = 0 THEN return (-3); END IF;
|
|
|
|
-- Now we have the latest date, get the values and insert them.
|
|
FOR record_backup IN select * from TABLENAME_backup
|
|
where oid = highest_oid
|
|
LOOP
|
|
|
|
insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
|
|
active BACKUPCOLUMNS ,error_code)
|
|
values (purged_id, record_backup.date_updated,
|
|
timestamp1, record_backup.active
|
|
BACKUPVALUES , ''unpurge''
|
|
);
|
|
|
|
-- Get the unique oid of the row just inserted.
|
|
GET DIAGNOSTICS oid1 = RESULT_OID;
|
|
-- If oid1 less than 1, return -1
|
|
IF oid1 < 1 THEN return (-1); END IF;
|
|
|
|
insert into TABLENAME (TABLENAME_id, date_updated, date_created,
|
|
active BACKUPCOLUMNS)
|
|
values (purged_id, timestamp1,
|
|
timestamp1, record_backup.active
|
|
BACKUPVALUES );
|
|
-- Get the unique oid of the row just inserted.
|
|
GET DIAGNOSTICS oid1 = RESULT_OID;
|
|
-- If oid1 less than 1, return -2
|
|
IF oid1 < 1 THEN return (-2); END IF;
|
|
|
|
END LOOP;
|
|
|
|
END LOOP;
|
|
|
|
-- We got this far, it must be true, return how many were affected.
|
|
return (purged_no);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
|
|
---------------------------------------------------------------------
|
|
drop function sql_TABLENAME_unpurgeone (int4);
|
|
CREATE FUNCTION sql_TABLENAME_unpurgeone (int4) RETURNS int2 AS '
|
|
DECLARE
|
|
record_id int4;
|
|
record1 RECORD;
|
|
record2 RECORD;
|
|
record_backup RECORD;
|
|
return_int4 int4 :=0;
|
|
purged_id int4 := 0;
|
|
purge_count int4 :=0;
|
|
timestamp1 timestamp;
|
|
purged_no int4 := 0;
|
|
oid1 int4 := 0;
|
|
oid_found int4 := 0;
|
|
highest_oid int4 := 0;
|
|
|
|
BEGIN
|
|
|
|
purged_id := $1;
|
|
-- If purged_id less than 1, return -1
|
|
IF purged_id < 1 THEN return (-1); END IF;
|
|
--- Get the current timestamp.
|
|
timestamp1 := CURRENT_TIMESTAMP;
|
|
|
|
FOR record1 IN select distinct TABLENAME_id from TABLENAME_backup
|
|
where TABLENAME_backup.error_code = ''purge''
|
|
and NOT TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)
|
|
and TABLENAME_id = purged_id
|
|
LOOP
|
|
purged_no := purged_no + 1;
|
|
|
|
END LOOP;
|
|
|
|
-- If purged_no less than 1, return -1
|
|
IF purged_no < 1 THEN return (-3); END IF;
|
|
|
|
-- Now find the highest oid.
|
|
FOR record2 IN select max(oid) from TABLENAME_backup
|
|
where TABLENAME_id = purged_id and error_code = ''purge''
|
|
LOOP
|
|
-- record we got the date and also record the highest date.
|
|
oid_found := 1;
|
|
highest_oid := record2.max;
|
|
END LOOP;
|
|
|
|
-- If the oid_found is 0, return error.
|
|
IF oid_found = 0 THEN return (-4); END IF;
|
|
|
|
-- Now get the data and restore it.
|
|
FOR record_backup IN select * from TABLENAME_backup
|
|
where oid = highest_oid
|
|
LOOP
|
|
-- Insert into backup that it was unpurged.
|
|
insert into TABLENAME_backup (TABLENAME_id, date_updated, date_created,
|
|
active BACKUPCOLUMNS ,error_code)
|
|
values (purged_id, timestamp1,
|
|
record_backup.date_created, record_backup.active
|
|
BACKUPVALUES , ''unpurgeone''
|
|
);
|
|
|
|
-- Get the unique oid of the row just inserted.
|
|
GET DIAGNOSTICS oid1 = RESULT_OID;
|
|
-- If oid1 less than 1, return -1
|
|
IF oid1 < 1 THEN return (-1); END IF;
|
|
-- Insert into live table.
|
|
insert into TABLENAME (TABLENAME_id, date_updated, date_created,
|
|
active BACKUPCOLUMNS)
|
|
values (record_backup.TABLENAME_id, timestamp1,
|
|
record_backup.date_updated, record_backup.active
|
|
BACKUPVALUES );
|
|
-- Get the unique oid of the row just inserted.
|
|
GET DIAGNOSTICS oid1 = RESULT_OID;
|
|
-- If oid1 less than 1, return -2
|
|
IF oid1 < 1 THEN return (-2); END IF;
|
|
|
|
END LOOP;
|
|
|
|
-- We got this far, it must be true, return how many were affected (1).
|
|
return (purged_no);
|
|
END;
|
|
' LANGUAGE 'plpgsql';
|
|
|
|
|
|
|
|
|
|
|
|
</pre>
|
|
|
|
and lastly <a href="misc/nielsen/Custom.sql">Custom.sql</a>.
|
|
<pre>
|
|
--- Custom Sample SQL for Perl/PostgreSQL version 0.1
|
|
|
|
--- Copyright 2001, Mark Nielsen
|
|
--- All rights reserved.
|
|
--- This Copyright notice was copied and modified from the Perl
|
|
--- Copyright notice.
|
|
--- This program is free software; you can redistribute it and/or modify
|
|
--- it under the terms of either:
|
|
|
|
--- a) the GNU General Public License as published by the Free
|
|
--- Software Foundation; either version 1, or (at your option) any
|
|
--- later version, or
|
|
|
|
--- b) the "Artistic License" which comes with this Kit.
|
|
|
|
--- This program 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 either
|
|
--- the GNU General Public License or the Artistic License for more details.
|
|
|
|
--- You should have received a copy of the Artistic License with this
|
|
--- Kit, in the file named "Artistic". If not, I'll be glad to provide one.
|
|
|
|
--- You should also have received a copy of the GNU General Public License
|
|
--- along with this program in the file named "Copying". If not, write to the
|
|
--- Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
|
|
--- 02111-1307, USA or visit their web page on the internet at
|
|
--- http://www.gnu.org/copyleft/gpl.html.
|
|
|
|
drop function clean_text (text);
|
|
CREATE FUNCTION clean_text (text) RETURNS text AS '
|
|
my $Text = shift;
|
|
# Get rid of whitespace in front.
|
|
$Text =~ s/^\\s+//;
|
|
# Get rid of whitespace at end.
|
|
$Text =~ s/\\s+$//;
|
|
# Get rid of anything not text.
|
|
$Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
|
|
# Replace all multiple whitespace with one space.
|
|
$Text =~ s/\\s+/ /g;
|
|
return $Text;
|
|
' LANGUAGE 'plperl';
|
|
-- Just to show you what this function cleans up.
|
|
select clean_text (' ,./<>?aaa aa !@#$%^&*()_+| ');
|
|
|
|
drop function clean_alpha (text);
|
|
CREATE FUNCTION clean_alpha (text) RETURNS text AS '
|
|
my $Text = shift;
|
|
$Text =~ s/[^a-z0-9_]//gi;
|
|
return $Text;
|
|
' LANGUAGE 'plperl';
|
|
-- Just to show you what this function cleans up.
|
|
select clean_alpha (' ,./<>?aaa aa !@#$%^&*()_+| ');
|
|
|
|
drop function clean_numeric (text);
|
|
CREATE FUNCTION clean_numeric (text) RETURNS int4 AS '
|
|
my $Text = shift;
|
|
$Text =~ s/[^0-9]//gi;
|
|
return $Text;
|
|
' LANGUAGE 'plperl';
|
|
-- Just to show you what this function cleans up.
|
|
select clean_numeric (' ,./<>?aaa aa !@#$%^&*()_+| ');
|
|
|
|
drop function clean_numeric (int4);
|
|
CREATE FUNCTION clean_numeric (int4) RETURNS int4 AS '
|
|
my $Text = shift;
|
|
$Text =~ s/[^0-9]//gi;
|
|
return $Text;
|
|
' LANGUAGE 'plperl';
|
|
-- Just do show you what this function cleans up.
|
|
select clean_numeric (1111);
|
|
|
|
|
|
|
|
</pre>
|
|
|
|
<p>
|
|
After you save the perl script, execute "chmod 755 Create_Functions.pl"
|
|
and then "./Create_Functions.pl". That should do it.
|
|
<p>
|
|
If you have installed PostgreSQL and Perl correctly, and you have setup
|
|
the database and your account has permissions to that database, then everything
|
|
should have worked fine.
|
|
|
|
<h3><a NAME="consider"></a>Considerations to explore.</h3>
|
|
I would like to test TCL, Python, and other languages as well for
|
|
stored procedures. If you are using MySQL, and I don't believe it has
|
|
stored procedures, you may want to consider PostgreSQL if you like the
|
|
style I mentioned. A nice GUI application to create tables and
|
|
make changes to tables would be nice. Lastly, examples of how to
|
|
connect to the database server to use these stored procedures (using Perl,
|
|
Python, PHP, TCL, C, etc) would be nice.
|
|
|
|
<h3><a NAME="Conclusion"></a>Conclusion</h3>
|
|
The combination of PostgreSQL and Perl rocks. I can use Perl for three things,
|
|
stored procedures, to setup my database, and to make Perl modules for
|
|
Apache that connect to the PostgreSQL database. Similar stuff can be
|
|
accomplished with other programming languages like Python, TCL, and others.
|
|
I want to try Python at some point when it gets out of beta
|
|
for PostgreSQL.
|
|
<p>
|
|
All database servers should use procedures exclusively for changing data.
|
|
You could even argue that you should make custom stored procedures
|
|
for selecting data as well. The reason why this is so important is because
|
|
the web programmer (or other type of programmer) doesn't have to know
|
|
anything about how to manipulate the data. They just submit variables
|
|
to procedures. This lets the web programmer use any programming language
|
|
he/she wants to without changing the behaviour of the database. The database
|
|
and how you use it becomes abstract.
|
|
<p>
|
|
One stupid thing my perl script does is execute the custom sql code
|
|
for each table. This s very bad. I will have to go back and fix it later.
|
|
You may want to test my stuff out with these commands:
|
|
<pre>
|
|
select sql_account_insert();
|
|
select sql_account_delete(1);
|
|
select sql_account_insert();
|
|
select sql_account_update(2,'mark','nielsen');
|
|
select sql_account_purge();
|
|
select sql_account_unpurge();
|
|
select * from account_backup;
|
|
select sql_account_delete(2);
|
|
select sql_account_insert();
|
|
select sql_account_update(1,'john','nielsen');
|
|
select sql_account_purge();
|
|
select * from account_backup;
|
|
</pre>
|
|
|
|
<h3>
|
|
<a NAME="REF"></a>References</h3>
|
|
|
|
<ol>
|
|
<li><a href="../issue67/nielsen.html">
|
|
My Previous PostgreSQL article</a>.
|
|
<li>
|
|
If this article
|
|
changes, it will be available at
|
|
<a href="http://www.gnujobs.com/Articles/22/Perl_PostgreSQL2.html">
|
|
http://www.gnujobs.com/Articles/22/Perl_PostgreSQL2.html</a></li>
|
|
</ol>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<!-- *** BEGIN bio *** -->
|
|
<SPACER TYPE="vertical" SIZE="30">
|
|
<p>
|
|
<h4><img align=bottom alt="" src="../gx/note.gif">Mark Nielsen</h4>
|
|
<EM>Mark works as an independent consultant donating time to causes like
|
|
GNUJobs.com, writing articles, writing free software, and working
|
|
as a volunteer at <a href="http://www.eastmont.net">eastmont.net</a>.</EM>
|
|
|
|
<!-- *** END bio *** -->
|
|
|
|
<!-- *** BEGIN copyright *** -->
|
|
<P> <hr> <!-- P -->
|
|
<H5 ALIGN=center>
|
|
|
|
Copyright © 2001, Mark Nielsen.<BR>
|
|
Copying license <A HREF="../copying.html">http://www.linuxgazette.com/copying.html</A><BR>
|
|
Published in Issue 69 of <i>Linux Gazette</i>, August 2001</H5>
|
|
<!-- *** END copyright *** -->
|
|
|
|
<!--startcut ==========================================================-->
|
|
<HR><P>
|
|
<CENTER>
|
|
<!-- *** BEGIN navbar *** -->
|
|
<IMG ALT="" SRC="../gx/navbar/left.jpg" WIDTH="14" HEIGHT="45" BORDER="0" ALIGN="bottom"><A HREF="mathew.html"><IMG ALT="[ Prev ]" SRC="../gx/navbar/prev.jpg" WIDTH="16" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="index.html"><IMG ALT="[ Table of Contents ]" SRC="../gx/navbar/toc.jpg" WIDTH="220" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><A HREF="../index.html"><IMG ALT="[ Front Page ]" SRC="../gx/navbar/frontpage.jpg" WIDTH="137" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="http://www.linuxgazette.com/cgi-bin/talkback/all.py?site=LG&article=http://www.linuxgazette.com/issue69/nielsen.html"><IMG ALT="[ Talkback ]" SRC="../gx/navbar/talkback.jpg" WIDTH="121" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><A HREF="../faq/index.html"><IMG ALT="[ FAQ ]" SRC="./../gx/navbar/faq.jpg"WIDTH="62" HEIGHT="45" BORDER="0" ALIGN="bottom"></A><A HREF="okopnik.html"><IMG ALT="[ Next ]" SRC="../gx/navbar/next.jpg" WIDTH="15" HEIGHT="45" BORDER="0" ALIGN="bottom" ></A><IMG ALT="" SRC="../gx/navbar/right.jpg" WIDTH="15" HEIGHT="45" ALIGN="bottom">
|
|
<!-- *** END navbar *** -->
|
|
</CENTER>
|
|
</BODY></HTML>
|
|
<!--endcut ============================================================-->
|