В этой статье я продолжу рассказывать о создании web приложения для проверки URL. Сегодня мы займемся разработкой моделей.
Примечание. Ссылки на предыдущие статьи этого цикла находятся здесь.
Как вы, наверное, знаете, модели используются для работы с данными. Т.е. если строго следовать модели MVC, модель должна читать / сохранять данные, представление – отображать их, а контроллер – обеспечивать обработку запросов и взаимодействие между моделью и представлением.
Для этого приложения мы сделаем две модели. По одной для каждой таблицы в базе данных.
Примечание. Подробно структуру приложения и базы данных мы рассмотрели в предыдущей статье.
В общем-то, разделение моделей по таблицам довольно условное. Некоторые запросы обращаются к обеим таблицам.
Теперь рассмотрим их подробнее.
UrlModel
class UrlModel extends Model {
function UrlModel() {
parent::Model();
}
/**
* Возвращает массив со всеми URL
*
* @return массив с URL, FALSE - если URL не найдены
*/
function getAllUrls() {
$qGetAllUrls = "SELECT * FROM urls";
$res = $this->db->query($qGetAllUrls);
if ($res->num_rows() == 0) {
return false;
}
else {
return $res->result_array();
}
}
/**
* Ищет URL по заданному id
*
* @param идентификатор URL в БД
* @return если URL найден, возвращает массив с URL и его id,
* FALSE - в противном случае
*/
function getUrlById($urlId) {
if (!isset($urlId) || !is_numeric($urlId) || $urlId <= 0) {
return false;
}
$qGetUrl = "SELECT * FROM urls WHERE id=?";
$res = $this->db->query($qGetUrl, array($urlId));
if ($res->num_rows() == 0) {
return false;
}
else {
return $res->result_array();
}
}
/**
* Этот метод добавляет URL в таблицу
*
* @param URL (включая http://)
* @return id новой записи (если URL был добавлен), FALSE - в противном случае
*/
function addURL($url) {
if (!isset($url) || $url == "") {
return false;
}
$qAddURL = "INSERT INTO urls (id, url) VALUES (NULL, ?)";
$res = $this->db->query($qAddURL, array($url));
if ($res === TRUE) {
return $this->db->insert_id();
}
return $res;
}
/**
* Этот метод удаляет URL из базы данных.
* Автоматически удаляются все результаты запросов для данного URL.
*
* @param идентификатор URL в БД
* @return TRUE - если URL был удален, FALSE - в случае ошибки
*/
function deleteURL($urlId) {
if (!isset($urlId) || !is_numeric($urlId) || $urlId <= 0) {
return false;
}
$qDeleteUrl = "DELETE FROM urls WHERE id=?";
$res = $this->db->query($qDeleteUrl, array($urlId));
return $res;
}
/**
* Этот метод обновляет URL в базе данных.
* Удобно использовать если URL сайта изменился.
*
* @param идентификатор URL в БД
* @param новый URL
* @return TRUE - если URL был изменен, FALSE - в случае ошибки
*/
function updateURL($urlId, $newUrl) {
if (!isset($urlId) || !is_numeric($urlId) || $urlId <= 0 || !isset($url) || $url == "") {
return false;
}
$qUpdateUrl = "UPDATE urls SET url=? WHERE id=?";
$res = $this->db->query($qUpdateUrl, array($newUrl, $urlId));
return $res;
}
}
Тут все методы выполняют простые операции чтения (getAllUrls, getUrlById), вставки (addURL), удаления (deleteURL) и обновления (updateURL) URL.
Для доступа к базе данных используется стандартная библиотека CodeIgniter’а.
Единственное на что, я хочу обратить ваше внимание – это метод deleteURL. На первый взгляд здесь выполняется обычный запрос удаления, но у нас две связанные таблицы.
Поле id таблицы urls является внешним ключом для таблицы results. А во время создания этой таблицы мы указали, что при удалении записей должны каскадно обновляться все связанные таблицы (ON DELETE CASCADE).
Примечание. SQL запросы создания таблиц находятся в файле simpleurl.sql. Запрос создающий таблицу results выглядит так:
CREATE TABLE IF NOT EXISTS `simpleurldb`.`results` (
`id` INT NOT NULL AUTO_INCREMENT ,
`urlId` INT NOT NULL ,
`checktime` TIMESTAMP NOT NULL ,
`result` INT NOT NULL ,
`responsetime` FLOAT NOT NULL ,
`responselength` FLOAT NOT NULL ,
`responsespeed` FLOAT NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX urlFK (`urlId` ASC) ,
CONSTRAINT `urlFK`
FOREIGN KEY (`urlId` )
REFERENCES `simpleurldb`.`URLs` (`id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
Кстати, обратите внимание, тип движка обязательно должен быть InnoDB (если, конечно, вы используете MySQL). Насколько я знаю, MyISAM не поддерживает каскадное обновление таблиц (и внешние ключи вообще).
Переходим к ResultModel.
class ResultModel extends Model {
function ResultModel() {
parent::Model();
}
/**
* Этот метод сохраняет результаты запроса URL
*
* @param массив с результатами
* @return TRUE - если данные успешно сохранены в БД, FALSE - в случае ошибки
*/
function saveResults($resultsData) {
if (!isset($resultsData)) {
return false;
}
$qSave = "INSERT INTO results VALUES (NULL, ?, NULL, ?, ?, ?, ?)";
$res = $this->db->query($qSave, array(
$resultsData['urlId'],
$resultsData['result'],
$resultsData['responsetime'],
$resultsData['responselength'],
$resultsData['responsespeed']));
return $res;
}
/**
* Возвращает всю информацию о результатах проверки заданного URL
*
* @param идентификатор URL
* @return массив с данными для указанного URL, FALSE - если ничего не найдено
*/
function getURLData($urlId) {
if (!isset($urlId) || !is_numeric($urlId) || $urlId <= 0) {
return false;
}
$qGetUrlData = "SELECT results.id, results.checktime, results.result, "
."results.responsetime, results.responselength, results.responsespeed, "
."urls.url FROM results, "
."urls WHERE results.urlId=? AND urls.id=results.urlId";
$res = $this->db->query($qGetUrlData, array($urlId));
if ($res->num_rows() == 0) {
return false;
}
else {
return $res->result_array();
}
}
/**
* Возвращает краткую информацию о проверках по всем URL
*
* @return массив с данными проверок, FALSE - если ничего не найдено
*/
function getGeneralData() {
$qGetAll = "SELECT ".
"urls.id, ".
"urls.url, ".
"COUNT(results.result) AS totalRequests, ".
"COUNT(results.result)-SUM(results.result) AS totalErrors, ".
"SUM(results.responsetime)/NULLIF(SUM(results.result), 0) AS avgResponse, ".
"SUM(results.responselength)/NULLIF(SUM(results.result), 0) AS avgLength, ".
"SUM(results.responsespeed)/NULLIF(SUM(results.result), 0) AS avgSpeed ".
"FROM urls ".
"LEFT JOIN results ON results.urlId=urls.id ".
"GROUP BY urls.id";
$res = $this->db->query($qGetAll);
if ($res->num_rows() == 0) {
return false;
}
else {
return $res->result_array();
}
}
/**
* Удаляет результат проверки
*
* @param id результата
* @return TRUE - если результат удален, FALSE - в противном случае
*/
function removeResult($id) {
if (isset($id) && is_numeric($id) && $id > 0) {
$qRemoveRes = "DELETE FROM results WHERE id=?";
return $this->db->query($qRemoveRes, array($id));
}
else {
return false;
}
}
}
Методы saveResults и removeResult служат для вставки и удаления данных. Останавливаться на них я не буду, т.к. ничего особенного в них нет.
А вот методы getURLData и getGeneralData рассмотрим подробнее.
getURLData($urlId) возвращает результаты проверок заданного url. Чтобы не делать два запроса, выборка осуществляется одновременно из обеих таблиц. Из таблицы urls мы получаем адрес, а из results – результаты проверок данного адреса.
Чтобы гарантировать отсутствие повторов, в предложении WHERE мы используем условие urls.id=results.urlId.
Таким образом, с помощью одного запроса мы получаем все данные необходимые для создания таблицы с результатами проверки выбранного URL.
В методе getGeneralData() запрос немного сложнее. Здесь мы должны получить усредненные данные о результатах проверок.
Поэтому в запросе для большинства полей используются агрегатные функции.
Сразу хочу обратить ваше внимание на выражения вида SUM(results.responsetime)/NULLIF(SUM(results.result), 0). На первый взгляд, кажется, что здесь удобнее использовать функцию AVG, но проблема в том, что у нас могут быть неудачные проверки (когда URL был недоступен). Для них время доступа будет равно 0, т.е. эти результаты не должны учитываться при расчете среднего значения.
Поэтому мы суммируем все значения и делим на количество успешных запросов.
Вторая проблема может возникнуть, если успешных запросов не было вообще (деление на ноль). Вообще-то, MySQL в этом случае вернет NULL, что нас устраивает, но другие СУБД могут вернуть ошибку (прервать выполнение запроса). Чтобы этого избежать, мы используем функцию NULLIF. Она принимает два параметра, и возвращает NULL, если они равны. А при делении на NULL ошибок возникать не должно.
Предложение FROM urls LEFT JOIN results ON results.urlId=urls.id объединяет таблицу urls с results, причем «левое объединение» гарантирует, что в результат будут включены все адреса из таблицы urls независимо от того, проводились для них проверки или нет.
Завершающим этапом мы группируем результаты запроса по полю urls.id (GROUP BY urls.id). Т.е. агрегатные функции будут применяться не ко всем найденным результатам, а отдельно для каждого url.
Как видите, этот запрос возвращает все данные, необходимые для создания таблицы на главной странице приложения.
С моделями мы закончили, и пора переходить к контроллеру. Но о нем в следующем выпуске 🙂 .
До встречи!
P.S. Ссылки на все статьи и примеры цикла я публикую здесь.
