CREATE TABLE IF NOT EXISTS def_decision_bulk_cache (
DecisionName VARCHAR(255) NOT NULL,
Version INT NOT NULL,
SourceTable VARCHAR(255) NOT NULL,
OutcomeCase LONGTEXT,
ValueCase LONGTEXT,
GeneratedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (DecisionName, Version, SourceTable)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS data_decision_sim_analysis (
guid VARCHAR(64) NOT NULL,
DecisionName VARCHAR(255) NOT NULL,
Version INT NOT NULL,
Package CHAR(64) NOT NULL,
SimGuid VARCHAR(255),
BlockType VARCHAR(50) NOT NULL,
BlockTitle VARCHAR(255) NOT NULL,
BlockContent TEXT,
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (guid),
KEY idx_dec (DecisionName, Version, BlockType)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
SELECT *
FROM def_decision_tree
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
ORDER BY Version DESC
LIMIT 1
SELECT *
FROM def_decision_tree
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
AND Version = '{version}'
SELECT *
FROM def_decision_treenodes
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
AND Version = (
SELECT COALESCE(MAX(Version), '1')
FROM def_decision_tree
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
)
ORDER BY COALESCE(Rank, 100), Guid
SELECT *
FROM def_decision_treenodes
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
AND Version = '{version}'
ORDER BY COALESCE(Rank, 100), Guid
SELECT COALESCE(MAX(Version), '0')
FROM def_decision_tree
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
SELECT Version, VersionDate,
LEFT(Description, 60) AS Description
FROM def_decision_tree
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
ORDER BY Version DESC
INSERT INTO def_decision_tree_history (
Guid, DecisionName, Package, Version,
ThreeWords, VersionDate, NodeCount,
ChangedBy, ChangeType, ChangeNotes
) VALUES (
'{guid}', '{decision_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_tree_history
WHERE DecisionName = '{decision_name}'
AND Package = '{package}'
ORDER BY Version DESC
SELECT DISTINCT condition_field
FROM def_decision_treenodes
WHERE DecisionName = '{decision_name}'
AND condition_field IS NOT NULL
AND condition_field != ''
CREATE TABLE IF NOT EXISTS {table_name} (
GUID VARCHAR(255) PRIMARY KEY,
SetGuid VARCHAR(255),
{columns},
Output_Node VARCHAR(50),
Output_Description VARCHAR(255),
Expected_Outcome VARCHAR(50),
Package CHAR(64)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE {base_table} (
Guid VARCHAR(255) PRIMARY KEY,
SetGuid VARCHAR(255),
payload JSON,
Outcome VARCHAR(50),
Expected_Outcome VARCHAR(50),
Verification CHAR(1){calc_columns}
)
INSERT INTO {base_table}
(Guid, SetGuid, payload, Expected_Outcome, Verification)
VALUES ('{rec_guid}', '{set_guid}', '{escaped_json}',
'{expected_outcome}', '{verification}')
INSERT INTO {base_table}
(Guid, SetGuid, payload)
VALUES ('{rec_guid}', '{set_guid}', '{escaped_json}')
SELECT * FROM {input_table}
SELECT * FROM {input_table}
LIMIT {limit}
SELECT * FROM {input_table}
WHERE {where_clause}
SELECT * FROM {input_table}
WHERE {where_clause}
LIMIT {limit}
SELECT payload
FROM {output_table}
UPDATE def_decision_tree
SET operationsql = '{sql_statement}'
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
UPDATE def_decision_tree
SET diagram = '{diagram}'
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
SELECT
JSON_UNQUOTE(JSON_EXTRACT(payload, {outcome_json_path})) AS outcome,
COUNT(*) AS count
FROM {base_table}
GROUP BY outcome
ORDER BY count DESC
DELETE FROM def_decision_treenodes
WHERE DecisionName = '{decision_name}'
AND Package = '{package}'
DELETE FROM def_decision_tree
WHERE DecisionName = '{decision_name}'
AND Package = '{package}'
INSERT INTO def_decision_tree
(Guid, DecisionName, Package, Version)
VALUES ('{guid}', '{decision_name}', '{package}', '{version}')
SELECT
Verification,
COUNT(*) as count
FROM {output_table}
GROUP BY Verification
SELECT
Guid,
payload,
Expected_Outcome,
Verification
FROM {output_table}
WHERE Verification = 'N'
ORDER BY Guid
LIMIT {limit}
SELECT
Expected_Outcome,
COUNT(*) as count
FROM {output_table}
WHERE Verification = 'N'
GROUP BY Expected_Outcome
ORDER BY count DESC
SELECT
SetGuid,
COUNT(*) as total_records,
SUM(CASE WHEN Verification = 'Y' THEN 1 ELSE 0 END) as passed,
SUM(CASE WHEN Verification = 'N' THEN 1 ELSE 0 END) as failed
FROM {output_table}
GROUP BY SetGuid
DELETE FROM {output_table}
WHERE SetGuid = '{set_guid}'
DELETE FROM {output_table}
SELECT * FROM {input_table}
ORDER BY RAND()
LIMIT {limit}
SELECT * FROM {input_table}
WHERE {where_clause}
ORDER BY RAND()
LIMIT {limit}
SELECT payload
FROM {output_table}
WHERE JSON_EXTRACT(payload, '$.{unique_id_field}') IN ({id_list})
SELECT `{pk_col}` AS pk,
`{outcome_col}` AS bulk_outcome
FROM `{input_table}`
WHERE `{pk_col}` IN ({pk_list})
INSERT INTO data_decision_sim_analysis (
guid, DecisionName, Version, Package,
SimGuid, BlockType, BlockTitle, BlockContent
) VALUES (
'{block_guid}',
'{decision_name}',
{version},
'{package}',
'{sim_guid}',
'reconcile',
'Bulk vs Live Reconciliation',
'{content}'
)