Автоматизация тестирования таблиц в Postgresql на SQL

[am-habr]

Хороший способ для больших проектов или где много людей работают над одними и теми же объектами. Тоже так делали, только в виде отдельных вьюшек и потом одна сборная. Проверять можно также правила написания полей и таблиц, префиксы, суффиксы и тд. Структура была проще: описание, таблица и текст. Тогда и тесты можно более гибкими делать.

[Imortholity]

Случаем, не осталось примера под рукой? Интересно было бы посмотреть)

[sshikov]

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

Разработчики не должны создавать таблицы вручную. А если они создаются кодом, как положено, и мигрируют на новую версию тоже кодом (то есть имеет место повторяемость), то трудоемкость написания того, что вы тут показываете, выглядит намного большей, чем трудоемкость написания DDL для самого создания таблиц. Ну т.е. тест должен по сложности примерно соответствовать сложности написания CREATE TABLE, иначе вы замучаетесь его сопровождать.

Ну т.е. сама по себе идея проверять соответствие схемы базы требованиям - она несомненно правильная, но показанное решение выглядит как не слишком гибкое и трудоемкое в разработке. Ну или возможно что я вас не очень понял, вы описали внутренности инструмента, а на самом деле реальные тесты выглядят совсем несложно и пишутся/сопровождаются легко и быстро?

[Imortholity]

Это правда, DDL написать проще. В моем случае удобство использования такого запроса связано с тем, что:

1. Требования к таблицам описываются аналитиком в виде отдельной таблицы. DDL при этом отсутствует;

2. Новые таблицы, описанные в требованиях, добавляются по согласованию либо аналитиком, либо разработчиком;

3. Бывает так, что добавленные новые таблицы правят в БД, но вместе с тем не обновляют таблицу с требованиями в Wiki. Отсюда получается расхождение;

4. Со своей стороны я, будучи QA, не завожу новые таблицы, но могу проверить их на соответствие требованиям. Это можно сделать глазами, открыв два окна и сравнивая значения, либо через описанный запрос. С учетом того, что после внесения обновлений в требования могут быть опять, к примеру, изменения в БД, то в этом случае прогнать запрос значительно быстрее, чем проводить сравнение глазами. У нас еще и алгоритмы, являющиеся частью функциональностей проекта, могут падать из-за измененных метаданных таблиц. И проверочные запросы помогают локализовать дефект, когда в логах появляется название одной из таких таблиц

[sshikov]

Я прекрасно представляю, насколько сложно проверить структуру - у меня это одна из задач немаленького приложения. Так что уточню, что я вовсе не имел в виду, что вы что-то делаете не так.

[Imortholity]

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

Спасибо вам! :)

у меня это одна из задач немаленького приложения

А вы как проверяете?)

[sshikov]

А вы как проверяете?)

Да в общем-то примерно так же. Разница с нашим в том, что мне нужно сравнить схему базы в источнике (это одна из трех СУБД разного вида) и реплике (это Apache Hive), где у колонок разные типы данных, некоторых типов вообще в реплике нет, репликация происходит без внешних ключей и скажем индексов, и т.п. Ну т.е. в чем-то у нас проще, в чем-то сложнее. В сущности, есть набор запросов для выборки данных про схему (для источников там есть и запросы про ключи, партиционирование и т.п.), а потом некая надстройка над всем этим, которая проверяет, что скажем числовые типы там и там не просто равны (это иногда невозможно), а совместимы, т.е. множество значений целевого типа шире чем у исходного.

[VanKrock]

Сам подход понятен, но не понятно ЗАЧЕМ это делать. Вы описываете таблицу, в sql это делается декларативно, то есть вы в скрипте на создание уже указали какие поля уникальные, какие является первичным ключём, какие вторичным. Писать тест на то что вы правильно всё описали просто в другом виде? А где гарантия, что если вы забыли колонку при создании таблицы, вы не забудете её в тестах? По мне так тестировать следует функциональность а не описание, то есть если вы создали таблицу в котором колонка может содержать только уникальные значения, то проверять следует, что при попытке вставить не уникальное значение, вы получите ошибку, так же если у вас колонка помечена как вторичный ключ, то при попытке вставить значение, которого нет в связанной таблице, вы получите ошибку

