SERVER 101: Освежите ваши знания в области работы с базами данных, часть 2

Оригинал: Server 101: Brush Up Your Database Skills. Part 2
Автор: Mike Saunders
Дата публикации: 19 сентября 2016 г.
Перевод: А.Панин
Дата перевода: 26 октября 2016 г.

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

Для чего это нужно?

• Вы узнаете об особенностях работы многих веб-приложений.
• Вы сможете работать непосредственно с данными, сохраненными такими веб-приложениями, как WordPress, OwnCloud и другими.
• Вы научитесь использовать язык запросов SQL для выполнения сложных операций поиска.

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

Во второй статье серии мы углубимся в SQL и рассмотрим команды, предназначенные для модификации данных, осуществления более сложных операций поиска данных и связывания результатов поиска в множестве таблиц. После этого мы перейдем к рассмотрению вопроса осуществления доступа к базам данных из сценариев, разработанных с использованием языка программирования PHP, являющегося основополагающим при создании собственных веб-сайтов. После прочтения данной статьи вы будете обладать всеми необходимыми навыками для исследования кода OwnCloud, phpBB и многих других подобных веб-приложений, разработанных с использованием языка программирования PHP и интенсивно использующих базы данных.

Продвинутый SQL

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

mysql -ulvuser -p

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

use lvtest;
show tables;

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

select ID, Name from login_dates;

В результате будут выведены значения из столбцов ID и Name, а значения из всех остальных столбцов - отброшены. Мы можем дополнительно ограничить объем выводимых данных с помощью аналогичной команды:

select ID, Name from login_dates where ID > 1;

Для изменения данных в существующей строке таблицы мы можем использовать команду update, передав имя столбца, значение в котором следует изменить, новое значение, а также ссылку на эту строку. Например, если нам нужно модифицировать третью строку в нашей таблице и заменить "Graham" на "Ben", мы можем использовать следующую команду:

update login_dates set Name = 'Ben' where ID = 3;

Существуют и некоторые другие команды, которые стоит знать. Первая из них предназначена для удаления строки из таблицы, а вторая и третья - для добавления и удаления столбцов соответственно. Помните о том, что ни MySQL, ни MariaDB не будет препятствовать любым вашим действиям - сервер базы данных с удовольствием удалит большие объемы данных после ввода простой команды! У вас не будет возможности подтвердить свои намерения, поэтому в процессе работы с действительно важными реальными данными следует проявлять особую осторожность…

delete from login_dates where ID = 3;
alter table login_dates add Shell varchar(20) after Name;
alter table login_dates drop column Shell;

В первой команде alter мы добавляем столбец с именем Shell, который может содержать текстовые строки с максимальной длиной в 20 символов, и размещаем его после столбца Name. (Если убрать часть команды after, столбец будет просто добавлен в конец таблицы.) Вторая команда alter удаляет этот столбец и все данные, которые могут содержаться в нем.

NoSQL: другие базы данных

Реляционные базы данных используются многими веб-сайтами. MySQL/MariaDB, PostgreSQL, Oracle, Microsoft SQL Server и другие базы данных обрабатывают огромные объемы данных каждый день и, несомненно, будут продолжать использоваться в течение многих десятилетий. При этом новым, привлекающим пристальное влияние веянием в области баз данных является полный отказ от классической концепции таблиц и реляционных моделей. Для обозначения баз данных, разработанных в соответствии с этим веянием, используется термин NoSQL.

MongoDB (www.mongodb.com) является одной из самых известных баз данных NoSQL, хранящей информацию в документах JSON (JavaScript Object Notation), а не в таблицах. В документах JSON используются пары "атрибут-значение", при этом сам формат немного похож на XML и был спроектирован для упрощения разбора на уровне сценариев JavaScript. А это пример документа JSON:

{
"ID": 1,
"Name": "Graham",
"Commands": ["crontab -e", "shutdown"]
"ExitCodes": [0, 1]
}

В данном документе описано событие посещения сервера с идентификатором 1 пользователем по имени Graham, причем в полях Commands и ExitCodes несложно обнаружить массивы значений, помещенные в квадратные скобки. Потенциальные преимущества данного подхода заключаются в упрощении архитектуры баз данных и повышении производительности в случае масштабирования в рамках больших кластеров компьютеров, поэтому многие веб-сайты уже сейчас используют одну из баз данных NoSQL. (Учтите, что несмотря на свои названия, некоторые базы данных NoSQL все также позволяют использовать команды, свойственные для баз данных с поддержкой языка запросов SQL, что значительно упрощает процесс перехода.)

Работа с таблицами

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

create table commands(ID int auto_increment primary key, Command varchar(255), ExitCode int);
insert into commands values(0, 'df -h', 0);
insert into commands values(0, 'crontab -e', 1);
insert into commands values(0, 'shutdown', 1);

