Работа с SQLite

Работа с SQLite

Автор: Гончаров А.Н.

Введение

SQLite – это реляционная база данных, запросы к которой можно осуществлять при помощи языка запросов SQL. База данных не поддерживает все особенности SQL и уступает в функциональности другим развитым СУБД, но вполне подходит для хранения и извлечения информации.

Отличие SQLite от MySQL и аналогичных СУБД

Классические СУБД, такие как MySQL (а так же MS SQL, Oracle, PostgreeSQL) состоят из отдельного сервера, поддерживающего работу базы данных и прослушивающих определённый порт, на предмет обращения клиентов. В качестве клиента может выступать в том числе и расширение PHP, реализующего интерфейс, с помощью которого осуществляются запросы к базе. Движок SQLite и интерфейс к ней реализованы в одной библиотеке, что увеличивает скорость выполнения запросов. Такой сервер часто называют встроенным.

Замечание

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

SQLite является бестиповой базой данных. Точнее, есть только два типа – целочисленный "integer" и текстовый "text". Причём "integer" используется преимущественно для первичного ключа таблицы, а для остальных данных пойдёт "text". Длина строки, записываемой в текстовое поле, может быть любой.

Особенности SQLite

Все базы данных хранятся в файлах, по одному файлу на базу. Количество баз данных, а так же таблиц в них, ограниченно только свободным местом, имеющимся на сайте. А максимально возможный объём одной базы данных составляет 2 Тб.

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

Установка SQLite

В PHP5 поддержка SQLite установлена и включена по умолчанию.

Установка под Windows: Для установки SQLite необходимо скачать и скопировать в папку с расширениями библиотеку "php_sqlite.dll", которую можно загрузить по ссылке: http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll. Затем необходимо раскомментировать (или добавить) строку "extension=php_sqlite.dll" в файле "php.ini". Для нормального функционирования SQLite также необходимо раскомментировать строку "extension=php_pdo.dll".

Замечание

Если используется полная версия PHP в zip-архиве, а не в виде инсталлятора, соответствующие библиотеки расширения должны находится в директории расширений PECL.

Замечание

Библиотека "php_pdo.dll" должна загружаться до загрузки "php_sqlite.dll". То есть в php.ini строка "extension=php_sqlite.dll" должна стоять после "extension=php_pdo.dll".

