-- ------------------------------------------
--- Friendica 2022.05-dev (Siberian Iris)
--- DB_UPDATE_VERSION 1457
+-- Friendica 2022.05-rc (Siberian Iris)
+-- DB_UPDATE_VERSION 1461
-- ------------------------------------------
`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`)
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
--
--
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';
--
`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
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 '',
+ 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
--
`publisher-image` varbinary(255) COMMENT 'Image of the publisher of the media',
PRIMARY KEY(`id`),
UNIQUE INDEX `uri-id-url` (`uri-id`,`url`),
+ 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
--
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
+ `author`.`uri-id` AS `author-uri-id`,
`post-user`.`owner-id` AS `owner-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
`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`.`url` AS `causer-link`,
`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`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`parent-post`.`network` AS `parent-network`,
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`;
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
+ `author`.`uri-id` AS `author-uri-id`,
`post-thread-user`.`owner-id` AS `owner-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
`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`.`url` AS `causer-link`,
`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`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`parent-post`.`network` AS `parent-network`,
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`;
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
+ `author`.`uri-id` AS `author-uri-id`,
`post`.`owner-id` AS `owner-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
`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`.`url` AS `causer-link`,
`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`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`parent-post`.`network` AS `parent-network`,
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`;
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
+ `author`.`uri-id` AS `author-uri-id`,
`post-thread`.`owner-id` AS `owner-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
`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`.`url` AS `causer-link`,
`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`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`parent-post`.`network` AS `parent-network`,
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`;
`post-collection`.`type` AS `type`,
`post`.`author-id` AS `cid`,
`post`.`received` AS `received`,
- `post`.`created` AS `created`
+ `post`.`created` AS `created`,
+ `post-thread`.`commented` AS `commented`,
+ `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` ON `post-collection`.`uri-id` = `post`.`uri-id`
+ INNER JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id`;
--
-- VIEW tag-view
`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`;