Автор: © Марк Нильсен [Mark Nielsen]
Перевод: © Сергей Скороходов.

1. Введение
2. Загрузка и установка Perl
3. Загрузка и установка PostrgreSQL с Perl
4. Примеры команд perl/sql
5. Настройка таблиц и хранимых процедур на pl/perl для наших операций Insert, Update и Delete
6. Хранимая процедура Insert на pl/pgsql
7. Хранимая процедура Update на pl/pgsql
8. Хранимая процедура Delete на pl/pgsql
9. Информация к размышлению
10. Убираем в Perl кое-какие запреты
11. Заключение
12. Ссылки

Введение

К версии 7.1 PostgreSQL претерпел существенное развитие. Лично я ждал улучшения в работе с объектами большого размера. В более ранних версиях имелось 32-килобайтное ограничение на размер поля в таблице. Если Вас это не устраивало, то для манипулирования "большими" объектами приходилось использовать неуклюжие приемы.

В конце концов, я решил установить в PostgreSQL Perl, потому что в самом PostrgeSQL появилось много того, что мне нравится:

1. "Настоящий" язык для хранимых процедур (PL/pgSQL).
2. Удобная работа с большими объектами.
3. Встроенные команды Perl.
4. PostgreSQL во многих отношениях напоминает Oracle, что делает разумным переход с Oracle на PostgreSQL и обратно.
5. В нем есть много дополнительных возможностей, которые мне хотелось бы иметь в сервере баз данных.
6. В сети есть общедоступная книга по PostrgeSQL. А я "торчу" от свободной документации.

В целом, процесс установки был болезненным из-за требующейся то тут то там "работы напильником". Основные этапы таковы:

1. Установка Perl 5.6.1. Годятся настройки по умолчанию за исключением двух моментов.
2. PostgreSQL устанавливается после того, как установлен Perl.
3. Установить Perl в PostgreSQL и подправить одну мелочь.

Загрузка и установка Perl

Perl обязательно нужно устанавливать перед PostgreSQL. Я не знаю, есть ли разделяемый модуль libperl в последних версиях RedHat 7.1 или Debian.

cd /usr/local/src

lynx --source http://www.tcu-inc.com/perl5.6.1.tgz >perl-5.6.1.tgz

tar -zxvf perl-5.6.1.tgz

cd perl-5.6.1

rm -f config.sh Policy.sh

sh Configure

Префикс по умолчанию надо изменить с "/usr/local" на "/usr". Кроме того, на вопрос "Build a shared libperl.so (y/n) [n] " нужно ответить "y". На все остальные вопросы просто нажимайте ENTER.

make

make install

Загрузка и установка PostgreSQL с Perl

Когда я загружал PostgreSQL, я попытался установить интерфейсы для tcl, C, python и odbc. Я не пробовал установить JAVA, но и это возможно. Кроме того, если Вы собираетесь использовать PostgreSQL в Perl, то я советую загрузить с cpan.perl.com и установить модули DBI и DBD:Pg.

В качестве процедурного языка можно использовать Tcl и Perl. Код Tcl и Perl можно выполнять непосредственно в теле команд SQL. В дополнение Вы получаете PL/pgSQL в качестве стандартного языка для написания процедур (эта связка очень похожа на pl/sql). Ниже приведены пошаговое описание моей установки PostgreSQL со "встроенным" Perl. А здесь это лежит в текстовом формате.

### Сначала надо собрать Perl в виде динамически загружаемого модуля. 

### Если этого не сделать, то установке postgresql ничто не помешает,

### но интерфейса plperl не будет. 

cd /usr/local/src

lynx --source ftp://postgresql.readysetnet.com/pub/postgresql/v7.1.1/postgresql-7.1.1.tar.gz > postgresql-7.1.1.tar.gz

tar -zxvf postgresql-7.1.1.tar.gz

cd postgresql-7.1.1

### Нужно установить некоторые переменные окружения -- и поместить их

#### в ~/.profile чтобы postgres мог ими воспользоваться. 

PATH=/usr/local/pg711/bin:$PATH

export PATH

