|
Совместное использование Perl и PostgreSQL, часть 2-я: Процедуры с PL/pgSQL
Автор: © Марк Нильсен [Mark Nielsen]
|
|
ВведениеПосле возни с установкой PostgreSQL, Perl и встраивания в него Perl, мне захотелось стандартизированного способа создания таблиц, последовательностей, хранимых процедур и резервного копирования таблиц. Возможно, у кого-то для этого есть милые графические утилиты, но мне они не попадались. Хорошо бы кто нибудь стал со мной сотрудничать в создании графического интерфейса для того, о чем расказывается ниже. Цели я формулирую так:
Скрипт Perl для создания таблиц, процедур, резервного копирования таблиц и последовательностей.Я пользуюсь таким скриптом. Его можно также взять здесь Create_Functions.pl.txt.
#!/usr/bin/perl
# Создание функций для Perl/PostgreSQL, версия 0.1
# Copyright 2001, Mark Nielsen
# All rights reserved.
# Это лицензия составлена по образцу той, под которой распространяется Perl.
#
# Это свободная программа;
# Вы можете ее распространять и/или модифицировать
# на следующих условиях:
# a) либо под лицензией на свободное программное обеспечение
# GNU General Public License версии 1 или любой последующей версией
# по вашему выбору в том виде, в каком она опубликована
# Free Software Foundation;
# либо
# b) под "Artistic License", которая прилагается к этому пакету.
# Данная программа распространяется в надежде, что она окажется полезной,
# но БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ, касающихся или подразумевающих то,
# что данная программа может обладать КОММЕРЧЕСКОЙ ЦЕННОСТЬЮ
# или ОКАЖЕТСЯ ПРИМЕНИМОЙ ДЛЯ ТОЙ ИЛИ ИНОЙ ЦЕЛИ.
# Подробности можно выяснить из текста GNU General Public License
# или Artistic License.
# Копию Artistic License, прилагаемую к этому пакету,
# можно найти в файле "Artistic".
# Если по каким-либо причинам этого файла нет,
# то я с радостью его предоставлю.
# Вместе с этой программой Вы также должны получить копию
# GNU General Public License в файле "Copying". Если по каким-либо причинам
# этого файла нет, напишите в Фонд Свободного Програмного обеспечения
# по адресу:
# Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA
# или посетите веб-сайт http://www.gnu.org/copyleft/gpl.html.
use strict;
### Определим некоторые глобальные переменные, которые
### ВАМ НАДО ИЗМЕНИТЬ ДЛЯ СВОЕЙ СИСТЕМЫ.
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;
## Создаем нужные нам директории, если их еще нет.
if (!(-e "$Home/Tables")) {system "mkdir -p $Home/Tables"}
if (!(-e "$Home/Backups")) {system "mkdir -p $Home/Backups"}
### Открываем шаблоны функций и файл, содержащий
### информацию для создания таблиц.
open(FILE,$Template); my @Template = <FILE>; close FILE;
open(FILE,$File); my @File = <FILE>; close FILE;
open(FILE,$Custom); my @Custom = <FILE>; close FILE;
### Отфильтруем строки, не содержащие ни строк, ни цифр.
@File = grep($_ =~ /[a-z0-9]/i, @File);
### Избавимся от любой строки, содержащей символ #
@File = grep(!($_ =~ /\#/), @File);
### Избавимся от символов новой строки.
grep(chomp $_, @File);
### Избавимся от символов табуляции и заменим их пробелами .
grep($_ =~ s/\t/ /g, @File);
### Заменим несколько смежных пробелов одним.
grep($_ =~ s/ +/ /g, @File);
### Две следующие строки избавляются от начальных и завершающих пробелов.
grep($_ =~ s/^ //g, @File);
grep($_ =~ s/ $//g, @File);
### Удалим завершающие запятые, мы вернем их на место позже.
grep($_ =~ s/\,$//g, @File);
my $Tables = {};
my $TableName = "";
### Для каждой строки в файле либо создаем новый массив для таблицы,
### либо сохраняем строки в предназначенном для таблицы массиве.
foreach my $Line (@File)
{
my $Junk = "";
### Если строка начинается с "TABLENAME", то создаем новый массив.
if ($Line =~ /^TABLENAME/)
{
($Junk,$TableName, $Junk) = split(/ /,$Line);
### Это создает массив для таблицы.
$Tables->{$TableName} = [];
}
else
{
### Сохраняем строку для таблицы.
push (@{$Tables->{$TableName}}, $Line) ;
}
}
### Если мы перечислили определенные таблицы, то ограничиваемся следующим.
if (@List)
{
foreach my $TableName (sort keys %$Tables)
{ if (!(grep($_ eq $TableName, @List))) {delete $Tables->{$TableName};} }
}
### Получим ключи для доступа к массиву $Tables
### получим данные для этого массива, создадим наш файл и примемся им пользоваться.
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 = "";
### Таблицы отличаются в том отношении, что резервная таблица
### не требует уникальности и не используют 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,
);
## Следовало бы разрешить для id значения null, а не 0
## но так как sequence начинается с 1, то я использую 0 как null.
### Ненавижу null'ы...
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 "Генерируем функции для таблицы '$TableName'\n";
my $No = 1;
### Для каждой относящейся к этой таблице строки делаем следующее.
### Нам нужно создать несколько переменных
### и поместить их в шаблон.
foreach my $Line (@Temp)
{
$Table .= "$Line,\n";
$Table_Backup .= "$Line,\n";
my ($Name,$Type,$Ext) = split(/ /,$Line,3);
### Резервные колонки
$Backup_Columns .= ", $Name";
### Поля обновления
$No++; $Update_Fields .= ", $Name = var_$No";
### Сохраненные [backuped] значения
$Backup_Values .= ", record_backup.$Name";
### А теперь поля, используемые при копировании функцией copy.
$Field_Copy_Values .= ", clean_text(record2.$Name)";
### Типы полей для функции update.
$FieldTypes .= ", $Type";
### Нужно определить переменные для функции обновления.
$CleanVariables .= " var_$No $Type;\n";
### Нам нужно определить тип, пока я проверяю text и int4.
my $Temp = "\$$No";
if ($Type eq "int4") {$Temp = "clean_numeric($Temp)";}
elsif ($Type eq "text") {$Temp = "clean_text($Temp)";}
### А теперь надо установить переменные.
$RemakeVariables .= " var_$No := $Temp;\n";
### Нам также потребуется функция для очистки переменных
### перед тем, как они будут передаваться по назначению.
}
### Запишем, сколько у нас получилось строк.
### Добавим строку для проверки update'а.
my $Number_Of_Rows = $No;
my $Update_Test = "1";
for (my $i = 1; $i < $Number_Of_Rows - 1; $i++) {$Update_Test .= ",$i";}
### Последнюю запятую нужно отбросить.
chomp $Table; chop $Table; chomp $Table_Backup; chop $Table_Backup;
### А теперь настроим создание и уничтожение основных и резервных таблиц.
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";
### Создадим представление [view] для показа активных записей в таблице.
$Tables .= "drop view $TableName\_active;\n";
$Tables .= "create view $TableName\_active as select * from $TableName
where active = 1;\n";
### Сгенерим view для просмотра неактивных или удаленных элементов.
$Tables .= "drop view $TableName\_deleted;\n";
$Tables .= "create view $TableName\_deleted as select * from $TableName
where active = 0;\n";
### Представление, выводящее список удаленных уникальных идентификаторов.
$Tables .= "drop view $TableName\_backup_ids;\n";
$Tables .= "create view $TableName\_backup_ids as
select distinct $TableName\_id from $TableName\_backup;\n";
### Создадим список "вычищенных" данных (последние для данного 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";
### Я провожу поиск в массивах с помощью grep.
### Можно было бы использовать словари [maps], но я люблю грепить.
my @Temp = @Template;
### а теперь добавим пользовательскую команду sql.
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);
### Переместим все из массива @Temp в @Template_Copy.
my @Template_Copy = @Temp;
### А теперь сохраним файл. Мы не будем его удалять (кроме случаев повторного
### выполнения скрипта), так что всегда можно выяснить, что же было сделано.
open(FILE,">$Home/Tables/$TableName\.table_functions");
### Создадим последовательность [sequence] для таблицы.
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";
### Распечатаем основную и резервную [backup] таблицы.
print FILE $Tables;
### Распечатаем 4 основных функции: insert, delete, update, и copy.
foreach my $Temp (@Template_Copy) {print FILE "$Temp";}
close FILE;
### Перед запуском создадим резервную копию таблицы
### на тот случай, если какой-нибудь новичок запустит это
### на работающем сервере.
my $Backup_File = "$Home/Backups/$TableName\_0.backup";
my $No = 0;
while (-e $Backup_File)
{$No++; $Backup_File = "$Home/Backups/$TableName\_$No\.backup";}
### А теперь, когда у нас есть имя файла для резервной копии, можно работать.
system ("pg_dump -t $TableName -f $Backup_File $Database");
### Снимите комментарий, если хотите видеть, что содержится в файле.
## system ("cat $Home/Tables/$TableName\.table_functions");
### Удаляем таблицы и функции, создаем таблицы и функции
### и делаем резервную таблицу.
system ("psql -d $Database -c '\\i $Home/Tables/$TableName\.table_functions'");
print "Check the file\n $Home/Tables/$TableName\.table_functions.\n";
}
Переименуем скрипт "Create_Functions.pl.txt". Вот кое-что необходимое для того, чтобы он заработал:
Выполнение скриптов PerlВам понадобятся еще файлы. А именно файл 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, В качестве примера можно воспользоваться моим файлом, но я рекомендую Вам модифицировать его в соответствии с собственными нуждами. В примере симулируется система из трех таблиц. Одна содержит имена пользователей и пароли, а вторая связывает имена пользователей со списком контрактов. Вам также понадобится файл Generic.fun
--- Примеры функций для Perl/PostgreSQL версии 0.1
--- Copyright 2001, Mark Nielsen
--- All rights reserved.
--- Это лицензия составлена по образцу той, под которой расространяется Perl.
---
--- Это свободная программа;
--- Вы можете ее распространять и/или модифицировать
--- на следующих условиях:
--- a) либо под лицензией на свободное программное обеспечение
--- GNU General Public License версии 1 или любой последующей версией
--- по вашему выбору в том виде, в каком она опубликована
--- Free Software Foundation;
--- либо
--- b) под "Artistic License", которая прилагается к этому пакету.
--- Данная программа распространяется в надежде, что она окажется полезной,
--- но БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ, касающихся или подразумевающих то,
--- что данная программа может обладать КОММЕРЧЕСКОЙ ЦЕННОСТЬЮ
--- или ОКАЖЕТСЯ ПРИМЕНИМОЙ ДЛЯ ТОЙ ИЛИ ИНОЙ ЦЕЛИ.
--- Подробности можно выяснить из текста GNU General Public License
--- или Artistic License.
--- Копию Artistic License, прилагаемую к этому пакету,
--- можно найти в файле "Artistic".
--- Если по каким-либо причинам этого файла нет,
--- то я с радостью ее предоставлю.
--- Вместе с этой программой Вы также должны получить копию
--- GNU General Public License в файле "Copying". Если по каким-либо причинам
--- этого файла нет, напишите в Фонд Свободного Програмного обеспечения
--- по адресу:
--- Free Software Foundation, Inc.,
--- 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA
--- или посетите веб-сайт http://www.gnu.org/copyleft/gpl.html.
-- создать метод для восстановления [unpurge] только одного элемента.
-- создать метод для очистки [purge] только одного элемента.
-- \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);
-- Получить уникальный oid только что вставленной строки.
GET DIAGNOSTICS oid1 = RESULT_OID;
-- Получить TABLENAME id.
FOR record1 IN SELECT TABLENAME_id FROM TABLENAME where oid = oid1
LOOP
id := record1.TABLENAME_id;
END LOOP;
-- Если id NULL, либо insert не удался, либо еще что-то не прошло.
IF id is NULL THEN return (-1); END IF;
-- Он также должен быть больше 0, в противном случае что-то случилось.
IF id < 1 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, error_code)
values (id, record_backup.date_updated, record_backup.date_created,
record_backup.active, ''insert'');
END LOOP;
-- Все хорошо, возвращаем id как 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
-- Если id не больше 0 возвращаем ошибку.
id := clean_numeric($1);
IF id < 1 THEN return -1; END IF;
-- Если id нашелся, устанавливаем 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;
-- Если мы не нашли id, то прерываем выполнение и возвращаем -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;
-- Возвращаем ошибку если id_exists == 0.
-- Это означает, что такого id никогда не существовало.
IF id_exists = 0 THEN return (-1); END IF;
-- Т.к. мы здесь, то все верно. Возвращаем 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
-- Возвращаем ошибку если id не больше 0.
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;
-- Т.к. мы здесь, то все верно. Возвращаем 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
-- Если id не больше 0, то возвращаем ошибку.
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;
--- Получаем новый id
FOR record1 IN SELECT sql_TABLENAME_insert() as TABLENAME_insert
LOOP
TABLENAME_new := record1.TABLENAME_insert;
END LOOP;
-- Если TABLENAME_new не больше 0, то возвращаем ошибку.
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
-- Вставьте сюда произвольную команду просто для того, чтобы она выполнилась.
id_exists := 1;
END LOOP;
END LOOP;
-- Коли мы здесь, значит все вышло. Возвращаем 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
-- Удаляем по одной.
FOR record_backup IN SELECT * FROM TABLENAME where active = 0
LOOP
-- Записываем id, который собираемся удалить.
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''
);
-- Получаем уникальный oid для только что вставленной строки.
GET DIAGNOSTICS oid1 = RESULT_OID;
-- Если oid1 меньше 1, возвращаем -2
IF oid1 < 1 THEN return (-2); END IF;
-- Удаляем из основной таблицы.
delete from TABLENAME where TABLENAME_id = delete_id;
-- Получим число только что удаленных строк, должна быть 1.
GET DIAGNOSTICS deleted = ROW_COUNT;
-- Если удалено меньше 1, то возвращаем -3
IF deleted < 1 THEN return (-3); END IF;
delete_count := delete_count + 1;
END LOOP;
-- Уж если мы здесь, то все прокатило, возвращаем соответствующее число.
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;
-- Если просят удалить менее 1, то возвращаем -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;
-- Если purged_no меньше 1, возвращаем -1
IF purged_no < 1 THEN return (-1); END IF;
-- А теперь удаляем по одной.
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''
);
-- Получаем уникальный oid только что удаленной строки.
GET DIAGNOSTICS oid1 = RESULT_OID;
-- Если oid1 меньше 1, возвращаем -2
IF oid1 < 1 THEN return (-2); END IF;
-- А теперь удаляем из главной таблицы.
delete from TABLENAME where TABLENAME_id = delete_id;
-- Получаем число только что удаленных строк, которое должно равнятся 1.
GET DIAGNOSTICS deleted = ROW_COUNT;
-- Если удалено менее одной строки, возвращаем -3
IF deleted < 1 THEN return (-3); END IF;
delete_count := delete_count + 1;
END LOOP;
-- Если мы дошли до сюда, то все хорошо: возвращаем число удаленных строк.
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
-- Получаем уникальные id вычищенных строк.
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;
-- Теперь, когда у нас есть id, найдем для каждого последнюю дату.
FOR record2 IN select max(oid) from TABLENAME_backup
where TABLENAME_id = purged_id and error_code = ''purge''
LOOP
-- запишем полученную дату и самую большую дату.
oid_found := 1;
highest_oid := record2.max;
END LOOP;
-- Если oid_found == 0, то возвращаем ошибку.
IF oid_found = 0 THEN return (-3); END IF;
-- Теперь, когда у нас есть последняя дата, получим соответствующие
-- значения и вставим из в таблицу.
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''
);
-- Получаем уникальный oid только что вставленной строки.
GET DIAGNOSTICS oid1 = RESULT_OID;
-- Если oid1 меньше единицы, то возвращаем -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 );
-- Получаем уникльный oid только что вставленной строки.
GET DIAGNOSTICS oid1 = RESULT_OID;
-- Если oid1 меньше 1, то возвращаем -2
IF oid1 < 1 THEN return (-2); END IF;
END LOOP;
END LOOP;
-- Все хорошо, коли мы здесь, возвращаем число обработанных строк.
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;
-- Если purged_id меньше 1, возвращаем -1
IF purged_id < 1 THEN return (-1); END IF;
--- Получаем отметку времени.
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;
-- Если purged_no меньше 1, возвращаем -1
IF purged_no < 1 THEN return (-3); END IF;
-- Ну вот и найден самый большой oid.
FOR record2 IN select max(oid) from TABLENAME_backup
where TABLENAME_id = purged_id and error_code = ''purge''
LOOP
-- запишем, что получили вместе с последней датой.
oid_found := 1;
highest_oid := record2.max;
END LOOP;
-- Если oid_found == 0, возвращаем ошибку.
IF oid_found = 0 THEN return (-4); END IF;
-- Получаем данные и восстанавливаем их.
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, timestamp1,
record_backup.date_created, record_backup.active
BACKUPVALUES , ''unpurgeone''
);
-- Получаем уникальный oid только что вставленной строки.
GET DIAGNOSTICS oid1 = RESULT_OID;
-- Если oid1 меньше 1, возвращаем -1
IF oid1 < 1 THEN return (-1); END IF;
-- Вставка в рабочую таблицу.
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 );
-- Получаем уникальный oid только что вставленной строки.
GET DIAGNOSTICS oid1 = RESULT_OID;
-- Если oid1 меньше единицы, то возвращаем -2
IF oid1 < 1 THEN return (-2); END IF;
END LOOP;
-- Все хорошо, поскольку мы здесь, возвращаем число затронутых записей (1).
return (purged_no);
END;
' LANGUAGE 'plpgsql';
и наконец Custom.sql.
--- Пример SQL для Perl/PostgreSQL version 0.1
--- Copyright 2001, Mark Nielsen
--- All rights reserved.
--- Это лицензия составлена по образцу той, под которой расространяется Perl.
---
--- Это свободная программа;
--- Вы можете ее распространять и/или модифицировать
--- на следующих условиях:
--- a) либо под лицензией на свободное программное обеспечение
--- GNU General Public License версии 1 или любой последующей версией
--- по вашему выбору в том виде, в каком она опубликована
--- Free Software Foundation;
--- либо
--- b) под "Artistic License", которая прилагается к этому пакету.
--- Данная программа распространяется в надежде, что она окажется полезной,
--- но БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ, касающихся или подразумевающих то,
--- что данная программа может обладать КОММЕРЧЕСКОЙ ЦЕННОСТЬЮ
--- или ОКАЖЕТСЯ ПРИМЕНИМОЙ ДЛЯ ТОЙ ИЛИ ИНОЙ ЦЕЛИ.
--- Подробности можно выяснить из текста GNU General Public License
--- или Artistic License.
--- Копию Artistic License, прилагаемую к этому пакету,
--- можно найти в файле "Artistic".
--- Если по каким-либо причинам этого файла нет,
--- то я с радостью ее предоставлю.
--- Вместе с этой программой Вы также должны получить копию
--- GNU General Public License в файле "Copying". Если по каким-либо причинам
--- этого файла нет, напишите в Фонд Свободного Програмного обеспечения
--- по адресу:
--- Free Software Foundation, Inc.,
--- 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA
--- или посетите веб-сайт http://www.gnu.org/copyleft/gpl.html.
drop function clean_text (text);
CREATE FUNCTION clean_text (text) RETURNS text AS '
my $Text = shift;
# Избавимся от начальных пробелов.
$Text =~ s/^\\s+//;
# Избавимся от конечных пробелов.
$Text =~ s/\\s+$//;
# Избавимся от всего, что не есть текст.
$Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
# Заменяем несколько пробелов одним.
$Text =~ s/\\s+/ /g;
return $Text;
' LANGUAGE 'plperl';
-- Просто показывает то, что эта функция очищает.
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';
-- Просто показывает то, что эта функция очищает.
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';
-- Просто показывает то, что эта функция очищает.
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';
-- Просто показывает то, что эта функция очищает.
select clean_numeric (1111);
После того, как Вы сохранили скрипт на perl, нужно выполнить "chmod 755 Create_Functions.pl", а затем "./Create_Functions.pl". Должно получиться. Если PostgreSQL и Perl установлены правльно, Вы верно настроили базу данных, и Ваша учетная запись обладает нужными правами доступа к этой базе данных -- все должно прекрасно работать. Информация к размышлениюЯ бы хотел проверить использование TCL, Python и других языков в хранимых процедурах. Если Вы используете MySQL, а я не думаю, что в нем вообще есть хранимые процедуры, Вам следует рассмотреть переход на PostgreSQL, если предложенный стиль Вас заинтересовал. Хорошо бы создать приложение с графическим интерфейсом для создания и изменения таблиц. И наконец, примеры того, как устанавливать соединение с сервером базы данных для использования этих хранимых процедур (на Perl, Python, PHP, TCL, C etc.) тоже не помешали бы. ЗаключениеСочетание PostgreSQL и Perl потрясает. Я могу использовать Perl для трех вещей: для хранимых процедур, для установки и настройки базы данных и для создания Perl-модуля Apache, который будет соединяться с базой данных PostgreSQL. Похожих результатов можно достичь и с помощью таких языков програмирования, как Python, TCL и других. Я хочу испытать Python в тот момент, когда его использование в PostgreSQL выйдет из стадии бета-тестирования. Для изменения данных все серверы СУБД должны пользоваться исключительно хранимыми процедурами. Вы можете даже спорить, что настраиваемые хранимые процедуры следует использовать и для выборки данных. Причина, по которой это кажется таким важным связана с тем, что веб-програмисту (или другому програмисту) нет нужды знать что-либо о том, как манипулировать данными. Они просто поставляют переменные процедурам. Это позоволяет веб-программисту самому решать, каким языком програмирования ему воспользоваться, не меняя поведение базы данных. База данных и способ ее использования становятся абстракцией. Одна глупость, которую можно усмотреть в моем пользовательском Perl-скрипте, это то, что он выполняет код sql для каждой таблицы. Это очень плохо. Раньше или позже, но мне придется вернуться к этому и исправить ошибку. Вы можете поработать с моими примерами следующим образом: 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; Ссылки
Марк Нильсен [Mark Nielsen]Марк -- независимый консультант, уделяющий часть своего времени работе на GNUJobs.com, написанию статей и свободных программ и добровольной работе на eastmont.net.
|
|
Copyright (C) 2001, Mark Nielsen. Copying license http://www.linuxgazette.com/copying.html Published in Issue 69 of Linux Gazette, August 2001 |
|
Вернуться на главную страницу |