[Imortholity]

В комменте выше описал, почему в моем случае этот запрос оказался полезен. Возможно, эта информация подойдет в качестве ответа.

А где гарантия, что если вы забыли колонку при создании таблицы, вы не забудете её в тестах

Создает один специалист, тесты пишет другой. Так снижается вероятность ошибки. 100%-х гарантий тестирование дать не может, полагаю, исходя из принципов тестирования.

[Mingun]

А почему вы решили использовать string_agg для сбора списков, а не array_agg? Кажется, что последний поудобнее будет.

И я же правильно понял, что в проверке внешних ключей вы не достаёте информацию о том, в какую таблицу ведёт этот внешний ключ? Тогда запрос будет показывать неверные результаты, если одна таблица (например) ссылается на несколько справочников (условно, price.unit_id => units.id и price.model_id => models.id) или если есть один общий универсальный справочник, в разные разделы которого подключаются разные поля (price.unit_id => refs.id и price.model_id => refs.id).

[Imortholity]

Кажется, что последний поудобнее будет.

Так?

И я же правильно понял, что в проверке внешних ключей вы не достаёте информацию о том, в какую таблицу ведёт этот внешний ключ? Тогда запрос будет показывать неверные результаты.

Ага, есть такое. У меня по задачам не возникало такой ситуации, поэтому не думал об этом. Весомое и дельное замечание. Название таблиц, на которое направлено ограничение, а также схемы, в которых хранятся эти таблицы, можно вытащить по запросу в CTE constraints_query. Однако этого недостаточно для корректной работы запроса, нужно также доработать логику сравнения ограничений. Оставлю здесь исправленный вариант для описанного в статье примера:

Исправленный код

