CREATE TABLE `def_decision_segment` (
`SegmentName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT 'ObjDecisionSegmentation',
`Version` INT NOT NULL DEFAULT 1,
`BaseTable` VARCHAR(255) DEFAULT NULL,
`BaseRank` VARCHAR(50) DEFAULT 'rank',
`BaseGuid` VARCHAR(50) DEFAULT 'guid',
`BaseValues` VARCHAR(500) DEFAULT NULL,
`InputGuid` VARCHAR(50) DEFAULT 'guid',
`MetaSegment` VARCHAR(255) DEFAULT '$basetable$_seg_meta',
`DefaultOutcome` TEXT DEFAULT NULL,
`SimDataTable` VARCHAR(255) DEFAULT NULL,
`DataTable` VARCHAR(255) DEFAULT NULL,
`ConstantsTables` VARCHAR(500) DEFAULT NULL,
`ThreeWords` VARCHAR(255) DEFAULT NULL,
`Description` TEXT DEFAULT NULL,
`Active` CHAR(1) DEFAULT 'Y',
`RaciSimulation` TEXT DEFAULT NULL,
`RaciProduction` TEXT DEFAULT NULL,
`RaciOutcome` TEXT DEFAULT NULL,
`RaciDevelopment` TEXT DEFAULT NULL,
PRIMARY KEY (`SegmentName`, `Package`, `Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
¶ def_decision_segment_columns
CREATE TABLE `def_decision_segment_columns` (
`SegmentName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT 'ObjDecisionSegmentation',
`Version` INT NOT NULL DEFAULT 1,
`Rank` INT NOT NULL DEFAULT 0,
`ColumnName` VARCHAR(255) NOT NULL,
`ColumnMatch` VARCHAR(255) DEFAULT NULL,
`ColumnOperation` VARCHAR(20) DEFAULT 'IN',
`Active` CHAR(1) DEFAULT 'Y',
PRIMARY KEY (
`SegmentName`, `Package`, `Version`, `Rank`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
CREATE TABLE `def_decision_segment_history` (
`SegmentName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT 'ObjDecisionSegmentation',
`Version` INT NOT NULL DEFAULT 1,
`ThreeWords` VARCHAR(255) DEFAULT NULL,
`VersionDate` DATETIME DEFAULT CURRENT_TIMESTAMP,
`NodeCount` INT DEFAULT 0,
`ChangedBy` VARCHAR(255) DEFAULT NULL,
`ChangeType` VARCHAR(50) DEFAULT NULL,
`ChangeNotes` TEXT DEFAULT NULL,
`CreatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (
`SegmentName`, `Package`, `Version`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
SELECT *
FROM def_decision_segment
WHERE SegmentName = '{segment_name}'
AND Package IN ('{package}', 'CORE', '')
AND Active = 'Y'
ORDER BY Version DESC
LIMIT 1
SELECT *
FROM def_decision_segment
WHERE SegmentName = '{segment_name}'
AND Package IN ('{package}', 'CORE', '')
AND Version = {version}
LIMIT 1
¶ get_columns
SELECT ColumnName, ColumnMatch, ColumnOperation
FROM def_decision_segment_columns
WHERE SegmentName = '{segment_name}'
AND Package IN ('{package}', 'CORE', '')
AND Version = {version}
AND Active = 'Y'
ORDER BY Rank
SELECT SegmentName, Package, Version,
BaseTable, ThreeWords, Description
FROM def_decision_segment
WHERE Package IN ('{package}', 'CORE', '')
AND Active = 'Y'
ORDER BY SegmentName
SELECT COALESCE(MAX(Version), 0)
FROM def_decision_segment
WHERE SegmentName = '{segment_name}'
AND Package IN ('{package}', 'CORE', '')
INSERT INTO def_decision_segment (
SegmentName, Package, Module, Version,
BaseTable, BaseRank, BaseGuid, BaseValues,
InputGuid, MetaSegment, DefaultOutcome,
SimDataTable, DataTable, ConstantsTables,
ThreeWords, Description, Active
) VALUES (
'{segment_name}', '{package}',
'ObjDecisionSegmentation', {version},
'{base_table}', '{base_rank}',
'{base_guid}', '{base_values}',
'{input_guid}', '{meta_segment}',
'{default_outcome}', '{sim_data_table}',
'{data_table}', '{constants_tables}',
'{three_words}', '{description}', 'Y'
)
¶ insert_column
INSERT INTO def_decision_segment_columns (
SegmentName, Package, Module, Version,
Rank, ColumnName, ColumnMatch,
ColumnOperation, Active
) VALUES (
'{segment_name}', '{package}',
'ObjDecisionSegmentation', {version},
{rank}, '{column_name}', '{column_match}',
'{column_operation}', 'Y'
)
INSERT IGNORE INTO def_decision_segment_history (
SegmentName, Package, Module, Version,
ThreeWords, VersionDate, NodeCount,
ChangedBy, ChangeType
) VALUES (
'{segment_name}', '{package}',
'ObjDecisionSegmentation', {version},
'{three_words}', NOW(), {node_count},
'{changed_by}', '{change_type}'
)
SELECT Version, ThreeWords, VersionDate,
NodeCount, ChangedBy, ChangeType
FROM def_decision_segment_history
WHERE SegmentName = '{segment_name}'
AND Package IN ('{package}', 'CORE', '')
ORDER BY Version DESC
SELECT {select_cols}
FROM {base_table}
WHERE {where_clause}
ORDER BY {base_rank} LIMIT 1
REPLACE INTO {meta_table} (
`{segment_name}_date`,
`seg_guid`,
`seg_id`,
`seg_version`,
`{input_guid}`,
{output_cols}
)
CREATE TABLE IF NOT EXISTS `{meta_table}` (
`{segment_name}_date` DATETIME DEFAULT NULL,
`seg_guid` VARCHAR(255) DEFAULT NULL,
`seg_id` VARCHAR(255) DEFAULT NULL,
`seg_version` VARCHAR(50) DEFAULT NULL,
`{input_guid}` VARCHAR(255) NOT NULL,
{output_col_defs}
PRIMARY KEY (`{input_guid}`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
INSERT IGNORE INTO def_decision_segment (
SegmentName, Package, Module, Version,
BaseTable, BaseRank, BaseGuid, BaseValues,
InputGuid, MetaSegment, Active
)
SELECT
SegmentName, Package,
'ObjDecisionSegmentation', 1,
BaseTable, BaseRank, BaseGuid, BaseValues,
InputGuid, MetaSegment, Active
FROM def_segment
WHERE SegmentName = '{segment_name}'
AND Package IN ('{package}', 'CORE', '')