Avoid warning " Undefined variable $browsershare"

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index d52abf1ccbf8d9ce024c43543f79791f4efe888e..48a75377fe94b99fb5430644fbed98f82a5f547e 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
 -- Friendica 2022.05-dev (Siberian Iris)
--- DB_UPDATE_VERSION 1456
+-- DB_UPDATE_VERSION 1458
 -- ------------------------------------------
 
 
@@ -1067,7 +1067,7 @@ CREATE TABLE IF NOT EXISTS `post-category` (
 --
 CREATE TABLE IF NOT EXISTS `post-collection` (
        `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
-       `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
+       `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0 - Featured',
         PRIMARY KEY(`uri-id`,`type`),
         INDEX `type` (`type`),
        FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
@@ -1161,6 +1161,32 @@ CREATE TABLE IF NOT EXISTS `post-media` (
        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
 --
@@ -1284,7 +1310,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` (
        `changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date that something in the conversation changed, indicating clients should fetch the conversation again',
        `commented` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
        `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id which owns this copy of the item',
-       `pinned` boolean NOT NULL DEFAULT '0' COMMENT 'The thread is pinned on the profile page',
+       `pinned` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated',
        `starred` boolean NOT NULL DEFAULT '0' COMMENT '',
        `ignored` boolean NOT NULL DEFAULT '0' COMMENT 'Ignore updates for this thread',
        `wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid',
@@ -1309,7 +1335,6 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` (
         INDEX `commented` (`commented`),
         INDEX `uid_received` (`uid`,`received`),
         INDEX `uid_wall_received` (`uid`,`wall`,`received`),
-        INDEX `uid_pinned` (`uid`,`pinned`),
         INDEX `uid_commented` (`uid`,`commented`),
         INDEX `uid_starred` (`uid`,`starred`),
         INDEX `uid_mention` (`uid`,`mention`),
@@ -1621,7 +1646,6 @@ CREATE VIEW `post-user-view` AS SELECT
        `post-thread-user`.`pubmail` AS `pubmail`,
        `post-user`.`visible` AS `visible`,
        `post-thread-user`.`starred` AS `starred`,
-       `post-thread-user`.`pinned` AS `pinned`,
        `post-user`.`unseen` AS `unseen`,
        `post-user`.`deleted` AS `deleted`,
        `post-user`.`origin` AS `origin`,
@@ -1722,6 +1746,10 @@ CREATE VIEW `post-user-view` AS SELECT
        `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`,
@@ -1747,6 +1775,7 @@ CREATE VIEW `post-user-view` AS SELECT
                        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`;
@@ -1783,7 +1812,6 @@ CREATE VIEW `post-thread-user-view` AS SELECT
        `post-thread-user`.`ignored` AS `ignored`,
        `post-user`.`visible` AS `visible`,
        `post-thread-user`.`starred` AS `starred`,
-       `post-thread-user`.`pinned` AS `pinned`,
        `post-thread-user`.`unseen` AS `unseen`,
        `post-user`.`deleted` AS `deleted`,
        `post-thread-user`.`origin` AS `origin`,
@@ -1883,6 +1911,10 @@ CREATE VIEW `post-thread-user-view` AS SELECT
        `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`,
@@ -1907,6 +1939,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT
                        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`;
@@ -2009,6 +2042,10 @@ CREATE VIEW `post-view` AS SELECT
        `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`,
@@ -2030,6 +2067,7 @@ CREATE VIEW `post-view` AS SELECT
                        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`;
 
@@ -2131,6 +2169,10 @@ CREATE VIEW `post-thread-view` AS SELECT
        `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`,
@@ -2152,6 +2194,7 @@ CREATE VIEW `post-thread-view` AS SELECT
                        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`;
 
@@ -2169,6 +2212,19 @@ CREATE VIEW `category-view` AS SELECT
        FROM `post-category`
                        LEFT JOIN `tag` ON `post-category`.`tid` = `tag`.`id`;
 
+--
+-- VIEW collection-view
+--
+DROP VIEW IF EXISTS `collection-view`;
+CREATE VIEW `collection-view` AS SELECT 
+       `post-collection`.`uri-id` AS `uri-id`,
+       `post-collection`.`type` AS `type`,
+       `post`.`author-id` AS `cid`,
+       `post`.`received` AS `received`,
+       `post`.`created` AS `created`
+       FROM `post-collection`
+                       INNER JOIN `post` ON `post-collection`.`uri-id` = `post`.`uri-id`;
+
 --
 -- VIEW tag-view
 --