Поиск 

Совместное использование Perl и PostgreSQL

Вторник, Январь 25, 2011 г.

Загрузка и установка 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.... > 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

Поскольку с "моей подачи" Вы создали базу данных "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

Вам предстоит следующее:

  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

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;

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

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

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

Источник: postgresql.ru.net
Теги: PERL Автор: Tais | Просмотров: 3451 | Нет комментариев | print |

Похожие статьи

все похожие статьи 
Категории
ТОП 10 - Авторы
  1     Луна   1964     2.94   
  2     pobeda   487     2.96   
  3     Tais   444     3.11   
  4     Foma   139     2.92   
  5     Lubov   52     2.91   
  6     Angel   45     2.92   
  7     Dolores   45     2.77   
  8     Paradiz   31     2.82   
  9     Xenta   29     2.85   
  10     Pryanik   26     2.8   
все авторы 
Последние статьи

Вода

Среда, Январь 24, 2018 г.
|
Луна | 744 |

Фруктовые соки

Среда, Январь 24, 2018 г.
|
Луна | 625 |

Вода и жизнь

Среда, Январь 24, 2018 г.
|
Луна | 1165 |

Торт "Пьяная вишня"

Среда, Январь 24, 2018 г.
|
Луна | 1228 |

Голубцы с грибами

Среда, Январь 24, 2018 г.
|
Луна | 1197 |
Популярные статьи

Эфиопия

Четверг, Февраль 17, 2011 г.
|
Луна | 8701 |

История Древнего Египта

Вторник, Апрель 05, 2011 г.
|
Луна | 246469 |

Степи и Лесостепи

Вторник, Май 11, 2010 г.
|
Tais | 89470 |

Стихи Тютчева

Воскресенье, Январь 23, 2011 г.
|
Луна | 370747 |
|
Tais | 56323 |

Облако тегов