X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=bb53181e8840f7df4780bf6197eb68aabe8b4b37;hb=fdf8002df1cd0235700ef4d80938b821665ee145;hp=26f948c2d322fc5abc0b35b05075b05751d934eb;hpb=904ee0ea591894da1dd9d1fc50a444c28966d946;p=friendica.git diff --git a/database.sql b/database.sql index 26f948c2d3..bb53181e88 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.05-dev (Siberian Iris) --- DB_UPDATE_VERSION 1459 +-- Friendica 2022.05-rc (Siberian Iris) +-- DB_UPDATE_VERSION 1461 -- ------------------------------------------ @@ -554,6 +554,18 @@ CREATE TABLE IF NOT EXISTS `diaspora-interaction` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Signed Diaspora Interaction'; +-- +-- TABLE endpoint +-- +CREATE TABLE IF NOT EXISTS `endpoint` ( + `url` varbinary(255) NOT NULL COMMENT 'URL of the contact', + `type` varchar(20) NOT NULL COMMENT '', + `owner-uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', + PRIMARY KEY(`url`), + UNIQUE INDEX `owner-uri-id_type` (`owner-uri-id`,`type`), + FOREIGN KEY (`owner-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='ActivityPub endpoints - used in the ActivityPub implementation'; + -- -- TABLE event -- @@ -700,13 +712,16 @@ CREATE TABLE IF NOT EXISTS `hook` ( -- CREATE TABLE IF NOT EXISTS `inbox-status` ( `url` varbinary(255) NOT NULL COMMENT 'URL of the inbox', + `uri-id` int unsigned COMMENT 'Item-uri id of inbox url', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of this entry', `success` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful delivery', `failure` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed delivery', `previous` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Previous delivery date', `archive` boolean NOT NULL DEFAULT '0' COMMENT 'Is the inbox archived?', `shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?', - PRIMARY KEY(`url`) + PRIMARY KEY(`url`), + INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Status of ActivityPub inboxes'; -- @@ -1102,6 +1117,23 @@ CREATE TABLE IF NOT EXISTS `post-content` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; +-- +-- TABLE post-delivery +-- +CREATE TABLE IF NOT EXISTS `post-delivery` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `inbox-id` int unsigned NOT NULL COMMENT 'Item-uri id of inbox url', + `uid` mediumint unsigned COMMENT 'Delivering user', + `created` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', + `command` varbinary(32) COMMENT '', + PRIMARY KEY(`uri-id`,`inbox-id`), + INDEX `inbox-id_created` (`inbox-id`,`created`), + INDEX `uid` (`uid`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`inbox-id`) REFERENCES `item-uri` (`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='Status of ActivityPub inboxes'; + -- -- TABLE post-delivery-data -- @@ -1707,6 +1739,8 @@ CREATE VIEW `post-user-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, `post-user`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -1716,6 +1750,7 @@ CREATE VIEW `post-user-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-user`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -1872,6 +1907,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, `post-thread-user`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -1881,6 +1918,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread-user`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -2023,6 +2061,8 @@ CREATE VIEW `post-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, `post`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -2032,6 +2072,7 @@ CREATE VIEW `post-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -2150,6 +2191,8 @@ CREATE VIEW `post-thread-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, `post-thread`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -2159,6 +2202,7 @@ CREATE VIEW `post-thread-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -2222,9 +2266,14 @@ CREATE VIEW `collection-view` AS SELECT `post-collection`.`type` AS `type`, `post`.`author-id` AS `cid`, `post`.`received` AS `received`, - `post`.`created` AS `created` + `post`.`created` AS `created`, + `post-thread`.`commented` AS `commented`, + `post`.`thr-parent-id` AS `thr-parent-id`, + `post`.`author-id` AS `author-id`, + `post`.`gravity` AS `gravity` FROM `post-collection` - INNER JOIN `post` ON `post-collection`.`uri-id` = `post`.`uri-id`; + INNER JOIN `post` ON `post-collection`.`uri-id` = `post`.`uri-id` + INNER JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id`; -- -- VIEW tag-view