"Gaceta Linux...¡haciendo Linux un poco mas divertido!"


Combiando Perl y PostgreSQL, Parte 2: Procedimientos con PL/pgSQL

Por Mark Nielsen

Traducción al español por Freddy Galarza
el día 08 Abril 2003, para La Gaceta de Linux


  1. Introducción
  2. Script Perl para crear tablas, procedimientos, respaldar tablas y secuencias.
  3. Ejecutando el Script Perl
  4. Consideraciones para explorar.
  5. Conclusiones
  6. Referencias

Introducción

Después de tratar la instalación de PostgreSQL, Perl y Perl en PostgreSQL, quise una manera estandar de crear tablas, secuencias, procedimientos almacenados, y respaldar tablas. Tal vez otras personas tienen bonitas GUI para hacer esto, pero no he visto alguna. Me gustaría si alguien pudiera trabajar conmigo en una interface GUI para realizar lo que estoy haciendo aqui. Mis metas son:
  1. Siempre respaldar los datos sin importar lo que pase.
  2. Utilizar siempre procedimientos almacenados para insertar, actualizar, borrar, copiar o hacer cualquier cosa que cambie datos en las tablas. Uno hasta debería crear procedimientos almacenados para seleccionar datos.
  3. Tener un Perl script para crear tablas, secuencias, respaldar tablas y los procedimientos almacenados para manipular los datos.
  4. Tener los procedimientos almacenados para limpiar datos utilizando Perl.
  5. Respaldar datos si alguien ejecuta un script Perl en un sistema.
  6. Debe haber una columna activa en la tabla para que puedas especificar las filas activas o inactivas en la tabla. Creamos una vista en la cual se ve las filas activas o inactivas de la tabla.
  7. Los procedimientos almacenados deben registrar la fecha de creación y la fecha de ultima actualización.
  8. Todas las filas tienen un identificador unico. Aun si escogemos no utilizarlo, aun asi deberán tenerlo. No siempre es buenos utilizar el oid para conseguir filas unicas.
  9. Estar en capacidad de borrar filas inactivas con un procedimiento de limpieza. El procedimiento de limpieza sólo las hace inactivas. También, limpieza inversa de datos con los últimos datos limpiados para un identificador único. Esto es bacan.
  10. Todos los números negativos retornados de los procedimientos pl/sql son considerados fallas. Todos los números positivos (incluyendo el 0) son considerados exitosos desde el punto de vista que erroneo pasó. Ellos son 0, el cual significa que nada pasó, o alguna cosa mas grande que el 0 el cual indica que número de items afectados o un número de identificación.
Mis metas futuras incluyen:
  1. Crear una interface GUI. Preferiblemente una que no sea dependiente de las librerías GNOME o KDE pero si de Python. Puedes crear binarios Python fácilmente, así que preferiría Python/TK.
  2. Permitir al diseño GUI para hacer cambios en vivo a las tablas además de:
  3. Registrar todos los cambios de la base de datos para revisar el historial.

Script Perl para crear tablas, procedimientos, respaldar tablas y secuencias.

Aquí está un script Perl que utilizo. También puedes conseguir una copia aquí Create_Functions.pl.txt.
#!/usr/bin/perl

#              Crear Funciones para Perl/PostgreSQL version 0.1

#                       Derechos de Autor 2001, Mark Nielsen
#                            Todos los derechos reservados.
#    Estos derechos de autor fueron copiados y modificados de los Derechos de 
#    Autor de Perl. 
#    Este programa es un software gratuito; puedes redistribuirlo y/o modificarlo
#    bajo los siguientes terminos:

#        a) La GNU Licencia Publica General también publicada por la Fundación del
#        Software Libre (Free Software Foundation); versión 1, o (a su elección)
#        cualquier versión posterior, o

#        b) la "Licencia Artistica" la cual viene con este Kit.

#    Este programa es distribuido con la esperanza que será útil, pero SIN NINGUNA
#    GARANTIA; sin si quiera la garantía de NEGOCIABILIDAD o UTILIDAD A UN PROPOSITO
#    EN PARTICULAR.  Vease la GNU Licencia Publica General o la Licencia Artistica
#    para mas detalles.

#    Deberías haber recibido una copia de la Licencia Artistica con este
#    Kit, en el archivo nombreado "Artistic".  Si no, estaré gustoso de proveerte una.