Если вы выполните команду select * from commands;, то увидите все данные, которые были только что добавлены в эту таблицу. А если вы рассмотрите каждую из таблиц по отдельности, вы сможете сделать вывод о том, что идентификатор 2 из столбца ID соответствует посещению сервера под именем Ben 2015-04-25, причем в рамках данного посещения была выполнена команда crontab -e, завершившаяся с кодом 1 (неудачно). Но как получить все эти данные в совокупности с помощью команды SQL? И как быть в том случае, если нам понадобится получить не наборы значений из некоторых столбцов первой и второй таблицы, а единый набор значений?

В этом случае придется воспользоваться полезной функцией SQL под названием join. Давайте представим, что нам нужно сгенерировать набор данных, содержащий значение из столбца ID, имя пользователя из таблицы login_dates и исполненную команду из таблицы commands:

select login_dates.ID, login_dates.Name, commands.Command from login_dates join commands on login_dates.ID = commands.ID;

Ну и ну, какая сложная команда! Давайте рассмотрим ее составные части по очереди. Мы начинаем нашу команду, сообщая серверу о том, что нам нужно сформировать набор данных из трех столбцов: ID и Name из таблицы login_dates и Command из таблицы commands. Мы используем команду join для вставки данных из таблицы commands в результирующий набор данных и хотим, чтобы в нем присутствовали данные из строк с одинаковыми значениями в столбце ID.

Все понятно? На Рисунке 1 представлены результаты исполнения данной команды. Синтаксис SQL и, в особенности, инструкций join, может становиться крайне сложным и именно по этой причине некоторые системные администраторы используют символы в верхнем регистре для отделения команд от имен баз данных и таблиц, как было сказано в предыдущей статье. Если вы работаете с большими наборами данных из множества таблиц, операции join могут очень помочь в получении лишь необходимых вам данных.

Рисунок 1. Результат выполнения нашей операции join, содержащий значения из столбца Name одной таблицы и из столбца Command другой.

Создавайте резервные копии ваших данных!

После наполнения базы данных большим объемом информации вам придется регулярно создавать ее резервные копии. MariaDB является очень надежной базой данных, которая, тем не менее, не спасет вашу информацию в случае отказа аппаратного обеспечения или повреждения поверхности жесткого диска. Хотя MariaDB и хранит информацию в бинарных файлах весьма сложной структуры, вы можете сгенерировать их текстовые версии с целью резервного копирования с помощью утилиты mysqldump (с интерфейсом командной строки) аналогичным образом:

mysqldump -ulvuser -p lvtest > backup.sql

Если вы откроете файл backup.sql с помощью текстового редактора, то обнаружите в нем команды SQL, необходимые для воссоздания таблиц базы данных lvtest и наполнения их информацией, поэтому вы можете воспользоваться утилитой gzip для сохранения резервной копии базы данных в более компактном формате. Позднее при необходимости воссоздания таблиц базы данных вы сможете воспользоваться стандартным инструментом mysql, передав ему имя базы данных и имя файла резервной копии:

mysql -ulvuser -p lvtest < backup.sql

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

Переходим в глобальную сеть

Работа с данным посредством клиента с интерфейсом командной строки является очень нудным и практически бесполезным в перспективе занятием, но что можно сказать по поводу альтернатив? Вы можете воспользоваться компилируемым языком программирования для создания приложения, которое будет взаимодействовать с сервером базы данных, то есть, к примеру, создать менеджер коллекций, систему управления кадрами или аналогичную программу. Но более простое (и переносимое) решение заключается в создании веб-сайта, работающего с базой данных. Благодаря существованию языка программирования PHP, это не представляет каких-либо сложностей, причем вам потребуются лишь базовые знания языка разметки HTML и приемов программирования.

Для начала вам придется установить веб-сервер Apache, интерпретатор языка PHP и модуль, который связывает оба этих программных компонента. В дистрибутиве Debian, а также в дистрибутивах, основанных на Ubuntu, вы можете установить соответствующие пакеты программного обеспечения с помощью следующей команды:

sudo apt-get install apache2 php5 libapache2-mod-php5 php5-mysql

(Если вы используете дистрибутив из отличного семейства, вам придется самостоятельно искать с помощью менеджера пакетов пакеты с аналогичными именами, которые будут содержать упомянутые программные компоненты.) После того, как все необходимое программное обеспечение будет установлено, следует перейти по адресу http://localhost (или http://127.0.0.1) с помощью вашего веб-браузера для того, чтобы убедиться в корректности работы веб-сервера Apache на локальной машине. Если вы увидите сообщение "It works", вы можете начинать работу с PHP.

Перейдите в директорию /var/www/html и создайте в ней файл с именем test.php со следующим содержимым:

