CREATE TABLE `def_transfer` (
`TransferCode` char(255) NOT NULL,
`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,
`TrackFiles` char(1) DEFAULT NULL,
`direction` char(255) DEFAULT NULL,
`Protocol` char(255) DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
`Company` char(255) DEFAULT NULL,
`Remoteconnection` char(255) DEFAULT NULL,
`localdir` char(255) DEFAULT NULL,
`filename` char(255) DEFAULT NULL,
`processeddir` char(255) DEFAULT NULL,
`Subblock` char(255) DEFAULT NULL,
`remotedir` char(255) DEFAULT NULL,
`PreSql` mediumtext DEFAULT NULL,
`ActionActive` char(1) DEFAULT 'Y',
`RepeatInterval` char(255) DEFAULT NULL,
`Overwrite` char(1) DEFAULT 'N',
`PostSql` 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 '',
`FileSelectStatusColumn` char(255) DEFAULT NULL,
`FileSelectIdColumn` char(255) DEFAULT NULL,
`FileSelectRemoteconnection` char(255) DEFAULT NULL,
`FileSelectTable` char(255) DEFAULT NULL,
`FileSelectSql` mediumtext DEFAULT NULL,
`FileSelectTargetDir` char(255) DEFAULT NULL,
PRIMARY KEY (
`TransferCode`, `Strategy_id`, `Block`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_Transferbulk` (
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` varchar(255) DEFAULT NULL COMMENT 'Module identifier for system organization',
`Transfercode` varchar(255) NOT NULL,
`SourceSchema` varchar(100) DEFAULT NULL,
`SourceTable` varchar(255) DEFAULT NULL,
`SourceQuery` longtext DEFAULT NULL COMMENT 'Optional direct source query for on-demand transfers',
`SourceRemoteconnection` varchar(255) DEFAULT '',
`TargetTable` varchar(255) DEFAULT NULL,
`TargetRemoteconnection` varchar(255) DEFAULT '',
`TransferGuid` varchar(100) DEFAULT 'guid',
`TransferFilter` varchar(255) DEFAULT '1=1',
`TransferSchedule` varchar(100) DEFAULT NULL COMMENT 'Cron expression for scheduled transfers',
`TransferMode` varchar(20) DEFAULT 'SUPPLEMENT' COMMENT 'SUPPLEMENT or REPLACE - determines how target data is updated',
`LastTransfer` datetime DEFAULT NULL,
`LastTransferVolume` int(11) DEFAULT 0,
`IncrementalField` varchar(100) DEFAULT NULL COMMENT 'Field to use for incremental transfers (e.g., UpdatedDate)',
`IncrementalMode` varchar(20) DEFAULT NULL COMMENT 'TIMESTAMP, SEQUENCE, or NULL for full transfers',
`LastIncrementalValue` varchar(255) DEFAULT NULL COMMENT 'Last processed value for incremental transfers',
`MaxRetries` int(11) DEFAULT 3 COMMENT 'Maximum retry attempts for failed transfers',
`RetryBackoffSeconds` int(11) DEFAULT 60 COMMENT 'Initial retry delay in seconds (exponential backoff)',
`RetryOnErrorTypes` varchar(255) DEFAULT 'NETWORK,TIMEOUT,LOCK' COMMENT 'Comma-separated error types to retry',
`EnableRowCountValidation` char(1) DEFAULT 'Y' COMMENT 'Validate row counts after transfer',
`EnableChecksumValidation` char(1) DEFAULT 'N' COMMENT 'Validate data checksums after transfer',
`Description` text DEFAULT NULL,
`Active` char(1) DEFAULT 'Y',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`UpdatedDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Package`, `Transfercode`),
KEY `idx_package` (`Package`),
KEY `idx_active` (`Active`),
KEY `idx_schedule` (`TransferSchedule`),
KEY `idx_last_transfer` (`LastTransfer`),
KEY `idx_incremental` (`IncrementalField`, `IncrementalMode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Data transfer bulk configuration and scheduling';
CREATE TABLE `def_RemoteConnections` (
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` varchar(255) DEFAULT NULL COMMENT 'Module identifier for system organization',
`Remote` varchar(255) NOT NULL,
`Remotetype` varchar(50) DEFAULT 'MYSQL' COMMENT 'MYSQL, MSSQL, MONGO, YAML',
`Remoteip` varchar(255) DEFAULT NULL,
`Remoteport` int(11) DEFAULT NULL,
`Remotedatabase` varchar(255) DEFAULT NULL,
`Remoteuser` varchar(255) DEFAULT NULL,
`Remotepassword` varchar(255) DEFAULT NULL,
`Smtpport` int(11) DEFAULT NULL COMMENT 'For backwards compatibility',
`Description` text DEFAULT NULL,
`Active` char(1) DEFAULT 'Y',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`UpdatedDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Package`, `Remote`),
KEY `idx_package` (`Package`),
KEY `idx_type` (`Remotetype`),
KEY `idx_active` (`Active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Remote database connection configurations';
CREATE TABLE IF NOT EXISTS `stage_transfer` (
`Guid` char(255) NOT NULL,
`TransferCode` char(50) NOT NULL,
`Strategy_id` char(25) NOT NULL,
`Status` char(50) DEFAULT NULL,
`StatusError` char(255) DEFAULT NULL,
`ResultCode` char(50) DEFAULT NULL,
`Response` longtext DEFAULT NULL,
`Result1` mediumtext DEFAULT NULL,
`Result2` mediumtext DEFAULT NULL,
`Result3` mediumtext DEFAULT NULL,
`Filename` char(255) DEFAULT NULL,
`SourceGuid` char(255) NOT NULL,
`ReturnGuid` char(255) NOT NULL DEFAULT '',
`ReturnPid` int(11) NOT NULL DEFAULT 0,
`Overwrite` char(1) DEFAULT NULL,
`RDG` int(11) DEFAULT NULL,
`LiveStatus` char(50) DEFAULT 'NOTLIVE',
`Param1` char(255) DEFAULT NULL,
`Param2` char(255) DEFAULT NULL,
`Param3` char(255) DEFAULT NULL,
`Param4` char(255) DEFAULT NULL,
`Param5` char(255) DEFAULT NULL,
`Param6` char(255) DEFAULT NULL,
`Param7` char(255) DEFAULT NULL,
`Param8` char(255) DEFAULT NULL,
`Param9` char(255) DEFAULT NULL,
`Thread` int(11) 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(),
`TimeThreaded` datetime DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `data_track_bulktransfer` (
`Package` varchar(100) NOT NULL DEFAULT '',
`TrackGuid` varchar(100) NOT NULL,
`Transfercode` varchar(255) NOT NULL,
`SourceTable` varchar(255) DEFAULT NULL,
`SourceRemoteconnection` varchar(255) DEFAULT NULL,
`SourceRemotetype` varchar(50) DEFAULT NULL COMMENT 'MYSQL, MSSQL, MONGO, YAML',
`TargetTable` varchar(255) DEFAULT NULL,
`TargetRemoteconnection` varchar(255) DEFAULT NULL,
`TargetRemotetype` varchar(50) DEFAULT NULL COMMENT 'MYSQL, MSSQL, MONGO, YAML',
`TransferMode` varchar(20) DEFAULT 'SUPPLEMENT' COMMENT 'SUPPLEMENT or REPLACE',
`TransferFilter` varchar(255) DEFAULT '1=1',
`TransferGuid` varchar(100) DEFAULT 'guid',
`StartTime` datetime DEFAULT NULL,
`EndTime` datetime DEFAULT NULL,
`DurationSeconds` decimal(10,2) DEFAULT NULL,
`RecordsTransferred` int(11) DEFAULT 0,
`RecordsDeleted` int(11) DEFAULT 0 COMMENT 'For REPLACE mode',
`Status` varchar(50) DEFAULT 'STARTED' COMMENT 'STARTED, SUCCESS, FAILED, PARTIAL',
`ErrorMessage` text DEFAULT NULL,
`ErrorDetails` text DEFAULT NULL,
`NotificationSent` char(1) DEFAULT 'N',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`UpdatedDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Package`, `TrackGuid`),
KEY `idx_package` (`Package`),
KEY `idx_transfercode` (`Transfercode`),
KEY `idx_status` (`Status`),
KEY `idx_start_time` (`StartTime`),
KEY `idx_end_time` (`EndTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Tracks all bulk transfer executions with success/failure status';
CREATE TABLE `data_transfer_progress` (
`Package` varchar(100) NOT NULL DEFAULT '',
`TrackGuid` varchar(100) NOT NULL,
`RecordsProcessed` int(11) DEFAULT 0,
`RecordsRemaining` int(11) DEFAULT 0,
`TotalRecords` int(11) DEFAULT 0,
`PercentComplete` decimal(5,2) DEFAULT 0.00,
`EstimatedTimeRemainingSeconds` int(11) DEFAULT NULL,
`CurrentBatchSize` int(11) DEFAULT 0,
`RecordsPerSecond` decimal(10,2) DEFAULT 0.00,
`BytesProcessed` bigint(20) DEFAULT 0,
`BytesPerSecond` decimal(15,2) DEFAULT 0.00,
`CurrentPhase` varchar(50) DEFAULT 'INITIALIZING' COMMENT 'INITIALIZING, READING, WRITING, VALIDATING, CLEANUP',
`LastUpdateTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Package`, `TrackGuid`),
KEY `idx_track_guid` (`TrackGuid`),
KEY `idx_last_update` (`LastUpdateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Real-time progress tracking for active transfers';
CREATE TABLE `data_transfer_validation` (
`Package` varchar(100) NOT NULL DEFAULT '',
`ValidationGuid` varchar(100) NOT NULL,
`TrackGuid` varchar(100) NOT NULL,
`Transfercode` varchar(255) NOT NULL,
`ValidationType` varchar(50) NOT NULL COMMENT 'ROW_COUNT, CHECKSUM, SCHEMA, CUSTOM_SQL',
`ValidationStage` varchar(20) NOT NULL COMMENT 'PRE_TRANSFER, POST_TRANSFER',
`ValidationStatus` varchar(20) DEFAULT 'PASSED' COMMENT 'PASSED, FAILED, WARNING, SKIPPED',
`SourceValue` text DEFAULT NULL,
`TargetValue` text DEFAULT NULL,
`ExpectedValue` text DEFAULT NULL,
`ActualValue` text DEFAULT NULL,
`ValidationMessage` text DEFAULT NULL,
`ValidationQuery` text DEFAULT NULL,
`ValidationTime` datetime DEFAULT CURRENT_TIMESTAMP,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Package`, `ValidationGuid`),
KEY `idx_track_guid` (`TrackGuid`),
KEY `idx_transfercode` (`Transfercode`),
KEY `idx_status` (`ValidationStatus`),
KEY `idx_type_stage` (`ValidationType`, `ValidationStage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Transfer validation results and data quality checks';
INSERT INTO data_track_bulktransfer
(Package, TrackGuid, Transfercode, SourceTable,
SourceRemoteconnection, SourceRemotetype,
TargetTable, TargetRemoteconnection, TargetRemotetype,
TransferMode, StartTime, Status)
VALUES
('{package}', '{track_guid}', '{transfer_code}',
'{source_table}',
'{source_remote_connection}',
'{source_remote_type}',
'{target_table}',
'{target_remote_connection}',
'{target_remote_type}',
'{transfer_mode}', NOW(), 'STARTED')
UPDATE data_track_bulktransfer
SET EndTime = NOW(),
DurationSeconds = TIMESTAMPDIFF(SECOND, StartTime, NOW()),
RecordsTransferred = {records_transferred},
RecordsDeleted = {records_deleted},
Status = '{status}',
ErrorMessage = {error_message},
ErrorDetails = {error_details},
NotificationSent = 'Y'
WHERE TrackGuid = '{track_guid}'
SELECT Transfercode, SourceTable, TargetTable,
TransferMode, DurationSeconds,
SourceRemotetype, TargetRemotetype
FROM data_track_bulktransfer
WHERE TrackGuid = '{track_guid}'
SELECT database()
SELECT Transfercode,
SourceTable,
coalesce(SourceQuery,'') AS SourceQuery,
coalesce(SourceRemoteconnection,'') AS SourceRemoteconnection,
TargetTable,
coalesce(TargetRemoteconnection,'') AS TargetRemoteconnection,
TransferGuid, TransferFilter,
coalesce(TransferSchedule,'') AS TransferSchedule,
coalesce(TransferMode,'SUPPLEMENT') AS TransferMode
FROM def_Transferbulk
WHERE Package = '{package}'
AND SourceSchema = '{schema}'
SELECT Transfercode,
SourceTable,
coalesce(SourceQuery,'') AS SourceQuery,
coalesce(Sourceremote_connection,'') AS SourceRemoteconnection,
TargetTable,
coalesce(Targetremote_connection,'') AS TargetRemoteconnection,
TransferGuid, TransferFilter,
coalesce(TransferSchedule,'') AS TransferSchedule,
coalesce(TransferMode,'SUPPLEMENT') AS TransferMode
FROM def_Transferbulk
WHERE Package = '{package}'
AND SourceSchema = '{schema}'
SELECT Transfercode,
SourceTable,
coalesce(SourceQuery,'') AS SourceQuery,
coalesce(SourceRemoteconnection,'') AS SourceRemoteconnection,
TargetTable,
coalesce(TargetRemoteconnection,'') AS TargetRemoteconnection,
TransferGuid, TransferFilter, SourceSchema,
coalesce(TransferSchedule,'') AS TransferSchedule,
coalesce(TransferMode,'SUPPLEMENT') AS TransferMode
FROM def_Transferbulk
WHERE Package = '{package}'
AND transfercode = '{transfercode}'
SELECT Transfercode,
SourceTable,
coalesce(SourceQuery,'') AS SourceQuery,
coalesce(Sourceremote_connection,'') AS SourceRemoteconnection,
TargetTable,
coalesce(Targetremote_connection,'') AS TargetRemoteconnection,
TransferGuid, TransferFilter, SourceSchema,
coalesce(TransferSchedule,'') AS TransferSchedule,
coalesce(TransferMode,'SUPPLEMENT') AS TransferMode
FROM def_Transferbulk
WHERE Package = '{package}'
AND transfercode = '{transfercode}'
UPDATE def_Transferbulk SET
LastTransfer = NOW(),
LastTransferVolume = '{count}'
WHERE Package = '{package}'
AND SourceSchema = '{schema}'
AND Transfercode = '{transfer_code}'
UPDATE def_Transferbulk SET
LastTransfer = NOW(),
LastTransferVolume = '{count}'
WHERE Package = '{package}'
AND Transfercode = '{transfer_code}'
SELECT {filter_guid} FROM {table_source}
WHERE {filter_query}
LIMIT {buffer_size} OFFSET {offset}
SELECT *
FROM {table_source}
WHERE {filter_guid} = '{guid}'
SELECT {filter_guid} FROM [{schema}].[dbo].[{table_source}]
WHERE {filter_query}
ORDER BY {filter_guid}
OFFSET {offset} ROWS
FETCH NEXT {buffer_size} ROWS ONLY
SELECT *
FROM {table_source}
WHERE {filter_guid} = '{guid}'
INSERT IGNORE INTO {table_target} ({fields})
VALUES ({values})
INSERT INTO {table_target} ({fields})
SELECT {values}
WHERE NOT EXISTS (
SELECT 1
FROM {table_target} t
WHERE {filter_guid} = '{guid}'
)
SELECT COUNT(*) FROM {table_target}
WHERE TransferDate < '{transfer_start_time}'
OR TransferDate IS NULL
DELETE FROM {table_target}
WHERE TransferDate < '{transfer_start_time}'
OR TransferDate IS NULL
SELECT COUNT(*) FROM {table_target}
WHERE TransferDate < '{transfer_start_time}'
OR TransferDate IS NULL
DELETE FROM {table_target}
WHERE TransferDate < '{transfer_start_time}'
OR TransferDate IS NULL
SELECT IncrementalField, IncrementalMode, LastIncrementalValue,
MaxRetries, RetryBackoffSeconds, RetryOnErrorTypes,
EnableRowCountValidation, EnableChecksumValidation
FROM def_Transferbulk
WHERE Package = '{package}'
AND Transfercode = '{transfer_code}'
UPDATE def_Transferbulk
SET LastIncrementalValue = '{incremental_value}',
UpdatedDate = NOW()
WHERE Package = '{package}'
AND Transfercode = '{transfer_code}'
SELECT {filter_guid} FROM {table_source}
WHERE {filter_query}
AND {incremental_field} > '{last_value}'
ORDER BY {incremental_field}
LIMIT {buffer_size} OFFSET {offset}
SELECT MAX({incremental_field}) as max_value
FROM {table_source}
WHERE {filter_query}
INSERT INTO data_transfer_progress
(Package, TrackGuid, TotalRecords, CurrentPhase)
VALUES ('{package}', '{track_guid}', {total_records}, '{phase}')
ON DUPLICATE KEY UPDATE
TotalRecords = {total_records},
CurrentPhase = '{phase}',
LastUpdateTime = NOW()
UPDATE data_transfer_progress
SET RecordsProcessed = {records_processed},
RecordsRemaining = {records_remaining},
PercentComplete = {percent_complete},
EstimatedTimeRemainingSeconds = {time_remaining},
CurrentBatchSize = {batch_size},
RecordsPerSecond = {records_per_second},
BytesProcessed = {bytes_processed},
BytesPerSecond = {bytes_per_second},
CurrentPhase = '{phase}',
LastUpdateTime = NOW()
WHERE TrackGuid = '{track_guid}'
SELECT RecordsProcessed, RecordsRemaining, TotalRecords,
PercentComplete, EstimatedTimeRemainingSeconds,
RecordsPerSecond, CurrentPhase, LastUpdateTime
FROM data_transfer_progress
WHERE TrackGuid = '{track_guid}'
INSERT INTO data_transfer_validation
(Package, ValidationGuid, TrackGuid, Transfercode,
ValidationType, ValidationStage, ValidationStatus,
SourceValue, TargetValue, ValidationMessage, ValidationQuery)
VALUES
('{package}', '{validation_guid}', '{track_guid}', '{transfer_code}',
'{validation_type}', '{validation_stage}', '{status}',
{source_value}, {target_value}, {message}, {query})
SELECT COUNT(*) as record_count
FROM {table_source}
WHERE {filter_query}
SELECT COUNT(*) as record_count
FROM {table_target}
SELECT MD5(GROUP_CONCAT({guid_field} ORDER BY {guid_field})) as checksum
FROM {table_source}
WHERE {filter_query}
SELECT MD5(GROUP_CONCAT({guid_field} ORDER BY {guid_field})) as checksum
FROM {table_target}