export LD_LIBRARY_PATH=/usr/local/pg711/lib

export PGDATA=/usr/local/pg711/data

export PGLIB=/usr/local/pg711/lib

export POSTGRES_HOME=/usr/local/pg711

═ ### Этот скрипт настроен на удаление любой предшествующей установки. 

══### Я сделал это для того, чтобы была возможность отладки, если все не заработает сразу.

═ #### Не обращайте внимание на сообщения о том, что не загружен сервер СУБД,

═ ### вероятно, у Вас его просто нет.

su -c '/usr/local/pg711/bin/initdb -D /usr/local/pg711/data -l logfile stop' postgres

═ ### Не обращайте внимания на сообщения, что такой пользователь уже имеется.

adduser postgres

rm -rvf /usr/local/pg711

═ ### А теперь создадим директорию и пусть postgres будет ее владельцем. 

mkdir /usr/local/pg711 

chown postgres /usr/local/pg711

═ ### Любые ошибки make clean можно проигнорировать. 

make clean

═ ### Соберите и установите postgresql.

./configure --prefix=/usr/local/pg711 --with-perl --with-tcl --with-CXX --with-python --enable-odbc 

make

make install

═ ### Теперь нужно установить интерфейс perl для postgresql.

gmake -C src/interfaces/perl5 install

cd /usr/local/src/postgresql-7.1.1/src/interfaces/perl5

perl Makefile.PL

make 

══### Если хотите протестировать, что получилось -- раскоментируйте следующую строку. 

##═ su -c 'make test' postgres

make install

═ ### Владельцем всех файлов должен быть пользователь postgres.

chown -R postgres /usr/local/pg711

═ ### Инициализация базы данных. 

su -c '/usr/local/pg711/bin/initdb -D /usr/local/pg711/data' postgres

═ ### Запуск сервера. 

su -c '/usr/local/pg711/bin/pg_ctl -D /usr/local/pg711/data -l logfile start' postgres

═ ### Интерфейсы для perl, tcl, и pl/pgsql уже должны быть созданы. 

══### Пора их добавить. 

══

su -c 'createlang plpgsql template1' postgres

su -c 'createlang pltcl template1' postgres

### А теперь, полагая, что Perl 5.6.1 установлен правильно...

rm -f /usr/local/pg711/lib/libperl.so

ln -s /usr/lib/perl5/5.6.1/i686-linux/CORE/libperl.so \

═ /usr/local/pg711/lib/libperl.so

su -c 'createlang plperl template1' postgres

═ ### Если все отработало без ошибок, новые базы данных будут создаваться

═ ### из шаблона с подключенными perl, tcl и pl/pgsql. 

═ ### И кое-что дополнительное.

su -c 'createdb postgres' postgres

В домашней директории пользователя postgres надо создать файл ".profile" со следующим содержимым:

#!/usr/bin

PATH=/usr/local/pg711/bin:$PATH

export PATH

export LD_LIBRARY_PATH=/usr/local/pg711/lib

export PGDATA=/usr/local/pg711/data

export PGLIB=/usr/local/pg711/lib

export POSTGRES_HOME=/usr/local/pg711

И наконец:

chmod 755 .profile

Примеры команд perl/sql

Выполните команды с этой страницы http://www.ca.postgresql.org/users-lounge/docs/7.1/programmer/plperl-use.html

Поскольку с "моей подачи" Вы создали базу данных "postgres", все, что надо сделать для того, чтобы запустить интерфейс psql, это выполнить от root'а:

su -l postgres

psql

Это в предположении, что .profile для пользователя postgres настроен верно. Если нет, то нужно сделать следующее:

su -l postgres

PATH=/usr/local/pg711/bin:$PATH

export PATH

export LD_LIBRARY_PATH=/usr/local/pg711/lib

export PGDATA=/usr/local/pg711/data

export PGLIB=/usr/local/pg711/lib

export POSTGRES_HOME=/usr/local/pg711

psql

Следующая функция позволяет провести поиск в данных и возвращает копию имени, если имя содержит искомый текст (без учета регистра).

drop function search_name(employee,text,integer);

