CREATE TABLE IF NOT EXISTS `def_bop` (
`BopCode` VARCHAR(64) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Title` VARCHAR(500) NOT NULL DEFAULT '',
`Description` TEXT,
`Version` INT NOT NULL DEFAULT 1,
`Status` VARCHAR(16) DEFAULT 'DRAFT',
`Classification` VARCHAR(64) DEFAULT 'INTERNAL',
`Department` VARCHAR(255) DEFAULT '',
`ProcessOwner` VARCHAR(255) DEFAULT '',
`Workflows` TEXT,
`Webhooks` TEXT,
`Services` TEXT,
`Reports` TEXT,
`DecisionTrees` TEXT,
`FeatureStores` TEXT,
`DataTables` TEXT,
`Scope` TEXT,
`Purpose` TEXT,
`AiReview` LONGTEXT,
`AiSummary` LONGTEXT,
`Active` CHAR(1) DEFAULT 'Y',
`Module` VARCHAR(255) DEFAULT 'ObjBOP',
`CreateDate` DATETIME DEFAULT CURRENT_TIMESTAMP,
`UpdateDate` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`BopCode`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_bop_revision` (
`Guid` VARCHAR(64) NOT NULL PRIMARY KEY,
`BopCode` VARCHAR(64) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Version` INT NOT NULL,
`Author` VARCHAR(255) DEFAULT '',
`ChangeDescription` TEXT,
`ApprovedBy` VARCHAR(255) DEFAULT '',
`ApprovedDate` DATETIME,
`Status` VARCHAR(16) DEFAULT 'DRAFT',
`Module` VARCHAR(255) DEFAULT 'ObjBOP',
`CreateDate` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_bop_datatables` (
`BopCode` VARCHAR(64) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`TableName` VARCHAR(255) NOT NULL,
`TableType` VARCHAR(16) DEFAULT 'data',
`Description` TEXT,
`PrimaryKeys` VARCHAR(500) DEFAULT '',
`AiReview` TEXT,
`DataOwner` VARCHAR(255) DEFAULT '',
`Retention` VARCHAR(64) DEFAULT '',
`Expanded` CHAR(1) DEFAULT 'N',
`Module` VARCHAR(255) DEFAULT 'ObjBOP',
PRIMARY KEY (`BopCode`, `Package`, `TableName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_bop_raci` (
`BopCode` VARCHAR(64) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Activity` VARCHAR(255) NOT NULL,
`Responsible` VARCHAR(255) DEFAULT '',
`Accountable` VARCHAR(255) DEFAULT '',
`Consulted` VARCHAR(255) DEFAULT '',
`Informed` VARCHAR(255) DEFAULT '',
`Module` VARCHAR(255) DEFAULT 'ObjBOP',
PRIMARY KEY (`BopCode`, `Package`, `Activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT * FROM def_bop WHERE BopCode = '{bop_code}'
AND Package = '{package}'
SELECT BopCode, Title, Version, Status,
Department, ProcessOwner
FROM def_bop WHERE Package = '{package}'
AND Active = 'Y'
ORDER BY BopCode
SELECT * FROM def_bop_revision WHERE BopCode = '{bop_code}'
AND Package = '{package}'
ORDER BY Version DESC
SELECT * FROM def_bop_datatables WHERE BopCode = '{bop_code}'
AND Package = '{package}'
ORDER BY TableType, TableName
SELECT * FROM def_bop_raci WHERE BopCode = '{bop_code}'
AND Package = '{package}'
ORDER BY Activity
SELECT WorkflowNodeGuid, Type, Name,
Rank, BranchDirect, Node_Data,
Description
FROM def_workflows WHERE WorkflowName = '{workflow}'
AND Package IN (
'{package}', '{archetype}',
'CORE', 'SYSTEM'
)
ORDER BY Rank
SELECT WorkflowName, Description,
KickoffType, ExecutionMode,
simulation_sql
FROM def_workflow WHERE WorkflowName = '{workflow}'
AND Package IN (
'{package}', '{archetype}',
'CORE', 'SYSTEM'
)
LIMIT 1
SELECT WebhookCode, Description, Package,
Method, Endpoint, Active
FROM def_webhook WHERE WebhookCode = '{webhook}'
AND Package IN (
'{package}', '{archetype}',
'CORE', 'SYSTEM'
)
LIMIT 1
SELECT ReportCode, Title, Notes,
ReportType, Template, Package
FROM def_report WHERE ReportCode = '{report}'
AND Package IN (
'{package}', '{archetype}',
'CORE', 'SYSTEM'
)
LIMIT 1
SELECT GROUP_CONCAT(
COLUMN_NAME ORDER BY ORDINAL_POSITION
) AS PrimaryKeys FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = '{table_name}'
AND CONSTRAINT_NAME = 'PRIMARY'
¶ get_table_columns
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_KEY FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = '{table_name}'
ORDER BY ORDINAL_POSITION
¶ get_expanded_table_data
SELECT * FROM `{table_name}` LIMIT 50
SELECT ServiceName, Description, Package FROM def_service WHERE ServiceName = '{service}'
AND Package IN (
'{package}', '{archetype}',
'CORE', 'SYSTEM'
)
LIMIT 1
SELECT TABLE_NAME,
COLUMN_NAME
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME LIKE 'bloom_form_%'
ORDER BY TABLE_NAME, ORDINAL_POSITION
SELECT TABLE_NAME,
COLUMN_NAME
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME LIKE 'collections_%'
ORDER BY TABLE_NAME, ORDINAL_POSITION