X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=database.sql;h=202d5365523d77b093bc95ef0ad8ff11ffe55683;hb=09f29cd577f0d9ef150a609b2870380af71882a2;hp=666a48f66786089cdd74531e57886ae886410025;hpb=f69dab6d1c51d54a9647bc2f191c34954cce2023;p=friendica.git diff --git a/database.sql b/database.sql index 666a48f667..202d536552 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2021.06-dev (Siberian Iris) --- DB_UPDATE_VERSION 1415 +-- Friendica 2021.06-rc (Siberian Iris) +-- DB_UPDATE_VERSION 1420 -- ------------------------------------------ @@ -375,10 +375,34 @@ CREATE TABLE IF NOT EXISTS `application` ( `redirect_uri` varchar(255) NOT NULL COMMENT '', `website` varchar(255) COMMENT '', `scopes` varchar(255) COMMENT '', + `read` boolean COMMENT 'Read scope', + `write` boolean COMMENT 'Write scope', + `follow` boolean COMMENT 'Follow scope', + `push` boolean COMMENT 'Push scope', PRIMARY KEY(`id`), UNIQUE INDEX `client_id` (`client_id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth application'; +-- +-- TABLE application-token +-- +CREATE TABLE IF NOT EXISTS `application-token` ( + `application-id` int unsigned NOT NULL COMMENT '', + `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id', + `code` varchar(64) NOT NULL COMMENT '', + `access_token` varchar(64) NOT NULL COMMENT '', + `created_at` datetime NOT NULL COMMENT 'creation time', + `scopes` varchar(255) COMMENT '', + `read` boolean COMMENT 'Read scope', + `write` boolean COMMENT 'Write scope', + `follow` boolean COMMENT 'Follow scope', + `push` boolean COMMENT 'Push scope', + PRIMARY KEY(`application-id`,`uid`), + INDEX `uid_id` (`uid`,`application-id`), + 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='OAuth user token'; + -- -- TABLE attach -- @@ -727,6 +751,7 @@ CREATE TABLE IF NOT EXISTS `mail` ( `from-photo` varchar(255) NOT NULL DEFAULT '' COMMENT 'contact photo link of the sender', `from-url` varchar(255) NOT NULL DEFAULT '' COMMENT 'profile linke of the sender', `contact-id` varchar(255) COMMENT 'contact.id', + `author-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the author of the mail', `convid` int unsigned COMMENT 'conv.id', `title` varchar(255) NOT NULL DEFAULT '' COMMENT '', `body` mediumtext COMMENT '', @@ -735,7 +760,11 @@ CREATE TABLE IF NOT EXISTS `mail` ( `replied` boolean NOT NULL DEFAULT '0' COMMENT '', `unknown` boolean NOT NULL DEFAULT '0' COMMENT 'if sender not in the contact table this is 1', `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-id` int unsigned COMMENT 'Item-uri id of the related mail', `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related mail', + `thr-parent` varchar(255) COMMENT '', + `thr-parent-id` int unsigned COMMENT 'Id of the item-uri table that contains the thread parent uri', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time of the private message', PRIMARY KEY(`id`), INDEX `uid_seen` (`uid`,`seen`), @@ -743,7 +772,15 @@ CREATE TABLE IF NOT EXISTS `mail` ( INDEX `uri` (`uri`(64)), INDEX `parent-uri` (`parent-uri`(64)), INDEX `contactid` (`contact-id`(32)), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `author-id` (`author-id`), + INDEX `uri-id` (`uri-id`), + INDEX `parent-uri-id` (`parent-uri-id`), + INDEX `thr-parent-id` (`thr-parent-id`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`thr-parent-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='private messages'; -- @@ -1485,6 +1522,29 @@ CREATE TABLE IF NOT EXISTS `workerqueue` ( INDEX `done_pid_priority_created` (`done`,`pid`,`priority`,`created`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Background tasks queue entries'; +-- +-- VIEW application-view +-- +DROP VIEW IF EXISTS `application-view`; +CREATE VIEW `application-view` AS SELECT + `application`.`id` AS `id`, + `application-token`.`uid` AS `uid`, + `application`.`name` AS `name`, + `application`.`redirect_uri` AS `redirect_uri`, + `application`.`website` AS `website`, + `application`.`client_id` AS `client_id`, + `application`.`client_secret` AS `client_secret`, + `application-token`.`code` AS `code`, + `application-token`.`access_token` AS `access_token`, + `application-token`.`created_at` AS `created_at`, + `application-token`.`scopes` AS `scopes`, + `application-token`.`read` AS `read`, + `application-token`.`write` AS `write`, + `application-token`.`follow` AS `follow`, + `application-token`.`push` AS `push` + FROM `application-token` + INNER JOIN `application` ON `application-token`.`application-id` = `application`.`id`; + -- -- VIEW post-user-view -- @@ -1625,7 +1685,9 @@ CREATE VIEW `post-user-view` AS SELECT `parent-post`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, - `parent-post-author`.`network` AS `parent-author-network` + `parent-post-author`.`network` AS `parent-author-network`, + `parent-post-author`.`blocked` AS `parent-author-blocked`, + `parent-post-author`.`hidden` AS `parent-author-hidden` FROM `post-user` STRAIGHT_JOIN `post-thread-user` ON `post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` AND `post-thread-user`.`uid` = `post-user`.`uid` STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-user`.`contact-id`