CREATE TABLE `def_ticket_template` (
`Guid` char(50) NOT NULL,
`TemplateName` varchar(255) NOT NULL,
`Package` char(255) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text DEFAULT NULL,
`Priority` enum('CRITICAL','HIGH','MEDIUM','LOW','INFO') DEFAULT 'MEDIUM',
`TicketType` enum('USER','INTERNAL','BUG','FEATURE') DEFAULT 'USER',
`Active` char(2) DEFAULT 'Y',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Guid`),
UNIQUE KEY `idx_name_package` (`TemplateName`, `Package`),
KEY `idx_package` (`Package`),
KEY `idx_active` (`Active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_ticket_template_task` (
`Guid` char(50) NOT NULL,
`TemplateGuid` char(50) NOT NULL,
`TaskName` varchar(500) DEFAULT NULL,
`Description` text DEFAULT NULL,
`Rank` int(11) DEFAULT 0,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Guid`),
KEY `idx_template_guid` (`TemplateGuid`),
KEY `idx_rank` (`Rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT *
FROM def_ticket_template
WHERE TemplateName = '{template_name}'
AND Package = '{package}'
SELECT *
FROM def_ticket_template
WHERE Guid = '{guid}'
INSERT INTO def_ticket_template
(Guid, TemplateName, Package, Subject,
Description, Priority, TicketType,
Active, CreatedDate)
VALUES
('{guid}', '{template_name}', '{package}',
'{subject}', '{description}',
'{priority}', '{ticket_type}',
'Y', now())
SELECT *
FROM def_ticket_template
WHERE Package = '{package}'
AND Active = 'Y'
ORDER BY TemplateName
INSERT INTO def_ticket_template_task
(Guid, TemplateGuid, TaskName,
Description, Rank, CreatedDate)
VALUES
('{guid}', '{template_guid}',
'{task_name}', '{description}',
{rank}, now())
SELECT *
FROM def_ticket_template_task
WHERE TemplateGuid = '{template_guid}'
ORDER BY Rank, CreatedDate