<?php
	echo "PHP works!";
?>

Если вы никогда не работали с PHP ранее, вам стоит знать, что этот язык программирования имеет C-подобный синтаксис, причем весь код должен размещаться между тэгами <?php и ?> для отделения кода PHP от кода HTML. Теперь вы можете открыть адрес http://localhost/test.php с помощью вашего веб-браузера и в том случае, если интерпретатор языка программирования был установлен корректно, вы увидите сообщение "PHP works". Теперь мы точно готовы двигаться дальше!

Для работы с базой данных в первую очередь следует инициировать соединение и ассоциировать его с соответствующим объектом. В том случае, если соединение не удастся установить, нам придется завершить исполнение сценария (с помощью вызова die) перед тем, как сделать что-либо еще; в противном случае созданное соединение должно быть использовано для выполнения запроса, причем результат этого запроса должен быть сохранен в переменной. Далее должен быть осуществлен обход элементов списка результатов запроса с обработкой отдельных значений из полей базы данных. Давайте используем PHP для получения данных из нашей таблицы login_dates и их вывода; вам придется сохранить следующий код в файле test.php:

<?php $conn = mysqli_connect("localhost", "lvuser", "pass123", "lvtest"); if(!$conn) die("Couldn't connect"); $result = $conn->query("select * from login_dates"); while($row = $result->fetch_assoc()) echo $row['ID'] . ", " . $row['Name'] . ", " .$row['Login'] . "<br />"; ?>

Большая часть приведенного выше кода должна быть очевидной. Функция mysqli_connect() реализована в рамках пакета php5-mysql, который мы установили ранее, причем мы передаем ей четыре параметра: имя узла или IP-адрес сервера, с которым мы хотим соединиться, имя пользователя, пароль, соответствующий этому имени пользователя, а также имя базы данных, которую мы хотим использовать. Эта функция возвращает объект, который сохраняется нами в переменной $conn. После этого мы проверяем наличие значения переменной $conn, ведь если эта переменная не содержит значения, значит нам не удалось установить соединение по какой-либо причине (такой, как некорректные параметры учетной записи или неработоспособность сервера базы данных), поэтому стоит завершить исполнение сценария выводом сообщения об ошибке.

Если все заработает, мы выполним SQL-запрос таким же образом, как в случае с клиентом с интерфейсом командной строки, сохраняя его результаты в созданной переменной $result. Две последние строки могут немного смутить вас: по сути, после выполнения запроса переменная $result будет содержать множество строк из базы данных. Поэтому мы используем цикл while для того, чтобы обойти все эти строки и извлечь их содержимое в ассоциативный массив, то есть, массив, в котором каждый элемент имеет свое имя. В нашем случае эти имена элементов будут именами столбцов нашей таблицы, а именно, ID, Name и Login.

С помощью оператора echo языка программирования PHP мы преобразуем наборы элементов этого массива в отдельные строки, объединяя по три элемента в рамках одной строки с добавлением запятых, символов пробела и тэга <br /> в конце для читаемости результатов. В конечном итоге вы должны получить то, что показано на Рисунке 2, то есть, версию нашей таблицы login_dates в формате примитивного документа HTML!

Рисунок 2. Требуется всего лишь несколько строк кода на языке PHP для извлечения информации из базы данных и вывода ее в формате документа HTML.

Конечно же, если вы имеете опыт работы с языком разметки HTML и таблицами каскадных стилей CSS, вы можете улучшить внешний вид генерируемой страницы, задействовав таблицы, тэги div или другие необходимые тэги. Мы не будем подобно рассматривать данный вопрос в этой статье, так как у нас есть еще работа, но, в любом случае, благодаря данному примеру вы должны понять, как веб-сайты соединяются с серверами базы данных и получают информацию от них.

Итак, наш сценарий выводит информацию, сохраненную в базе данных, но как организовать добавление новой информации в эту же базу данных? Какой способ добавления информации в базу данных является предпочтительным? Да, существует несколько способов выполнения данной операции, но простейший из них заключается в использовании формы HTML и кода PHP для обработки результатов. В конце сценария test.php после тэга ?> (который указывает на завершение PHP-кода) следует добавить следующий код HTML:

<hr />
<form action="test.php">
Name: <input type="text" name="Name" /><br />
Login: <input type="text" name="Login" /><br />
<input type="submit" />
</form>

Это код простой HTML-формы, которая передает данные вызванному сценарию (test.php) после нажатия на кнопку "Отправить" и содержит два поля ввода: Name и Login, соответствующих столбцам таблицы login_dates. В результате эта HTML-форма будет отображаться сразу же под информацией, извлеченной из базы данных. Однако, для того, чтобы обработать информацию, переданную в результате отправки формы, нам придется немного доработать код PHP, расположенный в секции выше. После строки с вызовом die следует добавить две следующие строки:

