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

Как определить установлен ли DBD::Pg для 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

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

#!/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});

Запрос 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).