CREATE TABLE IF NOT EXISTS
`def_decision_ruleset` (
`Guid` VARCHAR(64) NOT NULL,
`RulesetName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(50) NOT NULL,
`Module` VARCHAR(255)
DEFAULT 'ObjDecisionRuleset',
`Version` INT NOT NULL DEFAULT 1,
`Description` TEXT DEFAULT NULL,
`ConstantsTables` TEXT DEFAULT NULL,
`DefaultOutcome` VARCHAR(255)
DEFAULT 'DEFAULT',
`ThreeWords` VARCHAR(255) DEFAULT NULL,
`VersionDate` DATETIME DEFAULT NULL,
`DataTable` TEXT DEFAULT NULL,
`SimDataTable` VARCHAR(255) DEFAULT NULL,
`RaciSimulation` TEXT DEFAULT NULL,
`RaciProduction` TEXT DEFAULT NULL,
`RaciOutcome` TEXT DEFAULT NULL,
`RaciDevelopment` TEXT DEFAULT NULL,
PRIMARY KEY (
`RulesetName`, `Package`, `Version`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS
`def_decision_ruleset_rules` (
`Guid` VARCHAR(64) NOT NULL,
`RulesetName` VARCHAR(100) NOT NULL,
`Package` VARCHAR(50) NOT NULL,
`Module` VARCHAR(255)
DEFAULT 'ObjDecisionRuleset',
`Version` INT NOT NULL DEFAULT 1,
`Priority` INT NOT NULL DEFAULT 99,
`RuleName` VARCHAR(100) NOT NULL,
`ConditionField` VARCHAR(100)
DEFAULT NULL,
`ConditionOperator` VARCHAR(20)
DEFAULT '=',
`ConditionValue` VARCHAR(512)
DEFAULT NULL,
`Outcome` VARCHAR(255) NOT NULL,
`OutcomeDescription` TEXT DEFAULT NULL,
`Active` CHAR(1) DEFAULT 'Y',
PRIMARY KEY (
`RulesetName`, `Package`, `Version`,
`Priority`, `RuleName`,
`ConditionField`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS
`def_decision_ruleset_history` (
`Guid` VARCHAR(64) NOT NULL,
`RulesetName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(50) NOT NULL,
`Module` VARCHAR(255)
DEFAULT 'ObjDecisionRuleset',
`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 (
`RulesetName`, `Package`, `Version`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
SELECT *
FROM def_decision_ruleset
WHERE RulesetName = '{ruleset_name}'
AND Package = '{package}'
ORDER BY Version DESC
LIMIT 1
SELECT *
FROM def_decision_ruleset
WHERE RulesetName = '{ruleset_name}'
AND Package = '{package}'
AND Version = {version}
SELECT *
FROM def_decision_ruleset_rules
WHERE RulesetName = '{ruleset_name}'
AND Package = '{package}'
AND Version = (
SELECT COALESCE(MAX(Version), 1)
FROM def_decision_ruleset
WHERE RulesetName = '{ruleset_name}'
AND Package = '{package}'
)
ORDER BY Priority, RuleName, ConditionField
SELECT *
FROM def_decision_ruleset_rules
WHERE RulesetName = '{ruleset_name}'
AND Package = '{package}'
AND Version = {version}
ORDER BY Priority, RuleName, ConditionField
SELECT COALESCE(MAX(Version), 0)
FROM def_decision_ruleset
WHERE RulesetName = '{ruleset_name}'
AND Package = '{package}'
SELECT RulesetName, Version, ThreeWords,
VersionDate, Description,
DefaultOutcome, DataTable
FROM def_decision_ruleset
WHERE Package IN (
'CORE', 'SYSTEM', '{package}'
)
ORDER BY RulesetName, Version DESC
INSERT INTO def_decision_ruleset (
Guid, RulesetName, Package, Version,
DefaultOutcome, VersionDate
) VALUES (
'{guid}', '{ruleset_name}', '{package}',
{version}, '{default_outcome}', NOW()
)
SELECT * FROM {input_table}
SELECT * FROM {input_table}
LIMIT {limit}