X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=11fbd514e754cff3efa8a915ec9a1978d71f7f30;hb=667f384bfc3a42b51f5dd2f8d7882e20d6ad97d2;hp=7b3156f6f7c8d4cab53fa3898ca4759836b7eb5b;hpb=23a8245fb066d2b64ced49b50dd10f40ffabba1e;p=friendica.git diff --git a/database.sql b/database.sql index 7b3156f6f7..11fbd514e7 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1497 +-- Friendica 2023.09-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1529 -- ------------------------------------------ @@ -34,6 +34,7 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `last_poco_query` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', `last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last successful connection request', `last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last failed connection request', + `blocked` boolean COMMENT 'Server is blocked', `failed` boolean COMMENT 'Connection failed', `next_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Next connection request', PRIMARY KEY(`id`), @@ -70,7 +71,7 @@ CREATE TABLE IF NOT EXISTS `user` ( `verified` boolean NOT NULL DEFAULT '0' COMMENT 'user is verified through email', `blocked` boolean NOT NULL DEFAULT '0' COMMENT '1 for user is blocked', `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 unkown viewers', + `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 '', @@ -100,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 -- @@ -129,6 +143,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `avatar` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the avatar', `header` varbinary(383) COMMENT 'Header picture', `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `nurl` varbinary(383) NOT NULL DEFAULT '' COMMENT '', @@ -158,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', @@ -188,7 +203,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', @@ -250,9 +265,9 @@ CREATE TABLE IF NOT EXISTS `permissionset` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id of this permission set', `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', - `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', + `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles', `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', - `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', + `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles', PRIMARY KEY(`id`), INDEX `uid_allow_cid_allow_gid_deny_cid_deny_gid` (`uid`,`allow_cid`(50),`allow_gid`(30),`deny_cid`(50),`deny_gid`(30)), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE @@ -338,22 +353,6 @@ CREATE TABLE IF NOT EXISTS `account-user` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Remote and local accounts'; --- --- TABLE addon --- -CREATE TABLE IF NOT EXISTS `addon` ( - `id` int unsigned NOT NULL auto_increment COMMENT '', - `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'addon base (file)name', - `version` varchar(50) NOT NULL DEFAULT '' COMMENT 'currently unused', - `installed` boolean NOT NULL DEFAULT '0' COMMENT 'currently always 1', - `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'currently unused', - `timestamp` int unsigned NOT NULL DEFAULT 0 COMMENT 'file timestamp to check for reloads', - `plugin_admin` boolean NOT NULL DEFAULT '0' COMMENT '1 = has admin config, 0 = has no admin config', - PRIMARY KEY(`id`), - INDEX `installed_name` (`installed`,`name`), - UNIQUE INDEX `name` (`name`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='registered addons'; - -- -- TABLE apcontact -- @@ -471,9 +470,9 @@ CREATE TABLE IF NOT EXISTS `attach` ( `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time', `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time', `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>', - `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', + `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles', `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', - `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', + `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles', `backend-class` tinytext COMMENT 'Storage backend class', `backend-ref` text COMMENT 'Storage backend data reference', PRIMARY KEY(`id`), @@ -498,8 +497,8 @@ CREATE TABLE IF NOT EXISTS `cache` ( -- CREATE TABLE IF NOT EXISTS `config` ( `id` int unsigned NOT NULL auto_increment COMMENT '', - `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT '', - `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '', + `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The category of the entry', + `k` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The key of the entry', `v` mediumtext COMMENT '', PRIMARY KEY(`id`), UNIQUE INDEX `cat_k` (`cat`,`k`) @@ -578,6 +577,61 @@ CREATE TABLE IF NOT EXISTS `delayed-post` ( FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Posts that are about to be distributed at a later time'; +-- +-- TABLE delivery-queue +-- +CREATE TABLE IF NOT EXISTS `delivery-queue` ( + `gsid` int unsigned NOT NULL COMMENT 'Target server', + `uri-id` int unsigned NOT NULL COMMENT 'Delivered post', + `created` datetime COMMENT '', + `command` varbinary(32) COMMENT '', + `cid` int unsigned COMMENT 'Target contact', + `uid` mediumint unsigned COMMENT 'Delivering user', + `failed` tinyint DEFAULT 0 COMMENT 'Number of times the delivery has failed', + PRIMARY KEY(`uri-id`,`gsid`), + INDEX `gsid_created` (`gsid`,`created`), + INDEX `uid` (`uid`), + INDEX `cid` (`cid`), + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for posts for the batch processing'; + +-- +-- TABLE diaspora-contact +-- +CREATE TABLE IF NOT EXISTS `diaspora-contact` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the contact URL', + `addr` varchar(255) COMMENT '', + `alias` varchar(255) COMMENT '', + `nick` varchar(255) COMMENT '', + `name` varchar(255) COMMENT '', + `given-name` varchar(255) COMMENT '', + `family-name` varchar(255) COMMENT '', + `photo` varchar(255) COMMENT '', + `photo-medium` varchar(255) COMMENT '', + `photo-small` varchar(255) COMMENT '', + `batch` varchar(255) COMMENT '', + `notify` varchar(255) COMMENT '', + `poll` varchar(255) COMMENT '', + `subscribe` varchar(255) COMMENT '', + `searchable` boolean COMMENT '', + `pubkey` text COMMENT '', + `gsid` int unsigned COMMENT 'Global Server ID', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `interacting_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts this contact interacts with', + `interacted_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts that interacted with this contact', + `post_count` int unsigned DEFAULT 0 COMMENT 'Number of posts and comments', + PRIMARY KEY(`uri-id`), + UNIQUE INDEX `addr` (`addr`), + INDEX `alias` (`alias`), + INDEX `gsid` (`gsid`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation'; + -- -- TABLE diaspora-interaction -- @@ -621,9 +675,9 @@ CREATE TABLE IF NOT EXISTS `event` ( `nofinish` boolean NOT NULL DEFAULT '0' COMMENT 'if event does have no end this is 1', `ignore` boolean NOT NULL DEFAULT '0' COMMENT '0 or 1', `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', - `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', + `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles', `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', - `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', + `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles', PRIMARY KEY(`id`), INDEX `uid_start` (`uid`,`start`), INDEX `cid` (`cid`), @@ -633,39 +687,6 @@ CREATE TABLE IF NOT EXISTS `event` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events'; --- --- TABLE fcontact --- -CREATE TABLE IF NOT EXISTS `fcontact` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'unique id', - `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', - `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `request` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `batch` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `notify` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `poll` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `confirm` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `network` char(4) NOT NULL DEFAULT '' COMMENT '', - `alias` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `pubkey` text COMMENT '', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `interacting_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts this contact interactes with', - `interacted_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts that interacted with this contact', - `post_count` int unsigned DEFAULT 0 COMMENT 'Number of posts and comments', - PRIMARY KEY(`id`), - INDEX `addr` (`addr`(32)), - UNIQUE INDEX `url` (`url`(190)), - UNIQUE INDEX `uri-id` (`uri-id`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation'; - -- -- TABLE fetch-entry -- @@ -708,29 +729,29 @@ CREATE TABLE IF NOT EXISTS `group` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `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 group has been deleted', - `cid` int unsigned COMMENT 'Contact id of forum. When this field is filled then the members are synced automatically.', - `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'human readable name of group', + `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the circle has been deleted', + `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`), INDEX `cid` (`cid`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy groups, group info'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy circles, circle info'; -- -- TABLE group_member -- CREATE TABLE IF NOT EXISTS `group_member` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `gid` int unsigned NOT NULL DEFAULT 0 COMMENT 'groups.id of the associated group', - `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id of the member assigned to the associated group', + `gid` int unsigned NOT NULL DEFAULT 0 COMMENT 'group.id of the associated circle', + `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id of the member assigned to the associated circle', PRIMARY KEY(`id`), INDEX `contactid` (`contact-id`), UNIQUE INDEX `gid_contactid` (`gid`,`contact-id`), FOREIGN KEY (`gid`) REFERENCES `group` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy groups, member info'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy circles, member info'; -- -- TABLE gserver-tag @@ -801,6 +822,7 @@ CREATE TABLE IF NOT EXISTS `inbox-entry-receiver` ( CREATE TABLE IF NOT EXISTS `inbox-status` ( `url` varbinary(383) NOT NULL COMMENT 'URL of the inbox', `uri-id` int unsigned COMMENT 'Item-uri id of inbox url', + `gsid` int unsigned COMMENT 'ID of the related server', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of this entry', `success` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful delivery', `failure` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed delivery', @@ -809,7 +831,9 @@ CREATE TABLE IF NOT EXISTS `inbox-status` ( `shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?', PRIMARY KEY(`url`), INDEX `uri-id` (`uri-id`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `gsid` (`gsid`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Status of ActivityPub inboxes'; -- @@ -837,6 +861,16 @@ CREATE TABLE IF NOT EXISTS `intro` ( FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; +-- +-- TABLE key-value +-- +CREATE TABLE IF NOT EXISTS `key-value` ( + `k` varbinary(50) NOT NULL COMMENT '', + `v` mediumtext COMMENT '', + `updated_at` int unsigned NOT NULL COMMENT 'timestamp of the last update', + PRIMARY KEY(`k`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='A key value storage'; + -- -- TABLE locks -- @@ -859,7 +893,7 @@ CREATE TABLE IF NOT EXISTS `mail` ( `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this private message', `from-name` varchar(255) NOT NULL DEFAULT '' COMMENT 'name of the sender', `from-photo` varbinary(383) NOT NULL DEFAULT '' COMMENT 'contact photo link of the sender', - `from-url` varbinary(383) NOT NULL DEFAULT '' COMMENT 'profile linke of the sender', + `from-url` varbinary(383) NOT NULL DEFAULT '' COMMENT 'profile link of the sender', `contact-id` varbinary(255) COMMENT 'contact.id', `author-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the author of the mail', `convid` int unsigned COMMENT 'conv.id', @@ -1093,9 +1127,9 @@ CREATE TABLE IF NOT EXISTS `photo` ( `scale` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', `profile` boolean NOT NULL DEFAULT '0' COMMENT '', `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', - `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', + `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles', `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', - `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', + `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles', `accessible` boolean NOT NULL DEFAULT '0' COMMENT 'Make photo publicly accessible, ignoring permissions', `backend-class` tinytext COMMENT 'Storage backend class', `backend-ref` text COMMENT 'Storage backend data reference', @@ -1296,6 +1330,9 @@ CREATE TABLE IF NOT EXISTS `post-link` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `url` varbinary(511) NOT NULL COMMENT 'External URL', `mimetype` varchar(60) COMMENT '', + `height` smallint unsigned COMMENT 'Height of the media', + `width` smallint unsigned COMMENT 'Width of the media', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the link', PRIMARY KEY(`id`), UNIQUE INDEX `uri-id-url` (`uri-id`,`url`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE @@ -1434,7 +1471,7 @@ CREATE TABLE IF NOT EXISTS `post-user` ( `event-id` int unsigned COMMENT 'Used to link to the event.id', `unseen` boolean NOT NULL DEFAULT '1' COMMENT 'post has not been seen', `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'Marker to hide the post from the user', - `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', `wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid', `origin` boolean NOT NULL DEFAULT '0' COMMENT 'item originated at this site', `psid` int unsigned COMMENT 'ID of the permission set of this post', @@ -1546,7 +1583,7 @@ CREATE TABLE IF NOT EXISTS `post-user-notification` ( -- CREATE TABLE IF NOT EXISTS `process` ( `pid` int unsigned NOT NULL COMMENT 'The ID of the process', - `hostname` varchar(32) NOT NULL COMMENT 'The name of the host the process is ran on', + `hostname` varchar(255) NOT NULL COMMENT 'The name of the host the process is ran on', `command` varbinary(32) NOT NULL DEFAULT '' COMMENT '', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', PRIMARY KEY(`pid`,`hostname`), @@ -1562,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 '', @@ -1669,16 +1706,36 @@ CREATE TABLE IF NOT EXISTS `register` ( CREATE TABLE IF NOT EXISTS `report` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `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-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 (`cid`) REFERENCES `contact` (`id`) 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 (`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=''; -- @@ -1692,7 +1749,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 @@ -1779,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 '', @@ -1845,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 -- @@ -1853,7 +1952,7 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`id` AS `id`, `post-user`.`id` AS `post-user-id`, `post-user`.`uid` AS `uid`, - `parent-post`.`id` AS `parent`, + `post-thread-user`.`post-user-id` AS `parent`, `item-uri`.`uri` AS `uri`, `post-user`.`uri-id` AS `uri-id`, `parent-item-uri`.`uri` AS `parent-uri`, @@ -1939,23 +2038,27 @@ 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`, `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`, `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`, `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`, @@ -1963,10 +2066,12 @@ 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`, `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`, @@ -1996,14 +2101,14 @@ CREATE VIEW `post-user-view` AS SELECT EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-user`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, - `parent-post`.`network` AS `parent-network`, - `parent-post`.`author-id` AS `parent-author-id`, + `post-thread-user`.`network` AS `parent-network`, + `post-thread-user`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, `parent-post-author`.`nick` AS `parent-author-nick`, `parent-post-author`.`network` AS `parent-author-network` FROM `post-user` - STRAIGHT_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 `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-user`.`contact-id` STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-user`.`author-id` STRAIGHT_JOIN `contact` AS `owner` ON `owner`.`id` = `post-user`.`owner-id` @@ -2021,8 +2126,7 @@ CREATE VIEW `post-user-view` AS SELECT LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-user`.`uri-id` AND `post-user`.`origin` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-user`.`psid` - LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid` - LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; + LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `post-thread-user`.`author-id`; -- -- VIEW post-thread-user-view @@ -2032,7 +2136,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-user`.`id` AS `id`, `post-user`.`id` AS `post-user-id`, `post-thread-user`.`uid` AS `uid`, - `parent-post`.`id` AS `parent`, + `post-thread-user`.`post-user-id` AS `parent`, `item-uri`.`uri` AS `uri`, `post-thread-user`.`uri-id` AS `uri-id`, `parent-item-uri`.`uri` AS `parent-uri`, @@ -2101,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`, @@ -2117,6 +2222,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`, @@ -2129,11 +2235,13 @@ 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`, `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`, @@ -2141,10 +2249,12 @@ 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`, `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`, @@ -2174,11 +2284,12 @@ CREATE VIEW `post-thread-user-view` AS SELECT EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread-user`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, - `parent-post`.`network` AS `parent-network`, - `parent-post`.`author-id` AS `parent-author-id`, - `parent-post-author`.`url` AS `parent-author-link`, - `parent-post-author`.`name` AS `parent-author-name`, - `parent-post-author`.`network` AS `parent-author-network` + `post-thread-user`.`network` AS `parent-network`, + `post-thread-user`.`author-id` AS `parent-author-id`, + `author`.`url` AS `parent-author-link`, + `author`.`name` AS `parent-author-name`, + `author`.`nick` AS `parent-author-nick`, + `author`.`network` AS `parent-author-network` 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` @@ -2197,9 +2308,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-thread-user`.`uri-id` AND `post-thread-user`.`origin` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread-user`.`uri-id` - LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-thread-user`.`psid` - LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-thread-user`.`uid` - LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; + LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-thread-user`.`psid`; -- -- VIEW post-view @@ -2281,6 +2390,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`, @@ -2293,23 +2403,27 @@ 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`, `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`, `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`, `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`, @@ -2318,10 +2432,11 @@ CREATE VIEW `post-view` AS SELECT EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, - `parent-post`.`network` AS `parent-network`, - `parent-post`.`author-id` AS `parent-author-id`, + `post-thread`.`network` AS `parent-network`, + `post-thread`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, + `parent-post-author`.`nick` AS `parent-author-nick`, `parent-post-author`.`network` AS `parent-author-network` FROM `post` STRAIGHT_JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id` @@ -2338,8 +2453,7 @@ CREATE VIEW `post-view` AS SELECT LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post`.`uri-id` LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post`.`uri-id` - LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` - LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; + LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `post-thread`.`author-id`; -- -- VIEW post-thread-view @@ -2421,6 +2535,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`, @@ -2433,11 +2548,13 @@ 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`, `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`, @@ -2445,10 +2562,12 @@ 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`, `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`, @@ -2460,11 +2579,12 @@ CREATE VIEW `post-thread-view` AS SELECT (SELECT COUNT(DISTINCT(`author-id`)) FROM `post` WHERE `parent-uri-id` = `post-thread`.`uri-id` AND `gravity` = 6) AS `total-actors`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, - `parent-post`.`network` AS `parent-network`, - `parent-post`.`author-id` AS `parent-author-id`, - `parent-post-author`.`url` AS `parent-author-link`, - `parent-post-author`.`name` AS `parent-author-name`, - `parent-post-author`.`network` AS `parent-author-network` + `post-thread`.`network` AS `parent-network`, + `post-thread`.`author-id` AS `parent-author-id`, + `author`.`url` AS `parent-author-link`, + `author`.`name` AS `parent-author-name`, + `author`.`nick` AS `parent-author-nick`, + `author`.`network` AS `parent-author-network` FROM `post-thread` INNER JOIN `post` ON `post`.`uri-id` = `post-thread`.`uri-id` STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-thread`.`author-id` @@ -2479,9 +2599,7 @@ CREATE VIEW `post-thread-view` AS SELECT LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` - LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread`.`uri-id` - LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` - LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; + LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread`.`uri-id`; -- -- VIEW category-view @@ -2558,7 +2676,7 @@ CREATE VIEW `tag-view` AS SELECT DROP VIEW IF EXISTS `network-item-view`; CREATE VIEW `network-item-view` AS SELECT `post-user`.`uri-id` AS `uri-id`, - `parent-post`.`id` AS `parent`, + `post-thread-user`.`post-user-id` AS `parent`, `post-user`.`received` AS `received`, `post-thread-user`.`commented` AS `commented`, `post-user`.`created` AS `created`, @@ -2571,17 +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` - STRAIGHT_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` - LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid` + 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 @@ -2589,7 +2706,7 @@ CREATE VIEW `network-item-view` AS SELECT DROP VIEW IF EXISTS `network-thread-view`; CREATE VIEW `network-thread-view` AS SELECT `post-thread-user`.`uri-id` AS `uri-id`, - `parent-post`.`id` AS `parent`, + `post-thread-user`.`post-user-id` AS `parent`, `post-thread-user`.`received` AS `received`, `post-thread-user`.`commented` AS `commented`, `post-thread-user`.`created` AS `created`, @@ -2602,15 +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` - LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid` + 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 @@ -2802,11 +2918,11 @@ CREATE VIEW `account-view` AS SELECT `contact`.`blocked` AS `blocked`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `fcontact`.`guid` AS `diaspora-guid`, - `fcontact`.`batch` AS `diaspora-batch`, - `fcontact`.`notify` AS `diaspora-notify`, - `fcontact`.`poll` AS `diaspora-poll`, - `fcontact`.`alias` AS `diaspora-alias`, + `item-uri`.`guid` AS `diaspora-guid`, + `diaspora-contact`.`batch` AS `diaspora-batch`, + `diaspora-contact`.`notify` AS `diaspora-notify`, + `diaspora-contact`.`poll` AS `diaspora-poll`, + `diaspora-contact`.`alias` AS `diaspora-alias`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`, @@ -2820,11 +2936,13 @@ CREATE VIEW `account-view` AS SELECT `apcontact`.`statuses_count` AS `ap-statuses_count`, `gserver`.`site_name` AS `site_name`, `gserver`.`platform` AS `platform`, - `gserver`.`version` AS `version` + `gserver`.`version` AS `version`, + `gserver`.`blocked` AS `server-blocked`, + `gserver`.`failed` AS `server-failed` FROM `contact` LEFT JOIN `item-uri` ON `item-uri`.`id` = `contact`.`uri-id` LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `contact`.`uri-id` - LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = contact.`uri-id` + LEFT JOIN `diaspora-contact` ON `diaspora-contact`.`uri-id` = contact.`uri-id` LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid` WHERE `contact`.`uid` = 0; @@ -2903,14 +3021,14 @@ CREATE VIEW `account-user-view` AS SELECT `ucontact`.`reason` AS `reason`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `fcontact`.`guid` AS `diaspora-guid`, - `fcontact`.`batch` AS `diaspora-batch`, - `fcontact`.`notify` AS `diaspora-notify`, - `fcontact`.`poll` AS `diaspora-poll`, - `fcontact`.`alias` AS `diaspora-alias`, - `fcontact`.`interacting_count` AS `diaspora-interacting_count`, - `fcontact`.`interacted_count` AS `diaspora-interacted_count`, - `fcontact`.`post_count` AS `diaspora-post_count`, + `item-uri`.`guid` AS `diaspora-guid`, + `diaspora-contact`.`batch` AS `diaspora-batch`, + `diaspora-contact`.`notify` AS `diaspora-notify`, + `diaspora-contact`.`poll` AS `diaspora-poll`, + `diaspora-contact`.`alias` AS `diaspora-alias`, + `diaspora-contact`.`interacting_count` AS `diaspora-interacting_count`, + `diaspora-contact`.`interacted_count` AS `diaspora-interacted_count`, + `diaspora-contact`.`post_count` AS `diaspora-post_count`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`, @@ -2924,12 +3042,14 @@ CREATE VIEW `account-user-view` AS SELECT `apcontact`.`statuses_count` AS `ap-statuses_count`, `gserver`.`site_name` AS `site_name`, `gserver`.`platform` AS `platform`, - `gserver`.`version` AS `version` + `gserver`.`version` AS `version`, + `gserver`.`blocked` AS `server-blocked`, + `gserver`.`failed` AS `server-failed` FROM `contact` AS `ucontact` INNER JOIN `contact` ON `contact`.`uri-id` = `ucontact`.`uri-id` AND `contact`.`uid` = 0 LEFT JOIN `item-uri` ON `item-uri`.`id` = `ucontact`.`uri-id` LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `ucontact`.`uri-id` - LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr' + LEFT JOIN `diaspora-contact` ON `diaspora-contact`.`uri-id` = `ucontact`.`uri-id` LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`; -- @@ -3006,3 +3126,37 @@ CREATE VIEW `profile_field-view` AS SELECT `profile_field`.`edited` AS `edited` FROM `profile_field` INNER JOIN `permissionset` ON `permissionset`.`id` = `profile_field`.`psid`; + +-- +-- VIEW diaspora-contact-view +-- +DROP VIEW IF EXISTS `diaspora-contact-view`; +CREATE VIEW `diaspora-contact-view` AS SELECT + `diaspora-contact`.`uri-id` AS `uri-id`, + `item-uri`.`uri` AS `url`, + `item-uri`.`guid` AS `guid`, + `diaspora-contact`.`addr` AS `addr`, + `diaspora-contact`.`alias` AS `alias`, + `diaspora-contact`.`nick` AS `nick`, + `diaspora-contact`.`name` AS `name`, + `diaspora-contact`.`given-name` AS `given-name`, + `diaspora-contact`.`family-name` AS `family-name`, + `diaspora-contact`.`photo` AS `photo`, + `diaspora-contact`.`photo-medium` AS `photo-medium`, + `diaspora-contact`.`photo-small` AS `photo-small`, + `diaspora-contact`.`batch` AS `batch`, + `diaspora-contact`.`notify` AS `notify`, + `diaspora-contact`.`poll` AS `poll`, + `diaspora-contact`.`subscribe` AS `subscribe`, + `diaspora-contact`.`searchable` AS `searchable`, + `diaspora-contact`.`pubkey` AS `pubkey`, + `gserver`.`url` AS `baseurl`, + `diaspora-contact`.`gsid` AS `gsid`, + `diaspora-contact`.`created` AS `created`, + `diaspora-contact`.`updated` AS `updated`, + `diaspora-contact`.`interacting_count` AS `interacting_count`, + `diaspora-contact`.`interacted_count` AS `interacted_count`, + `diaspora-contact`.`post_count` AS `post_count` + FROM `diaspora-contact` + INNER JOIN `item-uri` ON `item-uri`.`id` = `diaspora-contact`.`uri-id` + LEFT JOIN `gserver` ON `gserver`.`id` = `diaspora-contact`.`gsid`;