X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=a988da7bfa1e729f0ea7a6f6794be5a915c47303;hb=51a6b0fcb61692128713f5a7c8d4beb324d9b7ee;hp=cf430b8ad4e016ea6c7a9e4c6f80f6d46629ed00;hpb=ce8e200461ed777fb31a9d3c2f07c7e8c31b3b56;p=friendica.git diff --git a/database.sql b/database.sql index cf430b8ad4..a988da7bfa 100644 --- a/database.sql +++ b/database.sql @@ -1,9 +1,37 @@ -- ------------------------------------------ -- Friendica 2020.06-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1348 +-- DB_UPDATE_VERSION 1353 -- ------------------------------------------ +-- +-- TABLE gserver +-- +CREATE TABLE IF NOT EXISTS `gserver` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `version` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `site_name` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `info` text COMMENT '', + `register_policy` tinyint NOT NULL DEFAULT 0 COMMENT '', + `registered-users` int unsigned NOT NULL DEFAULT 0 COMMENT 'Number of registered users', + `directory-type` tinyint DEFAULT 0 COMMENT 'Type of directory service (Poco, Mastodon)', + `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `network` char(4) NOT NULL DEFAULT '' COMMENT '', + `platform` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `relay-subscribe` boolean NOT NULL DEFAULT '0' COMMENT 'Has the server subscribed to the relay system', + `relay-scope` varchar(10) NOT NULL DEFAULT '' COMMENT 'The scope of messages that the server wants to get', + `detection-method` tinyint unsigned COMMENT 'Method that had been used to detect that server', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `last_poco_query` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', + `last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', + `last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', + PRIMARY KEY(`id`), + UNIQUE INDEX `nurl` (`nurl`(190)) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Global servers'; + -- -- TABLE clients -- @@ -57,6 +85,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `notify` varchar(255) COMMENT '', `poll` varchar(255) COMMENT '', `confirm` varchar(255) COMMENT '', + `subscribe` varchar(255) COMMENT '', `poco` varchar(255) COMMENT '', `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT '', `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -87,6 +116,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `unsearchable` boolean NOT NULL DEFAULT '0' COMMENT 'Contact prefers to not be searchable', `sensitive` boolean NOT NULL DEFAULT '0' COMMENT 'Contact posts sensitive content', `baseurl` varchar(255) DEFAULT '' COMMENT 'baseurl of the contact', + `gsid` int unsigned COMMENT 'Global Server ID', `reason` text COMMENT '', `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '', `info` mediumtext COMMENT '', @@ -95,7 +125,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '', `notify_new_posts` boolean NOT NULL DEFAULT '0' COMMENT '', `fetch_further_information` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `ffi_keyword_blacklist` text COMMENT '', + `ffi_keyword_denylist` text COMMENT '', PRIMARY KEY(`id`), INDEX `uid_name` (`uid`,`name`(190)), INDEX `self_uid` (`self`,`uid`), @@ -108,7 +138,9 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `nurl_uid` (`nurl`(32),`uid`), INDEX `nick_uid` (`nick`(32),`uid`), INDEX `dfrn-id` (`dfrn-id`(64)), - INDEX `issued-id` (`issued-id`(64)) + INDEX `issued-id` (`issued-id`(64)), + INDEX `gsid` (`gsid`), + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contact table'; -- @@ -209,7 +241,9 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `addr` varchar(255) COMMENT '', `alias` varchar(255) COMMENT '', `pubkey` text COMMENT '', + `subscribe` varchar(255) COMMENT '', `baseurl` varchar(255) COMMENT 'baseurl of the ap contact', + `gsid` int unsigned COMMENT 'Global Server ID', `generator` varchar(255) COMMENT 'Name of the contact\'s system', `following_count` int unsigned DEFAULT 0 COMMENT 'Number of following contacts', `followers_count` int unsigned DEFAULT 0 COMMENT 'Number of followers', @@ -218,7 +252,9 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( PRIMARY KEY(`url`), INDEX `addr` (`addr`(32)), INDEX `alias` (`alias`(190)), - INDEX `url` (`followers`(190)) + INDEX `followers` (`followers`(190)), + INDEX `gsid` (`gsid`), + 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'; -- @@ -254,7 +290,7 @@ CREATE TABLE IF NOT EXISTS `auth_codes` ( `scope` varchar(250) NOT NULL DEFAULT '' COMMENT '', PRIMARY KEY(`id`), INDEX `client_id` (`client_id`), - CONSTRAINT `auth_codes-client_id-clients-client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; -- @@ -345,7 +381,7 @@ CREATE TABLE IF NOT EXISTS `diaspora-interaction` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `interaction` mediumtext COMMENT 'The Diaspora interaction', PRIMARY KEY(`uri-id`), - CONSTRAINT `diaspora-interaction-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Signed Diaspora Interaction'; -- @@ -463,13 +499,16 @@ CREATE TABLE IF NOT EXISTS `gcontact` ( `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', `generation` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', `server_url` varchar(255) NOT NULL DEFAULT '' COMMENT 'baseurl of the contacts server', + `gsid` int unsigned COMMENT 'Global Server ID', PRIMARY KEY(`id`), UNIQUE INDEX `nurl` (`nurl`(190)), INDEX `name` (`name`(64)), INDEX `nick` (`nick`(32)), INDEX `addr` (`addr`(64)), INDEX `hide_network_updated` (`hide`,`network`,`updated`), - INDEX `updated` (`updated`) + INDEX `updated` (`updated`), + INDEX `gsid` (`gsid`), + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='global contacts'; -- @@ -523,33 +562,6 @@ CREATE TABLE IF NOT EXISTS `group_member` ( UNIQUE INDEX `gid_contactid` (`gid`,`contact-id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy groups, member info'; --- --- TABLE gserver --- -CREATE TABLE IF NOT EXISTS `gserver` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `version` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `site_name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `info` text COMMENT '', - `register_policy` tinyint NOT NULL DEFAULT 0 COMMENT '', - `registered-users` int unsigned NOT NULL DEFAULT 0 COMMENT 'Number of registered users', - `directory-type` tinyint DEFAULT 0 COMMENT 'Type of directory service (Poco, Mastodon)', - `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `network` char(4) NOT NULL DEFAULT '' COMMENT '', - `platform` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `relay-subscribe` boolean NOT NULL DEFAULT '0' COMMENT 'Has the server subscribed to the relay system', - `relay-scope` varchar(10) NOT NULL DEFAULT '' COMMENT 'The scope of messages that the server wants to get', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `last_poco_query` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - `last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - `last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - PRIMARY KEY(`id`), - UNIQUE INDEX `nurl` (`nurl`(190)) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Global servers'; - -- -- TABLE gserver-tag -- @@ -712,10 +724,10 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `uri-id` (`uri-id`), INDEX `parent-uri-id` (`parent-uri-id`), INDEX `thr-parent-id` (`thr-parent-id`), - CONSTRAINT `item-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, - CONSTRAINT `item-parent-uri-id-item-uri-id` FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, - CONSTRAINT `item-thr-parent-id-item-uri-id` FOREIGN KEY (`thr-parent-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, - CONSTRAINT `item-psid-permissionset-id` FOREIGN KEY (`psid`) REFERENCES `permissionset` (`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, + FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts'; -- @@ -731,7 +743,7 @@ CREATE TABLE IF NOT EXISTS `item-activity` ( UNIQUE INDEX `uri-hash` (`uri-hash`), INDEX `uri` (`uri`(191)), INDEX `uri-id` (`uri-id`), - CONSTRAINT `item-activity-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Activities for items'; -- @@ -762,7 +774,7 @@ CREATE TABLE IF NOT EXISTS `item-content` ( INDEX `uri` (`uri`(191)), INDEX `plink` (`plink`(191)), INDEX `uri-id` (`uri-id`), - CONSTRAINT `item-content-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; -- @@ -1024,8 +1036,8 @@ CREATE TABLE IF NOT EXISTS `post-category` ( `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', PRIMARY KEY(`uri-id`,`uid`,`type`,`tid`), INDEX `uri-id` (`tid`), - CONSTRAINT `post-category-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, - CONSTRAINT `post-category-tid-tag-id` FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to categories'; -- @@ -1044,7 +1056,7 @@ CREATE TABLE IF NOT EXISTS `post-delivery-data` ( `diaspora` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via Diaspora', `ostatus` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via OStatus', PRIMARY KEY(`uri-id`), - CONSTRAINT `post-delivery-data-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items'; -- @@ -1058,9 +1070,9 @@ CREATE TABLE IF NOT EXISTS `post-tag` ( PRIMARY KEY(`uri-id`,`type`,`tid`,`cid`), INDEX `tid` (`tid`), INDEX `cid` (`cid`), - CONSTRAINT `post-tag-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, - CONSTRAINT `post-tag-tid-tag-id` FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, - CONSTRAINT `post-tag-cid-contact-id` FOREIGN KEY (`cid`) 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 (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to tags'; -- @@ -1154,7 +1166,7 @@ CREATE TABLE IF NOT EXISTS `profile_field` ( INDEX `uid` (`uid`), INDEX `order` (`order`), INDEX `psid` (`psid`), - CONSTRAINT `profile_field-psid-permissionset-id` FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT + FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Custom profile fields'; -- @@ -1227,6 +1239,7 @@ CREATE TABLE IF NOT EXISTS `storage` ( -- CREATE TABLE IF NOT EXISTS `thread` ( `iid` int unsigned NOT NULL DEFAULT 0 COMMENT 'sequential ID', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the item uri', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT '', `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner', @@ -1262,7 +1275,9 @@ CREATE TABLE IF NOT EXISTS `thread` ( INDEX `uid_received` (`uid`,`received`), INDEX `uid_commented` (`uid`,`commented`), INDEX `uid_wall_received` (`uid`,`wall`,`received`), - INDEX `private_wall_origin_commented` (`private`,`wall`,`origin`,`commented`) + INDEX `private_wall_origin_commented` (`private`,`wall`,`origin`,`commented`), + 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='Thread related data'; -- @@ -1277,7 +1292,7 @@ CREATE TABLE IF NOT EXISTS `tokens` ( `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', PRIMARY KEY(`id`), INDEX `client_id` (`client_id`), - CONSTRAINT `tokens-client_id-clients-client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; -- @@ -1525,7 +1540,7 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`bd` AS `bd`, `contact`.`notify_new_posts` AS `notify_new_posts`, `contact`.`fetch_further_information` AS `fetch_further_information`, - `contact`.`ffi_keyword_blacklist` AS `ffi_keyword_blacklist`, + `contact`.`ffi_keyword_denylist` AS `ffi_keyword_denylist`, `user`.`parent-uid` AS `parent-uid`, `user`.`guid` AS `guid`, `user`.`nickname` AS `nickname`,