CREATE TABLE IF NOT EXISTS `def_package_roles` (
`Package` VARCHAR(100) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Activity` VARCHAR(100) NOT NULL,
`Role` CHAR(1) NOT NULL,
`Email` VARCHAR(255) NOT NULL,
`Name` VARCHAR(255) DEFAULT NULL,
`Active` CHAR(1) DEFAULT 'Y',
PRIMARY KEY (`Package`, `Activity`, `Role`, `Email`),
KEY `idx_pkg_role_email` (`Email`),
KEY `idx_pkg_role_activity` (`Package`, `Activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
SELECT Activity, Role, Email, Name
FROM def_package_roles
WHERE Package = '{package}'
AND Active = 'Y'
ORDER BY Activity, Role, Email
SELECT Role, Email, Name
FROM def_package_roles
WHERE Package = '{package}'
AND Activity = '{activity}'
AND Active = 'Y'
ORDER BY Role, Email
SELECT Email
FROM def_package_roles
WHERE Package = '{package}'
AND Activity = '{activity}'
AND Role = '{role}'
AND Active = 'Y'
INSERT INTO def_package_roles
(Package, Activity, Role, Email, Name, Active)
VALUES
('{package}', '{activity}', '{role}',
'{email}', '{name}', 'Y')
ON DUPLICATE KEY UPDATE
Name = VALUES(Name),
Active = 'Y'
UPDATE def_package_roles
SET Active = 'N'
WHERE Package = '{package}'
AND Activity = '{activity}'
AND Role = '{role}'
AND Email = '{email}'