#    También deberías haber recibido una copia de la Licencia Publica General GNU
#    con este programa en un archivo nombreado "Copying".  Si no, escriba a la
#    Fundación del Software Libre (Free Software Foundation), Inc., 59 Temple Place, 
#    Suite 330, Boston, MA 02111-1307, USA o visite su página web en la internet en
#    http://www.gnu.org/copyleft/gpl.html.

use strict;

  ### Queremos definir algunas variables LAS CUALES DEBES CAMBIAR PARA TU
  ### PROPIA COMPUTADORA. 
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;

  ## Vamos a crear los dos directorios que necesitamos si no están ahi.
if (!(-e "$Home/Tables")) {system "mkdir -p $Home/Tables"}
if (!(-e "$Home/Backups")) {system "mkdir -p $Home/Backups"}

  ### Abrimos la plantilla para las funciones y el archivo que contiene
  ### la información para crear las tablas.
open(FILE,$Template); my @Template = <FILE>; close FILE;
open(FILE,$File); my @File = <FILE>; close FILE;
open(FILE,$Custom); my @Custom = <FILE>; close FILE;

  ### Filtramos las líneas que no tienen números o letras.
@File = grep($_ =~ /[a-z0-9]/i, @File);
  ### Nos libramos de cualquier línea que contenga un #
