Материал из Linuxformat.

PostgreSQL
Часть 1: Первое знакомство Часть 2: Работа с базой Часть 3: Возможности Часть 4: Программные интерфейсы Часть 5: Настраиваем сервер Часть 6: Дополнительные главы Листинги и текст на сайте автора

<<LXF86

PostgreSQL. ЧАСТЬ 2: Работа с базой

Проблема компьютеров в том, что они делают то, что вы сказали, а не то, что подумали. Поэтому запустить PostgreSQL недостаточно – нужно еще суметь договориться с ним на понятном языке. Евгений Балдин открывает русско-машинный словарь...

Это прибор, – сказал Корнеев безнадежно. – С ним работают...

«Понедельник начинается в субботу»

Содержание 1 Работа с базой 2 SQL 3 Командная строка 4 psql 5 gql-shell 6 dbishell 7 GUI вам в помощь 8 PgAccess 9 pgAdmin III 10 TOra 11 OpenOffice.org и SDBC 12 Что выбрать? 13 См.также 14 Примечания

Работа с базой

В прошлой части мы узнали, как создать базу данных и запустить postmaster. Теперь дело за малым: надо научиться сохранять данные и «доступаться» до них. Для этого следует договориться с postmaster – благо его «родной» язык довольно высокоуровневый.

Как и в предыдущей части, все рассматривается с точки зрения дистрибутива Debian (Sarge). При прочтении следует иметь это в виду.

Год	Ревизия	Нововведения
1986	SQL-86, SQL-87	Первая версия стандарта ANSI. Принят ISO в 1987 году. Стандартизация синтаксиса.
1989	SQL-89	Стандартизован механизм ссылочной целостности.
1992	SQL-92 (SQL-2)	Множество нововведений. В отличие от предыдущих версий, где стандарт просто сертифицировал уже имеющиеся на рынке реляционных БД возможности, были заложены основы для развития языка. Введены три уровня соответствия стандарту Entry (начальный), Intermediate (промежуточный), Full (полный). Мало какая из баз данных поддерживает SQL-92 лучше, чем Entry.
1999	SQL:1999 (SQL-3)	Добавлены регулярные выражения, рекурсивные запросы, триггеры. Определена интеграция с объектно-ориентированным подходом. Вместо трех уровней соответствия введен набор свойств (features).
2003	SQL:2003	Стандартизованы XML-зависимые нововведения, интервальные функции (window functions), стандартные последовательности и столбцы с автоматически генерируемыми значениями.

SQL

В качестве языка общения с реляционными базами данных в подавляющем большинстве случаев используется SQL. Изначально эти три буквы были сокращением фразы Structured Query Language (язык структурированных запросов). Сейчас, когда язык стал стандартом,SQL уже не является аббревиатурой – это обычное название, которое произносится как «эс-кью-эл». Несмотря на это, даже англоязычные специалисты по прежнему часто называют SQL «сиквел». По-русски также часто говорят «эс-ку-эль».

У этого языка есть недостатки, приводящие к тому, что в реальности SQL дополняется различными расширениями. Кстати, сам Кодд, «отец» реляционных баз данных, считал SQL неудачной реализацией его теории. Но на сегодня это мощный открытый промышленный стандарт, который позволяет решать множество типовых задач по созданию, модификации и управлению данными – он есть здесь и сейчас.

За время своего существования SQL претерпел несколько ревизий. Основные вехи в истории стандарта перечислены в таблице. Степень соответствия PostgreSQL стандарту SQL:2003 подробно рассмотрена в Приложении D (Appendix D. SQL Conformance) стандартной документации.

Там же есть и простейший учебник, и исчерпывающий справочник по SQL. Существует море литературы, в которой подробно и не очень рассказывается, что же это за «зверь такой» – SQL. Необходимый для «вхождения в технологию» минимум настолько прост, что основы изучаются в пределах одного дня вдумчивого чтения учебника.

Для того, чтобы куда-то сохранить данные, необходимо создать«хранилище» – таблицу/таблицы:

CREATE TABLE fiodata (id int,fio text)
CREATE TABLE phonedata (id int,number text)

