Работа с MySql
Работа с MySql
Cухинин A., Webscript.ru
В последнее время весьма популярной базой данных стала MySql
Если я не ошибаюсь, то язык Perl занимает главенствующее положение в области Web-приложений. Основная масса CGI - скриптов и разнообразных информационных/управляющих систем на Web интерфейсе в данное время написано именно на Perl. Для этого языка накоплен огромный мировой опыт программирования, нашедший свое воплощение в библиотеке CPAN
Для использования библиотеки DBD необходимо наличие библиотеки DBI, поэтому они должны быть установлены на компьютере, где используется скрипт. Кроме того, необходимо наличие сервера MySql где-нибудь поблизости от вас для тестовых работ.
Итак,
В первую очередь,
вы должны договориться с сисадмином сервера предоставить вам доступ к MySql с возможностью создавать базу или хотя бы таблицы в базе. После этого, можно протестировать подключение к MySql серверу:
#!/usr/bin/perl use DBI; my $dbh = DBI->connect("DBI:mysql:database=ваша_база_данных;host=адрес_сервера_mysql", "логин", "пароль") || die $DBI::errstr; @tables = $dbh->func( '_ListTables' ); foreach $line(@tables) { print $line,"\n"; } $dbh->disconnect();
При этом, если все нормально, вы на выходе этого скрипта должны ничего получить что-то типа:
таблица1 таблица2 таблица3
Где таблица1 - наименование таблиц в вашей базе данных.
Если соединение создать не получилось, тогда что-то неправильно, или не тот хост, пароль, логин или нет такой базы - например, если нет базы данных, то будет что-то типа:
Unknown database 'ваша_база_данных' at try.pl line N.
Если нет доступа пользователю под "логин" или неправильные логин или пароль, то будет выдано сообщение
Access denied for user: 'логин@ваш_хост.домен' (Using password: YES) at try.pl line N.
Адрес сервера может быт как каноническим вида dbserver.domain.com (если корректно работет DNS) или абсолютным IP адресом.
Итак, если удалось успешно законнектится, то вы получите список таблиц в вашей БД. Если нет - проверьте логин/пароль, поругайтесь с сисадмином, чтобы он вам разрешил доступ с вашей машины к серверу MySql. (Только на сисадмина сильно не наезжайте - может что-то плохое сделать вам, сам знаю, сам сисадмин:)).
Теперь попробуем создать таблицу в вашей базе данных. Определимся сразу - мы сделаем небольшой каталог ссылок на ваши любимые ресурсы. Создадим таблицу, гордо именуемую "links". В таблице будут следующие поля:
| Наименование | Тип | Длина | Комментарий |
| id | TINYINT | 4 | Идентификатор, должен быть автоинкремент |
| name | VARCHAR | 64 | Нимаенование ссылки |
| url | VARCHAR | 128 | Ссылка - URL |
| category | TINYINT | 4 | Номер категории |
CREATE TABLE links ( id TINYINT, name VARCHAR(64), url VARCHAR(128), category TINYINT }
В Perl его надо исполнять следующим образом:
#!/usr/bin/perl use DBI; my $dbh = DBI->connect("DBI:mysql:database=ваша_база_данных;host=адрес_сервера_mysql", "логин", "пароль") || die $DBI::errstr; $dbh->do("CREATE TABLE links
(id TINYINT, name VARCHAR(64), url VARCHAR(128), category TINYINT)"); $dbh->disconnect();
Вот таков код для создания таблицы в базе данных.
Метод указателя базы данных do готовит и исполняет одиночную инструкцию SQL. Возвращает количество выбранных строк или undef в случае ошибки. Если возврат = -1, то количество строк неизвестно или отсутствует. В нашем случае количество строк неактуально, поэтому мы опустили оператор присваивания.
Этот метод эквивалентен следующему набору кода:
sub do { my($dbh, $statement, $attr, @bind_values) = @_; my $sth = $dbh->prepare($statement, $attr) or return undef; $sth->execute(@bind_values) or return undef; my $rows = $sth->rows; ($rows == 0) ? "0E0" : $rows; }
Как описано в таблице 1
индекс key не может создаваться по полю, которое может принимать значение null;
Значит нам нужно сначала поменять свойства поля id таким образом, чтобы поле не могло принимать значения null, было ключевым индексом, и было автоинкрементальным. вот код для этого, три SQL выражения, исполняемые методом do:
... $dbh->do("ALTER TABLE links CHANGE id id TINYINT (4) not null"); $dbh->do("ALTER TABLE links ADD PRIMARY KEY(id)"); $dbh->do("ALTER TABLE links CHANGE id id TINYINT (4) not null AUTO_INCREMENT"); ...
SQL инструкция ALTER TABLE позволяет изменять свойства таблицы. Более подробно смотри Документацию по MySql
Теперь таблица links соответствует нашим требованиям. При добавлении новой записи знаяение поля id в оной автоматически будет увеличиваться на 1.
Далее надо создать табличку категорий. Ее структура:
| Наименование | Тип | Длина | Комментарий |
| id | TINYINT | 4 | Идентификатор категории - автоинкремент |
| name | VARCHAR | 64 | Наименование категории |
CREATE TABLE category (id TINYINT not null AUTO_INCREMENT,
name VARCHAR (64) not null , PRIMARY KEY (id))
Исполняем его уже известным нам методом do :
$dbh->do("CREATE TABLE category (id TINYINT not null
AUTO_INCREMENT, name VARCHAR (64) not null , PRIMARY KEY (id))");
Теперь нужно в наши таблицы внести данные. Об этом в следующей статье
Работа с MySql - наполнение данными
Работа с MySql - наполнение данными
Сухинин A., Webscript.ru
Таблицы готовы. Теперь в них надо что-то записать. Чтобы записать, нужно туда что-то передать. вообщем, формат записи следующий:
INSERT INTO category (id, name) VALUES ('', 'Интернет')
и, соответственно, perl инструкция:
$dbh->do("INSERT INTO category (id, name) VALUES ('', 'Интернет')");
В данном случае, SQL инструкцией INSERT INTO вставляем в тадлицу category в поля id, name значения " ", "Интернет". Поле id автоматически получит следующее значение, т.е. если не было записей, то там буде значение 1.
Если у вас все ваши категории находятся в текстовом файле, то их можно подгрузить оттуда прямо в таблицу. Есть инструкция LOAD DATA INFILE. Чтобы загрузить данные из файла, который находится на локальной машине, нужно указать в инструкции ключевое слово LOCAL. Поля, в принципе могут быть разделены любым разделителем, текстовые поля могут быть заключены в кавычки. Записи закачиваются символом 'окончание строки'. SQL запрос будет выглядеть так:
LOAD DATA LOCAL INFILE '/MyDocs/categories.txt' REPLACE
INTO TABLE category FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED
BY '\"' LINES TERMINATED BY '\n'
В данном случае файл 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 выглядит так:
$dbh->do("LOAD DATA LOCAL INFILE '/MyDocs/categories.txt' REPLACE
INTO TABLE category FIELDS TERMINATED BY ';' OPTIONALLY
ENCLOSED BY '\"' LINES TERMINATED BY '\n'");
Если не получилось, проверьте расположение файлов, синтаксис.
После того, как данные импортированы, нужно простмотреть, что же у нас получилось. В SQL существует самая используемая инструкция SELECT. В нашем случае нужно написать следующий код:
use DBI; # коннектимся к серверу MySql
my $dbh = DBI->connect("DBI:mysql:database=ваша_база_данных;host=адрес_сервера_mysql", "логин", "пароль") || die $DBI::errstr; # готовим запрос
my $result = $dbh->prepare("SELECT * FROM category"); # и выполняем его
$result->execute(); # разбираем результат # результат помещается в хэш, # печатаем элементы поименно
while (my $ln = $result->fetchrow_hashref()) { print "name : $ln->{'name'} id: = $ln->{'id'}\n"; } $dbh->disconnect();
Если вs использовали данные, приведенные выше, то получите следующее:
name : Интернет id = 1 name : Развлечения id = 2 .... .... name : Новости id = 10
С категориями разобрались, база данных хранит данные и отдает по первому требованию, скрипты на Perl работают, теперь все это нужно облачить в удобоваримую Web-оболочку. Об этом в следующей статье.
Работа с MySql. Создание Web интерфейса
Работа с MySql. Создание Web интерфейса
Cухинин A., Webscript.ru
Весь смысл нашей разработки теряется, если мы ее не облачим в Web оболочку или иначе говоря, не создадим для нее Web интерфейс. В первую очередь, нас интересует список категорий. В предыдущей статье:"Работа с MySql. Введение."
был рассмотрен пример с SQL запросом, который выводит список категорий на консоль. Теперь нам надо вывести это все в HTML страницу.
Значит установим цель: вывести в две колонки список категорий, слегка облагородив его. Поступим так: изготовим любую HTML страницу с необходимым нам дизайном, и в то место, где нам нужен список категорий поставим комментарий :
<!-- CATEGORIES_HERE -->
Сей шаг мы делаем для того, чтобы скрипт не перегружать текст программы избыточным кодом. Итак, приступим:
Вывести заголовок типа данных;
Выполнить SQL запрос;
Разобрать результат;
Вставить результат в шаблон HTML страницы;
План действий составлен, можно кодировать.
>
#/usr/bin/perl # Используем библиотеку DBI
use DBI;
# Выведем заголовок типа данных
print "Content-type:text/html\n\n"; # коннектимся к серверу MySql
my $dbh = DBI->connect("DBI:mysql:database=ваша_база_данных;host=адрес_сервера_mysql", "логин", "пароль") || die $DBI::errstr; # готовим запрос
my $result = $dbh->prepare("SELECT * FROM category"); # и выполняем его
$result->execute(); # начинаем вывод в шаблон вместо комментария.
open FILE, "cat_template.html" || die ("Не могу открыть файл cat_template.html!\n"); @arr = <FILE>; close FILE; # ищем строку комментария
foreach $line(@arr) { if ($line =~ "<!-- CATEGORIES_HERE") { # если нашли,
print "<tr>\n"; # начинаем вывод таблицы
$bg = 1; # признак начатой строки таблицы
$col = 1; # номер текущей колонки
print "<table width = \"80%\">\n"; # разбор резульата SQL запроса
while (my $ln = $result->fetchrow_hashref()) { if ($bg == 1) { # начата строка таблицы
if ($col == 1) { # если первая колонка
print "<td>",$ln->{'name'},"</td>"; $col++; } else { # $col == 2 т.е. 2-я колонка
print "<td>",$ln->{'name'},"</td></tr>\n"; $col = 1; $bg = 0; }
} else { if ($col == 1) { # начинаем строку таблицы
print "<tr>&td;td>",$ln->{'name'},"&td;/td>"; $col = 2; $bg = 1; } else { # $col == 2
print "<td>",$ln->{'name'},"</td></tr>\n"; $col = 1; $bg = 0; } } } if ($bg == 1) { # если строка начата и больше нет данных в результате
print "<td> </td></tr>\n"; # надо корректно закрыть начатую строку таблицы
} print "</table>\n"; # и закрыть таблицу
} } # метод finish означает, что ссылка на текущий SQL запрос использоваться больше не будет # и его можно завершить. По идее, его нужно вызывать перед выполнением нового запроса или перед # разрывом соединения с базой данных. # Вообще-то когда все данные выбраны из результата, этот метод вызывается автоматически, # но возникают ситуации, когда его нужно вызывать принудительно. На моей машине именно так и было # У вас эта ситуация может и не возникнуть.
$result->finish(); # Разрыв соединения с сервером.
$dbh->disconnect();
В результате вы должны получить следующую таблицу (рамки включены умышленно):
Все хорошо, только толку от нашей таблицы нет никакого. Нужно, чтобы, щелкнув по категории, открывалась страница со ссылками этой категории. Для этого нужно немного дописать код нашей программы. Введем переменную "$scr_name", обозначающую файл, содержащий скрипт, который будет выводить страницу со ссылками. После строки use DBI; вставим строку $scr_name="getlinks.pl"
Далее, внутри цикла while заменим все параметры функции print .... $ln->{'name'} на
<a href=\"$scr_name?page=$ln->{'id'}\">$ln->{'name'}</a>
Должно получиться что-то типа:
print "<td>","<a href=\"$scr_name?page=$ln->{'id'}\">$ln->{'name'}</a>","</td></tr>\n";
После этой процедуры все категории в вышеприведенной таблице в результате окажутся ссылками, правла неработающими, так как сам скрипт нам предстоит только написать.
