Perl для CGI / MySQL в Perl

	Введение в MySQL

Как Вы уже поняли, Perl-скрипты работают на сервере, и благодаря этому обладают огромными возможностями по обработке статических данных и общему доступу к этим данным. В предыдущем разделе Вы ознакомились с тем, как хранить данные в файлах и как их оттуда извлекать. Наверное у Вас возникает вопрос, а зачем тогда мне нужны базы данных? Для начала отвечаю списком. Так-что прежде чем перескакивать в следующий раздел, ознакомьтесь с ним:

1. База данных MySQL хранит данные абсолютно надежно, исключена их порча или потеря вследствии многократных модификаций при общем доступе. Поясняю: Когда Вы храните данные в файле Вам иногда приходится их извлекать. Это происходит в режиме "только чтение" и проблем с порчей данных не возникает. Однако данные иногда приходится и изменять (добавлять, изменять, удалять), вот тут-то и начинаются проблемы. Когда кто-то записывает данные в файл, другой пользователь может открыть его для чтения, чтобы прочитав, внести в файл изменения. Но данные первым пользователем могут быть записаны не до конца, тогда второй пользователь уже получит испорченный файл. После того, как первый пользователь закроет файл, второй пользователь снова сохранит этот файл, но уже в искаженном виде. Конечно существует функция flock, которая управляет доступом к файлу, но, во-первых в Windows она не работает, а во-вторых даже в Unix она не сможет защитить от ошибок в виде досрочного завершения программы из-за нехватки ресурсов и т.п. Если вышеописанная ситуация кажется Вам маловероятной, то смею Вас разочаровать - это происходит гораздо чаще, чем Вы думаете. Поверьте моему опыту, если файл изменяется чаще чем раз в сутки, при размере больше 10Кб, то в конце-концов он обязательно будет испорчен. В MySQL эти неприятности исключены. Поэтому там где требуется высокая надежность данных - для их хранения используйте MySQL.
2. MySQL работает с данными гораздо быстрее, чем Perl или любой другой интерпретатор команд. Если все Ваши данные занимают сотню строк, то конечно трудно оценить приемущества в скорости, но если записей несколько тысяч, а тем более миллионов, то без базы данных Вам не обойтись. На мощном сервере Perl обрабатывает примерно 1 000 000 средних циклов в секунду (с вызовами не менее пяти различных функций и операторов в теле цикла). А для программы даже одна секунда - многовато. Теперь рассмотрим пример: нам нужно вывести в массив 2 000 000 записей, разделенных запятыми, из файла, а затем к записям, содержащим "time=", добавить время и снова сохранить это в файле. На Perl мы напишем следующую программу (если данные храняться в однострочном файле d.txt):
   
   open (F,"<d.txt");
   $f=<F>;
   close F;
   
   @arr=split(/\,/,$f);
   
   for (my $i=0; $i<=$#arr; $i++) {
   if ($arr[$i] eq "time=") {
   $arr[$i].=time();
   }
   }
   $fld=join(',',@arr);
   chomp($fld);
   
   open (F,">d.txt");
   print F, $fld;
   close F;
   
   Даже несмотря на компактность, программа будет выполняться для 2000000 записей около двух секунд. А теперь, забегая вперед, посмотрим, как можно это сделать используя MySQL (учитывая, что данные храняться в таблице "d" базы данных "My" с одной колонкой "Fld":
   
   use Mysql;
   
   $dbh=Mysql->Connect("localhost","My");
   
   $tme=time();
   $dbh->Query("update d set Fld=concat(Fld,'$tme') where (Fld='time=')");
   
   Вот и все. Как видите код стал компактнее и программа выполнится менее чем за 1 секунду. А все дело в том, что когда нам требуется модифицировать файл, его приходится изменять полностью, а в базе данных изменяются только те данные, которые удовлетворяют условию, да и циклы всегда составляют основное время работы программ. А представьте если нужно сортировать эти данные! Тогда Perl будет выполнять два вложенных цикла и программа сортировки для двух миллионов записей затянется на несколько десятков минут, а MySQL справится за пару секунд.
3. Хранить и обрабатывать данные в MySQL гораздо удобнее. Из предыдущего примера видно насколько сокращается код Perl-программы при использовании базы данных в качестве хранилища. Кроме того не требуется изобретать собственных форматов хранения данных, использовать многочисленные открытия-закрытия файлов и кучу циклов для их обработки. При обновлении данных изменяются только необходимые поля, а не все, что есть. Так же удобнее модифицировать хранилище при последующей модификации программ. Что может быть логичнее таблицы при хранении данных? Вскоре вы убедитесь в этом.

Если я Вас убедил, то давайте разберемся, что такое MySQL и базы данных вообще. MySQL - это реляционная система управления базами данных (СУБД), основанная на стандартном языке запросов к данным SQL (Source Query Language). Сама программа представляет из себя сервер, который должен постоянно работать на компьютере. Есть и другие серверные СУБД, но именно MySQL получила широкое распространение, благодаря бесплатности и, несмотря на это, высокой скорости и надежности. Поскольку MySQL реляционная СУБД, то в ней нет никакого пользовательского интерфейса и конструкторов. Все управление, начиная от создания таблиц, кончая их удалением построено на SQL-запросах, благодаря этому имеется возможность удаленного управления своими базами данных. Сам язык SQL не сложен, и вскоре вы изучите все, что может понадобиться. Вообще по независимым исследовнниям MySQL эффективно обрабатывает таблицы, содержащие до 100000000 строк, хотя максимальный размер таблицы ограничен лишь максимальным размером файла, поддерживаемого операционной системой.

Теперь о базах данных вообще. Данные хранятся в двумерных таблицах, колонки добавляются при создании таблицы, а строки при добавлении записей. Вобщем-то записью в базе данных и называется строка, а сам элемент в этой строке - поле. База данных в MySQL - это подкаталог в каталоге /mysql/data. Имя подкаталога и есть имя базы данных. Таблицы хранятся в бинарных файлах, так быстрее. Чтобы использовать MySQL из Perl, нужно чтобы у Вашего хостинг-провайдера была установлена библиотека Mysql.pm и все вызываемые ею в каталоге /perl/lib. Чтобы проверить, установлена-ли она, нужно в одну из первых строк реально работающей программы добавить строку: use Mysql;. Если Perl-программа заработала как и раньше, то библиотеки установлены. Существуют и другие модули для работы с MySQL из Perl, но здесь мы будем рассматривать синтаксис именно Mysql.pm (не путать с синтаксисом SQL-запросов, он одинаков независимо от способа их вызова). Кроме того вам потребуется доступ к MySQL на сервере, он заключается в знании имени Вашей базы данных, имени пользователя и пароля для доступа к ней. Чаще пользователь root, пароля нет, тогда их можно и не указывать при подключении к своей базе данных.

Для отладки баз данных на своем компьютере (или вы сам себе хостинг-провайдер) нужно скачать MySQL (для Windows, около 23Мб, для других ОС зайдите на сайт: www.mysql.com) и установить ее. Запускается в Windows при помощи GUI-оболочки mysql\bin\winmysqladmin.exe. Для инсталляции поддержки MySQL в Perl соединитесь с интернетом и запустите perl\bin\ppm.bat. В командной строке наберите:

install DBI
install DBD-mysql
exit

На все вопросы отвечайте "y". Все необходимые файлы скачаются в каталог perl/lib. После этого можете использовать MySQL на своем компьютере. Для создания баз данных создавайте соответствующие каталоги в папке mysql\data. Кстати базы данных и файлы таблиц можно переностить на другой MySQL-сервер простым копированием, но для этого серверы должены быть остановлены. Отсюда вытекает один недостаток хранения данных в MySQL, поскольку непосредственного доступа к ним Вы не имеете и не можете изменять путем прямого редактирования. Однако в конце раздела мы напишем программу на Perl, позволяющую импортировать данные из ваших таблиц в текстовые файлы и наоборот.

Время вносит свои коррективы. Вышеописанные команды подойдут для Perl 5.6, но не сработают для более поздних версий. В частности для ActivePerl 5.10, для инсталяции библиотек работы с MySQL потребуются следующие действия.

В командной строке перейдите в каталог perl\bin. Для этого можно набрать:

cd c:\perl\bin\

Теперь наберите:

ppm install DBI

В ответ скорее всего выйдет:

No missing packages to install

Сие означает, что библиотека уже установлена. Если нет - подождите ее загрузки из репозитория. Далее введите:

ppm install DBD-mysql

Через некоторое время, когда загрузка закончится, введите (для проверки):

ppm install DBD::mysql

Скорее всего эта команда выдаст:

No missing packages to install

В принципе этим все предусмотренные библиотеки установлены. Только работать напрямую с DBI или DBD::mysql не очень удобно. Скачаем этот архив и распакуем из него файл Mysql.pm в perl\lib, а папку Mysql в perl\site\lib. Теперь с MySQL можно будет работать как в Perl 5.6. А как собственно, написано ниже.

	SQL

В этой главе мы ознакомимся с основными инструкциями SQL. При описании инструкций я буду пользоваться стандартом, принятым в таких случаях, то есть необязательные параметры, которые можно опустить, буду брать в квадратные скобки ([]). Разумеется эти скобки при вызове инструкций указывать нельзя. Запросы SQL равнодушны к лишним пробелам, переносам строк и регистру инструкций, так-же как и HTML. Но имена колонок в таблице уже чувствительны к регистру букв, и пробелы в именах не допускаются. Поскольку учиться лучше всего на примерах, то допустим, что нам нужно учитывать животных в кошачьем клубе.

	Создание таблиц

create table ИмяТаблицы(ИмяКолонки1 тип, ИмяКолонки2 тип, ... [Индексы])

Теперь вернемся к нашему примеру. Для начала нам нужно создать таблицу для хранения данных о кошках. Пока нас устроит такая таблица (назовем ее Animals):

На первое время мы определились с таблицей. Перед ее созданием рассмотрим основные типы данных, хранимые в таблицах.

Числа

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

[Префикс]int[(Длина)] [unsigned]

Флаг UNSIGNED означает, что числа могут быть только положительными. Соответственно и диапазон увеличивается в два раза. Например тип: tinyint может хранить числа от -128 до +127, а тип: tinyint unsigned лежит в диапазоне от 0 до 255.

Длину числа можно уменьшить, указав ее в скобках после типа, но увеличить нельзя. Например: tinyint(1), будет хранить только положительные числа (для хранения любого символа нужно 2 бита, в том числе и для знака -) от 0 до 9, а тип: tinyint(5) ошибочен.

Кроме целых чисел разумеется можно хранить дробные. Они записываются в виде:

ИмяТипа[(Длина,ЗнаковПослеЗапятой)] [unsigned]

Строки

Строковые типы данных записываются в виде:

[Префикс]char(Длина) [binary],

[Префикс]text и

[Префикс]blob

Первая строка относится только к однострочным типам малой длины CHAR и VARCHAR. Они отличаются от остальных тем, что могут хранить от 0 до 255 символов и очищают вносимый данные от начальных и конечных пробелов и переносов строк (подобно функции chomp в Perl). CHAR(n) всегда должен содержать n символов (не больше, не меньше), VARCHAR(n) может содержать от 0 до n символов. Если не указан флаг BINARY, то при запросах типа select не учитывается регистр символов, т.е. "Вася" и "ВАСЯ" считаются одним словом. В таблице мы не указываем эти типы, поскольку с ними и так все ясно. Типы данных, содержащих в имени TEXT идентичны содержащим в имени BLOB, но в последнем типе данные хранятся в двоичном виде, поэтому поиск в них осуществляется с учетом регистра ("abc" и "ABC" для BLOB разные строки). В таблице BLOB-тип я тоже не указываю, поскольку его синтаксис идентичен TEXT, просто подставьте вместо TEXT - BLOB.

Дата и время

Надо сказать, что дату и время проще формировать в Perl и хранить в текстовом или поле int (для реального timestamp, формируемого функцией time). Но тип timestamp в MySQL генерируется и изменяется автоматически при вставке и изменении записи, что может оказаться полезным при сортировке по дате. Дату можно сравнивать с текущей, она вызывается функцией curdate().

Перечисления и множества

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

enum(Значение1, Значение2, ...)

Еще один тип образует множество. То есть в поле такого типа может содержаться ряд из множества перечисленных (до 64), но никакое другое:

set(Значение1, Значение2, ...)

Модификаторы и флаги типов

К типу поля можно добавлять модификаторы, задающие дополнительные свойства для поля:

Так же после описания всех полей можно указывать ключи и индексы в виде:

ТипКлюча(Поле1, Поле2, ...)

Поля уже должны быть указаны на создание перед указанием их в качестве ключей. Вот основные ключи MySQL:

Теперь мы можем создать приведенную выше таблицу. Для этого используется следующий SQL-запрос:

create table Animals(Name varchar(20) not null, Poroda varchar(10), Date_rogdenia date not null, Hozain varchar(255), Memo text)

Обратите внимание, что название таблицы нечувствительно к регистру, если MySQL работает под управлением Windows, и чувствительно под Unix. Ведь таблица - это файл в папке mysql/data. Имена колонок тоже могут быть чувствительны или нечувствительны к регистру, в зависимости от настроек. Но лучше всегда использовать реальные имена, вплоть до регистра букв.

	Модификация (изменение) таблиц

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

alter table ИмяТаблицы Инструкция1, Инструкция2, ...

Инструкции очень похожи на инструкции при создании полей в таблице посредством запроса create table, но перед ними ставится ключевое слово. Вот список некоторых команд:

Давайте модифицируем нашу таблицу, добавив новое поле Type, указывающее на тип животного (кошка или собака), добавим пол, вес и увеличим максимальный размер клички до 255 символов:

alter table Animals add Type enum('Кошка','Собака'), add Sex enum('f','m'), add Massa float, modify Name varchar(255) not null

Обратите внимание! Всегда, когда в запросах применяются значения текстового типа или типа даты, их нужно брать в одинарные или парные кавычки (' или "), и ничего ставить не нужно, если тип числовой. Иначе будет ошибка, однако числовые типы брать в кавычки не возбраняется. При программировании на Perl лучше использовать одинарные кавычки, поскольку парные мы уже используем в выражении Perl, для подстановки переменных. Не забывайте, что: print '$var'; выведет $var, а print " '$var' "; выведет значение переменной $var.

	Удаление таблиц

Когда таблица вместе со всеми данными в ней больше не нужна ее можно удалить из базы данных запросом:

drop table ИмяТаблицы

	Добавление записей

Запись в таблицу добавляется запросом:

insert into ИмяТаблицы(ИмяПоля1, ИмяПоля2, ...) values(Значение1, Значение2, ...)

Вот теперь и начнем заполнять нашу таблицу запросами:

insert into Animals (Name,Date_rogdenia,Type,Sex,Massa) values ('Кеша','1998-08-01','Кошка','m',5)
insert into Animals (Name,Date_rogdenia,Type,Sex) values ('Норд','1992-01-12','Собака','m')
insert into Animals (Name,Date_rogdenia,Type,Sex) values ('Бишка','1998-10-22','Кошка','m')
insert into Animals (Name,Date_rogdenia,Type,Sex) values ('Муська','1995-10-22','Кошка','m')

Теперь в нашей таблице 4 записи.

	Удаление записей

delete from ИмяТаблицы [where(Условие)]

Запрос: delete from ИмяТаблицы очистит таблицу, то есть удалит все имеющиеся записи. Конструкция фильтра where, которая позволяет удалять только определенные записи, нам встретится еще не раз и мы ее рассмотрим немного позднее.

	Обновление (изменение) записей

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

update ИмяТаблицы set Поле1=Значение1, Поле2=Значение2, ... [where(Условие)]

Если опустить оператор where, то изменятся все колонки, указанные в запросе, для всех записей таблицы. Условия в MySQL строятся довольно просто. В простейшем варианте условия можно сравнивать значения полей. Если условие выполняется, то запись попадает в запрос и обрабатывается. Можно использовать логические операторы and (И) и or (ИЛИ), то есть несколько условий. При сложных условиях, для определения приоритета можно использовать скобки. Например если нам нужно обновить записи выполняющие условие1 и условие2 или условие3 и условие4, то пишем: where((условие1 and условие2) or (условие3 and условие4)

В MySQL применяются следующие операторы сравнения:

Все операторы сравнения можно без ограничений использовать для числовых полей и полей типа время и дата, для текстовых полей можно применять только = и <>. Поля можно сравнивать не только с фиксированными значениями, но и друг с другом, например так: ИмяПоля>=ИмяДругогоПоля Кроме того можно использовать не просто присваивания к полю для обновления, но и арифметические выражения, декремент-инкремент и конкатенацию. Инкремент: ИмяПоля=ИмяПоля+Цифра, декремент: ИмяПоля=ИмяПоля-Цифра (инкремент и декремент применяется только для числовых полей), конкатенация: ИмяПоля=concat(ИмяПоля,'Строка') (только для текстовых типов). Из этого видно, что ИмяПоля является подобием переменных. На самом деле в SQL-запросах тоже есть переменные, они обозначаются как индексные массивы в Perl (@var), но, думаю Вам вряд-ли это понадобится на практике, поэтому здесь мы этого рассматривать не будем.
Напоследок отметим, что текстовые типы полей сложнее в обработке, но встречаются чаще числовых. Одного прямого сравнивания тексотвых полей недостаточно на практике, чаще требуется организовать некое подобие поиска. В MySQL существует довольно много функций для работы с текстовыми полями. Например функция position(Подстрока in Поле) ищет вхождение Подстроки в указанном поле и возвращает номер позиции, где вхождение найдено, или -1, если вхождение в указанном Поле отсутствует. Эту функцию можно использовать для поиска текстовых вхождений, так как функции MySQL работают быстрее циклов Perl. Не забывайте, что функция POSITION(str IN field) будет не чувствительна к регистру, если поле field будет иметь тип text.

Давайте исправим досадную ошибку, совершенную нами при добавлении записей и дадим кошке Муське правильный пол, а заодно добавим ее вес:

update Animals set Sex='f', Massa=4.7 where (Name='Муська')

	Выборка записей

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

select ИмяПоля1, ИмяПоля2, ... || [*] from ИмяТаблицы [where(Условие)] [order by(ИмяПоля1) [desc]]

Если хотите просто вывести всю таблицу, то примените запрос: select * from ИмяТаблицы Про where мы уже говорили, а вот order by используется для сортировки. Если нужна обратная сортировка, то в конце укажите инструкцию desc. Сортировать можно по нескольким полям, таким образом инициируется группировка, поля для сортировки разделяются запятыми, но в этом случае скобки нужно опустить: order by Col1,Col2,Col3,... Кстати сортировка текстовых полей с символами кирилицы может производиться неправильно, если текстовое поле не бинарного типа. Так что если планируете сортировать данные по какому-то текстовому полю, то не задавайте для него тип TEXT, а используйте флаг BINARY для CHAR и VARCHAR, или тип BLOB!

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

select * from Animals where(Type='Кошка' and Sex='m') order by(Name)

В результате такого запроса мы получим из таблицы:

таблицу:

Если-бы нас интересовали не все поля, а только клички и вес:

select Name, Massa from Animals where(Type='Кошка' and Sex='m') order by(Name)

Можно получить уникальные значения одного или нескольких столбцов:

select distinct ИмяПоля1, ИмяПоля2, ... from ИмяТаблицы [where(Условие)] [order by(ИмяПоля) [desc]]

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

select distinct Hozain from Animals

В большинстве запросов может учавствовать не одна таблица, а несколько. Тогда имена таблиц просто разделяются запятыми. Если в этих таблицах есть одноименные поля, то для ссылки на них в многотабличных запросах нужно указать к какой таблице поле относится: ИмяТаблицы.ИмяПоля. Если в таблицах-участниках все имена полей уникальны, то можно указывать только имена полей. Если таблица в другой базе данных и доступ к ней свободен, то к ней можно обращаться так: ИмяБазыДанный.ИмяТаблицы. Разумеется и одноименные поля указываются уже более полно:

ИмяБазыДанных.ИмяТаблицы.ИмяПоля

Напоследок потренируемся на поиске текстовых вхождений при помощи запросов MySQL. Найдем все записи, содержащие в кличке вхождение "Мусь":

select * from Animals where (position('Мусь' in Name)>=0)

Данный запрос найдет только кошку под именем Муська.

Ну вот и все, что нам потребуется знать об SQL. Если хотите знать больше, обратитесь к HTML-руководству, поставляемого с MySQL. Правда это руководство на английском языке, но есть и переведенные на русский язык. Например в нашей Библиотеке.

	MySQL в Perl

До сих пор мы говорили, что Perl - не объектно-ориентированный язык, однако это не совсем так. Perl5 поддерживает объекты, но даже для очень сложных приложений вполне можно обойтись обычными переменными, поэтому в этой книге такая возможность не рассматривается. По сути объект - это более сложная переменная, вот и все. Я заговорил здесь об объектах только потому, что для работы с библиотекой Mysql.pm нам потребуется работать с ее объектами. Если Вы когда-нибудь программировали на других объектно-ориентированных языках (хотя-бы на javascript), то знаете, что для указания свойств объекта используется структура:

object[.subobject].svojstvo

То-есть имена разделяются точками. Но в Perl точки уже используются для конкатенации строк. Поэтому разделителем объекта в Perl является пара "->":

$object[->$subobject]->svojstvo;

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

Перед использованием объектов из Mysql.pm для доступа к данным MySQL нужно вызвать библиотеку строкой:

use Mysql;

	Подключение (Connect)

Итак мы включили в программу библиотеку Mysql.pm, но еще не подключились к MySQL. Для подключения требуется создать копию объекта Mysql в любой переменной. Однако просто создавать копию бессмысленно, конструктор Mysql устроен таким образом, чтобы объект при создании сразу подключался к базе данных. Для этого используется метод Connect из объекта Mysql. Это делается строкой:

$dbh = Mysql->Connect[(Хост[,БазаДанных[,Пароль,Логин]])];

Про аргументы этого метода мы говорили в самом начале раздела. Если для доступа к MySQL требуется аутентификация, то нужно указать логин и пароль, если нет, то эти аргументы можно опустить. Хост чаще всего localhost, если сервер MySQL работает на том-же компьютере, с которого и вызывается Perl-программой, иначе нужно указать другой внутрисетевой хост. Если мы планируем работать с одной базой данных, то можно прямо здесь указать ее имя, иначе для соединения с конкретной базой данных используйте:

$dbh->SelectDB(ИмяБазыДанных);

Так мы можем переключаться на другую базу данных без дополнительного подключения.

	Пересылка запросов (Query)

Подключившись к базе данных мы можем работать с ее таблицами. Работа с таблицами ведется только при помощи SQL-запросов, рассмотренных нами в предыдущей главе. Для пересылки запросов служит метод Query:

$dbh->Query("SQLЗапрос");

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

$rq = $dbh->Query("SQLЗапрос");

Как и при работе с файлами мы можем обрабатывать ошибки в случае неудачных запросов:

$rq = $dbh->Query("SQLЗапрос") || Другое действие;

Существует возможность вывода ошибки в SQL-синтаксисе на консоль:

$rq = $dbh->Query("SQLЗапрос") or die $Mysql::db_errstr;

В любом слючае, если вы ошибетесь в синтаксисе SQL, то ошибка выйдет на консоль. Но если запрос не был управляющим, а был на выборку данных, то программа продолжит выполняться, а сообщение об ошибке в библиотеке Mysql.pm будет добавлено в конец STDOUT программы.

	Список доступных таблиц (ListTables)

Чтобы проверить, существует таблица, или ее нужно создавать, идеально подходит этот метод:

@arr = $dbh->ListTables;

Таким образом мы получаем в массиве @arr все таблицы из открытой базы данных. В большинстве случаев таблицы выводятся в виде `ИмяТаблицы`, но в некототорых настройках выводится имя таблицы без апострофов.

	Свойства таблицы (ListFields)

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

$sth = ListFields $dbh ИмяТаблицы;

Теперь нам доступны следующие свойства:

	Переход к строке (DataSeek)

Позиционирует указатель на произвольную строку в данных. Первая строка начинается с 0:

DataSeek $rq НомерСтроки;

Здесь и далее $rq - дескриптор, возвращающий поток запроса, полученный методом Query (см.выше).

	Получение столбца данных (FetchCol)

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

@arr = $rq->FetchCol(НомерСтолбца);

Обратите внимание, что эта функция сбросит текущий указатель строки на конец таблицы. Вы должны использовать DataSeek(0), чтобы переустановить его. При помощи этого метода можно легко узнать количество возвращенных строк. Оно равно $#arr+1.

	Получение строки данных

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

@arr = $rq->FetchRow;

При помощи этого метода можно так-же узнать количество возвращаемых колонок. Оно равно $#arr+1.

Еще один метод возвращает ассоциативный массив строки и передвигает указатель строк на следующую. В качестве индексов массива выступают имена колонок:

%hash = $rq->FetchHash;

	Примеры

Если предыдущее описание вам было немного непонятно, то сейчас все прояснится. Когда мы рассматривали SQL-запросы, то работали с таблицей Animals. Давайте напишем программу на Perl, которая сделает все, что мы делали с таблицей Animals:

#!/usr/bin/perl
#test_mysql.cgi

use Mysql; # Подключаем библиотеку

$host="localhost"; # Эти переменные
$database="test"; # создаем
$table="animals"; # для удобства

$dbh = Mysql->Connect($host,$database); # Подключаемся к базе данных "test"

# Этот блок программы создан только для того, чтобы можно было
# использовать программу неограниченно. Без него она сработает
# только один раз, пока не существует таблица Animals.
@tables=$dbh->ListTables; # Получаем список таблиц в базе данных test
foreach $tbl (@tables) { # Проходимся по массиву с таблицами
if ($tbl eq "`".$table."`") { # Проверяем, не существует-ли уже таблица
# Animals, имя которой записано в переменной $table
$dbh->Query("drop table $table"); # Если существует, то удаляем ее
}
}
# Конец блока проверки существования таблицы

# Создаем таблицу Animals
$dbh->Query("create table $table(Name varchar(20) not null, Poroda varchar(10), Date_rogdenia date not null, Hozain varchar(255), Memo text)");
# Модифицируем ее
$dbh->Query("alter table $table add Type enum('Кошка','Собака'), add Sex enum('f','m'), add Massa float, modify Name varchar(255) not null");

# Вносим в таблицу данные
$dbh->Query("insert into $table (Name,Date_rogdenia,Type,Sex,Massa) values ('Кеша','1998-08-01','Кошка','m',5)");
$dbh->Query("insert into $table (Name,Date_rogdenia,Type,Sex) values ('Норд','1992-01-12','Собака','m')");
$dbh->Query("insert into $table (Name,Date_rogdenia,Type,Sex) values ('Бишка','1998-10-22','Кошка','m')");
$dbh->Query("insert into $table (Name,Date_rogdenia,Type,Sex) values ('Муська','1995-10-22','Кошка','m')");

# Изменяем ошибочную запись, здесь мы не используем $table только для большей наглядности
$dbh->Query("update Animals set Sex='f', Massa=4.7 where (Name='Муська')");

# Теперь выводим кошек мужского пола в порядке возрастания имени
$rq=$dbh->Query("select * from $table where(Type='Кошка' and Sex='m') order by(Name)");

@cols=$rq->FetchCol(0); # Получаем первую колонку запроса в массив @cols
DataSeek $rq 0; # Возвращаем указатель на первую строку

# Подготовка к выводу данных в HTML-таблицу
print "Content-Type: text/html\n\n";
print "<table><tr><th>Name</th><th>Poroda</th> <th>Date_rogdenia</th><th>Hozain</th> <th>Memo</th><th>Type</th><th>Sex</th> <th>Massa</th></tr>";

for ($i=0; $i<=$#cols; $i++) { # Заводим цикл для вывода данных, равный количеству строк выводимых данных
%string=$rq->FetchHash; # Получаем строку данных в ассоциативный массив
print "<tr><td>$string{'Name'}</td><td>$string{'Poroda'}</td><td>$string{'Date_rogdenia'}</td><td>$string{'Hozain'}</td><td>$string{'Memo'}</td><td>$string{'Type'}</td><td>$string{'Sex'}</td><td>$string{'Massa'}</td></tr>";
}
print "</table>"; # Завершаем таблицу

Как видно, в этой программе большую часть работы делают SQL-запросы, но для вывода данных все равно приходится использовать Perl-циклы. Однако это скорее достоинство, чем недостаток, поскольку при выводе мы получаем большую гибкость, а основная нагрузка по обработке данных ложится на быстрые SQL-запросы. Этот скрипт гарантированно будет работать на хостах с установленными библиотеками для работы с MySQL через Perl и при соединении с существующей базой данных. На некоторых хостингах потребуется изменить базу данных с test на свою и ввести при подключении свой Логин и пароль.

Ну а напоследок, как и обещал, программа, которая будет выводить данные из таблиц MySQL в текстовый файл и наоборот. В принципе можно написать программу, которая-бы самостоятельно создавала таблицу, если она не существует, но для этого пришлось-бы написать интерпретатор числовых кодов, чтобы создавать поля как у таблицы, из которой эти данные пришли. Я не буду идти так далеко и перекладываю работу по созданию таблицы на человека. Но, чтобы эта работа была проще, первая строка текстового файла будет содержать имена полей и их длины. Последующие строки в файле - это строки таблицы, разделенные двумя точками с запятой (;;). Чтобы не испортить файл лишними переводами строк и точками с запятой, которые могут встретится в данных, мы будем их заменять, а при возврате в таблицу возвращать и эти символы.

Нашу программу мы назовем datar.cgi и будем вызывать ее строкой: datar.cgi?db=БазаДанных&tb=ИмяТаблицы, если хотим экспортировать данные из таблицы в файл и: datar.cgi?db=БазаДанных&tb=ИмяТаблицы&file=ИмяФайла, если хотим импортировать данные из файла в таблицу. Файл будет иметь такое-же имя, как и таблица и хранится в папке с программой:

# Считываем строку параметров и создаем из параметров ассоциативный массив %query
$qs = $ENV{'QUERY_STRING'};

my @split = split (/&/, $qs);
foreach $split (@split) {
   ($query_key, $query_value) = split (/=/, $split);
   $query_value =~ tr/+/ /;
   $query_value =~ s/%([\dA-Fa-f][\dA-Fa-f])/ pack ("C", hex ($1))/eg;
   $query{$query_key} = $query_value;
}

# Если нет параметра db или tb, то вызов программы не корректен
if (!$query{'db'} || !$query{'tb'}) {
 print "Content-type: text/html\n\n";
 print "Не указана база данных или таблица!";
 exit;
}

use Mysql;

$dbh = Mysql->Connect("localhost",$query{'db'}); # Подключаемся к базе данных

# Если нет параметра file, то экспортируем данные
if (!$query{'file'}) {
 $data=""; # В этой переменной будем хранить данные для файла

 # Получаем имена и длины полей для первой строки файла
 $sth = ListFields $dbh $query{'tb'};

 @cols = $sth->name; # Получаем имена полей, количество столбцов=$#cols
 @len = $sth->length; # и их длины

 for (my $i=0; $i<=$#cols; $i++) {
  $data.="$cols[$i]=$len[$i]"; # Добавляем в первую строку все имена и длины полей
  # Условие: добавлять разделитель или конец строки
  if ($i==$#cols) {
   $data.="\n";
  } else {
   $data.=";;";
  }
 }

# Получаем все записи из таблицы
 $sth=$dbh->Query("select * from $query{'tb'}");
 @rows=$sth->FetchCol(0); # Получаем количество строк

 for (my $i=0; $i<=$#rows; $i++) {
  DataSeek $sth $i; # Ставим указатель строки на текущую
  @row=$sth->FetchRow; # и получаем ее
  for (my $j=0; $j<=$#row; $j++) {
   # "Чистим" поле
   $row[$j] =~ s/;/_;_/g;
   $row[$j] =~ s/\n/_CR_/g;
   $row[$j] =~ s/\r//g; # Это для Windows
   $data.=$row[$j];
   if ($j==$#row) {
    $data.="\n";
   } else {
    $data.=";;";
   }
  }
 }

 chomp($data); # Очищаем переменную от мусора
 # Открываем файл с именем таблицы и текстовым типом для записи
 open (DAT,">$query{'tb'}.txt");
 print DAT $data; # и выводим в файл полученные данные
 close DAT;
 # Выводим отчет на экран
 print "Content-type: text/html\n\n";
 $rows=$#rows+1;
 print "В файл $query{'tb'}.txt выведено строк: $rows";

} else { # Иначе импортируем данные
 # Удаляем все записи из таблицы
 $dbh->Query("delete from $query{'tb'}");
 # Открываем и считываем файл в переменную
 open(DAT,"<$query{'file'}");
 @data=<DAT>;
 close DAT;

 # Получаем имена полей из первой строки файла в переменную @names
 @temp=split(/;;/,$data[0]);
 @names=(); # Инициализируем массив
 for (my $i=0; $i<=$#temp; $i++) {
  @tmp=split(/=/,$temp[$i]);
  $names[$i]=$tmp[0];
 }
 $names=join(',',@names); # Объединяем имена полей в переменную в SQL-формате

 # Добавляем записи в существующую таблицу из файла
 for (my $i=1; $i<=$#data; $i++) {
  # Разбиваем строку на данные
  @dat=split(/;;/,$data[$i]);
  for (my $j=0; $j<=$#dat; $j++) {
   # Возвращаем замененные символы обратно
   $dat[$j] =~ s/_;_/;/g;
   $dat[$j] =~ s/_CR_/\n/g;
   # Берем значение в одинарные кавычки, если оно содержит нечисловые символы или пустое.
   $dat[$j] = "'".$dat[$j]."'" if ($dat[$j]=~/\D/ || $dat[$j] eq "" || !$dat[$j]);
  }
  $values=join(',',@dat); # Объединяем значения полей в переменную в SQL-формате
  $dbh->Query("insert into $query{'tb'}($names) values($values)"); # Добавляем запись
 }

 print "Content-type: text/html\n\n";
 print "Перенос данных в таблицу закончен.";
}

При желании всегда можно усовершенствовать этот код, но и в данном виде он работает исправно.

С.А.Семищенко