X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=database.sql;h=59320fe8729f96ce72e8cdf1c3aed10d29b5dd1b;hb=26df7595ac5366bd0f14f94ef961cf8af6b72ebb;hp=cf430b8ad4e016ea6c7a9e4c6f80f6d46629ed00;hpb=ce8e200461ed777fb31a9d3c2f07c7e8c31b3b56;p=friendica.git diff --git a/database.sql b/database.sql index cf430b8ad4..59320fe872 100644 --- a/database.sql +++ b/database.sql @@ -1,9 +1,38 @@ -- ------------------------------------------ --- Friendica 2020.06-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1348 +-- Friendica 2020.09-dev (Red Hot Poker) +-- DB_UPDATE_VERSION 1359 -- ------------------------------------------ +-- +-- 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 '', + `failed` boolean COMMENT 'Connection failed', + PRIMARY KEY(`id`), + UNIQUE INDEX `nurl` (`nurl`(190)) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Global servers'; + -- -- TABLE clients -- @@ -57,6 +86,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 '', @@ -66,11 +96,13 @@ CREATE TABLE IF NOT EXISTS `contact` ( `last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last try to update the contact info', `success_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful contact update', `failure_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed update', + `failed` boolean COMMENT 'Connection failed', `name-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `uri-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `avatar-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `term-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `last-item` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last post', + `last-discovery` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last follower discovery', `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', `blocked` boolean NOT NULL DEFAULT '1' COMMENT 'Node-wide block status', `block_reason` text COMMENT 'Node-wide block reason', @@ -87,6 +119,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 +128,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`), @@ -107,8 +140,13 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `addr_uid` (`addr`(32),`uid`), INDEX `nurl_uid` (`nurl`(32),`uid`), INDEX `nick_uid` (`nick`(32),`uid`), + INDEX `attag_uid` (`attag`(32),`uid`), INDEX `dfrn-id` (`dfrn-id`(64)), - INDEX `issued-id` (`issued-id`(64)) + INDEX `issued-id` (`issued-id`(64)), + INDEX `network_uid_lastupdate` (`network`,`uid`,`last-update`), + INDEX `uid_lastitem` (`uid`,`last-item`), + 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 +247,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 +258,10 @@ 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 `baseurl` (`baseurl`(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 +297,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'; -- @@ -301,8 +344,12 @@ CREATE TABLE IF NOT EXISTS `contact-relation` ( `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact the related contact had interacted with', `relation-cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'related contact who had interacted with the contact', `last-interaction` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last interaction', + `follow-updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last update of the contact relationship', + `follows` boolean NOT NULL DEFAULT '0' COMMENT '', PRIMARY KEY(`cid`,`relation-cid`), - INDEX `relation-cid` (`relation-cid`) + INDEX `relation-cid` (`relation-cid`), + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`relation-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Contact relations'; -- @@ -345,7 +392,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'; -- @@ -418,86 +465,6 @@ CREATE TABLE IF NOT EXISTS `fsuggest` ( PRIMARY KEY(`id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='friend suggestion stuff'; --- --- TABLE gcign --- -CREATE TABLE IF NOT EXISTS `gcign` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Local User id', - `gcid` int unsigned NOT NULL DEFAULT 0 COMMENT 'gcontact.id of ignored contact', - PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `gcid` (`gcid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contacts ignored by friend suggestions'; - --- --- TABLE gcontact --- -CREATE TABLE IF NOT EXISTS `gcontact` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by', - `nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the contacts profile page', - `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the profile photo', - `connect` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `updated` 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 '', - `last_discovery` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last contact discovery', - `archive_date` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - `archived` boolean NOT NULL DEFAULT '0' COMMENT '', - `location` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `about` text COMMENT '', - `keywords` text COMMENT 'puplic keywords (interests)', - `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', - `birthday` varchar(32) NOT NULL DEFAULT '0001-01-01' COMMENT '', - `community` boolean NOT NULL DEFAULT '0' COMMENT '1 if contact is forum account', - `contact-type` tinyint NOT NULL DEFAULT -1 COMMENT '', - `hide` boolean NOT NULL DEFAULT '0' COMMENT '1 = should be hidden from search', - `nsfw` boolean NOT NULL DEFAULT '0' COMMENT '1 = contact posts nsfw content', - `network` char(4) NOT NULL DEFAULT '' COMMENT 'social network protocol', - `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `notify` varchar(255) COMMENT '', - `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', - 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`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='global contacts'; - --- --- TABLE gfollower --- -CREATE TABLE IF NOT EXISTS `gfollower` ( - `gcid` int unsigned NOT NULL DEFAULT 0 COMMENT 'global contact', - `follower-gcid` int unsigned NOT NULL DEFAULT 0 COMMENT 'global contact of the follower', - `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates that the connection has been deleted', - PRIMARY KEY(`gcid`,`follower-gcid`), - INDEX `follower-gcid` (`follower-gcid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Followers of global contacts'; - --- --- TABLE glink --- -CREATE TABLE IF NOT EXISTS `glink` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `cid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `gcid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `zcid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - PRIMARY KEY(`id`), - UNIQUE INDEX `cid_uid_gcid_zcid` (`cid`,`uid`,`gcid`,`zcid`), - INDEX `gcid` (`gcid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='\'friends of friends\' linkages derived from poco'; - -- -- TABLE group -- @@ -523,33 +490,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 +652,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 +671,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 +702,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 +964,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 +984,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 +998,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 +1094,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 +1167,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 +1203,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 +1220,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 +1468,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`,