Документирование сервера

Очень много времени и энергии уходит на настройку SQL-сервера и объектов, расположенных на нем. Способ документирования подобной информации может пригодиться в ряде ситуаций. Если база данных будет повреждена, а резервной копии не окажется, вам придется восстанавливать все ее таблицы. При необходимости перенести данные с одного сервера на другой важно знать конфигурацию источника и приемника данных. Даже для собственных баз данных может пригодиться возможность просмотреть карту таблиц.

Чтобы передать всю «прелесть» непереносимой (nonportable) природы администрирования баз данных, приведу пример реализации одной простой задачи для трех различных SQL-серверов с использованием как DBI, так и ODBC. Каждая из этих программ делает одно и то же: выводит список всех баз данных на сервере, их таблицы и структуру каждой таблицы. Эти сценарии можно очень легко расширить для предоставления более подробной информации о каждом объекте. Например, бывает полезно узнать, в каких полях есть значения ti'J.; или NOT NULL. Вывод всех трех программ выглядит одинаково:

— sysad'ii — hosts

i.ame [c:;ar(30)J ipaddr [char(lb)

aliases [char(50)]

owner [char(40)J

dept [char(15)]

bldg [char(10)]

room [char(4)]

manuf [char(10)J

model [char(10)] —hpotter—

customers

cid [char(4)J

cname [varchar(l3)]

city [varchar(20)J

discnt [real(7>] agents

aid [char(3)]

aname [varchar(13)]

city [varchar(20)]

percent [int(10)] products

pid [char(3)]

pname [varchar(13)]

city [varchar(20)]

quantity [int(10)]

price [real(7)] orders

ordno [int(10>]

month [char(3)]

cid [char(4)]

aid [char(3)]

pid [char(3)]

qty [int(K))]

dollars [real(7)

Сервер MySQL и DBI

Вот как выглядит способ получить эту информацию с сервера MySQL с использованием DBI. Существующее в MySQL дополнение команды SHOW очень упрощает эту задачу:

use DBI;

print "Введите имя пользователя: ";

chomp($user = <STDIN>);

print "Введите пароль для $user: chomp($pw = <STDIN>)

Sstart = "mysql"; tf первоначально будем подсоединяться к этой базе данных

О соединяемся с базой данных

$dbh = DBI->connect("DBI:mysql:$start".$user.$pw);

die "Невозможно соединиться: ".$DBI::errstr."\n"

unless (defined $don): ft ищем базы данных на сервере

$sth=$doh->prepartj(q;SHOW DATABASES}) or

die "Невозможно подготовить запрос show dataoaset; ".

die "Невозмохо заполнить запрос

push(@dcs.$a'-e*->[0]): > $sth->finish;

# ищем таблица в каждой базе данных foreach $db (!g>dbs) { print "---$db---\n";

$sth=$dbh->prepare(qq{SHOW TABLES FROM $db}) or

die "Невозможно подготовить запрос

show tables: ". $dbh->t:r rs*

die "Невозможно выполнить запрос show tables: ".

$dbh->crr:

(g>tables=();

while (Saref = $sth->fetchrow_arrayref) {

push(Stables,$aref->[0]); }

$sth->finish;

tt

ищем информацию о полях для каждой таблицы

foreach Stable (^tables) { print "\t$table\n":

$sth=$dbh->prepare(qq[SHOW COLUMNS FROM Stable FROM Sob!) o'

die "Невозможно подготовить запрос show columns: ". $cihh-^errstr."\n";

$sth->execute or die "Невозможно выполнить запрос show columns: ".

while (Saref = $sth->fetchrow_arrayref) {

print "\tAt".Saref->[0]." [". $aref->[1 ]. "]\n":

$stn->finisn \ I

} Sdbr->d;.scor/iec::

Добавим несколько комментариев к приведенной программе:

Мы соединяемся с начальной базой данных только для того, чтобы удовлетворить принятой в DBI семантике соединения, но такой контекст возникает не обязательно благодаря командам.

Если читатель думает, что команды подготовки и выполнения запросов SHOW TABLES и SHOW COLUMNS являются отличными кандидатами на использование заполнителей, то он совершенно прав. К сожалению, именно эта комбинация DBD драйвера /сервера не поддерживает заполнители в таком контексте (по крайней мере, это было так в период написания данной книги). В следующем примере мы столкнемся с подобной ситуацией.

Имя пользователя базы данных и его пароль запрашиваются интерактивно,

поскольку альтернативы (прописывание их в коде или передача в командной строке, при которых любой, кто просматривает список процессов, сможет их увидеть) еще хуже. В данном случае символы пароля будут отображаться при вводе. Для большей осторожности стоит применять что-то подобное Те г тт. : Reaakey, чтобы подавить отображение символов.

Сервер Sybase и DBI

В этом подразделе представлен аналог для Sybase. Внимательно просмотрите программу, а после этого поговорим о некоторых существенных моментах:

use DBI;

print "Введите имя пользователя: "; chomp($user = <STDIM>);

print "Введите пароль для $user: "; chomp($pw = <STDIN>);

$dbh = DBl->connect('dbi:Sybase'',Suser,$pw);

die "Невозможно соединиться: $DBI::errstr\n" unless (defined $dbh);

ищем базы данных на сервере

$sth = $dbh->prepare(q{SELECT name from master dbo.sysdatarases}) cr

die "Невозможно подготовить запрос к sysdatabases: ".

$db'i->er rst r . "\n", $stli->oxecute or

die "Невозможно выполнить запрос к sysdarabases: '.

$dori->errstr. "\п";

while (Sarof = $sth->fetchrow_arrayref) (

push((3dbs, $aref->[0]): }

$sth->finisn:

foreach $cm (Mbs) {

$dbh->do("USE $do") or

die "Невозможно использовать $db: ".

®tables=():

while ($агч'( - $.::;->fotchrow_arrayref) {

die "Невозможно изменить Sdb: ".

S'Jor->err-str."' n":

# ищем поля для каждой таблицы foreach Stable (tables) { print "\t$table\n";

$sth=$dbh->prepare(qq{EXEC bp_colunns Stable}) or

die "Невозможно подготовить запрос sp^columns ", $obh-:-err.v

$sth->execute or

die "Невозможно выполнить запрос sp^columns: ".$dbh->errstr.

while ($aref = $sth->fetchrow_arrayref) {

print "\t\t",$aref->[3]," [",$aref->[5],"(",

$aref->[6],")]\n": }

$sth->finish; ! }

$dbh->discohnect or warn "Невозможно отсоединиться: ".

$dbh->errstr."\n";

Вот обещанные заметные моменты:

Sybase хранит информацию о базах данных и таблицах в специальных системных таблицах sysdatabases и sysobjects. Каждая база данных содержит таблицу sysobjects, в то время как сервер хранит обобщенную информацию о них в одной таблице sysdatabases, расположенной в основной базе данных. Мы используем более явный синтаксис databases, owner, table в первом запросе SELECT, чтобы недвусмысленно обратиться именно к этой таблице. Для перехода к sysobjects каждой базы данных можно применять этот же синтаксис, вместо того чтобы явно переключаться между ними при помощи USE. Более того, как и при переходе в каталог средствами cd, такой контекст упрощает написание других запросов.

Запрос SELECT к sysobjects применяет ключевое слово iVHE-L, чтобы вернуть информацию только о пользовательских таблицах. Это было сделано для ограничения размера вывода. Если бы мы хотели включить также и все системные таблицы.

Складывается впечатление, что заполнители в DBD: : Sybase реализованы так для того, чтобы препятствовать их употреблению с хранимыми процедурами. Будь реализация другой, следовало бы использовать заполнители в EXEC sp_columns.

Сервер MS-SQL и ODBC

Наконец, вот код для получения той же информации с сервера MS-SQL через ODBC. Заметьте, что применяемый синтаксис SQL практически идентичен предыдущему примеру благодаря связи Sybase/MS-SQL. Интересны отличия между этим примером и предыдущим:

Использование DSN, которое предоставляет нам контекст базы данных по умолчанию, так что нет необходимости указывать, где искать таблицу

sysdatabases.

Употребление $dbh->DropCursor() в качестве грубой аналогии $sth-fi-nish.

Неудобный синтаксис, который приходится применять для запуска хранимых процедур. Информацию о хранимых процедурах и других подобных аномалиях ищите на веб-страницах по Win32: : ODBC.

Вот как выглядит программа:

use Win32::ODBC;

print "Введите имя пользователя: ";

chomp($user = <STDIN>);

print "Введите пароль для $user: "; chomp($pw = <STDIN>);

$dsn="sysadm"; и имя источника данных, которое мы используем

# ищем доступные DSN, создаем переменную $dsn,

если она еще не существует

die "Невозможно запросить доступные DSN",Win32::ODBC::Error()."\n"

unless (%dsnavail = Win32::ODBC::DataSources());

if (Idefined $dsnavail{$dsn}) {

die "невозможно создать DSN:".Win32::ODBC::Error()."\n"

unless (Win32::ODBC::ConfigDSN(ODBC_ADD_DSN. "SQL Server", ("DSN=$dsn",

"DESCRIPTION=DSN for PeriSysAdm",

"SERVER=mssql.happy.edu". "DATABASE=master",

"NETWORK=DBMSSOCN".

библиотека сокетов TCP/IP ))); }

it

соединение с основной базой данных

$dbh = new Win32: :ODBC("DSN=$dsn;UID=$iJSer:PWD=$pw: "):

die "Невозможно соединиться с DSN $dsn:".Win32:

# ищем базы данных на сервере

if (defined $dbh->Sql(q{SELECT name from sysdatabases})){

die "Невозможно послать запрос к базе данных:" .Win32: :ODBC: Error().

while ($dbh->FetchRow()){

push(@dbs, $doh->0ata("name"));

}

$dbh->DropCursor();

п

ищем пользовательские таблицы в каждой базе данных

foreach $db (@dbs) {

if (defined $dbh->Sql("use $db")){

die "Невозможно изменить базу данных на $db:" .

Win32::ODBC::Error() . "\n"; >

print "---$db---\n"; @tables=(); if (defined $dbh->

Sql(q{SELECT name from sysobjects

WHERE type="U"})){ die "Невозможно запросить таблицы из $db:" .

Win32::ODBC::Error() . "\n"; } while ($dbh->FetchRow()) {

push(@tables,$dbh->Data("name")); > $dbh->DropCursor();

ft ищем информацию о полях для каждой таблицы

foreach Stable (©tables) { print "\t$table\n";

if (defined $dbh->Sql(" {call sp_columns (\'$table\')} ")){

die "Невозможно запросить поля из таблицы Stable:".

Win32::ODBC::Error() . "\n"; >

while ($dbh->FetchRow()) { @cols=();

@cols=$dbh->Data("COLUMN_NAME","TYPE.NAME","PRECISION");

print "\t\t",$cols[0]," [",$cols[1],"(",$cols[2],")]\n";

} $dbh->DropCursor();

}

}

$dbh->Close();

"SQL Server","DSN=$dsn"))

Информация о модулях из этой главы

Модуль	Идентификатор на CPAN	Версия
DBI	TIMB	1.13
Msql-Mysql-модули (OBD: :mysql)	JWIED	1.2210
DBD: : Sybase		MEWP	0.21
Win32 :: ODBC (с http://www.roth.net)	GBARR	970208

Использование DBI

Вот что следует делать для использования DBI. Информацию о самом DBI можно найти в книге «Programming the Perl DBI» Аллигатора Декарта (Alligator Descartes) и Тима Банса (Tim Bunce) (O'Reilly).

Шаг 1: Загрузите нужный модуль

Здесь нет ничего особенного, нужно лишь написать:

use DBI;

Шаг 2: Соединитесь с базой данных и получите дескриптор соединения

Код на Perl, устанавливающий DBI-соединение с базой данных MySQL и возвращающий дескриптор базы данных, выглядит примерно так:

соединиться с базой данных $database используя заданное имя

Sdatabas

$dbh = DBI-->comect("D8I:nysal :Sdatabase Saseriare $pw);

die "Онибка Н-'-юзмолно соединиться:

$dbh);

До соединения с сервером DBI загрузит низкоуровневый DBD-драйвер (DBD: :^ysc:!). Перед тем как двигаться дальше, проверим, что соединение (при помощи con пес: ()) установлено. DBI предоставляет два параметра для connect () - Raise-Error и Print Er , определяющие, будет ли DBI выполнять эту проверку или сообщит об ошибках, когда они возникнут. В частности, если применить:

$dbh = DBI->connect("DBI:mysql:$database",

$username,$pw,{RaiseError =

то DBI вызовет die, при условии, что соединение с базой данных не произошло.

Шаг 3: Отправьте команды SQL-серверу

Когда модуль загружен и соединение с сервером баз данных установлено, начинается самое интересное. Пошлем серверу несколько SQL-команд. Мы будем использовать запросы, приведенные в качестве примеров из приложения D. В этих запросах для заключения в кавычки применяется оператор q (т. e. something записывается как q{something}), следовательно, не нужно беспокоиться о кавычках внутри запросов. Вот первый из двух DBI-методов, существующих для отправки команд:

$results=$dbh->do(q{UPDATE hosts

SET bldg = 'Main'

WHERE name = 'bendir'});

die "Невозможно выполнить обновление:

$DBI: :errstr\n" unless (define;; Sresults);

Переменная Sresuits равна либо количеству обновленных записей, либо uricief, если произошла ошибка. И хотя важно знать, сколько записей было обработано, такой метод абсолютно не подходит для операторов, подобных SELECT, где необходимо увидеть сами данные. Следовательно, нужно применить второй метод.

Для использования второго метода необходимо сначала подготовить оператор SQL (с помощью команды оч-иге), а затем выполнить (execute) его на сервере.

Команда рrераrе() возвращает нечто новое, чего мы раньше никогда не встречали: дескриптор команды. Так же, как дескриптор базы данных ссылается на открытое соединение с базой данных, дескриптор команды ссылается на конкретный SQL-оператор, который был подготовлен с помощью р"есагс(). Получив дескриптор команды, можно использовать exucir.u для отправки запроса на сервер. Позже подобный дескриптор команды будет применяться для получения результатов запроса.

Вероятно, вас удивляет, зачем сначала подготавливать оператор, вместо того чтобы напрямую выполнить его. Подобная операция позволяет драйверу DBD (или, что более вероятно, вызываемой библиотеке) выполнить синтаксический разбор SQL-запроса. После того как оператор подготовлен, можно повторно выполнить его через дескриптор команды, не проводя больше синтаксический анализ. Зачастую это основной фактор повышения эффективности. В действительности, DBI-метод do(), используемый по умолчанию, для каждой выполняемой команды применяет сначала рrераrе(), а затем execute().

Как и вызов do, рассмотренный ранее, метод execute() возвращает количество обработанных записей. Если результатом запроса является ноль записей, возвращается строка ОЕО, эта строка при логической проверке воспримется как «истина». Если драйверу неизвестно, сколько записей было обработано, возвращается -1.

Перед тем как перейти к ODBC, стоит упомянуть еще об одной особенности prepared), поддерживаемой большинством DBD-модулей, а именно о заполнителях (placeholders). Заполнители, также называемые позиционными маркерами, позволяют подготовить команду с оставленными в ней «дырами», в которые можно внести значения во время выполнения команды (executeO). Подобное свойство помогает конструировать запросы на лету, не затрачивая лишнего времени на их синтаксический разбор. Знак вопроса используется как заполнитель для одного скалярного значения. Вот пример кода на Perl, демонстрирующий такое применение заполнителей:

©machines = qw(bendir shimmer sander);

$sth = $dbh->prepare(q{SELECT nair.e, ipaddr FROM hosts WHERE naire = 9}):

foreach Sname (&machines){ $sth->execute($narr.e):

Каждый раз, проходя через цикл гегемон, запрос SELECT выполняется с разными условиями WHERE. Можно применять и несколько заполнителей:

$str->prepare;

q{SELECT ria.T.o :nad''!" f ROM t ou's WHERE (name = ? AND bldg = ? AND dept = ?)}):

$sth->execute($nan-!0t $bldg, Sdept);

Теперь, когда известно, как получить количество записей, обрабатываемых запросом, отличным от SELECT, посмотрим, как получить результаты запросов SELECT.

Шаг 4: Получите результаты запроса SELECT

Механизм, рассмотренный здесь, похож на историю о курсорах из приложения D. Когда серверу при помощи execi::p() посылается запрос SELECT, применяется способ, позволяющий возвращать результаты построчно.

Для получения данных в DBI используется один из методов, перечисленных в табл. 7.1.

Таблица 7.1. DBI-методы получения данных

Имя	Возвращает	Возвращает, если больше нет записей
fetchrow_arrayref ( )	Ссылка на анонимный массив со значениями, являющимися полями следующей записи	undef
fetchrow_array()	Массив со значениями, являющимися полями следующей записи	Пустой список
fetchrow_hashref ( )	Ссылка на анонимный хэш, ключами которого являются имена полей, а значениями - значения полей следующей записи	undef
fetchall_arrayref ()	Ссылка на массив массивов	Ссылка на пустой массив

Посмотрим, как работают эти методы в нашем случае. Для каждого из примеров будем считать, что перед вызовом метода выполнено следующее:

$sth = $dbh->prepare(q{SELECT name,ipaddr,dept from nosts}) or

die "Невозможно подготовить запрос: ".$dbh->errstr." \n":

$sth->execute or die "Невозможно выполнить запрос: ". $dbh-->orr

Вот метод fetchrow_arrayref () в действии:

while (Saref = $sth->fetchrow_arrayref){ print "name: " .

$aref~>[0] . "\n"; print "ipaddr: " . $aref->[1] . "\n";

print "dept: " . $aref->[2] . "\n":

А теперь рассмотрим «удобный» метод fe*chall_array"of (). Он загружает весь полученный результат в одну структуру данных, возвращая ссылку на массив ссылок. При использовании данного метода будьте осторожны и учитывайте размер запросов, поскольку результаты целиком считываются в память. Если размер результата равен 100 Гбайт, это может вызвать проблемы.

Каждая ссылка выглядит точно так же, как и результат вызова метода fetchrow_an ayref ().

Вот какой код выводит результат запроса целиком:

$aref_aref = $sth->fetchall_arrayref;

foreach $rowref (@$aref_aref){

print "name: " . $rowref->[0] . "\n";

print "ipaddr: " . $rowref->[1] . "\n";

print "dept: " . $rowref->[2] . "\n";

print '-'x30."\n": }

Этот пример применим только к конкретному набору данных, поскольку предполагается, что количество полей фиксировано и они следуют в определенном порядке. Например, считается, что имя машины возвращается в первом поле запроса ($rowref->[0]).

Можно переписать предыдущий пример и сделать его более общим, если использовать атрибуты (часто называемые метаданными) дескриптора команды. В частности, если после запроса посмореть на $sth->{NUM_OF_FIELDS}, то можно узнать количество полей в полученных данных. $sth->{NAME} содержит ссылку на массив названий полей.

Обязательно изучите документацию по DBI, чтобы подробно узнать об остальных метаданных.

Шаг 5: Закройте соединение с сервером

С DBI это очень просто сделать:

сообщаем серверу, что данные из дескриптора команды больше

и не нужны (необязательно, т. к. мы собираемся завершить

# соединение)

$sth->finish;

ft разорвать соединение дескриптора с базой данных

$dbh->disconnect;

Что еще надо сказать о DBI

Осталось еще две темы, которые стоит обсудить, прежде чем переходить к ODBC. Во-первых, это набор методов, которые я называю «удобными» (shortcut methods). Методы, приведенные в табл. 7.2, объединяют перечисленные шаги 3 и 4.

Таблица 7.2. Удобные методы DBI

Название	Объединяет в себе следующие методы
select row_a r ray ref (Sstmnt)	prepare(Sstmnt), execute( ), fetchrow_arrayref ( )
selectcol_arrayref ($stmnt)	prepare($stmnt), execute( ), (@{fetchrow_arrayref()})[0] (т. е. возвращает первое поле для каждой записи)
select rowar ray (Sstmnt)	prepare(Sstmnt), execute( ), fetchrow_array( )

Во-вторых, заслуживает внимания способность DBI связывать переменные с результатами запроса. Методы bind_coL() и bina_ccl :r.r s() используются для автоматического помещения результатов запроса в указанную переменную или список переменных. Обычно это заменяет дополнительный шаг, а то и два при написании программы. Ниже приведен пример, включающий bind_colu"ns():

die "Невозможно выполнить запрос:".$dbh~>errstr" \n";

К эти переменные получат 1-й, 2-й и 3-й столбы из SELECT

$rc = $sth->Pind_columns(\$name, \$ipaddr, \$dept):

while ($sth->fetchrow_arrayref){

tt

$name, Sipaddr и $dept автоматически получают значения из

tt

результатов запроса

Использование ODBC

Основные шаги при использовании ODBC похожи на только что рассмотренные действия с DBI.

Шаг 1: Загрузите нужный модуль Perl

use Win32::ODBC;

Шаг 2: Соединитесь с базой данных и получите дескриптор соединения

Перед тем как установить соединение в ODBC, следует выполнить один дополнительный шаг. Нужно создать имя источника данных (Data Source Name, DSN). DSN - это именованная ссылка, в которой хранится конфигурационная информация (например, имя сервера и базы данных), необходимая для доступа к источнику информации, такому как SQL-сервер. Имена источников данных бывают двух типов:

пользовательские (user) и системные (system), различающиеся тем, будет ли соединение доступно одному пользователю на машине либо любому пользователю или службе.

DSN можно создать либо из панели управления ODBC в Windows NT/2000, либо программным образом из Perl. Мы пойдем вторым путем, хотя бы для того, чтобы не вызывать насмешек со стороны пользователей Unix. Вот как можно поступить для создания пользовательского имени источника данных на сервере MS-SQL:

и создаем пользовательское имя источника данных на Microsoft-SQL-сервере

# Замечание: чтобы создать системное имя источника данных,

замените ODBC_ADD_DSN на ODBC_ADD_SYS,DSN

if (Win32::ODBC::ConfigDSN(

ODBC_ADD_DSN,

"SOL Server",

("DSN=PerlSysAdfr-", "DESCRIPTION=DSN for PerlSysAdm"

"SERVER=mssql.happy.edu".

имя сервера

"ADDRESS=192.168, 1. 4".

it IP-адрес сервера

"DATABASE=sysadm",

"NETWORK=DBMSSOCN".

print "DSN создам--} else {

После того как имя источника данных создано, его можно использовать для открытия соединения с базой данных:

соединяемся с DSN, возвращаем дескриптор базы данных

$dbh=new Win32::ODBC("DSN=PeriSysAdm;UID=$username; PWD=$pw; ");

die "Невозможно соединиться с DSN PerlSysAdm:" . Win32::ODBC::Erroi() "\n"

unless (defined $dbh);

Шаг З: Отправьте команды SQL на сервер

ODBC-эквивалент DBI-командам do(), prepare() и execute() немного проще, потому что модуль Win32: :ODBC имеет один метод Sql() для отправки команды на сервер. Хотя в ODBC теоретически упоминается о подготовке команды и заполнителях, это не реализовано в текущей версии модуля Win32: : ODBC. В Win32: : ODBC также не используются дескрипторы команд; взаимодействие происходит через дескриптор базы данных, открытый ранее при помощи метода new. Так что нам остается команда с простейшей структурой:

$rc = $dbh->Sql(q{SELECT « from hosts});

Есть разница между методами ODBC и DBI: в отличие от do() из DBI, Sql() возвращает undef, если запрос был завершен успешно, и некоторое ненулевое значение, если запрос не был выполнен.

Если необходимо узнать, сколько записей было обработано в результате запроса INSERT, DELETE или UPDATE, следует использовать метод RowCount(). В документации по Win32: :ODBC сказано, что этот вызов реализован не во всех драйверах ODBC (либо реализован не для всех операторов SQL), поэтому не стоит слепо полагаться на драйвер, лучше сначала все проверить. Как и в случае с методом ехе из DBI, RowCount() вернет 1, если драйверу недоступна информация о количестве полученных записей.

Шаг 4: Получите результаты запроса SELECT

Получение результатов запроса SELECT для ODBC выполняется подобно тому, как это было сделано для DBI, но с одним отличием. Во-первых, получение

данных с сервера и обращение к ним являются двумя разными шагами в Win32: : ODBC. Метод FetchRow() получает следующую запись, возвращает 1, если все прошло успешно, и undef, если что-то было не так. Когда запись получена, можно выбрать один из двух методов, чтобы обратиться к ней.

Метод Data() возвращает список полученных полей, если вызывается в списочном контексте, и все поля, склеенные вместе, если вызывается в скалярном контексте. Data() может принимать в качестве необязательного аргумента список, определяющий, какие поля возвращать и в каком порядке (иначе, в соответствии с документацией, они возвращаются в «неопределенном» порядке).

Метод DataHash() возвращает хэш, ключами которого являются имена полей. Это очень похоже на DBI-метод fetchrow_hashref(), с тем исключением, что он возвращает хэш, а не ссылку на хэш. Как и Data(), DataHash() тоже может принимать список в качестве необязательного аргумента для определения того, какие поля возвращать.

В нашем случае эти методы выглядят так:

if ($dbh->FetchRow()){

@ar = $dbh->Data();

сделать-что-то-со-значениями @ar }

и:

if ($dbh->FetchRQw()){

%ha = $doh->DataHash('name', 'ipaddr' ):

сделать-что-то-со-значемям-$Ьа1пате}

Справедливости ради надо отметить, что информацию, передаваемую через атрибут дескриптора команды {NAME} в DBI, в мире Wi'i3? : : ODBC можно получить. Для того чтобы узнать количество полей (как в {NUM_OF_F-ItLDS}), придется пересчитать элементы в списке, возвращенном методом Р- Lelcifb).

Шаг 5: Закройте соединение с сервером

$dbh->close(;

Если вы создали имя источника данных и хотите удалить его, чтобы убрать за собой, используйте оператор, похожий на тот, который применялся для его создания:

# замените ODBC_REMOVE_DSN на ODBC_REMOVE_SYS_DSN, если

создавали системное имя источника данных

if (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN

"SQL Server", "DSN=PKriSysAdni')){

print "DSN удален\п"; } else {

die "Невозможно удалить DSN:".Win32::ODBC::Error()."\n"; }

Теперь известно, как работать с базами данных из Perl, используя DBI и ODBC. Применим эти знания на практике и поработаем с более сложными примерами из области администрирования баз данных.

Мониторинг состояния сервера

В качестве последнего примера рассмотрим несколько способов наблюдения за состоянием SQL-сервера. Программы такого рода по своей природе похожи на службы наблюдения за сетью, уже рассмотренные в главе 5 «Службы имен TCP/IP».

Наблюдение за свободным пространством

Если на мгновение вникнуть в технические тонкости, то сервер баз данных - это место для хранения разного добра. И если места для его хранения не остается, то это либо плохо, либо очень плохо. Так что программы, помогающие следить за свободным и занятым пространством на сервере, действительно очень полезны. Посмотрим на DBI-программу, созданную для наблюдения за дисковым пространством на сервере Sybase.

Вот отрывок вывода программы, которая в графическом виде показывает, как любая база данных использует место на сервере. В каждом разделе отражено, сколько процентов пространства занято данными и журналом. В следующей диаграмме d соответствует пространству, занятому данными, а 1 - журналам. Для каждой диаграммы указан объем занятого и доступного пространства (в процентах):

| ddddddd |15.23%/5MB

hpotter--------1 |

| |0.90%/5МВ

| ddddddd |15.23%/5MB

dumbledore-----1 |

| |1.52%/5МВ

(dddddddd |16.48%/5MB

hgranger------- |

| |1.52%/5МВ

Iddddddd |15.23%/5MB

rweasley-------1

1 |3.40%/5МВ

(ddddddddddddddddddddddddddd |54.39%/2MB hagrid---------1 |

I - no log I

Вот как генерировался этот вывод:

use DBI;

Sad.Tiin = 'sa':

print "Введите паролэ дл? $adn:r-:

cho'iip($ow = <'STOIN,.-).

$dbh = DBI->connect('dbi:Sybase:',$admin,$pw);

die "Невозможно соединиться: $DBI::errstr\n" unless (defined $dbh);

ft получаем имена баз данных на сервере

$sth = $dbh->prepare(q{SELECT name from sysdatabases}) or

die "Невозможно подготовить запрос к sysdatabases: ".$abh->errstr."\n": $sth->execute or

die "Невозможно выполнить запрос к sysdatabases: ".$dbh->errstr. "\n":

while ($aref = $sth->fetchrow_arrayref) { push(@dbs, $aref->[0]);

}

$sth->finish;

tt

получаем состояние для каждой из баз данных foreach $db (@dbs) {

ff получаем и суммируем значения из поля size для всех

ft сегментов, не относящихся к журналам регистрации

$size = &querysum(qq{SELECT size FROM master.dbo.sysusages

WHERE dbid = db_id(\'$db\')

AND segmap != 4});

ft получаем и суммируем значения из поля size для сегмента,

ft соответствующего журналам регистрации $logsize = &querysum(qq

{SELECT size FROM master.dbo.sysusages

WHERE dbid = db_id(\'$db\')

AND segmap =4}):

ft переходим к другой базе данных и получаем информацию об

ft используемом пространстве $dbh->do(q{use $db}) or

die "Невозможно изменить базу данных на $db: ".$dbh->errstr."\n":

ft мы использовали функцию reserved_pgs, чтобы вернуть

ft количество страниц, используемых под данные (doampg) и

индекс (ioampg)

$used=&querysum(q{SELECT reserved_pgs(id,doampg)+reserved_pgs(id.ioampg)

FROM sysindexes

WHERE id != 8});

ft то же самое, только на этот раз получаем информацию по

ft журналам регистрации

$logused=&querysum(q{SELECT reserved_pgs(id, doampg)

FROM sysindexes

WHERE id=8}):

ft выводим информацию в графическом виде

&graph($do,Ssize,Slogsize,$used,Slogused): } $dbh->disconnect:

ft готовим/выполняем запрос SELECT, получаем сумму результатов sub querysum

{

my($query) = shift; rm/($sth $aref, Ison);

$sth = $dbh->prepare($query) or

die "Невозможно подготовить запрос $que-~, .

Sdbn- -errstr.

$sth->execute or

die "Невозможно выполнить запрос Sqjery ".

while ($aref=$stn->fetcnrow_arrayref) {

$sum += $aref->[0]: } $sth->finish;

$sum; }

# выводим в виде диаграммы имя базы данных, ее размер, размер журнала

регистрации и информацию об использовании пространства sub graph

{

my($dbname,Ssize,Slogsize,Sused,Slogused) = @_;

в строка для информации об использовании пространства данными

tt

использованное пространство и общий обьем, отведенный под данные

printf("%.2f", ($used/$size-100)V,

print "%/". (($size * $pages)/1024)."MB\n";

print Sdbname.'-'x(14-length($dbname)).'-|'.(' 'x 49)."|\n";

if (defined Slogsize) { n строка для информации об

tt

использовании пространства под журналы регистрации

print ' 'х15 . "|- no log".(' 'х 41)."|\п": }

print "\n"; }

Читатель, разбирающийся в SQL, вероятно, удивится, зачем использовать специальную подпрограмму для суммирования данных из одного столбца вместо того, чтобы применить отличный оператор SUM из SQL. querysum() придумана только в качестве примера того, что можно сделать на лету из Perl. Подпрограмма на Perl подходит, скорее, для более сложных задач. Например, если нужно отдельно просуммировать данные, выбирая их по регулярному выражению, лучите

это сделать из Perl, чем обращаться к серверу и просить его составить таблицы (даже если это можно сделать).

Где выполняется вся работа?

Вопрос, который может возникнуть при написании SQL-программ из Perl, звучит так: «Нужно ли обрабатывать данные на сервере при помощи SQL или на клиенте при помощи Perl? » Часто SQL-функции на сервере (например, SUMO) и операторы Perl пересекаются.

Так, было бы эффективнее использовать ключевое слово DISTINCT, чтобы удалить повторяющиеся записи из возвращаемых данных, перед тем как передавать их программе на Perl, даже если эту операцию легко можно выполнить в самом Perl.

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

Насколько эффективно сервер обрабатывает определенный запрос?

Сколько данных обрабатывается?

Сколько нужно обрабатывать данные и насколько сложна эта обработка?

Каковы скорость сервера, клиента и сети (если она используется)?

Хотите ли вы, чтобы код можно было перенести на другой сервер баз данных?

Зачастую приходится испытать оба способа, прежде чем сделать выбор.

Наблюдение за использованием процессорного времени на SQL-сервере

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

use DBI:

Ssyadmin = "sa':

print "Пароль администратора Sybase:

chomp($sypw = <STDltJ>) Smsadmin = "sa";

print "Пароль администратора базы данных MS-SOL' ";

chomp($mspw = <STDIN>);

ft соединяемся с сервером Sybase

Ssydbh = DBI->connect("dbi:Sybase:server=SYBASE",Ssyadmin.Ssypw);

die "Невозмржно соединиться с сервером

Sybase: $D8I::errstr\n" unless (defined Ssydbh);

# включаем параметр ChopBlanks, чтобы удалить концевые пробелы из столбцов

$sydbh->{ChopBlanks} = 1;

п

соединяемся с сервером MS-SOL (очень здорово, что мы можем

# использовать для этого OBD::Sybase! )

Smsdbh = DBI->connect("dbi:Sybase:server=MSSQL",Smsadmin,Smspw);

die "Невозможно соединиться с сервером mssql: $DBI::errstr\n" unless (defined Smsdbh);

# включаем параметр ChopBlanks, чтобы удалить концевые пробелы $msdbh->{ChopBlanks} = 1;

$1=1; # выключаем буферизацию вывода STOOUT

и инициализируем обработчик сигнала с тем, чтобы можно было

# корректно завершиться $SIG{INT} = suu (Sbyebye = 1;};

и бесконечный цикл, который завершится при установке

ft нашего флага прерывания while (1) {

last if (Sbyebye);

и запускаем хранимую процедуру sp_monitor Ssystn = $sydbh->prepare(q{sp_monitor» or

die "Невозможно подготовить sy sp_monitor:".$sydbh->errstr."\n";

$systh->execute or

die "Невозможно выполнить sy sp_monitor;".$sydbh->errstr."\n";

цикл для получения нужных нам строк, и мы знаем, что у нас есть все, что нужно, когда мы

# получаем информацию

cpu_busy wbile($href = $systh->fetchrow_hasnref or

$systh->{syb^more_results}) {

К есть то, что нужно, перестаем спрашивать

last if (defined $href->{cpu_busy}); ! $systh->firush;

ft заменяем все, кроме %, значениями, которые мы

в получили

for (keys %{$href}) <

$nref->{$_} =" s/. '-(\d+%)/\V; }

# собираем все нужные нам данные в одну строку

$info - "Sybase: (". $nref-Xcpu_busy}. "

CPU), ". "(".$href->{io_busy}." 10), ". "(".$href->{idle}." idle) ";

отлично, тпг.ерь сделаем то же самое и для другого сервера

(MS-SQL)

Smssth = $msdbh->prepare(q{sp_monitor}) or

die "Невозможно подготовить ms spjnonitor;".

$o)sdoh->errstr. "\л': $Disstb->execute or

die "Невозможно выполнить ms spjronitor:".

$nsdbn->errstr."\n": while($href = $mssth->

fetchrow^hashref or $mssth->{syb_more_results})

{

# есть то, что нужно, перестаем спрашивать

last if (defined $href->{cpu_busy»:

}

$mssth->finish;

# заменяем все, кроме % for (keys %{$href» {

Sirifo .= "MSSQL: (" . $href->{' cpu_busy'}." CPU), ".

"C'.$href->{'io_busy'}." 10), ".

"(".$riref->{'idle'}." idle)"; print " "x78,"\r"; print $info,"\r";

sleep(5) unless (Sbyebye); }

# попадаем сюда, только если мы прервали цикл

$sydbh->disconnect;

$msdbh->disconnect;

Сценарий выводит эту строку на экран и обновляет ее каждые пять секунд:

Sybase: (33% CPU), (33% 10), (0% idle) MSSQL: (0% CPU), (0%!0), (100% idle)

Основу данной программы составляет хранимая процедура

sp_mon:tor,

существующая как на Sybase-, так и на MS-SQL-сервере.

Вывод sp_mo-nitor выглядит примерно так:

last_run current_run seconds

Аид 3 1998 12:05АМ Аид 3 1998 12:05АМ 1

cpu_busy io_busy idle

0(0)-0% 0(0)-0% 40335(0)^0%

packets^received packets_sent packet_errors

1648(0) 1635(0) 0(0)

total_read total_write total_errors connections

391(0) 180(0) 0(0) 11(0)

К сожалению, sp_monitor показывает непереносимую особенность Sybase, которая прекочевала к MS-SQL: множественные наборы результатов. Каждая из строк возвращается в виде отдельного результата. Модуль DBD: : Sybase справляется с этим, устанавливая специальный атрибут команды. Вот как возникла эта проверка:

while($href = $systh->fetchrow_hashref or

$systh->{syb_more_results}) {

и вот почему следовало выйти из цикла до того, как были замечены нужные поля:

# есть то, что нужно, перестаем спрашивать

last if (defined $href->{cpu_busy});

Сама программа будет выполняться в вечном цикле до тех пор, пока не получит сигнал прерывания (наиболее вероятно, что это будет нажатие клавиш <Ctrl>+<C> пользователем). Получив такой сигнал, мы делаем самую безопасную вещь из тех, что можно сделать с обработчиком сигнала, и устанавливаем флаг возврата. Подобную технологию рекомендуют использовать на страницах perlipc для безопасной обработки сигналов. После получения сигнала INT будет установлен соответствующий флаг, который выбросит нас из цикла на следующей итерации. Получение этого сигнала позволяет программе «деликатно» закрыть дескрипторы баз данных, перед тем как сбросить «этот бренный шум».

Эта небольшая программа всего лишь затронула возможности наблюдения за состоянием сервера, доступные нам. Было бы несложно, взяв полученные от sp_monitor результаты, построить график, чтобы получить более наглядное представление о том, как используется сервер. Но... оставим заботы об украшательстве читателю.

Учетные записи баз данных

Как упоминалось раньше, администраторы баз данных вынуждены иметь дело с рядом тех же вопросов, с которыми борются системные администраторы, в частности, с поддержкой регистрационных имен и учетных записей. Например, днем на работе мы ведем занятия по программированию баз данных. Каждый студент, посещающий эти занятия, получает регистрационное имя на сервере Sybase и свою собственную (пусть и маленькую) базу данных, с которой он может работать. Вот упрощенная версия программы, которую мы используем для создания таких баз данных и регистрационных имен:

use OBI;

и ИСПОЛЬЗОВАНИЕ: syaccreate <username>

Sadmin = 'sa';

print "Введите пароль для Sadmin: ";

chomp($pw = <STDIN>);

$user=$ARGV[0];

# генерируем фиктивный пароль, основываясь на имени

# пользователя, записанном в обратном порядке, и дополняем его

дефисами, чтобы его длина составляла 6 символов

Sgenpass = reverse join(''. reverse split(//,$user)):

Sgenpass .= "-" x (6-length($genpass));

# вот перечень SQL-команд, которые используем

tt мы: 1) создаем базу данных на устройстве USER_DISK

с журналом регистрации в USER_LOG ft 2)

добавляем регистрационное имя для пользователя

на сервере, делая новую базу базой по умолчанию

# 3) переключаемся на вновь созданную базу данных

# 4) изменяем владельца базы данных на пользователя

(^commands = ("create database Suser on USER_DISK=5 log on USER_LOG=5", "

sp_addlogin $user,\"$genpass\",Suser". "use $user", "sp_changedbowner $user"):

Я соединяемся с сервером

$dbh = DB!->connect('dbi:Sybase:',Sadmin.$pw):

die "Невозможно соединиться: $DBI: errstr•n" unless (defined Sdbh):

$dbh->disconnect:

Поскольку эта задача заключается в выполнении ряда команд, которые не возвращают данных, можно записать их в компактном цикле, в котором вызывается повторно $dbh->ao(). Можно было бы использовать полностью идентичный сценарий для удаления этих учетных записей и баз данных, когда занятия завершатся:

use DBI;

# ИСПОЛЬЗОВАНИЕ: syacdelete <username>

$admin = 'sa':

print "Введите пароло для Sadmin: ":

chomp($pw = <STDIN>);

$user=$ARGV[0];

#

перечень SQL-команд, которые мы будем использовать;

мы удаляем базу данных пользователя

и удаляем регистрационное имя с сервера

©commands = ("drop database $user", "sp_droplogin Suser");

# соединяемся с сервером

$dbh = OBI->connect('dbi:Sybase:',$admin,$pw);

die "Невозможно соединиться: $DBI::errstr\n" unless(defined $dbh);

# обходим в цикле массив команд, выполняя их последовательно for (©commands) {

$dbh->do($_) or die "Невозможно $_: " . $dbh->errstr . "\n": }

$dbh->disconnect or warn "Невозможно рассоединигься: " . $dbh->errstr . "\n":

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

Проверка паролей

Утилита соединяется с сервером и получает список баз данных и учетных записей; затем пытается установить связь, применяя ненадежные пароли (регистрационные имена, пустые пароли, пароли по умолчанию).

Карта пользователей

Создаем список регистрационных имен и баз данных, доступных пользователям с этими регистрационными именами.

Управление паролями

Система ограничения срока действия паролей.

Взаимодействие с SQL-сервером из Perl

Существует два стандартных способа взаимодействия с SQL-сервером: DBI (DataBase Interface) и ODBC (Open DataBase Connectivity). Когда-то DBI был стандартом Unix, a ODBC - стандартом Win32, но эти границы начали расплываться после того, как ODBC стал доступным в мире Unix, a DBI был перенесен на Win32. Еще сильнее стирает эти границы пакет DBD: :ODBC - DBD-модуль, «говорящий» на ODBC из DBI.

DBI и ODBC очень похожи как в своем предназначении, так и в использовании, поэтому рассматриваться они будут одновременно. И DBI, и ODBC можно считать «промежуточным программным обеспечением» (middleware). Они создают уровень абстракции, позволяющий программисту писать программы, применяя вызовы DBI/ODBC, не имея представления об API какой-либо конкретной базы данных. Передать эти вызовы на уровень, зависящий от баз данных, дело DBI ODBC. Модуль DBI обращается для этого к драйверу DBD; менеджер ODBC вызывает зависящий от источника данных ODBC драйвер, который заботится обо всех частностях, необходимых для соединения с сервером. В обоих случаях получается по меньшей мере трехъярусная модель:

Лежащая в основе система управления базами данных (Oracle, MySQL, Sybase, Microsoft SQL Server и т. д.).

Уровень, зависящий от базы данных, который выполняет созданные программистом запросы к серверу. Программисты не работают с этим уровнем напрямую; они используют третий ярус. В DBI с этим уровнем имеет дело специальный модуль DBD. Для взаимодействия с базой данных Oracle будет применяться модуль DBL): : Огчс : В процессе компилирования модули DBD обычно связываются с клиентской библиотекой данного сервера, поставляемой создателем сервера. В ODBC с этим уровнем работает ODBC-драйвер, зависящий от источника данных и устанавливаемый поставщиком.

Мост над пропастью между базами данных Unix и NT/2000

Очень часто системные администраторы, работающие в многоплатформенном окружении, задают вопрос: «Как я могу использовать Microsoft SQL Server из Unix?» Если центральная система администрирования или наблюдения построена на Unix, то установка нового MS-SQL-сервера представляет собой трудную задачу. Я знаю три способа справиться с этой ситуацией. Второй и третий способы не зависят от SQL-сервера, поэтому даже если вы применяете не Microsoft SQL-сервер, однажды они могут вам пригодиться.

Скомпилируйте и используйте DBD: : Sybase. Модуль DBD: : Зуиазн потребует некоторых библиотек для соединения с базой данных. Существует два набора библиотек, а этого более чем достаточно. Первый набор - библиотеки Sybase OpenClient - может быть доступен для вашей платформы (так, они бесплатно распространяются с некоторыми дистрибутивами Linux как часть пакета Sybase Adaptive Server Enterprise). Если вы используете MS-SQL-сервер версии 6.5 или ниже, то DBD: :Sybase, собранный с этими библиотеками, будет работать. Если это сервер версии 7.0 или выше, для совместимости может понадобиться «файл-заплата» от Microsoft. Информацию о нем следует искать на http://su.pport.Microsoft.com/suppor-t/kb/articlcti/ q239/8/83.asp (KB статья Q239883). Второй вариант - установить библиотеки FreeTDS, которые можно найти на http:// www.freetds.org. Изучите инструкции на этом сайте, чтобы собрать нужную версию для своего сервера.

Используйте OBD: :Proxy. Этот модуль DBD входит в состав DBI. Он позволяет на машине с MS-SQL-сервером запустить дополнительный маленький, который будет служить прозрачным прокси-сервером для запросов от Unix-клиентов.

Получите и применяйте Unix ODBC из DBD; :ODBC. Некоторые разработчики, включая MERANT (http://www.merant.com) и OpenLink Software (http://www.openlinksw.com), продают такое программное обеспечение; но стоит попытаться использовать то, что было создано разработчиками из проекта Open Source. Подробную информацию можно найти на странице freeODBC Брайана Джепсона (Brian Jepson) на http://users.ids.net/-bjep son/freeODBC. Вам понадобится и драйвер ODBC для Unix-платформы (разработанный производителем базы данных) и менеджер ODBC (подобный urixODBC или iGDBC).

Уровень независимого от базы данных интерфейса прикладного программирования (API). Очень скоро мы будем писать сценарии на Perl, взаимодействующие с этим уровнем. В DBI он известен как уровень DBI (т. е. будут выполняться DBI-вызовы). В ODBC обычно происходит взаимодействие с менеджером ODBC-драйверов через вызовы ODBC API.

Прелесть ситуации состоит в том, что код, написанный для DBI или ODBC, можно без осложнений переносить с одного сервера на другой. API-вызовы остаются прежними, поскольку не зависят от используемой базы данных. Эта идея остается справедливой практически во всех случаях программирования баз данных. К сожалению, программы, которые мы будем писать (т. е. предназначенные для администрирования баз данных), обречены зависеть от сервера, т. к. практически нет двух серверов, которые администрировались бы хоть отдаленно похожим образом. Опытные системные администраторы любят переносимые решения, но, увы, их не ожидают.

Впрочем, это грустные размышления, лучше посмотрим, как использовать DBI и ODBC. Обе технологии выполняют одни и те же действия, поэтому может показаться, что в объяснениях или по крайней мере в заголовках присутствует избыточность.

В следующем разделе мы будем исходить из того, что сервер баз данных и необходимые модули Perl уже установлены. В некоторых примерах кода для DBI будет использоваться сервер MySQL; а для ODBC -Microsoft SQL Server.

Документирование сервера

Информация о модулях из этой главы

Использование DBI

Использование ODBC

Мониторинг состояния сервера

Рекомендуемая дополнительная литература SQL

Учетные записи баз данных

Взаимодействие с SQL-сервером из Perl