Журнал LinuxFormat - перейти на главную

LXF89:PostgreSQL

Материал из Linuxformat
Перейти к: навигация, поиск
Подписка на печатную версию
Весь 2015 год (12 номеров)
Первое полугодие (6 номеров)
Второе полугодие (6 номеров)
Подписка на электронную версию
Весь 2015 год (12 номеров)
Первое полугодие (6 номеров)
Второе полугодие (6 номеров)
Подшивки старых номеров журнала (печатные версии)
Весь 2014 год (12 номеров)
Первое полугодие (6 номеров)
Второе полугодие (6 номеров)

История PostgreSQL Изучаем самый мощный из открытых серверов баз данных

Содержание

Интерфейсы

ЧАСТЬ 4: Интерфейсы, как известно, бывают не только пользовательскими, но и программными. С первыми мы уже успели познакомиться, а сегодня Евгений Балдин расскажет, какие API существуют для доступа к PostgreSQL из различных языков программирования.

В институте им очень дорожили, так как
попутно он использовался для некоторых
уникальных экспериментов и как переводчик
при общении со Змеем Горынычем.
(О Кощее Бессмертном)
«Понедельник начинается в субботу»

Понятно, что любую базу данных, в принципе, можно заполнить вручную; правда, некоторые придется заполнять очень долго. СУБД – это просто хранилище, а для заполнения и доступа к хранилищу необходима инфраструктура, и эту инфраструктуру надо создавать. Вот такая она – жизнь.

Родной библиотекой для доступа к PostgreSQL является libpq. Написана она на чистом C, что тоже не удивительно, так как это основной язык родной системы. Все остальные языки важны, но безусловно, вторичны.


libpq

Чтобы общаться с базой данных, много функций не требуется: одна для открытия соединения, одна для посылки запроса, одна для получения ответа и одна для закрытия соединения. В реальности, конечно, все немного сложнее, но суть остается неизменной.

К вопросу о переносимости

Библиотека libpq написана на чистом C, поэтому связь с PostgreSQL можно организовать практически везде, где можно найти gcc. Мне как-то пришлось делать это для VAX/VMS – все решилось методом тыка, даже думать почти не потребовалось. Все данные – текст, поэтому вопрос бинарной совместимости платформ попросту отсутствует.

С чего начать

Чтобы воспользоваться вызовами libpq, ее необходимо иметь в системе. В Debian (Sarge) для этого надо установить пакет PostgreSQL-dev: 

> sudo apt-get install postgresql-dev

Для доступа к функциям libpq необходимо включить в исходный текст соответствующие заголовки: 

#include “libpq-fe.h”

Скрипт pg_config (man pg_config) позволяет получить информацию о том, куда помещаются include-файлы, библиотеки и тому подобное. Для сборки можно также использовать скрипт libpq3-config (man libpq3-config – годится, естественно, только для libpq третьей версии), который заведомо есть в Debian (Sarge): 

> #сборка программы
> gcc -o “бинарник” “исходник”.c -I`pg_config --includedir` \
-lpq `/usr/bin/libpq3-config`
> cat /usr/bin/libpq3-config
#!/bin/bash
echo -lssl -lcrypto -lkrb5 -lcrypt -lresolv -lnsl -lpthread

libpq3-config просто выводит список всех библиотек, от которых зависит libpq.


Открытие и закрытие соединения

Даже открывать соединение с PostgreSQL можно двумя способами: 

//открыть соединение
PGconn *PQconnectdb(const char *conninfo);
//то же, но не блокируя программу
PGconn *PQconnectStart(const char *conninfo);
//проверка статуса соединения (после PQconnectStart)
PostgresPollingStatusType PQconnectPoll(PGconn *conn);

PQconnectdb – обычная функция, принимающая текстовую строку conninfo, содержащую параметры для соединения с сервером, и возвращающая структуру типа PGconn с информацией о созданном соединении и успешности данной операции. В дальнейшем при передаче данных эта структура будет использоваться в качестве параметра.

