CREATE TABLE `data_ticket_task` (
`Guid` char(50) NOT NULL,
`TicketGuid` char(50) NOT NULL,
`TaskName` varchar(500) DEFAULT NULL,
`Description` text DEFAULT NULL,
`Status` enum('PENDING','IN_PROGRESS','DONE','CANCELLED') DEFAULT 'PENDING',
`AssignedTo` varchar(255) DEFAULT NULL,
`PullRequest` text DEFAULT NULL,
`Package` char(255) DEFAULT NULL,
`Rank` int(11) DEFAULT 0,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`UpdatedDate` datetime DEFAULT NULL,
`CompletedDate` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`),
KEY `idx_ticket_guid` (`TicketGuid`),
KEY `idx_status` (`Status`),
KEY `idx_ticket_status` (`TicketGuid`, `Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT *
FROM data_ticket_task
WHERE Guid = '{guid}'
INSERT INTO data_ticket_task
(Guid, TicketGuid, TaskName, Description,
Status, AssignedTo, PullRequest, Package,
Rank, CreatedDate)
VALUES
('{guid}', '{ticket_guid}', '{task_name}',
'{description}', 'PENDING', '{assigned_to}',
'{pull_request}', '{package}',
{rank}, now())
UPDATE data_ticket_task
SET TaskName = '{task_name}',
Description = '{description}',
AssignedTo = '{assigned_to}',
PullRequest = '{pull_request}',
Package = '{package}',
Rank = {rank}
WHERE Guid = '{guid}'
UPDATE data_ticket_task
SET Status = '{status}'
WHERE Guid = '{guid}'
UPDATE data_ticket_task
SET Status = 'DONE',
CompletedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket_task
SET Status = 'CANCELLED',
CompletedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket_task
SET Status = 'IN_PROGRESS'
WHERE Guid = '{guid}'
SELECT *
FROM data_ticket_task
WHERE TicketGuid = '{ticket_guid}'
ORDER BY Rank, CreatedDate
SELECT COUNT(*)
FROM data_ticket_task
WHERE TicketGuid = '{ticket_guid}'
AND Status IN ('PENDING', 'IN_PROGRESS')
SELECT COUNT(*)
FROM data_ticket_task
WHERE TicketGuid = '{ticket_guid}'
AND Status = 'DONE'
SELECT COALESCE(MAX(Rank), 0) + 10
FROM data_ticket_task
WHERE TicketGuid = '{ticket_guid}'