CREATE TABLE `def_simulation_table` (
`SimulationGuid` varchar(100) NOT NULL,
`Package` varchar(100) NOT NULL,
`Module` varchar(100) NOT NULL,
`TableName` varchar(150) NOT NULL,
`Operation` varchar(10) NOT NULL,
`TrackedAt` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`SimulationGuid`, `Module`, `TableName`, `Operation`),
KEY `idx_simulation_guid` (`SimulationGuid`),
KEY `idx_package` (`Package`),
KEY `idx_module` (`Module`),
KEY `idx_table_name` (`TableName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
INSERT IGNORE INTO def_simulation_table
(SimulationGuid, Package, Module, TableName, Operation)
VALUES
('{simulation_guid}', '{package}', '{module}', '{table_name}', '{operation}')
SELECT
Module,
TableName,
Operation,
COUNT(*) as Count
FROM def_simulation_table
WHERE SimulationGuid = '{simulation_guid}'
GROUP BY Module, TableName, Operation
ORDER BY Module, TableName, Operation
SELECT DISTINCT
TableName,
Operation
FROM def_simulation_table
WHERE SimulationGuid = '{simulation_guid}'
AND Module = '{module}'
ORDER BY TableName, Operation
SELECT DISTINCT
TableName,
Operation
FROM def_simulation_table
WHERE SimulationGuid = '{simulation_guid}'
ORDER BY TableName, Operation
DELETE FROM def_simulation_table
WHERE SimulationGuid = '{simulation_guid}'
SELECT
Package,
Module,
Operation,
COUNT(DISTINCT TableName) as UniqueTableCount,
COUNT(*) as TotalReferences
FROM def_simulation_table
WHERE SimulationGuid = '{simulation_guid}'
GROUP BY Package, Module, Operation
ORDER BY Package, Module, Operation