X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=01bd84b00b28ed58bc76f467e1478fbc3fba5d58;hb=3546d0aabe9ee7db4ecdeddfb18138fbff5d5c7b;hp=b9e9d284f2ed4669c3104cb894cc29b49592e8ad;hpb=4829c240e9fde18fa1c61113adac716f970e66da;p=friendica.git diff --git a/database.sql b/database.sql index b9e9d284f2..01bd84b00b 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.05-rc (Siberian Iris) --- DB_UPDATE_VERSION 1463 +-- Friendica 2022.09-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1473 -- ------------------------------------------ @@ -297,6 +297,7 @@ CREATE TABLE IF NOT EXISTS `2fa_trusted_browser` ( `cookie_hash` varchar(80) NOT NULL COMMENT 'Trusted cookie hash', `uid` mediumint unsigned NOT NULL COMMENT 'User ID', `user_agent` text COMMENT 'User agent string', + `trusted` boolean NOT NULL DEFAULT '1' COMMENT 'Whenever this browser should be trusted or not', `created` datetime NOT NULL COMMENT 'Datetime the trusted browser was recorded', `last_used` datetime COMMENT 'Datetime the trusted browser was last used', PRIMARY KEY(`cookie_hash`), @@ -386,6 +387,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 +865,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 +1217,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 +1235,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'; @@ -1416,7 +1433,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( CREATE TABLE IF NOT EXISTS `post-user-notification` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `uid` mediumint unsigned NOT NULL COMMENT 'Owner id which owns this copy of the item', - `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', PRIMARY KEY(`uid`,`uri-id`), INDEX `uri-id` (`uri-id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, @@ -1736,6 +1753,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 +1778,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 +1789,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 +1802,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`, @@ -1816,6 +1836,8 @@ CREATE VIEW `post-user-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-user`.`uri-id` AND `post-category`.`uid` = `post-user`.`uid`) AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-user`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -1905,6 +1927,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`, @@ -1929,6 +1952,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`, @@ -1939,8 +1963,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`, @@ -1952,6 +1976,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`, @@ -1985,6 +2010,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-thread-user`.`uri-id` AND `post-category`.`uid` = `post-thread-user`.`uid`) AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread-user`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -2060,6 +2087,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`, @@ -2084,6 +2112,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`, @@ -2094,8 +2123,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`, @@ -2107,6 +2136,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`, @@ -2120,6 +2150,8 @@ CREATE VIEW `post-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + 0 AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -2191,6 +2223,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`, @@ -2215,6 +2248,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`, @@ -2225,8 +2259,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`, @@ -2238,6 +2272,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`, @@ -2251,6 +2286,8 @@ CREATE VIEW `post-thread-view` AS SELECT `post-question`.`multiple` AS `question-multiple`, `post-question`.`voters` AS `question-voters`, `post-question`.`end-time` AS `question-end-time`, + 0 AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -2301,6 +2338,9 @@ CREATE VIEW `collection-view` AS SELECT `post`.`received` AS `received`, `post`.`created` AS `created`, `post-thread`.`commented` AS `commented`, + `post`.`private` AS `private`, + `post`.`visible` AS `visible`, + `post`.`deleted` AS `deleted`, `post`.`thr-parent-id` AS `thr-parent-id`, `post`.`author-id` AS `author-id`, `post`.`gravity` AS `gravity` @@ -2678,6 +2718,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`,