X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=database.sql;h=11fbd514e754cff3efa8a915ec9a1978d71f7f30;hb=667f384bfc3a42b51f5dd2f8d7882e20d6ad97d2;hp=5b6f1695c389c0c3bdc3cd933349e87ba9bcb5ea;hpb=76de49a25cd08d63b08b3352e31a1ed0f3145603;p=friendica.git diff --git a/database.sql b/database.sql index 5b6f1695c3..11fbd514e7 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2023.09-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1522 +-- DB_UPDATE_VERSION 1529 -- ------------------------------------------ @@ -101,6 +101,19 @@ CREATE TABLE IF NOT EXISTS `user` ( FOREIGN KEY (`parent-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='The local users'; +-- +-- TABLE user-gserver +-- +CREATE TABLE IF NOT EXISTS `user-gserver` ( + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', + `gsid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Gserver id', + `ignored` boolean NOT NULL DEFAULT '0' COMMENT 'server accounts are ignored for the user', + PRIMARY KEY(`uid`,`gsid`), + INDEX `gsid` (`gsid`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User settings about remote servers'; + -- -- TABLE item-uri -- @@ -160,8 +173,8 @@ CREATE TABLE IF NOT EXISTS `contact` ( `archive` boolean NOT NULL DEFAULT '0' COMMENT '', `unsearchable` boolean NOT NULL DEFAULT '0' COMMENT 'Contact prefers to not be searchable', `sensitive` boolean NOT NULL DEFAULT '0' COMMENT 'Contact posts sensitive content', - `baseurl` varbinary(383) DEFAULT '' COMMENT 'baseurl of the contact', - `gsid` int unsigned COMMENT 'Global Server ID', + `baseurl` varbinary(383) DEFAULT '' COMMENT 'baseurl of the contact from the gserver record, can be missing', + `gsid` int unsigned COMMENT 'Global Server ID, can be missing', `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '', `reason` text COMMENT '', `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self', @@ -1586,7 +1599,7 @@ CREATE TABLE IF NOT EXISTS `profile` ( `profile-name` varchar(255) COMMENT 'Deprecated', `is-default` boolean COMMENT 'Deprecated', `hide-friends` boolean NOT NULL DEFAULT '0' COMMENT 'Hide friend list from viewers of this profile', - `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Unused in favor of user.username', `pdesc` varchar(255) COMMENT 'Deprecated', `dob` varchar(32) NOT NULL DEFAULT '0000-00-00' COMMENT 'Day of birth', `address` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -1695,7 +1708,7 @@ 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 NOT NULL COMMENT 'Reported contact server', + `gsid` int unsigned COMMENT 'Reported contact server', `comment` text COMMENT 'Report', `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', @@ -1712,6 +1725,7 @@ CREATE TABLE IF NOT EXISTS `report` ( 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`), @@ -1833,8 +1847,8 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( `rel` tinyint unsigned COMMENT 'The kind of the relation between the user and the contact', `info` mediumtext COMMENT '', `notify_new_posts` boolean COMMENT '', - `remote_self` boolean COMMENT '', - `fetch_further_information` tinyint unsigned COMMENT '', + `remote_self` tinyint unsigned COMMENT '0 => No mirroring, 1-2 => Mirror as own post, 3 => Mirror as reshare', + `fetch_further_information` tinyint unsigned COMMENT '0 => None, 1 => Fetch information, 3 => Fetch keywords, 2 => Fetch both', `ffi_keyword_denylist` text COMMENT '', `subhub` boolean COMMENT '', `hub-verify` varbinary(383) COMMENT '', @@ -1899,6 +1913,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 -- @@ -2000,6 +2045,7 @@ CREATE VIEW `post-user-view` AS SELECT `author`.`hidden` AS `author-hidden`, `author`.`updated` AS `author-updated`, `author`.`gsid` AS `author-gsid`, + `author`.`baseurl` AS `author-baseurl`, `post-user`.`owner-id` AS `owner-id`, `owner`.`uri-id` AS `owner-uri-id`, `owner`.`url` AS `owner-link`, @@ -2012,6 +2058,7 @@ CREATE VIEW `post-user-view` AS SELECT `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, `owner`.`updated` AS `owner-updated`, + `owner`.`gsid` AS `owner-gsid`, `owner`.`contact-type` AS `owner-contact-type`, `post-user`.`causer-id` AS `causer-id`, `causer`.`uri-id` AS `causer-uri-id`, @@ -2024,6 +2071,7 @@ CREATE VIEW `post-user-view` AS SELECT `causer`.`network` AS `causer-network`, `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, + `causer`.`gsid` AS `causer-gsid`, `causer`.`contact-type` AS `causer-contact-type`, `post-delivery-data`.`postopts` AS `postopts`, `post-delivery-data`.`inform` AS `inform`, @@ -2157,6 +2205,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `contact`.`pending` AS `contact-pending`, `contact`.`rel` AS `contact-rel`, `contact`.`uid` AS `contact-uid`, + `contact`.`gsid` AS `contact-gsid`, `contact`.`contact-type` AS `contact-contact-type`, IF (`post-user`.`network` IN ('apub', 'dfrn', 'dspr', 'stat'), true, `contact`.`writable`) AS `writable`, `contact`.`self` AS `self`, @@ -2192,6 +2241,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, `owner`.`updated` AS `owner-updated`, + `owner`.`gsid` AS `owner-gsid`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread-user`.`causer-id` AS `causer-id`, `causer`.`uri-id` AS `causer-uri-id`, @@ -2204,6 +2254,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `causer`.`network` AS `causer-network`, `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, + `causer`.`gsid` AS `causer-gsid`, `causer`.`contact-type` AS `causer-contact-type`, `post-delivery-data`.`postopts` AS `postopts`, `post-delivery-data`.`inform` AS `inform`, @@ -2359,6 +2410,7 @@ CREATE VIEW `post-view` AS SELECT `owner`.`hidden` AS `owner-hidden`, `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, + `owner`.`gsid` AS `owner-gsid`, `post`.`causer-id` AS `causer-id`, `causer`.`uri-id` AS `causer-uri-id`, `causer`.`url` AS `causer-link`, @@ -2371,6 +2423,7 @@ CREATE VIEW `post-view` AS SELECT `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, `causer`.`contact-type` AS `causer-contact-type`, + `causer`.`gsid` AS `causer-gsid`, `post-question`.`id` AS `question-id`, `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, @@ -2501,6 +2554,7 @@ CREATE VIEW `post-thread-view` AS SELECT `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, `owner`.`updated` AS `owner-updated`, + `owner`.`gsid` AS `owner-gsid`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread`.`causer-id` AS `causer-id`, `causer`.`uri-id` AS `causer-uri-id`, @@ -2513,6 +2567,7 @@ CREATE VIEW `post-thread-view` AS SELECT `causer`.`network` AS `causer-network`, `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, + `causer`.`gsid` AS `causer-gsid`, `causer`.`contact-type` AS `causer-contact-type`, `post-question`.`id` AS `question-id`, `post-question`.`multiple` AS `question-multiple`, @@ -2634,16 +2689,16 @@ CREATE VIEW `network-item-view` AS SELECT `post-user`.`contact-id` AS `contact-id`, `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` - 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` + INNER JOIN `post-thread-user` ON `post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` AND `post-thread-user`.`uid` = `post-user`.`uid` + 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` 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 (`author`.`blocked` IS NULL OR NOT `author`.`blocked`) - AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`); + AND NOT `authorcontact`.`blocked` AND NOT `ownercontact`.`blocked` + AND NOT EXISTS(SELECT `cid` FROM `user-contact` WHERE `uid` = `post-thread-user`.`uid` AND `cid` IN (`authorcontact`.`id`, `ownercontact`.`id`) AND (`blocked` OR `ignored`)) + AND NOT EXISTS(SELECT `gsid` FROM `user-gserver` WHERE `uid` = `post-thread-user`.`uid` AND `gsid` IN (`authorcontact`.`gsid`, `ownercontact`.`gsid`) AND `ignored`); -- -- VIEW network-thread-view @@ -2664,14 +2719,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` - 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` + 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` 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 (`author`.`blocked` IS NULL OR NOT `author`.`blocked`) - AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`); + AND NOT `authorcontact`.`blocked` AND NOT `ownercontact`.`blocked` + AND NOT EXISTS(SELECT `cid` FROM `user-contact` WHERE `uid` = `post-thread-user`.`uid` AND `cid` IN (`authorcontact`.`id`, `ownercontact`.`id`) AND (`blocked` OR `ignored`)) + AND NOT EXISTS(SELECT `gsid` FROM `user-gserver` WHERE `uid` = `post-thread-user`.`uid` AND `gsid` IN (`authorcontact`.`gsid`, `ownercontact`.`gsid`) AND `ignored`); -- -- VIEW owner-view