X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=database.sql;h=5a8cee23c4a88f50949da1cac4009a3d1e83722e;hb=5c8708f4c9cfe06bb0924a744f75a8b3bbc14296;hp=697f250fee392c29baf80a1e29267f8eb4af4182;hpb=1129e8f17d0e11f803965467abd3f2539495dae7;p=friendica.git diff --git a/database.sql b/database.sql index 697f250fee..5a8cee23c4 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2023.09-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1519 +-- DB_UPDATE_VERSION 1523 -- ------------------------------------------ @@ -190,7 +190,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `confirm` varbinary(383) COMMENT '', `poco` varbinary(383) COMMENT '', `writable` boolean NOT NULL DEFAULT '0' COMMENT '', - `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = false instead', + `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a group. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = false instead', `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = true instead', `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '', `site-pubkey` text COMMENT 'Deprecated', @@ -717,7 +717,7 @@ CREATE TABLE IF NOT EXISTS `group` ( `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', `visible` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the member list is not private', `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the circle has been deleted', - `cid` int unsigned COMMENT 'Contact id of forum. When this field is filled then the members are synced automatically.', + `cid` int unsigned COMMENT 'Contact id of group. When this field is filled then the members are synced automatically.', `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'human readable name of circle', PRIMARY KEY(`id`), INDEX `uid` (`uid`), @@ -1695,19 +1695,34 @@ CREATE TABLE IF NOT EXISTS `report` ( `uid` mediumint unsigned COMMENT 'Reporting user', `reporter-id` int unsigned COMMENT 'Reporting contact', `cid` int unsigned NOT NULL COMMENT 'Reported contact', + `gsid` int unsigned COMMENT 'Reported contact server', `comment` text COMMENT 'Report', - `category` varchar(20) COMMENT 'Category of the report (spam, violation, other)', - `rules` text COMMENT 'Violated rules', + `category-id` int unsigned NOT NULL DEFAULT 1 COMMENT 'Report category, one of Entity\Report::CATEGORY_*', `forward` boolean COMMENT 'Forward the report to the remote server', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `status` tinyint unsigned COMMENT 'Status of the report', + `public-remarks` text COMMENT 'Remarks shared with the reporter', + `private-remarks` text COMMENT 'Remarks shared with the moderation team', + `last-editor-uid` mediumint unsigned COMMENT 'Last editor user', + `assigned-uid` mediumint unsigned COMMENT 'Assigned moderator user', + `status` tinyint unsigned NOT NULL COMMENT 'Status of the report, one of Entity\Report::STATUS_*', + `resolution` tinyint unsigned COMMENT 'Resolution of the report, one of Entity\Report::RESOLUTION_*', + `created` datetime(6) NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `edited` datetime(6) COMMENT 'Last time the report has been edited', PRIMARY KEY(`id`), INDEX `uid` (`uid`), INDEX `cid` (`cid`), INDEX `reporter-id` (`reporter-id`), + INDEX `gsid` (`gsid`), + INDEX `last-editor-uid` (`last-editor-uid`), + INDEX `assigned-uid` (`assigned-uid`), + INDEX `status-resolution` (`status`,`resolution`), + INDEX `created` (`created`), + INDEX `edited` (`edited`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`reporter-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`last-editor-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`assigned-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; -- @@ -1721,7 +1736,18 @@ CREATE TABLE IF NOT EXISTS `report-post` ( INDEX `uri-id` (`uri-id`), FOREIGN KEY (`rid`) REFERENCES `report` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Individual posts attached to a moderation report'; + +-- +-- TABLE report-rule +-- +CREATE TABLE IF NOT EXISTS `report-rule` ( + `rid` int unsigned NOT NULL COMMENT 'Report id', + `line-id` int unsigned NOT NULL COMMENT 'Terms of service rule line number, may become invalid after a TOS change.', + `text` text NOT NULL COMMENT 'Terms of service rule text recorded at the time of the report', + PRIMARY KEY(`rid`,`line-id`), + FOREIGN KEY (`rid`) REFERENCES `report` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Terms of service rule lines relevant to a moderation report'; -- -- TABLE search @@ -1874,6 +1900,37 @@ CREATE VIEW `application-view` AS SELECT FROM `application-token` INNER JOIN `application` ON `application-token`.`application-id` = `application`.`id`; +-- +-- VIEW circle-member-view +-- +DROP VIEW IF EXISTS `circle-member-view`; +CREATE VIEW `circle-member-view` AS SELECT + `group_member`.`id` AS `id`, + `group`.`uid` AS `uid`, + `group_member`.`contact-id` AS `contact-id`, + `contact`.`uri-id` AS `contact-uri-id`, + `contact`.`url` AS `contact-link`, + `contact`.`addr` AS `contact-addr`, + `contact`.`name` AS `contact-name`, + `contact`.`nick` AS `contact-nick`, + `contact`.`thumb` AS `contact-avatar`, + `contact`.`network` AS `contact-network`, + `contact`.`blocked` AS `contact-blocked`, + `contact`.`hidden` AS `contact-hidden`, + `contact`.`readonly` AS `contact-readonly`, + `contact`.`archive` AS `contact-archive`, + `contact`.`pending` AS `contact-pending`, + `contact`.`self` AS `contact-self`, + `contact`.`rel` AS `contact-rel`, + `contact`.`contact-type` AS `contact-contact-type`, + `group_member`.`gid` AS `circle-id`, + `group`.`visible` AS `circle-visible`, + `group`.`deleted` AS `circle-deleted`, + `group`.`name` AS `circle-name` + FROM `group_member` + INNER JOIN `contact` ON `group_member`.`contact-id` = `contact`.`id` + INNER JOIN `group` ON `group_member`.`gid` = `group`.`id`; + -- -- VIEW post-user-view -- @@ -1968,6 +2025,7 @@ CREATE VIEW `post-user-view` AS SELECT `author`.`addr` AS `author-addr`, IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`, `author`.`nick` AS `author-nick`, + `author`.`alias` AS `author-alias`, IF (`contact`.`url` = `author`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `author`.`thumb`) AS `author-avatar`, `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, @@ -1980,6 +2038,7 @@ CREATE VIEW `post-user-view` AS SELECT `owner`.`addr` AS `owner-addr`, IF (`contact`.`url` = `owner`.`url` AND `contact`.`name` != '', `contact`.`name`, `owner`.`name`) AS `owner-name`, `owner`.`nick` AS `owner-nick`, + `owner`.`alias` AS `owner-alias`, IF (`contact`.`url` = `owner`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `owner`.`thumb`) AS `owner-avatar`, `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, @@ -1992,6 +2051,7 @@ CREATE VIEW `post-user-view` AS SELECT `causer`.`addr` AS `causer-addr`, `causer`.`name` AS `causer-name`, `causer`.`nick` AS `causer-nick`, + `causer`.`alias` AS `causer-alias`, `causer`.`thumb` AS `causer-avatar`, `causer`.`network` AS `causer-network`, `causer`.`blocked` AS `causer-blocked`, @@ -2145,6 +2205,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `author`.`addr` AS `author-addr`, IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`, `author`.`nick` AS `author-nick`, + `author`.`alias` AS `author-alias`, IF (`contact`.`url` = `author`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `author`.`thumb`) AS `author-avatar`, `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, @@ -2157,6 +2218,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `owner`.`addr` AS `owner-addr`, IF (`contact`.`url` = `owner`.`url` AND `contact`.`name` != '', `contact`.`name`, `owner`.`name`) AS `owner-name`, `owner`.`nick` AS `owner-nick`, + `owner`.`alias` AS `owner-alias`, IF (`contact`.`url` = `owner`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `owner`.`thumb`) AS `owner-avatar`, `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, @@ -2169,6 +2231,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `causer`.`addr` AS `causer-addr`, `causer`.`name` AS `causer-name`, `causer`.`nick` AS `causer-nick`, + `causer`.`alias` AS `causer-alias`, `causer`.`thumb` AS `causer-avatar`, `causer`.`network` AS `causer-network`, `causer`.`blocked` AS `causer-blocked`, @@ -2308,6 +2371,7 @@ CREATE VIEW `post-view` AS SELECT `author`.`addr` AS `author-addr`, `author`.`name` AS `author-name`, `author`.`nick` AS `author-nick`, + `author`.`alias` AS `author-alias`, `author`.`thumb` AS `author-avatar`, `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, @@ -2320,6 +2384,7 @@ CREATE VIEW `post-view` AS SELECT `owner`.`addr` AS `owner-addr`, `owner`.`name` AS `owner-name`, `owner`.`nick` AS `owner-nick`, + `owner`.`alias` AS `owner-alias`, `owner`.`thumb` AS `owner-avatar`, `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, @@ -2332,6 +2397,7 @@ CREATE VIEW `post-view` AS SELECT `causer`.`addr` AS `causer-addr`, `causer`.`name` AS `causer-name`, `causer`.`nick` AS `causer-nick`, + `causer`.`alias` AS `causer-alias`, `causer`.`thumb` AS `causer-avatar`, `causer`.`network` AS `causer-network`, `causer`.`blocked` AS `causer-blocked`, @@ -2448,6 +2514,7 @@ CREATE VIEW `post-thread-view` AS SELECT `author`.`addr` AS `author-addr`, `author`.`name` AS `author-name`, `author`.`nick` AS `author-nick`, + `author`.`alias` AS `author-alias`, `author`.`thumb` AS `author-avatar`, `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, @@ -2460,6 +2527,7 @@ CREATE VIEW `post-thread-view` AS SELECT `owner`.`addr` AS `owner-addr`, `owner`.`name` AS `owner-name`, `owner`.`nick` AS `owner-nick`, + `owner`.`alias` AS `owner-alias`, `owner`.`thumb` AS `owner-avatar`, `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, @@ -2472,6 +2540,7 @@ CREATE VIEW `post-thread-view` AS SELECT `causer`.`addr` AS `causer-addr`, `causer`.`name` AS `causer-name`, `causer`.`nick` AS `causer-nick`, + `causer`.`alias` AS `causer-alias`, `causer`.`thumb` AS `causer-avatar`, `causer`.`network` AS `causer-network`, `causer`.`blocked` AS `causer-blocked`, @@ -2598,13 +2667,15 @@ CREATE VIEW `network-item-view` AS SELECT `ownercontact`.`contact-type` AS `contact-type` FROM `post-user` INNER JOIN `post-thread-user` ON `post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` AND `post-thread-user`.`uid` = `post-user`.`uid` - INNER JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id` + STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id` + STRAIGHT_JOIN `contact` AS `authorcontact` ON `authorcontact`.`id` = `post-thread-user`.`author-id` + STRAIGHT_JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id` LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `post-thread-user`.`uid` AND `author`.`cid` = `post-thread-user`.`author-id` LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `post-thread-user`.`uid` AND `owner`.`cid` = `post-thread-user`.`owner-id` - INNER JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id` WHERE `post-user`.`visible` AND NOT `post-user`.`deleted` AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`) AND (`post-user`.`hidden` IS NULL OR NOT `post-user`.`hidden`) + AND NOT `authorcontact`.`blocked` AND NOT `ownercontact`.`blocked` AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`) AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`); @@ -2627,12 +2698,14 @@ CREATE VIEW `network-thread-view` AS SELECT FROM `post-thread-user` INNER JOIN `post-user` ON `post-user`.`id` = `post-thread-user`.`post-user-id` STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id` + STRAIGHT_JOIN `contact` AS `authorcontact` ON `authorcontact`.`id` = `post-thread-user`.`author-id` + STRAIGHT_JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id` LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `post-thread-user`.`uid` AND `author`.`cid` = `post-thread-user`.`author-id` LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `post-thread-user`.`uid` AND `owner`.`cid` = `post-thread-user`.`owner-id` - LEFT JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id` WHERE `post-user`.`visible` AND NOT `post-user`.`deleted` AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`) AND (`post-thread-user`.`hidden` IS NULL OR NOT `post-thread-user`.`hidden`) + AND NOT `authorcontact`.`blocked` AND NOT `ownercontact`.`blocked` AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`) AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`);