@File = grep(!($_ =~ /\#/), @File);
  ### Nos libramos de las líneas nuevas.
grep(chomp $_, @File);
  ### Nos libramos de los tabs y los reemplazamoscon espacios.
grep($_ =~ s/\t/ /g, @File);
  ### Convertimos todos los espacios multiples en uno.
grep($_ =~ s/  +/ /g, @File);
  ### En las próximas dos líneas nos libramos de los espacios
grep($_ =~ s/^ //g, @File);
grep($_ =~ s/ $//g, @File);
  ### Borramos cualquier coma al final, los pondremos otra vez después.
grep($_ =~ s/\,$//g, @File);

my $Tables = {};
my $TableName = "";
  ### Para cada línea en el archivo, hacemos un nuevo arreglo para cada tabla,
  ### o almacenamos las líneas en el arreglo para un tabla.
foreach my $Line (@File)
  {
  my $Junk = "";
    ### Si la línea comienza con "TABLENAME" entonces creamos un nuevo arreglo.
  if ($Line =~ /^TABLENAME/)
    {
    ($Junk,$TableName, $Junk) = split(/ /,$Line);
       ### Esto crea el arreglo para la tabla.
    $Tables->{$TableName} = [];
    }
  else
    {
       ### Almacenando líneas para la tabla.
    push (@{$Tables->{$TableName}}, $Line) ;
    }
  }

    ### Si listamos tablas especificas, entonces sòlo hacemos esto.
  if (@List)
    {
    foreach my $TableName (sort keys %$Tables)
      { if (!(grep($_ eq $TableName, @List))) {delete $Tables->{$TableName};} }
    }

  ### Ponemos las referencias de las claves en un arreglo $Tables
  ### y conseguimos los datos para ese arreglo, creamos nuestro archivo,
  ### y entonces utilizamos el archivo.
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 = "";
    ### Las dos Tablas son diferentes de una forma, la tabla de respaldo
    ### no requiere claves unicas y no requiere uso de una secuencia.
  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,
);
    ### Debería permitir nulos para la identidad en lugar de 0, pero debido a que la secuencia
    ### comienza con 1, utilizo 0 como nulo.  Odio los nulos.
  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;
    ### Para cada línea de este tabla hacemos esto.
    ### Queremos crear unas pocas variables van a ser puestas dentro
    ### de una plantilla.
  foreach my $Line (@Temp)
    {
    $Table .= "$Line,\n";
    $Table_Backup .= "$Line,\n";
    my ($Name,$Type,$Ext) = split(/ /,$Line,3);
      ### Las columnas de respaldo
    $Backup_Columns .= ", $Name";
      ### Los campos de actualización
    $No++; $Update_Fields .= ", $Name = var_$No";
      ### Valores de respaldo
    $Backup_Values .= ", record_backup.$Name";
      ### Ahora los campos donde copiamos las cosas con la función cyop
    $Field_Copy_Values .= ", clean_text(record2.$Name)";
      ### Ahora los tipos de campos para la función actualizar (update)
    $FieldTypes .= ", $Type";
      ### Necesitamos definir las variable para la función de actualizacion
    $CleanVariables .= "          var_$No $Type;\n";
      ### Necesitamos definir el tipo, sólo reviso texto e int4 por ahora.
    my $Temp = "\$$No";
    if ($Type eq "int4") {$Temp = "clean_numeric($Temp)";}
    elsif  ($Type eq "text") {$Temp = "clean_text($Temp)";}
      ### Ahora necesitamos poner las variables
    $RemakeVariables .= "         var_$No := $Temp;\n";

     ### También necesitamos añadir la función para limpiar las variables antes
     ### antes que sean enviadas.
    }
   ### Registramos cuantas filas tenemos.  Hacemos una línea para el comando
   ### de actualización para probarla.
  my $Number_Of_Rows = $No;
  my $Update_Test = "1";
  for (my $i = 1; $i < $Number_Of_Rows - 1; $i++) {$Update_Test .= ",$i";}

    ### Necesitamos quitar la última coma.
  chomp $Table; chop $Table; chomp $Table_Backup; chop $Table_Backup;
    ### Ahora eliminamos y creamos la tabla y la tabla de respaldo.
  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";
    ### Cramos una vista para las filas activas en nuestra tabla.
  $Tables .= "drop view $TableName\_active;\n";
  $Tables .= "create view $TableName\_active as select * from $TableName
        where active = 1;\n";
    ### Creamos una vista para los items inactivos o borrados.
  $Tables .= "drop view $TableName\_deleted;\n";
  $Tables .= "create view $TableName\_deleted as select * from $TableName
        where active = 0;\n";
    ### Creamos una vista para listar las identificaciones unicas de los respaldos.
  $Tables .= "drop view $TableName\_backup_ids;\n";
  $Tables .= "create view $TableName\_backup_ids as
           select distinct $TableName\_id from $TableName\_backup;\n";
    ### Creamos una lista de los datos purgados (los últimos datos por identificación).
  $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";

     ### Utilizo el comando grep para buscar y reemplazar cosas en los arreglos.
     ### Podría utilizar map, pero me gustan los greps.
  my @Temp = @Template;
     ### ahora añado comandos sql propios.
  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);

    ### Ahora movemos las cosas desde el arrglo @Temp hacia @Template_Copy
  my @Template_Copy = @Temp;

    ### Ahora guardamos el archivo.  No lo borramos (aunque corramos el script
    ### otra vez) para que sepamos que que se hiso.
  open(FILE,">$Home/Tables/$TableName\.table_functions");
    ### Creamos la secuencia para la tabla.
  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";
    ### Mostramos la tabla y la tabla de respaldo.
  print FILE $Tables;
    ### Mostramos las 4 funciones, insertar (insert), borrar (borrar), actualizar (update)
    ### y copiar (copy)
  foreach my $Temp (@Template_Copy) {print FILE "$Temp";}

  close FILE;

    ### Antes de ejecutar, respaldamos la tabla en caso de que algún novato
    ### lo ejecute en un servidor de producción.
  my $Backup_File = "$Home/Backups/$TableName\_0.backup";
  my $No = 0;
  while (-e $Backup_File)
    {$No++; $Backup_File = "$Home/Backups/$TableName\_$No\.backup";}
    ### Ahora que tenemos el nombre del archivo para almacenar el respaldo, lo ejecutamos.
  system ("pg_dump -t $TableName -f $Backup_File $Database");

  ### Descomentar esta opción si desea ver hay dentro del archivo.
##  system ("cat $Home/Tables/$TableName\.table_functions");

    ### Eliminamos la tabla y las funciones, creamos una tabla y funciones
    ### para reemplazar y respaldar la tabla.
  system ("psql -d $Database -c '\\i $Home/Tables/$TableName\.table_functions'");
  print "Check the file\n $Home/Tables/$TableName\.table_functions.\n";

  }



Renombrar el script perl "Create_Functions.pl.txt". Aquí hay cosas que necesitan ser hechas para que funcione:

Ejecutando un script Perl

Necesitaras algunos otros archivos. El archivo Tables.txt.

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,
Puedes utilizar mi archivo como un ejemplo, pero suguiero modificarlo a tus propias necesidades. Simula hacer tres tablas. Una conteniendo los nombre de usuarios y passwords, y las otras asociando los nombres de usuario a la lista de contactos. Otro archivo que necesitaras es Generic.fun
---              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);
     -- Conseguimose el identificador de la fila recien insertada.
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Conseguimos la identificación de TABLENAME.
   FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where oid = oid1
      LOOP
      id := record1.TABLENAME_id;
   END LOOP;
   
     -- Si la identificación es NULL, el insert falló o algo está mal.
   IF id is NULL THEN return (-1); END IF;
     -- También debería ser mayor a 0, en caso contrario algo está mal.
   IF id < 1 THEN return (-2); END IF;

      -- Ahora respaldamos los datos.
    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;

     -- Todo ha pasado, retornamos la identificación como 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
     -- Si la id no es mayor a 0, regresamos error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- Si encontramos la id, ponemos 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;

     -- Si no encontramos la id, abortamos y regresamos -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;

     -- Si id_exists == 0, retornamos error
     -- Significa que nunca existió.
   IF id_exists = 0 THEN return (-1); END IF;

     -- Si llegamos hasta aquí, debe ser cierto, retornamos 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
     -- Si la id no es mayor a 0, retornamos 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;

     -- Si llegamos tan lejos, debe ser cierto, retornamos 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
     -- Si la id no es mayor a 0, retornamos 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;

     --- Obtenemos una nueva id.
   FOR record1 IN SELECT sql_TABLENAME_insert() as TABLENAME_insert
      LOOP
      TABLENAME_new := record1.TABLENAME_insert;
   END LOOP;
     -- Si la TABLENAME_new no es mayor a 0, retornamos 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
        -- Ejecutamos algunos comandos arbitrarios sólo para complementar.
      id_exists := 1;
     END LOOP;
   END LOOP;

     -- Si llegamos tan lejos, debe ser cierto, retornamos la nueva 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

     -- Ahora borramos uno por uno.
   FOR record_backup IN SELECT * FROM TABLENAME where active = 0
      LOOP
         -- Registramos la id que deseamos borrar.
      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''
          );

        -- Obtenemos la oid única de la fila recien insertada.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- Si la oid1 es menor a 1, retornamos -1
      IF oid1 < 1 THEN return (-2); END IF;
        -- Ahora la borramos de la tabla principal.
      delete from TABLENAME where TABLENAME_id = delete_id;

        -- Obtenemos el contador de filas recien borradas, debe ser 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- Si borramos menos que una, retornamos -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- Si llegamos tan lejos, debe ser cierto, retornamos lso número que tenemos.
   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;
        -- Si purged_id es menor a 1, retornamos -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;

        -- Si purged_no es menor que 1, retornamos -1
   IF purged_no < 1 THEN return (-1); END IF;

     -- Ahora borramos uno a uno.
   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''
          );

        -- Conseguimos la oid única de la fila recien insertada.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- Si oid1 es menor a 1, retornamos -2
      IF oid1 < 1 THEN return (-2); END IF;
        -- Ahora borramos de la tabla principal.
      delete from TABLENAME where TABLENAME_id = delete_id;

        -- Obtenemos el contador de filas, recien borradas, debe ser 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- Si las borradas es menor a 1, retornamos -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- Si llegamos tan lejos, debe ser cierto, retornamos el números que tenemos.
   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

     -- Ahora obtenemos los ids únicos que fueron purgados.
   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;

        -- Ahora que tenemos el id único, encontramos su última fecha.

      FOR record2 IN select max(oid) from TABLENAME_backup
          where TABLENAME_id = purged_id and error_code = ''purge''
        LOOP
          -- registramos la fechas que obtuvimos y también registramos la fecha mas alta.
        oid_found := 1;
        highest_oid := record2.max;
      END LOOP;

         -- Si oid_found es 0, retornamos error.
      IF oid_found = 0 THEN return (-3); END IF;

        -- Ahora tenemos la última fecha, obtenemos los valores y los insertamos.
      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''
          );

        -- Obtenemos la oid único de la fila que recien insertamos.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- Si oid1 es menor que 1, retornamos -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 );
        -- Obtenemos la oid única para la fila recien insertada.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- Si oid1 es menor que 1, retornamos -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

   END LOOP;

     -- Si llegamos tan lejos, debe ser cierto, retornamos cuantos fueron afectados.
   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;
        -- Si purged_id es menor que 1, retornamos -1
      IF purged_id < 1 THEN return (-1); END IF;
        --- Obtenemos la timestamp actual.
      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;

        -- Si purged_no es menor que 1, retornamos -1
   IF purged_no < 1 THEN return (-3); END IF;

        -- Ahora encontramos el oid mas alto.
   FOR record2 IN select max(oid) from TABLENAME_backup
          where TABLENAME_id = purged_id and error_code = ''purge''
        LOOP
          -- registramos la fecha y también registramos la fecha mas alta.
        oid_found := 1;
        highest_oid := record2.max;
    END LOOP;

         -- Si las oid_found es 0, retornamos error.
    IF oid_found = 0 THEN return (-4); END IF;

        -- Ahora obtenemos los datos y los restauramos.
    FOR record_backup IN select * from TABLENAME_backup
          where oid  = highest_oid
        LOOP
        -- Insertamos dentro del respaldo que fue despurgado.
      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''
          );

        -- Obtenemos la oid única de la fila recien insertada.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- Si oid1 es menor que 1, retornamos -1
      IF oid1 < 1 THEN return (-1); END IF;
        -- Insertamos dentro de la tabla.
      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 );
        -- Conseguimos la oid única de la fila recien insertada.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- Si oid1 es menor que 1 retornamos -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

     -- Si llegamos tan lejos debe ser cierto, retornamos cuantas fueron afectas (1).
   return (purged_no);