CREATE FUNCTION search_name(employee,text,integer) RETURNS text AS '

═══ my $emp = shift;

═══ my $Text = shift;

═══ my $Case = shift;

═══ if (($Case > 0) && ($emp->{''name''} =~ /\\Q$Text\\E/i)) 

══════{ return $emp->{''name''}; }

═══ elsif ($Case > 0) {return "";}

═══ elsif ($emp->{''name''} =~ /\\Q$Text\\E/) 

═══════{═══ return $emp->{''name''}; }

═══ else { return "";}

' LANGUAGE 'plperl';

insert into EMPLOYEE values ('John Doe',10000,1);

insert into EMPLOYEE values ('Jane Doe',10000,1);

insert into EMPLOYEE values ('Giny Majiny',10000,1);

select name,search_name(employee,'j',0) from employee;

select name,search_name(employee,'j',1) from employee;

select name from employee where search_name(employee,'j',1) = name;

select name from employee where search_name(employee,'j',0) = name;

Само собой, пример нелепый. Нужно возвращать 0 при неудаче или 1, если поиск удался. Я заставил функцию возвращать копию имени только для наглядности.

Настройка таблиц и хранимых процедур на pl/perl для наших операций Insert, Update и Delete

Копия команд SQL в виде простого текста находится в файле: SQL_setup.txt. Вам предстоит следующее:

1. Создать хранимые процедуры insert, update, и delete, которые будут создавать резервную копию изменяемых данных в специальной таблице истории или резервного копирования. Записываться будет все, что происходит. Требуется разумная проверка ошибок. Проверки можно усовершенствовать, но тогда написанная мной процедура стала бы слишком большой.
2. Использовать код на Perl для придания заносимым в таблицы данным аккуратного вида. Конечно, можно было бы использовать sql, но Perl я понимаю лучше.

Не думаю, что можно выполнять операторы insert, update, delete или select в теле процедур на Perl. Все, что мне удалось с помощью Perl'а -- это принимать несколько значений и возвращать одно значение. Вам никогда не следует прибегать к выполнению sql из Perl. Perl используется для изменения данных, как фильтр или для проверки ошибок, а не для вызова операторов SQL. Командами SQL следует управлять из pl/pgsql. Ограничьте использование Perl'а модификацией данных и не делайте ничего, напрямую связанного с базой данных.

Ниже я создаю три таблицы: jobs, jobs_backup и contact. Две процедуры на Perl предназначены для проверки введенных данных, удаления "непечатных" символов и лишних пробелов. Для настоящих команд insert, update и delete используется pl/pgsql.

С помощью этого базового метода работы с данными, их можно реплицировать в любую другую таблицу.

В некоторых случаях мне пришлось соблюдать осторожность, поскольку мне хотелось, чтобы названия должностей были уникальными. Мне не хотелось, чтобы два предложения работы от одного агента имели одинаковые имена. Потребовались некоторые ухищрения, но все получилось.

Для того, чтобы предотвратить появление в таблице 'jobs' значений contact_id, отсутствующих в таблице 'contact', можно было бы воспользоваться ограничением по внешнему ключу. Но и в этом случае все можно сломать, если случайно удалить строки contact_id из таблицы contact. Лучшее решение -- добавить в таблицы "jobs" и "contact" столбцы "active", и с их помощью "включать" и "выключать" объекты. В этом случае ни один уникальный идентификатор не будет когда-либо удален.

 --- Создание таблицы jobs. 

═--- ограничение по внешнему ключу из таблицы contact

 --- было бы здравым предложением.

create sequence Job_Sequence;

drop table jobs;

create table jobs (

job_id int4 unique DEFAULT nextval('Job_Sequence'),

contact_id int4,

job_no int4,

job_name═ text,

job_location text 

);

CREATE UNIQUE INDEX job_index ON jobs (job_name, contact_id);

-- А это настоящая таблица для резервного копирования. 

-- Все изменения должны добавляться именно в эту таблицу. 

-- Тут нет места удалению, только вставке и обновлению. 

-- На самом деле это не просто резервная копия, а настоящая "историческая" таблица.

