SQL engine for generating reports. Solution Draft

Introduction

In the first article ( Engine for building reports on SQL. Idea ) I shared the idea. Now I will share the solution (draft). This draft is my first experience of “serious” work with T-SQL , so you should not take it as a sample of “good” code.
The most important thing in this draft is the mechanism for substituting formulas into a dynamic query. The second most important is the mechanism for storing the results of calculations.

When I got to work, I expected great difficulties, but in fact everything turned out to be very simple. A lot of scribbling and just a couple of moments when I had to stop and think. The first moment is the generation of the line number in the query output, the second is the generation of the value for the key field.
Eyes to be afraid - hands do!
I will begin at once from the most basic and interesting, for those to whom this is not enough - a thorough analysis of logic will be below. Let's get started

Formula Calculation

Difference between Columns and Partitions

There is a significant difference between the calculation of the formula for filling the column and the calculation of the formula for filling the fields of the section (header or basement). This difference is that the column is calculated for each row separately, and the section is calculated once for all rows at once.
Formulas for caps are always aggregate functions and the result of calculating the formula must be “pasted” into the “footprint” of the template.
The calculated value for the column must be "zabindit" (linked) with the row for which this column was calculated.
Therefore, different templates have been developed for the calculation of the column and header.
')

Section Template

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

Everything is linear:

calculated the formula;
the result is framed in the template;
that turned out saved in the appropriate table ( report_region_instances );
end - cap formed;

Column Template

With columns more complicated. If the result of calculating the header is one value, then the result of calculating the column is the set of values, that is, it is a table consisting of one column and a certain number of rows.
In order to display all the rows of columns during the output of the report, it was necessary to synchronize with each other, when saving the result (to the table report_cell_instances ), each row should be numbered.
To do this, it is necessary to sort the lines in some uniform way - sort. Add the phrase " ORDER BY " to the query for column calculation, add " ROW_NUMBER () OVER (ORDER BY) " to " SELECT ".
Request Template:

 SET @sql_text = N' SELECT ROW_NUMBER() OVER( ORDER BY key_column) ,' + @formula + N' FROM table ORDER BY key_column'

Not difficult. The next interesting point is the preservation of calculations - the results of our work.

Save the result.

With the preservation of the section (headings or basement), there are no difficulties - the banal " INSERT " which is necessary where necessary (to the table report_region_instances ).
With the preservation of the computed column is also not that difficult, it is necessary to add our dynamic query with the operator " INSERT ".
The problem is only in the generation of the value of the unique key field. There is an excellent solution to this problem using an auto-increment column ( IDENTITY property), but I like to have maximum control over what my program does, so I used another tool - " SEQUENCE " - and generate each number manually.
Request Template:

 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

Thorough analysis of implementation

The implementation is in the form of a T-SQL script, in a working implementation it must be a stored procedure, the composition of the input parameters is questionable — it depends on the needs of the customer. In my script it is:

customer - randomly selected from the consumer_reference table
station number - randomly selected from the meteo_stations_reference table,
date period - two random dates are selected from the meteo_measurements table for the selected station

Other things that should be at least constants made in the style of "hard code" aka "magic number", consider this a cost of "draft".
I wrote the code in dbForge Studio , this IDE has the best source formatter (this is the only plus of this IDE), but I don’t have it configured, so the formatting was done manually, and only where I remembered about it.
Out of habit for C # and PL / SQ L, each sentence ends with a ";".
Read the rest in the comments to the code (quite obvious things have no comment, I'm sorry I'm not quite boring):

script with detailed comments

 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;

Testing the solution

Testing was superficial, the behavior of the script in case of errors in the data was not checked.

Test data set

To generate a test suite, I used the dbForge Studio generator.
In the meteo_measurements table, the type for the read_timestamp column had to be changed from " timestamp " to " datetimeoffset (7)", because the value with the type " timestamp " can only be created by the server, disabled manually, and the dataset generation is done manually in mode - a script with specifically prescribed operators "INSERT".
In addition, the value for the “meteo_station_id” column had to be substituted by hands, in the sense of finishing the generated script:

replace “measurements (read_timestamp,” with “measurements (meteo_station_id, read_timestamp,”
replace " wind_speed) VALUES (' " with " wind_speed) VALUES ((SELECT TOP 1 id FROM meteo_stations_reference ORDER BY NEWID ()),' "

The test suite had to be limited to 15,000 entries, while generating a script for more than 16,000, the line breaks were lost.

Tables with settings

In addition, for testing, records were added to other tables. A pair of unique indexes was changed, and I don’t remember which indexes it is, so I’ll just repeat all the main tables.
DDL table creation scripts and DML data insertion scripts are on GitHub .

DDL tables and DML with data insertion

 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, N'', N' ""') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (2, N'', N'   ') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (3, N'', N' ') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (4, N'', N'  ') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (5, N'', N'   - ') 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, N' ', N'    ') INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (2, N' ', N'   ') INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (3, N' 23', N'   23') INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (4, N'426  2016', N'  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, N'temperature', N'COALESCE(temperature ,0) AS temperature') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (2, N'pressure', N'COALESCE(pressure,0) AS pressure') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (3, N'wind_direction', N'COALESCE(wind_direction,0) AS wind_direction') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (4, N'wind_speed', N'wind_speed AS wind_speed') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (5, N'temperature_max', N'MAX(COALESCE(temperature,0)) ') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (6, N'temperature_min', N'MIN(COALESCE(temperature,0)) ') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (7, N'temperature_avg', N'AVG(COALESCE(temperature,0)) ') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (8, N'speed_m_s', N'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, N'', N'  (   ) ') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (2, 2, N'', N'  (    )') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (3, 3, N'', N' ') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (4, 4, N'', N'  ( / )') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (5, 8, N'', N' ') 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, N'max temp = $MAX_TEMP$ , min temp = $MIN_TEMP$ , average temp = $AVG_TEMP$', N'temp_statistics', N'  ') INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (2, N'  426 ( 2016 )     ', N'426_2016_title', N'') INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (3, N' ', N'empty', N' ') INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (4, N'   ', N' ', NULL) INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (5, N' ', N' ', 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, N'', N'') INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (2, N'', N'') INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (3, N'', N'') INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (4, N'', N'') GO

«» NVARCHAR (MAX), — .

formula_parameters , .

Conclusion

, :)
, .
Thanks for attention.

Links

Source: https://habr.com/ru/post/278921/

All Articles