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

GetDBTablesDepth();

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