-- Мы записываем даже окончательный вывод. 

create sequence Backup_Job_Sequence;

drop table jobs_backup;

create table jobs_backup (

backup_id int4 unique DEFAULT nextval('Backup_Job_Sequence'),

action text CHECK (action in ('insert','update','delete','')),

error_code int4,

job_id int4,

contact_id int4,

job_no int4,

job_name═ text,

job_location text

);

create sequence Contact_Sequence;

drop table contact;

create table contact (

contact_id int4═ UNIQUE DEFAULT nextval('Contact_Sequence'),

name text unique,

phone text,

website text

);

 --- Добавим два контакта в таблицу агентов по найму.

 --- Для этой таблицы я не стал делать хранимых процедур. 

insert into contact (name,phone,website) 

══values ('Mark Nielsen','(408) 891-6485','http://www.gnujobs.com');

insert into contact (name,phone,website)

═ values ('Joe Shmoe','(1234) 111-1111','http://www.gnujobs.net');

insert into contact (name,phone,website)

═ values ('Lolix.org','(12345) 111-1111','http://www.lolix.org');

 --- Посмотрим, что получилось.

select * from contact;

 --- Воспользуемся созданной функцией на Perl (без особой на то нужды),

 --- которая будет проверять, что вводимые данные не пусты. 

drop function job_values_verify (int4,text,text);

CREATE FUNCTION═ job_values_verify (int4,text,text) RETURNS int4 AS '

═══ my $Contact_Id = shift;

═══ my $Job_Name = shift;

═══ my $Job_Description = shift;

═══ my $Error = 0;

═══ if ($Contact_Id < 1) {$Error = -100;}

═══ if (!($Job_Name =~ /[a-z0-9]/i)) {$Error = -101;}

═══ if (!($Job_Description =~ /[a-z0-9]/i)) {$Error = -102;}

═ return $Error;

' LANGUAGE 'plperl';

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═════ !@#$%^&*()_+| ');

--

Хранимая процедура Insert на pl/pgsql

Исходный текст программы на SQL можно взять в файле SQL_insert.txt.

drop function insert_job (int4,text,text);

CREATE FUNCTION insert_job (int4,text,text) RETURNS int2 AS '

DECLARE

═══ c_id_ins int4; j_name_ins text;═ l_ins text; 

════job_id1 int4; oid1 int4; test_id int4 := 0; j_no_ins int4 := 0;

═══ record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD;

BEGIN

══ j_name_ins := $2; l_ins═ := $3; c_id_ins := $1;

════ -- Теперь выполним несколько процедур на Perl. Это просто примеры

════ -- Perl-процедур.

════ -- Приведем в порядок название предлагаемой должности.

══ SELECT INTO record4 clean_text(j_name_ins) as text1;

══ j_name_ins = record4.text1;

════ -- Приведем в порядок местоположение предлагаемой работы.

══ SELECT INTO record4 clean_text(l_ins) as text1;

══ l_ins = record4.text1;

════ -- Проверка правильности введенных значений.

══ SELECT INTO record4 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;

══ IF record4.no < 0 THEN return (record3.no); END IF;

════ -- Проверка уникальности имен, в противном случае возвращаем 0.

══ FOR record1 IN SELECT job_id FROM jobs═ 

══════where contact_id = c_id_ins and job_name = j_name_ins

═════ LOOP

═════ test_id := record1.job_id;

══ END LOOP;

════ -- Если в результате job_id == null, отлично,

═══════  -- в противном случае прерываемся и возвращаем -1;

══ IF test_id > 0 THEN return (-1); END IF;

══ FOR record3 IN SELECT max(job_no) from jobs_backup where contact_id = c_id_ins

═════ LOOP

═════ IF record3.max IS NULL THEN j_no_ins := 0; END IF;

═════ IF record3.max > -1 THEN j_no_ins = record3.max + 1; END IF;

══ END LOOP;

════ -- Собственно вставка. Значение job_id определяется порядком вставки.

══ insert into jobs (contact_id, job_no, job_name, job_location)