Передача информации о сервере в качестве строки (conninfo) позволяет не менять внешний интерфейс вызова в случае появления дополнительных параметров и легко добавлять дополнительные опции. Пример открытия соединения: 

const char *conninfo= “dbname = test host=localhost”;
PGconn *conn=PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK) {
fprintf(stderr, “Не удалось соединиться с базой данных: %s”,
PQerrorMessage(conn));
/*завершаем работу*/ …}

Параметры передаются в форме «ключевое слово»=«значение». Пары разделяются обычным пробелом. Пробелы вокруг знака равенства можно опустить. Если необходимо передать значение с пробелами, то его необходимо заключить в одинарные кавычки ‘«составное» «значение»’. Для передачи одинарной кавычки ее необходимо экранировать с помощью обратной косой черты \’. При отсутствии какого-либо параметра в строке conninfo его значение берется из соответствующей переменной окружения, если таковая определена. Если нет, то при открытии соединения используется значение по умолчанию.

Функции открытия соединения распознают следующие параметры и переменные окружения (кое-какие особенности опущены):

  • host DNS-имя узла, на котором находится сервер PostgreSQL. Соответствует переменной окружения PGHOST. Значение по умолчанию: localhost.
  • hostaddr Числовой адрес узла, на котором находится PostgreSQL (альтернатива host). Соответствует переменной окружения PGHOSTADDR. Значение по умолчанию эквивалентно: localhost.
  • port Номер порта, который «слушает» POSTMASTER. Соответствует переменной окружения PGPORT. Значение по умолчанию обычно 5432.
  • dbname Имя базы данных. Соответствует переменной окружения PGDATABASE. Значение по умолчанию совпадает с системной учетной записью пользователя.
  • user Имя пользователя базы данных. Соответствует переменной окружения PGUSER. Значение по умолчанию совпадает с системной учетной записью пользователя.
  • password Поле пароля, если для аутентификации требуется пароль. Соответствует переменной окружения PGPASSWORD. Если аутентификация требуется, а поле не определено, то для доступа используются данные файла ~/.pgpass. Переменная окружения PGPASSFILE может указать другой файл для проведения аутентификации.
  • connect_timeout Устанавливает максимальное время ожидания соединения в секундах. С сервером и сетью всякое может случиться. Соответствует переменной окружения PGCONNECT_TIMEOUT. Значение по умолчанию равно 0, что означает бесконечное время ожидания. Не рекомендуется устанавливать значение ожидания меньше 2 секунд.
  • options Опции, посылаемые непосредственно серверу, если таковые потребуются. Соответствует переменной окружения PGOPTIONS.
  • sslmode Определяет порядок действий при SSL-соединении. Принимает четыре возможных значения:
    • disable – без шифрования
    • allow – сначала попробовать соединиться без шифрования, а в случае неудачи постараться установить защищенное соединение,
    • prefer – сначала попробовать установить защищенное соединение, а в случае неудачи повторить соединение без шифрования,
    • require – выполнять только защищенное соединение. Соответствует переменной окружения PGSSLMODE.

Значение по умолчанию: prefer.

  • krbsrvname Имя Kerberos-сервиса. Используется для аутентификации с помощью Kerberos-5[1]. Это совершенно отдельная тема, выходящая за рамки данной статьи. Соответствует переменной окружения PGKRBSRVNAME.
  • PGDATESTYLE Переменная окружения, позволяющая установить представление времени и даты по умолчанию. Соответствует SQL-команде SET datestyle TO …
  • PGTZ Переменная окружения, позволяющая установить текущий часовой пояс. Соответствует SQL-команде SET timezone TO …
  • PGCLIENTENCODING Переменная окружения, позволяющая установить кодировку клиента. Соответствует SQL-команде SET client_encoding TO …

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

