Движок для построения отчётов на SQL. Набросок реализации. complete text

Движок для построения отчётов на SQL. Набросок реализации
SQL*, ERP-системы*
Введение

В первой статье ( Движок для построения отчётов на SQL. Идея ) я поделился идеей. Теперь поделюсь решением ( черновиком ). Этот черновик — мой первый опыт «серьезной» работы с T-SQL, поэтому не стоит его принимать за образец «хорошего» кода.
Самое важное в этом черновике это механизм подстановки формул в динамический запрос. Второе по важности это механизм сохранения результатов вычислений. 

Когда я приступал к работе — я ожидал больших трудностей, но на самом деле все оказалось очень просто. Много писанины и всего пара моментов когда пришлось остановиться и подумать. Первый момент это генерация номера строки в выдаче запроса, Второй — генерация значения для ключевого поля. 
Глаза бояться — руки делают!
Я начну сразу с самого основного и интересного, для тех кому этого мало — ниже будет доскональный разбор логики. Приступим.

Вычисление формул

Разница между Колонками и Разделами

Есть существенная разница между вычислением формулы для заполнения колонки и вычислением формулы для заполнения полей раздела ( шапки или подвала ). Эта разница заключается в том что колонка вычисляется для каждой строки отдельно, а раздел вычисляется один раз для всех строк разом.
Формулы для шапки это всегда агрегатные функции и результат вычисления формулы надо «вклеить» в «посадочное место» шаблона.
Вычисленное значение для колонки надо «забиндить» ( связать ) со строкой для которой эта колонка была вычислена.
Поэтому для вычисления колонки и шапки разработаны разные шаблоны.
Шаблон для Раздела

SET @sql_text = 
N' SELECT  @result = '
+ @formula 
+ N' FROM table '

Все линейно:
вычислили формулу;
результат подставили в шаблон;
что получились сохранили в соответствующей таблице ( report_region_instances );
конец — шапка сформирована;

Шаблон для Колонки

С колонками посложней. Если результат вычисления шапки это одно значение, то результат вычисления колонки это множество — таблица состоящая из одной колонки и некоторого количества строк.
Для сохранения результата ( в таблицу report_cell_instances ) необходимо каждую строку пронумеровать, что бы во время вывода отчёта все строки колонок можно было синхронизировать между собой. 
Для этого необходимо строки некоторым единым способом упорядочить — отсортировать. Добавляем в запрос на вычисление колонки фразу «ORDER BY», в «SELECT» добавляем " ROW_NUMBER() OVER( ORDER BY )".
Шаблон запроса: 
SET @sql_text = 
N' SELECT 
    ROW_NUMBER() OVER( ORDER BY key_column) ,'
 + @formula 
 + N' FROM table ORDER BY key_column'

Не сложно. Следующий интересный момент это сохранение вычислений — результатов нашей работы.

Сохранение результата.

