Разработка и анализ хранимой процедуры для получения глубины дерева связей таблицы и схемы базы данных

Размещено на http://www.allbest.ru/

Разработка и анализ хранимой процедуры для получения глубины дерева связей таблицы и схемы базы данных

Рыбанов Александр Александрович



В статье представлена хранимая процедура для получения получения глубины дерева связей таблицы и схемы базы данных. Произведен анализ процедуры с помощью SQLDetective 4.3. Получены и проанализированны метрики кода и составлена блок-схема хранимой процедуры.

Похожие материалы

· Анализ навигационного меню образовательного сервиса с использованием метода сортировки карточек

· Проектирование навигационного меню сайта на основе метода сортировки карточек

· Анализ программ моделирующих транспортные потоки методом Саати

· Сравнительный анализ программных продуктов оценки инвестиционных проектов

· Сравнительный анализ библиотек, поддерживающих контрактное программирование

Анализ количественных метрик физических схем баз данных является актуальной задачей [4-6, 10, 12], так как база данных является ядром информационной системы.

Применение количественных метрик физических схем баз данных позволяет разработчикам [1-3, 8, 13]:

1. оценить сложность разработанной базы данных;

2. оценить общий объем работ по созданию базы данных;

3. оценить объем работ, выполненных каждым членом команды;

4. выбрать наилучшую схему базы данных из нескольких вариантов;

5. оценить сложность реализации базы данных.

Целью работы является повышение эффективности процесса анализа хранимых процедур с помощью CASE-средств (на примере хранимой процедуры для получения глубины дерева связей таблиц и схемы базы данных).

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

Расчет физических характеристик физических схем баз данных рассмотрим на примере СУБД MySQL. Исходные данные для получения метрических характеристик MySQL базы данных хранятся в базе данных метаинформации - information_schema.

На основе метаданных Information_schema могут быть получены следующие метрики БД [7, 9, 11]: глубина дерева связей таблиц БД; глубина дерева связей схемы БД.

Глубина дерева связей таблицы - это расстояние от ее конкретной таблицы, до элемента самого нижнего уровня.

Глубина дерева связей схемы БД - это максимальное расстояние от её корневого элемента, до элемента самого нижнего уровня.

Для апробации предлагаемого подхода получения метрических характеристик была использованная база данных «sakila» (рис. 1) с сайта dev.mysql.com (http://dev.mysql.com/doc/sakila/en/sakila-structure.html).

Рис. 1. Физическая схема базы данных «sakila»



Метрические характеристики глубины дерева связей для таблиц базы данных «sakila» приведены в таблице 1.

хранимый процедура код данные

Таблица 1. Глубина дерева связей для таблиц базы данных «sakila»

Имя таблица	Глубина дерева связей таблицы
payment	7
rental	5
inventory	5
staff	4
store	4
customer	3
address	2
film_category	2
city	1
film	1
film_actor	1
language	0
actor	0

Анализ метрических характеристик, представленных в таблице 1, показывает, что глубина дерева связей базы данных «sakila» равна 7.

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

Возможны следующие варианты вызова:

1) Получение метрических характеристик для всех таблиц базы данных:

GetDBTablesDepth(<имя базы данных>);

2) Получение метрических характеристик для конкретной таблицы базы данных

GetDBTablesDepth(<имя базы данных>, <имя таблицы>);

Исходный код хранимой процедуры:

PROCEDURE GetDBTablesDepth(IN for_schema_name varchar(240), IN for_table_name varchar(240))

BEGIN

-- идентификатор сети "родительской" таблицы при составлении подсетей

declare netId1 mediumint;

-- идентификатор сети "таблицы-потомка" при составлении подсетей

declare netId2 mediumint;

-- счётчик количества подсетей

declare curNetId mediumint default 0;

-- идентификатор подсети для текущей пары таблиц

declare curStepNetId mediumint;

-- имя "родительской" таблицы

declare t1 varchar(240);

-- имя "таблицы-потомка"

declare t2 varchar(240);

-- текущий цикл завершён?

declare done int default false;

-- текущая "родительская" таблица при расчёте глубины

declare t varchar(240) default t1;

-- текущая "таблица-потомок" (следующий шаг) при расчете глубины

declare t_next varchar(240);

-- количество не обойденных "таблиц-потомков" у текущей "родительской" таблицы

declare child_count mediumint;

-- текущая достигнутая глубина обхода

declare d mediumint default 1;

-- перекресток - глубина таблицы, к которой требуется возвратиться

