-- ------------------------------------------
--- Friendica 2020.09-rc (Red Hot Poker)
--- DB_UPDATE_VERSION 1368
+-- Friendica 2020.12-dev (Red Hot Poker)
+-- DB_UPDATE_VERSION 1372
-- ------------------------------------------
`network` char(4) NOT NULL DEFAULT '' COMMENT 'Network from where the item comes from',
`owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the owner of this item',
`author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item',
+ `causer-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation',
`icid` int unsigned COMMENT 'Id of the item-content table entry that contains the whole item content',
`iaid` int unsigned COMMENT 'Id of the item-activity table entry that contains the activity data',
`vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs',
INDEX `uri-id` (`uri-id`),
INDEX `parent-uri-id` (`parent-uri-id`),
INDEX `thr-parent-id` (`thr-parent-id`),
+ INDEX `causer-id` (`causer-id`),
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,
FOREIGN KEY (`thr-parent-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts';
`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',
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-media
+--
+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',
+ `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',
+ `preview-height` smallint unsigned COMMENT 'Height of the preview picture',
+ `preview-width` smallint unsigned COMMENT 'Width of the preview picture',
+ `description` text COMMENT '',
+ PRIMARY KEY(`id`),
+ UNIQUE INDEX `uri-id-url` (`uri-id`,`url`),
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Attached media';
+
--
-- TABLE post-tag
--
PRIMARY KEY(`id`),
INDEX `done_parameter` (`done`,`parameter`(64)),
INDEX `done_executed` (`done`,`executed`),
- INDEX `done_priority_created` (`done`,`priority`,`created`),
+ INDEX `done_priority_retrial_created` (`done`,`priority`,`retrial`,`created`),
INDEX `done_priority_next_try` (`done`,`priority`,`next_try`),
INDEX `done_pid_next_try` (`done`,`pid`,`next_try`),
INDEX `done_pid_retrial` (`done`,`pid`,`retrial`),
`item`.`network` AS `network`,
`item`.`unseen` AS `unseen`,
`item`.`gravity` AS `gravity`,
- `item`.`contact-id` AS `contact-id`
+ `item`.`contact-id` AS `contact-id`,
+ `ownercontact`.`contact-type` AS `contact-type`
FROM `item`
INNER JOIN `thread` ON `thread`.`iid` = `item`.`parent`
STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id`
LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid`
LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id`
LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id`
+ LEFT JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `thread`.`owner-id`
WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated`
AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`)
`thread`.`starred` AS `starred`,
`thread`.`mention` AS `mention`,
`thread`.`network` AS `network`,
- `thread`.`contact-id` AS `contact-id`
+ `thread`.`contact-id` AS `contact-id`,
+ `ownercontact`.`contact-type` AS `contact-type`
FROM `thread`
STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id`
STRAIGHT_JOIN `item` ON `item`.`id` = `thread`.`iid`
LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid`
LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id`
LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id`
+ LEFT JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `thread`.`owner-id`
WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated`
AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`)