ConnStatusType PQstatus(const PGconn *conn);
char *PQerrorMessage(const PGconn *conn);

PQstatus возвращает информацию о том, как прошло соединение. Интересны состояния CONNECTION_OK – все хорошо и CONNECTION_BAD – ничего не вышло. Функция PQerrorMessage позволяет получить текстовую строку с описанием последней возникшей проблемы.

Для того, чтобы разорвать соединение, используется функция: 

void PQfinish(PGconn *conn);

Внимание! Соединения следует закрывать сразу же, как только в них отпадает необходимость. Число соединений, которые поддерживает POSTMASTER, ограничено – очень легко парализовать работу базы данных, просто открывая новые соединения.


SQL-запросы

Что ж, до сервера мы уже «дозвонились», теперь пора с ним «поговорить».

Выполнение запросов

Простейший способ выполнить SQL-запрос – это воспользоваться функцией PQexec: 

PGresult *PQexec(PGconn *conn, const char *command);

В качестве параметров функции передается структура соединения conn и строка с SQL-командой command. Возвращается указатель на структуру типа PGresult, где сохраняется информация, полученная от СУБД в ответ на запрос. При желании можно отсылать сразу несколько SQL-команд в одном запросе, разделяя их ; – точкой с запятой. В этом случае информация, сохраненная в структуре PGresult, будет относиться только к последнему запросу.


По умолчанию, каждый PQexec открывает отдельную транзакцию, если явно не начать ее с помощью команды BEGIN. В последнем случае транзакция будет продолжаться либо до COMMIT, либо до ROLLBACK.

Есть более сложный вызов PQexecParams, который позволяет передавать вызов и параметры к этому вызову раздельно. Таким образом исчезает необходимость самостоятельно формировать строку SQL-команды и заботиться об экранировании данных, что важно в случае сохранения бинарных последовательностей. В качестве платы за соображения безопасности PQexecParams способен послать не более одной команды за раз.

В некоторых случаях для увеличения скорости выполнения часто встречающихся запросов полезно обратить внимание на пару функций PQprepare и PQexecPrepared. Эти команды эквивалентны своим SQL-аналогам PREPARE и EXECUTE. Идея оптимизации состоит в том, что прежде чем выполнить запрос, PostgreSQL сначала анализирует его, затем планирует порядок действий и только потом, собственно, выполняет запрос. Первые два этапа для похожих запросов с разными условиями отбора можно выполнить заранее с помощью команды PREPARE. Затем, с помощью команды EXECUTE, можно выполнять подобные уже подготовленные (prepared) запросы.

Все упомянутые выше команды работают с сервером БД синхронным образом, то есть посылают запрос и ждут ответа. Клиентское приложение на это время «засыпает». В libpq предусмотрен целый класс функций, предназначенный для асинхронных операций, не блокирующих клиентское приложение. Их применение усложняет код и логику программы, хотя все в пределах допустимого. С моей точки зрения, лучше организовать все так, чтобы время, использованное на ожидание результатов запроса, не влияло фатально на внешние процессы, и обеспечить бесперебойную работу сети и сервера базы данных.

Информация о состоянии запроса

После выполнения запроса всегда интересно узнать, каково его состояние: 

ExecStatusType PQresultStatus(const PGresult *res);

На вход подается структура PGresult, создаваемая в результате работы PQexec-подобных функций, а на выходе получаем информацию о состоянии в виде числа, значение которого можно сравнить со следующими константами:

  • PGRES_COMMAND_OK – все прошло хорошо (для запросов, которые не возвращают данные, например, INSERT),
  • PGRES_TUPLES_OK – все прошло хорошо, плюс получены данные в ответ на запрос (для запросов типа SELECT или SHOW),
  • PGRES_EMPTY_QUERY – строка запроса почему-то была пустой,
  • PGRES_COPY_OUT – идет передача данных от сервера,
  • PGRES_COPY_IN – идет передача данных на сервер,
  • PGRES_BAD_RESPONSE – ошибка, ответ сервера не разборчив,
  • PGRES_NONFATAL_ERROR – нефатальная ошибка: предупреждение (notice) или информация к сведению (warning),
  • PGRES_FATAL_ERROR – при выполнении запроса произошла серьезная ошибка.

