CREATE TABLE `def_service` (
`ServiceCode` char(255) NOT NULL DEFAULT '',
`Strategy_id` char(25) NOT NULL DEFAULT '-1',
`Block` char(36) NOT NULL DEFAULT 'ADHOC',
`Package` char(36) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`FinalOutputTable` char(255) DEFAULT NULL,
`Service` char(255) DEFAULT '',
`ThreadCount` tinyint(4) DEFAULT 0,
`ServiceCacheTime` int(11) DEFAULT 60,
`RemoteConnection` char(255) DEFAULT '',
`Async_strategy` char(1) DEFAULT 'Y',
`Description` char(255) DEFAULT '',
`RemoteConnection2` char(255) DEFAULT '',
`RemoteServiceConnection2` char(255) DEFAULT '',
`Company` char(255) DEFAULT 'TECHNOCORE',
`PayloadKey` char(50) DEFAULT '',
`Fire_strategyId` int(11) DEFAULT 0,
`PayloadType` char(50) DEFAULT 'JSON',
`PreSql` mediumtext DEFAULT NULL,
`ActionActive` char(1) DEFAULT 'Y',
`RescheduleInterval` char(255) DEFAULT '',
`PayloadTemplate` mediumtext DEFAULT NULL,
`ExternalArchive` char(255) DEFAULT '',
`Companies` mediumtext DEFAULT NULL,
`SetSql` mediumtext DEFAULT NULL,
`PostSql` mediumtext DEFAULT NULL,
`DataKey` char(255) DEFAULT '',
`DataTable` char(255) DEFAULT NULL,
`ExternalFolder` char(255) DEFAULT '',
`ServiceType` char(15) DEFAULT '',
`UserGroups` mediumtext DEFAULT NULL,
`Buildstructure` char(255) DEFAULT '',
`PayloadTable` char(255) DEFAULT '',
`RemoteServiceConnection` char(255) DEFAULT '',
`PayloadSlices` mediumtext DEFAULT NULL,
`PayloadSql` mediumtext DEFAULT NULL,
`Subblock` char(255) DEFAULT NULL,
`serviceDescription` char(255) DEFAULT NULL,
`ExternalStage` char(15) DEFAULT '',
`Virtual` char(255) DEFAULT '',
`IsTest` char(255) DEFAULT '',
`Documentpdf` char(255) DEFAULT '',
`CancelUrl` mediumtext DEFAULT NULL,
`ErrorUrl` mediumtext DEFAULT NULL,
`SuccessUrl` mediumtext DEFAULT NULL,
`NotifyUrl` mediumtext DEFAULT NULL,
`CleanupSql` mediumtext DEFAULT NULL,
`VirtualResponse` mediumtext DEFAULT NULL,
`Result1Col` char(255) DEFAULT '',
`Result2Col` char(255) DEFAULT '',
`Result3Col` char(255) DEFAULT '',
`Result4Col` char(255) DEFAULT '',
`Result5Col` char(255) DEFAULT '',
`Result6Col` char(255) DEFAULT '',
`Result7Col` char(255) DEFAULT '',
`Result8Col` char(255) DEFAULT '',
`Result9Col` char(255) DEFAULT '',
`Sourceguidcolumn` char(255) DEFAULT NULL,
`OtherUrl` mediumtext DEFAULT NULL,
`DataModel` varchar(255) DEFAULT NULL,
PRIMARY KEY (
`ServiceCode`, `Strategy_id`, `Block`
),
KEY `INDEX` (`ServiceCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_service_parameters` (
`ServiceCode` char(255) NOT NULL DEFAULT '',
`Parameter` char(255) NOT NULL DEFAULT '',
`Block` char(36) NOT NULL DEFAULT 'ADHOC',
`Strategy_id` char(25) NOT NULL DEFAULT '-1',
`Package` char(36) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`NormalParameter` char(255) DEFAULT NULL,
`Direction` char(4) DEFAULT NULL,
`ReturnExtractSql` text DEFAULT NULL,
`ValueType` char(255) DEFAULT NULL,
`Required` char(255) DEFAULT NULL,
`Rank` int(11) DEFAULT 0,
`BloomExtractSql` text DEFAULT NULL,
`ReturnColNo` char(3) DEFAULT NULL,
`ParameterSubBlock` char(255) DEFAULT NULL,
`ParameterSubSubBlock` char(255) DEFAULT NULL,
`ParameterBlock` char(255) DEFAULT NULL,
`ParameterSubSubSubBlock` char(255) DEFAULT NULL,
PRIMARY KEY (
`ServiceCode`, `Parameter`,
`Block`, `Strategy_id`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_service` (
`Guid` char(255) NOT NULL DEFAULT '0',
`ServiceCode` char(255) NOT NULL DEFAULT '0',
`Package` char(255) DEFAULT NULL,
`Strategy_id` char(25) NOT NULL,
`Status` char(50) DEFAULT '',
`StatusError` char(255) DEFAULT NULL,
`ResultCode` char(10) DEFAULT '',
`Response` mediumtext DEFAULT NULL,
`Collated` char(1) DEFAULT 'N',
`Version` char(255) DEFAULT '',
`SendPayload` char(1) DEFAULT '',
`Payload` longtext DEFAULT NULL,
`Webcall_id` char(255) DEFAULT '',
`Calllog` mediumtext DEFAULT NULL,
`Weblog` mediumtext DEFAULT NULL,
`Param1` mediumtext DEFAULT NULL,
`Param2` mediumtext DEFAULT NULL,
`Param3` mediumtext DEFAULT NULL,
`Param4` mediumtext DEFAULT NULL,
`Param5` mediumtext DEFAULT NULL,
`Param6` mediumtext DEFAULT NULL,
`Param7` mediumtext DEFAULT NULL,
`Param8` mediumtext DEFAULT NULL,
`Param9` mediumtext DEFAULT NULL,
`Result1` mediumtext DEFAULT NULL,
`Result2` mediumtext DEFAULT NULL,
`Result3` mediumtext DEFAULT NULL,
`Result4` mediumtext DEFAULT NULL,
`Result5` mediumtext DEFAULT NULL,
`Result6` mediumtext DEFAULT NULL,
`Result7` mediumtext DEFAULT NULL,
`Result8` mediumtext DEFAULT NULL,
`Result9` mediumtext DEFAULT NULL,
`SourceGuid` char(255) DEFAULT NULL,
`ReturnGuid` char(255) NOT NULL DEFAULT '',
`ReturnPid` int(11) NOT NULL DEFAULT 0,
`Fire_strategyId` int(11) NOT NULL DEFAULT 0,
`Async_strategy` char(1) NOT NULL DEFAULT 'Y',
`LiveStatus` char(50) DEFAULT 'NOTLIVE',
`LastTouch` datetime DEFAULT NULL,
`Step_id` int(11) DEFAULT 0,
`RDG` int(11) DEFAULT NULL,
`Thread` tinyint(4) DEFAULT 0,
`WorkerPid` int(11) NOT NULL DEFAULT 0,
`DispatcherName` char(255) DEFAULT NULL,
`DispatcherPid` int(11) NOT NULL DEFAULT 0,
`Instance` char(255) DEFAULT NULL,
`Nodename` char(255) DEFAULT NULL,
`Checkoutname` char(255) DEFAULT NULL,
`CPULoad` int(11) DEFAULT 0,
`MemoryLoad` int(11) DEFAULT 0,
`DiskLoad` int(11) DEFAULT 0,
`TimeScheduled` datetime DEFAULT NULL,
`TimeAdded` timestamp NULL DEFAULT current_timestamp(),
`TimeEntered` datetime DEFAULT current_timestamp(),
`TimeResheduled` int(11) DEFAULT 0,
`TimeThreaded` datetime DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`),
KEY `idx_status` (`Status`),
KEY `idx_timescheduled` (`TimeScheduled`),
KEY `idx_timecompleted` (`TimeCompleted`),
KEY `idx_servicecode` (`ServiceCode`),
KEY `idx_thread` (`Thread`),
KEY `idx_strategy_id` (`Strategy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_servicetype` (
`ServiceType` CHAR(255) NOT NULL DEFAULT '',
`Description` CHAR(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_service_monitor` (
`DateTimeSTAMP` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Datestamp` date DEFAULT NULL,
`Timestamp` time DEFAULT NULL,
`Processed` varchar(13) DEFAULT NULL,
`STATUS` char(50) DEFAULT NULL,
`Thread` tinyint(4) DEFAULT NULL,
`Max Time to process` bigint(21) DEFAULT NULL,
`New Records L1H` decimal(22,0) DEFAULT NULL,
`Not processed >= 15m` decimal(22,0) DEFAULT NULL,
`Processed <= 1m` decimal(22,0) DEFAULT NULL,
`Processed > 1m` decimal(22,0) DEFAULT NULL,
`Processed > 15m` decimal(22,0) DEFAULT NULL,
`Processed > 30m` decimal(22,0) DEFAULT NULL,
`Processed > 45m` decimal(22,0) DEFAULT NULL,
`Processed > 1H` decimal(22,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
INSERT INTO def_servicetype
(ServiceType, Description)
VALUES ('{service_type}', '{service_type}')
INSERT INTO def_service
(ServiceCode, ServiceType,
RemoteServiceConnection, Description)
VALUES
('{service_type_upper}', 'SERVICE',
'{service_type_upper}', '{service_type_title}')
SELECT D.*
FROM def_{this_service} AS D
WHERE D.ServiceCode = '{service_code}'
AND Package IN
('{package}', '{archetype}',
'core', 'hyperion')
SELECT RemoteUrl, RemoteIp, UserName,
Sshport, RemotePassword,
LocalDir, RemoteDir,
Serviceid, Vendorid, Campaignid
FROM Def_RemoteConnections
WHERE Remote LIKE '{remote_connection}'
AND IFNULL(Package, 'CORE') = '{package}'
SELECT DISTINCT D.ServiceCode,
D.Presql, D.remoteconnection
FROM def_service AS D
WHERE LENGTH(D.PreSql) > 0
AND D.ServiceCode = '{service_code}'
AND Package IN
('{package}', '{archetype}',
'core', 'hyperion')
SELECT D.Postsql, D.remoteconnection
FROM def_service AS D
WHERE LENGTH(D.PostSql) > 0
AND D.ServiceCode = '{service_code}'
AND Package IN
('{package}', '{archetype}',
'core', 'hyperion')
UPDATE stage_{this_service}
SET Status = 'LAUNCH',
Timeentered = NOW(),
Timethreaded = NOW(),
Payload = '', Weblog = '',
Response = '', Resultcode = '',
Result1 = '', Result2 = '',
Result3 = ''
WHERE Guid = '{guid}'
UPDATE stage_{this_service}
SET Status = ''
WHERE Guid = '{guid}'
INSERT INTO stage_{this_service}
(Guid, Package, ServiceCode, Instance)
VALUES
('{guid}', '{package}',
'{service_code}', 'MQ')
SELECT Service FROM def_service
WHERE Package = '{package}'
AND ServiceCode = '{service_code}'