CREATE TABLE `def_workflow` (
`WorkflowName` varchar(100) NOT NULL DEFAULT '',
`Package` varchar(100) NOT NULL DEFAULT '',
`Level` int(11) DEFAULT 0,
`Description` text DEFAULT NULL,
`Async` char(1) DEFAULT 'N',
`Active` char(1) DEFAULT 'Y',
`Status` varchar(50) DEFAULT '',
`UpdateDate` datetime DEFAULT NULL,
`ExecutionMode` varchar(20) DEFAULT 'SINGLE',
`WorkflowGuid` varchar(255) DEFAULT NULL,
`ProcessGuidSql` text DEFAULT NULL,
`piper_flow` text DEFAULT NULL,
`ThreeWords` varchar(255) DEFAULT NULL,
`Version` varchar(50) DEFAULT NULL,
`Result1Map` varchar(255) DEFAULT NULL,
`Result2Map` varchar(255) DEFAULT NULL,
`Result3Map` varchar(255) DEFAULT NULL,
`Result4Map` varchar(255) DEFAULT NULL,
`Result5Map` varchar(255) DEFAULT NULL,
`Result6Map` varchar(255) DEFAULT NULL,
`Result7Map` varchar(255) DEFAULT NULL,
`Result8Map` varchar(255) DEFAULT NULL,
`Result9Map` varchar(255) DEFAULT NULL,
`Module` varchar(255) DEFAULT NULL,
`FinalOutputTables` TEXT DEFAULT NULL,
`FinalDataRedact` TEXT DEFAULT NULL,
`SimPrompt` TEXT DEFAULT NULL,
`SimReviewPrompt` TEXT DEFAULT NULL,
`SimOutcomePrompt` TEXT DEFAULT NULL,
`DataModel` varchar(255) DEFAULT NULL,
`referenced_bloom` text DEFAULT NULL,
`referenced_data` text DEFAULT NULL,
`RaciSimulation` text DEFAULT NULL,
`RaciProduction` text DEFAULT NULL,
`RaciOutcome` text DEFAULT NULL,
`RaciDevelopment` text DEFAULT NULL,
PRIMARY KEY (`WorkflowName`,`Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_workflows` (
`WorkflowName` varchar(100) NOT NULL DEFAULT '',
`Package` varchar(100) NOT NULL DEFAULT '',
`Rank` int(11) NOT NULL DEFAULT 0,
`Type` varchar(50) DEFAULT NULL,
`Name` varchar(255) DEFAULT NULL,
`Description` text DEFAULT NULL,
`Async` char(5) DEFAULT NULL,
`Active` char(1) DEFAULT 'Y',
`WorkflowGuid` varchar(255) DEFAULT NULL,
`WorkflowNodeGuid` varchar(255) DEFAULT NULL,
`BranchDirect` varchar(255) DEFAULT NULL,
`BranchSql` text DEFAULT NULL,
`PossibleBranches` text DEFAULT NULL,
`InputGuidSql` text DEFAULT NULL,
`ValueSql` text DEFAULT NULL,
`Selector` varchar(255) DEFAULT NULL,
`Node_Actor` varchar(255) DEFAULT NULL,
`Node_Lane` varchar(255) DEFAULT NULL,
`Node_Pool` varchar(255) DEFAULT NULL,
`Node_Data` text DEFAULT NULL,
`Version` varchar(50) DEFAULT NULL,
`Module` varchar(255) DEFAULT NULL,
PRIMARY KEY (`WorkflowName`,`Package`,`Rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_workflow_actors` (
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`WorkflowName` varchar(100) NOT NULL DEFAULT '',
`Version` varchar(100) NOT NULL DEFAULT '',
`Actor` varchar(100) NOT NULL DEFAULT '',
`Description` varchar(255) DEFAULT NULL,
`Lane` varchar(255) DEFAULT NULL,
`Pool` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Package`, `WorkflowName`, `Actor`, `Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_workflow_transitions` (
`WorkflowSourceGuid` varchar(255) NOT NULL DEFAULT '',
`WorkflowTargetGuid` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`WorkflowSourceGuid`,`WorkflowTargetGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_workflow` (
`TaskGuid` varchar(255) NOT NULL DEFAULT '',
`Guid` varchar(255) DEFAULT NULL,
`Package` varchar(100) DEFAULT NULL,
`WorkflowCode` varchar(100) DEFAULT NULL,
`Instance` varchar(50) DEFAULT NULL,
`ResumeNode` varchar(255) DEFAULT NULL,
`ScheduledTime` datetime DEFAULT NULL,
`Context` text DEFAULT NULL,
`FanInNode` varchar(255) DEFAULT NULL,
`FanInSubGuids` text DEFAULT NULL,
`Status` varchar(50) DEFAULT 'PENDING',
`Payload` text DEFAULT NULL,
`Result1` text DEFAULT NULL,
`Result2` text DEFAULT NULL,
`Result3` text DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
PRIMARY KEY (`TaskGuid`),
KEY `idx_stage_workflow_guid` (`Guid`),
KEY `idx_stage_workflow_status` (`Status`, `ScheduledTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_workflow_avro` (
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`WorkflowName` varchar(100) NOT NULL DEFAULT '',
`Direction` varchar(10) DEFAULT NULL,
`Node` varchar(100) NOT NULL DEFAULT '',
`Parameter` varchar(100) NOT NULL DEFAULT '',
`DataType` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Package`, `WorkflowName`, `Node`, `Parameter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_workflow_history` (
`Guid` VARCHAR(64) NOT NULL,
`WorkflowName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(50) NOT NULL,
`Module` VARCHAR(255) DEFAULT 'ObjWorkflow',
`Version` VARCHAR(50) 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 (`WorkflowName`, `Package`, `Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT
S.WorkflowName, W.`Level`, COUNT(*) AS StepCount,
max(if (S.Rank = 1, S.`Type`,''))
FROM def_workflow AS W
LEFT JOIN def_workflows AS S
ON
W.WorkflowName = S.WorkflowName
AND W.Package = S.Package
WHERE
S.Package = '$package$'
GROUP BY WorkflowName
SELECT WORKFLOWNAME, coalesce(Description, WORKFLOWNAME) FROM def_workflow WHERE WORKFLOWNAME = '{webhook_code}' AND Package in ('{package}','{archetype}','CORE','SYSTEM')
SELECT WORKFLOWNAME, coalesce(WORKFLOWNAME, Description) FROM def_workflows WHERE TYPE in ({WORKFLOW_KICKOFF})
AND WORKFLOWNAME = '{webhook_code}'
AND ACTIVE = 'Y'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
LIMIT 1
SELECT selector FROM def_workflows WHERE `Type` = 'WORKFLOW'
AND WorkflowName = '{workflow_code}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
AND WorkflowGuid = '{workflow_code}'
SELECT COUNT(*) FROM (
SELECT *
FROM def_workflows
WHERE WorkflowName = '{workflow_code}'
AND Package IN ('{package}', '{archetype}', 'CORE', 'SYSTEM')
ORDER BY RANK
LIMIT 1
) AS A WHERE Type IN ('FORM');
SELECT coalesce(Async,'') FROM def_workflow WHERE WorkflowName = '{workflow_code}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
UPDATE def_workflow set WorkflowGuid = WorkflowName where WorkflowGuid is null or WorkflowGuid = ''
¶ prepare_run_get_workflow_and_rank
SELECT WORKFLOWNAME,RANK FROM def_workflows WHERE TYPE in ({WORKFLOW_KICKOFF}) AND WORKFLOWNAME = '{workflow_code}' AND ACTIVE = 'Y' AND Package in ('{package}','{archetype}','CORE','SYSTEM') ORDER BY RANK LIMIT 1
SELECT coalesce(processguidsql,'') FROM def_workflow WHERE WorkflowName = '{workflow}' AND Package in ('{package}','{archetype}','CORE','SYSTEM') AND Workflowguid = '{workflow_code}'
UPDATE def_workflows SET WorkflowNodeGuid = upper(WorkflowNodeGuid), BranchDirect = upper(BranchDirect) WHERE WorkflowName = '{workflow}' AND Package in ('{package}','{archetype}','CORE','SYSTEM')
SELECT
WorkflowNodeGuid, Rank, Type, coalesce(Async,''),
Name,
lower(BranchDirect) as BranchDirect,
coalesce(InputGuidSql,''),
coalesce(BranchSql,'') as BranchSql,
coalesce(Node_Data,'') as Node_Data,
coalesce(ValueSql, '') as Value_Sql,
coalesce(Selector, '') as Selector
FROM def_workflows WHERE WorkflowName = '{workflow}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
ORDER BY rank
SELECT upper(coalesce(COALESCE(R.Template, P.DefaultTemplate), 'basic')) AS Template FROM def_report as R LEFT JOIN def_package AS P ON P.Package='{package}' WHERE
reportcode = '{target}'
and R.Package = '{package}'
SELECT upper(coalesce(COALESCE(R.Template, P.DefaultTemplate), 'basic')) AS Template FROM def_form as R LEFT JOIN def_package AS P ON P.Package='{package}' WHERE
name = '{target}'
and R.Package = '{package}'
SELECT DefaultTemplate from def_package WHERE Package = '{package}'
SELECT Service FROM def_service WHERE
Package in('','{package}')
AND ServiceCode like '{name}'
SELECT {result_columns} FROM def_workflow WHERE
WORKFLOWNAME = '{workflow_code}'
AND package = '{package}'
AND Active = 'Y'
UPDATE def_workflow SET WorkflowName = UPPER(WorkflowName) ,Package = UPPER(Package)
SELECT workflowname, package, workflowguid, version FROM def_workflow WHERE
workflowname = '{webhook_code}'
AND active = 'Y'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
LIMIT 1
UPDATE def_workflow SET WorkflowGuid = '{workflow_guid}',
Version = '{version}'
WHERE
workflowname = '{webhook_code}'
AND active = 'Y'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
SELECT coalesce(ThreeWords,'') FROM def_workflow WHERE WORKFLOWNAME = '{workflow}' AND ACTIVE = 'Y' AND Package = '{work_package}'
Update def_workflow
set ThreeWords = '{threewords}'
WHERE WORKFLOWNAME = '{workflow}' AND ACTIVE = 'Y' AND Package = '{work_package}'
UPDATE def_workflows SET WorkflowNodeGuid = WorkflowGuid WHERE WorkflowNodeGuid = ''
UPDATE def_workflows SET WorkflowGuid = '{workflow_guid}' WHERE
workflowname = '{webhook_code}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
UPDATE def_workflows SET Version = '{version}' WHERE
( Version is NULL or Version = '')
AND workflowname = '{webhook_code}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
UPDATE def_workflows SET Description = CONCAT(Type, ' ',Name) WHERE
( Description is NULL or Description = '')
AND workflowname = '{webhook_code}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
SELECT workflowname, package FROM def_workflows WHERE type IN ({WORKFLOW_KICKOFF})
AND workflowname = '{webhook_code}'
AND active = 'Y'
AND version = '{version}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
LIMIT 1
UPDATE def_workflows SET
Selector = if(LENGTH(BranchDirect)>2,'DIRECT',if(LENGTH(BranchSql)>2,'QUERY',''))
WHERE
Type = 'GATE'
AND version = '{version}'
AND workflowname = '{workflow}'
AND Package = '{work_package}'
SELECT
UPPER(WorkflowNodeGuid),
if(length(BranchDirect)>2,BranchDirect,PossibleBranches) as Branches,
Type, Name, Rank
FROM def_workflows where workflowname = '{workflow}' AND Package = '{work_package}'
ORDER BY RANK, if(TYPE = 'GATE',0,1)
UPDATE def_workflows SET
WorkflowNodeGuid = '{node_guid}'
WHERE Package = '{work_package}'
AND WorkflowName = '{workflow}'
AND version = '{version}'
And Rank = '{rank}'
INSERT IGNORE INTO def_workflow_transitions
(WorkflowSourceGuid, WorkflowTargetGuid)
VALUES
('{node_from}','{node_to}')
SELECT * FROM def_workflows WHERE Package in ('{package}','{archetype}','CORE','SYSTEM') and WorkflowName = '{workflow}' ORDER BY RANK, if(TYPE = 'GATE',0,1)
SELECT '' as WorkflowGuid,if(length(BranchDirect)>2,BranchDirect,PossibleBranches) as Branches,
Type,Name,
coalesce(node_lane, type) as Lane,
coalesce(Description, '') as Des,
coalesce(node_pool, 'type') as Pool
FROM def_workflows WHERE Package in ('{package}','{archetype}','CORE','SYSTEM') and WorkflowName = '{workflow}' ORDER BY RANK, if(TYPE = 'GATE',0,1)
UPDATE def_workflow set piper_flow = '{flow}' WHERE
workflowname = '{webhook_code}' and Package = '{package}'
SELECT concat(WorkflowName,' : ',coalesce(DESCRIPTION, WorkFlowName)) AS Description FROM
def_workflow
WHERE
Package in ('{package}', '{archetype}', 'CORE', 'SYSTEM')
SELECT {table_key} from {table_base} where ( {table_status} is null
or {table_status} in ('','N')
)
INSERT IGNORE INTO stage_workflow (TaskGuid, Guid, Package, WorkflowCode, Instance) VALUES ('{guid}', '{guid}', '{package}', '{workflow_code}', 'MQ');
INSERT INTO stage_workflow
(TaskGuid, Guid, Package, WorkflowCode, ResumeNode,
ScheduledTime, Context, FanInNode, FanInSubGuids, Status)
VALUES
('{task_guid}', '{guid}', '{package}', '{workflow_code}', '{resume_node}',
{scheduled_time}, '{context}', '{fanin_node}', '{fanin_subguids}', '{status}')
ON DUPLICATE KEY UPDATE
Status = VALUES(Status),
ResumeNode = VALUES(ResumeNode),
ScheduledTime = VALUES(ScheduledTime),
Context = VALUES(Context),
FanInNode = VALUES(FanInNode),
FanInSubGuids = VALUES(FanInSubGuids)
UPDATE stage_workflow SET Status = '{status}' WHERE TaskGuid = '{task_guid}'
UPDATE stage_workflow SET Status = 'DONE',
Result1 = '{result1}',
Result2 = '{result2}',
Result3 = '{result3}',
Payload = '{payload}',
TimeCompleted = NOW()
WHERE TaskGuid = '{task_guid}'
SELECT TaskGuid, Guid, WorkflowCode, ResumeNode, Context FROM stage_workflow WHERE TaskGuid = '{guid}' LIMIT 1
(SELECT TaskGuid, Guid, WorkflowCode, ResumeNode, Context FROM stage_workflow WHERE Package = '{package}'
AND Status = 'PENDING'
AND (ScheduledTime IS NULL OR ScheduledTime <= NOW())
LIMIT {limit}) UNION ALL (SELECT TaskGuid, Guid, WorkflowCode, ResumeNode, Context FROM stage_workflow WHERE Package = '{package}'
AND Status = 'PAUSED'
AND ScheduledTime <= NOW()
LIMIT {limit}) LIMIT {limit}
SELECT TaskGuid, Guid, WorkflowCode, FanInNode, ResumeNode,
Context, FanInSubGuids
FROM stage_workflow WHERE Package = '{package}'
AND Status = 'FANIN_WAIT'
SELECT COUNT(*) FROM stage_workflow WHERE TaskGuid IN ({subguid_list})
AND Status NOT IN ('DONE', 'FAILED')
SELECT FanInSubGuids FROM stage_workflow WHERE Guid = '{guid}'
AND FanInNode = '{fanin_node}'
LIMIT 1
INSERT INTO sys_user (user, UUID, Package, PASSWORD) VALUES ('TEST_{uuid}','{uuid}','REFERENCE','{uuid}')
DELETE FROM sys_user WHERE
user = 'TEST_{uuid}'
SELECT
WorkflowNodeGuid, Rank, Type, coalesce(Async,''),
Name,
lower(BranchDirect) as BranchDirect,
coalesce(InputGuidSql,''),
coalesce(BranchSql,'') as BranchSql,
coalesce(Node_Data,'') as Node_Data,
coalesce(ValueSql, '') as Value_Sql,
coalesce(Selector, '') as Selector
FROM def_workflows
WHERE WorkflowName = '{workflow}'
AND Package in ('{package}','{archetype}','CORE','SYSTEM')
ORDER BY rank
UPDATE stage_workflow SET {parts} WHERE TaskGuid='{guid}'
UPDATE def_workflows
SET
`Rank` = %s,
`Description` = %s,
`Name` = %s,
`Active` = %s,
`Type` = CASE
WHEN %s IS NULL THEN `Type`
ELSE %s
END
WHERE WorkflowName = %s
AND WorkflowNodeGuid = %s
AND package LIKE %s
INSERT INTO `def_workflows` (
`Package`, `WorkflowName`, `WorkflowNodeGuid`, `Name`, `Description`, `Type`, `version`
)
VALUES (
%s, %s, %s, %s, %s, %s, %s
)
delete from def_workflows
where WorkflowName = '{workflow}'
and WorkflowNodeGuid = '{workflow_node_guid}'
and package = "{package}"
UPDATE def_workflows
SET
branchdirect = CASE
WHEN INSTR(%s, ',') > 0 THEN ''
ELSE %s
END,
PossibleBranches = CASE
WHEN INSTR(%s, ',') > 0 THEN %s
ELSE ''
END,
`BranchSql` = %s
WHERE
WorkflowNodeGuid = %s
AND WorkflowName = %s
AND package = %s
SELECT * FROM def_sms
WHERE
`smscode` = "{smscode}"
AND package = "{package}"
UPDATE `def_sms`
SET
`Smscode` = %s,
`Package` = %s,
`Provider` = %s,
`Description` = %s,
`SMSconnection` = %s,
`Message` = %s
WHERE
`Smscode` = %s
AND `Package` = %s
LIMIT 1
INSERT INTO `def_sms` (
`Smscode`, `Package`, `Provider`, `Description`, `SMSconnection`, `Message`
)
VALUES (
%s, %s, %s, %s, %s, %s
)
SELECT * FROM def_channel
WHERE
`channel` = "{channel}"
AND `package` = "{package_upper}"
UPDATE `def_channel`
SET
`Channel` = %s,
`description` = %s,
`notes` = %s,
`lineheader` = %s,
`email` = %s,
`background` = %s,
`style` = %s,
`package` = %s
WHERE
`channel` = %s
AND `Package` = %s
LIMIT 1
INSERT INTO `def_channel` (
`Channel`, `description`, `notes`, `lineheader`, `email`, `background`, `style`, `package`
)
VALUES (
%s, %s, %s, %s, %s, %s, %s, %s
)
SELECT * FROM def_alert
WHERE
`alert` = "{alert}"
AND `package` = "{package_upper}"
UPDATE `def_alert`
SET
`description` = %s,
`triggercooldown` = %s,
`triggersql` = %s,
`alertvaluesql` = %s,
`active` = %s,
`alert` = %s,
`package` = %s
WHERE
`alert` = %s
AND `Package` = %s
LIMIT 1
INSERT INTO `def_alert` (
`package`, `alert`, `description`, `triggercooldown`, `triggersql`, `alertvaluesql`, `active`, `Workflow`, `infrastructure`
)
VALUES (
%s, %s, %s, %s, %s, %s, %s, %s, %s
)
SELECT * FROM def_notify
WHERE
`notifycode` = "{notifycode}"
AND `package` = "{package_upper}"
UPDATE `def_notify`
SET
`notifycode` = %s,
`description` = %s,
`rank` = %s,
`notifyvaluesql` = %s,
`triggersql` = %s,
`triggercooldown` = %s,
`package` = %s
WHERE
`notifycode` = %s
AND `Package` = %s
LIMIT 1
INSERT INTO `def_notify` (
`package`, `notifycode`, `description`, `rank`, `notifyvaluesql`, `triggersql`, `triggercooldown`, `active`
)
VALUES (
%s, %s, %s, %s, %s, %s, %s, %s
)
SELECT * FROM def_service
WHERE
`servicecode` = "{servicecode}"
AND `package` = "{package_upper}"
UPDATE `def_service`
SET
`servicecode` = %s,
`service` = %s,
`servicetype` = %s,
`description` = %s,
`servicecachetime` = %s,
`remoteconnection` = %s,
`remoteconnection2` = %s,
`remoteserviceconnection` = %s,
`remoteserviceconnection2` = %s,
`payloadtype` = %s,
`package` = %s
WHERE
`servicecode` = %s
AND `Package` = %s
LIMIT 1
INSERT INTO `def_service` (
`servicecode`, `service`, `servicetype`, `description`,
`servicecachetime`, `remoteconnection`, `remoteconnection2`,
`remoteserviceconnection`, `remoteserviceconnection2`, `payloadtype`,
`package`, `ActionActive`
)
VALUES (
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
)
SELECT * FROM def_calculations
WHERE
`calculationgroup` = "{calculationname}"
AND `CalculationName` = "{calculationname}"
AND `package` = "{package_upper}"
UPDATE `def_calculations`
SET
`calculationgroup` = %s,
`calculationname` = %s,
`rank` = %s,
`calculationsql` = %s,
`description` = %s,
`hasresult` = %s,
`package` = %s
WHERE
`calculationgroup` = %s
AND `CalculationName` = %s
AND `package` = %s
LIMIT 1
INSERT INTO `def_calculations` (
`calculationgroup`, `calculationname`, `rank`, `calculationsql`,
`description`, `hasresult`, `package`, `version`, `lastUpdate`, calculationGuid, `active`
)
VALUES (
"{calculationgroup}","{calculationname}","{rank}","{sql}",
"{description}","{hasresult}","{package_upper}", 1.0, NOW(), UUID(), "Y"
);
delete from def_calculations
where calculationname = '{calculationname}'
and calculationgroup = '{calculationgroup}'
and package = "{package_upper}"
LIMIT 1
SELECT `calculationgroup`, `calculationname`, `rank`, `calculationsql`,
`description`, `hasresult` FROM def_calculations
WHERE
`calculationgroup` = "{calculationgroup}"
AND `package` = "{package_upper}"
DELETE FROM def_calculations
WHERE
`calculationgroup` = "{calculationgroup}"
AND `package` = "{package_upper}"