Это продолжение
Добавляем поддержку PostgreSQL
Сначала адаптирую запрос для работы в PostgreSQL.
Попытка выполнения запросов из предыдущих частей в PostgreSQL 15.6 вызывает ошибку:
ERROR: в рекурсивном запросе «levels» столбец 4 имеет тип
character(2000)в нерекурсивной части, но в результате типbpchar
LINE 23:cast(parent_node_id as char(2000)) as parents,
^
HINT: Приведите результат нерекурсивной части к правильному типу.
Это несколько неожиданно (по крайней мере, для меня) - "bpchar" это "blank-padded char", по идее то же самое, что и char(с указанием длины). Не буду спорить, просто заменю повсеместно char() на varchar:
Все тот же длинный запрос из части 2
with recursive Mapping as ( select id as node_id, parent_directory_id as parent_node_id, name as node_name from Files ), RootNodes as ( select node_id as root_node_id from Mapping where -- Exactly one line below should be uncommented -- parent_node_id is null -- Uncomment to build from root(s) node_id in (3, 10, 17) -- Uncomment to add node_id(s) into the brackets ), Levels as ( select node_id, parent_node_id, node_name, cast(parent_node_id as varchar) as parents, cast(node_name as varchar) as full_path, 0 as node_level from Mapping inner join RootNodes on node_id = root_node_id union select Mapping.node_id, Mapping.parent_node_id, Mapping.node_name, concat(coalesce(concat(prev.parents, '-'), ''), cast(Mapping.parent_node_id as varchar)), concat_ws(' ', prev.full_path, Mapping.node_name), prev.node_level + 1 from Levels as prev inner join Mapping on Mapping.parent_node_id = prev.node_id ), Branches as ( select node_id, parent_node_id, node_name, parents, full_path, node_level, case when root_node_id is null then case when node_id = last_value(node_id) over WindowByParents then '└── ' else '├── ' end else '' end as node_branch, case when root_node_id is null then case when node_id = last_value(node_id) over WindowByParents then ' ' else '│ ' end else '' end as branch_through from Levels left join RootNodes on node_id = root_node_id window WindowByParents as ( partition by parents order by node_name rows between current row and unbounded following ) order by full_path ), Tree as ( select node_id, parent_node_id, node_name, parents, full_path, node_level, node_branch, cast(branch_through as varchar) as all_through from Branches inner join RootNodes on node_id = root_node_id union select Branches.node_id, Branches.parent_node_id, Branches.node_name, Branches.parents, Branches.full_path, Branches.node_level, Branches.node_branch, concat(prev.all_through, Branches.branch_through) from Tree as prev inner join Branches on Branches.parent_node_id = prev.node_id ), FineTree as ( select tr.node_id, tr.parent_node_id, tr.node_name, tr.parents, tr.full_path, tr.node_level, concat(coalesce(parent.all_through, ''), tr.node_branch, tr.node_name) as fine_tree from Tree as tr left join Tree as parent on parent.node_id = tr.parent_node_id order by tr.full_path ) select fine_tree, node_id from FineTree ;
Этого оказалось достаточно, чтобы запрос заработал в соответствии с ожиданиями:
Возможно, использование varchar без указания длины несет в себе ограничения, с которыми не удается столкнуться на столь компактной иерархии - как обычно, "подозреваю" поле full_path
Чтобы проверить ограничения, нужна относительно большая иерархия, остается ее раздобыть.
Новый вариант запроса корректно работает в SQLite (в нем все текстовые типы, похоже, эквивалентны), но не в MySQL, в нем возникает ошибка вызова функции преобразования CAST():
Error occurred during SQL query execution
Причина:
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)) as parents,cast(node_name as char(2000)) as full_path,
0 as nod' at line 23
Большая иерархия (80 000+ элементов)
Не буду рассуждать на тему "зачем нужно визуализировать столь объемную иерархию".
Это делается для проверки работы скрипта, и выявления его возможных ограничений.
Удобный способ получения объемных иерархий - использовать структуру zip-архива. В качестве подопытного можно взять большой проект с Git'а - например, OpenJDK (83499 папок и файлов для использованной мною версии 22).
Чтобы скачать архив, нужно из раскрывающегося меню выбрать Download ZIP (объем архива 181 МБ)
gen_table()
Для генерации SQL-скрипта создания таблицы и вставки в нее строк со структурой архива я написал функцию gen_table() на Python
# # Generate SQL-script for creation of hierarchical table by zip-archive structure # from zipfile import ZipFile from itertools import count def file_size_b(file_size): """ Returns file size string in XBytes """ for b in ["B", "KB", "MB", "GB", "TB"]: if file_size < 1024: break file_size /= 1024 return f"{round(file_size)} {b}" def gen_table(zip_file, table_name='ZipArchive', chunk_size=10000, out_extension='sql'): """ by iqu 2024-04-28 params: zip_file - zip archive full path table_name - table to be created chunk_size - limit values() for each insert into part out extension - replaces zip archive file extension -> None, creates file with SQL script. Create table columns: id, name, parent_id, file_size - obvious, bytes - string with file size generated by file_size_b() """ def gen_create_table(file): print(f"drop table if exists {table_name};", file=file) print(f"create table {table_name} (id int, name varchar(255), parent_id int, file_size int, bytes varchar(16))" , file=file) def gen_insert(file): print(f";\ninsert into {table_name} (id, name, parent_id, file_size, bytes) values", file=file) out_file = ".".join(zip_file.split(".")[:-1] + [out_extension]) cnt = count() parents = ['NULL'] with open(out_file, mode='w') as of: gen_create_table(of) with ZipFile(zip_file) as zf: for zi in zf.infolist(): zi_id = cnt.__next__() if zi_id % chunk_size == 0: gen_insert(of) delimiter = '' else: delimiter = ',' level = zi.filename.count("/") - zi.is_dir() name = zi.filename.split("/")[level] file_size = -1 if zi.is_dir() else zi.file_size file_size_s = 'DIR' if zi.is_dir() else file_size_b(zi.file_size) if zi.is_dir(): if len(parents) < level + 2: parents.append(f"{zi_id}") else: parents[level + 1] = f"{zi_id}" print(f"{delimiter}({zi_id}, '{name}', {parents[level]}, {file_size}, '{file_size_s}')", file=of) print(';', file=of) gen_table(r"C:\TEMP\jdk-master.zip") # Sample archive https://github.com/openjdk/jdk/archive/refs/heads/master.zip
Объяснять код в деталях не буду, это совсем не по теме статьи. Существенно, что скрипт разбивается на части длиной максимум chunk_size (10 000 по-умолчанию) в каждом блоке values()
Кроме ИД, имени и ссылки на родителя, каждая запись содержит поле file_size с длиной в байтах (-1 для папок) и поле bytes с длиной, преобразованной к строке в байтах, килобайтах и т.д. ('DIR' для папок)
Передав функции параметром путь к скачанному выше архиву, я получил SQL-скрипт создания иерархии. Прикладывать его не буду, его архив "весит" почти 1МБ, способ его получения детально описан
Скрипт добавления записей состоит из 9 частей. Он успешно выполнился во всех трех "подопытных" СУБД. Индексы не создавались.
Проверка в MySQL, SQLite и PostgreSQL
Для проверки MySQL и SQLite буду использовать скрипт из второй части статьи, для PostgreSQL - приложенный в начале этой статьи.
Приведу время выполнения скриптов на своем домашнем ПК под Windows 10. Все СУБД установлены в разделе C:\, находящемся на SSD, файлы баз данных расположены на нем же. Все настройки при установке СУБД по-умолчанию.
Все скрипты выполняются из DBeaver 24.0.2, время округлено - задача не сравнить СУБД между собою, а проверить работоспособность скриптов в их средах.
Для разнообразия при визуализации иерархии к имени узла будет добавляться строковый размер файла в скобках, а из финального CTE будет извлекаться так же уровень узла в иерархии:
with recursive Mapping as ( select id as node_id, parent_id as parent_node_id, concat(name, ' (', bytes, ')') as node_name from ZipArchive ), ... select fine_tree, node_id, node_level from FineTree ;
Так же будет выполнен скрипт для проверки иерархий (показана только нижняя строка), вычисляющий сумму длин поля full_path всех узлов, и максимальную длину этого поля:
... select sum(length(full_path)), max(length(full_path)) from FineTree ;
Скрипт | MySQL 8.2 | SQLite 3 | PostgreSQL 15.6 |
| 1800 ms | 200 ms | 500 ms |
Визуализация иерархии | 2 s | 1 s | 3 s |
Проверка иерархии sum(length(full_path)) max(length(full_path)) | 2 s
| 1 s
| 3 s
|
При создании и наполнении таблицы я ориентировался на статистику, отображаемую после выполнения скрипта
Пример для SQLite
Наибольшая вложенность иерархии - 16
Порядок отображения иерархии несколько отличается между СУБД - для SQLite он регистрозависимый, а регистронезависимые MySQL и PostgreSQL по-разному сортируют некоторые строки:
Для целей визуализации иерархии эти различия не принципиальны. В рамках одной СУБД вывод стабилен
Выводы
Результаты проверки иерархии во всех СУБД совпали, даже с учетом разных типов данных в MySQL и PostgreSQL. Максимальная длина поля full_path превышает 255, таким образом, можно смело "снять подозрения" с варианта запроса для PostgreSQL с использованием varchar без указания длины, обрезки строки не происходит, можно использовать запрос в этом варианте так же для SQLite.
В целом удалось достичь цели, используя практически одинаковый запрос для трех разных СУБД.
Делитесь в комментариях, какие наиболее объемные иерархии вам удалось визуализировать, и удалось ли добиться аномалий при работе запроса.
На этом цикл статей окончен, спасибо всем за проявленный интерес - он стал для меня неожиданностью, так как практической составляющей в статьях нет )))
