X-Git-Url: https://reisub.nsupdate.info/git/?a=blobdiff_plain;f=database.sql;h=120edf32942c61fb47b57a65ddb9600ae409674c;hb=bae1f6342436f324e8a91031fe799d20bf3ed39d;hp=39a56e7966bbbdefe121249da87211d25d8c6bff;hpb=43f9be367ff36129ad3c5550293252aa7e663e79;p=friendica.git%2F.git diff --git a/database.sql b/database.sql index 39a56e7966..120edf3294 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2023.09-rc (Giant Rhubarb) --- DB_UPDATE_VERSION 1540 +-- Friendica 2024.06-dev (Yellow Archangel) +-- DB_UPDATE_VERSION 1559 -- ------------------------------------------ @@ -73,8 +73,6 @@ CREATE TABLE IF NOT EXISTS `user` ( `blockwall` boolean NOT NULL DEFAULT '0' COMMENT 'Prohibit contacts to post to the profile page of the user', `hidewall` boolean NOT NULL DEFAULT '0' COMMENT 'Hide profile details from unknown viewers', `blocktags` boolean NOT NULL DEFAULT '0' COMMENT 'Prohibit contacts to tag the post of this user', - `unkmail` boolean NOT NULL DEFAULT '0' COMMENT 'Permit unknown people to send private mails to this user', - `cntunkmail` int unsigned NOT NULL DEFAULT 10 COMMENT '', `notify-flags` smallint unsigned NOT NULL DEFAULT 65535 COMMENT 'email notification options', `page-flags` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'page/profile type', `account-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', @@ -504,8 +502,13 @@ CREATE TABLE IF NOT EXISTS `channel` ( `access-key` varchar(1) COMMENT 'Access key', `include-tags` varchar(1023) COMMENT 'Comma separated list of tags that will be included in the channel', `exclude-tags` varchar(1023) COMMENT 'Comma separated list of tags that aren\'t allowed in the channel', + `min-size` int unsigned COMMENT 'Minimum post size', + `max-size` int unsigned COMMENT 'Maximum post size', `full-text-search` varchar(1023) COMMENT 'Full text search pattern, see https://mariadb.com/kb/en/full-text-index-overview/#in-boolean-mode', `media-type` smallint unsigned COMMENT 'Filtered media types', + `languages` mediumtext COMMENT 'Desired languages', + `publish` boolean COMMENT 'publish channel content', + `valid` boolean COMMENT 'Set, when the full-text-search is valid', PRIMARY KEY(`id`), INDEX `uid` (`uid`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE @@ -536,6 +539,7 @@ CREATE TABLE IF NOT EXISTS `contact-relation` ( `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', + `post-score` smallint unsigned COMMENT 'score for the amount of posts from cid that can be seen by relation-cid', PRIMARY KEY(`cid`,`relation-cid`), INDEX `relation-cid` (`relation-cid`), FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, @@ -1236,6 +1240,23 @@ CREATE TABLE IF NOT EXISTS `post-category` ( FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to categories'; +-- +-- TABLE post-counts +-- +CREATE TABLE IF NOT EXISTS `post-counts` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `vid` smallint unsigned NOT NULL COMMENT 'Id of the verb table entry that contains the activity verbs', + `reaction` varchar(4) NOT NULL COMMENT 'Emoji Reaction', + `parent-uri-id` int unsigned COMMENT 'Id of the item-uri table that contains the parent uri', + `count` int unsigned DEFAULT 0 COMMENT 'Number of activities', + PRIMARY KEY(`uri-id`,`vid`,`reaction`), + INDEX `vid` (`vid`), + INDEX `parent-uri-id` (`parent-uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Original remote activity'; + -- -- TABLE post-collection -- @@ -1263,6 +1284,7 @@ CREATE TABLE IF NOT EXISTS `post-content` ( `location` varchar(255) NOT NULL DEFAULT '' COMMENT 'text location where this item originated', `coord` varchar(255) NOT NULL DEFAULT '' COMMENT 'longitude/latitude pair representing location where this item originated', `language` text COMMENT 'Language information about this post', + `sensitive` boolean COMMENT 'If true, this post contains sensitive content', `app` varchar(255) NOT NULL DEFAULT '' COMMENT 'application which generated this item', `rendered-hash` varchar(32) NOT NULL DEFAULT '' COMMENT '', `rendered-html` mediumtext COMMENT 'item.body converted to html', @@ -1275,7 +1297,6 @@ CREATE TABLE IF NOT EXISTS `post-content` ( PRIMARY KEY(`uri-id`), INDEX `plink` (`plink`(191)), INDEX `resource-id` (`resource-id`), - FULLTEXT INDEX `title-content-warning-body` (`title`,`content-warning`,`body`), INDEX `quote-uri-id` (`quote-uri-id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`quote-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE @@ -1326,10 +1347,12 @@ 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', + `media-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Type of media in a bit array (1 = image, 2 = video, 4 = audio)', + `language` char(2) COMMENT 'Language information about this post in the ISO 639-1 format', `searchtext` mediumtext COMMENT 'Simplified text for the full text search', + `size` int unsigned COMMENT 'Body size', `created` datetime COMMENT '', + `network` char(4) 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, ...)', @@ -1442,6 +1465,26 @@ CREATE TABLE IF NOT EXISTS `post-question-option` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Question option'; +-- +-- TABLE post-searchindex +-- +CREATE TABLE IF NOT EXISTS `post-searchindex` ( + `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', + `media-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Type of media in a bit array (1 = image, 2 = video, 4 = audio)', + `language` char(2) COMMENT 'Language information about this post in the ISO 639-1 format', + `searchtext` mediumtext COMMENT 'Simplified text for the full text search', + `size` int unsigned COMMENT 'Body size', + `created` datetime COMMENT '', + `restricted` boolean NOT NULL DEFAULT '0' COMMENT 'If true, this post is either unlisted or not from a federated network', + PRIMARY KEY(`uri-id`), + INDEX `owner-id` (`owner-id`), + INDEX `created` (`created`), + FULLTEXT INDEX `searchtext` (`searchtext`), + 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='Content for all posts'; + -- -- TABLE post-tag -- @@ -1507,6 +1550,7 @@ CREATE TABLE IF NOT EXISTS `post-user` ( `post-reason` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Reason why the post arrived at the user', `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', `private` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=public, 1=private, 2=unlisted', + `restrictions` tinyint unsigned COMMENT 'Bit array of post restrictions (1 = Reply, 2 = Like, 4 = Announce)', `global` boolean NOT NULL DEFAULT '0' COMMENT '', `visible` boolean NOT NULL DEFAULT '0' COMMENT '', `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'item has been marked for deletion', @@ -1534,7 +1578,8 @@ CREATE TABLE IF NOT EXISTS `post-user` ( INDEX `event-id` (`event-id`), INDEX `psid` (`psid`), INDEX `author-id_uid` (`author-id`,`uid`), - INDEX `author-id_received` (`author-id`,`received`), + INDEX `author-id_created` (`author-id`,`created`), + INDEX `owner-id_created` (`owner-id`,`created`), INDEX `parent-uri-id_uid` (`parent-uri-id`,`uid`), INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `uid_contactid` (`uid`,`contact-id`), @@ -1595,11 +1640,17 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( INDEX `post-user-id` (`post-user-id`), INDEX `commented` (`commented`), INDEX `received` (`received`), + INDEX `author-id_created` (`author-id`,`created`), + INDEX `owner-id_created` (`owner-id`,`created`), INDEX `uid_received` (`uid`,`received`), INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `uid_commented` (`uid`,`commented`), + INDEX `uid_created` (`uid`,`created`), INDEX `uid_starred` (`uid`,`starred`), INDEX `uid_mention` (`uid`,`mention`), + INDEX `contact-id_commented` (`contact-id`,`commented`), + INDEX `contact-id_received` (`contact-id`,`received`), + INDEX `contact-id_created` (`contact-id`,`created`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`conversation-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, @@ -1686,7 +1737,6 @@ CREATE TABLE IF NOT EXISTS `profile` ( `net-publish` boolean NOT NULL DEFAULT '0' COMMENT 'publish profile in global directory', PRIMARY KEY(`id`), INDEX `uid_is-default` (`uid`,`is-default`), - FULLTEXT INDEX `pub_keywords` (`pub_keywords`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='user profiles data'; @@ -1868,14 +1918,14 @@ CREATE TABLE IF NOT EXISTS `subscription` ( ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Push Subscription for the API'; -- --- TABLE test-full-text-search +-- TABLE check-full-text-search -- -CREATE TABLE IF NOT EXISTS `test-full-text-search` ( - `pid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Process id of the worker', +CREATE TABLE IF NOT EXISTS `check-full-text-search` ( + `pid` int unsigned NOT NULL COMMENT 'The ID of the process', `searchtext` mediumtext COMMENT 'Simplified text for the full text search', PRIMARY KEY(`pid`), FULLTEXT INDEX `searchtext` (`searchtext`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Test for a full text search match in user defined channels before storing the message in the system'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Check for a full text search match in user defined channels before storing the message in the system'; -- -- TABLE userd @@ -1898,6 +1948,7 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( `ignored` boolean COMMENT 'Posts from this contact are ignored', `collapsed` boolean COMMENT 'Posts from this contact are collapsed', `hidden` boolean COMMENT 'This contact is hidden from the others', + `channel-only` boolean COMMENT 'This contact is displayed only in channels, but not in the network stream.', `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 '', @@ -1968,7 +2019,8 @@ CREATE VIEW `application-view` AS SELECT `application-token`.`follow` AS `follow`, `application-token`.`push` AS `push` FROM `application-token` - INNER JOIN `application` ON `application-token`.`application-id` = `application`.`id`; + INNER JOIN `application` ON `application-token`.`application-id` = `application`.`id` + INNER JOIN `user` ON `user`.`uid` = `application-token`.`uid` AND `user`.`verified` AND NOT `user`.`blocked` AND NOT `user`.`account_removed` AND NOT `user`.`account_expired`; -- -- VIEW circle-member-view @@ -2001,6 +2053,20 @@ CREATE VIEW `circle-member-view` AS SELECT INNER JOIN `contact` ON `group_member`.`contact-id` = `contact`.`id` INNER JOIN `group` ON `group_member`.`gid` = `group`.`id`; +-- +-- VIEW post-counts-view +-- +DROP VIEW IF EXISTS `post-counts-view`; +CREATE VIEW `post-counts-view` AS SELECT + `post-counts`.`uri-id` AS `uri-id`, + `post-counts`.`vid` AS `vid`, + `verb`.`name` AS `verb`, + `post-counts`.`reaction` AS `reaction`, + `post-counts`.`parent-uri-id` AS `parent-uri-id`, + `post-counts`.`count` AS `count` + FROM `post-counts` + INNER JOIN `verb` ON `verb`.`id` = `post-counts`.`vid`; + -- -- VIEW post-timeline-view -- @@ -2046,6 +2112,38 @@ CREATE VIEW `post-timeline-view` AS SELECT STRAIGHT_JOIN `contact` AS `owner` ON `owner`.`id` = `post-user`.`owner-id` LEFT JOIN `contact` AS `causer` ON `causer`.`id` = `post-user`.`causer-id`; +-- +-- VIEW post-searchindex-user-view +-- +DROP VIEW IF EXISTS `post-searchindex-user-view`; +CREATE VIEW `post-searchindex-user-view` AS SELECT + `post-thread-user`.`uid` AS `uid`, + `post-searchindex`.`uri-id` AS `uri-id`, + `post-searchindex`.`owner-id` AS `owner-id`, + `post-searchindex`.`media-type` AS `media-type`, + `post-searchindex`.`language` AS `language`, + `post-searchindex`.`searchtext` AS `searchtext`, + `post-searchindex`.`size` AS `size`, + `post-thread-user`.`commented` AS `commented`, + `post-thread-user`.`received` AS `received`, + `post-thread-user`.`created` AS `created`, + `post-thread-user`.`network` AS `network`, + `post-searchindex`.`language` AS `restricted`, + 0 AS `comments`, + 0 AS `activities` + FROM `post-thread-user` + INNER JOIN `post-searchindex` ON `post-searchindex`.`uri-id` = `post-thread-user`.`uri-id` + 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` + 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 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 post-user-view -- @@ -2103,6 +2201,8 @@ CREATE VIEW `post-user-view` AS SELECT `post-content`.`plink` AS `plink`, `post-content`.`location` AS `location`, `post-content`.`coord` AS `coord`, + `post-content`.`sensitive` AS `sensitive`, + `post-user`.`restrictions` AS `restrictions`, `post-content`.`app` AS `app`, `post-content`.`object-type` AS `object-type`, `post-content`.`object` AS `object`, @@ -2287,6 +2387,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-content`.`plink` AS `plink`, `post-content`.`location` AS `location`, `post-content`.`coord` AS `coord`, + `post-content`.`sensitive` AS `sensitive`, + `post-user`.`restrictions` AS `restrictions`, `post-content`.`app` AS `app`, `post-content`.`object-type` AS `object-type`, `post-content`.`object` AS `object`, @@ -2457,6 +2559,7 @@ CREATE VIEW `post-view` AS SELECT `post-content`.`plink` AS `plink`, `post-content`.`location` AS `location`, `post-content`.`coord` AS `coord`, + `post-content`.`sensitive` AS `sensitive`, `post-content`.`app` AS `app`, `post-content`.`object-type` AS `object-type`, `post-content`.`object` AS `object`, @@ -2603,6 +2706,7 @@ CREATE VIEW `post-thread-view` AS SELECT `post-content`.`plink` AS `plink`, `post-content`.`location` AS `location`, `post-content`.`coord` AS `coord`, + `post-content`.`sensitive` AS `sensitive`, `post-content`.`app` AS `app`, `post-content`.`object-type` AS `object-type`, `post-content`.`object` AS `object`, @@ -2776,36 +2880,6 @@ CREATE VIEW `tag-view` AS SELECT LEFT JOIN `tag` ON `post-tag`.`tid` = `tag`.`id` LEFT JOIN `contact` ON `post-tag`.`cid` = `contact`.`id`; --- --- VIEW network-item-view --- -DROP VIEW IF EXISTS `network-item-view`; -CREATE VIEW `network-item-view` AS SELECT - `post-user`.`uri-id` AS `uri-id`, - `post-thread-user`.`post-user-id` AS `parent`, - `post-user`.`received` AS `received`, - `post-thread-user`.`commented` AS `commented`, - `post-user`.`created` AS `created`, - `post-user`.`uid` AS `uid`, - `post-thread-user`.`starred` AS `starred`, - `post-thread-user`.`mention` AS `mention`, - `post-user`.`network` AS `network`, - `post-user`.`unseen` AS `unseen`, - `post-user`.`gravity` AS `gravity`, - `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` - 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 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 -- @@ -2831,8 +2905,8 @@ CREATE VIEW `network-thread-view` AS SELECT 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 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`); + AND NOT EXISTS(SELECT `cid` FROM `user-contact` WHERE `uid` = `post-thread-user`.`uid` AND `cid` IN (`post-thread-user`.`author-id`, `post-thread-user`.`owner-id`, `post-thread-user`.`causer-id`) AND (`blocked` OR `ignored` OR `channel-only`)) + 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 @@ -2929,8 +3003,6 @@ CREATE VIEW `owner-view` AS SELECT `user`.`blockwall` AS `blockwall`, `user`.`hidewall` AS `hidewall`, `user`.`blocktags` AS `blocktags`, - `user`.`unkmail` AS `unkmail`, - `user`.`cntunkmail` AS `cntunkmail`, `user`.`notify-flags` AS `notify-flags`, `user`.`page-flags` AS `page-flags`, `user`.`account-type` AS `account-type`,