═══════ values (c_id_ins, j_no_ins, j_name_ins, l_ins);

════ -- Получим уникальный oid только что вставленной строки. 

═══GET DIAGNOSTICS oid1 = RESULT_OID;

════ -- Получаем job_id. Не следует использовать SELECT INTO,

═══════  -- т.к. надо присвоить значение из record2.

══ FOR record2 IN SELECT job_id FROM jobs where oid = oid1

═════ LOOP

═════ job_id1 := record2.job_id;

══ END LOOP;

══ 

═════-- Если job_id1 == NULL, то вставка не удалась или еще какая бяка стряслась.

══ IF job_id1 is NULL THEN return (-2); END IF;

════ -- Если ничего не случилось, то должно быть больше 0.

══ IF job_id1 < 1 THEN return (-3); END IF;

════ -- Получилось, возвращаем job_id1 как job_id.

══ insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code)

═══════ values (c_id_ins, j_no_ins, j_name_ins, l_ins, ''insert'', job_id1);

══ return (job_id1);

END;

' LANGUAGE 'plpgsql';

select insert_job (1,'Job Title 1','Boston, MA');

select insert_job (1,'Job Title 2','San Jose, CA');

select insert_job (2,'Job Title 1','Columbus, Ohio');

select insert_job (2,'Job Title 2','Houston, TX');

select insert_job (3,'Job Title 1','Denver, CO');

select insert_job (3,'Job Title 2','New York, NT');

select * from jobs;

Хранимая процедура Update на pl/pgsq

Исходный текст программы на SQL находится в файле SQL_update.txt. Процедура update должна проверить, есть ли в таблице предложение работы с таким же именем как то, на которое мы хотим изменить название текущего предложения работы. Мы не хотим производить какие-либо изменения (кроме случая, когда job_id совпадают), если такое предложение работы имеется. Помните о требовании уникальности на название предложений работы для каждого агента

drop function update_job (int4,text,text,int4);

CREATE FUNCTION update_job (int4,text,text,int4) RETURNS int2 AS '

DECLARE

═══ c_id_ins int4; j_name_ins text;═ l_ins text; 

════job_id1 ALIAS FOR $4; oid1 int4; test_id int4 := 0;

═══ record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD; record5 RECORD;═ 

════return_int4 int4 := 0; job_no1 int4 := 0;

BEGIN

══ j_name_ins := $2; l_ins═ := $3; c_id_ins := $1;

════ -- немного кода на Perl. 

═════-- приведем в порядок имя предложения работы.

══ SELECT INTO record4 clean_text(j_name_ins) as text1;

══ j_name_ins = record4.text1;

════ -- Приведем в порядок местоположение работы. 

═══SELECT INTO record5 clean_text(l_ins) as text1;

══ l_ins = record5.text1;

════ -- Проверка значений, которые мы хотим занести в базу данных.

══ SELECT INTO record3 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;

══ IF record3.no < 0 THEN return (record3.no); END IF;

════ -- Посмотрим, нет ли предложения работы с таким же именем

════ -- у того же агента по найму.

══ FOR record1 IN SELECT job_id FROM jobs═ 

══════where contact_id = c_id_ins and job_name = j_name_ins

═══════ and job_id != job_id1

═════ LOOP

═════ test_id := record1.job_id;

══ END LOOP;

════ -- Если job_id == null, то все отлично, иначе возвращаем -1;

══ IF test_id > 0 THEN return (-1); END IF;

════ -- Проверим, что такое предложение работы существует, иначе возвращаем -2.

══ FOR record1 IN SELECT * FROM jobs where job_id = job_id1═ 

══════LOOP

═════ update jobs set contact_id = c_id_ins,═ 

════════job_name = j_name_ins, job_location = l_ins

═══════ where job_id = job_id1;

═════ GET DIAGNOSTICS return_int4 = ROW_COUNT;

═════ test_id := 1;

═════ job_no1 := record1.job_no;

══ END LOOP;

════ -- Если такого предложения работы нет, то что мы собираемся обновлять?

═══════  -- Возвращаем код ошибки. 

═══IF test_id = 0 THEN return (-2); END IF;