Теперь можно добавлять данные:

INSERT INTO fiodata VALUES (1,'Иванов И.П.')
INSERT INTO phonedata VALUES (1,'555-32-23')

и так далее. Мы создали две обычных таблицы «без наворотов»: в одной хранятся имена, а в другой – телефоны. Сопоставление телефонов именам происходит через поля id. Почему так? На одно имя может быть заведено несколько телефонов, а на одном телефоне может «сидеть» несколько человек.

Теперь надо извлечь данные, и в этом нам поможет оператор SELECT. Собственно говоря, пользователю, кроме этого оператора, больше ничего знать и не надо – все выборки делаются с его помощью. Выведем все имена и соответствующие им телефоны:

SELECT fio, number
     FROM fiodata,phonedata WHERE fiodata.id=phonedata.id

SQL, очевидно, заслуживает большего, чем это «микровведение», и его, в любом случае, придется изучать тем, кто реально хочет заниматься базами данных – то есть от книжек никуда не денешься. А если подходить к делу серьезно, то кроме описания SQL следует изучить и основы реляционных баз данных того же К.Дж. Дейта (C.J. Date) – но это уже совсем другая история.

Командная строка

Когда набирается текст, а SQL – это именно текст, то лучше, чтобы ничего вокруг не отвлекало. Надежная, «толстая» и дешевая связь – вещь хорошая, только вот случается она не всегда, так что командная строка оказывается вне конкуренции.

psql

Вместе с пакетом postgresql-client поставляется утилита psql – интерактивная оболочка для «разговоров» с PostgreSQL. Она же – лучший инструмент для администрирования.

Окно psql.

Пусть существует база данных test, в которой заведены таблицы fiodata и phonedata, описанные в предыдущем разделе. Подсоединимся к базе и что-нибудь «спросим» у нее:

> psql test

Добро пожаловать в pSQL 7.4.7 – Интерактивный Терминал PostgreSQL. Наберите: \copyright для условий распространения

\h для подсказки по SQL командам
\? для подсказки по внутренним slash-командам (\команда) 
\g или ";" для завершения и выполнения запроса
\q для выхода

test=> SELECT fio, number
test->       FROM fiodata,phonedata WHERE fiodata.id=phonedata.id;
        fio  | number 
-------------+----------------
 Иванов И.П. | 555-32-23
 Балдин Е.М. | 555-41-37
 Балдин Е.М. | (+7)5559323919
(записей: 3) 

Если хочется подсоединиться к серверу на другой машине, то нужно указать имя машины после ключа -h. Ключ -U позволяет указать имя пользователя.

psql передает SQL-команды на сервер. Обратите внимание, что для завершения SQL-команды используется точка с запятой – ;.

Как и всякая человеко-ориентированная оболочка, pSQL использует библиотеку Readline. Это означает наличие стандартных горячих emacs-подобных комбинаций символов для общепринятого редактирования ввода командной строки, в том числе и завершение SQL-команд по Tab. По клавише Tab завершаются не только SQL-команды, но и названия таблиц и имена колонок, если это возможно.

psql поддерживает историю команд, которая сохраняется в .pSQL_history. Это также особенность библиотеки Readline. Полезным является интерактивный поиск по истории команд, который вызывается с помощью комбинации C^r.

Кроме команд SQL, psql имеет набор собственных специальных команд. Все такие команды начинаются с обратной косой черты \. Число спецкоманд довольно обширно и полное их описание можно распечатать, выполнив команду man pSQL. Далее будет перечислены наиболее интересные из них:

• \q Закончить работу с pSQL. Выйти из оболочки.
• \? Вывести справку по имеющимся спец-командам.
• \h [SQL-команда] Вывести помощь по запрашиваемой SQL-команде в форме Бэкуса-Наура (Backus Naur Form). SQL-команда может состоять из нескольких слов. При исполнении \h без аргумента выводится список доступных SQL-команд.
• \! [shell-команда] Запустить командный интерпретатор и выполнить команду оболочки.
• \i «файл» Прочитать текстовый файл и выполнить имеющиеся в нем команды. Удобно для нетривиальных операций.

