Работа с MySql

Работа с MySql

В последнее время приобретает все большую популярность MySql — небольшой, очень шустрый, к тому же бесплатный сервер баз данных. Не лишенный, правда, недостатков, среди которых основной (на мой взгляд) — отсутствие поддержки сложных SQL-запросов. Но его скорострельность и бесплатность, по-видимому, сыграли решающую роль в склонности многих веб-мастеров использовать именно MySql в своих разработках.

Введение

Если я не ошибаюсь, то язык Perl занимает главенствующее положение в области разработки web-приложений. Основная масса CGI-скриптов и разнообразных информационных или управляющих систем, реализованных под web-интерфейс, в данное время написано именно на Perl. Тысячами Perl-программистов накоплен огромный опыт использования этого языка, нашедший свое воплощение в библиотеке CPAN. Уже написана масса библиотек для всех случаев жизни (как вам, например, bioperl — «A library of tools and modules of particular interest to biologists»?), избавляющих от изобретения всевозможных велосипедов. В данном случае мы, рассматривая взаимодействие языка Perl с сервером баз данных MySql, будем использовать библиотеку DBD::mysql.

Для использования библиотеки DBD необходимо наличие библиотеки DBI, поэтому обе должны быть установлены на компьютере, где используется скрипт. Кроме того, необходимо наличие сервера MySql где-нибудь поблизости от вас для тестовых работ (на вашей машине или в вашей сети).

Итак, в первую очередь вы должны договориться с сисадмином сервера, чтобы тот предоставил вам доступ к MySql с возможностью создавать базу или хотя бы таблицы в базе. Что для этого нужно, не мне вам рассказывать — взять пива (его много не бывает) и пойти к сисадмину. Протягивая только что открытую бутылку сисадмину со словами «У меня не получается выполнить сиквел-запрос», вы провоцируете последнего проверить ваш аккаунт в MySql-сервере, а так как его скорее всего там нет, задобренный сисадмин немедленно его создаст. .После этого можно протестировать подключение к MySql-серверу в любом текстовом редакторе (MS Word специалисты для этих целей почему-то не рекомендуют использовать), набрав следующие строки:

Если вы не уподобились «блондинке» из анекдота, то вместо ваша_база_данных вы ввели имя вашей базы данных, которую вприкуску с пивом создавали вместе с сисадмином, а вместо адрес_сервреа_mysql ввели IP-адрес компьютера, на котором установлен MySQL-сервер.

При этом, если все нормально, вы на выходе этого скрипта должны получить что-то типа:

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

Если нет доступа пользователю под «логин» или введены неправильные логин или пароль, то будет выдано сообщение

Адрес сервера может быт как каноническим вида dbserver.domain.com (если корректно работает DNS) или абсолютным IP-адресом.

Итак, если удалось успешно законнектиться, то вы получите список таблиц в вашей БД. Если нет — проверьте логин/пароль, поругайтесь с сисадмином, чтобы он вам разрешил доступ с вашей машины к серверу MySql. (Только на сисадмина сильно не наезжайте — может что-нибудь плохое сделать вам — сам знаю, сам сисадмин :-)).

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

Структура всей базы данных будет примерно такова ( рис. 1).

Как видно из схемы данных, необходимо наличие двух таблиц: Category и Links.

Каждой записи таблицы Category могут соответствовать сколько угодно записей из таблицы Links, при этом каждой записи из таблицы Links может соответствовать только одна запись в таблице Category. Для тех, кто не знаком с теорией реляционных баз данных, поясняю, что такое отношение между записями в таблицах называется «один-ко-многим». Связь между этими таблицами устанавливается посредством внесения в поле category в таблице links значение id из таблицы category, т. е. links.category = category.id.

Автоинкремент — указывает, что при добавлении записи значение поля автоматически увеличивается на заданную величину. Обычно на 1 (т. е. значение_поля++).

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

Для выполнения SQL-запроса в библиотеке DBI существует метод do. Его синтаксис таков:

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

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

Метод указателя базы данных do готовит и исполняет одиночную инструкцию SQL. Возвращает количество выбранных строк или undef в случае ошибки. Если возврат = -1, значит, количество строк неизвестно или отсутствует. В нашем случае количество строк неактуально, поэтому мы опустили оператор присваивания.

Этот метод эквивалентен следующему набору кода:

Поле id должно быть auto increment. Теперь возникают некоторые замечания:

поле AUTO_INCREMENT должно быть ключевым (первичный индекс) — key;

индекс key не может создаваться по полю, которое может принимать значение null;

Значит, нам нужно сначала поменять свойства поля id таким образом, чтобы поле не могло принимать значения null, было ключевым индексом и было автоинкрементальным. Вот соответствующий код (три SQL-выражения, исполняемые методом do):