Для получения более подробной информации об ошибке следует воспользоваться функцией 

char *PQresultErrorMessage(const PGresult *res);

При вызове этой функции в качестве результата будет сформирована строка с информацией об ошибке или пустая строка, если все прошло хорошо.

Получение данных

При получении данных предполагается, что статус запроса соответствует PGRES_TUPLES_OK. Теперь, если примерно известно, что хочется получить в результате запроса, то для получения данных достаточно четырех функций: 

int PQntuples(const PGresult *res);
int PQnfields(const PGresult *res);
char *PQgetvalue(const PGresult *res,
    int row_number, int column_number);
int PQgetisnull(const PGresult *res,
    int row_number, int column_number);

Первые две функции являются информационными и позволяют узнать, сколько строк получено в результате запроса (PQntuples) и сколько в каждой такой строке колонок (PQnfields). Возьмите на заметку, что 0 строк – это тоже хороший результат.

Функция PQgetvalue позволяет получить доступ к данным. В качестве параметров кроме структуры соединения (res) передается номер строки (column_number) и номер колонки (column_number). Все данные возвращаются (как и посылаются) в виде текстовой строки, то есть перед употреблением их необходимо перевести в привычный формат. Например, в случае целых чисел можно воспользоваться функцией atoi.

Следует помнить, что данные SQL могут иметь неопределенное значение (NULL). Если подобная возможность существует, то перед получением значения следует проверить, определено ли оно. Pqgetisnull позволяет разобраться с этой проблемой. По передаваемым параметрам эта функция эквивалентна PQgetvalue, а в качестве результата возвращает 1, если значение не определено, и 0, если определено.

Кроме упомянутых выше, существует целый ряд функций, позволяющих получить информацию о данных, как то: имя колонки (PQfname), размер передаваемых данных в байтах (PQgetlength) и тому подобное. Для экранирования специальных символов при операции с бинарными или текстовыми данными есть набор сервисных функций PQescape*.

COPY

SQL-команда COPY является расширением, специфичным для PostgreSQL. Основное преимущество SQL – «все есть понятный текст», в некоторых случаях, когда надо передавать большие объемы данных, оборачивается недостатком. Функции PQputCopyData и PQgetCopyData в ряде случаев позволяют значительно ускорить передачу данных между сервером и клиентом.

Асинхронные сигналы

Стандартный SQL не предполагает взаимодействия разных пользователей, кроме как через изменение данных в таблицах. PostgreSQL позволяет посылать асинхронные сигналы с помощью команд LISTEN и NOTIFY. LISTEN “имя сигнала” передается серверу как обычная SQL- команда. Если статус запроса становится равным PGRES_COMMAND_OK, то это означает, что ранее был выполнен запрос NOTIFY “имя сигнала”. Если же инициализация сигнала (NOTIFY) ожидается позже регистрации (LISTEN), то функция PQnotifies позволяет вновь проверить наличие сигнала после любого запроса.

Сборка «мусора»

«Мусор» убирать придется руками. Каждая функция типа PQexec создает объект типа PGresult. После того, как вся необходимая информация о результатах запроса получена, следует освободить память, занимаемую этим объектом с помощью команды: 

void PQclear(PGresult *res);

Если утечки памяти вас не волнуют, то можно этого и не делать. В этом случае следует побеспокоиться о том, почему вас не беспокоят утечки памяти?


Большие объекты