Имя файла с командами можно передать при запуске psql посредством ключа -f. В этом случае после чтения и исполнения всех команд psql автоматически прекращает работу.

• \o [«файл»] Сохранить результаты выполнения будущих команд в файл. Если аргумент отсутствует, то вывод переключается на терминал. psql имеет набор команд, которые позволяют сформировать вывод в удобном для пользователя виде.

Имя файла, в котором следует сохранить результаты, также можно передать при запуске psql с помощью ключика -o. Этот ключ удобно применять совместно с ключом -f.

• d [«регулярное выражение»] Вывести структуру объекта. Годится для таблицы (table), представления (view), индекса (indexes) или последовательности (sequences). Список объектов можно получить, добавив первую букву названия объекта t, v, i, s к команде \d.

В дополнение к вышесказанному, psql поддерживает простейший механизм присваивания значений собственным переменным и их интерполяции в SQL-запросах:

test=> \set proba 'phonedata' 
test=> select * from :proba;
 id | number
----+----------------
  2 | 555-41-37
  2 | (+7)5559323919
  1 | 555-32-23
(записей: 3)

Следует учитывать, что интерполяция переменных не работает, если переменная используется внутри SQL-строки. В любом случае, хоть какое-то подспорье.

gql-shell

Небольшая psql-подобная оболочка, написанная одним человеком. Разработка заморожена. Естественно, она не обладает всеми возможностями psql, зато может подсоединяться и «разговаривать» не только с PostgreSQL. Для подсоединения к базе данных используется библиотека GQL (Generic C++ SQL Library). Для работы с PostgreSQL необходимо установить драйвер:

> sudo apt-get install gql-shell
> sudo apt-get install libgql-driver-0.5-pg
> gql-shell pg:test
Welcome to gql-shell, the interactive SQL terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit 

test=>

dbishell

dbishell – интерактивная оболочка на основе Perl::DBI. Как и gql-shell, поддерживает не только PostgreSQL. dbishell представляет из себя скрипт на Perl и занимает при установке чуть больше 150 Кб.

> sudo apt-get install dbishell
> dbishell --driver Pg --dsn host=localhost\;database=test --user baldin
Password:
Using DBIShell::dr::Pg engine

dbi:Pg:host=localhost;database=test:baldin>quit/

Для завершения любой команды используется косая черта /.

GUI вам в помощь

Следует признать, что программа с графическим пользовательским интерфейсом выглядит гораздо солиднее какой-то там командной строки. Об эффективности работы в случае необходимости показать, что занят важным делом, речь, естественно, не идет. Зачем один терминал, когда можно открыть кучу красивеньких окошечек с иконками? Конечно, PostgreSQL позволяет разговаривать с собой и через окна.

PgAccess

