CREATE TABLE IF NOT EXISTS def_decision_matrix (
Guid CHAR(50) NOT NULL,
MatrixName CHAR(255) NOT NULL,
Package CHAR(50) NOT NULL,
Module VARCHAR(255) DEFAULT 'ObjDecisionMatrix',
Description TEXT,
Version INT NOT NULL DEFAULT 1,
RowAxis VARCHAR(255) NOT NULL,
ColAxis VARCHAR(255) NOT NULL,
DefaultValue TEXT,
SimDataTable VARCHAR(255),
DataTable TEXT,
ConstantsTables TEXT,
RaciSimulation TEXT DEFAULT NULL,
RaciProduction TEXT DEFAULT NULL,
RaciOutcome TEXT DEFAULT NULL,
RaciDevelopment TEXT DEFAULT NULL,
PRIMARY KEY (MatrixName, Package, Version),
KEY idx_decision_matrix_guid (Guid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS def_decision_matrix_axes (
Guid VARCHAR(50) NOT NULL,
MatrixName VARCHAR(255) NOT NULL,
Package VARCHAR(50) NOT NULL,
Version INT NOT NULL,
AxisName VARCHAR(255) NOT NULL,
Position INT NOT NULL,
MatchType VARCHAR(20) NOT NULL DEFAULT 'EXACT',
MatchValue VARCHAR(255),
RangeLow VARCHAR(255),
RangeHigh VARCHAR(255),
Label VARCHAR(255),
PRIMARY KEY (MatrixName, Package, Version, AxisName, Position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS def_decision_matrix_cells (
Guid VARCHAR(50) NOT NULL,
MatrixName VARCHAR(255) NOT NULL,
Package VARCHAR(50) NOT NULL,
Version INT NOT NULL,
RowPosition INT NOT NULL,
ColPosition INT NOT NULL,
CellValue TEXT,
CellType VARCHAR(20) NOT NULL DEFAULT 'VALUE',
PRIMARY KEY (MatrixName, Package, Version, RowPosition, ColPosition)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS def_decision_matrix_history (
Guid VARCHAR(64) NOT NULL,
MatrixName VARCHAR(255) NOT NULL,
Package VARCHAR(50) NOT NULL,
Module VARCHAR(255) DEFAULT 'ObjDecisionMatrix',
Version INT NOT NULL,
ThreeWords VARCHAR(255) DEFAULT NULL,
VersionDate DATETIME DEFAULT NULL,
NodeCount INT DEFAULT 0,
ChangedBy VARCHAR(255) DEFAULT NULL,
ChangeType VARCHAR(50) DEFAULT 'IMPORT',
ChangeNotes TEXT DEFAULT NULL,
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (MatrixName, Package, Version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS {table_name} (
GUID VARCHAR(255) PRIMARY KEY,
SetGuid VARCHAR(255),
{columns},
Output_Value TEXT,
Expected_Value TEXT,
Package VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE {base_table} (
Guid VARCHAR(255) PRIMARY KEY,
SetGuid VARCHAR(255),
payload JSON,
CellValue TEXT,
Expected_Value TEXT,
Verification CHAR(1)
)
DELETE FROM def_decision_matrix
WHERE MatrixName = '{matrix_name}'
AND Package = '{package}'
DELETE FROM def_decision_matrix_axes
WHERE MatrixName = '{matrix_name}'
AND Package = '{package}'
DELETE FROM def_decision_matrix_cells
WHERE MatrixName = '{matrix_name}'
AND Package = '{package}'
SELECT
Guid,
payload,
Expected_Value,
Verification
FROM {output_table}
WHERE Verification = 'N'
ORDER BY Guid
LIMIT {limit}
SELECT COALESCE(MAX(Version), 0)
FROM def_decision_matrix
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
SELECT
CellValue AS outcome,
COUNT(*) AS count
FROM {base_table}
GROUP BY CellValue
ORDER BY count DESC
SELECT
Verification,
COUNT(*) as count
FROM {output_table}
GROUP BY Verification
SELECT Version, ThreeWords, VersionDate,
NodeCount, ChangedBy, ChangeType,
ChangeNotes
FROM def_decision_matrix_history
WHERE MatrixName = '{matrix_name}'
AND Package = '{package}'
ORDER BY Version DESC
INSERT INTO def_decision_matrix
(Guid, MatrixName, Package, Version, RowAxis, ColAxis, DefaultValue)
VALUES ('{guid}', '{matrix_name}', '{package}', {version},
'{row_axis}', '{col_axis}', '{default_value}')
INSERT INTO def_decision_matrix_axes
(Guid, MatrixName, Package, Version, AxisName, Position,
MatchType, MatchValue, RangeLow, RangeHigh, Label)
VALUES ('{guid}', '{matrix_name}', '{package}', {version},
'{axis_name}', {position}, '{match_type}',
'{match_value}', '{range_low}', '{range_high}', '{label}')
INSERT INTO def_decision_matrix_cells
(Guid, MatrixName, Package, Version, RowPosition, ColPosition,
CellValue, CellType)
VALUES ('{guid}', '{matrix_name}', '{package}', {version},
{row_position}, {col_position}, '{cell_value}', '{cell_type}')
INSERT INTO {base_table}
(Guid, SetGuid, payload, CellValue, Expected_Value, Verification)
VALUES ('{rec_guid}', '{set_guid}', '{escaped_json}',
'{cell_value}', '{expected_value}', '{verification}')
INSERT INTO def_decision_matrix_history (
Guid, MatrixName, Package, Version,
ThreeWords, VersionDate, NodeCount,
ChangedBy, ChangeType, ChangeNotes
) VALUES (
'{guid}', '{matrix_name}', '{package}',
{version}, '{three_words}', NOW(),
{node_count}, '{changed_by}', '{change_type}',
'{change_notes}')
SELECT Version, ThreeWords, VersionDate,
NodeCount, ChangedBy, ChangeType,
ChangeNotes
FROM def_decision_matrix_history
WHERE MatrixName = '{matrix_name}'
AND Package = '{package}'
ORDER BY Version DESC
SELECT *
FROM def_decision_matrix
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
ORDER BY Version DESC
LIMIT 1
SELECT *
FROM def_decision_matrix
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
AND Version = {version}
SELECT *
FROM def_decision_matrix_axes
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
AND Version = (
SELECT COALESCE(MAX(Version), 1)
FROM def_decision_matrix
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
)
ORDER BY AxisName, Position
SELECT *
FROM def_decision_matrix_axes
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
AND Version = {version}
ORDER BY AxisName, Position
SELECT *
FROM def_decision_matrix_cells
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
AND Version = (
SELECT COALESCE(MAX(Version), 1)
FROM def_decision_matrix
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
)
ORDER BY RowPosition, ColPosition
SELECT *
FROM def_decision_matrix_cells
WHERE Package = '{package}'
AND MatrixName = '{matrix_name}'
AND Version = {version}
ORDER BY RowPosition, ColPosition
SELECT * FROM {input_table}
SELECT * FROM {input_table}
LIMIT {limit}
SELECT payload
FROM {output_table}
UPDATE def_decision_matrix_cells
SET CellValue = '{cell_value}',
CellType = '{cell_type}'
WHERE MatrixName = '{matrix_name}'
AND Package = '{package}'
AND Version = {version}
AND RowPosition = {row_position}
AND ColPosition = {col_position}