X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=7663b71590c22ff0c568b1b02af0b48385a9833f;hb=f0c8b26bf1f429a0f97d862f90fda941476730bb;hp=1508f358dc071046bf01410dcea8da338a395c4b;hpb=a943dbb420ca81e63b8ec3c1e508d55004424daf;p=friendica.git diff --git a/database.sql b/database.sql index 1508f358dc..7663b71590 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.05-rc (Siberian Iris) --- DB_UPDATE_VERSION 1462 +-- Friendica 2022.09-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1477 -- ------------------------------------------ @@ -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 -- @@ -631,6 +632,21 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( 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 +-- +CREATE TABLE IF NOT EXISTS `fetch-entry` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `url` varbinary(255) COMMENT 'url that awaiting to be fetched', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of the fetch request', + `wid` int unsigned COMMENT 'Workerqueue id', + PRIMARY KEY(`id`), + UNIQUE INDEX `url` (`url`), + INDEX `created` (`created`), + INDEX `wid` (`wid`), + FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; + -- -- TABLE fsuggest -- @@ -707,6 +723,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 +902,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 +1116,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 -- @@ -1127,6 +1192,7 @@ CREATE TABLE IF NOT EXISTS `post-delivery` ( `created` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', `command` varbinary(32) COMMENT '', `failed` tinyint DEFAULT 0 COMMENT 'Number of times the delivery has failed', + `receivers` mediumtext COMMENT 'JSON encoded array with the receiving contacts', PRIMARY KEY(`uri-id`,`inbox-id`), INDEX `inbox-id_created` (`inbox-id`,`created`), INDEX `uid` (`uid`), @@ -1154,6 +1220,32 @@ CREATE TABLE IF NOT EXISTS `post-delivery-data` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items'; +-- +-- TABLE post-history +-- +CREATE TABLE IF NOT EXISTS `post-history` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of edit', + `title` varchar(255) NOT NULL DEFAULT '' COMMENT 'item title', + `content-warning` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `body` mediumtext COMMENT 'item body content', + `raw-body` mediumtext COMMENT 'Body without embedded media links', + `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', + `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', + `object-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams object type', + `object` text COMMENT 'JSON encoded object structure unless it is an implied object (normal post)', + `target-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams target type if applicable (URI)', + `target` text COMMENT 'JSON encoded target structure if used', + `resource-id` varchar(32) NOT NULL DEFAULT '' COMMENT 'Used to link other tables to items, it identifies the linked resource (e.g. photo) and if set must also set resource_type', + `plink` varchar(255) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source', + PRIMARY KEY(`uri-id`,`edited`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Post history'; + -- -- TABLE post-link -- @@ -1173,13 +1265,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 '', @@ -1191,7 +1283,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'; @@ -1243,6 +1335,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', @@ -1252,12 +1345,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 @@ -1336,6 +1431,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', @@ -1360,6 +1456,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`), @@ -1374,6 +1471,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, @@ -1389,7 +1487,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, @@ -1665,6 +1763,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`, @@ -1689,6 +1789,7 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`global` AS `global`, EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-user`.`uri-id`) AS `featured`, `post-user`.`network` AS `network`, + `post-user`.`protocol` AS `protocol`, `post-user`.`vid` AS `vid`, `post-user`.`psid` AS `psid`, IF (`post-user`.`vid` IS NULL, '', `verb`.`name`) AS `verb`, @@ -1709,6 +1810,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`, @@ -1733,6 +1835,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`, @@ -1743,8 +1846,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`, @@ -1756,6 +1859,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`, @@ -1789,6 +1893,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`, @@ -1808,6 +1914,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` @@ -1834,6 +1941,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`, @@ -1878,6 +1987,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`, @@ -1902,6 +2012,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`, @@ -1912,8 +2023,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`, @@ -1925,6 +2036,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`, @@ -1958,6 +2070,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`, @@ -1976,6 +2090,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` @@ -1998,6 +2113,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`, @@ -2033,6 +2150,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`, @@ -2057,6 +2175,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`, @@ -2067,8 +2186,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`, @@ -2080,6 +2199,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`, @@ -2093,6 +2213,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`, @@ -2110,6 +2232,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` @@ -2129,6 +2252,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`, @@ -2164,6 +2289,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`, @@ -2188,6 +2314,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`, @@ -2198,8 +2325,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`, @@ -2211,6 +2338,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`, @@ -2224,6 +2352,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`, @@ -2241,6 +2371,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` @@ -2274,6 +2405,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` @@ -2651,6 +2785,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`,