はじめに
この記事は、ERPシステムのテクニカルサポートのプログラマーの観点から書かれています。
次の空席によると、次のテストタスクを実行して、別の素晴らしいアイデアを思いつきました。
レポート列を計算するための数式を編集するユーザーを提供してみませんか？ 誰もが慣れているのと同じExcelを取得します。構文のみがSQLです。
そしてもちろん、ERPシステムの開発の30年にわたって、これは確かにどこかで実装されました。 しかし、彼らのアルゴリズムは彼らの秘密ですが、私は気にしません。 アイデアを共有します。
テストタスク
毎日やってくるデータを備えたリレーショナルデータベースがあります。 これらのデータに基づいて毎月レポートが作成されます。 定期的なレポートを構築するためのシステムのアーキテクチャを考え、提案する必要があります。
システム要件：
1）レポートは、Webインターフェイスで表示でき、Excelにダウンロードして、JSONの形式でWebサービスから取得できます（外部システムへの転送用）。
2）レポートはさまざまな顧客向けに作成されています。 各クライアントは、独自のヘッダーとフッター、表示される列、列の順序、追加の計算列など、標準とは異なるレポートを必要とします。
3）レポートを保存する機能。 初期データが変更され、保存されたレポートは変更されません。
4）Webインターフェイスでその場でレポート（ソースデータ）を編集します。
結果はER（Entity-Relationship）ダイアグラムになります。 ソリューションの説明文。
アイデア
通常のレポートとは何ですか？ これは、計算された値を持ついくつかの列が追加された、またはソースデータの概要が要約された印刷されたソースデータテーブルです。
ユーザーはプログラマに何を望んでいますか？ ソースデータの選択のロジックに関係する場合、計算のロジックを変更します。レポートを作成するプレゼンテーションまたはストアドプロシージャのロジックのいずれかを変更する必要があるため、プログラマのみが対処できます。ユーザーの慈悲で。 何が提供されていますか？
ユーザーにSQL式の作成方法を教えるために、ユーザーはExcel式を作成するのに十分賢く、必要に応じてSQLも習得します。もちろん、SQLの標準セットはドメイン固有の関数で拡張する必要があります。
関数がSQLで記述されている場合、それらをSELECTに置き換えることができます。これを動的クエリとして実行する必要があります。
この機能は、T-SQLおよびPL / SQLで使用できます。
概略設計プロジェクト
キーエンティティ
- フォーミュラ
- コラム
- セクションは、レポートのヘッダーまたはフッター、または列以外の何か、単一のコピーに存在する印刷フォームの領域です。
- インスタンス -生成されたレポート。
- クライアント -私が実行したテストタスクによると、各クライアントには独自の列セットがあり、一般的な形式のconsumer_idは、独自のルールに従ってデータに基づいた別個のレポートです。
フォーミュラ
フィールドを計算するための式は、 式テーブルDDLに保存されます。
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]
各式には、固有のコード指定（ コードフィールド）があります。 formula-値を計算するためのSQL式。
式のテキストはフレーズ「SELECT」に置き換えられ、動的に実行されるため、式でパラメーター（ "：PARAM1"）を使用できます。SQLコードを動的に実行する場合、これらのパラメーターの値を置き換える必要があります。 同様に、句「FROM」、「WHERE」、およびSQLステートメントの他の部分を定式化できます。
数式で列を計算する場合、ソースデータにない追加のパラメーターが必要になる場合があります。数式でそのようなパラメーターを置き換えるには、「プレースホルダー」を指定する必要があります。
これを行うには、 formula_parameters 、DDLテーブルのレコードを数式テーブルレコードに「添付」できます。
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]
code-一意のパラメーターコード。
placeholder-パラメーター値で置き換えるテキスト。式では、適切なCASTまたは文字列から必要なものに型を変換する別の方法（DBMSの詳細を考慮）で値を置換する必要があります。パラメータ値のフォーマット。
列とセクション
数式は、列とレポートセクションで置き換えることができます。 列の値は、1つの式を使用して計算されます。 レポートのセクションはヘッダーと地下室です。 他のタイプのパーティションを追加できます。 セクションには複数の計算値を含めることができるため、複数の数式を1つのセクションにマッピングできます。
列式は、 列の DDLテーブルに保存されます。
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]
名前 -各列には独自の名前があり、異なる列に同じ名前を付けることができます。
description-説明、空の場合があります。
パーティション式-region_formulas 、DDL：
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]
プレースホルダー -数式で計算された値に置き換える必要があるテキスト。
1つのセクション（ region_id ）では、1つの式（ formula_id ）を一度だけ計算できます。結果を挿入する必要があるすべての場所に対して、同じplaceholderが必要です。
もちろん、ユーザーに自由な手綱を与えることができます。この制限は「自動操縦による」最適化です。
セクションテンプレートパラメータの計算値をセクションテンプレートに挿入する必要があります。セクションテンプレートはリージョンテーブルです。
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]
パターン -実際のパーティションテンプレート
name-セクション名
description-説明、空の場合があります。
セクションの特定のテキストを計算するときは、 パターンのテキスト（テーブル領域）のサブストリングをテキストプレースホルダー （テーブルregion_formulas）に置き換え、計算値に置き換える必要があります。
レポートインスタンス内のセクションの場所は、 consumer_report_regions 、DDLテーブルによって設定されます。
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]
type_id-セクションタイプのディレクトリへのリンク（ヘッダー/フッター/その他）;
region_order-レポート内のセクションの順序; 1つのクライアントでは、順序値を繰り返すことはできません。
各顧客の列の順序は一意であり、テーブルconsumer_report_columns 、DDLに格納されます。
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]
column_order-列のシリアル番号。1つのクライアントについて、各列には独自の順序値があります。
したがって、 consumers_report_columnsおよびconsumer_report_regionsテーブルを使用して、レポート構造は各クライアントに個別に設定されます。
列とregion_formulasテーブルは、列とセクションの計算ルールを指定します。 数式テーブルは、値を計算するためのルールを定義します。
インスタンス
計算されたレポート（インスタンス）は、テーブル構造report_instances、report_region_instances、report_cell_instancesに保存されます。 report_instancesテーブルには、生成されたレポートインスタンス、DDLに関する情報が格納されます。
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]
name-テーブル内で一意のインスタンス名。
description-説明 。空の場合があります。
state_id-インスタンスステータスのディレクトリへのリンク（「形成済み」、「準備完了」、「保存済み」、「削除済み」）。
レポートの形成されたセクション-report_region_instances 、DDL：
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]
値 -セクションのテキスト。テキストは既に「接着」されて保存され、式によって計算された個々の値は保存されません。
テーブルパーツのセルの生成値は、 report_cell_instances 、DDLです。
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]
row_order-行順序、テーブルパーツの行番号。
値 -セルテキスト。
1つのクライアントのレポートの1つのインスタンスでは、1つの計算列に複数の同一行番号を含めることはできません。つまり、セルの一意の座標はペア（列、行）です。
クライアントレポートの構造（consumers_report_columns、consumers_report_regions）および計算値（report_region_instances、report_cell_instances）に従って、レポートの特定のインスタンスを「接着」できます。
したがって、各クライアントの式の一意性と列の構成のメカニズムが実装され、計算されたレポートを保存するメカニズムが実装されます。
式とそれに対応する保存によって値を計算するためのストアドプロシージャを開発することだけが残っています。
建設的な批判や、このようなロジックの古典的な実装へのリンクが嬉しいです。