if($_GET['Name']) $result = $conn->query("insert into login_dates values(0, '$_GET[Name]', '$_GET[Login]')");

По умолчанию при отправке формы HTML значения из ее полей передаются заданному сценарию (в нашем случае это сценарий test.php) в рамках массива $_GET. Это также означает, что значения из ее полей передаются в составе строки URL, в чем вы можете самостоятельно убедиться, отправив форму.

В первую очередь нам нужно будет убедиться в том, что в поле формы Name было что-либо введено, то есть, строка не является пустой, после чего выполнить SQL-запрос, вставив данные описанным выше образом. Учтите, что это чрезвычайно быстрый и простой способ выполнения SQL-запроса; в реальности вам придется выполнять большое количество дополнительных проверок корректности и безопасности переданных данных для того, чтобы быть уверенным в том, что никому не удастся передать исполняемый PHP-код посредством поля формы! По сути, этот вопрос достоин отдельной статьи…

Рисунок 3. Клиент сервера базы данных с интерфейсом командной строки является не особо удобным, поэтому мы предоставляем пользователю возможность добавления информации в базу данных с помощью формы HTML. Вы можете сделать ее более привлекательной, задействовав каскадную таблицу стилей.

Теперь сценарий test.php выполняет три операции: выводит содержимое таблицы login_dates, выводит форму для добавления в эту таблицу новых данных, а также обрабатывает переданные с помощью этой формы данные и добавляет их в базу данных в случае корректного заполнения формы. Проверьте работоспособность сценария самостоятельно - введите какой-либо текст в поле Name, дату в корректном формате (например, 2015-10-04) в поле Login, нажмите кнопку "Отправить", после чего обновите страницу и убедитесь в том, что в таблице базы данных появилась новая строка, как на Рисунке 3.

Рисунок 4. Текстовый редактор Vim подсвечивает синтаксические конструкции практически всех существующих языков программирования, причем язык структурированных запросов SQL также поддерживается (что весьма удобно при редактировании файлов резервных копий таблиц).

Совет: ранее вы наверняка сталкивались с аббревиатурой "LAMP": она относится к стеку программного обеспечения, обычно используемого для обслуживания веб-сайтов. Данная аббревиатура расшифровывается как Linux, Apache, MySQL/MariaDB, PHP, хотя последнее слово в некоторых случаях может быть заменено на Perl или Python. Кроме того, существуют и другие подобные аббревиатуры, возникшие после перевода некоторых веб-сайтов с веб-сервера Apache на его более легковесные альтернативы, такие, как Nginx.

И это все!

Теперь вы обладаете всеми необходимыми навыками для создания интерактивных веб-сайтов, использующих базы данных. Что еще более важно, теперь вы понимаете, как они работают вплоть до уровня отдельных SQL-инструкций. На данный момент существует огромное количество веб-фреймворков и различных уровней абстракции, которые выполнят всю сложную работу за вас и позволят полностью абстрагироваться от взаимодействия с базой данных - они окажутся особенно полезными в том случае, если вы будете разрабатывать еще один веб-сайт Web 3.0 (или сейчас уже актуален Web 4.0?).

Но как и в случае с языком ассемблера или проект Linux From Scratch для самостоятельной сборки дистрибутива, ничто не сравнится с самостоятельной разработкой конечного продукта. В следующий раз при использовании веб-сайта с формами и системой обработки данных вы будете хорошо понимать принцип его работы и методику сохранения и извлечения информации.

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

Пока же вы можете попытаться самостоятельно решить несколько описанных ниже задач, используя полученные знания; если вы столкнетесь с трудностями, вам наверняка поможет кто-либо с форума нашего журнала, расположенного по адресу http://forums.linuxvoice.com:

1. Используйте таблицы и тэги div для того, чтобы сделать HTML-страницу с информацией из таблицы login_dates более привлекательной; возможно, вы также найдете применение каскадным таблицам стилей.
2. Добавьте проверку заполнения поля формы Login по аналогии с ее полем Name. Вы можете объединить две операции проверки в рамках одной операции if.
3. Предоставьте пользователю возможность удаления строки из базы данных. Например, вы можете реализовать данную функцию с помощью раскрывающегося списка, выполняя соответствующий SQL-запрос при выборе номера строки.
4. Реализуйте проверку корректности формата дат, вводимых в поле Login формы. В данном случае следует воспользоваться функциями для работы со строками языка программирования PHP.

Для выполнения некоторых из этих задач вам понадобятся знания языка программирования PHP, выходящие за пределы полученных в процессе чтения данной статьи, поэтому вам определенно следует ознакомиться с отличными руководствами, расположенными по адресу www.w3schools.com/php. Удачной разработки!

---

---