-- ------------------------------------------
-- Friendica 2022.05-rc (Siberian Iris)
--- DB_UPDATE_VERSION 1461
+-- DB_UPDATE_VERSION 1464
-- ------------------------------------------
`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
`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='Status of ActivityPub inboxes';
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for posts for the batch processing';
--
-- TABLE 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
--
`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';
`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`,
`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`,
`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`,
`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`,
`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`,
`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`,
`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`,
`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`,