Три статьи по работе с MySQL

         

Работа с MySql


Работа с MySql

Cухинин A., Webscript.ru

В последнее время весьма популярной базой данных стала MySql

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

Если я не ошибаюсь, то язык Perl занимает главенствующее положение в области Web-приложений. Основная масса CGI - скриптов и разнообразных информационных/управляющих систем на Web интерфейсе в данное время написано именно на Perl. Для этого языка накоплен огромный мировой опыт программирования, нашедший свое воплощение в библиотеке CPAN

. Уже написана масса библиотек для всех случаев жизни (как вам например, bioperl - "A library of tools and modules of particular interest to biologists."?), и зачастую, вместо "изобретения велосипеда" нужно просто просмотреть список готовых библиотек Perl, наверняка, что-то уже написано по поводу решаемой вами проблемы. В данном случае, мы рассматривая взаимодействие языка Perl с сервером баз данных MySql, будем использовать библиотоеку DBD::mysql.

Для использования библиотеки 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 Номер категории
Когда со структурой таблицы понятно, изготовим SQL запрос для ее создания:

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
, поле id должно быть autoincrement. Теперь возникают некоторые замечания: поле AUTO_INCREMENT должно быть ключевым (первичный индекс) - key;

индекс 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 Наименование категории
На сей раз мы создадим таблицу со всеми необходимыми нам атрибутами полей, - поле id будет ключевым, автоинкрементом. вот SQL запрос на ее создание:

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 значения " &quot, "Интернет". Поле 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";

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

//