четверг, 24 мая 2012 г.

Работа с 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. Вот табличка соответствия параметров и переменных окружения, а также значение по умолчанию, которое присваивается параметру, если его значение не задано явно.

ПараметрПеременная окруженияЗначение по умолчанию
dbnamePGDATABASEимя текущего пользователя
hostPGHOSTlocalhost
portPGPORT5432
optionsPGOPTIONS
ttyPGTTY
usernamePGUSERимя текущего пользователя
passwordPGPASSWORD

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

Запрос 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_arrayrefselectcol_arrayref и соответственно fetchrow_arrayreffetchall_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 для обозначения начала транзакции.
Хочу также заметить, что возможность использования commitrollback даже при использовании begin_work с AutoCommit ON противоречит документации на DBD::Pg. В документации утверждается, что при AutoCommit = ON любые операторы begincommit и rollback будут отвергнуты. Как видим, на практике это не так!

Заключение

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


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



Комментариев нет:

Отправить комментарий