Когда обсуждается графический пользовательский интерфейс к PostgreSQL, тут же всплывает слово PgAccess (http://www.pgaccess.org/). PgAccess создан Константином Теодореску (Constantin Teodorescu) и имеет довольно длительную историю развития. На текущий момент разработка, похоже, заморожена. С другой стороны, «нет худа без добра»: новых версий тащить не надо – достаточно поставить то, что идет стандартно с вашим дистрибутивом:

> sudo apt-get install PgAccess
> PgAccess

Для того, чтобы подсоединиться к базе данных необходимо воспользоваться диалогом открытия соединения: Database -> Open. По умолчанию предполагается, что postmaster запущен на этом же компьютере (Host: localhost), и он «слушает» порт номер 5432 – если при установке PostgreSQL ничего специально не делалось, то так оно и есть. Далее требуется указать базу данных, к которой надо подсоединиться, пользователя и, если необходимо, пароль.

PgAccess явно что-то умеет.

PgAccess – это кросс-платформенный графический интерфейс к PostgreSQL написанный на чистом Tcl/Tk, и как следствие этого, работает везде, где этот инструментарий имеется (даже на «альтернативной» платформе). Размер дистрибутива по современным меркам крошечный: при установке все укладывается в 4 Мб.

В программе есть возможность создавать, редактировать и просматривать таблицы, запросы, представления, функции, пользователей, то есть довольно многое из того, что можно делать c помощью SQL. Плюс к этому можно создавать графические формы для ввода/просмотра данных, рисовать простые диаграммы и графики, просматривать картинки, сохраненные в базе данных. Так как программа написана на Tcl/Tk, то есть возможность писать свои скрипты, используя объекты, уже определенные в PgAccess.

Если хочется «сляпать» на скорую руку формочку, которую мож но запустить фактически где угодно после минимальной доводки, то PgAccess вполне может подойти для этого дела. В самом PgAccess масса ограничений и недостатков, но так как программа относительно небольшая, то ее можно доделать «по месту».

Информацию о созданных формах, запросах и тому подобных объектах PgAccess сохраняет непосредственно в базе данных, в таблицах, начинающихся с префикса pga_. Так что то, что сделано кем-то одним, будет доступно и всем пользователям базы.

Наличие PgAccess на машине, с моей точки зрения, поощряет нездоровое желание что-то «сляпать», а не сделать по-человечески. Так что работать с этим предметом надо осторожно, и если нет необходимости, то лучше убрать его от греха подальше. По мне, так pSQL гораздо удобнее и эффективнее, а самое главное – пользователи наведываются в БД гораздо реже.

pgAdmin III

Программа порадует вас заявлением, что она наиболее популярная и функциональная платформа администрирования и разработки для PostgreSQL, а также своим отсутствием в дистрибутиве Debian (Sarge), посему установка начнется с ее выкачивания. К счастью, на сайте проекта http://www.pgadmin.org/ можно найти сборки под множество дистрибутивов. Есть и специальный репозитарий для Debian – в /etc/apt/source.list добавляется строка:

deb [MIRROR URL]/pgadmin3/release/debian sarge pgadmin

где вместо [MIRROR URL] подставляется одно из официальных зеркал PostgreSQL, например: ftp://ftp.ru.PostgreSQL.org/pub/mirrors/pgSQL, и производится установка программы:

 > sudo apt-get update
 > sudo apt-get install pgadmin3
 > pgadmin3

При этом скачивается около 7,5 Мб. После запуска можно убедиться, что программа выглядит вполне солидно.

pgAdmin III подробно объясняет что надо «сказать» чтобы создать выбранный объект.

Новое соединение создается через меню File Add server. Требуется указать Address (localhost для локальной машины), сделать краткое описание соединения (Description), выбрать базу данных (Maintenance DB) и пользователя. После подсоединения доступны все объекты, которыми может управлять текущий пользователь.

PgAdmin III – это продукт для администрирования и управления базами данных под управлением PostgreSQL и его потомков. PgAdmin III содержит в себе графический интерфейс для управления данными, SQL-редактор с графическим представлением EXPLAIN, имеет инструменты для создания и редактирования таблиц, умеет управляться с системой репликации Slony-I и многое другое, что действительно упрощает администрирование. И все-таки PgAdmin III не для пользователя. Пока нет понимания того, что происходит, не следует уповать на картинки.

Изначально pgAdmin разрабатывался под Windows, но на сегодня этот продукт является многоплатформенным решением и работает также под Linux, Mac OSX, FreeBSD и Solaris. В качестве графической библиотеки используется wxWidgets (http://www.wxwidgets.org).

Русский перевод интерфейса, в принципе, существует, но на текущий момент не поддерживается. С другой стороны, это прежде всего инструмент администрирования и управления данными. В любом случае, разработчики приветствуют новые и обновленные переводы.

TOra

TOra возникла благодаря тому, что Генри Джонсон (Henrik Johnson) не смог запустить VMWare с Windows в далеком 2000 году. В то же время ему хотелось иметь графическую утилиту для администрирования Oracle, подобную той, которой пользовались его друзья, так и не отошедшие от «окон». TOra – это toolkit for Oracle. Так было, но на сегодня (в том числе и вследствие того, что TOra написана с использованием библиотеки Qt3), также можно работать и с PostgreSQL. Кроме PostgreSQL, дополнительно поддерживается MySQL и все, что работает через ODBC. ^[1]

Установка и запуск TOra просты:

> sudo apt-get install TOra
> TOra

TOra предлагает диалог создания нового соединения сразу при старте. Требуется указать Connection рrovider (PostgreSQL), Username, Host (localhost), Port (5432) и DataBase. Из-за прошлого программы, многие возможности TOra оказались привязаны к особенностям Oracle. В случае работы с PostgreSQL, TOra полезна прежде всего как браузер по SQL-объектам, SQL-терминал и изощренный SQL-редактор. Как и в случае pgAdmin III, TOra позволяет создавать и редактировать таблицы с помощью графических диалогов, но не владеет специфичными для PostgreSQL настройками. TOra – это крепко «сбитый» программный продукт, который позволяет работать с разными реляционными СУБД в пределах одной программы.

OpenOffice.org и SDBC

OpenOffice.org – монстр, но ситуация на сегодня такая, что люди любят монстров, и ничего в обозримом будущем с этим не поделаешь.

OpenOffice + PostgreSQL.

Для прямого доступа из OpenOffice к PostgreSQL без промежуточного уровня в виде ODBC/JDBC драйверов разрабатывается драйвер PostgreSQL-sdbc. На сегодня в стандартной поставке OpenOffice.org этот пакет отсутствует.

Для установки необходимо скачать zip-архив этого драйвера с его домашней странички OpenOffice.org и положить куда-нибудь у себя на диске не распаковывая (!). Далее, запустив OpenOffice, следует открыть диалог управления пакетами: Сервис Управление пакетами… и с помощью кнопки Добавить установить этот пакет. В моем случае после установки пришлось перезапустить OpenOffice.

Для подсоединения к уже существующей базе данных PostgreSQL следует открыть диалог Мастера базы данных:

Создать Базу данных Выбор базы данных, поставить галочку Подключиться к существующей базе данных и выбрать PostgreSQL. Далее при настройке соединения в следует ввести строчку вида:

dbname="имя БД" host="адрес сервера"

подставив вместо имя БД и адрес сервера имя предварительно созданной базы данных и адрес сервера, на котором «крутится» postmaster, например, dbname=test host=localhost. Далее, во вкладке Аутентификация пользователя необходимо ввести имя пользователя – затем можно протестировать соединение. Если тест прошел нормально, то можно продолжить и выполнить подключение.

Во время окончания действия мастера предлагается сохранить все, что проделано в ODB-файле (формат База данных OpenDocument). Затем это соединение можно будет выполнить простым открытием файла. Туда же сохраняется информация обо всех созданных формах, запросах и отчетах. Как конкретно создаются формы и отчеты – это совсем другая история и относится она не к PostgreSQL, а к OpenOffice.org (см.LXF85:PostgreSQL).

При выборе таблиц легко видеть, что они в PostgreSQL разбиты на группы. Пользовательские таблицы по умолчанию находятся в группе public. В группах pg_catalog и informaion_schema представлена системная информация и статистика.

Что выбрать?

Естественно, мы рассмотрели далеко не все возможные программы общего назначения для работы с PostgreSQL, но даже из того, что рассмотрено, нельзя выбрать что-то одно. Каждая программа имеет свои особенности и преимущества. psql позволяет легко работать удаленно, OpenOffice.org удовлетворяет нашу любовь к монстрам, PgAdmin III содержит множество подсказок по делу, PgAccess удивляет своей интеграцией с TCL/Tk, а TOra – «просто красивая».

Я всегда выбирал psql, но это скорее всего связано со специфичностью решаемых мной задач. Я вполне могу представить себе ситуацию, когда наличие, например, TOra значительно облегчит жизнь. Ну и не следует забывать про данные, которые кто-то должен вводить. Если лень писать специальную программу (которую лучше все-таки написать), то OpenOffice.org поможет, особенно если вводить не вам.

См.также

• Азбука SQL

Примечания

1. ↑ Вследствие того, что PostgreSQL распространяется под лицензией BSD, имеется несколько коммерческих продуктов, основанных на его коде, например, EnterpriseDB, Pervasive Postgres и SRA PowerGres.0