Установка под Unix: Скачайте свежую версию SQLite с официального сайта (http://sqlite.org/download.html). Прочтите файл "INSTALL", поставляемый с исходными тестами модуля. Или просто воспользуйтесь командой установки PEAR: "pear install sqlite".

Работа с SQLite

Создание базы данных: Для того чтобы создать новую базу данных необходимо воспользоваться функцией sqlite_open(). Если базы, имя которой указано в параметре "filename" не существует, то функция создаст новую базу данных с именем "filename" и вернёт идентификатор базы данных.

resource sqlite_open ( string filename [, int mode [, string &error_message]] )

В скрипте, преведённом ниже, демонстрируется создание новой базы данных:

<?php
  // Создадим базу данных
  $db = sqlite_open("my_database.db");
  if (!$db) exit("Не удалось создать базу данных!"); ?>

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

Создание таблиц: Все запросы к базе данных выполняет функция sqlite_query(), которая имеет следующий синтаксис:

resource sqlite_query ( resource dbhandle, string query )

Замечание

Для работы с SQLite, как и любой реляционной базой данных используется язык запросов SQL. Поэтому создать таблицу данных можно при помощи традиционного запроса CREATE TABLE, вставить запись при помощи оператора INSERT, извлечь запись при помощи SELECT, а обновить существующую запись при помощи запроса UPDATE.

В приведённом ниже примере создаётся таблица table1, содержащая три поля: целочисленное поле id, которое выступает в качестве первичного ключа, и два текстовых поля field1 и field2.

<?php
  // Создадим новую базу данных
  $db = sqlite_open("my_database.db");
  if (!$db) exit("Невозможно создать базу данных!");
  // Создадим таблицу "table1" в базе
  $query_table = sqlite_query($db, "CREATE TABLE table1
                              (id INTEGER PRIMARY KEY,
                              /* id автоматически станет автоинкрементным */
                               field1 TEXT,
                               field2 TEXT);
                              ");
  if (!$query_table) exit("Невозможно создать таблицу в базе данных!");
  // Запишем что-нибудь в таблицу
  $query_insert = sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('PHP5', 'Apache');");
  if (!$query_insert) exit("Невозможно записать данные в таблицу!"); ?>

После создания таблицы, в неё добавляется запись, содержащая строки 'PHP5' и 'Apache', поле id автоматически получает значение 1.

Вывод данных из базы: Для вывода данных из таблиц используется всё та же функция – sqlite_query(). Если выбирается несколько записей, результат выборки следует обработать при помощи цикла while() и функции sqlite_fetch_array(), которая имеет следующий синтаксис:

array sqlite_fetch_array ( resource result [, int result_type [, bool decode_binary]] )

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

<?php
  // Создадим новую базу данных
  $db = sqlite_open("my_database.db");
  if (!$db) exit("Невозможно создать базу данных!");
  // Создадим таблицу "table1" в базе
  $query_table = sqlite_query($db, "CREATE TABLE table1
                              (id INTEGER PRIMARY KEY,
                               /* id автоматически станет автоинкрементным */
                               field1 TEXT,
                               field2 TEXT);
                              ");
  if (!$query_table) exit("Невозможно создать таблицу в базе данных!");
  // Запишем что-нибудь в таблицу
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('PHP5+', 'Apache');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('SQLite – ', 'классная вещь');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('Посетите ', 'sqlite.org');");
  // Сделаем выборку данных
  $res = sqlite_query($db, "SELECT * FROM table1;");
  // В цикле выведем все полученные данные
  while ($array = sqlite_fetch_array($res))
  {
    echo($array['field1'].$array['field2']." (id записи:".$array['id'].")<br />");
  } ?>

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

PHP5+Apache (id записи:1)
SQLite – классная вещь (id записи:2)
посетите sqlite.org (id записи:3)

Редактрирование записи: Для изменения поля воспользуемся функцией sqlite_query() и передадим ей запрос на обновление (UPDATE).

<?php
  // Создадим новую базу данных
  $db = sqlite_open("my_database.db");
  if (!$db) exit("Невозможно создать базу данных!");
  // Создадим таблицу "table1" в базе
  $query_table = sqlite_query($db, "CREATE TABLE table1
                              (id INTEGER PRIMARY KEY,
                               /* id автоматически станет автоинкрементным */
                               field1 TEXT,
                               field2 TEXT);
                              ");
  if (!$query_table) exit("Невозможно создать таблицу в базе данных!");
  // Запишем что-нибудь в таблицу
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('PHP5+', 'Apache');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('SQLite – ', 'классная вещь');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('Посетите ', 'sqlite.org');");
  // Изменим поле с id=1
  sqlite_query($db, "UPDATE table1 SET field2='Apache+Linux' WHERE id=1;");
  // Сделаем выборку данных
  $query = sqlite_query($db, "SELECT * FROM table1;");
  // В цикле выведем все полученные данные
  while ($array = sqlite_fetch_array($query))
{
    echo($array['field1'].$array['field2']." (id записи:".$array['id'].")<br />");
  } ?>

В результате получим:

PHP5+Apache+Linux (id записи:1)
SQLite – классная вещь (id записи:2)
посетите sqlite.org (id записи:3)

Удаление записи из таблицы: Чтобы удалить запись из таблицы, нужно передать функции sqlite_query() запрос на удаление (DELETE).

<?php
  // Создадим новую базу данных
  $db = sqlite_open("my_database.db");
  if (!$db) exit("Невозможно создать базу данных!");
  // Создадим таблицу "table1" в базе
  $query_table = sqlite_query($db, "CREATE TABLE table1
                              (id INTEGER PRIMARY KEY,
                               /* id автоматически станет автоинкрементным */
                               field1 TEXT,
                               field2 TEXT);
                              ");
  if (!$query_table) exit("Невозможно создать таблицу в базе данных!");
  // Запишем что-нибудь в таблицу
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('PHP5+', 'Apache');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('SQLite – ', 'классная вещь');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('Посетите ', 'sqlite.org');");
  // Удалим поле с id=2
  sqlite_query($db, "DELETE FROM table1 WHERE id=2;");
  // Сделаем выборку данных
  $query = sqlite_query($db, "SELECT * FROM table1;");
  // В цикле выведем все полученные данные
  while ($array = sqlite_fetch_array($query))
  {
    echo($array['field1'].$array['field2']." (id записи:".$array['id'].")<br />");
  } ?>

В результате получим:

PHP5+Apache (id записи:1)
посетите sqlite.org (id записи:3)

Закрытие базы данных: Для закрытия базы данных используется функция sqlite_close(). В качестве единственного параметра функция принимает идентификатор открытой базы данных.

void sqlite_close ( resource dbhandle )

Схема использования данной функции представлена ниже

<?php
  $db = sqlite_open("my_database.db");
  /*
  ... Здесь происходит работа с БД ...
  */
  sqlite_close($db); ?>

Замечание

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

Ссылки

• http://www.sqlite.org/ – официальный сайт проекта SQLite
• http://php.su/functions/?cat=sqlite – документация по функциям SQLite в PHP
• http://www.softtime.ru/info/sqlite_gbook.php – гостевая книга, основанная на SQLite

Операторы манипулирования данными SQL

В операции манипулирования данными входят три операции: операция удаления записей — ей соответствует оператор DELETE, операция добавления или ввода новых записей — ей соответствует оператор INSERT и операция изменения (обновления записей) — ей соответствует оператор UPDATE. Рассмотрим каждый из операторов подробнее.

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

Оператор ввода данных INSERT имеет следующий синтаксис:

INSERT INTO имя_таблицы [(<список столбцов>) ] VALUES (<список значений>)

Подобный синтаксис позволяет ввести только одну строку в таблицу. Задание списка столбцов необязательно тогда, когда мы вводим строку с заданием значений всех столбцов. Например, введем новую книгу в таблицу BOOKS

INSERT INTO BOOKS ( ISBN.TITL.AUTOR.COAUTOR.YEARIZD.PAGES)

VALUES ("5-88782-290-2","Аппаратные средства IBM PC. Энциклопедия".

'Гук М. "."",2000.816)

В этой книге только один автор, нет соавторов, но мы в списке столбцов задали столбец COAUTOR, поэтому мы должны были ввести соответствующее значение в разделе VALUES. Мы ввели пустую строку, потому что мы знаем точно, что нет соавтора. Мы могли бы ввести неопределенное значение NULL.

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

INSERT INTO BOOKS VALUES ("5-88782-290-2",

"Аппаратные средства IBM PC. Энциклопедия"."Гук М.","".2000.816)

Результаты работы обоих операторов одинаковые.

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

INSERT INTO BOOKS ( ISBN,TITL.AUTOR.YEARIZD,PAGES)

VALUES ("5-88782-290-2"."Аппаратные средства IBM PC. Энциклопедия".

Гук М.".2000,816)

Столбцу COAUTOR будет присвоено в этом случае значение NULL.

Какие столбцы должны быть заданы при вводе данных? Это определяется тем, как описаны эти столбцы при описании соответствующей таблицы, и будет рассмотрено более подробно при описании языка DDL (Data Definition Language) в главе 8. Здесь мы пока отметим, что если столбец или атрибут имеет признак обязательный (NOT NULL) при описании таблицы, то оператор INSERT должен обязательно содержать данные для ввода в каждую строку данного столбца. Поэтому если в таблице все столбцы обязательные, то каждая вводимая строка должна содержать полный перечень вводимых значений, а указание имен столбцов в этом случае необязательно. В противном случае, если имеется хотя бы один необязательный столбец и вы не вводите в него значений, задание списка имен столбцов — обязательно.

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

INSERT INTO EXEMPLAR (INV.ISBN.YES_NO.NUM_READER.DATE_IN, DATE_OUT)

VALUES (1872. "5-88782-290-2" .NO,344.GetDate() .DateAdcKd.GetDate() ,14))

И это означает, что мы выдали экземпляр книги с инвентарным номером 1872 читателю с номером читательского билете 344, отметив, что этот экземпляр не присутствует с этого момента в библиотеке, и определили дату выдачи книги как текущую дату (функция GetDateO), а дату возврата задали двумя неделями позднее, использовав при этом функцию DateAdd О, которая позволяет к одной дате добавить заданное количество интервалов даты и тем самым получить новое значение типа «дата». Мы добавили 14 дней к текущей дате.

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

INSERT INTO READER (NAME_READER. ADRESS. HOOM_PHONE. BIRTH_DAY)

SELECT (NAMEJTUDENT. ADRESS, HOOM_PHONE, BIRTH_DAY)

FROM STUDENT

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

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

Синтаксис оператора DELETE следующий:

DELETE FROM имя_таблицы [WHERE условия_отбора]

Если условия отбора не задаются, то из таблицы удаляются все строки, однако это не означает, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.

Например, если нам надо удалить результаты прошедшей сессии, то мы можем удалить все строки из отношения R1 командой

DELETE FROM R1

Условия отбора в части WHERE имеют тот же вид, что и условия фильтрации в операторе SELECT. Эти условия определяют, какие строки из исходного отношения будут удалены. Например, если мы исключим студента Миронова А. В., то мы должны написать следующую команду:

DELETE FROM R2 WHERE ФИО = 'Миронов А.В.'

В части WHERE может находиться встроенный запрос. Например, если нам надо исключить неуспевающих студентов, то по закону о высшем образовании неуспевающим считается студент, имеющий две и более задолженности по последней сессии. Тогда нам в условиях отбора надо найти студентов, имеющих либо две или более двоек, либо два и более несданных экзамена из числа тех, которые студент сдавал. Для поиска таких горе-студентов нам надо выбрать из отношения R1 все строки с оценкой 2 или с неопределенным значением, потом надо сгруппировать полученный результат по атрибуту ФИО и, подсчитав количество строк в каждой группе, которое соответствует количеству несданных экзаменов каждым студентом, отобрать те группы, у которых количество строк не менее двух. Теперь попробуем просто записать эту сложную конструкцию на SQL и убедимся, что этот сложный запрос записывается достаточно компактно.

DELETE FROM R2 WHERE R2.ФИО IN (SELECT Rl.ФИО FROM Rl

WHERE Оценка = 2 OR Оценка IS NULL GROOP BY Rl.ФИО HAVING COUNT(*) >= 2

Однако при выполнении операции DELETE, включающей сложный подзапрос, в подзапросе нельзя упоминать таблицу, из которой удаляются строки, поэтому СУБД отвергнет такой красивый подзапрос, который попытается удалить всех не только сдававших, но и несдававших студентов, которые имеют более двух задолженностей.

DELETE FROM R2 WHERE R2.ФИО IN

(SELECT Rl.ФИО

FROM (R2 NATURAL INNER JOIN R3 )

LEFT JOIN Rl USING ( ФИО. Дисциплина)

WHERE Оценка = 2 OR Оценка IS NULL

GROOP BY Rl.ФИО HAVING COUNT(*) >= 2

Все операции манипулирования данными связаны с понятием целостности базы данных, которое будет рассматриваться далее в главе 9. В настоящий момент мне бы хотелось отметить только то, что операции манипулирования данными не всегда выполнимы, даже если синтаксически они написаны правильно. Действительно, если мы бы захотели удалить какую-нибудь группу из отношения R3, то СУБД не позволила бы нам это сделать, так как в отношениях R1 и R2 есть строки, связанные с удаляемой строкой в отношении R3. Почему так делается, мы узнаем позднее, а пока просто примем к сведению, что не все операторы манипулирования выполнимы.

Операция обновления данных UPDATE требуется тогда, когда происходят изменения во внешнем мире и их надо адекватно отразить в базе данных, так как надо всегда помнить, что база данных отражает некоторую предметную область. Да-примср, в нашем учебном заведении произошло счастливое событие, которое связано с тем, что госпожа Степанова К. Е. пересдала экзамен по дисциплине «Базы данных» с двойки сразу на четверку. В этом случае нам надо срочно выполнить соответствующую корректировку таблицы R1. Операция обновления имеет следующий формат:

UPDATE имя_таблицы

SET имя_столбца = новое_значение [WHERE условие_отбора]

Часть WHERE является необязательной, так же как и в операторе DELETE. Она играет здесь ту же роль, что и в операторе DELETE, — позволяет отобрать строки, к которым будет применена операция модификации. Если условие отбора не задается, то операция модификации будет применена ко всем строкам таблицы.

Для решения ранее поставленной задачи нам необходимо выполнить следующую операцию

UPDATE Rl

SET Rl.Оценка = 4

WHERE R1.ФИО = "Степанова К.Е." AND R1.Дисциплина = "Базы данных"

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

R4 = < Группа, Курс>

	R4
	Группа	Курс
	4906	3
	4807	4

В этом случае перевод на следующий курс можно выполнить следующей операцией обновления:

UPDATE R4

SET R4.Kypc = R4.Kypc + 1

И результат будет выглядеть следующим образом:

	Группа	Курс
	4906	4
	4807	5

Операция модификации, так же как и операция удаления, может использовать сложные подзапросы. Расширим нашу базу еще одним отношением, которое будет содержать перечень студентов, получающих стипендию с указанием надбавки, которую они получают за отличную учебу. Исходно там могут находиться все студенты с указанием неопределенного размера стипендии. По мере анализа отношения R1 мы можем постепенно заменять неопределенные значения на конкретные размеры стипендии. Отношение R5 имеет вид:

	R5
	ФИО	Группа	Стипендия
	Петров Ф. И.	4906	<Null>
	Сидоров К. А.	4906	<Null>
	Миронов А. В.	4906	<Null>
	Крылова Т. С.	4906	<Null>
	Владимиров В. А.	4906	<Null>
	Трофимов П. А.	4807	<Null>
	Иванова Е. А.	4807	<Null>
	Уткина Н. В.	4807	<Null>

Будем считать наличие трех пятерок по сессии признаком повышенной стипендии, + 50% к основной, наличие двух пятерок из сданных экзаменов и, отсутствие двоек и троек на сданных экзаменах — признаком повышения стипендии на 25%, наличие хотя бы одной двойки среди сданных экзаменов — признаком снятия или отсутствия стипендии вообще, то есть -100% надбавки. При отсутствии троек на сданных экзаменах назначим обычную стипендию с надбавкой 0%. Однако все эти изменения мы должны будем сделать отдельными операциями обновления.

Назначение повышенной стипендии:

UPDATE R5

SET R5.Стипендия = 50% WHERE R5.ФИО IN

(SELECT Rl.ФИО

FROM Rl

WHERE Rl.Оценка =5 

GROOP BY Rl.ФИО

HAVING COUNT(*) =3 )

Назначение стипендии с надбавкой 25%:

UPDATE R5 SET R5.Стипендия = 25%

WHERE R5.ФИО IN (SELECT Rl.ФИО FROM R1

WHERE Rl.ФИО NOT IN (SELECT А.ФИО FROM Rl A

WHERE А.Оценка <=3 OR А.Оценка IS NULL)

GROOP BY Rl.ФИО HAVING COUNT(*)>2 )

Назначение обычной стипендии:

UPDATE R5

SET R5.Стипендия = 0% WHERE R5.ФИО IN (SELECT Rl.ФИО FROM Rl

WHERE Rl.Оценка >=4 AND Р1.ФИО NOT IN (SELECT А.ФИО FROM Rl A

WHERE А.Оценка <= 3 OR А.Оценка IS NULL) )

Снятие стипендии:

UPDATE R5

SET R5.Стипендия =-100% WHERE R5.ФИО IN

(SELECT Rl.ФИО

FROM Rl

WHERE Rl.Оценка <= 2 OR Rl.Оценка IS NULL)

Почему мы в первом запросе на обновление не использовали дополнительную проверку на отсутствие двоек, троек и несданных экзаменов, как мы сделали это при назначении следующих видов стипендии? Просто мы учли особенности нашей предметной области: у нас в соответствии с исходными данными не только 3 экзамена. Но если мы можем предположить, что число экзаменов может быть произвольным и изменяться от семестра к семестру, то нам надо изменить наш запрос. Запрос — это некоторый алгоритм решения конкретной задачи, которую мы формулируем заранее на естественном языке. И оттого, что наша задача решается всего одним оператором языка SQL, она не становится примитивной. Мощность языка SQL и состоит в том, что он позволяет одним предложением сформулировать ответы на достаточно сложные запросы, для реализации которых на традиционных языках понадобилось бы писать большую программу. Итак, подумаем, как нам надо изменить текст нашего запроса на обновление для назначения повышенной стипендии при любом количестве сданных экзаменов. Прежде всего, каждая группа может иметь свое число экзаменов в сессию, это зависит от специальности и учебного плана, по которому учится данная группа. Поэтому для каждого студента нам надо знать, сколько экзаменов он должен был сдавать и сколько экзаменов он сдал на пять, и в том случае, когда эти два числа равны, мы можем назначить ему повышенную стипендию.

Будем решать нашу задачу по шагам. В конечном счете нам все равно надо знать, сколько экзаменов должен сдавать каждый конкретный студент, поэтому сначала сосчитаем количество экзаменов, которые должна сдавать группа, в которой учится этот студент.

Это мы делать умеем, для этого надо сделать запрос SELECT над отношением R3, сгруппировав его по атрибуту Группа, и вывести для каждой группы количество дисциплин, по которым должны сдаваться экзамены. Если мы учтем, что в одной сессии по одной дисциплине не бывает более одного экзамена, то можно просто подсчитывать количество строк в каждой группе.

SELECT R3.Группа. Число_экзаменов = COUNT(*)

FROM R3 GROOP BY R3.Группа

Однако нам нужен не этот запрос, нам нужен запрос, в котором мы определяем для каждого студента количество экзаменов. Этот запрос мы должны строить по схеме встроенного запроса:

SELECT COUNT(*) FROM R3

WHERE R2.Группа = R3.Группа GROOP BY R3.Группа

А почему мы здесь в части FROM не написали имя второго отношения R2? Мы имя этого отношения укажем для связи с вышестоящим запросом, когда будем формировать запрос полностью. Теперь попробуем сформулировать полностью запрос. Нам надо объединить отношения R1 и R2 по атрибуту ФИО, нам надо знать группу, в которой учится каждый студент, далее надо выбрать все строки с оценкой 5 и сгруппировать их по фамилии студента, сосчитав количество строк в каждой группе, а выбирать мы будем те группы, в которых число строк в группе равно числу строк во встроенном запросе, рассмотренном ранее, при условии равенства количества строк в группе результату подзапроса, который выводит только одно число.

SELECT Rl.ФИО FROM R1.R2

WHERE Rl. ФИО = R2ФИО AND Rl.Оценка = 5

GROOP BY Rl.ФИО HAVING COUNK*) = (SELECT COUNT(*)

FROM R3

WHERE R2.Группа = R3.Группа

GROOP BY R3.Группа) 

Ну а теперь нам осталась последняя простейшая операция: надо заменить старый вложенный запрос, определявший отличников, получивших три пятерки на сессии, на новый универсальный запрос:

UPDATE R5

SET R5.Стипендия = 50%

WHERE R5.<WO IN (SELECT Rl.ФИО

FROM R1.R2

WHERE Rl. ФИО = Р2.ФИО AND Rl.Оценка = 5

GROOP BY Rl.ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R3

WHERE R2.Группа = R3.Группа GROOP BY R3.Группа))

Вот какой сложный запрос мы построили. Это ведь практически один оператор, а какую сложную задачу он решает. Действительно, мощность языка SQL иногда удивляет даже профессионалов, кажется невозможно построить один запрос для решения конкретной задачи, но когда начинаешь поэтапно его конструировать — все получается. Самое сложное — это сделать переход от словесной формулировки задачи к представлению ее в терминах нашего SQL, но этот процесс сродни процессу алгоритмизации при решении задач традиционного программирования, а он всегда был самым трудным, творческим и неформализуемым процессом. Недаром на заре развития программирования известный американский специалист по программированию Дональд Е. Кнут озаглавил свой многотомный капитальный труд по теории и практике программирования «Искусство программирования для ЭВМ» («The art of computer programming»).

Работа с БД. Анализ логов

Работа с БД. Анализ логов

Автор: Дмитрий Лебедев

Источник: detail.phpclub.net

Хочу снова похвалиться своим "творчеством".

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

Что там было в таблице? Дата, адрес от корня виртуального хоста ($PHP_SELF), броузер, реферер, ip-адрес пользователя и имя хоста. Строка вставлялась так:

@mysql_query("INSERT INTO logs (date, ip, host, address, referer, browser) VALUES (NOW(), '$REMOTE_ADDR', '". gethostbyaddr($REMOTE_ADDR). "', '$PHP_SELF', '$HTTP_REFERER', '$HTTP_USER_AGENT')");

А что мы хотим видеть в статистике? Просто посмотрим разные варианты: посещения по дням, распределение посещений по времени суток. Затем эти же две выборки, только не для всех логов, а для главной страницы. Распределение по дням недели, посещаемость за последние недели и месяцы. Количество посетителей, пришедших с других сайтов. Бывает интересно посмотреть, сколько посетителей приходило с определённой ссылки в разные дни. Распределение по времени за определённый день (неделю, месяц). Адреса, куда уходят с такой-то страницы.

Систематизировать это не так сложно, как кажется. Всё вышеописаное укладывается в восемь вариантов группировки таблицы. Адрес, реферер, броузер (увы, тут получается сравнение строчек "HTTP_USER_AGENT", включающих в себя и версии, и ОС, а не отдельных броузеров. Тут без обработки на входе не обойтись), день, неделя, месяц, день недели, час.

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

Добавим к выбору группировки выбор ограничения по дням (последние n дней) а так же условия выборки для поля WHERE, которые можно ввести в текстовое поле, и получим систему, в которую укладывается те выборки и распределения, которые я описал.

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

$selection[0] = array(
  "name" => "график по дням",
  "select" => array("date_format(date,'%e.%m.%Y') as dday", "count(date) as visits"),
  "group" => array("dday"),
  "order" => array("date DESC"),
  "type" => 1
  );

Элемент "name" — это название для крутилки, "type" — тип таблицы. Типов таблиц два — просто список и список со "столбиками". Если список сортируется по количеству посещений, то столбики в принципе не нужны, а для удобства восприятия, например, графика посещений по дням, график желателен. Остальные элементы можно не комментировать.

В запросе учитываются так же и временные ограничения, и условие, которое ввёл пользователь. Переменная $type — номер запрашиваемой выборки.

$days = intval($days);
if ($days>0)
  $selection[$type]["where"][] = "date>DATE_SUB(NOW(),INTERVAL $days DAY)";

$where = stripslashes(trim($where));
if (strlen($where)>0)
  $selection[$type]["where"][] = "($where)";

После этого рисуется форма (в крутилках первыми строчками выводятся выбранные значения. Затем строится запрос, которым узнаётся общее количество строк. Это, надо признать, скользкое место, потому что в нём никаких упрощений, просто в отличие от основного запроса, здесь просто отсутствует сортировка. Но количество строк узнаётся "в лоб" — выбирается всё то же самое и потом делается mysql_num_rows. Если у кого будут идеи, можете прислать мне или публиковать свой анализатор (только ссылку на меня поставьте, пожалуйста).

$amount_request = "SELECT ". implode(", ", $selection[$type]["select"]). " FROM logs ";
if (sizeof($selection[$type]["where"])>0)
  $amount_request .= " WHERE ". implode(" AND ", $selection[$type]["where"]);
$amount_request .= " GROUP BY ". implode(", ", $selection[$type]["group"]);

$request = "SELECT ". implode(", ", $selection[$type]["select"]). " FROM logs ";
if (sizeof($selection[$type]["where"])>0)
 $request .= " WHERE ". implode(" AND ", $selection[$type]["where"]);
$request .= " GROUP BY ". implode(", ", $selection[$type]["group"]). 
 " ORDER BY ". implode(", ", $selection[$type]["order"]). " ". 
 get_limit($page, $amount, $in_page);

Как видите, я использую описанный в предыдущем выпуске модуль постраничного вывода запросов (get_limit).

Но мало дать неограниченные возможности построения запросов. Вот, допустим, смотрю я на статистику рефереров по популярности и хочу посмотреть, как народ шёл ко мне с hackzone.ru. Что делать? Выбирать нужные параметры крутилок и писать в текстовом поле "referer like '%hackzone%'" ну просто влом!

Оказывается, это тоже несложно сделать. Два часа мыслительных усилий и редактирования текста, и вот вышла некое подобие возможности детализации выборки. Нажимаю "список рефереров" в закладках (в закладках, потому что в адресе передаётся параметр "referer not like 'http://detail.phpclub.net%'"), получаю таблицу, в ней в строке "hackzone.ru" нажимаю на ссылку и вот оно, распределение зашедших с этого сайта по дням. Можно нажать на другую ссылку и получить распределение по времени суток — как угодно.

Какие ещё делать выборки — зависит от вашей фантазии. Если написанного не хватает, можно добавить свои. Обещаю по мере сил обновлять и улучшать программу.

В принципе, имея логи у себя на офисной или домашней машине, можно делать с ними всё, что душе угодно. Надо только написать обработчик. Делов-то! :) На SpyLog я не претендую. По крайней мере, для себя я написал такую вещь, которая позволяет делать многое из того, что угодно душе. И, кстати, не грузить лишнюю информацию и рекламу Спайлог-информера и прочего.