С сохранением раздела ( шапки или подвала ) нет ни каких трудностей — банальный «INSERT» чего надо куда надо ( в таблицу report_region_instances ).
С сохранением вычисленной колонки тоже ни чего сложно, надо наш динамический запрос дополнить оператором «INSERT».
Проблема только в генерации значения поля уникального ключа. Есть отличное решение этой задачи с использованием автоинкрементной колонки ( свойство IDENTITY ), но я люблю иметь максимальный контроль за тем что делает моя программа, поэтому я воспользовался другим инструментом — «SEQUENCE» — и генерирую каждый номер в ручную.
Шаблон запроса: 
SET @sql_text = 
N' INSERT INTO report_cell_instances 
( id , row_order , value )
SELECT (NEXT VALUE FOR [dbo].[report_cell_instances_sequence] OVER( ' 
+ @C_ORDER_BY 
+ N' ) ) AS Record_Id , ROW_NUMBER() OVER( ' 
+ @C_ORDER_BY 
+ N' ) AS Row_Order , '
+ @formula
+ N' AS Formula_Result FROM table' + @C_ORDER_BY


Доскональный разбор реализации

Реализация выполнена в качестве T-SQL скрипта, в рабочей реализации это должна быть хранимая процедура, состав входных параметров под вопросом — зависит от потребностей заказчика. У меня в скрипте это: 
номер станции — выбирается случайным образом из таблицы meteo_stations_reference,
период дат — выбираются две случайные даты из таблицы meteo_measurements для выбранной станции
клиент — выбирается случайным образом из таблицы consumer_reference

Другие вещи которые должны быть как минимум константами сделаны в стиле «hard code» aka «magic number», считайте это издержками «черновика».
Код я писал в dbForge Studio, у этого IDE самый лучший форматировщик исходников ( это единственный плюс этого IDE ), но у меня он не настроен, поэтому форматирование выполнено в ручную, и только там где я про него помнил.
По привычке к C# и PL/SQL каждое предложение заканчивается знаком ";".
Остальное читайте в комментариях к коду ( совсем очевидные вещи не имеют комментария, извините я не достаточно зануден ):
BEGIN
/*
Способ сортировки, используется в одном варианте для сортировки входных данных, для сортировки при нумерации строк и при генерации значения ключа
*/
  DECLARE @C_ORDER_BY NVARCHAR(MAX) = ' ORDER BY mm.meteo_station_id , mm.read_timestamp ' ;
/*
Шаблон для сохранения результирующей колонки
*/
  DECLARE @C_COLUMN_FORMULA_INSERT NVARCHAR(MAX) = 
N' INSERT INTO report_cell_instances 
  (id 
  ,instance_id 
  ,consumer_id 
  ,column_id 
  ,row_order 
  ,value)
';
/*
Шаблон для запроса на вычисление формулы, включает в себя все поля необходимые для вставки записи в таблицу report_cell_instances
*/
  DECLARE @C_COLUMN_FORMULA_SELECT NVARCHAR(MAX) = 
  N' SELECT 
      (NEXT VALUE FOR [dbo].[report_cell_instances_sequence] OVER( ' 
+ @C_ORDER_BY 
+ N' ) ) AS RecordId ,       
      @Instance_Id AS InstanceId ,
      @Consumer_Id AS ConsumerId , 
      @Column_Id AS ColumnId ,
      ROW_NUMBER() OVER( ' 
+ @C_ORDER_BY + N' ) AS Row_Order , ';
/*
Завершающая часть шаблона запроса на вычисление колонки
*/
  DECLARE @C_COLUMN_FORMULA_FROM NVARCHAR(MAX) = 
  N' FROM 
      meteo_measurements mm 
   WHERE 
      mm.meteo_station_id = @Station_Id 
      AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate   
    ' + @C_ORDER_BY
  ;
/*
Параметры динамического запроса на вычисление колонки
@Station_Id - станция источник данных
@FromDate - брать данные для вычисления от даты
@ThruDate - брать данные для вычисления по дату
@Column_Id - значение ключа для колонки которая вычисляется
@Instance_Id - значение ключа экземпляра отчёта
@Consumer_Id - значение ключа клиента
*/
  DECLARE @ColumnFormulaParams NVARCHAR(MAX); 
  SET @ColumnFormulaParams = 
    N' @Station_Id bigint , ' + 
    N' @FromDate datetimeoffset(7) , ' +
    N' @ThruDate datetimeoffset(7) , ' +
    N' @Column_Id INT , ' +
    N' @Instance_Id INT , ' +
    N' @Consumer_Id INT '
  ;
/*
Станция на основе данных которой будет сформирован отчёт, берётся случайная из имеющихся
*/
  DECLARE @Station BIGINT ;
  SELECT TOP 1 @Station = sr.id FROM meteo_stations_reference sr ORDER BY NEWID();
/*
Вывод отладочной информации в консоль, в скрипте все вызовы "PRINT" служат только этой цели
*/
  PRINT N' @Staton  = ' + CAST ( @Station AS NVARCHAR ) ;
/*
параметры отбора данных для формирования отчёта,
@From - от даты
@Thru  - по дату
*/
  DECLARE @From DATETIMEOFFSET(7) ;
  DECLARE @Thru DATETIMEOFFSET(7) ;
/*
берём случайные даты
*/
  SELECT TOP 1 @From = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID();
  SELECT TOP 1 @Thru = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID();
/*
даты "сортируем" в порядке возрастания
*/
  DECLARE @SwapVariable DATETIMEOFFSET(7) ;
  IF ( @From > @Thru )
    BEGIN
      SET @SwapVariable = @Thru;
      SET @Thru = @From ;
      SET @From = @SwapVariable ;
    END; 
  PRINT N' @From = ' + CAST ( @From  AS NVARCHAR )+ N' @Thru = ' + CAST ( @Thru  AS NVARCHAR );
/*
ключ записи экземпляра отчёта, берётся из соответствующей последовательности
*/
  DECLARE @Instance INT ;
  SET @Instance = NEXT VALUE FOR [dbo].[report_instances_sequence] ;
/*
Добавили запись экземпляра отчёта, с ключом состояния 1 - "формируется"
*/
  INSERT INTO report_instances 
    ( id , name , description , state_id )
  VALUES 
    (@Instance,CAST(@Instance AS NVARCHAR ),' DEBUG ', 1 )
  ;
/*
Клиент, выбирается из существующих случайным образом
*/
  DECLARE @ConsumerId INT ;
  SELECT TOP 1 @ConsumerId = cr.id FROM consumer_reference cr ORDER BY NEWID();
  PRINT N' @ConsumerId  = ' + CAST ( @ConsumerId AS NVARCHAR ) ;
/*
Перебор и индивидуальная обработка записей набора данных в T-SQL возможна только с использованием механизма курсоров ( буду рад оказаться не правым ).
Курсор может накладывать некоторые ограничения на источник данных, которые могут привести к блокировке источника для доступа другими процессами.
Что бы не разбираться с грамотным использованием курсоров в T-SQL я решил сохранить результат выборки в коллекцию.
T-SQL не имеет механизма коллекций ( буду рад оказаться не правым ), заменой ему служит механизм временных таблиц, либо табличных переменных.
Временная таблица может быть записана на диск и проиндексирована, временная таблица существует даже после завершения выполнения блока кода.
Табличная переменная существует только в оперативной памяти и только внутри блока кода и не может быть проиндексирована.
Мне было нужно последовательно пробежаться по всем записям - индексация не требуется. 
Использовать данные в других блоках кода не предполагалось.
Размер выборки - в пределах десятка записей, даже если их будет 1000, то это не создаст существенной нагрузки на оперативную память.
Поэтому я выбрал сохранение выборки в табличную переменную, но для истории сохранил вариант с временной таблицей.
*/  
--  CREATE TABLE #consumers_report_columns(
--  column_id int )
--
--  INSERT INTO #consumers_report_columns ( column_id )
--  SELECT 
--    rc.column_id 
--  FROM 
--    consumers_report_columns rc 
--  WHERE
--    rc.consumer_id = @ConsumerId
--  ;
/*
Табличная переменная для колонок отчёта
*/
  DECLARE @consumers_report_columns TABLE ( column_id INT )
  INSERT INTO @consumers_report_columns (column_id)
  SELECT 
    rc.column_id 
  FROM 
    consumers_report_columns rc 
  WHERE
    rc.consumer_id = @ConsumerId
  ;

  /* -=* CYCLE BEGIN *=- */

--  DECLARE consumers_report_columns_cursor CURSOR FOR 
--  SELECT 
--    rc.column_id 
--  FROM 
--    #consumers_report_columns rc
--  ;
/*
Курсор для перебора записей табличной переменной с колонками
*/
  DECLARE consumers_report_columns_cursor CURSOR FOR 
  SELECT 
    rc.column_id 
  FROM 
    @consumers_report_columns rc
  ;
/*
переменная для текущей вычисляемой колонки
*/  
  DECLARE @ColumnId INT ;

  OPEN consumers_report_columns_cursor ;
  
  FETCH NEXT FROM consumers_report_columns_cursor 
  INTO 
      @ColumnId
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
    PRINT N' @ColumnId  = ' + CAST ( @ColumnId AS NVARCHAR ) ;
/*
Код получения текста формулы конечно надо было выполнить единым запросом с получением колонок отчёта, но на тот момент я не был достаточно уверен в себе, поэтому процесс разбит на элементарные действия.
*/
/*
Получаем "ссылку" на формулу из записи текущей колонки
*/
    DECLARE @FormulaId INT;
    SELECT 
      @FormulaId = cl.formula_id
    FROM 
      columns cl
    WHERE
      cl.id = @ColumnId  ;
    PRINT N' @FormulaId  = ' + CAST ( @FormulaId AS NVARCHAR ) ;
/*
Получаем формулу для вычисления текущей колонки
*/    
    DECLARE @formula NVARCHAR(MAX);
    SELECT 
      @formula = fm.formula 
    FROM 
      formulas fm 
    WHERE
      fm.id = @FormulaId  ;
    PRINT N' @formula  = ' + @formula ;
/*
Текст динамического запроса для вычисления текущей колонки, здесь происходит только выборка данных без сохранения в таблицу
*/  
    DECLARE @column_formula_phrase NVARCHAR(MAX);  
    SET @column_formula_phrase = @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ;
    PRINT N' @column_formula_phrase  = ' + @column_formula_phrase ;
/*
лишнее присваивание, но мне в отладке так удобней
*/
    DECLARE @column_formula_sql NVARCHAR(MAX);
    SET @column_formula_sql = @column_formula_phrase ;
/*
Вызываем встроенную процедуру для выполнения динамического запроса вычисления колонки, вызов отладочный, в рабочей реализации не нужен, будут отображены результаты вычисления колонки
*/
    EXEC sp_executesql 
        @column_formula_sql
      , @ColumnFormulaParams
      , @Station_Id = @Station
      , @FromDate = @From
      , @ThruDate =  @Thru 
      , @Column_Id = @ColumnId
      , @Instance_Id = @Instance
      , @Consumer_Id = @ConsumerId
/*
формирование рабочего запроса для вычисления колонки и её сохранения в таблицу report_cell_instances
*/
    SET @column_formula_phrase = @C_COLUMN_FORMULA_INSERT + @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ;
    PRINT N' @column_formula_phrase  = ' + @column_formula_phrase ;
/*
Выполняем динамический запрос для вычисления и сохранения
*/
    SET @column_formula_sql = @column_formula_phrase ;                 
    EXEC sp_executesql 
        @column_formula_sql
      , @ColumnFormulaParams
      , @Station_Id = @Station
      , @FromDate = @From
      , @ThruDate =  @Thru 
      , @Column_Id = @ColumnId
      , @Instance_Id = @Instance
      , @Consumer_Id = @ConsumerId
  
    FETCH NEXT FROM consumers_report_columns_cursor 
    INTO 
        @ColumnId
  END 
  CLOSE consumers_report_columns_cursor;
/*
не уверен что "DEALLOCATE" действительно необходим
*/
  DEALLOCATE consumers_report_columns_cursor;  

  /* -=* CYCLE END *=- */
/*
освобождаем оперативную память
*/
--  DROP TABLE #consumers_report_columns
  DELETE @consumers_report_columns ;
/*
Вычисление колонок завершено
*/
/*
табличная переменная для разделов отчёта
*/
  DECLARE @consumers_report_regions TABLE ( region_id INT )
  INSERT INTO @consumers_report_regions (region_id)
  SELECT 
    rr.region_id 
  FROM 
    consumers_report_regions rr 
  WHERE
    rr.consumer_id = @ConsumerId
  ;
/*
Курсор по разделам отчёта записанным в табличную переменную 
*/
  DECLARE consumers_report_regions_cursor CURSOR FOR 
  SELECT 
    rr.region_id 
  FROM 
    @consumers_report_regions rr
  ;
/*
Часть шаблона для вычисления формулы раздела
*/
  DECLARE @C_REGION_FORMULA_SELECT NVARCHAR(MAX) = N' SELECT @Result = ' ;
/*
Завершающая часть шаблона для вычисления формулы раздела
*/
  DECLARE @C_REGION_FORMULA_FROM NVARCHAR(MAX) = 
N'
FROM 
  meteo_measurements mm 
WHERE 
  mm.meteo_station_id = @Station_Id 
  AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate   
';
/*
Параметры динамического запроса вычисления формулы раздела
@Station_Id - вычисление формулы на данных со станции
@FromDate - вычисление на данных от даты
@ThruDate - вычисление на данных до даты
@Result - результат вычисления формулы
*/
  DECLARE @C_REGION_FORMULA_PARAMS NVARCHAR(MAX) = 
    N' @Station_Id bigint , ' + 
    N' @FromDate datetimeoffset(7) , ' +
    N' @ThruDate datetimeoffset(7) , ' +
    N' @Result NVARCHAR(MAX) OUT '
  ;   
/*Переменная для ключа текущего вычисляемого раздела*/
  DECLARE @RegionId INT ;

  OPEN consumers_report_regions_cursor ;
  
  FETCH NEXT FROM consumers_report_regions_cursor 
  INTO 
      @RegionId
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
    PRINT N' @RegionId  = ' + CAST ( @RegionId AS NVARCHAR ) ;
/*
Получаем шаблон раздела
*/
    DECLARE @Pattern NVARCHAR(MAX) ;
    SELECT
      @Pattern = rg.pattern
    FROM
      regions rg
    WHERE
      rg.id = @RegionId
    ;
    PRINT N' @Pattern  = ' + @Pattern ;
/*
Табличная переменная. для формул и их отметок в шаблоне раздела
*/
    DECLARE @region_formulas_and_placeholders TABLE ( formula NVARCHAR(MAX) , placeholder NVARCHAR(MAX) )
/*
Записываем формулы раздела в табличную переменную
*/
    INSERT INTO @region_formulas_and_placeholders ( formula , placeholder )
    SELECT
       fr.formula
      , rf.placeholder
    --  , rg.pattern
    FROM
      regions rg
      JOIN region_formulas rf 
      ON rg.id = rf.region_id
      JOIN formulas fr 
      ON rf.formula_id = fr.id
    WHERE
      rg.id = @RegionId
    ;
/*
Курсор для перебора записей табличной переменной с формулами раздела 
*/
    DECLARE region_formulas_and_placeholders_cursor CURSOR FOR 
    SELECT 
        fp.formula
      , fp.placeholder 
    FROM 
      @region_formulas_and_placeholders fp
    ;
/* переменная для текущей формулы раздела */
    DECLARE @region_formula NVARCHAR(MAX);
/* 
переменная для текущей отметки в шаблоне раздела. места куда надо вставить результат вычисления формулы
*/
    DECLARE @placeholder NVARCHAR(MAX);

    OPEN region_formulas_and_placeholders_cursor ;
    
    FETCH NEXT FROM region_formulas_and_placeholders_cursor 
    INTO 
        @region_formula
      , @placeholder
    
    WHILE @@FETCH_STATUS = 0
    BEGIN

      PRINT N' @region_formula  = ' + @region_formula + N' @placeholder = ' + @placeholder;
/*
Переменная для запроса на вычисление текущей формулы раздела
*/    
      DECLARE @region_formula_phrase NVARCHAR(MAX) ;  
      SET @region_formula_phrase = @C_REGION_FORMULA_SELECT + @region_formula + @C_REGION_FORMULA_FROM ;
      PRINT N' @region_formula_phrase  = ' + @region_formula_phrase ;
      
      DECLARE @region_formula_sql NVARCHAR(MAX) ; 
      SET @region_formula_sql = @region_formula_phrase ;
/*
Переменная для результата вычисления формулы раздела и подстановки в шаблон раздела в соответствии с текущей меткой
*/
      DECLARE @Substitute NVARCHAR(MAX) ; 
/*
Выполняем динамический запрос вычисления поля раздела, результат получаем в @Substitute
*/                   
      EXEC sp_executesql 
          @region_formula_sql
        , @C_REGION_FORMULA_PARAMS
        , @Station_Id = @Station
        , @FromDate = @From
        , @ThruDate =  @Thru
        , @Result = @Substitute OUT
      ;
      PRINT N' @Substitute = ' + @Substitute ;

/* Выполняем замену метки на вычисленное значение */
      SET @Pattern = REPLACE ( @Pattern , @placeholder , @Substitute ) ; 

      FETCH NEXT FROM region_formulas_and_placeholders_cursor 
      INTO 
          @region_formula
        , @placeholder
    END 
    CLOSE region_formulas_and_placeholders_cursor;
    DEALLOCATE region_formulas_and_placeholders_cursor;

/* удаляем из табличной переменной обработанные данные */
    DELETE @region_formulas_and_placeholders ;

    PRINT N' FINISH @Pattern ' + @Pattern ;
/*
Сохраняем вычисленный раздел в таблицу report_region_instances
*/
    INSERT INTO report_region_instances
    ( instace_id ,consumer_id ,region_id ,value )
    VALUES( @Instance , @ConsumerId , @RegionId , @Pattern )    
    ;
  
    FETCH NEXT FROM consumers_report_regions_cursor 
    INTO 
        @RegionId
  END

  CLOSE consumers_report_regions_cursor;
  DEALLOCATE consumers_report_regions_cursor;
/* очищаем табличную переменную - освобождаем оперативную память */
  DELETE @consumers_report_regions ; 
/*
Все колонки вычислены и сохранены. все разделы вычислены и сохранены - мы молодцы :)
*/
END;


Тестирование решения

Тестирование было поверхностным, поведение скрипта при ошибках в данных не проверялось.
Тестовый набор данных

Для генерации тестового набора я использовал генератор dbForge Studio.
В таблице meteo_measurements, тип для колонки read_timestamp пришлось поменять с "timestamp" на "datetimeoffset(7)", потому что значение с типом "timestamp" может создать только сервер, в ручную запрещено, а генерация набора данных — в dbForge Studio выполняется в ручном режиме — скриптом с конкретно прописанными операторами «INSERT».
Кроме того значение для колонки «meteo_station_id» пришлось подставлять руками, в смысле допиливать сгенерированный скрипт:
заменить «measurements(read_timestamp,» на «measurements(meteo_station_id,read_timestamp,»
заменить "wind_speed) VALUES ('" на "wind_speed) VALUES ((SELECT TOP 1 id FROM meteo_stations_reference ORDER BY NEWID()),'"

Тестовый набор пришлось ограничить на 15 000 записей, при генерации скрипта более чем на 16 000 сбивался перенос строк.
Таблицы с настройками

Кроме того для тестирования были добавлены записи в другие таблицы. Пара уникальных индексов была изменена, и я уже не помню которые это индексы, поэтому я просто повторю все основные таблицы.
DDL таблиц и DML с вставкой данных есть на GitHub.
DDL таблиц и DML с вставкой данных
Значения вычисляемых формул «сохраняются» как NVARCHAR(MAX), но шаблоны сохранения результатов не предусматривают преобразования типа — это на совести пользователей и их квалификации.

Упущенные моменты

В решении нет обработки таблицы formula_parameters, не выполняется подстановка произвольного значения в формулу.

Ссылки

Движок для построения отчётов на SQL. Идея
Как заполнить базу данных MS SQL разнородными случайными данными или 17 часов ожидания
dbForge Studio for SQL Server
How to request a random row in SQL?
The Curse and Blessings of Dynamic SQL
Temporary Tables
 ERP, SQL, t-sql