Движок для построения отчётов на SQL. Идея source text

<h5>Введение</h5>
Статья написана с точки зрения программиста из техподдержки ERP системы.

Выполняя очередное тестовое задание, по очередной вакансии, мне в голову пришла очередная гениальная идея. 
Почему бы не отдать пользователю редактирование формул для вычисления колонок отчёта ? Получиться тот же самый Excel к которому все привыкли, только синтаксис - SQL. 

И конечно за 30 лет развития ERP систем это уже конечно где то было реализовано. Но их алгоритмы это их тайны, а мне не жалко. я поделюсь идеей. 
<cut />
<h5>Тестовое задание</h5>
Есть реляционная база с данными, которые поступают ежедневно. Каждый месяц на основе этих данных строятся отчёты. Необходимо продумать и предложить архитектуру системы построения периодических отчётов. 

Требования к системе:

1) Отчёты можно просмотреть в web интерфейсе, скачать в excel, забрать с веб сервиса в виде JSON (для передачи во внешнюю систему).
2) Отчёты строятся для различных клиентов. Каждый клиент хочет отчёт отличный от типового: своя шапка и подвал, отображаемые столбцы, порядок столбцов, дополнительные вычисляемые столбцы.
3) Возможность сохранять отчёты. Исходные данные изменяются, а сохранённый отчёт остаётся неизменным.
4) Редактирование отчёта(исходных данных) на лету в web интерфейсе.

Результатом должна быть ER (Entity-Relationship) диаграмма. Текстовое описание решения.

<h5>Идея</h5>
Что такое обычный отчёт ? это распечатанная таблица исходных данных к которой добавлено несколько колонок с вычисленными значениями, либо подведён итог по исходным данным.

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

Если функции написаны на SQL, то мы можем их подставить в SELECT, который затем следует выполнить как динамический запрос. 
Такая возможность есть в T-SQL и PL/SQL.

<h5>Эскизный проект реализации</h5>
<h6>Основные сущности</h6>
<ul>
	<li><b>Формула</b></li>
	<li><b>Колонка</b></li>
	<li><b>Раздел</b> - шапка или подвал отчёта, или что угодно ещё не являющееся колонкой, область печатной формы которая существует в единственном экземпляре. </li>
	<li><b>Экземпляр</b> - сформированный отчёт. </li>
	<li><b>Клиент</b> - по тестовому заданию которое я выполнял, у каждого клиента свой  набор колонок, в общем виде <b>consumer_id</b> - это отдельный отчёт, который строиться на своих данных по своим правилам. </li>
</ul>
<h6>Формулы</h6>
Формулы для вычисления полей будут храниться в таблице <b>formulas</b>, DDL:

<source lang="sql">
CREATE TABLE formulas (
  id INT NOT NULL
,code NCHAR(50) NOT NULL
,formula NVARCHAR(MAX) NOT NULL
,CONSTRAINT PK_formulas PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_formulas_code UNIQUE (code)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</source>
У каждой формулы своё уникальное кодовое обозначение - поле <b>code</b>; <b>formula</b> - SQL выражение для вычисления значения.

Текст формулы будет подставлен во фразу “SELECT”, которая будет выполнена динамически, поэтому в формуле можно использовать параметры ( ":PARAM1") и при динамическом выполнении SQL кода надо значения этих параметров подставить. Аналогичным образом можно формировать фразу “FROM”, “WHERE” и другие части SQL предложения.

При вычислении колонки по формуле могут потребоваться дополнительные параметры которых нет в исходных данных, для подстановки таких параметров в формуле следует указать “посадочное место” (placeholder) - текст который надо заменить на значение параметра.

Для этого к записи таблицы <b>formula</b> можно "прицепить" запись из таблицы <b>formula_parameters</b>, DDL:

<source lang="sql">
CREATE TABLE formula_parameters (
  id INT NOT NULL
,code NCHAR(50) NOT NULL
,formula_id INT NOT NULL
,placeholder NVARCHAR(4000) NOT NULL
,CONSTRAINT PK_formula_parameters PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_formula_parameters_code UNIQUE (formula_id, code)
,CONSTRAINT UK_formula_parameters_placeholder UNIQUE (formula_id, placeholder)
,CONSTRAINT FK_formula_parameters_formulas_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id)
) ON [PRIMARY]
</source>
<b>code</b> - уникальный код параметра;
<b>placeholder</b> - текст для замены на значение параметра, в формуле значение должно подставляться через соответствующий CAST или другой способ преобразования типа из строкового в требуемый (учитывая специфику СУБД), в формулу подставляется текст поэтому CAST должен быть из текста в требуемый тип, это надо учитывать при форматировании значения параметра.

