Mandatory index added

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index e91c8b2f6934421e46c875f7cd5ce4c31749d86c..a28094eff986ed6a44db08201acc1512bb62bef0 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
 -- Friendica 2021.12-dev (Siberian Iris)
--- DB_UPDATE_VERSION 1436
+-- DB_UPDATE_VERSION 1440
 -- ------------------------------------------
 
 
@@ -711,8 +711,9 @@ CREATE TABLE IF NOT EXISTS `inbox-status` (
 CREATE TABLE IF NOT EXISTS `intro` (
        `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
        `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
-       `fid` int unsigned COMMENT '',
+       `fid` int unsigned COMMENT 'deprecated',
        `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT '',
+       `suggest-cid` int unsigned COMMENT 'Suggested contact',
        `knowyou` boolean NOT NULL DEFAULT '0' COMMENT '',
        `duplex` boolean NOT NULL DEFAULT '0' COMMENT '',
        `note` text COMMENT '',
@@ -722,9 +723,11 @@ CREATE TABLE IF NOT EXISTS `intro` (
        `ignore` boolean NOT NULL DEFAULT '0' COMMENT '',
         PRIMARY KEY(`id`),
         INDEX `contact-id` (`contact-id`),
+        INDEX `suggest-cid` (`suggest-cid`),
         INDEX `uid` (`uid`),
        FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
-       FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+       FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+       FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
 
 --
@@ -970,6 +973,7 @@ CREATE TABLE IF NOT EXISTS `photo` (
        `title` varchar(255) NOT NULL DEFAULT '' COMMENT '',
        `desc` text COMMENT '',
        `album` varchar(255) NOT NULL DEFAULT '' COMMENT 'The name of the album to which the photo belongs',
+       `photo-type` tinyint unsigned COMMENT 'User avatar, user banner, contact avatar, contact banner or default',
        `filename` varchar(255) NOT NULL DEFAULT '' COMMENT '',
        `type` varchar(30) NOT NULL DEFAULT 'image/jpeg',
        `height` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
@@ -993,6 +997,7 @@ CREATE TABLE IF NOT EXISTS `photo` (
         INDEX `uid_album_scale_created` (`uid`,`album`(32),`scale`,`created`),
         INDEX `uid_album_resource-id_created` (`uid`,`album`(32),`resource-id`,`created`),
         INDEX `resource-id` (`resource-id`),
+        INDEX `uid_photo-type` (`uid`,`photo-type`),
        FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE RESTRICT,
        FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='photo storage';
@@ -2355,6 +2360,7 @@ CREATE VIEW `account-view` AS SELECT
        `contact`.`url` AS `url`,
        `contact`.`nurl` AS `nurl`,
        `contact`.`uri-id` AS `uri-id`,
+       `item-uri`.`guid` AS `guid`,
        `contact`.`addr` AS `addr`,
        `contact`.`alias` AS `alias`,
        `contact`.`name` AS `name`,
@@ -2424,6 +2430,7 @@ CREATE VIEW `account-view` AS SELECT
        `apcontact`.`followers_count` AS `ap-followers_count`,
        `apcontact`.`statuses_count` AS `ap-statuses_count`
        FROM `contact`
+                       LEFT JOIN `item-uri` ON `item-uri`.`id` = `contact`.`uri-id`
                        LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `contact`.`uri-id`
                        LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = contact.`uri-id`
                        WHERE `contact`.`uid` = 0;
@@ -2439,6 +2446,7 @@ CREATE VIEW `account-user-view` AS SELECT
        `contact`.`url` AS `url`,
        `contact`.`nurl` AS `nurl`,
        `contact`.`uri-id` AS `uri-id`,
+       `item-uri`.`guid` AS `guid`,
        `contact`.`addr` AS `addr`,
        `contact`.`alias` AS `alias`,
        `contact`.`name` AS `name`,
@@ -2522,6 +2530,7 @@ CREATE VIEW `account-user-view` AS SELECT
        `apcontact`.`statuses_count` AS `ap-statuses_count`
        FROM `contact` AS `ucontact`
                        INNER JOIN `contact` ON `contact`.`uri-id` = `ucontact`.`uri-id` AND `contact`.`uid` = 0
+                       LEFT JOIN `item-uri` ON `item-uri`.`id` = `ucontact`.`uri-id`
                        LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `ucontact`.`uri-id`
                        LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr';
 
@@ -2579,3 +2588,23 @@ CREATE VIEW `workerqueue-view` AS SELECT
        FROM `process`
                        INNER JOIN `workerqueue` ON `workerqueue`.`pid` = `process`.`pid`
                        WHERE NOT `workerqueue`.`done`;
+
+--
+-- VIEW profile_field-view
+--
+DROP VIEW IF EXISTS `profile_field-view`;
+CREATE VIEW `profile_field-view` AS SELECT 
+       `profile_field`.`id` AS `id`,
+       `profile_field`.`uid` AS `uid`,
+       `profile_field`.`label` AS `label`,
+       `profile_field`.`value` AS `value`,
+       `profile_field`.`order` AS `order`,
+       `profile_field`.`psid` AS `psid`,
+       `permissionset`.`allow_cid` AS `allow_cid`,
+       `permissionset`.`allow_gid` AS `allow_gid`,
+       `permissionset`.`deny_cid` AS `deny_cid`,
+       `permissionset`.`deny_gid` AS `deny_gid`,
+       `profile_field`.`created` AS `created`,
+       `profile_field`.`edited` AS `edited`
+       FROM `profile_field`
+                       INNER JOIN `permissionset` ON `permissionset`.`id` = `profile_field`.`psid`;