X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=eff8f83faf819ba88b0e92fae6eccdb5329a745c;hb=aad7bac6866cade9c07bb152e658eb23c4e37426;hp=2121fec97943b3a620e1645481d1b208af2a510f;hpb=aed5e4cc960797a25f184d24d7c81265e05d4ee6;p=friendica.git diff --git a/database.sql b/database.sql index 2121fec979..eff8f83faf 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2021.09-dev (Siberian Iris) --- DB_UPDATE_VERSION 1425 +-- DB_UPDATE_VERSION 1434 -- ------------------------------------------ @@ -94,6 +94,18 @@ CREATE TABLE IF NOT EXISTS `user` ( FOREIGN KEY (`parent-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='The local users'; +-- +-- TABLE item-uri +-- +CREATE TABLE IF NOT EXISTS `item-uri` ( + `id` int unsigned NOT NULL auto_increment, + `uri` varbinary(255) NOT NULL COMMENT 'URI of an item', + `guid` varbinary(255) COMMENT 'A unique identifier for an item', + PRIMARY KEY(`id`), + UNIQUE INDEX `uri` (`uri`), + INDEX `guid` (`guid`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='URI and GUID for items'; + -- -- TABLE contact -- @@ -105,7 +117,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self', `remote_self` boolean NOT NULL DEFAULT '0' COMMENT '', `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact', - `duplex` boolean NOT NULL DEFAULT '0' COMMENT '', + `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', `network` char(4) NOT NULL DEFAULT '' COMMENT 'Network of the contact', `protocol` char(4) NOT NULL DEFAULT '' COMMENT 'Protocol of the contact', `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by', @@ -114,18 +126,20 @@ CREATE TABLE IF NOT EXISTS `contact` ( `about` text COMMENT '', `keywords` text COMMENT 'public keywords (interests) of the contact', `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', - `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', + `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '', `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '', `photo` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo of the contact', `thumb` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)', `micro` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (micro size)', `header` varchar(255) COMMENT 'Header picture', - `site-pubkey` text COMMENT '', - `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `site-pubkey` text COMMENT 'Deprecated', + `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', + `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', `pubkey` text COMMENT 'RSA public key 4096 bit', @@ -137,8 +151,8 @@ CREATE TABLE IF NOT EXISTS `contact` ( `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 '', + `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', + `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', `usehub` boolean NOT NULL DEFAULT '0' COMMENT '', `subhub` boolean NOT NULL DEFAULT '0' COMMENT '', `hub-verify` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -192,8 +206,6 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `nurl_uid` (`nurl`(128),`uid`), INDEX `nick_uid` (`nick`(128),`uid`), INDEX `attag_uid` (`attag`(96),`uid`), - INDEX `dfrn-id` (`dfrn-id`(64)), - INDEX `issued-id` (`issued-id`(64)), INDEX `network_uid_lastupdate` (`network`,`uid`,`last-update`), INDEX `uid_network_self_lastupdate` (`uid`,`network`,`self`,`last-update`), INDEX `uid_lastitem` (`uid`,`last-item`), @@ -202,22 +214,12 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `uid_self_contact-type` (`uid`,`self`,`contact-type`), INDEX `self_network_uid` (`self`,`network`,`uid`), INDEX `gsid` (`gsid`), + INDEX `uri-id` (`uri-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contact table'; --- --- TABLE item-uri --- -CREATE TABLE IF NOT EXISTS `item-uri` ( - `id` int unsigned NOT NULL auto_increment, - `uri` varbinary(255) NOT NULL COMMENT 'URI of an item', - `guid` varbinary(255) COMMENT 'A unique identifier for an item', - PRIMARY KEY(`id`), - UNIQUE INDEX `uri` (`uri`), - INDEX `guid` (`guid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='URI and GUID for items'; - -- -- TABLE tag -- @@ -230,21 +232,6 @@ CREATE TABLE IF NOT EXISTS `tag` ( INDEX `url` (`url`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions'; --- --- TABLE clients --- -CREATE TABLE IF NOT EXISTS `clients` ( - `client_id` varchar(20) NOT NULL COMMENT '', - `pw` varchar(20) NOT NULL DEFAULT '' COMMENT '', - `redirect_uri` varchar(200) NOT NULL DEFAULT '' COMMENT '', - `name` text COMMENT '', - `icon` text COMMENT '', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - PRIMARY KEY(`client_id`), - INDEX `uid` (`uid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; - -- -- TABLE permissionset -- @@ -332,6 +319,7 @@ CREATE TABLE IF NOT EXISTS `addon` ( -- CREATE TABLE IF NOT EXISTS `apcontact` ( `url` varbinary(255) NOT NULL COMMENT 'URL of the contact', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', `uuid` varchar(255) COMMENT '', `type` varchar(20) NOT NULL COMMENT '', `following` varchar(255) COMMENT '', @@ -344,6 +332,8 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '', `name` varchar(255) COMMENT '', `about` text COMMENT '', + `xmpp` varchar(255) COMMENT 'XMPP address', + `matrix` varchar(255) COMMENT 'Matrix address', `photo` varchar(255) COMMENT '', `header` varchar(255) COMMENT 'Header picture', `addr` varchar(255) COMMENT '', @@ -364,6 +354,8 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( INDEX `baseurl` (`baseurl`(190)), INDEX `sharedinbox` (`sharedinbox`(190)), INDEX `gsid` (`gsid`), + UNIQUE INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, 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'; @@ -430,20 +422,6 @@ CREATE TABLE IF NOT EXISTS `attach` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='file attachments'; --- --- TABLE auth_codes --- -CREATE TABLE IF NOT EXISTS `auth_codes` ( - `id` varchar(40) NOT NULL COMMENT '', - `client_id` varchar(20) NOT NULL DEFAULT '' COMMENT '', - `redirect_uri` varchar(200) NOT NULL DEFAULT '' COMMENT '', - `expires` int NOT NULL DEFAULT 0 COMMENT '', - `scope` varchar(250) NOT NULL DEFAULT '' COMMENT '', - PRIMARY KEY(`id`), - INDEX `client_id` (`client_id`), - FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; - -- -- TABLE cache -- @@ -456,20 +434,6 @@ CREATE TABLE IF NOT EXISTS `cache` ( INDEX `k_expires` (`k`,`expires`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Stores temporary data'; --- --- TABLE challenge --- -CREATE TABLE IF NOT EXISTS `challenge` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `challenge` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `type` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `last_update` varchar(255) NOT NULL DEFAULT '' COMMENT '', - PRIMARY KEY(`id`), - INDEX `expire` (`expire`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; - -- -- TABLE config -- @@ -531,6 +495,31 @@ CREATE TABLE IF NOT EXISTS `conversation` ( INDEX `received` (`received`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Raw data and structure information for messages'; +-- +-- TABLE workerqueue +-- +CREATE TABLE IF NOT EXISTS `workerqueue` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented worker task id', + `command` varchar(100) COMMENT 'Task command', + `parameter` mediumtext COMMENT 'Task parameter', + `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Task priority', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date', + `pid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Process id of the worker', + `executed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Execution date', + `next_try` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Next retrial date', + `retrial` tinyint NOT NULL DEFAULT 0 COMMENT 'Retrial counter', + `done` boolean NOT NULL DEFAULT '0' COMMENT 'Marked 1 when the task was done - will be deleted later', + PRIMARY KEY(`id`), + INDEX `command` (`command`), + INDEX `done_command_parameter` (`done`,`command`,`parameter`(64)), + INDEX `done_executed` (`done`,`executed`), + INDEX `done_priority_retrial_created` (`done`,`priority`,`retrial`,`created`), + INDEX `done_priority_next_try` (`done`,`priority`,`next_try`), + INDEX `done_pid_next_try` (`done`,`pid`,`next_try`), + INDEX `done_pid_retrial` (`done`,`pid`,`retrial`), + INDEX `done_pid_priority_created` (`done`,`pid`,`priority`,`created`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Background tasks queue entries'; + -- -- TABLE delayed-post -- @@ -539,9 +528,12 @@ CREATE TABLE IF NOT EXISTS `delayed-post` ( `uri` varchar(255) COMMENT 'URI of the post that will be distributed later', `uid` mediumint unsigned COMMENT 'Owner User id', `delayed` datetime COMMENT 'delay time', + `wid` int unsigned COMMENT 'Workerqueue id', PRIMARY KEY(`id`), UNIQUE INDEX `uid_uri` (`uid`,`uri`(190)), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `wid` (`wid`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Posts that are about to be distributed at a later time'; -- @@ -563,6 +555,7 @@ CREATE TABLE IF NOT EXISTS `event` ( `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact_id (ID of the contact in contact table)', `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time', `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time', `start` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'event start time', @@ -581,8 +574,10 @@ CREATE TABLE IF NOT EXISTS `event` ( PRIMARY KEY(`id`), INDEX `uid_start` (`uid`,`start`), INDEX `cid` (`cid`), + INDEX `uri-id` (`uri-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`cid`) REFERENCES `contact` (`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='Events'; -- @@ -592,6 +587,7 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'unique id', `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', `request` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -608,7 +604,9 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', PRIMARY KEY(`id`), INDEX `addr` (`addr`(32)), - UNIQUE INDEX `url` (`url`(190)) + UNIQUE INDEX `url` (`url`(190)), + UNIQUE 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='Diaspora compatible contacts - used in the Diaspora implementation'; -- @@ -1291,6 +1289,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( INDEX `post-user-id` (`post-user-id`), INDEX `commented` (`commented`), INDEX `uid_received` (`uid`,`received`), + INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `uid_pinned` (`uid`,`pinned`), INDEX `uid_commented` (`uid`,`commented`), INDEX `uid_starred` (`uid`,`starred`), @@ -1370,7 +1369,8 @@ CREATE TABLE IF NOT EXISTS `profile` ( `education` text COMMENT 'Deprecated', `contact` text COMMENT 'Deprecated', `homepage` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', + `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', `thumb` varchar(255) NOT NULL DEFAULT '' COMMENT '', `publish` boolean NOT NULL DEFAULT '0' COMMENT 'publish default profile in local directory', @@ -1381,23 +1381,6 @@ CREATE TABLE IF NOT EXISTS `profile` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='user profiles data'; --- --- TABLE profile_check --- -CREATE TABLE IF NOT EXISTS `profile_check` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id', - `dfrn_id` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `sec` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '', - PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `cid` (`cid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='DFRN remote auth use'; - -- -- TABLE profile_field -- @@ -1490,21 +1473,28 @@ CREATE TABLE IF NOT EXISTS `storage` ( ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Data stored by Database storage backend'; -- --- TABLE tokens +-- TABLE subscription -- -CREATE TABLE IF NOT EXISTS `tokens` ( - `id` varchar(40) NOT NULL COMMENT '', - `secret` text COMMENT '', - `client_id` varchar(20) NOT NULL DEFAULT '', - `expires` int NOT NULL DEFAULT 0 COMMENT '', - `scope` varchar(200) NOT NULL DEFAULT '' COMMENT '', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', +CREATE TABLE IF NOT EXISTS `subscription` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented image data id', + `application-id` int unsigned NOT NULL COMMENT '', + `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id', + `endpoint` varchar(511) COMMENT 'Endpoint URL', + `pubkey` varchar(127) COMMENT 'User agent public key', + `secret` varchar(32) COMMENT 'Auth secret', + `follow` boolean COMMENT '', + `favourite` boolean COMMENT '', + `reblog` boolean COMMENT '', + `mention` boolean COMMENT '', + `poll` boolean COMMENT '', + `follow_request` boolean COMMENT '', + `status` boolean COMMENT '', PRIMARY KEY(`id`), - INDEX `client_id` (`client_id`), - INDEX `uid` (`uid`), - FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE, + UNIQUE INDEX `application-id_uid` (`application-id`,`uid`), + INDEX `uid_application-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 usage'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Push Subscription for the API'; -- -- TABLE userd @@ -1540,31 +1530,6 @@ CREATE TABLE IF NOT EXISTS `worker-ipc` ( PRIMARY KEY(`key`) ) ENGINE=MEMORY DEFAULT COLLATE utf8mb4_general_ci COMMENT='Inter process communication between the frontend and the worker'; --- --- TABLE workerqueue --- -CREATE TABLE IF NOT EXISTS `workerqueue` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented worker task id', - `command` varchar(100) COMMENT 'Task command', - `parameter` mediumtext COMMENT 'Task parameter', - `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Task priority', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date', - `pid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Process id of the worker', - `executed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Execution date', - `next_try` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Next retrial date', - `retrial` tinyint NOT NULL DEFAULT 0 COMMENT 'Retrial counter', - `done` boolean NOT NULL DEFAULT '0' COMMENT 'Marked 1 when the task was done - will be deleted later', - PRIMARY KEY(`id`), - INDEX `command` (`command`), - INDEX `done_command_parameter` (`done`,`command`,`parameter`(64)), - INDEX `done_executed` (`done`,`executed`), - INDEX `done_priority_retrial_created` (`done`,`priority`,`retrial`,`created`), - INDEX `done_priority_next_try` (`done`,`priority`,`next_try`), - INDEX `done_pid_next_try` (`done`,`pid`,`next_try`), - INDEX `done_pid_retrial` (`done`,`pid`,`retrial`), - INDEX `done_pid_priority_created` (`done`,`pid`,`priority`,`created`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Background tasks queue entries'; - -- -- VIEW application-view -- @@ -1671,7 +1636,6 @@ CREATE VIEW `post-user-view` AS SELECT `contact`.`uri-date` AS `uri-date`, `contact`.`avatar-date` AS `avatar-date`, `contact`.`thumb` AS `thumb`, - `contact`.`dfrn-id` AS `dfrn-id`, `post-user`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -1833,7 +1797,6 @@ CREATE VIEW `post-thread-user-view` AS SELECT `contact`.`uri-date` AS `uri-date`, `contact`.`avatar-date` AS `avatar-date`, `contact`.`thumb` AS `thumb`, - `contact`.`dfrn-id` AS `dfrn-id`, `post-thread-user`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -1980,7 +1943,6 @@ CREATE VIEW `post-view` AS SELECT `author`.`uri-date` AS `uri-date`, `author`.`avatar-date` AS `avatar-date`, `author`.`thumb` AS `thumb`, - `author`.`dfrn-id` AS `dfrn-id`, `post`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -2102,7 +2064,6 @@ CREATE VIEW `post-thread-view` AS SELECT `author`.`uri-date` AS `uri-date`, `author`.`avatar-date` AS `avatar-date`, `author`.`thumb` AS `thumb`, - `author`.`dfrn-id` AS `dfrn-id`, `post-thread`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -2257,7 +2218,6 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`self` AS `self`, `contact`.`remote_self` AS `remote_self`, `contact`.`rel` AS `rel`, - `contact`.`duplex` AS `duplex`, `contact`.`network` AS `network`, `contact`.`protocol` AS `protocol`, `contact`.`name` AS `name`, @@ -2267,17 +2227,16 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`keywords` AS `keywords`, `contact`.`gender` AS `gender`, `contact`.`xmpp` AS `xmpp`, + `contact`.`matrix` AS `matrix`, `contact`.`attag` AS `attag`, `contact`.`avatar` AS `avatar`, `contact`.`photo` AS `photo`, `contact`.`thumb` AS `thumb`, `contact`.`micro` AS `micro`, `contact`.`header` AS `header`, - `contact`.`site-pubkey` AS `site-pubkey`, - `contact`.`issued-id` AS `issued-id`, - `contact`.`dfrn-id` AS `dfrn-id`, `contact`.`url` AS `url`, `contact`.`nurl` AS `nurl`, + `contact`.`uri-id` AS `uri-id`, `contact`.`addr` AS `addr`, `contact`.`alias` AS `alias`, `contact`.`pubkey` AS `pubkey`, @@ -2288,8 +2247,6 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`poll` AS `poll`, `contact`.`confirm` AS `confirm`, `contact`.`poco` AS `poco`, - `contact`.`aes_allow` AS `aes_allow`, - `contact`.`ret-aes` AS `ret-aes`, `contact`.`usehub` AS `usehub`, `contact`.`subhub` AS `subhub`, `contact`.`hub-verify` AS `hub-verify`, @@ -2381,6 +2338,185 @@ CREATE VIEW `owner-view` AS SELECT INNER JOIN `contact` ON `contact`.`uid` = `user`.`uid` AND `contact`.`self` INNER JOIN `profile` ON `profile`.`uid` = `user`.`uid`; +-- +-- VIEW account-view +-- +DROP VIEW IF EXISTS `account-view`; +CREATE VIEW `account-view` AS SELECT + `contact`.`id` AS `id`, + `contact`.`url` AS `url`, + `contact`.`nurl` AS `nurl`, + `contact`.`uri-id` AS `uri-id`, + `contact`.`addr` AS `addr`, + `contact`.`alias` AS `alias`, + `contact`.`name` AS `name`, + `contact`.`nick` AS `nick`, + `contact`.`about` AS `about`, + `contact`.`keywords` AS `keywords`, + `contact`.`xmpp` AS `xmpp`, + `contact`.`matrix` AS `matrix`, + `contact`.`avatar` AS `avatar`, + `contact`.`photo` AS `photo`, + `contact`.`thumb` AS `thumb`, + `contact`.`micro` AS `micro`, + `contact`.`header` AS `header`, + `contact`.`created` AS `created`, + `contact`.`updated` AS `updated`, + `contact`.`network` AS `network`, + `contact`.`protocol` AS `protocol`, + `contact`.`location` AS `location`, + `contact`.`attag` AS `attag`, + `contact`.`pubkey` AS `pubkey`, + `contact`.`prvkey` AS `prvkey`, + `contact`.`subscribe` AS `subscribe`, + `contact`.`last-update` AS `last-update`, + `contact`.`success_update` AS `success_update`, + `contact`.`failure_update` AS `failure_update`, + `contact`.`failed` AS `failed`, + `contact`.`last-item` AS `last-item`, + `contact`.`last-discovery` AS `last-discovery`, + `contact`.`contact-type` AS `contact-type`, + `contact`.`manually-approve` AS `manually-approve`, + `contact`.`unsearchable` AS `unsearchable`, + `contact`.`sensitive` AS `sensitive`, + `contact`.`baseurl` AS `baseurl`, + `contact`.`gsid` AS `gsid`, + `contact`.`info` AS `info`, + `contact`.`bdyear` AS `bdyear`, + `contact`.`bd` AS `bd`, + `contact`.`poco` AS `poco`, + `contact`.`name-date` AS `name-date`, + `contact`.`uri-date` AS `uri-date`, + `contact`.`avatar-date` AS `avatar-date`, + `contact`.`term-date` AS `term-date`, + `contact`.`hidden` AS `global-ignored`, + `contact`.`blocked` AS `global-blocked`, + `contact`.`hidden` AS `hidden`, + `contact`.`archive` AS `archive`, + `contact`.`deleted` AS `deleted`, + `contact`.`blocked` AS `blocked`, + `contact`.`request` AS `dfrn-request`, + `contact`.`notify` AS `dfrn-notify`, + `contact`.`poll` AS `dfrn-poll`, + `contact`.`confirm` AS `dfrn-confirm`, + `fcontact`.`guid` AS `diaspora-guid`, + `fcontact`.`batch` AS `diaspora-batch`, + `fcontact`.`notify` AS `diaspora-notify`, + `fcontact`.`poll` AS `diaspora-poll`, + `fcontact`.`alias` AS `diaspora-alias`, + `apcontact`.`uuid` AS `ap-uuid`, + `apcontact`.`type` AS `ap-type`, + `apcontact`.`following` AS `ap-following`, + `apcontact`.`followers` AS `ap-followers`, + `apcontact`.`inbox` AS `ap-inbox`, + `apcontact`.`outbox` AS `ap-outbox`, + `apcontact`.`sharedinbox` AS `ap-sharedinbox`, + `apcontact`.`generator` AS `ap-generator`, + `apcontact`.`following_count` AS `ap-following_count`, + `apcontact`.`followers_count` AS `ap-followers_count`, + `apcontact`.`statuses_count` AS `ap-statuses_count` + FROM `contact` + LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `contact`.`uri-id` + LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = contact.`uri-id` + WHERE `contact`.`uid` = 0; + +-- +-- VIEW account-user-view +-- +DROP VIEW IF EXISTS `account-user-view`; +CREATE VIEW `account-user-view` AS SELECT + `ucontact`.`id` AS `id`, + `contact`.`id` AS `pid`, + `ucontact`.`uid` AS `uid`, + `contact`.`url` AS `url`, + `contact`.`nurl` AS `nurl`, + `contact`.`uri-id` AS `uri-id`, + `contact`.`addr` AS `addr`, + `contact`.`alias` AS `alias`, + `contact`.`name` AS `name`, + `contact`.`nick` AS `nick`, + `contact`.`about` AS `about`, + `contact`.`keywords` AS `keywords`, + `contact`.`xmpp` AS `xmpp`, + `contact`.`matrix` AS `matrix`, + `contact`.`avatar` AS `avatar`, + `contact`.`photo` AS `photo`, + `contact`.`thumb` AS `thumb`, + `contact`.`micro` AS `micro`, + `contact`.`header` AS `header`, + `contact`.`created` AS `created`, + `contact`.`updated` AS `updated`, + `ucontact`.`self` AS `self`, + `ucontact`.`remote_self` AS `remote_self`, + `ucontact`.`rel` AS `rel`, + `contact`.`network` AS `network`, + `ucontact`.`protocol` AS `protocol`, + `contact`.`location` AS `location`, + `contact`.`attag` AS `attag`, + `contact`.`pubkey` AS `pubkey`, + `contact`.`prvkey` AS `prvkey`, + `contact`.`subscribe` AS `subscribe`, + `contact`.`last-update` AS `last-update`, + `contact`.`success_update` AS `success_update`, + `contact`.`failure_update` AS `failure_update`, + `contact`.`failed` AS `failed`, + `contact`.`last-item` AS `last-item`, + `contact`.`last-discovery` AS `last-discovery`, + `contact`.`contact-type` AS `contact-type`, + `contact`.`manually-approve` AS `manually-approve`, + `contact`.`unsearchable` AS `unsearchable`, + `contact`.`sensitive` AS `sensitive`, + `contact`.`baseurl` AS `baseurl`, + `contact`.`gsid` AS `gsid`, + `contact`.`info` AS `info`, + `contact`.`bdyear` AS `bdyear`, + `contact`.`bd` AS `bd`, + `contact`.`poco` AS `poco`, + `contact`.`name-date` AS `name-date`, + `contact`.`uri-date` AS `uri-date`, + `contact`.`avatar-date` AS `avatar-date`, + `contact`.`term-date` AS `term-date`, + `contact`.`hidden` AS `global-ignored`, + `contact`.`blocked` AS `global-blocked`, + `ucontact`.`hidden` AS `hidden`, + `ucontact`.`archive` AS `archive`, + `ucontact`.`pending` AS `pending`, + `ucontact`.`deleted` AS `deleted`, + `ucontact`.`notify_new_posts` AS `notify_new_posts`, + `ucontact`.`fetch_further_information` AS `fetch_further_information`, + `ucontact`.`ffi_keyword_denylist` AS `ffi_keyword_denylist`, + `ucontact`.`rating` AS `rating`, + `ucontact`.`readonly` AS `readonly`, + `ucontact`.`blocked` AS `blocked`, + `ucontact`.`block_reason` AS `block_reason`, + `ucontact`.`subhub` AS `subhub`, + `ucontact`.`hub-verify` AS `hub-verify`, + `ucontact`.`reason` AS `reason`, + `contact`.`request` AS `dfrn-request`, + `contact`.`notify` AS `dfrn-notify`, + `contact`.`poll` AS `dfrn-poll`, + `contact`.`confirm` AS `dfrn-confirm`, + `fcontact`.`guid` AS `diaspora-guid`, + `fcontact`.`batch` AS `diaspora-batch`, + `fcontact`.`notify` AS `diaspora-notify`, + `fcontact`.`poll` AS `diaspora-poll`, + `fcontact`.`alias` AS `diaspora-alias`, + `apcontact`.`uuid` AS `ap-uuid`, + `apcontact`.`type` AS `ap-type`, + `apcontact`.`following` AS `ap-following`, + `apcontact`.`followers` AS `ap-followers`, + `apcontact`.`inbox` AS `ap-inbox`, + `apcontact`.`outbox` AS `ap-outbox`, + `apcontact`.`sharedinbox` AS `ap-sharedinbox`, + `apcontact`.`generator` AS `ap-generator`, + `apcontact`.`following_count` AS `ap-following_count`, + `apcontact`.`followers_count` AS `ap-followers_count`, + `apcontact`.`statuses_count` AS `ap-statuses_count` + FROM `contact` AS `ucontact` + INNER JOIN `contact` ON `contact`.`uri-id` = `ucontact`.`uri-id` AND `contact`.`uid` = 0 + LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `ucontact`.`uri-id` + LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr'; + -- -- VIEW pending-view --