Движок для побудови звітів SQL. Чернетка рішення

Введення

У першій статті ( Движок для побудови звітів 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 скрипта, в робочій реалізації це має бути збережена процедура, склад вхідних параметрів під питанням — залежить від потреб замовника. У мене в скрипті це:
  1. клієнт — вибирається випадковим чином з таблиці consumer_reference
  2. номер станції — вибирається випадковим чином з таблиці meteo_stations_reference,
  3. період дат — вибираються дві випадкові дати з таблиці meteo_measurements для обраної станції
Інші речі, які повинні бути як мінімум константами зроблені в стилі «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» довелося підставляти руками, в сенсі допілівать згенерований скрипт:
  1. замінити «measurements(read_timestamp,» на «measurements(meteo_station_id,read_timestamp,»
  2. замінити "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 зі вставкою даних
CREATE TABLE Linegro.dbo.meteo_stations_reference (
id BIGINT NOT NULL
,name NVARCHAR(4000) NOT NULL
,description NVARCHAR(MAX) NULL
,CONSTRAINT PK_meteo_stations_reference PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_meteo_stations_reference_name UNIQUE (name)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (1, 'ТАГАНАЙ', 'Метеостанція "Таганай"')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (2, 'свердловськ', 'Метеостанція на Метео гірці')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (3, 'Центральна', 'Москва Центральна')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (4, 'Владик', 'Морський порт Владивосток')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (5, 'Якутія', 'Метеостанція республіки Саха - Якутія')
GO
CREATE TABLE Linegro.dbo.meteo_measurements (
meteo_station_id BIGINT NOT NULL
,read_timestamp DATETIMEOFFSET NOT NULL
,temperature DECIMAL(4, 1) NULL
,pressure INT NULL
,wind_direction INT NULL
,wind_speed INT NULL
,CONSTRAINT PK_meteo_measurements PRIMARY KEY CLUSTERED (meteo_station_id, read_timestamp)
,CONSTRAINT FK_meteo_measurements_meteo_stations_reference_id FOREIGN KEY (meteo_station_id) REFERENCES dbo.meteo_stations_reference (id)
) ON [PRIMARY]
GO
CREATE TABLE Linegro.dbo.consumer_reference (
id INT NOT NULL
,name NVARCHAR(4000) NOT NULL
,description NVARCHAR(MAX) NULL
,CONSTRAINT PK_consumer_reference PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_consumer_reference_name UNIQUE (name)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (1, 'ІП Наибенько', 'наиби перехожого на тебе схожого')
INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (2, 'МО РФ', 'Міністерство оборони Російської Федерації')
INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (3, 'Рота 23', 'Артилерійський розрахунок рота 23')
INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (4, N'426 Гірський 2016', 'група номер 426 ( 2016 ) Гірничого університету міста Екатеринубурга ')
GO
CREATE TABLE Linegro.dbo.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]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.formulas(id code, formula) VALUES (1, 'temperature', 'COALESCE(temperature ,0) AS temperature')
INSERT Linegro.dbo.formulas(id code, formula) VALUES (2, 'pressure', 'COALESCE(pressure,0) AS pressure')
INSERT Linegro.dbo.formulas(id code, formula) VALUES (3, 'wind_direction', 'COALESCE(wind_direction,0) AS wind_direction')
INSERT Linegro.dbo.formulas(id code, formula) VALUES (4, 'wind_speed', 'wind_speed AS wind_speed')
INSERT Linegro.dbo.formulas(id code, formula) VALUES (5, 'temperature_max', 'MAX(COALESCE(temperature,0)) ')
INSERT Linegro.dbo.formulas(id code, formula) VALUES (6, 'temperature_min', 'MIN(COALESCE(temperature,0)) ')
INSERT Linegro.dbo.formulas(id code, formula) VALUES (7, 'temperature_avg', 'AVG(COALESCE(temperature,0)) ')
INSERT Linegro.dbo.formulas(id code, formula) VALUES (8, 'speed_m_s', 'CAST ( COALESCE(wind_speed ,0) AS NVARCHAR ) + N" ( $M_S$ )" AS speed_m_s')
GO
CREATE TABLE Linegro.dbo.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]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (1, 1, 'температура', 'температура повітря ( градусів Цельсія ) ')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (2, 2, -тиск', 'атмосферний тиск ( мм рт ст )')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (3, 3, 'вітер', 'напрямок вітру')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (4, 4, 'швидкість', 'швидкість вітру ( м/с )')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (5, 8, 'швидкість', 'швидкість вітру')
GO
CREATE TABLE Linegro.dbo.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]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (1, 'max temp = $MAX_TEMP$ , min temp = $MIN_TEMP$ , average temp = $AVG_TEMP$', 'temp_statistics', 'статистика по температурі')
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (2, 'група номер 426 ( 2016 ) Гірничого університету міста Екатеринубурга ', '426_2016_title', 'шапка')
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (3, N' ', 'empty', 'порожнє поле')
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (4, 'Міністерство оборони Російської Федерації', 'МО РФ', NULL)
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (5, 'ІП Наибенько', 'ІП Наибенько', NULL)
GO
CREATE TABLE Linegro.dbo.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)
,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]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 1, 1)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 2, 1)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (4, 2, 3)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 2, 5)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 2, 14)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 3, 10)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (5, 3, 20)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 4, 11)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 4, 22)
GO
CREATE TABLE Linegro.dbo.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]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (1, 5, 1, 2)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 1, -1, 4)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 4, 1, 1)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 1, 50, 5)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 3, -100, 3)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 4, 5, 1)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (4, 2, 10, 3)
GO
CREATE TABLE Linegro.dbo.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 UNIQUE (region_id, formula_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]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (1, 5, 1, N'$MAX_TEMP$')
INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (2, 6, 1, N'$MIN_TEMP$')
INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (3, 7, 1, N'$AVG_TEMP$')
GO
CREATE TABLE Linegro.dbo.report_instace_states_reference (
id INT NOT NULL
code NCHAR(50) NOT NULL
,description NVARCHAR(MAX) NULL
,CONSTRAINT PK_report_instace_states_reference PRIMARY KEY CLUSTERED (id)
,CONSTRAINT UK_report_instace_states_reference_code UNIQUE (code)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.report_instace_states_reference(id code, description) VALUES (1, 'формується', 'формується')
INSERT Linegro.dbo.report_instace_states_reference(id code, description) VALUES (2, 'готовий', 'готовий')
INSERT Linegro.dbo.report_instace_states_reference(id code, description) VALUES (3, 'збережено', 'збережено')
INSERT Linegro.dbo.report_instace_states_reference(id code, description) VALUES (4, 'видалено', 'видалений')
GO


Значення в обчислюваних формул «зберігаються» NVARCHAR(MAX), але шаблони збереження результатів не передбачають перетворення типу — це на совісті користувачів і їх кваліфікації.

Втрачені моменти

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

Висновок

Власне тепер все у ваших руках, з цієї болванки можна змайструвати все що завгодно і як завгодно :)
Якщо я щось прогавила, то можу доповнити в коментарях.
Дякую за увагу.

Посилання

  1. Движок для побудови звітів SQL. Ідея
  2. Исходники — GitHub
  3. Як заповнити базу даних MS SQL різнорідними випадковими даними або 17 годин очікування
  4. dbForge Studio for SQL Server
  5. How to request a random row in SQL?
  6. The Curse and Blessings of Dynamic SQL
  7. Temporary Tables обговорення в коментах з'ясувалася лукавство в статті за посиланням, дійсне використання оперативної і постійної пам'яті табличними змінними розкрито по посиланню What's the difference between a temp table table and variable in SQL Server?
  8. SQL Server 2014 In Memory OLTP: Memory-Optimized Table Types and Table Variables


Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.