declare cur_crossroad_depth mediumint default 0;

-- идентификатор текущего обхода

declare cur_traversal_id mediumint default 1;

-- идентификатор последнего обхода

declare last_traversal_id mediumint;

-- ветвь - уникальный идентификатор однозначно идентифицирующий весь пройденный путь до текущей таблицы

declare cur_branch_id mediumint default 0;

-- максимальная длинна для текущего обхода

declare max_depth mediumint;

-- курсор для перебора всех таблиц, ссылающихся на другие таблицы в текущей базе данных

declare cur1 cursor for

select distinct `table_name`, `referenced_table_name`

from `information_schema`.`key_column_usage`

where (`table_schema` = for_schema_name and `referenced_table_name` is not null);

-- курсор для перебора всех таблиц, НЕ ссылающихся на другие таблицы в текущей базе данных

declare cur11 cursor for

select distinct `table_name`

from `information_schema`.`key_column_usage`

where (`table_schema` = for_schema_name and `referenced_table_name` is null);

-- курсор для перебора всех таблиц, в каждой из подсетей

declare cur2 cursor for

select `net_id` , `t_name`

from __TableNets

order by net_id;

drop table if exists __TableNets;

create table if not exists __TableNets (

net_id mediumint not null,

t_name char(64) not null,

parent_count mediumint not null);

open cur1;

-- из всех таблиц в текущей базе данных составляем выбираем те таблицы,

-- которые связаны друг с другом. В итоге получаем несколько связанных подсетей

make_nets: loop

begin

declare continue handler for not found set done = true;

fetch cur1 into t1, t2;

end;

if done then

leave make_nets;

end if;

set netId1 = null;

set netId2 = null;

-- находим есть ли уже данная таблица в __TableNets, берём её net_id

select net_id into netId1 from __TableNets where __TableNets.t_name=t1;

select net_id into netId2 from __TableNets where __TableNets.t_name=t2;

set curStepNetId = null;

if netId2 is not null then

set curStepNetId = netId2;

end if;

if netId1 is not null then

set curStepNetId = netId1;

end if;

-- если находим что 2 подсети связаны, меняем занчение net_id на одинаковое у обеих

if netId1 is not null and netId2 is not null and netId1 != netId2 then

update __TableNets set net_id=netId1 where net_id = netId2;

end if;

if curStepNetId is null then

set curStepNetId = curNetId;

-- наращиваем счётчик количества подсетей

set curNetId = curNetId + 1;

end if;

-- если первый элемент для сети новый

if netId1 is null then

-- добавляем его в эту подсеть

insert into __TableNets values(curStepNetId, t1, 0);

end if;

-- если второй новый для сети

if netId2 is null then

-- добавляем его в эту подсеть

insert into __TableNets values(curStepNetId, t2, 1);

else

update __TableNets set parent_count=parent_count + 1

where net_id = netId2 and t_name=t2;

end if;

end loop;

close cur1;

-- добавляем отдельно стоящие таблицы как отдельные подсети

open cur11;

set done = false;

add_standalone_vertices: loop

begin

declare continue handler for not found set done = true;

fetch cur11 into t1;

end;

if done then

leave add_standalone_vertices;

end if;

set curNetId = curNetId + 1;

-- вставляем таблицу, если она отсутствует

if t1 != "__Traversal" and t1 != "__TableNets" then

insert into __TableNets(net_id, t_name, parent_count)

select * from (select curNetId, t1, 0) AS ttmp

where not exists (

select t_name from __TableNets where t_name = t1) limit 1;

end if;

end loop;

close cur11;

-- таблица результатов: максимальные длины обхода для выбранного "корня"

drop table if exists Results;

create temporary table if not exists Results (

id mediumint not null auto_increment,

depth mediumint not null,

root char(64),

primary key(id));

open cur2;

set done = false;

-- для каждой таблицы в базе данных обходим считая её корневой, родительской всю подсетью

-- находя все возможные глубины обхода

all_tables: loop

begin

declare continue handler for not found set done = true;

fetch cur2 into curNetId, t1;

end;

if done then

leave all_tables;

end if;

set t = t1;

set d = 0;

set cur_crossroad_depth = 0;

set cur_traversal_id = 1;

set cur_branch_id = 0;

-- таблица в которой отражаются все обходы для текущей выбранной "корневой" таблицы

drop table if exists __Traversal;

