X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=02e21f137ece7c967386a543059183ffcd014c8c;hb=d12c9a8f581a22f2f9d04b73c85dce890fb037a1;hp=b9e9d284f2ed4669c3104cb894cc29b49592e8ad;hpb=7199a912899ac97724b9009a1d1f855f303b131a;p=friendica.git diff --git a/database.sql b/database.sql index b9e9d284f2..02e21f137e 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.05-rc (Siberian Iris) --- DB_UPDATE_VERSION 1463 +-- Friendica 2022.09-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1475 -- ------------------------------------------ @@ -297,6 +297,7 @@ CREATE TABLE IF NOT EXISTS `2fa_trusted_browser` ( `cookie_hash` varchar(80) NOT NULL COMMENT 'Trusted cookie hash', `uid` mediumint unsigned NOT NULL COMMENT 'User ID', `user_agent` text COMMENT 'User agent string', + `trusted` boolean NOT NULL DEFAULT '1' COMMENT 'Whenever this browser should be trusted or not', `created` datetime NOT NULL COMMENT 'Datetime the trusted browser was recorded', `last_used` datetime COMMENT 'Datetime the trusted browser was last used', PRIMARY KEY(`cookie_hash`), @@ -337,6 +338,7 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `featured-tags` varchar(255) COMMENT 'Address for the collection of featured tags', `manually-approve` boolean COMMENT '', `discoverable` boolean COMMENT 'Mastodon extension: true if profile is published in their directory', + `suspended` boolean COMMENT 'Mastodon extension: true if profile is suspended', `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '', `name` varchar(255) COMMENT '', `about` text COMMENT '', @@ -386,6 +388,22 @@ CREATE TABLE IF NOT EXISTS `application` ( UNIQUE INDEX `client_id` (`client_id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth application'; +-- +-- TABLE application-marker +-- +CREATE TABLE IF NOT EXISTS `application-marker` ( + `application-id` int unsigned NOT NULL COMMENT '', + `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id', + `timeline` varchar(64) NOT NULL COMMENT 'Marker (home, notifications)', + `last_read_id` varchar(255) COMMENT 'Marker id for the timeline', + `version` smallint unsigned COMMENT 'Version number', + `updated_at` datetime COMMENT 'creation time', + PRIMARY KEY(`application-id`,`uid`,`timeline`), + INDEX `uid_id` (`uid`), + FOREIGN KEY (`application-id`) REFERENCES `application` (`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='Timeline marker'; + -- -- TABLE application-token -- @@ -486,23 +504,6 @@ CREATE TABLE IF NOT EXISTS `conv` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='private messages'; --- --- TABLE conversation --- -CREATE TABLE IF NOT EXISTS `conversation` ( - `item-uri` varbinary(255) NOT NULL COMMENT 'Original URI of the item - unrelated to the table with the same name', - `reply-to-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'URI to which this item is a reply', - `conversation-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation URI', - `conversation-href` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation link', - `protocol` tinyint unsigned NOT NULL DEFAULT 255 COMMENT 'The protocol of the item', - `direction` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'How the message arrived here: 1=push, 2=pull', - `source` mediumtext COMMENT 'Original source', - `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Receiving date', - PRIMARY KEY(`item-uri`), - INDEX `conversation-uri` (`conversation-uri`), - INDEX `received` (`received`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Raw data and structure information for messages'; - -- -- TABLE workerqueue -- @@ -707,6 +708,44 @@ CREATE TABLE IF NOT EXISTS `hook` ( UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry'; +-- +-- TABLE inbox-entry +-- +CREATE TABLE IF NOT EXISTS `inbox-entry` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `activity-id` varbinary(255) COMMENT 'id of the incoming activity', + `object-id` varbinary(255) COMMENT '', + `in-reply-to-id` varbinary(255) COMMENT '', + `conversation` varbinary(255) COMMENT '', + `type` varchar(64) COMMENT 'Type of the activity', + `object-type` varchar(64) COMMENT 'Type of the object activity', + `object-object-type` varchar(64) COMMENT 'Type of the object\'s object activity', + `received` datetime COMMENT 'Receiving date', + `activity` mediumtext COMMENT 'The JSON activity', + `signer` varchar(255) COMMENT '', + `push` boolean COMMENT 'Is the entry pushed or have pulled it?', + `trust` boolean COMMENT 'Do we trust this entry?', + `wid` int unsigned COMMENT 'Workerqueue id', + PRIMARY KEY(`id`), + UNIQUE INDEX `activity-id` (`activity-id`), + INDEX `object-id` (`object-id`), + INDEX `received` (`received`), + INDEX `wid` (`wid`), + FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Incoming activity'; + +-- +-- TABLE inbox-entry-receiver +-- +CREATE TABLE IF NOT EXISTS `inbox-entry-receiver` ( + `queue-id` int unsigned NOT NULL COMMENT '', + `uid` mediumint unsigned NOT NULL COMMENT 'User id', + PRIMARY KEY(`queue-id`,`uid`), + INDEX `uid` (`uid`), + FOREIGN KEY (`queue-id`) REFERENCES `inbox-entry` (`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='Receiver for the incoming activity'; + -- -- TABLE inbox-status -- @@ -848,7 +887,7 @@ CREATE TABLE IF NOT EXISTS `notification` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned COMMENT 'Owner User id', `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', - `type` tinyint unsigned COMMENT '', + `type` smallint unsigned COMMENT '', `actor-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the actor that caused the notification', `target-uri-id` int unsigned COMMENT 'Item-uri id of the related post', `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post', @@ -1062,6 +1101,17 @@ CREATE TABLE IF NOT EXISTS `post` ( FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts'; +-- +-- TABLE post-activity +-- +CREATE TABLE IF NOT EXISTS `post-activity` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `activity` mediumtext COMMENT 'Original activity', + `received` datetime COMMENT '', + PRIMARY KEY(`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Original remote activity'; + -- -- TABLE post-category -- @@ -1200,13 +1250,13 @@ CREATE TABLE IF NOT EXISTS `post-link` ( CREATE TABLE IF NOT EXISTS `post-media` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `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 'Media URL', + `url` varbinary(1024) NOT NULL COMMENT 'Media URL', `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Media type', `mimetype` varchar(60) COMMENT '', `height` smallint unsigned COMMENT 'Height of the media', `width` smallint unsigned COMMENT 'Width of the media', - `size` int unsigned COMMENT 'Media size', - `preview` varbinary(255) COMMENT 'Preview URL', + `size` bigint unsigned COMMENT 'Media size', + `preview` varbinary(512) COMMENT 'Preview URL', `preview-height` smallint unsigned COMMENT 'Height of the preview picture', `preview-width` smallint unsigned COMMENT 'Width of the preview picture', `description` text COMMENT '', @@ -1218,7 +1268,7 @@ CREATE TABLE IF NOT EXISTS `post-media` ( `publisher-name` varchar(255) COMMENT 'Name of the publisher of the media', `publisher-image` varbinary(255) COMMENT 'Image of the publisher of the media', PRIMARY KEY(`id`), - UNIQUE INDEX `uri-id-url` (`uri-id`,`url`), + UNIQUE INDEX `uri-id-url` (`uri-id`,`url`(512)), INDEX `uri-id-id` (`uri-id`,`id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Attached media'; @@ -1270,6 +1320,7 @@ CREATE TABLE IF NOT EXISTS `post-tag` ( -- CREATE TABLE IF NOT EXISTS `post-thread` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `conversation-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the conversation uri', `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner', `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item author', `causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation', @@ -1279,12 +1330,14 @@ CREATE TABLE IF NOT EXISTS `post-thread` ( `changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date that something in the conversation changed, indicating clients should fetch the conversation again', `commented` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', PRIMARY KEY(`uri-id`), + INDEX `conversation-id` (`conversation-id`), INDEX `owner-id` (`owner-id`), INDEX `author-id` (`author-id`), INDEX `causer-id` (`causer-id`), INDEX `received` (`received`), INDEX `commented` (`commented`), 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, FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT @@ -1363,6 +1416,7 @@ CREATE TABLE IF NOT EXISTS `post-user` ( -- CREATE TABLE IF NOT EXISTS `post-thread-user` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `conversation-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the conversation uri', `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner', `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item author', `causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation', @@ -1387,6 +1441,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( `post-user-id` int unsigned COMMENT 'Id of the post-user table', PRIMARY KEY(`uid`,`uri-id`), INDEX `uri-id` (`uri-id`), + INDEX `conversation-id` (`conversation-id`), INDEX `owner-id` (`owner-id`), INDEX `author-id` (`author-id`), INDEX `causer-id` (`causer-id`), @@ -1401,6 +1456,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( INDEX `uid_starred` (`uid`,`starred`), INDEX `uid_mention` (`uid`,`mention`), 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, FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, @@ -1416,7 +1472,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( CREATE TABLE IF NOT EXISTS `post-user-notification` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `uid` mediumint unsigned NOT NULL COMMENT 'Owner id which owns this copy of the item', - `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', PRIMARY KEY(`uid`,`uri-id`), INDEX `uri-id` (`uri-id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, @@ -1692,6 +1748,8 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`parent-uri-id` AS `parent-uri-id`, `thr-parent-item-uri`.`uri` AS `thr-parent`, `post-user`.`thr-parent-id` AS `thr-parent-id`, + `conversation-item-uri`.`uri` AS `conversation`, + `post-thread-user`.`conversation-id` AS `conversation-id`, `item-uri`.`guid` AS `guid`, `post-user`.`wall` AS `wall`, `post-user`.`gravity` AS `gravity`, @@ -1736,6 +1794,7 @@ CREATE VIEW `post-user-view` AS SELECT `post-content`.`target` AS `target`, `post-content`.`resource-id` AS `resource-id`, `post-user`.`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`, @@ -1760,6 +1819,7 @@ CREATE VIEW `post-user-view` AS SELECT `contact`.`avatar-date` AS `avatar-date`, `contact`.`thumb` AS `thumb`, `post-user`.`author-id` AS `author-id`, + `author`.`uri-id` AS `author-uri-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`, @@ -1770,8 +1830,8 @@ CREATE VIEW `post-user-view` AS SELECT `author`.`hidden` AS `author-hidden`, `author`.`updated` AS `author-updated`, `author`.`gsid` AS `author-gsid`, - `author`.`uri-id` AS `author-uri-id`, `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`, @@ -1783,6 +1843,7 @@ CREATE VIEW `post-user-view` AS SELECT `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-user`.`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`, @@ -1816,6 +1877,8 @@ CREATE VIEW `post-user-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-user`.`uri-id` AND `post-category`.`uid` = `post-user`.`uid`) AS `has-categories`, + 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`, @@ -1835,6 +1898,7 @@ CREATE VIEW `post-user-view` AS SELECT LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-user`.`uri-id` LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id` LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post-user`.`parent-uri-id` + LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread-user`.`conversation-id` LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post-user`.`external-id` LEFT JOIN `verb` ON `verb`.`id` = `post-user`.`vid` LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id` @@ -1861,6 +1925,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-user`.`parent-uri-id` AS `parent-uri-id`, `thr-parent-item-uri`.`uri` AS `thr-parent`, `post-user`.`thr-parent-id` AS `thr-parent-id`, + `conversation-item-uri`.`uri` AS `conversation`, + `post-thread-user`.`conversation-id` AS `conversation-id`, `item-uri`.`guid` AS `guid`, `post-thread-user`.`wall` AS `wall`, `post-user`.`gravity` AS `gravity`, @@ -1905,6 +1971,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-content`.`target` AS `target`, `post-content`.`resource-id` AS `resource-id`, `post-thread-user`.`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`, @@ -1929,6 +1996,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `contact`.`avatar-date` AS `avatar-date`, `contact`.`thumb` AS `thumb`, `post-thread-user`.`author-id` AS `author-id`, + `author`.`uri-id` AS `author-uri-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`, @@ -1939,8 +2007,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `author`.`hidden` AS `author-hidden`, `author`.`updated` AS `author-updated`, `author`.`gsid` AS `author-gsid`, - `author`.`uri-id` AS `author-uri-id`, `post-thread-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`, @@ -1952,6 +2020,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread-user`.`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`, @@ -1985,6 +2054,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-thread-user`.`uri-id` AND `post-category`.`uid` = `post-thread-user`.`uid`) AS `has-categories`, + 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`, @@ -2003,6 +2074,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-thread-user`.`uri-id` LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id` LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post-user`.`parent-uri-id` + LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread-user`.`conversation-id` LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post-user`.`external-id` LEFT JOIN `verb` ON `verb`.`id` = `post-user`.`vid` LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id` @@ -2025,6 +2097,8 @@ CREATE VIEW `post-view` AS SELECT `post`.`parent-uri-id` AS `parent-uri-id`, `thr-parent-item-uri`.`uri` AS `thr-parent`, `post`.`thr-parent-id` AS `thr-parent-id`, + `conversation-item-uri`.`uri` AS `conversation`, + `post-thread`.`conversation-id` AS `conversation-id`, `item-uri`.`guid` AS `guid`, `post`.`gravity` AS `gravity`, `external-item-uri`.`uri` AS `extid`, @@ -2060,6 +2134,7 @@ CREATE VIEW `post-view` AS SELECT `post-content`.`target` AS `target`, `post-content`.`resource-id` AS `resource-id`, `post`.`author-id` AS `contact-id`, + `author`.`uri-id` AS `contact-uri-id`, `author`.`url` AS `contact-link`, `author`.`addr` AS `contact-addr`, `author`.`name` AS `contact-name`, @@ -2084,6 +2159,7 @@ CREATE VIEW `post-view` AS SELECT `author`.`avatar-date` AS `avatar-date`, `author`.`thumb` AS `thumb`, `post`.`author-id` AS `author-id`, + `author`.`uri-id` AS `author-uri-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, `author`.`name` AS `author-name`, @@ -2094,8 +2170,8 @@ CREATE VIEW `post-view` AS SELECT `author`.`hidden` AS `author-hidden`, `author`.`updated` AS `author-updated`, `author`.`gsid` AS `author-gsid`, - `author`.`uri-id` AS `author-uri-id`, `post`.`owner-id` AS `owner-id`, + `owner`.`uri-id` AS `owner-uri-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, `owner`.`name` AS `owner-name`, @@ -2107,6 +2183,7 @@ CREATE VIEW `post-view` AS SELECT `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `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`, @@ -2120,6 +2197,8 @@ CREATE VIEW `post-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + 0 AS `has-categories`, + 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`, @@ -2137,6 +2216,7 @@ CREATE VIEW `post-view` AS SELECT LEFT JOIN `item-uri` ON `item-uri`.`id` = `post`.`uri-id` LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post`.`thr-parent-id` LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post`.`parent-uri-id` + LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread`.`conversation-id` LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post`.`external-id` LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post`.`uri-id` @@ -2156,6 +2236,8 @@ CREATE VIEW `post-thread-view` AS SELECT `post`.`parent-uri-id` AS `parent-uri-id`, `thr-parent-item-uri`.`uri` AS `thr-parent`, `post`.`thr-parent-id` AS `thr-parent-id`, + `conversation-item-uri`.`uri` AS `conversation`, + `post-thread`.`conversation-id` AS `conversation-id`, `item-uri`.`guid` AS `guid`, `post`.`gravity` AS `gravity`, `external-item-uri`.`uri` AS `extid`, @@ -2191,6 +2273,7 @@ CREATE VIEW `post-thread-view` AS SELECT `post-content`.`target` AS `target`, `post-content`.`resource-id` AS `resource-id`, `post-thread`.`author-id` AS `contact-id`, + `author`.`uri-id` AS `contact-uri-id`, `author`.`url` AS `contact-link`, `author`.`addr` AS `contact-addr`, `author`.`name` AS `contact-name`, @@ -2215,6 +2298,7 @@ CREATE VIEW `post-thread-view` AS SELECT `author`.`avatar-date` AS `avatar-date`, `author`.`thumb` AS `thumb`, `post-thread`.`author-id` AS `author-id`, + `author`.`uri-id` AS `author-uri-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, `author`.`name` AS `author-name`, @@ -2225,8 +2309,8 @@ CREATE VIEW `post-thread-view` AS SELECT `author`.`hidden` AS `author-hidden`, `author`.`updated` AS `author-updated`, `author`.`gsid` AS `author-gsid`, - `author`.`uri-id` AS `author-uri-id`, `post-thread`.`owner-id` AS `owner-id`, + `owner`.`uri-id` AS `owner-uri-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, `owner`.`name` AS `owner-name`, @@ -2238,6 +2322,7 @@ CREATE VIEW `post-thread-view` AS SELECT `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread`.`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`, @@ -2251,6 +2336,8 @@ CREATE VIEW `post-thread-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + 0 AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -2268,6 +2355,7 @@ CREATE VIEW `post-thread-view` AS SELECT LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-thread`.`uri-id` LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post`.`thr-parent-id` LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post`.`parent-uri-id` + LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread`.`conversation-id` LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post`.`external-id` LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread`.`uri-id` @@ -2301,6 +2389,9 @@ CREATE VIEW `collection-view` AS SELECT `post`.`received` AS `received`, `post`.`created` AS `created`, `post-thread`.`commented` AS `commented`, + `post`.`private` AS `private`, + `post`.`visible` AS `visible`, + `post`.`deleted` AS `deleted`, `post`.`thr-parent-id` AS `thr-parent-id`, `post`.`author-id` AS `author-id`, `post`.`gravity` AS `gravity` @@ -2678,6 +2769,9 @@ CREATE VIEW `account-user-view` AS SELECT `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`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`,