X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=database.sql;h=95e96a135a6733013f45145a8f976005aaee960a;hb=d2408c1f1b73127d90a7563604d81323784f09c7;hp=5ee4ef053d3ea77eb682ab1743f0ca62798162c2;hpb=6354d7c81da4ef440131374f67620633ca4feef1;p=friendica.git diff --git a/database.sql b/database.sql index 5ee4ef053d..95e96a135a 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2021.09-dev (Siberian Iris) --- DB_UPDATE_VERSION 1426 +-- DB_UPDATE_VERSION 1430 -- ------------------------------------------ @@ -117,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', @@ -133,9 +133,9 @@ CREATE TABLE IF NOT EXISTS `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', @@ -150,8 +150,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 '', @@ -205,8 +205,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`), @@ -462,20 +460,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 -- @@ -1684,7 +1668,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`, @@ -1846,7 +1829,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`, @@ -1993,7 +1975,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`, @@ -2115,7 +2096,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`, @@ -2270,7 +2250,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`, @@ -2286,11 +2265,9 @@ CREATE VIEW `owner-view` AS SELECT `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`, @@ -2301,8 +2278,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`, @@ -2394,6 +2369,183 @@ 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`.`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`.`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 --