create table if not exists __Traversal (

id mediumint not null auto_increment,

depth mediumint not null,

t_name char(64) not null,

traversal_id mediumint not null,

traversal_depth mediumint,

last_crossroad_depth mediumint not null,

branch_id mediumint not null,

primary key(id));

insert into __Traversal (`depth`, `t_name`, `traversal_id`, `traversal_depth`, `last_crossroad_depth`, `branch_id`)

values(-1, t, cur_traversal_id, 1, -1, 0);

-- находим все возможные обходы и глубины этих обходов для текущей выбранной корневой таблицы.

net_traversal: loop

set t_next = null;

-- выбираем ребёнка текущей таблицы, который ещё не пройден в текущем проходе

-- или который не был пройден как такой же шаг у которого предок = текущей вершине

select count(distinct `referenced_table_name`) into child_count

from `information_schema`.`key_column_usage`

where `table_schema` = for_schema_name and `table_name`=t

and `referenced_table_name` not in

(select `t_name`

from __Traversal

where traversal_id=cur_traversal_id

or id in (select id+1 from __Traversal where `branch_id`=(

select `branch_id`

from __Traversal

order by id desc

limit 1)));

select distinct `referenced_table_name` into t_next

from `information_schema`.`key_column_usage`

where `table_schema` = for_schema_name and `table_name`=t

and `referenced_table_name` not in

(select `t_name`

from __Traversal

where traversal_id=cur_traversal_id

or id in (select id+1 from __Traversal where `branch_id`=(

select `branch_id`

from __Traversal

order by id desc

limit 1)))

limit 1;

-- следующая "таблица-потомок" не найдена

if t_next is null then

update __Traversal set traversal_depth = d where traversal_id=cur_traversal_id;

-- выбираем таблицу перекрёсток для текущей таблицы

select `last_crossroad_depth` into d

from __Traversal

order by id desc

limit 1;

if d = -1 then

leave net_traversal;

end if;

-- следующей таблицей будет таблица перекрёсток

select `t_name` into t

from __Traversal

where traversal_id=cur_traversal_id

and depth=d;

set cur_traversal_id = cur_traversal_id + 1;

-- восстанавливаем в __Traversal весь путь до этого перекрёстка

insert into __Traversal (`depth`, `t_name`, `traversal_id`, `last_crossroad_depth`, `branch_id`)

select `depth`, `t_name`, cur_traversal_id, `last_crossroad_depth`, `branch_id` from __Traversal

where traversal_id=cur_traversal_id - 1 and depth

