CC BY
338
Educational Technology & Society 8(1) 2005 ISSN 1436-4522
Упражнения по SQL. Модификация данных
С.И. Моисеенко, В.Ю. Калинкин кафедра информатики Донской государственный технический университет
Аннотация
В данной статье выполняется сравнительный анализ нескольких подходов к реализации упражнений на операторы модификации данных языка SQL, который является стандартным интерфейсом взаимодействия с реляционными системами управления базами данными (РСУБД). На основании анализа делается обоснованный выбор в пользу одного из методов, который был успешно реализован. Статья продолжает знакомить читателя с построением автоматизированной системы дистанционного обучения, основанной на приобретении учащимися практических навыков посредством решения задач с контролем правильности со стороны системы [Моисеенко С.И., Майстренко А.В., 2003]. Следует отметить, что описанная система реально существует (http://www.sal-ex.ru) и доказала свою эффективность именно как средство изучения языка SQL.
Ключевые слова
дистанционная обучающая система, язык SQL, операторы манипуляции данными, реляционные базы данных
За время, прошедшее с публикации первой статьи, посвященной сайту «Упражнения по SQL» (http://ifets.ieee.org/russian/depositorv/v6 i2/html/5.html), нами были реализованы упражнения и на другие операторы подъязыка SQL DML (Data Manipulation Language). В данной статье рассматривается несколько подходов к реализации и проводится их сравнительный анализ.
Напомним, что запрос пользователя, решающий предложенную задачу, на сайте выполняется реальным сервером баз данных (в настоящее время нами используется SQL Server 2000). В случае задач на выборку из базы данных (SELECT) сервер баз данных последовательно выполняет запрос пользователя и эталонный запрос, сравнивая возвращаемые результаты и сообщая пользователю о результатах этого сравнения.
Запросы на модификацию данных ставят дополнительные проблемы перед разработчиками:
- Безопасность. Разрешая пользователям изменять данные, мы делаем сайт уязвимым перед атаками хакеров.
- Невозможно непосредственное выполнение операторов, полученных от пользователя. Дело в том, изменение состояния учебной БД недопустимо, так как с ней могут одновременно работать много пользователей, и БД должна в любой момент времени находиться в заранее известном состоянии для каждого из них.
Схема работы должна выглядеть следующим образом:
Рис.1. Структурная схема обучающей системы.
Решая предложенную задачу, пользователь составляет запрос, модифицирующий данные одной из таблиц учебной БД (1 - здесь и далее цифры соответствуют элементам схемы на рис.1). После выполнения запроса данные извлекаются из таблицы и сохраняются (2) для последующего сравнения. Аналогичную операцию следует провести с тестовым запросом (4, 5). Необходимо предусмотреть комплекс мер (3) для сохранения или возврата оригинальной таблицы в первоначальное состояние после каждого выполненного запроса (и тестового, и пользовательского). Поиск наилучшего технического решения этого этапа и является нашей основной задачей. Последний этап - сравнение полученных данных (6) не вызывает никаких трудностей.
Важно обеспечить также высокое быстродействие системы и хорошие показатели масштабируемости. Также желательно, чтобы используемый комплекс мер работал максимально «прозрачно» для пользователя, не ограничивая его в выборе синтаксических конструкций языка DML, допускаемых стандартом языка и поддерживаемых используемым сервером баз данных.
В рамках перечисленных требований могут быть предложены следующие решения:
- Выполнять операторы пользователя на оригинальных таблицах в рамках транзакции, которая завершается откатом (ROLLBACK) после возвращения данных пользователю.
- Использовать на оригинальных таблицах специальным образом составленные триггеры типа “INSTEAD OF”. Т.е. вместо модификации триггер формирует тот набор строк, который был бы получен, если бы запрос пользователя фактически выполнялся сервером.
- Создавать для пользователя временные таблицы - копии таблиц, которые подвергаются воздействию операторов DML. По завершении транзакции временные таблицы автоматически удаляются.
- По мере надобности создавать для каждого пользователя полноценные таблицы - копии оригинальных таблиц. Таблицы остаются в БД в течение всего времени обучения пользователя и при необходимости приводятся в соответствие с оригинальными таблицами.
Последние два метода представляются более сложными в реализации, будут потенциально потреблять больше ресурсов и, как итог, приведут к увеличению времени отклика системы. Поэтому они описаны поверхностно, а детально оценивались только два первых решения. Рассмотрим каждое из них подробнее.
ROLLBACK-метод
Будем выполнять DML-оператор пользователя в рамках транзакции, которая будет завершаться откатом. Для удобства создадим специальную хранимую процедуру (здесь и далее используется язык Transact-SQL):
CREATE PROCEDURE EXEC_W_ROLLBACK @sqltxt varchar(8 00), -- текст запроса пользователя @tble varchar(100)
AS
BEGIN TRANSACTION;
EXEC(@sqltxt);
IF (@@ERROR=0)
EXEC('SELECT * from ' + @tble);
ROLLBACK TRANSACTION;
GO
После выполнения оператора пользователя нужно извлечь данные из таблицы. Так как внутри хранимой процедуры трудно определить, какая именно таблица подверглась модификации, имя интересующей нас таблицы передается вторым аргументом. Его можно получить синтаксическим анализом текста пользовательского запроса. Однако в нашем случае в этом нет необходимости, поскольку в каждом задании таблица указывается явно. Здесь уместно упомянуть о
том, что все задачи на сайте должны решаться одним SQL-запросом, т.е. выполнение пакетов инструкций пока не предусмотрено. Таким образом, указание имени таблицы никак не ограничивает общности данного подхода. Вызов хранимой процедуры будет выглядеть так:
exec EXEC W ROLLBACK 'insert into mytable1(code, model, price) values (12,2 000,399)','mytable1'
В результате выполнения хранимой процедуры будут возвращены все строки таблицы после выполнения модифицирующего оператора или сообщение об ошибке в запросе пользователя.
Преимущества:
- Максимальная реалистичность получаемых результатов, что выражается, например, в проверке всех ограничений, накладываемых схемой базы данных.
Недостатки:
- Требуется выяснять имя затрагиваемой таблицы или, как указано выше, создать дополнительное поле в таблице с упражнениями для хранения имени таблицы, к которой адресуется запрос.
- При совершении и откате транзакций происходит интенсивная работа с файлом журнала, что будет приводить к его непродуктивному росту и соответственно породит дополнительные административные процедуры.
- Выполнение транзакции накладывает блокировку на таблицу, что приведет к замедлению многопользовательской работы, на которую рассчитана наша обучающая система. Если же допускать «грязное» чтение, то это будет являться нарушением одной из поставленных нами задач, а именно, одинаковости состояния базы данных одновременно для всех пользователей.
TRIGGER-метод
Сразу скажем, что это реализованный метод: на оригинальной таблице будем использовать триггеры типа “INSTEAD OF” (замещающие триггеры). Они выполняются вместо операции, с которой они связаны. На каждую из модифицирующих таблицу операций - INSERT, UPDATE и DELETE - нужно написать имитационный триггер. Так для операции DELETE такой триггер должен вернуть пользователю в качестве результата все строки из таблицы кроме тех, которые подлежат удалению (в SQL Server удаляемые строки находятся в специальной таблице DELETED):
CREATE TRIGGER del_mytable1 ON mytablel INSTEAD OF delete AS
BEGIN SET rowcount 100
SELECT * from mytablel where code not in (SELECT code from deleted) order by 1 END
Так как текущая реализация Transact-SQL не поддерживает оператор EXCEPT, то мы используем подзапрос по первичному ключу таблицы.
Проще дело обстоит с операцией INSERT:
CREATE TRIGGER ins_mytable1 ON mytable1 INSTEAD OF INSERT AS
BEGIN SET rowcount 100 SELECT * from inserted UNION ALL
SELECT * from mytable1 order by 1 END
Здесь мы составляем объединение всех строк. Иначе (при использовании операции UNION) дубликаты строк были бы утеряны. Становится очевидным основной недостаток этого метода: при имитации вставки не отслеживается уникальность первичного ключа, и все результаты выдаются из предположения, что первичного ключа в таблице нет. Кроме того, не будет выполняться и проверка ссылочной целостности, т.е. проверка на допустимые значения внешнего ключа при срабатывании замещающих триггеров.
Операция UPDATE реализуется как совокупность операций вставки и удаления:
CREATE TRIGGER upd_mytable1 ON mytable1 INSTEAD OF update AS
BEGIN SET rowcount 100
SELECT * from mytable1 where code not in (select code from deleted)
UNION ALL
SELECT * from inserted order by 1 END
Все недостатки Rollback-метода здесь отсутствуют. Кроме того, как видно из приведенных примеров, триггеры позволяют естественным образом ограничить возвращаемый набор строк, что является защитой от попыток посредством декартового соединения таблиц «загрузить» сервер, а также выполняют однотипную сортировку, что упрощает последующий алгоритм сравнения (пункт 6 на схеме).
Другие методы
Рассмотрим метод временных таблиц:
CREATE PROCEDURE EXEC_TEMPORARY @sqltxt varchar(8 00), -- текст запроса @tbl varchar(30)
AS
declare @tmp varchar(32);
set @tmp='#'+@tbl; --имя временной таблицы SET @sqltxt=REPLACE(@sqltxt,@tbl,@tmp);
EXEC('SELECT * into '+@tmp+' from ' + @tbl+';'
+@sqltxt+';'
+'SELECT * from '+@tmp);
GO
Вместе с текстом запроса в процедуру передается имя затрагиваемой таблицы. На его основе строится имя локальной временной таблицы, в тексте запроса
проводится соответствующая замена. Тут кроется потенциальная ошибка: при замене имени таблицы можно «испортить» запрос, если такая символьная
последовательность содержится где-либо еще в запросе в виде подстроки. Следовало бы реализовать более интеллектуальный разбор текста с использованием регулярных выражений, но, к сожалению, Transact-SQL не предоставляет таких возможностей.
Далее выполняется набор операций: создание временной таблицы и
наполнение ее данными исходной таблицы при помощи конструкции SELECT INTO, выполнение оператора пользователя и выборка результата. Выполнять DROP TABLE нет необходимости, временная таблица удалится автоматически при выходе из хранимой процедуры.
SQL Server создает временные таблицы не в памяти, а физически, в базе tempdb. Поэтому от данного метода не следует ожидать высокой
производительности. К тому же, таким образом созданная временная таблица не заимствует из исходной таблицы никаких ограничений и значений по умолчанию. Единственное достоинство этого метода - отсутствие каких-либо задержек и блокировок при одновременной работе многих пользователей. У каждого пользователя будет своя таблица, т.к. SQL Server «прозрачно» добавляет к ее имени идентификатор сетевого подключения.
Метод постоянных «индивидуальных» таблиц.
Для реализации этого метода достаточно подготовить процедуру создания всех необходимых таблиц и ограничений, а так же процедуру приведения этих таблиц в исходное состояние. Параметром для процедур будет служить идентификатор текущего пользователя, который будет добавляться к именам всех объектов для обеспечения уникальности. Технические аспекты реализации аналогичны, используемым в методе временных таблиц; сохраняется и проблема анализа текста пользовательского запроса для замены имен.
Основная же проблема заключается в том, что при большом количестве пользователей база данных вскоре окажется заполненной «мусором» -
неиспользуемыми таблицами, и придется принимать меры для ее очистки.
Устранить очевидные недостатки последнего подхода можно было бы с помощью механизма, напоминающего реализацию высокопроизводительных многопотоковых серверов (например, Oracle). Назовем его «гостиничным». Его суть состоит в следующем: мы заранее создаем несколько готовых наборов таблиц, с идентификаторами от 0 до N. При этом будем вести учет состояния каждого из наборов. Когда посетитель сайта принимается за решение упражнения, мы выделяем ему один набор из пула доступных наборов. После выхода пользователя мы приводим таблицы этого набора в исходное состояние и помечаем его как вновь доступный для других пользователей. В случае нехватки наборов их можно динамически добавлять, увеличивая тем самым значение N. При таком подходе пользователи будут работать с полноценными таблицами, не замечая никаких неудобств. Вся черновая работа по «уборке номеров» будет производиться в фоновом режиме, не увеличивая время отклика системы.
Этот механизм нами не тестировался.
Оценка производительности
Была произведена некоторая оценка производительности предложенных
подходов к решению. Тестовый запрос типа INSERT INTO pc VALUES (........)
многократно запускался на выполнение с помощью следующего скрипта:
declare @t1 datetime; declare @cnt int; set @t1=getdate(); set @cnt=0; while(@cnt<500) begin
-- (сам оператор DML или вызов ХП EXEC *)
end
set @t1=getdate()-@t1; select @t1;
Далее, разделив количество циклов на полученное общее время выполнения, находим среднюю скорость работы метода. Мы провели оценку на следующих программно-аппаратных конфигурациях:
1. Процессор Pentium 166, RAM 256 Mb, NT4 Server, SQL2k TRIGGER-метод отрабатывал 173 раз/сек ROLLBACK-метод - 192 раз/сек
Метод на временных таблицах - 18 раз/сек
2. Процессор Pentium4 1400, RAM 1024 Mb, W2k Server, SQL2k TRIGGER-метод отрабатывал 455 раз/сек ROLLBACK-метод - 417 раз/сек
Метод на временных таблицах - 52 раз/сек
На обеих машинах использовались современные жесткие диски. В обоих случаях наблюдалась одинаковая картина: на триггерах дисковая активность практически отсутствовала, на откатах - слабая активность, на временных таблицах -заметная нагрузка.
Как и следовало ожидать, в тестах лидируют два первых метода. Мы остановили свой выбор на TRIGGER-методе, хотя если нам потребуется уделить особое внимание проверке ограничений, ROLLBACK-метод будет незаменим.
В настоящее время на сайте http://www.sal-ex.ru выставлены 19 задач на все операторы модификации данных (INSERT, UPDATE и DELETE). Мы приглашаем всех желающих оценить работу системы (потребуется регистрация).
Заключение
Опыт эксплуатации системы дистанционного обучения «Упражнения по 8рЬ» показал, что данный образовательный ресурс оказался весьма востребованным, о чем говорит растущее число постоянных посетителей сайта. Однако был выявлен и существенный недостаток, который связан с отсутствием анализа допущенных при решении задачи ошибок. Ответ системы при совпадении результатов на основной базе только констатирует результат решения: правильно или неверно. Анализ ошибок пока выполняется только «экспертом», которым является модератор сайта или разработчик. Устранением этого недостатка и определяется направление дальнейших работ по развитию сайта. Система уже снабжена средством протоколирования всех промежуточных вариантов решений, всех синтаксических и логических ошибок пользователей. Анализируя накапливаемые данные, мы рассчитываем создать экспертную систему анализа ошибок.
Литература
[Моисеенко С.И., Майстренко А.В., 2003] Моисеенко С.И., Майстренко А.В. Практические навыки и дистанционное обучение // Educational technology & Society -2003 - V.6 - N. 2.- с.111-116. - ISSN 1436-4522.
