X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=0dcf9afe80b0b782d77979de8d908038297ec3a9;hb=aefa4d4a08e4d1063c3a2ca72760a0351587a194;hp=1c835587b1bfd5190301656b0d44b99d503ded02;hpb=38196aec674ca9acc5db7e72898b52906f0d9070;p=friendica.git diff --git a/database.sql b/database.sql index 1c835587b1..0dcf9afe80 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2021.03-rc (Red Hot Poker) --- DB_UPDATE_VERSION 1412 +-- Friendica 2021.06-dev (Siberian Iris) +-- DB_UPDATE_VERSION 1417 -- ------------------------------------------ @@ -364,6 +364,43 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='ActivityPub compatible contacts - used in the ActivityPub implementation'; +-- +-- TABLE application +-- +CREATE TABLE IF NOT EXISTS `application` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'generated index', + `client_id` varchar(64) NOT NULL COMMENT '', + `client_secret` varchar(64) NOT NULL COMMENT '', + `name` varchar(255) NOT NULL COMMENT '', + `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', + 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', + 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 -- @@ -934,7 +971,7 @@ CREATE TABLE IF NOT EXISTS `post` ( `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the owner of this item', `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item', `causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation', - `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, bookmark, ...)', + `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, image, article, ...)', `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', `private` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=public, 1=private, 2=unlisted', `global` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -1037,6 +1074,13 @@ CREATE TABLE IF NOT EXISTS `post-media` ( `preview-height` smallint unsigned COMMENT 'Height of the preview picture', `preview-width` smallint unsigned COMMENT 'Width of the preview picture', `description` text COMMENT '', + `name` varchar(255) COMMENT 'Name of the media', + `author-url` varbinary(255) COMMENT 'URL of the author of the media', + `author-name` varchar(255) COMMENT 'Name of the author of the media', + `author-image` varbinary(255) COMMENT 'Image of the author of the media', + `publisher-url` varbinary(255) COMMENT 'URL of the publisher of the 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`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE @@ -1100,7 +1144,8 @@ CREATE TABLE IF NOT EXISTS `post-user` ( `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the owner of this item', `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item', `causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation', - `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, bookmark, ...)', + `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, image, article, ...)', + `post-reason` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Reason why the post arrived at the user', `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', `private` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=public, 1=private, 2=unlisted', `global` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -1462,6 +1507,28 @@ 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` + FROM `application-token` + INNER JOIN `application` ON `application-token`.`application-id` = `application`.`id`; + -- -- VIEW post-user-view -- @@ -1488,6 +1555,7 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`received` AS `received`, `post-thread-user`.`changed` AS `changed`, `post-user`.`post-type` AS `post-type`, + `post-user`.`post-reason` AS `post-reason`, `post-user`.`private` AS `private`, `post-thread-user`.`pubmail` AS `pubmail`, `post-user`.`visible` AS `visible`, @@ -1647,6 +1715,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-thread-user`.`received` AS `received`, `post-thread-user`.`changed` AS `changed`, `post-user`.`post-type` AS `post-type`, + `post-user`.`post-reason` AS `post-reason`, `post-user`.`private` AS `private`, `post-thread-user`.`pubmail` AS `pubmail`, `post-thread-user`.`ignored` AS `ignored`,