Для анализа метрических характеристик было использовано CASE-средство SQLDetective 4.3. Данное инструментальное средство позволяет получать метрики только для PL/SQL кода, поэтому для анализа кода процедуры был применен онлай конвертор из MySQL в PL/SQL - SQLine (http://www.sqlines.com/online).

Метрические характеристики для хранимой процедуры GetDBTablesDepth() приведены в таблице 2.

Таблица 2. Метрики хранимой процедуры GetDBTablesDepth

Метрика	Значение метрики
Цикломатическая сложность (Cyclomatic Complexity)	28
Комплексный показатель качества кода (Maintainability Index)	4
Количество строк программы (LOC)	284
Процент комментариев (Comment Pct.)	13
Объем программы по Холстеду (Halstead Volume)	2118
Длинна программы (Program Length)	336
Словарь программы (Program Vocabulary)	79
Сложность программы (Difficulty)	39
Усилия при разработке (Effort)	82605
Сложность интерфейса (Interface Complexity)	5
Входящие параметры (Input parameters)	5
Обязательные входящие параметры (Required Input parameters)	5
Точки возврата (Return Points)	0
Функциональная сложность (Functional Complexity)	33
Пустые строки (Blank Lines)	175
Строки комментариев (Comment Lines)	71
Эффективные строки кода (eLOC)	238
Логические операторы кода (lsLOC)	81
Строки псевдокода (Pseudocode Lines)	0

Блок схема хранимой процедуры, сгенерированная с помощью SQLDetective 4.3, представлена на рис. 2-6.



Рис.2. Блок схема процедуры GetDBTablesDepth: фрагмент 1



Рис.3. Блок схема процедуры GetDBTablesDepth: фрагмент 2

Рис.4. Блок схема процедуры GetDBTablesDepth: фрагмент 3



Рис.5. Блок схема процедуры GetDBTablesDepth: фрагмент 4

Рис.6. Блок схема процедуры GetDBTablesDepth: фрагмент 5



Значение цикломатической сложности хранимой процедуры GetDBTablesDepth попадает в интервал [21; 50] - при таком значении цикломатической сложности существует 30% вероятность возникновения проблем поиске ошибок в процессе тестирования процедуры. Комплексный показатель качества кода хранимой процедуры GetDBTablesDepth попадает в интервал [0; 9], что свидетельствует о сложности дальнейшего преобразования программного кода. Объем хранимой процедуры GetDBTablesDepth по Холстеду равен 2118, что превышает рекомендуемое значение 1000 для функций и процедур. Данные метрики в данном случае результат говорит о необходимости дальнейшей модификации программного кода хранимой процедуры. Не было цели создать оптимизированную процедуру. Полученная процедура полностью удовлетворяет требованиям к ней.

Представленный подход получения метрических характеристик для физической схемы MySQL-базы данных, основанный на метаинформации information_schema и реализации хранимых процедур, с последующей оценкой их качества с помощью SQLDetective 4.3, может использоваться для создания библиотеки хранимых процедур оценки метрик физических баз данных.



Список литературы

1. Азаров А.В., Рыбанов А.А. Автоматизированная система расчета метрических характеристик физической схемы базы данных с целью оценки трудоемкости процесса проектирования // Современная техника и технологии. 2014. № 5 (33). С. 39.

2. Кузьмин А.А., Рыбанов А.А. Исследование методов количественной оценки схем реляционных баз данных // Успехи современного естествознания. 2011. № 7. С. 137-138.

3. Морозов А.О., Рыбанов А.А. Подходы к измерению количественных метрик физических схем баз данных [Электронный ресурс] // Студенческий научный форум 2014: докл. VI междунар. студ. электрон. науч. конф., 15 февр. - 31 марта 2014 г. Направл.: Технические науки / РАЕ. - М., 2014. - C. 1-8. - Режим доступа: http://www.scienceforum.ru/2014/pdf/143.pdf.

4. Рыбанов А.А. Оценка сложности физической схемы реляционной базы данных // Cовременная техника и технологии. 2014. № 9 (37). С. 26-30.

5. Рыбанов А.А. Анализ базовых возможностей программных продуктов для исследования метрических характеристик баз данных // NovaInfo.Ru. 2015. Т. 2. № 33. С. 20-28.

6. Макушкина Л.А., Рыбанов А.А., Приходько Е.А. Электронный учебник как знаковое средство построения и организации обучения // Известия Волгоградского государственного технического университета. 2009. Т. 6. № 10 (58). С. 98-100.

7. Рыбанов А.А., Морозов А.О. Автоматизация расчета метрических характеристик физических схем баз данных на основе концептуальных графов // Молодой ученый. 2014. № 9 (68). С. 26-30.

8. Рыбанов А.А., Усмонов М.С.О., Попов Ф.А., Ануфриева Н.Ю., Бубарева О.А. Информационные системы и технологии / Научный ред. И. А. Рудакова / Центр научной мысли (г. Таганрог). Москва, 2013. Том Часть 4. - 90 с.

9. Рыбанов А.А., Коростелев Р.А., Киселев В.В. IDEF1X-модель базы данных web-ориентированной информационной системы оценки семантического качества меню пользователя // Молодой ученый. 2013. № 5. С. 170-172.

10. Свид. о гос. регистрации базы данных № 2013620276 от 13 февраля 2013 г. РФ, МПК (нет). База данных web-ориентированной информационной системы «Кафедра» / Рыбанов А.А.; ВолгГТУ. - 2013.

11. Утицких И.А., Рыбанов А.А. Анализ физических схем реляционных баз данных [Электронный ресурс]: доклад // Студенческий научный форум 2013: V междунар. студ. электрон. науч. конф., 15 февр. - 31 марта 2013 г. Направл. / Рос. акад. естествознания. - М., 2013. - С. 1-4. - Режим доступа:http://www.scienceforum.ru/2013/pdf/4553.pdf.

12. Утицких И.А., Рыбанов А.А. Исследование метрических характеристик физических схем реляционных баз данных // Девятнадцатая межвузовская научно-практическая конференция молодых учёных и студентов, г. Волжский, 27-31 мая 2013 г. : тез. докл. / Филиал МЭИ в г. Волжском. - Волжский, 2013. - C. 39-41.

13. Черняев А.О., Рыбанов А.А. Разработка и исследование алгоритмов автоматизированного проектирования логических схем реляционных баз данных // В мире научных открытий. 2010. № 4-11. С. 128-129.

Размещено на Allbest.ru