with
    -- Настраиваем запрос: задаем название таблицы и название схемы
    params(table_name, table_schema) as (
        values (
            'price',
            'public'
        )
    ),
    -- Описываем ожидаемые столбцы таблицы
    required_columns(column_name, data_type, is_nullable, character_maximum_length) as (
        values            
            ('id',          'bigint',               'NO', 	null),
            ('price_value', 'numeric',              'NO', 	18.6),
            ('model_id',    'integer',  		    'NO',	null),
            ('comment',     'character varying',    'YES',  255)
    ),
    -- Описываем ожидаемые ограничения
    required_constraints(column_name, constraint_type, constraint_table, table_schema) as (
        values            
            ('id',          'PRIMARY KEY',	'price',	'public'),
            ('id',    		'FOREIGN KEY',	'models',	'public'),
            ('model_id',    'FOREIGN KEY',	'price',	'public'),
            ('price_value', 'UNIQUE',		'price',	'public'),
            ('model_id',    'UNIQUE',		'price',	'public')
    ),
    -- Находим информацию о столбцах тестируемой таблицы и добавляем обработку null'ов
    columns_info as (
        select
            column_name, data_type, is_nullable,
            coalesce(numeric_precision, 0)          as numeric_precision,
            coalesce(numeric_scale, 0)              as numeric_scale,
            coalesce(character_maximum_length, 0)   as character_maximum_length
        from information_schema.columns
        where
            table_name       = (select table_name   from params)
            and table_schema = (select table_schema from params)
    ),
    -- Проверяем существование таблицы и подсчитываем количество столбцов в ней
    check_table_exist as (
        select
            case when count_all_fields < 1 then false else true end table_exists,
            case when count_all_fields < 1 then 1 else 0 end table_exists_error,
            count_all_fields
        from (
            select count (*) as count_all_fields
            from columns_info
        ) sq
    ),
    -- Сравниваем ожидаемый и текущий наборы атрибутов таблицы
    fields_comparison as (
        select t.*
        from columns_info t
        inner join required_columns r
            on  t.column_name   = r.column_name
            and t.data_type     = r.data_type
            and t.is_nullable   = r.is_nullable
            and (
                -- Сравниваем целую часть десятичных значений
                case
                    when t.data_type = 'numeric'
                    then t.numeric_precision = trunc(r.character_maximum_length::numeric)
                end
                and
                -- Сравниваем дробную часть десятичных значений
                case
                    when t.data_type = 'numeric'
                    then t.numeric_scale = (r.character_maximum_length::numeric - trunc(r.character_maximum_length::numeric))
                    * power(10, length(split_part(r.character_maximum_length::text, '.', 2)))
                end
                or t.character_maximum_length = coalesce(r.character_maximum_length::numeric, 0)
           )
    ),
    -- Ищем лишние столбцы и считаем их количество
    check_unexpected_fields as (
        select
            count (column_name) as count_unexpected_fields,
            string_agg(column_name, ', ') as unexpected_fields
        from (
            select column_name
            from columns_info
            except
            select column_name
            from required_columns
        ) sq
    ),
    -- Ищем недостающие столбцы и считаем их количество
    check_missing_fields as (
        select
            count (column_name) as count_missing_fields,
            string_agg(column_name, ', ') as missing_fields
        from (
            select column_name
            from required_columns
            except
            select column_name
            from columns_info
        ) sq
    ),
    -- Ищем невалидные столбцы и считаем их количество
    check_invalid_fields as (
        select
            count (column_name) as count_invalid_fields,
            string_agg(column_name, ', ') as invalid_fields
        from (
            select column_name
            from required_columns
            except
            select column_name
            from fields_comparison
            except
            select string_to_table(missing_fields, ', ')
            from check_missing_fields
        ) sq
    ),
    -- Ищем все ограничения для таблицы
    constraints_query as(
        select
            t1.constraint_type,
            t1.table_name 	as foreign_table_name,
            t1.table_schema	as foreign_table_schema,
       		t2.table_schema,
            t2.table_name,
            t2.column_name,
            t3.column_name	as foreign_column
        from information_schema.table_constraints t1
        left join information_schema.constraint_column_usage t2
            on t1.constraint_name = t2.constraint_name
        left join information_schema.key_column_usage as t3
            on t1.constraint_name = t3.constraint_name
            and t1.table_schema = t3.table_schema
        where
            t1.table_name            = (select table_name   from params)
            and t1.constraint_schema = (select table_schema from params)
            and t2.column_name is not null
    ),
    -- Включаем значения зависимых ключей (foreign_column) в список ограничений (column_name)
    union_foreign_ref_columns as (
        select distinct on (constraint_type, column_name) column_name, constraint_type, table_name, table_schema
        from constraints_query
        union all
        select foreign_column, constraint_type, foreign_table_name, foreign_table_schema
        from constraints_query
        where constraint_type = 'FOREIGN KEY'
    ),    
    -- Ищем лишние ограничения и считаем их количество
    check_unexpected_constraints as (
        select
            count (column_name) as count_unexpected_constraints,
            string_agg(concat_ws(', ', column_name, constraint_type, table_name, table_schema), E'\n') as unexpected_constraints
        from (
            select *
            from union_foreign_ref_columns
            except
            select column_name, constraint_type, constraint_table, table_schema
            from required_constraints
        ) sq
    ),
    -- Ищем недостающие ограничения и считаем их количество
    check_missing_constraints as (
        select
            count (column_name) as count_missing_constraints,
            string_agg(concat_ws(', ', column_name, constraint_type, constraint_table, table_schema), E'\n') as missing_constraints
        from (
            select column_name, constraint_type, constraint_table, table_schema
            from required_constraints
            except
            select *
            from union_foreign_ref_columns
        ) sq
    ),
    -- Собираем полученные данные
    checks as (
        select
            -- Выводим всю информацию об ошибках и суммируем их количество
            table_exists_error + count_unexpected_fields + count_missing_fields + count_invalid_fields +
            count_unexpected_constraints + count_missing_constraints as errors,
            *
        from        check_table_exist
        cross join  check_unexpected_fields
        cross join  check_missing_fields
        cross join  check_invalid_fields
        cross join  check_unexpected_constraints
        cross join  check_missing_constraints
)
select *
from checks

Спасибо вам!

[Mingun]

Так?

Да. Только у меня DBeaver в таких случаях показывает список первых нескольких значений через запятую в фигурных скобках, а не просто [2]. Не знаю, почему у вас вот так.