END;
' LANGUAGE 'plpgsql';





y finalmente Custom.sql.
---          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;
    # Nos dehacemos de los espacios en blanco al principio.
  $Text =~ s/^\\s+//;
    # Nos dehacemos de los espacios en blanco al final.
  $Text =~ s/\\s+$//;
    # Nos deshacemos de cualquier cosa que no sea texto.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Reemplazamos todos los espacios multiples con un espacio.
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Sòlo para mostrate que limpia esta función.
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';
 -- Sólo para mostrarte que limpia esta función.
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';
 -- Sólo para mostrarte que limpia esta función.
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';
 -- Sólo para mostrarte que limpia esta función.
select clean_numeric (1111);



Después de guardar el script perl, ejecuta "chmod 755 Create_Functions.pl" y después "./Create_Functions.pl". Eso debe hacerlo.

Si tiene instalado PostgreSQL y Perl correctamente, y ha configurado la base de datos y ha establecido todos los permisos de la cuenta en la base de datos, entonces todo debe trabajar bien.

Considerations to explore.

Me gustaría probar TCL, Python y otros lenguajes en tanto como procedimientos almacenados. Si está utilizando MySQL, y no creo que tenga procedimientos almacenados, talvez desees considerar PostgreSQL si te gusta el estilo que he mencionado. Una buena aplicación GUI para crear tablas y hacer cambios sería sampática. Por último, ejemplos de como conectarse a los servidores de base de datos para utilizar estos procedimientos almacenados (utilizando Perl, Python, PHP, TCL, C, etc) serían buenos.

