X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=329381b95b3dfc4e7efba5e50381646fe083941e;hb=4462ad6bf083307fb5af8b3b6a7882743358ce8a;hp=bc73b5ac088ade5a433147c39964faa72e12a331;hpb=ddc3c0db03267d29ace272b504dc6f803fa02990;p=friendica.git diff --git a/database.sql b/database.sql index bc73b5ac08..329381b95b 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2023.09-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1529 +-- DB_UPDATE_VERSION 1534 -- ------------------------------------------ @@ -510,9 +510,13 @@ CREATE TABLE IF NOT EXISTS `config` ( CREATE TABLE IF NOT EXISTS `contact-relation` ( `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact the related contact had interacted with', `relation-cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'related contact who had interacted with the contact', - `last-interaction` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last interaction', + `last-interaction` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last interaction by relation-cid on cid', `follow-updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last update of the contact relationship', - `follows` boolean NOT NULL DEFAULT '0' COMMENT '', + `follows` boolean NOT NULL DEFAULT '0' COMMENT 'if true, relation-cid follows cid', + `score` smallint unsigned COMMENT 'score for interactions of cid on relation-cid', + `relation-score` smallint unsigned COMMENT 'score for interactions of relation-cid on cid', + `thread-score` smallint unsigned COMMENT 'score for interactions of cid on threads of relation-cid', + `relation-thread-score` smallint unsigned COMMENT 'score for interactions of relation-cid on threads of cid', PRIMARY KEY(`cid`,`relation-cid`), INDEX `relation-cid` (`relation-cid`), FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, @@ -1296,6 +1300,26 @@ CREATE TABLE IF NOT EXISTS `post-delivery-data` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items'; +-- +-- TABLE post-engagement +-- +CREATE TABLE IF NOT EXISTS `post-engagement` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner', + `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Person, organisation, news, community, relay', + `media-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Type of media in a bit array (1 = image, 2 = video, 4 = audio', + `language` varbinary(128) COMMENT 'Language information about this post', + `created` datetime COMMENT '', + `restricted` boolean NOT NULL DEFAULT '0' COMMENT 'If true, this post is either unlisted or not from a federated network', + `comments` mediumint unsigned COMMENT 'Number of comments', + `activities` mediumint unsigned COMMENT 'Number of activities (like, dislike, ...)', + PRIMARY KEY(`uri-id`), + INDEX `owner-id` (`owner-id`), + INDEX `created` (`created`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Engagement data per post'; + -- -- TABLE post-history -- @@ -1549,6 +1573,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( INDEX `psid` (`psid`), INDEX `post-user-id` (`post-user-id`), INDEX `commented` (`commented`), + INDEX `received` (`received`), INDEX `uid_received` (`uid`,`received`), INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `uid_commented` (`uid`,`commented`), @@ -1710,15 +1735,15 @@ CREATE TABLE IF NOT EXISTS `report` ( `cid` int unsigned NOT NULL COMMENT 'Reported contact', `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_*', + `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', `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 '', + `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.000000' COMMENT '', `edited` datetime(6) COMMENT 'Last time the report has been edited', PRIMARY KEY(`id`), INDEX `uid` (`uid`), @@ -1843,6 +1868,7 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( `collapsed` boolean COMMENT 'Posts from this contact are collapsed', `hidden` boolean COMMENT 'This contact is hidden from the others', `is-blocked` boolean COMMENT 'User is blocked by this contact', + `channel-frequency` tinyint unsigned COMMENT 'Controls the frequency of the appearance of this contact in channels', `pending` boolean COMMENT '', `rel` tinyint unsigned COMMENT 'The kind of the relation between the user and the contact', `info` mediumtext COMMENT '', @@ -2693,14 +2719,12 @@ CREATE VIEW `network-item-view` AS SELECT 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` 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`); + 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 @@ -2723,15 +2747,12 @@ CREATE VIEW `network-thread-view` AS SELECT 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` 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`) - AND NOT EXISTS(SELECT `gsid` FROM `user-gserver` WHERE `uid` = `post-thread-user`.`uid` AND `gsid` IN (`authorcontact`.`gsid`, `ownercontact`.`gsid`) AND `ignored`); + 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