X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=database.sql;h=6bad8340a6d2ee12d50ae94fba2b3b5843316be4;hb=ca32134830a468c4e0a1de1e013e1deb5d4cbae6;hp=89a80b170b5dfae8b8475e51cac1e122a285cabb;hpb=76cc8eaff87eba930aaf3f70a1d3d7233bbdf2af;p=friendica.git diff --git a/database.sql b/database.sql index 89a80b170b..6bad8340a6 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2023.09-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1528 +-- DB_UPDATE_VERSION 1531 -- ------------------------------------------ @@ -173,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', @@ -513,6 +513,10 @@ CREATE TABLE IF NOT EXISTS `contact-relation` ( `last-interaction` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last interaction', `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 '', + `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,23 @@ 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', + `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item', + `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Person, organisation, news, community, relay', + `created` datetime COMMENT '', + `comments` mediumint unsigned COMMENT 'Number of comments', + `activities` mediumint unsigned COMMENT 'Number of activities (like, dislike, ...)', + PRIMARY KEY(`uri-id`), + INDEX `author-id` (`author-id`), + INDEX `created` (`created`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Engagement data per post'; + -- -- TABLE post-history -- @@ -2045,6 +2066,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`, @@ -2692,14 +2714,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 @@ -2722,15 +2742,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