════ -- теперь, когда все прошло, возвращаем return_int4.

══ insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code, job_id)

═══════ values (c_id_ins, job_no1, j_name_ins, l_ins, ''update'', return_int4, job_id1);

══ return (return_int4);

END;

' LANGUAGE 'plpgsql';

select update_job (3,'Изменяем название и владельца.','Boston, MA',1);

select * from jobs;

═ -- А здесь вы должны получить ошибку, потому что и название предложения работы,

═ -- и агент по найму, ее предлагающий, повторяются. 

select update_job (3,'Changing title and owner.','Boston, MA',1);

Хранимая процедура Delete на pl/pgsql

Исходный код на SQL можно найти в файле SQL_delete.txt.

drop function delete_job (int4);

CREATE FUNCTION delete_job (int4) RETURNS int2 AS '

DECLARE

═══ job_id1 ALIAS FOR $1;

═══ job_exists int4 := 0;

═══ job_backup_exists int4 := 0;

═══ record1 RECORD; 

════return_int4 int4 :=0;

BEGIN

════ -- Если job_id1 меньше 0, то возвращаем ошибку.

══ IF job_id1 < 1 THEN return -1; END IF;

════ -- Если мы находим такое предложение работы, то:

═══════  -- удаляем его, записываем, что мы ее нашли и делаем резервную копию. 

═════-- Я не люблю использовать LOOP для одной строки, но тут на это есть причина.

══ FOR record1 IN SELECT * FROM jobs where job_id = job_id1

═════ LOOP

═════ delete from jobs where job_id = job_id1;═ 

══════GET DIAGNOSTICS return_int4 = ROW_COUNT;══════ 

══════job_exists := 1;

═════ insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code, job_id)

═══════ values (record1.contact_id, record1.job_no, record1.job_name, 

═══════ ══record1.job_location, ''delete'', return_int4, record1.job_id);

══ END LOOP;

════ -- Если job_exists == 0 возвращаем ошибку.

════ -- Это значит, что такой записи никогда не существовало. 

═══IF job_exists = 0 THEN return (-1); END IF;

════ -- Уж коли мы сюда попали, то все правильно, возвращаем ROW_COUNT.══ 

═══return (return_int4);

END;

' LANGUAGE 'plpgsql';

select delete_job (1);

select * from jobs;

═ --- Запись удалена, так что теперь мы должны получить ошибку. 

select delete_job (1);

Информация к размышлению

Если вы установили Perl 5.6.1, то нужно проверить, что случилось с mod_perl или другими модулями perl, если Вы сами устанавливали их раньше. Установка Perl 5.6.1 может разрушить работу модулей, которые Вы раньше использовали с другой версией Perl. Я не уверен, но будьте осторожны. Возможно, модули придется пересобрать.

Вот список того, о чем стоит подумать:

1. Задайте ограничение целостности по внешнему ключу, это гарантирует, что contact_id предложения о работе уже имеются в таблице агентов по найму.
2. Никогда не удаляйте строки из таблиц 'contact' и 'job'. Вместо этого, добавьте поле и отмечайте в нем, что запись неактивна. Поле можно назвать 'active', значение 0 будет означать неактивную запись, а 1 -- активную.
3. Процедуры insert и update можно объединить в одну. Если Ваши данные всегда верны на все 100%, то используйте процедуру update, и, если она не находит искомого предложения о работе, то она просто добавит запись. Иногда это может быть полезным.
4. Добавляйте проверку ошибок всегда, когда это можно сделать. Хотя те проверки, которые я делаю в коде, достаточно разумны, можно сделать большее.
5. Даже не думайте выполнять команды SQL в теле процедур на Perl. Используйте их только для обработки данных.
6. Для одновременного использования Perl и SQL используйте pl/pgsql.
7. Процедуры должны быть настроены так, чтобы был возможен откат в случае, когда insert, update или delete по неясной причине натолкнуться на ошибку.
8. Я не знаю, сколько памяти поглощают процедуры на Perl, и я не знаю, как эта память освобождается после того, как Perl-процедура завершается. Далее, я не имею понятия, насколько больше ресурсов потребует включение кода на Perl. Для моих целей, процедуры на pl/pgsql всегда выполняются быстрее, чем команды SQL, вызываемые из скриптов Perl на веб-сервере. Так что, поскольку направление в целом верное, то я склонен мириться с дополнительными затратами на процедуры Perl. Кроме того, я, вероятно, смогу сжать очень сложный код SQL в несколько строк на Perl. И я вижу значительную силу, если удается найти верный баланс в использовании pl/pgsql, стандартного SQL и pl/perl.

