NQ0PUvW2

1CREATE TABLE IF NOT EXISTS `gallery` (
2    `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
3    `galleryId` INT UNSIGNED NOT NULL,
4    `createdAt` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
5    `name`      VARCHAR(64)  NULL,
6    PRIMARY KEY (`id`),
7    KEY `galleryId` (`galleryId`)
8)
9    ENGINE = InnoDB
10    DEFAULT CHARSET = `utf8`;
11
12CREATE TABLE IF NOT EXISTS `photo` (
13    `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
14    `publicId`  BINARY(16)   NOT NULL,
15    `galleryId` INT UNSIGNED NOT NULL,
16    `createdAt` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
17    PRIMARY KEY (`id`),
18    KEY `galleryId` (`galleryId`)
19)
20    ENGINE = InnoDB
21    DEFAULT CHARSET = `utf8`;
22
23
24CREATE TABLE IF NOT EXISTS `permission` (
25    `permissionOwnerId` BINARY(16)          NOT NULL,
26    `permission`        TINYINT    UNSIGNED NOT NULL,
27    `resourceOwnerId`   BINARY(16)          NOT NULL,
28    `resourceType`      TINYINT    UNSIGNED NOT NULL,
29    `resourceId`        INT        UNSIGNED NOT NULL,
30    PRIMARY KEY (`permissionOwnerId`, `permission`, `resourceOwnerId`, `resourceType`, `resourceId`)
31)
32    ENGINE = InnoDB
33    DEFAULT CHARSET = `utf8`;
34
35SELECT
36    p.*
37FROM `photo` AS `p`
38INNER JOIN `gallery` AS `g` ON `g`.`id` = `p`.`galleryId`
39INNER JOIN `permission`
40    ON `permission`.`permissionOwnerId` IN ('PERMISSION_OWNER_ID', 'ANY') -- PERMISSION_OWNER_ID - это id пользователя которым запрашиваем, а так же его групп
41        AND `permission`.`permission` IN('PERMISSION', 'ANY')
42        AND `permission`.`resourceOwnerId` IN ('RESOURCE_OWNER_ID', 'ANY')
43        AND `permission`.`resourceType` IN('GALLERY', 'ANY')
44        AND `permission`.`resourceId` IN (`g`.`id`, 'ANY');