X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=02e21f137ece7c967386a543059183ffcd014c8c;hb=8fb0fa292152c84b077875ac9409eae7d2552c39;hp=b7a923618def59740e1d0f759fa2dd0d40c127c7;hpb=2fdd33f504f9284a5834e3c39e1531d99c9df70a;p=friendica.git diff --git a/database.sql b/database.sql index b7a923618d..02e21f137e 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.05-dev (Siberian Iris) --- DB_UPDATE_VERSION 1450 +-- Friendica 2022.09-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1475 -- ------------------------------------------ @@ -232,6 +232,7 @@ CREATE TABLE IF NOT EXISTS `tag` ( `id` int unsigned NOT NULL auto_increment COMMENT '', `name` varchar(96) NOT NULL DEFAULT '' COMMENT '', `url` varbinary(255) NOT NULL DEFAULT '' COMMENT '', + `type` tinyint unsigned COMMENT 'Type of the tag (Unknown, General Collection, Follower Collection or Account)', PRIMARY KEY(`id`), UNIQUE INDEX `type_name_url` (`name`,`url`), INDEX `url` (`url`) @@ -296,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`), @@ -332,8 +334,11 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `inbox` varchar(255) NOT NULL COMMENT '', `outbox` varchar(255) COMMENT '', `sharedinbox` varchar(255) COMMENT '', + `featured` varchar(255) COMMENT 'Address for the collection of featured posts', + `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 '', @@ -383,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 -- @@ -483,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 -- @@ -551,6 +555,18 @@ CREATE TABLE IF NOT EXISTS `diaspora-interaction` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Signed Diaspora Interaction'; +-- +-- TABLE endpoint +-- +CREATE TABLE IF NOT EXISTS `endpoint` ( + `url` varbinary(255) NOT NULL COMMENT 'URL of the contact', + `type` varchar(20) NOT NULL COMMENT '', + `owner-uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', + PRIMARY KEY(`url`), + UNIQUE INDEX `owner-uri-id_type` (`owner-uri-id`,`type`), + FOREIGN KEY (`owner-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='ActivityPub endpoints - used in the ActivityPub implementation'; + -- -- TABLE event -- @@ -644,10 +660,13 @@ CREATE TABLE IF NOT EXISTS `group` ( `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', PRIMARY KEY(`id`), INDEX `uid` (`uid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + 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'; -- @@ -689,18 +708,59 @@ 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 -- CREATE TABLE IF NOT EXISTS `inbox-status` ( `url` varbinary(255) NOT NULL COMMENT 'URL of the inbox', + `uri-id` int unsigned COMMENT 'Item-uri id of inbox url', `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', `previous` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Previous delivery date', `archive` boolean NOT NULL DEFAULT '0' COMMENT 'Is the inbox archived?', `shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?', - PRIMARY KEY(`url`) + PRIMARY KEY(`url`), + 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='Status of ActivityPub inboxes'; -- @@ -827,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', @@ -879,7 +939,7 @@ CREATE TABLE IF NOT EXISTS `notify` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='notifications'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='[Deprecated] User notifications'; -- -- TABLE notify-threads @@ -1041,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 -- @@ -1050,13 +1121,24 @@ CREATE TABLE IF NOT EXISTS `post-category` ( `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', PRIMARY KEY(`uri-id`,`uid`,`type`,`tid`), - INDEX `uri-id` (`tid`), - INDEX `uid` (`uid`), + INDEX `tid` (`tid`), + INDEX `uid_uri-id` (`uid`,`uri-id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to categories'; +-- +-- TABLE post-collection +-- +CREATE TABLE IF NOT EXISTS `post-collection` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0 - Featured', + PRIMARY KEY(`uri-id`,`type`), + INDEX `type` (`type`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Collection of posts'; + -- -- TABLE post-content -- @@ -1085,6 +1167,25 @@ CREATE TABLE IF NOT EXISTS `post-content` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; +-- +-- TABLE post-delivery +-- +CREATE TABLE IF NOT EXISTS `post-delivery` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `inbox-id` int unsigned NOT NULL COMMENT 'Item-uri id of inbox url', + `uid` mediumint unsigned COMMENT 'Delivering user', + `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`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`inbox-id`) REFERENCES `item-uri` (`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 post-delivery-data -- @@ -1104,6 +1205,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 -- @@ -1123,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 '', @@ -1141,10 +1268,37 @@ 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'; +-- +-- TABLE post-question +-- +CREATE TABLE IF NOT EXISTS `post-question` ( + `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', + `multiple` boolean NOT NULL DEFAULT '0' COMMENT 'Multiple choice', + `voters` int unsigned COMMENT 'Number of voters for this question', + `end-time` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Question end time', + PRIMARY KEY(`id`), + 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='Question'; + +-- +-- TABLE post-question-option +-- +CREATE TABLE IF NOT EXISTS `post-question-option` ( + `id` int unsigned NOT NULL COMMENT 'Id of the question', + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `name` varchar(255) COMMENT 'Name of the option', + `replies` int unsigned COMMENT 'Number of replies for this question option', + PRIMARY KEY(`uri-id`,`id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Question option'; + -- -- TABLE post-tag -- @@ -1166,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', @@ -1175,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 @@ -1259,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', @@ -1268,13 +1426,13 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( `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 '', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id which owns this copy of the item', - `pinned` boolean NOT NULL DEFAULT '0' COMMENT 'The thread is pinned on the profile page', + `pinned` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `starred` boolean NOT NULL DEFAULT '0' COMMENT '', `ignored` boolean NOT NULL DEFAULT '0' COMMENT 'Ignore updates for this thread', `wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid', `mention` boolean NOT NULL DEFAULT '0' COMMENT '', `pubmail` boolean NOT NULL DEFAULT '0' COMMENT '', - `forum_mode` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `forum_mode` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Deprecated', `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.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', @@ -1283,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`), @@ -1293,11 +1452,11 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( INDEX `commented` (`commented`), INDEX `uid_received` (`uid`,`received`), INDEX `uid_wall_received` (`uid`,`wall`,`received`), - INDEX `uid_pinned` (`uid`,`pinned`), INDEX `uid_commented` (`uid`,`commented`), 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, @@ -1313,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, @@ -1521,6 +1680,7 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( `ignored` boolean COMMENT 'Posts from this contact are ignored', `collapsed` boolean COMMENT 'Posts from this contact are collapsed', `hidden` boolean COMMENT 'This contact is hidden from the others', + `is-blocked` boolean COMMENT 'User is blocked by this contact', `pending` boolean COMMENT '', `rel` tinyint unsigned COMMENT 'The kind of the relation between the user and the contact', `info` mediumtext COMMENT '', @@ -1588,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`, @@ -1604,14 +1766,13 @@ CREATE VIEW `post-user-view` AS SELECT `post-thread-user`.`pubmail` AS `pubmail`, `post-user`.`visible` AS `visible`, `post-thread-user`.`starred` AS `starred`, - `post-thread-user`.`pinned` AS `pinned`, `post-user`.`unseen` AS `unseen`, `post-user`.`deleted` AS `deleted`, `post-user`.`origin` AS `origin`, `post-thread-user`.`origin` AS `parent-origin`, - `post-thread-user`.`forum_mode` AS `forum_mode`, `post-thread-user`.`mention` AS `mention`, `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`.`vid` AS `vid`, `post-user`.`psid` AS `psid`, @@ -1633,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`, @@ -1657,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`, @@ -1665,7 +1828,10 @@ CREATE VIEW `post-user-view` AS SELECT `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`, `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`, @@ -1674,8 +1840,10 @@ CREATE VIEW `post-user-view` AS SELECT `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`, `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`, @@ -1705,6 +1873,12 @@ CREATE VIEW `post-user-view` AS SELECT `event`.`type` AS `event-type`, `event`.`nofinish` AS `event-nofinish`, `event`.`ignore` AS `event-ignore`, + `post-question`.`id` AS `question-id`, + `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`, @@ -1724,12 +1898,14 @@ 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` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-user`.`uri-id` 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`; @@ -1749,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`, @@ -1766,13 +1944,12 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-thread-user`.`ignored` AS `ignored`, `post-user`.`visible` AS `visible`, `post-thread-user`.`starred` AS `starred`, - `post-thread-user`.`pinned` AS `pinned`, `post-thread-user`.`unseen` AS `unseen`, `post-user`.`deleted` AS `deleted`, `post-thread-user`.`origin` AS `origin`, - `post-thread-user`.`forum_mode` AS `forum_mode`, `post-thread-user`.`mention` AS `mention`, `post-user`.`global` AS `global`, + EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-thread-user`.`uri-id`) AS `featured`, `post-thread-user`.`network` AS `network`, `post-user`.`vid` AS `vid`, `post-thread-user`.`psid` AS `psid`, @@ -1794,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`, @@ -1818,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`, @@ -1826,7 +2005,10 @@ CREATE VIEW `post-thread-user-view` AS SELECT `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`, `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`, @@ -1835,8 +2017,10 @@ CREATE VIEW `post-thread-user-view` AS SELECT `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`, `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`, @@ -1866,6 +2050,12 @@ CREATE VIEW `post-thread-user-view` AS SELECT `event`.`type` AS `event-type`, `event`.`nofinish` AS `event-nofinish`, `event`.`ignore` AS `event-ignore`, + `post-question`.`id` AS `question-id`, + `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`, @@ -1884,12 +2074,14 @@ 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` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread-user`.`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`; @@ -1905,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`, @@ -1919,6 +2113,7 @@ CREATE VIEW `post-view` AS SELECT `post`.`visible` AS `visible`, `post`.`deleted` AS `deleted`, `post`.`global` AS `global`, + EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post`.`uri-id`) AS `featured`, `post`.`network` AS `network`, `post`.`vid` AS `vid`, IF (`post`.`vid` IS NULL, '', `verb`.`name`) AS `verb`, @@ -1939,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`, @@ -1963,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`, @@ -1971,7 +2168,10 @@ CREATE VIEW `post-view` AS SELECT `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`, `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`, @@ -1980,8 +2180,10 @@ CREATE VIEW `post-view` AS SELECT `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`, `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`, @@ -1991,6 +2193,12 @@ CREATE VIEW `post-view` AS SELECT `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, `causer`.`contact-type` AS `causer-contact-type`, + `post-question`.`id` AS `question-id`, + `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`, @@ -2008,10 +2216,12 @@ 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` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post`.`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`; @@ -2026,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`, @@ -2040,6 +2252,7 @@ CREATE VIEW `post-thread-view` AS SELECT `post`.`visible` AS `visible`, `post`.`deleted` AS `deleted`, `post`.`global` AS `global`, + EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-thread`.`uri-id`) AS `featured`, `post-thread`.`network` AS `network`, `post`.`vid` AS `vid`, IF (`post`.`vid` IS NULL, '', `verb`.`name`) AS `verb`, @@ -2060,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`, @@ -2084,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`, @@ -2092,7 +2307,10 @@ CREATE VIEW `post-thread-view` AS SELECT `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`, `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`, @@ -2101,8 +2319,10 @@ CREATE VIEW `post-thread-view` AS SELECT `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`, `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`, @@ -2112,6 +2332,12 @@ CREATE VIEW `post-thread-view` AS SELECT `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, `causer`.`contact-type` AS `causer-contact-type`, + `post-question`.`id` AS `question-id`, + `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`, @@ -2129,10 +2355,12 @@ 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` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread`.`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`; @@ -2150,6 +2378,27 @@ CREATE VIEW `category-view` AS SELECT FROM `post-category` LEFT JOIN `tag` ON `post-category`.`tid` = `tag`.`id`; +-- +-- VIEW collection-view +-- +DROP VIEW IF EXISTS `collection-view`; +CREATE VIEW `collection-view` AS SELECT + `post-collection`.`uri-id` AS `uri-id`, + `post-collection`.`type` AS `type`, + `post`.`author-id` AS `cid`, + `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` + FROM `post-collection` + INNER JOIN `post` ON `post-collection`.`uri-id` = `post`.`uri-id` + INNER JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id`; + -- -- VIEW tag-view -- @@ -2160,7 +2409,8 @@ CREATE VIEW `tag-view` AS SELECT `post-tag`.`tid` AS `tid`, `post-tag`.`cid` AS `cid`, CASE `cid` WHEN 0 THEN `tag`.`name` ELSE `contact`.`name` END AS `name`, - CASE `cid` WHEN 0 THEN `tag`.`url` ELSE `contact`.`url` END AS `url` + CASE `cid` WHEN 0 THEN `tag`.`url` ELSE `contact`.`url` END AS `url`, + CASE `cid` WHEN 0 THEN `tag`.`type` ELSE 1 END AS `tag-type` FROM `post-tag` LEFT JOIN `tag` ON `post-tag`.`tid` = `tag`.`id` LEFT JOIN `contact` ON `post-tag`.`cid` = `contact`.`id`; @@ -2411,10 +2661,8 @@ CREATE VIEW `account-view` AS SELECT `contact`.`archive` AS `archive`, `contact`.`deleted` AS `deleted`, `contact`.`blocked` AS `blocked`, - `contact`.`request` AS `dfrn-request`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `contact`.`confirm` AS `dfrn-confirm`, `fcontact`.`guid` AS `diaspora-guid`, `fcontact`.`batch` AS `diaspora-batch`, `fcontact`.`notify` AS `diaspora-notify`, @@ -2430,11 +2678,15 @@ CREATE VIEW `account-view` AS SELECT `apcontact`.`generator` AS `ap-generator`, `apcontact`.`following_count` AS `ap-following_count`, `apcontact`.`followers_count` AS `ap-followers_count`, - `apcontact`.`statuses_count` AS `ap-statuses_count` + `apcontact`.`statuses_count` AS `ap-statuses_count`, + `gserver`.`site_name` AS `site_name`, + `gserver`.`platform` AS `platform`, + `gserver`.`version` AS `version` 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 `gserver` ON `gserver`.`id` = contact.`gsid` WHERE `contact`.`uid` = 0; -- @@ -2510,15 +2762,16 @@ CREATE VIEW `account-user-view` AS SELECT `ucontact`.`subhub` AS `subhub`, `ucontact`.`hub-verify` AS `hub-verify`, `ucontact`.`reason` AS `reason`, - `contact`.`request` AS `dfrn-request`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `contact`.`confirm` AS `dfrn-confirm`, `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`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`, @@ -2529,12 +2782,16 @@ CREATE VIEW `account-user-view` AS SELECT `apcontact`.`generator` AS `ap-generator`, `apcontact`.`following_count` AS `ap-following_count`, `apcontact`.`followers_count` AS `ap-followers_count`, - `apcontact`.`statuses_count` AS `ap-statuses_count` + `apcontact`.`statuses_count` AS `ap-statuses_count`, + `gserver`.`site_name` AS `site_name`, + `gserver`.`platform` AS `platform`, + `gserver`.`version` AS `version` 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 `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr' + LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`; -- -- VIEW pending-view