Conclusión

La combinación de PostgreSQL y Perl es increible. Puedo utilizar Perl para tres cosas, procedimientos almacenados, para configurar mi base de datos y para hacer modulos Perl para Apache que se conecten a la base de datos PostgreSQL. Cosas similiares pueden ser hechas con otros lenguajes de programación como por ejemplo, Python, TCL, y otros. Quiero tratar Python en algún punto cuando salga el beta para PostgreSQL.

Todos los servidores de bases de datos deben utilizar exclusivamente procedimientos para cambiar datos. Podrías argumentar que deberías hacer procedimientos almacenados propios para selecionar datos. La razòn del por què es tan importa es debido a la programaciòn web (u otro tipo de programaciòn) no tienen que saber nada acerca de como manipular los datos. Sólo enviar variables a los procedimientos. Esto deja a los programadores web utilizar cualquier lenguaje de programación que él/ella desee sin cambiar el comportamiento de la base de datos. La base de datos y como utilizarla se hacen abstractos.

Una cosa estupida que mi script perl hace es ejecutar còdigo sql propio para cada tabla. Esto es muy malo. Tendrè que regresar y cambiarlo posteriormente. Tal vez quieran probar sin estos comandos:

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;

References

  1. Mi artículos PostgreSQL previo.
  2. Si este artículo cambia estará disponible en http://www.gnujobs.com/Articles/22/Perl_PostgreSQL2.html

Mark Nielsen

Mark trabajo como un consultor independiente donando tiempo a las causas como GNUJobs.com, escribiendo artículos, escribiendo software gratuito y trabajando como voluntario en eastmont.net.


Copyright © 2001, Mark Nielsen.
Licencia de Copia http://www.linuxgazette.com/copying.html
Publicado en el número 69 de Linux Gazette, Agosto 2001