CREATE TABLE `def_dataimport` (
`DataimportCode` varchar(255) NOT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Description` varchar(255) DEFAULT '',
`Notes` text DEFAULT NULL,
`Directory` varchar(255) DEFAULT NULL,
`Filename` varchar(255) DEFAULT NULL,
`ImportType` varchar(50) DEFAULT NULL,
`Tablename` varchar(255) DEFAULT NULL,
`Delimiter` varchar(10) DEFAULT NULL,
`Separator` varchar(10) DEFAULT NULL,
`Quotechar` varchar(10) DEFAULT NULL,
`Encoding` varchar(100) DEFAULT NULL,
`SheetName` varchar(255) DEFAULT NULL,
`SheetNumber` int(11) DEFAULT 1,
`TriggerMask` varchar(255) DEFAULT NULL,
`Overwrite` char(1) DEFAULT 'N',
`Archive` char(1) DEFAULT 'Y',
`Normalize` char(1) DEFAULT 'Y',
`ProcessedDir` varchar(255) DEFAULT NULL,
`FileHeaders` text DEFAULT NULL,
`ExtraCols` text DEFAULT NULL,
`ExtraKeys` text DEFAULT NULL,
`RemoteConnection` varchar(255) DEFAULT NULL,
`RelatedImports` text DEFAULT NULL,
`MaxBlockSize` int(11) DEFAULT NULL,
`Presql` mediumtext DEFAULT NULL,
`Postsql` mediumtext DEFAULT NULL,
`WorkflowName` varchar(255) DEFAULT NULL,
`WorkflowFail` varchar(255) DEFAULT NULL,
`NotifyCode` varchar(255) DEFAULT NULL,
`parameter_1` varchar(255) DEFAULT NULL,
`parameter_2` varchar(255) DEFAULT NULL,
`parameter_3` varchar(255) DEFAULT NULL,
`parameter_4` varchar(255) DEFAULT NULL,
`parameter_5` varchar(255) DEFAULT NULL,
`parameter_6` varchar(255) DEFAULT NULL,
`parameter_7` varchar(255) DEFAULT NULL,
`parameter_8` varchar(255) DEFAULT NULL,
`parameter_9` varchar(255) DEFAULT NULL,
`expectedImportCount` int(11) DEFAULT 0,
`lastImportCount` int(11) DEFAULT 0,
`actionactive` char(1) DEFAULT 'Y',
PRIMARY KEY (`DataimportCode`, `Package`),
KEY `idx_package` (`Package`),
KEY `idx_directory` (`Directory`),
KEY `idx_active` (`actionactive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_dataimport` (
`guid` varchar(255) NOT NULL,
`DataimportCode` varchar(100) NOT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Status` varchar(50) DEFAULT 'QUEUED',
`Filename` varchar(255) DEFAULT NULL,
`FileGuid` varchar(255) DEFAULT NULL,
`RowCount` int(11) DEFAULT 0,
`ErrorMessage` text DEFAULT NULL,
`TimeAdded` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`TimeStarted` datetime DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
PRIMARY KEY (`guid`),
KEY `idx_dataimportcode` (`DataimportCode`),
KEY `idx_status` (`Status`),
KEY `idx_package` (`Package`),
KEY `idx_fileguided` (`FileGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_dataimport_metric` (
`DataimportCode` varchar(255) NOT NULL COMMENT 'Import configuration code',
`Package` varchar(100) NOT NULL COMMENT 'Package identifier',
`Module` VARCHAR(255) DEFAULT NULL,
`AxionImportTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of the import execution',
`AxionImportGuid` varchar(36) NOT NULL COMMENT 'Unique GUID for this import run',
`Filename` varchar(255) DEFAULT NULL COMMENT 'Name of the file that was imported',
`FileSizeMB` decimal(12,2) DEFAULT NULL COMMENT 'Size of the file in megabytes',
`FileHash` varchar(32) DEFAULT NULL COMMENT 'MD5 hash of the imported file',
`RowsImported` int(11) DEFAULT 0 COMMENT 'Number of rows successfully imported',
`ElapsedSeconds` decimal(10,3) DEFAULT NULL COMMENT 'Time taken for import in seconds',
`RowsPerSecond` decimal(12,2) DEFAULT NULL COMMENT 'Import throughput (calculated)',
`MemoryUsageMB` decimal(12,2) DEFAULT NULL COMMENT 'Peak memory usage during import in megabytes',
`ImportSuccess` char(1) DEFAULT 'N' COMMENT 'Y/N - Whether import succeeded',
`ErrorMessage` text DEFAULT NULL COMMENT 'Error message if import failed',
`NormalizationSkipped` char(1) DEFAULT 'N' COMMENT 'Y/N - Whether normalization was skipped (large file)',
`TableName` varchar(255) DEFAULT NULL COMMENT 'Target table name for the import',
PRIMARY KEY (`DataimportCode`, `Package`, `AxionImportTime`, `AxionImportGuid`),
KEY `idx_import_time` (`AxionImportTime`),
KEY `idx_success` (`ImportSuccess`),
KEY `idx_dataimport_recent` (`DataimportCode`, `Package`, `AxionImportTime`),
KEY `idx_dataimport_lookup` (`DataimportCode`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Performance metrics for data import executions';
SELECT TriggerMask
FROM def_dataimport
WHERE DataimportCode = '{dataimport_code}'
AND Package IN ('CORE', 'ADHOC', '{package}')
AND actionactive = 'Y'
ORDER BY CASE
WHEN Package = '{package}' THEN 0
WHEN Package = 'CORE' THEN 1
WHEN Package = 'ADHOC' THEN 2
ELSE 3
END
LIMIT 1
SELECT *
FROM `{def_table}`
WHERE DataimportCode = '{dataimport_code}'
AND Package IN ('CORE', 'ADHOC', '{package}')
AND actionactive = 'Y'
ORDER BY CASE
WHEN Package = '{package}' THEN 0
WHEN Package = 'CORE' THEN 1
WHEN Package = 'ADHOC' THEN 2
ELSE 3
END
LIMIT 1
SELECT Directory, Filename, DataimportCode
FROM def_dataimport
WHERE Package = '{package}'
AND Directory IS NOT NULL
AND Directory != ''
AND actionactive = 'Y'
SELECT DataimportCode, WorkflowName, Filename, Directory
FROM def_dataimport
WHERE Package IN ('CORE', 'ADHOC', '{package}')
AND actionactive = 'Y'
ORDER BY DataimportCode
SELECT COUNT(*)
FROM def_workflow
WHERE WorkflowName = '{workflow_name}'
AND Package = '{package}'
UPDATE stage_dataimport
SET Status = 'DONE',
TimeCompleted = NOW()
WHERE guid = '{guid}'
UPDATE def_dataimport
SET expectedImportCount = {line_count}
WHERE DataimportCode = '{dataimport_code}'
UPDATE def_dataimport
SET lastImportCount = {row_count}
WHERE DataimportCode = '{dataimport_code}'
SHOW TABLES LIKE 'data_import_%'
SELECT COUNT(*) FROM `{build_table}`
CREATE TABLE `{table_name}` AS
SELECT * FROM `{build_table}`
INSERT IGNORE INTO `{table_name}` ({column_list})
SELECT {column_list} FROM `{build_table}`
DROP TABLE IF EXISTS `{table_name}`
ALTER TABLE {table_name} ADD KEY {column_name}Idx({column_name}(255))
CREATE TABLE `{build_table}` (
{column_definitions}
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
INSERT INTO {build_table} ({column_list}) VALUES ({placeholders})