Работа с PostgreSQL через DBI в Perl

Очень доходчиво.

Оригинал тут http://www.lghost.ru/docs/postgres/DBI_pg.html

В этом небольшом руководстве я рассмотрю работу с SQL сервером PostgreSQL в языке Perl. Поскольку для Perl существует стандартный интерфейс для работы с разными СУБД, который называется DBI (Database Independent Interface), то я буду рассматривать именно его. Хотя DBI и является стандартной вещью, но всё-равно от специфики конкретных СУБД никуда не уйти, так как некоторые СУБД могут не поддерживать весь спектр возможностей заложенных в DBI. Поэтому, в данном случае, я буду говорить только о реализации DBI интерфейса, которую поддерживает DBI драйвер для PostgreSQL. Этот драйвер поставляется в виде отдельного модуля, который интегрируется с DBI и называется DBD::Pg (DataBase Driver для PostgreSQL).

Информация, которую вы найдёте в данном руководстве не является (за малыми исключениями) моим личным опытом. Я просто сделал небольшую компиляцю странички man наDBD::Pg и снабдил ряд моментов пояснениями и примерами. В принципе, если у вас нет трудностей с английским языком, а также есть желание почитать оригинал - вы всегда можете это сделать.

Ещё одно НО касается версий. Все матриалы данного руководства тестировались на Red Hat Linux 9.0 и PostgreSQL 7.3.3. На других дистрибутивах и версиях также должно работать, но не поручусь.

Как определить установлен ли DBD::Pg для DBI

Очень просто. Вот пример, который показывает установленные драйверы для DBI:

#!/usr/bin/perl
use DBI;

@drivers = DBI->available_drivers;
print "------------- drivers -----------\n";
for $i (@drivers) {
  print "$i\n";
}
print "---------------------------\n";

После запуска этой программы вы увидите список драйверов. У меня он выглядит так:

ExampleP
Pg
Proxy
mysql

Как можно заметить второй сверху Pg - значит DBD::Pg установлен.

Подключение к базе данных

Подключение к базе данных осуществляется полностью стандартным для DBI способом. Единственное, что нужно сделать - это указать правильный драйвер: в нашем случае, дляPostgreSQL. В данном примере даётся самый простой и лёгкий способ подключения к СУБД с вводом минимума параметров:

#!/usr/bin/perl

use DBI;

# имя базы данных
$dbname = "template1";
# имя пользователя
$username = "postgres";

$dbh = DBI->connect("dbi:Pg:dbname=$dbname","$username","",
      {PrintError => 0});

if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}

$dbh->disconnect();

Верояно нужно сделать несколько пояснений. Во-первых, если не указать $username, то будет подставлено имя текущего пользователя. Во-вторых, конструкция {PrintError => 0} нужна для того, чтобы DBI не выдавал ошибки на стандартный вывод в случае их возникновения (мы будет обрабатывать их сами). Вы, конечно, можете оставить вывод ошибок за DBI, но подумайте, как это будет некрасиво, особенно в CGI сценариях.

После выполнения DBI->connect мы смотрим, что у нас получилось, анализируя код ошибки в переменной $DBI::err. Если код равен нулю, то мы успешно подключились к базе, если нет, то мы выводим сам текст сообщения, который находится в переменной $DBI::errstr и завершаем работу программы с кодом возврата, который равен $DBI::err. Функция $dbh->disconnect, на которую мы попадаем только если подключение прошло успешно, закрывает соединение с базой данных.

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

Теперь мы рассмотрим функцию DBI->connect со всеми параметрами. Итак, вот ещё один пример, но уже более подробный:

#!/usr/bin/perl

use DBI;

# имя базы данных
$dbname = "template1";
# имя пользователя
$username = "postgres";
# пароль
$password = "";
# имя или IP адрес сервера
$dbhost = "localhost";
# порт
$dbport = "5432";
# опции
$dboptions = "-e";
# терминал
$dbtty = "ansi";

$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost;port=$dbport;options=$dboptions;tty=$dbtty","$username","$password",
      {PrintError => 0});

if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}

$dbh->disconnect();