Еще один способ сохранять неструктурированные данные в PostgreSQL – это сохранять их в больших объектах (Large Objects). PostgreSQL предоставляет интерфейс, схожий с файловым интерфейсом Unix: open (lo_open), read (lo_read), write (lo_write), lseek (lo_lseek) и так далее. Все lo_* команды работают со значениями, полученными из колонки с типом oid – это специальный тип данных, который является ссылкой на объект произвольного типа. То есть последовательность работы с большим объектом следующая: создается большой объект (lo_create). Далее возвращаемый lo_create указатель oid используется для записи данных в большой объект (lo_import/lo_write), а затем этот указатель вставляется в таблицу с помощью стандартных SQL-операторов. Чтение происходит в обратном порядке (lo_export/lo_read). Все операции с большими объектами должны происходить внутри транзакции.

Следует отметить, что необходимость интерфейса больших объектов на текущий момент не так уж и очевидна. Стандартными средствами в PostgreSQL можно сохранять бинарные данные размером вплоть до 1 Гб, что вполне может соперничать с максимальным размером для большого объекта (2 Гб).


ECPG

Чтобы не отставать от коммерческих баз данных, PostgreSQL имеет свой собственный вариант «встроенного SQL». Эта технология позволяет смешивать обычный язык C с SQL-структурами, примерно следующим образом: 

// файл test.pgc
#include <stdio.h>
#include <stdlib.h>
// структура для обработки ошибок
EXEC SQL include sqlca;
// реакция в случае ошибки/предупреждения
EXEC SQL whenever sqlwarning sqlprint;
EXEC SQL whenever sqlerror do ExitForError();
void ExitForError() {
    fprintf(stderr,”Все, конец - это фатально.\n”);
    sqlprint();
    exit(1);
}
 
int main(int argc, char **argv)
{
    // определение переменных, чтобы их можно было использовать
    // инструкциях ECPG
    EXEC SQL BEGIN DECLARE SECTION;
    const char *dbname = “test”;
    const char *user = “baldin”;
    VARCHAR FIO[128];
    VARCHAR NUMBER[128];
    EXEC SQL END DECLARE SECTION;
    // соединение с базой данных
    // внешние переменные предваряются двоеточием
    EXEC SQL CONNECT TO :dbname USER :user;
    // определение курсора через SELECT
    EXEC SQL DECLARE mycursor CURSOR FOR
    SELECT fio, number FROM fiodata,phonedata
    WHERE fiodata.id=phonedata.id;
    EXEC SQL open mycursor;
    // чтение данных из курсора
    EXEC SQL FETCH NEXT FROM mycursor INTO :FIO,:NUMBER;
    while (sqlca.sqlcode == 0) { // не 0, если данные больше нет
        printf(“ФИО: %s номер: %s\n”,FIO.arr,NUMBER.arr);
        EXEC SQL FETCH NEXT FROM mycursor INTO :FIO, :NUMBER;
    }
    // разъединение с базой данных
    EXEC SQL DISCONNECT;
}

Все SQL-команды начинаются с метки EXEC SQL. Она используется препроцессором ecpg как маркер для конструкций, подлежащих обработке. Внутри SQL-команд можно использовать переменные C. Для этого перед ними необходимо поставить двоеточие «:».

Для компиляции выше процитированного файла (test1.pgc) необходимо выполнить следующие действия: 

> # установить ecpg
> sudo apt-get install libecpg-dev
> # запустить препроцессор
> ecpg test1.pgc
> # скомпилировать получившийся файл
> gcc -o test1 test1.c -I`pg_config --includedir` -lecpg
> # проверить работоспособность программы
> ./test1
ФИО: Иванов И.П. номер: 555-32-23
ФИО: Балдин Е.М. номер: 555-41-37
ФИО: Балдин Е.М. номер: (+7)5559323919

Удобно это или нет – решайте сами.

Все остальное

Эта статья называется «Интерфейсы», но большая часть ее посвящена только одному из них. Дело в том, что этот один является родным и наиболее полным, а все остальное – лишь подмножество. В простейшем случае все интерфейсы одинаковы: открыл соединение, послал запрос, обработал результаты, закрыл соединение. Также заметна энергосберегающая тенденция везде делать ровно один интерфейс на все типы СУБД.