<h6>Колонки и Разделы</h6>
Формулы можно подставлять в колонки и в раздела отчёта. Значение колонки вычисляется по одной формуле. Разделы отчёта это шапка и подвал. Можно добавить и другие типы разделов. Раздел может содержать несколько вычисляемых значений, поэтому одному разделу может быть сопоставлено несколько формул.

Формулы колонок хранятся в таблице <b>columns</b>, DDL:
 
<source lang="sql">
CREATE TABLE columns (
  id INT NOT NULL
,formula_id INT NOT NULL
,name NVARCHAR(MAX) NOT NULL
,description NVARCHAR(MAX) NULL
,CONSTRAINT PK_columns PRIMARY KEY CLUSTERED (id)
,CONSTRAINT FK_columns_formulas_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</source>
<b>name</b> - каждая колонка имеет своё имя, разные колонки могут иметь одинаковые имена.
<b>description</b> - описание, может быть пустым. 

Формулы разделов - <b>region_formulas</b>, DDL:

<source lang="sql">
CREATE TABLE region_formulas (
  id INT NOT NULL
,formula_id INT NOT NULL
,region_id INT NOT NULL
,placeholder NVARCHAR(4000) NOT NULL
,CONSTRAINT PK_region_formulas PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_region_formulas_formula_id_region_id UNIQUE (formula_id, region_id)
,CONSTRAINT FK_region_formulas_formulas_formula_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id)
,CONSTRAINT FK_region_formulas_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id)
) ON [PRIMARY]
</source>
<b>placeholder</b> - текст который надо будет заменить на вычисленное по формуле значение.

В одном разделе ( <b>region_id</b> ) одна формула ( <b>formula_id</b> ) может вычисляться только один раз, для всех мест где надо вставить её результат должен быть одинаковый <b>placeholder</b>.

Можно конечно дать волю пользователям, это ограничение - это оптимизация "по автопилоту".

Вычисленные значения параметров шаблона раздела надо вставить в шаблон раздела, шаблоны разделов это таблица <b>regions</b>:

<source lang="sql">
CREATE TABLE regions (
  id INT NOT NULL
,pattern NVARCHAR(MAX) NOT NULL
,name NVARCHAR(4000) NOT NULL
,description NVARCHAR(MAX) NULL
,CONSTRAINT PK_regions PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_regions_name UNIQUE (name)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</source>
<b>pattern</b> - собственно шаблон раздела
<b>name</b> - имя раздела
<b>description</b> - описание, может быть пустым.

При вычислении конкретного текста раздела надо заменить в тексте шаблона <b>pattern</b> ( таблица regions ) подстроки с текстом <b>placeholder</b> ( таблица region_formulas ) на вычисленные значения. 

Расположение разделов в экземляре отчёта задаётся таблицей <b>consumers_report_regions</b>, DDL:

<source lang="sql">
CREATE TABLE consumers_report_regions (
  consumer_id INT NOT NULL
,region_id INT NOT NULL
,region_order INT NOT NULL
,type_id INT NULL
,CONSTRAINT PK_consumers_report_base PRIMARY KEY CLUSTERED (consumer_id, region_id)
,CONSTRAINT UK_consumers_report_regions_region_order UNIQUE (consumer_id, region_order)
,CONSTRAINT FK_consumers_report_regions_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id)
,CONSTRAINT FK_consumers_report_regions_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id)
,CONSTRAINT FK_consumers_report_regions_report_region_types_id FOREIGN KEY (type_id) REFERENCES dbo.report_region_types (id)
) ON [PRIMARY]
</source>
<b>type_id</b> - ссылка на справочник типов разделов ( шапка / подвал / другое );
<b>region_order</b> - порядок расположения раздела в отчёте, для одного клиента значение порядка не может повторяться. 

Порядок колонок для каждого клиента уникален и хранится в таблице <b>consumers_report_columns</b>, DDL:

<source lang="sql">
CREATE TABLE consumers_report_columns (
  column_id INT NOT NULL
,consumer_id INT NOT NULL
,column_order INT NOT NULL
,CONSTRAINT PK_consumers_report_columns PRIMARY KEY CLUSTERED (consumer_id, column_id)
,CONSTRAINT UK_consumers_report_columns_column_order UNIQUE (consumer_id, column_order, column_id)
,CONSTRAINT FK_consumers_report_columns_columns_id FOREIGN KEY (column_id) REFERENCES dbo.columns (id)
,CONSTRAINT FK_consumers_report_columns_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id)
) ON [PRIMARY]
</source>
<b>column_order</b> - порядковый номер колонки, у одного клиента каждая колонка имеет собственное значение порядка;