Опять некоторые пояснения. Как видите параметров у DBI->connect может быть довольно много. Однако, самое интересное не в этом. Во-первых, если вы задаёте параметр host, то вы должны запустить PostgreSQL с разрешением осуществлять соединения по TCP/IP, а также не забывать про права доступа к базам, которые устанавливаются в файле pg_hba.conf, иначе, этот пример в отличие от предыдущего, работать не будет. В предыдущем примере, в силу того, чо параметр host не указан, драйвер соединяется не через TCP/IP, а через сокет, который создает при запуске PostgreSQL. Во-вторых уж если вы начали указывать прочие параметры в DBI->connect, то вы не должны оставлять пустые значения соответствующих переменных. Можете, например, попровать $dbtty="" или $dbport="" и убедиться, что DBI->connect к базе не подключается, генерируя ошибку. Если вам не нужны параметры, то вы можете их опустить, например использовав такой вид DBI->connect:

$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost;options=$dboptions","$username","$password",
    {PrintError => 0});

Вы можете опустить и параметр options, но я специально не стал этого делать, чтобы показать вам как передаются параметры для Postgres backend. Например, "-e" означает, что я хочу установить формат даты по европейскому стандарту - день впереди месяца. Полный перечень возможных параметров вы можете найти в руководстве по PostgreSQL.

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

Параметр	Переменная окружения	Значение по умолчанию
dbname	PGDATABASE	имя текущего пользователя
host	PGHOST	localhost
port	PGPORT	5432
options	PGOPTIONS
tty	PGTTY
username	PGUSER	имя текущего пользователя
password	PGPASSWORD

Итак, с подключением, отключением и обработкой ошибок подключения мы закончили. Переходим к более интересным вещам.

Запрос SELECT к базе данных

В DBI выполнение запроса SELECT может осуществляться в два этапа. На первом этапе выполняется функция prepare, а на втором этапе функция execute. Вот пример:

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=template1","postgres","",
      {PrintError => 0});

if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}

$query = "SELECT * FROM pg_tables";

$sth = $dbh->prepare($query);
$rv = $sth->execute();
if (!defined $rv) {
  print "При выполнении запроса '$query' возникла ошибка: " . $dbh->errstr . "\n";
  exit(0);
}

while (@array = $sth->fetchrow_array()) {
  foreach $i (@array) {
    print "$i\t";
  }
  print "\n";
}

$sth->finish();
$dbh->disconnect();

Снова пояснения.  Функция prepare говорит сама за себя. Она должна заниматься подготовкой запроса, однако в руководстве DBD::Pg честно написано, что PostgreSQL не поддерживает концепцию подготовки запросов и поэтому данная функция носит чисто косметическую роль - она просто записывает переданный ей запрос в некий буфер, который затем используется функцией execute. Таким образом, мы не анализируем вознкновение ошибок после выполнения prepare, потому что анализировать нечего: и синтаксическую проверку запроса и возврат ошибок в случае некорретных данных запроса осуществляет только функция execute.

Обратите внимение, что здесь мы обрабатываем ошибку по другому. Если запрос SELECT выполнился удачно, то переменная $rv содержит количество записей, которые возвратил запрос, а если во время запроса произошла ошибка, то переменная $rv будет неопределена. Сообщение об ошибке теперь берется из переменной $dbh->errstr.

После того как запрос успешно отработал, мы извлекаем данные, которые он вернул. Делать это можно несколькими способами, но в данный момент я использовал в примере функциюfetchrow_array(), которая выполняется в цикле while. Эта функция возвращает массив, в котором хранятся все поля одной записи. Далее в цикле foreach происходит вывод каждого поля записи в одной строке через табуляцию и после того как все поля закончились следует перенос строки. Цикл while закончится, когда очередной вызов fetchrow_array() вернет пустое значение undef.  И наконец вызов $sth->finish говорит, что из запроса, который был ассоциирован с переменной $sth больше не будет производится чтение данных и таким образом запрос$sth переходит в неактивное состояние. Это нужно, чтобы потом вы могли использовать в программе переменную $sth повторно с новым запросом.

У меня после выполнения примера получаются такие результаты:

pg_catalog pg_description postgres 1 0 0 
pg_catalog pg_group postgres 1 0 1 
pg_catalog pg_proc postgres 1 0 0 
pg_catalog pg_rewrite postgres 1 0 0 
pg_catalog pg_xactlock postgres 0 0 0 
pg_catalog pg_type postgres 1 0 0 
pg_catalog pg_attribute postgres 1 0 0 
pg_catalog pg_class postgres 1 0 0 
pg_catalog pg_inherits postgres 1 0 0 
pg_catalog pg_index postgres 1 0 0 
pg_catalog pg_operator postgres 1 0 0 
pg_catalog pg_opclass postgres 1 0 0 
pg_catalog pg_am postgres 1 0 0 
pg_catalog pg_amop postgres 1 0 0 
pg_catalog pg_amproc postgres 1 0 0 
pg_catalog pg_language postgres 1 0 0 
pg_catalog pg_largeobject postgres 1 0 0 
pg_catalog pg_aggregate postgres 1 0 0 
pg_catalog pg_trigger postgres 1 0 0 
pg_catalog pg_listener postgres 0 0 0 
pg_catalog pg_cast postgres 1 0 0 
pg_catalog pg_namespace postgres 1 0 0 
pg_catalog pg_shadow postgres 1 0 1 
pg_catalog pg_conversion postgres 1 0 0 
pg_catalog pg_depend postgres 1 0 0 
pg_catalog pg_attrdef postgres 1 0 0 
pg_catalog pg_constraint postgres 1 0 0 
pg_catalog pg_database postgres 1 0 0 
pg_catalog pg_statistic postgres 1 0 0 

Функция fetchrow_array хороша для тех случаев, когда нужно вывести содержимое таблицы, у которой неизвестна структура (количество полей и их названия). В других случаях можно использовать другие функции. Допустим, что мы хотим получить из таблицы pg_tables не все поля, а только два tablename и tableowner. В принципе это можно сделать слегка подправив предыдущий пример, но в этом случае, помоему, будет удобней воспользоваться функцией fetchrow_hashref. Смотрите пример:

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=template1","postgres","",
      {PrintError => 0});

if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}

$query = "SELECT * FROM pg_tables";

$sth = $dbh->prepare($query);
$rv = $sth->execute();
if (!defined $rv) {
  print "При выполнении запроса '$query' возникла ошибка: " . $dbh->errstr . "\n";
  exit(0);
}

while ($ref = $sth->fetchrow_hashref()) {
  ($tablename, $tableowner) = ($ref->{'tablename'}, $ref->{'tableowner'});
  print "$tablename\t$tableowner\n";
}

$sth->finish();
$dbh->disconnect();

Снова пояснения. Как видите, после выполнения fetchrow_hashref мы получаем хэш, который содержит в качестве ключей - имена полей в тех записях, которые нам вернул запрос, а в качестве данных - значения этих полей для одной записи. Все записи как и в предыдущем примере перебираются в цикле while, который заканчивается, когда функция fetchrow_hashrefвовзращает пустое значение undef. У меня после выполнения этого примера получаются такие результаты:

pg_description postgres
pg_group postgres
pg_proc postgres
pg_rewrite postgres
pg_xactlock postgres
pg_type postgres
pg_attribute postgres
pg_class postgres
pg_inherits postgres
pg_index postgres
pg_operator postgres
pg_opclass postgres
pg_am postgres
pg_amop postgres
pg_amproc postgres
pg_language postgres
pg_largeobject postgres
pg_aggregate postgres
pg_trigger postgres
pg_listener postgres
pg_cast postgres
pg_namespace postgres
pg_shadow postgres
pg_conversion postgres
pg_depend postgres
pg_attrdef postgres
pg_constraint postgres
pg_database postgres
pg_statistic postgres

Вот собственно наиболее популярные функции для работы с запросами вида SELECT. Конечно, этим возможности DBI не исчерпываются. Есть, например, ещё функции selectrow_array,selectall_arrayref, selectcol_arrayref и соответственно fetchrow_arrayref, fetchall_arrayref. В каких-то случаях, наверное, будет удобней воспользоваться одной из них. Тем не менее я не стану их описывать и отправляю желающих изучить к руководству man по DBI.

Запросы не возвращающие данные (INSERT, UPDATE, DELETE и т.д.)

