Changes:

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index 57ce736f13d0ed3a9761e2f541832148edf3037e..10b18d4cae9e993baea6c60319ef9f41585545f7 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
--- Friendica 2022.05-rc (Siberian Iris)
--- DB_UPDATE_VERSION 1466
+-- Friendica 2022.09-dev (Giant Rhubarb)
+-- DB_UPDATE_VERSION 1472
 -- ------------------------------------------
 
 
@@ -386,6 +386,22 @@ CREATE TABLE IF NOT EXISTS `application` (
         UNIQUE INDEX `client_id` (`client_id`)
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth application';
 
+--
+-- TABLE application-marker
+--
+CREATE TABLE IF NOT EXISTS `application-marker` (
+       `application-id` int unsigned NOT NULL COMMENT '',
+       `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id',
+       `timeline` varchar(64) NOT NULL COMMENT 'Marker (home, notifications)',
+       `last_read_id` varchar(255) COMMENT 'Marker id for the timeline',
+       `version` smallint unsigned COMMENT 'Version number',
+       `updated_at` datetime COMMENT 'creation time',
+        PRIMARY KEY(`application-id`,`uid`,`timeline`),
+        INDEX `uid_id` (`uid`),
+       FOREIGN KEY (`application-id`) REFERENCES `application` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+       FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Timeline marker';
+
 --
 -- TABLE application-token
 --
@@ -848,7 +864,7 @@ CREATE TABLE IF NOT EXISTS `notification` (
        `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
        `uid` mediumint unsigned COMMENT 'Owner User id',
        `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs',
-       `type` tinyint unsigned COMMENT '',
+       `type` smallint unsigned COMMENT '',
        `actor-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the actor that caused the notification',
        `target-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',
@@ -1200,13 +1216,13 @@ CREATE TABLE IF NOT EXISTS `post-link` (
 CREATE TABLE IF NOT EXISTS `post-media` (
        `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',
-       `url` varbinary(511) NOT NULL COMMENT 'Media URL',
+       `url` varbinary(1024) NOT NULL COMMENT 'Media URL',
        `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Media type',
        `mimetype` varchar(60) COMMENT '',
        `height` smallint unsigned COMMENT 'Height of the media',
        `width` smallint unsigned COMMENT 'Width of the media',
-       `size` int unsigned COMMENT 'Media size',
-       `preview` varbinary(255) COMMENT 'Preview URL',
+       `size` bigint unsigned COMMENT 'Media size',
+       `preview` varbinary(512) COMMENT 'Preview URL',
        `preview-height` smallint unsigned COMMENT 'Height of the preview picture',
        `preview-width` smallint unsigned COMMENT 'Width of the preview picture',
        `description` text COMMENT '',
@@ -1218,7 +1234,7 @@ CREATE TABLE IF NOT EXISTS `post-media` (
        `publisher-name` varchar(255) COMMENT 'Name of the publisher of the media',
        `publisher-image` varbinary(255) COMMENT 'Image of the publisher of the media',
         PRIMARY KEY(`id`),
-        UNIQUE INDEX `uri-id-url` (`uri-id`,`url`),
+        UNIQUE INDEX `uri-id-url` (`uri-id`,`url`(512)),
         INDEX `uri-id-id` (`uri-id`,`id`),
        FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Attached media';
@@ -1736,6 +1752,7 @@ CREATE VIEW `post-user-view` AS SELECT
        `post-content`.`target` AS `target`,
        `post-content`.`resource-id` AS `resource-id`,
        `post-user`.`contact-id` AS `contact-id`,
+       `contact`.`uri-id` AS `contact-uri-id`,
        `contact`.`url` AS `contact-link`,
        `contact`.`addr` AS `contact-addr`,
        `contact`.`name` AS `contact-name`,
@@ -1760,6 +1777,7 @@ CREATE VIEW `post-user-view` AS SELECT
        `contact`.`avatar-date` AS `avatar-date`,
        `contact`.`thumb` AS `thumb`,
        `post-user`.`author-id` AS `author-id`,
+       `author`.`uri-id` AS `author-uri-id`,
        `author`.`url` AS `author-link`,
        `author`.`addr` AS `author-addr`,
        IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`,
@@ -1770,8 +1788,8 @@ CREATE VIEW `post-user-view` AS SELECT
        `author`.`hidden` AS `author-hidden`,
        `author`.`updated` AS `author-updated`,
        `author`.`gsid` AS `author-gsid`,
-       `author`.`uri-id` AS `author-uri-id`,
        `post-user`.`owner-id` AS `owner-id`,
+       `owner`.`uri-id` AS `owner-uri-id`,
        `owner`.`url` AS `owner-link`,
        `owner`.`addr` AS `owner-addr`,
        IF (`contact`.`url` = `owner`.`url` AND `contact`.`name` != '', `contact`.`name`, `owner`.`name`) AS `owner-name`,
@@ -1783,6 +1801,7 @@ CREATE VIEW `post-user-view` AS SELECT
        `owner`.`updated` AS `owner-updated`,
        `owner`.`contact-type` AS `owner-contact-type`,
        `post-user`.`causer-id` AS `causer-id`,
+       `causer`.`uri-id` AS `causer-uri-id`,
        `causer`.`url` AS `causer-link`,
        `causer`.`addr` AS `causer-addr`,
        `causer`.`name` AS `causer-name`,
@@ -1907,6 +1926,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT
        `post-content`.`target` AS `target`,
        `post-content`.`resource-id` AS `resource-id`,
        `post-thread-user`.`contact-id` AS `contact-id`,
+       `contact`.`uri-id` AS `contact-uri-id`,
        `contact`.`url` AS `contact-link`,
        `contact`.`addr` AS `contact-addr`,
        `contact`.`name` AS `contact-name`,
@@ -1931,6 +1951,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT
        `contact`.`avatar-date` AS `avatar-date`,
        `contact`.`thumb` AS `thumb`,
        `post-thread-user`.`author-id` AS `author-id`,
+       `author`.`uri-id` AS `author-uri-id`,
        `author`.`url` AS `author-link`,
        `author`.`addr` AS `author-addr`,
        IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`,
@@ -1941,8 +1962,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT
        `author`.`hidden` AS `author-hidden`,
        `author`.`updated` AS `author-updated`,
        `author`.`gsid` AS `author-gsid`,
-       `author`.`uri-id` AS `author-uri-id`,
        `post-thread-user`.`owner-id` AS `owner-id`,
+       `owner`.`uri-id` AS `owner-uri-id`,
        `owner`.`url` AS `owner-link`,
        `owner`.`addr` AS `owner-addr`,
        IF (`contact`.`url` = `owner`.`url` AND `contact`.`name` != '', `contact`.`name`, `owner`.`name`) AS `owner-name`,
@@ -1954,6 +1975,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT
        `owner`.`updated` AS `owner-updated`,
        `owner`.`contact-type` AS `owner-contact-type`,
        `post-thread-user`.`causer-id` AS `causer-id`,
+       `causer`.`uri-id` AS `causer-uri-id`,
        `causer`.`url` AS `causer-link`,
        `causer`.`addr` AS `causer-addr`,
        `causer`.`name` AS `causer-name`,
@@ -2064,6 +2086,7 @@ CREATE VIEW `post-view` AS SELECT
        `post-content`.`target` AS `target`,
        `post-content`.`resource-id` AS `resource-id`,
        `post`.`author-id` AS `contact-id`,
+       `author`.`uri-id` AS `contact-uri-id`,
        `author`.`url` AS `contact-link`,
        `author`.`addr` AS `contact-addr`,
        `author`.`name` AS `contact-name`,
@@ -2088,6 +2111,7 @@ CREATE VIEW `post-view` AS SELECT
        `author`.`avatar-date` AS `avatar-date`,
        `author`.`thumb` AS `thumb`,
        `post`.`author-id` AS `author-id`,
+       `author`.`uri-id` AS `author-uri-id`,
        `author`.`url` AS `author-link`,
        `author`.`addr` AS `author-addr`,
        `author`.`name` AS `author-name`,
@@ -2098,8 +2122,8 @@ CREATE VIEW `post-view` AS SELECT
        `author`.`hidden` AS `author-hidden`,
        `author`.`updated` AS `author-updated`,
        `author`.`gsid` AS `author-gsid`,
-       `author`.`uri-id` AS `author-uri-id`,
        `post`.`owner-id` AS `owner-id`,
+       `owner`.`uri-id` AS `owner-uri-id`,
        `owner`.`url` AS `owner-link`,
        `owner`.`addr` AS `owner-addr`,
        `owner`.`name` AS `owner-name`,
@@ -2111,6 +2135,7 @@ CREATE VIEW `post-view` AS SELECT
        `owner`.`updated` AS `owner-updated`,
        `owner`.`contact-type` AS `owner-contact-type`,
        `post`.`causer-id` AS `causer-id`,
+       `causer`.`uri-id` AS `causer-uri-id`,
        `causer`.`url` AS `causer-link`,
        `causer`.`addr` AS `causer-addr`,
        `causer`.`name` AS `causer-name`,
@@ -2197,6 +2222,7 @@ CREATE VIEW `post-thread-view` AS SELECT
        `post-content`.`target` AS `target`,
        `post-content`.`resource-id` AS `resource-id`,
        `post-thread`.`author-id` AS `contact-id`,
+       `author`.`uri-id` AS `contact-uri-id`,
        `author`.`url` AS `contact-link`,
        `author`.`addr` AS `contact-addr`,
        `author`.`name` AS `contact-name`,
@@ -2221,6 +2247,7 @@ CREATE VIEW `post-thread-view` AS SELECT
        `author`.`avatar-date` AS `avatar-date`,
        `author`.`thumb` AS `thumb`,
        `post-thread`.`author-id` AS `author-id`,
+       `author`.`uri-id` AS `author-uri-id`,
        `author`.`url` AS `author-link`,
        `author`.`addr` AS `author-addr`,
        `author`.`name` AS `author-name`,
@@ -2231,8 +2258,8 @@ CREATE VIEW `post-thread-view` AS SELECT
        `author`.`hidden` AS `author-hidden`,
        `author`.`updated` AS `author-updated`,
        `author`.`gsid` AS `author-gsid`,
-       `author`.`uri-id` AS `author-uri-id`,
        `post-thread`.`owner-id` AS `owner-id`,
+       `owner`.`uri-id` AS `owner-uri-id`,
        `owner`.`url` AS `owner-link`,
        `owner`.`addr` AS `owner-addr`,
        `owner`.`name` AS `owner-name`,
@@ -2244,6 +2271,7 @@ CREATE VIEW `post-thread-view` AS SELECT
        `owner`.`updated` AS `owner-updated`,
        `owner`.`contact-type` AS `owner-contact-type`,
        `post-thread`.`causer-id` AS `causer-id`,
+       `causer`.`uri-id` AS `causer-uri-id`,
        `causer`.`url` AS `causer-link`,
        `causer`.`addr` AS `causer-addr`,
        `causer`.`name` AS `causer-name`,
@@ -2689,6 +2717,9 @@ CREATE VIEW `account-user-view` AS SELECT
        `fcontact`.`notify` AS `diaspora-notify`,
        `fcontact`.`poll` AS `diaspora-poll`,
        `fcontact`.`alias` AS `diaspora-alias`,
+       `fcontact`.`interacting_count` AS `diaspora-interacting_count`,
+       `fcontact`.`interacted_count` AS `diaspora-interacted_count`,
+       `fcontact`.`post_count` AS `diaspora-post_count`,
        `apcontact`.`uuid` AS `ap-uuid`,
        `apcontact`.`type` AS `ap-type`,
        `apcontact`.`following` AS `ap-following`,