bash

Да, да к bash тоже есть свой интерфейс, правда для этого надо наложить специальный патч. Возни, конечно, немало – зато к базе данных можно будет обращаться прямо из shell-скриптов.

Сайт проекта: http://www.psn.co.jp/postgresql/pgbash/index-e.html.

Java

Совершенно ожидаемо, что Java общается с PostgreSQL стандартным образом, а именно через JDBC. Поэтому, если вы знакомы с Java [а если нет – зачем вам использовать ее для доступа к PostgreSQL? – прим. ред.], то достаточно добыть драйвер JDBC для PostgreSQL, например отсюда: http://jdbc.postgresql.org/. В Debian (Sarge) достаточно набрать 

> sudo apt-get install libpgjava

и, прочитав README к пакету, приступить к работе.

Lisp

Точнее, Common Lisp. Скорее всего, эти драйвера подойдут и для других диалектов: 

> sudo apt-get install cl-pg
#или
> sudo apt-get install cl-sql-postgresql

Второй вариант является драйвером для единого интерфейса доступа к SQL-базам данных из Common Lisp CLSQL (http://clsql.b9.com/).

Perl

Интерфейс для связи с PostgreSQL DBD-Pg используется в Perl через DBI[2]. Все подробности доступны на CPAN: http://search.cpan.org/~dbdpg/dbd-pg/pg.pm. 

> sudo apt-get install libdbd-pg-perl

DBD-Pg охватывает, фактически, все имеющиеся на сегодня возможности PostgreSQL: от больших объектов (large objects) до точек сохранения (savepoints).

PHP

О том как использовать PostgreSQL в PHP-проектах можно прочитать здесь: http://www.php.net/manual/en/ref.pgsql.php. Драйвер, скорее всего, тоже доступен в репозиториях дистрибутива: 

> sudo apt-get install php4-pgsql

Говорят, почти единственной причиной, по которой PHP-разработчики предпочитают MySQL, является то, что раньше не было «родной» версии PostgreSQL под Windows. Начиная с PostgreSQL 8.0, конкретно этот довод «против» уже не работает.

Python

Модуль для Python существует уже больше десяти лет. Подробности можно узнать здесь: http://www.druid.net/pygresql/. Установка модуля: 

> sudo apt-get install python-pygresql
Ruby

Кое-что можно прочесть здесь: http://ruby.scripting.ca/postgres/. Установка происходит как обычно: 

> sudo apt-get install libdbd-pg-ruby
ODBC

Разработка драйвера идет на pgFoundry. Аскетичная страничка проекта доступна по адресу: http://pgfoundry.org/projects/psqlodbc/. Установка: 

> sudo apt-get install odbc-postgresql


Послесловие

Конечно, наш обзор интерфейсов к PostgreSQL нельзя назвать всеобъемлющим, но изложенных сведений вполне хватит для того, чтобы начать работать. Если впоследствии вам придет в голову идея «дописать что-то свое», не спешите хвататься за перо, то есть клавиатуру – советую сначала обратиться по адресу http://techdocs.postgresql.org/oresources.php и посмотреть, что уже сделано.


  1. ↑ Kerberos — промышленный стандарт для аутентификации и взаимодействия в условиях незащищенного окружения. Алгоритмы Kerberos основаны на шифровании с использованием симметричного криптографического ключа и требуют наличия доверенного агента.
  2. ↑ DBI — унифицированный интерфейс для доступа к данным. Подробнее об этом пакете можно узнать на CPAN: http://search.cpan.org/~timb/DBI-1.52/DBI.pm
Источник — «http://wiki.linuxformat.ru/wiki/index.php?title=LXF89:PostgreSQL&oldid=2676»
Персональные инструменты
купить
подписаться
Яндекс.Метрика