Убираем в Perl кое-какие запреты

То, что я собираюсь сделать -- очень нехорошо. Это снизит правила безопасности, применяемые в Perl для того, чтобы расширить возможности.

Во-первых, этот метод нужно добавить сразу после метода "permit" в Safe.pm. Мой Safe.pm был здесь: /usr/local/src/perl-5.6.1/lib/Safe.pm. Изменение модуля, который создали не Вы, означает, что все изменения будут потеряны, если Вы его обновите. Еще раз, внося такие изменения, Вы МОЖЕТЕ ПОЛОМАТЬ ПРОГРАММУ СВОЕГО ПРИЯТЕЛЯ, КОТОРЫЙ РАБОТАЕТ НА ТОЙ ЖЕ МАШИНЕ. И еще раз, я делаю гадкие вещи, которые, вообще-то, не следует делать.

sub permit_all {

═══ my $obj = shift;

═══ $obj->{Mask} = invert_opset full_opset;

}

Во-вторых, остановите Ваш сервер СУБД.

И третье, пересоберите plperl, сделав некоторые изменения.

Сделайте в файле plperl.c следующие изменения. Отсюда...

═══════════════ "require Safe; SPI::bootstrap();"

═══════════════ "sub ::mksafefunc { my $x = new Safe; $x->permit_only(':default');$x->permit(':base_math');"

═══════════════ "$x->share(qw[&elog &DEBUG &NOTICE &NOIND &ERROR]);"

═══════════════ " return $x->reval(qq[sub { $_[0] }]); }"

... и досюда (собственно код можно найти в файле New_plperl.txt)

═══════════════ "require Safe; SPI::bootstrap();"

═══════════════ "sub ::mksafefunc { my $x = new Safe; $x->permit_only(':default');$x->permit(':base_math');"

 "$x->permit_all('');"

═══════════════ "$x->share(qw[&elog &DEBUG &NOTICE &NOIND &ERROR]);"

═══════════════ " return $x->reval(qq[sub { $_[0] }]); }"

Теперь пересоберите plperl и установите его.

cd /usr/local/src/postgresql-7.1.1/src/pl/plperl

rm -f *.o

make 

make install

Четвертое, перезапустите postgresql.

Проверьте, возможен ли выход в shell,

drop function ls_bad ();

CREATE FUNCTION═ ls_bad () RETURNS text AS '

my @Temp = `ls /tmp`;

my $List = "@Temp";

$List =~ s/\n/ /g;

═ return $List;

' LANGUAGE 'plperl';

select ls_bad();

Если Вы получаете содержимое директории "/tmp", то выход в shell удался. Это очень опасно.

Я в течение целого дня пытался найти способ заставить DynaLoader работать в pl/perl. Главное, я прочел документацию по встраиванию Perl в C, и это оказалось не слишком сложно. Про это даже есть страница man. Я продолжаю исследовать проблему. Последний раз я попытался совсем отказаться от использования модуля Safe.pm, но мне не удалось продвинуться особенно далеко. Я был готов "вкомпилировать" DynaLoader в plperl, но сдался. Угробив день, я хотел бы, чтобы кто-нибудь еще попытался это сделать.

Если у Вас получиться заставить DynaLoader нормально работать с plperl, или, если точнее, сделать так, чтобы я мог загружать из plperl любой модуль по своему желанию, пожалуйста, дайте мне знать. Я могу загружать любой pm модуль, но не модули, имеющие C-компоненты. А мне хотелось бы иметь возможность загружать какие угодно модули. Уверен, что нужно прекратить прибегать к Safe.pm для того, чтобы решать эти проблемы. Пошлите мне письмо по адресу [email protected]. Мне будет очень интересно, если у Вас получиться!

