Ключевые слова:postgresql, database, perl, install, (найти похожие документы)
From: Eugene <asm at uinc.ru>
Date: Mon, 28 Aug 2004 14:31:37 +0000 (UTC)
Subject: Обзор PostgreSQL с примерами
Оригинал: http://www.php-soft.com/article/postgres.html
Postgres - пища для размышлений...
В последнее время в интернете все большее значение отводится базам
данных. По данным статистики на данный момент более 2/3 серверов
работают под управлением UNIX-подобных систем, в связи с чем
установились некоторые лидеры среди СУБД под юникс. В первую очередь
это, конечно, MySQL - мощнейшая среда, способная удовлетворить
практически любые запросы пользователя. На второе место по
распостраненности я бы поставил именно рассматриваемый нами Postgres.
Но если при желании разобраться с MySQL Вам достаточно посидеть с
довольно удачной книгой издательства O'Reilly, то если Вы захотите
изучить Postgres бумажной литературы Вы , скорее всего, не найдете.
Единственный выход - облазить основные сайты с технической
документацией в поисках крошечных объемов информации. Именно по этой
причине и родилась эта статья - краткий обзор СУБД PostgreSQL
вперемешку с готовыми рабочими примерами. Для начала давайте
разберемся - зачем же нам нужен Postgres. Главные его преимущества,
по сравнению с тем же MySQL, - полноценная поддержка ANSI-92, а так же
возможность разделения полномочий для пользователей (прим. : речь идет
о стандартном пакете MySQL). Это именно те причины, по которой
постгрес приобретает так много сторонников. Как видно из названия для
работы с данными можно использовать язык SQL запросов, что делает БД
переносимыми, кроме того разработчику не надо тратить время на
изучение нового языка запросов.
Окунемся в историю...
Все началось в 1977-м году, когда в Univercity of California at
Berkeley была начата разработка Ingres - первого сервера реляционных
баз данных. Именно Ingres и считается родоначальником PostgreSQL.
Работа надо проектом длилась до 1985-ого года, при этом права на СУБД
перешли к ново созданной Relation Technologies/Ingres Copporation.
Проект возглавил Michael Stonebraker и в это же время получил название
Postgres. До 94-ого года принципиальных изменений проект не претерпел
и вот в 1994-ом году 2 студента Беркли Jolly Chen и Andrew Yu добавили
в Постргес возможность работы с SQL запросами. Так на свет появился
Postgres95.
Летом 96-ого года Marc G. Fournier предложил сервер для создания
почтовой рассылки и создания CVS (concurrent versions system)
репозитория. В то время Постгрес уже завоевал популярность. Чен,
который на тот момент возглавлял разработку говорил : "Для разработки
нам нужно несколько человек с обилием свободного времени и множество с
его отсутствием". О причинах такого изречения уже на тот момент
говорили 250 тысяч строк кода на Си.
И несколько таких людей в скором времени были найдены. Ими были: Marc
Fournier, Thomas Lockhart и Vadim Mikheev. Как пишет один из
разработчиков, в то время типичным сообщением о проблеме с СУБД(bug
report), поступавшим к команде разработчиков было что-то в стиле
"Когда я запускаю сервер базы данных, он сразу же падает". Шло время,
в конце 96-ого года проект в очередной раз поменял свое название, на
этот раз надолго, теперь СУБД называлась PostgreSQL.
Bruce Mobjian писал : "Дискуссии в группах Usenet подстегнули нас к
работе. Изначально мы с горечью наблюдали за тем, как люди, испытавшие
Постгрес, настойчиво рекомендовали использовать другие СУБД. Но позже
ситуация кардинально изменилась и Постгрес стал ходовым сервером БД."
Практика...
Что же нам потребуется для работы с этим пакетом? Первым делом это,
конечно, сам Postgres. Взять его можно с официального сайта
www.postgresql.com, размер пакета приблизительно 8,1Mb . (все
нижеприведенные примеры проверены на работоспособность с
использованием Postgres версии 7.1.3).Если у Вас уже установлен
Постгрес, то следующий абзац Вы можете пропустить.
Установка
Процесс установки стандартен :
gunzip postgresql-7.1.3.tar.gz
tar -xvf postgresql-7.1.3.tar
cd Postgres-7.1.3
./configure
gmake
gmake install
Для дальнейшей работы нам понадобится новый пользователь с именем
"Postgres". Создадим его следующей командой:
adduser Postgres
Далее все действия касательно администрирования баз данных будем
проводить от имени этого пользователя.
su Postgres
Сфера использования баз данных очень широка - от рабочих станций и
корпоративных сетей до интернета. По этому сам по себе Postgres -
сетевое приложение, доступ к которому можно получить не только с
локальной машины (AF_UNIX), а и с удаленной (AF_INET). По умолчанию
используется порт с номером 5432, но можно установить и другое
значение, используя параметр "-p <port>" при запуске сервера
(postmaster). У пользователя есть возможность взаимодействовать с
базой данных как посредством командного интерпретатора postgresql
(путем запуска /usr/local/pgsql/bin/psql <database name>), так и путем
написания скрипт-приложений на языках Perl, PHP, C и т.д.
Именно Перл-скриптам и будет посвящена последующая часть данной статьи. Для
начала нам нужен модуль DBI для работы с Postgres. Взять его можно,
как и многие другие модули на сайте www.perl.org , либо же на сайте
http://www.cpan.org.
Установка модуля типична:
gunzip DBD-Pg-1.01.tar.gz
tar -xvf DBD-Pg-1.01.tar
cd DBD-Pg-1.01
perl Makefile.PL
make
make install
(заметим: при установке на моем Slackware 8.0 , kernel 2.4.10-pre8
потребовалось вручную прописать переменные окружения:
export POSTGRES_LIB=/usr/local/pgsql/lib
export POSTGRES_INCLUDE=/usr/local/pgsql/include
Вот и все - теперь Ваш перл понимает "use DBD".
Этого нам вполне достаточно для работы с Постгресом. Следует сказать
пару слов о самом интерфейсе DBI. Сам по себе он очень напоминает ODBC
- довольно удачную реализацию идеи работы со множеством форматов БД
одним набором функций (особое место занимает в Win32 системах, хотя
есть и UNIX-реализация), более подбробно с этим проектом Вы можете
ознакомиться на сайте проекта www.unixodbc.org .
Что это дает нам? Это позволит нашим скрипт-приложениям работать с
практически любыми базами данных, которые на данный момент только
существуют для пользователя, для этого Вам придется лишь посетить (уже
упоминавшийся) www.perl.org и скачать необходимый DBI модуль
(package).
На этом этапе предлагаю перейти непосредственно к практике. Для
нормальной работы нам необходимо запустить сам демон Постгреса, а
именно:
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
(рекомендуется запускать его с ключом "-i" для активизации возможности
создания TCP/IP соединений). Параметр "-D" указывает расположение
директории с базами данных, использование параметра "-p" позволит Вам
вручную указать номер порта, который будет использовать демон для
создания сетевых соединений, а "-d" - указать уровень отладки
(debugging level). Более полное описание Вы можете получить использую
ключ "--help". Сама директория создается так:
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Для полноценной работы создадим и саму базу данных. Для этого введем:
/usr/local/pgsql/bin/createdb <database name>
(в нашем примере будем использовать имя "database").
Теперь для входа в SQL консоль Вам хватит одной команды:
/usr/local/pgsql/bin/psql database . Вот мы и внутри. Что теперь? Для
дальнейшей работы нам понадобятся примитивные знания языка запросов
SQL. В консольном режиме работа с Postgres практически ничем не
отличается от работы с MySQL.
Рассмотрим некоторые основные виды SQL запросов, которые могут Вам
пригодиться при работе с Postgres.
Безусловно, первоочередная задача при работе с БД - создание таблиц.
Для этого предусмотрена команда create table <table name> values
(<fields>);
fields представляет собой набор полей базы. При объявлении поля,
необходимо указать его имя и типа (например surname varchar(35) - под
поле "surname" резервируется строка из 35 символов).
Просмотреть ново созданную таблицу можно воспользовавшись командой \d
<table name>, которая выведет содержимое таблицы <table name> . Эта же
команда без параметров приведет к выводу на терминал названий всех
таблиц в базе данных (аналог "show tables;" в MySQL).
Помимо "\d" среда Postgres предоставляет и другие не SQL команды.
Рассмотрим их:
\q - выход из среды.
\h - вывод помощи по SQL командам.
\? - вывод помощи по "\*" командам.
\i <file> - загрузить и выполнить запросы, содержащиеся в файле <file>.
\p - показать буфер запросов.
\r - очистить буфер запросов.
\l - показать список баз данных (аналог "show databases;" в MySQL).
\z - показать разрешения на доступ к базам данных.
\s <file> - показать history или сохранить ее в <file>.
\set <var> <value> - установить внутреннюю переменную.
\df(do) - вывод на консоль (терминал) доступных функций/операторов-
названия, параметры, возвращаемое значение, описание.
Другие "\*" команды используются значительно реже и особенного
интереса для нас на данный момент не представляют.
Теперь разберемся с наполнением нашей базы данных. Для добавления
записей в Postgres предусмотрена команда INSERT.
Примером использования insert может послужить следующее:
insert into data values ('Ivan' , 'Ivanov');
При этом в таблицу data (name varchar(n1), surname varchar(n2)) будет
добавлена запись "Ivan Ivanov".
Для просмотра содержимого таблиц в СУБД Постгрес предусмотрена команда
select.
Синтаксис ее таков:
SELECT <условие> FROM <таблица> WHERE <поле>{=,>,<,...}<значение>;
Например запрос "select * from data where name='Ivan'" приведет к
выводу на терминал подобной таблицы:
+---------------+-------------+
| name | surname |
+---------------+-------------+
| Ivan | Ivanov |
+---------------+-------------+
1 row is set ( 0.02 sec)
В удалении записи также нет ничего сложного:
DELETE FROM <таблица> WHERE <поле>{=,>,<,...}<значение>;
Приведем пример:
DELETE FROM data WHERE surname='Ivanov';
При этом из таблицы data будут удалены все записи, поле surname
которых содержит значение Ivanov.
Поставим перед собой задачу - создание телефонного справочника
сотрудников фирмы, работа с которым осуществлялась бы посредством
WEB-интерфейса.
Для этого создадим таблицу "data":
create table data (
name varchar(15),
surname varchar(20),
addr varchar(20),
tel varchar(15)
);
Все четыре поля для простоты сделаем текстовыми. Теперь командой "\q"
выйдем из консольного режима управления базой данных и окунемся в мир
Перла. С подробностями установки DBI модуля для работы с PostgreSQL
можно ознакомиться в первой части статьи. Рассмотрим простой скрипт,
выводящий пользователю все содержимое базы database таблицы data :
#!/usr/bin/perl
use DBI; # Подключение DBI модуля
$dbn = "database";
$user = "Postgres";
$table = "data"; # указываем параметры для установки скриптом соединения с базой
print "Content-type: text/html\n\n";
print "<HTML>";
print "<BODY>";
print "<LI>Соединение с базой данных...\r\n</LI>";
$conn = DBI->connect("DBI:Pg:dbname=$dbn","$user","") or die "Ошибка соединения.";
# вот мы и соединились.
$req = "SELECT * FROM $table;";
# формируем SQL запрос
$result = $conn->do($req);
# в переменную $result запишется количество записей в таблице ,
# полученное в результате выполнения сформированного запроса.
$strin = $conn->prepare($req);
@cont = $strin->execute; # выполним сам запрос
print "<P>В базе данных $dbn в таблице $table было найдено $result записи(ей).</P>\n";
print "<P>Они и представлены Вашему вниманию:</P>\n";
print "<table border=1>\n";
while($i<$result) # для всех записей в таблице
{
@row = $strin->fetchrow_array; # используя соединение $conn получим содержимое первой записи
print "<tr><td>$row[0]</td><td> $row[1]</td><td> $row[2]</td><td>$row[3]</td><td></tr>\n";
# выведем полученные данные .
$i++;
}
print "</table>\n";
@cont = $strin->finish; # завершим работы с созданным соединением
$rc = $conn->disconnect; # отсоединимся от базы данных
print "</BODY>";
print "</HTML>";
Вот и все - теперь пользователь получит у себя на экране содержимое
базы database. Как видим, синтаксис напоминает работу с MySQL (я не
имею в виду DBI модуль).
Этого же результата можно было достичь, введя в Postgresql консоли (по
умолчанию /usr/local/pgsql/bin/pgsql <database name>) SQL запрос
"select * from data;"
При выводе информации из базы данных существует возможность
определения порядка выводимых записей. Для этого используется
конструкция ORDER BY.
Например выполнение запроса
"SELECT * FROM DATA ORDER BY NAME;"
приведет к выводу содержимого таблицы data в порядке алфавитного
следования полей name.
Еще одну интересную возможность представляет использование оператора
case.
Пример:
select name , case when surname!='Ivanov' then 'not Ivanov' else 'this is Ivanov' end from data;
Результатом будет вывод на терминал 2-х столбцов. 1-й столбец -
'name', 2-й - столбец 'case'. При этом в столбце case для тех записей,
поле surname которых содержит значение Ivanov будет содержать текст
'this is Ivanov', в противном случае - 'not Ivanov'. Конечно, при этом
допустима не только операция проверки на равенство, но и другие
операции.
Иногда, у пользователя возникает необходимость не только вывести не
терминал содержимое определенных записей таблицы, а и произвести
подсчет количества таких записей, среднее значение по какому-либо
параметру. Для этих целей в Постгресе предусмотреные специальные
функции : COUNT/MAX/MIN/AVG/SUM. Например выполнение запроса
SELECT COUNT(*) FROM table;
даст пользователю информацию о количестве записей в таблице, запрос
SELECT SUM(money) FROM bank WHERE id <=50;
подсчитает количество денег на банковских счетах первых 50-ти
клиентов.
Следует отметить еще одну особенность таблиц - каждая запись в БД
имеет свой собственный уникальный номер, называемый oid. Проверить это
утверждение можно простым запросом:
SELECT oid FROM table;
Это поле может быть использовано пользоватем для его нужд.
Вложенные запросы.
Постгрес дает пользователю еще одну интересную возможность -
использование вложенных запросов (subqueries). Вложенность запросов -
довольно гибкий механизм, позволяющий более надежно производить
манипуляции с записями БД. Приведем пример вложенного запроса:
SELECT * FROM stuff WHERE surname<>(SELECT surname FROM friends WHERE id=8);
Результатом обработки такого запроса будет вывод на терминал
информации из таблицы stuff, для которых поле surname отлично от поля
surname записи с id=8. Таким образом, в случае если содержимое записи
с id=8 будет изменено, это не отразится на корректной работе запроса,
так как изменения бубут учтены динамически. Следует заметить, что при
таком использовании вложенности, необходимым условием является
возвращение вложенным запросом внешнему лишь одного значения. Однако,
все преимущества subqueries видны при использовании операций
IN/ALL/ANY/EXISTS. Это дает возможность с легкостью находить
совпадения данных в таблицах, анализировать наличие или же отсутствие
таковых и многое другое.
SELECT * FROM data1 where tel in (SELECT tel FROM data2);
Выполнение вышеприведенного запроса приведет к выводу тех записей из
data1, поля tel которых присутствуют в таблице data2. EXISTS вернет
true, если подзапрос возвращает хотя бы 1 запись, ANY - если
переменная равна какому-либо значению из списка, ALL - если условию
для переменная удовлетвореют все значения.
Транзакции.
Рассмотрим некоторую промышленную базу данных, насчитывающую многие
тысячи записей. Предположим , что в некоторый момент некий
пользователь вносит серьезные изменения в записи базы. Пусть база
данных содержит данные о балансе клиентов некоторого банка и изменения
заключаются в добавлении 50$ к каждому счету. Время , необходимое на
совершение этого действия зависит , безусловно, не только от машины,
на которой производится изменение, а также и от размера самой базы. В
некоторый момент происходит непредвиденное - питание машины было
отключено или же произошел сбой сервера БД. В результате действие не
было полностью завершено. Возникает вопрос - что произойдет с данными?
Возможно, что на момент возникновения внештатной ситуации изменения
были внесены лишь в некоторую часть записей, но ведь вариант, когда
лишь часть клиентов получат прибавку к балансу является недопустимым.
С другой стороны, повторное выполенение операции добавления пятидесяти
долларов , пожалуй, также неустраивает банк, так как некоторые клиенты
получат больше денег, нежели им причитается. На самом дела PostgreSQL
обладает механизмом транзакций - на практике возможны лишь 2 варианта
. Либо ни к одному из счетов не будет добавлена сумма, либо же все
счета будут обновлены. В такой ситуации пользователь может проверить
лишь один счет и на основе его состояния сделать вывод - какой из
вышеупомянутых вариантов имел место на практике. Подобные сложности
вполне возможны на практике , если рассматривать, например, сильно
загруженный Web сервер в ситуации возросшего числа обращений к нему.
При высоких нагрузках вероятность сбоя ПО сильно возрастает. Усложним
нашу ситуацию - предположим возникла необходимость перевести деньги с
одного счет на другой .
update date set money = money + 2000 where id = 100;
update date set money = money - 2000 where id = 101;
При этом возникает та же внештатная ситуация, что и описанная выше.
Что же произойдет? Возможно, деньги будут сняты со счета одного
клиента, но операция добавления не будет произведена. В данном случае,
каждый запрос является отдельной транзакцией. Постргес может
обеспечить целостное выполнение одной операции, но не их
последовательности. Что же далеать в такой ситуации? Для разрешениея
подобных проблем в распоряжении пользователя есть возможность
объединять несколько запросов в одну транзакцию.
begin work;
update date set money = money + 2000 where id = 100;
update date set money = money - 2000 where id = 101;
commit work;
В такой ситуации все запросы, присутствующие между "begin" и "commit"
являются одной транзакцией. Преимущества такого подхода очевидны и
были описаны ранее. До выполнения commit пользователь может отменить
предшествующие запросы используя "rollback work;". Рассмотрим еще одну
интересную особенность транзакций. До выполнения транзакции, изменения
внесенные в базу данных видны лишь пользователю, который эти изменения
внес. Такое поведение СУБД можно изменить, заменив "уровень изоляции".
Пример такого изменения: set transaction isolation level serializable;
В при таком значении уровня, находясь внутри транзакции пользователь
не будет замечать изменений, которые внесли в базу данных другие
пользователи. Таким образом, можно утверждать, что транзакции
представляют собой мощный механизм предупреждения неадекватных реакций
БД на внештатные ситуации.
Рассмотрим еще одну интересную возможность пакета Postgres : программа
pg_dump позволяет выдать содержимое базы данных на стандартный вывод
или же просто записать в текстовый файл .
Рассмотрим пример:
/usr/local/bin/pgsql/bin/pg_dump database >file.txt
При этом содержимое базы database будет записано в файл file.txt.
При этом пользователь может в дальнейшем создать новую базу данных на
основе сохраненной информации. Для этого ему необходимо при создании
базы createdb необходимо перенаправить в stdin содержимое файла.
На этом предлагаю завершить наш вводный курс в сервер реляционных баз
данных Postgres. Надеюсь, эта статья оказалась для Вас полезной.
Эпилог...
Как я уже писал, при написании этой статьи я не руководствовался
желанием создать руководство пользователя. Эта статья - материал для
размышлений, информация, которая может помочь пользователю ответить на
непростой вопрос - "А стоит ли? Стоит ли браться на PostgreSQL? Или
все же более разумно придерживаться более консервативных взглядов и
пользоваться MySQL. ". На мой взгляд, на данный момент Постгрес - уже
не просто конкурент MySQL. На данный момент это нечто вроде
альтернативы, при чем альтернатива это вполне достойна занять свою
нишу в доле современных баз данных. Разработка этой СУБД - плод
многолетней работы множества талантливых программистов, в связи с чем
нельзя не относиться серьезно к такому программному продукту.
Литература и ссылки...
1. "PostgreSQL. Introduction and Concepts". Bruce Momjian. "Addison Wesley" 2001. (409).
2. http://www.postgresql.com
3. http://www.postgresql.ru
4. http://www.linuxgazette.com (issue #67)
by Eugene <asm at uinc.ru>