При работе с запросами, которые не возвращают данные используется функция do, которая выполняется без prepare. Создадим таблицу tmp_tbl, состоящую из двух полей: id типа INT иname типа VARCHAR(20). Пример, который создаст эту таблицу может выглядеть так:

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=template1","postgres","",
      {PrintError => 0});

if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}

$query = "CREATE TABLE tmp_tbl (id INT, name VARCHAR(20))";

$rv = $dbh->do($query);
if (!defined $rv) {
  print "При выполнении запроса '$query' возникла ошибка: " . $dbh->errstr . "\n";
  exit(0);
}

$dbh->disconnect();

Я думаю, что этот пример в пояснениях не нуждается.

Точно также, через функцию do, можно выполнять и другие запросы, например INSERT, UPDATE или DELETE, а также SET. Я, например, первым делом после успешного подключения к базе выполнил бы запрос "SET DATESTYLE TO GERMAN", чтобы дата была в формате DD.MM.YYYY, а не как по умолчанию в формате ISO где год и месяц идут впереди числа.

Ещё можно заметить, что функция do возвращает в переменной $rv количество записей, которое обработал указанный в функции запрос. А если это невозможно (как в случае с оператором SET), то возвращается значение "-1".
Это очень важно, для определения количества записей, которые были обработаны соответствующим запросом. Например, так вы можете опрелить сколько записей было удалено запросом DELETE или сколько модифицированно оператором UPDATE.

Работа с транзакциями

Как и всякий уважающий себя SQL сервер, PostgreSQL поддерживает работу с транзакциями. Поведение DBI касательно транзакций управляется атрибутом AutoCommit. По умолчанию вDBI этот атрибут устанавливается в значение ON (включено). Это означает, что каждый запрос считается отдельной транзакцией и все изменения в базу будут заносится непосредствено в момент выполнения запроса, а такие функции как, commit и rollback будут игнорироваться с выдачей соответствующего сообщения. Однако, как показывает практика, даже приAutoCommit = ON вы можете явно задать транзакцию, с помощью функции begin_work и тогда функции commit и rollback будут работать как им и полагается, соответственно фиксируя транзакцию или отменяя её. Вот пример:

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=template1","postgres","",
      {PrintError => 0, AutoCommit => 0 });

if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}

$query = "INSERT INTO tmp_tbl VALUES (1, 'kaka')";
$dbh->begin_work();
$rv = $dbh->do($query);
$dbh->commit();
if (!defined $rv) {
  print "При выполнении запроса '$query' возникла ошибка: " . $dbh->errstr . "\n";
  exit(0);
}

$dbh->disconnect();

Как видите, при выполнении connect мы устанавливаем атрибут AutoCommit в OFF, разрешая таким образом явную работу с транзакциями. В принципе, функция begin_work здесь не нужна и добавлена исключительно благообразия ради. Функция commit завершает транзакцию и автоматически открывает новую.

Однако, если вы закомментируете вызов begin_work, а затем исправите строчку, установив AutoCommit => 1, то при выполнении программы вы увидите сообщение вида:

commit ineffective with AutoCommit enabled at ./pg8.pl line 16.

говорящее вам о том, что вызов commit не окажет никакого влияния. В данном случае это не важно, но если в программе нам понадобится заменить вызов commit на вызов rollback, то неработоспосбность rollback не позволит нам отменить транзакцию. Таким образом будьте внимательны и если не хотите заботится об установке атрибута AutoCommit, то всегда используйте функцию begin_work для обозначения начала транзакции.

Хочу также заметить, что возможность использования commit, rollback даже при использовании begin_work с AutoCommit = ON противоречит документации на DBD::Pg. В документации утверждается, что при AutoCommit = ON любые операторы begin, commit и rollback будут отвергнуты. Как видим, на практике это не так!

Заключение

Разумеется, я рассмотрел далеко не все возможности, которые предоставляет DBI. В частности не рассмотрены функции работы с большими объектами (т.е. BLOB), а также сервисные и специфические для PostgreSQL функции. Вполне возможно, что до этого руки дойдут в следущих редакциях этого документа. А желающим изучить эти возможности именно сейчас, советую почитать страницы электронного руководства man (DBI и DBD::Pg).

Виктор Вислобоков
Версия 1.0 от 10.10.2003