SQL-инструкция ALTER TABLE позволяет изменять свойства таблицы. Более подробно смотри Документацию по MySql (http://www.mysql.com/) или по языку SQL.

Теперь таблица links соответствует нашим требованиям. При добавлении новой записи значение поля id в оной автоматически будет увеличиваться на 1.

Далее надо создать табличку категорий.

На сей раз мы создадим таблицу со всеми необходимыми нам атрибутами полей, — поле id будет ключевым, автоинкрементом. Вот SQL-запрос на ее создание:

Исполняем его уже известным нам методом do:

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

SQL-запрос для добавления записи

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

Этот формат справедлив применительно к нашему случаю, в целом он несколько шире — читайте мануал по SQL.

Нам нужно внести в таблицу категорий (category) запись, в которой поле name будет содержать значение «Интернет», т. е. category.name = 'Интернет':

Если вы внимательно читаете, то обратили внимание, что значение, соответствующее полю id, пустое. Все верно, т. к. поле id заведено на autoincrement, и MySQL автоматически присвоит этому полю следующий по порядку номер. Если таблица была пустая, то id = 1 и, соответственно, perl-инструкция будет такой:

Загрузка данных из файла

В данном случае, с помощью SQL-инструкции INSERT INTO мы вставляем в таблицу category в поля id и name значения " " и "Интернет". Если у вас все ваши категории находятся в текстовом файле, то их можно подгрузить из него прямо в таблицу. Есть инструкция LOAD DATA INFILE. Чтобы загрузить данные из файла, который находится на локальной машине, нужно указать в инструкции ключевое слово LOCAL. Поля, в принципе, могут быть разделены любым ограничителем, текстовые поля могут быть заключены в кавычки. Записи заканчиваются символом 'окончание строки'. В результате SQL-запрос будет выглядеть так:

В данном случае файл categories.txt находится на машине под управлением MS Windows, в каталоге C:\MyDocs. Обратите внимание на UNIX-стиль написания пути. Perl-скрипт будет брать файл с текущего диска из каталога MyDocs. Команда REPLACE в SQL-запросе означает, что необходимо замещать записи с совпадающими значениями ключей. INTO TABLE указывает имя таблицы, куда будут импортированы данные. FIELDS TERMINATED BY ';' указывает разделители полей — порядок полей должен быть таким же, как и в таблице назначения; OPTIONALLY ENCLOSED BY '\"' указывает, что поля VARCHAR взяты в двойные кавычки, и LINES TERMINATED BY '\n' указывает, что записи заканчиваются символом новой строки (\n).В случае, если вы работаете в MS Windows, вам нужно указать LINES TERMINATED BY '\r', т. к. текстовые файлы в UNIX имеют окончание строки символов \n == 0x0A, а в MS Windows \r == 0x0D.
Я экспериментировал с текстовым файлом следующего содержания:

1 «Интернет»;

2 «Развлечения»;

3 «Документация»;

4 «Софт»;

5 «Поисковые системы»;

6 «Чаты»;

7 «Для разработчиков»;

8 «Документация по ПХП»

9; «Фидо-гейты»

10 «Новости»

Как вы уже догадались, Perl-скрипт выглядит так:

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

Попробуем посмотреть базу с записями.

После того как данные импортированы, нужно просмотреть, что же у нас получилось. В SQL существует самая используемая инструкция SELECT. Формат этой замечательной инструкции таков:

или

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

Выбор нужных нам записей.

В предыдущем примере мы отобразили содержимое таблицы категорий. Мы выбирали все записи. Запросы на выборку всех записей применяются примерно в 15% случаев — обычно требуется какая-то фильтрация данных. Этот эксперимент проведем для общего развития. Например, нам нужно вывести все записи поля id, имеющие значение больше 3. В этом случае запрос будет выглядеть так:

Скрипт напишите сами — просто в предыдущем примере поменяйте в строке, содержащей $dbh-prepare, аргумент метода prepare.

Если есть потребность вывести все записи, поле name которых начинается на букву «Д», то наш запрос будет иметь следующий вид:

Из двух вышеперечисленных инструкций видно, что операции сравнения для строковых полей можно производить оператором like. А для числовых полей справедливы «нормальные» операторы сравнения. При использовании оператора like в части шаблона для сравнения можно использовать метасимвол % для обозначения любого символа, сродни символу * при определении маски файлов в операционной системе.

Если нам понадобится вывести имена категорий с номерами от 2 до 6, то инструкция будет такой:

или

Заметьте, что выбираем только поле name, а условие применяем к полю id. Если возникла нужда отсортировать вывод по наименованию, то нужно сделать следующее:

Я здесь привел наиболее распространенные способы применения простых запросов. На самом деле, инструкция SELECT — это, пожалуй, самое гениальное, что было вообще придумано на компьютере для компьютера. Если вы обратитесь к документации по языку SQL и поэкспериментируете, то ощутите мощь SQL и, в частности, инструкции SELECT.

Стоит не забывать, что все эти инструкции мы не вводим с какого-нибудь SQL-монитора, а пишем программки на Perl’е для исполнения запросов, и поэтому инструкции можно динамически строить в зависимости от условий в процессе исполнения программы и помещать в переменные, именуемые в Perl’е скалярами. Например, предыдущую инструкцию можно было изобразить на так:

Честно говоря, уже последние примеры даны для людей, которым лень или некогда :-) читать документацию. Но не зная принципов построения SQL-запросов, дальше продвигаться нельзя. Поэтому рекомендую поэкспериментировать с инструкцией SELECT и придумать другие вариации запросов.

С категориями разобрались: база данных хранит данные и отдает по первому требованию, Perl-скрипты работают — теперь все это нужно облачить в удобоваримую web-оболочку.