Merge pull request #9944 from MrPetovan/bug/9905-block-contact-not-found

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index 3b53e76e0539261a07aa1a9a90cc141b3f88cfdc..8e0c51157834cb54d9b892ec37b2317f8c220527 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
 -- Friendica 2021.03-dev (Red Hot Poker)
--- DB_UPDATE_VERSION 1400
+-- DB_UPDATE_VERSION 1402
 -- ------------------------------------------
 
 
@@ -889,7 +889,7 @@ CREATE TABLE IF NOT EXISTS `notify` (
        `msg` mediumtext COMMENT '',
        `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
        `link` varchar(255) NOT NULL DEFAULT '' COMMENT '',
-       `iid` int unsigned COMMENT 'item.id',
+       `iid` int unsigned COMMENT '',
        `parent` int unsigned COMMENT '',
        `uri-id` int unsigned COMMENT 'Item-uri id of the related post',
        `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post',
@@ -915,17 +915,15 @@ CREATE TABLE IF NOT EXISTS `notify` (
 CREATE TABLE IF NOT EXISTS `notify-threads` (
        `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
        `notify-id` int unsigned NOT NULL DEFAULT 0 COMMENT '',
-       `master-parent-item` int unsigned COMMENT '',
+       `master-parent-item` int unsigned COMMENT 'Deprecated',
        `master-parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post',
        `parent-item` int unsigned NOT NULL DEFAULT 0 COMMENT '',
        `receiver-uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
         PRIMARY KEY(`id`),
-        INDEX `master-parent-item` (`master-parent-item`),
         INDEX `master-parent-uri-id` (`master-parent-uri-id`),
         INDEX `receiver-uid` (`receiver-uid`),
         INDEX `notify-id` (`notify-id`),
        FOREIGN KEY (`notify-id`) REFERENCES `notify` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
-       FOREIGN KEY (`master-parent-item`) REFERENCES `item` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
        FOREIGN KEY (`master-parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
        FOREIGN KEY (`receiver-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
@@ -1233,11 +1231,11 @@ CREATE TABLE IF NOT EXISTS `post-user` (
         INDEX `uid_hidden` (`uid`,`hidden`),
         INDEX `event-id` (`event-id`),
         INDEX `uid_wall` (`uid`,`wall`),
-        INDEX `parent-uri-id` (`parent-uri-id`),
+        INDEX `parent-uri-id_uid` (`parent-uri-id`,`uid`),
         INDEX `thr-parent-id` (`thr-parent-id`),
         INDEX `external-id` (`external-id`),
         INDEX `owner-id` (`owner-id`),
-        INDEX `author-id` (`author-id`),
+        INDEX `author-id_uid` (`author-id`,`uid`),
         INDEX `causer-id` (`causer-id`),
         INDEX `vid` (`vid`),
         INDEX `uid_received` (`uid`,`received`),
@@ -1617,6 +1615,7 @@ CREATE VIEW `post-view` AS SELECT
        `post-user`.`unseen` AS `unseen`,
        `post-user`.`deleted` AS `deleted`,
        `post-user`.`origin` AS `origin`,
+       `post-thread-user`.`origin` AS `parent-origin`,
        `post-thread-user`.`forum_mode` AS `forum_mode`,
        `post-thread-user`.`mention` AS `mention`,
        `post-user`.`global` AS `global`,
@@ -1748,7 +1747,6 @@ DROP VIEW IF EXISTS `post-thread-view`;
 CREATE VIEW `post-thread-view` AS SELECT 
        `post-user`.`id` AS `id`,
        `item`.`id` AS `item-id`,
-       `post-user`.`id` AS `iid`,
        `post-user`.`id` AS `post-user-id`,
        `post-thread-user`.`uid` AS `uid`,
        `parent-post`.`id` AS `parent`,
@@ -1936,7 +1934,7 @@ CREATE VIEW `tag-view` AS SELECT
 DROP VIEW IF EXISTS `network-item-view`;
 CREATE VIEW `network-item-view` AS SELECT 
        `post-user`.`uri-id` AS `uri-id`,
-       `post-user`.`parent-uri-id` AS `parent`,
+       `parent-post`.`id` AS `parent`,
        `post-user`.`received` AS `received`,
        `post-thread-user`.`commented` AS `commented`,
        `post-user`.`created` AS `created`,
@@ -1954,6 +1952,7 @@ CREATE VIEW `network-item-view` AS SELECT
                        LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `post-thread-user`.`uid` AND `author`.`cid` = `post-thread-user`.`author-id`
                        LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `post-thread-user`.`uid` AND `owner`.`cid` = `post-thread-user`.`owner-id`
                        INNER JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id`
+                       LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid`
                        WHERE `post-user`.`visible` AND NOT `post-user`.`deleted`
                        AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
                        AND (`post-user`.`hidden` IS NULL OR NOT `post-user`.`hidden`)
@@ -1966,7 +1965,7 @@ CREATE VIEW `network-item-view` AS SELECT
 DROP VIEW IF EXISTS `network-thread-view`;
 CREATE VIEW `network-thread-view` AS SELECT 
        `post-thread-user`.`uri-id` AS `uri-id`,
-       `post-user`.`parent-uri-id` AS `parent`,
+       `parent-post`.`id` AS `parent`,
        `post-thread-user`.`received` AS `received`,
        `post-thread-user`.`commented` AS `commented`,
        `post-thread-user`.`created` AS `created`,
@@ -1982,6 +1981,7 @@ CREATE VIEW `network-thread-view` AS SELECT
                        LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `post-thread-user`.`uid` AND `author`.`cid` = `post-thread-user`.`author-id`
                        LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `post-thread-user`.`uid` AND `owner`.`cid` = `post-thread-user`.`owner-id`
                        LEFT JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id`
+                       LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid`
                        WHERE `post-user`.`visible` AND NOT `post-user`.`deleted`
                        AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
                        AND (`post-thread-user`.`hidden` IS NULL OR NOT `post-thread-user`.`hidden`)
@@ -2152,6 +2152,7 @@ DROP VIEW IF EXISTS `tag-search-view`;
 CREATE VIEW `tag-search-view` AS SELECT 
        `post-tag`.`uri-id` AS `uri-id`,
        `post-user`.`uid` AS `uid`,
+       `post-user`.`id` AS `iid`,
        `post-user`.`private` AS `private`,
        `post-user`.`wall` AS `wall`,
        `post-user`.`origin` AS `origin`,