Пожалуйста, не делайте этого. Я просто хотел показать, как можно обойти связанные с безопасностью рогатки, если Вам и вправду этого хочется.

Заключение

Связка Perl и PL/PGSQL -- это КРУТО. Почему?

1. Мне нравится использовать Perl (или любой другой язык -- может надо сделать интерфейс для Python?) для обработки данных, потому что SQL иногда превращается в настоящий геморрой (в вопросах обработки данных).
2. Сочетание Perl и PL/PGSQL позволяет сделать большую часть работы на стороне сервера базы данных, что означает меньше программирования на стороне клиента. Например, у Вас есть веб-сервер, связанный с сервером базы данных. Если сервер базы данных делает значительную часть работы Perl, Ваши Perl-скрипты на веб-сервере будут уже не такими большими. Так всегда бывает при использовании хранимых процедур. Не приятно ли, когда одна хранимая процедура делает для Вас все, вместо того, чтобы делать эту работу по шагам в скриптах на веб-сервере?
3. Если большую часть замысловатого Perl-программирования выполнить на стороне сервера базы данных, то меньше надо будет сделать на том языке, который Вы выберете для связи с БД. Хранимые процедуры превратятся в объекты, и Вашим веб-программистам потребуется лишь знать, как ими пользоваться, а не как они работают. Это мило.
4. Я собираюсь постепенно использовать все больше процедур на Perl (там, где это уместно) и проверить стабильность pl/perl.
5. Мне хотелось бы иметь возможность загружать из plperl любой модуль, просто для забавы. Если Вам удастся понять, как это сделать, пожалуйста пришлите мне письмо на [email protected].

PostgreSQL -- это самый крутой сервер баз данных, с каким мне когда-либо приходилось работать. MySQL на втором месте с небольшим отрывом. Я никогда не получал настоящего удовольствия от работы с какой-либо коммерческой СУБД. В PostgreSQL заключен такой потенциал, что я прямо вижу, как коммерческие серверы баз данных идут в том же направлении, что и PostgreSQL. Как было бы хорошо, если кто-нибудь бы сделал интерфейс для Python, чтобы было можно писать хранимые процедуры на этом языке. Глава 20 последнего издания "Programming Python" (ISBN: 0-596-00085-5) обсуждает встраивание Python в C. Когда у меня будет время, может быть в следующем году, я может быть и попробую, если кто-нибудь не пристукнет меня до той поры! Я уверен, что возможность писать хранимые процедуры на нескольких языках станет очень ценной в будущем. Еще я бы очень обрадовался возможность возвращать из процедур более одного значения. То, что мы можем возвратить только одно значение -- очень раздражает. Я пытался сконструировать функцию, возвращающую более одного значения, но из этого ничего не вышло. Я пытался заставить процедуру возвращать RECORD, но тоже не преуспел.

Ссылки

1. Языки написания хранимых процедур
2. PostgreSQL: Введение и концепции
3. Последняя статья: http://www.newbienetwork.net/sections.php?op=viewarticle&artid=25
4. Изменения в этой статье всегда можно найти здесь: http://www.gnujobs.com/Articles/20/Perl_PostgreSQL.html

Марк работает независимым консультантом, добровольно работает в проектах наподобие GNUJobs.com, пишет статьи, свободные программы и работает добровольцем в проекте eastmont.net.

Марк Нильсен [Mark Nielsen]

Марк работает на ZING (www.genericbooks.com) и GNUJobs.com. До этого Марк основал сайт The Computer Underground (Компьтерное подполье). Он работает для некоммерческих и основанных на добровольном участии [non-profit and volunteer] проктах, направленных на распространение открытой и бесплатной литературы и программ. На жизнь зарабатывает поиском людей для работы в различных связанных с GNU проектах, а также консультированием и разработкой в области веб-приложений и баз данных с применением Linux, FreeBSD, Apache, Zope, Perl, Python и PostgreSQL.