Таким образом с помощью таблиц <b>consumers_report_columns</b> и <b>consumers_report_regions</b> задаётся структура отчета, индивидуально для каждого клиента.

Таблицы <b>columns</b> и <b>region_formulas</b> задают правила вычисления для колонок и разделов. Таблица <b>formulas</b> задаёт правила вычисления значений.

<h6>Экземпляр</h6>
Вычисленный отчёт (экземпляр) сохраняется в структуре таблиц report_instances, report_region_instances, report_cell_instances. Таблица <b>report_instances</b> хранит информацию о сформированных экземплярах отчётов, DDL:

<source lang="sql">
CREATE TABLE report_instances (
  id INT NOT NULL
,name NVARCHAR(4000) NOT NULL
,description NVARCHAR(MAX) NULL
,state_id INT NOT NULL
,CONSTRAINT PK_report_instances PRIMARY KEY NONCLUSTERED (id)
,CONSTRAINT UK_report_instances_name UNIQUE (name)
,CONSTRAINT FK_report_instances_report_instace_states_reference_id FOREIGN KEY (state_id) REFERENCES dbo.report_instace_states_reference (id)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</source>
<b>name</b> - имя экземпляра, уникальное в таблице;
<b>description</b> - описание, может быть пустым;
<b>state_id</b> - ссылка на справочник статусов экземпляра ("формируется", "готов", "сохранён", "удалён").

Сформированные разделы отчёта - <b>report_region_instances</b>, DDL:
 
<source lang="sql">
CREATE TABLE report_region_instances (
  instace_id INT NOT NULL
,consumer_id INT NOT NULL
,region_id INT NOT NULL
,value NVARCHAR(MAX) NULL
,CONSTRAINT PK_report_region_instances PRIMARY KEY CLUSTERED (instace_id, consumer_id, region_id)
,CONSTRAINT FK_report_region_instances_consumers_report_regions FOREIGN KEY (consumer_id, region_id) REFERENCES dbo.consumers_report_regions (consumer_id, region_id)
,CONSTRAINT FK_report_region_instances_report_instances_instace_id FOREIGN KEY (instace_id) REFERENCES dbo.report_instances (id)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</source>
<b>value</b> - текст раздела, текст сохраняется уже " склеенным" , вычисленные по формулам отдельные значения не сохраняются.

Сформированные значения ячеек табличной части - <b>report_cell_instances</b>, DDL:

<source lang="sql">
CREATE TABLE report_cell_instances (
  id INT NOT NULL
,instance_id INT NOT NULL
,consumer_id INT NOT NULL
,column_id INT NOT NULL
,row_order INT NOT NULL
,value NVARCHAR(MAX) NULL
,CONSTRAINT PK_report_cell_instances PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_report_cell_instances_column_id_row_order UNIQUE (instance_id, consumer_id, column_id, row_order)
,CONSTRAINT FK_report_cell_instances FOREIGN KEY (consumer_id, column_id) REFERENCES dbo.consumers_report_columns (consumer_id, column_id)
,CONSTRAINT FK_report_cell_instances_report_instances_id FOREIGN KEY (instance_id) REFERENCES dbo.report_instances (id)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</source>
<b>row_order</b> - порядок строки, номер строки табличной части;
<b>value</b> - текст ячейки.

У одного экземпляра отчёта одного клиента одна вычисленная колонка не может иметь несколько одинаковых номеров строки, то есть уникальна координата ячейки - пара (колонка, строка).

В соответствии со структурой отчёта клиента (consumers_report_columns, consumers_report_regions) и вычисленными значениями (report_region_instances, report_cell_instances) можно “склеить” конкретный экземпляр отчёта. 

Таким образом реализован механизм уникальности формул и состава колонок для каждого клиента, реализован механизм сохранения вычисленного отчёта. 

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

Буду рад конструктивной критике или ссылкам на классические реализации такой логики.


<h4>Ссылки</h4>
<ul>
	<li><a href="https://habrahabr.ru/post/278921/">Движок для построения отчётов на SQL. Черновик решения</a></li>
	<li><a href="https://github.com/SbWereWolf/SqlReportConstructor">Исходники на GitHub</a></li>
</ul>