X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=f8d5432658564638a521d2b2201dab2ea3a36ddd;hb=b1afcb5ebfb270f7419339a636293890ce2da8ea;hp=d13a3330c2b3a92c85d4118aff33321b9dbe7977;hpb=a662245c744f4d2faa1b533977f7d21b4de6a724;p=friendica.git

diff --git a/database.sql b/database.sql
index d13a3330c2..f8d5432658 100644
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
 -- Friendica 2022.05-rc (Siberian Iris)
--- DB_UPDATE_VERSION 1462
+-- DB_UPDATE_VERSION 1465
 -- ------------------------------------------
 
 
@@ -1155,6 +1155,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
 --
@@ -1790,6 +1816,8 @@ CREATE VIEW `post-user-view` AS SELECT
 	`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`,
@@ -1959,6 +1987,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT
 	`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`,
@@ -2094,6 +2124,8 @@ CREATE VIEW `post-view` AS SELECT
 	`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`,
@@ -2225,6 +2257,8 @@ CREATE VIEW `post-thread-view` AS SELECT
 	`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`,
@@ -2275,6 +2309,9 @@ CREATE VIEW `collection-view` AS SELECT
 	`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`