replace "p" with higher level database functions

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index 3e7e65e7263230b531738b5997848ce520be891f..b26a9d57ccdb5618bb678e90310f8cfa4d1fd535 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
 -- Friendica 2021.12-dev (Siberian Iris)
--- DB_UPDATE_VERSION 1435
+-- DB_UPDATE_VERSION 1438
 -- ------------------------------------------
 
 
@@ -565,7 +565,6 @@ CREATE TABLE IF NOT EXISTS `event` (
        `location` text COMMENT 'event location',
        `type` varchar(20) NOT NULL DEFAULT '' COMMENT 'event or birthday',
        `nofinish` boolean NOT NULL DEFAULT '0' COMMENT 'if event does have no end this is 1',
-       `adjust` boolean NOT NULL DEFAULT '1' COMMENT 'adjust to timezone of the recipient (0 or 1)',
        `ignore` boolean NOT NULL DEFAULT '0' COMMENT '0 or 1',
        `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'',
        `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
@@ -971,6 +970,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 '',
@@ -994,6 +994,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';
@@ -1699,7 +1700,6 @@ CREATE VIEW `post-user-view` AS SELECT
        `event`.`location` AS `event-location`,
        `event`.`type` AS `event-type`,
        `event`.`nofinish` AS `event-nofinish`,
-       `event`.`adjust` AS `event-adjust`,
        `event`.`ignore` AS `event-ignore`,
        `diaspora-interaction`.`interaction` AS `signed_text`,
        `parent-item-uri`.`guid` AS `parent-guid`,
@@ -1860,7 +1860,6 @@ CREATE VIEW `post-thread-user-view` AS SELECT
        `event`.`location` AS `event-location`,
        `event`.`type` AS `event-type`,
        `event`.`nofinish` AS `event-nofinish`,
-       `event`.`adjust` AS `event-adjust`,
        `event`.`ignore` AS `event-ignore`,
        `diaspora-interaction`.`interaction` AS `signed_text`,
        `parent-item-uri`.`guid` AS `parent-guid`,
@@ -2358,6 +2357,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`,
@@ -2427,6 +2427,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;
@@ -2442,6 +2443,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`,
@@ -2525,6 +2527,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';