X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=d3e75e753e03d619db62898de691c49cdf7b84cf;hb=4226ef4e3ea5b5b0122f25ffad3f2065f838600a;hp=addd396ffd2d10b3924229eadbfae16f9a030069;hpb=3d455645e7a1e10d1420d6105e989c24c4d24b41;p=friendica.git diff --git a/database.sql b/database.sql index addd396ffd..d3e75e753e 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2020.09-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1367 +-- Friendica 2020.12-dev (Red Hot Poker) +-- DB_UPDATE_VERSION 1372 -- ------------------------------------------ @@ -654,6 +654,7 @@ CREATE TABLE IF NOT EXISTS `item` ( `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', @@ -739,9 +740,11 @@ CREATE TABLE IF NOT EXISTS `item` ( 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'; @@ -772,6 +775,7 @@ CREATE TABLE IF NOT EXISTS `item-content` ( `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', @@ -786,6 +790,7 @@ CREATE TABLE IF NOT EXISTS `item-content` ( `verb` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams verb', PRIMARY KEY(`id`), UNIQUE INDEX `uri-plink-hash` (`uri-plink-hash`), + FULLTEXT INDEX `title-content-warning-body` (`title`,`content-warning`,`body`), INDEX `uri` (`uri`(191)), INDEX `plink` (`plink`(191)), INDEX `uri-id` (`uri-id`), @@ -1060,6 +1065,27 @@ 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-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 -- @@ -1386,7 +1412,7 @@ CREATE TABLE IF NOT EXISTS `workerqueue` ( 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`), @@ -1445,13 +1471,15 @@ CREATE VIEW `network-item-view` AS SELECT `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`) @@ -1474,13 +1502,15 @@ CREATE VIEW `network-thread-view` AS SELECT `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`)